c8f68414b0b24e73ff8076887de413b4cb7bea06.svn-base 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344
  1. package com.chinacreator.process.dao;
  2. import java.sql.SQLException;
  3. import org.springframework.stereotype.Component;
  4. import com.chinacreator.process.bean.NetOrderBean;
  5. import com.chinacreator.process.util.DataSource;
  6. import com.frameworkset.common.poolman.SQLExecutor;
  7. @Component
  8. public class PreOrderDao{
  9. public NetOrderBean existUser(String userid,String cpid,String spid) throws SQLException{
  10. 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";
  11. return SQLExecutor.queryObjectWithDBName(NetOrderBean.class, DataSource.NET3G, sql, userid,cpid,spid);
  12. }
  13. public void order(NetOrderBean bean) throws SQLException{
  14. String sql = "merge into TD_PRE_ORDER_RELATIONS a using (select #[cpid] cpid, #[spid] spid, #[userid] userid,1 syncstatus from dual) b "+
  15. "on(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid and a.syncstatus=b.syncstatus) "+
  16. "when matched then update "+
  17. "set ordertime=to_date(#[ordertimestr],'yyyymmddhh24miss'), "+
  18. "effecttime=trunc(add_months(sysdate,-1),'mm'), "+
  19. "canceltime='', "+
  20. "endtime='',ordertype=1, "+
  21. "status=0,orderchannel=#[orderchannel], "+
  22. "orderchannel2=#[channel2],orderstaffid=#[staffid],orderdepartid=#[departid] "+
  23. "when not matched then insert (id,cpid,spid,userid,ordertime,effecttime,endtime,status,province,area,orderchannel,syncstatus,ordertype,orderchannel2,orderstaffid,orderdepartid)values( "+
  24. "to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, "+
  25. "#[cpid],#[spid],#[userid], "+
  26. "to_date(#[ordertimestr],'yyyymmddhh24miss'), "+
  27. "trunc(add_months(sysdate,-1),'mm'),'', "+
  28. "0,#[province],#[area],#[orderchannel],1,1,#[channel2],#[staffid],#[departid]) ";
  29. SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
  30. }
  31. public void cancel(NetOrderBean bean) throws SQLException{
  32. String sql = "merge into TD_PRE_ORDER_RELATIONS a using (select #[cpid] cpid, #[spid] spid, #[userid] userid from dual) b "+
  33. "on(a.cpid = b.cpid and a.spid = b.spid and a.userid = b.userid)"+
  34. "when matched then update "+
  35. "set canceltime=to_date(#[canceltimestr],'yyyymmddhh24miss'),"+
  36. "endtime=trunc(last_day(to_date(#[canceltimestr],'yyyymmddhh24miss')))+1-1/86400,syncstatus=1,"+
  37. "status=1,cancelchannel=#[cancelchannel],cancelchannel2=#[channel2],cancelstaffid=#[staffid],canceldepartid=#[departid]";
  38. SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
  39. }
  40. }