<?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.kindergarteninfo.KindergartenInfoMapper">

    <resultMap type="kindergartenInfoDTO" id="KindergartenInfoResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="principalId" column="principal_id"/>
        <result property="organizeId" column="organize_id"/>
        <result property="kindergartenName" column="kindergarten_name"/>
        <result property="kindergartenAddress" column="kindergarten_address"/>
        <result property="kindergartenType" column="kindergarten_type"/>
        <result property="phone" column="phone"/>
        <result property="contacts" column="contacts"/>
        <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="organizeName" column="organize_name"/>
        <result property="principalName" column="principalName"/>
    </resultMap>

    <sql id="selectKindergartenInfoVo">
        select id,
               user_id,
               principal_id,
               organize_id,
               kindergarten_name,
               kindergarten_address,
               kindergarten_type,
               phone,
               contacts,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time
        from kindergarten_info
    </sql>

    <select id="selectKindergartenInfoList" parameterType="KindergartenInfoDTO" resultMap="KindergartenInfoResult">
        SELECT
        ki.id,
        ki.user_id,
        ki.principal_id,
        u.user_name principalName,
        ki.organize_id,
        oi.organize_name,
        ki.kindergarten_name,
        ki.kindergarten_address,
        ki.kindergarten_type,
        ki.phone,
        ki.contacts,
        ki.del_flag,
        ki.create_by,
        ki.create_time,
        ki.update_by,
        ki.update_time
        FROM
        kindergarten_info ki
        LEFT JOIN organize_info oi ON ki.organize_id = oi.id
        LEFT JOIN sys_user u ON ki.principal_id = u.user_id
        <where>
            <if test="id != null">
                and ki.id = #{id}
            </if>
            <if test="organizeId != null ">
                and ki.organize_id = #{organizeId}
            </if>
            <if test="userId != null ">
                and ki.user_id = #{userId}
            </if>
            <if test="principalId != null ">
                and ki.principal_id = #{principalId}
            </if>
            <if test="kindergartenName != null  and kindergartenName != ''">
                and ki.kindergarten_name like concat('%', #{kindergartenName}, '%')
            </if>
            <if test="kindergartenAddress != null  and kindergartenAddress != ''">
                and ki.kindergarten_address like concat('%', #{kindergartenAddress}, '%')
            </if>
            <if test="kindergartenType != null  and kindergartenType != ''">
                and ki.kindergarten_type = #{kindergartenType}
            </if>
            <if test="phone != null  and phone != ''">
                and ki.phone = #{phone}
            </if>
            <if test="contacts != null  and contacts != ''">
                and ki.contacts = #{contacts}
            </if>
            <!-- 查询区域角色信息 -->
            <if test="organizeIdList != null and organizeIdList.size() > 0">
                and ki.organize_id in
                <foreach collection="organizeIdList" item="organizeId" open="(" separator="," close=")">
                    #{organizeId}
                </foreach>
            </if>
            <!-- 查询即是老师又是家长角色的幼儿园信息 -->
            <if test="idList != null and idList.size() > 0">
                and ki.id in
                <foreach collection="idList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <!-- 查询家长角色的幼儿园信息 -->
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ki.id in
                <foreach collection="kindergartenIdList" item="kId" open="(" separator="," close=")">
                    #{kId}
                </foreach>
            </if>
            and ki.del_flag = 0
        </where>
        order by ki.create_time desc
    </select>

    <select id="selectKindergartenInfoCount" parameterType="KindergartenInfoDTO" resultType="int">
        SELECT
        count(1)
        FROM
        kindergarten_info ki
        LEFT JOIN organize_info oi ON ki.organize_id = oi.id
        LEFT JOIN sys_user u ON ki.principal_id = u.user_id
        <where>
            <if test="id != null">
                and ki.id = #{id}
            </if>
            <if test="organizeId != null ">
                and ki.organize_id = #{organizeId}
            </if>
            <if test="userId != null ">
                and ki.user_id = #{userId}
            </if>
            <if test="principalId != null ">
                and ki.principal_id = #{principalId}
            </if>
            <if test="kindergartenName != null  and kindergartenName != ''">
                and ki.kindergarten_name like concat('%', #{kindergartenName}, '%')
            </if>
            <if test="kindergartenAddress != null  and kindergartenAddress != ''">
                and ki.kindergarten_address like concat('%', #{kindergartenAddress}, '%')
            </if>
            <if test="kindergartenType != null  and kindergartenType != ''">
                and ki.kindergarten_type = #{kindergartenType}
            </if>
            <if test="phone != null  and phone != ''">
                and ki.phone = #{phone}
            </if>
            <if test="contacts != null  and contacts != ''">
                and ki.contacts = #{contacts}
            </if>
            <!-- 查询区域角色信息 -->
            <if test="organizeIdList != null and organizeIdList.size() > 0">
                and ki.organize_id in
                <foreach collection="organizeIdList" item="organizeId" open="(" separator="," close=")">
                    #{organizeId}
                </foreach>
            </if>
            <!-- 查询即是老师又是家长角色的幼儿园信息 -->
            <if test="idList != null and idList.size() > 0">
                and ki.id in
                <foreach collection="idList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <!-- 查询家长角色的幼儿园信息 -->
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ki.id in
                <foreach collection="kindergartenIdList" item="kId" open="(" separator="," close=")">
                    #{kId}
                </foreach>
            </if>
            and ki.del_flag = 0
        </where>
    </select>

    <select id="selectKindergartenInfoById" parameterType="Long"
            resultMap="KindergartenInfoResult">
        SELECT ki.id,
               ki.user_id,
               ki.principal_id,
               u.user_name principalName,
               ki.organize_id,
               oi.organize_name,
               ki.kindergarten_name,
               ki.kindergarten_address,
               ki.kindergarten_type,
               ki.phone,
               ki.contacts,
               ki.del_flag,
               ki.create_by,
               ki.create_time,
               ki.update_by,
               ki.update_time
        FROM kindergarten_info ki
                 LEFT JOIN organize_info oi ON ki.organize_id = oi.id
                 LEFT JOIN sys_user u ON ki.principal_id = u.user_id
        where ki.id = #{id}
    </select>

    <insert id="insertKindergartenInfo" parameterType="KindergartenInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into kindergarten_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,
            </if>
            <if test="principalId != null">principal_id,
            </if>
            <if test="organizeId != null">organize_id,
            </if>
            <if test="kindergartenName != null">kindergarten_name,
            </if>
            <if test="kindergartenAddress != null">kindergarten_address,
            </if>
            <if test="kindergartenType != null">kindergarten_type,
            </if>
            <if test="phone != null">phone,
            </if>
            <if test="contacts != null">contacts,
            </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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},
            </if>
            <if test="principalId != null">#{principalId},
            </if>
            <if test="organizeId != null">#{organizeId},
            </if>
            <if test="kindergartenName != null">#{kindergartenName},
            </if>
            <if test="kindergartenAddress != null">#{kindergartenAddress},
            </if>
            <if test="kindergartenType != null">#{kindergartenType},
            </if>
            <if test="phone != null">#{phone},
            </if>
            <if test="contacts != null">#{contacts},
            </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>
        </trim>
    </insert>

    <update id="updateKindergartenInfo" parameterType="KindergartenInfo">
        update kindergarten_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id =
                #{userId},
            </if>
            <if test="principalId != null">principal_id =
                #{principalId},
            </if>
            <if test="organizeId != null">organize_id =
                #{organizeId},
            </if>
            <if test="kindergartenName != null">kindergarten_name =
                #{kindergartenName},
            </if>
            <if test="kindergartenAddress != null">kindergarten_address =
                #{kindergartenAddress},
            </if>
            <if test="kindergartenType != null">kindergarten_type =
                #{kindergartenType},
            </if>
            <if test="phone != null">phone =
                #{phone},
            </if>
            <if test="contacts != null">contacts =
                #{contacts},
            </if>
            <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>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteKindergartenInfoById" parameterType="Long">
        delete
        from kindergarten_info
        where id = #{id}
    </delete>

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

    <update id="updateKindergartenInfoByIds" parameterType="String">
        update kindergarten_info set del_flag = 1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="updateKindergartenInfoUserId" parameterType="KindergartenInfo">
        update kindergarten_info
        <trim prefix="SET" suffixOverrides=",">
            principal_id = #{principalId},
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <select id="getKindergartenIdByParentId" parameterType="String" resultType="long">
        SELECT
        DISTINCT ki.id
        FROM
        parent_info pi
        LEFT JOIN parent_student_info psi ON pi.id = psi.parent_id
        LEFT JOIN student_info si ON psi.student_id = si.id
        LEFT JOIN class_info ci ON si.class_id = ci.id
        LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        <where>
            <if test="phone != null  and phone != ''">
                and pi.phone = #{phone}
            </if>
        </where>
        and pi.del_flag = 0
    </select>

    <select id="getKindergartenSideAnalysis"
            resultType="com.xinyilu.system.domain.vo.kindergartenSideAnalysis.KindergartenSideAnalysisVO">
        SELECT
        ki.kindergarten_name,
        ki.kindergarten_type,
        si.id student_id,
        si.birth_date,
        si.student_sex,
        IFNULL(SUM(ts.item_score), 0) item_score
        FROM
        kindergarten_info ki
        INNER JOIN student_info si ON ki.id = si.kindergarten_id
        INNER JOIN test_item_score ts ON si.id = ts.student_id
        <where>
            ki.del_flag = 0 AND ts.del_flag = 0
            <if test="kindergartenId != null">
                AND ki.id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND ts.batch_code = #{batchCode}
            </if>
        </where>
        GROUP BY si.id
    </select>

    <select id="getFirstKinderCategoryScoreInfo"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        ti.item_code,
        tsi.test_item_id,
        ti.item_name,
        IFNULL(SUM(tsi.item_fraction), 0) itemFraction
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tsi.test_item_id
        ORDER BY tsi.test_item_id asc
    </select>

    <select id="getItemCategoryScoreGroupByClassId" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        ti.item_code,
        tsi.class_id,
        tsi.test_item_id,
        ti.item_name,
        IFNULL(ROUND(SUM(tsi.item_fraction),2), 0) itemFraction
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="classId != null">
                AND tsi.class_id=#{classId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex = #{sex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tsi.test_item_id,tsi.class_id
        ORDER BY tsi.test_item_id asc
    </select>

    <select id="getItemCategoryScoreGroupByKindergartenId" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        ti.item_code,
        si.kindergarten_id,
        tsi.test_item_id,
        ti.item_name,
        IFNULL(ROUND(SUM(tsi.item_fraction),2), 0) itemFraction
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex = #{sex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tsi.test_item_id,si.kindergarten_id
        ORDER BY tsi.test_item_id asc
    </select>

    <select id="getKinderCategoryScoreInfo"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        tc.id,
        tc.category_name categoryName,
        tc.category_name_abbreviate categoryNameAbbreviate,
        tsi.student_id,
        si.birth_date,
        IFNULL(SUM(tsi.item_fraction), 0) itemFraction
        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
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tc.id
        ORDER BY tc.id asc
    </select>

    <select id="getKinderCategoryScoreInfoByClass" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        tc.id,
        tc.category_name categoryName,
        tc.category_name_abbreviate categoryNameAbbreviate,
        tsi.student_id,
        tsi.class_id,
        si.birth_date,
        IFNULL(ROUND(SUM(tsi.item_fraction),2), 0) itemFraction
        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
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="classId != null">
                AND tsi.class_id= #{classId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex = #{sex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tc.id,tsi.class_id
        ORDER BY tc.id asc
    </select>

    <select id="getKinderCategoryScoreInfoByKindergarten" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        tc.id,
        tc.category_name categoryName,
        tc.category_name_abbreviate categoryNameAbbreviate,
        tsi.student_id,
        si.kindergarten_id,
        si.birth_date,
        IFNULL(ROUND(SUM(tsi.item_fraction),2), 0) itemFraction
        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
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex = #{sex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tc.id,si.kindergarten_id
        ORDER BY tc.id asc
    </select>

    <select id="getAvgKinderCategoryScoreInfo"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.FirstCategoryScoreVO">
        SELECT
        tc.parent_id parentId,
        (select category_name from test_item_category where id = tc.parent_id) parentName,
        tc.id categoryId,
        tc.category_name categoryName,
        IFNULL(SUM(tsi.item_fraction), 0) avgCategoryFraction
        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
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tc.id
        ORDER BY tc.id asc
    </select>

    <select id="getAvgCityCategoryScoreInfo"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.FirstCategoryScoreVO">
        SELECT
        tc.id categoryId,
        tc.category_name categoryName,
        IFNULL( SUM( tsi.item_avg_score ), 0 ) avgCategoryFraction
        FROM
        test_item_city_score tsi
        INNER JOIN test_items ti ON tsi.item_id = ti.id
        INNER JOIN test_item_category tc ON ti.item_category_id = tc.id
        <where>
            tsi.del_flag = 0
            <if test="studentSex != null and studentSex != ''">
                AND tsi.sex = #{studentSex}
            </if>
            <if test="kindergartenId != null">
                AND tsi.area_id = (
                SELECT
                ai.id
                FROM
                sys_area ai
                INNER JOIN organize_info oi ON ai.id = oi.area_id
                INNER JOIN kindergarten_info ki ON oi.id = ki.organize_id
                WHERE
                ki.id = #{kindergartenId}
                )
            </if>
        </where>
        GROUP BY tc.id
        ORDER BY tc.id ASC
    </select>

    <select id="getKindergartenPhoneCount" resultType="int">
        SELECT
        COUNT( 1 )
        FROM
        kindergarten_info
        <where>
            <if test="phone != null  and phone != ''">
                and phone = #{phone}
            </if>
            and del_flag = 0
        </where>
    </select>

    <select id="getKindergartenPrincipalIdCount" resultType="int">
        SELECT
        COUNT( 1 )
        FROM
        kindergarten_info
        <where>
            <if test="principalId != null ">
                principal_id = #{principalId}
            </if>
            AND del_flag = 0
        </where>
    </select>

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

    <select id="getStudentAndKindergartenCount" parameterType="long"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.StudentAndKindergartenVO">
        SELECT
        ki.id kindergartenId,
        si.id studentId,
        si.student_sex
        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
        <where>
            ki.del_flag = 0 AND si.del_flag = 0
            <if test="areaIdList != null and areaIdList.size() > 0">
                AND 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="getAvgWholeCityCategoryScoreInfo"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.FirstCategoryScoreVO">
        SELECT
        tc.parent_id parentId,
        (select category_name from test_item_category where id = tc.parent_id) parentName,
        tc.id categoryId,
        tc.category_name categoryName,
        IFNULL(SUM(tsi.item_fraction), 0) avgCategoryFraction
        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
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null and kindergartenId.size() > 0">
                AND si.kindergarten_id in
                <foreach item="kindergartenId" collection="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tc.id
        ORDER BY tc.id asc
    </select>

    <select id="getAvgWholeCityItemScore"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.FirstCategoryScoreVO">
        SELECT
        ti.item_code,
        tsi.test_item_id,
        ti.item_name,
        IFNULL(SUM(tsi.item_fraction), 0) avgCategoryFraction
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null and kindergartenId.size() > 0">
                AND si.kindergarten_id in
                <foreach item="kindergartenId" collection="kindergartenId" open="(" separator="," close=")">
                    #{kindergartenId}
                </foreach>
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tsi.test_item_id
        ORDER BY tsi.test_item_id asc
    </select>

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

    <select id="getKindergartenIdListByArea" parameterType="long"
            resultType="long">
        SELECT
        DISTINCT ki.id kindergartenId
        FROM
        kindergarten_info ki
        LEFT JOIN organize_info oi ON oi.id = ki.organize_id
        <where>
            ki.del_flag = 0
            <if test="areaIdList != null and areaIdList.size() > 0">
                AND oi.area_id IN
                <foreach collection="areaIdList" item="areaId" open="(" separator="," close=")">
                    #{areaId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getStudentCount" resultType="java.lang.Long">
        SELECT
        DISTINCT count( tsi.student_id )as num
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
            GROUP BY
            tsi.test_item_id
            ORDER BY
            num Desc LIMIT 1
        </where>
    </select>

    <select id="getItemCategoryStudentCount" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="java.lang.Long">
        SELECT
        DISTINCT count( tsi.student_id )as num
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="classId != null">
                AND tsi.class_id= #{classId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex = #{sex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
            GROUP BY
            tsi.test_item_id
            ORDER BY
            num Desc LIMIT 1
        </where>
    </select>

    <select id="getFirstKinderCategoryScore"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        ti.item_code,
        tsi.test_item_id,
        ti.item_name,
        IFNULL(SUM(tsi.item_fraction), 0) itemFraction
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="kindergartenId != null">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tsi.test_item_id
        ORDER BY tsi.test_item_id asc
    </select>

    <select id="getTestItemCategoryParentId"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT tc.id,
               tc.parent_id                                                           parentId,
               ti.item_code,
               ti.item_name,
               (select category_name FROM test_item_category where id = tc.parent_id) categoryName
        FROM test_items ti
                 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 ti.item_code &lt;&gt; #{itemCode}
        ORDER BY tc.id asc
    </select>


    <select id="getTestItemList"
            resultType="com.xinyilu.system.domain.vo.kindergartenSideAnalysis.KindergartenTestItemVO">
        SELECT
        tis.test_item_id,
        ti.item_name,
        tis.student_id,
        tis.class_id,
        tic.parent_id parentId,
        ( SELECT category_name FROM test_item_category WHERE id = tic.parent_id ) parentName,
        tic.id categoryId,
        tic.category_name,
        tic.category_name_abbreviate,
        SUM(tis.item_fraction) avgScore
        FROM
        test_item_score tis
        INNER JOIN test_items ti ON tis.test_item_id = ti.id
        INNER JOIN test_category_item tci ON ti.id = tci.item_id
        INNER JOIN test_item_category tic ON tic.id = tci.item_category_id
        INNER JOIN class_info ci ON ci.id = tis.class_id
        INNER JOIN kindergarten_info ki ON ki.id = ci.kindergarten_id
        <where>
            tis.del_flag = 0
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="itemIdList != null and itemIdList.size() > 0">
                and tis.test_item_id in
                <foreach collection="itemIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            and ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tic.id,tis.student_id
        ORDER BY tic.id ASC
    </select>

    <select id="getKindergartenNameList"
            resultType="com.xinyilu.system.domain.kindergarteninfo.KindergartenInfo">
        select id,
        kindergarten_name
        from kindergarten_info
        where del_flag = 0
        <if test="kindergartenName != null  and kindergartenName != ''">
            and kindergarten_name like concat('%', #{kindergartenName}, '%')
        </if>
    </select>

    <select id="getKindergartenByKindergartenName"
            resultType="long">
        select id
        from kindergarten_info
        where del_flag = 0
          and kindergarten_name = #{kindergartenName}
    </select>

    <select id="getStudentBirthDate"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        tsi.id,
        tsi.student_id,
        si.birth_date,
        ti.item_code,
        tsi.test_item_id,
        ti.item_name,
        IFNULL( tsi.item_fraction, 0 ) itemFraction
        FROM
        test_item_score tsi
        INNER JOIN test_items ti ON tsi.test_item_id = ti.id
        INNER JOIN student_info si ON si.id = tsi.student_id
        <where>
            tsi.del_flag = 0
            <if test="batchCode != null and batchCode != ''">
                and tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
            <if test="kindergartenId != null ">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
        </where>
    </select>

    <select id="getCategoryScoreAndStudentBirthDate"
            resultType="com.xinyilu.system.domain.vo.kinderPhysiqueAnalysis.KinderCategoryScoreVO">
        SELECT
        tc.id,
        tc.category_name categoryName,
        tc.category_name_abbreviate categoryNameAbbreviate,
        tsi.student_id,
        si.birth_date,
        IFNULL( tsi.item_fraction, 0 ) itemFraction
        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
        INNER JOIN student_info si ON tsi.student_id = si.id
        <where>
            tsi.del_flag = 0
            <if test="batchCode != null and batchCode != ''">
                and tsi.batch_code = #{batchCode}
            </if>
            <if test="studentSex != null and studentSex != ''">
                AND si.student_sex = #{studentSex}
            </if>
            <if test="kindergartenId != null ">
                AND si.kindergarten_id = #{kindergartenId}
            </if>
            AND ti.item_code &lt;&gt; #{itemCode}
        </where>
    </select>
</mapper>
