package com.chinacreator.process.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.math.NumberUtils;
import org.springframework.stereotype.Component;

import com.chinacreator.process.bean.NetOrderBean;
import com.chinacreator.process.bean.OrderBean;
import com.chinacreator.process.util.DataSource;
import com.chinacreator.process.util.JsonUtil;
import com.frameworkset.common.poolman.PreparedDBUtil;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
public class YoutuActiveDao {

	public OrderBean existUser(String userid,String cpid,String spid,String activetype) throws SQLException{
		String sql = "select vipstatus,to_char(viptime,'yyyymmddhh24miss') viptime,userid,cpid,spid,orderstatus ,id,to_char(ordertime,'yyyymmddhh24miss') ordertime,to_char(canceltime,'yyyymmddhh24miss') canceltime,to_char(endtime,'yyyymmddhh24miss') endtime,orderchannel,cancelchannel,vipconstatus,to_char(vipcontime,'yyyymmddhh24miss')  vipcontime, flowstatus from TD_BUSSINESS_HANDLE  where userid =? and cpid =? and spid=? and ACTIVETYPE=? and fhcpid is null and fhspid is null";
		return  SQLExecutor.queryObjectWithDBName(OrderBean.class, DataSource.NET3G, sql, userid,cpid,spid,activetype);
	}
	
	/**
	 * 获取主键ID
	 * @return
	 * @throws SQLException
	 */
	public String getId() throws SQLException{
		String id = "";
		String sql = "select to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval from dual";
		id = SQLExecutor.queryObjectBeanWithDBName(String.class, DataSource.NET3G, sql, null);
		return id;
	}
	
	/**
	 * 新增活动表记录
	 * @param bean
	 * @throws SQLException
	 */
	public void insertBeans(OrderBean bean) throws SQLException{
		String sql =  "insert into  TD_BUSSINESS_HANDLE(id,userid,cpid,spid,province,area,ordertime,effectivetime,activitystatus,activitytime,orderchannel,orderstatus,inserttime,ischarge,flowstatus,activetype,vipstatus,canceltime,endtime)values( "
				+ "#[id], #[userid],#[cpid],#[spid],#[province],#[area], "
				+ "to_date(#[ordertime],'yyyymmddhh24miss'), sysdate, 0,sysdate, "
				+ "#[orderchannel],#[orderstatus],sysdate,2,#[flowstatus],#[activeType],2,to_date(#[canceltime],'yyyymmddhh24miss'), to_date(#[endtime], 'yyyymmddhh24miss') )";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	}
	
	  public List<NetOrderBean> findOrder(String cpid, String userid,String spid) throws SQLException
	  {
	    PreparedDBUtil pdb = new PreparedDBUtil();
	    String sql = "select userid, cpid, spid, province, area, orderchannel, cancelchannel, ordertime, endtime, canceltime,(case when canceltime is null then '0' when endtime >= sysdate then '1' else '2' end) status, ordertype,isexperience,effecttime from TD_ORDER_RELATIONS where cpid=? and userid=? and spid=? order by ordertime desc";
	    pdb.preparedSelect(DataSource.NET3G, sql);
	    pdb.setString(1, cpid);
	    pdb.setString(2, userid);
	    pdb.setString(3, spid);
	    return  pdb.executePreparedForList(NetOrderBean.class);
	  }
	  
	  /**
	   * 更新活动表
	   * @param bean
	   * @throws SQLException
	   */
	  public void update(OrderBean bean) throws SQLException {
	  		String sql1 = "update TD_BUSSINESS_HANDLE set flowstatus = #[flowstatus], ordertime = to_date(#[ordertime],'yyyymmddhh24miss'),canceltime = to_date(#[canceltime],'yyyymmddhh24miss'), "
	  				+" endtime = to_date(#[endtime],'yyyymmddhh24miss'), effectivetime = to_date(#[effectivetime],'yyyymmddhh24miss'), orderchannel = #[orderchannel], cancelchannel= #[cancelchannel], "
					+ " orderstatus = #[orderstatus],vipstatus = #[vipstatus],viptime = to_date(#[viptime],'yyyymmddhh24miss')  where id = #[id]";

		  	//退订
	  		/*
		  	String sql = "update TD_BUSSINESS_HANDLE set canceltime = to_date(#[updateTime],'yyyymmddhh24miss'), "
		  			+" endtime = last_day(trunc(to_date(#[updateTime],'yyyymmddhh24miss')))+1-1/(24*60*60),cancelchannel= #[channel], orderstatus = 1  where id = #[id]";
		  	if("0".equals(bean.getOrderstatus())){ //订购
		  		sql = "update TD_BUSSINESS_HANDLE set flowstatus = #[flowstatus], ordertime = to_date(#[ordertime],'yyyymmddhh24miss'),canceltime = '', "
		  				+" endtime = to_date('20501231235959','yyyymmddhh24miss'),orderchannel = #[orderchannel],cancelchannel = '' "
						+ ",orderstatus = 0,vipstatus = #[vipstatus],viptime = to_date(#[viptime],'yyyymmddhh24miss'), effectivetime = sysdate  where id = #[id]";
		  	}
		  	*/
			SQLExecutor.updateBean(DataSource.NET3G, sql1, bean);
		}
	  
