<?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.GraduateTextMapper">
    <resultMap id="SaveStudentResult" type="com.srs.comprehensive.domain.SaveStudent">
        <result property="id" column="id"/>
        <result property="studentName" column="student_name"/>
        <result property="studentCode" column="student_code"/>
        <result property="studentCollege" column="student_college"/>
        <result property="studentGrade" column="student_grade"/>
        <result property="studentClass" column="student_class"/>
        <result property="sex" column="sex"/>
        <result property="nation" column="nation"/>
        <result property="birthData" column="birth_data"/>

        <!-- 嵌套映射 List<GraduateVo> -->
        <collection property="graduateVoList" ofType="com.srs.comprehensive.domain.Vo.GraduateVo">
            <result property="politics" column="politics"/>
            <result property="moralCharacter" column="moral_character"/>
            <result property="observeDiscipline" column="observe_discipline"/>
            <result property="attitude" column="attitude"/>
            <result property="attendanceSituation" column="attendance_situation"/>
            <result property="positionHeld" column="position_held"/>
            <result property="workingAttitude" column="working_attitude"/>
            <result property="workingAbility" column="working_ability"/>
            <result property="ldeologicalPolitical" column="ldeological_political"/>
            <result property="scientificQuality" column="scientific_quality"/>
            <result property="physicalQuality" column="physical_quality"/>
            <result property="totalPoints" column="total_points"/>
            <result property="counselorComments" column="counselor_comments"/>
            <result property="counselorName" column="counselor_name"/>
            <result property="fillData" column="fill_data"/>
            <result property="collegeOpinion" column="college_opinion"/>
            <result property="stuYear" column="stu_year"/>
            <result property="biaoJit" column="biao_jit"/>
        </collection>
    </resultMap>

    <select id="selectGraduateList" parameterType="com.srs.comprehensive.domain.GraduateStudentNews" resultType="com.srs.comprehensive.domain.GraduateStudentNews">
        select id,student_name, student_code, student_class, create_time, update_time from srs_graduate_studentinfo
        <where>
            <if test="studentCode != null and studentCode != ''">
                student_code = #{studentCode}
            </if>
            <if test="studentClass != null and studentClass != ''">
                AND student_class = #{studentClass}
            </if>
        </where>
    </select>

    <select id="selectGraduateClassData" parameterType="Integer" resultType="String">
        SELECT class_name AS studentClass
        FROM srs_class
        <where>
            1=1 <!-- 关键点 -->
            <if test="classId != null">
                AND class_id = #{classId}
            </if>
        </where>
        ORDER BY RAND()
        LIMIT 1
    </select>

    <select id="selectStuYearByStudentCode" parameterType="String" resultType="Integer">
        select b.stu_year from srs_graduate_studentinfo a left join srs_graduate_student b
            on a.id = b.graduate_id where a.student_code = #{studentCode};
    </select>

    <select id="selectByStudentById" parameterType="Long" resultMap="SaveStudentResult">
        SELECT
            gsi.id,
            gsi.student_name,
            gsi.student_code,
            gsi.student_college,
            gsi.student_grade,
            gsi.student_class,
            gsi.sex,
            gsi.nation,
            gsi.birth_data,

            gs.politics,
            gs.moral_character,
            gs.observe_discipline,
            gs.attitude,
            gs.attendance_situation,
            gs.position_held,
            gs.working_attitude,
            gs.working_ability,
            gs.ldeological_political,
            gs.scientific_quality,
            gs.physical_quality,
            gs.total_points,
            gs.counselor_comments,
            gs.counselor_name,
            gs.fill_data,
            gs.college_opinion,
            gs.stu_year,
            gs.biao_jit
        FROM srs_graduate_studentinfo gsi
                 LEFT JOIN srs_graduate_student gs ON gsi.id = gs.graduate_id
        WHERE gsi.id = #{id}
        ORDER BY gs.biao_jit DESC
    </select>

    <select id="graduateByIdsList" parameterType="Long" resultMap="SaveStudentResult">
        SELECT
            gsi.id,
            gsi.student_name,
            gsi.student_code,
            gsi.student_college,
            gsi.student_grade,
            gsi.student_class,
            gsi.sex,
            gsi.nation,
            gsi.birth_data,

            gs.politics,
            gs.moral_character,
            gs.observe_discipline,
            gs.attitude,
            gs.attitude,
            gs.attendance_situation,
            gs.position_held,
            gs.working_attitude,
            gs.working_ability,
            gs.ldeological_political,
            gs.scientific_quality,
            gs.physical_quality,
            gs.total_points,
            gs.counselor_comments,
            gs.counselor_name,
            gs.fill_data,
            gs.college_opinion,
            gs.stu_year,
            gs.biao_jit
        FROM srs_graduate_studentinfo gsi
             LEFT JOIN srs_graduate_student gs ON gsi.id = gs.graduate_id
        WHERE gsi.id IN
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
        ORDER BY gs.biao_jit DESC
    </select>

    <insert id="inserts" parameterType="com.srs.comprehensive.domain.GraduateStudentNews" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO srs_graduate_studentinfo (
            student_name,
            student_code,
            student_college,
            student_grade,
            student_class,
            sex,
            nation,
            birth_data)
        VALUES (
            #{studentName},
            #{studentCode},
            #{studentCollege},
            #{studentGrade},
            #{studentClass},
            #{sex},
            #{nation},
            #{birthData})
    </insert>

    <insert id="insertGraduateStudent" parameterType="com.srs.comprehensive.domain.GraduateStudentNews">
        INSERT INTO srs_graduate_student (
            politics,
            moral_character,
            observe_discipline,
            attitude,
            attendance_situation,
            position_held,
            working_attitude,
            working_ability,
            ldeological_political,
            scientific_quality,
            physical_quality,
            total_points,
            counselor_comments,
            counselor_name,
            fill_data,
            college_opinion,
            stu_year,
            graduate_id,
            biao_jit)
        VALUES (
            #{politics},
            #{moralCharacter},
            #{observeDiscipline},
            #{attitude},
            #{attendanceSituation},
            #{positionHeld},
            #{workingAttitude},
            #{workingAbility},
            #{ldeologicalPolitical},
            #{scientificQuality},
            #{physicalQuality},
            #{totalPoints},
            #{counselorComments},
            #{counselorName},
            #{fillData},
            #{collegeOpinion},
            #{stuYear},
            #{graduateId},
            #{biaoJit})
    </insert>

    <update id="updateGraduateStudentByGraduateId" parameterType="com.srs.comprehensive.domain.GraduateStudentNews">
        UPDATE srs_graduate_student
        <set>
            <if test="politics != null">politics = #{politics},</if>
            <if test="moralCharacter != null">moral_character = #{moralCharacter},</if>
            <if test="observeDiscipline != null">observe_discipline = #{observeDiscipline},</if>
            <if test="attitude != null">attitude = #{attitude},</if>
            <if test="attendanceSituation != null">attendance_situation = #{attendanceSituation},</if>
            <if test="positionHeld != null">position_held = #{positionHeld},</if>
            <if test="workingAttitude != null">working_attitude = #{workingAttitude},</if>
            <if test="workingAbility != null">working_ability = #{workingAbility},</if>
            <if test="ldeologicalPolitical != null">ldeological_political = #{ldeologicalPolitical},</if>
            <if test="scientificQuality != null">scientific_quality = #{scientificQuality},</if>
            <if test="physicalQuality != null">physical_quality = #{physicalQuality},</if>
            <if test="totalPoints != null">total_points = #{totalPoints},</if>
            <if test="counselorComments != null">counselor_comments = #{counselorComments},</if>
            <if test="counselorName != null">counselor_name = #{counselorName},</if>
            <if test="fillData != null">fill_data = #{fillData},</if>
            <if test="collegeOpinion != null">college_opinion = #{collegeOpinion},</if>
            <if test="stuYear != null">stu_year = #{stuYear},</if>
            <if test="biaoJit != null">biao_jit = #{biaoJit},</if>
        </set>
        WHERE graduate_id = #{graduateId} and biao_jit = #{biaoJit}
    </update>

</mapper>