<?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.testItem.TestItemsMapper">

    <resultMap type="testItemsVO" id="TestItemsResult">
        <result property="id" column="id"/>
        <result property="itemCategoryId" column="item_category_id"/>
        <result property="resultDictType" column="result_dict_type"/>
        <result property="itemName" column="item_name"/>
        <result property="itemCode" column="item_code"/>
        <result property="itemContent" column="item_content"/>
        <result property="measureUnit" column="measure_unit"/>
        <result property="largeStandardScore" column="large_standard_score"/>
        <result property="middleStandardScore" column="middle_standard_score"/>
        <result property="smallStandardScore" column="small_standard_score"/>
        <result property="unifiedStandardScore" column="unified_standard_score"/>
        <result property="sorting" column="sorting"/>
        <result property="remark" column="remark"/>
        <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="standardType" column="standard_type"/>
        <result property="maxScore" column="max_score"/>
        <result property="minScore" column="min_score"/>
        <result property="levelDescription" column="level_description"/>
        <result property="categoryName" column="category_name"/>
    </resultMap>

    <sql id="selectTestItemsVo">
        select id, item_category_id, result_dict_type, item_name, item_code, item_content, measure_unit, large_standard_score, middle_standard_score, small_standard_score, unified_standard_score, sorting, remark, del_flag, create_by, create_time, update_by, update_time from test_items
    </sql>

    <select id="selectTestItemsList" parameterType="TestItems" resultMap="TestItemsResult">
        SELECT
        ti.id,
        ti.item_category_id,
        ti.result_dict_type,
        ti.item_name,
        ti.item_code,
        ti.item_content,
        ti.measure_unit,
        ti.large_standard_score,
        ti.middle_standard_score,
        ti.small_standard_score,
        ti.unified_standard_score,
        ti.sorting,
        ti.remark,
        ti.del_flag,
        ti.create_by,
        ti.create_time,
        ti.update_by,
        ti.update_time,
        ts.standard_type,
        ts.max_score,
        ts.min_score,
        ts.level_description
        FROM
        test_items ti
        LEFT JOIN test_item_standard ts ON ti.id = ts.test_items_id
        <where>
            <if test="resultDictType != null ">
                and ti.result_dict_type = #{resultDictType}
            </if>
            <if test="itemName != null  and itemName != ''">
                and ti.item_name like concat('%', #{itemName}, '%')
            </if>
            <if test="itemCode != null  and itemCode != ''">
                and ti.item_code = #{itemCode}
            </if>
            <if test="itemContent != null  and itemContent != ''">
                and ti.item_content = #{itemContent}
            </if>
            <if test="measureUnit != null  and measureUnit != ''">
                and ti.measure_unit = #{measureUnit}
            </if>
            <if test="largeStandardScore != null ">
                and ti.large_standard_score = #{largeStandardScore}
            </if>
            <if test="middleStandardScore != null ">
                and ti.middle_standard_score = #{middleStandardScore}
            </if>
            <if test="smallStandardScore != null ">
                and ti.small_standard_score = #{smallStandardScore}
            </if>
            <if test="unifiedStandardScore != null ">
                and ti.unified_standard_score = #{unifiedStandardScore}
            </if>
            <if test="sorting != null ">
                and ti.sorting = #{sorting}
            </if>
            and ti.del_flag = 0
        </where>
        order by ti.create_time desc, ti.sorting asc
    </select>

    <select id="selectTestItemsById" parameterType="Long" resultMap="TestItemsResult">
        SELECT
        ti.id,
        ti.item_category_id,
        ti.result_dict_type,
        ti.item_name,
        ti.item_code,
        ti.item_content,
        ti.measure_unit,
        ti.large_standard_score,
        ti.middle_standard_score,
        ti.small_standard_score,
        ti.unified_standard_score,
        ti.sorting,
        ti.remark,
        ti.del_flag,
        ti.create_by,
        ti.create_time,
        ti.update_by,
        ti.update_time,
        ts.standard_type,
        ts.max_score,
        ts.min_score,
        ts.level_description,
        tc.category_name
        FROM
        test_items ti
        LEFT JOIN test_item_standard ts ON ti.id = ts.test_items_id
        LEFT JOIN test_item_category tc ON ti.item_category_id = tc.id
        where ti.id = #{id}
    </select>

    <insert id="insertTestItems" parameterType="TestItems" useGeneratedKeys="true"
            keyProperty="id">
        insert into test_items
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="itemCategoryId != null">item_category_id,
            </if>
            <if test="resultDictType != null">result_dict_type,
            </if>
            <if test="itemName != null">item_name,
            </if>
            <if test="itemCode != null">item_code,
            </if>
            <if test="itemContent != null">item_content,
            </if>
            <if test="measureUnit != null">measure_unit,
            </if>
            <if test="largeStandardScore != null">large_standard_score,
            </if>
            <if test="middleStandardScore != null">middle_standard_score,
            </if>
            <if test="smallStandardScore != null">small_standard_score,
            </if>
            <if test="unifiedStandardScore != null">unified_standard_score,
            </if>
            <if test="sorting != null">sorting,
            </if>
            <if test="remark != null">remark,
            </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="itemCategoryId != null">#{itemCategoryId},
            </if>
            <if test="resultDictType != null">#{resultDictType},
            </if>
            <if test="itemName != null">#{itemName},
            </if>
            <if test="itemCode != null">#{itemCode},
            </if>
            <if test="itemContent != null">#{itemContent},
            </if>
            <if test="measureUnit != null">#{measureUnit},
            </if>
            <if test="largeStandardScore != null">#{largeStandardScore},
            </if>
            <if test="middleStandardScore != null">#{middleStandardScore},
            </if>
            <if test="smallStandardScore != null">#{smallStandardScore},
            </if>
            <if test="unifiedStandardScore != null">#{unifiedStandardScore},
            </if>
            <if test="sorting != null">#{sorting},
            </if>
            <if test="remark != null">#{remark},
            </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="updateTestItems" parameterType="TestItems">
        update test_items
        <trim prefix="SET" suffixOverrides=",">
            <if test="itemCategoryId != null">item_category_id =
                #{itemCategoryId},
            </if>
            <if test="resultDictType != null">result_dict_type =
                #{resultDictType},
            </if>
            <if test="itemName != null">item_name =
                #{itemName},
            </if>
            <if test="itemCode != null">item_code =
                #{itemCode},
            </if>
            <if test="itemContent != null">item_content =
                #{itemContent},
            </if>
            <if test="measureUnit != null">measure_unit =
                #{measureUnit},
            </if>
            <if test="largeStandardScore != null">large_standard_score =
                #{largeStandardScore},
            </if>
            <if test="middleStandardScore != null">middle_standard_score =
                #{middleStandardScore},
            </if>
            <if test="smallStandardScore != null">small_standard_score =
                #{smallStandardScore},
            </if>
            <if test="unifiedStandardScore != null">unified_standard_score =
                #{unifiedStandardScore},
            </if>
            <if test="sorting != null">sorting =
                #{sorting},
            </if>
            <if test="remark != null">remark =
                #{remark},
            </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="deleteTestItemsById" parameterType="Long">
        delete from test_items where id = #{id}
    </delete>

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

    <update id="updateTestItemsByIds" parameterType="java.lang.Long">
        update test_items set del_flag = 1, update_time = now() where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="selectItemCodeInfo" parameterType="string" resultType="int">
        select count(1) itemCount from test_items
        <where>
            del_flag = 0
            <if test="itemCode != null and itemCode != ''">
                and item_code = #{itemCode}
            </if>
            <if test="itemName != null and itemName != ''">
                and item_name = #{itemName}
            </if>
        </where>
    </select>

    <select id="getBodyShapeScoreInfo" resultType="BodyShapeItemScoreVO">
        SELECT
        ti.item_code,
        ti.item_name,
        ti.measure_unit,
        IFNULL(ts.item_score, 0) item_score
        FROM
        test_item_score ts
        INNER JOIN test_items ti ON ts.test_item_id = ti.id
        INNER JOIN sys_dict_data sd ON ts.score_dict_id = sd.dict_code
        INNER JOIN test_score_batch tb ON ts.batch_code = tb.batch_code
        WHERE
        ts.del_flag = 0
        AND ts.student_id = #{studentId}
        AND tb.batch_code = #{batchCode}
        AND sd.dict_value = 'number'
        AND ti.item_code IN
        <foreach collection="itemCodeList" item="itemCode" open="(" separator="," close=")">
            #{itemCode}
        </foreach>
    </select>

    <select id="getTestItemsLevelList" parameterType="string" resultType="testItemLevel">
        SELECT
            tl.level_name,
            tl.level_min_score,
            tl.level_max_score
        FROM
            test_item_level tl
            LEFT JOIN  test_items ti ON tl.item_id = ti.id
            where ti.item_code = #{itemCode} AND tl.del_flag = 0
    </select>

    <select id="getItemAvgScore"
            resultType="com.xinyilu.system.domain.vo.physicalActionAnalysis.PhysicalActionAnalysisVO">
        SELECT
        tis.test_item_id item_id,
        ti.item_name,
        si.student_sex,
        IF( si.student_sex = 'MALE', AVG( tis.item_fraction ), 0 ) maleAvgScore,
        IF( si.student_sex = 'FEMALE', AVG( tis.item_fraction ), 0 ) feMaleAvgScore
        FROM
        test_item_score tis
        INNER JOIN test_items ti ON ti.id = tis.test_item_id
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN student_info si ON si.id = tis.student_id
        <where>
            tis.del_flag = 0
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="classType != null and classType != ''">
                and ci.class_type = #{classType}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            and ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY
        tis.test_item_id,
        si.student_sex
    </select>

    <select id="scoreExportByClass" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="com.xinyilu.system.domain.vo.scoreExport.PhysicalActionScoreVO">
        SELECT
        ki.kindergarten_name AS kindergartenName,
        ki.id as kindergartenId,
        ci.class_name AS className,
        ci.id as classId,
        tis.test_item_id item_id,
        ti.item_name,
        ti.item_code,
        ROUND(AVG( tis.item_score ),2) AS avgScore,
        ROUND(AVG( tis.item_fraction ),2) AS avgFraction
        FROM
        test_item_score tis
        INNER JOIN test_items ti ON ti.id = tis.test_item_id
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        INNER JOIN student_info si ON si.id = tis.student_id
        <where>
            tis.del_flag = 0 AND ci.del_flag =0
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
            <if test="classId != null ">
                and ci.id = #{classId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex=#{sex}
            </if>
        </where>
        GROUP BY
        tis.test_item_id,ci.id
    </select>

    <select id="scoreExportByKindergarten" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="com.xinyilu.system.domain.vo.scoreExport.PhysicalActionScoreVO">
        SELECT
        ki.kindergarten_name AS kindergartenName,
        ki.id as kindergartenId,
        tis.test_item_id item_id,
        ti.item_name,
        ti.item_code,
        ROUND(AVG( tis.item_score ),2) AS avgScore,
        ROUND(AVG( tis.item_fraction ),2) AS avgFraction
        FROM
        test_item_score tis
        INNER JOIN test_items ti ON ti.id = tis.test_item_id
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        INNER JOIN student_info si ON si.id = tis.student_id
        <where>
            tis.del_flag = 0 AND ci.del_flag =0
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex=#{sex}
            </if>
        </where>
        GROUP BY
        tis.test_item_id,ki.id
    </select>

    <select id="getClassByScoreExport" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="long">
        SELECT
        ci.id AS classId
        FROM
        test_item_score tis
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        INNER JOIN student_info si ON si.id = tis.student_id
        <where>
            tis.del_flag = 0 AND ci.del_flag =0
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
            <if test="kindergartenId == null and kindergartenIdList != null and kindergartenIdList.size() > 0">
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                    and ki.id in #{kindergartenId}
                </foreach>
            </if>
            <if test="classId != null ">
                and ci.id = #{classId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex=#{sex}
            </if>
        </where>
        GROUP BY
        tis.class_id
    </select>

    <select id="getKindergartenByScoreExport" parameterType="com.xinyilu.system.domain.dto.ScoreExportDTO" resultType="long">
        SELECT
        ki.id
        FROM
        test_item_score tis
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        INNER JOIN student_info si ON si.id = tis.student_id
        <where>
            tis.del_flag = 0 AND ci.del_flag =0
            <if test="kindergartenId != null ">
                and ki.id = #{kindergartenId}
            </if>
            <if test="kindergartenId == null and kindergartenIdList != null and kindergartenIdList.size() > 0">
                <foreach collection="kindergartenIdList" item="kindergartenId" open="(" separator="," close=")">
                and ki.id in #{kindergartenId}
                </foreach>

            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="sex != null and sex != ''">
                AND si.student_sex=#{sex}
            </if>
        </where>
        GROUP BY
        ki.id
    </select>

    <select id="getItemFractionInfo"
            resultType="com.xinyilu.system.domain.vo.physicalActionAnalysis.ItemFractionInfo">
        SELECT
        ts.student_id,
        si.student_sex,
        ts.test_item_id item_id,
        ti.item_name,
        IFNULL(ts.item_fraction, 0) item_fraction
        FROM
        test_item_score ts
        INNER JOIN test_items ti ON ts.test_item_id = ti.id
        INNER JOIN student_info si ON ts.student_id = si.id
        INNER JOIN class_info ci ON si.class_id = ci.id
        <where>
            ts.del_flag = 0
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="classType != null and classType != ''">
                and ci.class_type = #{classType}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and ts.batch_code = #{batchCode}
            </if>
            <if test="itemIdList != null and itemIdList.size() >0">
                and ts.test_item_id in
                <foreach collection="itemIdList" item="itemId" open="(" separator="," close=")">
                    #{itemId}
                </foreach>
            </if>
            and ti.item_code &lt;&gt; #{itemCode}
        </where>
        ORDER BY ts.test_item_id asc
    </select>

    <select id="getTestItemLevelByItemId" parameterType="long" resultType="testItemLevel">
        SELECT
        tl.item_id,
        tl.level_name,
        tl.level_min_score,
        tl.level_max_score
        FROM
        test_item_level tl
        <where>
            tl.del_flag = 0 and standard_flag = 1
            <if test="itemIdList != null and itemIdList.size() >0">
                and tl.item_id IN
                <foreach collection="itemIdList" item="itemId" open="(" separator="," close=")">
                    #{itemId}
                </foreach>
            </if>
        </where>
        ORDER BY
        tl.item_id ASC
    </select>

    <select id="getItemById" parameterType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO">
        SELECT
            id itemId,
            item_name itemName
        FROM
            test_items
        where id = #{Id}
          AND del_flag = 0
    </select>

    <select id="getItemCityAvgScore"
            resultType="com.xinyilu.system.domain.vo.physicalActionAnalysis.PhysicalActionAnalysisVO">
        SELECT
        tis.test_item_id item_id,
        ti.item_name,
        si.student_sex,
        IF( si.student_sex = 'MALE', AVG( tis.item_fraction ), 0 ) maleCityAvgScore,
        IF( si.student_sex = 'FEMALE', AVG( tis.item_fraction ), 0 ) feMaleCityAvgScore
        FROM
        test_item_score tis
        INNER JOIN test_items ti ON ti.id = tis.test_item_id
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN student_info si ON si.id = tis.student_id
        WHERE
        tis.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>
        AND ci.class_type = #{classType}
        AND tis.batch_code = #{batchCode}
        AND ti.item_code &lt;&gt; #{itemCode}
        GROUP BY
        tis.test_item_id,
        si.student_sex
    </select>

    <select id="getCategoryItemInfo" parameterType="long" resultType="categoryItemInfoVO">
        SELECT
            tic.id category_id,
            tic.category_name,
            tic.category_code,
            ti.id item_id,
            ti.item_name,
            ti.item_code
        FROM
            test_items ti
            INNER JOIN test_category_item tci ON ti.id = tci.item_id
            INNER JOIN test_item_category tic ON tci.item_category_id = tic.id
        WHERE
            ti.id = #{itemId}
            AND ti.del_flag = 0
            AND tci.del_flag = 0
            AND tic.del_flag = 0
    </select>
</mapper>
