ai_time_task_creative_handler.py 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270
  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_lower_case_name, 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/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 is None:
  40. logger.info("没有获取到视频,不发送ai策略请求!")
  41. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  42. self.flush()
  43. else:
  44. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  45. if (campaign_name is None) or (group_name is None):
  46. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  47. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  48. self.flush()
  49. else:
  50. request_data = get_request_data(account_id, campaign_id, video_info, campaign_name, group_name,
  51. "补充遗漏素材", "补充遗漏素材")
  52. request = ai_strategy_request_parse(request_data)
  53. logger.info("ai策略的返回信息:%s" % request)
  54. self.write(json.dumps(request))
  55. self.flush()
  56. except Exception:
  57. logger.error(traceback.format_exc())
  58. self.write(json.dumps(traceback.format_exc()))
  59. self.flush()
  60. class AiAutoCreative(tornado.web.RequestHandler):
  61. """
  62. 每5分钟,检查一次是否有上新素材,有的话,就自动创建 -- 需要上定时任务测试
  63. """
  64. def post(self):
  65. data = self.request.body
  66. data = str(data, 'utf8')
  67. data = json.loads(data, encoding='utf8')
  68. logger.info("*************************************** NEW REQUEST ***************************************")
  69. logger.info("raw data from request is %s" % data)
  70. account_id = data.get('account_id')
  71. campaign_id = data.get('campaign_id')
  72. try:
  73. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  74. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  75. # 1-1 获取当天计划中包含“自动上新”计划的,计划id
  76. sql = """
  77. select campaign_id, campaign_name from ctop_kuaishou_campaign
  78. where account_id = %s
  79. and put_create_time >= '%s'
  80. and put_create_time <= '%s'
  81. """ % (account_id, start_time, end_time)
  82. df = pd.read_sql(sql, engine)
  83. for row in df.itertuples():
  84. if '素材自动上新' in getattr(row, 'campaign_name'):
  85. campaign_id = getattr(row, 'campaign_id')
  86. break
  87. video_info = get_new_video_info(account_id, get_new_video_url)
  88. if video_info is None:
  89. logger.info("没有获取到视频,不发送ai策略请求!")
  90. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  91. self.flush()
  92. else:
  93. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  94. if (campaign_name is None) or (group_name is None):
  95. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  96. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  97. self.flush()
  98. else:
  99. request_data = get_request_data(account_id, campaign_id, video_info, campaign_name, group_name,
  100. "自动上新", "素材自动上新")
  101. request = ai_strategy_request_parse(request_data)
  102. logger.info("ai策略的返回信息:%s" % request)
  103. self.write(json.dumps(request))
  104. self.flush()
  105. except Exception:
  106. logger.error(traceback.format_exc())
  107. self.write(json.dumps(traceback.format_exc()))
  108. self.flush()
  109. class AiHighQualityMaterial(tornado.web.RequestHandler):
  110. """
  111. 补充历史高质量素材 -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  112. """
  113. def post(self):
  114. data = self.request.body
  115. data = str(data, 'utf8')
  116. data = json.loads(data, encoding='utf8')
  117. logger.info("*************************************** NEW REQUEST ***************************************")
  118. logger.info("raw data from request is %s" % data)
  119. account_id = data.get('account_id')
  120. campaign_id = data.get('campaign_id')
  121. try:
  122. video_info = get_top_video_info(account_id, high_quality_video_cnt,
  123. (datetime.datetime.now() + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d"),
  124. str(datetime.date.today()), get_high_quality_video_url)
  125. if video_info is None:
  126. logger.info("没有获取到视频,不发送ai策略请求!")
  127. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  128. self.flush()
  129. else:
  130. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  131. if (campaign_name is None) or (group_name is None):
  132. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  133. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  134. self.flush()
  135. else:
  136. request_data = get_request_data(account_id, campaign_id, video_info, campaign_name, group_name,
  137. "高质量素材", "高质量素材")
  138. request = ai_strategy_request_parse(request_data)
  139. logger.info("ai策略的返回信息:%s" % request)
  140. self.write(json.dumps(request))
  141. self.flush()
  142. except Exception:
  143. logger.error(traceback.format_exc())
  144. self.write(json.dumps(traceback.format_exc()))
  145. self.flush()
  146. class AiCheckAndUpTOFullCreative(tornado.web.RequestHandler):
  147. """
  148. 每天22:00 调用该任务,检查当天创意是否创建达到上限,否则使用历史高质量素材填满 -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  149. """
  150. def post(self):
  151. data = self.request.body
  152. data = str(data, 'utf8')
  153. data = json.loads(data, encoding='utf8')
  154. logger.info("*************************************** NEW REQUEST ***************************************")
  155. logger.info("raw data from request is %s" % data)
  156. account_id = data.get('account_id')
  157. campaign_id = data.get('campaign_id')
  158. try:
  159. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  160. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  161. # 1-1 获取当天计划中包含“高质量素材”计划的,计划id
  162. sql = """
  163. select campaign_id, campaign_name from ctop_kuaishou_campaign
  164. where account_id = %s
  165. and put_create_time >= '%s'
  166. and put_create_time <= '%s'
  167. """ % (account_id, start_time, end_time)
  168. df = pd.read_sql(sql, engine)
  169. for row in df.itertuples():
  170. if '高质量素材' in getattr(row, 'campaign_name'):
  171. campaign_id = getattr(row, 'campaign_id')
  172. break
  173. # 2-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数
  174. sql = """
  175. select image_cnt from ctop_ai_kuaishou_advertiser_strategy
  176. where account_id = %s
  177. and status = 1
  178. limit 1
  179. """ % account_id
  180. df = pd.read_sql(sql, engine)
  181. image_cnt = int(df['image_cnt'].values[0])
  182. # 3-1 从 ctop_kuaishou_creative 获取当天该账户到此刻 已经创建的创意个数
  183. video_cnt = 0
  184. sql = """
  185. select count(1) creative_cnt from ctop_kuaishou_creative
  186. where account_id = %s
  187. and creative_id is not NULL
  188. and creative_create_time <= '%s'
  189. and creative_create_time >= '%s'
  190. """ % (account_id, end_time, start_time)
  191. df = pd.read_sql(sql, engine)
  192. creative_cnt = int(df['creative_cnt'].values[0])
  193. if creative_cnt >= 2000:
  194. video_cnt = 0
  195. else:
  196. video_cnt = (2000 - creative_cnt) // image_cnt
  197. # 4-1 获取视频信息
  198. if video_cnt == 0:
  199. logger.info("创意已经建满,不需要补充素材!")
  200. self.write(json.dumps("message: 创意已经建满,不需要补充素材!"))
  201. self.flush()
  202. else:
  203. logger.info("需要补充的视频数量:%s" % video_cnt)
  204. now = datetime.datetime.now()
  205. video_info = get_top_video_info(account_id,
  206. video_cnt,
  207. (now + datetime.timedelta(days=-2*high_quality_video_days)).strftime("%Y-%m-%d"),
  208. (now + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d"),
  209. get_high_quality_video_url)
  210. if video_info is None:
  211. logger.info("没有获取到视频,不发送ai策略请求!")
  212. self.write(json.dumps({"message": "没有获取到视频,不发送ai策略请求!"}))
  213. self.flush()
  214. else:
  215. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  216. if (campaign_name is None) or (group_name is None):
  217. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  218. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  219. self.flush()
  220. else:
  221. request_data = get_request_data(account_id, campaign_id, video_info, campaign_name, group_name,
  222. "高质量素材", "高质量素材")
  223. request = ai_strategy_request_parse(request_data)
  224. logger.info("ai策略的返回信息:%s" % request)
  225. self.write(json.dumps(request))
  226. self.flush()
  227. except Exception:
  228. logger.error(traceback.format_exc())
  229. self.write(json.dumps(traceback.format_exc()))
  230. self.flush()
  231. def get_request_data(account_id, campaign_id, video_info, campaign_name, group_name,ai_strategy_remark, name_replace):
  232. res = {
  233. "video": video_info,
  234. "operation_type": 1,
  235. "account_id": account_id,
  236. "ai_strategy_remark": ai_strategy_remark,
  237. "campaign_info":
  238. {"campaign_id": campaign_id,
  239. "campaign_name": campaign_name.replace('自定义', name_replace) + str(datetime.datetime.now())},
  240. 'group_info':
  241. {'group_name': group_name.replace('自定义', name_replace) + str(datetime.datetime.now()),
  242. 'begin_time': str(datetime.date.today())},
  243. 'creative_info': {'is_sticky': 0}
  244. }
  245. return res