<?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.boot.manager.basic.dao.PersonnelLibraryDao">

    <insert id="addInfo" keyProperty="hospId" useGeneratedKeys="true">
        <!-- 插入到pm_base_physical表 -->
        insert into
        pm_base_physical(phy_name,idcard,age,depart_id,sex,if_higher,telep_number,if_job,special_job,phy_password,is_active,status,nosocomium,set_meal,personnel_type,pushStatus)
        values
        (#{personName},#{idNumber},#{age},#{departmentId},#{sex},#{higherJob},#{phone},#{personType},#{specialPost},#{password},'1','1',#{hospital},#{setMeal},#{personnelType},'1')
    </insert>
    <insert id="addresult">
        insert into pm_physical_result(physical_id)
        values (#{id})
    </insert>
    <insert id="AddInf">
        <!-- 判断特定条件是否满足，然后再插入记录到pm_occupation_phy_result表 -->
        INSERT INTO pm_occupation_phy_result (phy_id,phy_name, depart, sex, idcard, telep_number, special_job)
        VALUES (#{hospId},#{personName}, #{departmentId}, #{sex}, #{idNumber}, #{phone}, #{specialPost})
    </insert>
    <update id="updateInfo">
        update pm_base_physical
        set depart_id      = #{departmentId},
            if_higher      =#{higherJob},
            if_job         = #{personType},
            special_job    = #{specialPost},
            nosocomium     = #{hospital},
            personnel_type = #{personnelType},
            set_meal       = #{setMeal}
        where id = #{id}
          and is_active = '1'
    </update>
    <update id="resetPassword">
        update pm_base_physical
        set phy_password = #{password}
        where id = #{id}
    </update>
    <update id="delById">
        update pm_base_physical
        set is_active = '0'
        where id = #{id}
    </update>

    <update id="updatePersonExport">
        insert into pm_base_physical(phy_name, idcard, depart_id, sex, telep_number, phy_password, if_job, age,
                                     special_job, is_active)
        values (#{personName}, #{idNumber}, #{department}, #{sex}, #{phone}, #{password}, #{personType}, #{age},
                #{specialPost}, '1')
    </update>
    <update id="updatephyInfo">
        update pm_base_physical
        set phy_name=#{personName},
            idcard=#{idNumber},
            depart_id=#{department},
            telep_number=#{phone},
            if_job=#{personType},
            age=#{age},
            special_job=#{specialPost}
        where telep_number = #{phone}
          and is_active = '1'
    </update>
    <update id="updatete">
        update pm_occupation_phy_result
        set depart      = #{departmentId},
            special_job = #{specialPost}
        where phy_id = #{id}
          and is_active = '1'
    </update>

    <select id="getList" resultType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean">
        select
        pbp.id,
        pbp.phy_name as personName,
        pbp.idcard as idNumber,
        pbp.age,
        if (pbp.sex = '0','男','女') as sex,
        pbp.telep_number as phone,
        pbp.depart_id as departmentId,
        po.NAME as department,
        if(if_higher = '0','非高职','高职') as higherJob,
        sdd.name as jobType,
        pbsj.job_name as specialPost,
        pbh.hospital as hospital,
        case pbp.personnel_type
        when '0' then '编外'
        when '1' then '编内'
        else ''
        end as personnelType,
        case pbp.set_meal
        when '1' then '心血管'
        when '2' then '肿瘤'
        when '3' then '综合'
        else ''
        end as setMeal
        from pm_base_physical pbp
        left join pm_base_hospital pbh on pbh.id = pbp.nosocomium
        left join sys_dic_detail sdd on sdd.id = pbp.if_job
        left join pm_base_special_job pbsj on pbsj.id = pbp.special_job and pbsj.is_active = '1'
        left join pm_phy_organization po on po.id = pbp.depart_id
        where pbp.is_active = '1'
        <if test="personName != null and personName != ''">
            AND pbp.phy_name LIKE concat ('%',#{personName},'%')
        </if>
        <if test="higherJob != null and higherJob != ''">
            AND pbp.if_higher= #{higherJob}
        </if>
        <if test="jobType != null and jobType != ''">
            AND pbp.if_job = #{jobType}
        </if>
    </select>
    <select id="getListById" resultType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean">
        select pbp.id,
               pbp.phy_name       as personName,
               pbp.idcard         as idNumber,
               pbp.age,
               pbp.sex,
               pbp.telep_number   as phone,
               pbp.depart_id      as departmentId,
               po.NAME            as department,
               pbp.if_higher      as higherJob,
               pbp.if_job         as jobType,
               pbp.special_job    as specialPost,
               pbsj.job_name      as specialPostName,
               pbp.nosocomium     as hospital,
               pbp.personnel_type as personnelType,
               pbp.set_meal       as setMeal
        from pm_base_physical pbp
                 left join pm_base_special_job pbsj on pbsj.id = pbp.special_job and pbsj.is_active = '1'
                 left join pm_phy_organization po on po.id = pbp.depart_id
        where pbp.is_active = '1'
          and pbp.id = #{id}
    </select>
    <select id="getDepartmentTree" resultType="com.bonus.boot.manager.basic.entity.ZNode">
        SELECT ID,
               `NAME`,
               PARENT_ID as pId,
               0         as type
        FROM pm_organization
        WHERE IS_ACTIVE = '1'
    </select>
    <select id="getinfoByidcard" resultType="java.lang.Integer">
        SELECT count(1)
        from pm_base_physical
        where is_active = '1'
          and telep_number = #{phone}
    </select>
    <select id="getdepartmentName" resultType="java.lang.String">
        select id
        from pm_phy_organization
        where `name` = #{department}
          and is_active = '1'
        limit 1
    </select>
    <select id="getspecJobName" resultType="java.lang.String">
        select id
        from pm_base_special_job
        where job_name = #{specJob}
          and is_active = '1'
        limit 1
    </select>
    <select id="getEyId" resultType="com.bonus.boot.manager.manager.model.SysUser">
        SELECT
            phy_name AS username,
            telep_number AS phone
        FROM pm_base_physical
        WHERE id =  #{userId};
    </select>
</mapper>

