ai_time_task_creative_handler.py 51 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802
  1. import pandas as pd
  2. from utils.UrlConfig import *
  3. import json
  4. import logging
  5. import traceback
  6. import random
  7. import tornado.web
  8. from concurrent_log import ConcurrentTimedRotatingFileHandler
  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,get_app_detail, get_request_data
  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个素材)
  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. sql = """
  37. select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  38. where account_id=%s order by create_time desc limit 1
  39. """ % account_id
  40. df = pd.read_sql(sql, engine)
  41. if df['general_track'].values[0] == 0:
  42. app_id_array = eval(df['app_id_array'].values[0])
  43. random.shuffle(app_id_array)
  44. app_id_cnt = 0
  45. for app_id in app_id_array:
  46. campaign_id = None
  47. # 1、获取该 app_id 的应该详情
  48. app_id_info = get_app_detail(account_id=account_id,
  49. url=get_app_detail_url,
  50. app_id=app_id)
  51. if app_id_info['code'] != 0:
  52. continue
  53. # 2、依据 appVersion 的值,来判断当天是否已经有对应的计划,如果有则获取 campaign_id,没有则设置 campaign_name
  54. app_version = app_id_info['data'].get('appVersion', 'error')
  55. track_url = app_id_info['data'].get('trackUrl', 'error')
  56. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  57. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  58. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign
  59. where account_id = %s
  60. and put_create_time >= '%s'
  61. and put_create_time <= '%s'
  62. """ % (account_id, start_time, end_time)
  63. campaign_df = pd.read_sql(sql, engine)
  64. for row in campaign_df.itertuples():
  65. if ('遗漏素材' in getattr(row, 'campaign_name')) and (app_version in getattr(row, 'campaign_name')):
  66. campaign_id = getattr(row, 'campaign_id')
  67. break
  68. # 3、获取通用视频1个
  69. general_video_info = get_missing_video_info(account_id=account_id,
  70. cnt=1,
  71. url=get_missing_video_url,
  72. start_time=missing_video_start_time,
  73. end_time=missing_video_end_time,
  74. app_version='all')
  75. # 4、获取app_id对应的视频1个
  76. special_video_info = get_missing_video_info(account_id=account_id,
  77. cnt=1,
  78. url=get_missing_video_url,
  79. start_time=missing_video_start_time,
  80. end_time=missing_video_end_time,
  81. app_version=app_version)
  82. # 5、video_info 合并
  83. full_video_info = []
  84. if general_video_info['code'] == 0:
  85. full_video_info.extend(general_video_info['data'])
  86. if special_video_info['code'] == 0:
  87. full_video_info.extend(special_video_info['data'])
  88. # 6、拼装request_data,调用ai_strategy_request_parse方法
  89. # 广告计划名称:渠道包 - 游戏名称 - 版位 - 日期
  90. # 广告组名称:渠道包 - 游戏名称 - 优选 - 不限(定向)-自定义 / 程序化 - 日期
  91. if full_video_info:
  92. request_data = get_request_data(account_id=account_id,
  93. campaign_id=campaign_id,
  94. video_info=full_video_info,
  95. campaign_name=app_version + '-优选广告位-' + '遗漏素材',
  96. group_name=app_version + '-优选广告位-不限-自定义-' + '遗漏素材',
  97. ai_strategy_remark="补充遗漏素材",
  98. unit_type=4,
  99. app_id=app_id,
  100. click_track_url=track_url)
  101. request = ai_strategy_request_parse(request_data)
  102. logger.info("account_id=%s, app_id=%s, 补充遗漏素材,ai策略的返回信息:%s" % (account_id, app_id, request))
  103. app_id_cnt += 1
  104. if app_id_cnt >= 100:
  105. break
  106. self.write(json.dumps({"message": "account_id=%s, 多应用的补充遗漏素材请求已走完!" % account_id}))
  107. self.flush()
  108. else:
  109. video_info = get_missing_video_info(account_id,
  110. missing_video_cnt,
  111. get_missing_video_url,
  112. missing_video_start_time,
  113. missing_video_end_time)
  114. if video_info['code'] != 0:
  115. logger.info("account_id = %s,补充历史遗漏素材,没有获取到视频,不发送ai策略请求!" % account_id)
  116. self.write(json.dumps({"account_id": account_id,
  117. "message": "补充历史遗漏素材,没有获取到视频,不发送ai策略请求!"}))
  118. self.flush()
  119. else:
  120. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  121. if (campaign_name is None) or (group_name is None):
  122. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  123. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  124. self.flush()
  125. else:
  126. request_data = get_request_data(account_id=account_id,
  127. campaign_id=campaign_id,
  128. video_info=video_info['data'],
  129. campaign_name=campaign_name,
  130. group_name=group_name,
  131. ai_strategy_remark="补充遗漏素材",
  132. name_replace="补充遗漏素材")
  133. request = ai_strategy_request_parse(request_data)
  134. logger.info("account_id = %s, 补充遗漏素材,ai策略的返回信息:%s" % (account_id, request))
  135. self.write(json.dumps(request))
  136. self.flush()
  137. except Exception:
  138. logger.error(traceback.format_exc())
  139. self.write(json.dumps(traceback.format_exc()))
  140. self.flush()
  141. class AiAutoCreative(tornado.web.RequestHandler):
  142. """
  143. 每5分钟,检查一次是否有上新素材,有的话,就自动创建 -- 需要上定时任务测试
  144. """
  145. def post(self):
  146. data = self.request.body
  147. data = str(data, 'utf8')
  148. data = json.loads(data, encoding='utf8')
  149. logger.info("*************************************** NEW REQUEST ***************************************")
  150. account_id = data.get('account_id')
  151. campaign_id = data.get('campaign_id')
  152. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  153. (account_id, os.getpid(), os.getppid(), data))
  154. try:
  155. sql = """
  156. select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  157. where account_id=%s order by create_time desc limit 1
  158. """ % account_id
  159. df = pd.read_sql(sql, engine)
  160. if df['general_track'].values[0] == 0:
  161. app_id_array = eval(df['app_id_array'].values[0])
  162. random.shuffle(app_id_array)
  163. for app_id in app_id_array:
  164. campaign_id = None
  165. # 1、获取该 app_id 的应该详情
  166. app_id_info = get_app_detail(account_id=account_id,
  167. url=get_app_detail_url,
  168. app_id=app_id)
  169. if app_id_info['code'] != 0:
  170. continue
  171. # 2、依据 appVersion 的值,来判断当天是否已经有对应的计划,如果有则获取 campaign_id,没有则设置 campaign_name
  172. app_version = app_id_info['data'].get('appVersion', 'error')
  173. track_url = app_id_info['data'].get('trackUrl', 'error')
  174. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  175. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  176. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign
  177. where account_id = %s
  178. and put_create_time >= '%s'
  179. and put_create_time <= '%s'
  180. """ % (account_id, start_time, end_time)
  181. campaign_df = pd.read_sql(sql, engine)
  182. for row in campaign_df.itertuples():
  183. if ('素材自动上新' in getattr(row, 'campaign_name')) and (app_version in getattr(row, 'campaign_name')):
  184. campaign_id = getattr(row, 'campaign_id')
  185. break
  186. # 3、获取通用视频
  187. general_video_info = get_new_video_info(account_id=account_id,
  188. url=get_new_video_url,
  189. app_version='all')
  190. # 4、获取app_id对应的特定视频
  191. special_video_info = get_new_video_info(account_id=account_id,
  192. url=get_new_video_url,
  193. app_version=app_version)
  194. # 5、video_info 合并
  195. full_video_info = []
  196. if general_video_info['code'] == 0:
  197. full_video_info.extend(general_video_info['data'])
  198. if special_video_info['code'] == 0:
  199. full_video_info.extend(special_video_info['data'])
  200. # 6、拼装request_data,调用ai_strategy_request_parse方法
  201. # 广告计划名称:渠道包 - 游戏名称 - 版位 - 日期
  202. # 广告组名称:渠道包 - 游戏名称 - 优选 - 不限(定向)-自定义 / 程序化 - 日期
  203. if full_video_info:
  204. request_data = get_request_data(account_id=account_id,
  205. campaign_id=campaign_id,
  206. video_info=full_video_info,
  207. campaign_name=app_version + '-优选广告位-' + '素材自动上新',
  208. group_name=app_version + '-优选广告位-不限-自定义-' + '素材自动上新',
  209. ai_strategy_remark="素材自动上新",
  210. unit_type=4,
  211. app_id=app_id,
  212. click_track_url=track_url)
  213. request = ai_strategy_request_parse(request_data)
  214. logger.info("account_id=%s, app_id=%s, 素材自动上新,ai策略的返回信息:%s" % (account_id, app_id, request))
  215. else:
  216. logger.info("account_id=%s, app_id=%s, 素材自动上新, 没有获取到视频,不发送ai策略请求!" % (account_id, app_id))
  217. self.write(json.dumps({"message": "account_id=%s, 多应用的素材自动上新请求已走完!" % account_id}))
  218. self.flush()
  219. else:
  220. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  221. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  222. # 1-1 获取当天计划中包含“自动上新”计划的,计划id
  223. sql = """
  224. select campaign_id, campaign_name from ctop_kuaishou_campaign
  225. where account_id = %s
  226. and put_create_time >= '%s'
  227. and put_create_time <= '%s'
  228. """ % (account_id, start_time, end_time)
  229. df = pd.read_sql(sql, engine)
  230. for row in df.itertuples():
  231. if '素材自动上新' in getattr(row, 'campaign_name'):
  232. campaign_id = getattr(row, 'campaign_id')
  233. break
  234. video_info = get_new_video_info(account_id, get_new_video_url)
  235. if video_info['code'] != 0:
  236. self.write(json.dumps({"account_id": account_id, "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("account_id = %s, 素材自动上新 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"
  242. % account_id)
  243. self.write(json.dumps({"account_id": account_id,
  244. "message": "素材自动上新 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  245. self.flush()
  246. else:
  247. request_data = get_request_data(account_id=account_id,
  248. campaign_id=campaign_id,
  249. video_info=video_info['data'],
  250. campaign_name=campaign_name,
  251. group_name=group_name,
  252. ai_strategy_remark="自动上新",
  253. name_replace="素材自动上新")
  254. request = ai_strategy_request_parse(request_data)
  255. logger.info("account_id = %s, 素材自动上新 ai策略的返回信息:%s" % (account_id, request))
  256. self.write(json.dumps(request))
  257. self.flush()
  258. except Exception:
  259. logger.error(traceback.format_exc())
  260. self.write(json.dumps(traceback.format_exc()))
  261. self.flush()
  262. class AiHighQualityMaterial(tornado.web.RequestHandler):
  263. """
  264. 补充历史高质量素材 -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  265. """
  266. def post(self):
  267. data = self.request.body
  268. data = str(data, 'utf8')
  269. data = json.loads(data, encoding='utf8')
  270. logger.info("*************************************** NEW REQUEST ***************************************")
  271. account_id = data.get('account_id')
  272. campaign_id = data.get('campaign_id')
  273. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  274. (account_id, os.getpid(), os.getppid(), data))
  275. try:
  276. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  277. where account_id=%s order by create_time desc limit 1
  278. """ % account_id
  279. df = pd.read_sql(sql, engine)
  280. if df['general_track'].values[0] == 0:
  281. app_id_array = eval(df['app_id_array'].values[0])
  282. random.shuffle(app_id_array)
  283. app_id_cnt = 0
  284. for app_id in app_id_array:
  285. campaign_id = None
  286. # 1、获取该 app_id 的应该详情
  287. app_id_info = get_app_detail(account_id=account_id,
  288. url=get_app_detail_url,
  289. app_id=app_id)
  290. if app_id_info['code'] != 0:
  291. continue
  292. # 2、依据 appVersion 的值,来判断当天是否已经有对应的计划,如果有则获取 campaign_id,没有则设置 campaign_name
  293. app_version = app_id_info['data'].get('appVersion', 'error')
  294. track_url = app_id_info['data'].get('trackUrl', 'error')
  295. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  296. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  297. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign
  298. where account_id = %s
  299. and put_create_time >= '%s'
  300. and put_create_time <= '%s'
  301. """ % (account_id, start_time, end_time)
  302. campaign_df = pd.read_sql(sql, engine)
  303. for row in campaign_df.itertuples():
  304. if ('高质量素材' in getattr(row, 'campaign_name')) and (app_version in getattr(row, 'campaign_name')):
  305. campaign_id = getattr(row, 'campaign_id')
  306. break
  307. # 3、获取1个通用视频
  308. general_video_info = get_top_video_info(account_id=account_id,
  309. url=get_high_quality_video_url,
  310. start_time=(datetime.datetime.now() +
  311. datetime.timedelta(days=-high_quality_video_days)).
  312. strftime("%Y-%m-%d %H:%M:%S"),
  313. end_time=datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  314. cnt=1,
  315. app_version='all')
  316. # 4、获取app_id对应的1个视频
  317. special_video_info = get_top_video_info(account_id=account_id,
  318. url=get_high_quality_video_url,
  319. start_time=(datetime.datetime.now() +
  320. datetime.timedelta(days=-high_quality_video_days)).
  321. strftime("%Y-%m-%d %H:%M:%S"),
  322. end_time=datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  323. cnt=1,
  324. app_version=app_version)
  325. # 5、video_info 合并
  326. full_video_info = []
  327. if general_video_info['code'] == 0:
  328. full_video_info.extend(general_video_info['data'])
  329. if special_video_info['code'] == 0:
  330. full_video_info.extend(special_video_info['data'])
  331. # 6、拼装request_data,调用ai_strategy_request_parse方法
  332. # 广告计划名称:渠道包 - 游戏名称 - 版位 - 日期
  333. # 广告组名称:渠道包 - 游戏名称 - 优选 - 不限(定向)-自定义 / 程序化 - 日期
  334. if full_video_info:
  335. request_data = get_request_data(account_id=account_id,
  336. campaign_id=campaign_id,
  337. video_info=full_video_info,
  338. campaign_name=app_version + '-优选广告位-' + '高质量素材',
  339. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  340. ai_strategy_remark="高质量素材复建",
  341. unit_type=4,
  342. app_id=app_id,
  343. click_track_url=track_url)
  344. request = ai_strategy_request_parse(request_data)
  345. logger.info("account_id=%s, app_id=%s, 高质量素材复建,ai策略的返回信息:%s" % (account_id, app_id, request))
  346. app_id_cnt += 1
  347. if app_id_cnt >= 100:
  348. break
  349. self.write(json.dumps({"message": "account_id=%s, 多应用的高质量素材复建请求已走完!" % account_id}))
  350. self.flush()
  351. else:
  352. video_info = get_top_video_info(account_id, high_quality_video_cnt,
  353. (datetime.datetime.now() + datetime.timedelta(
  354. days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  355. datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  356. get_high_quality_video_url)
  357. if video_info['code'] != 0:
  358. logger.info("account_id = %s, 高质量素材复建,没有获取到视频,不发送ai策略请求!" % account_id)
  359. self.write(json.dumps({"account_id": account_id,
  360. "message": "高质量素材复建,没有获取到视频,不发送ai策略请求!"}))
  361. self.flush()
  362. else:
  363. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  364. if (campaign_name is None) or (group_name is None):
  365. logger.error("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  366. self.write(json.dumps({"account_id": account_id,
  367. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  368. self.flush()
  369. else:
  370. request_data = get_request_data(account_id=account_id,
  371. campaign_id=campaign_id,
  372. video_info=video_info['data'],
  373. campaign_name=campaign_name,
  374. group_name=group_name,
  375. ai_strategy_remark="高质量素材复建",
  376. name_replace="高质量素材")
  377. request = ai_strategy_request_parse(request_data)
  378. logger.info("account_id = %s, 高质量素材复建,ai策略的返回信息:%s" % (account_id, request))
  379. self.write(json.dumps(request))
  380. self.flush()
  381. except Exception:
  382. logger.error(traceback.format_exc())
  383. self.write(json.dumps(traceback.format_exc()))
  384. self.flush()
  385. class AiCheckAndUpTOFullCreative(tornado.web.RequestHandler):
  386. """
  387. 每天22:00 调用该任务,检查当天创意是否创建达到上限,否则使用历史高质量素材填满
  388. """
  389. def post(self):
  390. data = self.request.body
  391. data = str(data, 'utf8')
  392. data = json.loads(data, encoding='utf8')
  393. logger.info("*************************************** NEW REQUEST ***************************************")
  394. account_id = data.get('account_id')
  395. campaign_id = data.get('campaign_id')
  396. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  397. (account_id, os.getpid(), os.getppid(), data))
  398. try:
  399. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  400. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  401. now = datetime.datetime.now()
  402. # 1-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数
  403. sql = """
  404. select image_cnt from ctop_ai_kuaishou_advertiser_strategy
  405. where account_id = %s
  406. limit 1
  407. """ % account_id
  408. df = pd.read_sql(sql, engine)
  409. image_cnt = int(df['image_cnt'].values[0])
  410. # 2-1 从 ctop_kuaishou_creative 获取当天该账户到此刻 已经创建的创意个数
  411. video_cnt = 0
  412. sql = """
  413. select count(1) creative_cnt from ctop_kuaishou_creative
  414. where account_id = %s
  415. and creative_id is not NULL
  416. and creative_create_time <= '%s'
  417. and creative_create_time >= '%s'
  418. """ % (account_id, end_time, start_time)
  419. df = pd.read_sql(sql, engine)
  420. creative_cnt = int(df['creative_cnt'].values[0])
  421. if creative_cnt >= 2000:
  422. video_cnt = 0
  423. else:
  424. video_cnt = (2000 - creative_cnt) // image_cnt
  425. # 3-1 获取视频信息
  426. if video_cnt == 0:
  427. logger.info("创意已经建满,不需要补充素材!")
  428. self.write(json.dumps("message: 创意已经建满,不需要补充素材!"))
  429. self.flush()
  430. else:
  431. logger.info("需要补充的视频数量:%s" % video_cnt)
  432. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  433. where account_id=%s order by create_time desc limit 1
  434. """ % account_id
  435. df = pd.read_sql(sql, engine)
  436. if df['general_track'].values[0] == 0:
  437. needed_app_id_cnt = video_cnt
  438. app_id_array = eval(df['app_id_array'].values[0])
  439. random.shuffle(app_id_array)
  440. app_id_cnt = 0
  441. for app_id in app_id_array:
  442. campaign_id = None
  443. # 1、获取该 app_id 的应该详情
  444. app_id_info = get_app_detail(account_id=account_id,
  445. url=get_app_detail_url,
  446. app_id=app_id)
  447. if app_id_info['code'] != 0:
  448. continue
  449. # 2、依据 appVersion 的值,来判断当天是否已经有对应的计划,如果有则获取 campaign_id, 没有则设置 campaign_name
  450. app_version = app_id_info['data'].get('appVersion', 'error')
  451. track_url = app_id_info['data'].get('trackUrl', 'error')
  452. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  453. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  454. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign
  455. where account_id = %s
  456. and put_create_time >= '%s'
  457. and put_create_time <= '%s'
  458. """ % (account_id, start_time, end_time)
  459. campaign_df = pd.read_sql(sql, engine)
  460. for row in campaign_df.itertuples():
  461. if ('高质量素材' in getattr(row, 'campaign_name')) and (
  462. app_version in getattr(row, 'campaign_name')):
  463. campaign_id = getattr(row, 'campaign_id')
  464. break
  465. # 3、获取app_id对应的1个视频
  466. special_video_info = get_top_video_info(account_id=account_id,
  467. url=get_high_quality_video_url,
  468. start_time=(now + datetime.timedelta(days=-2*high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  469. end_time=(now + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  470. cnt=1,
  471. app_version=app_version)
  472. if special_video_info['code'] != 0:
  473. continue
  474. # 6、拼装request_data,调用ai_strategy_request_parse方法
  475. # 广告计划名称:渠道包 - 游戏名称 - 版位 - 日期
  476. # 广告组名称:渠道包 - 游戏名称 - 优选 - 不限(定向)-自定义 / 程序化 - 日期
  477. request_data = get_request_data(account_id=account_id,
  478. campaign_id=campaign_id,
  479. video_info=special_video_info['data'],
  480. campaign_name=app_version + '-优选广告位-' + '高质量素材',
  481. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  482. ai_strategy_remark="用高质量素材补满创意",
  483. unit_type=4,
  484. app_id=app_id,
  485. click_track_url=track_url)
  486. request = ai_strategy_request_parse(request_data)
  487. logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意,ai策略的返回信息:%s" % (account_id, app_id, request))
  488. app_id_cnt += 1
  489. if app_id_cnt >= needed_app_id_cnt:
  490. break
  491. self.write(json.dumps({"message": "account_id=%s, 多应用的高质量素材补满创意的请求已走完!" % account_id}))
  492. self.flush()
  493. else:
  494. # 1-1 获取当天计划中包含“高质量素材”计划的,计划id
  495. sql = """
  496. select campaign_id, campaign_name from ctop_kuaishou_campaign
  497. where account_id = %s
  498. and put_create_time >= '%s'
  499. and put_create_time <= '%s'
  500. """ % (account_id, start_time, end_time)
  501. df = pd.read_sql(sql, engine)
  502. for row in df.itertuples():
  503. if '高质量素材' in getattr(row, 'campaign_name'):
  504. campaign_id = getattr(row, 'campaign_id')
  505. break
  506. video_info = get_top_video_info(account_id,
  507. video_cnt,
  508. (now + datetime.timedelta(days=-2*high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  509. (now + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  510. get_high_quality_video_url)
  511. if video_info['code'] != 0:
  512. logger.info("account_id = %s, 历史高质量素材填满, 没有获取到视频,不发送ai策略请求!" % account_id)
  513. self.write(json.dumps({"account_id": account_id,
  514. "message": "历史高质量素材填满, 没有获取到视频,不发送ai策略请求!"}))
  515. self.flush()
  516. else:
  517. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  518. if (campaign_name is None) or (group_name is None):
  519. logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  520. self.write(json.dumps({"account_id": account_id,
  521. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  522. self.flush()
  523. else:
  524. request_data = get_request_data(account_id=account_id,
  525. campaign_id=campaign_id,
  526. video_info=video_info['data'],
  527. campaign_name=campaign_name,
  528. group_name=group_name,
  529. ai_strategy_remark="用高质量素材补满创意",
  530. name_replace="高质量素材")
  531. request = ai_strategy_request_parse(request_data)
  532. logger.info("account_id = %s, 用高质量素材补满创意,ai策略的返回信息:%s" % (account_id, request))
  533. self.write(json.dumps(request))
  534. self.flush()
  535. except Exception:
  536. logger.error(traceback.format_exc())
  537. self.write(json.dumps(traceback.format_exc()))
  538. self.flush()
  539. class AiProgramCreativeHighQualityMaterial(tornado.web.RequestHandler):
  540. """
  541. 使用历史跑量素材创建程序化创意
  542. 跑量素材150个(近14天的素材)
  543. 计划名称--跑量素材程序化
  544. timeTask: 每天上午10点执行 --(0 0 10 * * ?)
  545. ----------------------------------------
  546. 拼装参数和写入数据库时,需要修改名称(程序化与非程序化不一致):
  547. creative_name --> package_name(程序化创意名称)
  548. action_bar_text --> action_bar(行动号召按钮)
  549. description --> captions(作品广告语)
  550. click_track_url --> click_url
  551. 写入数据库时:
  552. image_md5s --> cover_image_tokens
  553. 程序化与非程序化的逻辑不一样:
  554. 需要5个视频4张封面一组,拼装程序化创意参数需要单独抽取一个方法出来
  555. 所有操作记录表中,添加status信息(已提交,成功,失败),和回调时的 message信息
  556. """
  557. def post(self):
  558. data = self.request.body
  559. data = str(data, 'utf8')
  560. data = json.loads(data, encoding='utf8')
  561. logger.info("*************************************** NEW REQUEST ***************************************")
  562. account_id = data.get('account_id')
  563. campaign_id = data.get('campaign_id')
  564. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  565. (account_id, os.getpid(), os.getppid(), data))
  566. try:
  567. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  568. where account_id=%s order by create_time desc limit 1
  569. """ % account_id
  570. df = pd.read_sql(sql, engine)
  571. if df['general_track'].values[0] == 0:
  572. app_id_array = eval(df['app_id_array'].values[0])
  573. random.shuffle(app_id_array)
  574. app_id_cnt = 0
  575. for app_id in app_id_array:
  576. # 1、获取该 app_id 的应该详情
  577. app_id_info = get_app_detail(account_id=account_id,
  578. url=get_app_detail_url,
  579. app_id=app_id)
  580. if app_id_info['code'] != 0:
  581. continue
  582. app_version = app_id_info['data'].get('appVersion', 'error')
  583. track_url = app_id_info['data'].get('trackUrl', 'error')
  584. # 2、获取特定视频
  585. special_video_info = get_top_video_info(account_id=account_id,
  586. url=get_high_quality_video_url,
  587. start_time=(datetime.datetime.now() +
  588. datetime.timedelta(days=-high_quality_video_days)).
  589. strftime("%Y-%m-%d %H:%M:%S"),
  590. end_time=datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  591. cnt=5,
  592. app_version=app_version)
  593. if special_video_info['code'] != 0:
  594. continue
  595. # 3、拼装request_data,调用ai_strategy_request_parse方法
  596. # 广告计划名称:渠道包 - 游戏名称 - 版位 - 日期
  597. # 广告组名称:渠道包 - 游戏名称 - 优选 - 不限(定向)-自定义 / 程序化 - 日期
  598. request_data = get_request_data(account_id=account_id,
  599. campaign_id=campaign_id,
  600. video_info=special_video_info['data'],
  601. campaign_name=app_version + '-优选广告位-' + '程序化高质量素材',
  602. group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',
  603. ai_strategy_remark="程序化高质量素材",
  604. unit_type=7,
  605. app_id=app_id,
  606. click_track_url=track_url)
  607. request = ai_strategy_request_parse(request_data)
  608. logger.info("account_id=%s, app_id=%s, 程序化高质量素材,ai策略的返回信息:%s" % (account_id, app_id, request))
  609. app_id_cnt += 1
  610. if app_id_cnt >= 30:
  611. break
  612. self.write(json.dumps({"message": "account_id=%s, 多应用的高质量素材程序化创意的请求已走完!" % account_id}))
  613. self.flush()
  614. else:
  615. video_info = get_top_video_info(account_id, programme_high_quality_video_cnt,
  616. (datetime.datetime.now() + datetime.timedelta(
  617. days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  618. datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  619. get_high_quality_video_url)
  620. if video_info['code'] != 0:
  621. logger.info("account_id = %s, 程序化高质量素材,没有获取到视频,不发送ai策略请求!" % account_id)
  622. self.write(json.dumps({"account_id": account_id,
  623. "message": "没有获取到视频,不发送ai策略请求!"}))
  624. self.flush()
  625. else:
  626. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  627. if (campaign_name is None) or (group_name is None):
  628. logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  629. self.write(json.dumps({"account_id": account_id,
  630. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  631. self.flush()
  632. else:
  633. request_data = get_request_data(account_id=account_id,
  634. campaign_id=campaign_id,
  635. video_info=video_info['data'],
  636. campaign_name=campaign_name,
  637. group_name=group_name,
  638. ai_strategy_remark="程序化高质量素材",
  639. name_replace="程序化高质量素材",
  640. unit_type=7)
  641. request = ai_strategy_request_parse(request_data)
  642. logger.info("account_id = %s, 程序化高质量素材, ai策略的返回信息:%s" % (account_id, request))
  643. self.write(json.dumps(request))
  644. self.flush()
  645. except Exception:
  646. logger.error(traceback.format_exc())
  647. self.write(json.dumps(traceback.format_exc()))
  648. self.flush()
  649. class AiProgramCreativeNewMaterial(tornado.web.RequestHandler):
  650. """
  651. 使用N天内 创意数关联小于M个(如当天创意关联数小于10的,150个素材)的素材创建程序化创意
  652. 计划名称--新素材程序化
  653. timeTask: 每天上午10点执行 --(0 0 10 * * ?)
  654. """
  655. def post(self):
  656. data = self.request.body
  657. data = str(data, 'utf8')
  658. data = json.loads(data, encoding='utf8')
  659. logger.info("*************************************** NEW REQUEST ***************************************")
  660. account_id = data.get('account_id')
  661. campaign_id = data.get('campaign_id')
  662. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  663. (account_id, os.getpid(), os.getppid(), data))
  664. try:
  665. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  666. where account_id=%s order by create_time desc limit 1
  667. """ % account_id
  668. df = pd.read_sql(sql, engine)
  669. if df['general_track'].values[0] == 0:
  670. app_id_array = eval(df['app_id_array'].values[0])
  671. random.shuffle(app_id_array)
  672. app_id_cnt = 0
  673. for app_id in app_id_array:
  674. # 1、获取该 app_id 的应该详情
  675. app_id_info = get_app_detail(account_id=account_id,
  676. url=get_app_detail_url,
  677. app_id=app_id)
  678. if app_id_info['code'] != 0:
  679. continue
  680. app_version = app_id_info['data'].get('appVersion', 'error')
  681. track_url = app_id_info['data'].get('trackUrl', 'error')
  682. # 2、获取特定视频
  683. special_video_info = get_history_video_info(account_id=account_id,
  684. url=get_history_video_url,
  685. start_time=(datetime.datetime.now() + datetime.timedelta(days=-60)).
  686. strftime("%Y-%m-%d %H:%M:%S"),
  687. end_time=datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  688. video_cnt=5,
  689. related_creative_max_cnt=related_creative_max_cnt,
  690. app_version=app_version
  691. )
  692. if special_video_info['code'] != 0:
  693. continue
  694. # 3、拼装request_data,调用ai_strategy_request_parse方法
  695. # 广告计划名称:渠道包 - 游戏名称 - 版位 - 日期
  696. # 广告组名称:渠道包 - 游戏名称 - 优选 - 不限(定向)-自定义 / 程序化 - 日期
  697. request_data = get_request_data(account_id=account_id,
  698. campaign_id=campaign_id,
  699. video_info=special_video_info['data'],
  700. campaign_name=app_version + '-优选广告位-' + '程序化上新素材',
  701. group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',
  702. ai_strategy_remark="程序化上新素材",
  703. unit_type=7,
  704. app_id=app_id,
  705. click_track_url=track_url)
  706. request = ai_strategy_request_parse(request_data)
  707. logger.info("account_id=%s, app_id=%s, 程序化上新素材,ai策略的返回信息:%s" % (account_id, app_id, request))
  708. app_id_cnt += 1
  709. if app_id_cnt >= 30:
  710. break
  711. self.write(json.dumps({"message": "account_id=%s, 多应用的上新素材程序化创意的请求已走完!" % account_id}))
  712. self.flush()
  713. else:
  714. video_info = get_history_video_info(account_id=account_id,
  715. url=get_history_video_url,
  716. start_time=(datetime.datetime.now() + datetime.timedelta(days=-7)).
  717. strftime("%Y-%m-%d %H:%M:%S"),
  718. end_time=datetime.date.today().strftime("%Y-%m-%d %H:%M:%S"),
  719. video_cnt=programme_new_video_cnt,
  720. related_creative_max_cnt=related_creative_max_cnt)
  721. if video_info['code'] != 0:
  722. self.write(json.dumps({"account_id": account_id,
  723. "message": "程序化上新素材,没有获取到视频,不发送ai策略请求!"}))
  724. self.flush()
  725. else:
  726. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  727. if (campaign_name is None) or (group_name is None):
  728. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  729. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  730. self.flush()
  731. else:
  732. request_data = get_request_data(account_id=account_id,
  733. campaign_id=campaign_id,
  734. video_info=video_info['data'],
  735. campaign_name=campaign_name,
  736. group_name=group_name,
  737. ai_strategy_remark="程序化上新素材",
  738. name_replace="程序化上新素材",
  739. unit_type=7)
  740. request = ai_strategy_request_parse(request_data)
  741. logger.info("account_id = %s, 程序化上新素材,ai策略的返回信息:%s" % (account_id, request))
  742. self.write(json.dumps(request))
  743. self.flush()
  744. except Exception:
  745. logger.error(traceback.format_exc())
  746. self.write(json.dumps(traceback.format_exc()))
  747. self.flush()