<?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.koobietech.eas.dao.mapper.GroupStudentsMapper">
    <resultMap id="groupStudentResultMap" type="com.koobietech.eas.dao.pojo.GroupStudentsPojo">
        <result column="avatar" property="avatar"/>
        <result column="id" property="id"/>
        <result column="enrollment_date" property="enrollmentDate"/>
        <result column="gender" property="gender"/>
        <result column="manager_id" property="managerId"/>
        <result column="phone" property="phone"/>
        <result column="email" property="email"/>
        <result column="student_name" property="studentName"/>
        <result column="student_number" property="studentNumber"/>
        <result column="student_id" property="studentId"/>
        <result column="admissions_id" property="admissionsId"/>
        <result column="type" property="type"/>
        <result column="admissions_name" property="admissionsName"/>
        <result column="manager_name" property="managerName"/>
        <result column="class_id" property="classId" />
    </resultMap>
    <select id="getStudents" resultType="com.koobietech.eas.dao.dto.GroupStudentsDto" resultMap="groupStudentResultMap">
        SELECT
            re.id,
            ss.avatar,
            ss.enrollment_date,
            ss.gender,
            ss.manager_id,
            ss.phone,
            ss.email,
            ss.student_name,
            ss.id AS student_id,
            ss.admissions_id,
            ss.student_number,
            re.type,
            re.class_id,
            su.relname AS admissions_name,
            suu.relname AS  manager_name
        FROM
            eas_edu_clt_relation AS re
                INNER JOIN eas_sys_student AS ss ON ss.id = re.student_id
                INNER JOIN eas_edu_class AS ec ON ec.id = re.class_id
                LEFT JOIN eas_sys_userinfo AS su ON su.id = ss.admissions_id
                LEFT JOIN eas_sys_userinfo AS suu ON suu.id = ss.manager_id
        WHERE ec.disabled = 'N'
        <if test="enrollmentDate != null">
            AND ss.enrollment_date = #{enrollmentDate}
        </if>
        <if test="gender != null and gender != ''">
            AND ss.gender = #{gender}
        </if>
        <if test="managerId != null and managerId != 0">
            AND ss.manager_id = #{managerId}
        </if>
        <if test="phone != null and phone != ''">
            AND ss.phone LIKE CONCAT('%', #{phone}, '%')
        </if>
        <if test="studentName != null and studentName != ''">
            AND ss.student_name LIKE CONCAT('%', #{studentName}, '%')
        </if>
        <if test="studentId != null  and studentId != 0">
            AND ss.id = #{studentId}
        </if>
        <if test="admissionsId != null and admissionsId != 0">
            AND ss.admissions_id = #{admissionsId}
        </if>
        <if test="type != null and type != ''">
            AND re.type = #{type}
        </if>
        <if test="admissionsName != null and admissionsName != ''">
            AND su.relname = #{admissionsName}
        </if>
        <if test="managerName != null and managerName != ''">
            AND suu.relname = #{managerName}
        </if>
        <if test="classId != null and classId > 0">
            AND re.class_id = #{classId}
        </if>

    </select>
</mapper>