ai_time_task_creative_handler.py 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417
  1. import pandas as pd
  2. from utils.UrlConfig import *
  3. import json
  4. import traceback
  5. from logging.handlers import TimedRotatingFileHandler
  6. from utils.BaseClass import *
  7. from utils.CommonFunction import get_history_video_info, get_new_video_info, get_missing_video_info, \
  8. get_top_video_info, get_campaign_and_group_name_rule
  9. from utils.ConstantConfig import *
  10. from utils.LogConfig import *
  11. from ai_strategy_request_func import ai_strategy_request_parse
  12. from utils.DataBaseConfig import *
  13. log_handler = TimedRotatingFileHandler("logs/ai_auto_create.log",
  14. when="midnight", backupCount=100)
  15. log_handler.setFormatter(log_formatter)
  16. logger = logging.getLogger('ai_time_task_creative_logger')
  17. logger.addHandler(log_handler)
  18. logger.setLevel(logging.DEBUG)
  19. print('id of ai_time_task_creative_logger %s' % id(logger))
  20. logger.info("ai_time_task_creative_server started!")
  21. class AiHistoricalMissingMaterial(tornado.web.RequestHandler):
  22. """
  23. 补充历史遗漏素材(关联创意个数为0个素材) -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  24. """
  25. def post(self):
  26. data = self.request.body
  27. data = str(data, 'utf8')
  28. data = json.loads(data, encoding='utf8')
  29. logger.info("*************************************** NEW REQUEST ***************************************")
  30. logger.info("raw data from request is %s" % data)
  31. account_id = data.get('account_id')
  32. campaign_id = data.get('campaign_id')
  33. try:
  34. video_info = get_missing_video_info(account_id,
  35. missing_video_cnt,
  36. get_missing_video_url,
  37. missing_video_start_time,
  38. missing_video_end_time)
  39. if video_info['code'] != 0:
  40. logger.info("the res of video is %s" % video_info['message'])
  41. logger.info("没有获取到视频,不发送ai策略请求!")
  42. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  43. self.flush()
  44. else:
  45. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  46. if (campaign_name is None) or (group_name is None):
  47. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  48. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  49. self.flush()
  50. else:
  51. request_data = get_request_data(account_id=account_id,
  52. campaign_id=campaign_id,
  53. video_info=video_info,
  54. campaign_name=campaign_name,
  55. group_name=group_name,
  56. ai_strategy_remark="补充遗漏素材",
  57. name_replace="补充遗漏素材")
  58. request = ai_strategy_request_parse(request_data)
  59. logger.info("ai策略的返回信息:%s" % request)
  60. self.write(json.dumps(request))
  61. self.flush()
  62. except Exception:
  63. logger.error(traceback.format_exc())
  64. self.write(json.dumps(traceback.format_exc()))
  65. self.flush()
  66. class AiAutoCreative(tornado.web.RequestHandler):
  67. """
  68. 每5分钟,检查一次是否有上新素材,有的话,就自动创建 -- 需要上定时任务测试
  69. """
  70. def post(self):
  71. data = self.request.body
  72. data = str(data, 'utf8')
  73. data = json.loads(data, encoding='utf8')
  74. logger.info("*************************************** NEW REQUEST ***************************************")
  75. logger.info("raw data from request is %s" % data)
  76. account_id = data.get('account_id')
  77. campaign_id = data.get('campaign_id')
  78. try:
  79. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  80. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  81. # 1-1 获取当天计划中包含“自动上新”计划的,计划id
  82. sql = """
  83. select campaign_id, campaign_name from ctop_kuaishou_campaign
  84. where account_id = %s
  85. and put_create_time >= '%s'
  86. and put_create_time <= '%s'
  87. """ % (account_id, start_time, end_time)
  88. df = pd.read_sql(sql, engine)
  89. for row in df.itertuples():
  90. if '素材自动上新' in getattr(row, 'campaign_name'):
  91. campaign_id = getattr(row, 'campaign_id')
  92. break
  93. video_info = get_new_video_info(account_id, get_new_video_url)
  94. if video_info['code'] != 0:
  95. logger.info("the res of video is %s" % video_info['message'])
  96. logger.info("没有获取到视频,不发送ai策略请求!")
  97. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  98. self.flush()
  99. else:
  100. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  101. if (campaign_name is None) or (group_name is None):
  102. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  103. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  104. self.flush()
  105. else:
  106. request_data = get_request_data(account_id=account_id,
  107. campaign_id=campaign_id,
  108. video_info=video_info,
  109. campaign_name=campaign_name,
  110. group_name=group_name,
  111. ai_strategy_remark="自动上新",
  112. name_replace="素材自动上新")
  113. request = ai_strategy_request_parse(request_data)
  114. logger.info("ai策略的返回信息:%s" % request)
  115. self.write(json.dumps(request))
  116. self.flush()
  117. except Exception:
  118. logger.error(traceback.format_exc())
  119. self.write(json.dumps(traceback.format_exc()))
  120. self.flush()
  121. class AiHighQualityMaterial(tornado.web.RequestHandler):
  122. """
  123. 补充历史高质量素材 -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  124. """
  125. def post(self):
  126. data = self.request.body
  127. data = str(data, 'utf8')
  128. data = json.loads(data, encoding='utf8')
  129. logger.info("*************************************** NEW REQUEST ***************************************")
  130. logger.info("raw data from request is %s" % data)
  131. account_id = data.get('account_id')
  132. campaign_id = data.get('campaign_id')
  133. try:
  134. video_info = get_top_video_info(account_id, high_quality_video_cnt,
  135. (datetime.datetime.now() + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d"),
  136. str(datetime.date.today()), get_high_quality_video_url)
  137. if video_info['code'] != 0:
  138. logger.info("the res of video is %s" % video_info['message'])
  139. logger.info("没有获取到视频,不发送ai策略请求!")
  140. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  141. self.flush()
  142. else:
  143. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  144. if (campaign_name is None) or (group_name is None):
  145. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  146. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  147. self.flush()
  148. else:
  149. request_data = get_request_data(account_id=account_id,
  150. campaign_id=campaign_id,
  151. video_info=video_info,
  152. campaign_name=campaign_name,
  153. group_name=group_name,
  154. ai_strategy_remark="高质量素材复建",
  155. name_replace="高质量素材")
  156. request = ai_strategy_request_parse(request_data)
  157. logger.info("ai策略的返回信息:%s" % request)
  158. self.write(json.dumps(request))
  159. self.flush()
  160. except Exception:
  161. logger.error(traceback.format_exc())
  162. self.write(json.dumps(traceback.format_exc()))
  163. self.flush()
  164. class AiCheckAndUpTOFullCreative(tornado.web.RequestHandler):
  165. """
  166. 每天22:00 调用该任务,检查当天创意是否创建达到上限,否则使用历史高质量素材填满 -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  167. """
  168. def post(self):
  169. data = self.request.body
  170. data = str(data, 'utf8')
  171. data = json.loads(data, encoding='utf8')
  172. logger.info("*************************************** NEW REQUEST ***************************************")
  173. logger.info("raw data from request is %s" % data)
  174. account_id = data.get('account_id')
  175. campaign_id = data.get('campaign_id')
  176. try:
  177. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  178. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  179. # 1-1 获取当天计划中包含“高质量素材”计划的,计划id
  180. sql = """
  181. select campaign_id, campaign_name from ctop_kuaishou_campaign
  182. where account_id = %s
  183. and put_create_time >= '%s'
  184. and put_create_time <= '%s'
  185. """ % (account_id, start_time, end_time)
  186. df = pd.read_sql(sql, engine)
  187. for row in df.itertuples():
  188. if '高质量素材' in getattr(row, 'campaign_name'):
  189. campaign_id = getattr(row, 'campaign_id')
  190. break
  191. # 2-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数
  192. sql = """
  193. select image_cnt from ctop_ai_kuaishou_advertiser_strategy
  194. where account_id = %s
  195. and status = 1
  196. limit 1
  197. """ % account_id
  198. df = pd.read_sql(sql, engine)
  199. image_cnt = int(df['image_cnt'].values[0])
  200. # 3-1 从 ctop_kuaishou_creative 获取当天该账户到此刻 已经创建的创意个数
  201. video_cnt = 0
  202. sql = """
  203. select count(1) creative_cnt from ctop_kuaishou_creative
  204. where account_id = %s
  205. and creative_id is not NULL
  206. and creative_create_time <= '%s'
  207. and creative_create_time >= '%s'
  208. """ % (account_id, end_time, start_time)
  209. df = pd.read_sql(sql, engine)
  210. creative_cnt = int(df['creative_cnt'].values[0])
  211. if creative_cnt >= 2000:
  212. video_cnt = 0
  213. else:
  214. video_cnt = (2000 - creative_cnt) // image_cnt
  215. # 4-1 获取视频信息
  216. if video_cnt == 0:
  217. logger.info("创意已经建满,不需要补充素材!")
  218. self.write(json.dumps("message: 创意已经建满,不需要补充素材!"))
  219. self.flush()
  220. else:
  221. logger.info("需要补充的视频数量:%s" % video_cnt)
  222. now = datetime.datetime.now()
  223. video_info = get_top_video_info(account_id,
  224. video_cnt,
  225. (now + datetime.timedelta(days=-2*high_quality_video_days)).strftime("%Y-%m-%d"),
  226. (now + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d"),
  227. get_high_quality_video_url)
  228. if video_info['code'] != 0:
  229. logger.info("the res of video is %s" % video_info['message'])
  230. logger.info("没有获取到视频,不发送ai策略请求!")
  231. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  232. self.flush()
  233. else:
  234. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  235. if (campaign_name is None) or (group_name is None):
  236. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  237. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  238. self.flush()
  239. else:
  240. request_data = get_request_data(account_id=account_id,
  241. campaign_id=campaign_id,
  242. video_info=video_info,
  243. campaign_name=campaign_name,
  244. group_name=group_name,
  245. ai_strategy_remark="用高质量素材补满创意",
  246. name_replace="高质量素材")
  247. request = ai_strategy_request_parse(request_data)
  248. logger.info("ai策略的返回信息:%s" % request)
  249. self.write(json.dumps(request))
  250. self.flush()
  251. except Exception:
  252. logger.error(traceback.format_exc())
  253. self.write(json.dumps(traceback.format_exc()))
  254. self.flush()
  255. class AiProgramCreativeHighQualityMaterial(tornado.web.RequestHandler):
  256. """
  257. 使用历史跑量素材创建程序化创意
  258. 跑量素材150个(近14天的素材)
  259. 计划名称--跑量素材程序化
  260. timeTask: 每天上午10点执行 --(0 0 10 * * ?)
  261. ----------------------------------------
  262. 拼装参数和写入数据库时,需要修改名称(程序化与非程序化不一致):
  263. creative_name --> package_name(程序化创意名称)
  264. action_bar_text --> action_bar(行动号召按钮)
  265. description --> captions(作品广告语)
  266. click_track_url --> click_url
  267. 写入数据库时:
  268. image_md5s --> cover_image_tokens
  269. 程序化与非程序化的逻辑不一样:
  270. 需要5个视频4张封面一组,拼装程序化创意参数需要单独抽取一个方法出来
  271. 所有操作记录表中,添加status信息(已提交,成功,失败),和回调时的 message信息
  272. """
  273. def post(self):
  274. data = self.request.body
  275. data = str(data, 'utf8')
  276. data = json.loads(data, encoding='utf8')
  277. logger.info("*************************************** NEW REQUEST ***************************************")
  278. logger.info("raw data from request is %s" % data)
  279. account_id = data.get('account_id')
  280. campaign_id = data.get('campaign_id')
  281. try:
  282. video_info = get_top_video_info(account_id, programme_high_quality_video_cnt,
  283. (datetime.datetime.now() + datetime.timedelta(
  284. days=-high_quality_video_days)).strftime("%Y-%m-%d"),
  285. str(datetime.date.today()), get_high_quality_video_url)
  286. if video_info['code'] != 0:
  287. logger.info("the res of video is %s" % video_info['message'])
  288. logger.info("没有获取到视频,不发送ai策略请求!")
  289. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  290. self.flush()
  291. else:
  292. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  293. if (campaign_name is None) or (group_name is None):
  294. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  295. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  296. self.flush()
  297. else:
  298. request_data = get_request_data(account_id=account_id,
  299. campaign_id=campaign_id,
  300. video_info=video_info,
  301. campaign_name=campaign_name,
  302. group_name=group_name,
  303. ai_strategy_remark="程序化高质量素材",
  304. name_replace="程序化高质量素材",
  305. unit_type=7)
  306. request = ai_strategy_request_parse(request_data)
  307. logger.info("ai策略的返回信息:%s" % request)
  308. self.write(json.dumps(request))
  309. self.flush()
  310. except Exception:
  311. logger.error(traceback.format_exc())
  312. self.write(json.dumps(traceback.format_exc()))
  313. self.flush()
  314. class AiProgramCreativeNewMaterial(tornado.web.RequestHandler):
  315. """
  316. 使用近期创意数关联小于10(当天创意关联数小于10的,150个素材)的素材创建程序化创意
  317. 计划名称--新素材程序化
  318. timeTask: 每天上午10点执行 --(0 0 10 * * ?)
  319. """
  320. def post(self):
  321. data = self.request.body
  322. data = str(data, 'utf8')
  323. data = json.loads(data, encoding='utf8')
  324. logger.info("*************************************** NEW REQUEST ***************************************")
  325. logger.info("raw data from request is %s" % data)
  326. account_id = data.get('account_id')
  327. campaign_id = data.get('campaign_id')
  328. try:
  329. video_info = get_history_video_info(account_id=account_id,
  330. url=get_history_video_url,
  331. start_time=(datetime.datetime.now() + datetime.timedelta(days=-2)).
  332. strftime("%Y-%m-%d"),
  333. end_time=str(datetime.date.today()),
  334. video_cnt=programme_new_video_cnt,
  335. related_creative_max_cnt=related_creative_max_cnt)
  336. if video_info['code'] != 0:
  337. logger.info("the res of video is %s" % video_info['message'])
  338. logger.info("没有获取到视频,不发送ai策略请求!")
  339. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  340. self.flush()
  341. else:
  342. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  343. if (campaign_name is None) or (group_name is None):
  344. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  345. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  346. self.flush()
  347. else:
  348. request_data = get_request_data(account_id=account_id,
  349. campaign_id=campaign_id,
  350. video_info=video_info,
  351. campaign_name=campaign_name,
  352. group_name=group_name,
  353. ai_strategy_remark="程序化上新素材",
  354. name_replace="程序化上新素材",
  355. unit_type=7)
  356. request = ai_strategy_request_parse(request_data)
  357. logger.info("ai策略的返回信息:%s" % request)
  358. self.write(json.dumps(request))
  359. self.flush()
  360. except Exception:
  361. logger.error(traceback.format_exc())
  362. self.write(json.dumps(traceback.format_exc()))
  363. self.flush()
  364. def get_request_data(account_id, campaign_id, video_info,
  365. campaign_name, group_name, ai_strategy_remark,
  366. name_replace, unit_type=4):
  367. res = {
  368. "video": video_info,
  369. "operation_type": 1,
  370. "account_id": account_id,
  371. "ai_strategy_remark": ai_strategy_remark,
  372. "campaign_info":
  373. {"campaign_id": campaign_id,
  374. "campaign_name": campaign_name.replace('自定义', name_replace) + str(datetime.datetime.now())},
  375. 'group_info':
  376. {'group_name': group_name.replace('自定义', name_replace) + str(datetime.datetime.now()),
  377. 'begin_time': str(datetime.date.today()),
  378. 'unit_type': unit_type},
  379. 'creative_info': {'is_sticky': 0}
  380. }
  381. return res