<?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.securitycontrol.background.mapper.HumanManageMapper">
    <!--新增/修改人员-->
    <insert id="addOrUpdatePersonnel">
        <if test="type == 1">
            INSERT INTO t_team_people
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="userId != null and userId != ''">user_id,</if>
                <if test="teamId != null and teamId != ''">team_id,</if>
                <if test="userName != null and userName != ''">user_name,</if>
                <if test="idNumber != null and idNumber!=''">id_number,</if>
                <if test="phone != null and phone!=''">phone,</if>
                <if test="userType != null and userType!=''">user_type,</if>
                <if test="createTime != null and createTime!=''">cratet_time,</if>
                del_falge,
                <if test="status != null and status!=''">status,</if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="userId != null and userId != ''">#{userId},</if>
                <if test="teamId != null and teamId != ''">#{teamId},</if>
                <if test="userName != null and userName != ''">#{userName},</if>
                <if test="idNumber != null and idNumber!=''">#{idNumber},</if>
                <if test="phone != null and phone!=''">#{phone},</if>
                <if test="userType != null and userType!=''">#{userType},</if>
                <if test="createTime != null and createTime!=''">#{createTime},</if>
                '0',
                <if test="status != null and status!=''">#{status},</if>
            </trim>
        </if>
        <if test="type == 2">
            UPDATE t_team_people
            <set>
                <if test="teamId != null and teamId != ''">team_id = #{teamId},</if>
                <if test="userName != null and userName != ''">user_name = #{userName},</if>
                <if test="idNumber != null and idNumber != ''">id_number = #{idNumber},</if>
                <if test="phone != null and phone != ''">phone = #{phone},</if>
                <if test="userType != null and userType != ''">user_type = #{userType},</if>
                <if test="status != null and status!=''">#{status},</if>
            </set>
            WHERE user_id= #{userId}
        </if>
    </insert>
    <!--保存人员照片资源-->
    <insert id="addFiles">
        INSERT INTO tb_resource_file
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="resourceId != null and resourceId != ''">id,</if>
            <if test="fileType != null">file_type,</if>
            <if test="fileId != null and fileId!=''">file_id,</if>
            <if test="fileSuffix != null and fileSuffix!=''">file_suffix,</if>
            <if test="fileName != null and fileName!=''">file_name,</if>
            <if test="sourceId != null and sourceId!=''">source_id,</if>
            <if test="sourceType != null and sourceType!=''">source_type,</if>
            del_flag,
            <if test="updateTime != null and updateTime!=''">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="resourceId != null and resourceId != ''">#{resourceId},</if>
            <if test="fileType != null">#{fileType},</if>
            <if test="fileId != null and fileId!=''">#{fileId},</if>
            <if test="fileSuffix != null and fileSuffix!=''">#{fileSuffix},</if>
            <if test="fileName != null and fileName!=''">#{fileName},</if>
            <if test="sourceId != null and sourceId!=''">#{sourceId},</if>
            <if test="sourceType != null and sourceType!=''">#{sourceType},</if>
            1,
            <if test="updateTime != null and updateTime!=''">#{updateTime},</if>
        </trim>
    </insert>
    <insert id="bindDev">
        insert tb_user_bind_cap(id,people_id,device_id,bind_time,bind_status)
            value ( #{id},#{userId},#{devId},now(),1 )
    </insert>
    <!--删除人员照片资源文件-->
    <delete id="delFile">
        DELETE FROM tb_resource_file WHERE file_id = #{fileId}
    </delete>
    <!--删除人员-->
    <delete id="delPersonnelById">
        DELETE FROM t_team_people WHERE user_id = #{id}
    </delete>
    <update id="delBind">
            update tb_user_bind_cap set  bind_status=0 where  people_id=#{userId}
    </update>
    <update id="updateDev">
             update tb_user_bind_cap set  device_id=#{devId} where  id=#{id}
    </update>
    <!--获取人员列表-->
    <select id="getHumanLists" resultType="com.securitycontrol.entity.background.vo.HumanManageVo">
        SELECT
        ttp.user_id AS userId,
        ttp.user_name AS userName,
        sd.dict_name AS userType,
        twt.team_leader AS teamLeader,
        twt.team_name AS teamName,
        tp.pro_name AS proName
        FROM t_team_people ttp
        LEFT JOIN tb_work_team twt on ttp.team_id = twt.team_id
        LEFT JOIN sys_dict sd ON ttp.user_type = sd.dict_code
        LEFT JOIN tb_project tp ON twt.bid_code = tp.bid_code
        <where>
            <if test="bidCode!=null and bidCode !=''">
                AND twt.bid_code = #{bidCode}
            </if>
            <if test="userName!=null and userName !=''">
                AND INSTR(ttp.user_name,#{userName}) > 0
            </if>
        </where>
    </select>
    <!--人员是否存在-->
    <select id="userIsExist" resultType="java.util.Map">
        <if test="userId == null or userId == ''">
            SELECT user_id AS id,id_number AS value FROM t_team_people
        </if>
        <if test="userId != null and userId != ''">
            SELECT user_id AS id,id_number AS value FROM t_team_people WHERE user_id != #{userId}
        </if>
    </select>
    <!--人员详情-->
    <select id="getPersonnelById" resultType="com.securitycontrol.entity.background.vo.HumanManageVo">
        SELECT
            ttp.user_id AS userId,
            ttp.user_name AS userName,
            ttp.user_type AS userType,
            ttp.id_number AS idNumber,
            ttp.phone,
            ttp.team_id AS teamId
        FROM t_team_people ttp
        WHERE ttp.user_id = #{id}
    </select>
    <!--获取人员照片-->
    <select id="getFiles" resultType="com.securitycontrol.entity.system.vo.ResourceFileVo">
        SELECT id AS resourceId,
               file_id AS fileId
        FROM tb_resource_file WHERE source_id = #{userId}
    </select>
    <!--获取人员出入记录-->
    <select id="getPersonnelAccessLists" resultType="com.securitycontrol.entity.background.vo.UserAccessVo">
        SELECT tua.user_name AS userName,
               tua.access_time AS accessTime,
               IF(access_type = '1','入场','出场') AS accessType
        FROM tb_user_access tua
        WHERE user_id = #{id}
        <if test="startTime!=null and startTime!='' and endTime!=null and endTime!=''">
            AND tua.access_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
    </select>
    <select id="getBindHis" resultType="java.lang.Integer">
            select count(1)
                from tb_user_bind_cap
                where  people_id=#{userId} and bind_status=1
    </select>

    <select id="getBindDetail" resultType="com.securitycontrol.entity.background.dto.ParamDto">
            select device_id devId,id
                from tb_user_bind_cap
                where  people_id=#{userId} and bind_status=1
    </select>
</mapper>