ai_time_task_creative_handler.py 23 KB

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