<?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.BackBusiOrderDao">
    <insert id="addOrderLog" parameterType="com.chinacreator.video.restful.bean.BackOrderLog">
        	insert into tl_order_log (id, userid, province, area, apptype, useragent, origin,
			channel, cpid, spid, errorcode, errorinfo, status, orderstatus, ordertype, inserttime, isexperience,times) values (
			to_char(sysdate, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval,
			#{userid,jdbcType=VARCHAR}, #{province,jdbcType=VARCHAR}, #{area,jdbcType=VARCHAR}, #{apptype,jdbcType=VARCHAR}, #{useragent,jdbcType=VARCHAR}, #{origin,jdbcType=VARCHAR}, #{channel,jdbcType=VARCHAR}, #{cpid,jdbcType=VARCHAR},
			#{spid,jdbcType=VARCHAR}, #{errorcode,jdbcType=VARCHAR}, #{errorinfo,jdbcType=VARCHAR}, #{status,jdbcType=VARCHAR}, #{orderstatus,jdbcType=VARCHAR}, #{ordertype,jdbcType=VARCHAR},
			sysdate, #{isexperience,jdbcType=VARCHAR}, #{times,jdbcType=VARCHAR})
    </insert>
    <insert id="addShareOrderLog">
        	insert into TL_BACKORDER_SHARE_PARMS (id, userid, inserttime,
			errorcode, errorinfo, cpid, spid, remark) values (
			to_char(sysdate, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval,
			#{userid}, sysdate, #{errorcode}, #{errorinfo}, #{cpid}, #{spid}, #{remark}
			)
    </insert>

    <update id="addOrderRec" parameterType="com.chinacreator.video.restful.bean.BackBusiOrderRec">

	MERGE INTO TD_BACKBUSI_ORDER_REC a USING (SELECT #{id,jdbcType=VARCHAR} ID FROM DUAL) b
			ON(a.ID = b.ID)
			WHEN MATCHED THEN UPDATE
			SET VIPSTATUS = #{vipstatus,jdbcType=VARCHAR},RESULTCODE =  #{resultcode,jdbcType=VARCHAR},RESULTINFO = #{resultinfo,jdbcType=VARCHAR},TIMES = #{times,jdbcType=VARCHAR}
			WHEN NOT MATCHED THEN
			INSERT (ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO, CHANNEL, TIMES, TYPE, ENDTIME, STARTTIME, VIPENDTIME, SUBCHANNEL)
			VALUES (
			   #{id,jdbcType=VARCHAR}, #{userid,jdbcType=VARCHAR}, #{orderid,jdbcType=VARCHAR},#{cpid,jdbcType=VARCHAR},#{spid,jdbcType=VARCHAR}, #{province,jdbcType=VARCHAR}, #{area,jdbcType=VARCHAR},#{vipstatus,jdbcType=VARCHAR},#{busitype,jdbcType=VARCHAR}, #{resultcode,jdbcType=VARCHAR},#{resultinfo,jdbcType=VARCHAR},#{channel,jdbcType=VARCHAR},#{times,jdbcType=VARCHAR},#{type,jdbcType=VARCHAR},TO_DATE(#{endtime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),TO_DATE(#{starttime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),TO_DATE(#{vipendtime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'), #{subchannel,jdbcType=VARCHAR}
			)

    </update>

    <update id="order" parameterType="com.chinacreator.video.bean.OrderInfo">
        MERGE INTO TD_ORDER_RELATIONS a USING (SELECT #{cpid,jdbcType=VARCHAR} cpid, #{spid,jdbcType=VARCHAR} spid, #{userid,jdbcType=VARCHAR} userid FROM DUAL) b
			ON(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid)
			WHEN MATCHED THEN UPDATE
			SET status = '0',ENDTIME=TO_DATE(#{endtime,jdbcType=VARCHAR},'yyyymmddhh24miss'),ORDERTIME=TO_DATE(#{ordertime,jdbcType=VARCHAR},'yyyymmddhh24miss'), effecttime=TO_DATE(#{effecttime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),orderchannel=#{orderchannel,jdbcType=VARCHAR},channel=#{channel,jdbcType=VARCHAR},CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1'
			WHEN NOT MATCHED THEN INSERT(id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,orderchannel,channel,synccount,syncsucceed,type,orderstatus,ordertype,apptype,isexperience,videoid,videoname,cachesucceed,cdnsucceed, showstatus)values(
			TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,
			#{cpid,jdbcType=VARCHAR},#{spid,jdbcType=VARCHAR},#{userid,jdbcType=VARCHAR},TO_DATE(#{ordertime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),TO_DATE(#{effecttime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),
			TO_DATE(#{endtime,jdbcType=VARCHAR}, 'yyyymmddhh24miss'),#{status,jdbcType=VARCHAR},#{province,jdbcType=VARCHAR},#{area,jdbcType=VARCHAR},#{orderchannel,jdbcType=VARCHAR},#{channel,jdbcType=VARCHAR},0,1,#{type,jdbcType=VARCHAR},#{orderstatus,jdbcType=VARCHAR},#{ordertype,jdbcType=VARCHAR},#{apptype,jdbcType=VARCHAR},#{isexperience,jdbcType=VARCHAR},#{videoid,jdbcType=VARCHAR},#{videoname,jdbcType=VARCHAR},1,'1', #{showstatus,jdbcType=VARCHAR})
    </update>

    <select id="generateID" resultType="java.lang.String">
     SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.NEXTVAL FROM DUAL
    </select>
    <select id="getBackBusiConf" resultType="java.util.HashMap" parameterType="string">
			SELECT * FROM TB_BACKBUSI_CONF WHERE STATUS = '0' AND CPID = #{cpid} AND SPID = #{spid}
    </select>

    <select id="findOrderRecByOrderid" resultType="com.chinacreator.video.restful.bean.BackBusiOrderRec"
            parameterType="string">
		 SELECT
				ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO, TO_CHAR(INSERTTIME, 'yyyymmddhh24miss') INSERTTIME, RETRYCOUNT, TO_CHAR(RETRYTIME, 'yyyymmddhh24miss') RETRYTIME, RETRYCHANNEL,TYPE
           FROM TD_BACKBUSI_ORDER_REC where ORDERID = #{orderid} and resultcode='0'
    </select>
    <select id="endtimeTheMonthLastDay" resultType="java.lang.String">
		SELECT TO_CHAR(TRUNC(LAST_DAY(sysdate),'dd')+1-1/24/60/60,'yyyymmddhh24miss')  FROM DUAL
    </select>
    <select id="endtimeNextMonthLastDay" resultType="java.lang.String">
select to_char(last_day(add_months(trunc(TO_DATE(#{currEndtime}, 'yyyymmddhh24miss')),1)+1-1/24/60/60),'yyyymmddhh24miss') from dual
    </select>
    <select id="endtimeParamDay" resultType="java.lang.String">
		SELECT TO_CHAR(#{netdays}+TO_DATE(#{currEndtime},'yyyymmddhh24miss'),'yyyymmddhh24miss')  FROM DUAL
    </select>
    <select id="currParamDay" resultType="java.lang.String">
	SELECT TO_CHAR(#{netdays}+TRUNC(SYSDATE)-1/86400,'yyyymmddhh24miss') FROM DUAL
    </select>
    <select id="getKsStartTime" resultType="java.lang.String">
     SELECT TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDDHH24MISS') FROM DUAL
    </select>
    <select id="getStartTime" resultType="java.lang.String">
SELECT TO_CHAR(TO_DATE(#{endtime},'YYYYMMDDHH24MISS')-#{netdays}+2/(24*60*60),'YYYYMMDDHH24MISS') FROM DUAL
    </select>

    <select id="findByUser" resultType="com.chinacreator.video.bean.OrderInfo">
select a.cpid cpid, a.spid spid,B.VACPRODUCTID productid,userid, to_char(ordertime, 'yyyymmddhh24miss') ordertime,
            to_char(endtime, 'yyyymmddhh24miss') endtime, to_char(canceltime, 'yyyymmddhh24miss') canceltime,
            to_char(effecttime, 'yyyymmddhh24miss') effecttime, status, orderchannel, cancelchannel,
            ordertype, orderstatus,videoid, videoname, a.type type, apptype, province, area, isexperience
            from TD_ORDER_RELATIONS a,tb_sp_info b where (endtime is null or endtime > sysdate) and a.spid=b.spid and a.userid=#{userid}

    </select>
    <select id="getCurrentTime" resultType="java.lang.String">
      select to_char(sysdate, 'yyyymmddhh24miss') from dual
    </select>
    <select id="findByUserAndSpid" resultType="java.util.Map">
			SELECT CPID, SPID, USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME,
				TO_CHAR(ENDTIME, 'yyyymmddhh24miss') ENDTIME, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME
			FROM TD_ORDER_RELATIONS WHERE USERID=#{userid} and CPID=#{cpid} AND SPID=#{spid}
    </select>

    <select id="getBusiCount" resultType="java.lang.Integer">
        	SELECT count(*) FROM TB_BACKBUSI_CONF WHERE STATUS = '0'  and  busitype='2'  and  SPID =#{spid}
    </select>
</mapper>