actitve-process/.svn/pristine/6e/6e10aeb10e31bca3addc69a46d454049f04f7c72.svn-base

package com.chinacreator.process.dao;

import java.sql.SQLException;
import java.util.List;

import org.springframework.stereotype.Component;

import com.chinacreator.process.bean.NetOrderBean;
import com.chinacreator.process.bean.OrderBean;
import com.chinacreator.process.bean.OrderLog;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
public class KfkChangeNumDao {

	public List<NetOrderBean> queryByUserid(String userid) throws SQLException {
		String sql = "select id,userid, cpid, spid, province, area, orderchannel, cancelchannel, ordertime, endtime, canceltime,(case when canceltime is null then '0' when endtime >= sysdate then '1' else '2' end) status, ordertype,isexperience from TD_ORDER_RELATIONS where  userid=? and (canceltime is null or endtime > sysdate) and spid in('6','84','1017','21156','979','1005','11','980','952')";
		return SQLExecutor.queryListWithDBName(NetOrderBean.class, DataSource.NET3G, sql, userid);
	}

	public void order(NetOrderBean order) throws Exception {
		String sql = "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=#if($ordertime && !$ordertime.equals(\"\")) to_date(#[ordertimestr], 'yyyymmddhh24miss') #else sysdate #end,"
				+ "effecttime=#if($effecttime && !$effecttime.equals(\"\")) to_date(#[effecttimestr], 'yyyymmddhh24miss') #elseif($ordertime) to_date(#[ordertimestr], 'yyyymmddhh24miss')  #else sysdate #end, "
				+ "canceltime=#if($canceltime && !$canceltime.equals(\"\"))to_date(#[canceltimestr], 'yyyymmddhh24miss') #else '' #end,#if($endtime && !$endtime.equals(\"\"))endtime=to_date(#[endtimestr], 'yyyymmddhh24miss') #else endtime='' #end,"
				+ "status=#[status],synccount=0,syncsucceed='1',lastsynctime='',issms='1',smstime='',"
				+ "orderchannel=#[orderchannel],fromurl=#[fromurl],type=#[type],orderstatus=#[orderstatus],"
				+ "ordertype=#[ordertype],apptype=#[apptype],isexperience=#[isexperience],ordervaccode=#[ordervaccode],"
				+ "videoid=#[videoid],videourl=#[videourl],videoname=#[videoname],videotag=#[videotag],videosize=#[videosize],"
				+ "cachesucceed=1,showstatus=#[showstatus],batchid=#[batchid] when not matched then insert (id,cpid,spid,userid,ordertime,"
				+ "effecttime,endtime,status,province,area,orderchannel,synccount,syncsucceed,fromurl,type,orderstatus,ordertype,apptype,"
				+ "isexperience,videoid,videoname,videourl, videotag, videosize,ordervaccode,cachesucceed, showstatus,batchid,canceltime,cancelchannel)"
				+ "values( to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#[cpid],#[spid],#[userid],"
				+ "#if($ordertime && !$ordertime.equals(\"\")) to_date(#[ordertimestr], 'yyyymmddhh24miss') #else sysdate #end,"
				+ "#if($effecttime && !$effecttime.equals(\"\")) to_date(#[effecttimestr], 'yyyymmddhh24miss') #elseif($ordertime) to_date(#[ordertimestr], 'yyyymmddhh24miss') #else sysdate #end, "
				+ "#if($endtime && !$endtime.equals(\"\"))to_date(#[endtimestr], 'yyyymmddhh24miss') #else '' #end,#[status],#[province],#[area],"
				+ "#[orderchannel],0,1,#[fromurl],#[type],#[orderstatus],#[ordertype],#[apptype],#[isexperience],"
				+ "#[videoid],#[videoname],#[videourl],#[videotag],#[videosize],#[ordervaccode],1, #[showstatus],#[batchid],to_date(#[canceltimestr], 'yyyymmddhh24miss'),#[cancelchannel])";

		SQLExecutor.insertBean(DataSource.NET3G, sql, order);
	}

	public void cancelOrder(String id) throws Exception {
		String sql = "update td_order_relations set canceltime =sysdate,endtime=trunc(last_day(sysdate))+1-1/86400,status=1,cancelchannel='kfkchange',orderstatus=5 where id=?";
		SQLExecutor.updateWithDBName(DataSource.NET3G, sql, id);
	}
	
	  public void addOrderLog(OrderLog orderLog) throws SQLException {
		  String sql = "insert into tl_order_log (id, userid, province, area, apptype, useragent, origin, "
				+"channel, cpid, spid, errorcode, errorinfo, status, orderstatus, ordertype, inserttime, isexperience) values ( "
				+"to_char(sysdate, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval,"
				+"#[userid], #[province], #[area], #[apptype], #[useragent], #[origin], #[channel], #[cpid],"
				+"#[spid], #[errorcode], #[errorinfo], #[status], #[orderstatus], #[ordertype],"
				+"sysdate, #[isexperience])";
		  SQLExecutor.insertBean(DataSource.NET3G, sql, orderLog);
		}
	  
