def importHuaweiAgencys(self, jsonresult):
totalCount = jsonresult.get("totalCount", 0)
accounts = jsonresult.get("accounts", [])
if 0 == totalCount or 0 == len(accounts):
return
# 入库account表
for i in accounts:
accountId = i.get('accountId', 0)
sql = 'select * from %s where `account_id` = "%d"' % (
Config.TableMap.get('HUAWEI_AGENCYS_TABLE'), accountId)
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
status = Config.AUDIT_OK if i['status'] == 'AUDITED' else Config.AUDIT_FAIL
jsondata = json.dumps({
"createTime": i.get('createTime', ''),
"lastDaySpent": i.get('lastDaySpent', ''),
"todaySpent": i.get('todaySpent', ''),
"status": i.get('status', '')
})
valuesTuple = (
i.get('corpName', ''),
status,
i.get('balance', 0),
accountId,
jsondata,
)
xx = time.strftime("%Y%m%d", time.localtime())
batch = self.api.getHbaseConn("account")
rowkey = '{}|{}|{}|{}|{}'.format(str(accountId)[-1:], xx, self.mediaId, accountId, self.agencyId)
data_list = json.dumps(jsondata)
batch.put(row=rowkey, data={'info:data': str(data_list)})
batch.send()
try:
if (res):
# update
sets = {
"`corp_name` = '%s'" % valuesTuple[0],
"`status` = %s" % valuesTuple[1],
"`balance` = %s" % valuesTuple[2],
"`account_id` = '%s'" % valuesTuple[3],
"`jsondata` = '%s'" % valuesTuple[4],
}
sql = "update %s set %s where `account_id` = '%s'" % (
Config.TableMap.get('HUAWEI_AGENCYS_TABLE'), ",".join(sets), accountId)
Utils.D("Update: %s" % sql)
else:
# insert
values = "'%s',%s,%s,'%s','%s'" % valuesTuple
sql = "insert into %s (`corp_name`, `status`, `balance`, `account_id`, `jsondata`) values (%s)" % (
Config.TableMap.get('HUAWEI_AGENCYS_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()
def importDenyKeywordList(self, jsonresult, accountId):
for i in jsonresult.get('taskNegativeSearchKeyInfo', []):
searchKey = i.get('searchKey', '')
appName = i.get('appName', '')
taskName = i.get('taskName', '')
taskId = str(i.get('taskId', ''))
keyword_str = searchKey + appName + taskName + taskId
deny_keyword_id = Utils.md5(keyword_str.encode("utf-8"))
valuesTuple = (
deny_keyword_id,
int(i.get('taskId', 0)),
i.get('taskName', ''),
appName,
searchKey,
accountId,
self.agencyId,
self.mediaId,
)
sql = 'select * from %s where `account_id` = "%s" and `agency_id` = %d and `deny_keyword_id` = "%s"' % (
Config.TableMap.get('KEYWORD_DENY_TABLE'), accountId, self.agencyId, deny_keyword_id)
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
try:
if (res):
# update
sets = {
"`deny_keyword_id` = '%s'" % valuesTuple[0],
"`task_id` = %s" % valuesTuple[1],
"`task_name` = '%s'" % valuesTuple[2],
"`app_name` = '%s'" % valuesTuple[3],
"`keyword` = '%s'" % valuesTuple[4],
"`account_id` = '%s'" % valuesTuple[5],
"`agency_id` = %s" % valuesTuple[6],
"`media_id` = %s" % valuesTuple[7],
}
sql = 'update %s set %s where `account_id` = "%s" and `agency_id` = %s and `deny_keyword_id` = "%s"' % (
Config.TableMap.get('KEYWORD_DENY_TABLE'), ",".join(sets), accountId, self.agencyId,
deny_keyword_id)
Utils.D("Update: %s" % sql)
else:
# insert
values = "'%s', %s, '%s', '%s', '%s', '%s', %s, %s" % valuesTuple
sql = "insert into %s (`deny_keyword_id`, `task_id`, `task_name`, `app_name`, `keyword`, `account_id`, `agency_id`, `media_id`) values (%s)" % (
Config.TableMap.get('KEYWORD_DENY_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()
def dataManager(self, startdate, enddate):
try:
dates = Utils.getDayDeltas(startdate, enddate)
for dateval in dates:
startdate = enddate = dateval
formdata = {
"beginTime": startdate,
"endTime": enddate,
"fromRecCount": 0,
"maxReqCount": self.maxReqCount,
"nsp_svc": "AppPromote.Agent.queryReport",
"access_token": self.accessToken,
"nsp_fmt": "JSON",
"nsp_ts": Utils.getMicSecond()
}
Utils.D(formdata)
result = self.s.post(self.restUrl,
data=formdata,
headers=self.headers).text
try:
jsonresult = json.loads(result, encoding="utf-8")
except:
continue
if 'error' in jsonresult:
raise HuaweiException(Config.KD_ERROR, [
jsonresult.get('error'),
'methodName: %s' % sys._getframe().f_code.co_name,
"resultText: %s" % result,
"agencyId: %s" % self.agencyId,
"mediaId: %s" % self.mediaId,
"startdate: %s" % startdate,
"enddate: %s" % enddate,
])
# print jsonresult
# exit()
# 入库huawei_daily_report_account表
self.logic.importDataManager(jsonresult, startdate)
time.sleep(Config.HUAWEI_REQUEST_TIME)
except HuaweiException as e:
self.log(e.code, e.msg, log_prefix="huawei")
def work(self, *args, **kwargs):
# print args
Utils.D(kwargs)
agencytype = kwargs['agencytype'] # 代理类型：agencyuser直客，agencys二代维护
operation = kwargs['operation']
startdate = kwargs['startdate']
enddate = kwargs['enddate']
agency_ext = kwargs.get("agency_ext", {})
agency_user_id = agency_ext.get('agency_user_id', '') # 二代用户id
# 直客
if 'agencyuser' == agencytype and '' != agency_user_id:
if 'getaccounts' == operation:
self.accountManager()
elif 'datamanager' == operation:
self.dataManager(startdate, enddate)
elif 'keywordreport' == operation:
self.keywordReport(startdate, enddate)
elif 'keywordlist' == operation:
self.keywordList(startdate, enddate)
elif 'keyworddenylist' == operation:
self.keywordDenyList()
elif 'taskreport' == operation:
self.taskReport(startdate, enddate)
elif 'tasklist' == operation:
self.taskList(agency_user_id)
else:
self.accountManager()
self.dataManager(startdate, enddate)
self.taskList(agency_user_id)
self.taskReport(startdate, enddate)
self.keywordList(startdate, enddate)
self.keywordDenyList()
self.keywordReport(startdate, enddate)
# 二代维护
if 'agencys' == agencytype:
if 'agencyslist' == operation:
self.huaweiAgencys()
'python Aiqy.py -t <agencytype> -o <operation> -s <startdate> -e <enddate>'
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print
'python Aiqy.py -t <agencytype> -o <operation> -s <startdate> -e <enddate>'
sys.exit()
elif opt in ("-t", "--agencytype"):
agencytype = arg
elif opt in ("-o", "--operation"):
operation = arg
elif opt in ("-s", "--startdate"):
startdate = arg
elif opt in ("-e", "--enddate"):
enddate = arg
Utils.D('代理类型：%s' % agencytype)
Utils.D('执行的操作：%s' % operation)
Utils.D('开始时间：%s' % startdate)
Utils.D('结束时间：%s' % enddate)
hl = HuaweiLogic()
agencys = hl.getAgencys(agencytype)
for i in agencys:
agency_ext = json.loads(i.get('agency_ext', '{}'))
kwargs = {
"agencytype": agencytype,
"operation": operation,
"startdate": startdate,
"enddate": enddate,
"agency_ext": agency_ext
}
hw = HuaweiSpider(i['media_id'], i['id'], kwargs)
def importKeywordReport(self, jsonresult, accountId, startdate):
for i in jsonresult.get('taskSearchKeyReportList'):
jsondata = json.dumps({
"aveRanking": i.get('aveRanking', ''),
"fastSearchAvePrice": i.get('fastSearchAvePrice', ''),
"matchType": i.get('matchType', ''),
"searchListAvePrice": i.get('searchListAvePrice', ''),
"siteType": i.get('siteType', ''),
"totalConsumeSum": i.get('totalConsumeSum', ''),
"totalDownloadAll": i.get('totalDownloadAll', ''),
}, encoding="utf-8")
searchKey = i.get('searchKey', '')
appName = i.get('appName', '')
taskId = str(i.get('taskId', ''))
taskName = i.get('taskName', '')
keyword_str = searchKey + appName + taskId + taskName + accountId
report_keyword_id = Utils.md5(keyword_str.encode("utf-8"))
valuesTuple = (
report_keyword_id,
int(i.get('taskId', 0)),
i.get('taskName', ''),
appName,
searchKey,
jsondata,
accountId,
self.agencyId,
self.mediaId,
startdate
)
try:
sql = 'select * from %s where `account_id` = "%s" and `agency_id` = %d and `report_keyword_id` = "%s" and `ymd` = "%s"' % (
Config.TableMap.get('KEYWORD_REPORT_TABLE'), accountId, self.agencyId, report_keyword_id, startdate)
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
if (res):
# update
sets = {
"`report_keyword_id` = '%s'" % valuesTuple[0],
"`task_id` = %s" % valuesTuple[1],
"`task_name` = '%s'" % valuesTuple[2],
"`app_name` = '%s'" % valuesTuple[3],
"`keyword` = '%s'" % valuesTuple[4],
"`jsondata` = '%s'" % valuesTuple[5],
"`account_id` = '%s'" % valuesTuple[6],
"`agency_id` = %s" % valuesTuple[7],
"`media_id` = %s" % valuesTuple[8],
}
sql = "update %s set %s where `account_id` = '%s' and `agency_id` = %s and `report_keyword_id` = '%s' and `ymd` = '%s'" % (
Config.TableMap.get('KEYWORD_REPORT_TABLE'), ",".join(sets), accountId, self.agencyId,
report_keyword_id, startdate)
Utils.D("Update: %s" % sql)
else:
# insert
values = "'%s', %s, '%s', '%s', '%s', '%s', '%s', %s, %s, '%s'" % valuesTuple
sql = "insert into %s (`report_keyword_id`,`task_id`, `task_name`, `app_name`, `keyword`, `jsondata`, `account_id`, `agency_id`, `media_id`,`ymd`) values (%s)" % (
Config.TableMap.get('KEYWORD_REPORT_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()
def importKeywordList(self, jsonresult, accountId, startdate):
for i in jsonresult.get('taskPositiveSearchKeyInfo', []):
status = Config.AUDIT_OK if i['searchKeyStatus'] == 'ON' else Config.AUDIT_FAIL
jsondata = json.dumps({
"downloadRate": i.get('downloadRate', ''),
"fastSearchPrice": i.get('fastSearchPrice', ''),
"searchListPrice": i.get('searchListPrice', ''),
"todayConsume": i.get('todayConsume', ''),
"totalConsumeSum": i.get('totalConsumeSum', ''),
"totalDownloadAll": i.get('totalDownloadAll', ''),
"totalImpressionAll": i.get('totalImpressionAll', ''),
"searchKeyStatus": i.get('searchKeyStatus', '')
}, encoding="utf-8")
searchKey = i.get('searchKey', '')
appName = i.get('appName', '')
searchKeyStatus = i.get('searchKeyStatus', '')
taskName = i.get('taskName', '')
taskId = str(i.get('taskId', ''))
keyword_str = searchKey + appName + searchKeyStatus + taskId + taskName + accountId
keyword_id = Utils.md5(keyword_str.encode("utf-8"))
valuesTuple = (
status,
int(i.get('taskId', 0)),
i.get('taskName', ''),
appName,
searchKey,
jsondata,
accountId,
self.agencyId,
self.mediaId,
keyword_id,
startdate
)
sql = 'select * from %s where `account_id` = "%s" and `agency_id` = %d and `keyword_id` = "%s" and `ymd` = "%s"' % (
Config.TableMap.get('KEYWORD_TABLE'), accountId, self.agencyId, keyword_id, startdate)
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
try:
if (res):
# update
sets = {
"`status` = %s" % valuesTuple[0],
"`task_id` = %s" % valuesTuple[1],
"`task_name` = '%s'" % valuesTuple[2],
"`app_name` = '%s'" % valuesTuple[3],
"`keyword` = '%s'" % valuesTuple[4],
"`jsondata` = '%s'" % valuesTuple[5],
"`account_id` = '%s'" % valuesTuple[6],
"`agency_id` = %s" % valuesTuple[7],
"`media_id` = %s" % valuesTuple[8],
"`keyword_id` = '%s'" % valuesTuple[9],
}
sql = 'update %s set %s where `account_id` = "%s" and `agency_id` = %s and `keyword_id` = "%s" and `ymd` = "%s"' % (
Config.TableMap.get('KEYWORD_TABLE'), ",".join(sets), accountId, self.agencyId, keyword_id,
startdate)
Utils.D("Update: %s" % sql)
else:
# insert
values = "%s, %s, '%s', '%s', '%s', '%s', '%s', %s, %s, '%s', '%s'" % valuesTuple
sql = "insert into %s (`status`, `task_id`, `task_name`, `app_name`, `keyword`, `jsondata`, `account_id`, `agency_id`, `media_id`, `keyword_id`, `ymd`) values (%s)" % (
Config.TableMap.get('KEYWORD_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()
def importTaskReport(self, jsonresult, accountId, startdate):
totalCount = jsonresult.get('totalCount', 0)
taskDateList = jsonresult.get('taskDateList', [])
if 0 == totalCount or 0 == len(taskDateList):
return
for i in taskDateList:
jsondata = json.dumps({
"clickRate": i.get('clickRate', ''),
"download": i.get('download', ''),
"downloadAvgMoney": i.get('downloadAvgMoney', ''),
"downloadRate": i.get('downloadRate', ''),
"impression": i.get('impression', ''),
"money": i.get('money', ''),
"pricingType": i.get('pricingType', ''),
"siteSlotName": i.get('siteSlotName', ''),
"slotName": i.get('slotName', ''),
}, encoding="utf-8")
taskName = i.get('taskName', '')
date = i.get('date', '')
appName = i.get('appName', '')
taskId = str(i.get('taskId', ''))
keyword_str = taskName + date + appName + taskId + accountId
report_task_id = Utils.md5(keyword_str.encode("utf-8"))
valuesTuple = (
report_task_id,
int(i.get('taskId', 0)),
taskName,
appName,
jsondata,
date,
accountId,
self.agencyId,
self.mediaId,
startdate
)
sql = 'select * from %s where `account_id` = "%s" and `agency_id` = %d and `report_task_id` = "%s" and `ymd` = "%s"' % (
Config.TableMap.get('TASK_REPORT_TABLE'), accountId, self.agencyId, report_task_id, startdate)
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
try:
if (res):
# update
sets = {
"`report_task_id` = '%s'" % valuesTuple[0],
"`task_id` = %s" % valuesTuple[1],
"`task_name` = '%s'" % valuesTuple[2],
"`app_name` = '%s'" % valuesTuple[3],
"`jsondata` = '%s'" % valuesTuple[4],
"`report_date` = '%s'" % valuesTuple[5],
"`account_id` = '%s'" % valuesTuple[6],
"`agency_id` = %s" % valuesTuple[7],
"`media_id` = %s" % valuesTuple[8],
}
sql = 'update %s set %s where `account_id` = "%s" and `agency_id` = %s and `report_task_id` = "%s" and `ymd` = "%s"' % (
Config.TableMap.get('TASK_REPORT_TABLE'), ",".join(sets), accountId, self.agencyId,
report_task_id, startdate)
Utils.D("Update: %s" % sql)
else:
# insert
values = "'%s', %s, '%s', '%s', '%s', '%s', '%s', %s, %s, '%s'" % valuesTuple
sql = "insert into %s (`report_task_id`, `task_id`, `task_name`, `app_name`, `jsondata`, `report_date`, `account_id`, `agency_id`, `media_id`, `ymd`) values (%s)" % (
Config.TableMap.get('TASK_REPORT_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()
def importTaskList(self, jsonresult, accountId):
totalCount = jsonresult.get('totalCount', 0)
taskList = jsonresult.get('taskList', [])
if 0 == totalCount or 0 == len(taskList):
return
for i in taskList:
sql = 'select * from %s where `account_id` = "%s" and `agency_id` = %d and `task_id` = %d' % (
Config.TableMap.get('TASK_TABLE'), accountId, self.agencyId, int(i['taskID']))
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
status = Config.AUDIT_OK if i['status'] == 'RUN' else Config.AUDIT_FAIL
jsondata = json.dumps({
"auditLastFlag": i.get('auditLastFlag', ''),
"auditLastTime": i.get('auditLastTime', ''),
"budget": i.get('budget', ''),
"contentStatus": i.get('contentStatus', ''),
"dailyLimit": i.get('dailyLimit', ''),
"download": i.get('download', ''),
"downloadRate": i.get('downloadRate', ''),
"endDate": i.get('endDate', ''),
"impression": i.get('impression'),
"mediaChannel": i.get('mediaChannel', ''),
"price": i.get('price', ''),
"pricingType": i.get('pricingType', ''),
"rtbStatus": i.get('rtbStatus', ''),
"schedule": i.get('schedule', ''),
"spent": i.get('spent', ''),
"startDate": i.get('startDate', ''),
"todaySpent": i.get('todaySpent', ''),
"status": i.get('status', '')
}, encoding="utf-8")
valuesTuple = (
status,
int(i.get('taskID', 0)),
i.get('taskName', ''),
int(i.get('contentAppId', 0)),
i.get('contentAppName', ''),
jsondata,
accountId,
self.agencyId,
self.mediaId,
)
try:
if (res):
# update
task_id = valuesTuple[1]
sets = {
"`status` = %s" % valuesTuple[0],
"`task_id` = %s" % task_id,
"`task_name` = '%s'" % valuesTuple[2],
"`app_id` = %s" % valuesTuple[3],
"`app_name` = '%s'" % valuesTuple[4],
"`jsondata` = '%s'" % valuesTuple[5],
"`account_id` = '%s'" % valuesTuple[6],
"`agency_id` = %s" % valuesTuple[7],
"`media_id` = %s" % valuesTuple[8],
}
sql = "update %s set %s where `account_id` = '%s' and `agency_id` = %s and `task_id` = %s" % (
Config.TableMap.get('TASK_TABLE'), ",".join(sets), accountId, self.agencyId, task_id)
Utils.D("Update: %s" % sql)
else:
# insert
values = "%s, %s, '%s', %s, '%s', '%s', '%s', %s, %s" % valuesTuple
sql = "insert into %s (`status`, `task_id`, `task_name`, `app_id`, `app_name`, `jsondata`, `account_id`, `agency_id`, `media_id`) values (%s)" % (
Config.TableMap.get('TASK_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()
def importDataManager(self, jsonresult, startdate):
totalCount = jsonresult.get("totalCount", 0)
reportDetail = jsonresult.get("reportDetail", [])
if 0 == totalCount or 0 == len(reportDetail):
return
# [1:]从第二条记录开始循环，第一条记录时报表的汇总信息
for i in reportDetail[1:]:
accountId = i.get('accountId', 0)
sql = 'select * from %s where `account_id` = "%s" and `agency_id` = %d and `ymd` = "%s"' % (
Config.TableMap.get('DATA_MANAGER_TABLE'), accountId, self.agencyId, startdate)
Utils.D("Query: %s" % sql)
res = self.cursor.execute(sql) # 如果找到返回主键id，否则返回0
jsondata = json.dumps({
"cashSpend": i.get('cashSpend', ''), # 现金消耗
"totalSpend": i.get('totalSpend', ''), # 总消耗
"exchangeSpend": i.get('exchangeSpend', ''), # 置换消耗
"giftSpend": i.get('giftSpend', ''), # 赠送消耗
"totalImp": i.get('totalImp', ''), # 曝光量
"totalDownload": i.get('totalDownload', ''), # 下载量
}, encoding="utf-8")
valuesTuple = (
accountId,
self.agencyId,
self.mediaId,
jsondata,
i.get('corpName', ''),
startdate
)
batch = self.api.getHbaseConn("huawei_daily_report_account")
rowkey = '{}|{}|{}|{}|{}'.format(str(accountId)[-1:], startdate.replace('-', ''), self.mediaId, accountId,
self.agencyId)
data_list = json.dumps(jsondata)
batch.put(row=rowkey, data={'info:data': str(data_list)})
batch.send()
try:
if (res):
# update
sets = {
"`account_id` = '%s'" % valuesTuple[0],
"`agency_id` = %s" % valuesTuple[1],
"`media_id` = %s" % valuesTuple[2],
"`jsondata` = '%s'" % valuesTuple[3],
"`corpname` = '%s'" % valuesTuple[4],
}
sql = "update %s set %s where `account_id` = '%s' and `agency_id` = %s and `ymd` = '%s'" % (
Config.TableMap.get('DATA_MANAGER_TABLE'), ",".join(sets), accountId, self.agencyId, startdate)
Utils.D("Update: %s" % sql)
else:
# insert
values = "'%s',%s,%s,'%s','%s','%s'" % valuesTuple
sql = "insert into %s (`account_id`, `agency_id`, `media_id`, `jsondata`, `corpname`, `ymd`) values (%s)" % (
Config.TableMap.get('DATA_MANAGER_TABLE'), values)
Utils.D("Insert: %s" % sql)
self.cursor.execute(sql)
except:
raise HuaweiException(Config.KD_ERROR, [
"sql: %s" % sql,
"value: %s" % json.dumps(valuesTuple)
])
# 提交事物
self.db.commit()