<?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.testItemScoreinfo.TestItemScoreMapper">
    <resultMap type="TestItemScoreinfoVO" id="TestItemScoreResult">
        <result property="id" column="id"/>
        <result property="classId" column="class_id"/>
        <result property="studentId" column="student_id"/>
        <result property="testItemId" column="test_item_id"/>
        <result property="userId" column="user_id"/>
        <result property="scoreDictId" column="score_dict_id"/>
        <result property="scoreDictValue" column="score_dict_value"/>
        <result property="batchCode" column="batch_code"/>
        <result property="itemScore" column="item_score"/>
        <result property="itemFraction" column="item_fraction"/>
        <result property="userName" column="user_name"/>
        <result property="organizeName" column="organize_name"/>
        <result property="syntheticalAssess" column="synthetical_assess"/>
        <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="studentName" column="student_name"/>
        <result property="itemName" column="item_name"/>
        <result property="remark" column="remark"/>
        <result property="categoryName" column="category_name"/>
        <result property="itemContent" column="item_content"/>
        <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="itemCategoryId" column="item_category_id"/>
        <result property="kindergartenId" column="kindergarten_id"/>
        <result property="classType" column="class_type"/>
        <result property="batchCode" column="batch_code"/>
        <result property="classType" column="class_type"/>
        <result property="areaId" column="area_id"/>
        <result property="categoryName" column="category_name"/>
    </resultMap>

    <resultMap type="com.xinyilu.system.domain.vo.motionprescriptionlibrary.MotionPrescriptionLibraryAndVideoVO"
               id="MotionPrescriptionVideo">
        <result property="id" column="id"/>
        <result property="physicalQualityName" column="physical_quality_name"/>
        <result property="degreeStartFraction" column="degree_start_fraction"/>
        <result property="degreeEndFraction" column="degree_end_fraction"/>
        <result property="motionType" column="motion_type"/>
        <result property="motionFrequency" column="motion_frequency"/>
        <result property="motionStrength" column="motion_strength"/>
        <result property="motionTime" column="motion_time"/>
        <result property="motionCapacity" column="motion_capacity"/>
        <result property="advancedTime" column="advanced_time"/>
        <result property="advancedScheme" column="advanced_scheme"/>
        <collection property="motionPrescriptionVideoInfoVOList" javaType="java.util.List"
                    resultMap="MotionPrescriptionVideoResult"/>
    </resultMap>

    <resultMap type="com.xinyilu.system.domain.vo.motionprescriptionlibrary.MotionPrescriptionVideoInfoVO"
               id="MotionPrescriptionVideoResult">
        <result property="motionPrescriptionVideoId" column="motionPrescriptionVideoId"/>
        <result property="prescriptionVideoUrl" column="prescription_video_url"/>
    </resultMap>


    <sql id="selectTestItemScoreVo">
        select id,
               class_id,
               student_id,
               test_item_id,
               score_dict_id,
               score_dict_value,
               batch_code,
               item_score,
               item_fraction,
               synthetical_assess,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time
        from test_item_score
    </sql>

    <select id="selectTestItemScoreList" parameterType="TestItemScoreinfoVO" resultType="TestItemScoreUpdateVO">
        select
        c.class_name,
        st.student_name,
        tsb.batch_name,
        tsb.id as batchId,
        s.user_id,
        s.class_id,
        s.student_id,
        s.batch_code,
        s.create_by,
        s.create_time,
        s.update_by,
        s.update_time
        from test_item_score s
        left join class_info c on s.class_id = c.id
        left join student_info st on s.student_id = st.id
        left join test_items ti on s.test_item_id = ti.id
        left join kindergarten_info ki on c.kindergarten_id = ki.id
        left join test_score_batch tsb on s.batch_code = tsb.batch_code
        <where>
            s.del_flag = 0
            <if test="classId != null ">
                and s.class_id = #{classId}
            </if>
            <if test="userId != null ">
                and s.user_id = #{userId}
            </if>
            <if test="userName != null ">
                and s.user_name = #{userName}
            </if>
            <if test="studentId != null ">
                and s.student_id = #{studentId}
            </if>
            <if test="kindergartenId != null ">
                and c.kindergarten_id = #{kindergartenId}
            </if>
            <if test="testItemId != null ">
                and s.test_item_id = #{testItemId}
            </if>
            <if test="scoreDictId != null ">
                and s.score_dict_id = #{scoreDictId}
            </if>
            <if test="scoreDictValue != null  and scoreDictValue != ''">
                and s.score_dict_value = #{scoreDictValue}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and s.batch_code = #{batchCode}
            </if>
            <if test="itemScore != null ">
                and s.item_score = #{itemScore}
            </if>
            <if test="syntheticalAssess != null  and syntheticalAssess != ''">
                and s.synthetical_assess = #{syntheticalAssess}
            </if>
            <if test="className != null  and className != ''">
                and c.class_name like concat('%', #{className}, '%')
            </if>
            <if test="studentName != null  and studentName != ''">
                and st.student_name like concat('%', #{studentName}, '%')
            </if>
            <if test="itemName != null  and itemName != ''">
                and ti.item_name like concat('%', #{itemName}, '%')
            </if>
            <if test="classIdList != null and classIdList.size() > 0">
                and s.class_id in
                <foreach collection="classIdList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
            </if>
            <if test="idList != null and idList.size() > 0">
                and s.student_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 (s.class_id in
                <foreach collection="classList" item="classId" open="(" separator="," close=")">
                    #{classId}
                </foreach>
                or
                s.student_id in
                <foreach collection="studentList" item="studentId" open="(" separator="," close=")">
                    #{studentId}
                </foreach>
                )
            </if>
            <if test="batchName != null and batchName != ''">
                and tsb.batch_name like concat('%', #{batchName}, '%')
            </if>
        </where>
        group by s.student_id, s.batch_code
        order by s.create_time desc
    </select>

    <select id="selectTestItemScoreById" parameterType="Long"
            resultType="TestItemScoreUpdateVO">
        select
        s.id,
        s.class_id,
        s.student_id,
        st.student_name,
        s.test_item_id,
        ti.item_code,
        ti.item_name,
        s.score_dict_id,
        s.score_dict_value,
        s.batch_code,
        s.item_score,
        s.item_fraction,
        s.synthetical_assess
        from test_item_score s
        left join test_items ti on s.test_item_id = ti.id
        left join student_info st on s.student_id = st.id
        <where>
            s.del_flag = 0
            <if test="studentId != null">
                and s.student_id = #{studentId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and s.batch_code = #{batchCode}
            </if>
        </where>
    </select>

    <insert id="insertTestItemScore" parameterType="TestItemScoreinfoDTO" useGeneratedKeys="true"
            keyProperty="id">
        insert into test_item_score
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                user_id,
            </if>
            <if test="classId != null">
                class_id,
            </if>
            <if test="studentId != null">
                student_id,
            </if>
            <if test="testItemId != null">
                test_item_id,
            </if>
            <if test="scoreDictId != null">
                score_dict_id,
            </if>
            <if test="scoreDictValue != null">
                score_dict_value,
            </if>
            <if test="batchCode != null">
                batch_code,
            </if>
            <if test="itemScore != null">
                item_score,
            </if>
            <if test="itemFraction != null">
                item_fraction,
            </if>
            <if test="syntheticalAssess != null">
                synthetical_assess,
            </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="classId != null">
                #{classId},
            </if>
            <if test="studentId != null">
                #{studentId},
            </if>
            <if test="testItemId != null">
                #{testItemId},
            </if>
            <if test="scoreDictId != null">
                #{scoreDictId},
            </if>
            <if test="scoreDictValue != null">
                #{scoreDictValue},
            </if>
            <if test="batchCode != null">
                #{batchCode},
            </if>
            <if test="itemScore != null">
                #{itemScore},
            </if>
            <if test="itemFraction != null">
                #{itemFraction},
            </if>
            <if test="syntheticalAssess != null">
                #{syntheticalAssess},
            </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="updateTestItemScore" parameterType="testItemScoreinfoDTO">
        update test_item_score
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">
                user_id =
                #{userId},
            </if>
            <if test="classId != null">
                class_id =
                #{classId},
            </if>
            <if test="studentId != null">
                student_id =
                #{studentId},
            </if>
            <if test="testItemId != null">
                test_item_id =
                #{testItemId},
            </if>
            <if test="scoreDictId != null">
                score_dict_id =
                #{scoreDictId},
            </if>
            <if test="scoreDictValue != null">
                score_dict_value =
                #{scoreDictValue},
            </if>
            <if test="batchCode != null">
                batch_code =
                #{batchCode},
            </if>
            <if test="itemScore != null">
                item_score =
                #{itemScore},
            </if>
            <if test="itemFraction != null">
                item_fraction =
                #{itemFraction},
            </if>
            <if test="syntheticalAssess != null">
                synthetical_assess =
                #{syntheticalAssess},
            </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="deleteTestItemScoreById" parameterType="Long">

                delete
                from test_item_score
                where id = #{id}

    </delete>

    <update id="updateTestItemScoreByIds">

                update test_item_score
                set del_flag    = 1,
                    update_time = now()
                where student_id = #{studentId}
                  and batch_code = #{batchCode}

    </update>

    <select id="getItemType" parameterType="long" resultType="string">

                SELECT IFNULL(sd.dict_value, '') AS dictValue
                FROM test_items ti
                         INNER JOIN sys_dict_data sd ON ti.result_dict_type = sd.dict_code
                where ti.id = #{itemId}

    </select>


    <update id="updateTestItemScoreById" parameterType="long">

                update test_item_score
                set item_score  = null,
                    update_time = now()
                where id = #{id}

    </update>

    <update id="updateTestItemTextById" parameterType="long">

                update test_item_score
                set score_dict_value = null,
                    update_time      = now()
                where id = #{id}

    </update>

    <select id="getSysAreaIdByParentId" parameterType="long" resultType="long">

                SELECT ci.id
                FROM class_info ci
                         LEFT JOIN kindergarten_info ki ON ci.kindergarten_id = ki.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 = #{id}
                  and ci.del_flag = 0
                group by ci.id

    </select>

    <select id="getClassInfoIdByParentId" parameterType="long" resultType="long">

                SELECT c.id
                FROM class_info c
                         LEFT JOIN kindergarten_info ki ON c.kindergarten_id = ki.id
                WHERE ki.principal_id = #{id}
                  and c.del_flag = 0
                group by c.id

    </select>

    <select id="getClassAndTeacherInfoIdByParentId" parameterType="long" resultType="long">

                SELECT c.id
                FROM class_info c
                         LEFT JOIN kindergarten_info ki ON c.kindergarten_id = ki.id
                         LEFT JOIN teacher_info te ON te.kindergarten_id = ki.id
                WHERE te.id = #{id}
                  and c.del_flag = 0
                group by c.id

    </select>

    <select id="getStudentInfoIdByParentId" parameterType="String" resultType="long">

                SELECT psi.student_id
                FROM parent_student_info psi
                         LEFT JOIN parent_info pi ON pi.id = psi.parent_id
                WHERE pi.phone = #{phone}
                  and psi.del_flag = 0
                group by psi.student_id

    </select>

    <select id="getAvgHeightAndWeightInfo"
            resultType="com.xinyilu.system.domain.vo.averageHeightAndWeight.HeightAndWeightAvgScoreVO">
        SELECT
        ci.class_type,
        si.student_sex,
        IFNULL(SUM(tl.item_score), 0) totalItemScore,
        ti.measure_unit,
        tl.test_item_id,
        ti.item_code,
        IFNULL(count(tl.student_id), 0) totalStudentCount
        FROM
        test_item_score tl
        INNER JOIN test_items ti ON tl.test_item_id = ti.id
        INNER JOIN student_info si ON tl.student_id = si.id
        INNER JOIN class_info ci ON si.class_id = ci.id
        <where>
            tl.del_flag = 0
            <if test="kindergartenId != null">
                AND ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                AND tl.batch_code = #{batchCode}
            </if>
            <if test="itemCodeList != null and itemCodeList.size() > 0">
                AND ti.item_code in
                <foreach collection="itemCodeList" item="itemCode" open="(" separator="," close=")">
                    #{itemCode}
                </foreach>
            </if>
        </where>
        GROUP BY ci.class_type, si.student_sex, tl.test_item_id
    </select>

    <select id="selectTestNameScoreList"
            resultType="com.xinyilu.system.domain.vo.testActionDevelopment.TestActionDevelopmentVO">
        SELECT
        ( SELECT category_name FROM test_item_category WHERE id = tic.parent_id ) rootCategoryName,
        tic.parent_id,
        tic.id item_category_id,
        ti.id item_id,
        tic.category_name,
        ti.item_name,
        ti.large_standard_score,
        ti.middle_standard_score,
        ti.small_standard_score,
        ti.unified_standard_score,
        s.item_fraction,
        ti.remark,
        ti.item_content,
        s.synthetical_assess
        FROM
        test_items ti
        INNER JOIN test_item_score s ON s.test_item_id = ti.id
        INNER JOIN test_category_item tci ON tci.item_id = ti.id
        INNER JOIN test_item_category tic ON tci.item_category_id = tic.id
        <where>
            <if test="studentId != null ">
                and s.student_id = #{studentId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and s.batch_code = #{batchCode}
            </if>
            and s.del_flag = 0 and ti.item_code &lt;&gt; #{itemCode}
        </where>
        order by tic.id desc
    </select>

    <select id="selectAllStudentScoreList"
            resultType="com.xinyilu.system.domain.vo.TestItemStudentScore.TestAllStudentScoreVO">
        SELECT
        ci.id class_id,
        ci.class_name,
        tis.student_id,
        si.student_name,
        tis.test_item_id,
        tis.item_fraction
        FROM
        test_item_score tis
        INNER JOIN class_info ci ON ci.id = tis.class_id
        INNER JOIN student_info si ON si.id = tis.student_id
        INNER JOIN kindergarten_info ki ON ki.id = ci.kindergarten_id
        <where>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != '' ">
                and tis.batch_code = #{batchCode}
            </if>
        </where>
        GROUP BY
        tis.student_id,tis.class_id,tis.test_item_id
    </select>

    <select id="selectAllClassById"
            resultType="com.xinyilu.system.domain.vo.TestItemStudentScore.AllClassByVO">
        SELECT
        ci.id class_id,
        ci.class_name,
        tis.student_id,
        si.student_name,
        tis.test_item_id,
        tis.item_fraction
        FROM
        test_item_score tis
        INNER JOIN class_info ci ON ci.id = tis.class_id
        INNER JOIN student_info si ON si.id = tis.student_id
        <where>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != '' ">
                and tis.batch_code = #{batchCode}
            </if>
        </where>
        GROUP BY
        tis.student_id,tis.class_id,tis.test_item_id
    </select>

    <select id="selectAllClassScoreList"
            resultType="com.xinyilu.system.domain.vo.TestItemStudentScore.AllStudentScoreVO">
        SELECT
        tis.class_id,
        tis.test_item_id,
        tis.student_id,
        ci.class_name,
        ci.class_type,
        tis.item_fraction
        FROM
        test_item_score tis
        INNER JOIN class_info ci ON tis.class_id = ci.id
        INNER JOIN kindergarten_info ki ON ki.id = ci.kindergarten_id
        <where>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != '' ">
                and tis.batch_code = #{batchCode}
            </if>
        </where>
        GROUP BY tis.student_id,tis.class_id,tis.test_item_id
    </select>

    <select id="selectTestItemAvgScoreList"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestItemAvgScoreClassVO">
        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,
        ci.class_name,
        ci.class_type,
        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="classId != null ">
                and tis.class_id = #{classId}
            </if>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </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,tic.id,tis.student_id
        ORDER BY tic.id ASC
    </select>

    <select id="selectTestItemAvgScoreSchoolList"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestItemAvgScoreClassVO">
        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="classType != null  and classType != ''">
                and ci.class_type = #{classType}
            </if>
            <if test="kindergartenId != null ">
                and ci.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="categoryIdList != null and categoryIdList.size() > 0">
                and tic.id in
                <foreach collection="categoryIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            and ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tis.test_item_id,tic.id,tis.student_id
        ORDER BY tic.id ASC
    </select>

    <select id="selectTestItemAvgScoreCityList"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestItemAvgScoreClassVO">
        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="classType != null  and classType != ''">
                and ci.class_type = #{classType}
            </if>
            <if test="kindergartenIdList != null and kindergartenIdList.size() > 0">
                and ki.id in
                <foreach collection="kindergartenIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            <if test="categoryIdList != null and categoryIdList.size() > 0">
                and tic.id in
                <foreach collection="categoryIdList" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            and ti.item_code &lt;&gt; #{itemCode}
        </where>
        GROUP BY tis.test_item_id,tic.id,tis.student_id
        ORDER BY tic.id ASC
    </select>

    <select id="getAllTestItemId" parameterType="Long" resultType="Long">
        SELECT
        id
        FROM
        test_item_score
        <where>
            del_flag = 0
            <if test="studentId != null ">
                and student_id = #{studentId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and batch_code = #{batchCode}
            </if>
            <if test="testItemIdList != null and testItemIdList.size() > 0">
                and test_item_id in
                <foreach collection="testItemIdList" item="itemId" open="(" separator="," close=") ">
                    #{itemId}
                </foreach>
            </if>
        </where>
    </select>


    <delete id="deleteTestItemById" parameterType="Long">
        delete from test_item_score
        <where>
            <if test="testItemIdList != null and testItemIdList.size()>0 ">
                id in
                <foreach collection="testItemIdList" item="id" open="(" separator="," close=") ">
                    #{id}
                </foreach>
            </if>
        </where>
    </delete>

    <select id="getAllTestItemScoreInfo"
            resultType="com.xinyilu.system.domain.vo.ShowItemBatchScoreVO">
        SELECT
        ti.id item_id,
        ti.result_dict_type score_dict_id,
        ti.item_code,
        ti.item_name
        FROM
        test_items ti
        INNER JOIN item_batch_info bi ON ti.id = bi.item_id
        INNER JOIN test_score_batch tc ON bi.batch_id = tc.id
        <where>
            <if test="batchId != null ">
                bi.batch_id = #{batchId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and tc.batch_code = #{batchCode}
            </if>
        </where>
        order by ti.id asc
    </select>

    <insert id="insertTestItemScoreList">
        insert into test_item_score(
        user_id,
        class_id,
        student_id,
        test_item_id,
        score_dict_id,
        score_dict_value,
        batch_code,
        item_score,
        item_fraction,
        synthetical_assess,
        del_flag,
        create_by,
        create_time
        ) values
        <foreach item="itemScore" index="index" collection="list" separator=",">
            (
            #{itemScore.userId},
            #{itemScore.classId},
            #{itemScore.studentId},
            #{itemScore.testItemId},
            #{itemScore.scoreDictId},
            #{itemScore.scoreDictValue},
            #{itemScore.batchCode},
            #{itemScore.itemScore},
            #{itemScore.itemFraction},
            #{itemScore.syntheticalAssess},
            #{itemScore.delFlag},
            #{itemScore.createBy},
            #{itemScore.createTime}
            )
        </foreach>
    </insert>

    <update id="updateTestItemScoreInfo" parameterType="testItemScore">

                update test_item_score
                set user_id            = #{userId},
                    class_id           = #{classId},
                    student_id         = #{studentId},
                    test_item_id       = #{testItemId},
                    score_dict_id      = #{scoreDictId},
                    score_dict_value   = #{scoreDictValue},
                    batch_code         = #{batchCode},
                    item_score         = #{itemScore},
                    item_fraction      = #{itemFraction},
                    synthetical_assess = #{syntheticalAssess},
                    update_by          = #{updateBy},
                    update_time        = #{updateTime}
                where id = #{id}

    </update>

    <select id="getTestItemScoreByStudent"
            resultType="com.xinyilu.system.domain.vo.ShowItemBatchScoreVO">
        SELECT
        ts.test_item_id item_id,
        ts.item_score,
        ts.score_dict_value,
        ts.item_fraction,
        ts.synthetical_assess
        FROM
        test_item_score ts
        <where>
            ts.del_flag = 0
            <if test="studentId != null ">
                and ts.student_id = #{studentId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and ts.batch_code = #{batchCode}
            </if>
        </where>
        order by ts.test_item_id asc
    </select>

    <select id="getTestBatchItemInfoList"
            resultType="com.xinyilu.system.domain.dto.ItemStudentScoreDTO">
        SELECT
        ti.id testItemId,
        ti.result_dict_type score_dict_id,
        ti.item_code,
        ti.item_name
        FROM
        test_items ti
        INNER JOIN item_batch_info bi ON ti.id = bi.item_id
        INNER JOIN test_score_batch tc ON bi.batch_id = tc.id
        <where>
            <if test="batchCode != null  and batchCode != ''">
                tc.batch_code = #{batchCode}
            </if>
        </where>
        order by ti.id asc
    </select>

    <insert id="insertTestItemScoreInfo" parameterType="TestItemScore" useGeneratedKeys="true"
            keyProperty="id">
        insert into test_item_score
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">
                user_id,
            </if>
            <if test="classId != null">
                class_id,
            </if>
            <if test="studentId != null">
                student_id,
            </if>
            <if test="testItemId != null">
                test_item_id,
            </if>
            <if test="scoreDictId != null">
                score_dict_id,
            </if>
            <if test="scoreDictValue != null">
                score_dict_value,
            </if>
            <if test="batchCode != null">
                batch_code,
            </if>
            <if test="itemScore != null">
                item_score,
            </if>
            <if test="itemFraction != null">
                item_fraction,
            </if>
            <if test="syntheticalAssess != null">
                synthetical_assess,
            </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="classId != null">
                #{classId},
            </if>
            <if test="studentId != null">
                #{studentId},
            </if>
            <if test="testItemId != null">
                #{testItemId},
            </if>
            <if test="scoreDictId != null">
                #{scoreDictId},
            </if>
            <if test="scoreDictValue != null">
                #{scoreDictValue},
            </if>
            <if test="batchCode != null">
                #{batchCode},
            </if>
            <if test="itemScore != null">
                #{itemScore},
            </if>
            <if test="itemFraction != null">
                #{itemFraction},
            </if>
            <if test="syntheticalAssess != null">
                #{syntheticalAssess},
            </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>

    <select id="getStudentFraction" parameterType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO">
        SELECT
        si.id studentId,
        si.student_sex,
        si.birth_date
        FROM
        student_info si
        <where>
            <if test="studentId != null ">
                si.id = #{studentId}
            </if>
            AND si.del_flag = 0
        </where>
    </select>

    <select id="getTestTargetScoreFraction" parameterType="String"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO">

                SELECT ttsf.test_target_name,
                       ttsf.age,
                       ttsf.sex,
                       ttsf.score_start_value,
                       ttsf.score_end_value,
                       ttsf.fraction
                FROM test_target_score_fraction ttsf

    </select>


    <select id="getHeightWeight" resultType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO"
            parameterType="com.xinyilu.system.domain.vo.testItemAvgScore.TestStudentFractionVO">

                SELECT hwf.sex,
                       hwf.height_start_value,
                       hwf.height_end_value,
                       hwf.weight_start_value,
                       hwf.weight_end_value,
                       hwf.fraction hwfFraction
                FROM height_weight_fraction hwf

    </select>

    <select id="getNationalPhysiqueGradeList" parameterType="NationalPhysiqueGrade" resultType="NationalPhysiqueGrade">
        SELECT
        d.id,
        d.start_fraction,
        d.end_fraction,
        d.rank,
        d.create_by,
        d.create_time,
        d.update_by,
        d.update_time
        FROM
        national_physique_grade d
        <where>
            <if test="startFraction != null ">
                and d.start_fraction = #{startFraction}
            </if>
            <if test="endFraction != null ">
                and d.end_fraction = #{endFraction}
            </if>
            <if test="rank != null  and rank != ''">
                and d.rank = #{rank}
            </if>
        </where>
    </select>

    <select id="getThisActionDevelopmentVO"
            parameterType="com.xinyilu.system.domain.vo.testItemAvgScore.ThisActionDevelopmentVO"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.ThisActionDevelopmentVO">
        SELECT
        ( SELECT category_name FROM test_item_category WHERE id = tic.parent_id ) rootCategoryName,
        tic.id item_category_id,
        tic.category_name,
        ti.id item_id,
        ti.item_name,
        s.item_fraction
        FROM
        test_items ti
        INNER JOIN test_item_score s ON s.test_item_id = ti.id
        INNER JOIN test_category_item tci ON tci.item_id = ti.id
        INNER JOIN test_item_category tic ON tci.item_category_id = tic.id
        <where>
            <if test="studentId != null ">
                and s.student_id = #{studentId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and s.batch_code = #{batchCode}
            </if>
            and s.del_flag = 0 and ti.item_code &lt;&gt; #{itemCode}
        </where>
        order by tic.id desc
    </select>

    <select id="selectThisActionDevelopmentVO"
            parameterType="com.xinyilu.system.domain.vo.testItemAvgScore.ThisActionDevelopmentVO"
            resultType="com.xinyilu.system.domain.vo.testItemAvgScore.ThisActionDevelopmentVO">
        SELECT
        tic.id item_category_id,
        tic.category_name,
        ti.item_name,
        IFNULL( SUM(s.item_fraction), 0 ) item_fraction
        FROM
        test_items ti
        INNER JOIN test_item_score s ON s.test_item_id = ti.id
        INNER JOIN test_category_item tci ON tci.item_id = ti.id
        INNER JOIN test_item_category tic ON tci.item_category_id = tic.id
        <where>
            <if test="studentId != null ">
                and s.student_id = #{studentId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and s.batch_code = #{batchCode}
            </if>
            and s.del_flag = 0 and ti.item_code &lt;&gt; #{itemCode}
        </where>
        group By tic.id
        order by tic.id desc
    </select>


    <select id="getMotionPrescriptionLibraryList"
            parameterType="com.xinyilu.system.domain.vo.motionprescriptionlibrary.MotionPrescriptionLibraryVO"
            resultType="com.xinyilu.system.domain.vo.motionprescriptionlibrary.MotionPrescriptionLibraryVO">

                SELECT id,
                       physical_quality_name,
                       degree_start_fraction,
                       degree_end_fraction,
                       motion_type,
                       motion_frequency,
                       motion_strength,
                       motion_time,
                       motion_capacity,
                       advanced_time,
                       advanced_scheme
                FROM motion_prescription_library

    </select>

    <select id="getMotionLibraryAndVideoList"
            resultMap="MotionPrescriptionVideo">

                SELECT msv.prescription_video_url,
                       msv.id motionPrescriptionVideoId,
                       mpl.id,
                       mpl.physical_quality_name,
                       mpl.degree_start_fraction,
                       mpl.degree_end_fraction,
                       mpl.motion_type,
                       mpl.motion_frequency,
                       mpl.motion_strength,
                       mpl.motion_time,
                       mpl.motion_capacity,
                       mpl.advanced_time,
                       mpl.advanced_scheme
                FROM motion_prescription_library mpl
                         LEFT JOIN motion_prescription_video msv ON mpl.id = msv.motion_prescription_id

    </select>

    <select id="getThisActionDevelopmentCount"
            parameterType="com.xinyilu.system.domain.vo.testItemAvgScore.ThisActionDevelopmentVO"
            resultType="integer">
        SELECT
        COUNT(1)
        FROM
        test_items ti
        INNER JOIN test_item_score s ON s.test_item_id = ti.id
        INNER JOIN test_category_item tci ON tci.item_id = ti.id
        INNER JOIN test_item_category tic ON tci.item_category_id = tic.id
        <where>
            <if test="studentIdList != null and studentIdList.size() > 0">
                AND s.student_id in
                <foreach item="studentId" collection="studentIdList" open="(" separator="," close=")">
                    #{studentId}
                </foreach>
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and s.batch_code = #{batchCode}
            </if>
            and s.del_flag = 0 and ti.item_code &lt;&gt; #{itemCode}
        </where>
        group By tic.id
        order by tic.id desc
    </select>

    <insert id="importTestItemScoreList">
        insert into test_item_score(
        user_id,
        class_id,
        student_id,
        test_item_id,
        score_dict_id,
        batch_code,
        item_score,
        item_fraction,
        synthetical_assess,
        del_flag,
        create_by,
        create_time
        ) values
        <foreach item="itemScore" index="index" collection="list" separator=",">
            (
            #{itemScore.userId},
            #{itemScore.classId},
            #{itemScore.studentId},
            #{itemScore.testItemId},
            #{itemScore.scoreDictId},
            #{itemScore.batchCode},
            #{itemScore.itemScore},
            #{itemScore.itemFraction},
            #{itemScore.syntheticalAssess},
            #{itemScore.delFlag},
            #{itemScore.createBy},
            #{itemScore.createTime}
            )
        </foreach>
    </insert>

    <select id="selectStudentIdByKindergartenId" resultType="com.xinyilu.system.domain.studentInfo.StudentInfo">
        select id,
               class_id,
               student_number
        from student_info
        where kindergarten_id = #{kindergartenId}
          and del_flag = 0
    </select>

    <select id="selectTestItemScore" resultType="com.xinyilu.system.domain.dto.ImportTestProjectScoresDTO">
        select
        tis.student_id,
        tis.test_item_id,
        ti.item_code,
        si.student_number
        from test_item_score tis
        LEFT JOIN student_info si on si.id = tis.id
        LEFT JOIN test_items ti on ti.id = tis.test_item_id
        <where>
            <if test="studentIds != null and studentIds.size() > 0">
                AND tis.student_id in
                <foreach item="studentIds" collection="studentIds" open="(" separator="," close=")">
                    #{studentIds}
                </foreach>
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
            and tis.del_flag = 0
        </where>
    </select>

    <select id="selectTestItemScoresByStudentIds"
            resultType="com.xinyilu.system.domain.vo.TestItemScoreUpdateVO">
        SELECT
        tis.id,
        tis.class_id,
        tis.student_id,
        tis.test_item_id,
        tis.batch_code,
        IFNULL( tis.item_score, 0 ) itemScore,
        IFNULL( tis.item_fraction, 0 ) itemFraction,
        ti.item_code,
        tic.parent_id testItemCategoryParentId,
        ( SELECT category_name FROM test_item_category WHERE id = tic.parent_id ) parentName,
        tic.category_name categoryName,
        tic.id testItemCategoryId
        FROM
        test_item_score tis
        LEFT JOIN test_items ti ON ti.id = tis.test_item_id
        LEFT JOIN test_category_item tci ON tci.item_id = ti.id
        LEFT JOIN test_item_category tic ON tic.id = tci.item_category_id
        <where>
            tis.del_flag = 0
            <if test="studentIds != null and studentIds.size() > 0">
                AND tis.student_id in
                <foreach item="studentIds" collection="studentIds" open="(" separator="," close=")">
                    #{studentIds}
                </foreach>
            </if>
            <if test="classId != null ">
                and tis.class_id = #{classId}
            </if>
            <if test="batchCode != null and batchCode != ''">
                and tis.batch_code = #{batchCode}
            </if>
        </where>
    </select>
</mapper>
