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

    <resultMap type="com.zhongzheng.modules.user.domain.UserExamGoods" id="UserExamGoodsResult">
        <result property="userExamGoodsId" column="user_exam_goods_id"/>
        <result property="userId" column="user_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="examNumber" column="exam_number"/>
        <result property="doNumber" column="do_number"/>
        <result property="orderGoodsId" column="order_goods_id"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.UserExamGoodsVo" id="UserExamGoodsVo">
        <result property="userExamGoodsId" column="user_exam_goods_id"/>
        <result property="userId" column="user_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="examNumber" column="exam_number"/>
        <result property="doNumber" column="do_number"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="expendNumber" column="expend_number"/>
        <result property="expendBefore" column="expend_before"/>
        <result property="goodsCode" column="goods_code"/>
        <result property="goodsName" column="goods_name"/>
        <result property="standPrice" column="stand_price"/>
        <result property="projectName" column="project_name"/>
        <result property="businessName" column="business_name"/>
        <result property="educationName" column="education_name"/>
        <result property="orderGoodsId" column="order_goods_id"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.UserExamGoodsSupplementVo" id="UserExamGoodsSupplementVo">
        <result property="userExamGoodsId" column="user_exam_goods_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="examNumber" column="exam_number"/>
        <result property="doNumber" column="do_number"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="code" column="goods_code"/>
        <result property="goodsName" column="goods_name"/>
        <result property="standPrice" column="stand_price"/>
        <result property="status" column="status"/>
        <result property="timeStatus" column="time_status"/>
        <result property="makeStartTime" column="make_start_time"/>
        <result property="makeEndTime" column="make_end_time"/>
        <result property="timeStatus" column="time_status"/>
        <result property="projectName" column="project_name"/>
        <result property="businessName" column="business_name"/>
        <result property="educationName" column="education_name"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.exam.domain.ExamNumber" id="ExamNumberResult">
        <result property="examNumberId" column="exam_number_id"/>
        <result property="code" column="code"/>
        <result property="examNumber" column="exam_number"/>
        <result property="doNumber" column="do_number"/>
        <result property="status" column="status"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="goodsType" column="goods_type"/>
        <result property="educationTypeId" column="education_type_id"/>
        <result property="businessId" column="business_id"/>
        <result property="projectId" column="project_id"/>
    </resultMap>

    <select id="listUserExam" parameterType="com.zhongzheng.modules.user.bo.UserExamGoodsQueryBo" resultMap="UserExamGoodsVo">
        SELECT
            *,
            (SELECT g.goods_name FROM goods g where ueg.goods_id = g.goods_id) as goods_name,
            (SELECT g.code FROM goods g where ueg.goods_id = g.goods_id) as goods_code,
            (SELECT g.stand_price FROM goods g where ueg.goods_id = g.goods_id) as stand_price,
            (SELECT cet.education_name FROM goods g LEFT JOIN course_education_type cet on cet.id = g.education_type_id where ueg.goods_id = g.goods_id) as education_name,
            (SELECT cpt.project_name FROM goods g LEFT JOIN course_project_type cpt on cpt.id = g.project_id  where ueg.goods_id = g.goods_id) as project_name,
            (SELECT cb.business_name FROM goods g LEFT JOIN course_business cb on cb.id = g.business_id where ueg.goods_id = g.goods_id) as business_name
        FROM
            user_exam_goods ueg
        WHERE
            ueg.user_id = #{userId}
        <if test="goodsId != null and goodsId != '' ">
            and ueg.goods_id = #{goodsId}
        </if>
    </select>

    <select id="getUserExam" parameterType="map" resultMap="UserExamGoodsSupplementVo">
        SELECT
            *,
            (SELECT g.goods_name FROM goods g where uegs.goods_id = g.goods_id) as goods_name,
            (SELECT g.code FROM goods g where uegs.goods_id = g.goods_id) as goods_code,
            (SELECT g.stand_price FROM goods g where uegs.goods_id = g.goods_id) as stand_price,
            (SELECT g.make_start_time FROM goods g where uegs.goods_id = g.goods_id) as make_start_time,
            (SELECT g.make_end_time FROM goods g where uegs.goods_id = g.goods_id) as make_end_time,
            (case WHEN (SELECT COUNT(g.goods_id) FROM goods g where uegs.goods_id = g.goods_id and unix_timestamp(now()) BETWEEN g.make_start_time and g.make_end_time) >0 then 1 ELSE 0 end) as time_status,
            (SELECT cet.education_name FROM goods g LEFT JOIN course_education_type cet on cet.id = g.education_type_id where uegs.goods_id = g.goods_id) as education_name,
            (SELECT cpt.project_name FROM goods g LEFT JOIN course_project_type cpt on cpt.id = g.project_id  where uegs.goods_id = g.goods_id) as project_name,
            (SELECT cb.business_name FROM goods g LEFT JOIN course_business cb on cb.id = g.business_id where uegs.goods_id = g.goods_id) as business_name
        FROM
            user_exam_goods_supplement uegs
        WHERE
            uegs.user_exam_goods_id = #{userExamGoodsId}
          and uegs.`status`=#{status}
    </select>

    <select id="queryExamGoods" parameterType="map" resultType="Long">
        SELECT
            count(1)
        FROM
            exam_number en
                LEFT JOIN exam_number_goods eng on eng.exam_number_id = en.exam_number_id
                LEFT JOIN user_exam_goods ueg   on 	ueg.goods_id = eng.goods_id
        where ueg.user_id = #{userId}
          AND ueg.goods_id = #{goodsId}
    </select>

    <select id="queryExamGoodsVo" parameterType="map" resultMap="ExamNumberResult">
        SELECT
            en.*
        FROM
            exam_number en
                LEFT JOIN exam_number_goods eng on eng.exam_number_id = en.exam_number_id
        where 1=1
          AND eng.goods_id = #{goodsId}
    </select>
</mapper>
