<?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.testor.module.train.course.dao.TTrainSmallNodeDao">

    <select id="getTreadInformation" resultType="com.testor.module.train.course.model.domain.TTrainSmallNode">
        SELECT * FROM t_train_small_node as ttsn
        left join t_train_my_management_read as ttmmr on ttsn.id = ttmmr.node_id and ttmmr.learner = #{loginUserId}
        <where>
            <if test="sectionIds.size > 0 and sectionIds != null">
                ttsn.section_id in
                <foreach collection="sectionIds" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and status = '0'
        </where>
        ORDER BY finish_time desc
    </select>
    <select id="getExaminationInformation"
            resultType="com.testor.module.train.course.model.domain.TTrainSmallNode">
        SELECT * FROM t_train_small_node as ttsn
        left join t_train_my_management_read as ttmmr on ttsn.id = ttmmr.node_id and ttmmr.learner = #{loginUserId} and
        management_id = #{managementId}
        <where>
            <if test="sectionIds.size > 0 and sectionIds != null">
                ttsn.section_id in
                <foreach collection="sectionIds" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and status = '0'
        </where>
        ORDER BY finish_time asc
    </select>

    <select id="getPercentage" resultType="java.lang.String">
        SELECT CONCAT(FORMAT((SELECT count(*) FROM t_train_my_management_read as ttmmr
        where ttmmr.learner = #{loginUserId} and management_id = #{managementId} and IFNULL(node_id, '') <![CDATA[<>]]> ''
        ) / (SELECT count(*) FROM t_train_small_node as ttsn
        <where>
            <if test="sectionIds.size > 0 and sectionIds != null">
                ttsn.section_id in
                <foreach collection="sectionIds" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and status = '0'
        </where>
        ), 2) * 100, '%') AS percentage
    </select>


    <select id="getPercentageByNodeIds" resultType="java.lang.String">
        SELECT CONCAT(
        FORMAT(
        (
        SELECT COUNT(*)
        FROM t_train_my_management_read AS ttmmr
        WHERE ttmmr.learner = #{loginUserId}
        AND management_id = #{managementId}
        <if test="nodeIds != null and nodeIds.size() > 0">
            AND node_id IN
            <foreach collection="nodeIds" item="nodeId" separator="," open="(" close=")">
                #{nodeId}
            </foreach>
        </if>
        AND IFNULL(node_id, '') &lt;&gt; ''
        ) / (
        SELECT COUNT(*)
        FROM t_train_small_node AS ttsn
        <where>
            <if test="sectionIds != null and sectionIds.size() > 0">
                AND ttsn.section_id IN
                <foreach collection="sectionIds" item="sectionId" separator="," open="(" close=")">
                    #{sectionId}
                </foreach>
            </if>
            AND status = '0'
        </where>
        ),
        2
        ) * 100, '%'
        ) AS percentage
    </select>

</mapper>