def reloadStop(self): querystopsql = 'SELECT a.innerid,a.`name` FROM sa_region AS a LEFT JOIN sa_region AS b ON b.parentid=a.innerid WHERE b.parentid IS NULL' stopresultls = SAPeakDataPublic.querysql(querystopsql) if stopresultls != None and len(stopresultls) > 0: for row in stopresultls: stop = Stop(row[0], row[1]) if stop != None: self.stops[row[0]] = stop PLOG.debug("load stop %s,id=%s" % (row[1], row[0])) else: PLOG.debug("load stop failed!") return False return True
def statisticsCurrentDayData(daydate): nextday = daydate + datetime.timedelta(days=1) startquerytime = daydate endquerytime = daydate + datetime.timedelta( hours=SAPeakDataPublic.st.queryunit) while endquerytime <= nextday: acctquerysql = "select acctinputoctets,acctoutputoctets,acctstarttime,acctstoptime,regionid from %s where acctstarttime>='%s' and acctstarttime<'%s'"%\ (SAPeakDataPublic.sadb.tablename,startquerytime.strftime('%Y-%m-%d %H:%M:%S'),endquerytime.strftime('%Y-%m-%d %H:%M:%S')) PLOG.debug("sql=%s" % acctquerysql) startquerytime = endquerytime endquerytime = endquerytime + datetime.timedelta( hours=SAPeakDataPublic.st.queryunit) i = 0 while i < SAPeakDataPublic.st.queryrepeattimes: res = SAPeakDataPublic.querysql(acctquerysql) if res != None: break else: i = i + 1 if i == 3 or res == None: print( "%s statistics data failed! db query appear error %d consecutive times,please execute again later!" % (daydate.strftime('%Y-%m-%d'), SAPeakDataPublic.st.queryrepeattimes)) PLOG.info( "%s statistics data failed! db query appear error %d consecutive times,please execute again later!" % (daydate.strftime('%Y-%m-%d'), SAPeakDataPublic.st.queryrepeattimes)) return # 统计数据 PLOG.trace("start statistics...") for row in res: if row[2] == None or row[3] == None or row[4] == None: PLOG.warn("lack essential data!skip this data") continue regionid = row[4] totalflow = 0 if row[0] != None: totalflow += row[0] if row[1] != None: totalflow += row[1] if row[3].day > row[2].day: # 跨天 endMinute = 23 * 60 + 59 elif row[3].day < row[2].day: PLOG.info( "stoptime day less than starttime day,invalid data,skip") else: endMinute = row[3].hour * 60 + row[3].minute startMinute = row[2].hour * 60 + row[2].minute #startMinute = datetime.datetime.strptime(row[2],'%Y-%m-%d %H:%M:%S') #endMinute = datetime.datetime.strptime(row[3],'%Y-%m-%d %H:%M:%S') totalMinute = endMinute - startMinute + 1 if totalMinute <= 0: PLOG.info("stoptime less than starttime,invalid data,skip") continue if SAStopDefine.stopDc.stops.has_key(regionid): stop = SAStopDefine.stopDc.stops[regionid] startindex = startMinute endindex = endMinute flowOneMinute = float(totalflow) / totalMinute / 1024 / 1024 index = startindex while index <= endindex: stop.dayArray[index][0] += 1 stop.dayArray[index][1] += flowOneMinute if stop.dayArray[index][0] > stop.peakonlinenum: stop.peakonlinenum = stop.dayArray[index][0] stop.peakonlinetime = datetime.datetime( daydate.year, daydate.month, daydate.day, index / 60, index % 60) if stop.dayArray[index][0] > stop.peakbandwidth: stop.peakbandwidth = stop.dayArray[index][1] stop.peakbandwidthtime = datetime.datetime( daydate.year, daydate.month, daydate.day, index / 60, index % 60) index += 1 PLOG.trace("statistics end") # 数据处理结束,输出各站点峰值数据 for stopid, stop in stopsCentor.stops.items(): peakbandwidth = stop.peakbandwidth * 8 / 60 print("%s %s %d %.2f" % (daydate.strftime('%Y-%m-%d'), stop.name, stop.peakonlinenum, peakbandwidth)) PLOG.debug( "%s %s %d %.2f %s %s" % (daydate.strftime('%Y-%m-%d'), stop.name, stop.peakonlinenum, peakbandwidth, stop.peakonlinetime.strftime('%H:%M'), stop.peakbandwidthtime.strftime('%H:%M')))
def statisticsCurrentDayData(daydate) : nextday = daydate+datetime.timedelta(days=1) startquerytime = daydate endquerytime = daydate+datetime.timedelta(hours=SAPeakDataPublic.st.queryunit) while endquerytime<=nextday: acctquerysql = "select acctinputoctets,acctoutputoctets,acctstarttime,acctstoptime,regionid from %s where acctstarttime>='%s' and acctstarttime<'%s'"%\ (SAPeakDataPublic.sadb.tablename,startquerytime.strftime('%Y-%m-%d %H:%M:%S'),endquerytime.strftime('%Y-%m-%d %H:%M:%S')) PLOG.debug("sql=%s"%acctquerysql) startquerytime=endquerytime endquerytime=endquerytime+datetime.timedelta(hours=SAPeakDataPublic.st.queryunit) i = 0 while i<SAPeakDataPublic.st.queryrepeattimes: res = SAPeakDataPublic.querysql(acctquerysql) if res!=None: break else: i = i+1 if i==3 or res==None: print("%s statistics data failed! db query appear error %d consecutive times,please execute again later!"%(daydate.strftime('%Y-%m-%d'),SAPeakDataPublic.st.queryrepeattimes)) PLOG.info("%s statistics data failed! db query appear error %d consecutive times,please execute again later!"%(daydate.strftime('%Y-%m-%d'),SAPeakDataPublic.st.queryrepeattimes)) return # 统计数据 PLOG.trace("start statistics...") for row in res: if row[2] ==None or row[3] ==None or row[4] ==None: PLOG.warn("lack essential data!skip this data") continue regionid = row[4] totalflow = 0 if row[0]!=None: totalflow += row[0] if row[1]!=None: totalflow += row[1] if row[3].day > row[2].day: # 跨天 endMinute = 23*60+59 elif row[3].day < row[2].day: PLOG.info("stoptime day less than starttime day,invalid data,skip") else: endMinute = row[3].hour*60+row[3].minute startMinute = row[2].hour*60+row[2].minute #startMinute = datetime.datetime.strptime(row[2],'%Y-%m-%d %H:%M:%S') #endMinute = datetime.datetime.strptime(row[3],'%Y-%m-%d %H:%M:%S') totalMinute = endMinute-startMinute + 1 if totalMinute <=0: PLOG.info("stoptime less than starttime,invalid data,skip") continue if SAStopDefine.stopDc.stops.has_key(regionid): stop = SAStopDefine.stopDc.stops[regionid] startindex = startMinute endindex = endMinute flowOneMinute = float(totalflow)/totalMinute/1024/1024 index = startindex while index <= endindex: stop.dayArray[index][0] += 1 stop.dayArray[index][1] += flowOneMinute if stop.dayArray[index][0] > stop.peakonlinenum: stop.peakonlinenum = stop.dayArray[index][0] stop.peakonlinetime = datetime.datetime(daydate.year,daydate.month,daydate.day,index/60,index%60) if stop.dayArray[index][0] > stop.peakbandwidth: stop.peakbandwidth = stop.dayArray[index][1] stop.peakbandwidthtime = datetime.datetime(daydate.year,daydate.month,daydate.day,index/60,index%60) index += 1 PLOG.trace("statistics end") # 数据处理结束,输出各站点峰值数据 for stopid,stop in stopsCentor.stops.items(): peakbandwidth = stop.peakbandwidth*8/60 print("%s %s %d %.2f"%(daydate.strftime('%Y-%m-%d'),stop.name,stop.peakonlinenum,peakbandwidth)) PLOG.debug("%s %s %d %.2f %s %s"%(daydate.strftime('%Y-%m-%d'),stop.name,stop.peakonlinenum,peakbandwidth,stop.peakonlinetime.strftime('%H:%M'),stop.peakbandwidthtime.strftime('%H:%M')))