/**
  * @ejb.interface-method
  * @ejb.transaction type="Supports"
  */
 public Collection findAll() throws LocalException {
   try {
     SpecialRateLocalHome home = SpecialRateUtil.getLocalHome();
     Collection col = home.findAll();
     List list = new CompressedSerializedList();
     for (Iterator iter = col.iterator(); iter.hasNext(); ) {
       SpecialRateLocal element = (SpecialRateLocal) iter.next();
       list.add(fill(element.getSpecialRateValue()));
     }
     return list;
   } catch (Exception e) {
     log.warn(StackTraceUtil.getStackTrace(e));
     throw new LocalException("An Error occured while findAll()!", e);
   }
 }
 // vvvvvvvvvvvvvvvvvvvvvvvvvv Private Business vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
 private boolean isDuplicate(
     String tablename,
     Boolean newIsImport,
     Long newSeaterminalId,
     Long newVendorId,
     java.util.Date durationFrom,
     java.util.Date durationTo) {
   boolean returnVal = true;
   Connection con = null;
   PreparedStatement stmt = null;
   ResultSet result = null;
   try {
     String sql =
         new String(
             "select id from "
                 + tablename
                 + " where isimport = "
                 + (newIsImport.booleanValue() ? "1" : "0")
                 + " and facilityid = "
                 + newSeaterminalId.longValue()
                 + " and vendorid = "
                 + newVendorId.longValue()
                 + " and durationfrom = ? and durationto = ?");
     con = getConnection();
     // Getting Identifier
     stmt = con.prepareStatement(sql);
     stmt.setDate(1, new java.sql.Date(durationFrom.getTime()));
     stmt.setDate(2, new java.sql.Date(durationTo.getTime()));
     result = stmt.executeQuery();
     returnVal = result.next();
   } catch (Exception ex) {
     log.warn(StackTraceUtil.getStackTrace(ex));
   } finally {
     closeQuietly(result, stmt, con);
   }
   return returnVal;
 }
  /**
   * @ejb.interface-method
   * @ejb.transaction type="Supports"
   */
  public List findSpecialRateLinesByInfo(
      Boolean isImport,
      Boolean isExport,
      Date validFrom,
      Date validTo,
      Long[] vendorIds,
      Long[] seaTerminalIds,
      String countryName,
      String zipCodeFrom,
      String zipCodeTo)
      throws LocalException {

    //
    // check parameters
    //
    if (isImport == null && isExport == null) {
      throw new LocalException("isImport AND isExport must not be null");
    }
    if ((validFrom == null && validTo != null) || (validFrom != null && validTo == null)) {
      throw new LocalException(
          "durationFrom AND durationTo must both be null, or both be filled! durationFrom: "
              + validFrom
              + " | durationTo: "
              + validTo);
    }

    // if( !(countryName == null && zipCodeFrom == null && zipCodeTo == null)
    // && !(countryName != null && zipCodeFrom != null && zipCodeTo != null) )
    // throw new LocalException("countryName AND zipCodeFrom AND zipCodeTo must all be null, or all
    // be filled!
    // countryId: "+countryName+" | zipCodeFrom: "+zipCodeFrom+" | zipCodeTo: "+zipCodeTo);

    // Reduce ZIP codes to make search result Rate Retrieval queries
    if (countryName != null) {
      String countryCode = WaypointValue.getPrefix(countryName);
      String p = getCache().getParameterAsStringOrDefault("", "");
      SuffixZipCodeReduction zcr = new SuffixZipCodeReduction(p);
      if (zipCodeFrom != null) {
        zipCodeFrom = zcr.reduce(zipCodeFrom, countryCode, "Customer");
      }
      if (zipCodeTo != null) {
        zipCodeTo = zcr.reduce(zipCodeTo, countryCode, "Customer");
      }
    }

    //
    // build query
    //
    SQLQuery query =
        new SQLQuery(
            "line.id as id,firstarea.countryid, firstarea.fromzipcode, firstarea.tozipcode, tariff.vendorid as vendorid, vendor.name as vendorname, tariff.isimport as isimport, tariff.facilityid as seaterminal",
            "specialrate tariff, specialrateline line, rateservicearea area, country, vendor, firstarea",
            true);

    query.addWhereClause("and", "tariff.id = line.specialRateId(+)");
    query.addWhereClause("and", "line.id = area.specialRateLineId(+)");
    query.addWhereClause("and", "area.countryid = country.id");
    query.addWhereClause("and", "tariff.vendorid = vendor.id");
    query.addWhereClause("and", "line.id = firstarea.specialratelineid(+)");

    // if import and export are specified, we don't care, because the field is marked as not null
    // so if we don't specify it in the where clause, true and false will be found.
    if (isImport != null && (isExport == null || !isExport.booleanValue())) {
      query.addWhereClause("and", "tariff.isImport", "=", isImport);
    }
    if ((isImport == null || !isImport.booleanValue()) && isExport != null) {
      query.addWhereClause(
          "and", "tariff.isImport", "=", isExport.booleanValue() ? Boolean.FALSE : Boolean.TRUE);
    }

    query.addWhereClause("and", "tariff.durationFrom", "<=", validTo);
    query.addWhereClause("and", "tariff.durationTo", ">=", validFrom);

    if (countryName != null) {
      query.addWhereClause("and", "country.name", "=", countryName);
    }

    if (zipCodeFrom != null && zipCodeTo != null && !zipCodeFrom.equals(zipCodeTo)) {
      query.addWhereClause("and", "area.fromzipcode", "<=", zipCodeTo);
      query.addWhereClause("and", "concat(area.tozipcode,'zzzzzz')", ">=", zipCodeFrom);
    }

    // bugfix 8982
    if ((zipCodeFrom != null && zipCodeTo != null && zipCodeFrom.equals(zipCodeTo))
        || (zipCodeFrom != null && zipCodeTo == null)) {
      query.addWhereClause("and", "area.fromzipcode", "=", zipCodeFrom);
    }

    //
    // build nested vendor query
    //
    if (vendorIds != null && vendorIds.length > 0) {
      SQLQuery nestedVendorQuery = new SQLQuery(true);
      nestedVendorQuery.addWhereClause("or", "tariff.vendorid", "=", vendorIds);
      query.addWhereClause("and", nestedVendorQuery);
    }

    //
    // build nested seaterminal query
    //
    if (seaTerminalIds != null && seaTerminalIds.length > 0) {
      SQLQuery nestedSeaterminalQuery = new SQLQuery(true);
      nestedSeaterminalQuery.addWhereClause("or", "tariff.facilityid", "=", seaTerminalIds);
      query.addWhereClause("and", nestedSeaterminalQuery);
    }

    if (log.isInfoEnabled()) log.info(query.getSQLDebugString());

    //
    // execute query
    //
    ResultSet result = null;
    PreparedStatement stmt = null;
    Connection con = null;
    List truckTariffLines = new CompressedSerializedList();

    try {
      con = getConnection();

      stmt = query.getPreparedStatement(con);
      result = stmt.executeQuery();
      SpecialRateLineManagerLocal srlm =
          (SpecialRateLineManagerLocal) SpecialRateLineManagerUtil.getLocalHome().create();

      while (result.next()) {
        long tariffLineId = result.getLong("id");

        SpecialRateLineValue ttlv =
            (SpecialRateLineValue)
                srlm.findByPrimaryKey(new SpecialRateLinePK(new Long(tariffLineId)));

        // fill vendor name
        ttlv.vendorName = result.getString("vendorname");
        ttlv.seaterminalID = result.getLong("seaterminal");
        ttlv.importTariff = result.getBoolean("isimport") ? Boolean.TRUE : Boolean.FALSE;
        ttlv.firstCountryId = new Long(result.getLong("countryid"));
        ttlv.firstzipFrom = result.getString("fromzipcode");
        ttlv.firstzipTo = result.getString("tozipcode");
        truckTariffLines.add(ttlv);
      }
    } catch (Exception ex) {
      log.warn(StackTraceUtil.getStackTrace(ex));
      throw new LocalException(ex);
    } finally {
      closeQuietly(result, stmt, con);
    }

    return truckTariffLines;
  }