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