List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
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;/* w ww . j a v a 2s .co 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 w ww . j av a 2 s . c o m @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;//w w w. j a va2 s . co m 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.report.excel.ExcelToHtmlConverter.java
License:Apache License
public void processWorkbook(HSSFWorkbook workbook) { evaluator = workbook.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = workbook.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); }//from w w w .j a v a2 s.com } } } final SummaryInformation summaryInformation = workbook.getSummaryInformation(); if (summaryInformation != null) { processDocumentInformation(summaryInformation); } if (isUseDivsToSpan()) { // prepare CSS classes for later usage this.cssClassContainerCell = htmlDocumentFacade.getOrCreateCssClass(cssClassPrefixCell, "padding:0;margin:0;align:left;vertical-align:top;"); this.cssClassContainerDiv = htmlDocumentFacade.getOrCreateCssClass(cssClassPrefixDiv, "position:relative;"); } for (int s = 0; s < workbook.getNumberOfSheets(); s++) { HSSFSheet sheet = workbook.getSheetAt(s); processSheet(sheet); } htmlDocumentFacade.updateStylesheet(); }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembersByExcel(InputStream is) throws Exception { // TODO Auto-generated method stub HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<Member> members = new ArrayList<Member>(); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; }//from w ww.j a v a 2 s . c o m int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); Member member = new Member(); String username = null; String mobile = null; String realName = null; for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (i == 0) { // ?? username = memberCell.toString(); } if (i == 1) { // mobile = memberCell.toString(); } if (i == 2) { // ?? realName = memberCell.toString(); } } // ? if (username != null) { member.setUsername(username); } else { String errorString = "" + rowNum + "????"; return null; } if (mobile != null) { member.setMobile(mobile); } else { String errorString = "" + rowNum + "???"; return null; } if (realName != null) { member.setRealName(realName); } else { String errorString = "" + rowNum + "????"; return null; } members.add(member); } } return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start/*from w w w.ja va 2 s . c o m*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.setIsUpdate(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getTeachers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start// w w w .jav a 2s. co m for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.s(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } if (i == 2) { // ??-? String classTeacherMap = memberCell.toString(); classTeacherMap = classTeacherMap.trim(); member.setAddress(classTeacherMap); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<DictStudent> getDictStudents(InputStream inputStream, DictSchool dictSchool, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<DictStudent> dictStudents = new ArrayList<DictStudent>(); // start--/* w w w. j a v a2 s . com*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow studentInfo = hssfSheet.getRow(rowNum); if (studentInfo == null) { continue; } DictStudent dictStudent = new DictStudent(); dictStudent.setCreateDate(new Date()); dictStudent.setModifyDate(new Date()); dictStudent.setStudentStatus(StudentStatus.active); String studentName = null; String studentNo = null; DictClass dictClass = new DictClass(); // start_?? for (int i = 0; i < studentInfo.getLastCellNum(); i++) { HSSFCell studentCell = studentInfo.getCell(i); if (studentCell == null) { continue; } if (i == 0) { // ? studentNo = studentCell.toString(); studentNo = studentNo.trim(); dictStudent.setStudentNo(studentNo); } if (i == 1) { // ?? studentName = studentCell.toString(); studentName = studentName.trim(); dictStudent.setStudentName(studentName); } if (i == 2) { // ? String className = studentCell.toString(); className = className.trim(); List<DictClass> classes = dictClassDao.getClassByName(className, dictSchool); if (classes != null) { if (classes.size() > 0) { dictClass = classes.get(0); dictStudent.setDictClass(dictClass); } } } if (i == 3) { // ? String memberString = studentCell.toString(); memberString = memberString.trim(); memberString = memberString.replaceAll("", ","); dictStudent.setStuRmark(memberString); } } // end___?? dictStudents.add(dictStudent); } } // end return dictStudents; }
From source file:com.servoy.extensions.plugins.excelxport.ImportSelectSheetPanel.java
License:Open Source License
public Runnable needsToRunFirst(final boolean forward) { return new Runnable() { public void run() { try { parent.blockGUI(Messages.getString("servoy.plugin.import.status.organizingData")); //$NON-NLS-1$ if (forward) { HSSFWorkbook wb = (HSSFWorkbook) state.getProperty("workbook"); //$NON-NLS-1$ SortedComboModel dcm = new SortedComboModel(StringComparator.INSTANCE); for (int i = 0; i < wb.getNumberOfSheets(); i++) { dcm.add(wb.getSheetName(i)); }// w ww.j a v a 2s . c o m sheetSelect.setModel(dcm); HSSFSheet sheet = wb.getSheetAt(0);//take first default tableModel = new SheetTableModel(sheet); tableModel.setUseHeaderRow(headerRows.isSelected()); table.setModel(tableModel); } } finally { parent.releaseGUI(); } } }; }
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
public void processWorkbook(HSSFWorkbook workbook) { final SummaryInformation summaryInformation = workbook.getSummaryInformation(); if (summaryInformation != null) { this.processDocumentInformation(summaryInformation); }/*from w ww . ja v a 2s. c om*/ if (this.isUseDivsToSpan()) { // prepare CSS classes for later usage this.cssClassContainerCell = this.htmlDocumentFacade.getOrCreateCssClass(this.cssClassPrefixCell, "padding:0;margin:0;align:left;vertical-align:top;"); this.cssClassContainerDiv = this.htmlDocumentFacade.getOrCreateCssClass(this.cssClassPrefixDiv, "position:relative;"); } for (int s = 0; s < workbook.getNumberOfSheets(); s++) { HSSFSheet sheet = workbook.getSheetAt(s); this.processSheet(sheet); } this.htmlDocumentFacade.updateStylesheet(); }