actitve-process/.svn/pristine/c2/c2b0a706df13a43ac87ba6cc715beea9b7446bf5.svn-base

package com.chinacreator.process.dao;

import com.chinacreator.process.bean.OrderLog;
import com.chinacreator.process.bean.PointShopOrderBean;
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.util.HashMap;
import java.util.List;

@Component
public class PointShopDao {

    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);
    }

    /**
     * 更新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 PointShopOrderBean getOrderRec(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_POINTS_ORDER_REC WHERE ID = ? ";
//		return  SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G,sql,orderId);
//	}

    /**
     * 获取赠送会员状态
     *
     * @param orderId
     * @return
     * @throws SQLException
     */
    public String getBackVipstatus(String id) throws SQLException {
        String sql = "SELECT VIPSTATUS FROM TD_BACKBUSI_ORDER_REC WHERE ID = ? ";
        return SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, id);
    }

    /**
     * 获取赠送会员处理结果
     *
     * @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 id
     * @param tabname 表名
     * @return
     * @throws SQLException
     */
//	public boolean updVipstatus(String vipstatus, String id, String tabname) throws SQLException {
//		//赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
//		String sql = " UPDATE "+tabname+" SET VIPSTATUS = ? WHERE ID = ? ";
//		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vipstatus, id);
//		return ((Integer)obj)>0 ? true : false;
//	}

    /**
     * 更新业务状态信息
     *
     * @param resultCode
     * @param resultInfo
     * @param id
     * @param tabname
     * @return
     * @throws SQLException
     */
    public boolean updBusiStatus(String resultCode, String resultInfo, String id, String tabname) throws SQLException {
        //赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
        String sql = " UPDATE " + tabname + " 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(String vipStatus, String resultCode, String resultInfo, String id, 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, vipStatus, resultCode, resultInfo, id);
//		return ((Integer)obj)>0 ? true : false;
//	}
    public boolean updBusiStatus(PointShopOrderBean bean, String tabname) {
        try {
            //赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
            String sql = " UPDATE " + tabname + " SET VIPSTATUS = ? , RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
            Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, bean.getVipstatus(), bean.getResultCode(), bean.getResultInfo(), bean.getId());
            return ((Integer) obj) > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("orderId: " + bean.getId() + ", 更新订购信息表出现异常，" + e.getMessage());
        }
        return 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);
