| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384 | # Author zhaohailiang# coding=utf-8# @Time    : 2022/01/19 21:10 下午# @Site    :# @File    : AppKuaishouAccountTodayBaseInfoD.py# @Software: PyCharm# @contact: zhaohailiang@c-top.com.cn# @Tel 18201631162import datetimeimport loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass AppKuaishouAccountTodayBaseInfoD:    def __init__(self):        self.conn = TidbConn.get_connection()        self.date_time = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")    def taskHandler(self):        try:            sql = """replace into application.app_kuaishou_account_today_base_info_d(stat_date, hour, project_id, account_id, user_id,                                                                user_name, leader_id, leader_name, new_all_unit_num,                                                                new_unit_num, new_trush_unit_num, account_name,                                                                project_name)select stat_date,       hour,       project_id,       a.advertiser_id,       info.user_id,       user.realname as user_name,       user.leader_id,       leader.realname,       all_unit_num,       unit_num,       trush_unit_num,       auth_name     as acount_name,       pro.project_namefrom (select stat_date,             advertiser_id,             hour,             count(distinct unit_id) as all_unit_num,             sum(unit_num)           as unit_num,             sum(trush_unit_num)     as trush_unit_num      from (               select date_format(create_time, '%Y%m%d') as stat_date,                      advertiser_id,                      unit_id,                      create_time,                      hour(create_time)                  as hour,                      if(unit_source = 0, 1, 0)          as unit_num,                      if(unit_source = 1, 1, 0)          as trush_unit_num               from media_api.kuaishou_ad_unit_list               where date_format(create_time, '%Y%m%d') = {date_time}) t      group by advertiser_id, hour) a         left join `jeecg-boot`.ctop_user_allocation info                   on a.advertiser_id = info.account_id         left join `jeecg-boot`.ctop_project pro                   on info.project_id = pro.id         left join `jeecg-boot`.sys_user user                   on info.user_id = user.id         left join `jeecg-boot`.sys_user leader                   on user.leader_id = leader.id            """.format(date_time=self.date_time)            print(sql)            TidbConn.upsert(conn=self.conn, sql_buff=sql)            self.conn.commit()            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = AppKuaishouAccountTodayBaseInfoD().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |