<?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.xinyilu.system.mapper.studentInfo.StudentInfoMapper">

    <resultMap type="studentInfoVO" id="StudentInfoResult">
        <result property="id" column="id"/>
        <result property="kindergartenId" column="kindergarten_id"/>
        <result property="classId" column="class_id"/>
        <result property="nationDictId" column="nation_dict_id"/>
        <result property="studentName" column="student_name"/>
        <result property="studentSex" column="student_sex"/>
        <result property="studentNumber" column="student_number"/>
        <result property="birthDate" column="birth_date"/>
        <result property="cardNumber" column="card_number"/>
        <result property="onceName" column="once_name"/>
        <result property="homeAddress" column="home_address"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="className" column="class_Name"/>
        <result property="userId" column="user_id"/>
    </resultMap>

    <sql id="selectStudentInfoVo">
        select id, kindergarten_id, class_id, nation_dict_id, student_name, student_sex, student_number, birth_date, card_number, once_name, home_address, del_flag, create_by, create_time, update_by, update_time, user_id from student_info
    </sql>

    <select id="selectStudentInfoList" parameterType="studentInfoDTO" resultMap="StudentInfoResult">
        SELECT
        si.id,
        si.kindergarten_id,
        ki.kindergarten_name,
        si.class_id,
        ci.class_name,
        si.nation_dict_id,
        si.student_name,
        si.student_sex,
        si.student_number,
        si.birth_date,
        si.card_number,
        si.once_name,
        si.home_address,
        si.del_flag,
        si.create_by,
        si.create_time,
        si.update_by,
        si.update_time,
        si.user_id
        FROM
        student_info si
        LEFT JOIN class_info ci ON si.class_id = ci.id
        LEFT JOIN kindergarten_info ki ON si.kindergarten_id = ki.id
        <where>
            si.del_flag = 0
            <if test="classId != null ">
                and si.class_id = #{classId}
            </if>
            <if test="nationDictId != null ">
                and si.nation_dict_id = #{nationDictId}
            </if>
            <if test="kindergartenId != null ">
                and si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="studentName != null  and studentName != ''">
                and si.student_name like concat('%', #{studentName}, '%')
            </if>
            <if test="studentSex != null  and studentSex != ''">
                and si.student_sex = #{studentSex}
            </if>
            <if test="studentNumber != null  and studentNumber != ''">
                and si.student_number = #{studentNumber}
            </if>
            <if test="startTime != null and endTime != null">
                and si.birth_date &gt;= #{startTime} and si.birth_date &lt;= #{endTime}
            </if>
            <if test="cardNumber != null  and cardNumber != ''">
                and si.card_number = #{cardNumber}
            </if>
            <if test="onceName != null  and onceName != ''">
                and si.once_name like concat('%', #{onceName}, '%')
            </if>
            <if test="homeAddress != null  and homeAddress != ''">
                and si.home_address = #{homeAddress}
            </if>
            <if test="classIdList != null and classIdList.size() > 0">
                and si.class_id in
                <foreach collection="classIdList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
            </if>
            <if test="idList != null and idList.size() > 0">
                and si.id in
                <foreach collection="idList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="classList != null and classList.size() > 0 and studentList != null and studentList.size() > 0">
                and (si.class_id in
                <foreach collection="classList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
                or si.id in
                <foreach collection="studentList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                )
            </if>
        </where>
        group by si.id
        order by si.create_time desc
    </select>

    <select id="selectStudentInfoCount" parameterType="studentInfoDTO" resultType="int">
        SELECT
        count(1)
        FROM
        student_info si
        LEFT JOIN class_info ci ON si.class_id = ci.id
        LEFT JOIN kindergarten_info ki ON si.kindergarten_id = ki.id
        <where>
            si.del_flag = 0
            <if test="classId != null ">
                and si.class_id = #{classId}
            </if>
            <if test="nationDictId != null ">
                and si.nation_dict_id = #{nationDictId}
            </if>
            <if test="kindergartenId != null ">
                and si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="studentName != null  and studentName != ''">
                and si.student_name like concat('%', #{studentName}, '%')
            </if>
            <if test="studentSex != null  and studentSex != ''">
                and si.student_sex = #{studentSex}
            </if>
            <if test="studentNumber != null  and studentNumber != ''">
                and si.student_number = #{studentNumber}
            </if>
            <if test="startTime != null and endTime != null">
                and si.birth_date &gt;= #{startTime} and si.birth_date &lt;= #{endTime}
            </if>
            <if test="cardNumber != null  and cardNumber != ''">
                and si.card_number = #{cardNumber}
            </if>
            <if test="onceName != null  and onceName != ''">
                and si.once_name like concat('%', #{onceName}, '%')
            </if>
            <if test="homeAddress != null  and homeAddress != ''">
                and si.home_address = #{homeAddress}
            </if>
            <if test="classIdList != null and classIdList.size() > 0">
                and si.class_id in
                <foreach collection="classIdList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
            </if>
            <if test="idList != null and idList.size() > 0">
                and si.id in
                <foreach collection="idList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="classList != null and classList.size() > 0 and studentList != null and studentList.size() > 0">
                and (si.class_id in
                <foreach collection="classList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
                or si.id in
                <foreach collection="studentList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                )
            </if>
        </where>
    </select>

    <select id="selectStudentInfoById" parameterType="Long"
            resultType="studentInfoVO">
        SELECT
        si.id,
        si.kindergarten_id,
        ki.kindergarten_name,
        si.class_id,
        ci.class_name,
        si.nation_dict_id,
        si.student_name,
        si.student_sex,
        si.student_number,
        si.birth_date,
        si.card_number,
        si.once_name,
        si.home_address,
        si.del_flag,
        si.create_by,
        si.create_time,
        si.update_by,
        si.update_time,
        si.user_id
        FROM
        student_info si LEFT JOIN class_info ci ON si.class_id = ci.id
        left join kindergarten_info ki on ki.id = si.kindergarten_id
        where si.id = #{id} and si.del_flag = 0
    </select>

    <insert id="insertStudentInfo" parameterType="StudentInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into student_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="kindergartenId != null">kindergarten_id,
            </if>
            <if test="classId != null">class_id,
            </if>
            <if test="nationDictId != null">nation_dict_id,
            </if>
            <if test="studentName != null">student_name,
            </if>
            <if test="studentSex != null">student_sex,
            </if>
            <if test="studentNumber != null">student_number,
            </if>
            <if test="birthDate != null">birth_date,
            </if>
            <if test="cardNumber != null">card_number,
            </if>
            <if test="onceName != null">once_name,
            </if>
            <if test="homeAddress != null">home_address,
            </if>
            <if test="delFlag != null">del_flag,
            </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="userId != null">user_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="kindergartenId != null">#{kindergartenId},
            </if>
            <if test="classId != null">#{classId},
            </if>
            <if test="nationDictId != null">#{nationDictId},
            </if>
            <if test="studentName != null">#{studentName},
            </if>
            <if test="studentSex != null">#{studentSex},
            </if>
            <if test="studentNumber != null">#{studentNumber},
            </if>
            <if test="birthDate != null">#{birthDate},
            </if>
            <if test="cardNumber != null">#{cardNumber},
            </if>
            <if test="onceName != null">#{onceName},
            </if>
            <if test="homeAddress != null">#{homeAddress},
            </if>
            <if test="delFlag != null">#{delFlag},
            </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="userId != null">#{userId},
            </if>
        </trim>
    </insert>

    <update id="updateStudentInfo" parameterType="StudentInfo">
        update student_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="kindergartenId != null">kindergarten_id =
                #{kindergartenId},
            </if>
            <if test="classId != null">class_id =
                #{classId},
            </if>
            <if test="nationDictId != null">nation_dict_id =
                #{nationDictId},
            </if>
            <if test="studentName != null">student_name =
                #{studentName},
            </if>
            <if test="studentSex != null">student_sex =
                #{studentSex},
            </if>
            student_number = #{studentNumber},
            <if test="birthDate != null">birth_date =
                #{birthDate},
            </if>
            <if test="cardNumber != null">card_number =
                #{cardNumber},
            </if>
            <if test="onceName != null">once_name =
                #{onceName},
            </if>
            home_address = #{homeAddress},
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </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>
            <if test="userId != null">user_id =
                #{userId},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteStudentInfoById" parameterType="Long">
        delete from student_info where id = #{id}
    </delete>

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

    <select id="getParentInfoList" parameterType="java.lang.Long" resultType="parentInfo">
        SELECT
            pi.id,
            pi.parent_name,
            pi.parent_sex,
            pi.phone
        FROM
            parent_info pi
            INNER JOIN parent_student_info psi ON pi.id = psi.parent_id
            where psi.student_id = #{studentId} and psi.del_flag = 0
    </select>

    <update id="updateStudentInfoByIds" parameterType="long">
        update student_info set del_flag = 1, update_time = now() where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="getClassIdByArea" parameterType="long" resultType="long">
        SELECT
            ci.id
        FROM
            kindergarten_info ki
            LEFT JOIN class_info ci ON ki.id = ci.kindergarten_id
            LEFT JOIN organize_info oi ON ki.organize_id = oi.id
            LEFT JOIN sys_area sa ON oi.area_id = sa.id
        WHERE
            sa.id = #{areaId}
            AND ci.del_flag = 0
            group by ci.id
    </select>

    <select id="getClassIdByPrincipalId" parameterType="long" resultType="long">
        SELECT
            ci.id
        FROM
            class_info ci
            LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        WHERE
            ki.principal_id = #{principalId} and ci.del_flag = 0
            group by ci.id
    </select>

    <select id="getClassIdByTeacherId" parameterType="long" resultType="long">
        SELECT
            cti.class_id
        FROM
            class_teacher_info cti
            LEFT JOIN teacher_info ti ON cti.teacher_id = ti.id
        WHERE
            ti.user_id = #{userId}
            AND cti.del_flag = 0
        GROUP BY
            cti.class_id
    </select>

    <select id="getStudentIdByParent" parameterType="String" resultType="long">
        SELECT
            psi.student_id
        FROM
            parent_student_info psi
            INNER JOIN parent_info pi ON psi.parent_id = pi.id
        where
            pi.phone = #{phone} and psi.del_flag = 0
    </select>

    <select id="getStudentInfoById" parameterType="long"
            resultType="com.xinyilu.system.domain.vo.physiqueAnalysis.PhysiqueAnalysisVO">
        SELECT
        si.id studentId,
        si.student_name,
        si.student_sex,
        si.birth_date,
        si.student_number,
        ci.class_name,
        ki.kindergarten_name,
        ki.kindergarten_address,
        sd.dict_label nationName
        FROM
        student_info si
        LEFT JOIN sys_dict_data sd ON si.nation_dict_id = sd.dict_code
        LEFT JOIN class_info ci ON si.class_id = ci.id
        LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        WHERE
        si.id = #{studentId} AND si.del_flag = 0
    </select>

    <select id="getFirstItemCategory"
            resultType="com.xinyilu.system.domain.vo.physiqueAnalysis.PhysiqueCategoryVO">
        SELECT
            tsi.test_item_id,
		    ti.item_name,
            tc.parent_id id,
            (select category_name FROM test_item_category where id = tc.parent_id) categoryName,
            tc.category_name_abbreviate categoryNameAbbreviate,
            IFNULL(COUNT(tsi.student_id ), 0 ) studentCount,
            IFNULL(tsi.item_fraction, 0) categoryScore
        FROM
            test_item_score tsi
            INNER JOIN test_items ti ON tsi.test_item_id = ti.id
            INNER JOIN test_category_item tci ON ti.id = tci.item_id
            INNER JOIN test_item_category tc ON tc.id = tci.item_category_id
            WHERE tc.parent_id in(select id FROM test_item_category where parent_id = #{parentId} AND del_flag = 0)
            AND tsi.student_id = #{studentId} AND tsi.batch_code = #{batchCode}
            AND tsi.del_flag = 0 AND ti.item_code &lt;&gt; #{itemCode}
            GROUP BY tc.parent_id, tsi.test_item_id;
    </select>

    <select id="getItemCategoryScore" resultType="com.xinyilu.system.domain.vo.physiqueAnalysis.PhysiqueCategoryVO">
        SELECT
            tc.id,
            tc.category_name categoryName,
            tc.category_name_abbreviate categoryNameAbbreviate,
            IFNULL(SUM(tsi.item_fraction ), 0) categoryScore
        FROM
            test_item_score tsi
            INNER JOIN test_items ti ON tsi.test_item_id = ti.id
            INNER JOIN test_category_item tci ON ti.id = tci.item_id
            INNER JOIN test_item_category tc ON tc.id = tci.item_category_id
        WHERE
            tsi.student_id = #{studentId}
            AND tsi.batch_code = #{batchCode}
            AND tsi.del_flag = 0
            AND ti.item_code &lt;&gt; #{itemCode}
        GROUP BY
            tc.id
    </select>

    <select id="getTestItemLevelByCategoryId" parameterType="long" resultType="testItemLevel">
        SELECT
        tl.item_category_id,
        tl.level_name,
        tl.level_min_score,
        tl.level_max_score
        FROM
        test_item_level tl
        <where>
            <if test="categoryIdList != null and categoryIdList.size() > 0">
                tl.item_category_id in
                <foreach collection="categoryIdList" item="categoryId" open="(" separator="," close=")">
                    #{categoryId}
                </foreach>
            </if>
            and tl.del_flag = 0
        </where>
    </select>

    <select id="getStudentCountById" parameterType="long" resultType="int">
        SELECT
            count(1) AS studentCount
        FROM
            student_info si
            JOIN class_info ci ON si.class_id = ci.id
            JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        WHERE
            ki.id = (SELECT kindergarten_id FROM student_info WHERE id = #{studentId})
    </select>

    <select id="getStudentItemScore" resultType="com.xinyilu.system.domain.vo.childrenRanking.ChildrenRankingVO">
        SELECT
            tis.student_id,
            IFNULL( SUM( tis.item_fraction ), 0 ) item_score
        FROM
            test_item_score tis
            INNER JOIN test_items ti ON tis.test_item_id = ti.id
            INNER JOIN student_info si ON tis.student_id = si.id
        WHERE
            si.kindergarten_id = ( SELECT kindergarten_id FROM student_info WHERE id = #{studentId} )
            AND tis.batch_code = #{batchCode}
            AND tis.del_flag = 0
        GROUP BY
            tis.student_id
    </select>

    <select id="getStudentItemScoreAndLevel" resultType="com.xinyilu.system.domain.vo.TestItemLevelScoreVO">
        SELECT
            ti.item_name,
            ts.test_item_id item_id,
            ts.item_fraction item_score,
            tl.level_min_score,
            tl.level_max_score
        FROM
            test_item_score ts
            LEFT JOIN test_items ti ON ts.test_item_id = ti.id
            LEFT JOIN test_item_level tl ON ti.id = tl.item_id
            where ts.student_id = #{studentId} and ts.batch_code = #{batchCode} AND ts.del_flag = 0
    </select>

    <select id="getItemScoreList" resultType="com.xinyilu.system.domain.vo.childrenRanking.ItemScoreInfoVO">
        SELECT
			tis.test_item_id itemId,
			IFNULL( SUM( tis.item_fraction ), 0 ) itemScore
        FROM
            test_item_score tis
            INNER JOIN test_items ti ON tis.test_item_id = ti.id
            INNER JOIN student_info si ON tis.student_id = si.id
        WHERE
            si.kindergarten_id = ( SELECT kindergarten_id FROM student_info WHERE id = #{studentId})
            AND tis.batch_code = #{batchCode}
            AND tis.del_flag = 0
        GROUP BY
                tis.test_item_id
    </select>

    <select id="getAreaScoreByStudentId" parameterType="long"
            resultType="com.xinyilu.system.domain.vo.childrenRanking.ItemScoreInfoVO">
        SELECT
            ti.item_id itemId,
            IFNULL(sum(ti.item_avg_score), 0) cityAvgScore
        FROM
            test_item_city_score ti
        WHERE
            ti.area_id = (
            SELECT
                sa.id
            FROM
                sys_area sa
                LEFT JOIN organize_info oi ON sa.id = oi.area_id
                LEFT JOIN kindergarten_info ki ON oi.id = ki.organize_id
                LEFT JOIN student_info si ON ki.id = si.kindergarten_id
            WHERE
                si.id = #{studentId}
            AND sa.del_flag = 0
            ) AND ti.del_flag = 0
      group by ti.item_id
    </select>

    <select id="getStudentCardNumberInfo" resultType="integer">
        SELECT
        COUNT( 1 )
        FROM
        student_info
        <where>
            <if test="cardNumber != null  and cardNumber != ''">
                and card_number = #{cardNumber}
            </if>
            and del_flag = 0
        </where>
    </select>
    <insert id="insertStudentInfoList" parameterType="java.util.Collections">
        INSERT INTO
        student_info(kindergarten_id, class_id, nation_dict_id,
        student_name,student_sex,student_number, birth_date,card_number,once_name,home_address,user_id,del_flag,
        create_by, create_time, update_by, update_time)
        VALUES
        <foreach collection="list" item="productCategory" index="index"
                 separator=",">
            (
            #{productCategory.kindergartenId},
            #{productCategory.classId},
            #{productCategory.nationDictId},
            #{productCategory.studentName},
            #{productCategory.studentSex},
            #{productCategory.studentNumber},
            #{productCategory.birthDate},
            #{productCategory.cardNumber},
            #{productCategory.onceName},
            #{productCategory.homeAddress},
            #{productCategory.userId},
            #{productCategory.delFlag},
            #{productCategory.createBy},
            #{productCategory.createTime},
            #{productCategory.updateBy},
            #{productCategory.updateTime}
            )
        </foreach>
    </insert>

    <select id="selectStudentInfoListByClassId" parameterType="Long"
            resultType="StudentInforImportlVO">
        SELECT si.id,
               si.kindergarten_id,
               ki.kindergarten_name,
               si.class_id,
               ci.class_name,
               si.nation_dict_id,
               si.student_name,
               si.student_sex,
               si.student_number,
               si.birth_date,
               si.card_number,
               si.once_name,
               si.home_address,
               si.del_flag,
               si.create_by,
               si.create_time,
               si.update_by,
               si.update_time,
               si.user_id
        FROM student_info si
                 LEFT JOIN class_info ci ON si.class_id = ci.id
                 left join kindergarten_info ki on ki.id = si.kindergarten_id
        where si.class_id = #{id}
          and si.del_flag = 0
    </select>

    <select id="getKindergartenInfoList" resultType="long">
        SELECT
            ki.id
        FROM
            kindergarten_info ki
            LEFT JOIN organize_info oi ON oi.id = ki.organize_id
        WHERE
            oi.area_id =(
            SELECT
                oi.area_id
            FROM
                student_info si
                LEFT JOIN kindergarten_info ki ON ki.id = si.kindergarten_id
                LEFT JOIN organize_info oi ON oi.id = ki.organize_id
                LEFT JOIN sys_area sa ON oi.area_id = sa.id
            WHERE
                si.id = #{studentId}
            AND sa.`level` = #{level}
            )
    </select>
    <select id="getStudentItemScoreBykindList"
            resultType="com.xinyilu.system.domain.vo.childrenRanking.ChildrenRankingVO">
        SELECT
        tis.student_id,
        IFNULL( SUM( tis.item_fraction ), 0 ) item_score
        FROM
        test_item_score tis
        INNER JOIN test_items ti ON tis.test_item_id = ti.id
        INNER JOIN student_info si ON tis.student_id = si.id
        <where>
            tis.del_flag = 0
            <if test="kindList != null and kindList.size() > 0">
                and si.kindergarten_id in
                <foreach collection="kindList" item="categoryId" open="(" separator="," close=")">
                    #{categoryId}
                </foreach>
            </if>
            AND tis.batch_code = #{batchCode}
        </where>
        GROUP BY
        tis.student_id
    </select>


    <select id="getCountBykindList" resultType="java.math.BigDecimal">
        SELECT count(1) FROM student_info
        <where>
            <if test="kindList != null and kindList.size() > 0">
                kindergarten_id IN
                <foreach collection="kindList" item="categoryId" open="(" separator="," close=")">
                    #{categoryId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getCityCode" parameterType="long" resultType="string">
	    SELECT
		    IFNULL(sa.`code`, '') code
	    FROM
		    student_info si
		LEFT JOIN kindergarten_info ki ON ki.id = si.kindergarten_id
		LEFT JOIN organize_info oi ON oi.id = ki.organize_id
		LEFT JOIN sys_area sa ON sa.id = oi.area_id
	    WHERE
		    si.id = #{studentId}
		AND si.del_flag = 0
    </select>

    <select id="getCityStudentCountById" parameterType="string" resultType="long">
        SELECT
	        COUNT( 1 ) AS studentCount
        FROM
	        student_info si
	    LEFT JOIN kindergarten_info ki ON si.kindergarten_id = ki.id
	    LEFT JOIN organize_info oi ON oi.id = ki.organize_id
	    LEFT JOIN sys_area sa ON sa.id = oi.area_id
        WHERE
	        sa.`code` = #{code}
	    AND si.del_flag = 0
    </select>

    <select id="getScoreByStudentId"
            resultType="com.xinyilu.system.domain.vo.childrenRanking.ItemScoreInfoVO">
        SELECT
        tis.test_item_id itemId,
        IFNULL( SUM( tis.item_fraction ), 0 ) itemScore
        FROM
        test_item_score tis
        LEFT JOIN student_info si ON si.id = tis.student_id
        LEFT JOIN kindergarten_info ki ON ki.id = si.kindergarten_id
        <where>
            <if test="batchCode != null and batchCode != ''">
                tis.batch_code = #{batchCode}
            </if>
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ki.id IN
                <foreach collection="kindergartenIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        GROUP BY tis.test_item_id
    </select>

    <select id="getSysAreaByStudentId" parameterType="long" resultType="sysArea">
        SELECT
            sa.id,
            sa.parent_id,
            sa.`level`
        FROM
            student_info si
            LEFT JOIN kindergarten_info ki ON ki.id = si.kindergarten_id
            LEFT JOIN organize_info oi ON oi.id = ki.organize_id
            LEFT JOIN sys_area sa ON oi.area_id = sa.id
        WHERE
            si.id = #{studentId}
    </select>

    <select id="getStudentAndKindergartenInfoByAreaIds" parameterType="long"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.StudentAndKindergartenVO">
        SELECT
        ki.id kindergarten_id,
        si.id student_id
        FROM
        kindergarten_info ki
        LEFT JOIN student_info si ON si.kindergarten_id = ki.id
        LEFT JOIN organize_info oi ON oi.id = ki.organize_id
        <where>
            <if test="areaIdList != null and areaIdList.size() > 0">
                oi.area_id IN
                <foreach collection="areaIdList" item="areaId" open="(" separator="," close=")">
                    #{areaId}
                </foreach>
            </if>
        </where>
        GROUP BY ki.id, si.id
    </select>

    <select id="getClassIdByKindergartenIdList" parameterType="long" resultType="long">
        SELECT
        ci.id
        FROM
        class_info ci
        LEFT JOIN kindergarten_info ki ON ki.id = ci.kindergarten_id
        <where>
            ci.del_flag = 0
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ci.kindergarten_id in
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
        </where>
        group by ci.id
    </select>

    <select id="selectStudentByKindergartenId" resultType="com.xinyilu.system.domain.vo.scoreExport.ScoreExportVO">
        select
        id studentId,
        kindergarten_id,
        class_id,
        student_name,
        student_sex sex,
        birth_date
        from student_info
        <where>
            del_flag = 0
            <if test="studentId != null ">
                and id = #{studentId}
            </if>
            <if test="kindergartenId != null ">
                and kindergarten_id = #{kindergartenId}
            </if>
            <if test="classId != null ">
                and class_id = #{classId}
            </if>
        </where>
    </select>
</mapper>
