List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void updateIndexPage(String fileName, com.photon.phresco.commons.model.TestCase tstCase, List<TestCase> testCases, HSSFWorkbook myWorkBook) throws UnknownHostException, PhrescoException { Iterator<Row> rowIterator; float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); String testId = tstCase.getTestCaseId(); String status = tstCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalPass = totalPass + 1;/* w w w. ja va2 s . c o m*/ } else if (testCaseStatus.equalsIgnoreCase("failure") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalBlocked = totalBlocked + 1; } if (testCase.getTestCaseId().equalsIgnoreCase(testId) && !testCase.getStatus().equalsIgnoreCase("success") && status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCase.getTestCaseId().equalsIgnoreCase(testId) && !testCase.getStatus().equalsIgnoreCase("failure") && status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCase.getTestCaseId().equalsIgnoreCase(testId) && !testCase.getStatus().equalsIgnoreCase("notApplicable") && status.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCase.getTestCaseId().equalsIgnoreCase(testId) && !testCase.getStatus().equalsIgnoreCase("blocked") && status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } HSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (StringUtils.isNotEmpty(tstCase.getTestCaseId()) && createObject.getName().equals(fileName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, 0, ""); } } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void writeTestCaseToXLS(String testSuiteName, String[] cellValue, String status, int numCol, int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException { Iterator<Row> rowIterator; try {/*from w w w . ja v a2s . c o m*/ FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet myHssfSheet = myWorkBook.getSheetAt(j); if (myHssfSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHssfSheet.rowIterator(); Row next; for (Cell cell : myHssfSheet.getRow((myHssfSheet.getLastRowNum()) - 1)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; if (cellno == 15) { break; } } float totalPass = 0; float totalFail = 0; float totalNotApp = 0; float totalBlocked = 0; float notExecuted = 0; float totalTestCases = 0; for (int i = 0; i <= 22; i++) { rowIterator.next(); } do { next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(1))) && !getValue(next.getCell(0)).equalsIgnoreCase("S.NO")) { String value = getValue(next.getCell(11)); if (StringUtils.isNotEmpty(value)) { if (value.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (value.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (value.equalsIgnoreCase("notApplicable")) { totalNotApp = totalNotApp + 1; } else if (value.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } else { notExecuted = notExecuted + 1; } } } while (rowIterator.hasNext()); //to update the status in the index page if (status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (status.equalsIgnoreCase("notApplicable")) { totalNotApp = totalNotApp + 1; } else if (status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } else { notExecuted = notExecuted + 1; } totalTestCases = totalPass + totalFail + totalNotApp + totalBlocked + notExecuted; HSSFSheet mySheetHssf = myWorkBook.getSheetAt(0); rowIterator = mySheetHssf.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (createObject.getName().equals(testSuiteName)) { addCalculationsToIndex(totalPass, totalFail, totalNotApp, totalBlocked, notExecuted, totalTestCases, next1); } } } Row r = null; if (myHssfSheet.getSheetName().equalsIgnoreCase("Index")) { r = myHssfSheet.createRow(next.getRowNum() - 1); } else { r = myHssfSheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); if (tryStyle[i] != null) { cell.setCellStyle(tryStyle[i]); } } FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } } } catch (PhrescoException e) { throw new PhrescoException(e); } catch (FileNotFoundException e) { throw new PhrescoException(e); } catch (IOException e) { throw new PhrescoException(e); } }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
public static ByteArrayOutputStream createTollUploadErrorResponse(InputStream is, List<String> errors) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFont font = wb.createFont();/*from ww w.ja v a2s .c om*/ font.setColor(Font.COLOR_RED); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font); HSSFSheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(0); int lastCell = row.getLastCellNum(); Cell cell = createExcelCell(sheet, row, lastCell, 256 * 100); cell.setCellStyle(cellStyle); cell.setCellValue("ERRORS"); for (String anError : errors) { String lineNoStr = StringUtils.substringBefore(anError, ":"); lineNoStr = StringUtils.substringAfter(lineNoStr, "Line "); Integer lineNo = new Integer(lineNoStr) - 1; row = sheet.getRow(lineNo); cell = createExcelCell(sheet, row, lastCell, 256 * 100); cell.setCellStyle(cellStyle); cell.setCellValue(anError); } return createOutputStream(wb); }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importLoadMiles(InputStream is, Long createdBy) throws Exception { List<LocationDistance> locationDistanceList = new ArrayList<LocationDistance>(); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; try {//from ww w . j a va 2s .co m POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount == 1) { continue; } boolean recordError = false; boolean fatalRecordError = false; StringBuffer recordErrorMsg = new StringBuffer(); LocationDistance locationDistance = null; try { String originName = ((String) getCellValue(row.getCell(3))); if (StringUtils.equals("END_OF_DATA", originName)) { break; } locationDistance = new LocationDistance(); List<Location> originList = retrieveLocationData(1, originName); if (originList.isEmpty()) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Transfer Station,"); } else { locationDistance.setOrigin(originList.get(0)); } String destinationName = ((String) getCellValue(row.getCell(4))); List<Location> destinationList = retrieveLocationData(2, destinationName); if (destinationList.isEmpty()) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Landfill,"); } else { locationDistance.setDestination(destinationList.get(0)); } Double miles = row.getCell(5).getNumericCellValue(); if (miles == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Miles,"); } else { locationDistance.setMiles(miles); } if (checkDuplicate(locationDistance)) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Duplicate record,"); } } catch (Exception ex) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Error while processing record, Line: " + recordCount); } if (recordError) { String msgPreffix = fatalRecordError ? "Record NOT loaded->" : "Record LOADED, but has errors->"; errorList.add(msgPreffix + "Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; } if (!fatalRecordError) { locationDistanceList.add(locationDistance); } } System.out.println("Done processing...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records being loaded: " + locationDistanceList.size()); if (!locationDistanceList.isEmpty()) { for (LocationDistance aLocationDistance : locationDistanceList) { aLocationDistance.setStatus(1); aLocationDistance.setCreatedBy(createdBy); aLocationDistance.setCreatedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(aLocationDistance); } } } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Location Distancedata: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importSubcontractorRateMainSheet(InputStream is, Date validFrom, Date validTo, Long createdBy) throws Exception { List<SubcontractorRate> subcontractorRateList = new ArrayList<SubcontractorRate>(); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; try {//from w w w .j a v a2 s . c o m POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount == 1) { continue; } boolean recordError = false; boolean fatalRecordError = false; StringBuffer recordErrorMsg = new StringBuffer(); SubcontractorRate subcontractorRate = null; try { String subcontractorName = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", subcontractorName)) { break; } String companyName = ((String) getCellValue(row.getCell(1))); String transferStationName = ((String) getCellValue(row.getCell(2))); String landfillName = ((String) getCellValue(row.getCell(3))); String billUsing = ((String) getCellValue(row.getCell(4))); String sortBy = ((String) getCellValue(row.getCell(5))); String rateType = ((String) getCellValue(row.getCell(6))); // Load date - 1, Unload date - 2 String rateUsing = "Load date"; Double rate = row.getCell(7).getNumericCellValue(); Double fuelSurchargeAmount = row.getCell(8).getNumericCellValue(); Double otherCharges = row.getCell(9).getNumericCellValue(); subcontractorRate = new SubcontractorRate(); String query = "select obj from SubContractor obj where " + " obj.status=1" + " and obj.name='" + subcontractorName + "'"; List<SubContractor> subcontractorList = genericDAO.executeSimpleQuery(query); if (subcontractorList.isEmpty()) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Subcontractor,"); } else { subcontractorRate.setSubcontractor(subcontractorList.get(0)); } List<Location> companyList = retrieveLocationData(3, companyName); if (companyList.isEmpty()) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Company,"); } else { subcontractorRate.setCompanyLocation(companyList.get(0)); } List<Location> transferStationList = retrieveLocationData(1, transferStationName); if (transferStationList.isEmpty()) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Transfer Station,"); } else { subcontractorRate.setTransferStation(transferStationList.get(0)); } List<Location> landfillList = retrieveLocationData(2, landfillName); if (landfillList.isEmpty()) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Landfill,"); } else { subcontractorRate.setLandfill(landfillList.get(0)); } List<StaticData> rateTypesList = retrieveStaticData("RATE_TYPE", rateType); List<StaticData> billUsingList = retrieveStaticData("BILL_USING", billUsing); List<StaticData> sortByList = retrieveStaticData("BILL_USING", sortBy); List<StaticData> rateUsingList = retrieveStaticData("RATE_USING", rateUsing); subcontractorRate.setRateType(new Integer(rateTypesList.get(0).getDataValue())); subcontractorRate.setBillUsing(new Integer(billUsingList.get(0).getDataValue())); subcontractorRate.setSortBy(new Integer(sortByList.get(0).getDataValue())); subcontractorRate.setRateUsing(new Integer(rateUsingList.get(0).getDataValue())); subcontractorRate.setFuelSurchargeAmount(fuelSurchargeAmount); subcontractorRate.setOtherCharges(otherCharges); subcontractorRate.setValue(rate); subcontractorRate.setValidFrom(validFrom); subcontractorRate.setValidTo(validTo); if (checkDuplicate(subcontractorRate)) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Duplicate record,"); } } catch (Exception ex) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Error while processing record,"); } if (recordError) { String msgPreffix = fatalRecordError ? "Record NOT loaded->" : "Record LOADED, but has errors->"; errorList.add(msgPreffix + "Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; } if (!fatalRecordError) { subcontractorRateList.add(subcontractorRate); } } System.out.println("Done processing...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records being loaded: " + subcontractorRateList.size()); if (!subcontractorRateList.isEmpty()) { for (SubcontractorRate aSubcontractorRate : subcontractorRateList) { aSubcontractorRate.setStatus(1); aSubcontractorRate.setCreatedBy(createdBy); aSubcontractorRate.setCreatedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(aSubcontractorRate); } } } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Subcontractor Rate data: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importWMInvoice(InputStream is, Long createdBy) throws Exception { SimpleDateFormat wmDateFormat = new SimpleDateFormat("MM/dd/yyyy"); SimpleDateFormat wmDateTimeFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy"); // 5/30/2017 4:15:53 AM SimpleDateFormat wmDateTimeStrFormat = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a"); SimpleDateFormat requiredTimeFormat = new SimpleDateFormat("HH:mm"); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; int successCount = 0; try {/*from ww w .j a v a 2 s .com*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Map<String, Integer> colMapping = TicketUtils.getWMInvoiceColMapping(); if (colMapping.size() <= 0) { errorList.add("Location not supported"); return errorList; } int recordsToBeSkipped = TicketUtils.getWMInvoiceRecordsToBeSkipped(); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount <= recordsToBeSkipped) { continue; } boolean recordError = false; StringBuffer recordErrorMsg = new StringBuffer(); WMInvoice currentWMInvoice = null; try { String endOfData = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", endOfData)) { break; } currentWMInvoice = new WMInvoice(); Integer ticketCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TICKET); String ticketStr = ((String) getCellValue(row.getCell(ticketCol))); if (StringUtils.isEmpty(ticketStr)) { recordError = true; recordErrorMsg.append("Ticket, "); } else { Long ticket = Long.parseLong(ticketStr); currentWMInvoice.setTicket(ticket); } Integer txnDateCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TXN_DATE); Object txnDateObj = getCellValue(row.getCell(txnDateCol), true); if (txnDateObj == null) { recordError = true; recordErrorMsg.append("Date, "); } else if (txnDateObj instanceof Date) { currentWMInvoice.setTxnDate((Date) txnDateObj); } else { String txnDateStr = txnDateObj.toString(); Date txnDate = wmDateFormat.parse(txnDateStr); currentWMInvoice.setTxnDate(txnDate); } Integer timeInCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TIME_IN); Object timeInObj = getCellValue(row.getCell(timeInCol), true); if (timeInObj == null) { recordError = true; recordErrorMsg.append("Time In, "); } else { SimpleDateFormat timeInDateFormat = wmDateTimeFormat; if (!(timeInObj instanceof Date)) { timeInDateFormat = wmDateTimeStrFormat; } String timeInStr = timeInObj.toString(); timeInStr = StringUtils.replace(timeInStr, ".", StringUtils.EMPTY); String reqTimeInStr = convertDateFormat(timeInStr, timeInDateFormat, requiredTimeFormat); if (StringUtils.isEmpty(reqTimeInStr)) { recordError = true; recordErrorMsg.append("Time In, "); } else { currentWMInvoice.setTimeIn(reqTimeInStr); } } Integer timeOutCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TIME_OUT); Object timeOutObj = getCellValue(row.getCell(timeOutCol), true); if (timeOutObj == null) { recordError = true; recordErrorMsg.append("Time Out, "); } else { SimpleDateFormat timeOutDateFormat = wmDateTimeFormat; if (!(timeOutObj instanceof Date)) { timeOutDateFormat = wmDateTimeStrFormat; } String timeOutStr = timeOutObj.toString(); timeOutStr = StringUtils.replace(timeOutStr, ".", StringUtils.EMPTY); String reqTimeOutStr = convertDateFormat(timeOutStr, timeOutDateFormat, requiredTimeFormat); if (StringUtils.isEmpty(reqTimeOutStr)) { recordError = true; recordErrorMsg.append("Time Out, "); } else { currentWMInvoice.setTimeOut(reqTimeOutStr); } } Integer wmOriginCol = colMapping.get(TicketUtils.WM_INVOICE_COL_ORIGIN); String wmOriginStr = ((String) getCellValue(row.getCell(wmOriginCol))); if (StringUtils.isEmpty(wmOriginStr)) { recordError = true; recordErrorMsg.append("Origin, "); } else { currentWMInvoice.setWmOrigin(wmOriginStr); List<Location> originList = retrieveLocationDataByLongName(1, wmOriginStr); if (originList == null || originList.isEmpty()) { recordError = true; recordErrorMsg.append("Origin, "); } else { currentWMInvoice.setOrigin(originList.get(0)); } } Integer wmDestinationCol = colMapping.get(TicketUtils.WM_INVOICE_COL_DESTINATION); String wmDestinationStr = ((String) getCellValue(row.getCell(wmDestinationCol))); if (StringUtils.isEmpty(wmDestinationStr)) { recordError = true; recordErrorMsg.append("Destination, "); } else { currentWMInvoice.setWmDestination(wmDestinationStr); List<Location> destinationList = retrieveLocationDataByLongName(2, wmDestinationStr); if (destinationList == null || destinationList.isEmpty()) { recordError = true; recordErrorMsg.append("Destination, "); } else { currentWMInvoice.setDestination(destinationList.get(0)); } } Integer wmVehicleCol = colMapping.get(TicketUtils.WM_INVOICE_COL_VEHICLE); if (wmVehicleCol != null) { String wmVehicleStr = ((String) getCellValue(row.getCell(wmVehicleCol))); if (StringUtils.isNotEmpty(wmVehicleStr)) { currentWMInvoice.setWmVehicle(wmVehicleStr); } } Integer wmTrailerCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TRAILER); if (wmTrailerCol != null) { String wmTrailerStr = ((String) getCellValue(row.getCell(wmTrailerCol))); if (StringUtils.isNotEmpty(wmTrailerStr)) { currentWMInvoice.setWmTrailer(wmTrailerStr); } } Integer grossCol = colMapping.get(TicketUtils.WM_INVOICE_COL_GROSS); Object grossObj = getCellValue(row.getCell(grossCol), true); Double grossWeight = null; if (grossObj instanceof Double) { grossWeight = (Double) grossObj; } else { String grossObjStr = (String) grossObj; grossObjStr = StringUtils.replace(grossObjStr, ",", StringUtils.EMPTY); grossWeight = Double.valueOf(grossObjStr); } currentWMInvoice.setGross(grossWeight); Integer tareCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TARE); Object tareObj = getCellValue(row.getCell(tareCol), true); Double tareWeight = null; if (tareObj instanceof Double) { tareWeight = (Double) tareObj; } else { String tareObjStr = (String) tareObj; tareObjStr = StringUtils.replace(tareObjStr, ",", StringUtils.EMPTY); tareWeight = Double.valueOf(tareObjStr); } currentWMInvoice.setTare(tareWeight); Integer netCol = colMapping.get(TicketUtils.WM_INVOICE_COL_NET); Object netObj = getCellValue(row.getCell(netCol), true); Double netWeight = null; if (netObj instanceof Double) { netWeight = (Double) netObj; } else { String netObjStr = (String) netObj; netObjStr = StringUtils.replace(netObjStr, ",", StringUtils.EMPTY); netWeight = Double.valueOf(netObjStr); } currentWMInvoice.setNet(netWeight); Integer amountCol = colMapping.get(TicketUtils.WM_INVOICE_COL_AMOUNT); Object amountObj = getCellValue(row.getCell(amountCol), true); Double amount = null; if (amountObj instanceof Double) { amount = (Double) amountObj; } else { amount = Double.valueOf((String) amountObj); } currentWMInvoice.setAmount(amount); Integer fscCol = colMapping.get(TicketUtils.WM_INVOICE_COL_FSC); Object fscObj = getCellValue(row.getCell(fscCol), true); Double fsc = null; if (fscObj instanceof Double) { fsc = (Double) fscObj; } else { fsc = Double.valueOf((String) fscObj); } currentWMInvoice.setFsc(fsc); Integer totalAmountCol = colMapping.get(TicketUtils.WM_INVOICE_COL_TOTAL_AMOUNT); Object totalAmountObj = getCellValue(row.getCell(totalAmountCol), true); Double totalAmount = null; if (totalAmountObj instanceof Double) { totalAmount = (Double) totalAmountObj; } else { totalAmount = Double.valueOf((String) totalAmountObj); } currentWMInvoice.setTotalAmount(totalAmount); Integer wmStatusCol = colMapping.get(TicketUtils.WM_INVOICE_COL_STATUS); if (wmStatusCol != null) { String wmStatusStr = ((String) getCellValue(row.getCell(wmStatusCol))); if (StringUtils.isNotEmpty(wmStatusStr)) { currentWMInvoice.setWmStatus(wmStatusStr); } } Integer wmStatusCodeCol = colMapping.get(TicketUtils.WM_INVOICE_COL_STATUS_CODE); if (wmStatusCodeCol != null) { String wmStatusCodeStr = ((String) getCellValue(row.getCell(wmStatusCodeCol))); if (StringUtils.isNotEmpty(wmStatusCodeStr)) { currentWMInvoice.setWmStatusCode(wmStatusCodeStr); } } Integer wmTicketCol = colMapping.get(TicketUtils.WM_INVOICE_COL_WM_TICKET); if (wmTicketCol != null) { String wmTicketStr = ((String) getCellValue(row.getCell(wmTicketCol))); if (StringUtils.isNotEmpty(wmTicketStr)) { currentWMInvoice.setWmTicket(wmTicketStr); } } WMInvoice existingWMInvoice = checkDuplicateWMInvoice(currentWMInvoice); if (existingWMInvoice != null) { if (StringUtils.equals(currentWMInvoice.getWmStatusCode(), existingWMInvoice.getWmStatusCode())) { recordError = true; recordErrorMsg.append("Duplicate WM Invoice, "); } else { existingWMInvoice.setWmStatusCode(currentWMInvoice.getWmStatusCode()); existingWMInvoice.setWmStatus(currentWMInvoice.getWmStatus()); existingWMInvoice.setModifiedBy(createdBy); existingWMInvoice.setModifiedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(existingWMInvoice); successCount++; continue; } } if (recordError) { errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } currentWMInvoice.setStatus(1); currentWMInvoice.setCreatedBy(createdBy); currentWMInvoice.setCreatedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(currentWMInvoice); successCount++; } catch (Exception ex) { recordError = true; errorCount++; recordErrorMsg.append("Error while processing record: " + recordCount + ", "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); } } System.out.println("Done processing...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records loaded: " + successCount); } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing WM Invoice: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importAccidentNotReportedData(InputStream is, Long createdBy) throws Exception { SimpleDateFormat accidentDateFormat = new SimpleDateFormat("MM-dd-yyyy"); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; int successCount = 0; try {/* w w w . ja va 2s . c o m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Map<String, Integer> colMapping = WorkerCompUtils.getAccidentNotReportedColMapping(); Iterator<Row> rows = sheet.rowIterator(); int recordsToBeSkipped = 1; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount <= recordsToBeSkipped) { continue; } boolean recordError = false; StringBuffer recordErrorMsg = new StringBuffer(); Accident currentAccident = null; try { String endOfData = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", endOfData)) { break; } currentAccident = new Accident(); Integer driverNameCol = colMapping.get(WorkerCompUtils.ACCIDENT_NOT_REPORTED_COL_NAME); String driverName = ((String) getCellValue(row.getCell(driverNameCol))); driverName = StringUtils.trimToEmpty(driverName); Driver driver = null; if (StringUtils.contains(driverName, ",")) { driver = WorkerCompUtils.retrieveDriverByCommaSep(driverName, genericDAO); } else { driver = WorkerCompUtils.retrieveDriver(driverName, genericDAO, true); } if (driver == null) { recordError = true; recordErrorMsg.append("Employee Name, "); } else { currentAccident.setDriver(driver); } Integer incidentDateCol = colMapping .get(WorkerCompUtils.ACCIDENT_NOT_REPORTED_COL_INCIDENT_DATE); Object incidentDateObj = getCellValue(row.getCell(incidentDateCol), true); if (incidentDateObj == null) { recordError = true; recordErrorMsg.append("Incident Date, "); } else if (incidentDateObj instanceof Date) { currentAccident.setIncidentDate((Date) incidentDateObj); } else { String incidentDateStr = incidentDateObj.toString(); incidentDateStr = StringUtils.trimToEmpty(incidentDateStr); Date incidentDate = accidentDateFormat.parse(incidentDateStr); currentAccident.setIncidentDate(incidentDate); } Accident existingAccident = WorkerCompUtils.retrieveMatchingAccident(currentAccident, genericDAO); if (existingAccident == null) { recordError = true; recordErrorMsg.append("No matching existing Accident record found to update costs, "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } Integer totalCostCol = colMapping.get(WorkerCompUtils.ACCIDENT_NOT_REPORTED_COL_TOTAL_COST); Object totalCostObj = getCellValue(row.getCell(totalCostCol), true); Double totalCost = null; if (totalCostObj != null) { if (totalCostObj instanceof Double) { totalCost = (Double) totalCostObj; } else { String totalCostStr = (String) totalCostObj; if (StringUtils.isNotEmpty(totalCostStr)) { totalCost = Double.valueOf(totalCostStr); } } existingAccident.setTotalCost(totalCost); } if (recordError) { errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } existingAccident.setModifiedBy(createdBy); existingAccident.setModifiedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(existingAccident); successCount++; } catch (Exception ex) { log.warn("Error while processing Accident Not Reported record: " + recordCount + ". " + ex); recordError = true; errorCount++; recordErrorMsg.append("Error while processing record: " + recordCount + ", "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); } } System.out.println("Done processing accidents not reported...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records loaded: " + successCount); } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Accident Not Reported data: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importAccidentReportedData(InputStream is, Long createdBy) throws Exception { SimpleDateFormat accidentDateFormat = new SimpleDateFormat("MM-dd-yyyy"); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; int successCount = 0; try {//ww w . j a va 2s . c o m POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Map<String, Integer> colMapping = WorkerCompUtils.getAccidentReportedColMapping(); Iterator<Row> rows = sheet.rowIterator(); int recordsToBeSkipped = 1; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount <= recordsToBeSkipped) { continue; } boolean recordError = false; StringBuffer recordErrorMsg = new StringBuffer(); Accident currentAccident = null; try { String endOfData = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", endOfData)) { break; } currentAccident = new Accident(); Integer insuranceCompanyCol = colMapping .get(WorkerCompUtils.ACCIDENT_REPORTED_COL_INUSRANCE_COMPANY); String inuranceCompanyStr = ((String) getCellValue(row.getCell(insuranceCompanyCol))); inuranceCompanyStr = StringUtils.trimToEmpty(inuranceCompanyStr); InsuranceCompany insuranceCompany = WorkerCompUtils .retrieveInsuranceCompanyByName(inuranceCompanyStr, genericDAO); if (insuranceCompany == null) { recordError = true; recordErrorMsg.append("Inurance Company, "); } else { currentAccident.setInsuranceCompany(insuranceCompany); } Integer insuranceClaimNoCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_CLAIM); String claimNo = ((String) getCellValue(row.getCell(insuranceClaimNoCol))); claimNo = StringUtils.trimToEmpty(claimNo); if (StringUtils.isEmpty(claimNo)) { recordError = true; recordErrorMsg.append("Claim No, "); } else { currentAccident.setClaimNumber(claimNo); } Integer driverNameCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_EMPLOYEE); String driverName = ((String) getCellValue(row.getCell(driverNameCol))); driverName = StringUtils.trimToEmpty(driverName); Driver driver = WorkerCompUtils.retrieveDriver(driverName, genericDAO, true); if (driver == null) { recordError = true; recordErrorMsg.append("Employee Name, "); } else { currentAccident.setDriver(driver); } Integer incidentDateCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_INCIDENT_DATE); Object incidentDateObj = getCellValue(row.getCell(incidentDateCol), true); if (incidentDateObj == null) { recordError = true; recordErrorMsg.append("Incident Date, "); } else if (incidentDateObj instanceof Date) { currentAccident.setIncidentDate((Date) incidentDateObj); } else { String incidentDateStr = incidentDateObj.toString(); incidentDateStr = StringUtils.trimToEmpty(incidentDateStr); Date incidentDate = accidentDateFormat.parse(incidentDateStr); currentAccident.setIncidentDate(incidentDate); } Accident existingAccident = WorkerCompUtils.retrieveMatchingAccident(currentAccident, genericDAO); if (existingAccident == null) { recordError = true; recordErrorMsg.append("No matching existing Accident record found to update costs, "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } Integer statusCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_STATUS); String statusStr = ((String) getCellValue(row.getCell(statusCol))); statusStr = StringUtils.trimToEmpty(statusStr); StaticData status = WorkerCompUtils.retrieveAccidentStatus(statusStr, genericDAO); if (status == null) { recordError = true; recordErrorMsg.append("Status, "); } else { existingAccident.setAccidentStatus(Integer.valueOf(status.getDataValue())); } Integer deductibleCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_DEDUCTIBLE); Object deductibleObj = getCellValue(row.getCell(deductibleCol), true); Double deductible = null; if (deductibleObj != null) { if (deductibleObj instanceof Double) { deductible = (Double) deductibleObj; } else { String deductibleStr = (String) deductibleObj; if (StringUtils.isNotEmpty(deductibleStr)) { deductible = Double.valueOf(deductibleStr); } } existingAccident.setDeductible(deductible); } Integer expenseCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_EXPENSE); Object expenseObj = getCellValue(row.getCell(expenseCol), true); Double expense = null; if (expenseObj != null) { if (expenseObj instanceof Double) { expense = (Double) expenseObj; } else { String expenseStr = (String) expenseObj; if (StringUtils.isNotEmpty(expenseStr)) { expense = Double.valueOf(expenseStr); } } existingAccident.setExpense(expense); } Integer reserveCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_RESERVE); Object reserveObj = getCellValue(row.getCell(reserveCol), true); Double reserve = null; if (reserveObj != null) { if (reserveObj instanceof Double) { reserve = (Double) reserveObj; } else { String reserveStr = (String) reserveObj; if (StringUtils.isNotEmpty(reserveStr)) { reserve = Double.valueOf(reserveStr); } } existingAccident.setReserve(reserve); } Integer paidCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_PAID); Object paidObj = getCellValue(row.getCell(paidCol), true); Double paid = null; if (paidObj != null) { if (paidObj instanceof Double) { paid = (Double) paidObj; } else { String paidStr = (String) paidObj; if (StringUtils.isNotEmpty(paidStr)) { paid = Double.valueOf(paidStr); } } existingAccident.setPaid(paid); } Integer totalCostCol = colMapping.get(WorkerCompUtils.ACCIDENT_REPORTED_COL_TOTAL_COST); Object totalCostObj = getCellValue(row.getCell(totalCostCol), true); Double totalCost = null; if (totalCostObj != null) { if (totalCostObj instanceof Double) { totalCost = (Double) totalCostObj; } else { String totalCostStr = (String) totalCostObj; if (StringUtils.isNotEmpty(totalCostStr)) { totalCost = Double.valueOf(totalCostStr); } } existingAccident.setTotalCost(totalCost); } if (recordError) { errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } existingAccident.setModifiedBy(createdBy); existingAccident.setModifiedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(existingAccident); successCount++; } catch (Exception ex) { log.warn("Error while processing Accident Reported record: " + recordCount + ". " + ex); recordError = true; errorCount++; recordErrorMsg.append("Error while processing record: " + recordCount + ", "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); } } System.out.println("Done processing accidents reported...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records loaded: " + successCount); } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Accident Reported data: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importAccidentMainData(InputStream is, Long createdBy) throws Exception { SimpleDateFormat accidentDateFormat = new SimpleDateFormat("MM-dd-yyyy"); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; int successCount = 0; try {// ww w.j ava 2 s . c om POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Map<String, Integer> colMapping = WorkerCompUtils.getAccidentMainColMapping(); Iterator<Row> rows = sheet.rowIterator(); int recordsToBeSkipped = 1; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount <= recordsToBeSkipped) { continue; } boolean recordError = false; StringBuffer recordErrorMsg = new StringBuffer(); Accident currentAccident = null; try { String endOfData = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", endOfData)) { break; } currentAccident = new Accident(); Integer insuranceCompanyCol = colMapping .get(WorkerCompUtils.ACCIDENT_MAIN_COL_INUSRANCE_COMPANY); String inuranceCompanyStr = ((String) getCellValue(row.getCell(insuranceCompanyCol))); inuranceCompanyStr = StringUtils.trimToEmpty(inuranceCompanyStr); InsuranceCompany insuranceCompany = null; if (!StringUtils.equalsIgnoreCase(inuranceCompanyStr, "Not Reported")) { insuranceCompany = WorkerCompUtils.retrieveInsuranceCompanyByName(inuranceCompanyStr, genericDAO); if (insuranceCompany == null) { recordError = true; recordErrorMsg.append("Inurance Company, "); } else { currentAccident.setInsuranceCompany(insuranceCompany); } } Integer insuranceClaimNoCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_CLAIM_NO); String claimNo = ((String) getCellValue(row.getCell(insuranceClaimNoCol))); claimNo = StringUtils.trimToEmpty(claimNo); if (StringUtils.isEmpty(claimNo)) { claimNo = null; } currentAccident.setClaimNumber(claimNo); boolean missingDriver = false; Integer driverNameCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_DRIVER_NAME); String driverName = ((String) getCellValue(row.getCell(driverNameCol))); driverName = StringUtils.trimToEmpty(driverName); Driver driver = WorkerCompUtils.retrieveDriverByCommaSep(driverName, genericDAO); if (driver == null) { /*recordError = true; recordErrorMsg.append("Employee Name, ");*/ missingDriver = true; } else { currentAccident.setDriver(driver); } if (missingDriver) { Integer subcontractorNameCol = colMapping .get(WorkerCompUtils.ACCIDENT_MAIN_COL_SUBCONTRACTOR); String subcontractorName = ((String) getCellValue(row.getCell(subcontractorNameCol))); subcontractorName = StringUtils.trimToEmpty(subcontractorName); SubContractor subcontractor = WorkerCompUtils.retrieveSubcontractor(subcontractorName, genericDAO); if (subcontractor == null) { recordError = true; recordErrorMsg.append("Either Employee or Subcontractor is required "); } else { currentAccident.setSubcontractor(subcontractor); } } Integer incidentDateCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_INCIDENT_DATE); Object incidentDateObj = getCellValue(row.getCell(incidentDateCol), true); if (incidentDateObj == null) { recordError = true; recordErrorMsg.append("Incident Date, "); } else if (incidentDateObj instanceof Date) { currentAccident.setIncidentDate((Date) incidentDateObj); } else { String incidentDateStr = incidentDateObj.toString(); incidentDateStr = StringUtils.trimToEmpty(incidentDateStr); Date incidentDate = accidentDateFormat.parse(incidentDateStr); currentAccident.setIncidentDate(incidentDate); } String dayOfWeek = WorkerCompUtils.deriveDayOfWeek(currentAccident.getIncidentDate()); currentAccident.setIncidentDayOfWeek(dayOfWeek); Integer vehicleCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_UNIT); String unit = ((String) getCellValue(row.getCell(vehicleCol))); unit = StringUtils.trimToEmpty(unit); if (StringUtils.isNotEmpty(unit)) { Vehicle vehicle = WorkerCompUtils.retrieveVehicleForUnit(unit, currentAccident.getIncidentDate(), genericDAO); if (vehicle == null) { recordError = true; recordErrorMsg .append("Vehicle (either unit is invalid or not valid for incident date), "); } else { currentAccident.setVehicle(vehicle); } } Integer monthsOfServiceCol = colMapping .get(WorkerCompUtils.ACCIDENT_MAIN_COL_MONTHS_OF_SERVICE); String monthsOfServiceStr = ((String) getCellValue(row.getCell(monthsOfServiceCol), true)); monthsOfServiceStr = StringUtils.trimToEmpty(monthsOfServiceStr); if (StringUtils.isNotEmpty(monthsOfServiceStr)) { //if (!StringUtils.isNumeric(monthsOfServiceStr)) { monthsOfServiceStr = StringUtils.substringBefore(monthsOfServiceStr, "."); Integer monthsOfService = Integer.valueOf(monthsOfServiceStr); currentAccident.setDriverMonthsOfService(monthsOfService); //} } Integer hireDateCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_HIRE_DATE); Object hireDateObj = getCellValue(row.getCell(hireDateCol), true); if (hireDateObj == null) { recordError = true; recordErrorMsg.append("Hire Date, "); } else if (hireDateObj instanceof Date) { currentAccident.setDriverHiredDate((Date) hireDateObj); } else { String hireDateStr = hireDateObj.toString(); hireDateStr = StringUtils.trimToEmpty(hireDateStr); if (StringUtils.isNotEmpty(hireDateStr)) { Date hireDate = accidentDateFormat.parse(hireDateStr); currentAccident.setDriverHiredDate(hireDate); } } Integer loationCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_LOCATION); String locationStr = ((String) getCellValue(row.getCell(loationCol))); locationStr = StringUtils.trimToEmpty(locationStr); currentAccident.setLocation(locationStr); Integer companyCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_COMPANY); String companyStr = ((String) getCellValue(row.getCell(companyCol))); companyStr = StringUtils.trimToEmpty(companyStr); List<Location> locationList = WorkerCompUtils.retrieveCompanyTerminal(companyStr, genericDAO); if (locationList == null || locationList.isEmpty()) { recordError = true; recordErrorMsg.append("Company, "); } else { currentAccident.setDriverCompany(locationList.get(0)); currentAccident.setDriverTerminal(locationList.get(1)); } Integer stateCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_STATE); String stateStr = ((String) getCellValue(row.getCell(stateCol))); stateStr = StringUtils.trimToEmpty(stateStr); if (StringUtils.isNotEmpty(stateStr)) { State state = WorkerCompUtils.retrieveState(stateStr, genericDAO); if (state == null) { recordError = true; recordErrorMsg.append("State, "); } else { currentAccident.setState(state); } } Integer accidentCauseCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_CAUSE); String accidentCauseStr = ((String) getCellValue(row.getCell(accidentCauseCol))); accidentCauseStr = StringUtils.trimToEmpty(accidentCauseStr); if (StringUtils.isNotEmpty(accidentCauseStr)) { AccidentCause accidentCause = WorkerCompUtils.retrieveAccidentCause(accidentCauseStr, genericDAO); if (accidentCause == null) { recordError = true; recordErrorMsg.append("Accident Cause, "); } else { currentAccident.setAccidentCause(accidentCause); } } Integer accidentRoadConditionCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_ROAD); String accidentRoadConditionStr = ((String) getCellValue( row.getCell(accidentRoadConditionCol))); accidentRoadConditionStr = StringUtils.trimToEmpty(accidentRoadConditionStr); if (StringUtils.isNotEmpty(accidentRoadConditionStr)) { AccidentRoadCondition accidentRoadCondition = WorkerCompUtils .retrieveAccidentRoadCondition(accidentRoadConditionStr, genericDAO); if (accidentRoadCondition == null) { recordError = true; recordErrorMsg.append("Road Condition, "); } else { currentAccident.setRoadCondition(accidentRoadCondition); } } Integer accidentWeatherCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_WEATHER); String accidentWeatherStr = ((String) getCellValue(row.getCell(accidentWeatherCol))); accidentWeatherStr = StringUtils.trimToEmpty(accidentWeatherStr); if (StringUtils.isNotEmpty(accidentWeatherStr)) { AccidentWeather accidentWeather = WorkerCompUtils .retrieveAccidentWeather(accidentWeatherStr, genericDAO); if (accidentWeather == null) { recordError = true; recordErrorMsg.append("Weather, "); } else { currentAccident.setWeather(accidentWeather); } } Integer commentsCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_ACCIDENT_COMMENTS); String commentsStr = ((String) getCellValue(row.getCell(commentsCol))); commentsStr = StringUtils.trimToEmpty(commentsStr); currentAccident.setNotes(commentsStr); Integer vehicleDamageCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_VEHICLE_DAMAGE); String vehicleDamageStr = ((String) getCellValue(row.getCell(vehicleDamageCol))); vehicleDamageStr = StringUtils.trimToEmpty(vehicleDamageStr); if (StringUtils.equalsIgnoreCase(vehicleDamageStr, "Yes")) { currentAccident.setVehicleDamage("Yes"); } else if (StringUtils.equalsIgnoreCase(vehicleDamageStr, "No")) { currentAccident.setVehicleDamage("No"); } Integer towedCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_TOWED); String towedStr = ((String) getCellValue(row.getCell(towedCol))); towedStr = StringUtils.trimToEmpty(towedStr); if (StringUtils.equalsIgnoreCase(towedStr, "Yes")) { currentAccident.setTowed("Yes"); } else if (StringUtils.equalsIgnoreCase(towedStr, "No")) { currentAccident.setTowed("No"); } Integer noInjuredCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_NO_INJURED); String noInjuredStr = ((String) getCellValue(row.getCell(noInjuredCol))); noInjuredStr = StringUtils.trimToEmpty(noInjuredStr); currentAccident.setNoInjured(noInjuredStr); Integer citationCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_CITATION); String citationStr = ((String) getCellValue(row.getCell(citationCol))); citationStr = StringUtils.trimToEmpty(citationStr); if (StringUtils.equalsIgnoreCase(citationStr, "Yes")) { currentAccident.setCitation("Yes"); } else if (StringUtils.equalsIgnoreCase(citationStr, "No")) { currentAccident.setCitation("No"); } Integer recordableCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_RECORDABLE); String recordableStr = ((String) getCellValue(row.getCell(recordableCol))); recordableStr = StringUtils.trimToEmpty(recordableStr); if (StringUtils.equalsIgnoreCase(recordableStr, "Yes")) { currentAccident.setRecordable("Yes"); } else if (StringUtils.equalsIgnoreCase(recordableStr, "No")) { currentAccident.setRecordable("No"); } Integer hmRelaseCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_HM_RELEASE); String hmReleaseStr = ((String) getCellValue(row.getCell(hmRelaseCol))); hmReleaseStr = StringUtils.trimToEmpty(hmReleaseStr); if (StringUtils.equalsIgnoreCase(hmReleaseStr, "Yes")) { currentAccident.setHmRelease("Yes"); } else if (StringUtils.equalsIgnoreCase(hmReleaseStr, "No")) { currentAccident.setHmRelease("No"); } Integer claimRepCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_CLAIM_REP); String claimRepStr = ((String) getCellValue(row.getCell(claimRepCol))); claimRepStr = StringUtils.trimToEmpty(claimRepStr); if (StringUtils.isNotEmpty(claimRepStr) && insuranceCompany != null) { InsuranceCompanyRep claimRep = WorkerCompUtils.retrieveClaimRep(claimRepStr, insuranceCompany, genericDAO); /*if (claimRep == null) { recordError = true; recordErrorMsg.append("Claim Rep, "); } else {*/ currentAccident.setClaimRep(claimRep); //} } Integer statusCol = colMapping.get(WorkerCompUtils.ACCIDENT_MAIN_COL_STATUS); String statusStr = ((String) getCellValue(row.getCell(statusCol))); statusStr = StringUtils.trimToEmpty(statusStr); StaticData status = WorkerCompUtils.retrieveAccidentStatus(statusStr, genericDAO); if (status == null) { recordError = true; recordErrorMsg.append("Status, "); } else { currentAccident.setAccidentStatus(Integer.valueOf(status.getDataValue())); } if (WorkerCompUtils.checkDuplicateAccident(currentAccident, genericDAO)) { recordError = true; recordErrorMsg.append("Duplicate Accident, "); } if (recordError) { errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } currentAccident.setStatus(1); currentAccident.setCreatedBy(createdBy); currentAccident.setCreatedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(currentAccident); successCount++; } catch (Exception ex) { log.warn("Error while processing Accident Main record: " + recordCount + ". " + ex); recordError = true; errorCount++; recordErrorMsg.append("Error while processing record: " + recordCount + ", "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); } } System.out.println("Done processing accidents...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records loaded: " + successCount); } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Accident Main: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importInjuryMainData(InputStream is, Long createdBy) throws Exception { SimpleDateFormat injuryDateFormat = new SimpleDateFormat("MM-dd-yyyy"); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; int successCount = 0; try {/*from ww w.j av a2 s.c o m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Map<String, Integer> colMapping = WorkerCompUtils.getInjuryMainColMapping(); Iterator<Row> rows = sheet.rowIterator(); int recordsToBeSkipped = 1; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount <= recordsToBeSkipped) { continue; } boolean recordError = false; StringBuffer recordErrorMsg = new StringBuffer(); Injury currentInjury = null; try { String endOfData = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", endOfData)) { break; } currentInjury = new Injury(); Integer insuranceCompanyCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_INUSRANCE_COMPANY); String inuranceCompanyStr = ((String) getCellValue(row.getCell(insuranceCompanyCol))); InsuranceCompany insuranceCompany = null; if (!StringUtils.equalsIgnoreCase(inuranceCompanyStr, "Not Reported")) { insuranceCompany = WorkerCompUtils.retrieveInsuranceCompanyByName(inuranceCompanyStr, genericDAO); if (insuranceCompany == null) { recordError = true; recordErrorMsg.append("Inurance Company, "); } else { currentInjury.setInsuranceCompany(insuranceCompany); } } Integer insuranceClaimNoCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_CLAIM_NO); String claimNo = ((String) getCellValue(row.getCell(insuranceClaimNoCol))); if (StringUtils.isEmpty(claimNo)) { claimNo = null; } currentInjury.setClaimNumber(claimNo); Integer driverLastNameCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_LAST_NAME); String driverLastName = ((String) getCellValue(row.getCell(driverLastNameCol))); Integer driverFirstNameCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_FIRST_NAME); String driverFirstName = ((String) getCellValue(row.getCell(driverFirstNameCol))); Driver driver = WorkerCompUtils.retrieveDriver(driverFirstName, driverLastName, genericDAO); if (driver == null) { recordError = true; recordErrorMsg.append("Employee Name, "); } else { currentInjury.setDriver(driver); } Integer driverAgeCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_AGE); String driverAgeStr = ((String) getCellValue(row.getCell(driverAgeCol), true)); if (StringUtils.isNotEmpty(driverAgeStr)) { //if (!StringUtils.isNumeric(driverAgeStr)) { driverAgeStr = StringUtils.substringBefore(driverAgeStr, "."); Integer driverAge = Integer.valueOf(driverAgeStr); currentInjury.setDriverAge(driverAge); //} } Integer monthsOfServiceCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_MONTHS_OF_SERVICE); String monthsOfServiceStr = ((String) getCellValue(row.getCell(monthsOfServiceCol), true)); if (StringUtils.isNotEmpty(monthsOfServiceStr)) { //if (!StringUtils.isNumeric(monthsOfServiceStr)) { monthsOfServiceStr = StringUtils.substringBefore(monthsOfServiceStr, "."); Integer monthsOfService = Integer.valueOf(monthsOfServiceStr); currentInjury.setDriverMonthsOfService(monthsOfService); //} } Integer companyCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_COMPANY); String companyStr = ((String) getCellValue(row.getCell(companyCol))); List<Location> locationList = WorkerCompUtils.retrieveCompanyTerminal(companyStr, genericDAO); if (locationList == null || locationList.isEmpty()) { recordError = true; recordErrorMsg.append("Company, "); } else { currentInjury.setDriverCompany(locationList.get(0)); currentInjury.setDriverTerminal(locationList.get(1)); } Integer positionCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_POSITION); String positionStr = ((String) getCellValue(row.getCell(positionCol))); EmployeeCatagory employeeCategory = WorkerCompUtils.retrieveEmployeeCategory(positionStr, genericDAO); if (employeeCategory == null) { recordError = true; recordErrorMsg.append("Position, "); } else { currentInjury.setDriverCategory(employeeCategory); } Integer incidentDateCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_INCIDENT_DATE); Object incidentDateObj = getCellValue(row.getCell(incidentDateCol), true); if (incidentDateObj == null) { recordError = true; recordErrorMsg.append("Incident Date, "); } else if (incidentDateObj instanceof Date) { currentInjury.setIncidentDate((Date) incidentDateObj); } else { String incidentDateStr = incidentDateObj.toString(); Date incidentDate = injuryDateFormat.parse(incidentDateStr); currentInjury.setIncidentDate(incidentDate); } String dayOfWeek = WorkerCompUtils.deriveDayOfWeek(currentInjury.getIncidentDate()); currentInjury.setIncidentDayOfWeek(dayOfWeek); Integer timeOfDayCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_TIME_OF_DAY); Object timeOfDayObj = getCellValue(row.getCell(timeOfDayCol), true); Integer amPMCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_AM_PM); String amPMStr = ((String) getCellValue(row.getCell(amPMCol))); String incidentTime = WorkerCompUtils.deriveIncidentTime(timeOfDayObj, amPMStr); currentInjury.setIncidentTime(incidentTime); currentInjury.setIncidentTimeAMPM(amPMStr); Integer returnToWorkCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_RETURN_TO_WORK); Object returnToWorkObj = getCellValue(row.getCell(returnToWorkCol), true); String returnToWorkStr = StringUtils.EMPTY; if (returnToWorkObj != null) { if (returnToWorkObj instanceof Date) { returnToWorkStr = injuryDateFormat.format((Date) returnToWorkObj); } else { returnToWorkStr = (String) returnToWorkObj; } } currentInjury.setReturnToWork(returnToWorkStr); Integer incidentTypeCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_INCIDENT_TYPE); String incidentTypeStr = ((String) getCellValue(row.getCell(incidentTypeCol))); InjuryIncidentType injuryIncidentType = WorkerCompUtils.retrieveIncidentType(incidentTypeStr, genericDAO); if (injuryIncidentType == null) { recordError = true; recordErrorMsg.append("Incident Type, "); } else { currentInjury.setIncidentType(injuryIncidentType); } Integer injuryToCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_INJURY_TO); String injuryToStr = ((String) getCellValue(row.getCell(injuryToCol))); if (StringUtils.isNotEmpty(injuryToStr)) { InjuryToType injuryToType = WorkerCompUtils.retrieveInjuryToType(injuryToStr, genericDAO); if (injuryToType == null) { recordError = true; recordErrorMsg.append("Injury To, "); } else { currentInjury.setInjuryTo(injuryToType); } } Integer commentsCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_INJURY_COMMENTS); String commentsStr = ((String) getCellValue(row.getCell(commentsCol))); currentInjury.setNotes(commentsStr); Integer lostWorkDaysCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_LOST_WORK_DAYS); String lostWorkDaysStr = ((String) getCellValue(row.getCell(lostWorkDaysCol), true)); if (StringUtils.isNotEmpty(lostWorkDaysStr)) { //if (StringUtils.isNumeric(lostWorkDaysStr)) { lostWorkDaysStr = StringUtils.substringBefore(lostWorkDaysStr, "."); Integer lostWorkDays = Integer.valueOf(lostWorkDaysStr); currentInjury.setNoOfLostWorkDays(lostWorkDays); //} } Integer tarpRelatedInjuryCol = colMapping .get(WorkerCompUtils.INJURY_MAIN_COL_TARP_RELATED_INJURY); String tarpRelatedInjuryStr = ((String) getCellValue(row.getCell(tarpRelatedInjuryCol))); if (StringUtils.equals(tarpRelatedInjuryStr, "Yes") || StringUtils.equals(tarpRelatedInjuryStr, "No")) { currentInjury.setTarpRelatedInjury(tarpRelatedInjuryStr); } Integer firstReportOfInjuryCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_FIRST_INJURY); String firstReportOfInjuryStr = ((String) getCellValue(row.getCell(firstReportOfInjuryCol))); if (StringUtils.equals(firstReportOfInjuryStr, "Yes") || StringUtils.equals(firstReportOfInjuryStr, "No")) { currentInjury.setFirstReportOfInjury(firstReportOfInjuryStr); } Integer claimRepCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_CLAIM_REP); String claimRepStr = ((String) getCellValue(row.getCell(claimRepCol))); if (StringUtils.isNotEmpty(claimRepStr) && insuranceCompany != null) { InsuranceCompanyRep claimRep = WorkerCompUtils.retrieveClaimRep(claimRepStr, insuranceCompany, genericDAO); /*if (claimRep == null) { recordError = true; recordErrorMsg.append("Claim Rep, "); } else {*/ currentInjury.setClaimRep(claimRep); //} } Integer statusCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_STATUS); String statusStr = ((String) getCellValue(row.getCell(statusCol))); StaticData status = WorkerCompUtils.retrieveInjuryStatus(statusStr, genericDAO); if (status == null) { recordError = true; recordErrorMsg.append("Status, "); } else { currentInjury.setInjuryStatus(Integer.valueOf(status.getDataValue())); } Integer loationCol = colMapping.get(WorkerCompUtils.INJURY_MAIN_COL_LOCATION); String locationStr = ((String) getCellValue(row.getCell(loationCol))); currentInjury.setLocation(locationStr); /*if (StringUtils.isNotEmpty(locationStr)) { Location location = InjuryUtils.retrieveInjuryLocation(locationStr, genericDAO); if (location == null) { recordError = true; recordErrorMsg.append("Location, "); } else { currentInjury.setLocation(location); } }*/ if (WorkerCompUtils.checkDuplicateInjury(currentInjury, genericDAO)) { recordError = true; recordErrorMsg.append("Duplicate Injury, "); } if (recordError) { errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; continue; } currentInjury.setStatus(1); currentInjury.setCreatedBy(createdBy); currentInjury.setCreatedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(currentInjury); successCount++; } catch (Exception ex) { log.warn("Error while processing Injury Main record: " + recordCount + ". " + ex); recordError = true; errorCount++; recordErrorMsg.append("Error while processing record: " + recordCount + ", "); errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>"); } } System.out.println("Done processing injuries...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records loaded: " + successCount); } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Injury Main: " + ex); } return errorList; }