Example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

Introduction

In this page you can find the example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem.

Prototype

public POIFSFileSystem(InputStream stream) throws IOException 

Source Link

Document

Create a POIFSFileSystem from an InputStream.

Usage

From source file:com.orange.ocara.model.export.docx.AuditDocxExporter.java

License:Mozilla Public License

/**
 * Create OleObject using a sample.//from   ww  w.j  av  a2s. c om
 *
 * @param from File to embed
 * @param to   Destination file
 */
private void createOleObject(File from, File to) throws IOException, Ole10NativeException {
    File existingOleObject = new File(templateDirectory, "word/embeddings/oleObject.bin");

    OutputStream os = null;
    try {
        // When
        POIFSFileSystem fs = new POIFSFileSystem(FileUtils.openInputStream(existingOleObject));

        fs.getRoot().getEntry(Ole10Native.OLE10_NATIVE).delete();

        Ole10Native ole = new Ole10Native(from.getName(), from.getName(), from.getName(),
                IOUtils.toByteArray(FileUtils.openInputStream(from)));

        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        ole.writeOut(stream);

        fs.getRoot().createDocument(Ole10Native.OLE10_NATIVE, new ByteArrayInputStream(stream.toByteArray()));

        os = FileUtils.openOutputStream(to);
        fs.writeFilesystem(os);

    } finally {
        IOUtils.closeQuietly(os);
    }
}

