List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
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 {/*from 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 {/*www .j ava2s. com*/ 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 {/* w w w . j a va2s. 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; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importEmployeeMainSheet(InputStream is, Long createdBy) throws Exception { List<Driver> driverListToBeSaved = new ArrayList<Driver>(); List<String> errorList = new ArrayList<String>(); SimpleDateFormat dobFormat = new SimpleDateFormat("M/d/yyyy"); int recordCount = 0; int dataSetIndex = 0; int errorCount = 0; String employeeName = StringUtils.EMPTY; boolean dataSetError = false; boolean fatalDataSetError = false; StringBuffer dataSetErrorMsg = new StringBuffer(); List<Driver> dataSetDriverList = null; try {/*from w w w.ja v a 2 s. c o m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); /*if (recordCount < 10) { continue; }*/ try { String endOfData = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", endOfData)) { if (dataSetError) { String msgPreffix = fatalDataSetError ? "Record NOT loaded->" : "Record LOADED, but has errors->"; errorList.add(msgPreffix + "Data set: " + (dataSetIndex) + "," + " For employee: " + employeeName + "->Errors: " + dataSetErrorMsg.toString() + "<br/>"); errorCount++; } if (!fatalDataSetError && dataSetDriverList != null && !dataSetDriverList.isEmpty()) { driverListToBeSaved.addAll(dataSetDriverList); } break; } String codeHeader = ((String) getCellValue(row.getCell(8))); if (StringUtils.equals("Code:", codeHeader)) { dataSetIndex++; if (dataSetError) { String msgPreffix = fatalDataSetError ? "Record NOT loaded->" : "Record LOADED, but has errors->"; errorList.add(msgPreffix + "Data set: " + (dataSetIndex - 1) + "," + " For employee: " + employeeName + "->Errors: " + dataSetErrorMsg.toString() + "<br/>"); errorCount++; } if (!fatalDataSetError && dataSetDriverList != null && !dataSetDriverList.isEmpty()) { driverListToBeSaved.addAll(dataSetDriverList); } dataSetDriverList = null; dataSetError = false; fatalDataSetError = false; dataSetErrorMsg = new StringBuffer(); employeeName = ((String) getCellValue(row.getCell(1))); employeeName = StringUtils.trimToEmpty(employeeName); dataSetDriverList = retrieveDriver(employeeName); if (dataSetDriverList == null || dataSetDriverList.isEmpty()) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("Employee Name, "); continue; } String ssn = ((String) getCellValue(row.getCell(9))); ssn = StringUtils.trimToEmpty(ssn); if (StringUtils.isEmpty(ssn)) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("SSN, "); continue; } if (!fatalDataSetError && dataSetDriverList != null && !dataSetDriverList.isEmpty()) { for (Driver driver : dataSetDriverList) { driver.setSsn(ssn); } } } String licenseHeader = ((String) getCellValue(row.getCell(1))); if (StringUtils.equals("License Number:", licenseHeader)) { String driverLicense = ((String) getCellValue(row.getCell(5))); driverLicense = StringUtils.trimToEmpty(driverLicense); if (StringUtils.isEmpty(driverLicense)) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("Driver License, "); continue; } String driverLicenseStateStr = ((String) getCellValue(row.getCell(8))); driverLicenseStateStr = StringUtils.trimToEmpty(driverLicenseStateStr); State driverLicenseState = retrieveStateByCode(driverLicenseStateStr); if (driverLicenseState == null) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("Driver License State, "); continue; } if (!fatalDataSetError && dataSetDriverList != null && !dataSetDriverList.isEmpty()) { for (Driver driver : dataSetDriverList) { driver.setDriverLicense(driverLicense); driver.setDriverLicenseState(driverLicenseState); } } } String dobHeader = ((String) getCellValue(row.getCell(1))); if (StringUtils.equals("Date of Birth:", dobHeader)) { String dobStr = ((String) getCellValue(row.getCell(5))); dobStr = StringUtils.trimToEmpty(dobStr); if (StringUtils.isEmpty(dobStr)) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("DOB, "); continue; } Date dob = null; try { dob = dobFormat.parse(dobStr); } catch (ParseException pe) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("DOB, "); continue; } if (!fatalDataSetError && dataSetDriverList != null && !dataSetDriverList.isEmpty()) { for (Driver driver : dataSetDriverList) { driver.setDob(dob); } } } /*if (checkDuplicate()) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("Duplicate Record, "); }*/ } catch (Exception ex) { dataSetError = true; fatalDataSetError = true; dataSetErrorMsg.append("Error while processing record, "); } } System.out.println("Done processing...Total record count: " + recordCount + ". Data set count: " + dataSetIndex + ". Error count: " + errorCount + ". Number of records being loaded: " + driverListToBeSaved.size()); for (Driver driverToBeSaved : driverListToBeSaved) { System.out.println("Now loading employee: " + driverToBeSaved.getFullName()); driverToBeSaved.setModifiedBy(createdBy); driverToBeSaved.setModifiedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(driverToBeSaved); } } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Employee data: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importMileageLogMainSheet(InputStream is, Date period, Double resetMiles, Long createdBy) throws Exception { List<String> errorList = new ArrayList<String>(); if (!checkOldGPSMileageLoaded(period)) { String errorMsg = "Please upload old mileage first"; errorList.add(errorMsg);// www . j a va 2s . c om return errorList; } List<MileageLog> mileageLogList = new ArrayList<MileageLog>(); int recordCount = 0; int errorCount = 0; int recordsToBeSkipped = 6; DecimalFormat milesFormat = new DecimalFormat("#.0"); try { POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(1); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); /*if (recordCount == 1) { continue; }*/ if (recordCount <= recordsToBeSkipped) { continue; } boolean recordError = false; boolean fatalRecordError = false; StringBuffer recordErrorMsg = new StringBuffer(); MileageLog mileageLog = null; try { String unit = ((String) getCellValue(row.getCell(0), true)); if (StringUtils.equals("END_OF_DATA", unit)) { break; } if (!StringUtils.isNumeric(unit)) { continue; } String stateStr = ((String) getCellValue(row.getCell(1), true)); /*if (StringUtils.equals("Total", stateStr)) { continue; }*/ mileageLog = new MileageLog(); Date firstInState = (Date) getCellValue(row.getCell(3), true); if (firstInState == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("First in State,"); } else { mileageLog.setFirstInState(firstInState); } Date lastInState = (Date) getCellValue(row.getCell(4), true); if (lastInState == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Last in State,"); } else { mileageLog.setLastInState(lastInState); } Vehicle vehicle = retrieveVehicle(unit, lastInState); if (vehicle == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Unit,"); } else { mileageLog.setUnitNum(unit); mileageLog.setUnit(vehicle); mileageLog.setVin(vehicle.getVinNumber()); mileageLog.setCompany(vehicle.getOwner()); } State state = retrieveStateByCode(stateStr); if (state == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("State,"); } else { mileageLog.setState(state); } String miles = ((String) getCellValue(row.getCell(7), true)); Double milesDbl = processMiles(miles, resetMiles); if (milesDbl == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Miles,"); } else { String formattedMilesStr = milesFormat.format(milesDbl.doubleValue()); Double formattedMiles = new Double(formattedMilesStr); mileageLog.setMiles(formattedMiles); } /*String vin = ((String) getCellValue(row.getCell(7))); if (!validateVin(vin)) { recordError = true; fatalRecordError = true; recordErrorMsg.append("VIN,"); } else { mileageLog.setVin(vin); } String groups = ((String) getCellValue(row.getCell(6))); groups = StringUtils.isEmpty(groups) ? StringUtils.EMPTY : groups; mileageLog.setGroups(groups);*/ mileageLog.setPeriod(period); VehiclePermit vehiclePermit = retrieveVehiclePermit(mileageLog); if (vehiclePermit != null && StringUtils.isNotEmpty(vehiclePermit.getPermitNumber())) { mileageLog.setVehiclePermit(vehiclePermit); mileageLog.setVehiclePermitNumber(vehiclePermit.getPermitNumber()); } else { mileageLog.setVehiclePermit(null); mileageLog.setVehiclePermitNumber(StringUtils.EMPTY); if (mileageLog.getState() != null && StringUtils.equals("NY", mileageLog.getState().getCode())) { recordError = true; recordErrorMsg.append("Could not determine vehicle permit,"); } } mileageLog.setGps("Y"); mileageLog.setSource(MileageLog.SOURCE_NEW_GPS); MileageLog existingMileageLog = checkDuplicate(mileageLog); if (existingMileageLog != null) { /*if (!StringUtils.equals(MileageLog.SOURCE_OLD_GPS, existingMileageLog.getSource())) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Duplicate record,"); } else {*/ Double consolidatedMiles = existingMileageLog.getMiles() + mileageLog.getMiles(); existingMileageLog.setMiles(consolidatedMiles); existingMileageLog.setLastInState(mileageLog.getLastInState()); if (StringUtils.equals(MileageLog.SOURCE_OLD_GPS, existingMileageLog.getSource()) && StringUtils.equals(MileageLog.SOURCE_NEW_GPS, mileageLog.getSource())) { existingMileageLog.setSource(MileageLog.SOURCE_OLD_NEW_GPS); } existingMileageLog.setModifiedAt(Calendar.getInstance().getTime()); existingMileageLog.setModifiedBy(createdBy); mileageLog = existingMileageLog; } } 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 + 1) + ": " + recordErrorMsg.toString() + "<br/>"); errorCount++; } if (!fatalRecordError) { mileageLogList.add(mileageLog); } } System.out.println("Done processing...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records being loaded: " + mileageLogList.size()); if (!mileageLogList.isEmpty()) { for (MileageLog aMileageLog : mileageLogList) { if (aMileageLog.getCreatedBy() == null) { aMileageLog.setCreatedBy(createdBy); aMileageLog.setCreatedAt(Calendar.getInstance().getTime()); } genericDAO.saveOrUpdate(aMileageLog); } uploadNoGPSMileageLogData(period, createdBy); } } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Mileage log: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importOldGPSMileageLogMainSheet(InputStream is, Date period, Double resetMiles, Long createdBy) throws Exception { List<MileageLog> mileageLogList = new ArrayList<MileageLog>(); List<String> errorList = new ArrayList<String>(); int recordCount = 0; int errorCount = 0; try {/*from w w w .j av a 2 s . c om*/ 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(); MileageLog mileageLog = null; try { String unit = ((String) getCellValue(row.getCell(0))); if (StringUtils.equals("END_OF_DATA", unit)) { break; } String stateStr = ((String) getCellValue(row.getCell(2))); if (StringUtils.equals("Total", stateStr)) { continue; } mileageLog = new MileageLog(); String firstInStateStr = ((String) getCellValue(row.getCell(4))); Date firstInState = processFirstInState(firstInStateStr); if (firstInState == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("First in State,"); } else { mileageLog.setFirstInState(firstInState); } String lastInStateStr = ((String) getCellValue(row.getCell(5))); Date lastInState = processLastInState(lastInStateStr); if (lastInState == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Last in State,"); } else { mileageLog.setLastInState(lastInState); } Vehicle vehicle = retrieveVehicle(unit, lastInState); if (vehicle == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Unit,"); } else { mileageLog.setUnitNum(unit); mileageLog.setUnit(vehicle); mileageLog.setCompany(vehicle.getOwner()); } State state = retrieveStateByLongName(stateStr); if (state == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("State,"); } else { mileageLog.setState(state); } String miles = ((String) getCellValue(row.getCell(3))); Double milesDbl = processMiles(miles, resetMiles); if (milesDbl == null) { recordError = true; fatalRecordError = true; recordErrorMsg.append("Miles,"); } else { mileageLog.setMiles(milesDbl); } String vin = ((String) getCellValue(row.getCell(7))); if (!validateVin(vin)) { recordError = true; fatalRecordError = true; recordErrorMsg.append("VIN,"); } else { mileageLog.setVin(vin); } String groups = ((String) getCellValue(row.getCell(6))); groups = StringUtils.isEmpty(groups) ? StringUtils.EMPTY : groups; mileageLog.setGroups(groups); mileageLog.setPeriod(period); VehiclePermit vehiclePermit = retrieveVehiclePermit(mileageLog); if (vehiclePermit != null && StringUtils.isNotEmpty(vehiclePermit.getPermitNumber())) { mileageLog.setVehiclePermit(vehiclePermit); mileageLog.setVehiclePermitNumber(vehiclePermit.getPermitNumber()); } else { mileageLog.setVehiclePermit(null); mileageLog.setVehiclePermitNumber(StringUtils.EMPTY); if (mileageLog.getState() != null && StringUtils.equals("NY", mileageLog.getState().getCode())) { recordError = true; recordErrorMsg.append("Could not determine vehicle permit,"); } } MileageLog existingMileageLog = checkDuplicate(mileageLog); if (existingMileageLog != null) { 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) { mileageLogList.add(mileageLog); } } System.out.println("Done processing...Total record count: " + recordCount + ". Error count: " + errorCount + ". Number of records being loaded: " + mileageLogList.size()); if (!mileageLogList.isEmpty()) { for (MileageLog aMileageLog : mileageLogList) { aMileageLog.setGps("Y"); aMileageLog.setSource(MileageLog.SOURCE_OLD_GPS); aMileageLog.setCreatedBy(createdBy); aMileageLog.setCreatedAt(Calendar.getInstance().getTime()); genericDAO.saveOrUpdate(aMileageLog); } } } catch (Exception ex) { errorList.add("Not able to upload XL!!! Please try again."); log.warn("Error while importing Mileage log: " + ex); } return errorList; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importeztollMainSheet(InputStream is, Boolean override) throws Exception { // initializing the InputStream from a file using // POIFSFileSystem, before converting the result // into an HSSFWorkbook instance //XSSFWorkbook HSSFWorkbook wb = null; StringBuffer buffer = null;//from www.j a v a 2 s .c o m List<String> list = new ArrayList<String>(); List<EzToll> eztolls = new ArrayList<EzToll>(); // List<String> emptydatalist=new ArrayList<String>(); int count = 1; int errorcount = 0; try { POIFSFileSystem fs = new POIFSFileSystem(is); ErrorData edata = new ErrorData(); // FileWriter writer = new FileWriter("e:/errordata.txt"); wb = new HSSFWorkbook(fs); int numOfSheets = wb.getNumberOfSheets(); Map criterias = new HashMap(); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; EzToll eztoll = null; Iterator rows = sheet.rowIterator(); StringBuffer lineError; while (rows.hasNext()) { boolean error = false; buffer = new StringBuffer(); int cellCount = 0; row = (HSSFRow) rows.next(); if (count == 1) { count++; continue; } lineError = new StringBuffer(""); try { eztoll = new EzToll(); // FOR Toll COMPANY String tollcompany = ((String) getCellValue(row.getCell(0))); try { criterias.clear(); criterias.put("name", tollcompany); TollCompany tollcompanyName = genericDAO.getByCriteria(TollCompany.class, criterias); if (tollcompanyName == null) throw new Exception("Invalid Toll Company Name"); eztoll.setToolcompany(tollcompanyName); } catch (Exception ex) { // System.out.println("\n\n Error in Driver first // name========>"+ex); error = true; lineError.append("Toll Company Name,"); log.warn(ex.getMessage()); } // FOR COMPANY String company = ((String) getCellValue(row.getCell(1))); try { criterias.clear(); criterias.put("type", 3); criterias.put("name", company); Location companyName = genericDAO.getByCriteria(Location.class, criterias); // System.out.println("\ncompanyName====>"+companyName+"\n"); if (companyName == null) throw new Exception("Invalid Company Name"); eztoll.setCompany(companyName); } catch (Exception ex) { // System.out.println("\n\n Error in Driver first // name========>"+ex); error = true; lineError.append("Invalid Company Name,"); log.warn(ex.getMessage()); } // FOR TERMINAL /* * try { criterias.clear(); String name = (String) * getCellValue(row.getCell(2)); * //System.out.println("\nTerminal====>"+name+"\n"); if * (StringUtils.isEmpty(name)) throw new * Exception("Invalid terminal"); else { * criterias.put("name", name); criterias.put("type", 4); } * Location location = * genericDAO.getByCriteria(Location.class, criterias); if * (location == null) throw new * Exception("no such Terminal"); else * eztoll.setTerminal(location); } catch (Exception ex) { * error = true; lineError.append("Terminal,"); * log.warn(ex.getMessage()); } */ if (override == false) { Date date2 = row.getCell(10).getDateCellValue(); try { if (validDate(date2)) { eztoll.setInvoiceDate(dateFormat1.parse(dateFormat1.format(date2))); } else { error = true; lineError.append("Invoice Date,"); } } catch (Exception ex) { error = true; lineError.append("Invoice Date,"); log.warn(ex.getMessage()); } } else { if (validDate(getCellValue(row.getCell(10)))) eztoll.setInvoiceDate((Date) getCellValue(row.getCell(10))); else { eztoll.setInvoiceDate(null); } } validateAndResetTollTagAndPlateNumber(row, eztoll); String plateNum = null; if (getCellValue(row.getCell(4)) == null) { // do nothing } else if (getCellValue(row.getCell(4)).equals("")) { // do nothing } else { plateNum = getCellValue(row.getCell(4)).toString(); } String tollNum = null; if (getCellValue(row.getCell(3)) == null) { // do nothing } else if (getCellValue(row.getCell(3)).equals("")) { // do nothing } else { tollNum = getCellValue(row.getCell(3)).toString(); } // if both toll number and plate number is empty if (tollNum == null && plateNum == null) { error = true; lineError.append("Either tolltag number or plate number is required,"); log.warn("Either Toll tag number or Plate number is required "); } else { // for toll number if (tollNum != null) { try { String transactiondate = null; if (validDate(getCellValue(row.getCell(6)))) { transactiondate = dateFormat .format(((Date) getCellValue(row.getCell(6))).getTime()); } StringBuffer query = new StringBuffer( "select obj from VehicleTollTag obj where obj.tollTagNumber='" + (String) getCellValue(row.getCell(3)) + "'"); if (eztoll.getToolcompany() != null) { query.append(" and obj.tollCompany='" + eztoll.getToolcompany().getId() + "'"); } query.append(" and obj.validFrom <='" + transactiondate + "' and obj.validTo >= '" + transactiondate + "'"); List<VehicleTollTag> vehicletolltags = genericDAO .executeSimpleQuery(query.toString()); if (vehicletolltags.isEmpty() && vehicletolltags.size() == 0) throw new Exception("no such Toll tag Number"); else { /***Correction for unit no. mapping to multiple vehicle ids***/ /*String vehquery = "Select obj from Vehicle obj where obj.unit=" + vehicletolltags.get(0).getVehicle().getUnit() + " and obj.validFrom <='" + transactiondate + "' and obj.validTo >= '" + transactiondate + "'"; String vehquery = "Select obj from Vehicle obj where obj.id=" + vehicletolltags.get(0).getVehicle().getId() + " and obj.validFrom <='" + transactiondate + "' and obj.validTo >= '" + transactiondate + "'"; System.out.println("******************** the vehicle query is " + vehquery); List<Vehicle> vehicle = genericDAO.executeSimpleQuery(vehquery.toString());*/ List<Vehicle> vehicle = retrieveVehicle(vehicletolltags.get(0), transactiondate); if (vehicle.isEmpty() && vehicle.size() == 0) { throw new Exception( "TOLL_ERROR_MSG: Invalid Toll Tag Number - No matching vehicle found for given id and txn date"); } else { eztoll.setUnit(vehicle.get(0)); eztoll.setTollTagNumber(vehicletolltags.get(0)); String drv_name = (String) getCellValue(row.getCell(5)); if (!(StringUtils.isEmpty(drv_name))) { /*criterias.clear(); criterias.put("fullName", getCellValue(row.getCell(5))); Driver driver = genericDAO.getByCriteria(Driver.class, criterias);*/ Driver driver = getDriverObjectFromName(drv_name, row); if (driver == null) { error = true; lineError.append("Invalid Driver Name, "); } else { eztoll.setDriver(driver); eztoll.setTerminal(driver.getTerminal()); } } else { /*String drivequery = "select obj from Ticket obj where obj.loadDate<='" + transactiondate + "' and obj.unloadDate>='" + transactiondate + "' and obj.vehicle=" + vehicle.get(0).getId(); System.out.println(" my query is " + drivequery); List<Ticket> tickets = genericDAO.executeSimpleQuery(drivequery);*/ List<Vehicle> vehicleList = retrieveVehicleForUnit( vehicletolltags.get(0).getVehicle().getUnit(), transactiondate); List<Ticket> tickets = getTicketsForVehicle(vehicleList, transactiondate); // More than one driver fix - 13th May 2016 String txnTime = getCellValue(row.getCell(7)).toString(); Date txnDate = (Date) getCellValue(row.getCell(6)); tickets = determineCorrectTicket(tickets, txnDate, txnTime); if (!tickets.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (Ticket ticket : tickets) { boolean d = driverid.contains(ticket.getDriver().getId() + ""); driverid.add(ticket.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { if (override == false) { error = true; lineError.append("More than one Driver, "); tic = false; } else { tic = false; try { criterias.clear(); String name = (String) getCellValue(row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO .getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } if (tic) { eztoll.setDriver(tickets.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, tickets.get(0).getDriver().getId()); eztoll.setTerminal(driver.getTerminal()); } } else { String driverFuelLogQuery = "select obj from DriverFuelLog obj where obj.transactionDate='" + transactiondate + "' and obj.truck=" + vehicle.get(0).getId(); System.out.println(" my query is " + driverFuelLogQuery); List<DriverFuelLog> driverFuelLog = genericDAO .executeSimpleQuery(driverFuelLogQuery); if (!driverFuelLog.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (DriverFuelLog drvFuelLog : driverFuelLog) { boolean d = driverid .contains(drvFuelLog.getDriver().getId() + ""); driverid.add(drvFuelLog.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { if (override == false) { error = true; lineError.append("More than one Driver, "); tic = false; } else { tic = false; try { criterias.clear(); String name = (String) getCellValue( row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO.getByCriteria( Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } if (tic) { eztoll.setDriver(driverFuelLog.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, driverFuelLog.get(0).getDriver().getId()); eztoll.setTerminal(driver.getTerminal()); } } else { String driverOdometerQuery = "select obj from Odometer obj where obj.recordDate='" + transactiondate + "' and obj.truck=" + vehicle.get(0).getId(); System.out.println(" odometer query is " + driverOdometerQuery); List<Odometer> odometer = genericDAO .executeSimpleQuery(driverOdometerQuery); if (!odometer.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (Odometer odometerObj : odometer) { boolean d = driverid .contains(odometerObj.getDriver().getId() + ""); driverid.add(odometerObj.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { if (override == false) { error = true; lineError.append("More than one Driver, "); tic = false; } else { tic = false; try { criterias.clear(); String name = (String) getCellValue( row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO .getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } if (tic) { eztoll.setDriver(odometer.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, odometer.get(0).getDriver().getId()); eztoll.setTerminal(driver.getTerminal()); } } else { if (override == false) { error = true; lineError.append( "No matching Ticket, Fuel Log, Odometer entry, "); } else { try { criterias.clear(); String name = (String) getCellValue(row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO .getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } } } } } } catch (Exception ex) { error = true; //lineError.append("Invalid Toll Tag Number, "); String errMsg = "Invalid Toll Tag Number"; String exceptionMsg = StringUtils.substringAfter(ex.getMessage(), "TOLL_ERROR_MSG: "); if (!StringUtils.isEmpty(exceptionMsg)) { errMsg = exceptionMsg; } lineError.append(errMsg + ", "); log.warn(ex.getMessage()); } } // FOR PLATE# if (plateNum != null) { try { /**Correction for plate no. verification - adding txn date***/ /*criterias.clear(); criterias.put("plate", (String) getCellValue(row.getCell(4))); Vehicle vehicle = genericDAO.getByCriteria(Vehicle.class, criterias);*/ Vehicle vehicle = retrieveVehicleForPlate((String) getCellValue(row.getCell(4)), row); if (vehicle == null) throw new Exception("no such Plate or Toll tag Number"); else { if (tollNum != null) { String transactiondate = null; if (validDate(getCellValue(row.getCell(6)))) { transactiondate = dateFormat .format(((Date) getCellValue(row.getCell(6))).getTime()); System.out.println("\n****--****\n"); } StringBuffer query = new StringBuffer( "select obj from VehicleTollTag obj where obj.tollTagNumber='" + (String) getCellValue(row.getCell(3)) + "'"); if (eztoll.getToolcompany() != null) { query.append(" and obj.tollCompany='" + eztoll.getToolcompany().getId() + "'"); } query.append(" and obj.vehicle='" + vehicle.getId() + "' and obj.validFrom <='" + transactiondate + "' and obj.validTo >= '" + transactiondate + "'"); System.out.println("******* query ======>" + query); try { List<VehicleTollTag> vehicletolltags = genericDAO .executeSimpleQuery(query.toString()); if (vehicletolltags.isEmpty() && vehicletolltags.size() == 0) throw new Exception("Invalid Plate Number"); else { /* * Code to get the active plate * numbers */ /*List<Vehicle> vehicleList = genericDAO .executeSimpleQuery( // Correction for unit no. mapping to multiple vehicle ids //"select o from Vehicle o where o.unit=" //+ vehicletolltags.get(0).getUnit() //+ " and o.validFrom<=SYSDATE() and o.validTo>=SYSDATE() "); "select o from Vehicle o where o.id=" + vehicletolltags.get(0).getVehicle().getId() + " and o.validFrom <='"+ transactiondate + "' and o.validTo >= '" + transactiondate + "'");*/ List<Vehicle> vehicleList = retrieveVehicle(vehicletolltags.get(0), transactiondate); if (vehicleList.isEmpty() && vehicleList.size() == 0) throw new Exception("Invalid Plate Number"); else eztoll.setPlateNumber(vehicleList.get(0)); } } catch (Exception ex) { System.out.println("\n*******\n"); } } else { String transactiondate1 = null; if (validDate(getCellValue(row.getCell(6)))) { transactiondate1 = dateFormat .format(((Date) getCellValue(row.getCell(6))).getTime()); System.out.println("\n****--****\n"); } StringBuffer query = new StringBuffer( "select obj from VehicleTollTag obj where "); query.append("obj.vehicle='" + vehicle.getId() + "' and obj.validFrom <='" + transactiondate1 + "' and obj.validTo >= '" + transactiondate1 + "'"); if (eztoll.getToolcompany() != null) { query.append(" and obj.tollCompany='" + eztoll.getToolcompany().getId() + "'"); } System.out.println("******* query ======>" + query); try { List<VehicleTollTag> vehicletolltags = genericDAO .executeSimpleQuery(query.toString()); if (vehicletolltags.isEmpty() && vehicletolltags.size() == 0) { // throw new Exception("Invalid Plate Number"); } else { eztoll.setTollTagNumber(vehicletolltags.get(0)); } } catch (Exception ex) { System.out.println("\n*******\n"); } String drv_name = (String) getCellValue(row.getCell(5)); if (!(StringUtils.isEmpty(drv_name))) { /*criterias.clear(); criterias.put("fullName", getCellValue(row.getCell(5))); Driver driver = genericDAO.getByCriteria(Driver.class, criterias);*/ Driver driver = getDriverObjectFromName(drv_name, row); if (driver == null) { error = true; lineError.append("Invalid Driver Name, "); } else { eztoll.setDriver(driver); eztoll.setTerminal(driver.getTerminal()); } } else { String transactiondate = null; if (validDate(getCellValue(row.getCell(6)))) { transactiondate = dateFormat .format(((Date) getCellValue(row.getCell(6))).getTime()); } /*String drivequery = "select obj from Ticket obj where obj.loadDate<='" + transactiondate + "' and obj.unloadDate>='" + transactiondate + "' and obj.vehicle=" + vehicle.getId(); System.out.println(" my query is " + drivequery); List<Ticket> tickets = genericDAO.executeSimpleQuery(drivequery);*/ List<Vehicle> vehicleListForDriver = new ArrayList<Vehicle>(); vehicleListForDriver.add(vehicle); List<Ticket> tickets = getTicketsForVehicle(vehicleListForDriver, transactiondate); // More than one driver fix - 13th May 2016 String txnTime = getCellValue(row.getCell(7)).toString(); Date txnDate = (Date) getCellValue(row.getCell(6)); tickets = determineCorrectTicket(tickets, txnDate, txnTime); if (!tickets.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (Ticket ticket : tickets) { boolean d = driverid.contains(ticket.getDriver().getId() + ""); driverid.add(ticket.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { if (override == false) { error = true; lineError.append("More than one Driver, "); tic = false; } else { tic = false; try { criterias.clear(); String name = (String) getCellValue(row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO .getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } if (tic) { eztoll.setDriver(tickets.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, tickets.get(0).getDriver().getId()); eztoll.setTerminal(driver.getTerminal()); } } else { String driverFuelLogQuery = "select obj from DriverFuelLog obj where obj.transactionDate='" + transactiondate + "' and obj.truck=" + vehicle.getId(); System.out.println(" my query is " + driverFuelLogQuery); List<DriverFuelLog> driverFuelLog = genericDAO .executeSimpleQuery(driverFuelLogQuery); if (!driverFuelLog.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (DriverFuelLog drvFuelLog : driverFuelLog) { boolean d = driverid .contains(drvFuelLog.getDriver().getId() + ""); driverid.add(drvFuelLog.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { if (override == false) { error = true; lineError.append("More than one Driver, "); tic = false; } else { tic = false; try { criterias.clear(); String name = (String) getCellValue( row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO.getByCriteria( Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } if (tic) { eztoll.setDriver(driverFuelLog.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, driverFuelLog.get(0).getDriver().getId()); eztoll.setTerminal(driver.getTerminal()); } } else { String driverOdometerQuery = "select obj from Odometer obj where obj.recordDate='" + transactiondate + "' and obj.truck=" + vehicle.getId(); System.out.println(" odometer query is " + driverOdometerQuery); List<Odometer> odometer = genericDAO .executeSimpleQuery(driverOdometerQuery); if (!odometer.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (Odometer odometerObj : odometer) { boolean d = driverid .contains(odometerObj.getDriver().getId() + ""); driverid.add(odometerObj.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { if (override == false) { error = true; lineError.append("More than one Driver, "); tic = false; } else { tic = false; try { criterias.clear(); String name = (String) getCellValue( row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO .getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } if (tic) { eztoll.setDriver(odometer.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, odometer.get(0).getDriver().getId()); eztoll.setTerminal(driver.getTerminal()); } } else { if (override == false) { error = true; lineError.append( "No matching Ticket, Fuel Log, Odometer entry, "); } else { try { criterias.clear(); String name = (String) getCellValue(row.getCell(2)); // System.out.println("\nTerminal====>"+name+"\n"); if (StringUtils.isEmpty(name)) throw new Exception("Invalid terminal"); else { criterias.put("name", name); criterias.put("type", 4); } Location location = genericDAO .getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such Terminal"); else eztoll.setTerminal(location); } catch (Exception ex) { error = true; lineError.append("Terminal,"); log.warn(ex.getMessage()); } } } } } } eztoll.setPlateNumber(vehicle); eztoll.setUnit(vehicle); } } } catch (Exception ex) { error = true; lineError.append("Invalid Plate or Toll tag Number, "); log.warn(ex.getMessage()); } } else { if (eztoll.getTollTagNumber() != null) { String transactiondate = null; if (validDate(getCellValue(row.getCell(6)))) { transactiondate = dateFormat .format(((Date) getCellValue(row.getCell(6))).getTime()); } VehicleTollTag vehicletoll = genericDAO.getById(VehicleTollTag.class, eztoll.getTollTagNumber().getId()); /* Code to get the active plate numbers */ /*List<Vehicle> vehicleList = genericDAO.executeSimpleQuery( // Correction for unit no. mapping to multiple vehicle ids //"select o from Vehicle o where o.unit=" + vehicletoll.getUnit() //+ " and o.validFrom<=SYSDATE() and o.validTo>=SYSDATE() "); "select o from Vehicle o where o.id=" + vehicletoll.getVehicle().getId() + " and o.validFrom <='"+ transactiondate + "' and o.validTo >= '" + transactiondate + "'");*/ List<Vehicle> vehicleList = retrieveVehicle(vehicletoll, transactiondate); if (vehicleList.isEmpty() && vehicleList.size() == 0) //throw new Exception("Invalid Plate Number"); throw new Exception( "Invalid Toll Tag Number - no matching vehicle found for given id and txn date"); else eztoll.setPlateNumber(vehicleList.get(0)); // eztoll.setPlateNumber(vehicletoll.getVehicle()); } } } // FOR TRANSACTION DATE try { if (validDate(getCellValue(row.getCell(6)))) eztoll.setTransactiondate((Date) getCellValue(row.getCell(6))); else { error = true; lineError.append("Transaction Date,"); } } catch (Exception ex) { System.out.println("\nERROR IN TRANSACTION DATE\n"); log.warn(ex.getMessage()); } // FOR TRANSACTION TIME try { if (validDate(getCellValue(row.getCell(7)))) { eztoll.setTransactiontime(dateFormat2.format((Date) getCellValue(row.getCell(7)))); } else { String trxTime1 = (String) getCellValue(row.getCell(7)); if (!(StringUtils.isEmpty(trxTime1))) { if (trxTime1.length() == 5 || trxTime1.length() == 8 || trxTime1.length() == 7) { StringBuilder time = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(7)), 4, '0')); // time.insert(2, ':'); if (trxTime1.length() == 8) { eztoll.setTransactiontime(time.toString().substring(0, 5)); } else if (trxTime1.length() == 7) { eztoll.setTransactiontime(time.toString().substring(0, 4)); } else { eztoll.setTransactiontime(time.toString()); } } else { // System.out.println("\ntrx time is not // valid\n"); error = true; lineError.append("Transaction Time,"); } } else { lineError.append("Transaction Time,"); } } } catch (Exception e) { } /* * if (validTime((String) getCellValue(row.getCell(6)))) { * StringBuilder time = new * StringBuilder(StringUtils.leftPad((String)getCellValue( * row.getCell(6)),4,'0')); int * hh=Integer.parseInt(time.substring(0,2)); int * mm=Integer.parseInt(time.substring(2)); * * if(hh==24) { if(mm==0) { time.insert(2, ':'); * eztoll.setTransactiontime(time.toString()); // * System.out.println("\nTRANSACTION TIME ====>"+time+"\n"); * } else { error = true; * lineError.append("transaction time,"); } } else { * if(hh<24) { if(mm<=59) { time.insert(2, ':'); * eztoll.setTransactiontime(time.toString()); // * System.out.println("\nTRANSACTION TIME ====>"+time+"\n"); * } else { error = true; * lineError.append("transaction time minut is > 59,"); } } * else { error = true; * lineError.append("transaction time hours is > 24,"); } } * } else { error = true; * lineError.append("transaction time more than 5 degits,"); * * } */ // FOR AGENCY try { eztoll.setAgency((String) getCellValue(row.getCell(8))); } catch (Exception ex) { error = true; lineError.append("Agency,"); log.warn(ex.getMessage()); } // FOR AMOUNTS String amount1 = row.getCell(9).toString(); Double amount2 = getValidGallon(amount1); if (amount2 != null) { //eztoll.setAmount(Math.abs(amount2)); eztoll.setAmount(amount2); } else { lineError.append("Amount,"); error = true; } /**Added dup check**/ // END OF CELL if (override == false) { System.out.println("***** eneter here ok 0"); if (!error) { System.out.println("***** eneter here ok 1"); Map prop = new HashMap(); prop.put("toolcompany", eztoll.getToolcompany().getId()); prop.put("company", eztoll.getCompany().getId()); prop.put("driver", eztoll.getDriver().getId()); prop.put("terminal", eztoll.getTerminal().getId()); prop.put("unit", eztoll.getUnit().getId()); prop.put("agency", eztoll.getAgency()); prop.put("invoiceDate", dateFormat1.format(eztoll.getInvoiceDate())); prop.put("transactiondate", dateFormat1.format(eztoll.getTransactiondate())); prop.put("transactiontime", eztoll.getTransactiontime()); if (eztoll.getTollTagNumber() != null) { prop.put("tollTagNumber", eztoll.getTollTagNumber().getId()); } if (eztoll.getPlateNumber() != null) { prop.put("plateNumber", eztoll.getPlateNumber().getId()); } prop.put("amount", eztoll.getAmount()); boolean rst = genericDAO.isUnique(EzToll.class, eztoll, prop); System.out.println("***** eneter here ok 2" + rst); if (!rst) { System.out.println("***** eneter here ok 3"); lineError.append("Toll tag entry already exists(Duplicate),"); error = true; errorcount++; } if (eztolls.contains(eztoll)) { lineError.append("Duplicate eztoll in excel,"); error = true; } // Toll upload improvement - 23rd Jul 2016 /*else { eztolls.add(eztoll); }*/ } else { errorcount++; } } else { if (!error) { eztolls.add(eztoll); } else { errorcount++; } } /**End of adding dup check**/ /*// END OF CELL if (!error) { if (eztolls.contains(eztoll)) { lineError.append("Duplicate eztoll,"); error = true; } else { eztolls.add(eztoll); } //eztolls.add(eztoll); } else { errorcount++; }*/ } // TRY INSIDE SHILE(LOOP) catch (Exception ex) { error = true; lineError.append("Exception while processing toll upload records,"); log.warn(ex); } if (lineError.length() > 0) { System.out.println("Error :" + lineError.toString()); list.add("Line " + count + ":" + lineError.toString() + "<br/>"); } // Toll upload improvement - 23rd Jul 2016 else { eztolls.add(eztoll); } System.out.println("Record No :" + count); count++; } // CLOSE while (rows.hasNext()) } // FIRST TRY catch (Exception e) { log.warn("Error in import eztoll :" + e); // Toll upload improvement - 23rd Jul 2016 throw e; } // Toll upload improvement - 23rd Jul 2016 //if (errorcount == 0) { for (EzToll etoll : eztolls) { Map criti = new HashMap(); criti.clear(); criti.put("id", etoll.getDriver().getId()); Driver drvOBj = genericDAO.getByCriteria(Driver.class, criti); if (drvOBj != null) etoll.setDriverFullName(drvOBj.getFullName()); criti.clear(); criti.put("id", etoll.getUnit().getId()); Vehicle vehObj = genericDAO.getByCriteria(Vehicle.class, criti); if (vehObj != null) etoll.setUnitNum(vehObj.getUnitNum()); genericDAO.saveOrUpdate(etoll); } // Toll upload improvement - 23rd Jul 2016 //} return list; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@SuppressWarnings("unchecked") @Override/*from ww w . j a v a 2s. com*/ @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW) public List<String> importfuellogMainSheet(InputStream is, Boolean override) throws Exception { // initializing the InputStream from a file using // POIFSFileSystem, before converting the result // into an HSSFWorkbook instance //XSSFWorkbook HSSFWorkbook wb = null; StringBuffer buffer = null; List<String> list = new ArrayList<String>(); List<FuelLog> fuellogs = new ArrayList<FuelLog>(); // List<String> emptydatalist=new ArrayList<String>(); int count = 1; int errorcount = 0; try { POIFSFileSystem fs = new POIFSFileSystem(is); ErrorData edata = new ErrorData(); wb = new HSSFWorkbook(fs); int numOfSheets = wb.getNumberOfSheets(); Map criterias = new HashMap(); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; FuelLog fuellog = null; Iterator rows = sheet.rowIterator(); StringBuffer lineError; while (rows.hasNext()) { System.out.println("Parsing row.."); boolean error = false; buffer = new StringBuffer(); int cellCount = 0; row = (HSSFRow) rows.next(); if (count == 1) { count++; continue; } lineError = new StringBuffer(""); try { fuellog = new FuelLog(); // Fuel log - subcontractor processSubContractor(row, fuellog); String Fname = (String) getCellValue(row.getCell(0)); if (override == false) { try { if (StringUtils.isEmpty(Fname)) { error = true; lineError.append("Fuel Vendor is blank,"); } else { criterias.clear(); criterias.put("name", Fname); FuelVendor fuelvendor = genericDAO.getByCriteria(FuelVendor.class, criterias); if (fuelvendor == null) { error = true; lineError.append("no such Fuel Vendor,"); // throw new Exception("no such // fuelvender"); } else { fuellog.setFuelvendor(fuelvendor); } } } catch (Exception ex) { error = true; lineError.append("fuelvendor,"); log.warn(ex.getMessage()); } } else { criterias.clear(); criterias.put("name", Fname); FuelVendor fuelvendor = genericDAO.getByCriteria(FuelVendor.class, criterias); fuellog.setFuelvendor(fuelvendor); } // FOR COMPANY String company = ((String) getCellValue(row.getCell(1))); if (override == false) { try { if (StringUtils.isEmpty(company)) { error = true; lineError.append("Company is blank,"); } else { criterias.clear(); criterias.put("type", 3); criterias.put("name", company); Location companyName = genericDAO.getByCriteria(Location.class, criterias); if (companyName == null) { error = true; lineError.append("no such Company,"); } else { fuellog.setCompany(companyName); } } } catch (Exception ex) { error = true; lineError.append("Company,"); log.warn(ex.getMessage()); } } else { criterias.clear(); criterias.put("type", 3); criterias.put("name", company); Location companyName = genericDAO.getByCriteria(Location.class, criterias); fuellog.setCompany(companyName); } if (override == false) { Date date2 = row.getCell(2).getDateCellValue(); try { if (validDate(date2)) { fuellog.setInvoiceDate(dateFormat1.parse(dateFormat1.format(date2))); } else { error = true; lineError.append("Invoice Date,"); } } catch (Exception ex) { error = true; lineError.append("Invoice Date,"); log.warn(ex.getMessage()); } } else { if (validDate(getCellValue(row.getCell(2)))) fuellog.setInvoiceDate((Date) getCellValue(row.getCell(2))); else { fuellog.setInvoiceDate(null); } } // FOR UNVOICED NUMBER // System.out.println("\nInvoiceNo====>"+(String) // getCellValue(row.getCell(4))+"\n"); String invoiceNo = ""; try { invoiceNo = (String) getCellValue(row.getCell(3)); } catch (Exception e) { error = true; lineError.append("Invalid Invoice Number, "); } if (override == false) { try { if ((StringUtils.isEmpty(invoiceNo))) { error = true; lineError.append("Invoice# is blank,"); } else { fuellog.setInvoiceNo((String) getCellValue(row.getCell(3))); } } catch (Exception ex) { error = true; lineError.append("Invoice Number,"); log.warn(ex.getMessage()); } } else { if ((StringUtils.isEmpty(invoiceNo))) fuellog.setInvoiceNo(null); else { fuellog.setInvoiceNo((String) getCellValue(row.getCell(3))); } } // FOR TRANSACTION DATE /* * try { if (validDate(getCellValue(row.getCell(5)))) * fuellog.setTransactiondate((Date) * getCellValue(row.getCell(5))); else { error = true; * lineError.append("transaction date,"); } } * catch(Exception ex) { * //System.out.println("\nERROR IN TRANSACTION DATE\n"); * log.warn(ex.getMessage()); * * } */ if (override == false) { try { Date date4 = row.getCell(4).getDateCellValue(); if (validDate(date4)) { fuellog.setTransactiondate(dateFormat1.parse(dateFormat1.format(date4))); } else { error = true; lineError.append("Transaction Date,"); } } catch (Exception ex) { error = true; lineError.append("Transaction Date,"); log.warn(ex.getMessage()); } } else { if (validDate(getCellValue(row.getCell(4)))) { fuellog.setTransactiondate((Date) getCellValue(row.getCell(4))); } else { fuellog.setTransactiondate(null); } } try { if (validDate(getCellValue(row.getCell(5)))) { fuellog.setTransactiontime(dateFormat2.format((Date) getCellValue(row.getCell(5)))); } else { // new trx time,uploading in 00:00 format String trxTime1 = (String) getCellValue(row.getCell(5)); if (!(StringUtils.isEmpty(trxTime1))) { if (override == false) { if (trxTime1.length() == 5 || trxTime1.length() == 8) { StringBuilder time = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(5)), 4, '0')); // time.insert(2, ':'); if (trxTime1.length() == 8) { fuellog.setTransactiontime(time.toString().substring(0, 5)); } else { fuellog.setTransactiontime(time.toString()); } } else { // System.out.println("\ntrx time is not // valid\n"); error = true; lineError.append("Transaction Time,"); } } else { if (trxTime1.length() == 5 || trxTime1.length() == 8) { StringBuilder time = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(5)), 4, '0')); // time.insert(2, ':'); if (trxTime1.length() == 8) { fuellog.setTransactiontime(time.toString().substring(0, 5)); } else { fuellog.setTransactiontime(time.toString()); } } else { fuellog.setTransactiontime((String) getCellValue(row.getCell(5))); } } } else { fuellog.setTransactiontime((String) getCellValue(row.getCell(5))); // System.out.println("\nElse trxTime // empty=="+trxTime1+"\n"); } } } catch (Exception ex) { fuellog.setTransactiontime((String) getCellValue(row.getCell(5))); } //String unit = ((String) getCellValue(row.getCell(6))); // if(override==false){ //error = setUnitNumberInFuelLog(criterias, row, fuellog, lineError, error, unit); try { String unit = validateAndResetUnitNumber(criterias, row); if (StringUtils.isEmpty(unit)) { String lastName = ((String) getCellValue(row.getCell(7))); String firstName = ((String) getCellValue(row.getCell(8))); if (!lastName.isEmpty() && !firstName.isEmpty()) { Driver driver = getDriverObjectFromName(criterias, firstName, lastName, row); if (driver == null) { error = true; lineError.append("Unit is blank (check driver name),"); } else { // HEMA: Added fuellog.setDriversid(driver); fuellog.setTerminal(driver.getTerminal()); String transdate = null; if (validDate(getCellValue(row.getCell(4)))) { transdate = dateFormat .format(((Date) getCellValue(row.getCell(4))).getTime()); } /*String drivequery = "select obj from Ticket obj where obj.loadDate<='" + transdate + "' and obj.unloadDate>='" + transdate + "' and obj.driver=" + driver.getId();*/ /*String drivequery = "select obj from Ticket obj where (obj.loadDate ='" + transdate + "' OR obj.unloadDate ='" + transdate + "') and obj.driver=" + driver.getId(); System.out.println("******** query is " + drivequery); List<Ticket> tickets = genericDAO.executeSimpleQuery(drivequery);*/ List<Ticket> tickets = getAllTicketsForDriver(String.valueOf(driver.getId()), transdate); if (!tickets.isEmpty()) { boolean tic = true; boolean first = true; List<String> vehicleid = new ArrayList<String>(); for (Ticket ticket : tickets) { boolean d = vehicleid.contains(ticket.getVehicle().getId() + ""); vehicleid.add(ticket.getVehicle().getId() + ""); if (first) { first = false; continue; } if (!d) { error = true; lineError.append("More than one vehicle, "); tic = false; } } if (tic) { fuellog.setUnit(tickets.get(0).getVehicle()); } } else { String driveFuelLogquery = "select obj from DriverFuelLog obj where obj.transactionDate='" + transdate + "' and obj.driver=" + driver.getId(); System.out.println("********driver fuel query is " + driveFuelLogquery); List<DriverFuelLog> driverFuelLog = genericDAO .executeSimpleQuery(driveFuelLogquery); if (!driverFuelLog.isEmpty()) { boolean tic = true; boolean first = true; List<String> truckid = new ArrayList<String>(); for (DriverFuelLog drvFuelLog : driverFuelLog) { boolean d = truckid.contains(drvFuelLog.getTruck().getId() + ""); truckid.add(drvFuelLog.getTruck().getId() + ""); if (first) { first = false; continue; } if (!d) { error = true; lineError.append("More than one vehicle, "); tic = false; } } if (tic) { fuellog.setUnit(driverFuelLog.get(0).getTruck()); } } else { String odometerQery = "select obj from Odometer obj where obj.recordDate='" + transdate + "' and obj.driver=" + driver.getId(); System.out.println("********odometer query is " + odometerQery); List<Odometer> driverOdometer = genericDAO .executeSimpleQuery(odometerQery); if (!driverOdometer.isEmpty()) { boolean tic = true; boolean first = true; List<String> vehid = new ArrayList<String>(); for (Odometer odometer : driverOdometer) { boolean d = vehid.contains(odometer.getTruck().getId() + ""); vehid.add(odometer.getTruck().getId() + ""); if (first) { first = false; continue; } if (!d) { error = true; lineError.append("More than one vehicle, "); tic = false; } } if (tic) { fuellog.setUnit(driverOdometer.get(0).getTruck()); } } else { error = true; lineError.append( "Unit is either blank or not valid for given transaction date and no matching Ticket, Fuel Log, Odometer entry found while detrmining correct unit "); } } } } } else { error = true; lineError.append("Unit is blank,"); } } else { criterias.clear(); String transactionDate = null; System.out.println("********** date value is " + getCellValue(row.getCell(4))); if (validDate(getCellValue(row.getCell(4)))) { transactionDate = dateFormat .format(((Date) getCellValue(row.getCell(4))).getTime()); } Vehicle vehicle = null; String vehicleQuery = "Select obj from Vehicle obj where obj.type=1 and obj.unit=" + Integer.parseInt((String) getCellValue(row.getCell(6))) + " and obj.validFrom<='" + transactionDate + "' and obj.validTo>='" + transactionDate + "'"; System.out.println("******* The vehicle query for fuel upload is " + vehicleQuery); List<Vehicle> vehicleList = genericDAO.executeSimpleQuery(vehicleQuery); if (vehicleList == null || vehicleList.size() == 0) { System.out.println("Entered here "); error = true; lineError.append("no such Vehicle,"); } else { fuellog.setUnit(vehicleList.get(0)); vehicle = vehicleList.get(0); // ***** newly added ********* String lastName = ((String) getCellValue(row.getCell(7))); try { if (!StringUtils.isEmpty(lastName)) { criterias.clear(); criterias.put("lastName", lastName); Driver lname = genericDAO.getByCriteria(Driver.class, criterias); if (lname == null) { error = true; lineError.append("No such Last Name,"); } else { // fuellog.setDriverLname(lname); } } } catch (Exception ex) { error = true; lineError.append("Driver Last Name,"); log.warn(ex.getMessage()); } String firstName = ((String) getCellValue(row.getCell(8))); try { if (!StringUtils.isEmpty(firstName)) { criterias.clear(); criterias.put("firstName", firstName); Driver fname = genericDAO.getByCriteria(Driver.class, criterias); if (fname == null) { error = true; lineError.append("No such First Name,"); } else { // fuellog.setDriverFname(fname); } } } catch (Exception ex) { error = true; lineError.append("Driver First Name,"); log.warn(ex.getMessage()); } // taking driverFname and driverLName and // storing as fullname try { if (StringUtils.isEmpty(lastName) && StringUtils.isEmpty(firstName)) { String transactiondate = null; if (validDate(getCellValue(row.getCell(4)))) { transactiondate = dateFormat .format(((Date) getCellValue(row.getCell(4))).getTime()); } /*String drivequery = "select obj from Ticket obj where obj.loadDate<='" + transactiondate + "' and obj.unloadDate>='" + transactiondate + "' and obj.vehicle=" + vehicle.getId();*/ /*String drivequery = "select obj from Ticket obj where (obj.loadDate ='" + transactiondate + "' OR obj.unloadDate ='" + transactiondate + "') and obj.vehicle=" + vehicle.getId(); System.out.println("******** query is " + drivequery); List<Ticket> tickets = genericDAO.executeSimpleQuery(drivequery);*/ List<Vehicle> vehicleListForDriver = new ArrayList<Vehicle>(); vehicleListForDriver.add(vehicle); List<Ticket> tickets = getTicketsForVehicle(vehicleListForDriver, transactiondate); if (!tickets.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (Ticket ticket : tickets) { boolean d = driverid.contains(ticket.getDriver().getId() + ""); driverid.add(ticket.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { error = true; lineError.append("More than one Driver, "); tic = false; } } if (tic) { fuellog.setDriversid(tickets.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, tickets.get(0).getDriver().getId()); fuellog.setTerminal(driver.getTerminal()); } } else { String driveFuelLogquery = "select obj from DriverFuelLog obj where obj.transactionDate='" + transactiondate + "' and obj.truck=" + vehicle.getId(); System.out.println("********driver fuel query is " + driveFuelLogquery); List<DriverFuelLog> driverFuelLog = genericDAO .executeSimpleQuery(driveFuelLogquery); if (!driverFuelLog.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (DriverFuelLog drvFuelLog : driverFuelLog) { boolean d = driverid .contains(drvFuelLog.getDriver().getId() + ""); driverid.add(drvFuelLog.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { error = true; lineError.append("More than one Driver, "); tic = false; } } if (tic) { fuellog.setDriversid(driverFuelLog.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, driverFuelLog.get(0).getDriver().getId()); fuellog.setTerminal(driver.getTerminal()); } } else { String odometerQery = "select obj from Odometer obj where obj.recordDate='" + transactiondate + "' and obj.truck=" + vehicle.getId(); System.out.println("********odometer query is " + odometerQery); List<Odometer> driverOdometer = genericDAO .executeSimpleQuery(odometerQery); if (!driverOdometer.isEmpty()) { boolean tic = true; boolean first = true; List<String> driverid = new ArrayList<String>(); for (Odometer odometer : driverOdometer) { boolean d = driverid .contains(odometer.getDriver().getId() + ""); driverid.add(odometer.getDriver().getId() + ""); if (first) { first = false; continue; } if (!d) { error = true; lineError.append("More than one Driver, "); tic = false; } } if (tic) { fuellog.setDriversid(driverOdometer.get(0).getDriver()); Driver driver = genericDAO.getById(Driver.class, driverOdometer.get(0).getDriver().getId()); fuellog.setTerminal(driver.getTerminal()); } } else { error = true; lineError.append( "No matching Ticket, Fuel Log, Odometer entry, "); } } } } else { Driver driver = getDriverObjectFromName(criterias, firstName, lastName, row); if (driver == null) { error = true; lineError.append("Invalid Driver,"); //throw new Exception("Invalid Driver"); } else { fuellog.setDriversid(driver); fuellog.setTerminal(driver.getTerminal()); } } } catch (Exception ex) { ex.printStackTrace(); log.warn(ex.getMessage()); } // ******** newly added ends here ******** } } } catch (Exception ex) { error = true; lineError.append("Unit,"); log.warn(ex.getMessage()); } // FOR FUEL CARD NUMBER String cardNo = ((String) getCellValue(row.getCell(9))); /* * if(override==false) { try { * //fuellog.setFuelCardNumber(((String) * getCellValue(row.getCell(10)))); if(!cardNo.isEmpty()){ * fuellog.setFuelCardNumber((String)getCellValue(row. * getCell(10))); } else{ error = true; * lineError.append("Card Number is blank,"); } } catch * (Exception ex) { error = true; * lineError.append("Card Number,"); } } else{ * fuellog.setFuelCardNumber((String)getCellValue(row. * getCell(10))); } */ /// try { // fuellog.setFuelCardNumber(((String) // getCellValue(row.getCell(10)))); if (override == false) {// StringUtils.isEmpty /* if(!cardNo.isEmpty()){ */ if (!StringUtils.isEmpty(cardNo)) { if (handleExcludedCardNumberChecks(fuellog, cardNo)) { // reset cardNo cardNo = StringUtils.EMPTY; } else { criterias.clear(); if (fuellog.getFuelvendor() != null) criterias.put("fuelvendor.id", fuellog.getFuelvendor().getId()); criterias.put("fuelcardNum", cardNo); System.out.println("Criterias for getting fuelcard = " + "fuelvendor.id = " + fuellog.getFuelvendor().getId() + ", fuelcardNum = " + cardNo); List<FuelCard> fuelcard = genericDAO.findByCriteria(FuelCard.class, criterias); if (!fuelcard.isEmpty() && fuelcard.size() > 0) { if (fuellog.getDriversid() != null && fuellog.getFuelvendor() != null) { // HEMA: Added to get fuel card using IN clause for driver String firstName = fuellog.getDriversid().getFirstName(); String lastName = fuellog.getDriversid().getLastName(); List<Driver> listOfDrivers = getDriversFromName(criterias, lastName, firstName); String listOfDriversStr = getCommaSeparatedListOfDriverID( listOfDrivers); criterias.clear(); System.out.println("Criterias for choosing card number -> DriverID: " + listOfDriversStr + ", FuelVendorID: " + fuellog.getFuelvendor().getId() + ", FuelCardID: " + fuelcard.get(0).getId()); String fuelCardQuery = "select obj from DriverFuelCard obj where " + "obj.driver IN (" + listOfDriversStr + ") " + " and obj.fuelvendor =" + fuellog.getFuelvendor().getId() + " and obj.fuelcard = " + fuelcard.get(0).getId(); System.out.println("********fuelcard query is " + fuelCardQuery); List<DriverFuelCard> driverfuelcard = genericDAO .executeSimpleQuery(fuelCardQuery); if (!driverfuelcard.isEmpty() && driverfuelcard.size() > 0) { fuellog.setFuelcard(fuelcard.get(0)); } else { if (validateFuelCardForVehicle(fuellog, fuelcard.get(0))) { fuellog.setFuelcard(fuelcard.get(0)); } else { error = true; lineError.append( " Invalid Fuel Card# for entered Fuel Vendor and Driver/Vehicle, "); } } } } else { error = true; lineError.append(" Invalid Card Number,"); } } } else { error = true; lineError.append("Card Number is blank,"); } } else { System.out.println("\nELSE OVerride card Number1\n"); /* if(!cardNo.isEmpty()){ */ if (!StringUtils.isEmpty(cardNo)) { System.out.println("\nOVerride card Number2\n"); criterias.clear(); if (fuellog.getFuelvendor() != null) criterias.put("fuelvendor.id", fuellog.getFuelvendor().getId()); criterias.put("fuelcardNum", cardNo); List<FuelCard> fuelcard = genericDAO.findByCriteria(FuelCard.class, criterias); if (!fuelcard.isEmpty() && fuelcard.size() > 0) { if (fuellog.getDriversid() != null && fuellog.getFuelvendor() != null) { // HEMA: Added to get fuel card using IN clause for driver String firstName = fuellog.getDriversid().getFirstName(); String lastName = fuellog.getDriversid().getLastName(); List<Driver> listOfDrivers = getDriversFromName(criterias, lastName, firstName); String listOfDriversStr = getCommaSeparatedListOfDriverID(listOfDrivers); criterias.clear(); System.out.println("Criterias for choosing card number -> DriverID: " + listOfDriversStr + ", FuelVendorID: " + fuellog.getFuelvendor().getId() + ", FuelCardID: " + fuelcard.get(0).getId()); String fuelCardQuery = "select obj from DriverFuelCard obj where " + "obj.driver IN (" + listOfDriversStr + ") " + " and obj.fuelvendor =" + fuellog.getFuelvendor().getId() + " and obj.fuelcard = " + fuelcard.get(0).getId(); System.out.println("********fuelcard query is " + fuelCardQuery); List<DriverFuelCard> driverfuelcard = genericDAO .executeSimpleQuery(fuelCardQuery); if (!driverfuelcard.isEmpty() && driverfuelcard.size() > 0) fuellog.setFuelcard(fuelcard.get(0)); } } /* * FuelCard fuelCard=null; * fuellog.setFuelcard(fuelCard); * System.out.println( * "\nOVerride card Number3\n"); */ } else { System.out.println("\nOVerride card Number4\n"); FuelCard fuelCard = null; fuellog.setFuelcard(fuelCard); } } } catch (Exception ex) { ex.printStackTrace(); error = true; System.out.println("\n\n Error in Card Number\n"); lineError.append("Card Number,"); } /// // new FOR FUEL CARD NUMBER for long /* * String cardNo = ((String) getCellValue(row.getCell(10))); * if(override==false) { try { * fuellog.setFuelCardNumber(Long.parseLong((String) * getCellValue(row.getCell(10)))); if(!cardNo.isEmpty()){ * fuellog.setFuelCardNumber((String)getCellValue(row. * getCell(10))); * fuellog.setFuelCardNumber((Long)getCellValue(row.getCell( * 10))); } else{ error = true; * lineError.append("Card Number is blank,"); } } catch * (Exception ex) { error = true; * lineError.append("Card Number,"); } } else{ * fuellog.setFuelCardNumber((Long)getCellValue(row.getCell( * 10))); } */ String fueltext = ((String) getCellValue(row.getCell(10))); if (override == false) { try { if (!fueltext.isEmpty()) { fuellog.setFueltype((String) getCellValue(row.getCell(10))); criterias.clear(); criterias.put("dataType", "fuel_type"); criterias.put("dataText", fueltext); StaticData staticdata = genericDAO.getByCriteria(StaticData.class, criterias); if (staticdata == null) { error = true; lineError.append("Fuel Type,"); } else { /* * System.out.println("\nstaticdata---id=>"+ * staticdata.getId()+"\n"); * System.out.println( * "\nstaticdata---dataType=>"+staticdata. * getDataType()+"\n"); System.out.println( * "\nstaticdata---dataText=>"+staticdata. * getDataText()+"\n"); */ fuellog.setFueltype((String) getCellValue(row.getCell(10))); } } else { error = true; lineError.append("Fuel Type is blank,"); } } catch (Exception ex) { error = true; lineError.append("Fuel Type,"); log.warn(ex.getMessage()); } } else { fuellog.setFueltype((String) getCellValue(row.getCell(10))); } // FOR FUEL TYPE /* * String fueltype=((String) getCellValue(row.getCell(11))); * if(override==false){ try { if(!fueltype.isEmpty()){ * fuellog.setFueltype((String)getCellValue(row.getCell(11)) * ); } else{ error = true; * lineError.append("Fueltype is blank,"); } } * catch(Exception ex) { error = true; * lineError.append("Fueltype,"); log.warn(ex.getMessage()); * } } else{ * fuellog.setFueltype((String)getCellValue(row.getCell(11)) * ); } */ // FOR CITY String city = ((String) getCellValue(row.getCell(11))); if (!(StringUtils.isEmpty(city))) { if (override == false) { try { if (!city.isEmpty()) { fuellog.setCity((String) getCellValue(row.getCell(11))); } else { error = true; lineError.append("City is blank,"); } } catch (Exception ex) { error = true; lineError.append("City,"); } } else { fuellog.setCity((String) getCellValue(row.getCell(11))); } } else { fuellog.setCity(""); System.out.println("\ncity is empty\n" + fuellog.getCity()); } // FOR STATE String name = (String) getCellValue(row.getCell(12)); if (!(StringUtils.isEmpty(name))) { if (override == false) { try { criterias.clear(); if (StringUtils.isEmpty(name)) { error = true; lineError.append("State is blank,"); // throw new Exception("Invalid state // name"); } else { criterias.clear(); criterias.put("name", name); State state = genericDAO.getByCriteria(State.class, criterias); if (state == null) { error = true; lineError.append("no such State,"); // throw new Exception("no such state"); } else { fuellog.setState(state); } } } catch (Exception ex) { error = true; lineError.append("State,"); System.out.println("\nerroe in state==>" + ex + "\n"); log.warn(ex.getMessage()); } } else { criterias.clear(); criterias.put("name", name); State state = genericDAO.getByCriteria(State.class, criterias); fuellog.setState(state); } } else { // System.out.println("\nstate is empty 11\n"); criterias.clear(); criterias.put("id", 3600l); State state = genericDAO.getByCriteria(State.class, criterias); // System.out.println("\nstate is empty44\n"); fuellog.setState(state); System.out.println("\nstate is empty55\n"); } // for GALLONS /* * String testgallon=row.getCell(14).toString(); Double * gallon = getValidGallon(testgallon); if (gallon != null) * { fuellog.setGallons(gallon); } else { * lineError.append("gallons,"); error = true; } */ String gallon = ""; if (override == false) { if (row.getCell(13) != null) { gallon = row.getCell(13).toString(); } Double gallon2 = getValidGallon(gallon); if (gallon2 != null) { fuellog.setGallons(gallon2); } else { lineError.append("Gallon is blank,"); error = true; } } else { if (row.getCell(13) != null) { gallon = row.getCell(13).toString(); } Double gallon2 = getValidGallon(gallon); if (gallon2 != null) { fuellog.setGallons(gallon2); } else { } } // for unitprice /* * String unitprice=row.getCell(15).toString(); Double * unitprice1 = getValidGallon(unitprice); if (unitprice1 != * null) { fuellog.setUnitprice(unitprice1); } else { * System.out.println("\nunitprice is null\n"); * lineError.append("unitprice,"); error = true; } */ String unitprice1 = ""; if (override == false) { if (row.getCell(14) != null) { unitprice1 = row.getCell(14).toString(); } Double unitprice2 = getValidGallon(unitprice1); if (unitprice2 != null) { fuellog.setUnitprice(unitprice2); } else { lineError.append("Unit Price is blank,"); error = true; } } else { if (row.getCell(14) != null) { unitprice1 = row.getCell(14).toString(); } Double unitprice2 = getValidGallon(unitprice1); if (unitprice2 != null) { fuellog.setUnitprice(unitprice2); } else { } } // Gross Cost String grossamount1 = ""; if (override == false) { if (row.getCell(15) != null) { grossamount1 = row.getCell(15).toString(); } if (!(StringUtils.isEmpty(grossamount1))) { Double grossamount2 = getValidGallon(grossamount1); if (grossamount2 != null) { fuellog.setGrosscost(grossamount2); } else { lineError.append("Gross Cost,"); error = true; } } } else { if (row.getCell(15) != null) { grossamount1 = row.getCell(15).toString(); } Double grossamount2 = getValidGallon(grossamount1); if (grossamount2 != null) { fuellog.setGrosscost(grossamount2); } else { } } // Gross Cost // FOR FEES String fees1 = ""; if (override == false) { if (row.getCell(16) != null) { fees1 = row.getCell(16).toString(); } Double fees2 = getValidGallon(fees1); if (fees2 != null) { fuellog.setFees(fees2); } else { lineError.append("Fees is blank,"); error = true; } } else { if (row.getCell(16) != null) { fees1 = row.getCell(16).toString(); } Double fees2 = getValidGallon(fees1); if (fees2 != null) { fuellog.setFees(fees2); } else { } } // FOR DISCOUNTS String discount1 = ""; // System.out.println("\ndiscount1===>"+discount1+"\n"); if (override == false) { if (row.getCell(17) != null) { discount1 = row.getCell(17).toString(); } Double discount2 = getValidGallon(discount1); if (discount2 != null) { discount2 = Math.abs(discount2); fuellog.setDiscounts(discount2); } else { lineError.append("Discount is blank,"); error = true; } } else { if (row.getCell(17) != null) { discount1 = row.getCell(17).toString(); } Double discount2 = getValidGallon(discount1); if (discount2 != null) { discount2 = Math.abs(discount2); fuellog.setDiscounts(discount2); } else { } } // FOR AMOUNTS /* * String amount1=row.getCell(18).toString(); Double amount2 * = getValidGallon(amount1); if (amount2 != null){ * fuellog.setAmount(amount2); } else { * System.out.println("\namount2 is null\n"); * lineError.append("amount,"); error = true; } */ String amount1 = null; /* * try{ amount1=row.getCell(18).toString(); } catch * (Exception e) { lineError.append("amount,"); error = * true; } */ if (override == false) { try { amount1 = row.getCell(18).toString(); Double amount2 = getValidGallon(amount1); if (amount2 != null) { fuellog.setAmount(amount2); } else { lineError.append("Amount is blank,"); error = true; } } catch (Exception e) { lineError.append("Amount,"); error = true; } } else { if (row.getCell(18) != null) amount1 = row.getCell(18).toString(); Double amount2 = getValidGallon(amount1); if (amount2 != null) { fuellog.setAmount(amount2); } else { } } // CALCULATING DISCOUNT AND NET AMMOUNT IF FUELDISCOUNT // PERCENTAGE IS PRESENT if (override == false) { if (!error) { try { if (!StringUtils.isEmpty(grossamount1)) { criterias.clear(); criterias.put("name", Fname); FuelVendor vendor = genericDAO.getByCriteria(FuelVendor.class, criterias); if (vendor != null) { System.out.println("\nDiscount first\n"); criterias.clear(); criterias.put("fuelvendor.id", vendor.getId()); FuelDiscount fueldicount = genericDAO.getByCriteria(FuelDiscount.class, criterias); System.out.println("\nDiscount Second\n"); if (fueldicount != null) { double firstdiscountamount = fuellog.getDiscounts(); double seconddiscountpercentage = fueldicount .getFuelDiscountPercentage(); double seconddiscountAmount = 0.0; double totalDiscount = 0.0; Double grossamount = getValidGallon(grossamount1); if (grossamount != null) { if (grossamount != 0) { // System.out.println("\nFirst // grossamount--->"+grossamount+"\n"); grossamount = grossamount - firstdiscountamount; seconddiscountAmount = (grossamount * seconddiscountpercentage); grossamount = grossamount - seconddiscountAmount; totalDiscount = firstdiscountamount + seconddiscountAmount; totalDiscount = MathUtil.roundUp(totalDiscount, 2); grossamount = MathUtil.roundUp(grossamount, 2); fuellog.setDiscounts(totalDiscount); fuellog.setAmount(grossamount); /* * System.out.println( * "\nfirstdiscountamount==>" * +firstdiscountamount+"\n" * ); System.out.println( * "\nseconddiscountpercentage==>" * +seconddiscountpercentage * +"\n"); * System.out.println( * "\nseconddiscountAmount==>" * +seconddiscountAmount+ * "\n"); * System.out.println( * "\ntotalDiscount==>"+ * totalDiscount+"\n"); * System.out.println( * "\nsetAmount(grossamount)==>" * +grossamount+"\n"); */ } } } } } Double grossamount = getValidGallon(grossamount1); if (grossamount == null) { double discountAmount = fuellog.getDiscounts(); double feesAmount = fuellog.getFees(); // System.out.println("grossamount == // null"); Double NetAmount = getValidGallon(amount1); // System.out.println("NetAmount == // "+NetAmount+"\n"); if (discountAmount == 0 && feesAmount == 0) { // double // grossAmount=NetAmount+(discountAmount-feesAmount); fuellog.setGrosscost(NetAmount); } else { lineError.append("Discount and Fees should be zero,"); error = true; } } if (grossamount == 0) { double discountAmount = fuellog.getDiscounts(); double feesAmount = fuellog.getFees(); // System.out.println("grossamount == // null"); Double NetAmount = getValidGallon(amount1); // System.out.println("NetAmount == // "+NetAmount+"\n"); if (discountAmount == 0 && feesAmount == 0) { // double // grossAmount=NetAmount+(discountAmount-feesAmount); fuellog.setGrosscost(NetAmount); } else { lineError.append("Discount and Fees should be zero,"); error = true; } } } catch (Exception ex) { System.out.println("error calculating total discount"); } } } /// If override is true else { /* if (!error) { */ try { if (!StringUtils.isEmpty(grossamount1)) { criterias.clear(); criterias.put("name", Fname); FuelVendor vendor = genericDAO.getByCriteria(FuelVendor.class, criterias); if (vendor != null) { System.out.println("\nDiscount first\n"); criterias.clear(); criterias.put("fuelvendor.id", vendor.getId()); FuelDiscount fueldicount = genericDAO.getByCriteria(FuelDiscount.class, criterias); System.out.println("\nDiscount Second\n"); if (fueldicount != null) { double firstdiscountamount = fuellog.getDiscounts(); double seconddiscountpercentage = fueldicount.getFuelDiscountPercentage(); double seconddiscountAmount = 0.0; double totalDiscount = 0.0; Double grossamount = getValidGallon(grossamount1); if (grossamount != null) { if (grossamount != 0) { // System.out.println("\nFirst // grossamount--->"+grossamount+"\n"); grossamount = grossamount - firstdiscountamount; seconddiscountAmount = (grossamount * seconddiscountpercentage); grossamount = grossamount - seconddiscountAmount; totalDiscount = firstdiscountamount + seconddiscountAmount; totalDiscount = MathUtil.roundUp(totalDiscount, 2); grossamount = MathUtil.roundUp(grossamount, 2); fuellog.setDiscounts(totalDiscount); fuellog.setAmount(grossamount); /* * System.out.println( * "\nfirstdiscountamount==>"+ * firstdiscountamount+"\n"); * System.out.println( * "\nseconddiscountpercentage==>" * +seconddiscountpercentage+ * "\n"); System.out.println( * "\nseconddiscountAmount==>"+ * seconddiscountAmount+"\n"); * System.out.println( * "\ntotalDiscount==>"+ * totalDiscount+"\n"); * System.out.println( * "\nsetAmount(grossamount)==>" * +grossamount+"\n"); */ } } } } } Double grossamount = getValidGallon(grossamount1); if (grossamount == null) { /* * double discountAmount=fuellog.getDiscounts(); * double feesAmount=fuellog.getFees(); */ // System.out.println("grossamount == null"); Double NetAmount = getValidGallon(amount1); System.out.println("\ngrossamount == null when orride true\n"); System.out.println("\nNetAmount==>" + NetAmount + "\n"); // System.out.println("NetAmount == // "+NetAmount+"\n"); /* if(discountAmount ==0 && feesAmount ==0){ */ // double // grossAmount=NetAmount+(discountAmount-feesAmount); fuellog.setGrosscost(NetAmount); /* } */ /* * else{ lineError.append( * "discount and fees should be zero,"); error = * true; } */ } if (grossamount == 0) { /* * double discountAmount=fuellog.getDiscounts(); * double feesAmount=fuellog.getFees(); */ // System.out.println("grossamount == null"); Double NetAmount = getValidGallon(amount1); System.out.println("\ngrossamount == 0 when orride true\n"); System.out.println("\nNetAmount==>" + NetAmount + "\n"); // System.out.println("NetAmount == // "+NetAmount+"\n"); /* if(discountAmount ==0 && feesAmount ==0){ */ // double // grossAmount=NetAmount+(discountAmount-feesAmount); fuellog.setGrosscost(NetAmount); /* } */ /* * else{ lineError.append( * "discount and fees should be zero,"); error = * true; } */ } } catch (Exception ex) { System.out.println("exp--->" + ex + "\n"); System.out.println("error calculating total discount when override id true"); } // } } // END OF CELL if (override == false) { System.out.println("***** eneter here ok 0"); if (!error) { System.out.println("***** eneter here ok 1"); Map prop = new HashMap(); prop.put("fuelvendor", fuellog.getFuelvendor().getId()); prop.put("driversid", fuellog.getDriversid().getId()); prop.put("company", fuellog.getCompany().getId()); prop.put("terminal", fuellog.getTerminal().getId()); prop.put("state", fuellog.getState().getId()); prop.put("unit", fuellog.getUnit().getId()); prop.put("invoiceDate", dateFormat1.format(fuellog.getInvoiceDate())); prop.put("invoiceNo", fuellog.getInvoiceNo()); prop.put("transactiondate", dateFormat1.format(fuellog.getTransactiondate())); prop.put("transactiontime", fuellog.getTransactiontime()); if (fuellog.getFuelcard() != null) { prop.put("fuelcard", fuellog.getFuelcard().getId()); } prop.put("fueltype", fuellog.getFueltype()); prop.put("city", fuellog.getCity()); prop.put("gallons", fuellog.getGallons()); prop.put("unitprice", fuellog.getUnitprice()); prop.put("fees", fuellog.getFees()); prop.put("discounts", fuellog.getDiscounts()); prop.put("amount", fuellog.getAmount()); boolean rst = genericDAO.isUnique(FuelLog.class, fuellog, prop); System.out.println("***** eneter here ok 2" + rst); if (!rst) { System.out.println("***** eneter here ok 3"); lineError.append("Fuel log entry already exists(Duplicate),"); error = true; errorcount++; } if (fuellogs.contains(fuellog)) { lineError.append("Duplicate Fuel log record in file,"); error = true; errorcount++; } else { fuellogs.add(fuellog); } } else { errorcount++; } } else { if (!error) { fuellogs.add(fuellog); } else { errorcount++; } } } // TRY INSIDE WHILE(LOOP) catch (Exception ex) { ex.printStackTrace(); System.out.println("***** Entered here in exception" + ex.getMessage()); error = true; log.warn(ex); } if (lineError.length() > 0) { System.out.println("Error :" + lineError.toString()); list.add("Line " + count + ":" + lineError.toString() + "<br/>"); } System.out.println("Record No :" + count); count++; } // CLOSE while (rows.hasNext()) } // FIRST TRY catch (Exception e) { list.add("Not able to upload XL !!! please try again"); log.warn("Error in import Fuel log :" + e); e.printStackTrace(); } System.out.println("Done here.. " + errorcount); if (errorcount == 0) { System.out.println("Error count = 0"); for (FuelLog fuelog : fuellogs) { /*String ticktQuery = "select obj from Ticket obj where obj.driver=" + fuelog.getDriversid().getId() + " and obj.loadDate <='" + drvdf.format(fuelog.getTransactiondate()) + "' and obj.unloadDate>='" + drvdf.format(fuelog.getTransactiondate()) + "'";*/ String ticktQuery = "select obj from Ticket obj where obj.driver=" + fuelog.getDriversid().getId() + " and (obj.loadDate ='" + drvdf.format(fuelog.getTransactiondate()) + "' OR obj.unloadDate ='" + drvdf.format(fuelog.getTransactiondate()) + "')"; System.out.println("Fuel Log Violation query = " + ticktQuery); List<Ticket> tickObj = genericDAO.executeSimpleQuery(ticktQuery); if (tickObj.size() > 0 && tickObj != null) { fuelog.setFuelViolation("Not Violated"); } else { fuelog.setFuelViolation("Violated"); } Map criti = new HashMap(); criti.clear(); criti.put("id", fuelog.getDriversid().getId()); Driver drvOBj = genericDAO.getByCriteria(Driver.class, criti); if (drvOBj != null) fuelog.setDriverFullName(drvOBj.getFullName()); criti.clear(); criti.put("id", fuelog.getUnit().getId()); Vehicle vehObj = genericDAO.getByCriteria(Vehicle.class, criti); if (vehObj != null) fuelog.setUnitNum(vehObj.getUnitNum()); genericDAO.saveOrUpdate(fuelog); } } else { System.out.println("Line Error = " + list); } System.out.println("Returning list"); return list; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override @Transactional(readOnly = false, propagation = Propagation.REQUIRED) public List<String> importMainSheet(InputStream is) throws Exception { SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy"); // initializing the InputStream from a file using // POIFSFileSystem, before converting the result // into an HSSFWorkbook instance System.out.println("***** Here step 2"); HSSFWorkbook wb = null; StringBuffer buffer = null;/*ww w.j a v a 2s. c om*/ List<String> list = new ArrayList<String>(); List<Ticket> tickets = new ArrayList<Ticket>(); // List<String> emptydatalist=new ArrayList<String>(); int count = 1; int errorcount = 0; try { POIFSFileSystem fs = new POIFSFileSystem(is); ErrorData edata = new ErrorData(); // FileWriter writer = new FileWriter("e:/errordata.txt"); wb = new HSSFWorkbook(fs); // loop for every worksheet in the workbook int numOfSheets = wb.getNumberOfSheets(); Map criterias = new HashMap(); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; Ticket ticket = null; Iterator rows = sheet.rowIterator(); StringBuffer lineError; while (rows.hasNext()) { boolean error = false; buffer = new StringBuffer(); int cellCount = 0; row = (HSSFRow) rows.next(); if (count == 1) { count++; continue; } lineError = new StringBuffer(""); try { ticket = new Ticket(); ticket.setTicketStatus(1); ticket.setPayRollStatus(1); Object loadDateObj = getCellValue(row.getCell(0), true); if (loadDateObj == null) { error = true; lineError.append("Load Date,"); } else if (loadDateObj instanceof Date) { ticket.setLoadDate((Date) loadDateObj); } else { String loadDateStr = loadDateObj.toString(); loadDateStr = StringUtils.trimToEmpty(loadDateStr); Date loadDate = sdf.parse(loadDateStr); ticket.setLoadDate(loadDate); } /*try { Date loadDate = sdf.parse((String) getCellValue(row.getCell(0), true)); ticket.setLoadDate(loadDate); } catch (ParseException p) { error = true; lineError.append("Load Date,"); }*/ /*if (validDate(getCellValue(row.getCell(0)))) ticket.setLoadDate((Date) getCellValue(row.getCell(0))); else { error = true; lineError.append("Load Date,"); }*/ if (validTime((String) getCellValue(row.getCell(1)))) { StringBuilder timeIn = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(1)), 4, '0')); timeIn.insert(2, ':'); ticket.setTransferTimeIn(timeIn.toString()); } else { error = true; lineError.append("Transfer Time In,"); } if (validTime((String) getCellValue(row.getCell(2)))) { StringBuilder timeOut = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(2)), 4, '0')); timeOut.insert(2, ':'); ticket.setTransferTimeOut(timeOut.toString()); } else { error = true; lineError.append("Transfer Time Out,"); } try { criterias.clear(); criterias.put("type", 1); criterias.put("unit", Integer.parseInt((String) getCellValue(row.getCell(3)))); Vehicle vehicle = genericDAO.getByCriteria(Vehicle.class, criterias); if (vehicle == null) throw new Exception("no such truck"); else ticket.setVehicle(vehicle); } catch (Exception ex) { error = true; lineError.append("Truck,"); log.warn(ex.getMessage()); } try { criterias.clear(); criterias.put("type", 2); criterias.put("unit", Integer.parseInt((String) getCellValue(row.getCell(4)))); Vehicle vehicle = genericDAO.getByCriteria(Vehicle.class, criterias); if (vehicle == null) throw new Exception("no such trailer"); else ticket.setTrailer(vehicle); } catch (Exception ex) { error = true; lineError.append("Trailer,"); log.warn(ex.getMessage()); } Object unloadDateObj = getCellValue(row.getCell(5), true); if (unloadDateObj == null) { error = true; lineError.append("Unload Date,"); } else if (unloadDateObj instanceof Date) { ticket.setUnloadDate((Date) unloadDateObj); } else { String unloadDateStr = unloadDateObj.toString(); unloadDateStr = StringUtils.trimToEmpty(unloadDateStr); Date unloadDate = sdf.parse(unloadDateStr); ticket.setUnloadDate(unloadDate); } /*try { Date unloadDate = sdf.parse((String) getCellValue(row.getCell(5))); ticket.setUnloadDate(unloadDate); } catch (ParseException p) { error = true; lineError.append("Unload Date,"); }*/ /*Object unloadDate = getCellValue(row.getCell(5)); if (validDate(unloadDate)) ticket.setUnloadDate((Date) unloadDate); else { error = true; lineError.append("" + " Date,"); }*/ if (validTime((String) getCellValue(row.getCell(6)))) { StringBuilder timeIn = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(6)), 4, '0')); timeIn.insert(2, ':'); ticket.setLandfillTimeIn(timeIn.toString()); } else { error = true; lineError.append("Landfill Time In,"); } if (validTime((String) getCellValue(row.getCell(7)))) { StringBuilder timeOut = new StringBuilder( StringUtils.leftPad((String) getCellValue(row.getCell(7)), 4, '0')); timeOut.insert(2, ':'); ticket.setLandfillTimeOut(timeOut.toString()); } else { error = true; lineError.append("Landfill Time Out,"); } try { criterias.clear(); criterias.put("type", 1); criterias.put("name", (String) getCellValue(row.getCell(8))); Location location = genericDAO.getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such origin"); else ticket.setOrigin(location); } catch (Exception ex) { error = true; lineError.append("Origin,"); // log.warn(ex.getMessage()); } try { ticket.setOriginTicket(Long.parseLong((String) getCellValue(row.getCell(9)))); } catch (Exception ex) { error = true; lineError.append("Origin Ticket,"); } try { criterias.clear(); criterias.put("type", 2); criterias.put("name", (String) getCellValue(row.getCell(10))); Location location = genericDAO.getByCriteria(Location.class, criterias); if (location == null) throw new Exception("no such destination"); else ticket.setDestination(location); } catch (Exception ex) { error = true; lineError.append("Destination,"); // log.warn(ex.getMessage()); } // FOR CUSTOMER AND COMPANY_LACATION BillingRate billingRate = null; try { criterias.clear(); criterias.put("type", 1); criterias.put("name", (String) getCellValue(row.getCell(8))); Location originid = genericDAO.getByCriteria(Location.class, criterias); criterias.clear(); criterias.put("type", 2); criterias.put("name", (String) getCellValue(row.getCell(10))); Location destinationid = genericDAO.getByCriteria(Location.class, criterias); if (originid != null && destinationid != null) { // BillingRate billingRate = null; String query = "select obj from BillingRate obj where transferStation=" + originid.getId() + " and landfill=" + destinationid.getId(); List<BillingRate> rates = genericDAO.executeSimpleQuery(query); for (BillingRate rate : rates) { if (rate.getRateUsing() == null) { billingRate = rate; break; } else if (rate.getRateUsing() == 1) { // calculation for a load date if ((ticket.getLoadDate().getTime() >= rate.getValidFrom().getTime()) && (ticket.getLoadDate().getTime() <= rate.getValidTo().getTime())) { billingRate = rate; break; } } else if (rate.getRateUsing() == 2) { // calculation for a unload date if ((ticket.getUnloadDate().getTime() >= rate.getValidFrom().getTime()) && (ticket.getUnloadDate().getTime() <= rate.getValidTo().getTime())) { billingRate = rate; break; } } } if (billingRate != null) { ticket.setCompanyLocation((billingRate.getCompanyLocation() != null) ? billingRate.getCompanyLocation() : null); ticket.setCustomer( (billingRate.getCustomername() != null) ? billingRate.getCustomername() : null); } else { System.out.println("Customer and Company Location"); } /* * { error = true; lineError.append( * "Rate is expired for this origin and destination,please contact to administrator," * ); } */ } } catch (Exception ex) { System.out.println("Customer and Company Location"); log.warn(ex.getMessage()); } try { ticket.setDestinationTicket(Long.parseLong((String) getCellValue(row.getCell(11)))); } catch (Exception ex) { error = true; lineError.append("Destination Ticket,"); } if (ticket.getOrigin() != null) { if (reportService.checkDuplicate(ticket, "O")) { lineError.append("Duplicate Origin Ticket,"); error = true; } } if (ticket.getDestination() != null) { if (reportService.checkDuplicate(ticket, "D")) { lineError.append("Duplicates Dest. Ticket,"); error = true; } } if (ticket.getUnloadDate() != null && ticket.getLoadDate() != null) { if (ticket.getUnloadDate().before(ticket.getLoadDate())) { lineError.append("Unload Date is before Load Date,"); error = true; } } Double tgross = getValidAmount((String) getCellValue(row.getCell(12))); if (tgross != null) ticket.setTransferGross(tgross); else { error = true; lineError.append("Transfer Gross,"); } Double ttare = getValidAmount((String) getCellValue(row.getCell(13))); if (ttare != null) ticket.setTransferTare(ttare); else { lineError.append("Transfer Tare,"); error = true; } if (tgross != null && ttare != null) { ticket.setTransferNet(tgross - ttare); ticket.setTransferTons((tgross - ttare) / 2000); /* if(billingRate.getBilledby().equals("bygallon")){ */ // Change to 8.35 - 28th Dec 2016 ticket.setGallons(ticket.getTransferNet() / 8.35); // } } Double lgross = getValidAmount((String) getCellValue(row.getCell(16))); if (lgross != null) ticket.setLandfillGross(lgross); else { error = true; lineError.append("Landfill Gross,"); } Double ltare = getValidAmount((String) getCellValue(row.getCell(17))); if (ltare != null) ticket.setLandfillTare(ltare); else { lineError.append("Landfill Tare,"); error = true; } if (lgross != null && ltare != null) { ticket.setLandfillNet(lgross - ltare); ticket.setLandfillTons((lgross - ltare) / 2000); } String driverName = ((String) getCellValue(row.getCell(21))); Driver driver = null; try { if (StringUtils.isEmpty(driverName)) throw new Exception("Invalid driver"); else { // String[] names = driverName.split(" "); criterias.clear(); /* * criterias.put("firstName", names[1]); * criterias.put("lastName", names[0]); */ criterias.put("status", 1); criterias.put("fullName", driverName); driver = genericDAO.getByCriteria(Driver.class, criterias); if (driver == null) throw new Exception("Invalid driver"); ticket.setDriver(driver); // ticket.setDriverCompany(driver.getCompany()); } } catch (Exception ex) { error = true; lineError.append("Driver,"); } try { String employeeCompanyName = ((String) getCellValue(row.getCell(23))); if (StringUtils.isEmpty(employeeCompanyName)) throw new Exception("Invalid company"); else { criterias.clear(); criterias.put("status", 1); criterias.put("name", employeeCompanyName); Location employeeCompany = genericDAO.getByCriteria(Location.class, criterias); if (employeeCompany == null) throw new Exception("Invalid company"); ticket.setDriverCompany(employeeCompany); } } catch (Exception ex) { error = true; lineError.append("employee company,"); } String subcontractor = ((String) getCellValue(row.getCell(20))); try { if (!StringUtils.isEmpty(subcontractor)) { /* * if (driver != null && !"Subcontractor" * .equalsIgnoreCase(driver .getLastName().trim())) * { throw new Exception("Invalid subcontractor"); } * * * else { */ //String subcontractorMod = subcontractor.replace("&", "\"&\""); criterias.clear(); criterias.put("name", subcontractor); SubContractor contractor = genericDAO.getByCriteria(SubContractor.class, criterias); if (contractor == null) { throw new Exception("Invalid subcontractor"); } else { ticket.setSubcontractor(contractor); } criterias.clear(); } // } } catch (Exception ex) { error = true; lineError.append("Sub Contractor,"); } Object billbatchDateObj = getCellValue(row.getCell(22), true); if (billbatchDateObj == null) { error = true; lineError.append("Bill Batch Date,"); } else if (billbatchDateObj instanceof Date) { ticket.setBillBatch((Date) billbatchDateObj); } else { String billbatchDateStr = billbatchDateObj.toString(); billbatchDateStr = StringUtils.trimToEmpty(billbatchDateStr); Date billbatchDate = sdf.parse(billbatchDateStr); ticket.setBillBatch(billbatchDate); } /*try { Date billBatch = sdf.parse((String) getCellValue(row.getCell(22))); ticket.setBillBatch(billBatch); } catch (ParseException p) { error = true; lineError.append("Batch Date,"); }*/ /*Object billBatch = getCellValue(row.getCell(22)); if (validDate(billBatch)) ticket.setBillBatch((Date) billBatch); else { error = true; lineError.append("Batch Date,"); }*/ try { criterias.clear(); String locCode = (String) getCellValue(row.getCell(24)); if (StringUtils.isEmpty(locCode)) throw new Exception("Invalid terminal"); else { criterias.put("code", locCode); criterias.put("type", 4); } Location location = genericDAO.getByCriteria(Location.class, criterias); if (location == null) { throw new Exception("no such terminal"); } else { criterias.clear(); criterias.put("status", 1); criterias.put("fullName", driverName); criterias.put("terminal", location); Driver driverobj = genericDAO.getByCriteria(Driver.class, criterias); if (driverobj == null) { throw new Exception("Terminal does not match with driver"); } else { ticket.setTerminal(location); } } } catch (Exception ex) { error = true; lineError.append("Terminal does not match with driver,"); log.warn(ex.getMessage()); } try { User user = genericDAO.getByUniqueAttribute(User.class, "username", (String) getCellValue(row.getCell(25))); if (user == null) { throw new Exception("Invalid user"); } else { ticket.setCreatedBy(user.getId()); ticket.setEnteredBy(user.getName()); } } catch (Exception ex) { error = true; lineError.append("User,"); } if (!error) { if (tickets.contains(ticket)) { lineError.append("Duplicate Ticket,"); error = true; errorcount++; } else { tickets.add(ticket); //genericDAO.saveOrUpdate(ticket); } } else { errorcount++; } } catch (Exception ex) { error = true; log.warn(ex); } if (lineError.length() > 0) { System.out.println("Error :" + lineError.toString()); list.add("Line " + count + ":" + lineError.toString() + "<br/>"); } System.out.println("Record No :" + count); count++; } } catch (Exception e) { log.warn("Error in import customer :" + e); } if (errorcount == 0) { for (Ticket ticket : tickets) { genericDAO.saveOrUpdate(ticket); } } return list; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@SuppressWarnings({ "rawtypes", "unchecked" })
@Override/*from w w w . j a va 2 s . c om*/
@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
public List<String> importVehiclePermitMainSheet(InputStream is) {
List<String> list = new ArrayList<String>();
List<VehiclePermit> vehiclePermits = new ArrayList<VehiclePermit>();
int errorcount = 0;
HSSFWorkbook wb = null;
try {
POIFSFileSystem fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
Map criterias = new HashMap();
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
Iterator rows = sheet.rowIterator();
VehiclePermit vehiclePermit = null;
int count = 1;
while (rows.hasNext()) {
row = (HSSFRow) rows.next();
if (count == 1) {
count++;
continue;
}
boolean error = false;
StringBuffer lineError = new StringBuffer();
vehiclePermit = new VehiclePermit();
String unitNum = "";
try {
unitNum = (String) getCellValue(row.getCell(0));
if (!StringUtils.isEmpty(unitNum)) {
criterias.clear();
criterias.put("unitNum", unitNum);
Vehicle vehicle = genericDAO.getByCriteria(Vehicle.class, criterias);
if (vehicle == null) {
error = true;
lineError.append("Invalid Vehicle Number, ");
} else {
vehiclePermit.setVehicle(vehicle);
}
} else {
error = true;
lineError.append("Vehicle Number is Empty, ");
}
} catch (Exception e) {
e.printStackTrace();
error = true;
lineError.append("Unit,");
log.warn(e.getMessage());
}
String company = "";
try {
company = (String) getCellValue(row.getCell(1));
if (!StringUtils.isEmpty(company)) {
criterias.clear();
criterias.put("name", company);
criterias.put("type", 3);
Location location = genericDAO.getByCriteria(Location.class, criterias);
if (location == null) {
error = true;
lineError.append("Invalid Company Name, ");
} else {
vehiclePermit.setCompanyLocation(location);
}
} else {
error = true;
lineError.append("Company is Empty, ");
}
} catch (Exception e) {
e.printStackTrace();
error = true;
lineError.append("Company, ");
log.warn(e.getMessage());
}
String permitType = "";
try {
permitType = (String) getCellValue(row.getCell(2));
if (!StringUtils.isEmpty(permitType)) {
criterias.clear();
criterias.put("dataType", "PERMIT_TYPE");
criterias.put("dataLabel", permitType);
SetupData setUpdata = genericDAO.getByCriteria(SetupData.class, criterias);
if (setUpdata == null) {
error = true;
lineError.append("Permit Type doesn't exists,");
} else {
SetupData data = genericDAO.getById(SetupData.class, setUpdata.getId());
vehiclePermit.setPermitType(data);
}
} else {
error = true;
lineError.append("Permit Type is Empty, ");
}
} catch (Exception e) {
e.printStackTrace();
error = true;
lineError.append("Permit Type, ");
log.warn(e.getMessage());
}
String permitNumber = "";
try {
permitNumber = (String) getCellValue(row.getCell(3));
vehiclePermit.setPermitNumber(permitNumber);
} catch (Exception e) {
e.printStackTrace();
error = true;
lineError.append("Permit Number, ");
log.warn(e.getMessage());
}
Date date4 = row.getCell(4).getDateCellValue();
try {
if (validDate(date4)) {
vehiclePermit.setIssueDate(dateFormat1.parse(dateFormat1.format(date4)));
} else {
error = true;
lineError.append("Invalid Effective date, ");
}
} catch (Exception e) {
e.printStackTrace();
error = true;
lineError.append("Invalid Effective Date, ");
log.warn(e.getMessage());
}
Date date5 = row.getCell(5).getDateCellValue();
try {
if (validDate(date5)) {
vehiclePermit.setExpirationDate(dateFormat1.parse(dateFormat1.format(date5)));
} else {
error = true;
lineError.append("Invalid Expiration Date");
}
} catch (Exception e) {
error = true;
e.printStackTrace();
lineError.append("Invalid Expiration Date, ");
log.warn(e.getMessage());
}
if (!error) {
java.sql.Date issueDate = new java.sql.Date(vehiclePermit.getIssueDate().getTime());
java.sql.Date expirationDate = new java.sql.Date(vehiclePermit.getExpirationDate().getTime());
criterias.clear();
criterias.put("dataType", "PERMIT_TYPE");
criterias.put("dataLabel", permitType);
SetupData setupData = genericDAO.getByCriteria(SetupData.class, criterias);
String query;
try {
query = "select obj from VehiclePermit obj " + " where obj.vehicle.unitNum='" + unitNum
+ "'" + " and obj.companyLocation.name='" + company + "' "
+ " and obj.companyLocation.type=3 " + " and obj.permitType=" + setupData.getId()
+ " " + " and (('" + issueDate
+ " 00:00:00' BETWEEN obj.issueDate and obj.expirationDate)" + " or ('"
+ expirationDate + " 00:00:00' BETWEEN obj.issueDate and obj.expirationDate)"
+ " or obj.issueDate>='" + issueDate + " 00:00:00' AND obj.expirationDate<='"
+ expirationDate + " 00:00:00')";
// +" and obj.issueDate='"+dateFormat1.format(date4)+"'
// "
// +" and
// obj.expirationDate='"+dateFormat1.format(date5)+"'";
List<VehiclePermit> duplicateCheck = genericDAO.executeSimpleQuery(query);
if (duplicateCheck != null && duplicateCheck.size() > 0) {
lineError.append("Vehicle Permit Already exists for specified date range, ");
errorcount++;
}
/*
* else{
*
* genericDAO.save(vehiclePermit); }
*/
} catch (Exception e) {
e.printStackTrace();
}
vehiclePermits.add(vehiclePermit);
} else {
errorcount++;
}
if (lineError.length() > 0) {
list.add("Line " + count + ":" + lineError.toString() + "<br/>");
}
count++;
}
} catch (Exception e) {
e.printStackTrace();
list.add("Not able to upload the file, please try again");
}
if (errorcount == 0) {
for (VehiclePermit vPermit : vehiclePermits) {
genericDAO.save(vPermit);
}
}
return list;
}