<?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="org.sky.scientific.mapper.TechnicianMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="technicianResultMap" type="org.sky.scientific.pojo.entity.TechnicianEntity">
        <result column="id" property="id"/>
        <result column="tenant_id" property="tenantId"/>
        <result column="name" property="name"/>
        <result column="number" property="number"/>
        <result column="identity_card" property="identityCard"/>
        <result column="gender" property="gender"/>
        <result column="birthday" property="birthday"/>
        <result column="ethnic" property="ethnic"/>
        <result column="secondary_unit" property="secondaryUnit"/>
        <result column="department" property="department"/>
        <result column="post" property="post"/>
        <result column="post_location" property="postLocation"/>
        <result column="nature" property="nature"/>
        <result column="hire_date" property="hireDate"/>
        <result column="education" property="education"/>
        <result column="degree" property="degree"/>
        <result column="school_name" property="schoolName"/>
        <result column="major" property="major"/>
        <result column="rank" property="rank"/>
        <result column="practicing_requirement" property="practicingRequirement"/>
        <result column="situation" property="situation"/>
        <result column="personnel_type" property="personnelType"/>
        <result column="year_and_month" property="yearAndMonth"/>
        <result column="remark" property="remark"/>
        <result column="create_user" property="createUser"/>
        <result column="create_dept" property="createDept"/>
        <result column="create_time" property="createTime"/>
        <result column="update_user" property="updateUser"/>
        <result column="update_time" property="updateTime"/>
        <result column="status" property="status"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>


    <select id="selectTechnicianPage" resultMap="technicianResultMap" parameterType="org.sky.scientific.pojo.dto.TechnicianDTO">

        select *
        from kd_technician a
        <if test="technician.yearAndMonth.length()==4">
            INNER JOIN (
            SELECT MAX(id) AS max_id FROM kd_technician GROUP BY name,number,identity_card
            ) b ON a.id = b.max_id
        </if>
        where is_deleted = 0
          <if test="technician.name!=null and technician.name!=''">and name like concat(concat('%', #{technician.name}), '%')</if>
          <if test="technician.number!=null and technician.number!=''">and name like concat(concat('%', #{technician.number}), '%')</if>
          <if test="technician.identityCard!=null and technician.identityCard!=''">and identity_card like concat(concat('%', #{technician.identityCard}), '%')</if>
          <if test="technician.secondaryUnit!=null and technician.secondaryUnit!=''">and secondary_unit like concat(concat('%', #{technician.secondaryUnit}), '%')</if>
          <if test="technician.department!=null and technician.department!=''">and department like concat(concat('%', #{technician.department}), '%')</if>
          <if test="technician.post!=null and technician.post!=''">and post like concat(concat('%', #{technician.post}), '%')</if>
          <if test="technician.postLocation!=null and technician.postLocation!=''">and post_location like concat(concat('%', #{technician.postLocation}), '%')</if>
          <if test="technician.nature!=null and technician.nature!=''">and nature = #{technician.nature}</if>
          <if test="technician.education!=null and technician.education!=''">and education = #{technician.education}</if>
          <if test="technician.degree!=null and technician.degree!=''">and degree = #{technician.degree}</if>
          <if test="technician.rank!=null and technician.rank!=''">and rank = #{technician.rank}</if>
          <if test="technician.personnelType!=null and technician.personnelType!=''">and personnel_type = #{technician.personnelType}</if>
          <if test="technician.situation !=null and technician.situation!=''">and situation = #{technician.situation}</if>
          <if test="technician.practicingRequirement !=null and technician.practicingRequirement!=''">and practicing_requirement like concat(concat('%', #{technician.practicingRequirement}), '%')</if>
          <if test="technician.hireStartDate!=null and technician.hireEndDate!=''">and hire_Date between #{technician.hireStartDate} and #{technician.hireEndDate}</if>
          <choose>
              <when test="technician.yearAndMonth.length()==4">
                  and date_format(year_and_month,'%Y') = #{technician.yearAndMonth}
              </when>
              <otherwise>
                  and date_format(year_and_month,'%Y-%m') = #{technician.yearAndMonth}
              </otherwise>
          </choose>
        order by year_and_month
    </select>

    <select id="exportTechnicianList" resultType="org.sky.scientific.excel.TechnicianExcel" parameterMap="technicianResultMap">

        select *
        from kd_technician a
        <if test="technician.yearAndMonth.length()==4">
            INNER JOIN (
            SELECT MAX(id) AS max_id FROM kd_technician GROUP BY name,number,identity_card
            ) b ON a.id = b.max_id
        </if>
        where is_deleted = 0
        <if test="technician.name!=null and technician.name!=''">and name like concat(concat('%', #{technician.name}), '%')</if>
        <if test="technician.number!=null and technician.number!=''">and name like concat(concat('%', #{technician.number}), '%')</if>
        <if test="technician.identityCard!=null and technician.identityCard!=''">and identity_card like concat(concat('%', #{technician.identityCard}), '%')</if>
        <if test="technician.secondaryUnit!=null and technician.secondaryUnit!=''">and secondary_unit like concat(concat('%', #{technician.secondaryUnit}), '%')</if>
        <if test="technician.department!=null and technician.department!=''">and department like concat(concat('%', #{technician.department}), '%')</if>
        <if test="technician.post!=null and technician.post!=''">and post like concat(concat('%', #{technician.post}), '%')</if>
        <if test="technician.postLocation!=null and technician.postLocation!=''">and post_location like concat(concat('%', #{technician.postLocation}), '%')</if>
        <if test="technician.nature!=null and technician.nature!=''">and nature = #{technician.nature}</if>
        <if test="technician.education!=null and technician.education!=''">and education = #{technician.education}</if>
        <if test="technician.degree!=null and technician.degree!=''">and degree = #{technician.degree}</if>
        <if test="technician.rank!=null and technician.rank!=''">and rank = #{technician.rank}</if>
        <if test="technician.personnelType!=null and technician.personnelType!=''">and personnel_type = #{technician.personnelType}</if>
        <if test="technician.situation !=null and technician.situation!=''">and situation = #{technician.situation}</if>
        <if test="technician.practicingRequirement !=null and technician.practicingRequirement!=''">and practicing_requirement like concat(concat('%', #{technician.practicingRequirement}), '%')</if>
        <if test="technician.hireStartDate!=null and technician.hireEndDate!=''">and hire_Date between #{technician.hireStartDate} and #{technician.hireEndDate}</if>
        <choose>
            <when test="technician.yearAndMonth.length()==4">
                and date_format(year_and_month,'%Y') = #{technician.yearAndMonth}
            </when>
            <otherwise>
                and date_format(year_and_month,'%Y-%m') = #{technician.yearAndMonth}
            </otherwise>
        </choose>
        order by year_and_month
    </select>

    <select id="selectByYearAndMonth">
        select * from kd_technician
        where is_deleted = 0 and date_format(year_and_month,'%Y-%m') = #{yearAndMonth}

    </select>

    <select id="exportTechnician" resultType="org.sky.scientific.excel.TechnicianExcel">
        SELECT *
        FROM kd_technician ${ew.customSqlSegment}
    </select>

    <select id="countByDateAndNumberOrIdentityCard" parameterType="org.sky.scientific.pojo.entity.TechnicianEntity">
        select count(*)
        from kd_technician
        where is_deleted = 0
        <choose>
            <when test="yearAndMonth.length()==4">
                and date_format(year_and_month,'%Y') = #{yearAndMonth}
            </when>
            <otherwise>
                and date_format(year_and_month,'%Y-%m') = #{yearAndMonth}
            </otherwise>
        </choose>
        and (number = #{number} or identity_card = #{identityCard})
    </select>

</mapper>
