actitve-process/.svn/pristine/72/7208d863cd25f5f76f7041b055adc97423b156f6.svn-base

package com.chinacreator.process.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Component;

import com.chinacreator.common.support.cache.annotation.CacheName;
import com.chinacreator.process.bean.ContinueBean;
import com.chinacreator.process.bean.KuaishouPushBean;
import com.chinacreator.process.bean.OrderBean;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.PreparedDBUtil;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
@CacheName("OrderSendVip")
public class OrderSendVipDao{
	
	/**
	 * 更新处理结果
	 * @param id
	 * @param resultcode
	 * @param resultinfo
	 * @return
	 * @throws SQLException
	 */
	public boolean updatePush(String id, String resultcode, String resultinfo)throws SQLException {
		String sql = "UPDATE TD_ORDER_SENDVIP_REC SET RESULTCODE = ? , RESULTINFO = ?  WHERE ID = ? ";
		Object rows = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, resultcode,resultinfo, id);
		if(Integer.parseInt(rows.toString()) > 0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 查询是否有配置信息
	 * @param cpid
	 * @param spid
	 * @param orderchannel
	 * @return
	 * @throws SQLException
	 */
	public boolean qryOrderSendvipConf(String cpid, String spid,String orderchannel)throws SQLException {
		String sql = " SELECT COUNT(1) CNT FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND CHANNEL = ? AND STATUS = '0' ";
		int cnt = SQLExecutor.queryObjectWithDBName(Integer.class, DataSource.NET3G,sql, cpid, spid, orderchannel);
		if(cnt > 0){
			return true;
		}else{
			return false;
		}
	}
	
  	/**
  	 * 查询所有自动领取会员配置
  	 * @param 
  	 * @return
  	 */
	@Cacheable(value="qryOrderSendvipAllConf", key="#cpid + '_' + #spid")
	public List<HashMap> qryOrderSendvipAllConf(String cpid, String spid)throws SQLException {
		 String sql = " SELECT ID,CPID,SPID,CHANNEL,VIPID,VIPTYPE,VIPPWD,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, ORDERTYPE, PRODUCTNAME, PRODUCTID, SENDTYPE, SUBCHANNEL FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND STATUS = '0' ";
		 return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid);
  	}
	
