package com.chinacreator.videoalliance.order.dao;

import com.chinacreator.common.dao.ExecutorDao;
import com.chinacreator.common.exception.BusinessException;
import com.chinacreator.videoalliance.common.util.DataSource;
import com.chinacreator.videoalliance.order.bean.BackBusiOrderRec;
import com.chinacreator.videoalliance.order.bean.OrderInfo;
import com.chinacreator.videoalliance.order.bean.OrderLog;
import com.frameworkset.common.poolman.SQLExecutor;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

import java.io.ByteArrayOutputStream;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 优酷后向产品
 * @author xu.zhou
 * @date 20190516
 */
@Component
public class BackBusiOrderDao extends ExecutorDao {

	private static Logger logger = Logger.getLogger("orderError");

	public List<OrderInfo> findByUser(String userid) throws SQLException {
		return getExecutor().queryListWithDBName(OrderInfo.class, "net3g", "findByUser", new Object[] { userid });
	}

	public void order(OrderInfo orderInfo) throws Exception {
		getExecutor().insertBean("net3g", "order", orderInfo);
	}

	public HashMap getCpSp(String cpid, String spid) throws SQLException{
		return (HashMap)getExecutor().queryObjectWithDBName(HashMap.class, "net3g", "getCpSp", new Object[] { cpid, spid });
	}

	public HashMap findByUserAndSpid(String userid, String cpid, String spid) throws SQLException {
		return (HashMap)getExecutor().queryObjectWithDBName(HashMap.class, "net3g", "findByUserAndSpid", new Object[] { userid, cpid, spid });
	}

	public List<OrderInfo> findByUserAndCpid(String userid, String cpid) throws SQLException {
		return getExecutor().queryListWithDBName(OrderInfo.class, "net3g", "findByUserAndCpid", new Object[] { userid, cpid });
	}

	public List<BackBusiOrderRec> findOrderRecByUserid(String userid) throws SQLException {
		return getExecutor().queryListWithDBName(BackBusiOrderRec.class, "net3g", "findOrderRecByUserid", new Object[] { userid });
	}

	/**
	 * 根据订单ID查询订单信息
	 * @param orderid
	 * @return
	 * @throws SQLException
	 */
	public List<BackBusiOrderRec> findOrderRecByOrderid(String orderid) throws SQLException {
		return getExecutor().queryListWithDBName(BackBusiOrderRec.class, "net3g", "findOrderRecByOrderid", new Object[] { orderid });
	}

	/**
	 * 保存订购日志
	 * @param orderLog
	 * @throws SQLException
	 * @throws BusinessException
	 */
	public void addOrderLog(OrderLog orderLog) throws SQLException  {
		this.getExecutor().insertBean(DataSource.NET3G, "addOrderLog", orderLog);
	}

	/**
	 * 添加订购记录
	 * @param orderInfo
	 * @throws BusinessException
	 */
	public void addOrderRec(BackBusiOrderRec bean) throws Exception {
		try{
			this.getExecutor().insertBean(DataSource.NET3G, "addOrderRec", bean);
		}catch(Exception e){
			ByteArrayOutputStream buf=new ByteArrayOutputStream();
			e.printStackTrace(new PrintWriter(buf,true));
			String expMessage=buf.toString();
			if(expMessage.indexOf("ORA-00001")!=-1){  //
				//System.out.println("=============+++++++++++++++====违反唯一约束");
				throw new BusinessException("9001", "重复订单，不处理");
			}else{
				throw e;
			}
		}
	}

