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

    <select id="getNo" resultType="String">
       SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval SENO FROM DUAL       
    </select>

    <insert id="addRec" parameterType="Map">
       INSERT INTO td_relation_query(
			ID,ORDERID,USERID,CPID,SPID,SPNAME,PRICE,STATUS,ORDERTIME,CANCELTIME,ENDTIME,ISCONTRACT,orderchannel,orderchannel2)
 		VALUES(#{ID},#{ORDERID},#{USERID},#{CPID},#{SPID},#{SPNAME},
 		#{PRICE},#{STATUS},#{ORDERTIME,jdbcType=DATE},#{CANCELTIME,jdbcType=DATE},#{ENDTIME,jdbcType=DATE},#{ISCONTRACT},#{ORDERCHANNEL,jdbcType=VARCHAR},#{ORDERCHANNEL2,jdbcType=VARCHAR})
    </insert>

    <update id="upRec" parameterType="Map">
		UPDATE td_relation_query SET orderid = #{ID,jdbcType=VARCHAR} , status = #{STATUS},ordertime = #{ORDERTIME,jdbcType=DATE},
		canceltime = #{CANCELTIME,jdbcType=DATE},endtime = #{ENDTIME,jdbcType=DATE},orderchannel = #{ORDERCHANNEL,jdbcType=VARCHAR},orderchannel2 = #{ORDERCHANNEL2,jdbcType=VARCHAR}
		WHERE SPID = #{SPID} and userid = #{USERID}
	</update>

    <select id="getRec" resultType="HashMap">
        SELECT id,userid,cpid,spid,spname,price fee,(case when iscontract = '1' then '0' when iscontract = '2' then '2' else '1'end) producttype,
               (case when status ='0' and iscontract= '0' then '3' else status end ) status,
               to_char(ordertime,'yyyy-mm-dd hh24:mi:ss') ordertime,
               to_char(canceltime,'yyyy-mm-dd hh24:mi:ss') canceltime,
               to_char(endtime,'yyyy-mm-dd hh24:mi:ss') endtime,
               (case when orderchannel = 'boyuan' then (case when orderchannel2 is not null then orderchannel2 else orderchannel end) else null end) channelkey
        from td_relation_query where userid = #{userid} order by ordertime desc
    </select>

    <select id="getRec2" resultType="HashMap">
       SELECT id,userid,spid,spname,price fee,orderchannel,orderchannel2,
        status,ordertime,canceltime,endtime from td_relation_query where userid = #{userid} and spid = #{spid} order by ordertime desc
    </select>

    <select id="getRelation" resultType="HashMap" >
        select t1.id,t1.cpid,t1.spid,t2.spname,t1.userid,ordertime,canceltime,t1.endtime,t1.orderchannel,t1.orderchannel2,
        t1.status,t2.price,
        (case when t2.directype = '3' then '0'  else t2.directype end ) iscontract
        from td_order_relations t1,tb_sp_info t2
        where t1.spid = t2.spid and t1.spid in (select spid from tb_sp_info where (directype in ('1','3') or spid='319')) and userid =#{userid}

    </select>

    <select id="getNextMonthRel" resultType="java.util.HashMap" parameterType="java.lang.String">
        select b1.id,b1.cpid,b1.spid,b2.spname,b1.userid,b1.ordertime,b1.canceltime,b1.endtime,b1.orderchannel,b1.orderchannel2,
               b1.status,b2.price,
               (case when b2.directype = '3' then '0' else b2.directype end ) iscontract
        from TD_NMA_ORDER_RELATIONS b1 ,tb_sp_info b2
        where b1.spid = b2.spid and b1.spid in (select spid from tb_sp_info where directype in ('1','3'))
          and userid =#{userid}
          and to_char(inserttime,'yyyyMM') = to_char(sysdate,'yyyyMM')
    </select>

    <select id="getCancelMonth" resultType="Integer">
        select month from TB_PRODUCT_CANCEL_CONF where  spid=#{spid} and status='0'
    </select>

    <select id="queryCancelConf" resultType="Integer">
        select count(1) from TB_PRODUCT_CANCEL_CONF where  spid=#{spid} and status='0'
    </select>

    <select id="isCancel" resultType="Integer">
        select count(1) from TD_ORDER_RELATIONS where  userid= #{userid}  and spid= #{spid} and  sysdate>to_date(TO_CHAR(ADD_MONTHS(ordertime,#{num}),'yyyyMM'),'yyyyMM')
    </select>

    <select id="getHyendtime" resultType="String">
        SELECT TO_CHAR(TRUNC(LAST_DAY(TO_DATE(TO_CHAR(ADD_MONTHS(to_date(#{ordertime},'YYYY-MM-DD HH24:MI:SS'),#{num}),'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS')))+1-1/86400, 'yyyy-mm-dd hh24:mi:ss') HYENDTIME FROM DUAl
    </select>


    <select id="getSpid" resultType="string" parameterType="string">
        SELECT spid
        FROM TB_INTENSIVE_PRODUCTID_CONF
        WHERE PRODUCTID = #{productid}
    </select>


</mapper>