---

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 www .j  a  v  a  2 s  . 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 {/*w w  w .  j a va 2  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();
            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 {/*w ww  .  j a v a  2  s . 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();
            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 {//  w w  w.  ja v  a  2  s . c  o m
        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 {/*from  w  ww . j  ava 2 s. 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 v a  2 s. co 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 {/*from  w  w  w.j  a va  2  s .  c o  m*/
        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 {//  w w w. ja v a2 s .  c  om
        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;
}

---

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

@Override
@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
public List<String> importInjuryReportedData(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 {/*  www  . j ava2 s. co m*/
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);

        Map<String, Integer> colMapping = WorkerCompUtils.getInjuryReportedColMapping();

        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_REPORTED_COL_INUSRANCE_COMPANY);
                String inuranceCompanyStr = ((String) getCellValue(row.getCell(insuranceCompanyCol)));
                InsuranceCompany insuranceCompany = null;
                insuranceCompany = WorkerCompUtils.retrieveInsuranceCompanyByName(inuranceCompanyStr,
                        genericDAO);
                if (insuranceCompany == null) {
                    recordError = true;
                    recordErrorMsg.append("Inurance Company, ");
                } else {
                    currentInjury.setInsuranceCompany(insuranceCompany);
                }

                Integer employeeCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_EMPLOYEE);
                String employeeName = ((String) getCellValue(row.getCell(employeeCol)));
                Driver driver = WorkerCompUtils.retrieveDriver(employeeName, genericDAO, true);
                if (driver == null) {
                    recordError = true;
                    recordErrorMsg.append("Employee, ");
                } else {
                    currentInjury.setDriver(driver);
                }

                Integer incidentDateCol = colMapping.get(WorkerCompUtils.INJURY_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) {
                    currentInjury.setIncidentDate((Date) incidentDateObj);
                } else {
                    String incidentDateStr = incidentDateObj.toString();
                    Date incidentDate = injuryDateFormat.parse(incidentDateStr);
                    currentInjury.setIncidentDate(incidentDate);
                }

                Injury existingInjury = WorkerCompUtils.retrieveMatchingInjury(currentInjury, genericDAO);
                if (existingInjury == null) {
                    recordError = true;
                    recordErrorMsg.append("No matching existing Injury record found to update costs, ");

                    errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>");
                    errorCount++;
                    continue;
                }

                Integer employedCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_EMPLOYED);
                String employedStr = ((String) getCellValue(row.getCell(employedCol)));
                if (StringUtils.equals(employedStr, "Yes") || StringUtils.equals(employedStr, "No")) {
                    existingInjury.setEmployed(employedStr);
                }

                Integer workingCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_WORKING);
                String workingStr = ((String) getCellValue(row.getCell(workingCol)));
                if (StringUtils.equals(workingStr, "Yes") || StringUtils.equals(workingStr, "No")) {
                    existingInjury.setWorking(workingStr);
                }

                Integer statusCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_STATUS);
                String statusStr = ((String) getCellValue(row.getCell(statusCol)));
                StaticData status = WorkerCompUtils.retrieveInjuryStatus(statusStr, genericDAO);
                if (status == null) {
                    recordError = true;
                    recordErrorMsg.append("Status, ");
                } else {
                    existingInjury.setInjuryStatus(Integer.valueOf(status.getDataValue()));
                }

                Integer medicalCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_MEDICAL);
                Object medicalObj = getCellValue(row.getCell(medicalCol), true);
                Double medical = null;
                if (medicalObj != null) {
                    if (medicalObj instanceof Double) {
                        medical = (Double) medicalObj;
                    } else {
                        String medicalStr = (String) medicalObj;
                        if (StringUtils.isNotEmpty(medicalStr)) {
                            medical = Double.valueOf(medicalStr);
                        }
                    }
                    existingInjury.setMedicalCost(medical);
                }

                Integer indemnityCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_INDEMNITY);
                Object indemnityObj = getCellValue(row.getCell(indemnityCol), true);
                Double indemnity = null;
                if (indemnityObj != null) {
                    if (indemnityObj instanceof Double) {
                        indemnity = (Double) indemnityObj;
                    } else {
                        String indemnityStr = (String) indemnityObj;
                        if (StringUtils.isNotEmpty(indemnityStr)) {
                            indemnity = Double.valueOf(indemnityStr);
                        }
                    }
                    existingInjury.setIndemnityCost(indemnity);
                }

                Integer expenseCol = colMapping.get(WorkerCompUtils.INJURY_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);
                        }
                    }
                    existingInjury.setExpense(expense);
                }

                Integer reserveCol = colMapping.get(WorkerCompUtils.INJURY_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);
                        }
                    }
                    existingInjury.setReserve(reserve);
                }

                Integer totalPaidCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_TOTAL_PAID);
                Object totalPaidObj = getCellValue(row.getCell(totalPaidCol), true);
                Double totalPaid = null;
                if (totalPaidObj != null) {
                    if (totalPaidObj instanceof Double) {
                        totalPaid = (Double) totalPaidObj;
                    } else {
                        String totalPaidStr = (String) totalPaidObj;
                        if (StringUtils.isNotEmpty(totalPaidStr)) {
                            totalPaid = Double.valueOf(totalPaidStr);
                        }
                    }
                    existingInjury.setTotalPaid(totalPaid);
                }

                Integer totalClaimCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_TOTAL_CLAIM);
                Object totalClaimObj = getCellValue(row.getCell(totalClaimCol), true);
                Double totalClaim = null;
                if (totalClaimObj != null) {
                    if (totalClaimObj instanceof Double) {
                        totalClaim = (Double) totalClaimObj;
                    } else {
                        String totalClaimStr = (String) totalClaimObj;
                        if (StringUtils.isNotEmpty(totalClaimStr)) {
                            totalClaim = Double.valueOf(totalClaimStr);
                        }
                    }
                    existingInjury.setTotalClaimed(totalClaim);
                }

                Integer attorneyCol = colMapping.get(WorkerCompUtils.INJURY_REPORTED_COL_ATTORNEY);
                String attorneyStr = ((String) getCellValue(row.getCell(attorneyCol)));
                if (StringUtils.equals(attorneyStr, "Yes") || StringUtils.equals(attorneyStr, "No")) {
                    existingInjury.setAttorney(attorneyStr);
                }

                if (recordError) {
                    errorList.add("Line " + recordCount + ": " + recordErrorMsg.toString() + "<br/>");
                    errorCount++;
                    continue;
                }

                existingInjury.setModifiedBy(createdBy);
                existingInjury.setModifiedAt(Calendar.getInstance().getTime());
                genericDAO.saveOrUpdate(existingInjury);

                successCount++;
            } catch (Exception ex) {
                log.warn("Error while processing Injury 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 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 Reported data: " + ex);
    }

    return errorList;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »