<?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.task.mapper.VisitTaskMapper">

    <select id="getVisitTaskList" parameterType="VisitTaskListDTO" resultType="VisitTaskListVO">
        select cust_type,campaign_name, campaign_id, start_time, end_time,task_type, check_target, visit_type,
        campaign_degree, remark,is_cooperate, hide_list from
        (
        select
        sc.campaign_name, sc.campaign_id, sc.start_time, sc.end_time, sc.task_type, sc.check_target, sc.visit_type,
        sc.campaign_degree, sc.cust_type, sc.remark,sc.is_cooperate, sc.hide_list
        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
        where sgc.user_id = #{userId} and sc.del_flag = '0'
        <if test="custType != null and custType != ''">
            and sc.cust_type = #{custType}
        </if>
        <if test="campaignName != null and campaignName != ''">
            and sc.campaign_name like concat('%', #{campaignName}, '%')
        </if>
        <if test="visitType != null and visitType != ''">
            and sc.visit_type = #{visitType}
        </if>
        <if test="startTimeMonth != null and startTimeMonth != '' ">
            and sc.start_time like concat(#{startTimeMonth}, '%')
        </if>
        and sgc.cust_claim_status = 1
        and sc.claim_end_time > current_timestamp()
        and sc.is_cooperate = '0'
        union
        select
        sc.campaign_name, sc.campaign_id, sc.start_time, sc.end_time, sc.task_type, sc.check_target, sc.visit_type,
        sc.campaign_degree, sc.cust_type, sc.remark,sc.is_cooperate, sc.hide_list
        from sys_campaign sc
        left join sys_campaign_group scg on scg.campaign_id = sc.campaign_id
        left join sys_campaign_group_customer_coop sgc on sgc.campaign_id = sc.campaign_id
        left join sys_group sg on sg.group_id = scg.group_id
        where (sgc.user_id = #{userId} or sgc.user_id is null) and sc.del_flag = '0'
        and #{deptId} in (select dept_id from sys_dept where dept_id = sc.cooperate_dept_id or  find_in_set(sc.cooperate_dept_id,ancestors))
        and sc.claim_end_time > current_timestamp()
        and sc.is_cooperate = '1'
        <if test="custType != null and custType != ''">
            and sc.cust_type = #{custType}
        </if>
        <if test="campaignName != null and campaignName != ''">
            and sc.campaign_name like concat('%', #{campaignName}, '%')
        </if>
        <if test="visitType != null and visitType != ''">
            and sc.visit_type = #{visitType}
        </if>
        <if test="startTimeMonth != null and startTimeMonth != '' ">
            and sc.start_time like concat(#{startTimeMonth}, '%')
        </if>
        )ttt
        group by cust_type,campaign_name, campaign_id, start_time, end_time,task_type, check_target,  visit_type, campaign_degree, remark,is_cooperate, hide_list
        order by end_time
    </select>

    <select id="getVisitTargetList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select
        a.cust_id,
        a.cust_idc,
        a.social_credit_code,
        a.cust_isn,
        a.cust_name,
        a.cust_phone,
        b.id as record_id,
        isnull(b.sign_id) as sign_flag,
        b.feedback_id,
        b.feedback_type,
        b.feedback_template_id,
        case when b.cust_id is null then false else true end as finish_flag,
        f.address
        from sys_campaign_group_customer a
        left join task_visit_record b on b.cust_id = a.cust_id and b.campaign_id = a.campaign_id and b.user_id = a.user_id
        left join task_visit_sign c on b.sign_id = c.id
        left join ibs_anchor e on a.cust_id = e.cust_id
        left join ibs_anchor_address f on e.address_id = f.id
        where a.campaign_id =  #{campaignId} and a.user_id = #{userId} and a.cust_claim_status = 1
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or f.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null ">
            having finish_flag = #{finishFlag}
        </if>
        order by c.sign_in_time desc, a.cust_id
        limit #{start}, #{size}
    </select>
    <select id="getCoopVisitTargetList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select
        a.cust_id,
        a.cust_idc,
        a.social_credit_code,
        a.cust_isn,
        a.cust_name,
        a.cust_phone,
        b.id as record_id,
        isnull(b.sign_id) as sign_flag,
        b.feedback_id,
        b.feedback_type,
        b.feedback_template_id,
        case when b.cust_id is null then false else true end as finish_flag,
        f.address
        from sys_campaign_group_customer_coop a
        left join task_visit_record b on b.cust_id = a.cust_id and b.campaign_id = a.campaign_id and b.user_id = a.user_id
        left join task_visit_sign c on b.sign_id = c.id
        left join ibs_anchor e on a.cust_id = e.cust_id
        left join ibs_anchor_address f on e.address_id = f.id
        where a.campaign_id =  #{campaignId} and (a.user_id = #{userId} or a.user_id is null)
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or f.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null ">
            having finish_flag = #{finishFlag}
        </if>
        order by c.sign_in_time desc, a.cust_id
        limit #{start}, #{size}
    </select>

    <select id="getVisitTargetRetailList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select a.cust_id, a.cust_idc, a.cust_isn, a.cust_gender, a.cust_name, a.cust_phone, b.id as record_id, isnull(
        b.sign_id) as sign_flag, b.feedback_id, b.feedback_type, b.feedback_template_id,case when b.cust_id is null then false else true end as finish_flag, d.address
        from cust_info_retail a
        left join (
            select a.id, a.cust_id , a.sign_id, a.feedback_id, a.feedback_type, a.feedback_template_id, b.sign_in_time from task_visit_record a
            left join task_visit_sign b on a.sign_id = b.id
            where a.campaign_id = #{campaignId} and a.user_id = #{userId}
        ) b on a.cust_id = b.cust_id
        left join ibs_anchor c on a.cust_idc = c.legal_id
        left join ibs_anchor_address d on c.address_id = d.id
        where a.cust_id in (
            select cust_id from sys_campaign_group_customer
                where campaign_id = #{campaignId}
                and user_id = #{userId}
                and cust_claim_status = 1
        )
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or d.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null ">
            having finish_flag = #{finishFlag}
        </if>
        order by b.sign_in_time desc
        limit #{start}, #{size}
    </select>

    <select id="getCoopVisitTargetRetailList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select a.cust_id, a.cust_idc, a.cust_isn, a.cust_gender, a.cust_name, a.cust_phone, b.id as record_id, isnull(b.sign_id) as sign_flag,
        b.feedback_id, b.feedback_type, b.feedback_template_id,case when b.cust_id is null then false else true end as finish_flag, d.address
        from cust_info_retail a
        left join (
            select a.id, a.cust_id , a.sign_id, a.feedback_id, a.feedback_type, a.feedback_template_id, b.sign_in_time from task_visit_record a
            left join task_visit_sign b on a.sign_id = b.id
            where a.campaign_id = #{campaignId} and a.user_id = #{userId}
        ) b on a.cust_id = b.cust_id
        left join ibs_anchor c on a.cust_idc = c.legal_id
        left join ibs_anchor_address d on c.address_id = d.id
        where a.cust_id in (
            select cust_id
            from sys_campaign_group_customer_coop
            where campaign_id =  #{campaignId}
            and (user_id = #{userId} or user_id is null)
        )
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or d.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null ">
            having finish_flag = #{finishFlag}
        </if>
        order by b.sign_in_time desc
        limit #{start}, #{size}
    </select>

    <select id="getVisitTargetBusinessList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select a.cust_id, a.cust_idc, a.social_credit_code, a.cust_gender, a.cust_name, a.cust_phone, a.status, b.id as
        record_id, isnull( b.sign_id) as sign_flag , b.feedback_id, b.feedback_type, b.feedback_template_id,case when b.cust_id is null then false else true end as finish_flag, d.address
        from cust_info_business a
        left join (
            select a.id, a.cust_id , a.sign_id, a.feedback_id, a.feedback_type, a.feedback_template_id, b.sign_in_time from task_visit_record a
            left join task_visit_sign b on a.sign_id = b.id
            where a.campaign_id = #{campaignId} and a.user_id = #{userId}
        ) b on a.cust_id = b.cust_id
        left join ibs_anchor c on a.social_credit_code = c.legal_id
        left join ibs_anchor_address d on c.address_id = d.id
        where a.cust_id in (
            select cust_id from sys_campaign_group_customer
            where campaign_id = #{campaignId}
              and user_id = #{userId}
              and cust_claim_status = 1
        )
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or d.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null">
            having finish_flag = #{finishFlag}
        </if>
        order by b.sign_in_time desc
        limit #{start}, #{size}
    </select>

    <select id="getCoopVisitTargetBusinessList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select a.cust_id, a.cust_idc, a.social_credit_code, a.cust_gender, a.cust_name, a.cust_phone, a.status, b.id as
        record_id, isnull( b.sign_id) as sign_flag , b.feedback_id, b.feedback_type, b.feedback_template_id,case when b.cust_id is null then false else true end as finish_flag, d.address
        from cust_info_business a
        left join (
            select a.id, a.cust_id , a.sign_id, a.feedback_id, a.feedback_type, a.feedback_template_id, b.sign_in_time from task_visit_record a
            left join task_visit_sign b on a.sign_id = b.id
            where a.campaign_id = #{campaignId} and a.user_id = #{userId}
        ) b on a.cust_id = b.cust_id
        left join ibs_anchor c on a.social_credit_code = c.legal_id
        left join ibs_anchor_address d on c.address_id = d.id
        where a.cust_id in (
        select cust_id
        from sys_campaign_group_customer_coop
        where campaign_id =  #{campaignId}
        and (user_id = #{userId} or user_id is null)
        )
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or d.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null">
            having finish_flag = #{finishFlag}
        </if>
        order by b.sign_in_time desc
        limit #{start}, #{size}
    </select>

    <select id="getVisitTargetMerchantList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select a.cust_id, a.cust_idc, a.social_credit_code, a.cust_gender, a.cust_name, a.cust_phone, a.status, b.id as
        record_id, isnull( b.sign_id) as sign_flag , b.feedback_id, b.feedback_type, b.feedback_template_id,case when b.cust_id is null then false else true end as finish_flag, d.address
        from cust_info_merchant a
        left join (
            select a.id, a.cust_id , a.sign_id, a.feedback_id, a.feedback_type, a.feedback_template_id, b.sign_in_time from task_visit_record a
            left join task_visit_sign b on a.sign_id = b.id
            where a.campaign_id = #{campaignId} and a.user_id = #{userId}
        ) b on a.cust_id = b.cust_id
        left join ibs_anchor c on a.social_credit_code = c.legal_id
        left join ibs_anchor_address d on c.address_id = d.id
        where a.cust_id in (
            select cust_id from sys_campaign_group_customer
            where campaign_id = #{campaignId}
              and user_id = #{userId}
              and cust_claim_status = 1
        )
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or d.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null ">
            having finish_flag = #{finishFlag}
        </if>
        order by b.sign_in_time desc
        limit #{start}, #{size}
    </select>

    <select id="getCoopVisitTargetMerchantList" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select a.cust_id, a.cust_idc, a.social_credit_code, a.cust_gender, a.cust_name, a.cust_phone, a.status, b.id as
        record_id, isnull( b.sign_id) as sign_flag , b.feedback_id, b.feedback_type, b.feedback_template_id,case when b.cust_id is null then false else true end as finish_flag, d.address
        from cust_info_merchant a
        left join (
            select a.id, a.cust_id , a.sign_id, a.feedback_id, a.feedback_type, a.feedback_template_id, b.sign_in_time from task_visit_record a
            left join task_visit_sign b on a.sign_id = b.id
            where a.campaign_id = #{campaignId} and a.user_id = #{userId}
        ) b on a.cust_id = b.cust_id
        left join ibs_anchor c on a.social_credit_code = c.legal_id
        left join ibs_anchor_address d on c.address_id = d.id
        where a.cust_id in (
        select cust_id
        from sys_campaign_group_customer_coop
        where campaign_id =  #{campaignId}
        and (user_id = #{userId} or user_id is null))
        <if test="custId != null and custId != ''">
            and (a.cust_id like concat('%', #{custId}, '%') or  a.cust_name like concat('%', #{custId}, '%') or d.address like concat('%', #{custId}, '%'))
        </if>
        <if test="finishFlag != null ">
            having finish_flag = #{finishFlag}
        </if>
        order by b.sign_in_time desc
        limit #{start}, #{size}
    </select>
    <select id="getAppointmentVisitTarget" parameterType="VisitTargetDTO" resultType="VisitTargetVO">
        select id, cust_id , sign_id, feedback_id, feedback_type, feedback_template_id from task_visit_record where
        campaign_id = #{campaignId} and user_id = #{userId} and cust_id = #{custId}
    </select>

    <select id="countCampaignTotal" resultType="Long">
        select  count(distinct cust_id) from sys_campaign_group_customer where campaign_id = #{campaignId} and user_id = #{userId}
    </select>

    <select id="countCoopCampaignTotal"  resultType="Long">
        select count(distinct cust_id) from sys_campaign_group_customer_coop where campaign_id = #{campaignId} and (user_id = #{userId} or user_id is null)
    </select>

    <select id="countCampaignComplete" resultType="Long">
        select count(distinct cust_id) from task_visit_record where campaign_id = #{campaignId} and user_id = #{userId}
    </select>

    <select id="selectFeedbackIdByRecordId" resultType="String">
        select feedback_id from task_visit_record where id = #{recordId}
    </select>

</mapper>