ai_time_task_creative_handler.py 77 KB

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