	/**
	 * 更新订购信息表
	 * @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();
			logger.error("orderId: "+orderBean.getId()+", 更新订购信息表出现异常，"+e.getMessage());
		}
	}

	/**
	 * 获取当前时间
	 * @return
	 * @throws SQLException
	 */
	public String getCurrentTime() throws SQLException {
		String sql = "select to_char(sysdate, 'yyyymmddhh24miss') from dual";
		return SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql);
	}

	/**
	 * 获取从当前天起31天的有效期
	 * @return
	 * @throws SQLException
	 */
	public String curr31Day() throws SQLException{
		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "curr31Day",  new Object[] {});
	}

	/**
	 * 获取从指定时间加31天有效期
	 * @param endtime
	 * @return
	 * @throws SQLException
	 */
	public String endtime31Day(String endtime) throws SQLException{
		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "endtime31Day",  new Object[] {endtime});
	}

	/**
	 * 获取从当前天起加多少天的有效期
	 * @param days	加多少天
	 * @return
	 * @throws SQLException
	 */
	public String currParamDay(String days) throws SQLException{
		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "currParamDay",  new Object[] {days});
	}

	/**
	 * 获取从指定时间加多少天有效期
	 * @param days	加多少天
	 * @param endtime	起始日期
	 * @return
	 * @throws SQLException
	 */
	public String endtimeParamDay(String days,String endtime) throws SQLException{
		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "endtimeParamDay",  new Object[] {days,endtime});
	}


	/**
	 * 获取当月最后一秒的时间
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public String endtimeTheMonthLastDay()throws SQLException{

		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "endtimeTheMonthLastDay",  new Object[] {});
	}

	/**
	 * 获取当月最后一秒的时间
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public String endtimeNextMonthLastDay(String endtime)throws SQLException{

		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "endtimeNextMonthLastDay",  new Object[] {endtime});
	}


	/**
	 * 生成ID
	 * @return
	 * @throws SQLException
	 */
	public String generateID() throws SQLException {
		String sql = "SELECT TO_CHAR(SYSDATE,'yyyyMMddhh24miss')||SEQ_COMMON6.NEXTVAL FROM DUAL";
		return SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql);
	}

	/**
	 * 获取活动配置表数据
	 * @param cpid
	 * @param spid
	 * @param province
	 * @return
	 * @throws SQLException
	 */
	public Map getActivityInfo(String cpid, String spid, String province) throws SQLException{
		String sql = "select cpid,spid,to_char(begintime,'yyyymmddhh24miss') begintime,to_char(endtime,'yyyymmddhh24miss') endtime,to_char(endtime,'yyyymmddhh24miss') cancelendtime ,activetype,province from TB_ACTIVITY_CONFIG where cpid = ? and spid = ? and province in ('0',?) and status =0 ";
		return SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql,new Object[] { cpid, spid, province});
	}

	/**
	 * 根据ORDERID查询会员赠送状态
	 * @param orderid
	 * @return
	 * @throws SQLException
	 */
	public String getVipstatus(String orderid) throws SQLException{
		String sql = "SELECT VIPSTATUS FROM TD_BACKBUSI_ORDER_REC WHERE ORDERID = ? ";
		return SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, new Object[] {orderid});
	}

	/**
	 * 获取后向产品配置信息
	 * @param cpid
	 * @param spid
	 * @return
	 * @throws SQLException
	 */
	public List<HashMap> getBackBusiConf(String cpid, String spid) throws SQLException {
		return getExecutor().queryListWithDBName(HashMap.class, "net3g", "getBackBusiConf", new Object[] { cpid, spid });
	}


	/**
	 *
	 * @param vipStatus
	 * @param resultCode
	 * @param resultInfo
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean updBusiStatus(String vipStatus, String resultCode, String resultInfo, String id) throws SQLException {
		//赠送会员状态，0成功，1未赠送，2赠送中，3失败，4不赠送
		String sql = " UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = ? , RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vipStatus, resultCode, resultInfo, id);
		return ((Integer)obj)>0 ? true : false;
	}

	/**
	 * 更新订购记录表
	 * @param vipstatus 赠送会员状态
	 * @param resultCode 处理结果
	 * @param resultInfo
	 * @param orderId 订单ID
	 * @param busitype 业务类型
	 * @throws SQLException
	 */
