<?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.srs.comprehensive.mapper.SrsClassMapper">

    <resultMap type="SrsClass" id="SrsClassResult">
        <result property="classId"    column="class_id"    />
        <result property="classCode"    column="class_code"    />
        <result property="majorId"    column="major_id"    />
        <result property="className"    column="class_name"    />
        <result property="gradeId"    column="grade_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="teacherId" column="teacher_id"/>
        <result property="status"    column="status"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <association property="dept"    column="dept_id" javaType="SysDept" resultMap="deptResult" />
        <association property="majors"    column="major_id" javaType="srsMajors" resultMap="SrsMajorsResult" />
        <association property="grade"    column="grade_id" javaType="SrsGrade" resultMap="SrsGradeResult" />
        <association property="teacher"    column="teacher_id" javaType="CphTeacher" resultMap="CphTeacherResult" />
    </resultMap>

    <resultMap type="SrsGrade" id="SrsGradeResult">
    <result property="gradeId"    column="grade_id"    />
    <result property="gradeName"    column="grade_name"    />
    </resultMap>
    <resultMap type="SrsMajors" id="SrsMajorsResult">
    <result property="majorId"    column="major_id"    />
    <result property="majorName"    column="major_name"    />
    </resultMap>
    <resultMap id="deptResult" type="SysDept">
    <id     property="deptId"    column="dept_id"     />
    <result property="parentId"  column="parent_id"   />
    <result property="deptName"  column="dept_name"   />
    </resultMap>
    <resultMap type="CphTeacher" id="CphTeacherResult">
        <result property="teacherId"    column="teacher_id"    />
        <result property="name"    column="name"    />
        <result property="employeeId"    column="employee_Id"    />
    </resultMap>

    <sql id="selectSrsClassVo">
        select class_id,class_name,class_code, major_id, teacher_id, grade_id,dept_id, status, create_by, create_time, update_by, update_time from srs_class
    </sql>
    <sql id="selectSrsClassJoin">
        select c.class_id, c.major_id, class_code,c.teacher_id,c.class_name, c.dept_id, c.status, c.create_by,
               c.create_time, c.update_by, c.update_time,
               m.*,d.*,g.*,t.*
        from srs_class c
                 left JOIN srs_majors m ON c.major_id = m.major_id
                 left JOIN sys_dept d ON m.college_id = d.dept_id
                 left join srs_grade g on c.grade_id = g.grade_id
                 left join cph_teacher t on t.teacher_id = c.teacher_id
    </sql>

    <select id="selectSrsClassInfoList" parameterType="SrsClass" resultMap="SrsClassResult">
        select * from srs_class
    </select>

    <select id="selectSrsClassList" parameterType="SrsClass" resultMap="SrsClassResult">
        <include refid="selectSrsClassJoin"/>
        <where>
            <if test="majorId != null "> and c.major_id = #{majorId}</if>
            <if test="gradeId != null "> and c.grade_id = #{gradeId}</if>
            <if test="className != null  and className != ''"> and class_name like concat('%', #{className}, '%')</if>
            <if test="deptId != null "> and d.dept_id = #{deptId}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="teacherName != null  and teacherName != ''"> and t.name like concat('%', #{teacherName}, '%')</if>

        </where>
        order by class_code desc
    </select>

    <select id="selectSrsClassListTeacherId" parameterType="SrsClass" resultMap="SrsClassResult">
        select * from srs_class where teacher_id=#{teacherId}
    </select>


    <select id="selectSrsClassNameList" parameterType="SrsClass" resultMap="SrsClassResult">
        select class_id,class_name, major_id from srs_class
        order by class_code
    </select>

    <select id="selectSrsClassByClassCode" parameterType="String" resultMap="SrsClassResult">
        <include refid="selectSrsClassVo"/>
        where class_code = #{classCode}
    </select>

    <select id="selectClassIdNameByTid" resultType="com.srs.comprehensive.domain.Vo.ClassIdName">
        select class_id,class_name from srs_class
        where teacher_id = #{t_id}
        order by grade_id desc,class_name asc
    </select>

    <select id="selectSrsClassByClassId" parameterType="Long" resultMap="SrsClassResult">
        <include refid="selectSrsClassJoin"/>
        where class_id = #{classId}
    </select>
    <select id="getGradeId" resultType="java.lang.Long">
        select grade_id from srs_grade where grade_code=#{njdm}
    </select>

    <insert id="insertSrsClass" parameterType="SrsClass">
        insert into srs_class
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="classId != null">class_id,</if>
            <if test="className != null">class_name,</if>
            <if test="majorId != null">major_id,</if>
            <if test="gradeId != null ">grade_id, </if>
            <if test="teacherId != null">teacher_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="status != null">status,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="classCode != null">class_code,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="classId != null">#{classId},</if>
            <if test="className != null">#{className},</if>
            <if test="majorId != null">#{majorId},</if>
            <if test="gradeId != null ">#{gradeId},</if>
            <if test="teacherId != null">#{teacherId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="status != null">#{status},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="classCode != null">#{classCode},</if>
         </trim>
    </insert>

    <update id="updateSrsClass" parameterType="SrsClass">
        update srs_class
        <trim prefix="SET" suffixOverrides=",">
            <if test="majorId != null">major_id = #{majorId},</if>
            <if test="className != null">class_name = #{className},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="classCode != null">class_code = #{classCode},</if>
            <if test="teacherId != null">teacher_id = #{teacherId},</if>
            <if test="gradeId != null "> grade_id =#{gradeId},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where class_id = #{classId}
    </update>

    <delete id="deleteSrsClassByClassId" parameterType="Long">
        delete from srs_class where class_id = #{classId}
    </delete>

    <delete id="deleteSrsClassByClassIds" parameterType="String">
        delete from srs_class where class_id in
        <foreach item="classId" collection="array" open="(" separator="," close=")">
            #{classId}
        </foreach>
    </delete>


    <update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" separator=";">
            UPDATE srs_class
            SET major_id = #{item.majorId},
            class_name = #{item.className},
            teacher_id = #{item.teacherId},
            dept_id = #{item.deptId},
            grade_id = #{item.gradeId},
            update_time = Now()
            WHERE class_code = #{item.classCode}
        </foreach>
    </update>

    <update id="updateSrsClassList" parameterType="java.util.List">
        update srs_class
        <trim prefix="set" suffixOverrides=",">
<!--            <trim prefix="class_id=case" suffix="end,">-->
<!--                <foreach collection="studentList" item="i" index="index">-->
<!--                    <if test="i.classId != null ">-->
<!--                        when class_code=#{i.classCode} then #{i.classId}-->
<!--                    </if>-->
<!--                </foreach>-->
<!--            </trim>-->
            <trim prefix="major_id=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.majorId != null ">
                        when class_code=#{i.classCode} then #{i.majorId}
                    </if>
                </foreach>
            </trim>
            <trim prefix="teacher_id=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.teacherId != null ">
                        when class_code=#{i.classCode} then #{i.teacherId}
                    </if>
                </foreach>
            </trim>
            <trim prefix="class_name=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.className != null ">
                        when class_code=#{i.classCode} then #{i.className}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_time=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.updateTime != null ">
                        when class_code=#{i.classCode} then #{i.updateTime}
                    </if>
                </foreach>
            </trim>
        </trim>
        where class_code in
        <foreach collection="studentList" separator="," item="i" index="index" open="(" close=")">
            #{i.classCode}
        </foreach>
    </update>


    <insert id="batchInsert" parameterType="java.util.List">

        INSERT INTO srs_class (major_id, class_name, teacher_id,dept_id,grade_id,class_code,create_time)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.majorId}, #{item.className}, #{item.teacherId}, #{item.deptId}, #{item.gradeId}, #{item.classCode},Now())
        </foreach>;
    </insert>


    <select id="listClassView" resultType="SrsClassInfo" parameterType="CphSearch">
        select a.class_id,a.class_name,c.major_name,d.dept_name,(select count(1) from srs_student where class_id = a.class_id) as stu_count
        from srs_class as a
        left join cph_teacher as b on a.teacher_id = b.teacher_id
        left join srs_majors as c on a.major_id = c.major_id
        left join sys_dept as d on c.college_id = d.dept_id
        <where>
            <if test="tNo != null  and tNo != ''"> and b.employee_id = #{tNo}</if>
            <if test="deptId != null"> and c.college_id = #{deptId}</if>
        </where>
        order by a.class_id desc
    </select>

    <select id="listInSchoolClass" parameterType="CphSearch" resultType="SrsClass">
        SELECT a.*
        from srs_class as a
        left join srs_grade as b on a.grade_id = b.grade_id
        <where>
            b.grade_status in (1,3)
            <if test="majorId != null "> and a.major_id = #{majorId}</if>
        </where>
        order by a.class_name asc
    </select>


</mapper>
