<?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.teachcloud.common.mapper.BasicStudentMapper">
    <delete id="deleteTeachStudentByBasicStudentIdSet">
        DELETE FROM teach_student WHERE
            <if test="idSet != null and idSet != '' and idSet.size > 0">
                student_id IN
                <foreach collection="idSet" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
    </delete>

    <select id="listByClass" resultType="com.qmth.teachcloud.common.bean.dto.TaskPrintClassDto">
        SELECT
            s.clazz_id classId,
            clz.clazz_name className,
            COUNT(1) studentCount
        FROM
            basic_student s
                LEFT JOIN
            basic_clazz clz ON s.clazz_id = clz.id and clz.enable = true
        WHERE
            s.school_id = #{schoolId}
            <if test="stringList != null and stringList != '' and stringList.size > 0">
                and s.clazz_id in
                <foreach collection="stringList" item="cla" open="(" close=")" separator=",">
                    #{cla}
                </foreach>
            </if>
        GROUP BY s.clazz_id, clz.clazz_name
    </select>

    <select id="findBasicStudentPage"
            resultType="com.qmth.teachcloud.common.bean.result.BasicStudentResult">
        SELECT
            stu.id,
            student_name AS studentName,
            student_code AS studentCode,
            phone_number AS phoneNumber,
            bc.id AS collegeId,
            bc.college_name AS collegeName,
            bm.id AS majorId,
            bm.name AS majorName,
            clazz_id AS clazzId,
            clz.clazz_name AS clazz,
            stu.enable,
            stu.create_id AS createId,
            stu.create_time AS createTime
        FROM
            basic_student stu
                LEFT JOIN
            basic_clazz clz ON stu.clazz_id = clz.id AND clz.enable = TRUE
                LEFT JOIN
            basic_major bm ON stu.major_id = bm.id AND bm.enable = TRUE
                LEFT JOIN
            basic_college bc ON stu.belong_org_id = bc.id
        <where>
            and stu.enable = true
            <if test="queryParams != null and queryParams != ''">
                and (student_name like concat('%',#{queryParams},'%') or student_code like concat('%',#{queryParams},'%'))
            </if>
            <if test="schoolId != null and schoolId != ''">
                and stu.school_id = #{schoolId}
            </if>
            <if test="collegeId != null and collegeId != ''">
                and stu.belong_org_id = #{collegeId}
            </if>
            <if test="majorId != null and majorId != ''">
                and stu.major_id = #{majorId}
            </if>
            <if test="clazzIdList != null and clazzIdList != '' and clazzIdList.size > 0">
                AND stu.clazz_id IN
                <foreach collection="clazzIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND stu.create_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND stu.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
        ORDER BY stu.create_time DESC
    </select>
    <select id="findById" resultType="com.qmth.teachcloud.common.bean.result.BasicStudentResult">
        SELECT
            stu.id,
            student_name AS studentName,
            student_code AS studentCode,
            phone_number AS phoneNumber,
            bc.id AS collegeId,
            bc.college_name AS collegeName,
            bm.id AS majorId,
            bm.name AS majorName,
            clazz_id AS clazzId,
            clz.clazz_name AS clazz,
            stu.enable,
            stu.create_id AS createId,
            stu.create_time AS createTime
        FROM
            basic_student stu
                LEFT JOIN
            basic_clazz clz ON stu.clazz_id = clz.id AND clz.enable = TRUE
                LEFT JOIN
            basic_major bm ON stu.major_id = bm.id AND bm.enable = TRUE
                LEFT JOIN
            basic_college bc ON stu.belong_org_id = bc.id
        <where>
            and stu.enable = true
            <if test="id != null and id != ''">
                AND stu.id = #{id}
            </if>
        </where>
    </select>
    <select id="findBasicStudentList" resultType="com.qmth.teachcloud.common.bean.result.BasicStudentResult">
        SELECT
            stu.id,
            student_name AS studentName,
            student_code AS studentCode,
            phone_number AS phoneNumber,
            bc.id AS collegeId,
            bc.college_name AS collegeName,
            bm.id AS majorId,
            bm.name AS majorName,
            clazz_id AS clazzId,
            clz.clazz_name AS clazz,
            stu.enable,
            stu.create_id AS createId,
            stu.create_time AS createTime
        FROM
            basic_student stu
                LEFT JOIN
            basic_clazz clz ON stu.clazz_id = clz.id AND clz.enable = TRUE
                LEFT JOIN
            basic_major bm ON stu.major_id = bm.id AND bm.enable = TRUE
                LEFT JOIN
            basic_college bc ON stu.belong_org_id = bc.id
        <where>
            and stu.enable = true
            <if test="schoolId != null and schoolId != ''">
                and stu.school_id = #{schoolId}
            </if>
            <if test="collegeId != null and collegeId != ''">
                and stu.belong_org_id = #{collegeId}
            </if>
            <if test="majorId != null and majorId != ''">
                and stu.major_id = #{majorId}
            </if>
            <if test="clazzId != null and clazzId != ''">
                and stu.clazz_id = #{clazzId}
            </if>
            <if test="studentCodeList != null and studentCodeList != '' and studentCodeList.size > 0">
                AND stu.student_code IN
                <foreach collection="studentCodeList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        ORDER BY stu.create_time DESC
    </select>
    <select id="listExportData" resultType="com.qmth.teachcloud.common.bean.dto.BasicStudentExportDto">
        SELECT
        stu.id,
        student_name AS studentName,
        student_code AS studentCode,
        phone_number AS phoneNumber,
        bc.id AS collegeId,
        bc.college_name AS collegeName,
        bm.id AS majorId,
        bm.name AS majorName,
        clazz_id AS clazzId,
        clz.clazz_name AS clazzName,
        stu.enable,
        case stu.enable when true then '启用' else '禁用' end enableName,
        stu.create_id AS createId,
        stu.create_time AS createTime
        FROM
        basic_student stu
        LEFT JOIN
        basic_clazz clz ON stu.clazz_id = clz.id AND clz.enable = TRUE
        LEFT JOIN
        basic_major bm ON stu.major_id = bm.id AND bm.enable = TRUE
        LEFT JOIN
        basic_college bc ON stu.belong_org_id = bc.id
        <where>
            and stu.enable = true
            <if test="queryParams != null and queryParams != ''">
                and (student_name like concat('%',#{queryParams},'%') or student_code like concat('%',#{queryParams},'%'))
            </if>
            <if test="schoolId != null and schoolId != ''">
                and stu.school_id = #{schoolId}
            </if>
            <if test="collegeId != null and collegeId != ''">
                and stu.belong_org_id = #{collegeId}
            </if>
            <if test="majorId != null and majorId != ''">
                and stu.major_id = #{majorId}
            </if>
            <if test="clazzIdList != null and clazzIdList != '' and clazzIdList.size > 0">
                AND stu.clazz_id IN
                <foreach collection="clazzIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND stu.create_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND stu.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
        ORDER BY stu.create_time DESC
    </select>
</mapper>
