<?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.qmth.themis.business.dao.TEStudentMapper">

    <select id="studentQuery" resultType="com.qmth.themis.business.dto.response.TEStudentDto">
        select
        t.id,
        t.name,
        t.identity,
        t.orgName,
        t.enable,
        t.basePhotoPath,
        if(t.updateName is not null, t.updateName, t.createName) as updateName,
        if(t.updateTime is not null, t.updateTime, t.createTime) as updateTime
        from
        (select
        tes.id,
        tes.name,
        tes.`identity`,
        tbo.name as orgName,
        tes.enable,
        tes.base_photo_path as basePhotoPath,
        (select tbu.name from t_b_user tbu where tbu.id = tes.create_id) as createName,
        (select tbu.name from t_b_user tbu where tbu.id = tes.update_id) as updateName,
        tes.create_time as createTime,
        tes.update_time as updateTime
        from
        t_e_student tes
        left join t_b_org tbo on
        tbo.id = tes.org_id
        <where>
            <if test="identity != null and identity != ''">
                and tes.identity like concat('%', #{identity}, '%')
            </if>
            <if test="name != null and name != ''">
                and tes.name like concat('%', #{name}, '%')
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and tes.enable = #{enable}
            </if>
        </where>
        ) t order by t.name
    </select>

    <select id="studentExamRecordQuery" resultType="com.qmth.themis.business.dto.response.TEStudentExamRecordDto">
        select
            tee.id as examId,
            tee.name as examName,
            tees.id as examStudentId,
            toer.id as examRecordId,
            tees.course_code as courseCode,
            tees.course_name as courseName,
            tees.`identity`,
            tees.name,
            toer.objective_score as objectiveScore,
            toer.status
        from
            t_e_exam_student tees
        left join t_e_exam tee on
            tee.id = tees.exam_id
        inner join t_oe_exam_record toer on
            toer.exam_student_id = tees.id and toer.exam_id = tee.id
        left join t_e_student tes on
            tes.id = tees.student_id
            <where>
                <if test="studentId != null and studentId != ''">
                    and tes.id = #{studentId}
                </if>
                <if test="examId != null and examId != ''">
                    and tee.id = #{examId}
                </if>
            </where>
    </select>
</mapper>
