ai_strategy_request_handler.py 17 KB

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