//		public void updOrderRec(String vipstatus, String resultCode, String resultInfo, String orderId, String busitype) throws SQLException {
//				String sql = " UPDATE TD_BACKBUSI_ORDER_REC SET VIPSTATUS = ?, RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
//				Object[] obj = new Object[]{vipstatus, resultCode, resultInfo, orderId};
//				if("2".equals(busitype)){
//					sql = " UPDATE TD_BACKBUSI_ORDER_REC SET RESULTCODE = ?, RESULTINFO = ? WHERE ID = ? ";
//					obj = new Object[]{resultCode, resultInfo, orderId};
//				}
//				//Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vipstatus, resultCode, resultInfo, orderId);
//				SQLExecutor.updateWithDBName(DataSource.NET3G, sql, obj);
//		}

	/**
	 * 获取两个时间相差分钟数
	 * @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;
		}
	}

	/**
	 * 获取从指定时间减31天后的日期
	 * @param endtime
	 * @return
	 * @throws SQLException
	 */
	public String endtimeMinus31Day(String endtime) throws SQLException{
		return getExecutor().queryFieldWithDBName(DataSource.NET3G, "endtimeMinus31Day",  new Object[] {endtime});
	}

	/**
	 * 根据手机号码、CPID、SPID查找有效的订购关系
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public Map findYoutuOrderRal(BackBusiOrderRec bean) throws SQLException{
		return getExecutor().queryObjectBeanWithDBName(HashMap.class, DataSource.NET3G, "findYoutuOrderRal", bean);
	}

	/**
	 * 退订
	 * @param orderInfo
	 * @throws Exception
	 */
	public void cancel(OrderInfo orderInfo) throws Exception {
		getExecutor().insertBean("net3g", "cancel", orderInfo);
	}

	/**
	 * 获取开始时间
	 * @param endtime
	 * @param netdays //免流天数
	 * @return
	 * @throws SQLException
	 */
	public String getStartTime(String endtime, String netdays)throws SQLException{
		String sql = " SELECT TO_CHAR(TO_DATE(?,'YYYYMMDDHH24MISS')-?+2/(24*60*60),'YYYYMMDDHH24MISS') FROM DUAL ";
		return SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, new Object[] {endtime,netdays});
	}


	/**
	 * 获取快手产品开始时间
	 * @return
	 * @throws SQLException
	 */
	public String getKsStartTime()throws SQLException{
		String sql = " SELECT TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDDHH24MISS') FROM DUAL ";
		return SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, new Object[] {});
	}

	/**
	 * 更新领取会员最后时间
	 * @param userid
	 * @param endtime
	 * @return
	 * @throws SQLException
	 */
	public boolean updVipEndtime(String userid, String endtime)throws SQLException{
		String sql = "UPDATE TD_BACKBUSI_ORDER_REC SET VIPENDTIME = TO_DATE(?,'yyyymmddhh24miss') WHERE CHANNEL = 'TX20_twback' AND BUSITYPE = '3' AND USERID = ? AND VIPSTATUS NOT IN ('0','8') AND VIPENDTIME IS NOT NULL AND VIPENDTIME >= SYSDATE";
		Object obj = SQLExecutor.updateWithDBName(DataSource.NET3G, sql, endtime,userid);
		return ((Integer)obj)>0 ? true : false;
	}

	/**
	 * 更新上海2元权益报文表
	 * @param orderno
	 * @param logid
	 * @throws SQLException
	 */
	public void updShInPaInfo(BackBusiOrderRec bean){
		try{
			this.getExecutor().updateWithDBName(DataSource.NET3G, "updShInPaInfo", new Object[] {bean.getId(),bean.getLogid()});
		}catch(Exception e){}
	}

    /**
     * 是否向省里推送订购关系
     * @param cpid
     * @param spid
     */
    public boolean orderPush(String cpid, String spid) throws SQLException {
        String sql="select ORDERPUSH from TB_BACKBUSI_CONF t  where CPID=? AND SPID=?";
        String orderPush = SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, cpid, spid);
        return "0".equals(orderPush);

    }

    /**
     * 获取产品id
     * @param spid
     */
    public String getProduct( String spid) throws SQLException {
        String sql="select  product_id from tb_sp_aop_config where sp_id=? ";
        String orderPush = SQLExecutor.queryObjectWithDBName(String.class, DataSource.NET3G, sql, spid);
        return orderPush;

    }

	public static void main(String[] args) {
		try {
			BackBusiOrderDao dao = new BackBusiOrderDao();
			// System.out.println(dao.findByUserAndSpid("18673197465", "youtu", "1167"));
			//System.out.println(dao.findOrderRecByUserid("18673197465"));
			OrderInfo orderInfo = new OrderInfo();
			orderInfo.setUserid("18673197465");
			orderInfo.setProvince("湖南");
			orderInfo.setArea("长沙");
			orderInfo.setOrderid("123456789");
			orderInfo.setType(0);
			//dao.addOrderRec(orderInfo);
			//System.out.println(dao.endtime31Day("20190620235959"));
			//System.out.println(dao.findByUserAndSpid("18673197465", "youtu", "1167"));
			//System.out.println(dao.getCpSp("youtu", "140"));
			//System.out.println(dao.getActivityInfo("youtu", "1167", "湖南"));
			//System.out.println(dao.generateID());
			//String vipstatus = dao.getVipstatus("028b4f73-14bc-4450-afaa-81a323ef9d24");
			//System.out.println(vipstatus);
			//System.out.println(dao.getBackBusiConf("test_01", "youtu", "1168"));
			//dao.updOrderRec("3", "9002", "失败", "123456", "2");
			//CPDao cd = new CPDao();
			//System.out.println(cd.findById("youtu"));
			//String sql = "SELECT * FROM  TB_CP_ACCOUNT_CONFIG a, tb_sp_info b where a.cpid=b.cpid and b.cpid= 'youtu' and b.spid= '141' ";
			//System.out.println(dao.getExecutor().queryObjectWithDBName(HashMap.class, "net3g", "sql", new Object[] { "youtu", "140" }));
			//System.out.println(SQLExecutor.queryListBeanWithDBName(List.class, "net3g", sql, null));
			//System.out.println(SQLExecutor.queryObjectWithDBName(HashMap.class, DataSource.NET3G, sql,new Object[] {}));
			//System.out.println(dao.endtimeParamDay("1","20190801235959"));
			System.out.println(dao.getStartTime("20191126235959","3"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}