videoalliance/.svn/pristine/a8/a88df5a7aa90056ee399c25d7e5046c756e25df8.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<properties>
	<property name="findByUserAndSpid">
		<![CDATA[
			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=? and CPID=? AND SPID=?
		]]>
	</property>

	<property name="findByUserAndCpid">
		<![CDATA[
			SELECT CPID, SPID, 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, type, apptype, province, area, isexperience
			FROM TD_ORDER_RELATIONS WHERE USERID=? and CPID=? and (endtime is null or endtime > sysdate)
		]]>
	</property>

	<property name="findByUser">
		<![CDATA[
			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=?
		]]>
	</property>


	<property name="findOrderRecByUserid">
		<![CDATA[
			SELECT
				ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO, INSERTTIME
           FROM TD_BACKBUSI_ORDER_REC where USERID=?
		]]>
	</property>

	<property name="findOrderRecByOrderid">
		<![CDATA[
		 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 = ?
		]]>
	</property>

	<property name="addOrderRec">
		<![CDATA[
			MERGE INTO TD_BACKBUSI_ORDER_REC a USING (SELECT #[id] ID FROM DUAL) b
			ON(a.ID = b.ID)
			WHEN MATCHED THEN UPDATE
			SET VIPSTATUS = #[vipstatus],RESULTCODE =  #[resultcode],RESULTINFO = #[resultinfo],TIMES = #[times]
			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], #[userid], #[orderid],#[cpid],#[spid], #[province], #[area],#[vipstatus],#[busitype], #[resultcode],#[resultinfo],#[channel],#[times],#[type],TO_DATE(#[endtime], 'yyyymmddhh24miss'),TO_DATE(#[starttime], 'yyyymmddhh24miss'),TO_DATE(#[vipendtime], 'yyyymmddhh24miss'), #[subchannel]
			)
		]]>
	</property>

	<property name="orderRecRollback">
		<![CDATA[
			UPDATE TD_YOUTU_ORDER_REC SET HASROLLBACK = '0' WHERE ORDERID = ?
		]]>
	</property>

	<property name="orderRecVipstatus">
		<![CDATA[
			UPDATE TD_YOUTU_ORDER_REC SET VIPSTATUS = '0' WHERE ORDERID = ?
		]]>
	</property>

	<property name="addOrderLog">
		<![CDATA[
			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], #[province], #[area], #[apptype], #[useragent], #[origin], #[channel], #[cpid],
			#[spid], #[errorcode], #[errorinfo], #[status], #[orderstatus], #[ordertype],
			sysdate, #[isexperience], #[times])
		]]>
	</property>

	<property name="getCpSp">
		<![CDATA[
	    SELECT
       b.cpid,b.spid,netpwd,vacproductid,spcode,type,mutex,mobtype,mutextype
	   FROM  TB_CP_ACCOUNT_CONFIG a, tb_sp_info b where a.cpid=b.cpid and b.cpid= ? and b.spid= ?
		]]>
	</property>

	<property name="curr31Day">
		<![CDATA[
		SELECT TO_CHAR(30+TRUNC(SYSDATE)+1-1/86400,'yyyymmddhh24miss') FROM DUAL
			]]>
	</property>

	<property name="currParamDay">
		<![CDATA[
		SELECT TO_CHAR(?+TRUNC(SYSDATE)-1/86400,'yyyymmddhh24miss') FROM DUAL
			]]>
	</property>

	<property name="endtime31Day">
		<![CDATA[
		SELECT TO_CHAR(31+TO_DATE(?,'yyyymmddhh24miss'),'yyyymmddhh24miss')  FROM DUAL
			]]>
	</property>

	<property name="endtimeParamDay">
		<![CDATA[
		SELECT TO_CHAR(?+TO_DATE(?,'yyyymmddhh24miss'),'yyyymmddhh24miss')  FROM DUAL
			]]>
	</property>

	<property name="endtimeTheMonthLastDay">
		<![CDATA[
		SELECT TO_CHAR(TRUNC(LAST_DAY(sysdate),'dd')+1-1/24/60/60,'yyyymmddhh24miss')  FROM DUAL
			]]>
	</property>

	<property name="endtimeNextMonthLastDay">
		<![CDATA[
		select to_char(last_day(add_months(trunc(TO_DATE(?, 'yyyymmddhh24miss')),1)+1-1/24/60/60),'yyyymmddhh24miss') from dual
			]]>
	</property>

	<property name="orderSuccess">
		<![CDATA[
			UPDATE TD_ORDER_RELATIONS SET  CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1', ENDTIME = ? WHERE USERID = ? AND CPID = ? AND SPID = ?
		]]>
	</property>

	<property name="orderFail">
		<![CDATA[
			UPDATE TD_ORDER_RELATIONS SET  ENDTIME = ? WHERE USERID = ? AND CPID = ? AND SPID = ?
		]]>
	</property>

	<property name="orderFailDel">
		<![CDATA[
			DELETE TD_ORDER_RELATIONS WHERE USERID = ? AND CPID = ? AND SPID = ?
		]]>
	</property>

	<property name="order">
		<![CDATA[
			MERGE INTO TD_ORDER_RELATIONS a USING (SELECT #[cpid] cpid, #[spid] spid, #[userid] 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],'yyyymmddhh24miss'),ORDERTIME=TO_DATE(#[ordertime],'yyyymmddhh24miss'), effecttime=TO_DATE(#[effecttime], 'yyyymmddhh24miss'),orderchannel=#[orderchannel],channel=#[channel],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],#[spid],#[userid],TO_DATE(#[ordertime], 'yyyymmddhh24miss'),TO_DATE(#[effecttime], 'yyyymmddhh24miss'),
			TO_DATE(#[endtime], 'yyyymmddhh24miss'),#[status],#[province],#[area],#[orderchannel],#[channel],0,1,#[type],#[orderstatus],#[ordertype],#[apptype],#[isexperience],#[videoid],#[videoname],1,'1', #[showstatus])
		]]>
	</property>

	<property name="getBackBusiConf">
		<![CDATA[
			SELECT * FROM TB_BACKBUSI_CONF WHERE STATUS = '0' AND CPID = ? AND SPID = ?
		]]>
	</property>

	<property name="findYoutuOrderRal">
		<![CDATA[
	     SELECT ID, CPID, SPID, USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME,
            TO_CHAR(ENDTIME, 'yyyymmddhh24miss') ENDTIME
         FROM TD_ORDER_RELATIONS WHERE USERID = #[userid] AND CPID = #[cpid] AND SPID = #[spid] AND ENDTIME > SYSDATE
		]]>
	</property>

	<property name="endtimeMinus31Day">
		<![CDATA[
		SELECT TO_CHAR(TO_DATE(?,'yyyymmddhh24miss')-31,'yyyymmddhh24miss') FROM DUAL
		]]>
	</property>

	<property name="cancel">
		<![CDATA[
			MERGE INTO TD_ORDER_RELATIONS a USING (SELECT #[cpid] cpid, #[spid] spid, #[userid] userid FROM DUAL) b
			ON(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid)
			WHEN MATCHED THEN UPDATE
			SET ENDTIME=TO_DATE(#[endtime],'yyyymmddhh24miss'), orderchannel=#[orderchannel], CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1'
		]]>
	</property>

	<property name="updShInPaInfo">
		<![CDATA[
		UPDATE TL_SHORDER_INOUT_PARAMS SET ORDER_NO = ? WHERE ID = ?
		]]>
	</property>
</properties>