<?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.common.module.achievements.mapper.DesignerMapper">


    <!-- 查询设计人员基础信息 -->
    <select id="getDesignerUserInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        u.id AS userId,
        u.realname AS userName,
        u.sex AS userSex,
        IFNULL(u.frozen_time,'-') AS frozenTime,
        d.depart_name AS departName,
        r.role_name AS roleName,
        u.`status`
        FROM
        `jeecg-boot`.sys_user u
        LEFT JOIN `jeecg-boot`.sys_depart d ON u.org_code = d.org_code
        LEFT JOIN `jeecg-boot`.sys_user_role ur ON ur.user_id = u.id
        LEFT JOIN `jeecg-boot`.sys_role r ON r.id = ur.role_id
        where u.STATUS = 1 AND u.del_flag = 0
        <if test="roleCodes != null and roleCodes.size() > 0">
            and
            <foreach collection="roleCodes" item="roleCode" separator=" OR " open="(" close=")">
                r.role_name LIKE concat(#{roleCode},'%')
            </foreach>
        </if>
        <if test="userIdList != null and userIdList.size() > 0">
            and u.id in
            <foreach collection="userIdList" item="userId" separator="," open="(" close=")">
                #{userId}
            </foreach>
        </if>
        UNION
        SELECT
        u.id AS userId,
        u.realname AS userName,
        u.sex AS userSex,
        u.frozen_time AS frozenTime,
        d.depart_name AS departName,
        r.role_name AS roleName,
        u.`status`
        FROM
        `jeecg-boot`.sys_user u
        LEFT JOIN `jeecg-boot`.sys_depart d ON u.org_code = d.org_code
        LEFT JOIN `jeecg-boot`.sys_user_role ur ON ur.user_id = u.id
        LEFT JOIN `jeecg-boot`.sys_role r ON r.id = ur.role_id
        where (u.STATUS != 1 OR u.del_flag != 0)
        <if test="roleCodes != null and roleCodes.size() > 0">
            and
            <foreach collection="roleCodes" item="roleCode" separator=" OR " open="(" close=")">
                r.role_name LIKE concat(#{roleCode},'%')
            </foreach>
        </if>
        <if test="userIdList != null and userIdList.size() > 0">
            and u.id in
            <foreach collection="userIdList" item="userId" separator="," open="(" close=")">
                #{userId}
            </foreach>
        </if>
        AND (DATE_FORMAT(u.update_time,'%Y%m%d') BETWEEN #{startTime} AND #{endTime} OR
        DATE_FORMAT(u.frozen_time,'%Y%m%d') BETWEEN #{startTime} AND #{endTime})
        ORDER BY userId
    </select>


    <!-- 查询设计人员基础信息 -->
    <select id="getHotVideoData" resultType="com.alibaba.fastjson.JSONObject">
        select * FROM (
        SELECT
        DATE_FORMAT( #{month1.startTime}, '%m' ) as 'monthTime1',
        IFNULL( COUNT( h.signature ), 0 ) AS 'hotNum1'
        FROM
        application.hot_material h
        LEFT JOIN `jeecg-boot`.ctop_material_ascription v ON v.material_id = h.signature
        WHERE
        h.media_id = #{mediaType}
        AND h.hot_date &gt;= #{month1.startTime}
        AND h.hot_date &lt;= #{month1.endTime}
        <if test="roleName == 'clip'.toString()">
            AND clip_id = #{userId}
        </if>
        <if test="roleName == 'shot'.toString()">
            AND shot_id = #{userId}
        </if>
        <if test="roleName == 'plan'.toString()">
            AND plan_id = #{userId}
        </if>
        <if test="roleName == 'all'.toString()">
            AND (plan_id = #{userId} or clip_id = #{userId} or shot_id = #{userId} or leader_id = #{userId})
        </if>
        ) t1
        LEFT JOIN (
        SELECT
        DATE_FORMAT( #{month2.startTime}, '%m' ) as 'monthTime2',
        IFNULL( COUNT( h.signature ), 0 ) AS 'hotNum2'
        FROM
        application.hot_material h
        LEFT JOIN `jeecg-boot`.ctop_material_ascription v ON v.material_id = h.signature
        WHERE
        h.media_id = #{mediaType}
        AND h.hot_date &gt;= #{month2.startTime}
        AND h.hot_date &lt;= #{month2.endTime}
        <if test="roleName == 'clip'.toString()">
            AND clip_id = #{userId}
        </if>
        <if test="roleName == 'shot'.toString()">
            AND shot_id = #{userId}
        </if>
        <if test="roleName == 'plan'.toString()">
            AND plan_id = #{userId}
        </if>
        <if test="roleName == 'all'.toString()">
            AND (plan_id = #{userId} or clip_id = #{userId} or shot_id = #{userId} or leader_id = #{userId})
        </if>
        )t2 ON 1=1
        LEFT JOIN (
        SELECT
        DATE_FORMAT( #{month3.startTime}, '%m' ) as 'monthTime3',
        IFNULL( COUNT( h.signature ), 0 ) AS 'hotNum3'
        FROM
        application.hot_material h
        LEFT JOIN `jeecg-boot`.ctop_material_ascription v ON v.material_id = h.signature
        WHERE
        h.media_id = #{mediaType}
        AND h.hot_date &gt;= #{month3.startTime}
        AND h.hot_date &lt;= #{month3.endTime}
        <if test="roleName == 'clip'.toString()">
            AND clip_id = #{userId}
        </if>
        <if test="roleName == 'shot'.toString()">
            AND shot_id = #{userId}
        </if>
        <if test="roleName == 'plan'.toString()">
            AND plan_id = #{userId}
        </if>
        <if test="roleName == 'all'.toString()">
            AND (plan_id = #{userId} or clip_id = #{userId} or shot_id = #{userId} or leader_id = #{userId})
        </if>
        )t3 ON 1=1

    </select>


    <!-- 查询设计人员 头条绩效-->
    <select id="getDesignerAchievementsBytedance" resultType="com.alibaba.fastjson.JSONObject">
        SELECT QUARTER(d.first_cost_day)                           quaterTime,
               DATE_FORMAT(d.first_cost_day, '%Y')                 yearTime,
               DATE_FORMAT(d.first_cost_day, '%m')                 monthTime,
               IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
               '0.3'                                            AS coefficient,
               IFNULL(round((sum(d.cost) * 0.002 * 0.3), 3), 0) AS commission,
               'clip'                                           AS type
        FROM application.bytedance_material_cost_daily d
        WHERE clip_id = #{userId}
          AND d.stat_date &gt;= #{startTime}
          AND d.stat_date &lt;= #{endTime}
        UNION ALL
        SELECT QUARTER(d.first_cost_day)                           quaterTime,
               DATE_FORMAT(d.first_cost_day, '%Y')                 yearTime,
               DATE_FORMAT(d.first_cost_day, '%m')                 monthTime,
               IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
               '0.1'                                            AS coefficient,
               IFNULL(round((sum(d.cost) * 0.002 * 0.1), 3), 0) AS commission,
               'shot'                                           AS type
        FROM application.bytedance_material_cost_daily d
        WHERE shot_id = #{userId}
          AND d.stat_date &gt;= #{startTime}
          AND d.stat_date &lt;= #{endTime}
        UNION ALL
        SELECT QUARTER(d.first_cost_day)                           quaterTime,
               DATE_FORMAT(d.first_cost_day, '%Y')                 yearTime,
               DATE_FORMAT(d.first_cost_day, '%m')                 monthTime,
               IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
               '0.4'                                            AS coefficient,
               IFNULL(round((sum(d.cost) * 0.002 * 0.4), 3), 0) AS commission,
               'plan'                                           AS type
        FROM application.bytedance_material_cost_daily d
        WHERE plan_id = #{userId}
          AND d.stat_date &gt;= #{startTime}
          AND d.stat_date &lt;= #{endTime}
    </select>


    <!-- 查询设计人员 快手绩效-->
    <select id="getDesignerAchievementsKuaishou" resultType="com.alibaba.fastjson.JSONObject">
        SELECT QUARTER(d.first_cost_day)                             quaterTime,
               DATE_FORMAT(d.first_cost_day, '%Y')                   yearTime,
               DATE_FORMAT(d.first_cost_day, '%m')                   monthTime,
               IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
               '0.3'                                              AS coefficient,
               IFNULL(round((sum(d.charge) * 0.003 * 0.3), 3), 0) AS commission,
               'clip'                                             AS type
        FROM application.kuaishou_material_cost_daily d
        WHERE clip_id = #{userId}
          AND d.stat_date &gt;= #{startTime}
          AND d.stat_date &lt;= #{endTime}
        UNION ALL
        SELECT QUARTER(d.first_cost_day)                             quaterTime,
               DATE_FORMAT(d.first_cost_day, '%Y')                   yearTime,
               DATE_FORMAT(d.first_cost_day, '%m')                   monthTime,
               IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
               '0.1'                                              AS coefficient,
               IFNULL(round((sum(d.charge) * 0.003 * 0.1), 3), 0) AS commission,
               'shot'                                             AS type
        FROM application.kuaishou_material_cost_daily d
        WHERE shot_id = #{userId}
          AND d.stat_date &gt;= #{startTime}
          AND d.stat_date &lt;= #{endTime}
        UNION ALL
        SELECT QUARTER(d.first_cost_day)                             quaterTime,
               DATE_FORMAT(d.first_cost_day, '%Y')                   yearTime,
               DATE_FORMAT(d.first_cost_day, '%m')                   monthTime,
               IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
               '0.4'                                              AS coefficient,
               IFNULL(round((sum(d.charge) * 0.003 * 0.4), 3), 0) AS commission,
               'plan'                                             AS type
        FROM application.kuaishou_material_cost_daily d
        WHERE plan_id = #{userId}
          AND d.stat_date &gt;= #{startTime}
          AND d.stat_date &lt;= #{endTime}
    </select>

    <!-- 查询设计 本季度 头条季度消耗汇总-->
    <select id="getDesignerBytedanceQuaterCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT QUARTER(#{startTime}) AS 'quaterTime',
            YEAR (
               #{startTime}) AS 'yearTime',
            IFNULL(ROUND(SUM(d.cost), 2), 0) AS 'cost',
            'byteDance' AS 'type'
        FROM
            application.bytedance_material_cost_daily d
        WHERE
            (
            d.clip_id = #{userId}
           OR d.shot_id = #{userId}
           OR d.plan_id = #{userId}
            )
          AND stat_date &gt;= #{startTime}
          AND stat_date &lt;= #{endTime}
    </select>
    <!-- 查询设计 本季度 快手季度消耗汇总-->
    <select id="getDesignerKuaishouQuaterCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT QUARTER(#{startTime}) AS 'quaterTime',
            YEAR (
               #{startTime}) AS 'yearTime',
            IFNULL(ROUND(SUM(d.charge), 2), 0) AS 'cost',
            'kuaiShou' AS 'type'
        FROM
            application.kuaishou_material_cost_daily d
        WHERE
            (
            d.clip_id = #{userId}
           OR d.shot_id = #{userId}
           OR d.plan_id = #{userId}
            )
          AND stat_date &gt;= #{startTime}
          AND stat_date &lt;= #{endTime}
    </select>

    <!-- 管理消耗-->
    <select id="getDesignerManagerLeaderCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
                 SELECT IFNULL(SUM(cost), 0) cost, leader_id, 'bytedance' as type, 1 as sort
                 FROM application.bytedance_material_cost_daily
                 WHERE leader_id = #{userId}
                   AND stat_date &gt;= #{startTime}
                   AND stat_date &lt;= #{endTime}
                 UNION
                 SELECT IFNULL(SUM(charge), 0) cost, leader_id, 'kuaishou' as type, 2 as sort
                 FROM application.kuaishou_material_cost_daily
                 WHERE leader_id = #{userId}
                   AND stat_date &gt;= #{startTime}
                   AND stat_date &lt;= #{endTime}
             ) t
        ORDER BY t.sort
    </select>


    <!-- 查询人员离职时间-->
    <select id="getUserFrozenInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT u.id                         userId,
               u.username                   userName,
               u.realname                   realName,
               u.create_time                createTime,
               u.frozen_time                frozenTime,
               IFNULL(u.open_screen_num, 0) openHotVideoNum,
               IFNULL(u.pay_salary, 0)      paySalary,
               u.remarks,
               u.status
        FROM `jeecg-boot`.sys_user u
        WHERE u.id = #{userId} limit 1
    </select>


    <!-- 查询人员 当前季度爆款素材数量 -->
    <select id="getUserHotMaterialQuarter" resultType="com.alibaba.fastjson.JSONObject">
        SELECT COUNT(h.signature) hotVideoNum
        FROM application.hot_material h
                 LEFT JOIN `jeecg-boot`.ctop_material_ascription v ON v.material_id = h.signature
        WHERE (v.plan_id = #{userId}
            OR v.clip_id = #{userId}
            or v.shot_id = #{userId}
            or v.leader_id = #{userId}
            )
          AND h.hot_date &gt;= #{startTime}
          AND h.hot_date &lt;= #{endTime}

    </select>


    <update id="updateUserHappyProbability">
        UPDATE `jeecg-boot`.sys_user
        <set>
            <if test="openScreenNum != null">
                open_screen_num = #{openScreenNum},
            </if>
            <if test="paySalary != null">
                pay_salary = #{paySalary},
            </if>
            <if test="remarks != null and remarks !=''">
                remarks = #{remarks},
            </if>
        </set>
        WHERE
        id = #{userId}
    </update>


    <!-- 根据角色查询人员 -->
    <select id="getUserByRoleName" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        u.id AS userId,
        u.realname AS userName,
        u.sex AS userSex,
        u.frozen_time AS frozenTime,
        d.depart_name AS departName,
        r.role_name AS roleName,
        u.`status`
        FROM
        `jeecg-boot`.sys_user u
        LEFT JOIN `jeecg-boot`.sys_depart d ON u.org_code = d.org_code
        LEFT JOIN `jeecg-boot`.sys_user_role ur ON ur.user_id = u.id
        LEFT JOIN `jeecg-boot`.sys_role r ON r.id = ur.role_id
        <where>
            <choose>
                <when test="roleName != null and roleName != ''">
                    and r.role_name LIKE concat('%',#{roleName},'%') or r.role_code LIKE concat('%',#{roleName},'%')
                </when>
                <otherwise>
                    and (r.role_name LIKE '%平面组长%'
                    or r.role_name LIKE '%设计组长%'
                    or r.role_name LIKE '%制片%'
                    or r.role_name LIKE '%平面%'
                    or r.role_name LIKE '%策划%'
                    or r.role_name LIKE '%拍摄%'
                    or r.role_name LIKE '%剪辑%' )
                </otherwise>
            </choose>
        </where>
    </select>


    <select id="getQuarterDesignerAchievementsByTedance" resultType="com.alibaba.fastjson.JSONObject">
        select *
        FROM (
                 SELECT DATE_FORMAT(#{month1.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.3'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.3), 3), 0) AS commission,
                        'clip'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE clip_id = #{userId}
                   AND d.stat_date &gt;= #{month1.startTime}
                   AND d.stat_date &lt;= #{month1.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month1.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.1'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.1), 3), 0) AS commission,
                        'shot'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE shot_id = #{userId}
                   AND d.stat_date &gt;= #{month1.startTime}
                   AND d.stat_date &lt;= #{month1.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month1.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.4'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.4), 3), 0) AS commission,
                        'plan'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE plan_id = #{userId}
                   AND d.stat_date &gt;= #{month1.startTime}
                   AND d.stat_date &lt;= #{month1.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month2.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.3'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.3), 3), 0) AS commission,
                        'clip'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE clip_id = #{userId}
                   AND d.stat_date &gt;= #{month2.startTime}
                   AND d.stat_date &lt;= #{month2.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month2.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.1'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.1), 3), 0) AS commission,
                        'shot'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE shot_id = #{userId}
                   AND d.stat_date &gt;= #{month2.startTime}
                   AND d.stat_date &lt;= #{month2.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month2.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.4'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.4), 3), 0) AS commission,
                        'plan'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE plan_id = #{userId}
                   AND d.stat_date &gt;= #{month2.startTime}
                   AND d.stat_date &lt;= #{month2.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month3.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.3'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.3), 3), 0) AS commission,
                        'clip'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE clip_id = #{userId}
                   AND d.stat_date &gt;= #{month3.startTime}
                   AND d.stat_date &lt;= #{month3.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month3.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.1'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.1), 3), 0) AS commission,
                        'shot'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE shot_id = #{userId}
                   AND d.stat_date &gt;= #{month3.startTime}
                   AND d.stat_date &lt;= #{month3.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month3.startTime}, '%m')              monthTime,
                        IFNULL(ROUND(SUM(d.cost), 2), 0)                 AS cost,
                        '0.4'                                            AS coefficient,
                        IFNULL(round((sum(d.cost) * 0.002 * 0.4), 3), 0) AS commission,
                        'plan'                                           AS type
                 FROM application.bytedance_material_cost_daily d
                 WHERE plan_id = #{userId}
                   AND d.stat_date &gt;= #{month3.startTime}
                   AND d.stat_date &lt;= #{month3.endTime}
             ) t
        order by monthTime, type
    </select>


    <!-- 查询设计人员 季度快手绩效-->
    <select id="getQuarterDesignerAchievementsByKuaishou" resultType="com.alibaba.fastjson.JSONObject">
        select *
        FROM (
                 SELECT DATE_FORMAT(#{month1.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.3'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.3), 3), 0) AS commission,
                        'clip'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE clip_id = #{userId}
                   AND d.stat_date &gt;= #{month1.startTime}
                   AND d.stat_date &lt;= #{month1.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month1.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.1'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.1), 3), 0) AS commission,
                        'shot'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE shot_id = #{userId}
                   AND d.stat_date &gt;= #{month1.startTime}
                   AND d.stat_date &lt;= #{month1.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month1.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.4'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.4), 3), 0) AS commission,
                        'plan'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE plan_id = #{userId}
                   AND d.stat_date &gt;= #{month1.startTime}
                   AND d.stat_date &lt;= #{month1.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month2.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.3'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.3), 3), 0) AS commission,
                        'clip'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE clip_id = #{userId}
                   AND d.stat_date &gt;= #{month2.startTime}
                   AND d.stat_date &lt;= #{month2.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month2.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.1'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.1), 3), 0) AS commission,
                        'shot'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE shot_id = #{userId}
                   AND d.stat_date &gt;= #{month2.startTime}
                   AND d.stat_date &lt;= #{month2.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month2.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.4'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.4), 3), 0) AS commission,
                        'plan'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE plan_id = #{userId}
                   AND d.stat_date &gt;= #{month2.startTime}
                   AND d.stat_date &lt;= #{month2.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month3.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.3'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.3), 3), 0) AS commission,
                        'clip'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE clip_id = #{userId}
                   AND d.stat_date &gt;= #{month3.startTime}
                   AND d.stat_date &lt;= #{month3.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month3.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.1'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.1), 3), 0) AS commission,
                        'shot'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE shot_id = #{userId}
                   AND d.stat_date &gt;= #{month3.startTime}
                   AND d.stat_date &lt;= #{month3.endTime}
                 UNION
                 SELECT DATE_FORMAT(#{month3.startTime}, '%m')                monthTime,
                        IFNULL(ROUND(SUM(d.charge), 2), 0)                 AS cost,
                        '0.4'                                              AS coefficient,
                        IFNULL(round((sum(d.charge) * 0.003 * 0.4), 3), 0) AS commission,
                        'plan'                                             AS type
                 FROM application.kuaishou_material_cost_daily d
                 WHERE plan_id = #{userId}
                   AND d.stat_date &gt;= #{month3.startTime}
                   AND d.stat_date &lt;= #{month3.endTime}
             ) t
        order by monthTime, type
    </select>

</mapper>