<?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.applet.AppletMapper">

    <select id="getParentInfoByPhone" parameterType="string"
            resultType="com.xinyilu.system.domain.parentInfo.ParentInfo">
        SELECT
        id,
        openid,
        unionid,
        parent_name,
        parent_sex,
        phone
        FROM
        parent_info
        <where>
            del_flag = 0
            <if test="phone != null and phone != ''">
                and phone = #{phone}
            </if>
        </where>
    </select>

    <select id="getParentAndStudentInfo" parameterType="long"
            resultType="com.xinyilu.system.domain.applet.ParentAndStudentInfo">
        SELECT
        si.id student_id,
        si.student_name,
        si.student_sex,
        si.birth_date,
        ci.id classId,
        ci.class_name,
        ci.class_teacher,
        ci.phone,
        ki.kindergarten_name,
        ki.id kindergarten_id,
        ki.kindergarten_address,
        ki.kindergarten_type,
        ki.phone kindergartenPhone,
        ki.principal_id,
        ( SELECT user_name FROM sys_user WHERE user_id = ki.principal_id ) principalName,
        ( SELECT count( 1 ) FROM test_item_score WHERE student_id = si.id AND del_flag = 0 ) studentItemCount
        FROM
        student_info si
        INNER JOIN parent_student_info psi ON si.id = psi.student_id
        INNER JOIN class_info ci ON si.class_id = ci.id
        INNER JOIN kindergarten_info ki ON ci.kindergarten_id = ki.id
        <where>
            si.del_flag = 0
            <if test="parentId != null">
                AND psi.parent_id = #{parentId}
            </if>
        </where>
    </select>

    <select id="getStudentInfoByCardNo" parameterType="string" resultType="studentInfo">
        select id, student_name, student_number as studentCount
        from student_info
        where card_number = #{cardNo}
    </select>

    <select id="getParentByStudentIdAndName" resultType="parentInfo">
        SELECT
        pi.id,
        pi.parent_name
        FROM
        parent_info pi
        INNER JOIN parent_student_info psi ON pi.id = psi.parent_id
        <where>
            pi.del_flag = 0
            <if test="studentId != null">
                AND psi.student_id = #{studentId}
            </if>
            <if test="parentName != null and parentName != ''">
                AND pi.parent_name = #{parentName}
            </if>
        </where>
    </select>

    <select id="getAppletStudentScoreById" parameterType="long"
            resultType="com.xinyilu.system.domain.applet.AppletStudentScore">
        SELECT
        ts.batch_code,
        tb.batch_name,
        tb.create_time,
        tb.create_time batchDate,
        IFNULL( SUM( ts.item_fraction ), 0 ) totalFraction,
        pcr.id,
        pcr.download_address,
        (select student_name from student_info where id = ts.student_id) student_name
        FROM
        test_item_score ts
        LEFT JOIN test_score_batch tb ON ts.batch_code = tb.batch_code
        LEFT JOIN personal_check_report pcr ON ts.student_id = pcr.student_id AND ts.batch_code = pcr.batch_code AND
        pcr.check_status = 1
        <where>
            ts.del_flag = 0
            <if test="studentIdList != null and studentIdList.size() > 0">
                AND ts.student_id in
                <foreach item="studentId" collection="studentIdList" open="(" separator="," close=")">
                    #{studentId}
                </foreach>
            </if>
        </where>
        GROUP BY
        ts.student_id, ts.batch_code
        ORDER BY
        ts.create_time DESC
    </select>

    <select id="getParentInfoByOpenId" parameterType="string"
            resultType="com.xinyilu.system.domain.parentInfo.ParentInfo">
        SELECT
        id,
        openid,
        unionid,
        parent_name,
        parent_sex,
        phone
        FROM
        parent_info
        <where>
            del_flag = 0
            <if test="openId != null and openId != ''">
                and openid = #{openId}
            </if>
        </where>
    </select>

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

    <select id="getChildrenResultScore"
            resultType="AppletChildrenResultScoreInfo">
        SELECT
        ts.student_id,
        (select student_name from student_info where id = ts.student_id) student_name,
        ts.batch_code,
        tb.batch_name,
        tb.create_time,
        tb.create_time batchDate,
        IFNULL( SUM( ts.item_fraction ), 0 ) totalFraction,
        pcr.id,
        pcr.download_address
        FROM
        test_item_score ts
        LEFT JOIN test_items ti ON ti.id = ts.test_item_id
        LEFT JOIN test_score_batch tb ON ts.batch_code = tb.batch_code
        LEFT JOIN personal_check_report pcr ON ts.student_id = pcr.student_id
        AND ts.batch_code = pcr.batch_code
        AND pcr.check_status = 1
        <where>
            ts.del_flag = 0
            <if test="studentIdList != null and studentIdList.size() > 0">
                AND ts.student_id in
                <foreach item="studentId" collection="studentIdList" open="(" separator="," close=")">
                    #{studentId}
                </foreach>
            </if>
            <if test="itemCode != null  and itemCode != ''">
                AND ti.item_code &lt;&gt; #{itemCode}
            </if>
        </where>
        GROUP BY
        ts.student_id, ts.batch_code
        ORDER BY
        ts.student_id DESC, ts.create_time DESC
    </select>
</mapper>
