<?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.personalcheckreport.PersonalCheckReportMapper">

    <resultMap type="com.xinyilu.system.domain.vo.personalcheckreport.PersonalCheckReportVO"
               id="PersonalCheckReportResult">
        <result property="id" column="id"/>
        <result property="studentId" column="student_id"/>
        <result property="batchCode" column="batch_code"/>
        <result property="reportCode" column="report_code"/>
        <result property="reportName" column="report_name"/>
        <result property="syntheticalAssess" column="synthetical_assess"/>
        <result property="physicalAnalysisExplain" column="physical_analysis_explain"/>
        <result property="unqualifyItemExplain" column="unqualify_item_explain"/>
        <result property="downloadAddress" column="download_address"/>
        <result property="prescriptionAdvice" column="prescription_advice"/>
        <result property="motionLibraryId" column="motion_library_id"/>
        <result property="checkUserId" column="check_user_id"/>
        <result property="checkTime" column="check_time"/>
        <result property="checkStatus" column="check_status"/>
        <result property="delFlag" column="del_flag"/>
        <result property="studentName" column="student_name"/>
        <result property="batchName" column="batch_name"/>
        <result property="checkUserName" column="check_user_name"/>
    </resultMap>

    <sql id="selectPersonalCheckReportVo">
        select id, student_id, batch_code, report_code, report_name, synthetical_assess, physical_analysis_explain, unqualify_item_explain, download_address, prescription_advice, motion_library_id, check_user_id, check_time, check_status, del_flag, create_by, create_time, update_by, update_time from personal_check_report
    </sql>

    <select id="selectPersonalCheckReportList"
            parameterType="com.xinyilu.system.domain.vo.personalcheckreport.PersonalCheckReportVO"
            resultMap="PersonalCheckReportResult">
        SELECT
        pcr.id,
        tic.student_id,
        si.student_name,
        tic.batch_code,
        tsb.batch_name,
        pcr.report_code,
        pcr.report_name,
        pcr.download_address,
        pcr.check_user_id,
        u.user_name check_user_name,
        pcr.check_time,
        pcr.check_status
        FROM
        test_item_score tic
        LEFT JOIN student_info si ON si.id = tic.student_id
        LEFT JOIN personal_check_report pcr ON pcr.batch_code = tic.batch_code AND pcr.student_id = tic.student_id
        LEFT JOIN test_score_batch tsb ON tsb.batch_code = tic.batch_code
        LEFT JOIN sys_user u ON pcr.check_user_id = u.user_id
        <where>
            tic.del_flag = 0
            <if test="studentId != null ">
                and tic.student_id = #{studentId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and tic.batch_code = #{batchCode}
            </if>
            <if test="reportCode != null  and reportCode != ''">
                and pcr.report_code = #{reportCode}
            </if>
            <if test="reportName != null  and reportName != ''">
                and pcr.report_name like concat('%', #{reportName}, '%')
            </if>
            <if test="checkStatus != null ">
                and pcr.check_status = #{checkStatus}
            </if>
            <if test="batchName != null and batchName != ''">
                and tsb.batch_name like concat('%', #{batchName}, '%')
            </if>
        </where>
        GROUP BY
        tic.student_id, tic.batch_code
        order by pcr.create_time desc
    </select>

    <select id="selectPersonalCheckReportAll"
            parameterType="com.xinyilu.system.domain.vo.personalcheckreport.PersonalExamineAndApproveVO"
            resultType="com.xinyilu.system.domain.dto.PersonalExamineAndApproveDTO">
        SELECT
        si.kindergarten_id,
        si.class_id,
        tic.student_id,
        si.student_name,
        tic.batch_code,
        tsb.batch_name
        FROM
        test_item_score tic
        LEFT JOIN student_info si ON si.id = tic.student_id
        LEFT JOIN test_score_batch tsb ON tsb.batch_code = tic.batch_code
        <where>
            tic.del_flag = 0
            <if test="classId != null ">
                and si.class_id = #{classId}
            </if>
            <if test="kindergartenId != null ">
                and si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and tic.batch_code = #{batchCode}
            </if>
        </where>
        GROUP BY
        tic.student_id, tic.batch_code
    </select>
    <select id="selectApprovedReportPass"
            parameterType="com.xinyilu.system.domain.vo.personalcheckreport.PersonalExamineAndApproveVO"
            resultType="com.xinyilu.system.domain.dto.PersonalExamineAndApproveDTO">
        SELECT
        pcr.student_id,
        pcr.batch_code,
        pcr.report_name
        FROM
        personal_check_report pcr
        LEFT JOIN student_info si ON si.id = pcr.student_id
        <where>
            pcr.del_flag = 0
            <if test="checkStatus != null ">
                and pcr.check_status = #{checkStatus}
            </if>
            <if test="classId != null ">
                and si.class_id = #{classId}
            </if>
            <if test="kindergartenId != null ">
                and si.kindergarten_id = #{kindergartenId}
            </if>
            <if test="batchCode != null  and batchCode != ''">
                and pcr.batch_code = #{batchCode}
            </if>
        </where>
    </select>

    <select id="selectPersonalCheckReportById" parameterType="Long"
            resultMap="PersonalCheckReportResult">
        <include refid="selectPersonalCheckReportVo"/>
        where id = #{id}
    </select>

    <insert id="insertPersonalCheckReport" parameterType="PersonalCheckReport" useGeneratedKeys="true"
            keyProperty="id">
        insert into personal_check_report
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="studentId != null">student_id,
            </if>
            <if test="batchCode != null">batch_code,
            </if>
            <if test="reportCode != null">report_code,
            </if>
            <if test="reportName != null">report_name,
            </if>
            <if test="syntheticalAssess != null">synthetical_assess,
            </if>
            <if test="physicalAnalysisExplain != null">physical_analysis_explain,
            </if>
            <if test="unqualifyItemExplain != null">unqualify_item_explain,
            </if>
            <if test="downloadAddress != null">download_address,
            </if>
            <if test="prescriptionAdvice != null">prescription_advice,
            </if>
            <if test="motionLibraryId != null">motion_library_id,
            </if>
            <if test="checkUserId != null">check_user_id,
            </if>
            <if test="checkTime != null">check_time,
            </if>
            <if test="checkStatus != null">check_status,
            </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="studentId != null">#{studentId},
            </if>
            <if test="batchCode != null">#{batchCode},
            </if>
            <if test="reportCode != null">#{reportCode},
            </if>
            <if test="reportName != null">#{reportName},
            </if>
            <if test="syntheticalAssess != null">#{syntheticalAssess},
            </if>
            <if test="physicalAnalysisExplain != null">#{physicalAnalysisExplain},
            </if>
            <if test="unqualifyItemExplain != null">#{unqualifyItemExplain},
            </if>
            <if test="downloadAddress != null">#{downloadAddress},
            </if>
            <if test="prescriptionAdvice != null">#{prescriptionAdvice},
            </if>
            <if test="motionLibraryId != null">#{motionLibraryId},
            </if>
            <if test="checkUserId != null">#{checkUserId},
            </if>
            <if test="checkTime != null">#{checkTime},
            </if>
            <if test="checkStatus != null">#{checkStatus},
            </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="updatePersonalCheckReport" parameterType="PersonalCheckReport">
        update personal_check_report
        <trim prefix="SET" suffixOverrides=",">
            <if test="studentId != null">student_id =
                #{studentId},
            </if>
            <if test="batchCode != null">batch_code =
                #{batchCode},
            </if>
            <if test="reportCode != null">report_code =
                #{reportCode},
            </if>
            <if test="reportName != null">report_name =
                #{reportName},
            </if>
            <if test="syntheticalAssess != null">synthetical_assess =
                #{syntheticalAssess},
            </if>
            <if test="physicalAnalysisExplain != null">physical_analysis_explain =
                #{physicalAnalysisExplain},
            </if>
            <if test="unqualifyItemExplain != null">unqualify_item_explain =
                #{unqualifyItemExplain},
            </if>
            <if test="downloadAddress != null">download_address =
                #{downloadAddress},
            </if>
            <if test="prescriptionAdvice != null">prescription_advice =
                #{prescriptionAdvice},
            </if>
            <if test="motionLibraryId != null">motion_library_id =
                #{motionLibraryId},
            </if>
            <if test="checkUserId != null">check_user_id =
                #{checkUserId},
            </if>
            <if test="checkTime != null">check_time =
                #{checkTime},
            </if>
            <if test="checkStatus != null">check_status =
                #{checkStatus},
            </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="deletePersonalCheckReportById" parameterType="Long">
        delete from personal_check_report where id = #{id}
    </delete>

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

    <select id="getPersonalCheckReportInfo"
            resultType="com.xinyilu.system.domain.vo.personalcheckreport.ExistCheckReportVO">
        select id, download_address from personal_check_report where student_id =#{studentId} and batch_code = #{batchCode}
    </select>

    <select id="getCheckReportCount" resultType="int">
        select count(1) from personal_check_report where student_id = #{studentId} and batch_code = #{batchCode}
    </select>
</mapper>
