#1.快手-近一周素材消耗全明细(线上)
#快手素材周报
SELECT
        ifnull(t2.company_name,'0') AS '公司',
        ifnull(t1.project_name,'-') AS '项目',
        ifnull(t2.video_code,'-') AS '素材标识',
        ifnull(t2.state_date,'-') AS '上线时间',
        ifnull(t2.channel_name,'-') AS '渠道',
        round(sum(t1.charge),3) AS '消耗',
        ifnull(t2.clip_name,'-') AS '剪辑',
        ifnull(t2.shot_name,'-') AS '拍摄',
        ifnull(t2.plan_name,'-') AS '编导',
        ifnull(t2.design_team_leader_name,'-') AS '负责人',
        ifnull(t2.video_name,'-') AS '素材名称',
        ifnull(t2.url,'-') AS '链接'
FROM
ctop_etl_kuaishou_account_material_report_daily t1
LEFT JOIN ctop_etl_kuaishou_video_info t2 ON t1.signature = t2.video_code
 where t1.stat_date between date_sub(${stat_date}, INTERVAL 6 DAY)  and ${stat_date}
GROUP BY
t1.signature;
#2.头条-近一周素材消耗全明细(线上)
SELECT
	ifnull(etlInfo.company_name,'0') AS '公司',
	ifnull(allocation.project_name,'-') AS '所属项目',
	ifnull(allocation.project_id,'-') AS '项目id',
	ifnull(video.signature,'-') AS '唯一标识',
	ifnull(round(SUM(report.cost),3),'-') AS '总消耗',
	ifnull(etlInfo.state_date,'-') AS '上线日期',
	ifnull(allocation.user_name,'-') AS '所属运营',
	ifnull(etlInfo.clip_name,'-') AS '剪辑',
	ifnull(etlInfo.plan_name,'-') AS '策划',
	ifnull(etlInfo.shot_name,'-') AS '拍摄',
	'-' AS '平面',
	ifnull(video.video_url,'-') AS '素材链接',
	ifnull(report.material_id,'-') AS '素材id',
	ifnull(mm.realname,'-') AS '负责人',
	'视频' AS '素材类型',
	ifnull(allocation.project_media_id,'-') AS '是否内广',
	ifnull(etlInfo.video_type,'-') AS '视频类型'
FROM
	`jeecg-boot`.ctop_bytedance_report_material_daily report
	LEFT JOIN ( SELECT material_id, account_id, video_url, signature FROM `jeecg-boot`.ctop_bytedance_video_info GROUP BY account_id, material_id ) video ON video.material_id = report.material_id
	AND report.account_id = video.account_id
	LEFT JOIN `jeecg-boot`.ctop_bytedance_video_etl_info etlInfo ON etlInfo.video_code = video.signature
	LEFT JOIN `jeecg-boot`.ctop_material_ascription u ON u.material_id = video.signature
	LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id
  left join  `jeecg-boot`.sys_user mm on u.leader_id=mm.id
WHERE
	report.stat_datetime between  date_sub(${stat_date}, INTERVAL 6 DAY)
GROUP BY
	report.material_id
UNION ALL
SELECT
	ifnull(uc.company_name,'0') AS '公司',
	allocation.project_name AS '所属项目',
  ifnull(allocation.project_id,'-') AS '项目id',
	video.signature AS '唯一标识',
	SUM( report.cost ) AS 'Cost',
	report.stat_datetime AS '日期',
	allocation.user_name AS '所属运营',
	'-' AS '剪辑',
	'-' AS '策划',
	'-' AS '拍摄',
	'-' AS '平面',
	video.image_url AS '素材链接',
	report.material_id AS '素材id',
	'-' AS '负责人',
	'图片' AS '素材类型',
	allocation.project_media_id AS '是否内广',
	'-' AS '视频类型'
FROM
	`jeecg-boot`.ctop_bytedance_report_image_daily report
	LEFT JOIN `jeecg-boot`.ctop_bytedance_image_info video ON video.material_id = report.material_id
	AND report.account_id = video.account_id
	LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id
	LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = allocation.user_id
WHERE
	report.stat_datetime between  date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
group by video.signature;

