ai_time_task_creative_handler.py 54 KB

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