<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.ctop.toutiao.modules.link.mapper.ETLDesignDailyReportMapper">

    <select id="queryMaterialCostGroupMd5ByStatDate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            sum(cost) cost,
            clip_id clipId,
            plan_id planId,
            shot_id shotId
        FROM
            etl_report_bytedance_video
        WHERE
            stat_datetime = #{statDate}
          AND cover_url!=''
        GROUP BY md5 HAVING sum(cost)>0
        union all
        SELECT
            t1.cost,
            t2.clip_id clipId,
            t2.plan_id planId,
            t2.shot_id shotId
        FROM
            (select sum(charge) cost,
                    t1.signature
             from ctop_etl_kuaishou_account_material_report_daily t1
             where
                 t1.stat_date = #{statDate}
             GROUP BY t1.signature HAVING  sum(charge)>0
            ) t1
                LEFT JOIN ctop_etl_kuaishou_video_info t2
                          ON t1.signature = t2.video_code
        where t2.clip_id  is not null
    </select>

    <select id="queryAllDesign" resultType="cn.com.ctop.toutiao.modules.link.entity.EtlDesignDailyReport">
        SELECT
            t1.id as userId,
            t1.realname as userName,
            t5.role_name as roleName,
            t1.leader_id,
            t1.leader_name,
            t3.id as depId,
            t3.depart_name as depName,
            #{statDate} as statDate
        FROM
            sys_user t1
                LEFT JOIN sys_user_depart t2 on t1.id=t2.user_id
                LEFT JOIN sys_depart t3 on t2.dep_id=t3.id
                LEFT JOIN sys_user_role t4 on t1.id=t4.user_id
                LEFT JOIN sys_role t5 on t4.role_id=t5.id
-- where t5.role_name in ('设计组长','剪辑','策划')
        where t3.depart_name like '快手设计%'
           or t3.depart_name like '设计-%'
           or t3.depart_name like '头条设计%'
            and t1.leader_id is not null
            and t1.`status`=1
            and t1.del_flag=0
    </select>

    <insert id="insertBatch">
        insert into etl_design_daily_report(
        user_id,
        user_name,
        cost,
        stat_date,
        role_name,
        leader_id,
        leader_name,
        dep_id,
        dep_name
        )
        values
        <foreach collection="records" item="record" separator=",">
            (#{record.userId},
            #{record.userName},
            #{record.cost},
            #{record.statDate},
            #{record.roleName},
            #{record.leaderId},
            #{record.leaderName},
            #{record.depId},
            #{record.depName}
            )
        </foreach>
    </insert>

</mapper>