ai_time_task_creative_handler.py 54 KB

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