<?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.zhongzheng.modules.user.mapper.UserStudyRecordMapper">

    <resultMap type="com.zhongzheng.modules.user.domain.UserStudyRecord" id="UserStudyRecordResult">
        <result property="recordId" column="record_id"/>
        <result property="userId" column="user_id"/>
        <result property="courseId" column="course_id"/>
        <result property="sectionId" column="section_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="studyDuration" column="study_duration"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="coverUrl" column="cover_url"/>
        <result property="courseName" column="course_name"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.GoodsStudyRecordVo" id="GoodsStudyRecordVoResult">
        <result property="userId" column="user_id"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="goodsId" column="goods_id"/>
        <result property="gradeId" column="grade_id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="code" column="code"/>
        <result property="year" column="year"/>
        <result property="classHours" column="class_hours"/>
        <result property="studyStartTime" column="study_start_time"/>
        <result property="studyEndTime" column="study_end_time"/>
        <result property="businessName" column="business_name"/>
        <result property="projectName" column="project_name"/>
        <result property="schoolName" column="school_name"/>
        <result property="educationName" column="education_name"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.SubjectStudyRecordVo" id="SubjectStudyRecordVoResult">
        <result property="userId" column="user_id"/>
        <result property="subjectId" column="subject_id"/>
        <result property="courseId" column="course_id"/>
        <result property="subjectName" column="subject_name"/>
        <result property="courseName" column="course_name"/>
        <result property="beginStatus" column="begin_status"/>
        <result property="moduleNum" column="module_num"/>
        <result property="chapterNum" column="chapter_num"/>
        <result property="sectionNum" column="section_num"/>
        <result property="recordNum" column="record_num"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.SectionStudyRecordVo" id="SectionStudyRecordVoResult">
        <result property="sectionId" column="section_id"/>
        <result property="moduleName" column="module_name"/>
        <result property="chapterName" column="chapter_name"/>
        <result property="sectionName" column="section_name"/>
        <result property="durationTime" column="duration_time"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="status" column="status"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.ExamStudyRecordVo" id="ExamStudyRecordVoResult">
        <result property="goodsId" column="goods_id"/>
        <result property="userId" column="user_id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="year" column="year"/>
        <result property="classHours" column="class_hours"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="studyStartTime" column="study_start_time"/>
        <result property="studyEndTime" column="study_end_time"/>
        <result property="businessName" column="business_name"/>
        <result property="projectName" column="project_name"/>
        <result property="schoolName" column="school_name"/>
        <result property="educationName" column="education_name"/>
        <result property="examNum" column="exam_num"/>
        <result property="stuAllNum" column="stu_all_num"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.ExamSonStudyRecordVo" id="ExamSonStudyRecordVoResult">
        <result property="questionId" column="question_id"/>
        <result property="questionName" column="question_name"/>
        <result property="moduleNum" column="module_num"/>
        <result property="chapterNum" column="chapter_num"/>
        <result property="examNum" column="exam_num"/>
        <result property="subjectName" column="subject_name"/>
        <result property="questionNum" column="question_num"/>
        <result property="examType" column="exam_type"/>
        <result property="recordNum" column="record_num"/>
    </resultMap>



    <select id="selectList" parameterType="com.zhongzheng.modules.user.bo.UserStudyRecordQueryBo"  resultMap="UserStudyRecordResult">
        SELECT DISTINCT
        temp.course_id,
        c.cover_url,
        c.course_name
        FROM
        ( SELECT course_id FROM user_study_record WHERE 1 = 1
        <if test="userId != null and userId != ''">
            AND  user_id = #{userId}
        </if>
        ORDER BY record_id DESC LIMIT 30 ) temp
        LEFT JOIN course c ON temp.course_id = c.course_id


    </select>

    <select id="selectUserStudy" parameterType="map"  resultMap="UserStudyRecordResult">
        select * FROM user_study_record where 1=1
        <if test="userId != null and userId != ''">
            and user_id =#{userId}
        </if>
        <if test="courseId != null and courseId !=''">
            and course_id = #{courseId}
        </if>
        and study_duration > 360
    </select>

    <select id="queryGoods" parameterType="com.zhongzheng.modules.user.bo.UserQueryBo"  resultMap="GoodsStudyRecordVoResult">
        SELECT
        g.goods_name,
        g.`code`,
        g.`year`,
        g.class_hours,
        g.study_start_time,
        g.study_end_time,
        g.goods_id,
        u.grade_id,
        u.user_id,
        (SELECT i.business_name FROM course_business i where i.id = g.business_id) as business_name,
        (SELECT p.project_name FROM course_project_type p where p.id = g.business_id) as project_name,
        (SELECT s.school_name FROM school s  where s.id = g.school_id) as school_name,
        (SELECT e.education_name FROM course_education_type e  where e.id = g.education_type_id) as education_name,
        (SELECT r.start_time FROM user_study_record r LEFT JOIN goods_course c on r.course_id= c.course_id where 1=1 AND g.goods_id=c.goods_id ORDER BY r.start_time ASC LIMIT 1) as start_time,
        (SELECT r.end_time FROM user_study_record r LEFT JOIN goods_course c on r.course_id= c.course_id where 1=1 AND g.goods_id=c.goods_id ORDER BY r.start_time DESC LIMIT 1) as end_time
        FROM
        class_grade_user u
        LEFT JOIN class_grade_goods d ON u.grade_id = d.grade_id
        LEFT JOIN goods g on g.goods_id = d.goods_id
        where 1=1
        <if test="userId != null and userId !=''">
            and u.user_id = #{userId}
        </if>
    </select>


    <select id="listSubject" parameterType="com.zhongzheng.modules.user.bo.SubjectStudyRecordQueryBo"  resultMap="SubjectStudyRecordVoResult">
        SELECT
        #{userId} as user_id,
        r.subject_id,
        r.course_id,
        r.course_name,
        (SELECT subject_name FROM course_subject s where  s.id = r.subject_id) as subject_name,
        (CASE WHEN (SELECT COUNT(id) FROM course_menu m where  m.course_id = r.course_id and m.type in(1,2,3))> 0 THEN '1' ELSE '0' END) as begin_status,
        (SELECT COUNT(id) FROM course_menu m where  m.course_id = r.course_id and m.type in(1)) as module_num,
        (SELECT COUNT(id)+(SELECT COUNT(p.id) FROM course_module_chapter p LEFT JOIN course_menu m  on m.menu_id = p.module_id where  m.course_id = r.course_id and m.type in(1)) FROM course_menu m where  m.course_id = r.course_id and m.type in(2)) as chapter_num,
        (SELECT COUNT(id)+(SELECT COUNT(n.id) FROM course_chapter_section n LEFT JOIN course_module_chapter p on n.chapter_id = p.chapter_id LEFT JOIN course_menu m on m.menu_id = p.module_id where m.course_id =  r.course_id and m.type in(1))+(SELECT COUNT(n.id) FROM course_chapter_section n  LEFT JOIN course_menu m on m.menu_id = n.chapter_id where m.course_id = r.course_id and m.type in(2)) FROM course_menu m where  m.course_id = r.course_id and m.type in(3)) as section_num,
        (select COUNT(DISTINCT section_id) FROM user_study_record c where c.course_id = r.course_id and c.user_id=#{userId} and c.status = 1 and c.grade_id = #{gradeId}) as record_num
        FROM
        course r
        LEFT JOIN goods_course c ON c.course_id = r.course_id
        where 1=1
        <if test="goodsId != null and goodsId !=''">
            and c.goods_id =#{goodsId}
        </if>
        <if test="courseId != null and courseId !=''">
            and c.course_id =#{courseId}
        </if>
    </select>

    <select id="listSection" parameterType="com.zhongzheng.modules.user.bo.SubjectStudyRecordQueryBo"  resultMap="SectionStudyRecordVoResult">
        SELECT
            s.section_id,
            s.name as section_name,
            NULL as chapter_name,
            NULL as module_name,
            s.duration_time,
            (select i.start_time FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
            ORDER BY i.start_time ASC LIMIT 1) as start_time,
            (select i.end_time FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
            ORDER BY i.start_time DESC LIMIT 1) as end_time,
            (select i.`status` FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        LIMIT 1) as `status`
        FROM
            course_menu m
            LEFT JOIN course_section s ON m.menu_id = s.section_id
        WHERE
            m.type =3
        <if test="courseId != null and courseId !=''">
            and m.course_id=#{courseId}
        </if>
        UNION
        SELECT
            s.section_id,
            s.name as section_name,
            (SELECT t.`name` FROM course_chapter t where m.menu_id = t.chapter_id ) as chapter_name,
            NULL as module_name,
            s.duration_time,
        (select i.start_time FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        ORDER BY i.start_time ASC LIMIT 1) as start_time,
        (select i.end_time FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        ORDER BY i.start_time DESC LIMIT 1) as end_time,
        (select i.`status` FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        LIMIT 1) as `status`
        FROM
            course_chapter_section c
            LEFT JOIN course_menu m on m.menu_id = c.chapter_id
            LEFT JOIN course_section s ON c.section_id = s.section_id
        WHERE
            m.type =2
        <if test="courseId != null and courseId !=''">
            and m.course_id=#{courseId}
        </if>
        UNION
        SELECT
            s.section_id,
            s.name as section_name,
            (SELECT t.`name` FROM course_chapter t where e.chapter_id = t.chapter_id ) as chapter_name,
            (SELECT t.module_name FROM course_module t where e.module_id = t.module_id ) as module_name,
            s.duration_time,
        (select i.start_time FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        ORDER BY i.start_time ASC LIMIT 1) as start_time,
        (select i.end_time FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        ORDER BY i.start_time DESC LIMIT 1) as end_time,
        (select i.`status` FROM user_study_record i where m.menu_id = i.section_id
        <if test="userId != null and userId !=''">
            and i.user_id = #{userId}
        </if>
        LIMIT 1) as `status`
        FROM
            course_chapter_section c
            LEFT JOIN course_module_chapter e on c.chapter_id = e.chapter_id
            LEFT JOIN course_menu m on m.menu_id = e.module_id
            LEFT JOIN course_section s ON c.section_id = s.section_id
        WHERE
            m.type =1
        <if test="courseId != null and courseId !=''">
            and m.course_id=#{courseId}
        </if>

    </select>


    <select id="sectionType" parameterType="map"  resultType="Long">
        SELECT
            s.section_id
        FROM
            course_menu m
                LEFT JOIN course_section s ON m.menu_id = s.section_id
        WHERE
            m.type =3
          and m.course_id=#{courseId}
          and s.section_type=#{sectionType}
        UNION
        SELECT
            s.section_id
        FROM
            course_chapter_section c
                LEFT JOIN course_menu m on m.menu_id = c.chapter_id
                LEFT JOIN course_section s ON c.section_id = s.section_id
        WHERE
            m.type =2
          and m.course_id=#{courseId}
          and s.section_type=#{sectionType}
        UNION
        SELECT
            s.section_id
        FROM
            course_chapter_section c
                LEFT JOIN course_module_chapter e on c.chapter_id = e.chapter_id
                LEFT JOIN course_menu m on m.menu_id = e.module_id
                LEFT JOIN course_section s ON c.section_id = s.section_id
        WHERE
            m.type =1
          and m.course_id=#{courseId}
          and s.section_type=#{sectionType}
    </select>

    <select id="querExamStudy" parameterType="com.zhongzheng.modules.user.bo.UserQueryBo"  resultMap="ExamStudyRecordVoResult">
        SELECT
        DISTINCT g.goods_id,
        #{userId} as user_id,
        g.goods_name,
        g.`year`,
        g.`code`,
        g.class_hours,
        g.study_start_time,
        g.study_end_time,
        ( SELECT i.business_name FROM course_business i WHERE i.id = g.business_id ) AS business_name,
        ( SELECT p.project_name FROM course_project_type p WHERE p.id = g.business_id ) AS project_name,
        ( SELECT s.school_name FROM school s WHERE s.id = g.school_id ) AS school_name,
        ( SELECT e.education_name FROM course_education_type e WHERE e.id = g.education_type_id ) AS education_name,
        (SELECT COUNT(a.id)+(SELECT COUNT(a.id) FROM goods_attached e LEFT JOIN question_chapter_exam a on e.major_id = a.chapter_exam_id where e.goods_id = g.goods_id and e.type=2)+(SELECT COUNT(h.id) FROM goods_attached e LEFT JOIN question_module_chapter a on e.major_id = a.module_exam_id LEFT JOIN question_chapter_exam h on a.chapter_exam_id = h.chapter_exam_id  where e.goods_id = g.goods_id and e.type=1) FROM goods_attached a where a.goods_id=g.goods_id and a.type = 3) as exam_num,
        (SELECT create_time FROM user_bank_record r where r.goods_id=g.goods_id ORDER BY create_time ASC LIMIT 1) as start_time,
        (SELECT create_time FROM user_bank_record r where r.goods_id=g.goods_id ORDER BY create_time DESC LIMIT 1) as end_time,
        (SELECT COUNT(DISTINCT exam_id) FROM user_bank_record r where r.goods_id=g.goods_id) as stu_all_num
        FROM
        goods g
        LEFT JOIN order_goods s ON g.goods_id = s.goods_id
        LEFT JOIN `order` o ON o.order_sn = s.order_sn
        WHERE
        g.goods_type =2
        <if test="userId != null and userId !=''">
          AND o.user_id = #{userId}
        </if>
    </select>

    <select id="listExamSon" parameterType="com.zhongzheng.modules.user.bo.SubjectStudyRecordQueryBo"  resultMap="ExamSonStudyRecordVoResult">
        SELECT
        a.id as question_id,
        e.exam_name as question_name,
        (SELECT s.subject_name FROM question_business b LEFT JOIN course_subject s on b.subject_id= s.id where b.type =2 and b.major_id =a.major_id) as subject_name,
        0 as module_num,
        0 as chapter_num,
        1 as exam_num,
        (select COUNT(q.id) FROM exam_question q where e.exam_id = q.exam_id) as question_num,
        (SELECT COUNT(DISTINCT r.exam_id) FROM user_bank_record r where r.goods_id = a.goods_id and r.exam_id = e.exam_id  <if test="userId != null and userId !=''">
        AND r.user_id = #{userId}
    </if> ) as record_num
        FROM
        goods_attached a
        LEFT JOIN exam e ON a.major_id = e.exam_id
        WHERE
        a.type =3
        <if test="goodsId != null and goodsId !=''">
            and a.goods_id =#{goodsId}
        </if>
        UNION
        SELECT
        a.id as question_id,
        c.`name` as question_name,
        (SELECT s.subject_name FROM question_business b LEFT JOIN course_subject s on b.subject_id= s.id where b.type =3 and b.major_id =a.major_id) as subject_name,
        0 as module_num,
        1 as chapter_num,
        (SELECT COUNT(e.id) FROM question_chapter_exam e where c.chapter_exam_id=e.chapter_exam_id) as exam_num,
        (select COUNT(q.id) FROM exam_question q LEFT JOIN question_chapter_exam m  on m.exam_id = q.exam_id where m.chapter_exam_id = a.major_id) as question_num,
        (SELECT COUNT(DISTINCT r.exam_id) FROM user_bank_record r LEFT JOIN question_chapter_exam m on r.exam_id = m.exam_id  where r.goods_id = a.goods_id and m.chapter_exam_id=c.chapter_exam_id   <if test="userId != null and userId !=''">
        AND r.user_id = #{userId}
    </if> ) as record_num
        FROM
        goods_attached a
        LEFT JOIN question_chapter c ON a.major_id = c.chapter_exam_id
        WHERE
        a.type =2
        <if test="goodsId != null and goodsId !=''">
            and a.goods_id =#{goodsId}
        </if>
        UNION
        SELECT
        a.id as question_id,
        c.module_name as question_name,
        (SELECT s.subject_name FROM question_business b LEFT JOIN course_subject s on b.subject_id= s.id where b.type =4 and b.major_id =a.major_id) as subject_name,
        1 as module_num,
        (SELECT COUNT(e.id) FROM question_module_chapter e where c.module_exam_id=e.module_exam_id) as chapter_num,
        (SELECT COUNT(e.id) FROM question_chapter_exam e LEFT JOIN question_module_chapter r on e.chapter_exam_id = r.chapter_exam_id  where c.module_exam_id=r.module_exam_id) as exam_num,
        (select COUNT(q.id) FROM exam_question q LEFT JOIN question_chapter_exam m on m.exam_id = q.exam_id LEFT JOIN question_module_chapter r on m.chapter_exam_id = r.chapter_exam_id where r.module_exam_id = a.major_id) as question_num,
        (SELECT COUNT(DISTINCT r.exam_id) FROM user_bank_record r LEFT JOIN question_chapter_exam m on r.exam_id = m.exam_id LEFT JOIN question_module_chapter l on l.chapter_exam_id = m.chapter_exam_id where r.goods_id = a.goods_id and l.module_exam_id = a.major_id  <if test="userId != null and userId !=''">
        AND r.user_id = #{userId}
    </if> ) as record_num
        FROM
        goods_attached a
        LEFT JOIN question_module c ON a.major_id = c.module_exam_id
        WHERE
        a.type =1
        <if test="goodsId != null and goodsId !=''">
            and a.goods_id =#{goodsId}
        </if>

    </select>

    <select id="examTypes" parameterType="Long" resultType="String">
        SELECT
            ( SELECT DISTINCT ep.paper_name FROM exam_paper ep WHERE ep.paper_id = e.exam_paper_id ) AS paper_name
        FROM
            goods_attached a
                LEFT JOIN exam e ON a.major_id = e.exam_id
        WHERE
            a.type = 3
        <if test="id != null and id !=''">
            and a.id =#{id}
        </if>
        UNION
        SELECT
            ( SELECT DISTINCT ep.paper_name FROM exam_paper ep WHERE ep.paper_id = e.exam_paper_id ) AS paper_name
        FROM
            goods_attached a
                LEFT JOIN question_chapter c ON a.major_id = c.chapter_exam_id
                LEFT JOIN question_chapter_exam qce ON qce.chapter_exam_id = c.chapter_exam_id
                LEFT JOIN exam e ON e.exam_id = qce.exam_id
        WHERE
            a.type = 2
        <if test="id != null and id !=''">
            and a.id =#{id}
        </if>
        UNION
        SELECT
            ( SELECT DISTINCT ep.paper_name FROM exam_paper ep WHERE ep.paper_id = e.exam_paper_id ) AS paper_name
        FROM
            goods_attached a
                LEFT JOIN question_module c ON a.major_id = c.module_exam_id
                LEFT JOIN question_module_chapter qmc ON c.module_exam_id = qmc.module_exam_id
                LEFT JOIN question_chapter qc ON qmc.chapter_exam_id = qc.chapter_exam_id
                LEFT JOIN question_chapter_exam qce ON qce.chapter_exam_id = qc.chapter_exam_id
                LEFT JOIN exam e ON e.exam_id = qce.exam_id
        WHERE
            a.type = 1
        <if test="id != null and id !=''">
            and a.id =#{id}
        </if>
    </select>

    <select id="selectStudyRecord" parameterType="com.zhongzheng.modules.user.domain.UserBankRecord" resultType="Long">
        SELECT
        COUNT( 1 )
        FROM
        user_period up
        LEFT JOIN user_period_status ups on up.id = ups.period_id
        WHERE
        1 =1
        <choose>
            <when test="chapterId != null and chapterId !=''">
                AND up.chapter_id = #{chapterId}
            </when>
            <otherwise>
                AND up.chapter_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="sectionId != null and sectionId !=''">
                AND up.section_id = #{sectionId}
            </when>
            <otherwise>
                AND up.section_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="moduleId != null and moduleId !=''">
                AND up.module_id = #{moduleId}
            </when>
            <otherwise>
                AND up.module_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="courseId != null and courseId !=''">
                AND up.course_id = #{courseId}
            </when>
            <otherwise>
                AND up.course_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="gradeId != null and gradeId !=''">
                AND up.grade_id = #{gradeId}
            </when>
            <otherwise>
                AND up.grade_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="goodsId != null and goodsId !=''">
                AND up.goods_id = #{goodsId}
            </when>
            <otherwise>
                AND up.goods_id = 0
            </otherwise>
        </choose>
          AND up.user_id = #{userId}
    </select>

    <select id="selectStudyExamRecord" parameterType="com.zhongzheng.modules.user.vo.UserBankRecordVo" resultType="Long">
        SELECT
        COUNT( 1 )
        FROM
        user_period up
        LEFT JOIN user_period_status ups on up.id = ups.period_id
        WHERE
        1 =1
        <choose>
            <when test="chapterId != null and chapterId !=''">
                AND up.chapter_id = #{chapterId}
            </when>
            <otherwise>
                AND up.chapter_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="sectionId != null and sectionId !=''">
                AND up.section_id = #{sectionId}
            </when>
            <otherwise>
                AND up.section_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="moduleId != null and moduleId !=''">
                AND up.module_id = #{moduleId}
            </when>
            <otherwise>
                AND up.module_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="courseId != null and courseId !=''">
                AND up.course_id = #{courseId}
            </when>
            <otherwise>
                AND up.course_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="gradeId != null and gradeId !=''">
                AND up.grade_id = #{gradeId}
            </when>
            <otherwise>
                AND up.grade_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="goodsId != null and goodsId !=''">
                AND up.goods_id = #{goodsId}
            </when>
            <otherwise>
                AND up.goods_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="examId != null and examId !=''">
                AND up.exam_id = #{examId}
            </when>
            <otherwise>
                AND up.exam_id = 0
            </otherwise>
        </choose>
        AND up.user_id = #{userId}
    </select>

    <select id="selectPeriod" parameterType="com.zhongzheng.modules.user.domain.UserStudyRecord" resultType="Long">
    SELECT
    COUNT( 1 )
    FROM
    user_period up
    LEFT JOIN user_period_status ups on up.id = ups.period_id
    WHERE
    1 =1
    <choose>
        <when test="chapterId != null and chapterId !=''">
            AND up.chapter_id = #{chapterId}
        </when>
        <otherwise>
            AND up.chapter_id = 0
        </otherwise>
    </choose>
    <choose>
        <when test="sectionId != null and sectionId !=''">
            AND up.section_id = #{sectionId}
        </when>
        <otherwise>
            AND up.section_id = 0
        </otherwise>
    </choose>
    <choose>
        <when test="moduleId != null and moduleId !=''">
            AND up.module_id = #{moduleId}
        </when>
        <otherwise>
            AND up.module_id = 0
        </otherwise>
    </choose>
    <choose>
        <when test="courseId != null and courseId !=''">
            AND up.course_id = #{courseId}
        </when>
        <otherwise>
            AND up.course_id = 0
        </otherwise>
    </choose>
    <choose>
        <when test="gradeId != null and gradeId !=''">
            AND up.grade_id = #{gradeId}
        </when>
        <otherwise>
            AND up.grade_id = 0
        </otherwise>
    </choose>
    <choose>
        <when test="goodsId != null and goodsId !=''">
            AND up.goods_id = #{goodsId}
        </when>
        <otherwise>
            AND up.goods_id = 0
        </otherwise>
    </choose>
    AND up.user_id = #{userId}
    AND ups.period_status = 1
    AND ups.`status` =2
    AND ups.record_end_time is NOT NULL
    </select>

    <resultMap type="com.zhongzheng.modules.grade.domain.UserPeriodStatus" id="UserPeriodStatus">
        <result property="status" column="status"/>
        <result property="recordStartTime" column="record_start_time"/>
        <result property="recordEndTime" column="record_end_time"/>
        <result property="id" column="id"/>
        <result property="periodId" column="period_id"/>
        <result property="periodStatus" column="period_status"/>
    </resultMap>

    <select id="selectPeriodCount" parameterType="com.zhongzheng.modules.user.domain.UserStudyRecord" resultMap="UserPeriodStatus">
        SELECT
        ups.*
        FROM
        user_period up
        LEFT JOIN user_period_status ups on up.id = ups.period_id
        WHERE
        1 =1
        <choose>
            <when test="chapterId != null and chapterId !=''">
                AND up.chapter_id = #{chapterId}
            </when>
            <otherwise>
                AND up.chapter_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="sectionId != null and sectionId !=''">
                AND up.section_id = #{sectionId}
            </when>
            <otherwise>
                AND up.section_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="moduleId != null and moduleId !=''">
                AND up.module_id = #{moduleId}
            </when>
            <otherwise>
                AND up.module_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="courseId != null and courseId !=''">
                AND up.course_id = #{courseId}
            </when>
            <otherwise>
                AND up.course_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="gradeId != null and gradeId !=''">
                AND up.grade_id = #{gradeId}
            </when>
            <otherwise>
                AND up.grade_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="goodsId != null and goodsId !=''">
                AND up.goods_id = #{goodsId}
            </when>
            <otherwise>
                AND up.goods_id = 0
            </otherwise>
        </choose>
        AND up.user_id = #{userId}
        AND ups.period_status = 1
    </select>

    <select id="userPlanSave" parameterType="Long" resultType="Long">
        SELECT
            COUNT(1)
        FROM
            user_plan_goods upg
                LEFT JOIN user_plan up	on upg.plan_id = up.plan_id
        WHERE
            upg.source_id = #{goodsId}
          and up.`status` = 1
    </select>

    <select id="userPlanSaveId" parameterType="Long" resultType="Long">
        SELECT
            upg.plan_id
        FROM
            user_plan_goods upg
                LEFT JOIN user_plan up	on upg.plan_id = up.plan_id
        WHERE
            upg.source_id = #{goodsId}
          and up.`status` = 1
            LIMIT 1
    </select>

    <select id="selectPland" parameterType="com.zhongzheng.modules.user.domain.UserStudyRecord" resultType="Long">
        SELECT
            COUNT( 1 )
        FROM
            user_study_record usr
        WHERE
            1 = 1
        <choose>
            <when test="chapterId != null and chapterId !=''">
                AND usr.chapter_id = #{chapterId}
            </when>
            <otherwise>
                AND usr.chapter_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="sectionId != null and sectionId !=''">
                AND usr.section_id = #{sectionId}
            </when>
            <otherwise>
                AND usr.section_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="moduleId != null and moduleId !=''">
                AND usr.module_id = #{moduleId}
            </when>
            <otherwise>
                AND usr.module_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="courseId != null and courseId !=''">
                AND usr.course_id = #{courseId}
            </when>
            <otherwise>
                AND usr.course_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="gradeId != null and gradeId !=''">
                AND usr.grade_id = #{gradeId}
            </when>
            <otherwise>
                AND usr.grade_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="goodsId != null and goodsId !=''">
                AND usr.goods_id = #{goodsId}
            </when>
            <otherwise>
                AND usr.goods_id = 0
            </otherwise>
        </choose>
          AND usr.plan_id =#{planId}
    </select>

    <select id="getPlanGoods" parameterType="Long" resultType="Long">
        SELECT
            upg.source_id
        FROM
            user_plan_goods upg
                LEFT JOIN user_plan up	on upg.plan_id = up.plan_id
        WHERE
            upg.plan_id =#{planId}
          and up.`status` = 1
    </select>

    <select id="selectPlandCount" parameterType="map" resultType="Long">
        SELECT
            COUNT(1)
        FROM
            user_study_record usr
        WHERE 1=1
          and usr.plan_id = #{planId}
          and usr.goods_id = #{goodsId}
    </select>

    <select id="selectStudyExamRecordOr" parameterType="com.zhongzheng.modules.user.vo.UserBankRecordVo" resultType="Long">
        SELECT
        COUNT( 1 )
        FROM
        user_period up
        LEFT JOIN user_period_status ups on up.id = ups.period_id
        WHERE
        1 =1
          and ups.period_status = 1
          and ups.status in (0,3)
        <choose>
            <when test="chapterId != null and chapterId !=''">
                AND up.chapter_id = #{chapterId}
            </when>
            <otherwise>
                AND up.chapter_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="sectionId != null and sectionId !=''">
                AND up.section_id = #{sectionId}
            </when>
            <otherwise>
                AND up.section_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="moduleId != null and moduleId !=''">
                AND up.module_id = #{moduleId}
            </when>
            <otherwise>
                AND up.module_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="courseId != null and courseId !=''">
                AND up.course_id = #{courseId}
            </when>
            <otherwise>
                AND up.course_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="gradeId != null and gradeId !=''">
                AND up.grade_id = #{gradeId}
            </when>
            <otherwise>
                AND up.grade_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="goodsId != null and goodsId !=''">
                AND up.goods_id = #{goodsId}
            </when>
            <otherwise>
                AND up.goods_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="examId != null and examId !=''">
                AND up.exam_id = #{examId}
            </when>
            <otherwise>
                AND up.exam_id = 0
            </otherwise>
        </choose>
        AND up.user_id = #{userId}
    </select>

    <select id="selectStudyExamRecordCount" parameterType="com.zhongzheng.modules.user.vo.UserBankRecordVo" resultMap="UserPeriodStatus">
        SELECT
        ups.*
        FROM
        user_period up
        LEFT JOIN user_period_status ups on up.id = ups.period_id
        WHERE
        1 =1
        <choose>
            <when test="chapterId != null and chapterId !=''">
                AND up.chapter_id = #{chapterId}
            </when>
            <otherwise>
                AND up.chapter_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="sectionId != null and sectionId !=''">
                AND up.section_id = #{sectionId}
            </when>
            <otherwise>
                AND up.section_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="moduleId != null and moduleId !=''">
                AND up.module_id = #{moduleId}
            </when>
            <otherwise>
                AND up.module_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="courseId != null and courseId !=''">
                AND up.course_id = #{courseId}
            </when>
            <otherwise>
                AND up.course_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="gradeId != null and gradeId !=''">
                AND up.grade_id = #{gradeId}
            </when>
            <otherwise>
                AND up.grade_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="goodsId != null and goodsId !=''">
                AND up.goods_id = #{goodsId}
            </when>
            <otherwise>
                AND up.goods_id = 0
            </otherwise>
        </choose>
        <choose>
            <when test="examId != null and examId !=''">
                AND up.exam_id = #{examId}
            </when>
            <otherwise>
                AND up.exam_id = 0
            </otherwise>
        </choose>
        AND up.user_id = #{userId}
        AND ups.period_status = 1
    </select>

    <select id="selectCountPlan" parameterType="Long" resultType="Long">
        SELECT COUNT(1) FROM user_study_record usr where usr.plan_id =#{planId}
    </select>
</mapper>
