ai_time_task_creative_handler.py 51 KB

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