<?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.material.mapper.FirstDeliveryValidMapper">

    <delete id="delMaterials">
        delete
        from first_delivery_valid_materials
        where delivery_year = #{deliveryYear}
          AND delivery_month = #{deliveryMonth}
    </delete>
    <select id="projectData" resultType="com.alibaba.fastjson.JSONObject">
        select t3.*,
               ifnull(t4.selfEffCount,0) as 'selfEffCount',
               concat(round( ifnull(t4.selfEffCount,0) / t3.effCount * 100, 2), '%') as 'selfEffRate', round(t3.effCount -  ifnull(t4.selfEffCount,0)) as 'thirdEffCount'
        from (
                 (select t1.*,
                         round(t1.effCount / t1.materialNum * 100, 2) as 'effRate', round(t2.target_value * 100, 2) as 'mediaEffRate', (
                             case
                                 when
                                     round(t1.effCount / t1.materialNum * 100, 2) >= round(t2.target_value * 100, 2)
                                     then 1
                                 else 2
                                 end
                             ) as 'color'
                  from (select project_id as 'projectId', project_name as 'projectName', replace(project_design_leader_name, '（石家庄）', '') as 'dessLeaderName', replace(replace(project_leader_name, '（头条总监）', ''), '（汇创）', '') as 'projectLeaderName', rebate_type as 'rebateType', settlement_1l as 'settlement1l', settlement_2l as 'settlement2l', count(material_id) as 'materialNum', ifnull
                      (
                      sum(
                              (
                                  case
                                      when first_valid = '是'
                                          then 1
                                      else
                                          0 end
                                  )
                      ) , 0 ) as 'effCount'
                        from etl_first_delivery_valid_materials
                        where delivery_year = #{year}
                          and delivery_month = #{month}
                        group by project_id, rebate_type, settlement_1l, settlement_2l) t1
                           left join (select *
                                      from first_delivery_valid_ratio
                                      where delivery_year = #{year}
                                        and delivery_month = #{month}) t2
                                     on t1.settlement2l = t2.settlement_2l)) t3
                 left join (select project_id as 'projectId', settlement_2l as 'settlement2l', ifnull(
            sum(
                    (
                        case
                            when first_valid = '是'
                                then 1
                            else
                                0 end
                        )
            ) ,0 ) as 'selfEffCount'
                            from etl_first_delivery_valid_materials
                            where delivery_year = #{year}
                              and delivery_month = #{month}
                              and clip_id is not null
                            group by project_id, settlement_2l) t4
                           on t3.projectId = t4.projectId and t3.settlement2l = t4.settlement2l
        order by t3.materialNum desc
    </select>
    <select id="projectDataTotal" resultType="java.lang.Integer">
        select count(1)
        from (select project_id
              from etl_first_delivery_valid_materials
              where delivery_year = #{year}
                and delivery_month = #{month}
              group by project_id, settlement_2l) t

    </select>
    <select id="getTotalDataSum" resultType="java.lang.Integer">
        select count(1)
        from first_delivery_valid_ratio
        where delivery_year = #{year}
          and delivery_month = #{month}
    </select>
    <select id="getTotalData" resultType="com.alibaba.fastjson.JSONObject">
        select *
        from first_delivery_valid_ratio
        where delivery_year = #{year}
          and delivery_month = #{month}

    </select>

    <insert id="replaceBatchMaterials">
        insert into first_delivery_valid_materials(
        delivery_year,
        delivery_month,
        operation_label,
        material_id,
        account_id,
        stat_date,
        account_name,
        ad_ids,
        customer_name,
        user_name,
        rebate_type,
        settlement_1l,
        settlement_2l,
        first_valid,
        cost_7day,
        cost
        )
        values
        <foreach collection="list" item="li" separator=",">
            (
            #{li.deliveryYear},
            #{li.deliveryMonth},
            #{li.operationLabel},
            #{li.materialId},
            #{li.accountId},
            #{li.statDate},
            #{li.accountName},
            #{li.adIds},
            #{li.customerName},
            #{li.userName},
            #{li.rebateType},
            #{li.settlement1l},
            #{li.settlement2l},
            #{li.firstValid},
            #{li.cost7day},
            #{li.cost}
            )
        </foreach>
    </insert>

    <delete id="delRatio">
        delete
        from first_delivery_valid_ratio
        where delivery_year = #{deliveryYear}
          AND delivery_month = #{deliveryMonth}
    </delete>

    <insert id="replaceBatchRatio">
        replace into first_delivery_valid_ratio(
        delivery_year,
        delivery_quarter,
        delivery_month,
        agent_combine,
        bid_type,
        settlement_type,
        settlement_2l,
        operation_label,
        first_delivery_month_materials,
        first_delivery_month_valid_materials,
        valid_material_ratio,
        target_value,
        rebate_ratio,
        full_dot_dif,
        rebate_ratio_ceiling,
        cost,
        rebate_cost,
        full_dot_dif_cost
        )
        values
        <foreach collection="list" item="li" separator=",">
            (
            #{li.deliveryYear},
            #{li.deliveryQuarter},
            #{li.deliveryMonth},
            #{li.agentCombine},
            #{li.bidType},
            #{li.settlementType},
            #{li.settlement2l},
            #{li.operationLabel},
            #{li.firstDeliveryMonthMaterials},
            #{li.firstDeliveryMonthValidMaterials},
            #{li.validMaterialRatio},
            #{li.targetValue},
            #{li.rebateRatio},
            #{li.fullDotDif},
            #{li.rebateRatioCeiling},
            #{li.cost},
            #{li.rebateCost},
            #{li.fullDotDifCost}
            )
        </foreach>
    </insert>

    <insert id="replaceEtl">
        replace
        into `jeecg-boot`.`etl_first_delivery_valid_materials`
        select `delivery_month`,
               `operation_label`,
               a.`material_id`,
               e.signature,
               `shot_leader_id`,
               `shot_leader_name`,
               f.`shot_id`,
               f.`plan_id`,
               f.`leader_id`,
               f.`clip_name`,
               f.`shot_name`,
               f.`plan_name`,
               f.`leader_name`,
               c.id                    as `project_id`,
               c.project_name,
               c.responsible_id           project_leader_id,
               d.realname              as project_leader_name,
               `stat_date`,
               `customer_name`,
               `rebate_type`,
               `settlement_1l`,
               `settlement_2l`,
               `first_valid`,
               `cost_7day`,
               `cost`,
               `delivery_year`,
               c.design_responsible_id as project_design_leader_id,
               h.realname              as project_design_leader_name,
               f.clip_id               as clip_id
        from `jeecg-boot`.first_delivery_valid_materials a
                 left join `jeecg-boot`.ctop_user_allocation b
                           on a.account_id = b.account_id
                 left join `jeecg-boot`.ctop_project c
                           on b.project_id = c.id
                 left join `jeecg-boot`.sys_user d
                           on c.responsible_id = d.id
                 left join application.bytedance_video_get e
                           on a.material_id = e.material_id
                 left join application.app_bytedance_material_info f
                           on e.signature = f.material_id
                 left join `jeecg-boot`.ctop_material_ascription g
                           on e.signature = g.material_id
                 left join `jeecg-boot`.sys_user h
                           on c.design_responsible_id = h.id
        where c.id is not null
        group by a.delivery_month, a.material_id, signature, b.project_id, settlement_2l, a.delivery_year
    </insert>


    <select id="getDataForProject" resultType="com.alibaba.fastjson.JSONObject">
        select t3.*,
               ifnull(t4.selfEffCount, 0) as 'selfEffCount', concat(round(ifnull(t4.selfEffCount, 0) / t3.effCount * 100, 2), '%') as 'selfEffRate', round(t3.effCount - ifnull(t4.selfEffCount, 0)) as 'thirdEffCount'
        from (
                 (select t1.*,
                         concat(round(t1.effCount / t1.materialNum * 100, 2), '%') as 'effRate',concat( round(ifnull(t2.target_value, 0) * 100, 2),'%' )as 'mediaEffRate', (
                             case
                                 when
                                     round(t1.effCount / t1.materialNum * 100, 2) >= round(t2.target_value * 100, 2)
                                     then 1
                                 else 2
                                 end
                             ) as 'color'
                  from (select project_id as 'projectId', project_name as 'projectName', replace(project_design_leader_name, '（石家庄）', '') as 'dessLeaderName', replace(replace(project_leader_name, '（头条总监）', ''), '（汇创）', '') as 'projectLeaderName', rebate_type as 'rebateType', settlement_1l as 'settlement1l', settlement_2l as 'settlement2l', count(material_id) as 'materialNum', ifnull
                      (
                      sum(
                              (
                                  case
                                      when first_valid = '是'
                                          then 1
                                      else
                                          0 end
                                  )
                      ) , 0 ) as 'effCount'
                        from etl_first_delivery_valid_materials
                        where delivery_year = #{year}
                          and delivery_month = #{month}
                        group by project_id, settlement_2l) t1
                           left join (select *
                                      from first_delivery_valid_ratio
                                      where delivery_year = #{year}
                                        and delivery_month = #{month}) t2
                                     on t1.settlement2l = t2.settlement_2l)) t3
                 left join (select project_id as 'projectId', settlement_2l as 'settlement2l', ifnull(
            sum(
                    (
                        case
                            when first_valid = '是'
                                then 1
                            else
                                0 end
                        )
            ) ,0 ) as 'selfEffCount'
                            from etl_first_delivery_valid_materials
                            where delivery_year = #{year}
                              and delivery_month = #{month}
                              and clip_id is not null
                            group by project_id, settlement_2l) t4
                           on t3.projectId = t4.projectId and t3.settlement2l = t4.settlement2l
        order by t3.materialNum desc
    </select>

    <select id="getDataForUserType" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t1.*,
               concat(IFNULL(round(t1.validMaterials / t1.materialCount * 100, 2), 0),
                      '%') AS 'validRatio', IFNULL(round(t1.validMaterials / t1.materialCount * 100, 2), 0) as 'validRatioValue', concat(IFNULL(round(t2.target_value * 100, 2), 0), '%') AS 'materialValidRatio', IFNULL(round(t2.target_value * 100, 2), 0) AS 'materialValidRatioValue'
        FROM (SELECT IFNULL(project_name, '-') AS 'projectName', IFNULL(REPLACE(leader_name, '（石家庄）', ''), '-') AS 'leaderName', IFNULL(
            REPLACE(REPLACE(project_leader_name, '（头条总监）', ''), '（汇创）', ''),
                '-') AS 'projectLeader', IFNULL(clip_name, '-') AS 'clipName', IFNULL(shot_name, '-') AS 'shotName', IFNULL(plan_name, '-') AS 'planName', IFNULL(REPLACE(project_design_leader_name, '（石家庄）', ''), '-') AS 'designLeader', IFNULL(rebate_type, '-') AS 'rebateType', IFNULL(settlement_1l, '-')as 'settlement1l', IFNULL(settlement_2l, '-')as  'settlement2l', count(material_id) AS 'materialCount', ifnull(
            sum((CASE WHEN first_valid = '是' THEN 1 ELSE 0 END)) ,0) AS 'validMaterials'
              FROM etl_first_delivery_valid_materials
              WHERE delivery_year = #{year}
                AND delivery_month = #{month}
              GROUP BY project_id,
                       ${userType}) t1
                 LEFT JOIN select *
        from first_delivery_valid_ratio
        where delivery_year = #{year}
          and delivery_month = #{month} t2
        ON t1.settlement2l = t2.settlement_2l
        ORDER BY t1.materialCount DESC
    </select>
    <select id="getDesignDataTotal" resultType="java.lang.Integer">
        select count(1)
        from (select *
        from etl_first_delivery_valid_materials
        where delivery_year = #{year}
        and delivery_month = #{month}
        <if test="designRole == 1">
            and clip_id is not null
            and clip_id != ''
            group by project_id, clip_id,settlement_2l
        </if>
        <if test="designRole == 2">
            and clip_id is not null
            and clip_id != ''
            group by project_id, shot_id,settlement_2l
        </if>
        <if test="designRole == 3">
            and clip_id is not null
            and clip_id != ''
            group by project_id, plan_id,settlement_2l
        </if>
        ) t

    </select>
    <select id="getDesignData" resultType="com.alibaba.fastjson.JSONObject">
        select t1.*,
        round(t1.effMaterialNum / t1.materialNum * 100, 2) as 'effRate',
        round(ifnull(t2.target_value,0) * 100, 2) as 'mediaEffRate',
        (
        case
        when
        round(t1.effMaterialNum / t1.materialNum * 100, 2) >= round(ifnull(t2.target_value,0) * 100, 2)
        then 1
        else 2
        end
        ) as 'color',
        (select replace(realname,'（石家庄）','') from sys_user where id = t3.leader_id) as 'leaderName'
        from (select project_name as 'projectName',
        replace(project_design_leader_name, '（石家庄）', '') as 'projectDesignLeaderName',
        replace(replace(project_leader_name, '（头条总监）', ''), '（汇创）', '') as 'projectLeaderName',
        <if test="designRole == 1">
            clip_name as 'designName',
            clip_id as 'designId',
        </if>
        <if test="designRole == 2">
            shot_name as 'designName',
            shot_id as 'designId',
        </if>
        <if test="designRole == 3">
            plan_name as 'designName',
            plan_id as 'designId',
        </if>
        rebate_type as 'rebateType',
        settlement_1l as 'settlement1l',
        settlement_2l as 'settlement2l',
        count(material_id) as 'materialNum',
        ifnull(
        sum(
        (
        case
        when first_valid = '是'
        then 1
        else
        0 end
        )
        ) ,0)as 'effMaterialNum'

        from etl_first_delivery_valid_materials
        where delivery_year = #{year}
        and delivery_month = #{month}
        <if test="designRole == 1">
            and clip_id is not null
            and clip_id != ''
            group by project_id, clip_id,settlement_2l
        </if>
        <if test="designRole == 2">
            and shot_id is not null
            and shot_id != ''
            group by project_id, shot_id,settlement_2l
        </if>
        <if test="designRole == 3">
            and plan_id is not null
            and plan_id != ''
            group by project_id, plan_id,settlement_2l
        </if>
        ) t1
        left join
        (select * from first_delivery_valid_ratio where delivery_year = #{year} and delivery_month = #{month})
        t2 on t1.settlement2l = t2.settlement_2l
        left join sys_user t3 on t1.designId = t3.id
        where t1.designId is not null and t1.designId != ''
        order by t1.materialNum desc
    </select>

</mapper>
