<?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.xjrsoft.module.student.mapper.SchoolRollStudentMapper">
    <select id="getPage" parameterType="com.xjrsoft.module.student.dto.SchoolRollStudentPageDto" resultType="com.xjrsoft.module.student.vo.SchoolRollStudentPageVo">
        SELECT t.id,t.sort_code,t1.name,t1.credential_number,t3.name AS scholarship_source_cn,
        t4.name AS before_class_name,t5.name AS back_class_name,t1.remark,t.in_class_date,t.out_class_date
        ,t.scholarship_source,t2.student_id,t8.name AS applicant_user_name FROM school_roll_student t
        LEFT JOIN xjr_user t1 ON t1.id = t.student_id
        LEFT JOIN base_student t2 ON t.student_id = t2.user_id
        LEFT JOIN xjr_dictionary_detail t3 ON t.scholarship_source = t3.code AND t3.item_id = 1729751199857102849
        LEFT JOIN base_class t4 ON t.before_class_id = t4.id
        LEFT JOIN base_class t5 ON t.back_class_id = t5.id
        LEFT JOIN school_roll_category t6 ON t.school_roll_category_id = t6.id
        LEFT JOIN school_roll_reason t7 ON t.school_roll_reason_id = t7.id
        LEFT JOIN xjr_user t8 ON t8.id = t.applicant_user_id
        WHERE t.delete_mark = 0 AND t.status = 1
        <if test="dto.name != null and dto.name != ''">
            and t1.name like concat('%', #{dto.name}, '%')
        </if>
        <if test="dto.scholarshipSource != null and dto.scholarshipSource != ''">
            and t.scholarship_source = #{dto.scholarshipSource}
        </if>
        <if test="dto.schoolRollReasonId != null">
            and t7.id = #{dto.schoolRollReasonId}
        </if>
    </select>
    <select id="getInfo" resultType="com.xjrsoft.module.student.vo.SchoolRollStudentVo">
        select t.id,t1.name,t2.student_id,t1.gender,t1.credential_number,t3.name as scholarship_source_cn
        ,t.scholarship_source,t.is_retain,t8.name as applicant_user_name,t.create_date,t.is_record
        ,t.leave_year,t.is_under_graduate,t.status
        ,t4.name AS before_class_name,t9.name as before_grade_name,t10.name as before_major_set_name,
        t11.name as before_department_name,
        t5.name AS back_class_name,t12.name as back_grade_name,t13.name as back_major_set_name,
        t14.name as back_department_name,t.reason,
        t1.remark,t.in_class_date,t.out_class_date,t.status,t.leave_start_year,t.leave_end_year,t.is_maintained
        from school_roll_student t
        left join xjr_user t1 on t1.id = t.student_id
        left join base_student t2 on t.student_id = t2.user_id
        left join xjr_dictionary_detail t3 on t.scholarship_source = t3.code and t3.item_id = 1729751199857102849
        left join base_class t4 on t.before_class_id = t4.id
        LEFT JOIN base_class t5 ON t.back_class_id = t5.id
        left join school_roll_category t6 on t.school_roll_category_id = t6.id
        left join school_roll_reason t7 on t.school_roll_reason_id = t7.id
        LEFT JOIN xjr_user t8 ON t8.id = t.applicant_user_id
        left join base_grade t9 on t9.id = t.before_grade_id
        left join base_major_set t10 on t10.id = t.before_major_set_id
        left join xjr_department t11 on t11.id = t.before_department_id
        LEFT JOIN base_grade t12 ON t12.id = t.back_grade_id
        LEFT JOIN base_major_set t13 ON t13.id = t.back_major_set_id
        LEFT JOIN xjr_department t14 ON t14.id = t.back_department_id
        where t.delete_mark = 0 and t.status = 1
        and t.id = #{id}
    </select>
</mapper>