package com.chinacreator.process.dao;

import java.sql.SQLException;
import org.springframework.stereotype.Component;
import com.chinacreator.process.bean.NetOrderBean;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
public class PreOrderDao{

	public NetOrderBean existUser(String userid,String cpid,String spid) throws SQLException{
		String sql = " select userid,cpid,spid,ordertime,canceltime,endtime,status from TD_PRE_ORDER_RELATIONS a where a.userid=? and cpid=? and spid=? and (endtime is null or endtime>sysdate) and syncstatus=1";
		return  SQLExecutor.queryObjectWithDBName(NetOrderBean.class, DataSource.NET3G, sql, userid,cpid,spid);
	}
	public void order(NetOrderBean bean) throws SQLException{
		 String sql = "merge into TD_PRE_ORDER_RELATIONS a using (select #[cpid] cpid, #[spid] spid, #[userid] userid,1 syncstatus from dual) b "+
					"on(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid and a.syncstatus=b.syncstatus) "+
					"when matched then update  "+
					"set ordertime=to_date(#[ordertimestr],'yyyymmddhh24miss'), "+
					"effecttime=trunc(add_months(sysdate,-1),'mm'),  "+
					"canceltime='', "+
					"endtime='',ordertype=1, "+
					"status=0,orderchannel=#[orderchannel], "+
				    "orderchannel2=#[channel2],orderstaffid=#[staffid],orderdepartid=#[departid] "+
					"when not matched then insert (id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,orderchannel,syncstatus,ordertype,orderchannel2,orderstaffid,orderdepartid)values( "+
					"to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, "+
					"#[cpid],#[spid],#[userid], "+
					"to_date(#[ordertimestr],'yyyymmddhh24miss'), "+
					"trunc(add_months(sysdate,-1),'mm'),'', "+
					"0,#[province],#[area],#[orderchannel],1,1,#[channel2],#[staffid],#[departid]) ";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
}
	
	public void cancel(NetOrderBean bean) throws SQLException{
		String sql = "merge into TD_PRE_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 canceltime=to_date(#[canceltimestr],'yyyymmddhh24miss'),"+
		"endtime=trunc(last_day(to_date(#[canceltimestr],'yyyymmddhh24miss')))+1-1/86400,syncstatus=1,"+
		"status=1,cancelchannel=#[cancelchannel],cancelchannel2=#[channel2],cancelstaffid=#[staffid],canceldepartid=#[departid]";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	}
}