ai_callback_add_creative_handler.py 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335
  1. from typing import Optional, Awaitable
  2. import pandas as pd
  3. import numpy as np
  4. import datetime
  5. import pymysql
  6. from sqlalchemy import create_engine
  7. import simplejson
  8. import requests
  9. import tornado.web
  10. import pandas as pd
  11. import json
  12. import numpy as np
  13. import datetime
  14. from sqlalchemy import create_engine
  15. from db_config import *
  16. import traceback
  17. import uuid
  18. class AiCallBackAddCreative(tornado.web.RequestHandler):
  19. def initialize(self, logger):
  20. self.logger = logger
  21. def post(self):
  22. data = self.request.body
  23. data = str(data, 'utf8')
  24. data = json.loads(data, encoding='utf8')
  25. self.logger.info("*************************************** NEW REQUEST ***************************************")
  26. self.logger.info("raw data from request is %s" % data)
  27. create_group_and_creative_response_data = None
  28. # 1、新增广告组操作
  29. try:
  30. if data["operation_type"] == 1:
  31. inst = ParseAddCampaignOrAddGroupRequest(data)
  32. inst.get_advertiser_strategy_info()
  33. inst.write_intelligence_strategy_table()
  34. if inst.campaign_id == "" or inst.campaign_id is None:
  35. if inst.add_campaign() == -1:
  36. self.write(json.dumps({"message": "广告计划创建失败"}))
  37. self.flush()
  38. return
  39. inst.get_group_dict()
  40. create_group_and_creative_res = inst.res_data
  41. print("create_group_and_creative_res: ", create_group_and_creative_res)
  42. request = requests.post(create_group_and_creative_url, create_group_and_creative_res)
  43. create_group_and_creative_response_data = simplejson.loads(request.text)
  44. except Exception:
  45. self.logger.error(traceback.format_exc())
  46. # 返回接口结果
  47. self.write(json.dumps({'account_id': inst.account_id,'ai_strategy_id': inst.ai_strategy_uuid,
  48. 'message': create_group_and_creative_response_data}))
  49. self.flush()
  50. # 该类解析的是 手动请求的新增广告计划或者新增广告组的数据
  51. class ParseAddCampaignOrAddGroupRequest(object):
  52. def __init__(self, request_data):
  53. self.ai_strategy_uuid = str(uuid.uuid4())
  54. self.request_data = request_data
  55. self.video = self.request_data['video']
  56. self.account_id = self.request_data['account_id']
  57. self.campaign_info = self.request_data['campaign_info']
  58. self.campaign_id = self.request_data['campaign_info'].get('campaign_id', None)
  59. self.group_info = self.request_data['group_info']
  60. self.creative_info = self.request_data['creative_info']
  61. self.operation_type = self.request_data['operation_type']
  62. self.advertiser_strategy_id = None
  63. self.advertiser_strategy = {}
  64. self.res_data = {}
  65. def get_advertiser_strategy_info(self):
  66. """
  67. 获取指定账户下客户的策略信息
  68. 为后续的写入库表,校验一致性做准备
  69. :return:
  70. """
  71. sql = """
  72. select *
  73. from ctop_ai_kuaishou_advertiser_strategy
  74. where account_id = %d
  75. and status = 1
  76. limit 1
  77. """ % self.account_id
  78. advertiser_strategy_df = pd.read_sql(sql, engine)
  79. self.advertiser_strategy_id = advertiser_strategy_df['id'].values[0]
  80. self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]
  81. def write_intelligence_strategy_table(self):
  82. """
  83. 请求信息写入 ctop_ai_kuaishou_intelligence_strategy 表中
  84. :return:
  85. """
  86. intelligence_strategy_dict = \
  87. {
  88. 'ai_strategy_uuid': self.ai_strategy_uuid,
  89. 'advertiser_strategy_id': self.advertiser_strategy_id,
  90. 'account_id': self.account_id,
  91. 'ai_strategy_request_content': str(self.request_data),
  92. 'ai_strategy_remark': self.request_data['ai_strategy_remark'],
  93. 'create_time': datetime.datetime.now()
  94. }
  95. df = pd.DataFrame.from_dict(intelligence_strategy_dict, orient='index').T
  96. df.to_sql(name="ctop_ai_kuaishou_intelligence_strategy", con=engine, if_exists='append', index=False)
  97. # 如果手动请求的数据结构中,"campaign" 中的 campaign_id 为空则调用该新增计划方法
  98. def add_campaign(self):
  99. """
  100. 新增广告计划
  101. :return:
  102. """
  103. # 1、校验请求信息中 有没有 与客户策略信息 中存在冲突的地方 -- 计划层级只判断 campaign_type
  104. if self.campaign_info['campaign_type'] != self.advertiser_strategy['campaign_type']:
  105. self.logger.info("客户策略的campaign_type: %d 和 AI策略的campaign_type:%d 不一致!" %
  106. (self.advertiser_strategy['campaign_type'],
  107. self.campaign_info['campaign_type']))
  108. return -1
  109. # TODO 待联调测试
  110. # create_campaign_req_data = {
  111. # 'account_id': self.account_id,
  112. # 'campaign_name': self.campaign_info['campaign_name'],
  113. # 'type': self.campaign_info['campaign_type']
  114. # }
  115. # request = requests.post(create_campaign_url, create_campaign_req_data)
  116. # res_data = simplejson.loads(request.text)
  117. res_data = {
  118. "code": 0,
  119. "data": {
  120. "account_id": 23212,
  121. "campaign_id": 30956133,
  122. "campaign_create_time": "2021-01-13 13:50:16"
  123. },
  124. "message": "SUCCESS"}
  125. if res_data['code'] == 0:
  126. self.campaign_id = res_data['data']['campaign_id']
  127. campaign_info_to_db = {
  128. 'campaign_uuid': str(uuid.uuid4()),
  129. 'account_id': self.account_id,
  130. 'ai_strategy_uuid': self.ai_strategy_uuid,
  131. 'campaign_name': self.campaign_info['campaign_name'],
  132. 'campaign_id': self.campaign_id,
  133. 'campaign_type': self.campaign_info['campaign_type'],
  134. 'operation_type': self.operation_type,
  135. 'campaign_create_time': res_data['data']['campaign_create_time'],
  136. 'create_time': datetime.datetime.now()
  137. }
  138. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  139. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record", con=engine, if_exists='append', index=False)
  140. else:
  141. self.logger.info("广告计划创建失败:%s" % res_data['message'])
  142. return -1
  143. def get_group_dict(self):
  144. """
  145. 拼接组和创意层级参数
  146. 写入数据库
  147. 返回 table_id
  148. :return:
  149. """
  150. # 1、TODO 校验请求信息中 有没有 与客户策略信息 中存在冲突的地方 -- 暂时不实现
  151. # 客户策略 和 请求信息 都有值 才会进行校验比对操作 -- 不得高于,不得不等于,不得超过集合
  152. # 因此分为3类 字段
  153. # is_equal_cols = ['template_id','bid_type']
  154. # is_less_than_cols = []
  155. # is_contain_cols = []
  156. #
  157. # for col in is_equal_cols:
  158. # if (self.advertiser_strategy[col] is not None) and \
  159. # (self.group_info.get(col, None) is not None) and \
  160. # (self.group_info.get(col) != self.advertiser_strategy[col]):
  161. # return -1
  162. #
  163. # for col in is_less_than_cols:
  164. # if (self.advertiser_strategy[col] is not None) and \
  165. # (self.group_info.get(col, None) is not None) and \
  166. # (self.group_info.get(col) > self.advertiser_strategy[col]):
  167. # return -1
  168. #
  169. # for col in is_contain_cols:
  170. # if (self.advertiser_strategy[col] is not None) and \
  171. # (self.group_info.get(col, None) is not None) and \
  172. # (self.group_info.get(col) > self.advertiser_strategy[col]):
  173. # return -1
  174. basic_group_info = {'template_id': self.advertiser_strategy['template_id'],
  175. 'bid_type': self.advertiser_strategy['bid_type'],
  176. 'use_app_market': self.advertiser_strategy['use_app_market'],
  177. 'app_store': self.advertiser_strategy['app_store'],
  178. 'bid': self.advertiser_strategy['bid'],
  179. 'cpa_bid': self.advertiser_strategy['cpa_bid'],
  180. 'smart_bid': self.advertiser_strategy['smart_bid'],
  181. 'ocpx_action_type': self.advertiser_strategy['ocpx_action_type'],
  182. 'deep_conversion_type': self.advertiser_strategy['deep_conversion_type'],
  183. 'roi_ratio': self.advertiser_strategy['roi_ratio'],
  184. 'scene_id': self.advertiser_strategy['scene_id'],
  185. 'unit_type': self.advertiser_strategy['unit_type'],
  186. 'begin_time': self.advertiser_strategy['begin_time'],
  187. 'end_time': self.advertiser_strategy['end_time'],
  188. 'schedule_time': self.advertiser_strategy['schedule_time'],
  189. 'convert_id': self.advertiser_strategy['convert_id'],
  190. 'url_type': self.advertiser_strategy['url_type'],
  191. 'web_uri_type': self.advertiser_strategy['web_uri_type'],
  192. 'url': self.advertiser_strategy['url'],
  193. 'schema_uri': self.advertiser_strategy['schema_uri'],
  194. 'app_id': self.advertiser_strategy['app_id'],
  195. 'show_mode': self.advertiser_strategy['show_mode'],
  196. 'speed': self.advertiser_strategy['speed'],
  197. 'site_type': self.advertiser_strategy['site_type'],
  198. 'gift_data': self.advertiser_strategy['gift_data'],
  199. 'video_landing_page': self.advertiser_strategy['video_landing_page'],
  200. 'auto_target': self.advertiser_strategy['auto_target'],
  201. 'auto_create_photo': self.advertiser_strategy['auto_create_photo'],
  202. 'region': self.advertiser_strategy['region'],
  203. 'district_ids': self.advertiser_strategy['district_ids'],
  204. 'user_type': self.advertiser_strategy['user_type'],
  205. 'age_min': self.advertiser_strategy['age_min'],
  206. 'age_max': self.advertiser_strategy['age_max'],
  207. 'ages_range': self.advertiser_strategy['ages_range'],
  208. 'gender': self.advertiser_strategy['gender'],
  209. 'platform_os': self.advertiser_strategy['platform_os'],
  210. 'android_osv': self.advertiser_strategy['android_osv'],
  211. 'ios_osv': self.advertiser_strategy['ios_osv'],
  212. 'network': self.advertiser_strategy['network'],
  213. 'device_brand': self.advertiser_strategy['device_brand'],
  214. 'device_price': self.advertiser_strategy['device_price'],
  215. 'business_interest_type': self.advertiser_strategy['business_interest_type'],
  216. 'business_interest': self.advertiser_strategy['business_interest'],
  217. 'fans_star': self.advertiser_strategy['fans_star'],
  218. 'interest_video': self.advertiser_strategy['interest_video'],
  219. 'app_interest': self.advertiser_strategy['app_interest'],
  220. 'app_ids': self.advertiser_strategy['app_ids'],
  221. 'filter_converted_level': self.advertiser_strategy['filter_converted_level'],
  222. 'population': self.advertiser_strategy['population'],
  223. 'exclude_population': self.advertiser_strategy['exclude_population'],
  224. 'paid_audience': self.advertiser_strategy['paid_audience'],
  225. 'is_open': self.advertiser_strategy['is_open'],
  226. 'no_age_break': self.advertiser_strategy['no_age_break'],
  227. 'no_gender_break': self.advertiser_strategy['no_gender_break'],
  228. 'no_area_break': self.advertiser_strategy['no_area_break'],
  229. 'behavior_interest': self.advertiser_strategy['behavior_interest']}
  230. # 3、从请求的信息中更新计划层级信息
  231. if len(self.group_info) > 0:
  232. for key, value in self.group_info.items():
  233. basic_group_info.update({key: value})
  234. self.res_data['group_list'] = []
  235. group_cnt = 1
  236. for video in self.video:
  237. single_group_info = {'group_uuid': str(uuid.uuid4())}
  238. single_group_info.update(basic_group_info)
  239. single_group_info['unit_name'] = self.group_info['unit_name'] + '-' + str(group_cnt)
  240. # 组信息写入数据表中
  241. single_group_info_to_db = single_group_info.copy()
  242. single_group_info_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  243. single_group_info_to_db['account_id'] = self.account_id
  244. single_group_info_to_db['campaign_id'] = self.campaign_id
  245. single_group_info_to_db['create_time'] = datetime.datetime.now()
  246. single_group_info_to_db['operation_type'] = self.operation_type
  247. df = pd.DataFrame.from_dict(single_group_info_to_db, orient='index').T
  248. engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  249. df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",
  250. con=engine,
  251. if_exists='append',
  252. index=False)
  253. single_group_info['creative_list'] = []
  254. group_cnt += 1
  255. creative_cnt = 1
  256. for image in video['imageList']:
  257. single_creative_info = {
  258. 'creative_uuid': str(uuid.uuid4()),
  259. 'is_sticky': 0,
  260. 'creative_name': self.creative_info['creative_name'] + '_' + str(creative_cnt),
  261. 'photo_id': video['photo_id'],
  262. 'image_md5': image, # TODO 需要和蒙蒙确认字段名称
  263. 'image_tokens': self.creative_info.get('image_tokens'),
  264. 'action_bar_text': self.creative_info['action_bar_text'],
  265. 'description': self.creative_info['description'],
  266. 'short_slogan': self.creative_info.get('short_slogan'),
  267. 'sticker_title': self.creative_info.get('sticker_title'),
  268. 'overlay_type': self.creative_info.get('overlay_type'),
  269. 'expose_tag': self.creative_info.get('expose_tag'),
  270. 'new_expose_tag': self.creative_info.get('new_expose_tag'),
  271. 'site_id': self.creative_info.get('site_id'),
  272. 'click_track_url': self.advertiser_strategy.get('click_track_url'),
  273. 'impression_url': self.advertiser_strategy.get('impression_url'),
  274. 'ad_photo_played_t3s_url': self.advertiser_strategy.get('ad_photo_played_t3s_url'),
  275. 'actionbar_click_url': self.advertiser_strategy.get('actionbar_click_url'),
  276. 'creative_category': self.creative_info.get('creative_category'),
  277. 'creative_tag': self.creative_info.get('creative_tag')}
  278. single_group_info['creative_list'].append(single_creative_info)
  279. # 创意层级写入数据表中
  280. single_creative_info_to_db = single_creative_info.copy()
  281. single_creative_info_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  282. single_creative_info_to_db['account_id'] = self.account_id
  283. single_creative_info_to_db['campaign_id'] = self.campaign_id
  284. single_creative_info_to_db['create_time'] = datetime.datetime.now()
  285. single_creative_info_to_db['operation_type'] = self.operation_type
  286. df = pd.DataFrame.from_dict(single_creative_info_to_db, orient='index').T
  287. engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  288. df.to_sql(name="ctop_ai_kuaishou_creative_level_operation_record",
  289. con=engine,
  290. if_exists='append',
  291. index=False)
  292. creative_cnt += 1
  293. # 组和创意信息添加到最终返回数据 group_list 中
  294. self.res_data['group_list'].append(single_group_info)
  295. self.res_data['account_id'] = self.account_id
  296. self.res_data['campaign_id'] = self.campaign_id
  297. # TODO 修改操作
  298. class ParseModifyRequest(object):
  299. def __init__(self):
  300. pass
  301. # TODO 关停操作
  302. class ParseShutDownRequest(object):
  303. def __init__(self):
  304. pass