| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2021/11/25 2:55 下午
 
- # @Site    : 
 
- # @File    : MonitorMediaApi.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import datetime
 
- from urllib import parse
 
- import pandas as pd
 
- from sqlalchemy import create_engine
 
- from OuterDataCompareInnerData.Utils.sendMessage import feishuMsg
 
- class MonitorMediaApi:
 
-     def __init__(self):
 
-         self.InnerStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
 
-             "hcst", parse.quote_plus("hcst@2021"), "192.168.0.184", 3390, "media_api")
 
-         self.InnerConn = create_engine(self.InnerStr,
 
-                                        pool_size=10,
 
-                                        max_overflow=5,
 
-                                        pool_timeout=10,
 
-                                        pool_recycle=3600)
 
-         self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (
 
-             "data", parse.quote_plus("hcst@2021"), "139.186.27.96", 3390, "jeecg-boot")
 
-         self.OuterConn = create_engine(self.OuterStr,
 
-                                        pool_size=10,
 
-                                        max_overflow=5,
 
-                                        pool_timeout=10,
 
-                                        pool_recycle=3600)
 
-         self.Media_List = {
 
-             "bytedance_ad_report_daily": "ctop_bytedance_report_plan_daily",
 
-             "bytedance_advertiser_report_daily": "ctop_bytedance_report_advertiser_daily",
 
-             "bytedance_creative_report_daily": "ctop_bytedance_report_creative_daily",
 
-             "bytedance_material_report_daily": "ctop_bytedance_report_material_daily",
 
-             "bytedance_campaign_report_daily": "ctop_bytedance_report_campaign_daily",
 
-             "kuaishou_account_report_daily": "ctop_kuaishou_report_daily_account",
 
-             "kuaishou_campaign_report_daily": "ctop_kuaishou_report_daily_campaign",
 
-             "kuaishou_material_video_report_daily": "ctop_kuaishou_report_daily_material",
 
-             "kuaishou_unit_report_daily": "ctop_kuaishou_report_daily_group"
 
-         }
 
-         self.stat_date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
 
-         self.date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y-%m-%d")
 
-     def compare_task(self):
 
-         for innertable, outertable in self.Media_List.items():
 
-             if 'bytedance' in innertable:
 
-                 if innertable == 'bytedance_material_report_daily':
 
-                     sql = """select advertiser_id,sum(cost) as cost from media_api.{innertable} where stat_datetime={date}
 
-                             and  image_mode in('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 
 
-                             'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO')
 
-                             group by advertiser_id""". \
 
-                         format(date=self.stat_date, innertable=innertable)
 
-                     sql2 = """select {stat_date} as stat_datetime,account_id as advertiser_id,sum(cost) as cost from 
 
-                     `jeecg-boot`.{outertable}
 
-                     where  stat_datetime ='{date}'
 
-                     and image_mode in('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 
 
-                     'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO')
 
-                     group by account_id,stat_datetime""".format(date=self.date, stat_date=self.stat_date,
 
-                                                                 outertable=outertable)
 
-                     InnerAdvertiserListFrame = pd.read_sql(sql, self.InnerConn)
 
-                     OuterAdvertiserListFrame = pd.read_sql(sql2, self.OuterConn)
 
-                     data = pd.merge(OuterAdvertiserListFrame, InnerAdvertiserListFrame, how='left', on='advertiser_id')
 
-                     result = data[data['cost_x'] != data['cost_y']]
 
-                     # print('线上{outertable}对内网上{innertable} 结果为'.format(innertable=innertable, outertable=outertable),
 
-                     #       result)
 
-                     if len(result.values.tolist()) == 0:
 
-                         message = """线上{outertable}对内网{innertable} 数据相等""".format(innertable=innertable,
 
-                                                                                   outertable=outertable)
 
-                         feishuMsg.send_robot_msg(
 
-                             """线上{outertable}对内网{innertable} 数据相等""".format(innertable=innertable,
 
-                                                                             outertable=outertable))
 
-                     else:
 
-                         messageList = ''
 
-                         for result in result.values.tolist():
 
-                             date = result[0]
 
-                             account_id = result[1]
 
-                             outerCost = result[2]
 
-                             innerCost = result[3]
 
-                             message = """广告主id={account_id} 线上消耗={outerCost},内网消耗={innerCost}""".format(date=date,
 
-                                                                                                         account_id=account_id,
 
-                                                                                                         outerCost=outerCost,
 
-                                                                                                         innerCost=innerCost)
 
-                             # print(message)
 
-                             message = message + '\n'
 
-                             messageList += message
 
