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 CommActiveDao {

	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 = ?";
		return  SQLExecutor.queryListWithDBName(OrderBean.class, DataSource.SDK, 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,to_date('20171231235959','yyyymmddhh24miss'),"
				+ "sysdate,'2',to_date(#[updateTime],'yyyymmddhh24miss'),#[flowstatus],1,#[vipstatus])";
		SQLExecutor.insertBean(DataSource.SDK, 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('20171231235959','yyyymmddhh24miss'),orderstatus=0,effectivetime=to_date(#[updateTime],'yyyymmddhh24miss'),orderchannel=#[channel] where id=#[id]";
		SQLExecutor.updateBean(DataSource.SDK, sql, bean);
	}
	
	public void cancelOrder(OrderBean bean) throws SQLException{
		String sql = "update TD_BUSSINESS_HANDLE set canceltime = to_date(#[updateTime],'yyyymmddhh24miss'),endtime = decode(sign(to_date(#[updateTime],'yyyymmddhh24miss')-endtime),1,endtime,last_day(trunc(to_date(#[updateTime],'yyyymmddhh24miss')))+1-1/(24*60*60)),cancelchannel= #[channel],"
				+ "orderstatus = 1  where id = #[id]";
		SQLExecutor.updateBean(DataSource.SDK, sql,bean);
	}
	
	
	public void updateChannel(String id,String channel) throws SQLException{
		String sql = "update TD_BUSSINESS_HANDLE set orderchannel = ? where id = ?";
		SQLExecutor.updateWithDBName(DataSource.SDK, sql, channel,id);
	}
	
	public void updateCharge(String id) throws SQLException{
		String sql = "update TD_BUSSINESS_HANDLE set ISCHARGE = 2 where id = ?";
		SQLExecutor.updateWithDBName(DataSource.SDK, 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");
		CommActiveDao dao = new CommActiveDao();
		System.out.println(dao.existUser("18673197465","leshilt","952").size());
	}
}