ai_time_task_creative_handler.py 75 KB

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