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 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()
