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

    <resultMap type="VisExitOutVo" id="VisitorResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="reason" column="reason"/>
        <result property="name" column="name"/>
        <result property="phone" column="phone"/>
        <result property="exitPermitNo" column="exit_permit_no"/>
        <result property="peopleId" column="people_id"/>
        <result property="visitingUnit" column="visiting_unit"/>
        <result property="visTime" column="vis_time"/>
        <result property="carNo" column="car_no"/>
        <result property="reviewer1" column="reviewer1"/>
        <result property="reviewer2" column="reviewer2"/>
        <result property="reviewer3" column="reviewer3"/>
        <result property="examineState" column="examine_state"/>
        <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="vehicle" column="vehicle"/>
        <result property="currentNode" column="current_node"/>
        <result property="isExistBack" column="is_exist_back"/>
<!--        <association property="examine" column="id" javaType="VisStuffInfoVo" resultMap="StuffResult"/>-->
    </resultMap>

    <resultMap type="VisStuffInfoVo" id="StuffResult">
        <result property="id" column="id"/>
        <result property="exitId" column="exit_id"/>
        <result property="name" column="name"/>
        <result property="purpose" column="purpose"/>
        <result property="type" column="type"/>
        <result property="model" column="model"/>
        <result property="unit" column="unit"/>
        <result property="number" column="number"/>
        <result property="imgUrls" column="img_urls"/>
        <result property="remark" column="remark"/>
        <result property="isBack" column="is_back"/>
        <result property="backTime" column="back_time"/>
        <result property="remark" column="remark"/>
        <result property="backName" column="back_name"/>
        <result property="backPurpose" column="back_purpose"/>
        <result property="backType" column="back_type"/>
        <result property="backModel" column="back_model"/>
        <result property="backUnit" column="back_unit"/>
        <result property="backNumber" column="back_number"/>
        <result property="backImgUrls" column="back_img_urls"/>
        <result property="backRemark" column="back_remark"/>
        <result property="sort" column="sort"/>
    </resultMap>

    <resultMap type="VisStuffInfoExportVo" id="StuffInfoExportResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="userName" column="userName"/>
        <result property="reason" column="reason"/>
        <result property="phone" column="phone"/>
        <result property="exitPermitNo" column="exit_permit_no"/>
        <result property="visitingUnit" column="visiting_unit"/>
        <result property="visTime" column="vis_time"/>
        <result property="carNo" column="car_no"/>
        <result property="exitId" column="exit_id"/>
        <result property="name" column="name"/>
        <result property="purpose" column="purpose"/>
        <result property="type" column="type"/>
        <result property="model" column="model"/>
        <result property="unit" column="unit"/>
        <result property="number" column="number"/>
        <result property="imgUrls" column="img_urls"/>
        <result property="remark" column="remark"/>
        <result property="isBack" column="is_back"/>
        <result property="backTime" column="back_time"/>
        <result property="remark" column="remark"/>
        <result property="backName" column="back_name"/>
        <result property="backPurpose" column="back_purpose"/>
        <result property="backType" column="back_type"/>
        <result property="backModel" column="back_model"/>
        <result property="backUnit" column="back_unit"/>
        <result property="backNumber" column="back_number"/>
        <result property="backImgUrls" column="back_img_urls"/>
        <result property="backRemark" column="back_remark"/>
        <result property="examineState" column="examine_state"/>
    </resultMap>

    <sql id="selectVisExitOutVo">
        select distinct
            e.id,
            e.user_id,
            e.reason,
            e.name,
            e.phone,
            e.`exit_permit_no`,
            e.`people_id`,
            e.visiting_unit,
            e.`car_no`,
            e.vis_time,
            e.reviewer1,
            e.reviewer2,
            e.reviewer3,
            e.examine_state,
            e.create_by,
            e.create_time,
            e.update_by,
            e.update_time,
            e.vehicle,
            e.current_node,
            e.is_exist_back,
            (select p.name from sys_people p where p.id = e.reviewer1) as reviewerName1,
            (select p.name from sys_people p where p.id = e.reviewer2) as reviewerName2,
            (select p.name from sys_people p where p.id = e.reviewer3) as reviewerName3
        from vis_exit_out e
    </sql>

    <update id="updateStaff">
        update vis_exit_out
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="name != null and name != ''"> `name` = #{name},</if>
            <if test="reason != null and reason != ''"> `reason` = #{reason},</if>
            <if test="phone != null and phone != ''"> `phone` = #{phone},</if>
            <if test="exitPermitNo != null and exitPermitNo != ''"> `exit_permit_no` = #{exitPermitNo},</if>
            <if test="peopleId != null and peopleId != ''"> `people_id` = #{peopleId},</if>
            <if test="visitingUnit != null and visitingUnit != ''">visiting_unit = #{visitingUnit},</if>
            <if test="visTime != null">vis_time = #{visTime},</if>
            <if test="vehicle != null and vehicle != ''">vehicle = #{vehicle},</if>
            <if test="carNo != null and carNo != ''">car_no = #{carNo},</if>
            <if test="reviewer1 != null">reviewer1 = #{reviewer1},</if>
            <if test="reviewer2 != null">reviewer2 = #{reviewer2},</if>
            <if test="reviewer3 != null">reviewer3 = #{reviewer3},</if>
            <if test="examineState != null and examineState != ''">examine_state = #{examineState},</if>
            <if test="currentNode != null">current_node = #{currentNode},</if>
            <if test="isExistBack != null">is_exist_back = #{isExistBack},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateVisitorReviewProcessById">
        update vis_visitor_review_process
        set state = #{state}
        <if test="rejectReason != null and rejectReason != ''">
            ,reject_reason = #{rejectReason}
        </if>
        where id = #{id}
    </update>

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

    <select id="selectList" resultType="com.dcsoft.system.visitor.domain.VisExitOutVo">
        <include refid="selectVisExitOutVo"/>
        <where>
            <if test="id != null">and e.id = #{id}</if>
            <if test="name != null  and name != ''">and e.`name` like concat('%', #{name}, '%')</if>
            <if test="userId != null ">and e.user_id = #{userId}</if>
            <if test="visTimeStart != null">and e.vis_time <![CDATA[ >= ]]> #{visTimeStart}</if>
            <if test="visTimeEnd != null">and e.vis_time <![CDATA[ <= ]]> #{visTimeEnd}</if>
            <if test="exitPermitNo != null and exitPermitNo != ''">and e.exit_permit_no = #{exitPermitNo}</if>
            <if test="examineState != null and examineState != '' and examineState != 3">and e.examine_state = #{examineState}</if>
            <if test="isExistBack != null">and e.is_exist_back = #{isExistBack}</if>
            <if test="createBy != null">and e.create_by = #{createBy}</if>
        </where>
        order by create_time desc
    </select>

    <select id="selectVisExitOutById" resultType="com.dcsoft.system.visitor.domain.VisExitOutVo">
        <include refid="selectVisExitOutVo"/>
        where e.id = #{id}
    </select>

    <select id="selectStuffCheckCode" resultType="com.dcsoft.system.visitor.domain.CheckCodeVo">
        select
            code
        from vis_check_code
        where exit_id = #{exitId}
        order by created_time desc
        limit 1
    </select>

    <select id="queryVisitorReviewProcessList" resultType="com.dcsoft.system.visitor.domain.VisitorReviewProcessVo">
        select reviewer,
               state,
               sp.position type,
               date_format(ifnull(updated_time,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 exit_id = #{id}
        order by ifnull(createdTime, 'zzz')
    </select>

    <select id="queryReviewProcessNew" resultType="com.dcsoft.system.visitor.domain.VisitorReviewProcessVo">
        select
        reviewer,
        state,
        reject_reason,
        updated_time updatedTime
        from vis_visitor_review_process
        where exit_id = #{id}
        and reviewer = #{reviewer}
        order by updated_time desc
    </select>

    <select id="selectStuffList" resultMap="StuffResult">
        select
            `id`,
            `exit_id`,
            `name`,
            `purpose`,
            `type`,
            `model`,
            `unit`,
            `number`,
            `img_urls`,
            `remark`,
            `is_back`,
            `back_time`,
            `back_name`,
            `back_purpose`,
            `back_type`,
            `back_model`,
            `back_unit`,
            `back_number`,
            `back_img_urls`,
            `back_remark`,
            `sort`
        from vis_stuff_info
        <where>
            <if test="exitId != null">and exit_id = #{exitId}</if>
            <if test="name != null  and name != ''">and `name` like concat('%', #{name}, '%')</if>
            <if test="createBy != null ">and create_by = #{createBy}</if>
            <if test="exitIds != null and exitIds.size() > 0">
                exit_id in
                <foreach item="id" collection="exitIds" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectVisitorList" resultMap="StuffInfoExportResult">
        SELECT
            s.`exit_id`,
            s.`name`,
            s.`purpose`,
            s.`type`,
            s.`model`,
            s.`unit`,
            s.`number`,
            s.`img_urls`,
            s.`remark`,
            s.`is_back`,
            s.`back_time`,
            s.`back_name`,
            s.`back_purpose`,
            s.`back_type`,
            s.`back_model`,
            s.`back_unit`,
            s.`back_number`,
            s.`back_img_urls`,
            s.`back_remark`,
            e.name username,
            e.phone,
            e.visiting_unit,
            e.vis_time,
            e.exit_permit_no,
            e.examine_state
        FROM
            `vis_exit_out` e
            LEFT JOIN vis_stuff_info s ON e.id = s.exit_id
        <where>
            <if test="id != null">and e.id = #{id}</if>
            <if test="name != null  and name != ''">and e.`name` like concat('%', #{name}, '%')</if>
            <if test="visTimeStart != null">and e.vis_time <![CDATA[ >= ]]> #{visTimeStart}</if>
            <if test="visTimeEnd != null">and e.vis_time <![CDATA[ <= ]]> #{visTimeEnd}</if>
            <if test="exitPermitNo != null and exitPermitNo != ''">and e.exit_permit_no = #{exitPermitNo}</if>
            <if test="examineState != null and examineState != '' and examineState != 3">and e.examine_state = #{examineState}</if>
            <if test="isExistBack != null">and e.is_exist_back = #{isExistBack}</if>
            <if test="ids != null and ids.size() > 0">
                or e.id in
                <foreach item="id" collection="ids" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
    <select id="selectVisitorReviewProcessOne"
            resultType="com.dcsoft.system.visitor.domain.VisitorReviewProcessVo">
        select
            id,
            visitor_id visitorId,
            reviewer,
            state,
            type,
            created_time createdTime,
            exit_id exitId
        from vis_visitor_review_process
        where exit_id = #{exitId}
        and reviewer = #{reviewer}
        and state = '2'
        order by created_time asc
        limit 1
    </select>

    <insert id="insertVisExitOut" useGeneratedKeys="true" keyProperty="id">
        insert into vis_exit_out
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">id,</if>
            <if test="userId != null">user_id,</if>
            <if test="reason != null and reason != ''">reason,</if>
            <if test="name != null and name != ''">`name`,</if>
            <if test="phone != null and phone != ''">`phone`,</if>
            <if test="exitPermitNo != null and exitPermitNo != ''">`exit_permit_no`,</if>
            <if test="peopleId != null and peopleId != ''">`people_id`,</if>
            <if test="visitingUnit != null and visitingUnit != ''">visiting_unit,</if>
            <if test="visTime != null">vis_time,</if>
            <if test="carNo != null and carNo != ''">car_no,</if>
            <if test="reviewer1 != null">reviewer1,</if>
            <if test="reviewer2 != null">reviewer2,</if>
            <if test="reviewer3 != null">reviewer3,</if>
            <if test="examineState != null and examineState != ''">examine_state,</if>
            <if test="createBy != null">create_by,</if>
            <if test="vehicle != null">vehicle,</if>
            <if test="currentNode != null">current_node,</if>
            <if test="isExistBack != null">is_exist_back,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">#{id},</if>
            <if test="userId != null">#{userId},</if>
            <if test="reason != null and reason != ''">#{reason},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="phone != null and phone != ''">#{phone},</if>
            <if test="exitPermitNo != null and exitPermitNo != ''">#{exitPermitNo},</if>
            <if test="peopleId != null and peopleId != ''">#{peopleId},</if>
            <if test="visitingUnit != null and visitingUnit != ''">#{visitingUnit},</if>
            <if test="visTime != null">#{visTime},</if>
            <if test="carNo != null and carNo != ''">#{carNo},</if>
            <if test="reviewer1 != null">#{reviewer1},</if>
            <if test="reviewer2 != null">#{reviewer2},</if>
            <if test="reviewer3 != null">#{reviewer3},</if>
            <if test="examineState != null and examineState != ''">#{examineState},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="vehicle != null">#{vehicle},</if>
            <if test="currentNode != null">#{currentNode},</if>
            <if test="isExistBack != null">#{isExistBack},</if>
        </trim>
    </insert>
    <insert id="saveVisitorReviewProcess">
        insert into vis_visitor_review_process (id, exit_id, reviewer
        <if test="state != null and state != ''">
            ,state
        </if>
        )
        VALUES (REPLACE(uuid(), '-', ''), #{id}, #{reviewer}
        <if test="state != null and state != ''">
            ,#{state}
        </if>)
    </insert>

    <insert id="insertVisStuffInfo">
        INSERT INTO vis_stuff_info (
            exit_id,
            `name`,
            purpose,
            type,
            model,
            unit,
            number,
            img_urls,
            remark,
            is_back,
            back_time,
            back_name,
            back_purpose,
            back_type,
            back_model,
            back_unit,
            back_number,
            back_img_urls,
            back_remark,
            sort
        ) VALUES
        <foreach collection="list" item="i" index="index" separator=",">
            (
            #{i.exitId},
            #{i.name},
            #{i.purpose},
            #{i.type},
            #{i.model},
            #{i.unit},
            #{i.number},
            #{i.imgUrls},
            #{i.remark},
            #{i.isBack},
            #{i.backTime},
            #{i.backName},
            #{i.backPurpose},
            #{i.backType},
            #{i.backModel},
            #{i.backUnit},
            #{i.backNumber},
            #{i.backImgUrls},
            #{i.backRemark,},
            #{i.sort}
            )
        </foreach>
    </insert>

    <insert id="saveVisitorReviewProcessList">
        insert into vis_visitor_review_process (id, exit_id, reviewer, state)
        VALUES
        <foreach collection="list" item="i" index="index" separator=",">
            (REPLACE(uuid(), '-', ''), #{i.id}, #{i.reviewer}, #{i.state})
        </foreach>
    </insert>

</mapper>
