package com.chinacreator.process.dao;

import com.chinacreator.process.bean.TencentVacOrderBean;
import com.chinacreator.process.util.DataSource;
import com.chinacreator.process.util.JsonUtil;
import com.frameworkset.common.poolman.PreparedDBUtil;
import com.frameworkset.common.poolman.SQLExecutor;
import org.springframework.stereotype.Component;

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

@Component
public class VacOrderDao {

	public void insert(TencentVacOrderBean bean) throws SQLException {
		String sql = "insert into td_vac_order (id,userid,cpid,spid,ordertime,canceltime,status,vacstatus,vaccount,inserttime,vactime) values("
                + "to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#[userid],#[cpid],#[spid],to_date(#[ordertime],'yyyymmddhh24miss'),to_date(#[canceltime],'yyyymmddhh24miss'),#[status],"
                + "1,0,sysdate,'')";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	}
	
	public void order(String ordertime,String id) throws SQLException{
		String sql = "update td_vac_order set ordertime=to_date(?,'yyyymmddhh24miss'),canceltime='',status=0,vacstatus=1,vaccount=0,inserttime=sysdate,vactime='' where id=?";
		SQLExecutor.updateWithDBName(DataSource.NET3G, sql, ordertime,id);
	}
	
	public void cancel(String canceltime,String id) throws SQLException{
		String sql ="update td_vac_order set canceltime=to_date(?,'yyyymmddhh24miss'),status=1,vacstatus=1,vaccount=0,inserttime=sysdate,vactime='' where id=?";
		SQLExecutor.updateWithDBName(DataSource.NET3G, sql, canceltime,id);
	}
	
	public TencentVacOrderBean query(String cpid,String spid,String userid) throws SQLException{
		String sql = "select id,userid,cpid,spid,status,to_char(ordertime,'yyyymmddhh24miss') ordertime, to_char(canceltime,'yyyymmddhh24miss') canceltime,vacstatus,vaccount from td_vac_order where cpid =? and spid=? and userid=?";
		return SQLExecutor.queryObjectWithDBName(TencentVacOrderBean.class, DataSource.NET3G, sql,cpid, spid,userid);
	}

	@SuppressWarnings("unchecked")
	public List<TencentVacOrderBean> query() {
		String sql = "select id,userid,cpid,spid,status,to_char(ordertime,'yyyymmddhh24miss') ordertime, to_char(canceltime,'yyyymmddhh24miss') canceltime,vacstatus,vaccount from td_vac_order where vacstatus=1 and  vaccount <=5 and (vactime is null or (sysdate-vactime)*24*60*60>30*vaccount)";
		PreparedDBUtil pdb = new PreparedDBUtil();
		try {
			return pdb.executeSelectForList(DataSource.NET3G, sql, 0, 500, TencentVacOrderBean.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void update(String id,String vaccount,String vacstatus) throws SQLException {
		String sql = "update td_vac_order set vacstatus=?,vactime=sysdate,vaccount=? where id =?";
		SQLExecutor.updateWithDBName(DataSource.NET3G, sql, vacstatus,vaccount,id);
	}
	
	public static void main(String[] args) throws SQLException {
		VacOrderDao dao = new VacOrderDao();
		TencentVacOrderBean bean = new TencentVacOrderBean();
		bean.setUserid("18574414678");
		bean.setCpid("tencent");
		bean.setSpid("84");
		bean.setStatus("0");
		bean.setOrdertime("20180412135959");
//		dao.update("11111111111", "1", "1");
 		dao.insert(bean);
 		System.out.println(JsonUtil.objectToJson(dao.query()));
	}
}