videoalliance/.svn/pristine/00/007040c5b0c490f636bb1e15691ba2d0fe2c8fcd.svn-base

package com.chinacreator.videoalliance.order.dao;

import java.sql.SQLException;
import org.apache.commons.lang.math.NumberUtils;
import org.springframework.stereotype.Component;
import com.chinacreator.videoalliance.order.bean.DiscntInfo;
import com.chinacreator.videoalliance.order.bean.EcbProductInfo;
import com.chinacreator.videoalliance.order.bean.OrderInfo;
import com.frameworkset.common.poolman.SQLExecutor;

@Component
public class EcbDiscntDao {

	public void order(EcbProductInfo ecbProductInfo,OrderInfo orderInfo) throws SQLException{
		DiscntInfo discntInfo = get(ecbProductInfo,orderInfo);
		String sql = "merge into TD_DISCNT_INFO a using (select #[userid] userid, #[discntid] discntid,#[discnttype] discnttype  from dual) b "+
			"on (a.userid = b.userid and a.discntid = b.discntid and a.discnttype = b.discnttype)"+
			"when matched then update set "+
			"ordertime=to_date(#[ordertime], 'yyyymmddhh24miss'), "+
			"endtime=to_date(#[endtime], 'yyyymmddhh24miss'), CANCELTIME='',"+
			" inserttime=sysdate,status=0,syncstatus=1,orderchannel=#[orderchannel] "+
			"when not matched then insert (userid,ordertime,endtime,status,orderchannel,discnttype,syncstatus,inserttime,discntid,discntfee,discntName,productId,productName,packageCode,packageName,discntOrderState) values ("+
			"#[userid],to_date(#[ordertime],'yyyyMMddhh24miss'),to_date(#[endtime],'yyyyMMddhh24miss'),0,#[orderchannel],#[discnttype],1,sysdate,#[discntid],#[discntfee],#[discntName],#[productId],#[productName],#[packageCode],#[packageName],#[discntOrderState]) ";
		SQLExecutor.insertBean("net3g", sql, discntInfo);
	}
	
	public static void main(String[] args) throws SQLException {
		EcbDiscntDao dao = new EcbDiscntDao();
		EcbProductInfo ecbProductInfo = new EcbProductInfo();
		ecbProductInfo.setDiscntCode("8244270");
		ecbProductInfo.setDiscntEndDate("20170930235959");
		ecbProductInfo.setDiscntValue("0");
		ecbProductInfo.setDiscntStartDate("20170923235959");
		OrderInfo orderInfo = new OrderInfo();
		orderInfo.setUserid("18600088691");
		//dao.order(ecbProductInfo, orderInfo);
		//dao.queryByDiscntid("18600088691", "8244270", "0",0);
		dao.cancel(ecbProductInfo, orderInfo);
	}
	
	public  DiscntInfo get(EcbProductInfo ecbProductInfo,OrderInfo orderInfo){
		DiscntInfo discntInfo = new DiscntInfo();
		discntInfo.setUserid(orderInfo.getUserid());
		discntInfo.setDiscntid(ecbProductInfo.getDiscntCode());
		discntInfo.setCancelchannel(orderInfo.getCancelchannel());
		discntInfo.setDiscnttype(ecbProductInfo.getDiscntValue());
		discntInfo.setOrderchannel(orderInfo.getOrderchannel());
		discntInfo.setOrdertime(ecbProductInfo.getDiscntStartDate());
		discntInfo.setEndtime(ecbProductInfo.getDiscntEndDate());
		discntInfo.setDiscntfee(ecbProductInfo.getDiscntFee());
		discntInfo.setDiscntName(ecbProductInfo.getDiscntName());
		discntInfo.setPackageCode(ecbProductInfo.getPackageCode());
		discntInfo.setPackageName(ecbProductInfo.getPackageName());
		discntInfo.setProductId(ecbProductInfo.getProductId());
		discntInfo.setProductName(ecbProductInfo.getProductName());
		discntInfo.setDiscntOrderState(ecbProductInfo.getDiscntOrderState());
		return discntInfo;
	}
	
