<?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.SysCampaignMapper">

    <resultMap type="SysCampaign" id="SysCampaignResult">
        <result property="id" column="id"/>
        <result property="campaignName" column="campaign_name"/>
        <result property="campaignId" column="campaign_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="userId" column="user_id"/>
        <result property="createName" column="create_name"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="campaignDegree" column="campaign_degree"/>
        <result property="remark" column="remark"/>
        <result property="delFlag" column="del_flag"/>
        <result property="campaignType" column="campaign_type"/>
        <result property="claimType" column="claim_type"/>
        <result property="distributeType" column="distribute_type"/>
        <result property="distributeType2" column="distribute_type2"/>
        <result property="distributeType3" column="distribute_type3"/>
        <result property="distributeCont" column="distribute_cont"/>
        <result property="distributeCont2" column="distribute_cont2"/>
        <result property="distributeCont3" column="distribute_cont3"/>
        <result property="claimStartTime" column="claim_start_time"/>
        <result property="claimEndTime" column="claim_end_time"/>
        <result property="executer" column="executer"/>
        <result property="createRole" column="create_role"/>
        <result property="groupId" column="group_id"/>
        <result property="secondPushStatus" column="second_push_status"/>
        <result property="taskType" column="task_type"/>
        <result property="checkTarget" column="check_target"/>
        <result property="taskIndex" column="task_index"/>
        <result property="reward" column="reward"/>
        <result property="feedBack" column="feed_back"/>
        <result property="custType" column="cust_type"/>
        <result property="customerNum" column="customer_num"/>
        <result property="orgClaimStatus" column="org_claim_status"/>
        <result property="orgDistributeStatus" column="org_distribute_status"/>
        <result property="custClaimStatus" column="cust_claim_status"/>
        <result property="userName" column="user_name"/>
        <result property="visitType" column="visit_type"/>
        <result property="relatedModelId" column="related_model_id"/>
        <result property="isCooperate" column="is_cooperate"/>
    </resultMap>

    <resultMap type="CustomerList" id="CustomerList">
        <result property="groupName"           column="group_name"          />
        <result property="custId"          column="cust_id"        />
        <result property="custIdc"          column="cust_idc"        />
        <result property="custIsn"          column="cust_isn"        />
        <result property="custName"          column="cust_name"        />
        <result property="custPhone"          column="cust_phone"        />
        <result property="pushStatus"          column="push_status"        />
        <result property="gridName"          column="grid_name"        />
        <result property="gridName2"          column="grid_name2"        />
        <result property="userName"          column="user_name"        />
        <result property="createName"          column="create_name"        />
        <result property="deptName"          column="dept_name"        />
        <result property="groupId"          column="group_id"        />
        <result property="outlets"          column="outlets"        />
        <result property="tellerId"          column="teller_id"        />
        <result property="lpName"          column="lp_name"        />
        <result property="socialCreditCode"          column="social_credit_code"        />
        <result property="socialCreditCodeType"          column="social_credit_code_type"        />
        <result property="idType"          column="id_type"        />
        <result property="custType"          column="cust_type"        />
        <result property="groupName"          column="group_name"        />
        <result property="custName"          column="cust_name"        />
        <result property="custPhone"          column="cust_phone"        />
        <result property="custIsn"          column="cust_isn"        />
        <result property="custClaimStatus"          column="cust_claim_status"        />
        <result property="adminFlag"          column="admin_flag"        />
        <result property="pushUserLevel"          column="push_user_level"        />
        <result property="secondPushStatus"          column="second_push_status"        />
    </resultMap>

    <resultMap type="AddressAndPhone" id="AddressAndPhone">
        <result property="custId"    column="cust_id"    />
        <result property="custLocation"    column="cust_location"    />
        <result property="registerLocation"    column="register_location"    />
        <result property="jobLocation"    column="job_location"    />
        <result property="custPhone"    column="cust_phone"    />
        <result property="tel"    column="tel"    />
    </resultMap>

    <resultMap type="com.ruoyi.ibs.list.domain.DeptAndOutlets" id="DeptAndOutlets">
        <result property="deptId"    column="dept_id"    />
        <result property="outletsId"    column="outlets_id"    />
        <result property="userId"    column="user_id"    />
        <result property="deptType"    column="dept_type"    />
        <result property="outlets"    column="outlets"    />
    </resultMap>

    <resultMap type="com.ruoyi.ibs.list.domain.GridInfoTs" id="GridInfoTs">
        <result property="gridId"    column="grid_id"    />
        <result property="shapeId"    column="shape_id"    />
        <result property="custId"    column="cust_id"    />
        <result property="userName"    column="user_name"    />
        <result property="relateDeptId"    column="relate_dept_id"    />
    </resultMap>

    <resultMap type="VisitInfo" id="VisitInfoResult">
        <result property="id"    column="id"    />
        <result property="campaignId"    column="campaign_id"    />
        <result property="campaignName"    column="campaign_name"    />
        <result property="visName"    column="vis_name"    />
        <result property="visId"    column="vis_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="visTime"    column="vis_time"    />
        <result property="custName"    column="cust_name"    />
        <result property="custIdc"    column="cust_idc"    />
        <result property="socialCreditCode"    column="social_credit_code"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="custType"    column="cust_type"    />
        <result property="deptName"    column="dept_name"    />

    </resultMap>

    <sql id="selectSysCampaignVo">
        select id, campaign_name, campaign_id, dept_id, user_id, create_by, create_time, update_by, update_time, start_time, end_time,
               campaign_degree, remark, del_flag, campaign_type, claim_type, distribute_type, distribute_type2, distribute_type3,
               distribute_cont, distribute_cont2, distribute_cont3,claim_start_time,visit_type,
               claim_end_time,
               executer,
               create_role,
               group_id,
               second_push_status,
               task_type,
               check_target,
               task_index,
               reward,
               feed_back,
               cust_type,
               related_model_id,
               is_cooperate
        from sys_campaign
    </sql>

    <select id="selectSysCampaignVoList"  resultMap="SysCampaignResult">
        <include refid="selectSysCampaignVo"/>
        <where>
            <if test="createRole != null and createRole != ''"> and create_role = #{createRole}</if>
            <if test="timeNow != null"> and #{timeNow} >= start_time  and  #{timeNow} &lt;= end_time</if>
            <if test="deptId != null and deptId != ''"> and dept_id = #{deptId}</if>
            <if test="custType != null and custType != ''"> and cust_type = #{custType}</if>
            <if test="campaignId != null and campaignId != ''"> and campaign_id = #{campaignId}</if>
        </where>
    </select>



    <select id="selectSysCampaignList"  resultMap="SysCampaignResult">
        select sc.campaign_name, sc.campaign_id,  sc.start_time, sc.end_time,sc.visit_type,
        sc.task_type, sc.check_target, sc.create_time, sc.claim_type,sc.campaign_degree,sc.claim_end_time,sc.claim_start_time,
        su.nick_name as create_name,su.user_name,
        count(distinct sgc.cust_id) as customer_num
        from 	sys_campaign sc
        left join sys_group sg on      sg.group_id = sc.group_id
        left join sys_campaign_group_customer sgc on    sgc.campaign_id = sc.campaign_id
        left join sys_user su on       sc.user_id = su.user_id
        <where>
            1=1 and sgc.cust_id != ''
            <if test="deptType == '0' or deptType == 0">
                <!--  查询自己创建的东西             -->
                <if test="sysCampaign.deptId != null ">and sc.dept_id = #{sysCampaign.deptId}</if>
                <if test="sysCampaign.createRole != null and sysCampaign.createRole != ''">
                    and sc.create_role = #{sysCampaign.createRole}</if>
            </if>
            <if test="sysCampaign.campaignName != null  and sysCampaign.campaignName != ''">and sc.campaign_name like concat('%', #{sysCampaign.campaignName},
                '%')
            </if>
            <if test="sysCampaign.loginOrg3 != null ">and sc.dept_id div 1000 = #{sysCampaign.loginOrg3}</if>
            <if test="sysCampaign.deptId != null ">and sc.dept_id = #{sysCampaign.deptId}</if>

            <if test="sysCampaign.startTime != null ">and sc.start_time = #{sysCampaign.startTime}</if>
            <if test="sysCampaign.endTime != null ">and sc.end_time = #{sysCampaign.endTime}</if>
            <if test="sysCampaign.campaignId != null  and sysCampaign.campaignId != ''">and sc.campaign_id = #{sysCampaign.campaignId}</if>
            <if test="sysCampaign.taskType != null and sysCampaign.taskType != ''"> and sc.task_type = #{sysCampaign.taskType}</if>
            <if test="sysCampaign.checkTarget != null and sysCampaign.checkTarget != ''"> and sc.check_target = #{sysCampaign.checkTarget}</if>
            <if test="sysCampaign.custType != null and sysCampaign.custType != ''"> and sc.cust_type = #{sysCampaign.custType}</if>
            <if test="sysCampaign.createRole != null and sysCampaign.createRole != ''"> and sc.create_role = #{sysCampaign.createRole}</if>
        </where>
        group by  sc.campaign_name, sc.campaign_id,  sc.start_time, sc.end_time,sc.visit_type,
        sc.task_type, sc.check_target, sc.create_time, sc.claim_type,sc.campaign_degree,sc.claim_end_time,sc.claim_start_time,su.nick_name,su.user_name
        order by sc.create_time desc
    </select>

    <select id="selectSysCampType1And2"  resultMap="SysCampaignResult" >

        select aa1.user_name ,
        aa1.create_name ,
        aa1.campaign_name ,
        aa1.campaign_id,
        aa1.start_time,
        aa1.end_time,
        aa1.task_type,
        aa1.check_target,
        aa1.claim_end_time,
        aa1.claim_start_time,
        aa1.campaign_degree,
        aa1.claim_type,
        cust_claim_status,
        aa1.second_push_status,
        aa1.org_claim_status,
        aa1.org_distribute_status,
        aa1.dept_id,
        aa1.create_time,
        aa1.visit_type,
        count(distinct cust_id ) as customer_num
        from (
        select
        sc.create_by as user_name,
        su.nick_name as create_name,
        sc.campaign_name ,
        sc.campaign_id,
        sc.start_time,
        sc.end_time,
        sc.task_type,
        sc.check_target,
        sc.claim_end_time,
        sc.claim_start_time,
        sc.campaign_degree,
        sc.claim_type,
        sc.dept_id,
        sc.visit_type,
        sc.create_time,
        scgc.cust_id,
        <if test="deptType==1">
        case when  (scgc.user_id is null  or   scgc.user_id != #{userId} )  then null   else scgc.cust_claim_status end as cust_claim_status,
        </if>
        <if test="deptType==2">
            null as cust_claim_status,
        </if>
        case when scgc.user_id is null  or scgc.push_user_level = 'branch'  then scgc.second_push_status        else null end as second_push_status,
        case when scgc.user_id is null  or scgc.push_user_level = 'branch'  then scgc.org_claim_status          else null end as org_claim_status,
        case when scgc.user_id is null  or scgc.push_user_level = 'branch'  then scgc.org_distribute_status     else null end as org_distribute_status
        from
        sys_campaign sc
        left join sys_dept sd2 on   sc.dept_id = sd2.dept_id
        left join sys_user su on   sc.create_by = su.user_name
        left join sys_campaign_group_customer scgc on  sc.campaign_id = scgc.campaign_id
        left join sys_dept a3 on scgc.dept_id = a3.dept_id
        where left(sc.dept_id,3) =  #{loginOrg3} and sc.cust_type =  #{custType}
        <if test="deptType==1">
            <if test="campaignLevel == -1  or  campaignLevel == 6 "> AND scgc.user_id = #{userId}</if>
            <if test="campaignLevel != -1">
                and scgc.dept_id = #{deptId}
                and ((( scgc.push_user_level is null or scgc.push_user_level = 'head') and scgc.user_id is null)  or (  scgc.push_user_level = 'branch'  or scgc.user_id = #{userId} ))
            </if>
        </if>
        <if test="deptType==2">
            <if test="campaignLevel == -1"> AND scgc.user_id = #{userId}   </if>
            <if test="campaignLevel == 5 "> AND ( a3.dept_id = #{deptId}  and  scgc.user_id is not null and  scgc.push_user_level = 'head' AND sd2.dept_type = 'head' )   </if>
            <if test="campaignLevel == 6">  AND ( scgc.outlets_id = #{deptId} and  scgc.user_id is not null and   sd2.dept_type = 'head' )</if>
            <if test="campaignLevel != 5 and campaignLevel !=  6 and campaignLevel != -1 ">    AND sd2.dept_type = 'branch'  </if>
        </if>
        <if test="campaignName != null  and campaignName != ''">and sc.campaign_name like concat('%', #{campaignName},  '%')</if>
            ) aa1
        group by
        aa1.user_name ,
        aa1.create_name ,
        aa1.campaign_name ,
        aa1.campaign_id,
        aa1.start_time,
        aa1.end_time,
        aa1.task_type,
        aa1.check_target,
        aa1.claim_end_time,
        aa1.claim_start_time,
        aa1.campaign_degree,
        aa1.claim_type,
        cust_claim_status,
        aa1.second_push_status,
        aa1.org_claim_status,
        aa1.org_distribute_status,
        aa1.dept_id,
        aa1.visit_type,
        aa1.create_time

    </select>

    <select id="getListByHeadAdmin"  resultType="CampaignVO" >
        SELECT sc.campaign_name, sc.campaign_id,  sc.claim_end_time, sc.claim_start_time,
        sc.task_type, sc.check_target,
        sc.campaign_degree, sc.claim_type, sc.cust_type,
        concat( su.nick_name,'-', sc.create_by) as creator,
        concat( su2.nick_name,'-', sc.update_by) as updateBy,
        count(distinct scgc.cust_id) as custNum
        FROM sys_campaign sc
        LEFT JOIN  (select * from sys_campaign_group_customer where push_status != '1' ) scgc ON sc.campaign_id = scgc.campaign_id
        left join sys_user su on sc.create_by  = su.user_name
        left join head_handled_mission hhm on sc.campaign_id = hhm.campaign_id
        left join sys_user su2 on hhm.update_by  = su2.user_name
        WHERE   sc.claim_end_time > now()   and sc.dept_id = #{deptId} and sc.create_role = #{userRoleNum}
        <if test=" isFinish">
            and hhm.id is not null
        </if>

        GROUP BY sc.campaign_name, sc.campaign_id, sc.claim_end_time, sc.claim_start_time,
        sc.task_type, sc.check_target,
        sc.campaign_degree, sc.claim_type, sc.cust_type, su.nick_name, sc.create_by,su2.nick_name,sc.update_by
        having
        <if test="! isFinish">count(distinct scgc.cust_id) > 0</if>
        <if test="isFinish"  >count(distinct scgc.cust_id) = 0</if>
    </select>





    <select id="getListByBranchAdmin"  resultType="CampaignVO" >
        select distinct
        a.campaign_name, a.campaign_id, a.task_type, a.claim_end_time as campaign_end_time,
        a.campaign_status,a.campaign_degree, a.cust_type, a.creator
        <if test="!isFinish">
            , count(distinct scgc2.cust_id) as cust_num
        </if>
        <if test="isFinish">
            , concat(su2.nick_name,'-',scs.create_by) as finisher, scs.create_time as finish_time
        </if>
        from (
            SELECT sc.campaign_name, sc.campaign_id, sc.start_time, sc.end_time, sc.task_type, sc.check_target,
            sc.claim_end_time, sc.claim_start_time, sc.campaign_degree, sc.claim_type, sc.cust_type,
            concat( su.nick_name,'-', sc.create_by) as creator,
            CONCAT(MIN(scgc.org_claim_status), MIN(scgc.org_distribute_status), MIN(IF(scgc.user_id IS NULL, 0, 1))) AS campaign_status ,
        <if test="isFinish">  '1' as count_sccess, '1' as count_all </if>
        <if test="!isFinish"> '2' as count_sccess, '1' as count_all </if>
            FROM sys_campaign sc
            LEFT JOIN sys_campaign_group_customer scgc ON sc.campaign_id = scgc.campaign_id
            left join sys_user su on sc.create_by  = su.user_name
            WHERE (scgc.dept_id = #{deptId} OR scgc.user_id = #{userId}) and sc.claim_end_time > now()
            and sc.dept_id != #{deptId}
            <if test="isFinish">
                and scgc.push_user_level != 'head'
            </if>
            GROUP BY sc.campaign_name, sc.campaign_id, sc.start_time, sc.end_time, sc.task_type, sc.check_target,
            sc.claim_end_time, sc.claim_start_time, sc.campaign_degree, sc.claim_type, sc.cust_type, su.nick_name, sc.create_by

        union
        select
        sc.campaign_name, sc.campaign_id, sc.start_time,sc.end_time,sc.task_type,sc.check_target,sc.claim_end_time,sc.claim_start_time,sc.campaign_degree,sc.claim_type,sc.cust_type,
        concat(su.nick_name, '-', sc.create_by) AS creator, CONCAT(MIN(scgc.org_claim_status), MIN(scgc.org_distribute_status),
        MIN(IF(scgc.user_id IS NULL, 0, 1))) AS campaign_status,
        sum( scgc.push_status = '1') count_sccess,
        count(scgc.push_status) count_all
        from
        sys_campaign sc
        left join sys_campaign_group_customer scgc on
        sc.campaign_id = scgc.campaign_id
        left join sys_user su on
        sc.create_by = su.user_name
        where sc.dept_id = #{deptId}  and sc.claim_end_time > now()
        group by
        sc.campaign_name,  sc.campaign_id,  sc.start_time,sc.end_time, sc.task_type, sc.check_target,
        sc.claim_end_time, sc.claim_start_time,  sc.campaign_degree,sc.claim_type,sc.cust_type, su.nick_name,sc.create_by
        ) a
        <if test="!isFinish">
            left join sys_campaign sc on sc.campaign_id = a.campaign_id
            left join sys_campaign_group_customer scgc2 on scgc2.campaign_id = a.campaign_id
        </if>
        <if test="isFinish">
            left join sys_campaign_second scs on a.campaign_id = scs.campaign_id
            left join sys_user su2 on scs.create_by = su2.user_name
        </if>
        where 1=1
        <if test="!isFinish">
            and a.campaign_status != '111' and scgc2.user_id is null and  (scgc2.dept_id = #{deptId} or sc.dept_id = #{deptId} ) and count_sccess != count_all
        </if>
        <if test="isFinish">
            and a.campaign_status = '111' and count_sccess = count_all
        </if>
        group by a.campaign_name, a.campaign_id, a.start_time, a.end_time, a.task_type, a.check_target,
        campaign_end_time, a.claim_start_time, a.campaign_degree, a.claim_type, a.cust_type,
        a.creator, a.campaign_status
        <if test="isFinish">
            ,finisher, finish_time
        </if>
        <if test="!isFinish">
        union
            select distinct sc.campaign_name, sc.campaign_id, sc.task_type, sc.claim_end_time as campaign_end_time,
            '客户认领' as campaign_status,sc.campaign_degree, sc.cust_type,
            concat( su.nick_name,'-', sc.create_by) as creator, count(distinct scgc.cust_id) as cust_num
            from sys_campaign sc
            left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
            left join sys_campaign_group_customer scgc on scgc.campaign_id = sc.campaign_id
            left join sys_group sg on sg.group_id = scg.group_id
            left join sys_user su on sc.user_id = su.user_id
            where scgc.user_id = #{userId}
            and sc.claim_end_time > now() and scgc.cust_claim_status = '0'
            group by sc.campaign_id , sc.campaign_name,sc.cust_type,
            sc.task_type, sc.claim_end_time, sc.create_by, su.nick_name, sc.campaign_degree
        union
            select distinct sc.campaign_name, sc.campaign_id, sc.task_type, sc.end_time as campaign_end_time,
            '客户触达' as campaign_status, sc.campaign_degree, sc.cust_type,
            concat( su.nick_name,'-', sc.create_by) as creator, count(distinct scgc.cust_id) as cust_num
            from sys_campaign sc
            left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
            left join sys_campaign_group_customer scgc on scgc.campaign_id = sc.campaign_id
            left join sys_group sg on sg.group_id = scg.group_id
            left join sys_user su on sc.user_id = su.user_id
            where scgc.user_id = #{userId}
            and sc.end_time > now() and scgc.cust_claim_status = '1'
            group by sc.campaign_id , sc.campaign_name,sc.cust_type,
            sc.task_type, sc.end_time, sc.create_by, su.nick_name, sc.campaign_degree
        </if>


    </select>

    <select id="selectSysCampTypeUser"  resultMap="SysCampaignResult" >
        select        sc.campaign_name, sc.campaign_id, sc.start_time, sc.end_time,
                        sc.task_type, sc.check_target,   sc.create_time, sc.claim_type,sc.visit_type,
                        sc.campaign_degree,sc.claim_end_time,sc.claim_start_time,su.nick_name as create_name,su.user_name,
                        count(distinct sgc.cust_id )  customer_num
        from sys_campaign sc
                 left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
                 left join sys_campaign_group_customer sgc on sgc.campaign_id = sc.campaign_id
                 left join sys_group sg on sg.group_id = scg.group_id
                 left join sys_user su on sc.user_id = su.user_id
        where sgc.user_id = #{userId}  and sc.cust_type = #{custType}
        <if test="campaignName != null  and campaignName != ''">and sc.campaign_name like concat('%', #{campaignName},
            '%')
        </if>
        group by sc.campaign_name, sc.campaign_id, sc.start_time, sc.end_time,
                 sc.task_type, sc.check_target,   sc.create_time, sc.claim_type,sc.visit_type,
                 sc.campaign_degree,sc.claim_end_time,sc.claim_start_time,su.nick_name ,su.user_name
    </select>

    <select id="getListByManager"  resultType="CampaignVO" >
        select distinct sc.campaign_name, sc.campaign_id, sc.task_type, sc.claim_end_time as campaign_end_time,
                        '客户认领' as campaign_status,sc.campaign_degree, sc.cust_type,
                        count(distinct scgc.cust_id) as cust_num, concat( su.nick_name,'-', sc.create_by) as creator
        from sys_campaign sc
                 left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
                 left join sys_campaign_group_customer scgc on scgc.campaign_id = sc.campaign_id
                 left join sys_group sg on sg.group_id = scg.group_id
                 left join sys_user su on sc.user_id = su.user_id
        where scgc.user_id = #{userId}
          and sc.claim_end_time > now() and scgc.cust_claim_status = '0'
        group by sc.campaign_id , sc.campaign_name,sc.cust_type,
                 sc.task_type, sc.claim_end_time, sc.create_by, su.nick_name, sc.campaign_degree
        union
        select distinct sc.campaign_name, sc.campaign_id, sc.task_type, sc.end_time as campaign_end_time,
                        '客户触达' as campaign_status, sc.campaign_degree, sc.cust_type,
                        count(distinct scgc.cust_id) as cust_num, concat( su.nick_name,'-', sc.create_by) as creator
        from sys_campaign sc
                 left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
                 left join sys_campaign_group_customer scgc on scgc.campaign_id = sc.campaign_id
                 left join sys_group sg on sg.group_id = scg.group_id
                 left join sys_user su on sc.user_id = su.user_id
        where scgc.user_id = #{userId}
          and sc.end_time > now() and scgc.cust_claim_status = '1'
        group by sc.campaign_id , sc.campaign_name,sc.cust_type,
                 sc.task_type, sc.end_time, sc.create_by, su.nick_name, sc.campaign_degree
    </select>

    <select id="getListByOutletManager"  resultType="CampaignVO" >
        select distinct sc.campaign_name, sc.campaign_id, sc.task_type, sc.claim_end_time as campaign_end_time,
                        '认领' as campaign_status,sc.campaign_degree, sc.cust_type,
                        count(scgc.cust_id) as cust_num, concat( su.nick_name,'-', sc.create_by) as creator
        from sys_campaign sc
                 left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
                 left join sys_campaign_group_customer scgc on scgc.campaign_id = sc.campaign_id
                 left join sys_group sg on sg.group_id = scg.group_id
                 left join sys_user su on sc.user_id = su.user_id
        where scgc.outlets_id = #{deptId}
          and sc.claim_end_time > now() and scgc.cust_claim_status = '0'
        group by sc.campaign_id , sc.campaign_name,sc.cust_type,
                 sc.task_type, sc.claim_end_time, sc.create_by, su.nick_name, sc.campaign_degree
        union
        select distinct sc.campaign_name, sc.campaign_id, sc.task_type, sc.end_time as campaign_end_time,
                        '触达' as campaign_status, sc.campaign_degree, sc.cust_type,
                        count(scgc.cust_id) as cust_num, concat( su.nick_name,'-', sc.create_by) as creator
        from sys_campaign sc
                 left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
                 left join sys_campaign_group_customer scgc on scgc.campaign_id = sc.campaign_id
                 left join sys_group sg on sg.group_id = scg.group_id
                 left join sys_user su on sc.user_id = su.user_id
        where scgc.outlets_id = #{deptId}
          and sc.end_time > now() and scgc.cust_claim_status = '1'
        group by sc.campaign_id , sc.campaign_name,sc.cust_type,
                 sc.task_type, sc.end_time, sc.create_by, su.nick_name, sc.campaign_degree
    </select>

    <select id="selectSysCampaignListBranch"  resultMap="SysCampaignResult">
        select distinct
        sc.campaign_id,sc.campaign_name,
        sc.start_time,
        sc.end_time,
        sc.group_id,
        sc.task_type, sc.check_target,scgc3.customer_num, sc.create_time
        from sys_campaign_group_customer scgc inner join sys_campaign sc on scgc.campaign_id = sc.campaign_id

        inner join
        ( select campaign_id,count(*) as customer_num from (select distinct scgc1.cust_id,scgc1.campaign_id from sys_campaign_group_customer scgc1  where scgc1.dept_id in (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))) scgc2 group by scgc2.campaign_id) scgc3 on scgc3.campaign_id = sc.campaign_id

        <where>
            <if test="campaignName != null  and campaignName != ''">and campaign_name like concat('%', #{campaignName},
                '%')
            </if>
            <if test="deptId != null ">and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors)) </if>
            <if test="userId != null ">and scgc.user_id = #{userId}</if>
            <if test="startTime != null ">and sc.start_time = #{startTime}</if>
            <if test="endTime != null ">and sc.end_time = #{endTime}</if>
            <if test="campaignId != null  and campaignId != ''">and sc.campaign_id = #{campaignId}</if>
            <if test="taskType != null and taskType != ''"> and sc.task_type = #{taskType}</if>
            <if test="checkTarget != null and checkTarget != ''"> and sc.check_target = #{checkTarget}</if>
            <if test="custType != null and custType != ''"> and sc.cust_type = #{custType}</if>
            <if test="createRole != null and createRole != ''"> and sc.create_role = #{createRole}</if>
        </where>
        order by sc.create_time desc
    </select>


    <select id="selectSysCampaignById" parameterType="Long" resultMap="SysCampaignResult">
        <include refid="selectSysCampaignVo"/>
        where id = #{id}
    </select>


    <select id="selectCampaignGroupByCampaignId" parameterType="String" resultMap="com.ruoyi.ibs.list.mapper.SysGroupMapper.SysGroupResult" >
        select sg.group_id, sg.group_name ,sg.cust_type
        from sys_group sg
            left join sys_campaign_group scg on sg.group_id = scg.group_id
        where scg.campaign_id = #{id}
    </select>

    <insert id="insertSysCampaign" parameterType="SysCampaign" useGeneratedKeys="true" keyProperty="id">
        insert into sys_campaign
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="campaignName != null">campaign_name,</if>
            <if test="campaignId != null">campaign_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="userId != null">user_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>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="campaignDegree != null">campaign_degree,</if>
            <if test="remark != null">remark,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="campaignType != null">campaign_type,</if>
            <if test="claimType != null">claim_type,</if>
            <if test="distributeType != null">distribute_type,</if>
            <if test="distributeType2 != null">distribute_type2,</if>
            <if test="distributeType3 != null">distribute_type3,</if>
            <if test="distributeCont != null">distribute_Cont,</if>
            <if test="distributeCont2 != null">distribute_Cont2,</if>
            <if test="distributeCont3 != null">distribute_Cont3,</if>
            <if test="claimStartTime != null">claim_start_time,</if>
            <if test="claimEndTime != null">claim_end_time,</if>
            <if test="executer != null">executer,</if>
            <if test="createRole != null">create_role,</if>
            <if test="groupId != null">group_id,</if>
            <if test="secondPushStatus != null">second_push_status,</if>
            <if test="taskType != null">task_type,</if>
            <if test="checkTarget != null">check_target,</if>
            <if test="taskIndex != null">task_index,</if>
            <if test="reward != null">reward,</if>
            <if test="feedBack != null">feed_back,</if>
            <if test="custType != null">cust_type,</if>
            <if test="visitType != null ">visit_type,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="campaignName != null">#{campaignName},</if>
            <if test="campaignName != null">#{campaignId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="userId != null">#{userId},</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>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="campaignDegree != null">#{campaignDegree},</if>
            <if test="remark != null">#{remark},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="campaignType != null">#{campaignType},</if>
            <if test="claimType != null">#{claimType},</if>
            <if test="distributeType != null">#{distributeType},</if>
            <if test="distributeType2 != null">#{distributeType2},</if>
            <if test="distributeType3 != null">#{distributeType3},</if>
            <if test="distributeCont != null">#{distributeCont},</if>
            <if test="distributeCont2 != null">#{distributeCont2},</if>
            <if test="distributeCont3 != null">#{distributeCont3},</if>
            <if test="claimStartTime != null">#{claimStartTime},</if>
            <if test="claimEndTime != null">#{claimEndTime},</if>
            <if test="executer != null">#{executer},</if>
            <if test="createRole != null">#{createRole},</if>
            <if test="groupId != null">#{groupId},</if>
            <if test="secondPushStatus != null">#{secondPushStatus},</if>
            <if test="taskType != null">#{taskType},</if>
            <if test="checkTarget != null">#{checkTarget},</if>
            <if test="taskIndex != null">#{taskIndex},</if>
            <if test="reward != null">#{reward},</if>
            <if test="feedBack != null">#{feedBack},</if>
            <if test="custType != null">#{custType},</if>
            <if test="visitType != null">#{visitType},</if>
        </trim>
    </insert>


    <insert id="insertSysCampaignSecond" parameterType="SysCampaign" useGeneratedKeys="true" keyProperty="id">
        insert into sys_campaign_second
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="campaignName != null">campaign_name,</if>
            <if test="campaignId != null">campaign_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="userId != null">user_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>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="campaignDegree != null">campaign_degree,</if>
            <if test="remark != null">remark,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="campaignType != null">campaign_type,</if>
            <if test="claimType != null">claim_type,</if>
            <if test="distributeType != null">distribute_type,</if>
            <if test="distributeType2 != null">distribute_type2,</if>
            <if test="distributeType3 != null">distribute_type3,</if>
            <if test="distributeCont != null">distribute_Cont,</if>
            <if test="distributeCont2 != null">distribute_Cont2,</if>
            <if test="distributeCont3 != null">distribute_Cont3,</if>
            <if test="claimStartTime != null">claim_start_time,</if>
            <if test="claimEndTime != null">claim_end_time,</if>
            <if test="executer != null">executer,</if>
            <if test="createRole != null">create_role,</if>
            <if test="groupId != null">group_id,</if>
            <if test="secondPushStatus != null">second_push_status,</if>
            <if test="taskType != null">task_type,</if>
            <if test="checkTarget != null">check_target,</if>
            <if test="taskIndex != null">task_index,</if>
            <if test="reward != null">reward,</if>
            <if test="feedBack != null">feed_back,</if>
            <if test="custType != null">cust_type,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="campaignName != null">#{campaignName},</if>
            <if test="campaignName != null">#{campaignId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="userId != null">#{userId},</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>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="campaignDegree != null">#{campaignDegree},</if>
            <if test="remark != null">#{remark},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="campaignType != null">#{campaignType},</if>
            <if test="claimType != null">#{claimType},</if>
            <if test="distributeType != null">#{distributeType},</if>
            <if test="distributeType2 != null">#{distributeType2},</if>
            <if test="distributeType3 != null">#{distributeType3},</if>
            <if test="distributeCont != null">#{distributeCont},</if>
            <if test="distributeCont2 != null">#{distributeCont2},</if>
            <if test="distributeCont3 != null">#{distributeCont3},</if>
            <if test="claimStartTime != null">#{claimStartTime},</if>
            <if test="claimEndTime != null">#{claimEndTime},</if>
            <if test="executer != null">#{executer},</if>
            <if test="createRole != null">#{createRole},</if>
            <if test="groupId != null">#{groupId},</if>
            <if test="secondPushStatus != null">#{secondPushStatus},</if>
            <if test="taskType != null">#{taskType},</if>
            <if test="checkTarget != null">#{checkTarget},</if>
            <if test="taskIndex != null">#{taskIndex},</if>
            <if test="reward != null">#{reward},</if>
            <if test="feedBack != null">#{feedBack},</if>
            <if test="custType != null">#{custType},</if>
        </trim>
    </insert>
<select id="selectSysCampaignGroupList" >
    select campaign_id
    from sys_campaign_group
    where group_id=#{group_id}
</select>

    <select id="selectSysCampaignGroupCount"  resultType="int">
        select count(1)
        from sys_campaign_group
        where group_id=#{groupId}   and campaign_id=#{campaignId}
    </select>

    <insert id="insertSysCampaignGroup" parameterType="SysCampaign" useGeneratedKeys="true" keyProperty="id">
        insert into sys_campaign_group
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="campaignId != null">campaign_id,</if>
            <if test="groupId != null">group_id,</if>
            <if test="createBy != null">create_by,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="campaignId != null">#{campaignId},</if>
            <if test="groupId != null">#{groupId},</if>
            <if test="createBy != null">#{createBy},</if>
            sysdate()
        </trim>
    </insert>


    <update id="updateSysCampaign" parameterType="SysCampaign">
        update sys_campaign
        <trim prefix="SET" suffixOverrides=",">
            <if test="campaignName != null">campaign_name = #{campaignName},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="campaignDegree != null">campaign_degree = #{campaignDegree},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="campaignType != null">campaign_type = #{campaignType},</if>
            <if test="claimType != null">claim_type = #{claimType},</if>
            <if test="distributeType != null">distribute_type = #{distributeType},</if>
            <if test="distributeType2 != null">distribute_type2 = #{distributeType2},</if>
            <if test="distributeType3 != null">distribute_type3 = #{distributeType3},</if>
            <if test="claimStartTime != null">claim_start_time = #{claimStartTime},</if>
            <if test="claimEndTime != null">claim_end_time = #{claimEndTime},</if>
            <if test="executer != null">executer = #{executer},</if>
            <if test="createRole != null">create_role = #{createRole},</if>
            <if test="groupId != null">group_id = #{groupId},</if>
            <if test="secondPushStatus != null">second_push_status = #{secondPushStatus},</if>
            <if test="taskType != null">task_type = #{taskType},</if>
            <if test="checkTarget != null">check_target = #{checkTarget},</if>
            <if test="taskIndex != null">task_index = #{taskIndex},</if>
            <if test="reward != null">reward = #{reward},</if>
            <if test="feedBack != null">feed_back = #{feedBack},</if>
            <if test="custType != null">cust_type = #{custType},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateHeadHandledMissionSysCampaign" parameterType="SysCampaign"  >
        update head_handled_mission
        <trim prefix="SET" suffixOverrides=",">
            <if test="updateBy != null">update_by = #{updateBy},</if>
            update_time = sysdate(),
        </trim>
        where campaign_id = #{campaignId}
    </update>

    <delete id="deleteSysCampaignById" parameterType="Long">
        delete
        from sys_campaign
        where id = #{id}
    </delete>

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

    <select id="selectSysCampaignByCampaignId" parameterType="String" resultMap="SysCampaignResult">
        <include refid="selectSysCampaignVo"/>
        where campaign_id = #{campaignId}
    </select>

    <select id="selectSysCampaignSecondByCampaignId"  resultMap="SysCampaignResult">
        select id, campaign_name, campaign_id, dept_id, user_id, create_by, create_time, update_by, update_time, start_time, end_time,
               campaign_degree, remark, del_flag, campaign_type, claim_type, distribute_type, distribute_type2, distribute_type3,
               distribute_cont, distribute_cont2, distribute_cont3,claim_start_time,
               claim_end_time, executer, create_role, group_id, second_push_status, task_type, check_target, task_index, reward, feed_back, cust_type
        from sys_campaign_second
        where campaign_id = #{campaignId}  and dept_id =  #{deptId}
    </select>


    <select id="selectCountByCampaignName" parameterType="String" resultType="integer">
        select count(*) from sys_campaign sc
        <where>
            <if test="campaignName != null">and campaign_name  = #{campaignName}</if>
        </where>
    </select>

    <select id="selectCustomerListAll2"  resultMap="CustomerList">
        select group_concat(distinct group_name separator ',') as group_name, group_concat(distinct group_id separator
        ',') as group_id, cust_id, cust_isn,
        cust_name, cust_phone ,cust_idc, cust_type, push_status , grid_name,grid_name2, create_name,
        dept_name, teller_id,outlets, admin_flag,push_user_level, second_push_status,user_name
        <if test=" userId !=null and userId !=''  " >  , cust_claim_status  </if>
        <if test=" tabName =='cust_info_business'  " > , social_credit_code,lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , social_credit_code,lp_name  </if>
        from
        (select
        sg.group_name,
        sg.group_id,
        scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,sgc.cust_idc,sgc.cust_isn,sg.cust_type,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as create_name,su.user_name,sd.dept_name,su.user_name as teller_id,sdsu.dept_name as outlets, 'admin' as  admin_flag,push_user_level,scgc.second_push_status
        <if test=" userId !=null and userId !=''  " >  , scgc.cust_claim_status  </if>
        <if test=" tabName =='cust_info_business'  " > , sgc.social_credit_code,sgc.lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , sgc.social_credit_code,sgc.lp_name  </if>
        from sys_campaign_group_customer scgc
        left join ( select sgc2.* from sys_campaign_group scg
                        left join sys_group_customer sgc2 on sgc2.group_id = scg.group_id
                            where scg.campaign_id = #{campaignId}
                    ) sgcgs on scgc.cust_id = sgcgs.cust_id
        left join ${tabName} sgc on scgc.cust_id = sgc.cust_id
        left join sys_campaign sc on scgc.campaign_id = sc.campaign_id
        left join sys_group sg on sgcgs.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        left join sys_dept sdsu on su.dept_id = sdsu.dept_id
        <where>
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custClaimStatus != null  and customerList.custClaimStatus != ''"> and scgc.cust_claim_status = #{customerList.custClaimStatus}</if>
            <if test="customerList.unSuccessBranchFlag != null  and customerList.unSuccessBranchFlag != ''"  >  and (scgc.second_push_status != '1' or scgc.push_status != '1') </if>
            <if test="customerList.unSuccessHeadFlag != null  and customerList.unSuccessHeadFlag != ''"  > and scgc.push_status != '1' </if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != '' ">
                -- 支行网点查询本机构
                and (   scgc.dept_id   in   (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
                    or sdsu.dept_id   in   (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
                     or  sc.dept_id   = #{deptId}
                     )

            </if>
            <if test="userId != null and userId != ''">and scgc.user_id = #{userId}  </if>
            <if test=" type == 1   and  ( userId == null or userId == '' )   "> and ( scgc.user_id is null or scgc.push_user_level = 'branch'  )</if>

            <if test=" userManagerId !=null and userManagerId == 888  " >
                and scgc.org_distribute_status = '1'  and scgc.org_claim_status = '1'
            </if>
            <if test=" userManagerId !=null and userManagerId == 777  " >
                and scgc.org_distribute_status = '0'   and scgc.org_claim_status = '1'
            </if>
            <if test=" userManagerId !=null and userManagerId == 666  " >
                and scgc.org_claim_status = '0'
            </if>
        </where>
        )a1
        group by cust_id, cust_isn, cust_name, cust_phone , cust_idc, cust_type, push_status ,grid_name, grid_name2, create_name, user_name,teller_id,outlets,admin_flag,dept_name,push_user_level,second_push_status
        <if test=" userId !=null and userId !=''  " >  , cust_claim_status  </if>
        <if test=" tabName =='cust_info_business'  " > , social_credit_code,lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , social_credit_code,lp_name  </if>
    </select>

    <select id="selectCustomerListAll3"  resultType="CustomerList">
        select group_concat(distinct group_name separator ',') as group_name, group_concat(distinct group_id separator
        ',') as group_id, cust_id, cust_isn,
        cust_name, cust_phone ,cust_idc, cust_type, push_status , grid_name,grid_name2, create_name,
        dept_name, teller_id,outlets, admin_flag, user_name,push_user_level,second_push_status
        <if test=" tabName =='cust_info_business'  " > , social_credit_code,lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , social_credit_code,lp_name  </if>
        from
        (select
        sg.group_name,
        sg.group_id,
        scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,sgc.cust_idc,sgc.cust_isn,sg.cust_type,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as create_name,su.user_name,sd.dept_name,su.user_name as teller_id,sdsu.dept_name as outlets, 'admin' as  admin_flag,scgc.push_user_level,scgc.second_push_status
        <if test=" tabName =='cust_info_business'  " > , sgc.social_credit_code,sgc.lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , sgc.social_credit_code,sgc.lp_name  </if>
        from sys_campaign_group_customer scgc
        left join ( select sgc2.* from sys_campaign_group scg
        left join sys_group_customer sgc2 on sgc2.group_id = scg.group_id
        where scg.campaign_id = #{campaignId}
        ) sgcgs on scgc.cust_id = sgcgs.cust_id
        left join sys_campaign sc on sc.campaign_id = scgc.campaign_id
        left join ${tabName} sgc on scgc.cust_id = sgc.cust_id
        left join sys_group sg on sgcgs.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        left join sys_dept sdsu on su.dept_id = sdsu.dept_id
        <where>
             <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
             <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
             <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>

             <if test="roleUser == 0 or roleUser == 2 or roleUser == 3 or roleUser == 4">
                 and sc.dept_id != #{deptId}
             </if>

            <if test="roleUser == 5 ">
                and scgc.push_user_level='head' and scgc.user_id is not null and  scgc.dept_id = #{deptId}
            </if>

            <if test="roleUser == 6 ">
                and  su.dept_id = #{deptId}
            </if>
        </where>
        )a1
        group by cust_id, cust_isn, cust_name, cust_phone , cust_idc, cust_type, push_status ,grid_name, grid_name2, create_name, user_name,teller_id,outlets,admin_flag,dept_name,push_user_level,second_push_status
        <if test=" tabName =='cust_info_business'  " > , social_credit_code,lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , social_credit_code,lp_name  </if>
    </select>



    <select id="selectCustomerListAll"  resultMap="CustomerList">
        select group_concat(distinct group_name separator ',') as group_name, group_concat(distinct group_id separator
        ',') as group_id, cust_id,  cust_isn,
        cust_name, cust_phone ,cust_idc, cust_type, push_status , grid_name,grid_name2, create_name, user_name,push_user_level,
         teller_id,outlets, admin_flag, dept_name,second_push_status
        <if test=" userId !=null and userId !=''  " >  , cust_claim_status  </if>
        <if test=" tabName =='cust_info_business'  " > , social_credit_code,lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , social_credit_code,lp_name  </if>
        from
        (select
        sg.group_name,
        sg.group_id,
        scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,sgc.cust_idc,sgc.cust_isn,sg.cust_type,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,scgc.second_push_status,
        case  when   su.nick_name   is not null   then su.nick_name else sucs.nick_name end  as create_name,
        case  when   su.user_name   is not null   then su.user_name   else sucs.user_name  end as user_name,
        case  when   sd.dept_name   is not null   then sd.dept_name  else sdsucs.dept_name  end as dept_name,
        case  when   su.user_name   is not null   then su.user_name  else sucs.user_name  end as teller_id,
        case when   sdsu.dept_name   is not null   then sdsu.dept_name else sdsucdd.dept_name  end as outlets,
        'admin' as  admin_flag,scgc.push_user_level
        <if test=" userId !=null and userId !=''  " >  , scgc.cust_claim_status  </if>
        <if test=" tabName =='cust_info_business'  " > , sgc.social_credit_code,sgc.lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , sgc.social_credit_code,sgc.lp_name  </if>
        from sys_campaign_group_customer scgc
        left join    sys_campaign_group_customer_supplement scgcs on scgc.campaign_id = scgcs.campaign_id and scgc.cust_id = scgcs.cust_id and scgcs.del_flag='0'
        left join   (select sgc2.* from sys_campaign_group scg
        left join sys_group_customer  sgc2 on sgc2.group_id = scg.group_id
        where   scg.campaign_id = #{campaignId}
        )   sgcgs on scgc.cust_id = sgcgs.cust_id
        left join ${tabName} sgc on scgc.cust_id = sgc.cust_id
        left join sys_group sg on sgcgs.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        left join sys_dept  sdsu   on  su.dept_id = sdsu.dept_id
        left join sys_user sucs  on  scgcs.user_id = sucs.user_id
        left join sys_dept  sdsucdd   on  sdsucdd.dept_id = sucs.dept_id
        left join sys_dept  sdsucs   on  scgcs.dept_id = sdsucs.dept_id
        <where>
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.unSuccessBranchFlag != null  and customerList.unSuccessBranchFlag != ''"  > and (scgc.second_push_status != '1' or scgc.push_status != '1') </if>
            <if test="customerList.unSuccessHeadFlag != null  and customerList.unSuccessHeadFlag != ''"  > and scgc.push_status != '1' </if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != '' ">
                and (   scgc.dept_id   in   (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
                or sdsu.dept_id   in   (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
                )
            </if>
            <if test=" userId !=null and userId !=''  " >and scgc.user_id = #{userId}  </if>
            <if test=" type == 1   and  ( userId == null or userId == '' )   "> and ( scgc.user_id is null or scgc.push_user_level = 'branch'  )</if>

            <if test=" userManagerId !=null and userManagerId == 888  " >
                and scgc.org_distribute_status = '1'  and scgc.org_claim_status = '1'
            </if>
            <if test=" userManagerId !=null and userManagerId == 777  " >
                and scgc.org_distribute_status = '0'   and scgc.org_claim_status = '1'
            </if>
            <if test=" userManagerId !=null and userManagerId == 666  " >
                and scgc.org_claim_status = '0'
            </if>
        </where>
        )a1
        group by cust_id, cust_isn, cust_name, cust_phone , cust_idc, cust_type, push_status ,grid_name, grid_name2, create_name, user_name,teller_id,outlets,admin_flag,dept_name,second_push_status,push_user_level
        <if test=" userId !=null and userId !=''  " >  , cust_claim_status  </if>
        <if test=" tabName =='cust_info_business'  " > , social_credit_code,lp_name  </if>
        <if test=" tabName =='cust_info_merchant'  " > , social_credit_code,lp_name  </if>
   </select>

    <select id="selectCustomerListRetail"  resultMap="CustomerList">
        select scgc.group_id,sg.group_name,scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,scgc.cust_idc,sgc.cust_isn,sg.cust_type,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as user_name,sd.dept_name,su.user_name as teller_id,scgc.outlets
        from sys_campaign_group_customer scgc
        inner join cust_info_retail sgc on scgc.cust_id = sgc.cust_id
        inner join sys_group sg  on scgc.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        <where>
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != ''">  and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            </if>
        </where>

    </select>

    <select id="selectCustomerListMerchant"  resultMap="CustomerList">
        select scgc.group_id,sg.group_name,scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,scgc.cust_idc,sgc.cust_isn,sg.cust_type,sgc.lp_name,sgc.social_credit_code,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as user_name,sd.dept_name,su.user_name as teller_id,scgc.outlets
        from sys_campaign_group_customer scgc
        inner join cust_info_merchant sgc on scgc.cust_id = sgc.cust_id
        inner join sys_group sg  on scgc.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        <where>
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != ''">  and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            </if>
        </where>

    </select>


    <select id="selectCustomerListBusiness"  resultMap="CustomerList">
        select scgc.group_id,sg.group_name,scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,scgc.cust_idc,sgc.cust_isn,sg.cust_type,sgc.lp_name,sgc.social_credit_code,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as user_name,sd.dept_name,su.user_name as teller_id,scgc.outlets
        from sys_campaign_group_customer scgc
        inner join cust_info_business sgc on scgc.cust_id = sgc.cust_id
        inner join sys_group sg  on scgc.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        <where>
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != ''">  and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            </if>
        </where>

    </select>


    <select id="selectCustomerListRetailBranchList"  resultMap="CustomerList">
        select scgc.group_id,sg.group_name,scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,scgc.cust_idc,sgc.cust_isn,sg.cust_type,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as user_name,sd.dept_name,su.user_name as teller_id,scgc.outlets
        from sys_campaign_group_customer scgc
        inner join cust_info_retail sgc on scgc.cust_id = sgc.cust_id
        inner join sys_group sg  on scgc.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        <where>
            scgc.org_claim_status in ('0','1')
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != ''">  and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            </if>
        </where>

    </select>

    <select id="selectCustomerListMerchantBranchList"  resultMap="CustomerList">
        select scgc.group_id,sg.group_name,scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,scgc.cust_idc,sgc.cust_isn,sg.cust_type,sgc.lp_name,sgc.social_credit_code,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as user_name,sd.dept_name,su.user_name as teller_id,scgc.outlets
        from sys_campaign_group_customer scgc
        inner join cust_info_merchant sgc on scgc.cust_id = sgc.cust_id
        inner join sys_group sg  on scgc.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        <where>
            scgc.org_claim_status in ('0','1')
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != ''">  and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            </if>
        </where>

    </select>


    <select id="selectCustomerListBusinessBranchList"  resultMap="CustomerList">
        select scgc.group_id,sg.group_name,scgc.cust_id,
        sgc.cust_name,scgc.cust_phone ,scgc.cust_idc,sgc.cust_isn,sg.cust_type,sgc.lp_name,sgc.social_credit_code,
        scgc.push_status ,scgc.grid_name,scgc.grid_name2,
        su.nick_name as user_name,sd.dept_name,su.user_name as teller_id,scgc.outlets
        from sys_campaign_group_customer scgc
        inner join cust_info_business sgc on scgc.cust_id = sgc.cust_id
        inner join sys_group sg  on scgc.group_id = sg.group_id
        left join sys_dept sd on scgc.dept_id = sd.dept_id
        left join sys_user su on scgc.user_id = su.user_id
        <where>
            scgc.org_claim_status in ('0','1')
            <if test="campaignId != null  and campaignId != ''"> and scgc.campaign_id = #{campaignId}</if>
            <if test="customerList.groupId != null  and customerList.groupId != ''"> and scgc.group_id = #{customerList.groupId}</if>
            <if test="customerList.custId != null  and customerList.custId != ''"> and scgc.cust_id like concat('%',  #{customerList.custId}, '%') </if>
            <if test="customerList.custIdc != null  and customerList.custIdc != ''"> and scgc.cust_idc like concat('%',  #{customerList.custIdc}, '%') </if>
            <if test="customerList.custIsn != null  and customerList.custIsn != ''"> and sgc.cust_isn like concat('%',  #{customerList.custIsn}, '%') </if>
            <if test="customerList.custName != null  and customerList.custName != ''"> and sgc.cust_name like concat('%',  #{customerList.custName}, '%')</if>
            <if test="customerList.deptName != null  and customerList.deptName != ''"> and sd.dept_name like concat('%',  #{customerList.deptName}, '%')</if>
            <if test="customerList.outlets != null  and customerList.outlets != ''"> and scgc.outlets like concat('%',  #{customerList.outlets}, '%')</if>
            <if test="customerList.username != null  and customerList.username != ''"> and su.nick_name like concat('%',  #{customerList.username}, '%')</if>
            <if test="customerList.socialCreditCode != null  and customerList.socialCreditCode != ''"> and sgc.social_credit_code like concat('%',  #{customerList.socialCreditCode}, '%')</if>
            <if test="customerList.pushStatus != null  and customerList.pushStatus != ''"> and scgc.push_status = #{customerList.pushStatus}</if>
            <if test="deptId != null  and deptId != ''">  and scgc.dept_id in
                (select dept_id from sys_dept where dept_id = #{deptId} or find_in_set(#{deptId},ancestors))
            </if>
        </where>

    </select>


    <select id="selectAddressAndPhoneByCustTypeAndCustIds" parameterType="String" resultMap="AddressAndPhone">
        <if test="custType == 0">
            select cust_id,cust_idc, cust_location,register_location,job_location,cust_phone from cust_info_retail where cust_idc in
            <foreach item="id" collection="custIds" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="custType == 1">
            select cust_id,social_credit_code, cust_location,register_location,cust_phone,tel from cust_info_merchant where social_credit_code in
            <foreach item="id" collection="custIds" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="custType == 2">
            select cust_id,social_credit_code, cust_location,register_location,cust_phone,tel from cust_info_business where social_credit_code in
            <foreach item="id" collection="custIds" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>

    </select>

    <select id="selectDeptAndOutletsByUserId" parameterType="String" resultMap="DeptAndOutlets">

            select su.user_id,su.dept_id,su.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type from sys_user su inner join sys_dept sd on su.dept_id = sd.dept_id where  dept_type = 'branch' and user_id
            in
        <foreach item="id" collection="userIds" open="(" separator="," close=")">
            #{id}
        </foreach>
        union

        select su.user_id ,sd.parent_id as dept_id,su.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type from sys_user su inner join sys_dept sd on su.dept_id = sd.dept_id where
       dept_type = 'outlet' and user_id in
        <foreach item="id" collection="userIds" open="(" separator="," close=")">
            #{id}
        </foreach>

        union

        select su.user_id ,sd.dept_id as dept_id,su.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type from sys_user su inner join sys_dept sd on su.dept_id = sd.dept_id where
        dept_type = 'head' and user_id in
        <foreach item="id" collection="userIds" open="(" separator="," close=")">
            #{id}
        </foreach>

    </select>

    <select id="selectDeptAndOutletsBydeptIds" parameterType="String" resultMap="DeptAndOutlets">

        select sd.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type,sd.dept_id from sys_dept sd where  sd.dept_type = 'branch' and dept_id
        in
        <foreach item="id" collection="deptIds" open="(" separator="," close=")">
            #{id}
        </foreach>
        union
        select sd.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type,sd.parent_id as dept_id  from sys_dept sd where sd.dept_type = 'outlet' and dept_id
        in
        <foreach item="id" collection="deptIds" open="(" separator="," close=")">
            #{id}
        </foreach>
        union
        select sd.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type,sd.dept_id as dept_id  from sys_dept sd where sd.dept_type = 'head' and dept_id
        in
        <foreach item="id" collection="deptIds" open="(" separator="," close=")">
            #{id}
        </foreach>

    </select>

    <select id="selectDeptAndOutletsByUserName" parameterType="String" resultMap="DeptAndOutlets">

        select su.user_id,su.dept_id,null as outlets_id,null as outlets,sd.dept_type from sys_user su inner join sys_dept sd on su.dept_id = sd.dept_id where  dept_type = 'branch' and user_name
       =#{userName}
        union
        select su.user_id ,sd.parent_id as dept_id,su.dept_id as outlets_id,sd.dept_name as outlets,sd.dept_type from sys_user su inner join sys_dept sd on su.dept_id = sd.dept_id where
        dept_type = 'outlet' and user_name=#{userName}

    </select>


    <select id="selectGridInfoTsByGridIds" parameterType="String" resultMap="GridInfoTs">

        select distinct gdur.grid_id,gdur.user_name ,gdur.relate_dept_id,gdsr.shape_id,dsc.cust_id,dsc.cust_name  from grid_draw_user_relate gdur inner join grid_draw_shape_relate gdsr on gdsr.grid_id  = gdur.grid_id
        inner join draw_shape_cust dsc on dsc.shape_id = gdsr.shape_id
        where gdur.grid_id in
        <foreach item="id" collection="gridIds" open="(" separator="," close=")">
            #{id}
        </foreach>

    </select>


    <select id="selectVisitInfoList" parameterType="VisitInfo" resultMap="VisitInfoResult">
        select vi.id, vi.campaign_id, concat( vi.vis_name,'-', vi.vis_id) as vis_name, vi.vis_id, vi.dept_id, vi.vis_time, vi.cust_name, vi.cust_idc, vi.social_credit_code, vi.create_by, vi.create_time, vi.update_by, vi.update_time, vi.remark, vi.cust_type, vi.dept_name,
        vi.campaign_name
        from visit_info vi
        left join sys_campaign sc on sc.campaign_id = vi.campaign_id
        <where>
            <if test="campaignId != null  and campaignId != ''"> and vi.campaign_id = #{campaignId}</if>
            <if test="visName != null  and visName != ''"> and vi.vis_name like concat('%', #{visName}, '%')</if>
            <if test="visId != null  and visId != ''"> and vi.vis_id = #{visId}</if>
            <if test="deptId != null  and deptId != ''"> and vi.dept_id = #{deptId}</if>
            <if test="visTime != null "> and vi.vis_time = #{visTime}</if>
            <if test="custName != null  and custName != ''"> and vi.cust_name like concat('%', #{custName}, '%')</if>
            <if test="custIdc != null  and custIdc != ''"> and vi.cust_idc = #{custIdc}</if>
            <if test="socialCreditCode != null  and socialCreditCode != ''"> and vi.social_credit_code = #{socialCreditCode}</if>
            <if test="custType != null  and custType != ''"> and vi.cust_type = #{custType}</if>
            <if test="deptName != null  and deptName != ''"> and vi.dept_name like concat('%', #{deptName}, '%')</if>
        </where>
    </select>

    <update id="updateVisitInfo" parameterType="VisitInfo">
        update visit_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

</mapper>