<?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.career.dao.CareerDao">
    <insert id="insertDate">
        insert into pm_occupation_phy_result(physical_id,phy_name,age,sex,depart,if_job,special_job,hazards,phy_status,hazards_num,check_exception_result,conclusion,suggest)
        values (#{physicalId},#{personName},#{age},#{sex},#{unit},#{personType},#{postName},#{hazards},#{phyStatus},#{hazardsNum},#{checkExceptionResult},#{conclusion},#{suggest})
    </insert>
    <update id="updateInfo">
        update pm_occupation_phy_result
        <set>
            <if test="status != null and status != ''">
                phy_status = #{status},
            </if>
            <if test="remark != null and remark != ''">
                remark = #{remark},
            </if>
        </set>

        where id = #{id}
    </update>
    <update id="updatePDFAddress">
        UPDATE pm_occupation_phy_result AS r
            JOIN (
                SELECT phy_name, MAX(update_time) AS max_update_time
                FROM pm_occupation_phy_result
                WHERE phy_name IN (
                    SELECT phy_name
                    FROM pm_base_physical
                    WHERE idcard = #{idNumber} AND is_active = '1'
                )
                GROUP BY phy_name
            ) AS sub
            ON r.phy_name = sub.phy_name AND r.update_time = sub.max_update_time
        set phy_report_address = #{filePath}
    </update>
    <update id="updateAppointStatus">
        update  pm_base_phy_appont set
            appoint_status = #{status}
        where user_id = (select id from pm_base_physical where idcard = #{idNumber} and is_active = '1') and if_cancel = '2' and if_career_appoint = '3'
    </update>
    <update id="updateAppointCancel">
        update pm_base_phy_appont set
            if_cancel = #{status}
        where user_id = (select id from pm_base_physical where idcard = #{idNumber} and is_active = '1') and if_cancel = '2' and if_career_appoint = '3'
    </update>
    <update id="updateExcelData">
    UPDATE pm_occupation_phy_result AS r
        JOIN (
            SELECT phy_name, MAX(update_time) AS max_update_time
            FROM pm_occupation_phy_result
            WHERE phy_name IN (
                SELECT phy_name
                FROM pm_base_physical
                WHERE idcard = #{idNumber} AND is_active = '1'
            )
            GROUP BY phy_name
        ) AS sub
        ON r.phy_name = sub.phy_name AND r.update_time = sub.max_update_time
    SET r.phy_time = #{physicalTime}, r.physical_status = #{physicalStatus};
    </update>


    <select id="getList" resultType="com.bonus.boot.manager.career.entity.CareerBean">
        SELECT
        pbp.phy_name AS personName,
        pbp.idcard AS idNumber,
        pbp.age AS age,
        ppo.`NAME` AS unit,
        IF(pbp.sex = 1, '女', '男') AS sex,
        pbp.telep_number AS phone,
        m.id,
        m.postName,
        p.hazards,
        m.checkExceptionResult,
        m.conclusion,
        p.phy_report_address AS filePath,
        m.phyCount AS counts,
        p.phy_status AS status,
        m.phyTime,
        m.suggest,
        m.physicalStatus
        FROM
        pm_base_physical pbp
        LEFT JOIN pm_phy_organization ppo ON ppo.id = pbp.depart_id
        LEFT JOIN (
        SELECT * FROM(
        SELECT
        DISTINCT
        popr.id AS id,
        popr.phy_name,
        popr.hazards_num AS hazardsNum,
        popr.check_exception_result AS checkExceptionResult,
        popr.conclusion AS conclusion,
        popr.suggest AS suggest,
        popr.phy_time AS phyTime,
        popr.create_time AS create_time,
        popr2.phyCount AS phyCount,
        popr.special_job AS postName,
        popr.physical_status AS physicalStatus
        FROM
        pm_occupation_phy_result popr
        LEFT JOIN (
        SELECT phy_name AS phyName, COUNT(phy_name) AS phyCount
        FROM pm_occupation_phy_result
        WHERE is_active = '1'
        GROUP BY phy_name
        ) popr2 ON popr2.phyName = popr.phy_name
        WHERE
        1 = 1
        <if test="physicalTime != null and physicalTime != ''">
            AND popr.create_time LIKE CONCAT('%', #{physicalTime}, '%')
        </if>
        ORDER BY popr.create_time DESC
        ) popr
        GROUP BY popr.phy_name
        ) m ON m.phy_name = pbp.phy_name
        LEFT JOIN pm_occupation_phy_result p ON p.id = m.id
        WHERE
        1 = 1
        AND pbp.is_active = '1'
        AND ppo.is_active = '1'
        AND pbp.special_job != '0'
        <if test="hazards != null and hazards != ''">
            AND p.hazards = #{hazards}
        </if>
        <if test="personType != null and personType != ''">
            AND pbp.if_job = #{personType}
        </if>
        <if test="status != null and status != ''">
            AND p.phy_status = #{status}
        </if>
        <if test="keyWord != null and keyWord != ''">
            AND (
            pbp.phy_name LIKE CONCAT('%', #{keyWord}, '%')
            OR ppo.`NAME` LIKE CONCAT('%', #{keyWord}, '%')
            OR p.hazards LIKE CONCAT('%', #{keyWord}, '%')
            OR pbp.idcard LIKE CONCAT('%', #{keyWord}, '%')
            OR pbp.telep_number LIKE CONCAT('%', #{keyWord}, '%')
            OR m.postName LIKE CONCAT('%', #{keyWord}, '%')
            )
        </if>


    </select>
    <select id="getPhysicalInfoForOne" resultType="com.bonus.boot.manager.career.entity.CareerBean">
        SELECT
        p.id,
        p.phy_name as personName,
        p.depart as unit,
        p.sex ,
        p.age,
        p.hazards,
        pbp.idcard as idNumber,
        pbp.telep_number as phone,
        p.special_job as postName,
        p.phy_status as status,
        pbpa.phy_appont_time as bookingTime,
        p.phy_time as physicalTime,
        p.phy_free as physicalFree,
        p.next_phy_time as nextPhysicalTime,
        p.phy_report_address as filePath,
        r.counts as counts,
        p.check_exception_result as checkExceptionResult,
        p.conclusion,
        p.suggest,
        p.if_job as personType
        FROM pm_occupation_phy_result p
        left join pm_base_physical pbp on pbp.phy_name = p.phy_name and pbp.is_active = '1'
        left join pm_base_phy_appont pbpa on pbpa.user_id = pbpa.id and pbpa.if_active = '1'
        left join (
        select phy_name,count(phy_name) as counts
        from pm_occupation_phy_result
        where phy_name = #{personName} and is_active = '1'
        <if test="physicalTime != null and physicalTime != ''">
            AND create_time LIKE concat ('%',#{physicalTime},'%')
        </if>
        ) r on r.phy_name = p.phy_name
        where p.is_active = '1' and p.phy_name = #{personName}
        <if test="physicalTime != null and physicalTime != ''">
            AND p.create_time LIKE concat ('%',#{physicalTime},'%')
        </if>
    </select>
    <select id="getIsExistAppoint" resultType="java.lang.Integer">
        select count(1)
        from pm_base_phy_appont
        where user_id = (select id from pm_base_physical where idcard = #{idNumber} and is_active = '1') and if_cancel = '2' and if_career_appoint = '3'
    </select>
    <select id="getAllPhysicalPerson" resultType="com.bonus.boot.manager.career.entity.CareerBean">
        select DISTINCT phy_name as personName,idcard as idNumber,id
        from pm_base_physical
        where is_active = '1'
    </select>
    <select id="getAllIdNumber" resultType="com.bonus.boot.manager.career.entity.CareerBean">
        select DISTINCT idcard as idNumber
        from pm_base_physical
        where is_active = '1'
    </select>
</mapper>

