# 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 1501435553 import datetime from urllib import parse import pandas as pd from sqlalchemy import create_engine from Apietl.conn.TidbConn import TidbConn class 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,project_name from application.bytedance_advertiser_report_daily_dw where stat_datetime>=20240401 and stat_datetime<=20240431 and cost>0 and project_id is not null group 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 '上线时间', '' 渠道, 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.video_url AS '链接' from (select clip_company_id,account_id,company_name,project_name,signature,channel_name,sum(cost)charge,clip_name,shot_name,plan_name,a.leader_name,video_name,video_url,b.realname user_name from application.bytedance_material_video_report_daily_dw a left join `jeecg-boot`.sys_user b on a.user_id=b.id where stat_datetime >= 20240401 and stat_datetime<=20240631 and project_id={project_id} group by signature) a left join application.bytedance_material_farst_cost_time b on a.signature=b.signature """.format(project_id=project_id) path = "/home/excel/toutiao/{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()