ai_check_and_upto_full_creative_handler.py 6.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160
  1. import datetime
  2. import json
  3. import traceback
  4. import pandas as pd
  5. import requests
  6. import tornado.web
  7. from utils.DataBaseConfig import *
  8. class AiCheckAndUpTOFullCreative(tornado.web.RequestHandler):
  9. """
  10. 每天22:00 调用该任务,检查当天创意是否创建达到上限,否则使用历史跑量素材填满
  11. """
  12. def initialize(self, logger):
  13. self.logger = logger
  14. def post(self):
  15. data = self.request.body
  16. data = str(data, 'utf8')
  17. data = json.loads(data, encoding='utf8')
  18. self.logger.info("*************************************** NEW REQUEST ***************************************")
  19. self.logger.info("raw data from request is %s" % data)
  20. account_id = data.get('account_id')
  21. try:
  22. inst = GetRequestData(account_id)
  23. if inst.video_cnt == 0:
  24. self.logger.info("创意已经建满,不需要补充素材!")
  25. self.write(json.dumps("message: 创意已经建满,不需要补充素材!"))
  26. self.flush()
  27. else:
  28. inst.get_video_info()
  29. if inst.video.get('code', None) != 0:
  30. self.logger.info("没有成功获取到视频信息: %s !" % inst.video.get('message'))
  31. self.write(json.dumps("message: %s" % inst.video.get('message')))
  32. self.flush()
  33. else:
  34. inst.get_request_data()
  35. request = requests.post(ai_strategy_request_url, inst.data)
  36. self.logger.info("ai策略的返回信息:%s" % request.text)
  37. self.write(request.text)
  38. self.flush()
  39. except Exception:
  40. self.logger.error(traceback.format_exc())
  41. self.write(json.dumps(traceback.format_exc()))
  42. self.flush()
  43. class GetRequestData(object):
  44. def __init__(self, account_id):
  45. self.video = None
  46. self.account_id = account_id
  47. self.campaign_id = None
  48. self.data = None
  49. self.campaign_name = None
  50. self.group_name = None
  51. self.video_cnt = 0
  52. self.get_campaign_id()
  53. self.get_video_cnt()
  54. self.get_campaign_and_group_name_rule()
  55. def get_campaign_id(self):
  56. """
  57. 获取当天计划中包含“跑量素材”计划的,计划id
  58. :return:
  59. """
  60. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  61. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  62. sql = """
  63. select campaign_id, campaign_name from ctop_kuaishou_campaign
  64. where account_id = %s
  65. and put_create_time >= '%s'
  66. and put_create_time <= '%s'
  67. """ % (self.account_id, start_time, end_time)
  68. df = pd.read_sql(sql, engine)
  69. for row in df.itertuples():
  70. if '跑量素材' in getattr(row, 'campaign_name'):
  71. self.campaign_id = getattr(row, 'campaign_id')
  72. break
  73. def get_video_cnt(self):
  74. """
  75. 获取需要补充的视频个数
  76. :return:
  77. """
  78. # 从客户策略表中获取该账户下 每个素材搭配的封面个数, 来计算需要补充的素材个数
  79. sql = """
  80. select image_cnt from ctop_ai_kuaishou_advertiser_strategy
  81. where account_id = %s
  82. and status = 1
  83. limit 1
  84. """ % self.account_id
  85. df = pd.read_sql(sql, engine)
  86. image_cnt = df['image_cnt'].values[0]
  87. # 从 ctop_kuaishou_creative 获取当天该账户到此刻 已经创建的创意个数
  88. start_time = (datetime.date.today()).strftime('%Y-%m-%d %H:%M:%S')
  89. end_time = (datetime.datetime.now()).strftime('%Y-%m-%d %H:%M:%S')
  90. sql = """
  91. select count(1) creative_cnt from ctop_kuaishou_creative
  92. where account_id = %s
  93. and creative_id is not NULL
  94. and creative_create_time <= '%s'
  95. and creative_create_time >= '%s'
  96. """ % (self.account_id, end_time, start_time)
  97. df = pd.read_sql(sql, engine)
  98. creative_cnt = df['creative_cnt'].values[0]
  99. if creative_cnt >= 2000:
  100. self.video_cnt = 0
  101. else:
  102. self.video_cnt = (2000-creative_cnt) // image_cnt
  103. def get_video_info(self):
  104. now = datetime.datetime.now()
  105. video_request_data = {
  106. "accountId": self.account_id,
  107. "num": self.video_cnt,
  108. "startTime": (now + datetime.timedelta(days=-2*high_quality_video_days)).strftime("%Y-%m-%d"),
  109. "endTime": (now + datetime.timedelta(days=-high_quality_video_days)).strftime("%Y-%m-%d")
  110. }
  111. request = requests.post(get_historical_high_quality_video_url,
  112. headers=headers,
  113. data=json.JSONEncoder().encode(video_request_data))
  114. self.video = request
  115. def get_campaign_and_group_name_rule(self):
  116. sql = """
  117. select campaign_name, unit_name
  118. from ctop_ai_kuaishou_advertiser_strategy
  119. where account_id = %d
  120. and status = 1
  121. limit 1
  122. """ % self.account_id
  123. df = pd.read_sql(sql, engine)
  124. self.campaign_name = df['campaign_name'].values[0]
  125. self.group_name = df['unit_name'].values[0]
  126. def get_request_data(self):
  127. self.data = {
  128. "video": self.video['data'],
  129. "operation_type": 1,
  130. "account_id": self.account_id,
  131. "ai_strategy_remark": "跑量素材",
  132. "campaign_info":
  133. {
  134. "campaign_id": self.campaign_id,
  135. "campaign_name": self.campaign_name.replace('自定义', '跑量素材')
  136. + str(datetime.datetime.now())
  137. },
  138. 'group_info':
  139. {
  140. 'group_name': self.group_name.replace('自定义', '跑量素材')
  141. + str(datetime.datetime.now()),
  142. 'begin_time': str(datetime.date.today())
  143. },
  144. 'creative_info':
  145. {
  146. 'is_sticky': 0
  147. }
  148. }