actitve-process/.svn/pristine/f1/f11c860389d1ea0ee305b64062e675cd3aab806e.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.commons.lang.StringUtils;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Component;
import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
public class CommonDao{
	
	/**
	 * 获取接口信息
	 * @param projname 工程名称
	 * @param invokename 接口名称
	 * @param invoketype 接口类型，1外部接口，2内部接口
	 * @return
	 * @throws Exception
	 */
	@Cacheable(value="InvokeUrlInfo", key="#projname+'-'+#invokename+'-'+#invoketype")
	public HashMap getInvokeUrlInfo(String projname, String invokename, String invoketype) throws Exception {
		String sql = "SELECT * FROM NET3G.TB_PROJINVOKEURL_CONF WHERE STATUS = '0' AND PROJNAME = ? AND INVOKENAME = ? AND INVOKETYPE = ? ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, projname, invokename, invoketype);
	}
	
	/**
	 * 查询所有后向配置
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="qryBackBusiAll")
	public List<HashMap> qryBackBusiAll() throws SQLException {
		String sql = "SELECT * FROM TB_BACKBUSI_CONF ";
		return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, null);
	}
	
	/**
	 * 根据SPID查询后向配置
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="qryBackBusiBySpid", key="#spid")
	public HashMap qryBackBusiBySpid(String spid) throws SQLException {
		String sql = "SELECT * FROM TB_BACKBUSI_CONF WHERE SPID = ? ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, spid);
	}
	
	/**
	 * 查询所有订购关系，cpid\spid可以为空
	 * @param userid
	 * @param cpid
	 * @param spid
	 * @param flag 2返回订购和退订未失效，3只返回订购，4只返回已失效，为空或其他值返回所有订购关系（订购中，退订中，已失效）
	 * @return
	 * @throws SQLException
	 */
	public List<HashMap> qryRealByUserid(String userid, String cpid, String spid, String flag) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		String wherestr = " WHERE USERID = ? ";
		paramList.add(userid);
		String sql = "SELECT "+ 
				" CPID, SPID, USERID, PROVINCE, AREA, ORDERCHANNEL, CANCELCHANNEL, ORDERCHANNEL2, CANCELCHANNEL2, "+	  
				" 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 NET3G.TD_ORDER_RELATIONS ";
		if(!StringUtils.isEmpty(cpid)){
			wherestr += " AND CPID = ? ";
			paramList.add(cpid);
		}
		if(!StringUtils.isEmpty(spid)){
			wherestr += " AND SPID = ? ";
			paramList.add(spid);
		}
		if("2".equals(flag)){//返回订购，退订未失效
			wherestr += " AND ( ENDTIME IS NULL OR ENDTIME >= SYSDATE )";
		}else if("3".equals(flag)){//3返回订购
			wherestr += " AND ENDTIME IS NULL ";
		}else if("4".equals(flag)){
			wherestr += " AND ( ENDTIME IS NULL OR ENDTIME < SYSDATE )";
		}
		sql += wherestr;
		//System.out.println(sql);
		Object [] params = paramList.toArray(new Object[paramList.size()]);
		return SQLExecutor.queryListWithDBName(HashMap.class, DataSource.NET3G, sql, params);
		//return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, params );
	}
	
	@Cacheable(value="qrySpinfoBySpid", key="#spid")
	public HashMap qrySpinfoBySpid(String spid) throws SQLException{
		String sql = "SELECT * FROM net3g.TB_SP_INFO WHERE SPID = ? ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql, spid);
	}
	
	/**
	 * 获取根据SPID获取合约产品的配置信息
	 * @param spid
	 * @return
	 * @throws SQLException
	 */
	@Cacheable(value="qryHyProduct", key="#spid")
    public HashMap qryHyProduct(String spid) throws SQLException {
	    String sql=" SELECT TO_CHAR(MONTH) MONTH, CPID, SPID, EXTEND1 WYJSPID FROM TB_PRODUCT_CANCEL_CONF WHERE SPID = ? AND STATUS = '0' ";
        return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql,  spid);
    }
	
	public static void main(String[] args) throws SQLException {
		CommonDao dao = new CommonDao();
		System.out.println(dao.qryRealByUserid("18673197465", null, null, null));
	}
}