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()
|