ai_time_task_creative_handler.py 52 KB

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