def insertNetInfo(waveCursor, netList):
try:
strLst = []
for netElm in netList:
if netElm['ntype'] == 'w':
strValue = "('%s', %s, '%s', '%s', '%s', %s, unix_timestamp())" % \
(netElm['playSessionID'], netElm['stTime'], netElm['ntype'], \
netElm['bssid'], netElm['ssid'], netElm['traffic'])
else:
strValue = "('%s', %s, '%s', NULL, NULL, %s, unix_timestamp())" % \
(netElm['playSessionID'], netElm['stTime'], netElm['ntype'], netElm['traffic'])
strLst.append(strValue)
if len(strLst) > 0:
sql = """insert into vidsession_net (playSessionID, stTime, ntype, bssid, ssid, traffic, lstuptmp)
values %s
on duplicate key update ntype = values(ntype), bssid = values(bssid), ssid = values(ssid), traffic = values(traffic), lstuptmp = unix_timestamp()
""" % ', '.join(strLst)
ret = waveCursor.execute(sql)
if ret == 0:
log.warn("insertNetInfo no record affected [%s]" % sql)
except Exception, e:
log.error("insertNetInfo error:%s" % e)
log.error(strLst)
raise e
def __makeCellId(self, plmnid, cid, lac):
try:
cellId = map(lambda x: str(x) if str(x).isdigit() else '0', [plmnid, cid, lac])
if 0 not in map(int, cellId) and len(cellId[0]) < 7:
return '_'.join(cellId)
except Exception, e:
log.error(e)
def handler(self, params):
try:
log.info("evtNetworkLog get")
#cocoa Log skip
if params.has_key('evtKey') == False and params.has_key('startTime') == False:
return
# 구 버전의 로그 무시
apatLogVer = map(int, params.get('ver', "1.00.0").split('.'))
if apatLogVer < [1,0,10]:
return
dataVer = map(int, params.get('ansLogVer', "0.5").split('.'))
if dataVer < [1,0] and params.has_key('evtKey') == False:
return
self.logInsert(params)
log.info("logInsert done")
except Exception, e:
log.error("processAnsLog ERROR[deviceID:%s, sID:%s, numTotalHits:%s] %s" % (params['deviceID'], params['sID'], params['numTotalHits'], e) )
if str(e).find('restart') > 0:
log.error("processAnsLog raise e")
raise e
def updateVidnet(waveCursor, vidDict):
if vidDict == None:
log.warn('updateVidnet : vidDict is null')
return False
playSessionID = vidDict.pop('playSessionID')
cols = vidDict.keys()
vals = vidDict.values()
try:
slist = []
for key in vidDict:
if type(vidDict[key]) == str or type(vidDict[key]) == unicode:
s = "%s = '%s'" % (key, vidDict[key].replace("'", "''"))
else:
s = "%s = %s" % (key, str(vidDict[key]))
slist.append(s)
slist.append("lstuptmp = unix_timestamp()")
#sql = "UPDATE vidsession SET %s WHERE playSessionID = '%s'" % (unicode(',', 'utf-8').join(map(lambda key:"%s='%s'" % (key, unicode(vidDict[key], 'utf-8').replace("'", "''")), vidDict)), playSessionID)
sql = "UPDATE vidsession SET %s WHERE playSessionID = '%s'" % (unicode(',', 'utf-8').join(slist), playSessionID)
waveCursor.execute(sql)
except Exception, e:
log.error("update vidsession ERROR:%s, playSessionID:%s" % (e, playSessionID))
log.error(vidDict)
raise e
def getBBinfo(rBBList, aatLog):
try:
if aatLog.has_key('bbCount') == False or aatLog.has_key('bbList') == False:
return
BBcount = aatLog['bbCount']
if BBcount == 0:
return
elif BBcount > 40:
BBcount = 40
if isinstance(aatLog['bbList'], list):
bblst = aatLog['bbList'][0:BBcount]
else:
bblst = aatLog['bbList'].strip('[ ]').split(',')
bblst = bblst[0:BBcount]
bblst = bblst[0:BBcount]
bbdict = {}
for bbItem in bblst:
if bbItem.find('|') < 0: continue
bbElm = bbItem.strip(" u'\"").split('|')
bbdict['psid'] = aatLog['playSessionId']
bbdict['bb'] = list(bbElm)
rBBList.append(bbdict.copy())
except Exception, e:
log.error("getBBinfo error:%s" % e)
log.error(aatLog)
raise e
def insertBBSQL(waveCursor, bbList):
try:
strLst = []
for bbElm in bbList:
if len(bbElm['bb']) == 8 and bbElm['bb'][7] == 'e':
strValue = "('%s', %s, %s, '%s', '%s', %s, %s, %s, unix_timestamp())" % (bbElm['psid'], bbElm['bb'][0], bbElm['bb'][2], \
'{0:02d}'.format(int(bbElm['bb'][1])), '{0:02d}'.format(int(bbElm['bb'][3])), bbElm['bb'][4], bbElm['bb'][5], bbElm['bb'][6])
elif len(bbElm['bb']) == 6:
strValue = "('%s', %s, %s, '%s', '%s', %s, %s, NULL, unix_timestamp())" % (bbElm['psid'], bbElm['bb'][0], bbElm['bb'][2], \
'{0:02d}'.format(int(bbElm['bb'][1])), '{0:02d}'.format(int(bbElm['bb'][3])), bbElm['bb'][4], bbElm['bb'][5])
else:
log.warn("BBList format error:")
log.warn(bbElm)
continue
strLst.append(strValue)
if len(strLst) > 0:
sql = """insert into vidsession_bb (playSessionID, stTime, endTime, stCode, endCode, trWF, trCell, stBBTime, lstuptmp)
values %s
on duplicate key update endTime = values(endTime), stCode = values(stCode), trWF = values(trWF), trCell = values(trCell), stBBTime = values(stBBTime), lstuptmp = unix_timestamp()
""" % ', '.join(strLst)
ret = waveCursor.execute(sql)
if ret == 0:
log.warn("insertBBSQL no record affected [%s]" % sql)
except Exception, e:
log.error("insertBBSQL error:%s" % e)
log.error(bbList)
raise e
def _closePrevAndMakeNewSession(self, session, data):
self.closeSession(session, data)
try:
self.makeSession(data)
except Exception, e:
log.error("makeSession Error:%s" % e)
raise e
def extractNetworkNode(self, params):
# net info structure
# wifi : 'wifi', status, ssid, bssid
# cell : 'cell', status, celltower id
# status : 'active' for current network, 'inactive' for logged network
timestamp = time.strftime('%Y%m%d%H%M%S', time.gmtime(params['tTM']))
logType = params.get('log_type', 'unknown')
netList = UniqueList()
netList.setKey(key=lambda x: x.cellid if isinstance(x, CellNode) else x.bssid)
if 'lat' in params and 'lng' in params:
geoloc = GeoInfo(lat=params.get('lat'), lng=params.get('lng'), acc=params.get('accuracy', 500), geosrc='device')
else:
geoloc = None
# APAT Header fields
try:
if 'pwf' in params:
pwf = params['pwf']
if 'bssid' in pwf and EthAddrType.isEthAddr(pwf['bssid']):
node = WiFiNode(state='active', bssid=pwf['bssid'], ssid=pwf.get('ssid', ''), regdtm=timestamp, geoloc=geoloc)
netList.addSet(node)
except Exception, e:
log.error(e)
log.error(params)
def handler(self, params):
# Event Key/Value 인 경우, 무시
if 'evtKey' in params.keys():
return
# Extract network nodes from logging
try:
networks = self.extractNetworkNode(params)
except Exception, e:
log.error(e)
log.error(params)
return
def closeSession(self, session, data):
try:
if session['startTime'] == session['endTime'] or session['startTime'] == 0:
sqlStr = """DELETE vd, vbb, vbc
FROM appsession AS vd
LEFT JOIN appsession_net AS vbb ON vd.sessionID = vbb.sessionID
LEFT JOIN appsession_netcell AS vbc ON vd.sessionID = vbc.sessionID
WHERE vd.sessionID = '%s'""" % (session['sessionID'])
self.rdbCur.execute(sqlStr)
actionkey = 'D'
else:
sqlStr = "UPDATE appsession SET valid=1, statAppss = '0', lstuptmp = unix_timestamp() WHERE sessionID='%s'" % (session['sessionID'])
self.rdbCur.execute(sqlStr)
actionkey = 'U'
anID = session['androidID']
pkgname = session['pkgnm']
appSSid = session['sessionID']
sID = session['sID']
sqlStr = """UPDATE vidsession
SET appSessionIDSt = IF(appSessionIDSt = '%s', '', appSessionIDSt),
appSessionIDEnd = IF(appSessionIDEnd = '%s', '', appSessionIDEnd),
lstuptmp = unix_timestamp()
WHERE (appSessionIDSt = '%s' or appSessionIDEnd = '%s') AND playSessionID like '%s%%' and androidID = '%s' and pkgnm = '%s' and sID = %d
""" % (appSSid, appSSid, appSSid, appSSid, anID, anID, pkgname, sID)
ret = self.rdbCur.execute(sqlStr)
sqlStr = """UPDATE vidsession_log SET appSessionID = '' WHERE appSessionID = '%s' and playSessionID like '%s%%'""" % (appSSid, anID)
ret = self.rdbCur.execute(sqlStr)
if actionkey == 'U':
sqlStr = """UPDATE vidsession
SET appSessionIDSt = '%s', lstuptmp = unix_timestamp()
WHERE playSessionID LIKE '%s%%' AND androidID = '%s' AND pkgnm = '%s' AND sID = %d
AND vidnetStartTime >= %d AND vidnetStartTime < %d""" % (appSSid, anID, anID, pkgname, sID, session['startTime'], session['endTime'])
ret = self.rdbCur.execute(sqlStr)
sqlStr = """UPDATE vidsession
SET appSessionIDEnd = '%s', lstuptmp = unix_timestamp()
WHERE playSessionID LIKE '%s%%' AND androidID = '%s' AND pkgnm = '%s' AND sID = %d
AND vidnetEndTime >= %d AND vidnetEndTime < %d""" % (appSSid, anID, anID, pkgname, sID, session['startTime'], session['endTime'])
ret = self.rdbCur.execute(sqlStr)
sqlStr = """UPDATE vidsession_log SET appSessionID = '%s', lstuptmp = unix_timestamp()
WHERE playSessionID like '%s%%' and logStartTime >= %d and logStartTime < %d """ % (appSSid, anID, session['startTime'], session['endTime'])
ret = self.rdbCur.execute(sqlStr)
except Exception, e:
log.error("appSession closeSession Error[sessionID:%s] %s" % (session['sessionID'], e))
raise e
def updateMcc(deviceID, plmnId):
try:
rcur = None
strSQL = ""
if plmnId.isdigit() == True and int(plmnId) > 0:
rcur = worker.dbmanager.allocDictCursor('myapmain')
strSQL = "UPDATE mdev SET plmnid = '%s' WHERE mosid = '%s' and (plmnid <= ' ' or plmnid is NULL)" % (plmnId, deviceID)
ret = rcur.execute(strSQL)
except Exception, e:
log.error("updateMcc: %s" % e)
log.error("updateMcc: [deviceID:%s, plmnid:%s, strSQL:%s]" % (deviceID, plmnId, strSQL))
def process(self, params):
try:
self.rdbCur = None
self.rdbCur = worker.dbmanager.allocDictCursor('myapwave')
pnm = "p%x" % (int(random.random()*100) % 15 + 1)
while True:
self.rdbCur.execute("START TRANSACTION")
strSQL = "SELECT deviceID, lstuptmp from appssdist partition (%s) where isbusy = '0' and procTmp < %d order by lstuptmp limit 1 for update" % (pnm, params['tmp'])
ret = self.rdbCur.execute(strSQL)
if ret > 0 and ret < 10000: # Sometimes Mysql returns 18446744073709551615 instead of -1
row = self.rdbCur.fetchone()
devid = row['deviceID']
lstuptmp = row['lstuptmp']
strSQL = "UPDATE appssdist SET isbusy = '1' where deviceID = '%s'" % (devid)
ret = self.rdbCur.execute(strSQL)
self.rdbCur.execute("COMMIT")
log.info("process:appssdist '1' %s" % devid)
try:
procnt = self.passLog(devid)
except Exception, e:
log.error("Appsession.process in while [%s] %s" % (devid, e))
while True:
try:
rtcnt = 0
if procnt > 0:
strSQL = "UPDATE appssdist SET isbusy = '0', procTmp = %d, lstuptmp = unix_timestamp() where deviceID = '%s'" % (params['tmp'], devid)
else:
if lstuptmp < params['tmp'] - 600:
strSQL = "DELETE FROM appssdist where deviceID = '%s'" % (devid)
else:
strSQL = "UPDATE appssdist SET isbusy = '0', procTmp = %d where deviceID = '%s'" % (params['tmp'], devid)
ret = self.rdbCur.execute(strSQL)
log.info("process: appssdist '0' %s" % devid)
break
except Exception, e:
if str(e).find('restart') > 0:
log.warn("process: appssdist[%s] set to '0' retry: %d, %s" % (devid, rtcnt, e))
if rtcnt > 10:
raise e
time.sleep(rtcnt+1)
rtcnt += 1
else:
log.error("process: appssdist[%s] set to '0' %s" % (devid, e))
raise e
else:
def logInsert(self, params):
try:
if params.has_key('tTM') == False:
try:
vrdict = params.get('__validityReport', '')
if vrdict.has_key('tTM'):
params['tTM'] = float(vrdict['tTM'][0][0].replace(",", "."))
except Exception, e:
log.error("logInsert tTM convert [androidID:%s, pkgName:%s, sID:%s, tTM:%s, numTotalHits:%s] %s" % \
(params.get('deviceID', '-'), params.get('pkgName', '-'), params.get('sID', -9), params.get('tTM', -9), params.get('numTotalHits', -9), e))
return
self.rdbCur = None
self.rdbCur = worker.dbmanager.allocDictCursor('myapwave')
log.info("logInsert: before tran")
self.rdbCur.execute("START TRANSACTION")
strSQL = """INSERT IGNORE INTO appsslog (
deviceID, pkgName, sID, tTM, objid, agentAllowAns, ansLogVer, ansVer, avgTP, bssid,
cellId, duration, estTP, evtKey, evtValue, evtSyncID, startCode, exitCode, startTime, eventTime,
initWifi, model, netType, numTotalHits, NWcharac, playAppPackageName, playTime, activeDownload, activeTime, totalDownload,
linkspd, apfreq, ver, verCode, myfiOn, myfiAP, parentAppName, insTs)
VALUES (
'%s', '%s', %d, %.3f, '%s', %s, '%s', '%s', %s, '%s',
'%s', %s, %s, '%s', %s, %s, %s, %s, %s, %s,
%s, '%s', '%s', %s, '%s', '%s', %s, %s, %s, %s,
%s, %s, '%s', %s, %s, %s, '%s', %s)""" % \
(params['deviceID'], params['pkgName'], int(params['sID']), float(params['tTM']), params.get('objid', ''),
int(params.get('agentAllowAns', -1)), params.get('ansLogVer', ''), params.get('ansVer', ''),
params.get('avgTP', 'NULL'), params.get('bssid', ''),
params.get('cellId', ''), params.get('duration', 'NULL'), params.get('estTP', 'NULL'),
params.get('evtKey', ''), params.get('evtValue', 'NULL'),
params.get('evtSyncID', 'NULL'), params.get('startCode', 'NULL'), params.get('exitCode', 'NULL'),
params.get('startTime', 'NULL'), params.get('eventTime', 'NULL'),
params.get('initWifi', 'NULL'), params.get('model', ''), params.get('netType', ''),
params.get('numTotalHits', 'NULL'), params.get('NWcharac', ''),
params.get('playAppPackageName', ''), params.get('playTime', 'NULL'), params.get('activeDownload', 'NULL'),
params.get('activeTime', 'NULL'),params.get('totalDownload', 'NULL'),
params.get('lnkspd', 'NULL'), params.get('apfreq', 'NULL'), params.get('ver', ''),
params.get('verCode', 'NULL'), params.get('myfiOn', 'NULL'),
params.get('myfiAP', 'NULL'), params.get('parentAppName', ''), params.get('insTs', 'NULL')
)
ret = self.rdbCur.execute(strSQL)
strSQL = """INSERT IGNORE INTO appssdist (deviceID, isbusy, lstuptmp) VALUES ('%s', '0', unix_timestamp())""" % (params['deviceID'])
ret = self.rdbCur.execute(strSQL)
self.rdbCur.execute("COMMIT")
log.info("logInsert: commit tran")
def handler(self, params):
global appss
global lastParamTmp
try:
log.info("evtTimeInterval get %d (last:%d)" % (params['tmp'], lastParamTmp))
if params['tmp'] > lastParamTmp:
log.info("appss.process start")
appss.process(params)
lastParamTmp = params['tmp']
log.info("appss.process done")
except Exception, e:
log.error("processAnsClock ERROR %s" % (e) )
def updateVidLog(waveCursor, vLog, row):
try:
strSQL = ""
strSQL = """UPDATE vidsession_log
SET playTime = if(playTime >= %d, playTime - %d, playTime),
pauseTime = if(pauseTime >= %d, pauseTime - %d, pauseTime),
elapsedTime = if(elapsedTime >= %d, elapsedTime - %d, elapsedTime),
cellRxBytes = if(cellRxBytes >= %d, cellRxBytes - %d, cellRxBytes),
wfRxBytes = if(wfRxBytes >= %d, wfRxBytes - %d, wfRxBytes),
cellDuration = if(cellDuration >= %d, cellDuration - %d, cellDuration),
wfDuration = if(wfDuration >= %d, wfDuration -%d, wfDuration),
lstuptmp = unix_timestamp()
WHERE playSessionID = '%s' and tTM = %s """ % (vLog['playTime'], vLog['playTime'],
vLog['pauseTime'], vLog['pauseTime'],
vLog['elapsedTime'], vLog['elapsedTime'],
vLog['cellRxBytes'], vLog['cellRxBytes'],
vLog['wfRxBytes'], vLog['wfRxBytes'],
vLog['cellDuration'], vLog['cellDuration'],
vLog['wfDuration'], vLog['wfDuration'],
vLog['playSessionID'], row['nextTTM'])
waveCursor.execute(strSQL)
except Exception, e:
log.error("updateVidLog %s" % e)
log.error(vLog)
log.error(row)
if strSQL > "":
log.error("[SQL] %s" % strSQL)
raise e
def updateAppssLst(self, data, sessionID):
try:
strSQL = """REPLACE appsession_lst
(androidID, pkgnm, tTM, numTotalHits, sID,sessionID, oid)
VALUES ('%s', '%s', %.3f, %d, %d, '%s', '%s') """ % (data['deviceID'], data['pkgName'],
float(data['tTM']), int(data['numTotalHits']), int(data.get('sID', 0)), sessionID, data.get('objID', ''))
ret = self.rdbCur.execute(strSQL)
if ret == 0:
log.warn("UPDATE appsession_lst 0 row strSQL:%s" % strSQL)
except Exception, e:
log.error("update appsession_lst :%s" % e)
log.error(strSQL)
raise e
def addCellTower(cursor, node):
strSql = """INSERT INTO
apmain.cellinfo (fullid, plmnid, cellid, lac, celltype, regdtm, lat, lng, acc, geosrc, seq)
VALUES('%s','%s','%s','%s','%d','%s','%s','%f','%f','%s', '1')
ON DUPLICATED UPDATE lat=((lat*seq)+VALUES(lat))/(seq+1), lng=((lng*seq)+VALUES(lng))/(seq+1), seq=seq+1, geosrc=VALUES(geosrc)"""
try:
strSql = strSql % (node.cellid, node.plmnid, node.cid, node.lac, 0, node.regdtm, node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, 'cellLoc' if node.geoloc.from_cell else node.geoloc.geosrc)
cursor.execute(strSql)
log.debug("INSERT - %s" % node.cellid)
except Exception, e:
# Duplicate entry error
if e[0] != 1062:
log.error(e)
log.error(strSql)
return False
def preprocessLog(self, data):
#exit code conversion
codeMap = {15:0, 21:10, 12:20, 20:30, 19:40, 22:50, 14:60, 13:100, 16:120, 23:121,
7:141, 6:201, 5:0, 17:202, 0:301, 3:302, 2:0, 1:303, 4:304, 11:0, 10:0,
8:305, 9:306, 24:307, -1:402}
try:
dataVer = map(int, data['ansLogVer'].split('.'))
if dataVer < [2,0]:
data['exitCode'] = codeMap.get(int(data.get('exitCode', -99)), -99)
data['startCode'] = codeMap.get(int(data.get('startCode', -99)), -99)
else:
data['exitCode'] = int(data.get('exitCode', -99))
data['startCode'] = int(data.get('startCode', -99))
except Exception, e:
log.error("exitCode field Error %s:%s" % (e, data.get('exitCode')))
data['exitCode'] = 0
def insertVidnetLog(waveCursor, vidLogDict):
if vidLogDict == None:
log.warn('vidLogDict is null')
return False
cols = vidLogDict.keys()
vals = vidLogDict.values()
try:
sql = """insert into vidsession_log (%s, lstuptmp) values (%s, unix_timestamp())""" % (",".join(cols), ",".join(["'" + str(val).replace("'", "''") + "'" for val in vals]))
waveCursor.execute(sql)
except Exception, e:
log.error("INSERT vidsession_log ERROR:%s" % e)
log.error(vidLogDict)
raise e
def addWiFi(cursor, node):
strSql = """INSERT INTO
apmain.apinfo (bssid, ssid, regdtm, bregap, bmap, lat, lng, acc, geosrc, optrcom, seq)
VALUES('%s','%s','%s','%d','%d','%f','%f','%d','%s','%s','%s')
ON DUPLICATED UPDATE
lat = IF(VALUES(seq) > seq, VALUES(lat), lat),
lng = IF(VALUES(seq) > seq, VALUES(lng), lng),
seq = IF(VALUES(seq) > seq, VALUES(seq), seq),
acc = IF(VALUES(seq) > seq, VALUES(acc), acc),
geosrc=VALUES(geosrc)"""
try:
strSql = strSql % (node.bssid, node.ssid, node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi)
except Exception, e:
log.error("SQL GEN ERR - %s" % bytes(node.ssid))
strSql = strSql % (node.bssid, '', node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi)
def procSyncID(self, data, sessionID):
try:
self.rdbCur.execute("START TRANSACTION")
tbCols = ['deviceID', 'pkgName', 'sID', 'evtSyncID', 'tTM', 'numTotalHits', 'evtValue', 'eventTime', 'startTime', 'startCode', 'exitCode',
'activeDownload', 'activeTime', 'agentAllowAns', 'ansLogVer', 'ansVer', 'avgTP', 'bssid', 'cellId', 'duration', 'initWifi', 'model',
'netType', 'NWcharac', 'playAppPackageName', 'parentAppName', 'playTime', 'totalDownload', 'ver', 'verCode', 'myfiOn', 'myfiAP']
strSQL = """SELECT * FROM appsync WHERE deviceID = '%s' and pkgName = '%s' and sID = %d and evtSyncID = %d
""" % (data.get('deviceID', ''), data.get('pkgName', ''), int(data.get('sID', 0)), int(data.get('evtSyncID', 0)))
retFunc = self.rdbCur.execute(strSQL)
if retFunc == 0:
sqlDict = {}
for col in tbCols:
if data.has_key(col):
if col == 'initWifi' or col == 'agentAllowAns':
if str(data[col]) in ['true', 'True', '1']:
sqlDict[col] = 1
elif str(data[col]) in ['false', 'False', '0']:
sqlDict[col] = 0
else:
sqlDict[col] = -1
else:
sqlDict[col] = data[col]
#sqlStr = "INSERT INTO appsession (%s) VALUES (%s)" % (','.join(session.keys()), str(map(str, session.values())).strip('[*]'))
strSQL = "INSERT INTO appsync (%s, lstuptmp) VALUES (%s, unix_timestamp())" % (','.join(sqlDict.keys()), str(map(str, sqlDict.values())).strip('[*]'))
self.rdbCur.execute(strSQL)
else:
row = self.rdbCur.fetchone()
for key in row:
if row[key] <> None and (data.get(key, None) == None or data[key] == ''):
data[key] = row[key]
strSQL = """DELETE FROM appsync WHERE deviceID = '%s' and pkgName = '%s'
""" % (data.get('deviceID', ''), data.get('pkgName', ''))
self.rdbCur.execute(strSQL)
self.updateAppssLst(data, sessionID)
self.rdbCur.execute("COMMIT")
return retFunc
except Exception, e:
self.rdbCur.execute("ROLLBACK")
log.error("procSyncID:%s" % e)
log.error(data)
return 0
def passLog(self, devid):
try:
strSQL = """SELECT
deviceID, pkgName, sID, tTM, objid, agentAllowAns, ansLogVer, ansVer, avgTP, bssid,
cellId, duration, estTP, evtKey, evtValue, evtSyncID, startCode, exitCode, startTime, eventTime,
initWifi, model, netType, numTotalHits, NWcharac, playAppPackageName, playTime, activeDownload, activeTime, totalDownload,
linkspd, apfreq, ver, verCode, myfiOn, myfiAP, parentAppName, insTs
from appsslog where deviceID = '%s'
order by deviceID, pkgName, sID, tTM""" % (devid)
ret = self.rdbCur.execute(strSQL)
log.info("passLog:appsslog ret = %d " % ret)
if ret > 0:
rows = self.rdbCur.fetchall()
cutTs = time.time() - 5
pdevid = 'NULL'
ppkgname = 'NULL'
psid = -9
for row in rows:
if pdevid == row['deviceID'] and ppkgname == row['pkgName'] and psid == row['sID']:
log.info("passLog: pass %s %s %s" % (pdevid, ppkgname, psid))
continue
if row['insTs'] > cutTs:
pdevid = row['deviceID']
ppkgname = row['pkgName']
psid = row['sID']
log.info("passLog: insTs(%d) > cutTs(%d) %s %s %s" % (row['insTs'], cutTs, pdevid, ppkgname, psid))
continue
keepkeys = filter(lambda x: row[x] <> None, row)
newrow = {k: row[k] for k in keepkeys}
# log.info("passLog: before processLog")
# log.info(newrow)
self.processLog(newrow)
strSQL = """DELETE from appsslog where deviceID = '%s' and pkgName = '%s' and sID = %d and tTM = %.3f""" % \
(newrow['deviceID'], newrow['pkgName'], newrow['sID'], newrow['tTM'])
ret = self.rdbCur.execute(strSQL)
log.info("passLog: delete appsslog %d (inTs:%.3f cutTs:%.3f) (%s, %s, %d, %.3f)" % (ret, newrow['insTs'], cutTs, newrow['deviceID'], newrow['pkgName'], newrow['sID'], newrow['tTM']))
return ret
except Exception, e:
log.error("passLog : %s" % e)
log.error(newrow)
raise e
async def set_all_challenges(lang: str) -> None:
html = http_get(f'{URL}/{lang}/Challenges/')
if html is None:
log.error('challenges_page_not_found')
return
categories = extract_categories(html)
if not categories:
log.warn('fetch_all_categories_failed', lang=lang)
return
log.debug('fetched_categories', categories=categories, lang=lang)
tp_func = partial(retrieve_category_info, lang)
with ThreadPool(len(categories)) as tp:
response = tp.map(tp_func, categories)
timestamp = datetime.now().isoformat()
await app.redis.set(
f'{lang}.challenges',
json.dumps({
'body': response,
'last_update': timestamp
}))
await app.redis.set(
f'{lang}.categories',
json.dumps({
'body': categories,
'last_update': timestamp
}))
for category_data in response:
await app.redis.set(
f'{lang}.categories.{category_data[0]["name"]}',
json.dumps({
'body': category_data,
'last_update': timestamp
}))
log.debug('set_all_challenges_success', lang=lang)
def __new__(cls, state, bssid, ssid='', regdtm='19000101000000', rssi=-200, bregap=False, bmap=False, optrcom='none', geoloc=None, priority=Priority.NORMAL):
# Classify WiFi
try:
if ssid not in ('', None):
ssid = re.sub(r'^\s*"(.*)"\s*$', r'\1', unicode(ssid))
if ssid.find('"') >= 0:
log.error("!!! SSID - %s" % ssid)
if cls.isHotspot(ssid):
priority = Priority.LOW
else:
optrcom = cls.getWiFiOperator(ssid)
bregap = True if optrcom != 'none' else False
if not bregap:
bmap = cls.isMobile(ssid)
try:
ssid = MySQLdb.escape_string(unicode(ssid).encode('utf-8'))
except Exception, e:
# Non-ascii data.
log.warn("SSID MySQLdb.escape_string Error - %s, %s" % (ssid, e))
if not geoloc:
geoloc = GeoInfo()
def insertVidnet(waveCursor, vidDict):
if vidDict == None:
log.warn('vidnetDict is null')
return False
cols = vidDict.keys()
vals = vidDict.values()
try:
slist = []
for v in vals:
if type(v) == str or type(v) == unicode:
slist.append("'" + v.replace("'", "''") + "'")
else:
slist.append(str(v))
sql = """insert into vidsession (%s, lstuptmp) values (%s, unix_timestamp())""" % (",".join(cols), unicode(",", "utf-8").join(slist))
waveCursor.execute(sql)
except Exception, e:
log.error("INSERT VIDNET ERROR:%s" % e)
log.error(vidDict)
raise e
def processNWcharac(self, alog):
try:
if alog.get('bssid', '') == '' or alog['bssid'] == None:
return
bssid = alog['bssid']
SumfrLst = [{'fq':0, 'avgrssi':0} for i in range(10)]
SumTpDict = {}
cocoaAvg = {'freq':0, 'estTP':0.0}
tpAvg = {'freq':0, 'avgTP':0.0}
#get saved nwcharac values from RDB
strSQL = "SELECT itype, lvl, freq, fvalue FROM apnwc WHERE bssid = '%s'" % bssid
ret = self.rdbCur.execute(strSQL)
if ret > 0:
rows = self.rdbCur.fetchall()
for row in rows:
if row['itype'] == '0':
SumfrLst[row['lvl']]['avgrssi'] = row['fvalue']
SumfrLst[row['lvl']]['fq'] = row['freq']
elif row['itype'] == '1':
SumTpDict[str(row['lvl'])] = row['freq']
elif row['itype'] == '2':
cocoaAvg = {'freq':row['freq'], 'estTP':row['fvalue']}
elif row['itype'] == '3':
tpAvg = {'freq':row['freq'], 'avgTP':row['fvalue']}
else:
log.warn("processNWcharac Unknow itype %s:%s" % (bssid, row['itype']))
continue
#parse aatlog's NWcharac string & store into temporary storage
if alog.get('NWcharac', None) <> None and alog['NWcharac'] > '':
nwstr = alog['NWcharac'].split('|')
if len(nwstr) == 2:
fqrssiLst = nwstr[0].split('.')
for i, fqrssi in enumerate(fqrssiLst):
fqlst = fqrssi.split(',')
if len(fqlst) == 2 and fqlst[0].isdigit() and fqlst[0] > 0 and fqlst[1].isdigit():
#RSSI AVG: frequency number * rssi / frequency number = (lastest) avg. rssi
#RSSI AVG Accumulated: (lastest avg. rssi * sum of frequency number + new freq * rssi) / (sum of freq number + new freq)
SumfrLst[i]['avgrssi'] = float(SumfrLst[i]['avgrssi']*SumfrLst[i]['fq'] + int(fqlst[0])*int(fqlst[1])) / float(SumfrLst[i]['fq']+int(fqlst[0]))
SumfrLst[i]['fq'] += int(fqlst[0])
varTPLst = nwstr[1].split('.')
for varTP in varTPLst:
TPLst = varTP.split(',')
if len(TPLst) == 2 and TPLst[0].isdigit():
if SumTpDict.has_key(TPLst[0]):
SumTpDict[TPLst[0]] += 1
else:
SumTpDict[TPLst[0]] = 1
if alog.has_key('estTP') and alog['estTP'] <> None:
if alog['estTP'] > 0:
cocoaAvg['freq'] += 1
cocoaAvg['estTP'] += int(alog['estTP'])
if alog.has_key('avgTP') and alog['avgTP'] <> None:
if alog['avgTP'] > 0:
tpAvg['freq'] += 1
tpAvg['avgTP'] += alog['avgTP']
#update RDB data
valuesLst = []
for i, fr in enumerate(SumfrLst):
strvalue = "('%s', '0', %d, %d, %f)" % (bssid, i, fr['fq'], fr['avgrssi'])
valuesLst.append(strvalue)
for key in SumTpDict:
strvalue = "('%s', '1', %d, %d, 0.0)" % (bssid, int(key), SumTpDict[key])
valuesLst.append(strvalue)
strvalue = "('%s', '2', 0, %d, %f)" % (bssid, cocoaAvg['freq'], cocoaAvg['estTP'])
valuesLst.append(strvalue)
strvalue = "('%s', '3', 0, %d, %f)" % (bssid, tpAvg['freq'], tpAvg['avgTP'])
valuesLst.append(strvalue)
strSQL = """INSERT INTO apnwc (bssid, itype, lvl, freq, fvalue)
VALUES %s
ON DUPLICATE KEY UPDATE
freq = VALUES(freq), fvalue = VALUES(fvalue)
""" % ', '.join(valuesLst)
ret = self.rdbCur.execute(strSQL)
except Exception, e:
log.error("processNWcharac: %s" % e)
log.error(alog)
acc = IF(VALUES(seq) > seq, VALUES(acc), acc),
geosrc=VALUES(geosrc)"""
try:
strSql = strSql % (node.bssid, node.ssid, node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi)
except Exception, e:
log.error("SQL GEN ERR - %s" % bytes(node.ssid))
strSql = strSql % (node.bssid, '', node.regdtm, int(node.bregap), int(node.bmap), node.geoloc.lat, node.geoloc.lng, node.geoloc.acc, node.geoloc.geosrc, node.optrcom, node.rssi)
try:
cursor.execute(strSql)
log.debug("INSERT - %s" % node.bssid)
except Exception, e:
# Duplicate entry error
if e[0] != 1062:
log.error(e)
log.error(strSql)
return False
return True
netTypeCode = {'gsm':1, 'cdma':2, 'lte':3}
def addCellTower(cursor, node):
strSql = """INSERT INTO
apmain.cellinfo (fullid, plmnid, cellid, lac, celltype, regdtm, lat, lng, acc, geosrc, seq)
VALUES('%s','%s','%s','%s','%d','%s','%s','%f','%f','%s', '1')
ON DUPLICATED UPDATE lat=((lat*seq)+VALUES(lat))/(seq+1), lng=((lng*seq)+VALUES(lng))/(seq+1), seq=seq+1, geosrc=VALUES(geosrc)"""
netDict['ntype'] = 'w'
netDict['bssid'] = netElm[4]
netDict['ssid'] = netElm[3].replace("'", "''")
netDict['traffic'] = netElm[6]
rstNetList.append(netDict.copy())
else:
if len(netElm) == 5:
netDict['playSessionID'] = aatLog['playSessionId']
netDict['stTime'] = netElm[0]
netDict['ntype'] = 'm'
netDict['traffic'] = netElm[4]
rstNetList.append(netDict.copy())
except Exception, e:
log.error("getNetconnInfo error:[%s]%s" % (type(e), e))
log.error(aatLog)
raise e
#Following function is not related to Open DD. You should ignore it
def insertNetInfo(waveCursor, netList):
try:
strLst = []
for netElm in netList:
if netElm['ntype'] == 'w':
strValue = "('%s', %s, '%s', '%s', '%s', %s, unix_timestamp())" % \
(netElm['playSessionID'], netElm['stTime'], netElm['ntype'], \
netElm['bssid'], netElm['ssid'], netElm['traffic'])
else:
strValue = "('%s', %s, '%s', NULL, NULL, %s, unix_timestamp())" % \
(netElm['playSessionID'], netElm['stTime'], netElm['ntype'], netElm['traffic'])
def handler(self, aatLog):
try:
waveCursor = None
#update apmain.mdev's plmnid for hoppin case
if aatLog.get('confOperator', '') > '':
updateMcc(aatLog.get('deviceID', ''), aatLog['confOperator'])
#in case of Off Log, process only End(0) log
if int(aatLog.get('agentAatOnOff', -1)) == 0:
if aatLog.get('agentLogType', -1) <> 0:
return
curAnID = aatLog.get('deviceID').strip(" u'")
curPkgname = aatLog.get('pkgName').strip(" u'")
curPsID = aatLog.get('playSessionId', '').strip(" u'")
curTTM = Decimal(aatLog.get('tTM', 0.0))
curEndTm = int(aatLog.get('agentLogEndTime', 0))
waveCursor = worker.dbmanager.allocDictCursor('myapwave')
waveCursor.execute("START TRANSACTION")
strSQL= None
strSQL = """SELECT a.*, IFNULL(b.psid, '') AS bpsid, b.*, e.* FROM
(SELECT m.*, MAX(n.tTM) AS maxTTM, MAX(IF(n.tTM = %.3f, 1, 0) ) AS bExist
FROM vidsession m LEFT OUTER JOIN vidsession_log n ON m.playSessionID = n.playSessionID
WHERE m.playSessionID = '%s') a LEFT OUTER JOIN
(SELECT playSessionID AS psid, MAX(tTM) AS lstTTM,
SUBSTR(MAX(CONCAT(RPAD(tTM, 14, '0'), logType)), 15) AS mLogType,
SUBSTR(MAX(CONCAT(RPAD(tTM, 14, '0'), logEndTime)), 15) AS mLogEndTime,
SUBSTR(MAX(CONCAT(RPAD(tTM, 14, '0'), IFNULL(bufferState, '0'))), 15) AS mBufferState,
SUM(playTime) AS mPlayTime, SUM(pauseTime) AS mPauseTime,
SUM(elapsedTime) AS mElapsedTime, SUM(cellRxBytes) AS mCellBytes, SUM(wfRxBytes) AS mWFBytes,
SUM(cellDuration) AS mCellDur, SUM(wfDuration) AS mWFDur
FROM vidsession_log
WHERE playSessionID = '%s' AND tTM < %.3f) b
ON a.playSessionID = b.psid
LEFT OUTER JOIN
(SELECT playSessionID AS psid, MIN(tTM) AS nextTTM
FROM vidsession_log
WHERE playSessionID = '%s' AND tTM > %.3f ) e
ON a.playSessionID = e.psid
""" % (curTTM, curPsID, curPsID, curTTM, curPsID, curTTM)
ret = waveCursor.execute(strSQL)
if ret > 0:
row = waveCursor.fetchone()
if row['playSessionID'] <> None:
if row['bExist'] == 1:
return
else:
vidupdate(waveCursor, aatLog, row)
else:
vidcreate(waveCursor, aatLog)
else: # Insert new playsession
vidcreate(waveCursor, aatLog)
# get BB, BW
#Following code is not related to Open DD. You should ignore it.
#### BEGIN - IGNORE
logSubList = []
getBBinfo(logSubList , aatLog)
insertBBSQL(waveCursor, logSubList)
logSubList = []
getNetConn(logSubList, aatLog)
insertNetInfo(waveCursor, logSubList)
#### END - IGNORE
waveCursor.execute("COMMIT")
except Exception, e:
log.error("processAATLOG : %s" % e)
log.error(aatLog)
if strSQL <> None:
log.error(strSQL)
if waveCursor <> None:
waveCursor.execute("ROLLBACK")
if str(e).find('Deadlock')> 0:
log.error("processAAATLog raise e")
raise e
def vidupdate(waveCursor, aatLog, row):
try:
vidDict = {}
vidLogDict = {}
#get some values to use
cellid = "%s_%s_%s" % (aatLog.get('confOperator', ''), aatLog.get('netCID', ''), aatLog.get('netLAC', ''))
psmode = int(aatLog['playServiceMode'])
logType = aatLog.get('agentLogType', -1)
if aatLog.get('netActiveNetwork', '').find('WIFI') >= 0:
netType = '0'
elif aatLog.get('netActiveNetwork', '').find('mobile') >= 0:
netType = '1'
else:
netType = '2'
batteryStart = 0
batteryEnd = 0
batteryValid = 0
if aatLog.has_key('batteryInfo'):
btList = aatLog['batteryInfo'].split('|')
if len(btList) == 2:
if len(btList[0].split('/')) >= 5 and len(btList[1].split('/')) >= 5:
nTotLevel = float(btList[0].split('/')[3])
nBatLevel = float(btList[0].split('/')[4])
batteryStart = (nBatLevel/nTotLevel)*100
nTotLevel = float(btList[1].split('/')[3])
nBatLevel = float(btList[1].split('/')[4])
batteryEnd = (nBatLevel/nTotLevel)*100
if btList[1].split('/')[1] == 'DISCHARGING': #All batteryInfo reporting log must be 'DISCHARGING' except first.
batteryValid = 1
else:
batteryValid = 0
elif len(btList) == 1:
if len(btList[0].split('/')) >= 5:
nTotLevel = float(btList[0].split('/')[3])
nBatLevel = float(btList[0].split('/')[4])
batteryStart = (nBatLevel/nTotLevel)*100
batteryEnd = batteryStart
batteryValid = 0
#get appSessionID
appSessionId = ''
strSQL = """SELECT sessionID FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d
and (startTime - 5) <= %d and startTime > 0 and (endTime > %d or statAppss > '0') ORDER BY sessionID DESC LIMIT 1""" % (aatLog['deviceID'],
aatLog['pkgName'], aatLog['sID'], aatLog['agentLogStartTime'], aatLog['agentLogStartTime'])
ret = waveCursor.execute(strSQL)
if ret > 0:
aarow = waveCursor.fetchone()
if aarow['sessionID'] > '':
appSessionId = aarow['sessionID']
#vidsession_log values
getVidLogStatic(vidLogDict, aatLog, appSessionId, netType)
#initialize if as-is record has no valid value.
if row['androidID'] == '': vidDict['androidID'] = aatLog.get('deviceID', '')
if row['vID'] == '': vidDict['vID'] = aatLog.get('vID', '')
if row['sID'] == 0: vidDict['sID'] = aatLog.get('sID', 0)
if row['verCode'] == 0: vidDict['verCode'] = aatLog.get('verCode', 0)
if row['osVer'] == '': vidDict['osVer'] = aatLog.get('osVer', '')
if row['brand'] == '': vidDict['brand'] = aatLog.get('brand', '')
if row['model'] == '': vidDict['model'] = aatLog.get('model', '')
if row['cellIdSt'] == '' and len(cellid) > 6: vidDict['cellIdSt'] = cellid
if row['cellIdEnd'] == '' and len(cellid) > 6: vidDict['cellIdEnd'] = cellid
if row['bMao'] < 0: vidDict['bMao'] = int(aatLog.get('agentAatOnOff', -1))
if row['bAnsAllow'] < 0: vidDict['bAnsAllow'] = int(aatLog.get('agentAllowAns', -1))
if row['bCellAllow'] < 0: vidDict['bCellAllow'] = int(aatLog.get('agentAllowMobile', -1))
if row['ansMode'] == '': vidDict['ansMode'] = aatLog.get('agentAnsMode', -1)
if row['agentUserSetup'] == '': vidDict['agentUserSetup'] = aatLog.get('agentUserSetup', '')
#if row['startLogType'] == '': vidDict['ansMode'] = aatLog.get('agentAnsMode', -1)
if row['hostName'] == '': vidDict['hostName'] = aatLog.get('playHost', '')
if row['originName'] == '': vidDict['originName'] = aatLog.get('playOrigin', '')
if row['contentID'] == '': vidDict['contentID'] = aatLog.get('playContentId', '')
if row['playServiceMode'] <= 0: vidDict['playServiceMode'] = aatLog.get('playServiceMode', 0)
if row['contentSize'] == 0:
if psmode == 1:
vidDict['contentSize'] = aatLog.get('vodContentSize', 0)
elif psmode == 4:
vidDict['contentSize'] = aatLog.get('audContentSize', 0)
elif psmode == 5:
vidDict['contentSize'] = aatLog.get('adnContentSize', 0)
if row['contentDuration'] == 0:
if psmode == 1:
vidDict['contentDuration'] = aatLog.get('vodContentDuration', 0)
elif psmode == 4:
vidDict['contentDuration'] = aatLog.get('audContentDuration', 0)
if row['contentBitrate'] == 0 and psmode in [2,3]:
vidDict['contentBitrate'] = aatLog.get('liveCurrentTSBitrate', 0)
#if row['channelName'] == '': vidDict['channelName'] = aatLog.get('playTitle', '').encode('utf-8')
if row['channelName'] == '': vidDict['channelName'] = aatLog.get('playTitle', '')
if row['pkgnm'] == '': vidDict['pkgnm'] = aatLog.get('pkgName', '')
if row['apppkgnm'] == '' or row['appvercd'] == '':
if(aatLog.has_key('playAppPackageName')):
appPkgs = aatLog['playAppPackageName'].split('/')
if len(appPkgs) >= 2:
vidDict['apppkgnm'] = appPkgs[0]
vidDict['appvercd'] = appPkgs[1]
if row['connectedNetCnt'] == 0:
if aatLog.has_key('netConnectedNetworkCount'):
vidDict['connectedNetCnt']=aatLog['netConnectedNetworkCount']
elif aatLog.has_key('netConnectivityCount'):
vidDict['connectedNetCnt']=aatLog['netConnectivityCount']
if row['abrBitrateList'] == '': vidDict['abrBitrateList'] = aatLog.get('playBitrateList', '')
if row['abrUserSelBR'] == '': vidDict['abrUserSelBR'] = aatLog.get('userSelectBitrate', '')
if psmode == 5:
if row['vidnetType'] == 0: vidDict['vidnetType'] = aatLog.get('adnStartCode', 0)
if row['adnMode'] == '' or (row['adnMode'] <> 'BB' and aatLog.get('adnMode', '') == 'BB') :
vidDict['adnMode'] = aatLog.get('adnMode', '')
if row['adnRangeStart'] == 0: vidDict['adnRangeStart'] = aatLog.get('adnContentRangeStart', 0)
if row['adnDownSize'] < aatLog.get('adnDownloadSize', 0):
vidDict['adnDownSize'] = aatLog.get('adnDownloadSize', 0)
if row['contentDuration'] < aatLog.get('adnDownloadTime', 0):
vidDict['contentDuration'] = aatLog.get('adnDownloadTime', 0)
if row['adnContentID'] == 0: vidDict['adnContentID'] = aatLog.get('adnContentID', 0)
vidDict['cellSysRxBytes'] = row['cellSysRxBytes'] + aatLog.get('trafficSystemMoRxBytes', 0)
vidDict['wfSysRxBytes'] = row['wfSysRxBytes'] + aatLog.get('trafficSystemWFRxBytes', 0)
# process attributes depending on log-order
if aatLog['tTM'] > row['maxTTM']: #The log is the last of this playSession
if len(cellid) > 6:
vidDict['cellIdEnd'] = cellid
vidDict['endLogType'] = logType
vidDict['vidnetEndTime'] = aatLog.get('agentLogEndTime', 0)
vidDict['vidnetDuration'] = vidDict['vidnetEndTime'] - row['vidnetStartTime']
if aatLog.get('playPlayingTime', 0) > row['playTime']:
vidDict['playTime'] = aatLog['playPlayingTime']
if aatLog.get('playSeekCount', 0) > row['seekCnt']:
vidDict['seekCnt'] = aatLog['playSeekCount']
if aatLog.get('playSeekForwardCount', 0) > row['ffCnt']:
vidDict['ffCnt'] = aatLog['playSeekForwardCount']
if aatLog.get('playSeekRewindCount', 0) > row['rwCnt']:
vidDict['rwCnt'] = aatLog['playSeekRewindCount']
if aatLog.has_key('netConnectedNetworkCount'):
if row['connectedNetCnt'] < aatLog['netConnectedNetworkCount']:
vidDict['connectedNetCnt']=aatLog['netConnectedNetworkCount']
elif aatLog.has_key('netConnectivityCount'):
if row['connectedNetCnt'] < aatLog['netConnectivityCount']:
vidDict['connectedNetCnt']=aatLog['netConnectivityCount']
if psmode in [1, 4, 5]:
vidDict['pauseCnt'] = aatLog.get('playBufferingCount', 0)
vidDict['resumeCnt'] = aatLog.get('playResumeCount', 0)
if aatLog.get('playAccBufferingTime', 0) > row['pauseTime']:
vidDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0)
if aatLog.get('playMaxBufferingTime', 0) > row['maxPauseTime']:
vidDict['maxPauseTime'] = aatLog.get('playMaxBufferingTime', 0)
if aatLog.get('trafficAgentMoBytes', 0) > row['cellRxBytes']:
vidDict['cellRxBytes'] = aatLog['trafficAgentMoBytes']
if aatLog.get('trafficAgentWFBytes', 0) > row['wfRxBytes']:
vidDict['wfRxBytes'] = aatLog['trafficAgentWFBytes']
vidDict['cellAvgTP'] = round(aatLog.get('trafficAgentMoAveBW',0), 4)
vidDict['wfAvgTP'] = round(aatLog.get('trafficAgentWFAveBW',0), 4)
if vidDict['cellAvgTP'] > 0:
vidDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000))
if vidDict['wfAvgTP'] > 0:
vidDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000))
vidDict['batteryEnd'] = batteryEnd
#get appSessionID for vidsession
strSQL = """SELECT sessionID FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d
and startTime < %d and startTime > 0 and ((endTime+5) > %d or statAppss > '0') ORDER BY sessionID DESC LIMIT 1""" % (aatLog['deviceID'],
aatLog['pkgName'], aatLog['sID'], aatLog['agentLogEndTime'], aatLog['agentLogEndTime'])
ret = waveCursor.execute(strSQL)
if ret > 0:
aarow = waveCursor.fetchone()
if aarow['sessionID'] > '':
vidDict['appSessionIDEnd'] = aarow['sessionID']
#vidsession_log values
if aatLog.get('playPlayingTime', 0) > row['playTime']:
vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0) - row['playTime']
else:
vidLogDict['playTime'] = 0
if aatLog.get('playAccBufferingTime', 0) > row['pauseTime']:
vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) - row['pauseTime']
else:
vidLogDict['pauseTime'] = 0
if aatLog.get('playPreparingTime', 0) > row['elapsedTime']:
vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) - row['elapsedTime']
else:
vidLogDict['elapsedTime'] = 0
if aatLog.get('trafficAgentMoBytes', 0) > row['cellRxBytes']:
vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) - row['cellRxBytes']
else:
vidLogDict['cellRxBytes'] = 0
if aatLog.get('trafficAgentWFBytes', 0) > row['wfRxBytes']:
vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) - row['wfRxBytes']
else:
vidLogDict['wfRxBytes'] = 0
if vidDict['cellAvgTP'] > 0 and vidDict['cellDuration'] > row['cellDuration']:
vidLogDict['cellDuration'] = vidDict['cellDuration'] - row['cellDuration']
else:
vidLogDict['cellDuration'] = 0
if vidDict['wfAvgTP'] > 0 and vidDict['wfDuration'] > row['wfDuration']:
vidLogDict['wfDuration'] = vidDict['wfDuration'] - row['wfDuration']
else:
vidLogDict['wfDuration'] = 0
elif row['bpsid'] == '': # The log is the first of this playSession
if len(cellid) > 6:
vidDict['cellIdSt'] = cellid
vidDict['startLogType'] = logType
vidDict['vidnetStartTime'] = aatLog.get('agentLogStartTime', 0)
vidDict['vidnetDuration'] = row['vidnetEndTime'] - vidDict['vidnetStartTime']
vidDict['batteryStart'] = batteryStart
if appSessionId > '':
vidDict['appSessionIDSt'] = appSessionId
#vidsession_log values
vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0)
vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0)
vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0)
vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0)
vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0)
if round(aatLog.get('trafficAgentMoAveBW',0), 4) > 0:
vidLogDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000))
else:
vidLogDict['cellDuration'] = 0
if round(aatLog.get('trafficAgentWFAveBW',0), 4) > 0:
vidLogDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000))
else:
vidLogDict['wfDuration'] = 0
updateVidLog(waveCursor, vidLogDict, row)
else: # The log is middle of this playSession
if aatLog.get('playPlayingTime', 0) > row['mPlayTime']:
vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0) - row['mPlayTime']
else:
vidLogDict['playTime'] = 0
if aatLog.get('playAccBufferingTime', 0) > row['mPauseTime']:
vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0) - row['mPauseTime']
else:
vidLogDict['pauseTime'] = 0
if aatLog.get('playPreparingTime', 0) > row['mElapsedTime']:
vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0) - row['mElapsedTime']
else:
vidLogDict['elapsedTime'] = 0
if aatLog.get('trafficAgentMoBytes', 0) > row['mCellBytes']:
vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0) - row['mCellBytes']
else:
vidLogDict['cellRxBytes'] = 0
if aatLog.get('trafficAgentWFBytes', 0) > row['mWFBytes']:
vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0) - row['mWFBytes']
else:
vidLogDict['wfRxBytes'] = 0
vidLogDict['cellDuration'] = 0
vidLogDict['wfDuration'] = 0
if round(aatLog.get('trafficAgentMoAveBW',0), 4) > 0:
tempdur = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000))
if tempdur > row['mCellDur']:
vidLogDict['cellDuration'] = tempdur - int(row['mCellDur'])
if round(aatLog.get('trafficAgentWFAveBW',0), 4) > 0:
tempdur = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000))
if tempdur > row['mWFDur']:
vidLogDict['wfDuration'] = tempdur - int(row['mWFDur'])
updateVidLog(waveCursor, vidLogDict, row)
# process independent attributes not depending on log-order
if psmode in [2, 3]:
if logType == 6:
vidDict['pauseCnt'] = row['pauseCnt'] + 1
elif logType == 7:
vidDict['resumeCnt'] = row['resumeCnt'] + 1
if logType in [5, 8]:
if netType == '0': #WIFI
vidDict['netW2CTransferCnt'] = row['netW2CTransferCnt'] + 1
elif netType == '1': #mobile
vidDict['netC2WTransferCnt'] = row['netC2WTransferCnt'] + 1
if row['batteryValid'] == '1' and batteryValid == 0:
vidDict['batteryValid'] = '0'
if aatLog.get('netCellState', -1) > 0 and row['netAllowCell'] == '1': #The log not allow cell, as-is fully allowed.
vidDict['netAllowCell'] = '2'
elif aatLog.get('netCellState', -1) == 0 and row['netAllowCell'] == '0': #The log allow cell, as-is not allowed at all.
vidDict['netAllowCell'] = '2'
vidDict['bbCount'] = row['bbCount'] + aatLog.get('bbCount', 0)
if row['elapsedTime'] == 0 and aatLog.get('playPreparingTime', 0) > 0:
vidDict['elapsedTime'] = aatLog['playPreparingTime']
elif row['mLogType'] == 10 and row['mBufferState'] == '2' and aatLog.get('playPreparingTime', 0) > 0:
vidDict['elapsedTime'] = row['elapsedTime'] + aatLog['playPreparingTime']
#insert tables
vidDict['playSessionID'] = row['playSessionID']
updateVidnet(waveCursor, vidDict)
insertVidnetLog(waveCursor, vidLogDict)
except Exception, e:
log.error("vidupdate %s" % e)
log.error(aatLog)
log.error(vidDict)
raise e
def vidcreate(waveCursor, aatLog):
try:
vidDict = {}
vidLogDict = {}
#get some values to use
cellid = "%s_%s_%s" % (aatLog.get('confOperator', ''), aatLog.get('netCID', ''), aatLog.get('netLAC', ''))
psmode = int(aatLog['playServiceMode'])
logType = aatLog.get('agentLogType', -1)
if aatLog.get('netActiveNetwork', '').find('WIFI') >= 0:
netType = '0'
elif aatLog.get('netActiveNetwork', '').find('mobile') >= 0:
netType = '1'
else:
netType = '2'
vidDict['playSessionID'] = aatLog['playSessionId']
vidDict['androidID'] = aatLog.get('deviceID', '')
vidDict['vID'] = aatLog.get('vID', '')
vidDict['sID'] = aatLog.get('sID', 0)
vidDict['verCode'] = aatLog.get('verCode', 0)
vidDict['osVer'] = aatLog.get('osVer', '')
vidDict['brand'] = aatLog.get('brand', '')
vidDict['model'] = aatLog.get('model', '')
vidDict['cellIdSt'] = cellid
vidDict['cellIdEnd'] = cellid
vidDict['bMao'] = int(aatLog.get('agentAatOnOff', -1))
vidDict['bAnsAllow'] = int(aatLog.get('agentAllowAns', -1))
vidDict['bCellAllow'] = int(aatLog.get('agentAllowMobile', -1))
vidDict['ansMode'] = aatLog.get('agentAnsMode', -1)
vidDict['agentUserSetup'] = aatLog.get('agentUserSetup', '')
#vidDict['ansMode'] = aatLog.get('agentAnsMode', -1)
vidDict['hostName'] = aatLog.get('playHost', '')
vidDict['originName'] = aatLog.get('playOrigin', '')
vidDict['contentID'] = aatLog.get('playContentId', '')
vidDict['playServiceMode'] = psmode
if psmode == 1:
vidDict['contentSize'] = aatLog.get('vodContentSize', 0)
elif psmode == 4:
vidDict['contentSize'] = aatLog.get('audContentSize', 0)
elif psmode == 5:
vidDict['contentSize'] = aatLog.get('adnContentSize', 0)
else:
vidDict['contentSize'] = 0
if psmode == 1:
vidDict['contentDuration'] = aatLog.get('vodContentDuration', 0)
elif psmode == 4:
vidDict['contentDuration'] = aatLog.get('audContentDuration', 0)
elif psmode == 5:
vidDict['contentDuration'] = aatLog.get('adnDownloadTime', 0)
else:
vidDict['contentDuration'] = 0
if psmode in [2,3]:
vidDict['contentBitrate'] = aatLog.get('liveCurrentTSBitrate', 0)
else:
vidDict['contentBitrate'] = 0
#vidDict['channelName'] = aatLog.get('playTitle', '').encode('utf-8')
vidDict['channelName'] = aatLog.get('playTitle', '')
vidDict['pkgnm'] = aatLog.get('pkgName', '')
vidDict['apppkgnm'] = ""
vidDict['appvercd'] = ""
if(aatLog.has_key('playAppPackageName')):
appPkgs = aatLog['playAppPackageName'].split('/')
if len(appPkgs) >= 2:
vidDict['apppkgnm'] = appPkgs[0]
vidDict['appvercd'] = appPkgs[1]
if aatLog.has_key('netConnectedNetworkCount'):
vidDict['connectedNetCnt']=aatLog['netConnectedNetworkCount']
elif aatLog.has_key('netConnectivityCount'):
vidDict['connectedNetCnt']=aatLog['netConnectivityCount']
else:
vidDict['connectedNetCnt']=0
vidDict['abrBitrateList'] = aatLog.get('playBitrateList', '')
vidDict['abrUserSelBR'] = aatLog.get('userSelectBitrate', '')
if psmode == 5:
vidDict['vidnetType'] = aatLog.get('adnStartCode', 0)
vidDict['adnMode'] = aatLog.get('adnMode', '')
vidDict['adnRangeStart'] = aatLog.get('adnContentRangeStart', 0)
vidDict['adnDownSize'] = aatLog.get('adnDownloadSize', 0)
vidDict['adnContentID'] = aatLog.get('adnContentID', 0)
vidDict['startLogType'] = logType
vidDict['endLogType'] = logType
vidDict['vidnetStartTime'] = aatLog.get('agentLogStartTime', 0)
vidDict['vidnetEndTime'] = aatLog.get('agentLogEndTime', 0)
vidDict['vidnetDuration'] = vidDict['vidnetEndTime'] - vidDict['vidnetStartTime']
# process independent attributes not depending on log-order
vidDict['pauseCnt'] = 0
vidDict['resumeCnt'] = 0
vidDict['netW2CTransferCnt'] = 0
vidDict['netC2WTransferCnt'] = 0
if psmode in [2, 3]:
if logType == 6:
vidDict['pauseCnt'] = 1
elif logType == 7:
vidDict['resumeCnt'] = 1
elif psmode in [1, 4, 5]:
vidDict['pauseCnt'] = aatLog.get('playBufferingCount', 0)
vidDict['resumeCnt'] = aatLog.get('playResumeCount', 0)
if logType in [5, 8]:
if netType == '0': #WIFI
vidDict['netW2CTransferCnt'] = 1
elif netType == '1': #mobile
vidDict['netC2WTransferCnt'] = 1
vidDict['playTime'] = aatLog.get('playPlayingTime', 0)
vidDict['seekCnt'] = aatLog.get('playSeekCount', 0)
vidDict['ffCnt'] = aatLog.get('playSeekForwardCount', 0)
vidDict['rwCnt'] = aatLog.get('playSeekRewindCount', 0)
vidDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0)
vidDict['maxPauseTime'] = aatLog.get('playMaxBufferingTime', 0)
vidDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0)
vidDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0)
vidDict['cellAvgTP'] = round(aatLog.get('trafficAgentMoAveBW',0), 4)
vidDict['wfAvgTP'] = round(aatLog.get('trafficAgentWFAveBW',0), 4)
vidDict['cellDuration'] = 0
vidDict['wfDuration'] = 0
vidDict['cellSysRxBytes'] = aatLog.get('trafficSystemMoRxBytes', 0)
vidDict['wfSysRxBytes'] = aatLog.get('trafficSystemWFRxBytes', 0)
if vidDict['cellAvgTP'] > 0:
vidDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000))
if vidDict['wfAvgTP'] > 0:
vidDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000))
batteryStart = 0
batteryEnd = 0
batteryValid = '0'
if aatLog.has_key('batteryInfo'):
btList = aatLog['batteryInfo'].split('|')
if len(btList) == 2:
if len(btList[0].split('/')) >= 5 and len(btList[1].split('/')) >= 5:
nTotLevel = float(btList[0].split('/')[3])
nBatLevel = float(btList[0].split('/')[4])
batteryStart = (nBatLevel/nTotLevel)*100
nTotLevel = float(btList[1].split('/')[3])
nBatLevel = float(btList[1].split('/')[4])
batteryEnd = (nBatLevel/nTotLevel)*100
if btList[1].split('/')[1] == 'DISCHARGING': #All batteryInfo reporting log must be 'DISCHARGING' except first.
batteryValid = 1
else:
batteryValid = 0
elif len(btList) == 1:
if len(btList[0].split('/')) >= 5:
nTotLevel = float(btList[0].split('/')[3])
nBatLevel = float(btList[0].split('/')[4])
batteryStart = (nBatLevel/nTotLevel)*100
batteryEnd = batteryStart
batteryValid = 0
vidDict['batteryStart'] = batteryStart
vidDict['batteryEnd'] = batteryEnd
vidDict['batteryValid'] = str(batteryValid)
if aatLog.get('netCellState', -1) > 0:
vidDict['netAllowCell'] = '0'
elif aatLog.get('netCellState', -1) == 0:
vidDict['netAllowCell'] = '1'
vidDict['bbCount'] = aatLog.get('bbCount', 0)
vidDict['elapsedTime'] = aatLog.get('playPreparingTime', 0)
#get appSessionID
vidDict['appSessionIDSt'] = ''
vidDict['appSessionIDEnd'] = ''
strSQL = """SELECT MAX(1) as ord, MAX(sessionID) as sessionID FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d
and (startTime - 5) <= %d and startTime > 0 and (endTime > %d or statAppss > '0')
UNION ALL
SELECT MAX(2), MAX(sessionID) FROM appsession WHERE androidID = '%s' and pkgnm = '%s' and sID = %d
and startTime < %d and startTime > 0 and ((endTime + 5) > %d or statAppss > '0')
""" % (aatLog['deviceID'], aatLog['pkgName'], aatLog['sID'], aatLog['agentLogStartTime'], aatLog['agentLogStartTime'],
aatLog['deviceID'], aatLog['pkgName'], aatLog['sID'], aatLog['agentLogEndTime'], aatLog['agentLogEndTime'])
ret = waveCursor.execute(strSQL)
if ret > 0:
aarows = waveCursor.fetchall()
for r in aarows:
if r['sessionID'] > '' and r['sessionID'] <> None:
if r['ord'] == 1:
vidDict['appSessionIDSt'] = r['sessionID']
elif r['ord'] == 2:
vidDict['appSessionIDEnd'] = r['sessionID']
#vidsession_log values
getVidLogStatic(vidLogDict, aatLog, vidDict['appSessionIDSt'], netType)
vidLogDict['playTime'] = aatLog.get('playPlayingTime', 0)
vidLogDict['pauseTime'] = aatLog.get('playAccBufferingTime', 0)
vidLogDict['elapsedTime'] = aatLog.get('playPreparingTime', 0)
vidLogDict['cellRxBytes'] = aatLog.get('trafficAgentMoBytes', 0)
vidLogDict['wfRxBytes'] = aatLog.get('trafficAgentWFBytes', 0)
vidLogDict['cellSysRxBytes'] = aatLog.get('trafficSystemMoRxBytes', 0)
vidLogDict['wfSysRxBytes'] = aatLog.get('trafficSystemWFRxBytes', 0)
vidLogDict['cellDuration'] = 0
vidLogDict['wfDuration'] = 0
if round(aatLog.get('trafficAgentMoAveBW',0), 4) > 0:
vidLogDict['cellDuration'] = int((aatLog.get('trafficAgentMoBytes', 0)*8) / (aatLog['trafficAgentMoAveBW']*1000000))
if round(aatLog.get('trafficAgentWFAveBW',0), 4) > 0:
vidLogDict['wfDuration'] = int((aatLog.get('trafficAgentWFBytes', 0)*8) / (aatLog['trafficAgentWFAveBW']*1000000))
#insert tables
insertVidnet(waveCursor, vidDict)
insertVidnetLog(waveCursor, vidLogDict)
except Exception, e:
log.error("vidcreate %s" % e)
log.error(aatLog)
raise e
