actitve-process/.svn/pristine/42/424f5b446096c561def92cac2fa07c95f6b71568.svn-base

package com.chinacreator.process.dao;

import com.chinacreator.common.exception.BusinessException;
import com.chinacreator.process.bean.ChannelOrderBean;
import com.chinacreator.process.bean.NetOrderBean;
import com.chinacreator.process.bean.OrderLog;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.PreparedDBUtil;
import com.frameworkset.common.poolman.SQLExecutor;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

@Component
public class NextMonthEffectDao
{
    private static Logger logger = Logger.getLogger(NextMonthEffectDao.class);    
    
	/**
	 * 订购次月生效的产品
	 * @param orderInfo
	 * @throws BusinessException
	 */
	public void nmaOrder(NetOrderBean orderBean) throws SQLException {
		String sql = "	MERGE INTO TD_NMA_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 ) "+
				"	WHEN MATCHED THEN UPDATE SET "+
				"	ORDERTIME = TO_DATE(#[ordertimestr], 'yyyymmddhh24miss'), CANCELTIME = NULL, CANCELCHANNEL = NULL, CANCELCHANNEL2 = NULL, SYNCTIME = NULL, ENDTIME = NULL, SYNCSTATUS = #[syncstatus], "+
				"	STATUS = #[status], ORDERCHANNEL = #[orderchannel], ORDERCHANNEL2 = #[channel2], EFFECTTIME = TO_DATE(#[effecttimestr], 'yyyymmddhh24miss'), ORDERSTAFFID = #[staffid], ORDERDEPARTID = #[departid] "+
				"	WHEN NOT MATCHED THEN INSERT ( "+
				"	ID, CPID, SPID, USERID, ORDERTIME, STATUS, PROVINCE, AREA, ORDERCHANNEL,ORDERCHANNEL2, EFFECTTIME, SYNCSTATUS, ORDERSTAFFID, ORDERDEPARTID, ORDERSTATUS "+
				"	)VALUES( "+
				"	TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval, #[cpid], #[spid], #[userid], TO_DATE(#[ordertimestr], 'yyyymmddhh24miss'), #[status], #[province], "+
				"	#[area], #[orderchannel], #[channel2], TO_DATE(#[effecttimestr], 'yyyymmddhh24miss'), #[syncstatus], #[staffid], #[departid], #[orderstatus] "+
				"	)";
		SQLExecutor.insertBean(DataSource.NET3G, sql, orderBean);
	}
	
	
	/**
	 * 订购次月生效的产品
	 * @param orderInfo
	 * @throws BusinessException
	 */
	public void nmaCancel(NetOrderBean orderBean) throws SQLException {
		//System.out.println(orderBean.getCanceltimestr()+"||"+orderBean.getEndtimestr()+"||"+orderBean.getEffecttimestr());
		
		String sql = " MERGE INTO TD_NMA_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 ) "+
				" WHEN MATCHED THEN UPDATE SET "+
				" CANCELTIME = TO_DATE(#[canceltimestr], 'yyyymmddhh24miss'), SYNCSTATUS = #[syncstatus], STATUS = '1', SYNCTIME = NULL, "+
				" CANCELCHANNEL = #[cancelchannel], CANCELCHANNEL2 = #[channel2], ORDERSTATUS = #[orderstatus], "+
				" EFFECTTIME = NULL, ENDTIME = TO_DATE(#[endtimestr], 'yyyymmddhh24miss'), CANCELSTAFFID = #[staffid], CANCELDEPARTID = #[departid] ";
		SQLExecutor.insertBean(DataSource.NET3G, sql, orderBean);
	}
    
	/**
	 * 获取配置数据
	 * @param spid
	 * @return
	 * @throws SQLException
	 */
	public List<HashMap> qryAopConf(String spid) throws SQLException {
		String sql = "SELECT * FROM TB_SP_AOP_CONFIG WHERE ENABLE_TAG = '2' AND SP_ID = ? ";
        PreparedDBUtil pdb = new PreparedDBUtil();
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1, spid);
            return pdb.executePreparedForList(HashMap.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
	}
	
	/**
	 * 获取已有订购关系
	 * @param orderInfo
	 * @return
	 * @throws SQLException
	 */
	public List<HashMap> qryNmaOrder(NetOrderBean orderBean) throws SQLException {
		String sql = " SELECT "+
				" STATUS, SYNCSTATUS, "+
				" TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, "+
				" TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME, "+
				" TO_CHAR(CANCELTIME,'YYYYMMDDHH24MISS') CANCELTIME, "+
				" TO_CHAR(EFFECTTIME,'YYYYMMDDHH24MISS') EFFECTTIME, "+
				" TO_CHAR(ORDERTIME,'YYYYMM') ORDERMONTH,  "+
				" TO_CHAR(CANCELTIME,'YYYYMM') CANCELMONTH,  "+
				" TO_CHAR(SYSDATE,'YYYYMM') CURRMONTH  "+
				" FROM TD_NMA_ORDER_RELATIONS A  "+
				" WHERE A.USERID = ? AND CPID = ? AND SPID = ? ";

        PreparedDBUtil pdb = new PreparedDBUtil();
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1, orderBean.getUserid());
            pdb.setString(2, orderBean.getCpid());
            pdb.setString(3, orderBean.getSpid());
            return pdb.executePreparedForList(HashMap.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
	}
	
