ai_time_task_creative_handler.py 86 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215
  1. import pandas as pd
  2. from utils.UrlConfig import *
  3. import json
  4. import logging
  5. import traceback
  6. import random
  7. import tornado.web
  8. from concurrent_log import ConcurrentTimedRotatingFileHandler
  9. from utils.ConstantConfig import *
  10. from utils.DataBaseConfig import *
  11. from utils.CommonFunction import get_history_video_info, get_new_video_info, get_missing_video_info, \
  12. get_top_video_info, get_campaign_and_group_name_rule, get_app_detail, get_request_data, \
  13. refresh_video, get_app_list
  14. from ai_strategy_request_func import ai_strategy_request_parse
  15. log_formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s', '%m/%d/%Y %I:%M:%S %p')
  16. log_handler = ConcurrentTimedRotatingFileHandler("logs/ai_auto_create.log", when="midnight", backupCount=100)
  17. log_handler.setFormatter(log_formatter)
  18. logger = logging.getLogger('ai_time_task_creative_logger')
  19. logger.addHandler(log_handler)
  20. logger.setLevel(logging.DEBUG)
  21. print('id of ai_time_task_creative_logger %s' % id(logger))
  22. logger.info("ai_time_task_creative_server started!")
  23. class AiHistoricalMissingMaterial(tornado.web.RequestHandler):
  24. """
  25. 补充历史遗漏素材(关联创意个数为0个素材)
  26. """
  27. def post(self):
  28. data = self.request.body
  29. data = str(data, 'utf8')
  30. data = json.loads(data, encoding='utf8')
  31. logger.info("*************************************** NEW REQUEST ***************************************")
  32. account_id = data.get('account_id')
  33. campaign_id = data.get('campaign_id')
  34. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  35. (account_id, os.getpid(), os.getppid(), data))
  36. try:
  37. sql = """
  38. select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  39. where account_id=%s order by create_time desc limit 1
  40. """ % account_id
  41. df = pd.read_sql(sql, engine)
  42. if df['general_track'].values[0] == 0:
  43. app_id_array = eval(df['app_id_array'].values[0])
  44. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  45. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  46. app_dict = {}
  47. for app_info in app_list['data']:
  48. if app_info['appId'] in app_id_array:
  49. app_dict[app_info['appVersion']] = app_info
  50. video_list = get_missing_video_info(account_id=account_id,
  51. cnt=missing_video_cnt,
  52. url=get_missing_video_url,
  53. start_time=missing_video_start_time,
  54. end_time=missing_video_end_time)
  55. # 返回视频中有通用视频和特定视频,需要分开处理
  56. # "photo_name": "all",
  57. # "photo_name": "ksjytdrca-糖豆人冲啊",
  58. if video_list['code'] == 0:
  59. video_df = pd.DataFrame(video_list['data'])
  60. # 1-非通用视频的创建
  61. special_video_df = video_df[video_df.photo_name != 'all']
  62. for photo_name in special_video_df['photo_name'].unique():
  63. if photo_name in app_dict.keys():
  64. campaign_id = None
  65. app_id = app_dict[photo_name].get('appId', 'error')
  66. app_version = app_dict[photo_name].get('appVersion', 'error')
  67. track_url = app_dict[photo_name].get('trackUrl', 'error')
  68. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  69. campaign_df = pd.read_sql(sql, engine)
  70. for row in campaign_df.itertuples():
  71. if (app_version + '--优选广告位-' + '遗漏素材') == getattr(row, 'campaign_name'):
  72. campaign_id = getattr(row, 'campaign_id')
  73. break
  74. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  75. app_id_video_list = []
  76. for k, v in app_id_video_df.T.to_dict().items():
  77. app_id_video_list.append(v)
  78. request_data = get_request_data(account_id=account_id,
  79. campaign_id=campaign_id,
  80. video_info=app_id_video_list,
  81. campaign_name=app_version + '--优选广告位-' + '遗漏素材',
  82. group_name=app_version + '-优选广告位-不限-自定义-' + '遗漏素材',
  83. ai_strategy_remark="补充遗漏素材",
  84. unit_type=4,
  85. app_id=app_id,
  86. click_track_url=track_url)
  87. request = ai_strategy_request_parse(request_data)
  88. logger.info("account_id=%s, app_id=%s, 补充遗漏素材-特定视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  89. # 2-通用视频的创建,random.shuffle 选择 app_id
  90. general_video_df = video_df[video_df.photo_name == 'all']
  91. if not general_video_df.empty:
  92. general_video_cnt = len(general_video_df)
  93. app_version_list = list(app_dict)
  94. random.shuffle(app_version_list)
  95. video_cnt = 0
  96. for app_version in app_version_list[:general_video_cnt]:
  97. campaign_id = None
  98. app_id = app_dict[app_version].get('appId', 'error')
  99. track_url = app_dict[app_version].get('trackUrl', 'error')
  100. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  101. campaign_df = pd.read_sql(sql, engine)
  102. for row in campaign_df.itertuples():
  103. if (app_version + '--优选广告位-' + '遗漏素材') == getattr(row, 'campaign_name'):
  104. campaign_id = getattr(row, 'campaign_id')
  105. break
  106. single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]
  107. request_data = get_request_data(account_id=account_id,
  108. campaign_id=campaign_id,
  109. video_info=single_video,
  110. campaign_name=app_version + '--优选广告位-' + '遗漏素材',
  111. group_name=app_version + '-优选广告位-不限-自定义-' + '遗漏素材',
  112. ai_strategy_remark="补充遗漏素材",
  113. unit_type=4,
  114. app_id=app_id,
  115. click_track_url=track_url)
  116. request = ai_strategy_request_parse(request_data)
  117. video_cnt += 1
  118. logger.info("account_id=%s, app_id=%s, 补充遗漏素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  119. else:
  120. logger.info("account_id=%s, 补充遗漏素材: 返回的视频中没有通用视频" % account_id)
  121. self.write(json.dumps({"message": "account_id=%s, 多应用的补充遗漏素材请求已走完!" % account_id}))
  122. self.flush()
  123. else:
  124. video_info = get_missing_video_info(account_id=account_id,
  125. cnt=missing_video_cnt,
  126. url=get_missing_video_url,
  127. start_time=missing_video_start_time,
  128. end_time=missing_video_end_time)
  129. if video_info['code'] != 0:
  130. logger.info("account_id = %s,补充历史遗漏素材,没有获取到视频,不发送ai策略请求!" % account_id)
  131. self.write(json.dumps({"account_id": account_id,
  132. "message": "补充历史遗漏素材,没有获取到视频,不发送ai策略请求!"}))
  133. self.flush()
  134. else:
  135. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  136. if (campaign_name is None) or (group_name is None):
  137. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  138. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  139. self.flush()
  140. else:
  141. request_data = get_request_data(account_id=account_id,
  142. campaign_id=campaign_id,
  143. video_info=video_info['data'],
  144. campaign_name=campaign_name,
  145. group_name=group_name,
  146. ai_strategy_remark="补充遗漏素材",
  147. name_replace="补充遗漏素材")
  148. request = ai_strategy_request_parse(request_data)
  149. logger.info("account_id = %s, 补充遗漏素材,ai策略的返回信息:%s" % (account_id, request))
  150. self.write(json.dumps(request))
  151. self.flush()
  152. except Exception:
  153. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  154. self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))
  155. self.flush()
  156. class AiAutoCreative(tornado.web.RequestHandler):
  157. """
  158. 每5分钟,检查一次是否有上新素材,有的话,就自动创建
  159. """
  160. def post(self):
  161. data = self.request.body
  162. data = str(data, 'utf8')
  163. data = json.loads(data, encoding='utf8')
  164. logger.info("*************************************** NEW REQUEST ***************************************")
  165. account_id = data.get('account_id')
  166. campaign_id = data.get('campaign_id')
  167. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  168. (account_id, os.getpid(), os.getppid(), data))
  169. try:
  170. # 刷新快手视频信息
  171. refresh_video(account_id=account_id, url=refresh_video_url)
  172. sql = """
  173. select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  174. where account_id=%s order by create_time desc limit 1
  175. """ % account_id
  176. df = pd.read_sql(sql, engine)
  177. if df['general_track'].values[0] == 0:
  178. app_id_array = eval(df['app_id_array'].values[0])
  179. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  180. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  181. app_dict = {}
  182. for app_info in app_list['data']:
  183. if app_info['appId'] in app_id_array:
  184. app_dict[app_info['appVersion']] = app_info
  185. video_list = get_new_video_info(account_id=account_id,
  186. url=get_new_video_url)
  187. # 返回的视频中有通用视频和特定视频,需要分开处理
  188. # "photo_name": "all",
  189. # "photo_name": "ksjytdrca-糖豆人冲啊",
  190. if video_list['code'] == 0:
  191. video_df = pd.DataFrame(video_list['data'])
  192. # 1-特定视频的创建
  193. special_video_df = video_df[video_df.photo_name != 'all']
  194. for photo_name in special_video_df['photo_name'].unique():
  195. if photo_name in app_dict.keys():
  196. campaign_id = None
  197. app_id = app_dict[photo_name].get('appId', 'error')
  198. app_version = app_dict[photo_name].get('appVersion', 'error')
  199. track_url = app_dict[photo_name].get('trackUrl', 'error')
  200. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  201. campaign_df = pd.read_sql(sql, engine)
  202. for row in campaign_df.itertuples():
  203. if (app_version + '--优选广告位-' + '素材自动上新') == getattr(row, 'campaign_name'):
  204. campaign_id = getattr(row, 'campaign_id')
  205. break
  206. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  207. app_id_video_list = []
  208. for k, v in app_id_video_df.T.to_dict().items():
  209. app_id_video_list.append(v)
  210. request_data = get_request_data(account_id=account_id,
  211. campaign_id=campaign_id,
  212. video_info=app_id_video_list,
  213. campaign_name=app_version + '--优选广告位-' + '素材自动上新',
  214. group_name=app_version + '-优选广告位-不限-自定义-' + '素材自动上新',
  215. ai_strategy_remark="素材自动上新",
  216. unit_type=4,
  217. app_id=app_id,
  218. click_track_url=track_url)
  219. request = ai_strategy_request_parse(request_data)
  220. logger.info("account_id=%s, app_id=%s, 素材自动上新-特定视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  221. # 2-通用视频的创建,random.shuffle 选择 app_id
  222. general_video_df = video_df[video_df.photo_name == 'all']
  223. if not general_video_df.empty:
  224. general_video_cnt = len(general_video_df)
  225. app_version_list = list(app_dict)
  226. random.shuffle(app_version_list)
  227. video_cnt = 0
  228. for app_version in app_version_list[:general_video_cnt]:
  229. campaign_id = None
  230. app_id = app_dict[app_version].get('appId', 'error')
  231. track_url = app_dict[app_version].get('trackUrl', 'error')
  232. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  233. campaign_df = pd.read_sql(sql, engine)
  234. for row in campaign_df.itertuples():
  235. if (app_version + '--优选广告位-' + '素材自动上新') == getattr(row, 'campaign_name'):
  236. campaign_id = getattr(row, 'campaign_id')
  237. break
  238. single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]
  239. request_data = get_request_data(account_id=account_id,
  240. campaign_id=campaign_id,
  241. video_info=single_video,
  242. campaign_name=app_version + '--优选广告位-' + '素材自动上新',
  243. group_name=app_version + '-优选广告位-不限-自定义-' + '素材自动上新',
  244. ai_strategy_remark="素材自动上新",
  245. unit_type=4,
  246. app_id=app_id,
  247. click_track_url=track_url)
  248. request = ai_strategy_request_parse(request_data)
  249. video_cnt += 1
  250. logger.info("account_id=%s, app_id=%s, 素材自动上新-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  251. else:
  252. logger.info("account_id=%s, 素材自动上新: 返回的视频中没有通用视频" % account_id)
  253. else:
  254. logger.info("account_id=%s, 素材自动上新: 没有获取到视频" % account_id)
  255. self.write(json.dumps({"message": "account_id=%s, 多应用的素材自动上新已走完!" % account_id}))
  256. self.flush()
  257. else:
  258. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  259. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  260. # 1-1 获取当天计划中包含“自动上新”计划的,计划id
  261. sql = """
  262. select campaign_id, campaign_name from ctop_kuaishou_campaign
  263. where account_id = %s
  264. and put_create_time >= '%s'
  265. and put_create_time <= '%s'
  266. """ % (account_id, start_time, end_time)
  267. df = pd.read_sql(sql, engine)
  268. for row in df.itertuples():
  269. if '素材自动上新' in getattr(row, 'campaign_name'):
  270. campaign_id = getattr(row, 'campaign_id')
  271. break
  272. video_info = get_new_video_info(account_id=account_id,
  273. url=get_new_video_url)
  274. if video_info['code'] != 0:
  275. self.write(json.dumps({"account_id": account_id, "message": "素材自动上新没有获取到视频,不发送ai策略请求!"}))
  276. self.flush()
  277. else:
  278. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  279. if (campaign_name is None) or (group_name is None):
  280. logger.info("account_id = %s, 素材自动上新 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"
  281. % account_id)
  282. self.write(json.dumps({"account_id": account_id,
  283. "message": "素材自动上新 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  284. self.flush()
  285. else:
  286. request_data = get_request_data(account_id=account_id,
  287. campaign_id=campaign_id,
  288. video_info=video_info['data'],
  289. campaign_name=campaign_name,
  290. group_name=group_name,
  291. ai_strategy_remark="自动上新",
  292. name_replace="素材自动上新")
  293. request = ai_strategy_request_parse(request_data)
  294. logger.info("account_id = %s, 素材自动上新 ai策略的返回信息:%s" % (account_id, request))
  295. self.write(json.dumps(request))
  296. self.flush()
  297. except Exception:
  298. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  299. self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))
  300. self.flush()
  301. class AiHighQualityMaterial(tornado.web.RequestHandler):
  302. """
  303. 补充历史高质量素材 -- 测试通过 在AD后台创建成功,检查AD信息和数据库表 没有问题
  304. """
  305. def post(self):
  306. data = self.request.body
  307. data = str(data, 'utf8')
  308. data = json.loads(data, encoding='utf8')
  309. logger.info("*************************************** NEW REQUEST ***************************************")
  310. account_id = data.get('account_id')
  311. campaign_id = data.get('campaign_id')
  312. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  313. (account_id, os.getpid(), os.getppid(), data))
  314. try:
  315. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  316. where account_id=%s order by create_time desc limit 1
  317. """ % account_id
  318. df = pd.read_sql(sql, engine)
  319. if df['general_track'].values[0] == 0:
  320. app_id_array = eval(df['app_id_array'].values[0])
  321. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  322. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  323. app_dict = {}
  324. for app_info in app_list['data']:
  325. if app_info['appId'] in app_id_array:
  326. app_dict[app_info['appVersion']] = app_info
  327. video_list = get_top_video_info(account_id=account_id,
  328. cnt=high_quality_video_cnt,
  329. start_time=(datetime.datetime.now() +
  330. datetime.timedelta(days=-high_quality_video_days)).
  331. strftime("%Y-%m-%d %H:%M:%S"),
  332. end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).
  333. strftime("%Y-%m-%d %H:%M:%S"),
  334. url=get_high_quality_video_url)
  335. # 单独处理返回的时候中,有通用视频,需要单独处理
  336. # "photo_name": "all",
  337. # "photo_name": "ksjytdrca-糖豆人冲啊",
  338. if video_list['code'] == 0:
  339. video_df = pd.DataFrame(video_list['data'])
  340. # 1-特定视频的创建
  341. special_video_df = video_df[video_df.photo_name != 'all']
  342. for photo_name in special_video_df['photo_name'].unique():
  343. if photo_name in app_dict.keys():
  344. campaign_id = None
  345. app_id = app_dict[photo_name].get('appId', 'error')
  346. app_version = app_dict[photo_name].get('appVersion', 'error')
  347. track_url = app_dict[photo_name].get('trackUrl', 'error')
  348. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  349. campaign_df = pd.read_sql(sql, engine)
  350. for row in campaign_df.itertuples():
  351. if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):
  352. campaign_id = getattr(row, 'campaign_id')
  353. break
  354. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  355. app_id_video_list = []
  356. for k, v in app_id_video_df.T.to_dict().items():
  357. app_id_video_list.append(v)
  358. request_data = get_request_data(account_id=account_id,
  359. campaign_id=campaign_id,
  360. video_info=app_id_video_list,
  361. campaign_name=app_version + '--优选广告位-' + '高质量素材',
  362. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  363. ai_strategy_remark="高质量素材复建",
  364. unit_type=4,
  365. app_id=app_id,
  366. click_track_url=track_url)
  367. request = ai_strategy_request_parse(request_data)
  368. logger.info("account_id=%s, app_id=%s, 高质量素材复建-特定视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  369. # 2-通用视频的创建,random.shuffle 选择 app_id
  370. general_video_df = video_df[video_df.photo_name == 'all']
  371. if not general_video_df.empty:
  372. general_video_cnt = len(general_video_df)
  373. app_version_list = list(app_dict)
  374. random.shuffle(app_version_list)
  375. video_cnt = 0
  376. for app_version in app_version_list[:general_video_cnt]:
  377. campaign_id = None
  378. app_id = app_dict[app_version].get('appId', 'error')
  379. track_url = app_dict[app_version].get('trackUrl', 'error')
  380. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  381. campaign_df = pd.read_sql(sql, engine)
  382. for row in campaign_df.itertuples():
  383. if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):
  384. campaign_id = getattr(row, 'campaign_id')
  385. break
  386. single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]
  387. request_data = get_request_data(account_id=account_id,
  388. campaign_id=campaign_id,
  389. video_info=single_video,
  390. campaign_name=app_version + '--优选广告位-' + '高质量素材',
  391. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  392. ai_strategy_remark="高质量素材复建",
  393. unit_type=4,
  394. app_id=app_id,
  395. click_track_url=track_url)
  396. request = ai_strategy_request_parse(request_data)
  397. video_cnt += 1
  398. logger.info("account_id=%s, app_id=%s, 高质量素材复建-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  399. else:
  400. logger.info("account_id=%s, 高质量素材复建: 返回的视频中没有通用视频" % account_id)
  401. else:
  402. logger.info("account_id=%s, 高质量素材复建: 没有获取到视频" % account_id)
  403. self.write(json.dumps({"message": "account_id=%s, 多应用的高质量素材复建请求已走完!" % account_id}))
  404. self.flush()
  405. else:
  406. video_info = get_top_video_info(account_id=account_id,
  407. cnt=high_quality_video_cnt,
  408. start_time=(datetime.datetime.now() +
  409. datetime.timedelta(days=-high_quality_video_days)).
  410. strftime("%Y-%m-%d %H:%M:%S"),
  411. end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).
  412. strftime("%Y-%m-%d %H:%M:%S"),
  413. url=get_high_quality_video_url)
  414. if video_info['code'] != 0:
  415. logger.info("account_id = %s, 高质量素材复建,没有获取到视频,不发送ai策略请求!" % account_id)
  416. self.write(json.dumps({"account_id": account_id,
  417. "message": "高质量素材复建,没有获取到视频,不发送ai策略请求!"}))
  418. self.flush()
  419. else:
  420. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  421. if (campaign_name is None) or (group_name is None):
  422. logger.error("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  423. self.write(json.dumps({"account_id": account_id,
  424. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  425. self.flush()
  426. else:
  427. request_data = get_request_data(account_id=account_id,
  428. campaign_id=campaign_id,
  429. video_info=video_info['data'],
  430. campaign_name=campaign_name,
  431. group_name=group_name,
  432. ai_strategy_remark="高质量素材复建",
  433. name_replace="高质量素材")
  434. request = ai_strategy_request_parse(request_data)
  435. logger.info("account_id = %s, 高质量素材复建,ai策略的返回信息:%s" % (account_id, request))
  436. self.write(json.dumps(request))
  437. self.flush()
  438. except Exception:
  439. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  440. self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))
  441. self.flush()
  442. class AiCheckAndUpTOFullCreative(tornado.web.RequestHandler):
  443. """
  444. 每天22:00 调用该任务,检查当天创意是否创建达到上限,否则使用历史高质量素材填满
  445. """
  446. def post(self):
  447. data = self.request.body
  448. data = str(data, 'utf8')
  449. data = json.loads(data, encoding='utf8')
  450. logger.info("*************************************** NEW REQUEST ***************************************")
  451. account_id = data.get('account_id')
  452. campaign_id = data.get('campaign_id')
  453. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  454. (account_id, os.getpid(), os.getppid(), data))
  455. try:
  456. # 1-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数
  457. sql = """select image_cnt from ctop_ai_kuaishou_advertiser_strategy
  458. where account_id = %s
  459. limit 1""" % account_id
  460. df = pd.read_sql(sql, engine)
  461. image_cnt = int(df['image_cnt'].values[0])
  462. # 1-2、获取从0点到now()为止,该账户下已有的创意个数 M
  463. sql = """select count(1) creative_cnt from ctop_kuaishou_creative
  464. where account_id = %s
  465. and creative_id is not NULL
  466. and creative_create_time <= '%s'
  467. and creative_create_time >= '%s'""" % (account_id,
  468. (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S'),
  469. (datetime.datetime.now()).strftime('%Y-%m-%d'))
  470. df = pd.read_sql(sql, engine)
  471. exist_creative_cnt = int(df['creative_cnt'].values[0])
  472. now_hour = datetime.datetime.now().hour
  473. now = datetime.datetime.now()
  474. should_creative_cnt = now_hour * 100
  475. if exist_creative_cnt < should_creative_cnt:
  476. need_video_cnt = (should_creative_cnt - exist_creative_cnt) // image_cnt
  477. logger.info("account_id = %s, 按小时需要补充的视频数量 = %s" % (account_id, need_video_cnt))
  478. if now_hour % 5 == 0:
  479. video_list = get_top_video_info(account_id=account_id,
  480. url=get_high_quality_video_url,
  481. start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).
  482. strftime("%Y-%m-%d %H:%M:%S"),
  483. end_time=(now + datetime.timedelta(days=-high_quality_video_days)).
  484. strftime("%Y-%m-%d %H:%M:%S"),
  485. cnt=need_video_cnt,
  486. app_version=None)
  487. else:
  488. video_list = get_history_video_info(account_id=account_id,
  489. url=get_history_video_url,
  490. start_time=history_video_start_time,
  491. end_time=history_video_end_time,
  492. video_cnt=need_video_cnt,
  493. related_creative_max_cnt=related_creative_max_cnt)
  494. # 根据获取到的视频进行创建
  495. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  496. where account_id=%s order by create_time desc limit 1""" % account_id
  497. df = pd.read_sql(sql, engine)
  498. if df['general_track'].values[0] == 0:
  499. app_id_array = eval(df['app_id_array'].values[0])
  500. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  501. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  502. app_dict = {}
  503. for app_info in app_list['data']:
  504. if app_info['appId'] in app_id_array:
  505. app_dict[app_info['appVersion']] = app_info
  506. # 单独处理返回的时候中,有通用视频,需要单独处理
  507. # "photo_name": "all",
  508. # "photo_name": "ksjytdrca-糖豆人冲啊",
  509. if video_list['code'] == 0:
  510. video_df = pd.DataFrame(video_list['data'])
  511. # 1-特定视频的创建
  512. special_video_df = video_df[video_df.photo_name != 'all']
  513. for photo_name in special_video_df['photo_name'].unique():
  514. if photo_name in app_dict.keys():
  515. campaign_id = None
  516. app_id = app_dict[photo_name].get('appId', 'error')
  517. app_version = app_dict[photo_name].get('appVersion', 'error')
  518. track_url = app_dict[photo_name].get('trackUrl', 'error')
  519. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  520. campaign_df = pd.read_sql(sql, engine)
  521. for row in campaign_df.itertuples():
  522. if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):
  523. campaign_id = getattr(row, 'campaign_id')
  524. break
  525. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  526. app_id_video_list = []
  527. for k, v in app_id_video_df.T.to_dict().items():
  528. app_id_video_list.append(v)
  529. request_data = get_request_data(account_id=account_id,
  530. campaign_id=campaign_id,
  531. video_info=app_id_video_list,
  532. campaign_name=app_version + '--优选广告位-' + '高质量素材',
  533. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  534. ai_strategy_remark="按小时补满创意",
  535. unit_type=4,
  536. app_id=app_id,
  537. click_track_url=track_url)
  538. request = ai_strategy_request_parse(request_data)
  539. logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-特定视频,ai策略的返回信息:%s" %
  540. (account_id, app_id, request))
  541. # 2-通用视频的创建,random.shuffle 选择 app_id
  542. general_video_df = video_df[video_df.photo_name == 'all']
  543. if not general_video_df.empty:
  544. general_video_cnt = len(general_video_df)
  545. app_version_list = list(app_dict)
  546. random.shuffle(app_version_list)
  547. video_cnt = 0
  548. for app_version in app_version_list[:general_video_cnt]:
  549. campaign_id = None
  550. app_id = app_dict[app_version].get('appId', 'error')
  551. track_url = app_dict[app_version].get('trackUrl', 'error')
  552. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  553. campaign_df = pd.read_sql(sql, engine)
  554. for row in campaign_df.itertuples():
  555. if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):
  556. campaign_id = getattr(row, 'campaign_id')
  557. break
  558. single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]
  559. request_data = get_request_data(account_id=account_id,
  560. campaign_id=campaign_id,
  561. video_info=single_video,
  562. campaign_name=app_version + '--优选广告位-' + '高质量素材',
  563. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  564. ai_strategy_remark="用高质量素材补满创意",
  565. unit_type=4,
  566. app_id=app_id,
  567. click_track_url=track_url)
  568. request = ai_strategy_request_parse(request_data)
  569. video_cnt += 1
  570. logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  571. else:
  572. logger.info("account_id=%s, 用高质量素材补满创意: 返回的视频中没有通用视频" % account_id)
  573. self.write(json.dumps({"message": "account_id=%s, 用高质量素材补满创意请求已走完!" % account_id}))
  574. self.flush()
  575. else:
  576. logger.info("account_id=%s, 按小时补满创意: 没有成功获取到视频!" % account_id)
  577. self.write(json.dumps({"message": "account_id=%s, 按小时补满创意: 没有成功获取到视频!" % account_id}))
  578. self.flush()
  579. else:
  580. # 1-1 获取当天计划中包含“高质量素材”计划的,计划id
  581. sql = """
  582. select campaign_id, campaign_name from ctop_kuaishou_campaign
  583. where account_id = %s
  584. and put_create_time >= '%s'
  585. and put_create_time <= '%s'
  586. """ % (account_id, start_time, end_time)
  587. df = pd.read_sql(sql, engine)
  588. for row in df.itertuples():
  589. if '高质量素材' in getattr(row, 'campaign_name'):
  590. campaign_id = getattr(row, 'campaign_id')
  591. break
  592. video_info = get_top_video_info(account_id=account_id,
  593. cnt=video_cnt,
  594. start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).
  595. strftime("%Y-%m-%d %H:%M:%S"),
  596. end_time=(now + datetime.timedelta(days=-high_quality_video_days)).
  597. strftime("%Y-%m-%d %H:%M:%S"),
  598. url=get_high_quality_video_url)
  599. if video_info['code'] != 0:
  600. logger.info("account_id = %s, 历史高质量素材填满, 没有获取到视频,不发送ai策略请求!" % account_id)
  601. self.write(json.dumps({"account_id": account_id,
  602. "message": "历史高质量素材填满, 没有获取到视频,不发送ai策略请求!"}))
  603. self.flush()
  604. else:
  605. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  606. if (campaign_name is None) or (group_name is None):
  607. logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  608. self.write(json.dumps({"account_id": account_id,
  609. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  610. self.flush()
  611. else:
  612. request_data = get_request_data(account_id=account_id,
  613. campaign_id=campaign_id,
  614. video_info=video_info['data'],
  615. campaign_name=campaign_name,
  616. group_name=group_name,
  617. ai_strategy_remark="用高质量素材补满创意",
  618. name_replace="高质量素材")
  619. request = ai_strategy_request_parse(request_data)
  620. logger.info("account_id = %s, 用高质量素材补满创意,ai策略的返回信息:%s" % (account_id, request))
  621. self.write(json.dumps(request))
  622. self.flush()
  623. else:
  624. logger.info("account_id = %s, 创意已经建满,不需要补充素材!" % account_id)
  625. self.write(json.dumps({"account_id": account_id, "message": "创意已经建满,不需要补充素材!"}))
  626. self.flush()
  627. # 2、如果创意个数M 小于 hour()*100, 则使用 (hour()*100 - M)// image_cnt 个视频
  628. # 2、datetime.datetime.now().hour % 5, 如果不等于0, 则使用创意关联数少的视频,否则使用历史跑量视频
  629. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  630. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  631. now = datetime.datetime.now()
  632. # 1-1 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数
  633. sql = """
  634. select image_cnt from ctop_ai_kuaishou_advertiser_strategy
  635. where account_id = %s
  636. limit 1
  637. """ % account_id
  638. df = pd.read_sql(sql, engine)
  639. image_cnt = int(df['image_cnt'].values[0])
  640. # 2-1 从 ctop_kuaishou_creative 获取当天该账户到此刻 已经创建的创意个数
  641. video_cnt = 0
  642. sql = """
  643. select count(1) creative_cnt from ctop_kuaishou_creative
  644. where account_id = %s
  645. and creative_id is not NULL
  646. and creative_create_time <= '%s'
  647. and creative_create_time >= '%s'
  648. """ % (account_id, end_time, start_time)
  649. df = pd.read_sql(sql, engine)
  650. creative_cnt = int(df['creative_cnt'].values[0])
  651. if creative_cnt >= 2000:
  652. video_cnt = 0
  653. else:
  654. video_cnt = (2000 - creative_cnt) // image_cnt
  655. # 3-1 获取视频信息
  656. if video_cnt == 0:
  657. logger.info("account_id = %s, 创意已经建满,不需要补充素材!" % account_id)
  658. self.write(json.dumps({"account_id": account_id, "message": "创意已经建满,不需要补充素材!"}))
  659. self.flush()
  660. else:
  661. logger.info("account_id = %s, 需要补充的视频数量 = %s" % (account_id, video_cnt))
  662. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  663. where account_id=%s order by create_time desc limit 1
  664. """ % account_id
  665. df = pd.read_sql(sql, engine)
  666. if df['general_track'].values[0] == 0:
  667. app_id_array = eval(df['app_id_array'].values[0])
  668. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  669. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  670. app_dict = {}
  671. for app_info in app_list['data']:
  672. if app_info['appId'] in app_id_array:
  673. app_dict[app_info['appVersion']] = app_info
  674. video_list = get_top_video_info(account_id=account_id,
  675. url=get_high_quality_video_url,
  676. start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).
  677. strftime("%Y-%m-%d %H:%M:%S"),
  678. end_time=(now + datetime.timedelta(days=-high_quality_video_days)).
  679. strftime("%Y-%m-%d %H:%M:%S"),
  680. cnt=video_cnt,
  681. app_version=None)
  682. # 单独处理返回的时候中,有通用视频,需要单独处理
  683. # "photo_name": "all",
  684. # "photo_name": "ksjytdrca-糖豆人冲啊",
  685. if video_list['code'] == 0:
  686. video_df = pd.DataFrame(video_list['data'])
  687. # 1-特定视频的创建
  688. special_video_df = video_df[video_df.photo_name != 'all']
  689. for photo_name in special_video_df['photo_name'].unique():
  690. if photo_name in app_dict.keys():
  691. campaign_id = None
  692. app_id = app_dict[photo_name].get('appId', 'error')
  693. app_version = app_dict[photo_name].get('appVersion', 'error')
  694. track_url = app_dict[photo_name].get('trackUrl', 'error')
  695. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  696. campaign_df = pd.read_sql(sql, engine)
  697. for row in campaign_df.itertuples():
  698. if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):
  699. campaign_id = getattr(row, 'campaign_id')
  700. break
  701. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  702. app_id_video_list = []
  703. for k, v in app_id_video_df.T.to_dict().items():
  704. app_id_video_list.append(v)
  705. request_data = get_request_data(account_id=account_id,
  706. campaign_id=campaign_id,
  707. video_info=app_id_video_list,
  708. campaign_name=app_version + '--优选广告位-' + '高质量素材',
  709. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  710. ai_strategy_remark="用高质量素材补满创意",
  711. unit_type=4,
  712. app_id=app_id,
  713. click_track_url=track_url)
  714. request = ai_strategy_request_parse(request_data)
  715. logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-特定视频,ai策略的返回信息:%s" %
  716. (account_id, app_id, request))
  717. # 2-通用视频的创建,random.shuffle 选择 app_id
  718. general_video_df = video_df[video_df.photo_name == 'all']
  719. if not general_video_df.empty:
  720. general_video_cnt = len(general_video_df)
  721. app_version_list = list(app_dict)
  722. random.shuffle(app_version_list)
  723. video_cnt = 0
  724. for app_version in app_version_list[:general_video_cnt]:
  725. campaign_id = None
  726. app_id = app_dict[app_version].get('appId', 'error')
  727. track_url = app_dict[app_version].get('trackUrl', 'error')
  728. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  729. campaign_df = pd.read_sql(sql, engine)
  730. for row in campaign_df.itertuples():
  731. if (app_version + '--优选广告位-' + '高质量素材') == getattr(row, 'campaign_name'):
  732. campaign_id = getattr(row, 'campaign_id')
  733. break
  734. single_video = [general_video_df.iloc[video_cnt, :].T.to_dict()]
  735. request_data = get_request_data(account_id=account_id,
  736. campaign_id=campaign_id,
  737. video_info=single_video,
  738. campaign_name=app_version + '--优选广告位-' + '高质量素材',
  739. group_name=app_version + '-优选广告位-不限-自定义-' + '高质量素材',
  740. ai_strategy_remark="用高质量素材补满创意",
  741. unit_type=4,
  742. app_id=app_id,
  743. click_track_url=track_url)
  744. request = ai_strategy_request_parse(request_data)
  745. video_cnt += 1
  746. logger.info("account_id=%s, app_id=%s, 用高质量素材补满创意-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  747. else:
  748. logger.info("account_id=%s, 用高质量素材补满创意: 返回的视频中没有通用视频" % account_id)
  749. self.write(json.dumps({"message": "account_id=%s, 用高质量素材补满创意请求已走完!" % account_id}))
  750. self.flush()
  751. else:
  752. # 1-1 获取当天计划中包含“高质量素材”计划的,计划id
  753. sql = """
  754. select campaign_id, campaign_name from ctop_kuaishou_campaign
  755. where account_id = %s
  756. and put_create_time >= '%s'
  757. and put_create_time <= '%s'
  758. """ % (account_id, start_time, end_time)
  759. df = pd.read_sql(sql, engine)
  760. for row in df.itertuples():
  761. if '高质量素材' in getattr(row, 'campaign_name'):
  762. campaign_id = getattr(row, 'campaign_id')
  763. break
  764. video_info = get_top_video_info(account_id=account_id,
  765. cnt=video_cnt,
  766. start_time=(now + datetime.timedelta(days=-2 * high_quality_video_days)).
  767. strftime("%Y-%m-%d %H:%M:%S"),
  768. end_time=(now + datetime.timedelta(days=-high_quality_video_days)).
  769. strftime("%Y-%m-%d %H:%M:%S"),
  770. url=get_high_quality_video_url)
  771. if video_info['code'] != 0:
  772. logger.info("account_id = %s, 历史高质量素材填满, 没有获取到视频,不发送ai策略请求!" % account_id)
  773. self.write(json.dumps({"account_id": account_id,
  774. "message": "历史高质量素材填满, 没有获取到视频,不发送ai策略请求!"}))
  775. self.flush()
  776. else:
  777. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  778. if (campaign_name is None) or (group_name is None):
  779. logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  780. self.write(json.dumps({"account_id": account_id,
  781. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  782. self.flush()
  783. else:
  784. request_data = get_request_data(account_id=account_id,
  785. campaign_id=campaign_id,
  786. video_info=video_info['data'],
  787. campaign_name=campaign_name,
  788. group_name=group_name,
  789. ai_strategy_remark="用高质量素材补满创意",
  790. name_replace="高质量素材")
  791. request = ai_strategy_request_parse(request_data)
  792. logger.info("account_id = %s, 用高质量素材补满创意,ai策略的返回信息:%s" % (account_id, request))
  793. self.write(json.dumps(request))
  794. self.flush()
  795. except Exception:
  796. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  797. self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))
  798. self.flush()
  799. class AiProgramCreativeHighQualityMaterial(tornado.web.RequestHandler):
  800. """
  801. 使用历史跑量素材创建程序化创意
  802. 跑量素材150个(近14天的素材)
  803. 计划名称--跑量素材程序化
  804. timeTask: 每天上午10点执行 --(0 0 10 * * ?)
  805. ----------------------------------------
  806. 拼装参数和写入数据库时,需要修改名称(程序化与非程序化不一致):
  807. creative_name --> package_name(程序化创意名称)
  808. action_bar_text --> action_bar(行动号召按钮)
  809. description --> captions(作品广告语)
  810. click_track_url --> click_url
  811. 写入数据库时:
  812. image_md5s --> cover_image_tokens
  813. 程序化与非程序化的逻辑不一样:
  814. 需要5个视频4张封面一组,拼装程序化创意参数需要单独抽取一个方法出来
  815. 所有操作记录表中,添加status信息(已提交,成功,失败),和回调时的 message信息
  816. """
  817. def post(self):
  818. data = self.request.body
  819. data = str(data, 'utf8')
  820. data = json.loads(data, encoding='utf8')
  821. logger.info("*************************************** NEW REQUEST ***************************************")
  822. account_id = data.get('account_id')
  823. campaign_id = data.get('campaign_id')
  824. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  825. (account_id, os.getpid(), os.getppid(), data))
  826. try:
  827. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  828. where account_id=%s order by create_time desc limit 1
  829. """ % account_id
  830. df = pd.read_sql(sql, engine)
  831. if df['general_track'].values[0] == 0:
  832. app_id_array = eval(df['app_id_array'].values[0])
  833. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  834. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  835. app_dict = {}
  836. for app_info in app_list['data']:
  837. if app_info['appId'] in app_id_array:
  838. app_dict[app_info['appVersion']] = app_info
  839. video_list = get_top_video_info(account_id=account_id,
  840. cnt=multi_app_id_programme_high_quality_video_cnt,
  841. start_time=(datetime.datetime.now() + datetime.timedelta(
  842. days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  843. end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).
  844. strftime("%Y-%m-%d %H:%M:%S"),
  845. url=get_high_quality_video_url)
  846. # 单独处理返回的时候中,有通用视频,需要单独处理
  847. # "photo_name": "all",
  848. # "photo_name": "ksjytdrca-糖豆人冲啊",
  849. if video_list['code'] == 0:
  850. video_df = pd.DataFrame(video_list['data'])
  851. # 1-特定视频的创建
  852. special_video_df = video_df[video_df.photo_name != 'all']
  853. for photo_name in special_video_df['photo_name'].unique():
  854. if photo_name in app_dict.keys():
  855. campaign_id = None
  856. app_id = app_dict[photo_name].get('appId', 'error')
  857. app_version = app_dict[photo_name].get('appVersion', 'error')
  858. track_url = app_dict[photo_name].get('trackUrl', 'error')
  859. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  860. campaign_df = pd.read_sql(sql, engine)
  861. for row in campaign_df.itertuples():
  862. if (app_version + '--优选广告位-' + '程序化高质量素材') == getattr(row, 'campaign_name'):
  863. campaign_id = getattr(row, 'campaign_id')
  864. break
  865. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  866. app_id_video_list = []
  867. for k, v in app_id_video_df.T.to_dict().items():
  868. app_id_video_list.append(v)
  869. request_data = get_request_data(account_id=account_id,
  870. campaign_id=campaign_id,
  871. video_info=app_id_video_list,
  872. campaign_name=app_version + '--优选广告位-' + '程序化高质量素材',
  873. group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',
  874. ai_strategy_remark="程序化高质量素材",
  875. unit_type=7,
  876. app_id=app_id,
  877. click_track_url=track_url)
  878. request = ai_strategy_request_parse(request_data)
  879. logger.info("account_id=%s, app_id=%s, 程序化高质量素材-特定视频,ai策略的返回信息:%s" %
  880. (account_id, app_id, request))
  881. # 2-通用视频的创建,random.shuffle 选择 app_id
  882. general_video_df = video_df[video_df.photo_name == 'all']
  883. general_video_list = []
  884. for k, v in general_video_df.T.to_dict().items():
  885. general_video_list.append(v)
  886. if general_video_list:
  887. app_version_list = list(app_dict)
  888. random.shuffle(app_version_list)
  889. # 每5个通用视频创建一个程序化广告组
  890. batch_num = 5
  891. app_id_cnt = 0
  892. for i in range(0, len(general_video_list), 5):
  893. if i + 5 < len(general_video_list):
  894. batch_general_video = general_video_list[i: i + batch_num]
  895. app_version = app_version_list[app_id_cnt]
  896. app_id = app_dict[app_version].get('appId', 'error')
  897. track_url = app_dict[app_version].get('trackUrl', 'error')
  898. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  899. campaign_df = pd.read_sql(sql, engine)
  900. for row in campaign_df.itertuples():
  901. if (app_version + '--优选广告位-' + '程序化高质量素材') == getattr(row, 'campaign_name'):
  902. campaign_id = getattr(row, 'campaign_id')
  903. break
  904. request_data = get_request_data(account_id=account_id,
  905. campaign_id=campaign_id,
  906. video_info=batch_general_video,
  907. campaign_name=app_version + '--优选广告位-' + '程序化高质量素材',
  908. group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',
  909. ai_strategy_remark="程序化高质量素材",
  910. unit_type=7,
  911. app_id=app_id,
  912. click_track_url=track_url)
  913. request = ai_strategy_request_parse(request_data)
  914. app_id_cnt += 1
  915. logger.info("account_id=%s, app_id=%s, 程序化高质量素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  916. else:
  917. batch_general_video = general_video_list[i: len(general_video_list)]
  918. app_version = app_version_list[app_id_cnt]
  919. app_id = app_dict[app_version].get('appId', 'error')
  920. track_url = app_dict[app_version].get('trackUrl', 'error')
  921. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  922. campaign_df = pd.read_sql(sql, engine)
  923. for row in campaign_df.itertuples():
  924. if (app_version + '--优选广告位-' + '程序化高质量素材') == getattr(row, 'campaign_name'):
  925. campaign_id = getattr(row, 'campaign_id')
  926. break
  927. request_data = get_request_data(account_id=account_id,
  928. campaign_id=campaign_id,
  929. video_info=batch_general_video,
  930. campaign_name=app_version + '--优选广告位-' + '程序化高质量素材',
  931. group_name=app_version + '-优选广告位-不限-程序化-' + '高质量素材',
  932. ai_strategy_remark="程序化高质量素材",
  933. unit_type=7,
  934. app_id=app_id,
  935. click_track_url=track_url)
  936. request = ai_strategy_request_parse(request_data)
  937. logger.info("account_id=%s, app_id=%s, 程序化高质量素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  938. else:
  939. logger.info("account_id=%s, 程序化高质量素材: 返回的视频中没有通用视频" % account_id)
  940. self.write(json.dumps({"message": "account_id=%s, 程序化高质量素材请求已走完!" % account_id}))
  941. self.flush()
  942. else:
  943. video_info = get_top_video_info(account_id=account_id,
  944. cnt=programme_high_quality_video_cnt,
  945. start_time=(datetime.datetime.now() + datetime.timedelta(
  946. days=-high_quality_video_days)).strftime("%Y-%m-%d %H:%M:%S"),
  947. end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).
  948. strftime("%Y-%m-%d %H:%M:%S"),
  949. url=get_high_quality_video_url)
  950. if video_info['code'] != 0:
  951. logger.info("account_id = %s, 程序化高质量素材,没有获取到视频,不发送ai策略请求!" % account_id)
  952. self.write(json.dumps({"account_id": account_id,
  953. "message": "没有获取到视频,不发送ai策略请求!"}))
  954. self.flush()
  955. else:
  956. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  957. if (campaign_name is None) or (group_name is None):
  958. logger.info("account_id = %s, 没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!" % account_id)
  959. self.write(json.dumps({"account_id": account_id,
  960. "message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  961. self.flush()
  962. else:
  963. request_data = get_request_data(account_id=account_id,
  964. campaign_id=campaign_id,
  965. video_info=video_info['data'],
  966. campaign_name=campaign_name,
  967. group_name=group_name,
  968. ai_strategy_remark="程序化高质量素材",
  969. name_replace="程序化高质量素材",
  970. unit_type=7)
  971. request = ai_strategy_request_parse(request_data)
  972. logger.info("account_id = %s, 程序化高质量素材, ai策略的返回信息:%s" % (account_id, request))
  973. self.write(json.dumps(request))
  974. self.flush()
  975. except Exception:
  976. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  977. self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))
  978. self.flush()
  979. class AiProgramCreativeNewMaterial(tornado.web.RequestHandler):
  980. """
  981. 使用N天内 创意数关联小于M个(如当天创意关联数小于10的,150个素材)的素材创建程序化创意
  982. 计划名称--新素材程序化
  983. timeTask: 每天上午10点执行 --(0 0 10 * * ?)
  984. """
  985. def post(self):
  986. data = self.request.body
  987. data = str(data, 'utf8')
  988. data = json.loads(data, encoding='utf8')
  989. logger.info("*************************************** NEW REQUEST ***************************************")
  990. account_id = data.get('account_id')
  991. campaign_id = data.get('campaign_id')
  992. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s, raw data from request is %s" %
  993. (account_id, os.getpid(), os.getppid(), data))
  994. try:
  995. sql = """select account_id, single_appid, app_id_array, general_track from ctop_ai_kuaishou_advertiser_strategy
  996. where account_id=%s order by create_time desc limit 1
  997. """ % account_id
  998. df = pd.read_sql(sql, engine)
  999. if df['general_track'].values[0] == 0:
  1000. app_id_array = eval(df['app_id_array'].values[0])
  1001. app_list = get_app_list(account_id=account_id, url=get_app_list_url)
  1002. # app_dict key = ("appVersion": "ksjyrlyn-人类一脑涂地大败") , value = 完整的app_id信息
  1003. app_dict = {}
  1004. for app_info in app_list['data']:
  1005. if app_info['appId'] in app_id_array:
  1006. app_dict[app_info['appVersion']] = app_info
  1007. video_list = get_history_video_info(account_id=account_id,
  1008. url=get_history_video_url,
  1009. start_time=(datetime.datetime.now() + datetime.timedelta(days=-7)).
  1010. strftime("%Y-%m-%d %H:%M:%S"),
  1011. end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).
  1012. strftime("%Y-%m-%d %H:%M:%S"),
  1013. video_cnt=multi_app_id_programme_new_video_cnt,
  1014. related_creative_max_cnt=related_creative_max_cnt)
  1015. # 单独处理返回的时候中,有通用视频,需要单独处理
  1016. # "photo_name": "all",
  1017. # "photo_name": "ksjytdrca-糖豆人冲啊",
  1018. if video_list['code'] == 0:
  1019. video_df = pd.DataFrame(video_list['data'])
  1020. # 1-特定视频的创建
  1021. special_video_df = video_df[video_df.photo_name != 'all']
  1022. for photo_name in special_video_df['photo_name'].unique():
  1023. if photo_name in app_dict.keys():
  1024. campaign_id = None
  1025. app_id = app_dict[photo_name].get('appId', 'error')
  1026. app_version = app_dict[photo_name].get('appVersion', 'error')
  1027. track_url = app_dict[photo_name].get('trackUrl', 'error')
  1028. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  1029. campaign_df = pd.read_sql(sql, engine)
  1030. for row in campaign_df.itertuples():
  1031. if (app_version + '--优选广告位-' + '程序化上新素材') == getattr(row, 'campaign_name'):
  1032. campaign_id = getattr(row, 'campaign_id')
  1033. break
  1034. app_id_video_df = special_video_df[special_video_df.photo_name == photo_name]
  1035. app_id_video_list = []
  1036. for k, v in app_id_video_df.T.to_dict().items():
  1037. app_id_video_list.append(v)
  1038. request_data = get_request_data(account_id=account_id,
  1039. campaign_id=campaign_id,
  1040. video_info=app_id_video_list,
  1041. campaign_name=app_version + '--优选广告位-' + '程序化上新素材',
  1042. group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',
  1043. ai_strategy_remark="程序化上新素材",
  1044. unit_type=7,
  1045. app_id=app_id,
  1046. click_track_url=track_url)
  1047. request = ai_strategy_request_parse(request_data)
  1048. logger.info("account_id=%s, app_id=%s, 程序化上新素材-特定视频,ai策略的返回信息:%s" %
  1049. (account_id, app_id, request))
  1050. # 2-通用视频的创建,random.shuffle 选择 app_id
  1051. general_video_df = video_df[video_df.photo_name == 'all']
  1052. general_video_list = []
  1053. for k, v in general_video_df.T.to_dict().items():
  1054. general_video_list.append(v)
  1055. if general_video_list:
  1056. app_version_list = list(app_dict)
  1057. random.shuffle(app_version_list)
  1058. # 每5个通用视频创建一个程序化广告组
  1059. batch_num = 5
  1060. app_id_cnt = 0
  1061. for i in range(0, len(general_video_list), 5):
  1062. if i + 5 < len(general_video_list):
  1063. batch_general_video = general_video_list[i: i + batch_num]
  1064. app_version = app_version_list[app_id_cnt]
  1065. app_id = app_dict[app_version].get('appId', 'error')
  1066. track_url = app_dict[app_version].get('trackUrl', 'error')
  1067. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  1068. campaign_df = pd.read_sql(sql, engine)
  1069. for row in campaign_df.itertuples():
  1070. if (app_version + '--优选广告位-' + '程序化上新素材') == getattr(row, 'campaign_name'):
  1071. campaign_id = getattr(row, 'campaign_id')
  1072. break
  1073. request_data = get_request_data(account_id=account_id,
  1074. campaign_id=campaign_id,
  1075. video_info=batch_general_video,
  1076. campaign_name=app_version + '--优选广告位-' + '程序化上新素材',
  1077. group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',
  1078. ai_strategy_remark="程序化上新素材",
  1079. unit_type=7,
  1080. app_id=app_id,
  1081. click_track_url=track_url)
  1082. request = ai_strategy_request_parse(request_data)
  1083. app_id_cnt += 1
  1084. logger.info("account_id=%s, app_id=%s, 程序化上新素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  1085. else:
  1086. batch_general_video = general_video_list[i: len(general_video_list)]
  1087. app_version = app_version_list[app_id_cnt]
  1088. app_id = app_dict[app_version].get('appId', 'error')
  1089. track_url = app_dict[app_version].get('trackUrl', 'error')
  1090. sql = """ select campaign_id, campaign_name from ctop_kuaishou_campaign where account_id = %s """ % account_id
  1091. campaign_df = pd.read_sql(sql, engine)
  1092. for row in campaign_df.itertuples():
  1093. if (app_version + '--优选广告位-' + '程序化上新素材') == getattr(row, 'campaign_name'):
  1094. campaign_id = getattr(row, 'campaign_id')
  1095. break
  1096. request_data = get_request_data(account_id=account_id,
  1097. campaign_id=campaign_id,
  1098. video_info=batch_general_video,
  1099. campaign_name=app_version + '--优选广告位-' + '程序化上新素材',
  1100. group_name=app_version + '-优选广告位-不限-程序化-' + '上新素材',
  1101. ai_strategy_remark="程序化上新素材",
  1102. unit_type=7,
  1103. app_id=app_id,
  1104. click_track_url=track_url)
  1105. request = ai_strategy_request_parse(request_data)
  1106. logger.info("account_id=%s, app_id=%s, 程序化上新素材-通用视频,ai策略的返回信息:%s" % (account_id, app_id, request))
  1107. else:
  1108. logger.info("account_id=%s, 程序化上新素材: 返回的视频中没有通用视频" % account_id)
  1109. self.write(json.dumps({"message": "account_id=%s, 程序化上新素材请求已走完!" % account_id}))
  1110. self.flush()
  1111. else:
  1112. video_info = get_history_video_info(account_id=account_id,
  1113. url=get_history_video_url,
  1114. start_time=(datetime.datetime.now() + datetime.timedelta(days=-7)).
  1115. strftime("%Y-%m-%d %H:%M:%S"),
  1116. end_time=(datetime.datetime.now() + datetime.timedelta(days=-1)).
  1117. strftime("%Y-%m-%d %H:%M:%S"),
  1118. video_cnt=programme_new_video_cnt,
  1119. related_creative_max_cnt=related_creative_max_cnt)
  1120. if video_info['code'] != 0:
  1121. self.write(json.dumps({"account_id": account_id,
  1122. "message": "程序化上新素材,没有获取到视频,不发送ai策略请求!"}))
  1123. self.flush()
  1124. else:
  1125. campaign_name, group_name = get_campaign_and_group_name_rule(account_id)
  1126. if (campaign_name is None) or (group_name is None):
  1127. logger.info("没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!")
  1128. self.write(json.dumps({"message": "没有获取到广告计划命名规则 或 广告组命名规则,不发送ai策略请求!"}))
  1129. self.flush()
  1130. else:
  1131. request_data = get_request_data(account_id=account_id,
  1132. campaign_id=campaign_id,
  1133. video_info=video_info['data'],
  1134. campaign_name=campaign_name,
  1135. group_name=group_name,
  1136. ai_strategy_remark="程序化上新素材",
  1137. name_replace="程序化上新素材",
  1138. unit_type=7)
  1139. request = ai_strategy_request_parse(request_data)
  1140. logger.info("account_id = %s, 程序化上新素材,ai策略的返回信息:%s" % (account_id, request))
  1141. self.write(json.dumps(request))
  1142. self.flush()
  1143. except Exception:
  1144. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  1145. self.write(json.dumps({"account_id": account_id, "message": traceback.format_exc(), "code": -1}))
  1146. self.flush()