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