<?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.yonge.cooleshow.biz.dal.dao.VipCardRecordDao">
	<resultMap id="BaseResultMap" type="com.yonge.cooleshow.biz.dal.entity.VipCardRecord">
            <result column="id_" property="id" />
	        <result column="user_id_" property="userId" />
	        <result column="order_no_" property="orderNo" />
            <result column="sub_order_no_" property="subOrderNo" />
            <result column="vip_card_id_" property="vipCardId" />
	        <result column="start_time_" property="startTime" />
	        <result column="create_time_" property="createTime" />
	        <result column="update_time_" property="updateTime" />
	        <result column="end_time_" property="endTime" />
            <result column="msg_status_" property="msgStatus" />
		</resultMap>

    <!-- 表字段 -->
    <sql id="baseColumns">
         t.id_ as id
        , t.user_id_ as userId
        , t.order_no_ as orderNo
        , t.sub_order_no_ as subOrderNo
        , t.vip_card_id_ as vipCardId
        , t.start_time_ as startTime
        , t.create_time_ as createTime
        , t.update_time_ as updateTime
        , t.end_time_ as endTime
        , t.msg_status_ as msgStatus
        ,t.client_type_ as clientType
        </sql>

    <select id="detail" resultType="com.yonge.cooleshow.biz.dal.vo.VipCardRecordVo">
        SELECT
            <include refid="baseColumns"/>,
            s.original_price_ as originalPrice,
            s.sale_price_ as salePrice,
            s.period_ as `period`
        FROM user_order_detail a
        left join vip_card_record t on a.order_no_ = t.order_no_ and a.sub_order_no_ = t.sub_order_no_
        left join member_price_settings s on t.vip_card_id_ = s.id_
        where a.id_ = #{orderDetilId}
        <if test="userId != null">
            and t.user_id_ = #{userId}
        </if>
    </select>


    <select id="selectPage" resultType="com.yonge.cooleshow.biz.dal.vo.VipCardRecordVo">
		SELECT         
        	<include refid="baseColumns" />,
            s.original_price_ as originalPrice,
            s.sale_price_ as salePrice,
            s.period_ as `period`
		FROM vip_card_record t
        left join member_price_settings s on t.vip_card_id_ = s.id_
        order by t.create_time_ desc
	</select>

    <select id="selectTemporaryRecord" resultType="com.yonge.cooleshow.biz.dal.vo.VipCardRecordVo">
        select
            <include refid="baseColumns"/>,
            u.phone_ as phone
        from (
            select max(id_) as id_ from vip_card_record group by user_id_
        ) a
        left join vip_card_record t on a.id_ = t.id_
        left join sys_user u on t.user_id_ = u.id_
        where t.end_time_ &gt;= now()
        and t.end_time_ &lt; DATE_ADD(now(),INTERVAL 3 DAY)
        and msg_status_ = 0
        order by end_time_ desc
    </select>

    <select id="selectExpireRecord" resultType="com.yonge.cooleshow.biz.dal.vo.VipCardRecordVo">
        select
            <include refid="baseColumns"/>,
            u.phone_ as phone
        from vip_card_record t
        left join sys_user u on t.user_id_ = u.id_
        where t.end_time_ &lt;= now()
        and msg_status_ in (0,1)
        order by end_time_ desc
    </select>
</mapper>