<?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.dcsoft.system.visitor.mapper.VisitorMapper">

    <resultMap type="Visitor" id="VisitorResult">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="phone" column="phone"/>
        <result property="sex" column="sex"/>
        <result property="avatar" column="avatar"/>
        <result property="idcard" column="idcard"/>
        <result property="carNo" column="car_no"/>
        <result property="userId" column="user_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="flag" column="flag"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="matter" column="matter"/>
        <result property="res" column="res"/>
        <result property="guid" column="guid"/>
        <result property="faceGuid" column="face_guid"/>
        <result property="remark" column="remark"/>
        <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="parentId" column="parent_id"/>
        <result property="inTime" column="in_time"/>
        <result property="outTime" column="out_time"/>
        <result property="peopleName" column="people_name"/>
        <result property="peoplePhone" column="people_phone"/>
        <result property="source" column="source"/>
        <result property="driver" column="driver"/>
        <result property="transportPermit" column="transport_permit"/>
        <result property="security" column="security"/>
        <result property="position" column="position"/>
        <result property="visitorPosition" column="visitorPosition"/>
        <result property="visitingUnit" column="visiting_unit"/>
        <result property="reviewer2" column="reviewer2"/>
        <result property="reviewer3" column="reviewer3"/>
        <result property="examineState" column="examine_state"/>
        <result property="park" column="park"/>
        <result property="visitorType" column="visitor_type"/>
        <result property="currentNode" column="current_node"/>
        <association property="branch" column="dept_id" javaType="SysBranch" resultMap="branchResult"/>
        <association property="people" column="user_id" javaType="SysPeople" resultMap="peopleResult"/>
        <association property="examine" column="id" javaType="VisVisitorExamine" resultMap="examineResult"/>

    </resultMap>

    <resultMap id="examineResult" type="VisVisitorExamine">
        <id property="visitorId" column="id"/>
        <result property="admittanceStart" column="admittance_start"/>
        <result property="admittanceEnd" column="admittance_end"/>
        <result property="pointId" column="point_id"/>
        <result property="examine" column="examine"/>
        <result property="createBy" column="examine_name"/>

    </resultMap>

    <resultMap id="branchResult" type="SysBranch">
        <id property="id" column="dept_id"/>
        <result property="name" column="branch_name"/>
    </resultMap>

    <resultMap id="peopleResult" type="SysPeople">
        <id property="id" column="user_id"/>
        <result property="name" column="people_name"/>
    </resultMap>

    <sql id="selectVisitorVo">
        select distinct
               vv.id,
               vv.name,
               vv.phone,
               vv.sex,
               vv.avatar,
               vv.idcard,
               vv.car_no,
               vv.user_id,
               vv.dept_id,
               vv.flag,
               vv.start_time,
               vv.end_time,
               vv.matter,
               vv.res,
               vv.guid,
               vv.face_guid,
               vv.remark,
               vv.create_by,
               vv.create_time,
               vv.update_by,
               vv.parent_id,
               vv.update_time,
               vve.admittance_start,
               vve.admittance_end,
               vve.point_id,
               sb.name                                        branch_name,
               if(sp.name is not null, sp.name, vv.user_name) people_name,
               vve.examine,
               vv.in_time,
               vv.out_time,
               vve.create_by                                  examine_name,
               sp.phone                                       people_phone,
               vv.source,
               sp.position,
               vv.position                                    visitorPosition,
               vv.visiting_unit,
               vv.reviewer2,
               vv.reviewer3,
               vv.current_node,
               vv.examine_state,
               vv.visitor_type,
               vv.park
        from vis_visitor vv
                 left join vis_visitor_examine vve on vv.id = vve.visitor_id
                 left join sys_branch sb on vv.dept_id = sb.id
                 left join sys_people sp on vv.user_id = sp.id
                 left join sys_dept d on d.dept_id = sb.dept_id
                 left join sys_user u on u.user_id = sp.user_id
                 left join vis_visitor_audit_records vvar on vvar.visitor_id = vv.id
    </sql>

    <select id="selectVisitorList" parameterType="Visitor" resultMap="VisitorResult">
        <include refid="selectVisitorVo"/>
        <where>
            and vv.parent_id is null
            <if test="name != null  and name != ''">and vv.name like concat('%', #{name}, '%')</if>
            <if test="phone != null  and phone != ''">and vv.phone like concat('%', #{phone}, '%')</if>
            <if test="tabFlag == null or tabFlag == ''">
                <if test="peoplePhone != null and peoplePhone != ''">
                    and (sp.phone in
                    <foreach collection="peoplePhone.split(',')" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach>
                    or vv.phone = #{peoplePhone})
                </if>
            </if>
            <if test="tabFlag != null and tabFlag != ''">
                <if test="peoplePhone != null and peoplePhone != ''">
                    and sp.phone in
                    <foreach collection="peoplePhone.split(',')" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach>
                </if>
            </if>
            <if test="sex != null  and sex != ''">and vv.sex = #{sex}</if>
            <if test="avatar != null  and avatar != ''">and vv.avatar = #{avatar}</if>
            <if test="userId != null ">and vv.user_id = #{userId}</if>
            <if test="deptId != null ">and vv.dept_id = #{deptId}</if>
            <if test="startTime != null ">and date_format(vv.start_time, '%Y-%m-%d') = date_format(#{startTime},
                '%Y-%m-%d')
            </if>
            <if test="peoplePhone != null  and peoplePhone != '' and position == null">
                <if test="tabFlag == 'wsh' ">
                    and vvar.id is null
                </if>
                <if test="tabFlag == 'ysh' ">
                    and vvar.id is not null
                </if>
            </if>
            <if test="peoplePhone == null or peoplePhone == '' or position != null">
                <if test="tabFlag == 'wsh' ">
                    and vve.id is null
                </if>
                <if test="tabFlag == 'ysh' ">
                    and vve.id is not null
                </if>
            </if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        union all
        select distinct vv1.id,
        vv1.name,
        vv1.phone,
        vv1.sex,
        vv1.avatar,
        vv1.idcard,
        vv1.car_no,
        vv1.user_id,
        vv1.dept_id,
        vv1.flag,
        vv1.start_time,
        vv1.end_time,
        vv1.matter,
        vv1.res,
        vv1.guid,
        vv1.face_guid,
        vv1.remark,
        vv1.create_by,
        vv1.create_time,
        vv1.update_by,
        vv1.parent_id,
        vv1.update_time,
        vve.admittance_start,
        vve.admittance_end,
        vve.point_id,
        sb.name                                        branch_name,
        if(sp.name is not null, sp.name, vv1.user_name) people_name,
        vve.examine,
        vv1.in_time,
        vv1.out_time,
        vve.create_by                                  examine_name,
        sp.phone                                       people_phone,
        vv1.source,
        sp.position,
        vv1.position                             visitorPosition,
        vv1.visiting_unit                        visitingUnit,
        vv1.reviewer2,
        vv1.reviewer3,
        vv1.examine_state,
        vv1.visitor_type,
        vv1.current_node,
        vv1.park
        from vis_visitor_review_process vvrp
        left join vis_visitor vv1 on vv1.id = vvrp.visitor_id
        left join sys_people s on vvrp.reviewer = s.id
        left join vis_visitor_examine vve on vv1.id = vve.visitor_id
        left join sys_branch sb on vv1.dept_id = sb.id
        left join sys_people sp on vv1.user_id = sp.id
        left join vis_visitor_audit_records vvar on vvar.next_step_reviewer = vvrp.reviewer and vvar.visitor_id = vvrp.visitor_id
        where 1=1
          and vv1.id is not null
        <if test="nextStepReviewer != null and nextStepReviewer != ''">
            and vvar.next_step_reviewer in
            <foreach collection="nextStepReviewer.split(',')" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach>
        </if>
        <choose>
            <when test="peoplePhone != null and peoplePhone != '' and tabFlag != null">
                and s.phone in
                <foreach collection="peoplePhone.split(',')" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach>
            </when>
            <otherwise>
                and sp.phone = #{peoplePhone}
            </otherwise>
        </choose>
        <if test="peoplePhone != null  and peoplePhone != '' and position == null">
            <if test="tabFlag != null  and tabFlag != ''">
                <if test="tabFlag == 'wsh' ">
                    and vvrp.state is null
                </if>
                <if test="tabFlag == 'ysh' ">
                    and vvrp.state is not null
                </if>
            </if>
        </if>
        <if test="peoplePhone == null or peoplePhone == '' or position != null">
            <if test="tabFlag == 'wsh' ">
                and vve.id is null
            </if>
            <if test="tabFlag == 'ysh' ">
                and vve.id is not null
            </if>
        </if>
        order by create_time desc
    </select>

    <select id="selectVisitorItemList" parameterType="Visitor" resultMap="VisitorResult">
        <include refid="selectVisitorVo"/>
        <where>
            <if test="parentId != null ">and vv.parent_id = #{parentId}</if>
        </where>
    </select>


    <select id="selectVisitorById" parameterType="Long" resultMap="VisitorResult">
        <include refid="selectVisitorVo"/>
        where vv.id = #{id}
    </select>

    <insert id="insertVisitor" parameterType="Visitor" useGeneratedKeys="true" keyProperty="id">
        insert into vis_visitor
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="phone != null">phone,</if>
            <if test="sex != null">sex,</if>
            <if test="avatar != null">avatar,</if>
            <if test="idcard != null">idcard,</if>
            <if test="carNo != null">car_no,</if>
            <if test="userId != null">user_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="flag != null">flag,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="matter != null">matter,</if>
            <if test="res != null">res,</if>
            <if test="guid != null">guid,</if>
            <if test="faceGuid != null">face_guid,</if>
            <if test="remark != null">remark,</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="parentId != null">parent_id,</if>
            <if test="inTime != null">in_time,</if>
            <if test="outTime != null">out_time,</if>
            <if test="source != null">source,</if>
            <if test="idCardStartTime != null">id_card_start_time,</if>
            <if test="idCardEndTime != null">id_card_end_time,</if>
            <if test="idCardAddress != null">id_card_address,</if>
            <if test="visitingUnit != null">visiting_unit,</if>
            <if test="examineState != null">examine_state,</if>
            <if test="visitorType != null">visitor_type,</if>
            <if test="park != null">park,</if>
            <if test="userName != null">user_name,</if>
            <if test="currentNode != null">current_node,</if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">#{id},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="phone != null">#{phone},</if>
            <if test="sex != null">#{sex},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="idcard != null">#{idcard},</if>
            <if test="carNo != null">#{carNo},</if>
            <if test="userId != null">#{userId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="flag != null">#{flag},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="matter != null">#{matter},</if>
            <if test="res != null">#{res},</if>
            <if test="guid != null">#{guid},</if>
            <if test="faceGuid != null">#{faceGuid},</if>
            <if test="remark != null">#{remark},</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="parentId != null">#{parentId},</if>
            <if test="inTime != null">#{inTime},</if>
            <if test="outTime != null">#{outTime},</if>
            <if test="source != null">#{source},</if>
            <if test="idCardStartTime != null">#{idCardStartTime},</if>
            <if test="idCardEndTime != null">#{idCardEndTime},</if>
            <if test="idCardAddress != null">#{idCardAddress},</if>
            <if test="visitingUnit != null">#{visitingUnit},</if>
            <if test="examineState != null">#{examineState},</if>
            <if test="visitorType != null">#{visitorType},</if>
            <if test="park != null">#{park},</if>
            <if test="userName != null">#{userName},</if>
            <if test="currentNode != null">#{currentNode},</if>

        </trim>
    </insert>
    <insert id="saveVisCheckCode">
        insert into vis_check_code (id, code, start_time, end_time, visitor_id, exit_id)
        VALUES (#{id}, #{code}, #{startTime}, #{endTime}, #{visitorId}, #{exitId})
    </insert>
    <insert id="saveVisitorRecord">
        insert into vis_visitor_record (id, visitor_id, sequence, start_time)
        VALUES (REPLACE(uuid(), '-', ''), #{visitorId}, #{sequence}, #{startTime})
    </insert>
    <insert id="saveVisitorAuditRecords">
        insert into vis_visitor_audit_records (id, visitor_id, reviewer, state, next_step_reviewer)
        VALUES (REPLACE(uuid(), '-', ''), #{id}, #{reviewer}, #{state}, #{nextStepReviewer})
    </insert>
    <insert id="saveFile">
        insert into sys_file (id, business_id, business_type, url, file_name) VALUES
        <foreach collection="list" item="i" index="index" separator=",">
            (
            #{i.id},
            #{i.businessId},
            #{i.businessType},
            #{i.url},
            #{i.fileName}
            )
        </foreach>
    </insert>
    <insert id="saveVisitorReviewProcess">
        insert into vis_visitor_review_process (id, visitor_id, reviewer, type
        <if test="state != null and state != ''">
            ,state
        </if>
        )
        VALUES (REPLACE(uuid(), '-', ''), #{id}, #{reviewer}, #{type}
        <if test="state != null and state != ''">
            ,#{state}
        </if>)
    </insert>
    <insert id="saveVisitorReviewProcessList">
        insert into vis_visitor_review_process (id, visitor_id, reviewer, state, reject_reason, approve_node)
        VALUES
        <foreach collection="list" item="i" index="index" separator=",">
            (REPLACE(uuid(), '-', ''), #{i.id}, #{i.reviewer}, #{i.state}, #{i.rejectReason}, #{i.approveNode})
        </foreach>
    </insert>

    <select id="addVisitorCheck" resultType="java.lang.Integer">
        select
            count(*)
        from vis_visitor
        where
            end_time <![CDATA[ >= ]]> #{startTime}
        and phone in
        <foreach item="phone" collection="phones" open="(" separator="," close=")">
            #{phone}
        </foreach>
    </select>

    <update id="updateVisitor" parameterType="Visitor">
        update vis_visitor
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="idcard != null">idcard = #{idcard},</if>
            <if test="carNo != null">car_no = #{carNo},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="flag != null">flag = #{flag},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="matter != null">matter = #{matter},</if>
            <if test="res != null">res = #{res},</if>
            <if test="guid != null">guid = #{guid},</if>
            <if test="faceGuid != null">face_guid = #{faceGuid},</if>
            <if test="remark != null">remark = #{remark},</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="parentId != null">parent_id = #{parentId},</if>
            <if test="inTime != null">in_time = #{inTime},</if>
            <if test="outTime != null">out_time = #{outTime},</if>
            <if test="type != null">type = #{type},</if>
            <if test="idCardStartTime != null">id_card_start_time = #{idCardStartTime},</if>
            <if test="idCardEndTime != null">id_card_end_time = #{idCardEndTime},</if>
            <if test="idCardAddress != null">id_card_address = #{idCardAddress},</if>
            <if test="visitingUnit != null">visiting_unit = #{visitingUnit},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="visitorPosition != null">position = #{visitorPosition},</if>
            <if test="reviewer2 != null and reviewer2 != ''">reviewer2 = #{reviewer2},</if>
            <if test="reviewer3 != null and reviewer3 != ''">reviewer3 = #{reviewer3},</if>
            <if test="examineState != null">examine_state = #{examineState},</if>
            <if test="currentNode != null">current_node = #{currentNode},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateVisitorPushType">
        update vis_visitor
        set push_type = '1'
        where 1=1
        and id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>
    <update id="updateVisitorInfo">
        update vis_visitor
        <trim prefix="SET" suffixOverrides=",">
            <if test="inTime != null">in_time = #{inTime},</if>
            <if test="outTime != null">out_time = #{outTime},</if>
            <if test="type != null">type = #{type},</if>
        </trim>
        where idcard = #{idcard}
        and date_format(create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
    </update>
    <update id="updateVisitorInfoGuid">
        UPDATE vis_visitor
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="guid =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="null != item.guid">
                        WHEN ID=#{item.id} THEN #{item.guid}
                    </if>
                </foreach>
            </trim>
            <trim prefix="face_guid =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="null != item.faceGuid">
                        WHEN ID=#{item.id} THEN #{item.faceGuid}
                    </if>
                </foreach>
            </trim>
        </trim>
        WHERE
        ID IN
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>
    <update id="updateVisitorRecord">
        update vis_visitor_record
        set end_time = #{endTime}
        where visitor_id = #{visitorId}
    </update>
    <update id="updateDisconnect">
        update vis_visitor
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="idcard != null">idcard = #{idcard},</if>
            <if test="carNo != null">car_no = #{carNo},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="flag != null">flag = #{flag},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="matter != null">matter = #{matter},</if>
            <if test="res != null">res = #{res},</if>
            <if test="guid != null">guid = #{guid},</if>
            <if test="faceGuid != null">face_guid = #{faceGuid},</if>
            <if test="remark != null">remark = #{remark},</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="parentId != null">parent_id = #{parentId},</if>
            <if test="inTime != null">in_time = #{inTime},</if>
            <if test="outTime != null">out_time = #{outTime},</if>
            <if test="type != null">type = #{type},</if>
            <if test="idCardStartTime != null">id_card_start_time = #{idCardStartTime},</if>
            <if test="idCardEndTime != null">id_card_end_time = #{idCardEndTime},</if>
            <if test="idCardAddress != null">id_card_address = #{idCardAddress},</if>
            <if test="visitingUnit != null">visiting_unit = #{visitingUnit},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="currentNode != null">current_node = #{currentNode},</if>
        </trim>
        where idcard = #{idcard} and out_time is null
    </update>
    <update id="updateVisitorReviewProcess">
        update vis_visitor_review_process
        set state = #{state}
            <if test="rejectReason != null and rejectReason != ''">
                ,reject_reason = #{rejectReason}
            </if>
        where visitor_id = #{id}
        <if test="reviewer != null and reviewer != ''">
            and reviewer = #{reviewer}
        </if>
        <if test="approveNode != null">
            and approve_node = #{approveNode}
        </if>
    </update>
    <update id="updateVisitorByParentId">
        update vis_visitor
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="idcard != null">idcard = #{idcard},</if>
            <if test="carNo != null">car_no = #{carNo},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="flag != null">flag = #{flag},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="matter != null">matter = #{matter},</if>
            <if test="res != null">res = #{res},</if>
            <if test="guid != null">guid = #{guid},</if>
            <if test="faceGuid != null">face_guid = #{faceGuid},</if>
            <if test="remark != null">remark = #{remark},</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="parentId != null">parent_id = #{parentId},</if>
            <if test="inTime != null">in_time = #{inTime},</if>
            <if test="outTime != null">out_time = #{outTime},</if>
            <if test="type != null">type = #{type},</if>
            <if test="idCardStartTime != null">id_card_start_time = #{idCardStartTime},</if>
            <if test="idCardEndTime != null">id_card_end_time = #{idCardEndTime},</if>
            <if test="idCardAddress != null">id_card_address = #{idCardAddress},</if>
            <if test="visitingUnit != null">visiting_unit = #{visitingUnit},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="visitorPosition != null">position = #{visitorPosition},</if>
            <if test="reviewer2 != null">reviewer2 = #{reviewer2},</if>
            <if test="reviewer3 != null">reviewer3 = #{reviewer3},</if>
            <if test="examineState != null">examine_state = #{examineState},</if>
            <if test="currentNode != null">current_node = #{currentNode},</if>
        </trim>
        where parent_id = #{parentId}
    </update>

    <delete id="deleteVisitorById" parameterType="Long">
        delete
        from vis_visitor
        where id = #{id}
    </delete>

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

    <delete id="deleteVisitorItemByParentId" parameterType="Long">
        delete
        from vis_visitor
        where parent_id = #{parentId}
    </delete>
    <delete id="deleteVisitorRecordList">
        update vis_visitor_record set delete_state = '1' where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <delete id="deleteFile">
        delete from sys_file where business_id = #{businessId} and business_type = #{businessType}
    </delete>

    <delete id="deleteVisitorReviewProcess">
    delete from vis_visitor_review_process where visitor_id = #{id}
    </delete>

    <select id="selectByGuid" parameterType="String" resultMap="VisitorResult">
        <include refid="selectVisitorVo"/>
        where vv.guid = #{guid}
    </select>

    <select id="selectVisitorByPhone" parameterType="String" resultMap="VisitorResult">
        <include refid="selectVisitorVo"/>
        where vv.phone = #{phone} LIMIT 1
    </select>

    <select id="queryVisitorInfo" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select tler.rule_id ladderRuleId, start_time startTime, end_time endTime
        from vis_visitor vv
                 left join sys_people sp on vv.user_id = sp.id
                 left join t_ladder_empower_record tler on tler.people_id = sp.id
        where tler.delete_state = '0'
          and sp.del_flag = '0'
          and tler.status = '0'
          and tler.info_down = '0'
          and tler.serial_number = #{sequence}
          and tler.people_id = #{id}
    </select>
    <select id="selectVisitorInfo" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select tler.rule_id ladderRuleId, start_time startTime, end_time endTime
        from vis_visitor vv
                 left join sys_people sp on vv.user_id = sp.id
                 left join t_ladder_empower_record tler on tler.people_id = sp.id
        where tler.delete_state = '0'
          and sp.del_flag = '0'
          and tler.status = '0'
          and tler.info_down = '0'
          and tler.serial_number = #{sequence}
          and vv.id = #{id}
    </select>
    <select id="queryVisitorAppointment" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select
        date_format(vv.in_time, '%Y-%m-%d %H:%i:%s') inTime,
        date_format(vv.out_time, '%Y-%m-%d %H:%i:%s') outTime,
        case
        when vv.in_time is not null THEN '2'
        when vve.id is null THEN '0'
        when vve.id is not null THEN '1'
        END state,
        vv.id,
        vv.avatar,
        vv.name,
        vv.phone,
        vv.sex,
        vv.idcard,
        vv.car_no carNo,
        vv.flag,
        vv.dept_id deptId,
        vv.user_id userId,
        date_format(vv.start_time, '%Y-%m-%d %H:%i:%s') startTime,
        date_format(vv.end_time, '%Y-%m-%d %H:%i:%s') endTime,
        vv.matter,
        vv.res,
        vv.remark,
        ifnull(vv.type, 0) type,
        id_card_start_time idCardStartTime,
        id_card_end_time id_cardEndTime,
        id_card_address idCardAddress
        from vis_visitor vv
        left join vis_visitor_examine vve on vv.id = vve.visitor_id
        where date_format(vv.create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
        and vv.out_time is null
        and vv.parent_id is null
        <if test="idcard != null and idcard != ''">
            and vv.idcard like concat('%', #{idcard}, '%')
        </if>
        <if test="name != null and name != ''">
            and (vv.name like concat('%', #{name}, '%') or vv.phone like concat('%', #{name}, '%'))
        </if>
        order by vv.create_time desc, id desc
    </select>
    <select id="queryVisitorByCardCount" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where out_time is null
        and examine_state in ('2','0')
        and idcard in
        <foreach item="idcard" collection="idcards" open="(" separator="," close=")">
            #{idcard}
        </foreach>
    </select>
    <select id="queryVisitorInfoById" resultType="com.dcsoft.system.visitor.domain.PersonnelInfoVo">
        select idcard
        from vis_visitor
        where id = #{id}
    </select>
    <select id="queryVisitorUnsigned" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select id, name, phone, idcard
        from vis_visitor
        where 1 = 1
          and out_time is null
          and in_time is not null
        order by create_time desc
    </select>
    <select id="queryVisCheckCodeById" resultType="com.dcsoft.system.visitor.domain.CheckCodeVo">
        select id, code, start_time startTime, end_time endTime
        from vis_check_code
        where id = #{id}
        limit 1
    </select>
    <select id="queryVisCheckCode" resultType="java.lang.String">
        select id
        from vis_check_code
        where visitor_id = #{visitorId}
        limit 1
    </select>
    <select id="querySysManageRecord" resultType="com.dcsoft.system.domain.SysManageRecord">
        select any_value(id)        id,
               any_value(people_id) peopleId,
               rule_id              ruleId
        from sys_empower_record
        where people_id = #{userId}
        group by rule_id

    </select>
    <select id="queryVisitorGuid" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select guid, id
        from vis_visitor
        where guid = #{guid}
        limit 1
    </select>
    <select id="queryVisitorRecord" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor_record
        where visitor_id = #{id}
          and start_time is not null
    </select>
    <select id="queryVisitorRecordList" resultType="com.dcsoft.system.visitor.domain.VisitorRecordVo">
        select vv.name,
        vvr.sequence,
        sp.name peopleName,
        vvr.start_time startTime,
        vvr.end_time endTime,
        vvr.id
        from vis_visitor_record vvr
        left join vis_visitor vv on vvr.visitor_id = vv.id
        left join sys_people sp on sp.id = vv.user_id and sp.del_flag = '0'
        where vvr.delete_state = '0'
        <if test="name != null and name != ''">
            and vv.name like concat('%', #{name}, '%')
        </if>
        <if test="startTimeTxt != null and startTimeTxt != ''">
            and date_format(vv.start_time, '%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> date_format(#{startTimeTxt}, '%Y-%m-%d
            %H:%i:%s')
        </if>
        <if test="endTimeTxt != null and endTimeTxt != ''">
            and date_format(vv.end_time, '%Y-%m-%d %H:%i:%s') >= date_format(#{endTimeTxt}, '%Y-%m-%d %H:%i:%s')
        </if>
        order by vvr.created_time desc
    </select>
    <select id="querySysBranch" resultType="com.dcsoft.system.domain.SysBranch">
        SELECT
            ID.LEVEL,
            DATA.id,
            DATA.name
        FROM
            (
                SELECT
                    @ids AS _ids,
                    ( SELECT @ids := GROUP_CONCAT( id ) FROM sys_branch WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
                    @l := @l + 1 AS LEVEL
                FROM
                    sys_branch,
                    ( SELECT @ids := #{deptType}, @l := 0 ) b
                WHERE
                    @ids IS NOT NULL
            ) ID,
            sys_branch DATA
        WHERE
            FIND_IN_SET( DATA.id, ID._ids )
        ORDER BY
            LEVEL,
            id
    </select>

    <select id="queryVisitorDayCount" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where dept_id = #{type}
          and type = '2'
          and date_format(now(), '%Y-%m-%d') = date_format(update_time, '%Y-%m-%d')
    </select>

    <select id="queryVisitorWeekCount" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where dept_id = #{type}
          and type = '2'
          and date_format(now(), '%Y-%v') = date_format(update_time, '%Y-%v')
    </select>
    <select id="queryVisitorMonthCount" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where dept_id = #{type}
          and type = '2'
          and date_format(now(), '%Y-%m') = date_format(update_time, '%Y-%m')
    </select>
    <select id="queryVisitorAvatar" resultType="com.dcsoft.system.visitor.domain.VisitorRecordVo">
        select avatar visitorAvatar
        from vis_visitor
        where type = '2'
        order by update_time desc
        limit 10
    </select>
    <select id="queryVisitorById" resultType="com.dcsoft.system.visitor.domain.VisitorRecordVo">
        select vv.id, vv.start_time startTime, vv.end_time endTime
        from vis_visitor vv
                 left join vis_visitor_examine vve on vv.id = vve.visitor_id
                 left join vis_check_code vcc on vv.id = vcc.visitor_id
        where vve.id is not null
          and vcc.id is not null
          and vv.out_time is null
          and vv.phone = #{phone}
        limit 1
    </select>
    <select id="queryVisitorByIdCount" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where out_time is null
        and examine_state in ('2','0')
        and phone in
        <foreach item="phone" collection="phones" open="(" separator="," close=")">
            #{phone}
        </foreach>
    </select>
    <select id="queryExportVisitorInfo" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select vv.name, vv.phone, vv.start_time startTime, vv.end_time endTime, sp.name userName, vv.matter
        from vis_visitor vv
                 left join sys_people sp on vv.user_id = sp.id and sp.del_flag = '0'
        where 1 = 1
          and date_format(vv.create_time, '%Y-%m-%d') >= date_format(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '%Y-%m-%d')
          and date_format(vv.create_time, '%Y-%m-%d') <![CDATA[ <= ]]> date_format(now(), '%Y-%m-%d')
    </select>
    <select id="queryBranch" resultType="java.lang.String">
        select b.id
        from sys_branch b
                 left join sys_dept d on b.dept_id = d.dept_id
        where b.rule_id is not null
          and b.rule_id != '10'
    </select>
    <select id="queryBranch1" resultType="java.lang.String">
        select GROUP_CONCAT(sb.name)
        from (WITH RECURSIVE parents_cte (id, parent_id) AS (SELECT id, parent_id
                                                             FROM sys_branch
                                                             WHERE id in (#{s})-- 子机id替换问号
                                                             UNION ALL
                                                             SELECT p.id, p.parent_id
                                                             FROM sys_branch p
                                                                      INNER JOIN parents_cte ON parents_cte.parent_id = p.id)
              SELECT id y, #{s} j
              FROM parents_cte) t
                 left join sys_branch sb on t.y = sb.id
        group by t.j
    </select>
    <select id="queryPropertyExaminePeople" resultType="com.dcsoft.system.domain.SysPeople">
        select sp.name, sp.id, sp.phone
        from sys_people sp
                 left join sys_branch sb on sp.branch_id = sb.id
        where sp.del_flag = '0'
          and sb.parent_id = #{deptId}
    </select>
    <select id="querySysFile" resultType="com.dcsoft.system.domain.vo.SysFileVo">
        select url, file_name fileName from sys_file where business_id = #{businessId} and business_type = #{businessType}
    </select>
    <select id="queryVisitorReviewProcess" resultType="com.dcsoft.system.domain.SysPeople">
        select sp.phone, sp.id
        from vis_visitor_audit_records vvar
                 left join sys_people sp on vvar.next_step_reviewer = sp.id
        where sp.del_flag = '0'
          and vvar.visitor_id = #{id}
        order by vvar.created_time desc
        limit 1
    </select>
    <select id="queryReviewProcess" resultType="com.dcsoft.system.visitor.domain.Visitor">
        select
            reviewer,
            state,
            reject_reason
        from vis_visitor_review_process
        where visitor_id = #{id}
          <if test="position != null and position != ''">
              and type = #{position}
          </if>
        <if test="reviewer != null and reviewer != ''">
            and reviewer = #{reviewer}
        </if>
        order by approve_node desc
        LIMIT 1
    </select>
    <select id="queryVisitorReviewProcessCount" resultType="java.lang.Integer">
        select sum(if(state is null, 1, 0)) state from vis_visitor_review_process where visitor_id = #{id} group by visitor_id
    </select>
    <select id="queryVisitorReviewProcessList"
            resultType="com.dcsoft.system.visitor.domain.VisitorReviewProcessVo">
        select reviewer,
               state,
               sp.position type,
               date_format(created_time, '%Y-%m-%d %H:%i:%s') createdTime,
               sp.name                                        reviewerName
        from vis_visitor_review_process vvrp
                 left join sys_people sp on vvrp.reviewer = sp.id and sp.del_flag = '0'
        where visitor_id = #{id}
        order by ifnull(createdTime, 'zzz')
    </select>

    <select id="selectAppVisitorFollow" resultType="java.lang.Long">
        select distinct
        vv.parent_id
        from vis_visitor vv left join vis_visitor vv1 on vv.parent_id = vv1.id
        where vv.parent_id is not null
        and vv.phone = #{createBy}
        <if test="examineState != null and examineState != 3">
            and vv1.examine_state = #{examineState}
        </if>
        <if test="park != null and park != ''">
            and vv1.park = #{park}
        </if>
    </select>


    <select id="selectAppVisitorList" parameterType="Visitor" resultMap="VisitorResult">
        select distinct
        vv.id,
        vv.name,
        vv.phone,
        vv.sex,
        vv.avatar,
        vv.idcard,
        vv.car_no,
        vv.user_id,
        vv.dept_id,
        vv.flag,
        vv.start_time,
        vv.end_time,
        vv.matter,
        vv.res,
        vv.guid,
        vv.face_guid,
        vv.remark,
        vv.create_by,
        vv.create_time,
        vv.update_by,
        vv.parent_id,
        vv.update_time,
        vve.admittance_start,
        vve.admittance_end,
        vve.point_id,
        sb.name                                        branch_name,
        if(sp.name is not null, sp.name, vv.user_name) people_name,
        vve.examine,
        vv.in_time,
        vv.out_time,
        vve.create_by                                  examine_name,
        sp.phone                                       people_phone,
        vv.source,
        sp.position,
        vv.position                                    visitorPosition,
        vv.visiting_unit,
        vv.reviewer2,
        vv.reviewer3,
        vv.examine_state,
        vv.visitor_type,
        vv.park
        from vis_visitor vv
        left join vis_visitor_examine vve on vv.id = vve.visitor_id
        left join sys_branch sb on vv.dept_id = sb.id
        left join sys_people sp on vv.user_id = sp.id
        <where>
            <if test="name != null  and name != ''">and vv.name like concat('%', #{name}, '%')</if>
            <if test="createBy != null ">and vv.create_by = #{createBy}</if>
            <if test="examineState != null and examineState != 3">and vv.examine_state = #{examineState}</if>
            <if test="park != null and park != ''">and vv.park = #{park}</if>
            <if test="visitorType != null">and vv.visitor_type = #{visitorType}</if>
            and vv.parent_id is null
            <if test="ids != null and ids.size() > 0">
                or vv.id in
                <foreach collection="ids" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="queryCode" resultType="com.dcsoft.system.visitor.domain.CheckCodeVo">
        SELECT
        vcc.visitor_id as visitorId,
        vcc.`code`
        FROM
        vis_check_code vcc
        JOIN (SELECT max(created_time) as created_time, visitor_id FROM vis_check_code GROUP BY visitor_id) t ON vcc.created_time = t.created_time
        left join vis_visitor vv on vv.examine_state = 0 and vcc.visitor_id = vv.id
        WHERE t.visitor_id IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        and vv.examine_state = 0
    </select>
    <select id="selectRecordsByPhone" resultType="java.lang.String">
        SELECT
            r.state
        FROM
            `vis_visitor_audit_records` r
                LEFT JOIN sys_people p ON r.reviewer = p.id
        WHERE
            r.visitor_id = #{id}
          AND p.phone = #{telephone}
        ORDER BY r.created_time desc LIMIT 1
    </select>
    <select id="queryVisitor" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where idcard = #{idcard}
          and out_time is null
    </select>
    <select id="queryVisitorByIdCountOld" resultType="java.lang.Integer">
        select count(1)
        from vis_visitor
        where phone = #{phone}
          and out_time is null
    </select>

    <select id="queryReviewProcessOld" resultType="java.lang.String">
        select reviewer from vis_visitor_review_process where visitor_id = #{id} and type = #{position}
    </select>
</mapper>
