| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384 | # Author renyupeng# coding=utf-8# @Time    : 2023/8/9 上午11:22# @Site    :# @File    : project_material_report.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import datetimefrom urllib import parseimport pandas as pdfrom sqlalchemy import create_enginefrom Apietl.conn.TidbConn import TidbConnclass project_material_report:    def __init__(self):        self.conn = TidbConn.get_connection()        self.OuterStr = "mysql+pymysql://%s:%s@%s:%d/%s" % (            "hcst", parse.quote_plus("hcst@2024"), "192.168.0.101", 3390, "application")        self.OuterConn = create_engine(self.OuterStr,                                       pool_size=10,                                       max_overflow=5,                                       pool_timeout=10,                                       pool_recycle=3600)    def Project_Handler(self):        sql = """        select project_id,replace(project_name,'/','-') from application.kuaishou_account_report_daily_dw  where  stat_date>=20240401 and stat_date<=20240631and charge>0 and project_id is not nullgroup by project_id        """        project_info_list = TidbConn.quary(self.conn, sql)        for project_info in project_info_list:            project_id = project_info[0]            project_name = project_info[1]            sql = """            select (CASE    WHEN a.clip_company_id = 'd57fecdcf7a94d009736d9c850731582' THEN'北京汇创思拓数字科技有限公司'WHEN a.clip_company_id = '6608ed13c0dd42de93c790dbdf124234' THEN'广州汇创思拓数字科技有限公司'WHEN a.clip_company_id = '4b10089775c040119e139087517aed88' THEN'上海汇创思拓数字科技有限公司'WHEN a.clip_company_id = '5e46b5dea3d740eeb8ff1a2895015a4b' THEN'北京骄阳数字科技有限公司'WHEN a.clip_company_id = '1648c7ceb4d449a9a13fbed7a8d9976c' THEN'杭州妙构思数字科技有限公司'WHEN a.clip_company_id = '3321d5893bcd430cab1238a99bc7db11' THEN'北京次元像素' WHEN a.clip_company_id='ace886fa51a244818e918b5d6d1bcc62' then    '北京聚意创量文化传媒有限公司'     when a.clip_company_id='3f362f527a804e8a9db6552ea12f7574'      then '石家庄汇创思拓数字科技有限公司'else '-'END) as '公司',       a.project_name AS '项目',       a.signature   AS '素材标识',       b.frast_cost_time AS '上线时间',       channel_name 渠道,       a.charge AS '消耗',       trim(a.clip_name) AS '剪辑',       trim(a.shot_name) AS '拍摄',       trim(a.plan_name) AS '编导',       trim(a.leader_name) AS '负责人',       a.video_name AS '素材名称',       a.photo_url  AS '链接' from(select clip_company_id,account_id,company_name,project_name,signature,channel_name,sum(charge)charge,clip_name,shot_name,plan_name,a.leader_name,video_name,photo_url,b.realname user_name fromapplication.kuaishou_material_video_report_daily_dw aleft join `jeecg-boot`.sys_user bon a.user_id=b.idwhere stat_date>=20240401 and stat_date<=20240631 and project_id={project_id}group by signature) aleft joinapplication.material_farst_cost_time bon a.signature=b.signature            """.format(project_id=project_id)            path = "/home/excel/ks/{project_name}.xls".format(project_name=project_name)            pd.read_sql_query(sql, self.OuterConn).to_excel(path, index=False, engine='openpyxl')if __name__ == '__main__':    project_material_report().Project_Handler()
 |