//	}
//	

    /**
     * 获取业务配置信息
     *
     * @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);
    }

    /**
     * 获取当前时间
     *
     * @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 {
        PointShopDao dao = new PointShopDao();
        //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.currParamDay("4"));
    }

    /**
     * 获取从当天算起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 resultCode
     * @param resultInfo
     * @param orderId
     * @return
     * @throws SQLException
     */
    public void updOrderRec(PointShopOrderBean bean) {
        try {
            String sql = " UPDATE TD_POINTS_ORDER_REC SET FINISHTIME = SYSDATE, VIPSTATUS = ?, RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
            Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, bean.getVipstatus(), bean.getResultCode(), bean.getResultInfo(), bean.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("Id: " + bean.getId() + ", 更新积分商城订购信息表出现异常，" + e.getMessage());
        }
    }

    /**
     * 更新后向产品订购信息表
     *
     * @param resultCode
     * @param resultInfo
     * @param orderId
     * @return
     * @throws SQLException
     */
    public void updBackOrderRec(PointShopOrderBean bean) {
        try {
            String sql = " UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = ?, RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
            Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, bean.getVipstatus(), bean.getResultCode(), bean.getResultInfo(), bean.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("id: " + bean.getId() + ", 更新后向订购信息表出现异常，" + e.getMessage());
        }
    }

    /**
     * 新增或更新订购关系
     *
     * @param bean
     * @throws SQLException
     */
    public void order(PointShopOrderBean bean) 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, " +
                "	CDNSUCCEED = '1', SYNCSUCCEED = '1',CDNCOUNT = '0', SYNCCOUNT = '0', CACHESUCCEED = '1', ORDERCHANNEL = #[orderchannel] " +
                "	WHEN NOT MATCHED THEN INSERT(ID,CPID,SPID,USERID,ORDERTIME,EFFECTTIME,ENDTIME,STATUS,PROVINCE,AREA,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],#[orderchannel],0,1,1,'3','1',0,1,'1','0') ";
        SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
    }

    /**
     * 记录订购日志
     *
     * @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 days 加多少天
     * @return
     * @throws SQLException
     */
    public String currParamDay(String days) throws SQLException {
        String sql = "SELECT TO_CHAR(?+TRUNC(SYSDATE)-1/86400,'yyyymmddhh24miss') FROM DUAL";
        return SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, days);
    }

    /**
     * 获取从指定时间加多少天有效期
     *
     * @param days    加多少天
     * @param endtime 起始日期
     * @return
     * @throws SQLException
     */
    public String endtimeParamDay(String days, String endtime) throws SQLException {
        String sql = "SELECT TO_CHAR(?+TO_DATE(?,'yyyymmddhh24miss'),'yyyymmddhh24miss')  FROM DUAL";
        return SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, days, endtime);
    }

    /**
     * 获取结束时间
     *
     * @param orderId
     * @return
     * @throws SQLException
     */
    public String getBackEndtime(String id) throws SQLException {
        String sql = " SELECT TO_CHAR(ENDTIME,'YYYYMMDDHH24MISS') FROM TD_BACKBUSI_ORDER_REC WHERE ID = ? ";
        return SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, id);
    }

    public String endtimeTheMonthLastDay() throws SQLException {
        String sql = "SELECT TO_CHAR(TRUNC(LAST_DAY(sysdate),'dd')+1-1/24/60/60,'yyyymmddhh24miss')  FROM DUAL";
        return SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql);
    }

    public String endtimeNextMonthLastDay(String currEndtime) throws SQLException {
        String sql="select to_char(last_day(add_months(trunc(TO_DATE(?, 'yyyymmddhh24miss')),1)+1-1/24/60/60),'yyyymmddhh24miss') from dual";
        return SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, currEndtime);
    }
		
		
		/*******************************20210118积分商城异步处理开始*****************************/
		
		/**
		 * 查询要异步赠送会员的数据
		 * @param trycount
		 * @return
		 */
		public List<HashMap> getVipAsynData(String trycount){
			//赠送会员状态，0成功，1待赠送，2赠送中，3失败，8已冻结
			String sql = " SELECT * FROM (SELECT ID, USERID, ORDERID,CHANNEL, CPID, SPID, VIPSTATUS, BUSITYPE, RETRYCOUNT, TO_CHAR(ENDTIME, 'YYYYMMDDHH24MISS') ENDTIME FROM TD_BACKBUSI_ORDER_REC WHERE INSERTTIME > SYSDATE - 10 "+
					" AND CHANNEL = 'CHSvideoD00' "+
					" AND RESULTCODE NOT IN ('0','2') "+
					" AND VIPSTATUS NOT IN ('0') "+
					" AND RETRYCOUNT < ? "+
					" AND (RETRYTIME IS NULL OR TO_NUMBER((SYSDATE - RETRYTIME)*24*60) >=2) "+ 
					" ORDER BY INSERTTIME) "+
					" WHERE ROWNUM < 1000 ";
			PreparedDBUtil pdb = new PreparedDBUtil();
			try {
				pdb.preparedSelect(DataSource.NET3G, sql);
				pdb.setInt(1, Integer.parseInt(trycount));
				return pdb.executePreparedForList(HashMap.class);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
		
		/**
		 * 更新业务处理状态
		 * @param vipstatus
		 * @param reesultCode
		 * @param resultInfo
		 * @param id
		 * @param tabname
		 * @param time
		 * @throws SQLException
		 */
		public void updSendVipRes(String vipstatus, String reesultCode, String resultInfo, String id, String tabname, String time) throws SQLException {
			if("TD_POINTS_ORDER_REC".equals(tabname)){
				String sql = " UPDATE TD_POINTS_ORDER_REC SET FINISHTIME = SYSDATE, VIPSTATUS = ?, RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
				Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vipstatus, reesultCode,resultInfo, id);
			}else{
				String sql = " UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = ?, RESULTCODE = ?, RESULTINFO = ?, RETRYCOUNT = RETRYCOUNT+1, RETRYTIME = SYSDATE, TIMES = ? WHERE ID = ? ";
				Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vipstatus, reesultCode,resultInfo, time, id);		
			}
		}
		
		/**
		 * 更新赠送会员状态
		 * @param bean
		 * @param tabname
		 * @return
		 * @throws SQLException
		 */
		public boolean updSendVipStatus(PointShopOrderBean bean, 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, bean.getVipstatus(), bean.getResultCode(), bean.getResultInfo(), bean.getId());
			return ((Integer)obj)>0 ? true : false;
		}
		
		/*******************************20210118积分商城异步处理结束*****************************/
}