List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
private Location retrieveOriginForWM(HSSFSheet sheet) { int originRowNo = 2; HSSFRow originRow = sheet.getRow(originRowNo); String originName = ((String) getCellValue(originRow.getCell(0))); originName = StringUtils.trimToEmpty(originName); if (StringUtils.isEmpty(originName)) { return null; }/*from w ww .j a va 2s .c o m*/ List<WMLocation> wmLocationList = retrieveWMLocationByName(originName, 1); if (wmLocationList == null || wmLocationList.isEmpty()) { return null; } else { return wmLocationList.get(0).getLocation(); } /*if (StringUtils.contains(originName, Location.FORGE_TRANSFER_STATION)) { originName = Location.FORGE_TRANSFER_STATION; } else if (StringUtils.contains(originName, Location.PHILADELPHIA_TRANSFER_STATION)) { originName = Location.PHILADELPHIA_TRANSFER_STATION; } else if (StringUtils.contains(originName, Location.BQE_TRANSFER_STATION)) { originName = Location.BQE_TRANSFER_STATION; } else if (StringUtils.contains(originName, Location.VARICK_I_TRANSFER_STATION)) { originName = Location.VARICK_I_TRANSFER_STATION; } else if (StringUtils.contains(originName, Location.YONKERS_TRANSFER_STATION)) { originName = Location.YONKERS_TRANSFER_STATION; } else if (StringUtils.contains(originName, Location.WAVERLY_TRANSFER_STATION)) { originName = Location.WAVERLY_TRANSFER_STATION; } List<Location> originList = retrieveLocationDataByLongName(1, originName); if (originList == null || originList.isEmpty()) { return null; } else { return originList.get(0); }*/ }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
private Location retrieveDestinationForWM(HSSFSheet sheet) { HSSFRow destinationRow = sheet.getRow(1); String destinationName = ((String) getCellValue(destinationRow.getCell(0))); destinationName = StringUtils.trimToEmpty(destinationName); if (StringUtils.isEmpty(destinationName)) { return null; }/*from w w w .j a v a 2 s .co m*/ List<Location> destinationList = retrieveLocationDataByLongName(2, destinationName); if (destinationList == null || destinationList.isEmpty()) { return null; } else { return destinationList.get(0); } }
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 ww w .j ava2s . co m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); /*if (recordCount < 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);//from w w w .j a v a 2 s .co m 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 {/*w ww . ja va 2 s .c o m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); recordCount++; System.out.println("Processing record No: " + recordCount); if (recordCount == 1) { continue; } boolean recordError = false; boolean fatalRecordError = false; StringBuffer recordErrorMsg = new StringBuffer(); 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;//from www.ja v a 2s . co m StringBuffer buffer = null; 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
private void processSubContractor(HSSFRow row, FuelLog fuelLog) { String driverLastName = ((String) getCellValue(row.getCell(7))); String driverFirstName = ((String) getCellValue(row.getCell(8))); if (StringUtils.isEmpty(driverLastName) && StringUtils.isEmpty(driverFirstName)) { return;//ww w . ja v a2s . co m } Map criterias = new HashMap(); List<Driver> driversList = getDriversFromName(criterias, driverLastName, driverFirstName); if (!driversList.isEmpty()) { return; } SubContractor subContractor = getSubcontractorObjectFromName(driverLastName, driverFirstName); if (subContractor == null) { return; } fuelLog.setSubContractor(subContractor); row.getCell(7).setCellValue("Subcontractor"); // Last name row.getCell(8).setCellValue("Subcontractor"); // First name row.getCell(6).setCellValue("0"); // Unit # }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@SuppressWarnings("unchecked") @Override//from w w w . j a va 2 s.c om @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
private String validateAndResetUnitNumber(Map criterias, HSSFRow row) { String unit = (String) getCellValue(row.getCell(6)); if (StringUtils.isEmpty(unit)) { return StringUtils.EMPTY; }//from w ww .ja v a 2s. c o m 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(unit) + " 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("User given unit number " + unit + " is not valid, returning EMPTY"); return StringUtils.EMPTY; } else { System.out.println("User given unit number " + unit + " is valid"); return unit; } }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
private boolean setUnitNumberInFuelLogRefactored(Map criterias, HSSFRow row, FuelLog fuellog, StringBuffer lineError, String unit) { boolean isError = false; try {//from w w w . java 2 s. c o m String lastName = ((String) getCellValue(row.getCell(7))); String firstName = ((String) getCellValue(row.getCell(8))); // Unit is EMPTY, Driver Name is EMPTY if (StringUtils.isEmpty(unit) && StringUtils.isEmpty(lastName) && StringUtils.isEmpty(firstName)) { isError = true; lineError.append("Unit is empty, Driver is empty"); return isError; } // if unit number is NOT EMPTY if (!StringUtils.isEmpty(unit)) { String transdate = getTransactionDateFromExcel(row, 4); if (!setVehicleInFuelLogFromUnitNumber(row, transdate, fuellog)) { isError = true; lineError.append("no such Vehicle,"); return isError; } // else : able to set the unit number, proceed down to set driver } // else : derive unit number using driver, so first proceed down to set driver // If driverName is NOT EMPTY isError = setDriverAndOrUnitInFuelLog(criterias, row, fuellog, lineError, lastName, firstName, unit); /*if (StringUtils.isEmpty(unit)) { isError = true; lineError.append("Unit is blank, No matching Ticket, Fuel Log, Odometer entry for given driver"); return isError; } */ } catch (Exception ex) { isError = true; lineError.append("Unit,"); log.warn(ex.getMessage()); } return isError; }