ai_strategy_request_handler.py 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324
  1. import datetime
  2. import json
  3. import traceback
  4. import uuid
  5. import pandas as pd
  6. import requests
  7. import simplejson
  8. import tornado.web
  9. from db_config import *
  10. class AiStrategyRequestParse(tornado.web.RequestHandler):
  11. def initialize(self, logger):
  12. self.logger = logger
  13. def post(self):
  14. data = self.request.body
  15. data = str(data, 'utf8')
  16. data = json.loads(data, encoding='utf8')
  17. self.logger.info("*************************************** NEW REQUEST ***************************************")
  18. self.logger.info("raw data from request is %s" % data)
  19. create_group_and_creative_response_data = None
  20. # 1、新增广告组操作
  21. try:
  22. if data["operation_type"] == 1:
  23. inst = ParseAddCampaignOrAddGroupRequest(data,self.logger)
  24. inst.get_advertiser_strategy_info()
  25. inst.write_intelligence_strategy_table()
  26. if inst.campaign_id == "" or inst.campaign_id is None:
  27. if inst.add_campaign() == -1:
  28. raise Exception('广告计划创建失败')
  29. inst.get_group_dict()
  30. create_group_and_creative_res = inst.res_data
  31. inst.update_intelligence_strategy_table()
  32. self.logger.info("create group and creative request info: %s" % create_group_and_creative_res)
  33. request = requests.post(create_group_and_creative_url,
  34. headers=headers,
  35. data=json.JSONEncoder().encode(create_group_and_creative_res)
  36. )
  37. create_group_and_creative_response_data = simplejson.loads(request.text)
  38. self.logger.info("请求返回:%s" % create_group_and_creative_response_data)
  39. self.write(json.dumps({'account_id': inst.account_id, 'ai_strategy_id': inst.ai_strategy_uuid,
  40. 'message': create_group_and_creative_response_data}))
  41. self.flush()
  42. else:
  43. self.write(json.dumps({"message": "operation_type 不支持"}))
  44. self.flush()
  45. except Exception:
  46. self.logger.error(traceback.format_exc())
  47. self.write(json.dumps(traceback.format_exc()))
  48. self.flush()
  49. # 该类解析的是 手动请求的新增广告计划或者新增广告组的数据
  50. class ParseAddCampaignOrAddGroupRequest(object):
  51. def __init__(self, request_data, logger):
  52. self.ai_strategy_uuid = str(uuid.uuid4())
  53. self.request_data = request_data
  54. self.logger = logger
  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_receive_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. def update_intelligence_strategy_table(self):
  98. """
  99. 拼装之后的json, 更新到 ai_strategy_request_content
  100. :return:
  101. """
  102. db_con = pymysql.connect(host=host,
  103. port=port,
  104. user=user,
  105. password=passwd,
  106. database=db,
  107. charset=charset)
  108. cursor = db_con.cursor()
  109. cursor.execute("""UPDATE ctop_ai_kuaishou_intelligence_strategy
  110. SET ai_strategy_request_content= %s
  111. WHERE ai_strategy_uuid = %s""", (str(self.res_data), self.ai_strategy_uuid))
  112. db_con.commit()
  113. db_con.close()
  114. # 如果手动请求的数据结构中,"campaign" 中的 campaign_id 为空则调用该新增计划方法
  115. def add_campaign(self):
  116. """
  117. 新增广告计划
  118. :return:
  119. """
  120. # 1、校验请求信息中 有没有 与客户策略信息 中存在冲突的地方 -- 计划层级只判断 campaign_type
  121. if self.campaign_info['campaign_type'] != self.advertiser_strategy['campaign_type']:
  122. self.logger.info("客户策略的campaign_type: %d 和 AI策略的campaign_type:%d 不一致!" %
  123. (self.advertiser_strategy['campaign_type'],
  124. self.campaign_info['campaign_type']))
  125. return -1
  126. create_campaign_req_data = {
  127. 'account_id': self.account_id,
  128. 'campaign_name': self.campaign_info['campaign_name'],
  129. 'type': self.campaign_info['campaign_type']
  130. }
  131. request = requests.post(url=create_campaign_url,
  132. headers=headers,
  133. data=json.JSONEncoder().encode(create_campaign_req_data))
  134. res_data = simplejson.loads(request.text)
  135. # res_data = {
  136. # "code": 0,
  137. # "data": {
  138. # "account_id": 23212,
  139. # "campaign_id": 30956133,
  140. # "campaign_create_time": "2021-01-13 13:50:16"
  141. # },
  142. # "message": "SUCCESS"}
  143. if res_data['code'] == 0:
  144. self.campaign_id = res_data['data']['campaign_id']
  145. campaign_info_to_db = {
  146. 'campaign_uuid': str(uuid.uuid4()),
  147. 'account_id': self.account_id,
  148. 'ai_strategy_uuid': self.ai_strategy_uuid,
  149. 'campaign_name': self.campaign_info['campaign_name'],
  150. 'campaign_id': self.campaign_id,
  151. 'campaign_type': self.campaign_info['campaign_type'],
  152. 'operation_type': self.operation_type,
  153. 'campaign_create_time': res_data['data']['campaign_create_time'],
  154. 'create_time': datetime.datetime.now()
  155. }
  156. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  157. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record", con=engine, if_exists='append', index=False)
  158. else:
  159. self.logger.info("广告计划创建失败:%s" % res_data['message'])
  160. return -1
  161. def get_group_dict(self):
  162. """
  163. 拼接组和创意层级参数
  164. 写入数据库
  165. 返回 table_id
  166. :return:
  167. """
  168. # 1、TODO 校验请求信息中 有没有 与客户策略信息 中存在冲突的地方 -- 暂时不实现
  169. # 客户策略 和 请求信息 都有值 才会进行校验比对操作 -- 不得高于,不得不等于,不得超过集合
  170. # 因此分为3类 字段
  171. # is_equal_cols = ['template_id','bid_type']
  172. # is_less_than_cols = []
  173. # is_contain_cols = []
  174. #
  175. # for col in is_equal_cols:
  176. # if (self.advertiser_strategy[col] is not None) and \
  177. # (self.group_info.get(col, None) is not None) and \
  178. # (self.group_info.get(col) != self.advertiser_strategy[col]):
  179. # return -1
  180. #
  181. # for col in is_less_than_cols:
  182. # if (self.advertiser_strategy[col] is not None) and \
  183. # (self.group_info.get(col, None) is not None) and \
  184. # (self.group_info.get(col) > self.advertiser_strategy[col]):
  185. # return -1
  186. #
  187. # for col in is_contain_cols:
  188. # if (self.advertiser_strategy[col] is not None) and \
  189. # (self.group_info.get(col, None) is not None) and \
  190. # (self.group_info.get(col) > self.advertiser_strategy[col]):
  191. # return -1
  192. drop_cols = ['id', 'account_id', 'status', 'campaign_type',
  193. 'site_id', 'click_track_url', 'impression_url', 'ad_photo_played_t3s_url',
  194. 'actionbar_click_url',
  195. 'create_time', 'effective_time', 'expiry_time']
  196. cols_to_list = ['app_store', 'scene_id', 'region', 'district_ids', 'ages_range', 'device_brand',
  197. 'device_price', 'business_interest', 'fans_star', 'interest_video', 'app_interest',
  198. 'app_ids', 'population', 'exclude_population', 'paid_audience', 'behavior_interest']
  199. basic_group_info = self.advertiser_strategy.copy()
  200. for col in drop_cols:
  201. del basic_group_info[col]
  202. # 3、从请求的信息中更新计划层级信息
  203. if len(self.group_info) > 0:
  204. for key, value in self.group_info.items():
  205. basic_group_info.update({key: value})
  206. self.res_data['group_list'] = []
  207. group_cnt = 1
  208. for video in self.video:
  209. single_group_info = {'group_uuid': str(uuid.uuid4())}
  210. single_group_info.update(basic_group_info)
  211. single_group_info['unit_name'] = self.group_info['unit_name'] + '-' + str(group_cnt)
  212. # 组信息写入数据表中
  213. single_group_info_to_db = single_group_info.copy()
  214. single_group_info_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  215. single_group_info_to_db['account_id'] = self.account_id
  216. single_group_info_to_db['campaign_id'] = self.campaign_id
  217. single_group_info_to_db['create_time'] = datetime.datetime.now()
  218. single_group_info_to_db['operation_type'] = self.operation_type
  219. df = pd.DataFrame.from_dict(single_group_info_to_db, orient='index').T
  220. engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  221. df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",
  222. con=engine,
  223. if_exists='append',
  224. index=False)
  225. # 发送的请求,这几个字段需要转为 [] 数组
  226. for col in cols_to_list:
  227. single_group_info[col] = eval(single_group_info[col]) if single_group_info[col] is not None else None
  228. single_group_info['creative_list'] = []
  229. group_cnt += 1
  230. creative_cnt = 1
  231. for image in video['imageList']:
  232. single_creative_info = {
  233. 'creative_uuid': str(uuid.uuid4()),
  234. 'is_sticky': 0,
  235. 'creative_name': self.creative_info['creative_name'] + '_' + str(creative_cnt),
  236. 'photo_id': video['photo_id'],
  237. 'image_md5': image, # TODO 需要和蒙蒙确认字段名称
  238. 'image_tokens': self.creative_info.get('image_tokens'),
  239. 'action_bar_text': self.creative_info['action_bar_text'],
  240. 'description': self.creative_info['description'],
  241. 'short_slogan': self.creative_info.get('short_slogan'),
  242. 'sticker_title': self.creative_info.get('sticker_title'),
  243. 'overlay_type': self.creative_info.get('overlay_type'),
  244. 'expose_tag': self.creative_info.get('expose_tag'),
  245. 'new_expose_tag': self.creative_info.get('new_expose_tag'),
  246. 'site_id': self.creative_info.get('site_id'),
  247. 'click_track_url': self.advertiser_strategy.get('click_track_url'),
  248. 'impression_url': self.advertiser_strategy.get('impression_url'),
  249. 'ad_photo_played_t3s_url': self.advertiser_strategy.get('ad_photo_played_t3s_url'),
  250. 'actionbar_click_url': self.advertiser_strategy.get('actionbar_click_url'),
  251. 'creative_category': self.creative_info.get('creative_category'),
  252. 'creative_tag': self.creative_info.get('creative_tag')}
  253. single_group_info['creative_list'].append(single_creative_info)
  254. # 创意层级写入数据表中
  255. single_creative_info_to_db = single_creative_info.copy()
  256. single_creative_info_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  257. single_creative_info_to_db['account_id'] = self.account_id
  258. single_creative_info_to_db['campaign_id'] = self.campaign_id
  259. single_creative_info_to_db['create_time'] = datetime.datetime.now()
  260. single_creative_info_to_db['operation_type'] = self.operation_type
  261. cols_to_str = ['creative_tag']
  262. for col in cols_to_str:
  263. single_creative_info_to_db[col] = str(single_creative_info_to_db[col]) \
  264. if single_creative_info_to_db[col] is not None else None
  265. df = pd.DataFrame.from_dict(single_creative_info_to_db, orient='index').T
  266. engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  267. df.to_sql(name="ctop_ai_kuaishou_creative_level_operation_record",
  268. con=engine,
  269. if_exists='append',
  270. index=False)
  271. creative_cnt += 1
  272. # 组和创意信息添加到最终返回数据 group_list 中
  273. self.res_data['group_list'].append(single_group_info)
  274. self.res_data['account_id'] = self.account_id
  275. self.res_data['campaign_id'] = self.campaign_id
  276. # TODO 修改操作
  277. class ParseModifyRequest(object):
  278. def __init__(self):
  279. pass
  280. # TODO 关停操作
  281. class ParseShutDownRequest(object):
  282. def __init__(self):
  283. pass