package com.kingdee.eas.custom.gggl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.util.BOSUuid;
import com.kingdee.eas.xr.XRSQLFacadeFactory;
import com.kingdee.jdbc.rowset.IRowSet;

public class Hyhelper {
	
	   /**
	    * 
	    * id：单据ID
	    * contractID 合同ID
	    * ggwID:广告位ID
	    * Date: yyyy-mm-dd
	 * @throws BOSException 
	    *  
	    *  
	    * */
	  public static boolean  isGgwCheck(BOSUuid id ,BOSUuid contractID,String ggwID,String Date) throws BOSException{
		  boolean flag=false;
		  StringBuffer sql=new StringBuffer();
		  sql.append(" select  * from ( ");
		  sql.append(" select con.fid fid,con.fid contractID,to_char(e1.CFZLSTARTDATE, 'yyyy-mm-dd')  startDate ,to_char(e1.CFZLENDDATE, 'yyyy-mm-dd') endDate,e1.CFGGWID ggwID");
		  sql.append(" from  CT_GGL_SaleContractE1  e1");
		  sql.append(" left join  CT_GGL_SaleContract con  on e1.fparentid=con.fid");
		  sql.append(" where con.fid !='"+id+"' and con.fid !='"+contractID+"'  and e1.CFGGWID='"+ggwID+"' ");
		  sql.append(" union all ");
		  sql.append(" select con.fid fid,con.CFContractNumID contractID,to_char(e1.CFZLSTARTDATE, 'yyyy-mm-dd')  startDate ,to_char(e1.CFZLENDDATE, 'yyyy-mm-dd') endDate,e1.CFGGWID ggwID ");
		  sql.append(" from  CT_GGL_ContractChangeE1  e1 ");
		  sql.append(" left join  CT_GGL_ContractChange con  on e1.fparentid=con.fid ");
		  sql.append(" where con.fid !='"+id+"' and con.CFContractNumID !='"+contractID+"'  and e1.CFGGWID='"+ggwID+"' ");
		  sql.append(" union all ");
		  sql.append(" select con.fid fid,con.CFContractID contractID,to_char(e1.CFZLSTARTDATE, 'yyyy-mm-dd')  startDate ,to_char(e1.CFZLENDDATE, 'yyyy-mm-dd') endDate,e1.CFGGWID ggwID ");
		  sql.append(" from  CT_GGL_SchedulBillE1  e1 ");
		  sql.append(" left join  CT_GGL_SchedulBill con  on e1.fparentid=con.fid ");
		  sql.append(" where con.fid !='"+id+"' and con.CFContractID !='"+contractID+"'  and e1.CFGGWID='"+ggwID+"' ");
		  sql.append(" ) t   where t.endDate>='"+Date+"'    " );//and t.fid='"+ggwID+"'
		   IRowSet rowset=XRSQLFacadeFactory.getRemoteInstance().executeQuery(sql.toString(), null);
		   if(rowset.size()>0){
			   flag=true;
		   }
		 return flag;
		  
	  }
	
	   /**
	    * 
	    * contractID 合同ID
	    * Date: yyyy-mm-dd
	 * @throws BOSException 
	 * @throws SQLException 
	    *  
	    *  
	    * */
	  public static BigDecimal  getRecYjAmount(BOSUuid contractID) throws BOSException, SQLException{
		  BigDecimal amount=BigDecimal.ZERO;
		  StringBuffer sql=new StringBuffer();
		  sql.append(" select isnull(sum(FACTRECAMT),0) amount from T_CAS_ReceivingBill where CFSALECONTRACTID ='"+contractID+"'  and FRECBILLTYPEID ='JQcAAAACE0AqqNs1'");
		   IRowSet rowset=XRSQLFacadeFactory.getRemoteInstance().executeQuery(sql.toString(), null);
		   if(rowset.size()>0){
			   while(rowset.next()){
				   amount=  rowset.getBigDecimal("amount");
			   }
		   }
		 return amount;
		  
	  }
	
	   /**
	    * 
	    * id：单据ID
	    * contractID 合同ID
	    * ggwID:广告位ID
	    * Date: yyyy-mm-dd
	 * @throws BOSException 
	 * @throws SQLException 
	    *  
	    * */
	  public static Date  getEndDate  (String contractID,String ggwID) throws BOSException, SQLException{
		  Date endDate=null;
		  StringBuffer sql=new StringBuffer();
		  sql.append(" select  e1.CFZLENDDATE  endDate,e1.CFGGWID ggwID,ggw.fname_l2 ggwName ");
		  sql.append(" from  CT_GGL_SaleContractE1  e1");
		  sql.append(" left join  CT_GGL_SaleContract con  on e1.fparentid=con.fid");
		  sql.append(" LEFT OUTER JOIN CT_GGL_Advert ggw ON E1.CFGgwID = ggw.FID");
		  sql.append(" where con.fid ='"+contractID+"'  and e1.CFGGWID='"+ggwID+"' ");
		   IRowSet rowset=XRSQLFacadeFactory.getRemoteInstance().executeQuery(sql.toString(), null);
		   if(rowset.size()>0){
			   while(rowset.next()){
				   endDate=  rowset.getDate("endDate");
			   }
		   }
		return endDate;
	  }
	
	
	
	  
	
	
	
	  
	

}
