根据日期筛选和模糊查询

<?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.springblade.mapper.EmployeeTrainingRecordMapper">
    <update id="timingEliminateInfo">
        update blade_employee_training_record set month_duration=''
    </update>
    <!--年初清空培训记录年度时长-->
    <update id="emptyYearDuration">
        update blade_employee_training_record set year_duration=''
    </update>

    <select id="getPageEmployeeTrainingRecordList" parameterType="org.springblade.entity.EmployeeTrainingRecord" resultType="org.springblade.entity.EmployeeTrainingRecord">
        select u.dept_id,u.account,u.real_name,e.department,e.entry_date,e.education_background,e.position,e.company,a.* from blade_employee_basic_information e LEFT JOIN blade_user u on e.id=u.id
        left join blade_employee_training_record a on u.account =a.account
        where u.tenant_id=#{bean.tenantId} and e.working_state='0'
        <if test="bean.userName != null and bean.userName !=''">
            and u.real_name like '%%${bean.userName}%%'
        </if>
        <if test="bean.account != null and bean.account !=''">
            and u.account like '%%${bean.account}%%'
        </if>
        <if test="bean.department != null and bean.department !=''">
            and e.department like '%%${bean.department}%%'
        </if>
        <if test="bean.company != null and bean.company !=''">
            and e.company like '%%${bean.company}%%'
        </if>
    </select>
    <select id="getOneInfo" resultType="org.springblade.entity.EmployeeTrainingRecord">
        select u.account,u.real_name,e.department,e.entry_date,e.education_background,e.position,a.* from blade_employee_basic_information e LEFT JOIN blade_user u on e.id=u.id
        left join blade_employee_training_record a on u.account =a.account
        where u.tenant_id=#{tenantId} and  u.account=#{account}
    </select>
    <select id="getDeptUser" resultType="org.springblade.entity.AllEmployeeBasicInformation">
        select e.*,u.* from blade_employee_basic_information e left join blade_user u on e.id=u.id where e.tenant_id=#{tenantId} and e.department=#{department}
        <if test="workingState !=null and workingState !='' ">
            and e.working_state=#{workingState}
        </if>

    </select>

    <!--查询当月在职人数-->
    <select id="getEachMonthEmployees" resultType="org.springblade.entity.EmployeeBasicInformation">
        select * from blade_employee_basic_information where tenant_id=#{tenantId} and working_state='0'
        UNION
        select * from blade_employee_basic_information where tenant_id=#{tenantId}
        <if test="startDate !=null and startDate !='' ">
            and last_working_day >= #{startDate}
        </if>
        <if test="endDate !=null and endDate !='' ">
            and last_working_day <= #{endDate}
        </if>
    </select>
    <!--根据部门部门id计算该部门每个月的培训时长-->
    <select id="getTrainingRecordTotalByDeptId" resultType="java.lang.Double">
        select IFNULL(sum(duration_time),0) from blade_employee_training_record_detail where tenant_id=#{tenantId}
        <if test="deptId !=null and deptId !='' ">
            and dept_id =#{deptId}
        </if>
        <if test="startDate !=null and startDate !='' ">
            and end_training_date >= #{startDate}
        </if>
        <if test="endDate !=null and endDate !='' ">
            and end_training_date <= #{endDate}
        </if>

    </select>
    <!--根据部门id查询当前部门在职人数-->
    <select id="getDeptUserByDeptId" resultType="org.springblade.entity.AllEmployeeBasicInformation">
        select a.department,b.real_name,b.account,a.last_working_day from blade_employee_basic_information a left join blade_user b on a.id=b.id where a.tenant_id=#{tenantId}
        and (a.last_working_day is null or trim(a.last_working_day)='') and b.dept_id like '%%${deptId}%%'
        UNION
        select c.department,d.real_name,d.account,c.last_working_day from blade_employee_basic_information c left join blade_user d on c.id=d.id where c.tenant_id=#{tenantId}
        and d.dept_id  like '%%${deptId}%%'

        <if test="startDate !=null and startDate !='' ">
            and c.last_working_day >= #{startDate}
        </if>
        <if test="endDate !=null and endDate !='' ">
            and c.last_working_day <= #{endDate}
        </if>

    </select>
    <!--查询公司当前在职人数-->
    <select id="getOnJobPerson" resultType="java.lang.Integer">
        select count(*) from blade_employee_basic_information where working_state='0' and tenant_id=#{tenantId}
    </select>
    <!--根据租户id获取员工基本信息-->
    <select id="getEmployeeMsg" resultType="org.springblade.entity.AllEmployeeBasicInformation">
        select e.*,u.* from blade_employee_basic_information e left join blade_user u on e.id=u.id where u.tenant_id=#{tenantId}
        and e.working_state='0'
        <if test="startDate !=null and startDate !='' ">
            and e.entry_date >= #{startDate}
        </if>
    </select>
    <!--获取员工培训记录时长,根据开始和结束时间区间-->
    <select id="getEmployeeTrainingHours" resultType="java.lang.Double">
        select IFNULL(sum(duration_time),0) from blade_employee_training_record_detail where tenant_id=#{tenantId}
        <if test="account !=null and account !='' ">
            and account =#{account}
        </if>
        <if test="startDate !=null and startDate !='' ">
            and end_training_date >= #{startDate}
        </if>
        <if test="endDate !=null and endDate !='' ">
            and end_training_date <= #{endDate}
        </if>
    </select>
    <!--根据人员名称获取员工信息-->
    <select id="getUserInfoByUserName" resultType="java.util.Map">
        select a.*,b.entry_date from blade_user a left join blade_employee_basic_information b on b.id=a.id  where a.real_name=#{userName} and a.is_deleted=0
    </select>
</mapper>