public String isValidULDId(String uldId, String terminalId) throws RemoteException {
Logger.info(FILE_NAME, " In isValidULDId() uldId " + uldId);
String result = "";
try {
ETGSDDAO gsdDAO = new ETGSDDAO();
result = gsdDAO.isValidULDId(uldId, terminalId);
Logger.info(FILE_NAME, " In isValidULDId() result " + result);
} catch (Exception e) {
Logger.error(FILE_NAME, " [isValidULDId()] -> " + e.toString());
throw new EJBException("Error while getting ULDId data");
}
return result;
}
public String updateTWHStatus(
String docId, String subOperation, String status, ESupplyGlobalParameters loginBean) {
Logger.info(FILE_NAME, " In updateTWHStatus() docId " + docId);
String result = "";
try {
ETGSDDAO gsdDAO = new ETGSDDAO();
result = gsdDAO.updateTWHStatus(docId, subOperation, status, loginBean);
Logger.info(FILE_NAME, " In getDetpatchData() ");
} catch (Exception e) {
Logger.error(FILE_NAME, " [updateTWHStatus()] -> " + e.toString());
throw new EJBException("Error while updating status");
}
return result;
}
/*
* ULD Build Methods Ends Here
*/
public String getDespatchData(String referenceId, String refType) throws RemoteException {
Logger.info(
FILE_NAME, " In getDetpatchData() ReferenceId " + referenceId + " RefType " + refType);
String result = "";
try {
ETGSDDAO gsdDAO = new ETGSDDAO();
result = gsdDAO.getDespatchData(referenceId, refType);
Logger.info(FILE_NAME, " In getDetpatchData() ");
} catch (Exception e) {
Logger.error(FILE_NAME, " [getDetpatchData()] -> " + e.toString());
throw new EJBException("Error while getting ULDId data");
}
return result;
}
// Added by SureshK for CR_1918
public ArrayList getMAWBDtl(String masterDocId, String terminalId) throws RemoteException {
Logger.info(FILE_NAME, " [getMasterDataForULDBuild()] [masterDocId] " + masterDocId);
ArrayList result = null;
try {
ETGSDDAO gsdDAO = new ETGSDDAO();
result = gsdDAO.getMAWBDetails(masterDocId, terminalId);
} catch (Exception e) {
Logger.error(FILE_NAME, " [getMasterDataForULDBuild()] -> " + e.toString());
throw new EJBException("Error getting getMasterDataForULDBuild ");
}
return result;
}
// Added by SureshK for CR_1918
public String getULDAllocationDtl(String houseDocId, String terminalId) throws RemoteException {
Logger.info(FILE_NAME, " [getMasterDataForULDBuild()] [houseDocId] " + houseDocId);
String result = null;
try {
ETGSDDAO gsdDAO = new ETGSDDAO();
result = gsdDAO.getULDAllocationDetails(houseDocId, terminalId);
} catch (Exception e) {
Logger.error(FILE_NAME, " [getMasterDataForULDBuild()] -> " + e.toString());
throw new EJBException("Error getting getMasterDataForULDBuild ");
}
return result;
}
/**
* @param con
* @param qry
* @return boolean
* @throws java.sql.SQLException
*/
private static boolean isIdExists(Connection con, String qry) throws SQLException {
boolean result = false;
Statement ps = null;
ResultSet rs = null;
try {
ps = con.createStatement();
rs = ps.executeQuery(qry);
if (rs.next()) {
result = true;
}
} catch (Exception ex) {
Logger.error(FILE_NAME, " isIdExists() " + ex);
Logger.info(FILE_NAME, "Query :" + qry);
throw new SQLException("Error while checking in Id " + ex);
} finally {
// @@ Modified by K Srinivasarao to resolve the Connection leaks
closeStatement(rs, ps);
rs = null;
ps = null;
}
return result;
}
/**
* @param shipmentMode
* @param searchStr
* @param operation
* @param teminalId
* @param chargeType
* @return
*/
public ArrayList getChargeIds(
String shipmentMode,
String searchStr,
String operation,
String teminalId,
String chargeType) {
ArrayList chargeIds = new ArrayList();
Connection connection = null;
PreparedStatement pStmt = null;
ResultSet rs = null;
String sqlQuery = "";
if (shipmentMode.equals("Air")) {
shipmentMode = "(1,3,5,7)";
} else if (shipmentMode.equals("Sea")) {
shipmentMode = "(2,3,6,7)";
} else if (shipmentMode.equals("Truck")) {
shipmentMode = "(4,5,6,7)";
}
try {
connection = getConnection();
if ("Add".equals(operation)) {
String sqlQuery1 = "SELECT COUNT(*) from FS_FR_STD_CHARGES ";
pStmt = connection.prepareStatement(sqlQuery1);
rs = pStmt.executeQuery();
int total = 0;
while (rs.next()) {
total = rs.getInt(1);
}
ConnectionUtil.closePreparedStatement(
pStmt, rs); // Added by Subhani for open cursor issue on 27/02/08.
if (total > 0) {
sqlQuery =
" SELECT DISTINCT CM.CHARGEID,CM.CHARGEDESC FROM FS_FR_STD_CHARGES F , FS_FR_CHARGESMASTER CM"
+ " WHERE CM.CHARGEIDTYPE='I' AND CM.CHARGEID NOT IN (SELECT CHARGEID from FS_FR_STD_CHARGES WHERE TRML_ID = '"
+ teminalId
+ "')"
+ " AND CM.CHARGEID NOT IN ('1','2','3','4','5') AND CM.SHIPMENTMODE IN "
+ shipmentMode
+ " AND CM.CHARGEID LIKE '"
+ searchStr
+ "%' ORDER BY CM.CHARGEID";
} else {
sqlQuery =
" SELECT DISTINCT CM.CHARGEID,CM.CHARGEDESC FROM FS_FR_CHARGESMASTER CM"
+ " WHERE CM.CHARGEIDTYPE='I' AND CM.CHARGEID NOT IN ('1','2','3','4','5') AND CM.SHIPMENTMODE IN "
+ shipmentMode
+ " AND CM.CHARGEID LIKE '"
+ searchStr
+ "%' ORDER BY CM.CHARGEID";
}
pStmt = null; // @@ Added by K Srinivasarao to resolve the Connection leaks
pStmt = connection.prepareStatement(sqlQuery);
} else {
if (!chargeType.equalsIgnoreCase("L")) {
sqlQuery =
" SELECT DISTINCT F.CHARGEID,CHARGEDESC FROM FS_FR_STD_CHARGES F,FS_FR_CHARGESMASTER CM "
+ " WHERE F.SHPMNT_MODE IN "
+ shipmentMode
+ " AND F.TRML_ID ='"
+ teminalId
+ "' AND F.CHARGEID=CM.CHARGEID AND CM.COSTINCURREDAT='"
+ chargeType
+ "' AND F.CHARGEID LIKE '"
+ searchStr
+ "%' ORDER BY F.CHARGEID";
} else {
sqlQuery =
" SELECT DISTINCT F.CHARGEID,CHARGEDESC FROM FS_FR_STD_CHARGES F,FS_FR_CHARGESMASTER CM "
+ " WHERE F.SHPMNT_MODE IN "
+ shipmentMode
+ " AND F.TRML_ID ='"
+ teminalId
+ "' AND F.CHARGEID=CM.CHARGEID AND (CM.COSTINCURREDAT='Origin' OR CM.COSTINCURREDAT='Destination') AND F.CHARGEID LIKE '"
+ searchStr
+ "%' ORDER BY F.CHARGEID";
}
pStmt = connection.prepareStatement(sqlQuery);
}
Logger.info(FILE_NAME, "getChargeIds() :sqlQuery=" + sqlQuery);
rs = pStmt.executeQuery();
while (rs.next()) {
chargeIds.add(rs.getString(1) + "," + rs.getString(2));
}
} catch (SQLException se) {
Logger.error(FILE_NAME, "getChargeIds()", se);
} finally {
try {
// @@ Modified by K Srinivasarao to resolve the Connection leaks
/*if(rs != null)
{rs.close();}
if(pStmt != null)
{pStmt.close();}
if(connection != null)
{connection.close();}*/
ConnectionUtil.closeConnection(connection, pStmt, rs);
rs = null;
pStmt = null;
} catch (Exception ex) {
Logger.error(FILE_NAME, "Exception in finally Block" + ex);
}
}
return chargeIds;
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
EAChequeFuntionalityMaintenanceController mainControllerObj;
mainControllerObj = new EAChequeFuntionalityMaintenanceController();
if (request.getParameter("chequeIssueReport") != null) {
String batchNo = request.getParameter("batchNo");
StringTokenizer splitBatchNo = new StringTokenizer(batchNo, ",");
String[] batchNoArray = new String[splitBatchNo.countTokens()];
int i = 0;
while (splitBatchNo.hasMoreTokens()) {
batchNoArray[i++] = new String(splitBatchNo.nextToken());
}
ESupplyGlobalParameters loginBean =
(ESupplyGlobalParameters) request.getSession(true).getAttribute("loginbean");
EAChequeFunctionalityDAO chequeDAO = new EAChequeFunctionalityDAO();
String report = chequeDAO.retrieveChequeIssueReport(batchNoArray, loginBean.getLocalTime());
response.setContentType("Application/txt");
response.setHeader("Content-Disposition", "attachment; filename=" + batchNo + ".txt");
OutputStream outStream = response.getOutputStream();
java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
baos.write(report.getBytes());
baos.writeTo(outStream);
baos.close();
outStream.flush();
outStream.close();
} else if (request.getParameter("fetchNextOpenCheque") != null) {
String chequeNo = "";
String stockId = "";
String chequeCurrency = "";
Logger.info(FILE_NAME, "fetchNextOpenCheque");
HashMap chequeMap = new HashMap();
if (!"".equals(StringUtility.noNull(request.getParameter("searchCompanyId"))))
chequeMap.put("companyId", StringUtility.noNull(request.getParameter("searchCompanyId")));
if (!"".equals(StringUtility.noNull(request.getParameter("searchTerminalId"))))
chequeMap.put("terminalId", StringUtility.noNull(request.getParameter("searchTerminalId")));
if (!"".equals(StringUtility.noNull(request.getParameter("searchAccountCode"))))
chequeMap.put(
"accountCode", StringUtility.noNull(request.getParameter("searchAccountCode")));
if (!"".equals(StringUtility.noNull(request.getParameter("searchChequeStatus"))))
chequeMap.put(
"chequeStatus", StringUtility.noNull(request.getParameter("searchChequeStatus")));
// Added by AMIT On 02DEC
if (!"".equals(StringUtility.noNull(request.getParameter("searchAccountName"))))
chequeMap.put(
"accountName", StringUtility.noNull(request.getParameter("searchAccountName")));
chequeMap.put("singleCheque", "Y");
// @@ Added by Siva S for CR_2097
if (!"".equals(StringUtility.noNull(request.getParameter("reservedCheques")))) {
chequeMap.put(
"reservedCheques", StringUtility.noNull(request.getParameter("reservedCheques")));
}
// @@ for CR_2097
try {
ArrayList result =
new EAChequeFunctionalityAdvDAO().getResult(chequeMap, "SearchOpenCheque");
if (result != null && result.size() > 0) {
HashMap chequeDetail = (HashMap) result.get(0);
stockId = (String) chequeDetail.get("stockId");
chequeNo = (String) chequeDetail.get("chequeNo");
chequeCurrency = (String) chequeDetail.get("chequeCurrency");
}
} catch (Exception ex) {
ex.printStackTrace();
}
StringBuffer chequeXML = new StringBuffer("<cheque>");
chequeXML.append("<stockId>" + stockId + "</stockId>");
chequeXML.append("<chequeNo>" + chequeNo + "</chequeNo>");
chequeXML.append("<chequeCurrency>" + chequeCurrency + "</chequeCurrency>");
chequeXML.append("</cheque>");
response.setContentType("text/xml");
PrintWriter out = response.getWriter();
out.write(chequeXML.toString());
out.close();
} else mainControllerObj.handleRequest(request, response);
}
public ArrayList getResult(ETAdvancedLOVMasterVO searchValues) {
ArrayList details = new ArrayList();
PreparedStatement pStmt = null;
Connection connection = null;
ResultSet rs = null;
ETGRNAdvVO grnAttributes = null;
try {
grnAttributes = (ETGRNAdvVO) searchValues;
connection = this.getConnection();
// Grid column structure
// GRN No | Depot Id | Rec. Date | Time | Received By | Pieces | Transport Company | Driver |
// Shipper | Condition
StringBuffer selectClause = new StringBuffer("SELECT DISTINCT ");
selectClause.append("G.GRN_ID, G.DEPOT_PARTY_ID, G.RECIEVED_DATE_TIME, ");
selectClause.append("G.RECIEVED_BY, G.NO_OF_PIECES, G.TRANSPORT_COMPANY_NAME, ");
selectClause.append("G.DRIVER_NAME, G.SHIPPER_ID, G.CONDITION_CODE, ");
selectClause.append("DECODE(NVL(P.GRN_ID ,'0'), '0','F','T') PRQ_GRN_ID ");
selectClause.append(
" , DECODE(NVL(G.GRN_STATUS, 'Open'), 'CMPLTE', 'Closed', 'Open') GRN_STATUS "); // Added
// by
// viswanadham for WPBN-Issue : 182589 on 20090930
StringBuffer fromClause = new StringBuffer("FROM FS_FR_GRNDETAILS G, FS_FR_PICKUPREQUEST P ");
StringBuffer whereClause =
new StringBuffer(
"WHERE G.RECORD_STATUS ='A' AND G.GRN_ID=P.GRN_ID(+) AND G.TERMINALID ='"
+ grnAttributes.getTerminalID()
+ "'");
if (grnAttributes.getGRNId() != null && !grnAttributes.getGRNId().equals("")) {
whereClause.append(" AND G.GRN_ID ");
whereClause.append(
getSearchString(searchValues.getSearchType(), grnAttributes.getGRNId().trim()));
}
if (grnAttributes.getDepotId() != null && !grnAttributes.getDepotId().equals("")) {
whereClause.append(" AND G.DEPOT_PARTY_ID ");
whereClause.append(
getSearchString(searchValues.getSearchType(), grnAttributes.getDepotId().trim()));
}
if (grnAttributes.getReceivedBy() != null && !grnAttributes.getReceivedBy().equals("")) {
whereClause.append(" AND G.RECIEVED_BY ");
whereClause.append(
getSearchString(searchValues.getSearchType(), grnAttributes.getReceivedBy().trim()));
}
// @@ Added By Smitha for PR-ET-1516
if (grnAttributes.getShipperId() != null && !grnAttributes.getShipperId().equals("")) {
whereClause.append(" AND G.SHIPPER_ID ");
whereClause.append(
getSearchString(searchValues.getSearchType(), grnAttributes.getShipperId().trim()));
}
if (grnAttributes.getTransportCompany() != null
&& !grnAttributes.getTransportCompany().equals("")) {
whereClause.append(" AND G.TRANSPORT_COMPANY_NAME ");
whereClause.append(
getSearchString(
searchValues.getSearchType(), grnAttributes.getTransportCompany().trim()));
}
// @@ End for PR-ET-1516
if (grnAttributes.getGRNStatus() != null && !grnAttributes.getGRNStatus().equals("")) {
// @@ Added By A.Vijay Kumar for WPBN Issue id 87830 on 20071016
String grnStatus = grnAttributes.getGRNStatus();
if (grnStatus.equals("All")) {
grnStatus = "";
} else if (grnStatus.equals("Closed")) {
grnStatus = "CMPLTE";
}
// @@ for WPBN Issue id 87830 on 20071016
whereClause.append(" AND G.GRN_STATUS ");
// @@ Added By A.Vijay Kumar for WPBN Issue id 87830 on 20071016
if (!grnStatus.equals("Open")) {
whereClause.append(getSearchString(searchValues.getSearchType(), grnStatus.trim()));
} else {
whereClause.append("<>'CMPLTE' ");
}
// @@ for WPBN Issue id 87830 on 20071016
}
// Validating Dates
if (grnAttributes.getDateFrom() != null && !grnAttributes.getDateFrom().equals("")) {
whereClause.append(" AND G.RECIEVED_DATE_TIME >= ? ");
}
if (grnAttributes.getDateTo() != null && !grnAttributes.getDateTo().equals("")) {
whereClause.append(" AND G.RECIEVED_DATE_TIME-1 <= ? ");
}
// Over-------------
StringBuffer resultQuery = new StringBuffer();
resultQuery.append(selectClause);
resultQuery.append(fromClause);
resultQuery.append(whereClause);
if ("SearchGRN".equalsIgnoreCase(grnAttributes.getInvokerOperation())) {
if ("".equals(grnAttributes.getSortBy())) {
resultQuery.append(" ORDER BY G.GRN_ID ");
resultQuery.append(", G.RECIEVED_DATE_TIME DESC ");
} else if ("0".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.GRN_ID ASC");
else resultQuery.append(" ORDER BY G.GRN_ID DESC");
} else if ("1".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.DEPOT_PARTY_ID ASC");
else resultQuery.append(" ORDER BY G.DEPOT_PARTY_ID DESC");
} else if ("2".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.RECIEVED_DATE_TIME ASC");
else resultQuery.append(" ORDER BY G.RECIEVED_DATE_TIME DESC");
} else if ("3".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.RECIEVED_DATE_TIME ASC");
else resultQuery.append(" ORDER BY G.RECIEVED_DATE_TIME DESC");
} else if ("4".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.RECIEVED_BY ASC");
else resultQuery.append(" ORDER BY G.RECIEVED_BY DESC");
} else if ("5".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.NO_OF_PIECES ASC");
else resultQuery.append(" ORDER BY G.NO_OF_PIECES DESC");
} else if ("6".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.TRANSPORT_COMPANY_NAME ASC");
else resultQuery.append(" ORDER BY G.TRANSPORT_COMPANY_NAME DESC");
} else if ("7".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.DRIVER_NAME ASC");
else resultQuery.append(" ORDER BY G.DRIVER_NAME DESC");
} else if ("8".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.SHIPPER_ID ASC");
else resultQuery.append(" ORDER BY G.SHIPPER_ID DESC");
} else if ("9".equals(grnAttributes.getSortBy())) {
if ("ascending".equals(grnAttributes.getSortOrder()))
resultQuery.append(" ORDER BY G.CONDITION_CODE ASC");
else resultQuery.append(" ORDER BY G.CONDITION_CODE DESC");
}
} else {
resultQuery.append(" ORDER BY G.GRN_ID DESC ");
}
Logger.info(FILE_NAME, "......Final Query......" + resultQuery.toString());
pStmt = connection.prepareStatement(resultQuery.toString());
if (grnAttributes.getDateFrom() != null) pStmt.setTimestamp(1, grnAttributes.getDateFrom());
if (grnAttributes.getDateFrom() != null && grnAttributes.getDateTo() != null)
pStmt.setTimestamp(2, grnAttributes.getDateTo());
else if (grnAttributes.getDateTo() != null) pStmt.setTimestamp(1, grnAttributes.getDateTo());
rs = pStmt.executeQuery();
while (rs.next()) {
ETGRNAdvVO grnAttributesArr = new ETGRNAdvVO();
grnAttributesArr.setGRNId(checkNull(rs.getString("GRN_ID")));
grnAttributesArr.setDepotId(checkNull(rs.getString("DEPOT_PARTY_ID")));
grnAttributesArr.setReceivedDateTime(rs.getTimestamp("RECIEVED_DATE_TIME"));
grnAttributesArr.setReceivedBy(checkNull(rs.getString("RECIEVED_BY")));
grnAttributesArr.setNoOfPieces(checkNull(rs.getString("NO_OF_PIECES")));
grnAttributesArr.setTransportCompany(checkNull(rs.getString("TRANSPORT_COMPANY_NAME")));
grnAttributesArr.setDriverName(checkNull(rs.getString("DRIVER_NAME")));
grnAttributesArr.setShipperId(checkNull(rs.getString("SHIPPER_ID")));
grnAttributesArr.setCondition(checkNull(rs.getString("CONDITION_CODE")));
// Modified by viswanadham for WPBN-Issue : 182589 on 20090930
// grnAttributesArr.setRecordStatus(grnAttributes.getGRNStatus()!=
// null?grnAttributes.getGRNStatus():"All");
grnAttributesArr.setRecordStatus(checkNull(rs.getString("GRN_STATUS")));
details.add(grnAttributesArr);
}
} catch (Exception e) {
e.printStackTrace();
Logger.error(FILE_NAME, "Exception in getResult =====" + e.toString());
throw new EJBException(e.getMessage());
} finally {
ConnectionUtil.closeConnection(connection, pStmt, rs);
}
return details;
} // end of getResult()
