actitve-process/.svn/pristine/db/dbd5b03bc4d3a0788f7004c228f853b678583982.svn-base

package com.chinacreator.process.dao;

import com.chinacreator.process.bean.BackBusiOrderRec;
import com.chinacreator.process.bean.BackShareOrderBean;
import com.chinacreator.process.bean.OrderLog;
import com.chinacreator.process.bean.PointShopOrderBean;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

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

@Component
public class VipRetryDao{
	
	private Logger log = Logger.getLogger("pointshop");
	
    public List<HashMap> findSpInfo(String spid)throws SQLException {
        String sql = " select cpid, spid, spname, price, vacproductid, spcode, type, feetype, cancancelorder, canorder, "+
                " errorhandle,duration,canaccumulation,mutex,relationSp,paytype,needUnifiedAuthSync,spcodename,haslocal "+
                " from tb_sp_info where spid = ? ";
        return  SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, spid);
    }
    
	public List<HashMap> findOrderRelaAll(String userid)throws SQLException {
		String sql = " SELECT CPID,SPID,USERID, 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 = ? ";
		return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, userid);
    }
	
	/**
	 * 根据渠道查询密码
	 * @param channel
	 * @return
	 * @throws SQLException
	 */
	public HashMap getChannelPwdByChannel(String channel)throws SQLException {
		String sql = " SELECT ID, PASSWORD FROM TB_CHANNEL_ORG where status = 0 and id= ? ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, channel);
	}
	
	/**
	 * 更新callBack状态
	 * @param bcstatus
	 * @param bcinfo
	 * @param id
	 * @return
	 * @throws SQLException
	 */
//	public void updCallBack(String bcstatus,String bcinfo, String id) {
//		try {
//			String sql = "UPDATE TD_POINTS_ORDER_REC SET BCSTATUS = ?, BCINFO = ? WHERE ID = ?";
//			Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, bcstatus, bcinfo,id);
//		} catch (SQLException e) {
//			e.printStackTrace();
//			log.error("orderId: "+id+", 更新callBack状态出现异常，"+e.getMessage());
//		}
//	}
	
//	public void updParamsByCb(String cbReqParams,String cbRspParams, String requestId)throws SQLException {
//		String sql = "UPDATE TL_POINTS_INOUT_PARAMS SET CBREQPARAMS = ?, CBRESPARAMS = ?, CBTIME = SYSDATE WHERE REQUESTID = ?";
//		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, cbReqParams, cbRspParams,requestId);
//	}
	
	/**
	 * 根据orderId和orderNo查询订购信息
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public BackBusiOrderRec getOrderRec(String id) throws SQLException {
		String sql = "  SELECT "+
		    " ID,USERID,ORDERID,CHANNEL,CPID,SPID,PROVINCE,AREA,VIPSTATUS,BUSITYPE,RESULTCODE,RESULTINFO,TIMES, "+
		    " TO_CHAR(INSERTTIME,'YYYYMMDDHH24MISS')INSERTTIME,RETRYCOUNT,TO_CHAR(RETRYTIME,'YYYYMMDDHH24MISS') RETRYTIME,RETRYCHANNEL "+
 		    " FROM TD_BACKBUSI_ORDER_REC WHERE ID = ? ";
		return  SQLExecutor.queryObjectWithDBName(BackBusiOrderRec.class, DataSource.NET3G, sql, id);
	}
	
	/**
	 * 根据orderId和orderNo查询积分商城订购信息
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public PointShopOrderBean getPSOrderRec(String id) throws SQLException {
		String sql = "  SELECT "+
				" ID, USERID,PROVINCE,AREA,CPID,SPID,ORDERNO,GOODSCODE,RESULTCODE,RESULTINFO,BUSITYPE,SERVICECODE, "+
	  			" TO_CHAR(INSERTTIME,'YYYYMMDDHH24MISS') INSERTTIME,TO_CHAR(FINISHTIME,'YYYYMMDDHH24MISS') FINISHTIME, "+
	  			" TO_CHAR(ORDERTIME,'YYYYMMDDHH24MISS') ORDERTIME,REQUESTID,VIPSTATUS,APPID "+
	 		    " FROM TD_POINTS_ORDER_REC WHERE ID = ? ";
		return  SQLExecutor.queryObjectWithDBName(PointShopOrderBean.class, DataSource.NET3G, sql, id);
	}
	
	/**
	 * 获取赠送会员状态
	 * @param orderId
	 * @return
	 * @throws SQLException
	 */
	public String getVipstatus(String orderId) throws SQLException {
		String sql = "SELECT VIPSTATUS FROM TD_BACKBUSI_ORDER_REC WHERE ID = ? ";
		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,orderId);
	}
	
	/**
	 * 获取赠送会员处理结果
	 * @param orderId
	 * @return
	 * @throws SQLException
	 */
