actitve-process/.svn/pristine/03/032319e2c739d570ef6e70830fdafdca9ccbf7f3.svn-base

package com.chinacreator.process.dao;

import com.chinacreator.common.dao.CacheableDao;
import com.chinacreator.common.support.cache.annotation.CacheName;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;

import org.apache.commons.lang.StringUtils;
import org.springframework.cache.annotation.Cacheable;
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
@CacheName("MqSmsConf")
public class MqSmsTempConfDao extends CacheableDao{
	
	/**
	 * 根据BUSITYPE查询短信模板配置信息
	 * @param spid
	 * @return
	 * @throws SQLException
	 */
	/***
	@Cacheable(value="mqsmstemp_tempinfo_busitype", key="#busitype")
	public HashMap getSmsTempInfoByBusitype(String busitype) throws SQLException {
		String sql = "SELECT SMSTEMPNAME, SMSTEMPID, PARAMS, PARAMSMAPP FROM TB_SMSTEMP_CONF WHERE STATUS = '0' AND BUSITYPE = ? ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, busitype);
	}
	***/
	/**
	 * 获取办理渠道配置信息
	 * @param busitype
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="getChannelConfByBusitype",key="#busitype")
	public HashMap getChannelConfByBusitype(String busitype) throws SQLException {	
		String sql = " SELECT * FROM TB_SMSTEMP_TRANCHANNEL_CONF T1 WHERE T1.STATUS = '0' AND T1.BUSITYPE = ? ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class,DataSource.NET3G, sql,busitype);
	}
	/**
	 * 根据SPID和BUSITYPE获取短信配置信息
	 * @param spid
	 * @param busitype	 
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="mqsmstempconf",key="#spid+'-'+#busitype")
	public List<HashMap> getConfListBySpid(String spid, String busitype) throws SQLException {
		String sql = " SELECT " +
				" T1.CPID, T1.SPID, T1.CHANNEL, T1.SUBCHANNEL, T1.PROVINCE, T1.BUSITYPE, T1.TEMPTYPE, T1.MQPARAMSMAPP , " +
				" TO_CHAR(T1.STARTTIME, 'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(T1.ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, " +
				" T2.SPNAME, TO_CHAR(T2.PRICE) PRICE, TO_CHAR(T2.MAXFLOW) MAXFLOW, " +
				" T3.SMSCPNAME, " +
				" T4.SMSTEMPID, T4.PARAMS, T4.PARAMSMAPP " +
				" FROM TB_MQ_SMSTEMP_CONF T1 " +
				" LEFT JOIN TB_SP_INFO T2 ON T1.SPID = T2.SPID " +
				" LEFT JOIN TB_CP_INFO T3 ON T1.CPID = T3.CPID " +
				" LEFT JOIN TB_SMSTEMP_CONF T4 ON T1.TEMPTYPE = T4.TEMPTYPE AND T4.STATUS = '0' " +
				" WHERE " +
				" T1.STATUS = '0' AND T1.SPID = ? AND T1.BUSITYPE = ? " +
				" ORDER BY T1.INSERTTIME DESC ";
		return SQLExecutor.queryListWithDBName(HashMap.class,DataSource.NET3G, sql,spid,busitype);
	}
	
	/**
	 * 根据SPID和BUSITYPE获取二次短信配置信息
	 * @param spid
	 * @param busitype	 
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="mqsmssectempconf",key="#spid+'-'+#busitype")
	public List<HashMap> getSecConfListBySpid(String spid, String busitype) throws SQLException {
		String sql = " SELECT " +
				" T1.CPID, T1.SPID, T1.CHANNEL, T1.SUBCHANNEL, T1.PROVINCE, T1.BUSITYPE, T1.TEMPTYPE, T1.MQPARAMSMAPP , T1.SMSWGNAME, " +
				" TO_CHAR(T1.STARTTIME, 'YYYYMMDDHH24MISS') STARTTIME, TO_CHAR(T1.ENDTIME,'YYYYMMDDHH24MISS') ENDTIME, " +
				" T2.SPNAME, TO_CHAR(T2.PRICE) PRICE, TO_CHAR(T2.MAXFLOW) MAXFLOW, " +
				" T3.SMSCPNAME, " +
				" T4.SMSTEMPID, T4.PARAMS, T4.PARAMSMAPP " +
				" FROM TB_MQ_SECSMSTEMP_CONF T1 " +
				" LEFT JOIN TB_SP_INFO T2 ON T1.SPID = T2.SPID " +
				" LEFT JOIN TB_CP_INFO T3 ON T1.CPID = T3.CPID " +
				" LEFT JOIN TB_SMSTEMP_CONF T4 ON T1.TEMPTYPE = T4.TEMPTYPE AND T4.STATUS = '0' " +
				" WHERE " +
				" T1.STATUS = '0' AND T1.SPID = ? AND T1.BUSITYPE = ? " +
				" ORDER BY T1.INSERTTIME DESC ";
		return SQLExecutor.queryListWithDBName(HashMap.class,DataSource.NET3G, sql,spid,busitype);
	}

	/**
	 * 更新华胜推送表数据，只更新24小时内入库的数据
	 * 用于华胜推送，只有订购退订短信发送成功后才推送。
	 * @param userid 手机号码
	 * @param spid
	 * @param busitype 短信业务类型
	 * @param pushhour 时间间隔	 
	 * @throws SQLException
	 */
	public boolean updHuaShengWaitInfo(String userid, String spid, String busitype, String pushhour) {
		try {
    		String ordertype = ""; //0订购，1退订
    		if( "tran_succ".equals(busitype) 
    				|| "ftp_tran_succ".equals(busitype) 
    				|| "cap_tran_succ".equals(busitype) 
    				|| "vac_tran_succ".equals(busitype)
    				|| "kafka_tran_succ".equals(busitype)
    				|| "vs_tran_succ".equals(busitype)
    				//以指定标识开头
    				|| busitype.indexOf("tran_succ") == 0
    				|| busitype.indexOf("ftp_tran_succ") == 0
    				|| busitype.indexOf("cap_tran_succ") == 0
    				|| busitype.indexOf("vac_tran_succ") == 0
    				|| busitype.indexOf("kafka_tran_succ") == 0
    				){
    			ordertype = "0";
    		}else if("cancel_succ".equals(busitype) 
    				|| "ftp_cancel_succ".equals(busitype) 
    				|| "cap_cancel_succ".equals(busitype) 
    				|| "vac_cancel_succ".equals(busitype)
    				|| "kafka_cancel_succ".equals(busitype)
    				|| "vs_cancel_succ".equals(busitype)
    				//以指定标识开头
    				|| busitype.indexOf("cancel_succ") == 0
    				|| busitype.indexOf("ftp_cancel_succ") == 0
    				|| busitype.indexOf("cap_cancel_succ") == 0
    				|| busitype.indexOf("vac_cancel_succ") == 0
    				|| busitype.indexOf("kafka_cancel_succ") == 0    				
    				){
    			ordertype = "1";
    		}
    		if(StringUtils.isEmpty(pushhour)){
    			pushhour = "1";
    		}else{
    			//验证是否数字
    			try {
    				Integer.parseInt(pushhour);
				} catch (Exception e) {
					pushhour = "1";
				}
    		}
    		if(!"".equals(ordertype)){
				String sql = " UPDATE TD_HUASHENGSYNC_WAIT SET RESULTCODE = '1' , UPDTIME = SYSDATE WHERE RESULTCODE = '3' AND INSERTTIME > SYSDATE - ?/24 " +
							" AND USERID = ? AND SPID = ? AND ORDERTYPE = ? ";
				Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, pushhour, userid, spid, ordertype);
				return ((Integer) obj) > 0 ? true : false;
    		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * 获取子模板信息，退订成功时的营销短信模板内容
	 * @param temptype	 
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="qryCancelYxConf",key="#busitype")
	public List<HashMap> qryCancelYxConf(String busitype) throws SQLException {
		String sql = " SELECT  T1.PHCPID, T1.HYSPID, T1.BUSITYPE, T1.TEMPTYPE, T1.MQPARAMSMAPP , "+
			"  T2.SPNAME, TO_CHAR(T2.PRICE) PRICE, TO_CHAR(T2.MAXFLOW) MAXFLOW, "+
			"  T3.SMSCPNAME, "+
			"  T4.SMSTEMPID, T4.PARAMS, T4.PARAMSMAPP "+
			"  FROM TB_SMSTEMP_CANCELYX_CONF T1 "+
			"  LEFT JOIN TB_SP_INFO T2 ON T1.HYSPID = T2.SPID "+
			"  LEFT JOIN TB_CP_INFO T3 ON T1.PHCPID = T3.CPID "+
			"  LEFT JOIN TB_SMSTEMP_CONF T4 ON T1.TEMPTYPE = T4.TEMPTYPE AND T4.STATUS = '0' "+
			"  WHERE "+
			"  T1.STATUS = '0' AND T1.BUSITYPE = ? "+
			"  ORDER BY T1.INSERTTIME DESC ";
		return SQLExecutor.queryListWithDBName(HashMap.class,DataSource.NET3G, sql, busitype);
	}
	
	/**
	 * 添加营销数据
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean addCancelyxRec(HashMap<String, String> params) throws SQLException {
		String sql = "INSERT INTO TD_SMSTEMP_CANCELYX_REC (ID, USERID, SPID, SPNAME, PHCPID, VARGS, BUSITYPE, RESULTCODE, ERRORINFO, OUTPARAMS, HYSPID) "+ 
				" VALUES(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || SEQ_COMMON6.NEXTVAL,?,?,?,?,?,?,?,?,?,?)";
		Object obj = SQLExecutor.insertWithDBName(DataSource.NET3G, sql, 
				params.get("USERID"), params.get("SPID"), params.get("SPNAME"), params.get("PHCPID"), params.get("VARGS"), 
				params.get("BUSITYPE"), params.get("RESULTCODE"), params.get("ERRORINFO"), params.get("OUTPARAMS"), params.get("HYSPID"));
		return ((Integer) obj) > 0 ? true : false;
	}
	
	/**
	 * 是否可以发送营销短信
	 * @param userid
	 * @param hyspid
	 * @param num
	 * @return
	 * @throws SQLException
	 */
	public boolean hasRetrySendSms(String userid, String hyspid, Integer num)throws SQLException {
		boolean res = true;
        String sql = " SELECT TO_CHAR(ADD_MONTHS(MAX(INSERTTIME), ? ),'YYYYMMDDHH24MISS') SENDTIME FROM TD_SMSTEMP_CANCELYX_REC  "+ 
						" WHERE USERID = ? AND HYSPID = ? AND RESULTCODE = '0' ";
        //获取最大的发送日期
    	String sendtime = SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, num, userid, hyspid);
    	if(!StringUtils.isEmpty(sendtime)){
    		res = false;
    		//判断当前时间是否大于发送日期
    		String currtime = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
    		if(Long.parseLong(currtime) > Long.parseLong(sendtime)){
    			res = true;
    		}
    	}
    	
    	return res;
	}
	
    /**
     * 合约产品退订后是否可以再次订购，与videoif的orderservice判断保持一致
     * @param userid
     * @param spid
     * @param num
     * @return
     * @throws SQLException
     */
    public boolean hasRetryOrder(String userid, String spid, Integer num) throws SQLException {
        String sql = " SELECT COUNT(1) CNT FROM TD_ORDER_RELATIONS  "+ 
        				" WHERE USERID = ? AND SPID = ? AND SYSDATE > TO_DATE(TO_CHAR(ADD_MONTHS(ORDERTIME, ? ),'YYYYMM'),'YYYYMM') ";
        return Integer.parseInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, userid, spid,num)) > 0;
    }
	
	public static void main(String[] args) {
		MqSmsTempConfDao dao = new MqSmsTempConfDao();
		try {
			//System.out.println(dao.updHuaShengWaitInfo("18774826258","1051","tran_succ","5"));
			//System.out.println(dao.getInfoByCpid("tencent"));
			System.out.println(dao.hasRetrySendSms("18774826254","1011",1));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}