videoalliance/.svn/pristine/55/5595ff9c71312ecdb0b652a69c8a7e1f60d7bded.svn-base

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

	<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],channel=#[channel],
			type=#[type],orderstatus=#[orderstatus],
			ordertype=#[ordertype],apptype=#[apptype],
			isexperience=#[isexperience],
			videoid=#[videoid],
			videoname=#[videoname],
			cachesucceed=#[cachesucceed],
			cdnsucceed='1',
			showstatus=#[showstatus],
			#if($subchannel && !$subchannel.equals("")) orderchannel2 = #[subchannel] #else orderchannel2='' #end
			when not matched then insert (id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,channel,orderchannel,synccount,syncsucceed,type,orderstatus,
			ordertype,apptype,isexperience,videoid,videoname,cachesucceed,cdnsucceed, showstatus,orderchannel2)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],#[channel],#[orderchannel],0,1,#[type],#[orderstatus],#[ordertype],#[apptype],#[isexperience],
			#[videoid],#[videoname],1,'1', #[showstatus],
			#if($subchannel && !$subchannel.equals("")) #[subchannel] #else '' #end)
		]]>
	</property>

	<property name="countOrder">
		<![CDATA[
			 insert into td_order_relations  (id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,channel,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'),
			#if($endtime && !$endtime.equals(""))to_date(#[endtime], 'yyyymmddhh24miss') #else '' #end,
			#[status],#[province],#[area],#[channel],#[orderchannel],0,1,#[type],#[orderstatus],#[ordertype],#[apptype],#[isexperience],#[videoid],#[videoname],#[cachesucceed],'1', #[showstatus])
		]]>
	</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], channel=#[channel], orderstatus=#[orderstatus], type=#[type],cachesucceed=1,cdnsucceed='1',
			#if($subchannel && !$subchannel.equals("")) cancelchannel2 = #[subchannel] #else cancelchannel2='' #end
			when not matched then insert (id, cpid,spid,userid,ordertime,effecttime,canceltime,endtime,status,province,area,channel,
			cancelchannel,synccount,syncsucceed,type,orderstatus,ordertype,cachesucceed,cdnsucceed,cancelchannel2) 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], #[channel], #[cancelchannel], 0, 1, #[type], #[orderstatus], #[ordertype],1,'1',
			#if($subchannel && !$subchannel.equals("")) #[subchannel] #else '' #end)
		]]>
	</property>

	<property name="findByUserAndSpid">
		<![CDATA[
			select id,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 spid=? and (endtime is null or endtime > sysdate)
		]]>
	</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="findpassword">
		<![CDATA[
	  select PASSWORD from TB_CHANNEL_ORG where STATUS = '0' and id = ?
	]]>
	</property>

	<property name="addcoupon">
		<![CDATA[
       			INSERT INTO td_coupon_rec(ID,USERID,ORDERID,SPID,STARTTIME,ENDTIME,STATUS,COUPONMONTH,INSERTTIME)
 			VALUES(to_char(sysdate, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval,#[userid],
 		#[orderid],#[spid],to_char(sysdate,'yyyy-mm-dd'),to_char(last_day(sysdate), 'yyyy-mm-dd'),'0',to_char(sysdate,'yyyymm'),sysdate)
		]]>
	</property>

	<property name="getorderid">
		<![CDATA[
       			select id from td_order_relations where userid =? and spid = ?
		]]>
	</property>


	<property name="getDictionryValue">
		<![CDATA[
	  select value from TB_DICTIONRY_INFO where name = ?
	]]>
	</property>

	<property name="getWYJSpid">
		<![CDATA[
	  select EXTEND1 from TB_PRODUCT_CANCEL_CONF where status = '0' and  spid = ?
	]]>
	</property>

	<property name="addWYJLog">
		<![CDATA[
		insert into td_wyj_order_job (id,userid,province,area,cpid,spid,channel,extend1) values
		(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')||SEQ_COMMON6.NEXTVAL,#[userid],#[province],#[area],'wyj',#[spid],#[channel],#[extend1])
	]]>
	</property>

	<property name="getSpname">
		<![CDATA[
	  select spname from tb_sp_info where  spid = ?
	]]>
	</property>


	<property name="inserArrearageData">
		<![CDATA[
       	insert into TD_REFLOW_LEVELTHREE (ID, USERID, SPID, RESULTCODE, RESULTINFO, ACTIVITYCODE)
        values (TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || SEQ_COMMON6.NEXTVAL,  #[userid], #[spid],  #[resultcode],  #[resultinfo], #[activitycode])
		]]>
	</property>

</properties>