package com.chinacreator.process.dao;

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

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.frameworkset.common.poolman.PreparedDBUtil;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
public class CctvActiveDao {

	public OrderBean existUser(String userid,String cpid,String spid) 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 from TD_BUSSINESS_HANDLE  where userid =? and cpid =? and spid=? and ACTIVETYPE=8  and fhcpid is null and fhspid is null";
		return  SQLExecutor.queryObjectWithDBName(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,effectivetime,activitystatus,activitytime,orderchannel,orderstatus,inserttime,ischarge,flowstatus,activetype,vipstatus,canceltime,endtime)values( "
				+ "to_char(sysdate,'yyyyMMddhh24miss')||SEQ_COMMON6.nextval, "
				+ "#[userid],#[cpid],#[spid],#[province],#[area], "
				+ "to_date(#[ordertime],'yyyymmddhh24miss'), "
				+ "sysdate,  "
				+ "0,sysdate, "
				+ "#[orderchannel],#[orderstatus],sysdate,2,8,8,2,to_date(#[canceltime],'yyyymmddhh24miss'),to_date(#[endtime],'yyyymmddhh24miss'))";
		SQLExecutor.insertBean(DataSource.NET3G, sql, bean);
	}
	
	  public List<NetOrderBean> findOrder(String cpid, String userid) 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 from TD_ORDER_RELATIONS where cpid=? and userid=? order by ordertime desc";
	    pdb.preparedSelect(DataSource.NET3G, sql);
	    pdb.setString(1, cpid);
	    pdb.setString(2, userid);
	    return  pdb.executePreparedForList(NetOrderBean.class);
	  }
	  
	  public void update(OrderBean bean) throws SQLException {
			String sql = "update TD_BUSSINESS_HANDLE set ordertime = to_date(#[ordertime],'yyyymmddhh24miss'),canceltime =to_date(#[canceltime],'yyyymmddhh24miss'),endtime=to_date(#[endtime],'yyyymmddhh24miss'),orderchannel= #[orderchannel],cancelchannel=#[cancelchannel]"
					+ ",orderstatus = #[orderstatus] ,vipstatus=#[vipstatus],viptime=to_date(#[viptime],'yyyymmddhh24miss')  where id = #[id]";
			SQLExecutor.updateBean(DataSource.NET3G, sql, bean);
		}
}