<?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.restful.dao.QueryCapOrderDao">
    <insert id="addCapRev">
        INSERT INTO "TD_CAP_REVSYNC_ORDER" ("ID", "INSERTTIME", "SEQUENCE_ID", "ORDER_ID", "UPDATE_TYPE", "MDN", "ACCEPT_TIME", "IN_MODE_CODE", "SOURCEDEVICE_ID", "SP_PRODUCT_ID", "SP_ID", "START_DATE", "END_DATE", "STATUS", "SYNCTIME", "RESULT", "DEPART_ID", "STAFF_ID")
        VALUES (TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, sysdate, #{SERVICE_ID,jdbcType=VARCHAR}, TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, '1', #{MDN,jdbcType=VARCHAR},SYSDATE, 'queryCapOrder', #{SUBCHANNEL,jdbcType=VARCHAR}, #{PRODUCT_ID,jdbcType=VARCHAR},#{SP_ID,jdbcType=VARCHAR}, TO_DATE(#{START_DATE,jdbcType=VARCHAR}, 'SYYYYMMDDHH24MISS'), TO_DATE(#{END_DATE,jdbcType=VARCHAR}, 'SYYYYMMDDHH24MISS'), '1', NULL, NULL, '', '')
    </insert>

    <insert id="insertQueryCap"  useGeneratedKeys="true" keyProperty="ID" >
        insert  into TL_QUERYCAP_ORDER(ID,CPID,SPID,USERID,CHANNEL,STATUS)
        VALUES (#{id},#{orderInfo.cpid},#{orderInfo.spid},#{orderInfo.userid},#{orderInfo.channel},'2')
    </insert>

    <update id="updateQueryCap">
    update TL_QUERYCAP_ORDER set RESPPARAMS=#{resp} ,RESPTIME=sysdate where id=#{id}
    </update>

    <select id="findProduct" resultType="java.lang.String">
     select VACPRODUCTID  from  tb_sp_info where spid=#{spid}
    </select>

    <select id="getID" resultType="java.lang.String">
    select TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval from dual
    </select>

    <insert id="insertSync">
           INSERT INTO TD_CHSYNC_ORDER_REC
             (ID,
              RELID,
              CPID,
              SPID,
              USERID,
              ORDERTIME,
              CANCELTIME,
              ENDTIME,
              PROVINCE,
              AREA,
              ORDERCHANNEL,
              CANCELCHANNEL,
              STATUS)

             select TO_CHAR(SYSDATE, 'yyyyMMddhh24miss') ||
                    SEQ_COMMON6.nextval,
                    ID,
                    CPID,
                    SPID,
                    USERID,
                    ORDERTIME,
                    CANCELTIME,
                    ENDTIME,
                    PROVINCE,
                    AREA,
                    ORDERCHANNEL,
                    CANCELCHANNEL,
                    status
               from td_order_relations
              where userid = #{userid}
                and spid IN (SELECT SPID FROM TB_SP_INFO WHERE VACPRODUCTID=#{productid})
                and (endtime > sysdate or endtime is null)
                and rownum = 1
    </insert>
    <insert id="addCapRevCancel">
          INSERT INTO "TD_CAP_REVSYNC_ORDER" ("ID", "INSERTTIME", "SEQUENCE_ID", "ORDER_ID", "UPDATE_TYPE", "MDN", "ACCEPT_TIME", "IN_MODE_CODE", "SOURCEDEVICE_ID", "SP_PRODUCT_ID", "SP_ID", "START_DATE", "END_DATE", "STATUS", "SYNCTIME", "RESULT", "DEPART_ID", "STAFF_ID")
        VALUES (TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, sysdate, #{SERVICE_ID,jdbcType=VARCHAR}, TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, '2', #{MDN,jdbcType=VARCHAR},SYSDATE, 'queryCapOrder', #{SUBCHANNEL,jdbcType=VARCHAR}, #{PRODUCT_ID,jdbcType=VARCHAR},#{SP_ID,jdbcType=VARCHAR}, TO_DATE(#{START_DATE,jdbcType=VARCHAR}, 'SYYYYMMDDHH24MISS'), TO_DATE(#{END_DATE,jdbcType=VARCHAR}, 'SYYYYMMDDHH24MISS'), '1', NULL, NULL, '', '')
    </insert>

    <insert id="insertSyncList">
            INSERT INTO TD_CHSYNC_ORDER_REC
             (ID,
              RELID,
              CPID,
              SPID,
              USERID,
              ORDERTIME,
              CANCELTIME,
              ENDTIME,
              PROVINCE,
              AREA,
              ORDERCHANNEL,
              CANCELCHANNEL,
              STATUS)

             select TO_CHAR(SYSDATE, 'yyyyMMddhh24miss') ||
                    SEQ_COMMON6.nextval,
                    ID,
                    CPID,
                    SPID,
                    USERID,
                    ORDERTIME,
                    CANCELTIME,
                    ENDTIME,
                    PROVINCE,
                    AREA,
                    ORDERCHANNEL,
                    CANCELCHANNEL,
                    status
               from td_order_relations
              where userid = #{userid}
                and cpid = #{cpid}
                and (endtime > sysdate or endtime is null)
    </insert>

    <select id="queryUserOrder" resultType="java.lang.String">
   select status from td_order_relations  where userid=#{userid} and spid in (select spid from tb_sp_info where VACPRODUCTID=#{productid}) and (endtime>sysdate or endtime is null) and  rownum =1
    </select>

    <select id="getProduct" resultType="java.lang.String">
             select VACPRODUCTID  from  tb_sp_info where cpid=#{cpid} and VACPRODUCTID is not null
    </select>


</mapper>