<?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.BaseStudentBehaviorManageMapper">
    <select id="getPage" parameterType="com.xjrsoft.module.student.dto.BaseStudentBehaviorManagePageDto" resultType="com.xjrsoft.module.student.vo.BaseStudentBehaviorManagePageVo">
        SELECT t.id, t2.score_type,
        t.file_id, t.assessment_address, t3.name AS semester_name, t1.sort_code,
        t.assessment_date, t.score, t.score_number, t.total_score, t4.name AS grade_name,
        t.is_affect, t1.name AS behaviorCategoryName, t2.name AS behaviorProjectName,
        (
        SELECT GROUP_CONCAT(b.name) FROM base_student_behavior_class_relation a
        LEFT JOIN base_class b ON a.class_id = b.id
        WHERE a.base_student_behavior_manage_id = t.id GROUP BY a.base_student_behavior_manage_id
        ) AS behavior_class_names,
        (
        SELECT NAME FROM xjr_user WHERE id = t.assessment_user_id
        ) AS assessment_user_name
        FROM base_student_behavior_manage t
        LEFT JOIN base_student_behavior_category t1 ON t1.id = t.base_student_behavior_category_id
        LEFT JOIN base_student_behavior_project t2 ON t2.id = t.base_student_behavior_project_id
        LEFT JOIN base_semester t3 ON t3.id = t.base_semester_id
        LEFT JOIN base_grade t4 ON t.grade_id = t4.id
        WHERE t.delete_mark = 0 AND t1.delete_mark = 0 AND t2.delete_mark = 0 AND t.status = 1 AND t1.delete_mark = 0

        <if test="dto.gradeId != null">
            and t.grade_id = #{dto.gradeId}
        </if>
        <if test="dto.semesterId != null">
            and t.base_semester_id = #{dto.semesterId}
        </if>
        <if test="dto.startDate != null and dto.startDate != ''">
            and t.assessment_date &gt;= #{dto.startDate}
        </if>
        <if test="dto.endDate != null and dto.endDate != ''">
            and t.assessment_date &lt;= #{dto.endDate}
        </if>
        <if test="dto.assessmentUserIds != null and dto.assessmentUserIds.size() > 0">
            AND t.assessment_user_id in
            <foreach item="userId" index="index" collection="dto.assessmentUserIds" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
    </select>

    <select id="getInfo" resultType="com.xjrsoft.module.student.vo.BaseStudentBehaviorManageVo">
        SELECT t.id, t2.score_type, t1.sort_code,
               t.file_id, t.assessment_address, t3.name AS semester_name,
               t.assessment_date, t.score, t.score_number, t.total_score, t4.name AS grade_name,
               t.is_affect, t1.name AS behaviorCategoryName, t2.name AS behaviorProjectName,
               (
                   SELECT GROUP_CONCAT(b.name) FROM base_student_behavior_class_relation a
                   LEFT JOIN base_class b ON a.class_id = b.id
                   WHERE a.base_student_behavior_manage_id = t.id GROUP BY a.base_student_behavior_manage_id
               ) AS asbinarysessment_class_names,
               (
                   SELECT name FROM xjr_user WHERE id = t.assessment_user_id
               ) AS assessment_user_name
        FROM base_student_behavior_manage t
                 LEFT JOIN base_student_behavior_category t1 ON t1.id = t.base_student_behavior_category_id
                 LEFT JOIN base_student_behavior_project t2 ON t2.id = t.base_student_behavior_project_id
                 LEFT JOIN base_semester t3 ON t3.id = t.base_semester_id
                 LEFT JOIN base_grade t4 ON t.grade_id = t4.id
        WHERE t.delete_mark = 0 AND t1.delete_mark = 0 AND t2.delete_mark = 0 AND t.status = 1
          AND t.id = #{id}
    </select>

    <select id="getMobilePage" parameterType="com.xjrsoft.module.student.dto.BaseStudentBehaviorManageMobilePageDto" resultType="com.xjrsoft.module.student.vo.BaseStudentBehaviorManageMobilePageVo">
        SELECT t1.sort_code,t1.name,t1.class_name,t.assessment_date,
        t3.name AS categoryName,t4.name AS projectName,t.score,t4.score_type,
        t2.name AS assessmentUserName,
        t.assessment_address,
        t3.name AS behaviorCategoryName,
        t4.name AS behaviorProjectName
        FROM base_student_behavior_manage t
        INNER JOIN base_student_behavior_student_relation t1 ON t1.base_student_behavior_manage_id = t.id
        <if test="dto.studentName != null and dto.studentName != ''">
            and t1.name like concat('%',#{dto.studentName},'%')
        </if>
        LEFT JOIN xjr_user t2 ON t2.id = t.assessment_user_id
        LEFT JOIN base_student_behavior_category t3 ON t3.id = t.base_student_behavior_category_id
        LEFT JOIN base_student_behavior_project t4 ON t4.id = t.base_student_behavior_project_id
        LEFT JOIN base_student_school_roll t5 ON t1.user_id = t5.user_id
        WHERE t.status = 1 AND t.delete_mark = 0 AND t3.delete_mark = 0
        AND t5.class_id IN (
        SELECT id FROM base_class WHERE teacher_id = #{dto.teacherId}
        )
        <if test="dto.gradeIds != null and dto.gradeIds.size() > 0">
            AND t5.grade_id in
            <foreach item="gradeId" index="index" collection="dto.gradeIds" open="(" close=")" separator=",">
                #{gradeId}
            </foreach>
        </if>
        <if test="dto.classIds != null and dto.classIds.size() > 0">
            AND t5.class_id in
            <foreach item="classId" index="index" collection="dto.classIds" open="(" close=")" separator=",">
                #{classId}
            </foreach>
        </if>
        <if test="dto.assessmentUserIds != null and dto.assessmentUserIds.size() > 0">
            AND t.assessment_user_id in
            <foreach item="assessmentUserId" index="index" collection="dto.assessmentUserIds" open="(" close=")" separator=",">
                #{assessmentUserId}
            </foreach>
        </if>
        <if test="dto.startDate != null and dto.startDate != ''">
            and t.assessment_date &gt;= #{dto.startDate}
        </if>
        <if test="dto.endDate != null and dto.endDate != ''">
            and t.assessment_date &lt;= #{dto.endDate}
        </if>
    </select>

</mapper>