//	public String getResultCode(String orderId) throws SQLException {
//		String sql = "SELECT RESULTCODE FROM TD_POINTS_ORDER_REC WHERE ID = ? ";
//		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,orderId);
//	}
	
	/**
	 * 更新会员状态
	 * @param vipstatus
	 * @param orderId
	 * @return
	 * @throws SQLException
	 */
//	public boolean updVipstatus(String vipstatus, String orderId) throws SQLException {
//		//赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
//		String sql = " UPDATE TD_POINTS_ORDER_REC SET VIPSTATUS = ? WHERE ID = ? ";
//		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vipstatus, orderId);
//		return ((Integer)obj)>0 ? true : false;
//	}
	
	/**
	 * 更新业务状态信息
	 * @param resultCode
	 * @param resultInfo
	 * @param id
	 * @return
	 * @throws SQLException
	 */
//	public boolean updBusiStatus(String resultCode, String resultInfo, String id) throws SQLException {
//		//赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
//		String sql = " UPDATE TD_BACKBUSI_ORDER_REC SET RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
//		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, resultCode, resultInfo, id);
//		return ((Integer)obj)>0 ? true : false;
//	}
	
	/**
	 * 
	 * @param vipStatus
	 * @param resultCode
	 * @param resultInfo
	 * @param id
	 * @param tabname
	 * @return
	 * @throws SQLException
	 */
	public boolean updBusiStatus(BackBusiOrderRec orderBean, String tabname) throws SQLException {
		//赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
		String sql = " UPDATE "+tabname+" SET VIPSTATUS = ? , RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, orderBean.getVipstatus(), orderBean.getResultcode(), orderBean.getResultinfo(), orderBean.getId());
		return ((Integer)obj)>0 ? true : false;
	}
	
	/**
	 * 根据条件查询订购关系
	 * @param userid
	 * @param cpid
	 * @param spid
	 * @return
	 * @throws SQLException
	 */
	public HashMap findByUserAndSpid(String userid, String cpid, String spid)throws SQLException {			
		String sql = "SELECT CPID, SPID, USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, "+
				" TO_CHAR(ENDTIME, 'yyyymmddhh24miss') ENDTIME, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME "+
				" FROM TD_ORDER_RELATIONS WHERE USERID= ? and CPID= ? AND SPID= ? ";
		return  SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, userid,cpid,spid);
	}
	
	/**
	 * 获取赠送会员加密密码
	 * @param goodsCode
	 * @return
	 * @throws SQLException
	 */
