ai_time_task_creative_handler.py 75 KB

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