| 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 1501435553import datetimefrom urllib import parseimport pandas as pdfrom sqlalchemy import create_enginefrom OuterDataCompareInnerData.Utils.sendMessage import feishuMsgclass 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()
 |