	/**
	 * 获取生效时间，下月第一天0时0分0秒
	 * @return
	 * @throws SQLException
	 */
	public Date getEffecttime() throws SQLException {
		String sql = "SELECT TO_DATE(TO_CHAR(LAST_DAY(SYSDATE)+1,'YYYYMMDD')||'000000','YYYYMMDDHH24MISS') EFFECTTIME FROM DUAL ";
		return SQLExecutor.queryObject(Date.class, DataSource.NET3G, sql, null);
	}
	
	/**
	 * 根据订购时间获取生效时间，下月第一天0时0分0秒
	 * @return
	 * @throws SQLException
	 */
	public Date getEffecttime(String ordertime) throws SQLException {
		PreparedDBUtil pdb = new PreparedDBUtil();
		String sql = "SELECT TO_DATE(TO_CHAR(LAST_DAY(TO_DATE(?,'YYYYMMDDHH24MISS'))+1,'YYYYMMDD')||'000000','YYYYMMDDHH24MISS') EFFECTTIME FROM DUAL ";
	    try {
	        pdb.preparedSelect(DataSource.NET3G, sql);
	        pdb.setString(1, ordertime);
	        pdb.executePrepared();
	        return pdb.getDate(0, 0);
	      } catch (SQLException e) {
	        e.printStackTrace();
	      }
	      return null;
	}
	
	/**
	 * 根据退订时间获取结束时间
	 */
	public Date getEndtime(String canceltime) throws SQLException{
		PreparedDBUtil pdb = new PreparedDBUtil();
		String sql = "SELECT trunc(last_day(to_date(?, 'yyyymmddhh24miss')))+1-1/86400 FROM DUAL";
	    try {
	        pdb.preparedSelect(DataSource.NET3G, sql);
	        pdb.setString(1, canceltime);
	        pdb.executePrepared();
	        return pdb.getDate(0, 0);
	      } catch (SQLException e) {
	        e.printStackTrace();
	      }
	      return null;
	}
	
	/**
	 * 添加订购日志
	 * @param orderLog
	 * @throws SQLException
	 */
	public void addNmaOrderLog(OrderLog orderLog)  throws SQLException {
		try {
			String sql = " insert into TL_NMA_ORDER_LOG (id, userid, province, area, apptype,   "+
					" channel, cpid, spid, errorcode, errorinfo, status, orderstatus, ordertype, inserttime) values (   "+
					" TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval, #[userid], #[province], #[area], #[apptype], #[channel], #[cpid],   "+
					" #[spid], #[errorcode], #[errorinfo], #[status], #[orderstatus], #[ordertype], sysdate) ";
			SQLExecutor.insertBean(DataSource.NET3G, sql, orderLog);
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(orderLog.getUserid()+"=>保存订购日志失败", e);
		}
	}
	
	public List<HashMap> findOrderRela(NetOrderBean orderBean) throws SQLException {
		String sql = "	SELECT CPID,SPID,USERID, ORDERCHANNEL, CANCELCHANNEL, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME,  "+
				" TO_CHAR(endtime, 'yyyymmddhh24miss') endtime, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME,  "+
				" (case when endtime is null then '0' when endtime >= sysdate then '1' else '2' end) STATUS  "+
				" FROM TD_ORDER_RELATIONS WHERE USERID = ? AND CPID = ? AND SPID = ? ";
        PreparedDBUtil pdb = new PreparedDBUtil();
        try {
            pdb.preparedSelect(DataSource.NET3G, sql);
            pdb.setString(1, orderBean.getUserid());
            pdb.setString(2, orderBean.getCpid());
            pdb.setString(3, orderBean.getSpid());
            return pdb.executePreparedForList(HashMap.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
	}
	
	public Date getEndDayOfCurrentMonth() throws SQLException {
		String sql = "select to_char(trunc(last_day(sysdate))+1-1/86400, 'yyyymmddhh24miss') from dual";
		return SQLExecutor.queryObject(Date.class, DataSource.NET3G, sql, null);
	}
	
	public static void main(String[] args) throws SQLException {
		NextMonthEffectDao dao = new NextMonthEffectDao();
		System.out.println(dao.getEffecttime("20220512154147"));
	}
}