<?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.diagnostic.mapper.DiaStudentInfoMapper">

    <resultMap type="DiaStudentInfo" id="DiaStudentInfoResult">
        <result property="id"    column="id"    />
        <result property="stuNo"    column="stu_no"    />
        <result property="collegeId"    column="college_id"    />
<!--        <result property="collegeName"    column="college_name"    />-->
        <result property="collegeName"    column="collegeName"    />
        <result property="majorId"    column="major_id"    />
        <result property="majorName"    column="major_name"    />
        <result property="userId"    column="user_id"    />
<!--        <result property="studentName"    column="student_name"    />-->
        <result property="studentName"    column="studentName"    />
        <result property="semesterId"    column="semester_id"    />
<!--        <result property="semesterName"    column="semester_name"    />-->
        <result property="semesterName"    column="semest_name"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="reportId"    column="report_id"    />
    </resultMap>

    <sql id="selectDiaStudentInfoVo">
        select id, stu_no, college_id, college_name, major_id, major_name, user_id, student_name, semester_id, semester_name, create_time, update_time from dia_student_info
    </sql>

    <select id="selectDiaStudentInfoList" parameterType="DiaStudentInfo" resultMap="DiaStudentInfoResult">
        SELECT
            s.*,
            u.nick_name AS studentName,
            p.major_name AS major_name,
            c.dept_name AS collegeName,
            r.stu_year_name AS semest_name
        FROM dia_student_info s
                 LEFT JOIN sys_user u ON s.user_id = u.user_id
                 LEFT JOIN srs_majors p ON s.college_id = p.college_id
                 LEFT JOIN sys_dept c ON s.college_id = c.dept_id
                 LEFT JOIN dia_report_release r ON s.report_id = r.relId

    </select>

    <select id="selectDiaStudentInfolistid" parameterType="Long" resultMap="DiaStudentInfoResult">
        SELECT
            s.*,
            u.nick_name AS studentName,
            p.major_name AS major_name,
            c.dept_name AS collegeName,
            r.stu_year_name AS semest_name
        FROM dia_student_info s
                 LEFT JOIN sys_user u ON s.user_id = u.user_id
                 LEFT JOIN srs_majors p ON s.college_id = p.college_id
                 LEFT JOIN sys_dept c ON s.college_id = c.dept_id
                 LEFT JOIN dia_report_release r ON s.report_id = r.relId
        where stu_no = #{userId}
    </select>

    <select id="selectDiaStudentInfoById" parameterType="Long" resultMap="DiaStudentInfoResult">
        <include refid="selectDiaStudentInfoVo"/>
        where id = #{id}
    </select>

    <insert id="insertDiaStudentInfo" parameterType="DiaStudentInfo" useGeneratedKeys="true" keyProperty="id">
        insert into dia_student_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="stuNo != null and stuNo != ''">stu_no,</if>
            <if test="collegeId != null">college_id,</if>
            <if test="collegeName != null and collegeName != ''">college_name,</if>
            <if test="majorId != null">major_id,</if>
            <if test="majorName != null and majorName != ''">major_name,</if>
            <if test="userId != null">user_id,</if>
            <if test="studentName != null and studentName != ''">student_name,</if>
            <if test="semesterId != null">semester_id,</if>
            <if test="semesterName != null and semesterName != ''">semester_name,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="reportId != null">report_id,</if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="stuNo != null and stuNo != ''">#{stuNo},</if>
            <if test="collegeId != null">#{collegeId},</if>
            <if test="collegeName != null and collegeName != ''">#{collegeName},</if>
            <if test="majorId != null">#{majorId},</if>
            <if test="majorName != null and majorName != ''">#{majorName},</if>
            <if test="userId != null">#{userId},</if>
            <if test="studentName != null and studentName != ''">#{studentName},</if>
            <if test="semesterId != null">#{semesterId},</if>
            <if test="semesterName != null and semesterName != ''">#{semesterName},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="reportId != null">#{reportId},</if>
        </trim>

        ON DUPLICATE KEY UPDATE
        <trim prefix="" suffixOverrides=",">
            update_time = NOW(),
        </trim>
    </insert>

    <update id="updateDiaStudentInfo" parameterType="DiaStudentInfo">
        UPDATE dia_student_info s
            LEFT JOIN dia_report_release r ON s.semester_id = r.term_id
            SET s.semester_name = r.stu_year_name
        WHERE r.term_id IS NOT NULL;
    </update>

    <delete id="deleteDiaStudentInfoById" parameterType="Long">
        delete from dia_student_info where id = #{id}
    </delete>

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