videoalliance/.svn/pristine/0e/0efc53de78ca948741998d8dab4f6de5bb2280fe.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<properties>

	<property name="findOrderRelaAll">
		<![CDATA[
			SELECT CPID,SPID,USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME,
			TO_CHAR(endtime, 'yyyymmddhh24miss') endtime, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,
			(case when endtime is null then '0' when endtime >= sysdate then '1' else '2' end) STATUS
			FROM TD_ORDER_RELATIONS WHERE USERID=?
		]]>
	</property>

	<property name="findOrderRela">
		<![CDATA[
			SELECT CPID,SPID,USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME,
			TO_CHAR(endtime, 'yyyymmddhh24miss') endtime, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,
			(case when endtime is null then '0' when endtime >= sysdate then '1' else '2' end) STATUS
			FROM TD_ORDER_RELATIONS WHERE USERID=? AND CPID=? AND SPID=?
		]]>
	</property>

	<property name="findPreOrderRelaAll">
		<![CDATA[
			SELECT ID,ORDERIDA,ORDERIDB,CPID,SPID,USERID,TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,
			STATUS,PROVINCE,AREA,ORDERCHANNEL,CANCELCHANNEL,SYNSTATUS
			FROM TD_PRE_ORDER_REC WHERE USERID=?
		]]>
	</property>

	<property name="findPreOrderRela">
		<![CDATA[
			SELECT ID,ORDERIDA,ORDERIDB,CPID,SPID,USERID,TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,
			STATUS,PROVINCE,AREA,ORDERCHANNEL,CANCELCHANNEL
			FROM TD_PRE_ORDER_REC WHERE SYNSTATUS = '1' AND USERID=? AND CPID=? AND SPID=?
		]]>
	</property>

	<property name="findOrderId">
		<![CDATA[
			SELECT
			ID,ORDERPREID,ORDERID,USERID,TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME,TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,RESULTCODE,RESULTINFO,BUSITYPE,PROVINCE,AREA,CHANNEL
			FROM TD_PRE_ORDERID_REC WHERE ORDERID=?
		]]>
	</property>



	<property name="addOrderRec">
		<![CDATA[
			INSERT INTO TD_PRE_ORDERID_REC(ID,ORDERPREID,ORDERID,USERID,ORDERTIME,CANCELTIME,RESULTCODE,RESULTINFO,BUSITYPE,PROVINCE,AREA,INSERTTIME,CPID,SPID,CHANNEL )
			VALUES (
				#[id], #[orderpreid],#[orderid],#[userid],TO_DATE(#[ordertime],'yyyymmddhh24miss'),TO_DATE(#[canceltime],'yyyymmddhh24miss'),
				#[resultcode],#[resultinfo],#[busitype],#[province],#[area],SYSDATE,#[cpid],#[spid],#[channel]
			)
		]]>
	</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="getPreOrderId">
		<![CDATA[
		SELECT to_char(sysdate, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval LOGID FROM DUAL
			]]>
	</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="getCurrTime">
		<![CDATA[
		SELECT TO_CHAR(SYSDATE,'yyyymmddhh24miss') FROM DUAL
			]]>
	</property>

	<property name="orderPre">
		<![CDATA[
			merge into TD_PRE_ORDER_REC a using (select #[cpid] cpid, #[spid] spid, #[userid] userid from dual) b
			on(a.userid = b.userid and a.cpid = b.cpid and a.spid = b.spid AND a.SYNSTATUS = '1')
			when matched then update
			set ordertime=to_date(#[ordertime], 'yyyymmddhh24miss'),canceltime='',cancelchannel='',orderidb='',
			status=#[status],orderchannel=#[orderchannel],orderida=#[orderid]
			when not matched then insert (id,cpid,spid,userid,ordertime,status,province,area,orderchannel,orderida)values(
			#[id],#[cpid],#[spid],#[userid],to_date(#[ordertime], 'yyyymmddhh24miss'),#[status],#[province],#[area],#[orderchannel],#[orderid])
		]]>
	</property>

	<property name="cancelOrderPre">
		<![CDATA[
			merge into TD_PRE_ORDER_REC a using (select #[cpid] cpid, #[spid] spid, #[userid] userid from dual) b
			on (a.userid = b.userid and a.cpid = b.cpid and a.spid = b.spid AND a.SYNSTATUS = '1')
			when matched then update set
			canceltime=to_date(#[canceltime], 'yyyymmddhh24miss'),
			status='1',cancelchannel=#[cancelchannel],orderidb=#[orderid]
			when not matched then insert (id, cpid,spid,userid,canceltime,status,province,area,cancelchannel,orderidb) values (
			to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,
			#[cpid], #[spid], #[userid],to_date(#[canceltime], 'yyyymmddhh24miss'),'1', #[province], #[area], #[cancelchannel],#[orderid])
		]]>
	</property>

	<property name="endtimeMonthLastDay">
		<![CDATA[
		select to_char(last_day(add_months(trunc(sysdate),?)+1-1/24/60/60),'yyyymmddhh24miss') from dual
			]]>
	</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 ordertime=to_date(#[ordertime], 'yyyymmddhh24miss'),
			effecttime=to_date(#[effecttime], 'yyyymmddhh24miss'),
			canceltime='',
			#if($endtime && !$endtime.equals(""))endtime=to_date(#[endtime], 'yyyymmddhh24miss') #else endtime='' #end,
			status=#[status],synccount=0,syncsucceed='1',lastsynctime='',issms='1',
			orderchannel=#[orderchannel],
			type=#[type],orderstatus=#[orderstatus],
			ordertype=#[ordertype],apptype=#[apptype],
			cachesucceed=#[cachesucceed],
			cdnsucceed='1',
			showstatus=#[showstatus]
			when not matched then insert (id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,orderchannel,synccount,syncsucceed,type,orderstatus,ordertype,apptype,cachesucceed,cdnsucceed)values(
			to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,
			#[cpid],#[spid],#[userid],
			to_date(#[ordertime], 'yyyymmddhh24miss'),
			to_date(#[effecttime], 'yyyymmddhh24miss'),
			#if($endtime && !$endtime.equals(""))to_date(#[endtime], 'yyyymmddhh24miss') #else '' #end,
			#[status],#[province],#[area],#[orderchannel],0,1,#[type],#[orderstatus],#[ordertype],#[apptype],1,'1')
		]]>
	</property>

	<property name="cancelOrder">
		<![CDATA[
			merge into TD_ORDER_RELATIONS a using (select #[cpid] cpid, #[spid] spid, #[userid] userid from dual) b
			on (a.userid = b.userid and a.cpid = b.cpid and a.spid = b.spid and a.status in ('0', '1'))
			when matched then update set
			canceltime=to_date(#[canceltime], 'yyyymmddhh24miss'),
			endtime=to_date(#[endtime], 'yyyymmddhh24miss'),
			status='1', synccount=0,syncsucceed='1',lastsynctime='',issms='1', cancelchannel=#[cancelchannel], orderstatus=#[orderstatus], type=#[type],cachesucceed=1,cdnsucceed='1'
			when not matched then insert (id, cpid,spid,userid,ordertime,effecttime,canceltime,endtime,status,province,area,cancelchannel,synccount,syncsucceed,type,orderstatus,ordertype,cachesucceed,cdnsucceed) values (
			to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,
			#[cpid], #[spid], #[userid],
			to_date(#[ordertime], 'yyyymmddhh24miss'),
			to_date(#[effecttime], 'yyyymmddhh24miss'),
			to_date(#[canceltime], 'yyyymmddhh24miss'),
			to_date(#[endtime], 'yyyymmddhh24miss'),
			'1', #[province], #[area], #[cancelchannel], 0, 1, #[type], #[orderstatus], #[ordertype],1,'1')
		]]>
	</property>

	<property name="updShInPaInfo">
		<![CDATA[
		UPDATE TL_SHORDER_INOUT_PARAMS SET ORDER_NO = ? WHERE ID = ?
		]]>
	</property>
	
	<!-- 次月生效订购关系订购  -->
	<property name="nmaOrder">
		<![CDATA[
			MERGE INTO TD_NMA_ORDER_RELATIONS A USING (SELECT #[cpid] CPID, #[spid] SPID, #[userid] USERID FROM DUAL) B
			ON (A.USERID = B.USERID AND A.CPID = B.CPID AND A.SPID = B.SPID )
			WHEN MATCHED THEN UPDATE SET
			ORDERTIME = TO_DATE(#[ordertime], 'yyyymmddhh24miss'), CANCELTIME = NULL, CANCELCHANNEL = NULL, CANCELCHANNEL2 = NULL, SYNCTIME = NULL, ENDTIME = NULL, SYNCSTATUS = #[syncstatus],
			ORDERSTATUS = #[orderstatus], STATUS = #[status], ORDERCHANNEL = #[orderchannel], ORDERCHANNEL2 = #[subchannel], EFFECTTIME = TO_DATE(#[effecttime], 'yyyymmddhh24miss'), CHANNEL = #[channel]
			WHEN NOT MATCHED THEN INSERT (
			ID, CPID, SPID, USERID, ORDERTIME, STATUS, PROVINCE, AREA, ORDERCHANNEL,ORDERCHANNEL2, EFFECTTIME, SYNCSTATUS, ORDERSTATUS, CHANNEL
			)VALUES(
			TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval, #[cpid], #[spid], #[userid], TO_DATE(#[ordertime], 'yyyymmddhh24miss'), #[status], #[province], 
			#[area], #[orderchannel], #[subchannel], TO_DATE(#[effecttime], 'yyyymmddhh24miss'), #[syncstatus], #[orderstatus],#[channel]
			)
		]]>
	</property>	
	
	<!-- 次月生效订购关系退订  -->
	<property name="nmaCancel">
		<![CDATA[
			MERGE INTO TD_NMA_ORDER_RELATIONS A USING (SELECT #[cpid] CPID, #[spid] SPID, #[userid] USERID FROM DUAL) B
			ON (A.USERID = B.USERID AND A.CPID = B.CPID AND A.SPID = B.SPID )
			WHEN MATCHED THEN UPDATE SET
			CANCELTIME = TO_DATE(#[canceltime], 'yyyymmddhh24miss'), SYNCSTATUS = #[syncstatus], STATUS = '1', SYNCTIME = NULL, 
			CANCELCHANNEL = #[cancelchannel], CANCELCHANNEL2 = #[subchannel], ORDERSTATUS = #[orderstatus], CHANNEL = #[channel],
			EFFECTTIME = TO_DATE(#[effecttime], 'yyyymmddhh24miss'), ENDTIME = TO_DATE(#[endtime], 'yyyymmddhh24miss')
		]]>
	</property>
	
	<!-- 按SPID查询次月生效业务配置  -->
	<property name="qryAopConf">
		<![CDATA[
		SELECT * FROM TB_SP_AOP_CONFIG WHERE ENABLE_TAG = '2' AND SP_ID = ?
		]]>
	</property>	
	
	<!-- 按SPID查询次月生效业务配置  -->
	<property name="qryNmaOrder">
		<![CDATA[
			SELECT 
			SYNCSTATUS,STATUS,CHANNEL,ORDERCHANNEL,CANCELCHANNEL,ORDERCHANNEL2,CANCELCHANNEL2,
			TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME, 
			TO_CHAR(CANCELTIME,'YYYYMMDDHH24MISS') CANCELTIME, 
			TO_CHAR(EFFECTTIME,'YYYYMMDDHH24MISS') EFFECTTIME,
			TO_CHAR(ORDERTIME,'YYYYMM') ORDERMONTH, 
			TO_CHAR(CANCELTIME,'YYYYMM') CANCELMONTH, 
			TO_CHAR(SYSDATE,'YYYYMM') CURRMONTH 
			FROM TD_NMA_ORDER_RELATIONS A 
			WHERE A.USERID = ? AND CPID = ? AND SPID = ?
		]]>
	</property>	
	
	<property name="getEffecttime">
		SELECT TO_DATE(TO_CHAR(LAST_DAY(SYSDATE)+1,'YYYYMMDD')||'000001','YYYYMMDDHH24MISS') EFFECTTIME FROM DUAL
	</property>
		
	<property name="addNmaOrderLog">
		<![CDATA[
			insert into TL_NMA_ORDER_LOG (id, userid, province, area, apptype,
			channel, cpid, spid, errorcode, errorinfo, status, orderstatus, ordertype, inserttime,times) values (
			TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval, #[userid], #[province], #[area], #[apptype], #[channel], #[cpid],
			#[spid], #[errorcode], #[errorinfo], #[status], #[orderstatus], #[ordertype], sysdate, #[times])
		]]>
	</property>		
</properties>