<?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.cool.store.mapper.EmployeeTrainingMapper">
    <resultMap id="BaseResultMap" type="com.cool.store.entity.EmployeeTrainingDO">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="shop_id" jdbcType="BIGINT" property="shopId"/>
        <result column="region_id" jdbcType="BIGINT" property="regionId"/>
        <result column="xfsg_user_detail_id" jdbcType="BIGINT" property="xfsgUserDetailId"/>
        <result column="assign_flag" jdbcType="TINYINT" property="assignFlag"/>
        <result column="training_start_time" jdbcType="TIMESTAMP" property="trainingStartTime"/>
        <result column="training_end_time" jdbcType="TIMESTAMP" property="trainingEndTime"/>
        <result column="training_store_id" jdbcType="VARCHAR" property="trainingStoreId"/>
        <result column="training_teacher_user_id" jdbcType="VARCHAR" property="trainingTeacherUserId"/>
        <result column="practical_assessment_user_id" jdbcType="VARCHAR" property="practicalAssessmentUserId"/>
        <result column="estimated_assessment_time" jdbcType="TIMESTAMP" property="estimatedAssessmentTime"/>
        <result column="actual_assessment_time" jdbcType="TIMESTAMP" property="actualAssessmentTime"/>
        <result column="assessment_num" jdbcType="INTEGER" property="assessmentNum"/>
        <result column="assessment_total_num" jdbcType="INTEGER" property="assessmentTotalNum"/>
        <result column="theoretical_exam_status" jdbcType="TINYINT" property="theoreticalExamStatus"/>
        <result column="theoretical_exam_score" jdbcType="FLOAT" property="theoreticalExamScore"/>
        <result column="practical_exam_status" jdbcType="TINYINT" property="practicalExamStatus"/>
        <result column="practical_exam_score" jdbcType="INTEGER" property="practicalExamScore"/>
        <result column="assessment_status" jdbcType="TINYINT" property="assessmentStatus"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="deleted" jdbcType="BIT" property="deleted"/>
    </resultMap>
    <select id="employeeTrainingList" resultType="com.cool.store.vo.EmployeeTrainingVO">
        select
        u.id,
        u.username,
        u.mobile,
        u.region_id as regionId,
        u.role_id as roleId,
        u.shop_id as shopId,
        u.register_time as registerTime,
        s.shop_name as shopName,
        u.line_id as lineId,
        e.id as employeeTrainingId,
        e.assessment_num as assessmentNum,
        e.assessment_total_num as assessmentTotalNum,
        e.theoretical_exam_status as theoreticalExamStatus,
        e.theoretical_exam_score as theoreticalExamScore,
        e.practical_exam_status as practicalExamStatus,
        e.practical_exam_score as practicalExamScore,
        e.assessment_status as assessmentStatus,
        e.training_teacher_user_id as trainingTeacherUserId,
        e.practical_assessment_user_id as practicalAssessmentUserId,
        e.training_start_time as trainingStartTime,
        e.training_end_time as trainingEndTime,
        e.training_store_id as trainingStoreId,
        e.estimated_assessment_time as estimatedAssessmentTime,
        e.actual_assessment_time as actualAssessmentTime,
        ifnull(e.assign_flag,0) as assignFlag
        from xfsg_temp_user_detail u
        left join xfsg_employee_training e on e.xfsg_user_detail_id = u.id
        left join xfsg_shop_info s on s.id = u.shop_id
        where u.deleted = 0
        <if test="keyWord != null and keyWord != ''">
            and (u.username like concat('%',#{keyWord},'%') or u.mobile like concat('%',#{keyWord},'%'))
        </if>
        <if test="regionIdList != null and regionIdList.size() > 0">
            and u.region_id in
            <foreach collection="regionIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="authRegionIdList != null and authRegionIdList.size() > 0">
            and u.region_id in
            <foreach collection="authRegionIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="assignFlag != null and assignFlag == 1">
            and e.assign_flag = #{assignFlag}
        </if>
        <if test="assignFlag != null and assignFlag == 0">
            and e.assign_flag is null
        </if>
        <if test="beginTime != null and beginTime != ''">
            and u.register_time &gt;= #{beginTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and u.register_time &lt;= #{endTime}
        </if>
        <if test="storeNumList != null and storeNumList.size() > 0">
            and s.store_num in
            <foreach collection="storeNumList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="theoreticalExamStatus != null">
            and e.theoretical_exam_status = #{theoreticalExamStatus}
        </if>
        <if test="practicalExamStatus != null">
            and e.practical_exam_status = #{practicalExamStatus}
        </if>
        <if test="assessmentStatus != null and assessmentStatus == 0">
            and e.assessment_total_num > e.assessment_num
        </if>
        <if test="assessmentStatus != null and assessmentStatus == 1">
            and e.assessment_total_num = e.assessment_num
        </if>
        <if test="assignStoreFlag != null and assignStoreFlag == 1">
            and s.store_num is not null
        </if>
        <if test="assignStoreFlag != null and assignStoreFlag == 0">
            and s.store_num is null
        </if>
    </select>
    <select id="trainingListByShopId" resultType="com.cool.store.vo.EmployeeTrainingVO">
        select
        u.id,
        u.username,
        u.mobile,
        u.region_id as regionId,
        u.role_id as roleId,
        u.shop_id as shopId,
        s.shop_name as shopName,
        u.id_card as idCard,
        u.register_time as registerTime,
        e.id as employeeTrainingId,
        e.training_store_id as trainingStoreId,
        e.training_teacher_user_id as trainingTeacherUserId,
        e.practical_assessment_user_id as practicalAssessmentUserId,
        e.assessment_num as assessmentNum,
        e.assessment_total_num as assessmentTotalNum,
        ifnull(e.theoretical_exam_status, 0) as theoreticalExamStatus,
        e.theoretical_exam_score as theoreticalExamScore,
        ifnull(e.practical_exam_status,0 ) as practicalExamStatus,
        e.practical_exam_score as practicalExamScore,
        ifnull(e.assessment_status,0) as assessmentStatus,
        ifnull(e.assign_flag,0) as assignFlag
        from xfsg_temp_user_detail u
        left join xfsg_employee_training e on e.xfsg_user_detail_id = u.id
        left join xfsg_shop_info s on s.id = u.shop_id
        where u.deleted = 0
        <if test="trainingTeacherUserId != null and trainingTeacherUserId != ''">
            and e.training_teacher_user_id = #{trainingTeacherUserId}
        </if>
        <if test="shopId != null">
            and u.shop_id = #{shopId}
        </if>
        <if test="status != null and status == 0">
            and e.assessment_total_num > e.assessment_num
        </if>
        <if test="status != null and status == 1">
            and e.assessment_total_num = e.assessment_num
        </if>
    </select>
    <select id="assessmentUserList" resultType="com.cool.store.vo.EmployeeTrainingVO">
        select
        u.id,
        u.username,
        u.mobile,
        u.region_id as regionId,
        u.role_id as roleId,
        u.shop_id as shopId,
        s.shop_name as shopName,
        u.id_card as idCard,
        u.register_time as registerTime,
        e.id as employeeTrainingId,
        e.training_store_id as trainingStoreId,
        e.training_teacher_user_id as trainingTeacherUserId,
        e.practical_assessment_user_id as practicalAssessmentUserId,
        e.assessment_num as assessmentNum,
        e.assessment_total_num as assessmentTotalNum,
        e.theoretical_exam_status as theoreticalExamStatus,
        e.theoretical_exam_score as theoreticalExamScore,
        e.practical_exam_status as practicalExamStatus,
        e.practical_exam_score as practicalExamScore,
        e.assessment_status as assessmentStatus,
        e.estimated_assessment_time as estimatedAssessmentTime,
        e.actual_assessment_time as actualAssessmentTime,
        e.training_start_time as trainingStartTime,
        e.training_end_time as trainingEndTime
        from xfsg_temp_user_detail u
        left join xfsg_employee_training e on e.xfsg_user_detail_id = u.id
        left join xfsg_shop_info s on s.id = u.shop_id
        where u.deleted = 0

        <if test="assessmentStatus != null">
            and e.assessment_status = #{assessmentStatus}
        </if>
        <if test="estimatedAssessmentBeginTime != null and estimatedAssessmentBeginTime != ''">
            and e.estimated_assessment_time &gt;= #{estimatedAssessmentBeginTime}
        </if>
        <if test="estimatedAssessmentEndTime != null and estimatedAssessmentEndTime != ''">
            and e.estimated_assessment_time &lt;= #{estimatedAssessmentEndTime}
        </if>
        <if test="actualAssessmentBeginTime != null and actualAssessmentBeginTime != ''">
            and e.actual_assessment_time &gt;= #{actualAssessmentBeginTime}
        </if>
        <if test="actualAssessmentEndTime != null and actualAssessmentEndTime != ''">
            and e.actual_assessment_time &lt;= #{actualAssessmentEndTime}
        </if>
        <if test="name != null and name != ''">
            and u.username like concat('%',#{name},'%')
        </if>
        <if test="practicalAssessmentUserId != null and practicalAssessmentUserId != '' and storeIdList != null and storeIdList.size() > 0">
            and (e.practical_assessment_user_id = #{practicalAssessmentUserId}
            or e.training_store_id in
            <foreach collection="storeIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>
        <if test="practicalAssessmentUserId == null and storeIdList != null and storeIdList.size() > 0">
            and e.training_store_id in
            <foreach collection="storeIdList" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>
</mapper>