  	/**
  	 * 查询自动领取会员配置
  	 * @param 
  	 * @return
  	 */
	public List<HashMap> qryOrderSendvipConf(String cpid, String spid)throws SQLException {
		 String sql = " SELECT CPID,SPID,CHANNEL,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, ORDERTYPE, PRODUCTNAME, PRODUCTID, SUBCHANNEL FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND STATUS = '0' AND SENDTYPE = '1' ";
		 return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid);
  	}
	
  	/**
  	 * 查询自动走直充送会员配置
  	 * @param 
  	 * @return
  	 */
	public List<HashMap> qryOrderSendvipConfByZc(String cpid, String spid)throws SQLException {
		 String sql = " SELECT CPID,SPID,CHANNEL,VIPID,VIPTYPE,VIPPWD,TO_CHAR(STARTTIME,'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, ORDERTYPE, PRODUCTNAME, PRODUCTID, SUBCHANNEL FROM TB_ORDER_SENDVIP_CONF WHERE CPID = ? AND SPID = ? AND STATUS = '0' AND SENDTYPE = '0' ";
		 return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, cpid, spid);
  	}
  	
  	/**
  	 * 获取ID
  	 * @return
  	 * @throws SQLException
  	 */
  	public String getNo()throws SQLException {
  		String sql = "SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval ID FROM DUAL ";
  		return (String)SQLExecutor.queryFieldWithDBName("net3g",sql,null);
  	}

  	/**
  	 * 添加自动赠送会员记录
  	 * @param bean
  	 * @throws SQLException
  	 */
  	public void addSendvipRec(HashMap<String,String> params) throws SQLException {
	    String sql = "INSERT INTO TD_ORDER_SENDVIP_REC(ID, USERID, CPID, SPID, SENDMONTH ,RESULTCODE, RESULTINFO ,ORDERTIME ,ORDERCHANNEL, INSERTTIME, CONFID, SUBCHANNEL)  " +
	    		" VALUES(#[ID], #[USERID],#[CPID],#[SPID],TO_CHAR(SYSDATE, 'YYYYMM'),#[RESULTCODE],#[RESULTINFO], TO_DATE(#[ORDERTIME],'YYYYMMDDHH24MISS'),#[ORDERCHANNEL], SYSDATE, #[CONFID],#[SUBCHANNEL] )";   	
	    SQLExecutor.insertBean("net3g", sql, params);
	 }
	
  	/**
  	 * 根据手机号码和SPID查询订购关系
  	 * @param userid
  	 * @param spid 	  
  	 * @return
  	 * @throws SQLException
  	 */
	public List<HashMap> findOrderRel(String userid,String spid)throws SQLException {
		String sql = " SELECT ID, CPID, SPID, USERID, TO_CHAR(ORDERTIME, 'yyyymmddhh24miss') ORDERTIME, "+ 
			" TO_CHAR(endtime, 'yyyymmddhh24miss') endtime, TO_CHAR(CANCELTIME, 'yyyymmddhh24miss') CANCELTIME, ORDERCHANNEL, ORDERCHANNEL2, CANCELCHANNEL2, "+
			" (case when endtime is null then '0' when endtime >= sysdate then '1' else '2' end) STATUS "+
			" FROM TD_ORDER_RELATIONS WHERE USERID = ? AND SPID = ? ";
		return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, userid, spid);
    }
	
	/**
	 * 获取待生成活动关系的数据
	 * @param srcflag
	 * @return
	 * @throws SQLException
	 */
	public List<OrderBean> getBusshandleWaitData(String srcflag) throws SQLException {
		String sql = "SELECT * FROM ( "+ 
				" SELECT ID WAITID,USERID,CPID,SPID,PROVINCE,AREA,STATUS ORDERTYPE, "+
				" DECODE(STATUS,'0',ORDERTIME,CANCELTIME) UPDATETIME, "+
				" DECODE(STATUS,'0', "+
				" DECODE(ORDERCHANNEL,'kf','spfbtx11',NVL(ORDERCHANNEL,'spfbtx1')), "+
				" DECODE(CANCELCHANNEL,'kf','spfbtx11',NVL(CANCELCHANNEL,'spfbtx1')) "+
				" ) CHANNEL, "+
				" DECODE(STATUS,'0', "+
				" DECODE(ORDERCHANNEL,NULL,'0','2'), "+
				" DECODE(CANCELCHANNEL,NULL,'0','2') "+
				" ) ACTIVATED "+
				" FROM TD_BUSSHANDLE_WAIT WHERE RESULTCODE = '1' AND SRCFLAG = ? ORDER BY INSERTTIME "+
				") WHERE ROWNUM < 200 ";
		return SQLExecutor.queryListWithDBName(OrderBean.class, DataSource.NET3G, sql, srcflag);
	}
	
	/**
	 * 更新记录表的状态
	 * @param id
	 * @param resultcode
	 * @param resultinfo
	 * @return
	 * @throws SQLException
	 */
	public boolean updBusshandleWaitData(String id, String resultcode, String resultinfo) throws SQLException {
		String sql = " UPDATE TD_BUSSHANDLE_WAIT SET RESULTCODE = ?, RESULTINFO = ?, UPDTIME = SYSDATE WHERE ID = ? ";
		Object rows = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, resultcode, resultinfo, id);
		if(Integer.parseInt(rows.toString()) > 0){
			return true;
		}else{
			return false;
		}
	}
  	
	public static void main(String[] args) throws SQLException {
		OrderSendVipDao dao = new OrderSendVipDao();
		//System.out.println(dao.updatePush("202005091510455306373", "0", "ok"));
		//System.out.println(dao.findOrderRelaBySpid("18673197465", "1022"));
		//System.out.println(dao.getNo());
		//System.out.println(dao.queryPush("202005091510455306373"));
		HashMap<String,String> params = new HashMap<String,String>();
		params.put("USERID", "18673197465");
		params.put("CPID", "youtu");
		params.put("SPID", "1168");
		params.put("SENDMONTH", "202005");
		params.put("RESULTCODE", "2");
		params.put("RESULTINFO", "处理中");
		params.put("ORDERTIME", "20200521164921");
		params.put("ORDERCHANNEL", "yyyjjj");
		//System.out.println(
		//dao.addSendvipRec(params); //);
		//System.out.println(dao.findOrderRel("18673197465", "1022"));
		//System.out.println(dao.updatePush("202005281022397945602", "0","OK"));
		System.out.println(dao.getBusshandleWaitData("0"));
		System.out.println(dao.updBusshandleWaitData("20210615110232000001", "0", "ok"));
	}
}