<?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.UserExamRecordMapper">

    <resultMap type="com.zhongzheng.modules.user.domain.UserExamRecord" id="UserExamRecordResult">
        <result property="recordId" column="record_id"/>
        <result property="examId" column="exam_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="userId" column="user_id"/>
        <result property="status" column="status"/>
        <result property="goodsId" column="goods_id"/>
        <result property="reportStatus" column="report_status"/>
        <result property="performance" column="performance"/>
        <result property="historyExamJson" column="history_exam_json"/>
        <result property="totalQuestionNum" column="total_question_num"/>
        <result property="rightQuestionNum" column="right_question_num"/>
        <result property="doQuestionNum" column="do_question_num"/>
        <result property="moduleExamId" column="module_exam_id"/>
        <result property="chapterExamId" column="chapter_exam_id"/>
        <result property="doTime" column="do_time"/>
        <result property="examTime" column="exam_time"/>
        <result property="totalScore" column="total_score"/>
        <result property="doQuestionIds" column="do_question_ids"/>
        <result property="rightQuestionIds" column="right_question_ids"/>
        <result property="score" column="score"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.UserExamRecordVo" id="UserExamRecordVoResult">
        <result property="recordId" column="record_id"/>
        <result property="examId" column="exam_id"/>
        <result property="updateTime" column="update_time"/>
        <result property="userId" column="user_id"/>
        <result property="status" column="status"/>
        <result property="goodsId" column="goods_id"/>
        <result property="reportStatus" column="report_status"/>
        <result property="performance" column="performance"/>
        <result property="historyExamJson" column="history_exam_json"/>
        <result property="totalQuestionNum" column="total_question_num"/>
        <result property="rightQuestionNum" column="right_question_num"/>
        <result property="doQuestionNum" column="do_question_num"/>
        <result property="moduleExamId" column="module_exam_id"/>
        <result property="chapterExamId" column="chapter_exam_id"/>
        <result property="doQuestionIds" column="do_question_ids"/>
        <result property="rightQuestionIds" column="right_question_ids"/>

        <result property="examName" column="exam_name"/>
        <result property="paperName" column="paper_name"/>
        <result property="doTime" column="do_time"/>
        <result property="examTime" column="exam_time"/>
        <result property="totalScore" column="total_score"/>
        <result property="score" column="score"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.UserExamWrongRecordVo" id="UserExamRecordQuestionVoResult">
        <result property="wrongId" column="wrong_id"/>
        <result property="recordId" column="record_id"/>
        <result property="questionId" column="question_id"/>
        <result property="userId" column="user_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="examId" column="exam_id"/>


        <result property="examName" column="exam_name"/>
        <result property="wrongQuestionNum" column="wrong_question_num"/>
        <result property="type" column="type"/>
        <result property="num" column="num"/>

        <result property="content" column="content"/>
        <result property="type" column="type"/>
        <result property="answerQuestion" column="answer_question"/>
        <result property="status" column="status"/>
        <result property="analysisContent" column="analysis_content"/>
        <result property="imgUrl" column="img_url"/>
        <result property="jsonStr" column="json_str"/>
        <result property="prefixName" column="prefix_name"/>
        <result property="knowledgeIds" column="knowledge_ids"/>
        <result property="publishStatus" column="publish_status"/>
        <result property="code" column="code"/>

    </resultMap>

    <select id="selectList" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultMap="UserExamRecordVoResult">
        SELECT
            ue.*,
            e.exam_name,
            ep.paper_name
        FROM
            user_exam_record ue
                LEFT JOIN exam e ON ue.exam_id = e.exam_id
                LEFT JOIN exam_paper ep ON e.exam_paper_id = ep.paper_id
        WHERE
            ue.user_id = #{userId}
        <if test="paperId != null and paperId != ''">
            AND ep.paper_id = #{paperId}
        </if>
        <if test="goodsId != null and goodsId != ''">
            AND ue.goods_id = #{goodsId}
        </if>
        <if test="examId != null and examId != ''">
            AND ue.exam_id = #{examId}
        </if>
        ORDER by ue.record_id DESC
    </select>

    <select id="selectDoNum" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultType="Long">
        SELECT
            IFNULL( COUNT(*), 0 ) num
        FROM
            (
                SELECT
                    e.exam_id,
                    q.*
                FROM
                    ( SELECT exam_id, group_concat( do_question_ids ) AS ids FROM user_exam_record WHERE user_id = #{userId} AND goods_id = #{goodsId} and `status` = 1 GROUP BY exam_id ) e
                        LEFT JOIN question q ON FIND_IN_SET( q.question_id, e.ids )) AS eq
    </select>

    <select id="selectRightNum" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultType="Long">
        SELECT
            IFNULL( COUNT( DISTINCT question_id ), 0 ) num
        FROM
            question
        WHERE
            FIND_IN_SET(
                    question_id,(
                SELECT
                    group_concat( uer.right_question_ids ) AS ids
                FROM
                    user_exam_record uer
                WHERE
                        record_id IN ( SELECT MAX( record_id ) record_id FROM user_exam_record WHERE user_id = #{userId} AND goods_id = #{goodsId} and `status` = 1 GROUP BY exam_id )))

    </select>

    <select id="selectDetail" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultMap="UserExamRecordVoResult">
        SELECT
            uer.*,
            e.exam_name
        FROM
            user_exam_record uer
                LEFT JOIN exam e ON uer.exam_id = e.exam_id
        WHERE
            uer.record_id = #{recordId}
          AND uer.user_id = #{userId}

    </select>

    <select id="selectExamList" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultMap="UserExamRecordVoResult">
        SELECT
            e.exam_id,
            e.exam_name,
            uer.total_question_num
        FROM
            ( SELECT exam_id, MAX( total_question_num ) total_question_num FROM user_exam_record WHERE user_id = #{userId} AND goods_id = #{goodsId} GROUP BY exam_id ) uer
                LEFT JOIN exam e ON e.exam_id = uer.exam_id

    </select>

    <select id="getExamRecordRightList" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultMap="UserExamRecordQuestionVoResult">
        SELECT
            *
        FROM
            question
        WHERE
            FIND_IN_SET(
                    question_id,(
                SELECT
                    right_question_ids
                FROM
                    user_exam_record uer
                WHERE
                    uer.user_id = #{userId}
                  AND uer.goods_id = #{goodsId}
                  AND uer.exam_id = #{examId}
                ORDER BY
                    record_id DESC

                LIMIT 1 ))

    </select>

    <select id="getExamRecordWrongList" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultMap="UserExamRecordQuestionVoResult">
        SELECT
            q.*
        FROM
            (
                SELECT
                    question_id
                FROM
                    user_exam_wrong_record
                WHERE
                        record_id = ( SELECT record_id FROM user_exam_record uer WHERE uer.user_id = #{userId} AND uer.exam_id = #{examId} AND uer.goods_id = #{goodsId} ORDER BY record_id DESC LIMIT 1 )) uewr
	LEFT JOIN question q ON q.question_id = uewr.question_id

    </select>

    <select id="getUserDoQuestionNum" parameterType="com.zhongzheng.modules.user.bo.UserExamRecordQueryBo" resultType="Long">
        SELECT
            IFNULL(count(*),0)
        FROM
            question q
        WHERE
            FIND_IN_SET(
                    q.question_id,(
                SELECT
                    GROUP_CONCAT( uer.do_question_ids )
                FROM
                    user_exam_record uer
                WHERE
                    uer.user_id = #{userId}
                  AND uer.goods_id = #{goodsId}
                  AND uer.do_question_ids IS NOT NULL
            ))

    </select>


</mapper>