-                         feishuMsg.send_robot_msg(
 
-                             """{date}线上{outertable}和内网{innertable}差异为:""".format(date=self.stat_date,
 
-                                                                                  outertable=outertable,
 
-                                                                                  innertable=innertable + '\n' + messageList)
 
-                         )
 
-                 else:
 
-                     sql = """select advertiser_id,sum(cost) as cost from media_api.{innertable} where stat_datetime={date}
 
-                             group by advertiser_id""". \
 
-                         format(date=self.stat_date, innertable=innertable)
 
-                     sql2 = """select {stat_date} as stat_datetime,advertiser_id as advertiser_id,sum(cost) as cost from 
 
-                     `jeecg-boot`.{outertable}
 
-                     where  stat_datetime ='{date}'
 
-                     group by advertiser_id,stat_datetime""".format(date=self.date, stat_date=self.stat_date,
 
-                                                                    outertable=outertable)
 
-                     InnerAdvertiserListFrame = pd.read_sql(sql, self.InnerConn)
 
-                     OuterAdvertiserListFrame = pd.read_sql(sql2, self.OuterConn)
 
-                     data = pd.merge(OuterAdvertiserListFrame, InnerAdvertiserListFrame, how='left', on='advertiser_id')
 
-                     result = data[data['cost_x'] != data['cost_y']]
 
-                     if len(result.values.tolist()) == 0:
 
-                         feishuMsg.send_robot_msg(
 
-                             '线上{outertable}对内网{innertable} 数据相等'.format(innertable=innertable, outertable=outertable))
 
-                     else:
 
-                         messageList = ''
 
-                         for result in result.values.tolist():
 
-                             date = result[0]
 
-                             account_id = result[1]
 
-                             outerCost = result[2]
 
-                             innerCost = result[3]
 
-                             message = """广告主id={account_id} 线上消耗={outerCost},内网消耗={innerCost}""".format(date=date,
 
-                                                                                                         account_id=account_id,
 
-                                                                                                         outerCost=outerCost,
 
-                                                                                                         innerCost=innerCost)
 
-                             # print(message)
 
-                             message = message + '\n'
 
-                             messageList += message
 
-                         feishuMsg.send_robot_msg(
 
-                             """{date}线上{outertable}和内网{innertable}差异为:""".format(date=self.stat_date,
 
-                                                                                  outertable=outertable,
 
-                                                                                  innertable=innertable + '\n' + messageList)
 
-                         )
 
-             else:
 
-                 sql = """select advertiser_id,sum(charge) as cost from media_api.{innertable} where stat_date={date}
 
-                         group by advertiser_id""". \
 
-                     format(date=self.stat_date, innertable=innertable)
 
-                 # print(sql)
 
-                 sql2 = """select {stat_date} as stat_date,account_id as advertiser_id,sum(charge) as cost from 
 
-                 `jeecg-boot`.{outertable}
 
-                 where  stat_date ='{date}'
 
-                 group by account_id,stat_date""".format(date=self.date, stat_date=self.stat_date,
 
-                                                         outertable=outertable)
 
-                 InnerAdvertiserListFrame = pd.read_sql(sql, self.InnerConn)
 
-                 OuterAdvertiserListFrame = pd.read_sql(sql2, self.OuterConn)
 
-                 # print(sql2)
 
-                 data = pd.merge(OuterAdvertiserListFrame, InnerAdvertiserListFrame, how='left', on='advertiser_id')
 
-                 result = data[data['cost_x'] != data['cost_y']]
 
-                 if len(result.values.tolist()) == 0:
 
-                     feishuMsg.send_robot_msg(
 
-                         '线上{outertable}对内网{innertable} 数据相等'.format(innertable=innertable, outertable=outertable))
 
-                 else:
 
-                     messageList = ''
 
-                     for result in result.values.tolist():
 
-                         date = result[0]
 
-                         account_id = result[1]
 
-                         outerCost = result[2]
 
-                         innerCost = result[3]
 
-                         message = """广告主id={account_id} 线上消耗={outerCost},内网消耗={innerCost}""".format(date=date,
 
-                                                                                                     account_id=account_id,
 
-                                                                                                     outerCost=outerCost,
 
-                                                                                                     innerCost=innerCost)
 
-                         # print(message)
 
-                         message = message + '\n'
 
-                         messageList += message
 
-                     feishuMsg.send_robot_msg(
 
-                         """{date}线上{outertable}和内网{innertable}差异为:""".format(date=self.stat_date, outertable=outertable,
 
-                                                                              innertable=innertable + '\n' + messageList)
 
-                     )
 
- if __name__ == '__main__':
 
-     MonitorMediaApi().compare_task()
 
 
  |