MonitorMediaApi.py 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/25 2:55 下午
  4. # @Site :
  5. # @File : MonitorMediaApi.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. from urllib import parse
  11. import pandas as pd
  12. from sqlalchemy import create_engine
  13. from OuterDataCompareInnerData.Utils.sendMessage import feishuMsg
  14. class MonitorMediaApi:
  15. def __init__(self):
  16. self.InnerStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  17. "hcst", parse.quote_plus("hcst@2021"), "192.168.0.184", 3390, "media_api")
  18. self.InnerConn = create_engine(self.InnerStr,
  19. pool_size=10,
  20. max_overflow=5,
  21. pool_timeout=10,
  22. pool_recycle=3600)
  23. self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
  24. "data", parse.quote_plus("hcst@2021"), "139.186.27.96", 3390, "jeecg-boot")
  25. self.OuterConn = create_engine(self.OuterStr,
  26. pool_size=10,
  27. max_overflow=5,
  28. pool_timeout=10,
  29. pool_recycle=3600)
  30. self.Media_List = {
  31. "bytedance_ad_report_daily": "ctop_bytedance_report_plan_daily",
  32. "bytedance_advertiser_report_daily": "ctop_bytedance_report_advertiser_daily",
  33. "bytedance_creative_report_daily": "ctop_bytedance_report_creative_daily",
  34. "bytedance_material_report_daily": "ctop_bytedance_report_material_daily",
  35. "bytedance_campaign_report_daily": "ctop_bytedance_report_campaign_daily",
  36. "kuaishou_account_report_daily": "ctop_kuaishou_report_daily_account",
  37. "kuaishou_campaign_report_daily": "ctop_kuaishou_report_daily_campaign",
  38. "kuaishou_material_video_report_daily": "ctop_kuaishou_report_daily_material",
  39. "kuaishou_unit_report_daily": "ctop_kuaishou_report_daily_group"
  40. }
  41. self.stat_date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
  42. self.date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y-%m-%d")
  43. def compare_task(self):
  44. for innertable, outertable in self.Media_List.items():
  45. if 'bytedance' in innertable:
  46. if innertable == 'bytedance_material_report_daily':
  47. sql = """select advertiser_id,sum(cost) as cost from media_api.{innertable} where stat_datetime={date}
  48. and image_mode in('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  49. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO')
  50. group by advertiser_id""". \
  51. format(date=self.stat_date, innertable=innertable)
  52. sql2 = """select {stat_date} as stat_datetime,account_id as advertiser_id,sum(cost) as cost from
  53. `jeecg-boot`.{outertable}
  54. where stat_datetime ='{date}'
  55. and image_mode in('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  56. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO')
  57. group by account_id,stat_datetime""".format(date=self.date, stat_date=self.stat_date,
  58. outertable=outertable)
  59. InnerAdvertiserListFrame = pd.read_sql(sql, self.InnerConn)
  60. OuterAdvertiserListFrame = pd.read_sql(sql2, self.OuterConn)
  61. data = pd.merge(OuterAdvertiserListFrame, InnerAdvertiserListFrame, how='left', on='advertiser_id')
  62. result = data[data['cost_x'] != data['cost_y']]
  63. # print('线上{outertable}对内网上{innertable} 结果为'.format(innertable=innertable, outertable=outertable),
  64. # result)
  65. if len(result.values.tolist()) == 0:
  66. message = """线上{outertable}对内网{innertable} 数据相等""".format(innertable=innertable,
  67. outertable=outertable)
  68. feishuMsg.send_robot_msg(
  69. """线上{outertable}对内网{innertable} 数据相等""".format(innertable=innertable,
  70. outertable=outertable))
  71. else:
  72. messageList = ''
  73. for result in result.values.tolist():
  74. date = result[0]
  75. account_id = result[1]
  76. outerCost = result[2]
  77. innerCost = result[3]
  78. message = """广告主id={account_id} 线上消耗={outerCost},内网消耗={innerCost}""".format(date=date,
  79. account_id=account_id,
  80. outerCost=outerCost,
  81. innerCost=innerCost)
  82. # print(message)
  83. message = message + '\n'
  84. messageList += message
  85. feishuMsg.send_robot_msg(
  86. """{date}线上{outertable}和内网{innertable}差异为:""".format(date=self.stat_date,
  87. outertable=outertable,
  88. innertable=innertable + '\n' + messageList)
  89. )
  90. else:
  91. sql = """select advertiser_id,sum(cost) as cost from media_api.{innertable} where stat_datetime={date}
  92. group by advertiser_id""". \
  93. format(date=self.stat_date, innertable=innertable)
  94. sql2 = """select {stat_date} as stat_datetime,advertiser_id as advertiser_id,sum(cost) as cost from
  95. `jeecg-boot`.{outertable}
  96. where stat_datetime ='{date}'
  97. group by advertiser_id,stat_datetime""".format(date=self.date, stat_date=self.stat_date,
  98. outertable=outertable)
  99. InnerAdvertiserListFrame = pd.read_sql(sql, self.InnerConn)
  100. OuterAdvertiserListFrame = pd.read_sql(sql2, self.OuterConn)
  101. data = pd.merge(OuterAdvertiserListFrame, InnerAdvertiserListFrame, how='left', on='advertiser_id')
  102. result = data[data['cost_x'] != data['cost_y']]
  103. if len(result.values.tolist()) == 0:
  104. feishuMsg.send_robot_msg(
  105. '线上{outertable}对内网{innertable} 数据相等'.format(innertable=innertable, outertable=outertable))
  106. else:
  107. messageList = ''
  108. for result in result.values.tolist():
  109. date = result[0]
  110. account_id = result[1]
  111. outerCost = result[2]
  112. innerCost = result[3]
  113. message = """广告主id={account_id} 线上消耗={outerCost},内网消耗={innerCost}""".format(date=date,
  114. account_id=account_id,
  115. outerCost=outerCost,
  116. innerCost=innerCost)
  117. # print(message)
  118. message = message + '\n'
  119. messageList += message
  120. feishuMsg.send_robot_msg(
  121. """{date}线上{outertable}和内网{innertable}差异为:""".format(date=self.stat_date,
  122. outertable=outertable,
  123. innertable=innertable + '\n' + messageList)
  124. )
  125. else:
  126. sql = """select advertiser_id,sum(charge) as cost from media_api.{innertable} where stat_date={date}
  127. group by advertiser_id""". \
  128. format(date=self.stat_date, innertable=innertable)
  129. # print(sql)
  130. sql2 = """select {stat_date} as stat_date,account_id as advertiser_id,sum(charge) as cost from
  131. `jeecg-boot`.{outertable}
  132. where stat_date ='{date}'
  133. group by account_id,stat_date""".format(date=self.date, stat_date=self.stat_date,
  134. outertable=outertable)
  135. InnerAdvertiserListFrame = pd.read_sql(sql, self.InnerConn)
  136. OuterAdvertiserListFrame = pd.read_sql(sql2, self.OuterConn)
  137. # print(sql2)
  138. data = pd.merge(OuterAdvertiserListFrame, InnerAdvertiserListFrame, how='left', on='advertiser_id')
  139. result = data[data['cost_x'] != data['cost_y']]
  140. if len(result.values.tolist()) == 0:
  141. feishuMsg.send_robot_msg(
  142. '线上{outertable}对内网{innertable} 数据相等'.format(innertable=innertable, outertable=outertable))
  143. else:
  144. messageList = ''
  145. for result in result.values.tolist():
  146. date = result[0]
  147. account_id = result[1]
  148. outerCost = result[2]
  149. innerCost = result[3]
  150. message = """广告主id={account_id} 线上消耗={outerCost},内网消耗={innerCost}""".format(date=date,
  151. account_id=account_id,
  152. outerCost=outerCost,
  153. innerCost=innerCost)
  154. # print(message)
  155. message = message + '\n'
  156. messageList += message
  157. feishuMsg.send_robot_msg(
  158. """{date}线上{outertable}和内网{innertable}差异为:""".format(date=self.stat_date, outertable=outertable,
  159. innertable=innertable + '\n' + messageList)
  160. )
  161. if __name__ == '__main__':
  162. MonitorMediaApi().compare_task()