ai_time_task_creative_handler.py 53 KB

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