/**
* @param chargeId
* @param originChargeCurrency
* @param terminalId
* @return
*/
public FOBChargesDOB getChargeDetails(
String chargeId, String originChargeCurrency, String terminalId) {
int i = 0;
FOBChargesDOB custContrDtl = new FOBChargesDOB();
String UOW = null; // A String to store the UOW of a given contract
String contractCurrency = null; // A String to store the contractCurrency of a given contract
double[] lowerBound = null; // A int[] to store the lowerBound of a given contract
double[] upperBound = null; // A int[] to store the upperBound of a given contract
double[] rate = null; // A double[] to store the rate of a given contract
String[] slab = null; // A String[] to store the slab of a given contract
double convFactor = 0;
String rateCalcFlag = null;
Statement st = null;
ResultSet charge = null;
Connection connection = null;
try {
connection = getConnection();
st = connection.createStatement();
String sql =
"SELECT UNITCHARGE,CURRENCYID,RATE_CALC_FLAG FROM "
+ "FS_FR_STD_CHARGES where CHARGEID='"
+ chargeId
+ "' AND TRML_ID='"
+ terminalId
+ "'";
charge = st.executeQuery(sql);
while (charge.next()) {
UOW = charge.getString("UNITCHARGE");
contractCurrency = charge.getString("CURRENCYID");
rateCalcFlag = charge.getString("RATE_CALC_FLAG");
} // End of while
charge = null;
sql =
"SELECT CHARGESLAB "
+ "FROM "
+ "FS_FR_STD_CHARGES where CHARGEID='"
+ chargeId
+ "' AND TRML_ID='"
+ terminalId
+ "'";
charge = st.executeQuery(sql);
while (charge.next()) {
i++;
}
lowerBound = new double[i];
upperBound = new double[i];
rate = new double[i];
slab = new String[i];
charge = null;
i = 0;
sql =
"SELECT LOWERBOUND,UPPERBOUND,TO_CHAR(CHARGERATE,999999.99) RATE , "
+ "CHARGESLAB "
+ "FROM "
+ "FS_FR_STD_CHARGES "
+ "WHERE CHARGEID='"
+ chargeId
+ "' AND TRML_ID='"
+ terminalId
+ "'";
charge = st.executeQuery(sql);
while (charge.next()) {
lowerBound[i] = charge.getDouble("LOWERBOUND");
upperBound[i] = charge.getDouble("UPPERBOUND");
rate[i] = charge.getDouble("RATE");
slab[i] = charge.getString("CHARGESLAB");
i++;
} // End of while
// method to get local currency
custContrDtl.setUOW(UOW);
custContrDtl.setContractCurrency(contractCurrency);
custContrDtl.setInBound(lowerBound);
custContrDtl.setUpBound(upperBound);
custContrDtl.setRate(rate);
custContrDtl.setSlab(slab);
custContrDtl.setCurrencyId(originChargeCurrency);
custContrDtl.setRateCalculationFlag(rateCalcFlag);
if (originChargeCurrency.equalsIgnoreCase(contractCurrency)) {
custContrDtl.setConvFactor(1);
} else {
String sql_Currency =
"SELECT CONVERSIONFACTOR FROM FS_FR_CURRENCYMASTER "
+ "WHERE "
+ "CURRENCY1='"
+ contractCurrency
+ "' AND CURRENCY2 = '"
+ originChargeCurrency
+ "' ";
charge = st.executeQuery(sql_Currency);
while (charge.next()) {
custContrDtl.setConvFactor(charge.getDouble("CONVERSIONFACTOR"));
}
}
} catch (SQLException se) {
Logger.error(FILE_NAME, "getChargeDetails()", se);
} finally {
try {
if (charge != null) {
charge.close();
}
if (st != null) {
st.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception ex) {
Logger.error(FILE_NAME, "Exception in finally Block" + ex);
}
}
return custContrDtl;
}
/**
* @param fobChargesDOB
* @return
*/
public boolean insertFOBCharges(ArrayList fobChargesDOB) {
Connection connection = null;
PreparedStatement pStmt = null;
FOBChargesDOB localChargesDOB = null;
String[] insQuery = new String[1];
boolean successfull = true;
try {
connection = getConnection();
insQuery[0] =
"INSERT INTO FS_FR_STD_CHARGES(TRML_ID , CHARGEID,SHPMNT_MODE,LINENO,CHARGERATE, INDICATOR, CURRENCYID , CHARGESLAB, LOWERBOUND, UPPERBOUND, UNITCHARGE, RATE_CALC_FLAG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
pStmt = connection.prepareStatement(insQuery[0]);
Iterator iter = fobChargesDOB.iterator();
while (iter.hasNext()) {
localChargesDOB = (FOBChargesDOB) iter.next();
pStmt.setString(1, localChargesDOB.getTerminalId());
pStmt.setString(2, localChargesDOB.getChargeId());
pStmt.setInt(3, localChargesDOB.getShipmentMode());
pStmt.setInt(4, localChargesDOB.getLineNo());
pStmt.setDouble(5, localChargesDOB.getChargeRate());
if (localChargesDOB.getIndicator() != null) {
pStmt.setString(6, localChargesDOB.getIndicator());
} else {
pStmt.setNull(6, java.sql.Types.VARCHAR);
}
if (localChargesDOB.getChargeCurrency() != null) {
pStmt.setString(7, localChargesDOB.getChargeCurrency());
} else {
pStmt.setNull(7, java.sql.Types.VARCHAR);
}
if (localChargesDOB.getChargeSlab() != null) {
pStmt.setString(8, localChargesDOB.getChargeSlab());
} else {
pStmt.setNull(8, java.sql.Types.VARCHAR);
}
double lowerBound = localChargesDOB.getLowerBound();
double upperBound = localChargesDOB.getUpperBound();
if ((lowerBound != 0.0 && upperBound == 0.0) || (lowerBound != 0 && upperBound == 0)) {
upperBound = 999999;
}
pStmt.setDouble(9, lowerBound);
pStmt.setDouble(10, upperBound);
if (localChargesDOB.getUnitCharge() != null) {
pStmt.setString(11, localChargesDOB.getUnitCharge());
} else {
pStmt.setNull(11, java.sql.Types.VARCHAR);
}
if (localChargesDOB.getRateCalculationFlag() != null) {
pStmt.setString(12, localChargesDOB.getRateCalculationFlag());
} else {
pStmt.setNull(12, java.sql.Types.VARCHAR);
}
pStmt.executeUpdate();
} // end of for loop
} catch (Exception sqEx) {
sqEx.printStackTrace();
Logger.error(FILE_NAME, " [insertFOBCharges(FOBChargeDOB)] -> " + sqEx.toString());
throw new EJBException();
} finally {
try {
if (pStmt != null) {
pStmt.close();
}
if (connection != null) {
connection.close();
}
} catch (Exception ex) {
Logger.error(FILE_NAME, "Exception in finally Block" + ex);
}
}
return successfull;
}