<?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="com.ruoyi.ibs.list.mapper.CampaignCountMapper">
    
    <resultMap type="CampaignCount" id="CampaignCountResult">
        <result property="dt"    column="dt"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="userId"    column="user_id"    />
        <result property="userName"    column="user_name"    />
        <result property="createCampNumYear"    column="create_camp_num_year"    />
        <result property="pushCampNumYear"    column="push_camp_num_year"    />
        <result property="otherCampNumYear"    column="other_camp_num_year"    />
        <result property="createCampNumMonth"    column="create_camp_num_month"    />
        <result property="pushCampNumMonth"    column="push_camp_num_month"    />
        <result property="otherCampNumMonth"    column="other_camp_num_month"    />
        <result property="createCampNumWeek"    column="create_camp_num_week"    />
        <result property="pushCampNumWeek"    column="push_camp_num_week"    />
        <result property="otherCampNumWeek"    column="other_camp_num_week"    />
        <result property="sumType"    column="sum_type"    />
        <result property="custType"    column="cust_type"    />
    </resultMap>

    <resultMap type="VisitCampaignCount" id="VisitCampaignCountResult">
        <result property="id"    column="id"    />
        <result property="campaignId"    column="campaign_id"    />
        <result property="custNum"    column="cust_num"    />
        <result property="pushSuccessNum"    column="push_success_num"    />
        <result property="allocateRate"    column="allocate_rate"    />
        <result property="issuedNum"    column="issued_num"    />
        <result property="claimNum"    column="claim_num"    />
        <result property="validVisitNum"    column="valid_visit_num"    />
        <result property="visitRate"    column="visit_rate"    />
        <result property="loanSignRate"    column="loan_sign_rate"    />
        <result property="increaseDepositAmount"    column="increase_deposit_amount"    />
        <result property="pushClientId"    column="push_client_id"    />
        <result property="custType"    column="cust_type"    />
        <result property="dispatchNum"    column="dispatch_num"    />
        <result property="userName"    column="user_name"    />
    </resultMap>

    <resultMap type="VisitCampaignComment" id="VisitCampaignCommentResult">
        <result property="id"    column="id"    />
        <result property="campaignId"    column="campaign_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="userId"    column="user_id"    />
        <result property="nickName"    column="nick_name"    />
        <result property="comment"    column="comment"    />
        <result property="outletsId"    column="outlets_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectCampaignCountVo">
        select dt, dept_id, dept_name, user_id, user_name, create_camp_num_year, push_camp_num_year, other_camp_num_year, create_camp_num_month, push_camp_num_month, other_camp_num_month, create_camp_num_week, push_camp_num_week, other_camp_num_week, sum_type, cust_type from campaign_count
    </sql>

    <select id="selectCampaignCountList" parameterType="CampaignCount" resultMap="CampaignCountResult">
        select dt, dept_id, dept_name, user_id, user_name, create_camp_num_year, push_camp_num_year, other_camp_num_year, create_camp_num_month, push_camp_num_month, other_camp_num_month, create_camp_num_week, push_camp_num_week, other_camp_num_week, sum_type, cust_type
        from campaign_count
        where 1=1
            <if test="sumType == 0 or sumType == 1 or sumType == 2 or sumType == 3">
                and dept_id = concat(left(#{deptId}, 3), '000')
            </if>
            <if test="sumType == 5 or sumType == -1">
                and dept_id = #{deptId}
            </if>
            <if test="dt != null  and dt != ''"> and dt = #{dt}</if>
            <if test="deptId != null  and deptId != ''"> and dept_id = #{deptId}</if>
            <if test="deptName != null  and deptName != ''"> and dept_name like concat('%', #{deptName}, '%')</if>
            <if test="sumType == -1">
                <if test="userId != null  and userId != ''"> and user_id = #{userId}</if>
            </if>
            <if test="userName != null  and userName != ''"> and user_name like concat('%', #{userName}, '%')</if>
            <if test="createCampNumYear != null "> and create_camp_num_year = #{createCampNumYear}</if>
            <if test="pushCampNumYear != null "> and push_camp_num_year = #{pushCampNumYear}</if>
            <if test="otherCampNumYear != null "> and other_camp_num_year = #{otherCampNumYear}</if>
            <if test="createCampNumMonth != null "> and create_camp_num_month = #{createCampNumMonth}</if>
            <if test="pushCampNumMonth != null "> and push_camp_num_month = #{pushCampNumMonth}</if>
            <if test="otherCampNumMonth != null "> and other_camp_num_month = #{otherCampNumMonth}</if>
            <if test="createCampNumWeek != null "> and create_camp_num_week = #{createCampNumWeek}</if>
            <if test="pushCampNumWeek != null "> and push_camp_num_week = #{pushCampNumWeek}</if>
            <if test="otherCampNumWeek != null "> and other_camp_num_week = #{otherCampNumWeek}</if>
            <if test="sumType != null "> and sum_type = #{sumType}</if>
            <if test="custType != null "> and cust_type = #{custType}</if>
    </select>

    <select id="selectVisitCampaignCountList" parameterType="VisitCampaignCount" resultMap="VisitCampaignCountResult">
        select vc.id, vc.campaign_id, vc.cust_num, vc.push_success_num, vc.allocate_rate, vc.issued_num, vc.claim_num, vc.valid_visit_num, vc.visit_rate, vc.loan_sign_rate, vc.increase_deposit_amount, vc.push_client_id, vc.cust_type,vc.dispatch_num,
        sc.campaign_name,sc.start_time,sc.end_time,sc.claim_start_time,sc.claim_end_time,sc.claim_type,sc.create_role
        from visit_campaign_count vc
        left join sys_campaign sc on vc.campaign_id = sc.campaign_id
        where vc.dept_type = '0'
            <if test="campaignId != null  and campaignId != ''"> and vc.campaign_id = #{campaignId}</if>
            <if test="campaignName != null  and campaignName != ''"> AND sc.campaign_name like concat('%', #{campaignName}, '%')</if>
            <if test="startTime != null "> and sc.start_time &gt;= date_format(#{startTime}, '%Y-%m-%d')</if>
            <if test="endTime != null "> and sc.end_time &lt;= date_format(#{endTime}, '%Y-%m-%d')</if>
            <if test="deptId != null "> and sc.dept_id = #{deptId}</if>
            <if test="custType != null "> and vc.cust_type = #{custType}</if>
            <if test="createRole != null "> and sc.create_role = #{createRole}</if>
    </select>

    <select id="selectVisitCampaignBranchList" parameterType="VisitCampaignCount" resultMap="VisitCampaignCountResult">
        select distinct vc.id, vc.campaign_id, vc.cust_num, vc.push_success_num, vc.allocate_rate, vc.issued_num, vc.claim_num, vc.valid_visit_num, vc.visit_rate, vc.loan_sign_rate, vc.increase_deposit_amount, vc.push_client_id, vc.cust_type,vc.dispatch_num,
        sc.campaign_name,sc.start_time,sc.end_time,sc.claim_start_time,sc.claim_end_time,sc.claim_type,sc.create_role,vc.allocate_status as org_distribute_status,
        vc.dept_id,dep.dept_name,
        CASE
        WHEN sc.create_role = '5' THEN '我创建的'
        ELSE '总行推送的'
        END AS campsource
        from visit_campaign_count vc
        left join sys_campaign sc on vc.campaign_id = sc.campaign_id
        left join sys_dept dep on vc.dept_id = dep.dept_id
        left join (
        select distinct campaign_id,org_distribute_status,dept_id from sys_campaign_group_customer as scgc
        where ((scgc.push_user_level = 'branch' or (scgc.push_user_level = 'head' and scgc.user_id is null)) or scgc.create_role = '5')
        <if test="campaignId != null  and campaignId != ''">  and scgc.campaign_id = #{campaignId}</if>
        and scgc.push_status = '1'
        )scgc2 on scgc2.campaign_id = vc.campaign_id and scgc2.dept_id=vc.dept_id
        where
        vc.dept_type = '1'
        <if test="campaignId != null  and campaignId != ''">  and vc.campaign_id = #{campaignId}</if>
        <if test="campaignName != null  and campaignName != ''"> AND sc.campaign_name like concat('%', #{campaignName}, '%')</if>
        <if test="deptId != null "> and vc.dept_id = #{deptId}</if>
        <if test="startTime != null "> and sc.start_time &gt;= date_format(#{startTime}, '%Y-%m-%d')</if>
        <if test="endTime != null "> and sc.end_time &lt;= date_format(#{endTime}, '%Y-%m-%d')</if>
        <if test="custType != null "> and vc.cust_type = #{custType}</if>
        <choose>
            <when test="campsource == '我创建的'">
                and sc.create_role = '5'
            </when>
            <when test="campsource == '总行推送的'">
                and sc.create_role != '5'
            </when>
        </choose>
    </select>

    <select id="selectVisitCampaignCountCheckList" parameterType="VisitCampaignCount" resultMap="VisitCampaignCountResult">
        select distinct vc.id, vc.campaign_id, vc.cust_num, vc.push_success_num, vc.allocate_rate, vc.issued_num, vc.claim_num, vc.valid_visit_num, vc.visit_rate, vc.loan_sign_rate, vc.increase_deposit_amount, vc.push_client_id, vc.cust_type,vc.dispatch_num,
        sc.campaign_name,sc.start_time,sc.end_time,sc.claim_start_time,sc.claim_end_time,sc.claim_type,sc.create_role,vc.allocate_status as org_distribute_status,
        vc.dept_id,
        case when substr(dep.dept_id,-3) = '000' then ''
        else dep.dept_name end as dept_name,
        CASE
        WHEN sc.create_role = '5' THEN '我创建的'
        ELSE '总行推送的'
        END AS campsource
        from visit_campaign_count vc
        left join sys_campaign sc on vc.campaign_id = sc.campaign_id
        left join sys_dept dep on vc.dept_id = dep.dept_id
        left join (
        select distinct campaign_id,org_distribute_status,dept_id from sys_campaign_group_customer as scgc
        where ((scgc.push_user_level = 'branch' or (scgc.push_user_level = 'head' and scgc.user_id is null)) or scgc.create_role = '5')
        <if test="campaignId != null  and campaignId != ''">  and scgc.campaign_id = #{campaignId}</if>
        and scgc.push_status = '1'
        )scgc2 on scgc2.campaign_id = vc.campaign_id and scgc2.dept_id=vc.dept_id
        where
        vc.dept_type = '1'
        <if test="campaignId != null  and campaignId != ''">  and vc.campaign_id = #{campaignId}</if>
        <if test="campaignName != null  and campaignName != ''"> AND sc.campaign_name like concat('%', #{campaignName}, '%')</if>
        <if test="startTime != null "> and sc.start_time = #{startTime}</if>
        <if test="endTime != null "> and sc.end_time = #{endTime}</if>
        <if test="custType != null "> and vc.cust_type = #{custType}</if>
        <choose>
            <when test="campsource == '我创建的'">
                and sc.create_role = '5'
            </when>
            <when test="campsource == '总行推送的'">
                and sc.create_role != '5'
            </when>
        </choose>
    </select>

    <select id="selectOutletList" parameterType="VisitCampaignCount" resultMap="VisitCampaignCountResult">
        select
        distinct vc.id,vc.campaign_id,vc.cust_num,vc.push_success_num,vc.allocate_rate,vc.issued_num,vc.claim_num,vc.valid_visit_num,vc.visit_rate,vc.loan_sign_rate,vc.increase_deposit_amount,vc.push_client_id,vc.cust_type,vc.dispatch_num,
        sc.campaign_name,sc.start_time,sc.end_time,sc.claim_start_time,sc.claim_end_time,sc.claim_type,sc.create_role,su.user_name as user_id,

        case
        when dep2.dept_type = 'head' then dep.dept_id
        when dep2.dept_type is null then dep.dept_id
        when dep2.dept_type ='bureau' then dep.dept_id
        else dep2.dept_id
        end as dept_id,

        case
        when dep2.dept_type = 'head' then ''
        when dep2.dept_type is null then ''
        when dep2.dept_type ='bureau' then ''
        else dep.dept_name
        end as outlets,

        case
        when dep2.dept_type = 'head' then ''
        when dep2.dept_type is null then ''
        when dep2.dept_type ='bureau' then ''
        else dep.dept_id
        end as outlets_id,

        case
        when dep2.dept_type = 'head' then dep.dept_name
        else dep2.dept_name
        end as dept_name,

        concat( su.nick_name, '-', su.user_name ) as nick_name,
        case
        when sc.create_role = '5' then '支行下发的'
        else '总行下发的'
        end as campsource
        from
        visit_campaign_count vc left join sys_campaign sc on
        vc.campaign_id = sc.campaign_id left join sys_user su on
        vc.dept_id = su.user_name
        left join sys_dept dep on vc.dept_id = dep.dept_id
        left join sys_dept dep2 on dep.parent_id = dep2.dept_id
        where
        vc.dept_type = '2'
        <if test="campaignId != null  and campaignId != ''"> and vc.campaign_id = #{campaignId}</if>
        <choose>
            <when test="deptId != null and deptId.toString().endsWith('000')">
                AND dep.dept_id = #{deptId}
            </when>
            <when test="deptId != null">
                AND (dep.dept_id = #{deptId} OR dep2.dept_id = #{deptId})
            </when>
        </choose>
        <if test="campaignName != null  and campaignName != ''"> AND sc.campaign_name like concat('%', #{campaignName}, '%')</if>
        <if test="startTime != null "> and sc.start_time &gt;= date_format(#{startTime}, '%Y-%m-%d')</if>
        <if test="endTime != null "> and sc.end_time &lt;= date_format(#{endTime}, '%Y-%m-%d')</if>
        <if test="custType != null "> and vc.cust_type = #{custType}</if>
        <choose>
            <when test="campsource == '支行下发的'">
                and sc.create_role = '5'
            </when>
            <when test="campsource == '总行下发的'">
                and sc.create_role != '5'
            </when>
        </choose>
    </select>

    <select id="selectPersonList" parameterType="VisitCampaignCount" resultMap="VisitCampaignCountResult">
        select
        distinct vc.id,vc.campaign_id,vc.cust_num,vc.push_success_num,vc.allocate_rate,vc.issued_num,vc.claim_num,vc.valid_visit_num,vc.visit_rate,vc.loan_sign_rate,vc.increase_deposit_amount,vc.push_client_id,vc.cust_type,vc.dispatch_num,
        vc.start_time,vc.end_time,sc.claim_type,su.user_name as user_id,
        case when   left(vc.campaign_id,7)='appoint' then '预约任务'  else  sc.campaign_name end as campaign_name,
        case when   left(vc.campaign_id,7)='appoint' then  vc.start_time else  sc.claim_start_time end as claim_start_time,
        case when   left(vc.campaign_id,7)='appoint' then  vc.end_time  else       sc.claim_end_time end as claim_end_time,
        case when   left(vc.campaign_id,7)='appoint' then  '-1'  else     sc.create_role end as create_role ,

        case
        when dep2.dept_type = 'head' then dep.dept_id
        else dep2.dept_id
        end as dept_id,
        case
        when dep2.dept_type = 'head' then ''
        else dep.dept_name
        end as outlets,

        case
        when dep2.dept_type = 'head' then dep.dept_name
        else dep2.dept_name
        end as dept_name,

        concat( su.nick_name, '-', su.user_name ) as nick_name,
        case
        when sc.create_role = '5' then '支行下发的'
        else '总行下发的'
        end as campsource
        from
        visit_campaign_count vc left join sys_campaign sc on
        vc.campaign_id = sc.campaign_id left join sys_user su on
        vc.dept_id = su.user_name
        left join sys_dept dep on su.dept_id = dep.dept_id
        left join sys_dept dep2 on dep.parent_id = dep2.dept_id
        where
        vc.dept_type = '3'
        <if test="campaignId != null  and campaignId != ''"> and vc.campaign_id = #{campaignId}</if>
        <if test="campaignName != null  and campaignName != ''"> AND sc.campaign_name like concat('%', #{campaignName}, '%')</if>
        <choose>
            <when test="outletsId != null">
                AND (dep.dept_id = #{outletsId} OR dep2.dept_id = #{outletsId})
            </when>
            <otherwise>
                <if test="deptId != null and personType == 'branch' and deptId.toString().endsWith('000')">
                    AND dep.dept_id = #{deptId}
                </if>
                <if test="deptId != null and personType == 'branch'">
                    AND (dep.dept_id = #{deptId} OR dep2.dept_id = #{deptId})
                </if>
                <if test="deptId != null and personType == 'outlet'">
                    AND (dep.dept_id = #{deptId})
                </if>
            </otherwise>
        </choose>
        <if test="userId != null " >and vc.dept_id =#{userId} </if>
        <if test="startTime != null "> and sc.start_time &gt;= date_format(#{startTime}, '%Y-%m-%d')</if>
        <if test="endTime != null "> and sc.end_time &lt;= date_format(#{endTime}, '%Y-%m-%d')</if>
        <if test="custType != null "> and vc.cust_type = #{custType}</if>
        <choose>
            <when test="campsource == '支行下发的'">
                and sc.create_role = '5'
            </when>
            <when test="campsource == '总行下发的'">
                and sc.create_role != '5'
            </when>
        </choose>
    </select>

    <select id="selectCommentList" parameterType="VisitCampaignComment" resultMap="VisitCampaignCommentResult">
        select id, campaign_id, dept_id, user_id, nick_name, comment, outlets_id, create_by, create_time, update_by, update_time from visit_campaign_comment
        <where>
            <if test="campaignId != null  and campaignId != ''"> and campaign_id = #{campaignId}</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="nickName != null  and nickName != ''"> and nick_name like concat('%', #{nickName}, '%')</if>
            <if test="comment != null  and comment != ''"> and comment = #{comment}</if>
            <if test="outletsId != null "> and outlets_id = #{outletsId}</if>
        </where>
    </select>

    <insert id="insertVisitCampaignComment" parameterType="VisitCampaignComment" useGeneratedKeys="true" keyProperty="id">
        insert into visit_campaign_comment
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="campaignId != null">campaign_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="nickName != null">nick_name,</if>
            <if test="comment != null">comment,</if>
            <if test="outletsId != null">outlets_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="campaignId != null">#{campaignId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="nickName != null">#{nickName},</if>
            <if test="comment != null">#{comment},</if>
            <if test="outletsId != null">#{outletsId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
        </trim>
    </insert>


    <update id="updateCampaignCount" parameterType="CampaignCount">
        update campaign_count
        <trim prefix="SET" suffixOverrides=",">
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="deptName != null">dept_name = #{deptName},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="createCampNumYear != null">create_camp_num_year = #{createCampNumYear},</if>
            <if test="pushCampNumYear != null">push_camp_num_year = #{pushCampNumYear},</if>
            <if test="otherCampNumYear != null">other_camp_num_year = #{otherCampNumYear},</if>
            <if test="createCampNumMonth != null">create_camp_num_month = #{createCampNumMonth},</if>
            <if test="pushCampNumMonth != null">push_camp_num_month = #{pushCampNumMonth},</if>
            <if test="otherCampNumMonth != null">other_camp_num_month = #{otherCampNumMonth},</if>
            <if test="createCampNumWeek != null">create_camp_num_week = #{createCampNumWeek},</if>
            <if test="pushCampNumWeek != null">push_camp_num_week = #{pushCampNumWeek},</if>
            <if test="otherCampNumWeek != null">other_camp_num_week = #{otherCampNumWeek},</if>
            <if test="sumType != null">sum_type = #{sumType},</if>
            <if test="custType != null">cust_type = #{custType},</if>
        </trim>
        where dt = #{dt}
    </update>

    <delete id="deleteCampaignCountByDt" parameterType="String">
        delete from campaign_count where dt = #{dt}
    </delete>

    <delete id="deleteCampaignCountByDts" parameterType="String">
        delete from campaign_count where dt in 
        <foreach item="dt" collection="array" open="(" separator="," close=")">
            #{dt}
        </foreach>
    </delete>
</mapper>