ai_time_task_creative_handler.py 52 KB

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