package com.chinacreator.process.dao;

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

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

@Component
public class NewYearActiveDao {

	public List<OrderBean> existUser(String userid,String cpid,String spid) throws SQLException{
		String sql = "select userid,cpid,spid,orderstatus ordertype,id,to_char(ordertime,'yyyymmddhh24miss') updatetime,usertype,to_char(oldcanceltime,'yyyymmddhh24miss') oldcanceltime,orderchannel  channel,ischarge, "
				+ "to_char(endtime,'yyyymm') endtime,activetype,vipstatus from TD_BUSSINESS_HANDLE where userid = ? and cpid = ? and spid = ? and fhcpid is null and fhspid is null";
		return  SQLExecutor.queryListWithDBName(OrderBean.class, DataSource.NET3G, sql, userid,cpid,spid);
	}
	
	public List<OrderBean> existUserBestv(String userid,String cpid,String spid) throws SQLException{
		String sql = "select userid,cpid,spid,orderstatus ordertype,id,to_char(ordertime,'yyyymmddhh24miss') updatetime,usertype,to_char(oldcanceltime,'yyyymmddhh24miss') oldcanceltime,orderchannel  channel,ischarge, "
				+ "to_char(endtime,'yyyymmddhh24miss') endtime,activetype,vipstatus from TD_BUSSINESS_HANDLE where userid = ? and cpid = ? and spid = ? and fhcpid is null and fhspid is null";
		return  SQLExecutor.queryListWithDBName(OrderBean.class, DataSource.NET3G, sql, userid,cpid,spid);
	}
	
	public void insertBeans(OrderBean bean) throws SQLException{
		String sql = "insert into TD_BUSSINESS_HANDLE(id,userid,cpid,spid,province,area,ordertime,activitystatus,orderchannel,"
				+ "orderstatus,inserttime,endtime,activitytime,ischarge,effectivetime,flowstatus,activetype,vipstatus) values(to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#[userid],#[cpid],#[spid],#[province],"
				+ "#[area],to_date(#[updateTime],'yyyymmddhh24miss'),0,#[channel],0,sysdate,#if($endtime && !$endtime.equals(\"\")) to_date(#[endtime], 'yyyymmddhh24miss') #else to_date('20501231235959','yyyymmddhh24miss') #end,"
				+ "sysdate,'2',to_date(#[updateTime],'yyyymmddhh24miss'),#[flowstatus],#[activeType],#[vipstatus])";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	}
	
	public void insertBeansBestv(OrderBean bean) throws SQLException{
		String sql = "insert into TD_BUSSINESS_HANDLE(id,userid,cpid,spid,province,area,ordertime,activitystatus,orderchannel,"
				+ "orderstatus,inserttime,endtime,activitytime,ischarge,effectivetime,flowstatus,activetype,vipstatus) values(to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval,#[userid],#[cpid],#[spid],#[province],"
				+ "#[area],to_date(#[updateTime],'yyyymmddhh24miss'),0,#[channel],0,sysdate,'',"
				+ "sysdate,'2',to_date(#[updateTime],'yyyymmddhh24miss'),#[flowstatus],4,#[vipstatus])";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	}
	
	public void UpdateRelations(OrderBean bean) throws SQLException{
		String sql =  "update TD_BUSSINESS_HANDLE set canceltime='', ordertime=to_date(#[updateTime],'yyyymmddhh24miss'),"
				+ "endtime=to_date('20501231235959','yyyymmddhh24miss'),orderstatus=0,effectivetime=to_date(#[updateTime],'yyyymmddhh24miss'),orderchannel=#[channel] where id=#[id]";
		SQLExecutor.updateBean(DataSource.NET3G, sql, bean);
	}
	
	public void UpdateRelationsBestv(OrderBean bean) throws SQLException{
		String sql =  "update TD_BUSSINESS_HANDLE set canceltime='', ordertime=to_date(#[updateTime],'yyyymmddhh24miss'),"
				+ "endtime='',orderstatus=0,effectivetime=to_date(#[updateTime],'yyyymmddhh24miss'),orderchannel=#[channel],vipstatus=#[vipstatus] where id=#[id]";
		SQLExecutor.updateBean(DataSource.NET3G, sql, bean);
	}
	
	public void cancelOrder(OrderBean bean) throws SQLException{
		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]";
		SQLExecutor.updateBean(DataSource.NET3G, sql,bean);
	}
	
	public void cancelOrderBestv(OrderBean bean) throws SQLException{
		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]";
		SQLExecutor.updateBean(DataSource.NET3G, sql,bean);
	}
	
	
	public void updateChannel(String id,String channel) throws SQLException{
		String sql = "update TD_BUSSINESS_HANDLE set orderchannel = ? where id = ?";
		SQLExecutor.updateWithDBName(DataSource.NET3G, sql, channel,id);
	}
	
	public void updateCharge(String id) throws SQLException{
		String sql = "update TD_BUSSINESS_HANDLE set ISCHARGE = 2 where id = ?";
		SQLExecutor.updateWithDBName(DataSource.NET3G, sql,id);
	}
	
	public static void main(String[] args) throws SQLException {
		OrderBean bean = new OrderBean();
		bean.setId("20160817142642440990");
		bean.setUserid("18574414678");
		bean.setProvince("湖南");
		bean.setArea("长沙");
		bean.setUpdateTime("20160727235959");
		bean.setActivated("2");
		bean.setChannel("khd");
		bean.setSpid("6");
		bean.setCpid("tencent");
		bean.setActivated("2");
		bean.setUsertype("2");
		bean.setUpdateTime("20160828235959");
		bean.setOrdertime("20160828235959");
		NewYearActiveDao dao = new NewYearActiveDao();
		System.out.println(dao.existUser("18673197465","leshilt","952").size());
	}
}