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

    <resultMap type="com.zhongzheng.modules.user.domain.UserSubscribe" id="UserSubscribeResult">
        <result property="subscribeId" column="subscribe_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="userId" column="user_id"/>
        <result property="subscribeStatus" column="subscribe_status"/>
        <result property="beforeStatus" column="before_status"/>
        <result property="examStatus" column="exam_status"/>
        <result property="performance" column="performance"/>
        <result property="result" column="result"/>
        <result property="certificateCode" column="certificate_code"/>
        <result property="applyId" column="apply_id"/>
        <result property="studentType" column="student_type"/>
        <result property="examExpend" column="exam_expend"/>
        <result property="beforeExpend" column="before_expend"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.UserSubscribeVo" id="UserSubscribeVo">
        <result property="subscribeId" column="subscribe_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="userId" column="user_id"/>
        <result property="subscribeStatus" column="subscribe_status"/>
        <result property="beforeStatus" column="before_status"/>
        <result property="examStatus" column="exam_status"/>
        <result property="performance" column="performance"/>
        <result property="result" column="result"/>
        <result property="certificateCode" column="certificate_code"/>
        <result property="applyId" column="apply_id"/>
        <result property="studentType" column="student_type"/>
        <result property="examExpend" column="exam_expend"/>
        <result property="beforeExpend" column="before_expend"/>
        <result property="createTime" column="create_time"/>
        <result property="goodsName" column="goods_name"/>
        <result property="categoryName" column="category_name"/>
        <result property="goodsCode" column="goods_code"/>
        <result property="remark" column="remark"/>
        <result property="standPrice" column="stand_price"/>
        <result property="realname" column="realname"/>
        <result property="idCard" column="id_card"/>
        <result property="applyName" column="apply_name"/>
        <result property="beforeId" column="before_id"/>
        <result property="beforeName" column="before_name"/>
        <result property="applySiteAddress" column="apply_site_address"/>
        <result property="applySiteStartTime" column="apply_site_start_time"/>
        <result property="applySiteEndTime" column="apply_site_end_time"/>
        <result property="applySiteExamTime" column="apply_site_exam_time"/>
        <result property="applySiteAddressTrain" column="apply_site_address_train"/>
        <result property="applySiteStartTrainTime" column="apply_site_start_train_time"/>
        <result property="applySiteEndTrainTime" column="apply_site_end_train_time"/>
        <result property="applySiteExamTrainTime" column="apply_site_exam_train_time"/>
        <result property="applyStartTime" column="apply_start_time"/>
        <result property="applyEndTime" column="apply_end_time"/>
    </resultMap>

    <select id="listSubscribe" parameterType="com.zhongzheng.modules.user.bo.UserSubscribeQueryBo" resultMap="UserSubscribeVo">
        SELECT
            *,
            (SELECT g.goods_name FROM goods g where us.goods_id = g.goods_id) as goods_name,
            (SELECT g.code FROM goods g where us.goods_id = g.goods_id) as goods_code,
            (SELECT g.stand_price FROM goods g where us.goods_id = g.goods_id) as stand_price,
            (SELECT m.category_name FROM goods g LEFT JOIN major m on g.major_id= m.id where us.goods_id = g.goods_id) as category_name,
            (SELECT u.realname FROM `user` u where us.user_id = u.user_id) as realname,
            (SELECT u.id_card FROM `user` u where us.user_id = u.user_id) as id_card,
            (SELECT ea.apply_name FROM exam_apply ea where ea.apply_id = us.apply_id) as apply_name,
            (SELECT ea.apply_start_time FROM exam_apply ea where ea.apply_id = us.apply_id) as apply_start_time,
            (SELECT ea.apply_end_time FROM exam_apply ea where ea.apply_id = us.apply_id) as apply_end_time,
            (SELECT eb.before_name FROM exam_before eb where eb.before_id = us.before_id) as before_name
        FROM
            user_subscribe us
        WHERE
            1 =1
        <if test="userId != null and userId != '' ">
            and us.user_id = #{userId}
        </if>
        <if test="goodsId != null and goodsId != '' ">
            and us.goods_id = #{goodsId}
        </if>
        <if test="result != null ">
            and us.result = #{result}
        </if>
        <if test="subscribeId != null and subscribeId != '' ">
            and us.subscribe_id = #{subscribeId}
        </if>
        <if test="subscribeStatus != null and subscribeStatus != '' ">
            and us.subscribe_status = #{subscribeStatus}
        </if>
        <if test="applyId != null and applyId != '' ">
            and us.apply_id = #{applyId}
        </if>
        <if test="studentType != null and studentType != '' ">
            and us.student_type = #{studentType}
        </if>
        <if test="examStatus != null ">
            and us.exam_status = #{examStatus}
        </if>
        <if test="examExpend != null ">
            and us.exam_expend is NOT NULL
        </if>
          <if test="beforeExpend != null ">
            and us.before_expend is NOT NULL
        </if>
        <if test="majorId != null and majorId != '' ">
            and (SELECT m.id FROM goods g LEFT JOIN major m on g.major_id= m.id where us.goods_id = g.goods_id) = #{majorId}
        </if>
        <if test="realname != null and realname != '' ">
            and (SELECT u.realname FROM `user` u where us.user_id = u.user_id) like concat('%', #{realname}, '%')
        </if>
    </select>


    <select id="selectGoodsId" parameterType="string"  resultType="Long">
        SELECT g.goods_id FROM goods g where g.`code`=#{code} and g.status = 1
    </select>

    <select id="selectUserId" parameterType="map"  resultType="Long">
        SELECT u.user_id FROM `user` u where u.realname=#{userName} and u.id_card=#{idCard}
    </select>

    <select id="selectBeforeId" parameterType="map"  resultType="Long">
        SELECT eb.before_id FROM exam_before eb where eb.before_name=#{beforeName} and eb.status = 1
    </select>

    <select id="selectApplyId" parameterType="map"  resultType="Long">
        SELECT ea.apply_id FROM exam_apply ea where ea.apply_name=#{applyName} and ea.status = 1
    </select>
    <select id="selectSubscribeId" parameterType="map"  resultType="Long">
        SELECT
            us.subscribe_id
        FROM
            user_subscribe us
        WHERE 1=1
          and us.subscribe_status =1
          and us.apply_id = #{applyId}
          and us.goods_id=#{goodsId}
          and us.user_id = #{userId}
    </select>
</mapper>
