<?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.physical.dao.RetirementDao">

    <select id="getList" resultType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean" parameterType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean">

        select a.id as personId,b.id as reservationId,a.phy_name as personName,a.idcard as idNumber,a.age,if(a.sex ='0','男','女') as sex,a.telep_number as phone,
        pmo.`NAME` as department,b.phy_appont_time as reservationTime,pbh.hospital as name,b.update_time as operateTime,sysu.username  as operatePerson
        ,pbh.id as id,a.if_higher as higherJob,if_cancel as  ifCancel
        from (
        select * from pm_base_physical  where if_job ='2' and is_active = '1'
        ) a
        LEFT JOIN (
        select * from pm_base_phy_appont where if_cancel ='2' and if_active = '1'
        )b on a.id = b.user_id
        LEFT JOIN pm_phy_organization pmo on pmo.ID = a.depart_id
        LEFT JOIN pm_base_hospital pbh on pbh.id = b.hospital_id
        LEFT JOIN sys_user sysu on sysu.id = b.appoint_name_id
        where 1= 1
        <if test="personName !=null and personName !='null'">
            and a.phy_name like concat ('%',#{personName},'%')
        </if>

        <if test="higherJob !=null and higherJob !='null'">
            and a.if_higher = #{higherJob}
        </if>

    </select>


  <select id="getPersonData" parameterType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean" resultType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean">
    select id as personId,phy_name as personName,idcard as idNumber,age,depart_id as department,
    sex,if_higher as higherJob,telep_number as phone,if_job as jobType,special_job as specialPost,
    is_appoint,create_time,update_time
     from pm_base_physical
     where  id in (${personStr})
  </select>

  <insert id="addReservationData" parameterType="com.bonus.boot.manager.basic.entity.PersonnelLibraryBean" keyProperty="tjId" useGeneratedKeys="true">
   insert into pm_base_phy_appont(
    hospital_id,
    user_id,
    phy_appont_time,
    appoint_name_id,
    if_cancel,
    sex,
    if_edu,
    person_type,
    if_active,
    check_content_id,
    if_career_appoint,
    comb_name
    )
    values (
        #{id},
        #{personId},
        #{reservationTime},
        #{operatePerson},
        #{ifCancel},
        #{sex},
        #{higherJob},
        #{jobType},
        '1',
        #{checkContentId},
        #{ifCareerAppoint},
        #{combName}
    )

  </insert>


  <insert id="addHistoryData" parameterType="string">
     insert into appont_phy_correlation(
     appont_id,meal_name,meal_content,meal_price
     )
     select #{tjId} as appont_id,combin_name as meal_name,combin_content as meal_content,combin_price as meal_price
     from pm_phy_combin where id  =#{tcId}
  </insert>

    <update id="cancelReservation" parameterType="string">
        update pm_base_phy_appont set if_cancel = #{ifCancel}
        where id = #{id}
    </update>


    <select id="getRetirementNum" parameterType="com.bonus.boot.manager.basic.entity.MapBean" resultType="com.bonus.boot.manager.basic.entity.MapBean">
        SELECT
        (select limit_person from pm_base_hosp_limit where start_date = #{keyWord} and if_active ='1' and hospital_id = #{hospitalId} limit 1
        ) as `key`,
        (
        select COUNT(id) from pm_base_phy_appont where if_active ='1' and if_cancel ='2' and hospital_id = #{hospitalId}
        and phy_appont_time = #{keyWord}
        ) as `value`
        from dual

    </select>

</mapper>

