<?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.chinacreator.video.mapper.QueryOrderByCpDao" >
    <resultMap id="BaseResultMap" type="com.chinacreator.video.bean.OrderInfo" >
        <result column="cpid" property="cpid" jdbcType="VARCHAR" />
        <result column="spid" property="spid" jdbcType="VARCHAR" />
        <result column="userid" property="userid" jdbcType="VARCHAR" />
        <result column="ordertime" property="ordertime" jdbcType="DATE" />
        <result column="endtime" property="endtime" jdbcType="DATE" />
        <result column="status" property="status" jdbcType="VARCHAR" />
    </resultMap>
    <sql id="Base_Column_List" >
        b.userid, b.cpid, b.spid, b.ordertime, endtime, (case when canceltime is null then '0' when endtime >= sysdate then '1' else '2' end) status
    </sql>

    <select id="getOrderByCp" parameterType="string"  resultMap="BaseResultMap"  >
        SELECT
        <include refid="Base_Column_List" />

        from NET3G.VB_FLOWCP_CONFIG a join NET3G.TD_ORDER_RELATIONS b
                on a.spid=b.spid  and (b.endtime>sysdate or b.endtime is null)
        where userid = #{userid} and a.flowcp=#{cpid}
    </select>

</mapper>

