1234567891011121314151617181920212223242526272829303132333435363738394041424344 |
- 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);
- }
- }
|