//	public String getPwdByGoodsCode(String goodsCode)throws SQLException {		
//		String sql = " SELECT PWD FROM TB_POINTS_GOODS_CONF WHERE GOODSCODE = ? ";
//		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,goodsCode);
//	}

	/**
	 * 获取当前时间
	 * @return
	 * @throws SQLException
	 */
	public String currTime()throws SQLException {	
		String sql = "SELECT TO_CHAR(SYSDATE,'yyyymmddhh24miss') CURRTIME FROM DUAL";
		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,null);
	}
	
	public static void main(String[] args) throws SQLException {
		VipRetryDao dao = new VipRetryDao();
		//System.out.println(dao.findSpInfo("1167"));
		//System.out.println(dao.getPwdByGoodsCode("pointshop130"));
		//System.out.println(dao.getOrderRec("201906211723268662899","d4078706-2ff3-4f1b-9aad-80436a294b22"));
		//System.out.println(dao.getBackBusiConf("youtu", "1168"));
		System.out.println(dao.getMinNum("20190717002822",2));
		
		BackShareOrderBean bean = new BackShareOrderBean();
		bean.setUserid("18673197465");
		bean.setErrorcode("0000");
		bean.setErrorinfo("OK");
		bean.setRemark("dfsdfsa");
		bean.setSpid("1168");
		bean.setCpid("youtu");
		try{
			dao.addShareOrderLog(bean);
		}catch (Exception e) {
			// TODO: handle exception
		}

	}
	
	/**
	 * 获取从当天算起31天
	 * @return
	 * @throws SQLException
	 */
	public String curr31Day()throws SQLException {	
		String sql = "SELECT TO_CHAR(30+TRUNC(SYSDATE)+1-1/86400,'yyyymmddhh24miss') curr31Day FROM DUAL";
		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,null);
	}
	
	/**
	 * 获取从指定时间开始起加31天
	 * @param endtime
	 * @return
	 * @throws SQLException
	 */
	public String endtime31Day(String endtime)throws SQLException {	
		String sql = "SELECT TO_CHAR(31+TO_DATE(?,'yyyymmddhh24miss'),'yyyymmddhh24miss') endtime31Day FROM DUAL";
		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,endtime);
	}

	/**
	 * 更新订购信息表
	 * @param BackBusiOrderRec
	 * @return
	 * @throws SQLException
	 */
	public void updOrderRecByVipRetry(BackBusiOrderRec orderBean) {	
		try {
			String sql = " UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = ?, RETRYTIME = SYSDATE, RETRYCHANNEL = ?, RETRYCOUNT = ?, RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
			Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, orderBean.getVipstatus(), orderBean.getRetrychannel(), 
					orderBean.getRetrycount(),orderBean.getResultcode(),orderBean.getResultinfo(),orderBean.getId());
		} catch (SQLException e) {
			e.printStackTrace();
			log.error("orderId: "+orderBean.getId()+", 更新订购信息表出现异常，"+e.getMessage());
		}
	}
	
	/**
	 * 新增或更新订购关系
	 * @param bean
	 * @throws SQLException
	 */
	public void order(BackBusiOrderRec orderBean) throws SQLException{
		 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 status = '0',ENDTIME = TO_DATE(#[endtime],'yyyymmddhh24miss'),ORDERTIME = SYSDATE, effecttime = SYSDATE, ORDERCHANNEL = #[orderchannle],CHANNEL = #[channel],"+
				 "	CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1' "+
				 "	WHEN NOT MATCHED THEN INSERT(ID,CPID,SPID,USERID,ORDERTIME,EFFECTTIME,ENDTIME,STATUS,PROVINCE,AREA,CHANNEL,ORDERCHANNEL,SYNCCOUNT,SYNCSUCCEED,TYPE,ORDERSTATUS,ORDERTYPE, "+
				 "	ISEXPERIENCE,CACHESUCCEED,CDNSUCCEED,CDNCOUNT) VALUES ( "+
				 "	TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, "+
				 "	#[cpid],#[spid],#[userid],SYSDATE,SYSDATE, "+
				 "	TO_DATE(#[endtime], 'yyyymmddhh24miss'),'0',#[province],#[area],#[channel],#[orderchannle],0,1,1,'3','1',0,1,'1','0') ";
		SQLExecutor.insertBean(DataSource.NET3G, sql, orderBean);
	}
	
	/**
	 * 记录订购日志
	 * @param orderLog
	 * @throws SQLException
	 */
	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);
	}
	
	/**
	 * 获取业务配置信息
	 * @param cpid
	 * @param spid
	 * @return
	 * @throws SQLException
	 */
	 public List<HashMap> getBackBusiConf(String cpid, String spid) throws SQLException {
		 String sql = "SELECT * FROM TB_BACKBUSI_CONF WHERE STATUS = '0' AND CPID = ? AND SPID = ? ";
		 return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid);
	 }
	 
	  /**
		 * 获取两个时间相差分钟数
		 * @param rectime
		 * @return
		 * @throws SQLException
	  */
	  public boolean getMinNum(String rectime, int minute) throws SQLException {
			String sql = "select FLOOR((SYSDATE - TO_DATE(?,'yyyymmddhh24miss'))*24*60) AS FZNUM FROM DUAL";
			int num = SQLExecutor.queryObjectWithDBName(Integer.class, DataSource.NET3G,sql, rectime);
			if(num < minute){
				return false;
			}else{
				return true;
			}
	  }
	  
	  /**
	   * 保存调能力平台接口日志
	   * @param bean
	   * @throws SQLException
	   */
	  public void addShareOrderLog(BackShareOrderBean bean)throws SQLException {
		  String sql = "insert into TL_BACKORDER_SHARE_PARMS (id, userid, inserttime,errorcode, errorinfo, cpid, spid, remark) values ("+
				  " to_char(sysdate, 'yyyymmddhh24miss') || SEQ_COMMON6.nextval, #[userid], sysdate, #[errorcode], #[errorinfo], #[cpid], #[spid], #[remark]) ";
		  SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	  }
}