ai_time_task_creative_handler.py 75 KB

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