Example usage for org.apache.poi.hssf.usermodel HSSFSheet rowIterator

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. rowIterator

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet rowIterator.

Prototype

@Override
public Iterator<Row> rowIterator()

Source Link

Usage

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 {// ww  w .j ava2 s.  com
        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 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();
            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 om*/
        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  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.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 {// www.java  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 {/*from ww w .  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.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  w  w  w .j a  v  a2s . co  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;
}

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 a 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 = 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;
}

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

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

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

        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 driverLastNameCol = colMapping.get(WorkerCompUtils.INJURY_NOT_REPORTED_COL_LAST_NAME);
                String driverLastName = ((String) getCellValue(row.getCell(driverLastNameCol)));
                Integer driverFirstNameCol = colMapping.get(WorkerCompUtils.INJURY_NOT_REPORTED_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 incidentDateCol = colMapping.get(WorkerCompUtils.INJURY_NOT_REPORTED_COL_INJURY_DATE);
                Object incidentDateObj = getCellValue(row.getCell(incidentDateCol), true);
                if (incidentDateObj == null) {
                    recordError = true;
                    recordErrorMsg.append("Injury 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 returnToWorkDateCol = colMapping
                        .get(WorkerCompUtils.INJURY_NOT_REPORTED_COL_RETURN_TO_WORK_DATE);
                Object returnToWorkDateObj = getCellValue(row.getCell(returnToWorkDateCol), true);
                if (returnToWorkDateObj != null) {
                    if (returnToWorkDateObj instanceof Date) {
                        existingInjury.setReturnToWorkDate((Date) returnToWorkDateObj);
                    } else {
                        String returnToWorkDateStr = returnToWorkDateObj.toString();
                        if (StringUtils.isNotEmpty(returnToWorkDateStr)) {
                            Date returnToWorkDate = injuryDateFormat.parse(returnToWorkDateStr);
                            existingInjury.setReturnToWorkDate(returnToWorkDate);
                        }
                    }
                }

                Integer lostWorkDaysCol = colMapping.get(WorkerCompUtils.INJURY_NOT_REPORTED_COL_LOST_DAYS);
                String lostWorkDaysStr = ((String) getCellValue(row.getCell(lostWorkDaysCol), true));
                if (StringUtils.isNotEmpty(lostWorkDaysStr)) {
                    if (StringUtils.isNumeric(lostWorkDaysStr)) {
                        Integer lostWorkDays = Integer.valueOf(lostWorkDaysStr);
                        existingInjury.setNoOfLostWorkDays(lostWorkDays);
                    }
                }

                Integer medicalCol = colMapping.get(WorkerCompUtils.INJURY_NOT_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_NOT_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 totalPaidCol = colMapping.get(WorkerCompUtils.INJURY_NOT_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);
                }

                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 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 injuries 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 Injury 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> importWMTickets(InputStream is, String locationType, String destinationLocation,
        Long createdBy) throws Exception {
    SimpleDateFormat wmDateTimeFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy");
    // 5/30/2017 4:15:53 AM
    SimpleDateFormat wmDateTimeStrFormat = new SimpleDateFormat("M/dd/yyyy h:mm:ss a");
    // 2017-02-03 00:00:00
    SimpleDateFormat requiredDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat requiredTimeFormat = new SimpleDateFormat("HH:mm");

    List<String> errorList = new ArrayList<String>();

    int recordCount = 0;
    int errorCount = 0;
    int successCount = 0;
    try {/*  ww  w  .j a  v a  2  s  .  c o m*/
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);

        Location origin = null;
        Location destination = null;
        Location locationPtr = null;
        if (StringUtils.equals(WMTicket.ORIGIN_TICKET_TYPE, locationType)) {
            origin = retrieveOriginForWM(sheet);
            locationPtr = origin;
            if (origin == null) {
                errorList.add("Origin could not be determined");
            }
        } else {
            //destination = retrieveDestinationForWM(sheet);
            destination = retrieveDestinationForWM(destinationLocation);
            locationPtr = destination;
            if (destination == null) {
                errorList.add("Destination could not be determined");
            }
        }

        if (!errorList.isEmpty()) {
            return errorList;
        }

        Map<String, Integer> colMapping = TicketUtils.getWMTicketColMapping(locationPtr.getId());
        if (colMapping.size() <= 0) {
            errorList.add("Location not supported");
            return errorList;
        }

        int recordsToBeSkipped = TicketUtils.getWMTicketRecordsToBeSkipped(locationPtr.getId());

        Iterator 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();
            WMTicket currentWMTicket = null;
            Ticket ticketToBeSaved = null;
            try {
                String endOfData = ((String) getCellValue(row.getCell(0)));
                if (StringUtils.equals("END_OF_DATA", endOfData)) {
                    break;
                }

                currentWMTicket = new WMTicket();
                if (origin != null) {
                    currentWMTicket.setTicketType(WMTicket.ORIGIN_TICKET_TYPE);
                    currentWMTicket.setOrigin(origin);
                } else {
                    currentWMTicket.setTicketType(WMTicket.DESTINATION_TICKET_TYPE);
                    currentWMTicket.setDestination(destination);
                }

                Integer ticketCol = colMapping.get(TicketUtils.WM_COL_TICKET);
                String ticketStr = ((String) getCellValue(row.getCell(ticketCol)));
                if (StringUtils.isEmpty(ticketStr)) {
                    recordError = true;
                    recordErrorMsg.append("Ticket, ");
                } else {
                    Long ticket = Long.parseLong(ticketStr);
                    currentWMTicket.setTicket(ticket);
                    if (StringUtils.equals(WMTicket.ORIGIN_TICKET_TYPE, currentWMTicket.getTicketType())) {
                        currentWMTicket.setOriginTicket(ticket);
                    } else {
                        currentWMTicket.setDestinationTicket(ticket);
                    }
                }

                Integer haulingTicketCol = colMapping.get(TicketUtils.WM_COL_HAULING_TICKET);
                if (haulingTicketCol != null) {
                    String haulingTicketStr = ((String) getCellValue(row.getCell(haulingTicketCol)));
                    if (StringUtils.isNotEmpty(haulingTicketStr) && StringUtils.isNumeric(haulingTicketStr)) {
                        currentWMTicket.setHaulingTicket(Long.parseLong(haulingTicketStr));
                    }
                }

                Integer txnDateCol = colMapping.get(TicketUtils.WM_COL_TXN_DATE);
                Object txnDateObj = getCellValue(row.getCell(txnDateCol), true);
                if (txnDateObj == null) {
                    recordError = true;
                    recordErrorMsg.append("Date, ");
                } else if (txnDateObj instanceof Date) {
                    currentWMTicket.setTxnDate((Date) txnDateObj);
                } else {
                    String txnDateStr = txnDateObj.toString();
                    Date txnDate = wmDateTimeStrFormat.parse(txnDateStr);
                    currentWMTicket.setTxnDate(txnDate);
                }

                Integer timeInCol = colMapping.get(TicketUtils.WM_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();
                    String reqTimeInStr = convertDateFormat(timeInStr, timeInDateFormat, requiredTimeFormat);
                    if (StringUtils.isEmpty(reqTimeInStr)) {
                        recordError = true;
                        recordErrorMsg.append("Time In, ");
                    } else {
                        currentWMTicket.setTimeIn(reqTimeInStr);
                    }
                }

                Integer timeOutCol = colMapping.get(TicketUtils.WM_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();
                    String reqTimeOutStr = convertDateFormat(timeOutStr, timeOutDateFormat, requiredTimeFormat);
                    if (StringUtils.isEmpty(reqTimeOutStr)) {
                        recordError = true;
                        recordErrorMsg.append("Time Out, ");
                    } else {
                        currentWMTicket.setTimeOut(reqTimeOutStr);

                        Date loadUnloadDate = convertDate(timeOutStr, timeOutDateFormat, requiredDateFormat);
                        if (StringUtils.equals(WMTicket.ORIGIN_TICKET_TYPE, currentWMTicket.getTicketType())) {
                            currentWMTicket.setLoadDate(loadUnloadDate);
                        } else {
                            currentWMTicket.setUnloadDate(loadUnloadDate);
                            Date batchDate = TicketUtils.calculateBatchDate(loadUnloadDate);
                            currentWMTicket.setBillBatch(batchDate);
                        }
                    }
                }

                Integer wmCompanyCol = colMapping.get(TicketUtils.WM_COL_COMPANY);
                if (wmCompanyCol != null) {
                    String wmCompanyStr = ((String) getCellValue(row.getCell(wmCompanyCol)));
                    if (StringUtils.isNotEmpty(wmCompanyStr)) {
                        currentWMTicket.setWmCompany(wmCompanyStr);
                    }
                }

                if (checkCompanyToBeSkipped(currentWMTicket)) {
                    continue;
                }

                Integer wmHaulingCompanyCol = colMapping.get(TicketUtils.WM_COL_HAULING_COMPANY);
                if (wmHaulingCompanyCol != null) {
                    String wmHaulingCompanyStr = ((String) getCellValue(row.getCell(wmHaulingCompanyCol)));
                    if (StringUtils.isNotEmpty(wmHaulingCompanyStr)) {
                        currentWMTicket.setWmHaulingCompany(wmHaulingCompanyStr);

                        if (StringUtils.equals(WMTicket.DESTINATION_TICKET_TYPE,
                                currentWMTicket.getTicketType())) {
                            /*List<Location> derivedOriginList = retrieveLocationDataByQualifier(1, "haulingName", 
                                  wmHaulingCompanyStr);
                            if (derivedOriginList != null && !derivedOriginList.isEmpty()) {
                               currentWMTicket.setOrigin(derivedOriginList.get(0));
                            }*/

                            List<WMLocation> wmLocationList = retrieveWMLocationByName(wmHaulingCompanyStr, 1);
                            if (wmLocationList != null && !wmLocationList.isEmpty()) {
                                currentWMTicket.setOrigin(wmLocationList.get(0).getLocation());
                            }
                        }
                    }
                }
                Integer wmDestinationCol = colMapping.get(TicketUtils.WM_COL_DESTINATION);
                if (wmDestinationCol != null) {
                    String wmDestinationStr = ((String) getCellValue(row.getCell(wmDestinationCol)));
                    if (StringUtils.isNotEmpty(wmDestinationStr)) {
                        currentWMTicket.setWmDestination(wmDestinationStr);
                    }
                }
                Integer wmVehicleCol = colMapping.get(TicketUtils.WM_COL_VEHICLE);
                if (wmVehicleCol != null) {
                    String wmVehicleStr = ((String) getCellValue(row.getCell(wmVehicleCol)));
                    if (StringUtils.isNotEmpty(wmVehicleStr)) {
                        currentWMTicket.setWmVehicle(wmVehicleStr);
                    }
                }
                Integer wmTrailerCol = colMapping.get(TicketUtils.WM_COL_TRAILER);
                if (wmTrailerCol != null) {
                    String wmTrailerStr = ((String) getCellValue(row.getCell(wmTrailerCol)));
                    if (StringUtils.isNotEmpty(wmTrailerStr)) {
                        currentWMTicket.setWmTrailer(wmTrailerStr);
                    }
                }

                Integer grossCol = colMapping.get(TicketUtils.WM_COL_GROSS);
                Object grossObj = getCellValue(row.getCell(grossCol), true);
                Double grossWeight = null;
                if (grossObj instanceof Double) {
                    grossWeight = (Double) grossObj;
                } else {
                    grossWeight = Double.valueOf((String) grossObj);
                }
                currentWMTicket.setGross(grossWeight);

                Integer tareCol = colMapping.get(TicketUtils.WM_COL_TARE);
                Object tareObj = getCellValue(row.getCell(tareCol), true);
                Double tareWeight = null;
                if (tareObj instanceof Double) {
                    tareWeight = (Double) tareObj;
                } else {
                    tareWeight = Double.valueOf((String) tareObj);
                }
                currentWMTicket.setTare(tareWeight);

                Integer netCol = colMapping.get(TicketUtils.WM_COL_NET);
                if (netCol != null) {
                    Object netObj = getCellValue(row.getCell(netCol), true);
                    Double netWeight = null;
                    if (netObj instanceof Double) {
                        netWeight = (Double) netObj;
                    } else {
                        netWeight = Double.valueOf((String) netObj);
                    }
                    currentWMTicket.setNet(netWeight);
                }
                Integer tonsCol = colMapping.get(TicketUtils.WM_COL_TONS);
                if (tonsCol != null) {
                    Object tonsObj = getCellValue(row.getCell(tonsCol), true);
                    Double tonWeight = null;
                    if (tonsObj instanceof Double) {
                        tonWeight = (Double) tonsObj;
                    } else {
                        tonWeight = Double.valueOf((String) tonsObj);
                    }
                    currentWMTicket.setTons(tonWeight);
                }
                Integer rateCol = colMapping.get(TicketUtils.WM_COL_RATE);
                if (rateCol != null) {
                    Object rateObj = getCellValue(row.getCell(rateCol), true);
                    Double rate = null;
                    if (rateObj instanceof Double) {
                        rate = (Double) rateObj;
                    } else {
                        rate = Double.valueOf((String) rateObj);
                    }
                    currentWMTicket.setRate(rate);
                }
                Integer amountCol = colMapping.get(TicketUtils.WM_COL_AMOUNT);
                if (amountCol != null) {
                    Object amountObj = getCellValue(row.getCell(amountCol), true);
                    Double amount = null;
                    if (amountObj instanceof Double) {
                        amount = (Double) amountObj;
                    } else {
                        amount = Double.valueOf((String) amountObj);
                    }
                    currentWMTicket.setAmount(amount);
                }

                mapBasedOnTicketType(currentWMTicket);
                TicketUtils.calculateNetAndTons(currentWMTicket);

                if (checkDuplicateWMTicket(currentWMTicket)) {
                    /*recordError = true;
                    recordErrorMsg.append("Duplicate WM Ticket, ");*/
                    continue;
                }

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

                currentWMTicket.setStatus(1);
                currentWMTicket.setCreatedBy(createdBy);
                currentWMTicket.setCreatedAt(Calendar.getInstance().getTime());

                Ticket existigTicket = retrieveMatchingTicket(currentWMTicket);
                if (existigTicket != null) {
                    currentWMTicket.setProcessingStatus(WMTicket.PROCESSING_STATUS_TICKET_ALREADY_EXISTS);
                    genericDAO.saveOrUpdate(currentWMTicket);

                    /*recordError = true;
                    errorList.add("Line " + recordCount + ": " + "Ticket already exists" + "<br/>");
                    errorCount++;*/

                    continue;
                }

                StringBuffer errorMsgBuff = new StringBuffer();
                WMTicket destinationTicketCopy = checkAndSetUpAsDestinationWMTicket(currentWMTicket,
                        errorMsgBuff);
                if (errorMsgBuff.length() != 0) {
                    recordError = true;
                    errorList.add("Line " + recordCount + ": " + errorMsgBuff.toString() + "<br/>");
                    errorCount++;
                    continue;
                }

                TripSheet tripSheet = retrieveMatchingTripsheet(currentWMTicket);
                if (tripSheet == null) {
                    currentWMTicket.setProcessingStatus(WMTicket.PROCESSING_STATUS_NO_TRIPSHEET);
                    genericDAO.saveOrUpdate(currentWMTicket);

                    if (destinationTicketCopy != null) {
                        destinationTicketCopy.setProcessingStatus(WMTicket.PROCESSING_STATUS_NO_TRIPSHEET);
                        genericDAO.saveOrUpdate(destinationTicketCopy);
                    }

                    /*recordError = true;
                    errorList.add("Line " + recordCount + ": " + "Did not find matching Trip sheet.  WM Ticket is saved for further processing." + "<br/>");
                    errorCount++;*/

                    continue;
                }

                TicketUtils.map(currentWMTicket, tripSheet);
                if (destinationTicketCopy != null) {
                    TicketUtils.map(destinationTicketCopy, tripSheet);
                    destinationTicketCopy.setProcessingStatus(WMTicket.PROCESSING_STATUS_PROCESSING);
                    genericDAO.saveOrUpdate(destinationTicketCopy);
                }

                // Driver subcontractor change 2 - 21st Jul 2017
                if (!TicketUtils.canCreateTicket(tripSheet)) {
                    currentWMTicket.setProcessingStatus(WMTicket.PROCESSING_STATUS_PROCESSING);
                    genericDAO.saveOrUpdate(currentWMTicket);
                    continue;
                }

                WMTicket wmOriginTicket = null;
                WMTicket wmDestinationTicket = null;
                WMTicket correspondingTicket = null;
                if (StringUtils.equals(WMTicket.ORIGIN_TICKET_TYPE, currentWMTicket.getTicketType())) {
                    wmDestinationTicket = TicketUtils.retrieveWMTicket(tripSheet.getDestinationTicket(),
                            tripSheet.getDestination().getId(), false, WMTicket.PROCESSING_STATUS_PROCESSING,
                            genericDAO);
                    if (wmDestinationTicket != null) {
                        currentWMTicket.setLandfillGross(wmDestinationTicket.getLandfillGross());
                        currentWMTicket.setLandfillTare(wmDestinationTicket.getLandfillTare());
                        currentWMTicket.setLandfillTimeIn(wmDestinationTicket.getLandfillTimeIn());
                        currentWMTicket.setLandfillTimeOut(wmDestinationTicket.getLandfillTimeOut());

                        ticketToBeSaved = new Ticket();
                        correspondingTicket = wmDestinationTicket;
                    }
                } else {
                    wmOriginTicket = TicketUtils.retrieveWMTicket(tripSheet.getOriginTicket(),
                            tripSheet.getOrigin().getId(), true, WMTicket.PROCESSING_STATUS_PROCESSING,
                            genericDAO);
                    if (wmOriginTicket != null) {
                        currentWMTicket.setTransferGross(wmOriginTicket.getTransferGross());
                        currentWMTicket.setTransferTare(wmOriginTicket.getTransferTare());
                        currentWMTicket.setTransferTimeIn(wmOriginTicket.getTransferTimeIn());
                        currentWMTicket.setTransferTimeOut(wmOriginTicket.getTransferTimeOut());

                        ticketToBeSaved = new Ticket();
                        correspondingTicket = wmOriginTicket;
                    }
                }

                if (ticketToBeSaved == null) {
                    currentWMTicket.setProcessingStatus(WMTicket.PROCESSING_STATUS_PROCESSING);
                    genericDAO.saveOrUpdate(currentWMTicket);
                    continue;
                }

                map(ticketToBeSaved, currentWMTicket);
                TicketUtils.calculateNetAndTons(ticketToBeSaved);
                TicketUtils.setAutomaticTicketData(ticketToBeSaved);
                TicketUtils.save(ticketToBeSaved, "complete", recordErrorMsg, genericDAO);

                if (recordErrorMsg.length() != 0) {
                    errorList.add("Line " + recordCount + ": Error while saving Ticket: "
                            + recordErrorMsg.toString() + "<br/>");
                    errorCount++;
                    continue;
                }

                successCount++;
                currentWMTicket.setProcessingStatus(WMTicket.PROCESSING_STATUS_DONE);
                genericDAO.saveOrUpdate(currentWMTicket);

                if (correspondingTicket != null) {
                    correspondingTicket.setProcessingStatus(WMTicket.PROCESSING_STATUS_DONE);
                    correspondingTicket.setModifiedBy(createdBy);
                    correspondingTicket.setModifiedAt(Calendar.getInstance().getTime());
                    genericDAO.saveOrUpdate(correspondingTicket);
                }
            } 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 Tickets: " + ex);
    }

    return errorList;
}