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