<?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.bonus.bracelet.mapper.PersonMgeMapper">
    <!--新增人员-->
    <insert id="addPerson" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO tb_people
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">name,</if>
            <if test="sex != null">sex,</if>
            <if test="idCard != null and idCard != ''">id_card,</if>
            <if test="phone != null and phone!=''">phone,</if>
            <if test="aqmCode != null and aqmCode!=''">aqm_code,</if>
            <if test="mjCode != null and mjCode!=''">mj_code,</if>
            create_time,
            create_user,
            update_time,
            update_user,
            del_flag,
            id,
            post,
            is_push
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">#{name},</if>
            <if test="sex != null">#{sex},</if>
            <if test="idCard != null and idCard != ''">#{idCard},</if>
            <if test="phone != null and phone!=''">#{phone},</if>
            <if test="aqmCode != null and aqmCode!=''">#{aqmCode},</if>
            <if test="mjCode != null and mjCode!=''">#{mjCode},</if>
            #{createTime},
            #{createUser},
            #{updateTime},
            #{updateUser},
            0,
            null,
            #{post},
            #{isPush}
        </trim>
    </insert>
    <!--修改人员-->
    <update id="editPerson">
        UPDATE tb_people SET name = #{name},sex = #{sex},id_card = #{idCard},phone = #{phone},aqm_code = #{aqmCode},mj_code = #{mjCode},update_time = #{updateTime},update_user = #{updateUser},post = #{post},is_push = #{isPush} WHERE id = #{id}
    </update>
    <!--删除人员-->
    <update id="delPerson">
        UPDATE tb_people SET del_flag = 1 WHERE id = #{id}
    </update>

    <!--人员身份证号是否存在-->
    <select id="personIsExist" resultType="java.util.Map">
        <if test="id == null">
            SELECT id,id_card AS value,phone FROM tb_people WHERE del_flag = 0
        </if>
        <if test="id != null">
            SELECT id,id_card AS value,phone FROM tb_people WHERE id != #{id} AND del_flag = 0
        </if>
    </select>
    <!--人员详情-->
    <select id="getPersonInfo" resultType="com.bonus.common.entity.bracelet.vo.PersonVo">
        SELECT tp.id,
               tp.name,
               tp.sex,
               tp.id_card AS idCard,
               tp.phone,
               tp.aqm_code AS aqmCode,
               tp.mj_code AS mjCode,
               sfs.file_path AS filePath,
               sfs.id AS fileId,
               tp.post,
               tp.team_id AS teamId
        FROM tb_people tp
        LEFT JOIN sys_file_source sfs ON tp.id = sfs.source_id AND sfs.source_type = #{sourceType} AND sfs.del_flag = 0
        WHERE tp.id = #{id} AND tp.del_flag = 0
    </select>
    <!--查询人员列表-->
    <select id="getPersonLists" resultType="com.bonus.common.entity.bracelet.vo.PersonVo">
        SELECT tp.id,
               tp.name,
               tp.sex,
               tp.id_card AS idCard,
               tp.phone,
               tp.aqm_code AS aqmCode,
               tp.mj_code AS mjCode,
               sfs.file_path AS filePath,
               sfs.id AS fileId,
               sdd.dict_label AS post,
               IFNULL(tp.is_push,0) AS isPush
        FROM tb_people tp
        LEFT JOIN sys_dict_data sdd ON tp.post = sdd.dict_value AND sdd.dict_type = 'post_type' AND sdd.status = '0'
        LEFT JOIN sys_file_source sfs ON tp.id = sfs.source_id AND sfs.source_type = #{sourceType} AND sfs.del_flag = 0
        LEFT JOIN tb_people_certificate tpc on tp.id = tpc.people_id
        WHERE tp.del_flag = 0
        <if test="name != null and name!=''">
            AND INSTR(tp.name,#{name}) > 0
        </if>
        <if test="sex != null">
            AND tp.sex = #{sex}
        </if>
        Group by tp.id,sfs.file_path,sfs.id,sdd.dict_label
        ORDER BY tp.create_time DESC
    </select>

    <!--获取人员证书数量-->
    <select id="getCertificateNum" resultType="java.lang.Integer">
        SELECT
        count(tpc.id) as certificateNum
        FROM tb_people_certificate tpc
        WHERE tpc.people_id = #{id}
    </select>

    <!--验证安全帽编号是否重复、马甲编号是否重复-->
    <select id="codeIsExist" resultType="java.lang.Integer">
        <if test="type == 1">
            <if test="params.id == null">
                SELECT COUNT(*) FROM tb_people WHERE aqm_code = #{params.aqmCode} AND del_flag = 0
            </if>
            <if test="params.id != null">
                SELECT COUNT(*) FROM tb_people WHERE id != #{params.id} AND aqm_code = #{params.aqmCode} AND del_flag = 0
            </if>
        </if>
        <if test="type == 2">
            <if test="params.id == null">
                SELECT COUNT(*) FROM tb_people WHERE mj_code = #{params.mjCode} AND del_flag = 0
            </if>
            <if test="params.id != null">
                SELECT COUNT(*) FROM tb_people WHERE mj_code = #{params.mjCode} AND id != #{params.id} AND del_flag = 0
            </if>
        </if>
    </select>
    <!--    根据人员id获取证书图片信息-->
    <select id="selectCertificateFile" parameterType="int" resultType="com.bonus.common.entity.file.ResourceFileVo">
        SELECT sfs.file_path AS filePath,
               sfs.id AS fileId,
               sfs.source_type as sourceImagType,
               sfs.file_name as fileName,
               sfs.file_type as fileType
        FROM tb_people_certificate tpc
                 LEFT JOIN sys_file_source sfs ON tpc.id = sfs.source_id AND sfs.source_type in(2,3,4) AND sfs.del_flag = 0
        WHERE   tpc.people_id = #{memberId}
    </select>
    <!--    根据人员id获取信息-->
    <select id="selectCertificateById" parameterType="int" resultType="com.bonus.common.entity.bracelet.vo.PersonVo">
        SELECT tp.id,
               tp.name,
               tp.sex,
               tp.id_card AS idCard,
               tp.phone,
               tp.aqm_code AS aqmCode,
               tp.mj_code AS mjCode
        FROM tb_people tp
        WHERE tp.id = #{memberId} AND tp.del_flag = 0
    </select>

    <!--获取高工证书id-->
    <select id="getHighId" parameterType="String" resultType="java.lang.Integer">
        select source_id as id
        from sys_file_source
        where file_path = #{filepath} and source_type = 2 and del_flag = 0
    </select>

    <!--删除高工信息-->
    <delete id="delHigh">
        delete from tb_people_certificate where
        id = #{id}
    </delete>

    <!--获取电工证书id-->
    <select id="getElectId" parameterType="String" resultType="java.lang.Integer">
        select source_id as id
        from sys_file_source
        where file_path = #{filepath} and source_type = 3 and del_flag = 0
    </select>

    <!--删除电工信息-->
    <delete id="delElect">
        delete from tb_people_certificate where
            id = #{id}
    </delete>

    <!--获取其他证书id-->
    <select id="getElseId" parameterType="String" resultType="java.lang.Integer">
        select source_id as id
        from sys_file_source
        where file_path = #{filepath} and source_type = 4 and del_flag = 0
    </select>

    <!--删除其他证书信息-->
    <delete id="delElse">
        delete from tb_people_certificate where
            id = #{id}
    </delete>

    <update id="delHighFile" >
        update sys_file_source
        set	del_flag = 1
        where file_path = #{filePath} and source_id = #{id} and source_type = 2
    </update>

    <update id="delElectFile" >
        update sys_file_source
        set	del_flag = 1
        where file_path = #{filePath} and source_id = #{id} and source_type = 3
    </update>

    <update id="delElseFile" >
        update sys_file_source
        set	del_flag = 1
        where file_path = #{filePath} and source_id = #{id} and source_type = 4
    </update>

    <insert id="insertCertificate" parameterType="com.bonus.common.entity.bracelet.vo.CertificateVo" useGeneratedKeys="true" keyProperty="id">
        insert into tb_people_certificate(
        <if test="peopleId != null">people_id,</if>
        <if test="certificateType != null">certificate_type</if>
        )values(
        <if test="peopleId != null">#{peopleId},</if>
        <if test="certificateType != null">#{certificateType}</if>
        )
    </insert>

    <!--删除该人员对应的证书-->
    <delete id="delCertificate">
        delete from tb_people_certificate where
            people_id = #{id}
    </delete>

    <select id="getCertificate" resultType="com.bonus.common.entity.bracelet.vo.CertificateVo">
        select id,certificate_type as certificateType
        from tb_people_certificate
        where people_id = #{id}
    </select>
    <!--查询导出人员数据-->
    <select id="getExportPersonLists" resultType="com.bonus.common.entity.bracelet.vo.PersonVo">
        SELECT tp.id,
        tp.name,
        tp.sex,
        tp.id_card AS idCard,
        tp.phone,
        tp.aqm_code AS aqmCode,
        tp.mj_code AS mjCode,
        sdd.dict_label AS post,
        tp.aqm_code AS aqmCode,
        tp.mj_code AS mjCode
        FROM tb_people tp
        LEFT JOIN sys_dict_data sdd ON tp.post = sdd.dict_value AND sdd.dict_type = 'post_type' AND sdd.status = '0'
        WHERE tp.del_flag = 0
        <if test="name != null and name!=''">
            AND INSTR(tp.name,#{name}) > 0
        </if>
        <if test="sex != null">
            AND tp.sex = #{sex}
        </if>
        ORDER BY tp.update_time DESC
    </select>
    <!--获取人员岗位-->
    <select id="getPostType" resultType="java.util.Map">
        SELECT dict_label AS name, dict_value AS value FROM sys_dict_data sdd
        WHERE sdd.dict_type = 'post_type' AND sdd.status = '0'
    </select>
    <!--判断人员是否绑定了手环/其他设备/领用手环箱/领用设备-->
    <select id="isLyDevices" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM tb_bracelet WHERE bid_id = #{id} AND peopel_type = 0 AND del_flag = 0
    UNION ALL
        SELECT COUNT(*) FROM tb_dev_ly WHERE ly_user = #{id}
    UNION ALL
        SELECT COUNT(*)
        FROM tb_sh_box tsb
        LEFT JOIN t_work_team twt ON tsb.team_id = twt.team_id AND twt.del_flag = 0 AND twt.team_status = 0
        WHERE twt.team_leader_id = #{id} AND tsb.del_flag = 0
    UNION ALL
        SELECT COUNT(*)
        FROM tb_dev_ly tdl
        LEFT JOIN t_work_team twt ON tdl.team_id = twt.team_id AND twt.del_flag = 0 AND twt.team_status = 0
        WHERE twt.team_leader_id = #{id}
    </select>
    <!--人员已分配班组，岗位限制修改 -->
    <select id="canItBeModified" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM tb_people tp WHERE id = #{id} AND post != #{post} AND team_id IS NOT NULL
    </select>
    <!--获取所有已入库的安全帽编号-->
    <select id="aqmCodeIsExist" resultType="java.util.Map">
        SELECT aqm_code AS code FROM tb_people WHERE aqm_code IS NOT NULL AND del_flag = 0
    </select>
    <!--获取所有已入库的马甲编号-->
    <select id="mjCodeIsExist" resultType="java.util.Map">
        SELECT mj_code AS code FROM tb_people WHERE mj_code IS NOT NULL AND del_flag = 0
    </select>

    <!--删除资源文件-->
    <update id="delCertificateResourceFile">
        UPDATE sys_file_source SET del_flag = 1 WHERE source_id = #{id} and source_type = (#{certificateType})
    </update>
    <!--更新人脸库推送状态-->
    <update id="updatePeoplePushStatus">
        UPDATE tb_people SET is_push = #{isPush} WHERE id = #{id}
    </update>
</mapper>