/**
	 * Checks if a billing term scheme is used by a billing term.
	 * 
	 * @param billingTermScheme - the billing term scheme to check
	 * @return true if a billing term scheme is used by a billing term, false if not
	 * @throws Exception
	 */
	public Boolean isUsedByBillingTerms(CRMDOBillingTermScheme billingTermScheme) throws Exception {
		
		ArrayList<Object> parameters = new ArrayList<Object>();
		
		String sql = 
				"\n SELECT 'x' FROM BILLINGTERMS " +
				"\n WHERE BTDELETED = 0 " +
				"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "BTEFFECTIVEDATE") + "<=" + DateUtil.getCurrentSQLDate(getCRMSession().getDbtype(),getCRMSession().getTimezone()) +
				"\n AND ((" + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "BTEXPIRATIONDATE") + ">" + DateUtil.getCurrentSQLDate(getCRMSession().getDbtype(),getCRMSession().getTimezone()) +
				"\n		OR BTEXPIRATIONDATE IS NULL)) " +  
				"\n AND BTSCHEMECODE = ?";
	
		parameters.add(billingTermScheme.getCode());
		
		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, getCRMSession().getDbtype()))
		{
			return new Boolean(true);
		}
		else
		{
			return new Boolean(false);
		}
	}

public ArrayList<CRMDO> loadEffective(Date asOfDate){
	ArrayList<Object> values = new ArrayList<Object>();
	String dbType = getCRMSession().getDbtype();
	if(expirationDate!=null)
	{
		values.add(asOfDate);
		criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".effectiveDate")+"<=:p"+values.size();
		criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".expirationDate")+">:p"+values.size();
	}
	
	return load(getDOName().toLowerCase() + ".isDeleted=0" +criteria,
				values,
				getDefaultAssociations(),
				null,
				null);	
	
}

public Boolean isEffective(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
		String dbtype = getCRMSession().getDbtype();

		String sql = 
						"\n 	SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
						"\n 	WHERE RT.RENTTYPEID = ?" +
						"\n     AND PV.PRODVALIDITYDELETED = 0 " +
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE") + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) + 
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " > " + DateUtil.getSQLDate(dbtype, asOfDate) ;
					
 
		ArrayList<Object> parameters = new ArrayList<Object>();
		parameters.add(rentalType.getId());

		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
		{
			return new Boolean(true);
		}
 
	return new Boolean(false);
}

public void deleteNonEffective(Date asOfDate, CUSTOMCRMDORentalType type){
	
	ArrayList<Object> parameters = new ArrayList<Object>();
 
	String sql = 
						"\n 	UPDATE TRN_RENTALTS " +
						"\n 	SET RENTALDELETED = 1" +
						"\n 	WHERE RENTALTYPEID = ?" +
						"\n 	AND "+ DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " < " + DateUtil.getSQLDate(dbtype, asOfDate);
	
	parameters.add(type.getId());
 
	ResultSetUtil rsu =  SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype(), numberOfRentals);
	
	SQLUtil.executeUpdateUsingPreparedStatement(
				sql, 
				getOrganisationID(), 
				parameters,
				getCRMSession().getDbtype());
}