#3.快手-近14天上线素材消耗全明细(线上)
SELECT
	( CASE WHEN t2.company_name IS NOT NULL THEN t2.company_name ELSE '-' END ) '公司',
	t1.project_name AS '项目',
	t2.video_code AS '素材标识',
	( CASE WHEN t3.material_innovate = 1 THEN '非创新素材' WHEN t3.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材',
	t2.state_date AS '上线时间',
	t2.channel_name AS '渠道',
	sum( t1.charge ) AS '消耗',
	t2.clip_name AS '剪辑',
	t2.shot_name AS '拍摄',
	t2.plan_name AS '编导',
	t2.design_team_leader_name AS '负责人',
	t2.video_name AS '素材名称',
	t2.url AS '素材'
FROM
	`jeecg-boot`.ctop_etl_kuaishou_account_material_report_daily t1
	LEFT JOIN `jeecg-boot`.ctop_etl_kuaishou_video_info t2 ON t1.signature = t2.video_code
	LEFT JOIN `jeecg-boot`.ctop_material_info t3 ON t1.signature = t3.`code`
WHERE
	t2.state_date >= date_sub(${stat_date}, INTERVAL 13 DAY)
	AND t2.state_date <= ${stat_date}
GROUP BY
	t1.signature;

#4.头条-近14天上线素材消耗全明细（线上）
#头条
SELECT
	( SELECT company_name FROM `jeecg-boot`.user_company WHERE user_id = ( SELECT id FROM `jeecg-boot`.sys_user WHERE realname = t1.clip_name ) ) AS '公司',
	t1.project_name AS '项目',
	t1.md5 AS '素材标识',
	( CASE WHEN t2.material_innovate = 1 THEN '非创新素材' WHEN t2.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材',
	t1.material_create_time AS '上线日期',
	sum( t1.cost ) AS '消耗',
	t1.clip_id AS 'id',
	t1.clip_name AS '剪辑',
	t1.shot_name AS '拍摄',
	t1.plan_name AS '编导',
	( SELECT leader_name FROM `jeecg-boot`.sys_user WHERE realname = t1.clip_name ) AS '负责人',
	t1.url AS '素材'
FROM
	`jeecg-boot`.etl_report_bytedance_video t1
	LEFT JOIN `jeecg-boot`.ctop_material_info t2 ON t1.md5 = t2.`code`
WHERE
	date(t1.material_create_time) >= date_sub(${stat_date}, INTERVAL 13 DAY)
	AND date(t1.material_create_time)<= ${stat_date}
GROUP BY
	t1.md5;

#5.快手-近一周有效素材全明细（线上）
SELECT
	t1.company_name AS '公司',
	round(t1.single_week_cost,3) AS '上线一周消耗',
	--  total_cost as '总消耗',
	ifnull(t1.clip_name,'-') AS '剪辑',
	ifnull(t1.shot_name,'-') AS '拍摄',
	ifnull(t1.plan_name,'-') AS '编导',
  ifnull(t2.leader_name,'-') as '负责人',
	ifnull(t1.url,'-') AS '素材链接'
FROM
	`jeecg-boot`.ctop_kuaishou_video_etl_info t1
		LEFT JOIN  `jeecg-boot`.ctop_material_ascription t2
	on t1.video_code = t2.material_id
WHERE
		t1.effect_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
 AND t1.effect_date IS NOT NULL
AND t1.effect_day_num <= 7;


#6.快手-近一周爆款素材全明细
SELECT
	t1.company_name AS '公司',
	round(t1.two_week_cost,3) AS '双周消耗',
	--  total_cost as '总消耗',
  t1.state_date as '上线日期',
	ifnull(t1.clip_name,'-') AS '剪辑',
	ifnull(t1.shot_name,'-') AS '拍摄',
	ifnull(t1.plan_name,'-') AS '编导',
  ifnull(t3.realname,'-') as '负责人',
	ifnull(t1.url,'-') AS '素材链接'
FROM
	`jeecg-boot`.ctop_kuaishou_video_etl_info t1
		LEFT JOIN  `jeecg-boot`.ctop_material_ascription t2
	on t1.video_code = t2.material_id
   left join `jeecg-boot`.sys_user t3
on t2.leader_id=t3.id
  WHERE
		t1.faddish_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
 AND t1.faddish_date IS NOT NULL
AND t1.faddish_day_num <=14;

#7.头条-近一周有效素材全明细
SELECT
	t1.company_name AS '公司',
	round(t1.single_week_cost,3) AS '上线一周消耗',
	--  total_cost as '总消耗',
	ifnull(t1.clip_name,'-') AS '剪辑',
	ifnull(t1.shot_name,'-') AS '拍摄',
	ifnull(t1.plan_name,'-') AS '编导',
  ifnull(t2.leader_name,'-') as '负责人',
	ifnull(t1.url,'-') AS '素材链接'
FROM
	`jeecg-boot`.ctop_bytedance_video_etl_info t1
LEFT JOIN  `jeecg-boot`.ctop_material_ascription t2
	on t1.video_code = t2.material_id
WHERE
	t1.effect_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
AND t1.effect_date IS NOT NULL
AND t1.effect_day_num <= 7;


#8.头条-近一周爆款素材全明细
# 头条爆款(线上)
SELECT
	t1.company_name AS '公司',
	round(t1.two_week_cost,3) AS '上线两周消耗',
	--  total_cost as '总消耗',
	ifnull(t1.clip_name,'-') AS '剪辑',
	ifnull(t1.shot_name,'-') AS '拍摄',
	ifnull(t1.plan_name,'-') AS '编导',
  ifnull(t2.leader_name,'-') as '负责人',
	ifnull(t1.url,'-') AS '素材链接'
FROM
	`jeecg-boot`.ctop_bytedance_video_etl_info t1
LEFT JOIN  `jeecg-boot`.ctop_material_ascription t2
	on t1.video_code = t2.material_id
WHERE
	t1.faddish_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
AND t1.faddish_date IS NOT NULL
AND t1.faddish_day_num <= 14;