	  public void copyUserMsg(String userid,String oldUserid) throws SQLException{
		  String sql = "insert into TD_USERMSG_INFO (CPID,SPID,USERID,MSG1,MSG2,MSG3,ACTIVEID,INSERTTIME,MSG4) select cpid,spid,? userid,MSG1,MSG2,MSG3,ACTIVEID,sysdate,'kfkchange' MSG4 from TD_USERMSG_INFO where userid=? and ACTIVEID=6 and cpid='tencent' and spid ='84' and rownum=1";
		  SQLExecutor.insertWithDBName(DataSource.NET3G, sql, userid,oldUserid);
	  }
	  
	  public void copyVacMsg(String userid,String oldUserid) throws SQLException{
		  String sql = "insert into TD_VACORDER_CONTROL(USERID,CPID,SPID,ORDERTIME,REMARK) select ? userid,CPID,SPID,ORDERTIME,'kfkchange' REMARK from TD_VACORDER_CONTROL where cpid='tencent' and spid='84' and userid =? and to_char(ordertime,'yyyymm')=to_char(sysdate,'yyyymm') and rownum=1";
		  SQLExecutor.insertWithDBName(DataSource.NET3G, sql, userid,oldUserid);
	  }
	  
	  public void copyGiveVipMsg(String userid,String oldUserid) throws SQLException{
		  String sql = "insert into TD_VIPGIVE_CONTROL(USERID,CPID,SPID,givetime,REMARK) select ? userid,CPID,SPID,GIVETIME,'kfkchange' REMARK from TD_VIPGIVE_CONTROL where cpid='tencent' and spid='84' and userid =? and to_char(GIVETIME,'yyyymm')=to_char(sysdate,'yyyymm') and rownum=1";
		  SQLExecutor.insertWithDBName(DataSource.NET3G, sql, userid,oldUserid);
	  }
	  
	  public void copyActivityMsg(String userid,String id) throws SQLException{
		  String sql = "insert into TD_BUSSINESS_HANDLE "
		  		+ "(id,userid,cpid,spid,province,area,ordertime,effectivetime,activitystatus,activitytime,orderchannel,orderstatus,inserttime,endtime,ischarge,flowstatus,activetype,vipstatus,canceltime,VIPTIME,VIPCONSTATUS,VIPCONTIME,cancelchannel ) "
		  		+ "select to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval id,? userid,cpid,spid,province,area,ordertime,effectivetime,activitystatus,activitytime,'kfkchange' orderchannel,orderstatus,inserttime,endtime,ischarge,flowstatus,activetype,vipstatus,canceltime,VIPTIME,VIPCONSTATUS,VIPCONTIME,decode(canceltime,'','','kfkchange') cancelchannel "
		  		+ "from TD_BUSSINESS_HANDLE where id=?";
		  SQLExecutor.insertWithDBName(DataSource.NET3G, sql, userid,id);
	  }
	  
	  public void cancelActivity(String id) throws SQLException {
			String sql = "update TD_BUSSINESS_HANDLE set  canceltime =sysdate,endtime=last_day(trunc(sysdate)+1-1/(24*60*60)),cancelchannel= 'kfkchange',"
					+ "orderstatus =1  where id=?";
			SQLExecutor.updateWithDBName(DataSource.NET3G, sql, id);
		}
	  
		public OrderBean existUser(String userid) throws SQLException {
			String sql = "select id,orderstatus,to_char(endtime,'yyyymmddhh24miss') endtime from TD_BUSSINESS_HANDLE  where userid =? and cpid ='tencent' and spid='84' and ACTIVETYPE=6";
			return SQLExecutor.queryObjectWithDBName(OrderBean.class, DataSource.NET3G, sql, userid);
		}
		
		public void updateAcative(String id,String newid) throws SQLException {
			String sql = "update TD_BUSSINESS_HANDLE set (ordertime,canceltime,endtime,orderchannel,cancelchannel,ORDERSTATUS,VIPSTATUS,VIPTIME,VIPCONSTATUS,VIPCONTIME) "
					+ "=(select ordertime,canceltime,endtime,orderchannel,cancelchannel,ORDERSTATUS,VIPSTATUS,VIPTIME,VIPCONSTATUS,VIPCONTIME from TD_BUSSINESS_HANDLE where id=?) where id =?";
			SQLExecutor.updateWithDBName(DataSource.NET3G, sql, id,newid);
		}
	  
	  public static void main(String[] args) throws SQLException {
		  KfkChangeNumDao dao = new KfkChangeNumDao();
		 System.out.println( dao.queryByUserid("18574414678").size());
	}
}