/** * @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; }