	public void cancel(EcbProductInfo ecbProductInfo,OrderInfo orderInfo) throws SQLException{
		DiscntInfo discntInfo = get(ecbProductInfo,orderInfo);
		String sql = "merge into TD_DISCNT_INFO a using (select #[userid] userid, #[discntid] discntid,#[discnttype] discnttype  from dual) b "+
				"on (a.userid = b.userid and a.discntid = b.discntid and a.discnttype = b.discnttype)"+
				"when matched then update set "+
				"ordertime=to_date(#[ordertime], 'yyyymmddhh24miss'), "+
				"endtime=to_date(#[endtime], 'yyyymmddhh24miss'),CANCELTIME=sysdate, "+
				"status=1,syncstatus=1,cancelchannel=#[cancelchannel] "+
				"when not matched then insert (userid,canceltime,ordertime,endtime,status,cancelchannel,discnttype,syncstatus,inserttime,discntid,discntfee,discntName,productId,productName,packageCode,packageName,discntOrderState) values ("+
				"#[userid],sysdate,to_date(#[ordertime],'yyyyMMddhh24miss'),to_date(#[endtime],'yyyyMMddhh24miss'),1,#[cancelchannel],#[discnttype],1,sysdate,#[discntid],#[discntfee],#[discntName],#[productId],#[productName],#[packageCode],#[packageName],#[discntOrderState]) ";
		SQLExecutor.updateBean("net3g", sql, discntInfo);
	}
	
	public boolean queryByDiscntid(String userid,String discntid,String discnttype,int status) throws SQLException{
		String sql ="select count(1) from TD_DISCNT_INFO where userid =? and discntid=? and discnttype= ? and status=?";
		return NumberUtils.toInt(SQLExecutor.queryFieldWithDBName("net3g", sql, userid,discntid,discnttype,status)) >0;
	}
	
	public DiscntInfo queryByUserid(String userid,String discnttype,int status) throws SQLException{
		String sql ="select to_char(ORDERTIME,'yyyymmddhh24miss') ORDERTIME,to_char(ENDTIME,'yyyymmddhh24miss') ENDTIME,DISCNTID,DISCNTTYPE,discntfee from TD_DISCNT_INFO where orderchannel='H5_ko' and userid =?  and discnttype= ? and status=?";
		return SQLExecutor.queryObjectWithDBName(DiscntInfo.class, "net3g", sql, userid,discnttype,status);
	}
	
	public DiscntInfo queryByUserid(String userid,String discnttype) throws SQLException{
		String sql ="select to_char(ORDERTIME,'yyyymmddhh24miss') ORDERTIME,to_char(ENDTIME,'yyyymmddhh24miss') ENDTIME,DISCNTID,DISCNTTYPE,discntfee,discntOrderState,PACKAGECODE,PRODUCTID,DISCNTTYPE from TD_DISCNT_INFO where  userid =?  and discnttype= ? and (endtime is null or endtime > sysdate)";
		return SQLExecutor.queryObjectWithDBName(DiscntInfo.class, "net3g", sql, userid,discnttype);
	}
	
	public EcbProductInfo queryProduct(String userid,String discnttype) throws SQLException{
		String sql ="select to_char(ORDERTIME,'yyyymmddhh24miss') discntStartDate,to_char(ENDTIME,'yyyymmddhh24miss') discntEndDate,DISCNTID discntCode,DISCNTTYPE discntValue,decode(discnttype,'','1040','0','1040','1150') spid,"
				+ "discntfee discntFee ,discntOrderState,DISCNTNAME,PRODUCTID,PRODUCTNAME,PACKAGECODE, PACKAGENAME from TD_DISCNT_INFO where userid =?  and discnttype= ? and status=0";
		return SQLExecutor.queryObjectWithDBName(EcbProductInfo.class, "net3g", sql, userid,discnttype);
	}
}