videoalliance/.svn/pristine/05/05c00d06af54bcd03cb7d72434d58479fabdb0f9.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_YOUTU_ORDER_REC where USERID=? 
		]]>
	</property>
	
	<property name="findOrderRecByOrderid">
		<![CDATA[
		 SELECT 
				ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO, INSERTTIME
           FROM TD_YOUTU_ORDER_REC where ORDERID=? 
		]]>
	</property>
	
	<property name="addOrderRec2">
		<![CDATA[
			INSERT INTO TD_YOUTU_ORDER_REC(ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO)
			VALUES (
				 #[id], #[userid], #[orderid],#[cpid],#[spid], #[province], #[area],#[vipstatus],#[busitype], #[resultcode],#[resultinfo]
			)
		]]>
	</property>

	<property name="addOrderRec">
		<![CDATA[	
			MERGE INTO TD_YOUTU_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]
			WHEN NOT MATCHED THEN 
			INSERT (ID, USERID, ORDERID, CPID, SPID, PROVINCE, AREA, VIPSTATUS, BUSITYPE, RESULTCODE, RESULTINFO)
			VALUES (
			   #[id], #[userid], #[orderid],#[cpid],#[spid], #[province], #[area],#[vipstatus],#[busitype], #[resultcode],#[resultinfo]
			)
		]]>
	</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="endtime31Day">
		<![CDATA[
		SELECT TO_CHAR(31+TO_DATE(?,'yyyymmddhh24miss'),'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'),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,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],0,1,#[type],#[orderstatus],#[ordertype],#[apptype],#[isexperience],#[videoid],#[videoname],1,'1', #[showstatus])
		]]>
	</property>	

</properties>