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

    <resultMap type="SysBlackList" id="SysBlackListResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="phone"    column="phone"    />
        <result property="sex"    column="sex"    />
        <result property="idcard"    column="idcard"    />
        <result property="carNo"    column="car_no"    />
        <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"    />
    </resultMap>

    <sql id="selectSysBlackListVo">
        select id, name, phone, sex, idcard, car_no, remark, create_by, create_time, update_by, update_time from sys_black_list
    </sql>

    <select id="selectSysBlackListList" parameterType="SysBlackList" resultMap="SysBlackListResult">
        <include refid="selectSysBlackListVo"/>
        <where>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>
            <if test="sex != null  and sex != ''"> and sex = #{sex}</if>
            <if test="idcard != null  and idcard != ''"> and idcard = #{idcard}</if>
            <if test="carNo != null  and carNo != ''"> and car_no = #{carNo}</if>
        </where>
    </select>

    <select id="selectSysBlackListById" parameterType="Long" resultMap="SysBlackListResult">
        <include refid="selectSysBlackListVo"/>
        where id = #{id}
    </select>
    <select id="queryPersonnelInfo" resultType="com.dcsoft.system.visitor.domain.PersonnelInfoVo">
        select id, name, idcard, car_no carNo, phone, avatar, date_format(in_time, '%Y-%m-%d %H:%i:%s') inTime,
        date_format(out_time, '%Y-%m-%d %H:%i:%s') outTime, type
        from vis_visitor
        where 1=1
        and user_id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        order by create_time desc
        limit 1
    </select>
    <select id="queryGeneralInfo" resultType="com.dcsoft.system.visitor.domain.PersonnelInfoVo">
        select id, name, idcard, car_no carNo, phone, avatar, type from vis_visitor where parent_id = #{id}
    </select>
    <select id="queryPeople" resultType="com.dcsoft.system.visitor.domain.PersonnelInfoVo">
        select distinct sp.id, sp.name, sp.branch_id branchId, sb.name branchName, sp.phone
        from sys_people sp left join sys_branch sb on sp.branch_id = sb.id
        where sp.del_flag = '0'
        <if test="name != null and name != ''">
            and sp.name like concat('%', #{name}, '%')
        </if>
    </select>
    <select id="queryAuditedVisitor" resultType="com.dcsoft.system.visitor.domain.VisitorParamVo">
        select case
        when vve.examine = '0' then '1'
        when vve.examine = '1' then '2'
        when vve.examine is null then '0'
        end reviewStatus,
        date_format(vv.in_time, '%Y-%m-%d %H:%i:%s') inTime,
        vv.end_time endTime
        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')
        <if test="idcard != null and idcard != ''">
            and vv.idcard = #{idcard}
        </if>
        and vv.parent_id is null
        and vv.in_time is null
        order by vv.create_time desc
        limit 1
    </select>
    <select id="queryVisitorInfo" resultType="com.dcsoft.system.visitor.domain.VisitorInfoVo">
        select vv.id,
        vv.avatar,
        vv.name,
        vv.phone,
        vv.sex,
        vv.idcard,
        vv.car_no                                        carNo,
        vv.flag,
        vv.dept_id                                       deptId,
        sb.name                                          deptName,
        vv.user_id                                       userId,
        sp.name                                          userName,
        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,
        vv.source,
        case
        when vv.in_time is null and vv.out_time is null then '1'  # 未签到
        when vv.in_time is not null and vv.out_time is null then '2'  # 已签到未签离
        when vv.in_time is not null and vv.out_time is not null then '3' # 已签离
        end state,
        ifnull(type, 0) type,
        id_card_start_time idCardStartTime,
        id_card_end_time idCardEndTime,
        id_card_address idCardAddress,
        visiting_unit visitingUnit
        from vis_visitor vv
        left join sys_people sp on vv.user_id = sp.id and sp.del_flag = '0'
        left join sys_branch sb on vv.dept_id = sb.id
        where 1=1
          and vv.parent_id is null
        <if test="idcard != null and idcard != ''">
            and vv.idcard = #{idcard}
        </if>
        <if test="id != null and id != ''">
            and vv.id = #{id}
        </if>
        <if test="type != null and type == '1'.toString()">
            and date_format(vv.create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
            and vv.in_time is not null
        </if>
        <if test="type != null and type == '2'.toString()">
            and date_format(vv.create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
            and vv.out_time is null
        </if>
        order by vv.create_time desc, id desc
        limit 1
    </select>
    <select id="queryFollowUp" resultType="com.dcsoft.system.visitor.domain.VisitorInfoVo">
        select id,
        avatar,
        name,
        phone,
        sex,
        idcard,
        car_no carNo,
        flag,
        dept_id deptId,
        user_id userId,
        date_format(start_time, '%Y-%m-%d %H:%i:%s') startTime,
        date_format(end_time, '%Y-%m-%d %H:%i:%s') endTime,
        matter,
        res,
        remark,
        case
        when in_time is null and out_time is null then '1'  # 未签到
        when in_time is not null and out_time is null then '2'  # 已签到未签离
        when in_time is not null and out_time is not null then '3' # 已签离
        end state
        from vis_visitor
        where 1=1
        <if test="idcard != null and idcard != ''">
            and parent_id = (select id from vis_visitor where idcard = #{idcard} order by create_time desc limit 1)
        </if>
        <if test="id != null and id != ''">
            and parent_id = #{id}
        </if>

    </select>
    <select id="queryBlackById" resultType="com.dcsoft.system.domain.SysBlackList">
        select id,
               name,
               phone,
               sex,
               idcard,
               car_no,
               remark,
               create_by,
               create_time,
               update_by,
               update_time
        from sys_black_list
        where idcard = #{idcard}
    </select>
    <select id="queryVisitorType" resultType="com.dcsoft.system.visitor.domain.VisitorInfoVo">
        select type, date_format(in_time, '%Y-%m-%d %H:%i:%s') inTime
        from vis_visitor vv
        WHERE date_format(vv.create_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')
            and vv.idcard = #{idcard}
        order by vv.create_time desc, id desc
        limit 1
    </select>

    <insert id="insertSysBlackList" parameterType="SysBlackList" useGeneratedKeys="true" keyProperty="id">
        insert into sys_black_list
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">name,</if>
            <if test="phone != null">phone,</if>
            <if test="sex != null">sex,</if>
            <if test="idcard != null">idcard,</if>
            <if test="carNo != null">car_no,</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>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">#{name},</if>
            <if test="phone != null">#{phone},</if>
            <if test="sex != null">#{sex},</if>
            <if test="idcard != null">#{idcard},</if>
            <if test="carNo != null">#{carNo},</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>
         </trim>
    </insert>

    <update id="updateSysBlackList" parameterType="SysBlackList">
        update sys_black_list
        <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="idcard != null">idcard = #{idcard},</if>
            <if test="carNo != null">car_no = #{carNo},</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>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSysBlackListById" parameterType="Long">
        delete from sys_black_list where id = #{id}
    </delete>

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