	  public void updatestatus(String id) throws SQLException {
			String sql = "update TD_BUSSINESS_HANDLE set vipstatus=4   where id = ?";
			SQLExecutor.updateWithDBName(DataSource.NET3G, sql, id);
		}
	  
	  /**
	   * 添加要送流量产品订购记录
	   * @param bean
	   * @throws SQLException
	   */
	  public void insertFlowRec(OrderBean bean) throws SQLException{
		  String sql = " INSERT INTO TD_FLOWORDER_REC  (ID,USERID,INSERTTIME,ORDERTIME,CANCELTIME,ORDERSTATUS,CPID,SPID,PROVINCE,AREA,ACTIVETYPE) "+
				  " VALUES (#[id],#[userid],SYSDATE, TO_DATE(#[ordertime],'yyyymmddhh24miss'), TO_DATE(#[canceltime],'yyyymmddhh24miss'), #[orderstatus], #[cpid], #[spid], #[province], #[area],#[activeType]) ";
		  SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	  }
	  
	  /**
	   * 查询是否有记录
	   * @param bean
	   * @return
	   * @throws SQLException
	   */
	  public boolean qryFlowRec(OrderBean bean) throws SQLException{
		  String sql = " SELECT COUNT(1) FROM TD_FLOWORDER_REC  WHERE USERID = ? AND CPID = ? AND SPID = ? ";
		  return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql, bean.getUserid(), bean.getCpid(), bean.getSpid()), 0) > 0;
	  }
	  
	  /**
	   * 查询已存在的记录
	   * @param bean
	   * @return
	   * @throws SQLException
	   */
	  public OrderBean existFlowRec(OrderBean bean) throws SQLException{
		  String sql = " SELECT id, userid, cpid, spid, ordertime,orderstatus,province,area,activeType FROM TD_FLOWORDER_REC  WHERE USERID = ? AND CPID = ? AND SPID = ? AND ACTIVETYPE = ?";
		  return  SQLExecutor.queryObjectWithDBName(OrderBean.class, DataSource.NET3G, sql, bean.getUserid(), bean.getCpid(), bean.getSpid(),bean.getActiveType());
	  }
	  
	  /**
	   * 查询已存在的记录
	   * @param bean
	   * @return
	   * @throws SQLException
	   */
	  public OrderBean existExpireFlowRec(OrderBean bean) throws SQLException{
		  //是订购状态，且订购日期到当前日期相差自然月小于或等于2
		  //String sql = " SELECT id, userid, cpid, spid, ordertime,orderstatus,province,area,activeType FROM TD_FLOWORDER_REC  WHERE USERID = ? AND CPID = ? AND SPID = ? AND ACTIVETYPE = ? AND orderstatus = '0' AND (MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE,'YYYYMM'),'YYYYMM'),TO_DATE(TO_CHAR(ORDERTIME,'YYYYMM'),'YYYYMM'))  <= 2) ";
		  String sql = " SELECT id, userid, cpid, spid, ordertime,orderstatus,province,area,activeType FROM TD_FLOWORDER_REC  WHERE USERID = ? AND CPID = ? AND SPID = ? AND ACTIVETYPE = ? AND orderstatus = '0' ";
		  return  SQLExecutor.queryObjectWithDBName(OrderBean.class, DataSource.NET3G, sql, bean.getUserid(), bean.getCpid(), bean.getSpid(),bean.getActiveType());
	  }
	  
	  /**
	   * 更新订购状态
	   * @param bean
	   * @throws SQLException
	   */
	  public void updateFlowRec(OrderBean bean) throws SQLException{
		  String sql = " UPDATE TD_FLOWORDER_REC SET ORDERSTATUS = #[orderstatus], CANCELTIME = TO_DATE(#[canceltime],'yyyymmddhh24miss') WHERE ID = #[id] ";
		  if("0".equals(bean.getOrderstatus())){ //订购
			  sql = " UPDATE TD_FLOWORDER_REC SET  ORDERSTATUS = #[orderstatus], ORDERTIME = TO_DATE(#[ordertime],'yyyymmddhh24miss') WHERE ID = #[id] ";
		  }
		  SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	  }
	  
	  
	  public static void main(String[] args) {
		  YoutuActiveDao dao = new YoutuActiveDao();
		  OrderBean bean = new OrderBean();
		  bean.setUserid("18673197465");
		  bean.setCpid("youtu");
		  bean.setSpid("1167");
		  bean.setOrderstatus("1");
		  bean.setProvince("北京");
		  bean.setArea("北京");
		  bean.setOrdertime("20190410103625");
		  bean.setCanceltime("20190410104021");
		  try {
			  //dao.insertFlowRec(bean);
			  //dao.updateFlowRec(bean);
			//System.out.println(JsonUtil.objectToJson(dao.existFlowRec(bean)));
			  System.out.println(dao.getId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 }
}