Java tutorial
package com.iana.boesc.dao; import java.io.File; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.log4j.Logger; import org.springframework.jdbc.datasource.DriverManagerDataSource; import com.iana.boesc.pojo.DVIRRCDStatisticBean; import com.iana.boesc.pojo.DVIR_EDI322Bean; import com.iana.boesc.pojo.EDI322Bean; import com.iana.boesc.pojo.GIERInfoDetails; import com.iana.boesc.pojo.UIIAInfoDetails; import com.iana.boesc.utility.DateTimeFormater; import com.iana.boesc.utility.GlobalVariables; import com.iana.boesc.utility.Utility; import com.iana.boesc.utility.ValidationUtils; public class BOESCDaoImpl implements BOESCDao { private final Logger logger = Logger.getLogger(this.getClass().getName()); private DriverManagerDataSource boescDataSource; private DriverManagerDataSource gierDataSource; private DriverManagerDataSource uiiaDataSource; public DriverManagerDataSource getGierDataSource() { return gierDataSource; } public void setGierDataSource(DriverManagerDataSource gierDataSource) { this.gierDataSource = gierDataSource; } public DriverManagerDataSource getBoescDataSource() { return boescDataSource; } public void setBoescDataSource(DriverManagerDataSource boescDataSource) { this.boescDataSource = boescDataSource; } public DriverManagerDataSource getUiiaDataSource() { return uiiaDataSource; } public void setUiiaDataSource(DriverManagerDataSource uiiaDataSource) { this.uiiaDataSource = uiiaDataSource; } @Override public DriverManagerDataSource getDataSource() { return this.boescDataSource; } @Override public Connection getConnection() throws SQLException { return this.boescDataSource.getConnection(); } @Override public Map<String, Map<String, Object>> getAllRegisteredUserDetailsMapByTypeId(int userTypeId) { Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>(); QueryRunner run = new QueryRunner(getDataSource()); try { String query = "SELECT C.BOESC_USER_ID,U.SCAC,C.SEG_DELIM, C.ELE_DELIM,C.TRAN_TYPE FROM BOESC_CONFIG C, BOESC_USERS U WHERE " + " C.BOESC_USER_ID = U.BOESC_USER_ID AND C.TRAN_TYPE IN ('" + GlobalVariables.BOESC + "','" + GlobalVariables.DVIR + "') AND U.BOESC_USER_TYPE_ID = ? "; List<Map<String, Object>> listUsersDetails = run.query(query, new MapListHandler(), userTypeId); //logger.info("List size ::"+listUsersDetails.size()); //logger.info("List ::"+listUsersDetails); if (listUsersDetails == null || listUsersDetails.size() == 0) return map; else map = populateUserDetailsFromUserType(userTypeId, listUsersDetails); } catch (SQLException e) { e.printStackTrace(); logger.error("SQLException occured :", e); logger.error("We are unable to load user information from DB due to SQL Exception."); } catch (Exception e) { e.printStackTrace(); logger.error("Exception occured :", e); logger.error("We are unable to load user information from DB due to Exception."); } return map; } private Map<String, Map<String, Object>> populateUserDetailsFromUserType(int userTypeId, List<Map<String, Object>> listUsersDetails) { Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>(); for (Map<String, Object> m : listUsersDetails) { if (userTypeId == GlobalVariables.USER_TYPE_ID_IEP) { if (ValidationUtils.validateStringObject(m.get("SCAC")).equals("")) { continue; } } else { if (ValidationUtils.validateStringObject(m.get("BOESC_USER_ID")).equals("")) { continue; } } String mapKey = null; if (userTypeId == GlobalVariables.USER_TYPE_ID_IEP) { mapKey = m.get("SCAC").toString(); //for IEP Key stored as SCAC Code while } else { if (userTypeId == GlobalVariables.USER_TYPE_ID_MRV) { mapKey = m.get("BOESC_USER_ID").toString() + GlobalVariables.DIRNAME_END_MRV; // For rest of the user Primary Key is stored as key } if (userTypeId == GlobalVariables.USER_TYPE_ID_FO) { mapKey = m.get("BOESC_USER_ID").toString() + GlobalVariables.DIRNAME_END_FO; // For rest of the user Primary Key is stored as key } if (userTypeId == GlobalVariables.USER_TYPE_ID_SU) { mapKey = m.get("BOESC_USER_ID").toString() + GlobalVariables.DIRNAME_END_SEC; // For rest of the user Primary Key is stored as key } } //logger.info("mapKey::"+mapKey); if (map.containsKey(mapKey)) { //Already contains Map key if (map.get(mapKey).containsKey("BOESC_SEG_DELI")) { // Now insert DVIR SEG_DELI map.get(mapKey).put("DVIR_SEG_DELI", m.get("SEG_DELIM")); map.get(mapKey).put("DVIR_ELE_DELI", m.get("ELE_DELIM")); } else { map.get(mapKey).put("BOESC_SEG_DELI", m.get("SEG_DELIM")); map.get(mapKey).put("BOESC_ELE_DELI", m.get("ELE_DELIM")); } } else { // found new Map Key code Map<String, Object> innerMap = new HashMap<String, Object>(); if (!ValidationUtils.validateStringObject(m.get("TRAN_TYPE")).equals("")) { String tranType = m.get("TRAN_TYPE").toString(); if (tranType.equalsIgnoreCase(GlobalVariables.BOESC)) { innerMap.put("BOESC_SEG_DELI", m.get("SEG_DELIM")); innerMap.put("BOESC_ELE_DELI", m.get("ELE_DELIM")); } else { innerMap.put("DVIR_SEG_DELI", m.get("SEG_DELIM")); innerMap.put("DVIR_ELE_DELI", m.get("ELE_DELIM")); } innerMap.put("BOESC_USER_ID", m.get("BOESC_USER_ID")); map.put(mapKey, innerMap); } } } return map; } @Override public boolean getPopulatedDataAndInsert(EDI322Bean eb, String boescUserId, String userType, File file, List<String> finalErrorList, Map<Integer, Object> fileTransStatus) throws Exception { logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId + " userType ::" + userType); String status = ""; logger.info("----- ISA ............"); String gsHeader = ""; String gsControl = ""; String st_control = ""; String n7Header = ""; String q5Header = ""; String equip_prefix = ""; String equip_number = ""; String w2Header = ""; String r4Header = ""; String r13Header = ""; String n9Header = ""; String eventType = ""; String port_qual = ""; String iana_splc = ""; QueryRunner qrun = new QueryRunner(getDataSource()); logger.info("----- GE ............"); for (int i = 0; i < eb.getListGSDetails().size(); i++) { gsHeader = eb.getListGSDetails().get(i).getHeaderDetails(); gsControl = eb.getListGSDetails().get(i).getGroupControlNumber(); logger.info("gsControl ::" + gsControl + " gsHeader ::" + gsHeader); int startIndex = i + 1; logger.info("----- ST & SE ............"); for (int a = 0; a < eb.getSTDetailsMap().get(startIndex).size(); a++) { Connection conn = getConnection(); conn.setAutoCommit(false); PreparedStatement pstmt = null; ResultSet rs = null; StringBuilder sbQuery = new StringBuilder( "INSERT INTO BOESC_TRAN_SET (ISA_HEADER, GS_HEADER, INPUT_TYPE, SENDER_ID, SENDER_TYPE, "); sbQuery.append( " ISA_DATETIME, GS_CONTROL, ST_CONTROL, EVENT_TYPE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, IEP_DOT, PORT_QUAL, IANA_SPLC, "); sbQuery.append(" POOL_ID, POOL_NAME, Q5_SEG, N7_SEG, W2_SEG, R4_SEG, N9_SEG, R13_SEG, "); if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) { sbQuery.append(" RECEIVER_ID, REC_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) { sbQuery.append(" MRV_ID, MRV_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) { sbQuery.append(" FO_ID, FO_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_SU)) { } try { status = ""; int changedIndex = a + 1; //very important Variable if (fileTransStatus != null && fileTransStatus.size() > 0) { logger.info("-------------------- changedIndex ::" + changedIndex + " fileTransStatus.get(startIndex) ::" + fileTransStatus.get(changedIndex)); if (fileTransStatus.get(changedIndex) == null) { status = GlobalVariables.STATUS_PENDING; } else { status = GlobalVariables.STATUS_REJECTED; } } else { status = GlobalVariables.STATUS_PENDING; } r13Header = ""; r4Header = ""; n9Header = ""; port_qual = ""; iana_splc = ""; GIERInfoDetails gierInfo = null; st_control = eb.getSTDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber(); logger.info(" st_control :" + st_control); /*String transactionControlNumberSE = eb.getSEDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber(); logger.info(" transactionControlNumberSE :"+transactionControlNumberSE );*/ logger.info("----- N7 ............"); for (int q = 0; q < eb.getN7DetailsMap().get(startIndex).get(changedIndex).size(); q++) { n7Header = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); logger.info("n7Header ::" + n7Header); equip_prefix = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q) .getEquipmentInitial(); equip_number = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q) .getEquipmentNumber(); logger.info("equip_prefix ::" + equip_prefix); logger.info("equip_number ::" + equip_number); equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix; equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number; gierInfo = getDVIRAdditionaldetails(equip_prefix, equip_number); //logger.info("gierInfo ::"+gierInfo); } logger.info("----- Q5 ............"); for (int q = 0; q < eb.getQ5DetailsMap().get(startIndex).get(changedIndex).size(); q++) { q5Header = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); eventType = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getStatusCode(); logger.info("q5Header ::" + q5Header + " eventType ::" + eventType); } logger.info("----- W2 ............"); for (int q = 0; q < eb.getW2DetailsMap().get(startIndex).get(changedIndex).size(); q++) { w2Header = eb.getW2DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); logger.info("w2Header ::" + w2Header); } logger.info("----- R4 ............"); String tempR4Header = ""; String tempPort_qual = ""; String tempIana_splc = ""; for (int q = 0; q < eb.getR4DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempR4Header = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); tempPort_qual = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getLocationQualifier(); tempIana_splc = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getLocationIdentifier(); r4Header = r4Header + GlobalVariables.FIELD_SEPARATOR + tempR4Header; port_qual = port_qual + GlobalVariables.FIELD_SEPARATOR + tempPort_qual; iana_splc = iana_splc + GlobalVariables.FIELD_SEPARATOR + tempIana_splc; logger.info("r4Header ::" + r4Header + " port_qual:: " + port_qual + " iana_splc ::" + iana_splc); } r4Header = r4Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r4Header.substring(1) : r4Header; port_qual = port_qual.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? port_qual.substring(1) : port_qual; iana_splc = iana_splc.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? iana_splc.substring(1) : iana_splc; logger.info("----- R13 ............"); String tempR13Header = ""; for (int q = 0; q < eb.getR13DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempR13Header = eb.getR13DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); r13Header = r13Header + GlobalVariables.FIELD_SEPARATOR + tempR13Header; logger.info("r13Header ::" + r13Header); } r13Header = r13Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r13Header.substring(1) : r13Header; logger.info("----- N9 ............"); String tempN9Header = ""; for (int q = 0; q < eb.getN9DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempN9Header = eb.getN9DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); n9Header = n9Header + GlobalVariables.FIELD_SEPARATOR + tempN9Header; logger.info("n9Header ::" + n9Header); } n9Header = n9Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? n9Header.substring(1) : n9Header; sbQuery.append( " , CREATED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); if (gierInfo == null) { gierInfo = new GIERInfoDetails(); //this situation happen when all segment are missing except : ISA,SE,ST,GE,GS,IEA } equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix; equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number; pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, eb.getISADetails().getHeaderDetails()); pstmt.setString(2, gsHeader); pstmt.setString(3, GlobalVariables.INPUT_TYPE_BOESC_322); pstmt.setString(4, eb.getISADetails().getInterchangeSenderId()); pstmt.setString(5, userType); pstmt.setString(6, eb.getISADetails().getInterchangeDate()); pstmt.setString(7, gsControl); pstmt.setString(8, st_control); pstmt.setString(9, eventType); pstmt.setString(10, equip_prefix); pstmt.setString(11, equip_number); pstmt.setString(12, equip_prefix + equip_number); pstmt.setString(13, gierInfo.getCompanySCACCode() == null ? "" : gierInfo.getCompanySCACCode()); pstmt.setString(14, gierInfo.getUsDotNumber() == null ? "" : gierInfo.getUsDotNumber()); pstmt.setString(15, port_qual); pstmt.setString(16, iana_splc); pstmt.setString(17, gierInfo.getChassisPoolId() == null ? "" : gierInfo.getChassisPoolId()); pstmt.setString(18, gierInfo.getChassisPoolName() == null ? "" : gierInfo.getChassisPoolName()); pstmt.setString(19, q5Header); pstmt.setString(20, n7Header); pstmt.setString(21, w2Header); pstmt.setString(22, r4Header); pstmt.setString(23, n9Header); pstmt.setString(24, r13Header); pstmt.setString(25, boescUserId); pstmt.setString(26, status); pstmt.setObject(27, DateTimeFormater.getSqlSysTimestamp()); logger.info("query :: " + sbQuery.toString()); int dbStat = 0; int boescKey = 0; dbStat = pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); if (dbStat != 0) { if (rs != null) { while (rs.next()) { boescKey = rs.getInt(1); logger.info("boescKey: " + boescKey); } } conn.commit(); } else { conn.rollback(); } if (boescKey != 0) { //Update BOESC_UNIQUE_NO : using business logic String sql = "UPDATE BOESC_TRAN_SET SET BOESC_UNIQUE_NO = ? WHERE BOESC_TRAN_ID = ? "; qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(boescKey, "BOESC-"), boescKey }); logger.info("Record Inserted successfully for BOESC..." + file.getName()); return true; } else { logger.error("Failure Data insertion in BOESC.."); } } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex1) { logger.error("Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage()); ex1.printStackTrace(); throw ex1; } catch (Exception e) { logger.error("Caught SQL exception in finally block " + e.getMessage()); e.printStackTrace(); throw e; } } } } return false; } @Override public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId, String userType, File file) throws Exception { logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId + " userType ::" + userType); QueryRunner qrun = new QueryRunner(getDataSource()); Connection conn = getConnection(); conn.setAutoCommit(false); PreparedStatement pstmt = null; ResultSet rs = null; try { StringBuilder sbQuery = new StringBuilder( "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,"); sbQuery.append( " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID, "); sbQuery.append( " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN, "); sbQuery.append( " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09, "); if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) { sbQuery.append(" IEP_ID, STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) { sbQuery.append(" MRV_ID, STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) { sbQuery.append(" FO_ID, STATUS "); } sbQuery.append( " ,CREATED_DATE ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); //get Additional Details from GIER DB GIERInfoDetails gierInfo = null; gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber()); System.out.println("Before UIIA Datasource"); UIIAInfoDetails uiiaInfo = null; uiiaInfo = getUIIAdetailsforDVIR(eb); //logger.info("gierInfo ::"+gierInfo); pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, eb.getIsaheader()); pstmt.setString(2, eb.getGsHeader()); pstmt.setString(3, eb.getSenderId()); pstmt.setString(4, userType); pstmt.setString(5, eb.getIsaDateTime()); pstmt.setString(6, eb.getGsControl()); pstmt.setString(7, eb.getStControl()); pstmt.setString(8, eb.getInspDate()); pstmt.setString(9, eb.getInspTime()); pstmt.setString(10, eb.getInspTimeZone()); pstmt.setString(11, eb.getEqpInitial()); pstmt.setString(12, eb.getEqpNumber()); pstmt.setString(13, eb.getChassisId()); pstmt.setString(14, gierInfo.getCompanySCACCode()); pstmt.setString(15, eb.getPortQualifier()); pstmt.setString(16, eb.getPortIdentifier()); pstmt.setString(17, eb.getDrvState()); pstmt.setString(18, eb.getDrvLicNo()); pstmt.setString(19, uiiaInfo.getDrvName()); pstmt.setString(20, eb.getMcScac()); pstmt.setString(21, eb.getMcName()); pstmt.setString(22, eb.getRcdInfo()); pstmt.setString(23, gierInfo.getUsDotNumber()); pstmt.setString(24, uiiaInfo.getMcEin()); pstmt.setString(25, uiiaInfo.getMcDot()); pstmt.setString(26, uiiaInfo.getIddPin()); pstmt.setString(27, eb.getQ5Details()); pstmt.setString(28, eb.getN7Details()); pstmt.setString(29, eb.getR4Details()); pstmt.setString(30, eb.getN1Details()); pstmt.setString(31, eb.getN1DrDetails()); pstmt.setInt(32, bean.getNoDefectsCount()); pstmt.setInt(33, bean.getBrakesCount()); pstmt.setInt(34, bean.getLightsCount()); pstmt.setInt(35, bean.getWheelCount()); pstmt.setInt(36, bean.getAirlineCount()); pstmt.setInt(37, bean.getCouplingCount()); pstmt.setInt(38, bean.getFrameCount()); pstmt.setInt(39, bean.getBolsterCount()); pstmt.setInt(40, bean.getFastenerCount()); pstmt.setInt(41, bean.getSliderCount()); pstmt.setString(42, boescUserId); pstmt.setString(43, GlobalVariables.STATUS_PENDING); pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp()); int dbStat = 0; int dvirKey = 0; dbStat = pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); if (dbStat != 0) { if (rs != null) { while (rs.next()) { dvirKey = rs.getInt(1); logger.info("dvirKey: " + dvirKey); } } } if (dvirKey != 0) { conn.commit(); //Update BOESC_UNIQUE_NO : using business logic String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? "; qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey }); logger.info("Record Inserted successfully for DVIR..." + file.getName()); return true; } else { conn.rollback(); logger.error("Failure Data insertion in DVIR.."); } } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex1) { logger.error( "Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage()); ex1.printStackTrace(); throw ex1; } catch (Exception e) { logger.error("Caught SQL exception in finally block " + e.getMessage()); e.printStackTrace(); throw e; } } return false; } /*----------------------------------------------USE OF GEAR DATA SOURCE ----------------------------------------------------------------*/ @Override public GIERInfoDetails getDVIRAdditionaldetails(String eqPrefix, String eqNumber) throws Exception { QueryRunner run = new QueryRunner(getGierDataSource()); logger.info("In getDVIRAdditionaldetails(" + eqPrefix + " , " + eqNumber + " )..."); GIERInfoDetails gierInfo = new GIERInfoDetails(); boolean errorFlag = false; String sql = "SELECT CurrentIEPCompanyId,ch_pool_id FROM Equipment WHERE EqPrefix = ? AND EqNumber = ?"; Map<String, Object> eqDetailsMap = run.query(sql, new MapHandler(), new Object[] { eqPrefix, eqNumber }); if (eqDetailsMap != null && eqDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(eqDetailsMap.get("CurrentIEPCompanyId")).equals("") && !ValidationUtils.validateStringObject(eqDetailsMap.get("ch_pool_id")).equals("")) { String companyId = eqDetailsMap.get("CurrentIEPCompanyId").toString(); String chassisPoolId = eqDetailsMap.get("ch_pool_id").toString(); sql = "SELECT CompanySCACcd, USDOTNumber FROM Company WHERE CompanyId = ?"; Map<String, Object> companyDetailsMap = run.query(sql, new MapHandler(), new Object[] { companyId }); if (companyDetailsMap != null && companyDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(companyDetailsMap.get("CompanySCACcd")).equals("") && !ValidationUtils.validateStringObject(companyDetailsMap.get("USDOTNumber")) .equals("")) { String scacCode = companyDetailsMap.get("CompanySCACcd").toString(); String usdotNumber = companyDetailsMap.get("USDOTNumber").toString(); sql = "SELECT CH_POOL_ID, CH_POOL_NAME FROM chassispool WHERE POOL_ID = ?"; Map<String, Object> poolDetailsMap = run.query(sql, new MapHandler(), new Object[] { chassisPoolId }); if (poolDetailsMap != null && poolDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(poolDetailsMap.get("CH_POOL_ID")).equals("") && !ValidationUtils.validateStringObject(poolDetailsMap.get("CH_POOL_NAME")) .equals("")) { String ch_pool_id = poolDetailsMap.get("CH_POOL_ID").toString(); String ch_pool_name = poolDetailsMap.get("CH_POOL_NAME").toString(); gierInfo = setGIERDetails(ch_pool_id, ch_pool_name, scacCode, usdotNumber); } else { gierInfo = setGIERDetails("", "", scacCode, usdotNumber); } } else { gierInfo = setGIERDetails("", "", scacCode, usdotNumber); } } else { errorFlag = true; } } else { errorFlag = true; } } else { errorFlag = true; } } else { errorFlag = true; } if (errorFlag) { gierInfo = setGIERDetails("", "", "", ""); } return gierInfo; } private GIERInfoDetails setGIERDetails(String poolId, String poolName, String scacCode, String usdotNumber) { GIERInfoDetails gierInfo = new GIERInfoDetails(); gierInfo.setChassisPoolId(poolId); gierInfo.setChassisPoolName(poolName); gierInfo.setCompanySCACCode(scacCode); gierInfo.setUsDotNumber(usdotNumber); return gierInfo; } /*----------------------------------------------USE OF UIIA DATA SOURCE ----------------------------------------------------------------*/ @Override public UIIAInfoDetails getUIIAdetailsforDVIR(DVIR_EDI322Bean eb) throws Exception { System.out.println("Inside getUIIAdetailsforDVIR"); QueryRunner run = new QueryRunner(getUiiaDataSource()); UIIAInfoDetails uiiaInfo = new UIIAInfoDetails(); StringBuffer sbQry = new StringBuffer( "SELECT CONCAT(IFNULL(d.DRV_FNAME,''),' ',IFNULL(d.DRV_LNAME,'')) AS DRV_NAME, IFNULL(d.MC_EIN,'') MC_EIN, IFNULL(d.idd_pin,'') idd_pin, IFNULL(d.MC_DOT,'') MC_DOT,IFNULL(d.SCAC_CODE,'') SCAC_CODE FROM dvir_driver d "); sbQry.append( " WHERE d.SCAC_CODE =? AND d.DRV_LICENSE_NO = ? AND d.STATE_ABBR = ? AND d.DRV_TERMINATED_DT IS NULL"); Map<String, Object> mcDetailsMap = run.query(sbQry.toString(), new MapHandler(), new Object[] { eb.getMcScac(), eb.getDrvLicNo(), eb.getDrvState() }); if (mcDetailsMap != null && mcDetailsMap.size() > 0) { if (!ValidationUtils.validateStringObject(mcDetailsMap.get("idd_pin")).equals("")) { String drvName = mcDetailsMap.get("DRV_NAME").toString(); String mcEin = mcDetailsMap.get("MC_EIN").toString(); String mcDot = mcDetailsMap.get("MC_DOT").toString(); String iddPin = mcDetailsMap.get("idd_pin").toString(); uiiaInfo = setUIIADetails(drvName, mcEin, mcDot, iddPin); } else { uiiaInfo = setUIIADetails("", "", "", ""); } } return uiiaInfo; } private UIIAInfoDetails setUIIADetails(String _drvName, String _mcEin, String _mcDot, String _iddPin) { UIIAInfoDetails uiiaInfo = new UIIAInfoDetails(); uiiaInfo.setDrvName(_drvName); uiiaInfo.setMcEin(_mcEin); uiiaInfo.setMcDot(_mcDot); uiiaInfo.setIddPin(_iddPin); return uiiaInfo; } }