<?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.SrsMajorsMapper">

    <resultMap type="SrsMajors" id="SrsMajorsResult">
        <result property="majorId"    column="major_id"    />
        <result property="majorName"    column="major_name"    />
        <result property="majorCode"    column="major_code"    />
        <result property="collegeId"    column="college_id"    />
        <result property="majorDescription"    column="major_description"    />
        <result property="status"    column="status"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="majorTypeName"    column="major_type_name"    />
        <association property="dept"    column="dept_id" javaType="SysDept" resultMap="deptResult" />
    </resultMap>
    <resultMap id="deptResult" type="SysDept">
        <id     property="deptId"    column="dept_id"     />
        <result property="parentId"  column="parent_id"   />
        <result property="deptName"  column="dept_name"   />
        <result property="ancestors" column="ancestors"   />
        <result property="orderNum"  column="order_num"   />
        <result property="leader"    column="leader"      />
        <result property="status"    column="dept_status" />
    </resultMap>


    <sql id="selectSrsMajorsVo">
        select major_id, major_name,major_code, college_id, major_description, status, create_time, create_by, update_time, update_by, del_flag from srs_majors
    </sql>

    <sql id="selectSrsMajorsJoin">
        select m.major_id,m.major_type_name,m.major_code, m.major_name, m.college_id, m.major_description, m.status,
               m.create_time,m.create_by, m.update_time, m.update_by, m.del_flag,
               d.*

        from srs_majors m
        left join sys_dept d on d.dept_id = m.college_id
    </sql>

    <select id="selectSrsMajorsList" parameterType="SrsMajors" resultMap="SrsMajorsResult">
        <include refid="selectSrsMajorsJoin"/>
        <where>
            <if test="majorTypeName != null  and majorTypeName != ''"> and major_type_name like concat('%', #{majorTypeName}, '%')</if>
            <if test="majorName != null  and majorName != ''"> and major_name like concat('%', #{majorName}, '%')</if>
            <if test="collegeId != null "> and college_id = #{collegeId}</if>
            <if test="majorDescription != null  and majorDescription != ''"> and major_description = #{majorDescription}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
        </where>
    </select>

    <select id="selectSrsMajorsByMajorId" parameterType="Long" resultMap="SrsMajorsResult">
        <include refid="selectSrsMajorsVo"/>
        where major_id = #{majorId}
    </select>
    <select id="selectSrsMajorsByMajorCode" parameterType="String" resultMap="SrsMajorsResult">
        <include refid="selectSrsMajorsVo"/>
        where major_code = #{majorCode}
    </select>
    <select id="selectSrsMajorsNameList" resultMap="SrsMajorsResult">
        select major_id,college_id,major_name from srs_majors
    </select>
    <select id="selectSrsmajorsList" resultMap="SrsMajorsResult">
        select * from srs_majors
    </select>

    <select id="selectMajorId" resultType="long">
        select major_id from srs_majors where major_code=#{zydm}
    </select>
    <select id="selectXYID" resultType="java.lang.Long">
        select dept_id from sys_dept where dept_code=#{xydm}
    </select>
    <select id="getMajorIdByName" resultType="java.lang.Long" parameterType="String">
        select major_id from srs_majors where major_name=#{name};
    </select>

    <insert id="insertSrsMajors" parameterType="SrsMajors">
        insert into srs_majors
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="majorId != null">major_id,</if>
            <if test="majorTypeName != null and majorTypeName != ''">major_type_name,</if>
            <if test="majorName != null and majorName != ''">major_name,</if>
            <if test="majorCode != null and majorCode != ''">major_code,</if>
            <if test="collegeId != null">college_id,</if>
            <if test="majorDescription != null">major_description,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="delFlag != null">del_flag,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="majorId != null">#{majorId},</if>
            <if test="majorTypeName != null and majorTypeName != ''">#{majorTypeName},</if>
            <if test="majorName != null and majorName != ''">#{majorName},</if>
            <if test="majorCode != null and majorCode != ''">#{majorCode},</if>
            <if test="collegeId != null">#{collegeId},</if>
            <if test="majorDescription != null">#{majorDescription},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="delFlag != null">#{delFlag},</if>
         </trim>
    </insert>

    <update id="updateSrsMajors" parameterType="SrsMajors">
        update srs_majors
        <trim prefix="SET" suffixOverrides=",">
            <if test="majorTypeName != null and majorTypeName != ''">major_type_name = #{majorTypeName},</if>
            <if test="majorName != null and majorName != ''">major_name = #{majorName},</if>
            <if test="majorCode != null and majorCode != ''">major_code = #{majorCode},</if>
            <if test="collegeId != null">college_id = #{collegeId},</if>
            <if test="majorDescription != null">major_description = #{majorDescription},</if>
            <if test="status != null and status != ''">status = #{status},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
        </trim>
        where major_id = #{majorId}
    </update>
    <update id="updateSrsStudentList" parameterType="java.util.List">
        update srs_majors
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="major_name=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.majorName != null ">
                        when major_code=#{i.majorCode} then #{i.majorName}
                    </if>
                </foreach>
            </trim>
            <trim prefix="college_id=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.collegeId != null ">
                        when major_code=#{i.majorCode} then #{i.collegeId}
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_time=case" suffix="end,">
                <foreach collection="studentList" item="i" index="index">
                    <if test="i.updateTime != null ">
                        when major_code=#{i.majorCode} then #{i.updateTime}
                    </if>
                </foreach>
            </trim>
        </trim>
        where major_code in
        <foreach collection="studentList" separator="," item="i" index="index" open="(" close=")">
            #{i.majorCode}
        </foreach>
    </update>

    <delete id="deleteSrsMajorsByMajorId" parameterType="Long">
        delete from srs_majors where major_id = #{majorId}
    </delete>

    <delete id="deleteSrsMajorsByMajorIds" parameterType="String">
        delete from srs_majors where major_id in
        <foreach item="majorId" collection="array" open="(" separator="," close=")">
            #{majorId}
        </foreach>
    </delete>
</mapper>
