Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package opn.greenwebs; import com.model.Customer; import com.model.CustomerDB; import com.model.Item; import com.model.ItemDB; import com.model.Order; import com.model.Quote; import com.model.QuoteDB; import com.model.Stock; import com.model.StockDB; import java.awt.Desktop; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FilenameFilter; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URI; import java.net.URISyntaxException; import java.net.URL; import java.net.URLEncoder; import java.nio.file.Files; import java.nio.file.StandardCopyOption; import java.time.Instant; import java.time.LocalDate; import java.time.ZoneId; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Optional; import java.util.ResourceBundle; import java.util.logging.FileHandler; import java.util.logging.Level; import java.util.logging.Logger; import java.util.logging.SimpleFormatter; import javafx.beans.property.SimpleDoubleProperty; import javafx.beans.property.SimpleObjectProperty; import javafx.beans.value.ObservableValue; import javafx.collections.FXCollections; import javafx.collections.ObservableList; import javafx.event.ActionEvent; import javafx.event.Event; import javafx.fxml.FXML; import javafx.fxml.Initializable; import javafx.scene.control.Alert; import javafx.scene.control.Alert.AlertType; import javafx.scene.control.Button; import javafx.scene.control.ButtonType; import javafx.scene.control.CheckBox; import javafx.scene.control.ChoiceBox; import javafx.scene.control.ComboBox; import javafx.scene.control.ContextMenu; import javafx.scene.control.DatePicker; import javafx.scene.control.MenuItem; import javafx.scene.control.ProgressBar; import javafx.scene.control.Tab; import javafx.scene.control.TabPane; import javafx.scene.control.TableCell; import javafx.scene.control.TableColumn; import javafx.scene.control.TableColumn.CellEditEvent; import javafx.scene.control.TableView; import javafx.scene.control.TextField; import javafx.scene.control.TextInputDialog; import javafx.scene.control.cell.ComboBoxTableCell; import javafx.scene.control.cell.PropertyValueFactory; import javafx.scene.control.cell.TextFieldTableCell; import javafx.scene.input.ContextMenuEvent; import javafx.scene.input.KeyEvent; import javafx.scene.input.MouseButton; import javafx.scene.input.MouseEvent; import javafx.util.converter.DefaultStringConverter; import javafx.util.converter.DoubleStringConverter; import net.sourceforge.barbecue.Barcode; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFTable; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author Brian */ public class FXMLDocumentController implements Initializable { private AccessData aData = null; private AccessDataCustomer aDataCust = null; private AccessDataStock aDataStock = null; private AccessDataQuote aDatQuote = null; private static boolean bIsGood = false; static Logger logger; List<StockDB> lstStock; Stock stock = null; JRegSettings jReg = new JRegSettings(); @FXML DatePicker dteOrderDate; @FXML ComboBox cmbSales; @FXML ChoiceBox<String> chbSupplier; @FXML TextField txtCustomer, txtAddress, txtCity, txtProvince, txtPhone, txtContact, txtPO, txtQty, txtMfr, txtSKU, txtDescrip, txtSupplier, txtSupPart, txtSerial, txtUnitPrice, txtEOS, txtFax, txtEmail, txtUPC; @FXML TableView<Item> tblItems, tblParts; @FXML TableView<Customer> tblCust; @FXML TableView<Quote> tblQuotes; @FXML TableView<Order> tblOrder; @FXML TableView<Stock> tblStock; @FXML TableColumn<Item, String> tcMfr, tcSKU, tcDescrip, tcSupplier, tcSupPart, tcSerial, tcMfr1, tcSKU1, tcDescrip1, tcSupplier1, tcSupPart1, tcUPC1; @FXML TableColumn<Item, Double> tcQty, tcQty1, tcSalePrice, tcSalePrice1, tcEOS, tcEOS1; @FXML TableColumn<Customer, String> tcCustomer, tcAddress, tcCity, tcProvince, tcEmail, tcPO, tcPhone, tcFax, tcContact; @FXML TableColumn<Quote, String> tcStatus, tcCust, tcCustPhone, tcQuote; @FXML TableColumn<Quote, Object> tcDate, tcDate2; @FXML TableColumn<Stock, Object> tcStockDate; @FXML TableColumn<Stock, String> tcStock, tcStockStat; @FXML CheckBox chkGST, chkPST; @FXML ProgressBar pgbLoaded; @FXML TabPane tpnAll; @FXML Tab tabQuote, tabParts, tabStock; private final ObservableList<Item> ItemData = FXCollections.observableArrayList(); private final ObservableList<Item> PartsData = FXCollections.observableArrayList(); private final ObservableList<Customer> CustData = FXCollections.observableArrayList(); private final ObservableList<Quote> QuoteData = FXCollections.observableArrayList(); private final ObservableList<Stock> StockData = FXCollections.observableArrayList(); ObservableList<String> options = FXCollections.observableArrayList("CherylD", "BrianG", "BryanH", "BrianB", "MelM"); ObservableList<String> cbValues = FXCollections.observableArrayList("SYNNEX", "INGRAM", "TOMAURI", "TECHDATA", "D&H", "XCEL"); ObservableList<String> cbStatus = FXCollections.observableArrayList("Created", "Faxed", "Emailed", "Ordered", "Cancelled", "Recieved"); File fUserDir = new File(System.getProperty("user.home"), "Documents"); File fDataFiles = new File(fUserDir, "\\JFXWilsons\\datafiles"); File fStockDir = new File(fUserDir, "STOCK"); File fQuoteDir = new File(fUserDir, "QUOTES"); SimpleDoubleProperty sdpPro = null; double dFcount = 1; @FXML private void handleCustClick(MouseEvent event) { ContextMenu context = new ContextMenu(); MenuItem mnuDelete = new MenuItem("Delete"); MenuItem mnuUpdate = new MenuItem("Update"); context.getItems().add(mnuDelete); context.getItems().add(mnuUpdate); tblCust.setContextMenu(context); MouseButton butt = event.getButton(); if (butt == MouseButton.PRIMARY) { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Customer) { Customer cust = (Customer) view.getSelectionModel().getSelectedItem(); txtCustomer.setText(cust.getSspCustomer()); txtAddress.setText(cust.getSspAddress()); txtCity.setText(cust.getSspCity()); txtProvince.setText(cust.getSspProvince()); txtPhone.setText(cust.getSspPhone()); txtFax.setText(cust.getSspFax()); txtEmail.setText(cust.getSspEmail()); txtContact.setText(cust.getSspContact()); txtPO.setText(cust.getSspPO()); chkGST.setSelected(cust.isSbpGST()); chkPST.setSelected(cust.isSbpPST()); } } } else if (butt == MouseButton.SECONDARY) { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Customer) { Customer cust = (Customer) view.getSelectionModel().getSelectedItem(); mnuDelete.setOnAction((ActionEvent event1) -> { aDataCust.delete(cust.createCustomerDB()); CustData.remove(cust); }); mnuUpdate.setOnAction((ActionEvent event2) -> { CustomerDB oldCust = cust.createCustomerDB(); cust.setSspCustomer(txtCustomer.getText()); txtCustomer.setText(""); cust.setSspAddress(txtAddress.getText()); txtAddress.setText(""); cust.setSspCity(txtCity.getText()); txtCity.setText(""); cust.setSspProvince(txtProvince.getText()); txtProvince.setText(""); cust.setSspPhone(txtPhone.getText()); txtPhone.setText(""); cust.setSspFax(txtFax.getText()); txtFax.setText(""); cust.setSspEmail(txtEmail.getText()); txtEmail.setText(""); cust.setSspContact(txtContact.getText()); txtContact.setText(""); cust.setSspPO(txtPO.getText()); txtPO.setText(""); cust.setSbpGST(chkGST.isSelected()); chkGST.setSelected(false); cust.setSbpPST(chkPST.isSelected()); chkPST.setSelected(false); aDataCust.updateCustomer(oldCust, cust.createCustomerDB()); }); } } } } @FXML private void handlePrint(ActionEvent event) { Barcode barcode = null; } @FXML private void handleTableClick(MouseEvent event) { ContextMenu context = new ContextMenu(); MenuItem mnuDelete = new MenuItem("Delete"); context.getItems().add(mnuDelete); mnuDelete.setOnAction((ActionEvent event1) -> { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Item) { Item item = (Item) view.getSelectionModel().getSelectedItem(); ItemData.remove(item); } } }); tblItems.setContextMenu(context); } @FXML private void handleContextMenu(ContextMenuEvent event) { System.out.println("Weeee"); } @FXML private void handleStockClick(MouseEvent event) { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Stock) { File fObjDataStock = new File(fDataFiles, "stock.odb"); aDataStock = new AccessDataStock(fObjDataStock); stock = (Stock) view.getSelectionModel().getSelectedItem(); File fStocked = new File(fStockDir, stock.getSspFStock()); if (fStocked.exists()) { if (event.getClickCount() == 2) { try { Desktop.getDesktop().open(fStocked); } catch (IOException ex) { logger.log(Level.INFO, "STOCKCLICK we have an error{0}", ex.getMessage()); } } else { List<ItemDB> lstFile = createListFromTable(fStocked); logger.log(Level.SEVERE, "STOCKCLICK what is the size {0}", lstFile.size()); ItemData.clear(); new Thread() { @Override public void run() { List<StockDB> lstStocked = aDataStock.query("strFStock", stock.getSspFStock()); if (lstStocked.isEmpty()) { lstFile.stream().forEach(item -> { logger.info("STOCKCLICK in the stream"); StockDB stockdb = new StockDB(); stockdb.setDated(stock.getSopDate()); logger.log(Level.INFO, "STOCKCLICK the item info is {0}", item.getDblQty()); stockdb.setDblQtyOrd(item.getDblQty()); stockdb.setDblQtyRec(0); stockdb.setStrFStock(stock.getSspFStock()); stockdb.setStrSupPart(item.getStrSupPart()); item.setDblQty(0); logger.log(Level.SEVERE, "STOCKCLICK we set the item quantity {0}", stockdb.toString()); aDataStock.addStock(stockdb); ItemData.add(item.createItem()); }); } else { logger.log(Level.SEVERE, "STOCKCLICK we have the object in the database already {0}", lstStocked.get(0).toString()); lstStocked.stream().forEach(stockdbb -> { lstFile.stream().forEach(itemm -> { if (stockdbb.getStrSupPart().equalsIgnoreCase(itemm.getStrSupPart())) { //logger.log(Level.WARNING, "good stuff {0}", itemm.toString()); logger.log(Level.WARNING, "STOCKCLICK stock Ordered {0} and stock received {1}", new Object[] { stockdbb.getDblQtyOrd(), stockdbb.getDblQtyRec() }); itemm.setDblQty(stockdbb.getDblQtyOrd() - stockdbb.getDblQtyRec()); if (stockdbb.getDblQtyOrd() > stockdbb.getDblQtyRec()) { logger.log(Level.SEVERE, "STOCKCLICK we did not receive all we ordered of {0}", itemm.getStrDescrip()); } ItemData.add(itemm.createItem()); } else { logger.log(Level.SEVERE, "STOCKCLICK so the item does not equal"); } }); }); } //here } }.start(); } } else { logger.info("STOCKCLICK it doesn't exist"); } } } } @FXML private void handleQuoteClick(MouseEvent event) { MouseButton butt = event.getButton(); if (butt == MouseButton.PRIMARY) { if (event.getClickCount() == 2) { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Quote) { Quote quote = (Quote) view.getSelectionModel().getSelectedItem(); File[] fileList = fQuoteDir .listFiles((File file) -> file.getName().startsWith(quote.getSspQuote())); if (fileList.length > 0) { try { Desktop.getDesktop().open(fileList[0]); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } } } } else if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Quote) { Quote quote = (Quote) view.getSelectionModel().getSelectedItem(); txtCustomer.setText(quote.getSspCustomer()); txtPhone.setText(quote.getSspPhone()); File[] fileList = fQuoteDir .listFiles((File file) -> file.getName().startsWith(quote.getSspQuote())); if (fileList.length > 0) { System.out.println("the filelist is bigger than 0"); try (FileInputStream fis = new FileInputStream(fileList[0]); XSSFWorkbook book = new XSSFWorkbook(fis)) { ItemData.clear(); XSSFSheet sheet = book.getSheet("Digital Version"); Iterator<Row> itr = sheet.rowIterator(); int nRow = 0; mainWhile: while (itr.hasNext()) { Row row = itr.next(); nRow++; if (nRow >= 21) { Iterator<Cell> cellIterator = row.cellIterator(); List lstItem = new ArrayList(); int nCell = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: lstItem.add(""); if (nCell == 0) { System.out.println("Quote Qty found a blank"); break mainWhile; } break; case Cell.CELL_TYPE_FORMULA: lstItem.add(cell.getCellFormula()); break; default: } nCell++; } insertData(lstItem, false); } else if (nRow < 21) { /* getCellData(file, row, cell); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); inject(wb, Date.from(instant), 3, 14); inject(wb, txtCustomer.getText(), 10, 2); inject(wb, txtAddress.getText(), 11, 2); inject(wb, txtCity.getText(), 12, 2); inject(wb, txtProvince.getText(), 13, 2); inject(wb, txtPhone.getText(), 14, 2); inject(wb, txtContact.getText(), 15, 2); inject(wb, txtFax.getText(), 14, 4); inject(wb, txtEmail.getText(), 16, 2); inject(wb, txtPO.getText(), 15, 4); if (chkGST.isSelected()) { inject(wb, "Y", 36, 8); } else { inject(wb, "N", 36, 8); } if (chkPST.isSelected()) { inject(wb, "Y", 37, 8); } else { inject(wb, "N", 37, 8); Data starts at row 21 Qty, Mfr, Sku, Descrip, Supplier, Suppart,Serial,SalePrice,EOS */ } } System.out.println("completely out of the while"); } catch (FileNotFoundException fe) { } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } } } } } @FXML private void handleClick(MouseEvent event) { MouseButton butt = event.getButton(); //ContextMenu context = null; //tblParts.setContextMenu(context); if (butt == MouseButton.SECONDARY) { MenuItem mnuDelete = new MenuItem("Delete Me"); MenuItem mnuCheck = new MenuItem("Check"); MenuItem mnuCost = new MenuItem("Set Cost"); ContextMenu context = new ContextMenu(mnuDelete); context.getItems().add(mnuCheck); context.getItems().add(mnuCost); tblParts.setContextMenu(context); mnuDelete.setOnAction((ActionEvent event1) -> { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Item) { Item item = (Item) view.getSelectionModel().getSelectedItem(); ItemDB itemDB = item.createItemDB(); aData.delete(itemDB); PartsData.remove(item); } } }); mnuCheck.setOnAction((ActionEvent event1) -> { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Item) { Item item = (Item) view.getSelectionModel().getSelectedItem(); try { if (item.getSspSupplier().equalsIgnoreCase("synnex")) { Desktop.getDesktop() .browse(new URI("https://ec.synnex.ca/ecx/part/searchResult.html?keyword=" + item.getSspSupPart() + "&keywordRelation=2&prefferedWarehouse=0&criteriaType=4&resultType=7&begin=0&catId=-1&_source=SearchBar")); } else if (item.getSspSupplier().equalsIgnoreCase("ingram")) { Desktop.getDesktop().browse(new URI( "https://ca-new.ingrammicro.com/_layouts/CommerceServer/IM/search2.aspx#PNavDS=N:0,Ntk:Products,Ntt:" + item.getSspSupPart() + ",Ntx:matchall")); } else if (item.getSspSupplier().equalsIgnoreCase("techdata")) { Desktop.getDesktop().browse(new URI( "http://www.techdata.ca/tools/Searching/SearchResultsProducts.aspx?KW=" + item.getSspSupPart())); } else if (item.getSspSupplier().equalsIgnoreCase("tomauri")) { Desktop.getDesktop().browse( new URI("https://www.tomauri.com/do/product?sku=" + item.getSspSupPart())); } } catch (IOException | URISyntaxException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } } }); mnuCost.setOnAction((ActionEvent action) -> { if (event.getSource() instanceof TableView) { TableView view = (TableView) event.getSource(); if (view.getSelectionModel().getSelectedItem() instanceof Item) { Item item = (Item) view.getSelectionModel().getSelectedItem(); TextInputDialog tidCost = new TextInputDialog(); tidCost.setTitle("Cost Value"); tidCost.setHeaderText("Please enter the cost of this item below"); tidCost.setContentText("What is the cost of this item?"); Optional<String> result = tidCost.showAndWait(); result.ifPresent(cost -> { aData.update(item.createItemDB(), "dblCost", Double.parseDouble(cost)); }); } } }); } } @FXML private void handleClose(ActionEvent event) throws Throwable { //finalize(); System.exit(0); } @FXML private void handleCustSearch(KeyEvent event) { TextField txtTemp = (TextField) event.getSource(); if (txtTemp == null) { System.out.println("we got null ? "); } if (txtTemp.getText().length() > 2) { List<CustomerDB> lGood = aDataCust.query(txtTemp.getId(), txtTemp.getText()); if (!lGood.isEmpty()) { CustData.clear(); lGood.stream().forEach((cust) -> { CustData.add(cust.createCustomer()); }); } } else { CustData.clear(); List<CustomerDB> lstCusts = aDataCust.readAll(); lstCusts.stream().forEach((cust) -> { CustData.add(cust.createCustomer()); }); } } @FXML private void handleSearch(KeyEvent event) { String strSearch = ""; String strID = ""; if (event.getSource() instanceof TextField) { TextField txtTemp = (TextField) event.getSource(); strSearch = txtTemp.getText(); strID = txtTemp.getId(); } else if (event.getSource() instanceof ChoiceBox) { } if (strSearch.length() > 2) { List<ItemDB> lGood = aData.query(strID, strSearch); if (lGood.size() > 0) { PartsData.clear(); lGood.stream().forEach((item) -> { PartsData.add(item.createItem()); }); } } else { PartsData.clear(); List<ItemDB> lstItems = aData.readAll(); lstItems.stream().forEach((item) -> { PartsData.add(item.createItem()); }); } } @FXML private void handleListStock(ActionEvent event) { /*File fObjPData = new File(fDataFiles, "pOrders.odb"); fObjPData.delete(); AccessData aPData = new AccessData(fObjPData, logger); //List<ItemDB> list = aPData.readAll(); ItemData.stream().forEach(item -> { aPData.addItem(item.createItemDB()); }); tcEOS.setText("Stock"); tpnAll.getSelectionModel().select(tabParts); File fObjDataStock = new File(fDataFiles, "stock.odb"); FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return lowercaseName.endsWith(".xlsx"); }; aDataStock = new AccessDataStock(fObjDataStock); File[] fStock = fStockDir.listFiles(xlsxFilter); List<File> lstSTK = new ArrayList<>(); lstSTK.addAll(Arrays.asList(fStock)); Collections.sort(lstSTK, Collections.reverseOrder()); lstSTK.stream().forEach(fFile -> { System.out.println("the file is " + fFile.getName()); if (aDataStock.query("strFStock", fFile.getName()).isEmpty()) { List<ItemDB> lstFile = createListFromTable(fFile); Object objDate = getCellData(fFile, 6, 10); final java.sql.Date date; if (objDate instanceof Date) { Date dte = (Date) objDate; date = new java.sql.Date(dte.getTime()); } else if (objDate instanceof Double) { date = new java.sql.Date(Math.round((Double) objDate)); } else { date = new java.sql.Date(fFile.lastModified()); } if (!lstFile.isEmpty()) { lstFile.stream().forEach(item -> { StockDB stkFile = new StockDB(); stkFile.setDblQtyRec(0); stkFile.setDated(date); stkFile.setStrFStock(fFile.getName()); stkFile.setDblQtyOrd(item.getDblQty()); if (!item.getStrSupPart().isEmpty()) { stkFile.setStrSupPart(item.getStrSupPart()); aDataStock.addStock(stkFile); } else if (!item.getStrSKU().isEmpty()) { ItemDB itemTemp = aData.query("txtSKU", item.getStrSKU()).get(0); if (itemTemp != null) { System.out.println("wow caught that one. " + itemTemp.toString()); if (!itemTemp.getStrSupPart().isEmpty()) { stkFile.setStrSupPart(itemTemp.getStrSupPart()); System.out.println("really really did catch it."); aDataStock.addStock(stkFile); } } } else { System.out.println("the item is empty " + item.toString()); /*Alert alert = new Alert(AlertType.ERROR,"The part number is empty", ButtonType.OK); alert.showAndWait(); } }); } } else { System.out.println("why would this file be in here " + fFile.getName()); } }); lstStock = aDataStock.readAll(); lstStock.stream().forEach(stocki -> { ItemDB itemdb = aData.query("txtSupPart", stocki.getStrSupPart()).get(0); itemdb.setDblQty(stocki.getDblQtyRec()); ItemData.add(itemdb.createItem()); });*/ } @FXML private void handleAddStock(ActionEvent event) { Item item = new Item(); if (!txtMfr.getText().isEmpty()) { item.setSspMfr(txtMfr.getText()); txtMfr.setText(""); } if (!txtDescrip.getText().isEmpty()) { item.setSspDescrip(txtDescrip.getText()); txtDescrip.setText(""); } if (!txtSKU.getText().isEmpty()) { item.setSspSKU(txtSKU.getText()); txtSKU.setText(""); } item.setSspSupplier(chbSupplier.getValue()); if (!txtSupPart.getText().isEmpty()) { item.setSspSupPart(txtSupPart.getText()); txtSupPart.setText(""); } if (!txtUPC.getText().isEmpty()) { item.setSspUPC(txtUPC.getText()); //System.out.println("what is upc " + txtUPC.getText()); txtUPC.setText(""); } if (!jReg.isbRegSetting("Markup")) { setMarkup(); } //NumberFormat nfDSales = NumberFormat.getCurrencyInstance(Locale.CANADA); //ParsePosition pp = new ParsePosition(0); TextInputDialog txtDiaCost = new TextInputDialog("25.00"); txtDiaCost.setTitle("Cost Value"); txtDiaCost.setHeaderText("Please enter the cost of this item below"); txtDiaCost.setContentText("What is the cost of this item?"); Optional<String> result = txtDiaCost.showAndWait(); result.ifPresent(cost -> { double dCost = Double.parseDouble(cost.replaceAll("[,$]", "")); item.setSdpCost(dCost); item.setSdpSalePrice(Math.round(dCost / Double.parseDouble(jReg.getStrRegSetting("Markup"))) - .05); }); TextInputDialog txtDiaSale = new TextInputDialog(Double.toString(item.getSdpSalePrice())); txtDiaSale.setTitle("Sale Price"); txtDiaSale.setHeaderText("If the price is different please enter it here"); txtDiaSale.setContentText("Sale Price"); Optional<String> resultSale = txtDiaSale.showAndWait(); resultSale.ifPresent(sale -> { sale = sale.replaceAll("[$,]", ""); item.setSdpSalePrice(Double.parseDouble(sale)); }); aData.addItem(item.createItemDB()); item.setSdpQty(1); PartsData.add(item); ItemData.add(item); } @FXML private void handleDumpStock(ActionEvent event) { try { List<ItemDB> list = aData.readAll(); Desktop.getDesktop().open(createStockFile(list)); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } @FXML private void handlePartOrder(ActionEvent event) { File fObjPData = new File(fDataFiles, "pOrders.odb"); logger.log(Level.INFO, "We created the db file {0}", fObjPData.getAbsolutePath()); AccessData aPData = new AccessData(fObjPData, logger); logger.log(Level.INFO, "We added the file to the new db {0}", aPData.toString()); Item item = null; if (tblParts.getSelectionModel().getSelectedItem() != null) { logger.log(Level.INFO, "An item in the table is selected "); item = tblParts.getSelectionModel().getSelectedItem(); } else if (!txtSupPart.getText().isEmpty()) { // get all values from text boxes and add stock item = new Item(); item.setSspMfr(txtMfr.getText()); item.setSspSKU(txtSKU.getText()); txtSKU.setText(""); item.setSspDescrip(txtDescrip.getText()); txtDescrip.setText(""); item.setSspSupplier(txtSupplier.getText()); txtSupplier.setText(""); item.setSspSupPart(txtSupPart.getText()); txtSupPart.setText(""); item.setSspUPC(txtUPC.getText()); txtUPC.setText(""); } Alert aa = new Alert(AlertType.CONFIRMATION, "Would you like to add this " + item.getSspDescrip() + "\n" + item.getSspSupPart() + "\nto Stock Order", ButtonType.YES, ButtonType.NO); aa.setTitle("Add Part"); aa.setHeaderText("Adding part to stock order"); final ItemDB itemfin = item.createItemDB(); aa.showAndWait().ifPresent((ButtonType result) -> { if (result == ButtonType.YES) { aPData.addItem(itemfin); logger.log(Level.INFO, "adding this item {0}", itemfin.toString()); } }); ItemData.clear(); List<ItemDB> items = aPData.readAll(); items.stream().forEach(ite -> { ItemData.add(ite.createItem()); }); } @FXML private void handleQuoteTrack(Event event) { getQuoteData(); } @FXML private void handleStockTrack(Event event) { FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return (!lowercaseName.startsWith("~$") && lowercaseName.endsWith(".xlsx")); }; new Thread() { @Override public void run() { File[] fStock = fStockDir.listFiles(xlsxFilter); List<File> lstSTK = new ArrayList<>(); lstSTK.addAll(Arrays.asList(fStock)); Collections.sort(lstSTK, Collections.reverseOrder()); lstSTK.stream().forEach((File fFile) -> { Stock stock = new Stock(); stock.setSspFStock(fFile.getName()); Object obj = getCellData(fFile, 6, 10); if (obj instanceof java.sql.Date) { stock.setSopDate((java.sql.Date) obj); } else if (obj instanceof Date) { Date dte = (Date) obj; stock.setSopDate(new java.sql.Date(dte.getTime())); } StockData.add(stock); }); } }.start(); } @FXML private void handleCreateStock(ActionEvent event) { List<ItemDB> listdb = new ArrayList<>(); ItemData.stream().forEach((item) -> { listdb.add(item.createItemDB()); }); ItemData.clear(); File file = createStockFile(listdb); //Desktop.getDesktop().print(file); Alert aa = new Alert(AlertType.CONFIRMATION); aa.setTitle("Email Purchasing"); aa.setHeaderText("Emailing Purchasing"); aa.setContentText("Would you like to email Purchasing?"); Optional<ButtonType> result = aa.showAndWait(); if (result.get() == ButtonType.OK) { if (file == null) { System.out.println("what happened to the file????"); } emailPurchasing(file.getName()); } else { System.out.println("why not?"); } } private File createStockFile(List<ItemDB> list) { int nSize = list.size(); XSSFWorkbook wbs = createStockWorkbook(); XSSFSheet sheetStock = wbs.getSheet("Digital Version"); List<XSSFTable> lTables = sheetStock.getTables(); // Create a FormulaEvaluator to use FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper() .createFormulaEvaluator(); File fStock = createFilename("STK", ""); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5)); if (!lTables.isEmpty()) { XSSFTable table = lTables.get(0); table.getCTTable() .setRef(new CellRangeAddress(table.getStartCellReference().getRow(), table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(), table.getEndCellReference().getCol()).formatAsString()); XSSFRow row; XSSFCell cell; font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); int nCellRef = table.getStartCellReference().getRow() + 1; for (ItemDB itemdb : list) { row = sheetStock.createRow(nCellRef++); cell = row.createCell(0); cellStyle = cell.getCellStyle(); cell.setCellValue(itemdb.getDblQty()); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(itemdb.getStrMfr()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(itemdb.getStrSKU()); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(itemdb.getStrDescrip()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(itemdb.getStrSupplier()); cell.setCellStyle(cellStyle); cell = row.createCell(5); cell.setCellValue(itemdb.getStrSupPart()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(itemdb.getDblSalePrice()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(itemdb.getDblCost()); cell.setCellStyle(cellStyle); /*cell = row.createCell(8); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); CellStyle style = wbs.createCellStyle(); style.setDataFormat(wbs.createDataFormat().getFormat("0%")); cell.setCellStyle(style);*/ mainWorkbookEvaluator.evaluateAll(); } try { try (FileOutputStream fileOut = new FileOutputStream(fStock)) { wbs.write(fileOut); return fStock; } } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } return null; } @FXML private void handleInjectExcel(ActionEvent event) { XSSFWorkbook wb = createWorkbook(); FormulaEvaluator evaluator; inject(wb, cmbSales.getValue(), 5, 14); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); inject(wb, Date.from(instant), 3, 14); inject(wb, txtCustomer.getText(), 10, 2); inject(wb, txtAddress.getText(), 11, 2); inject(wb, txtCity.getText(), 12, 2); inject(wb, txtProvince.getText(), 13, 2); inject(wb, txtPhone.getText(), 14, 2); inject(wb, txtContact.getText(), 15, 2); inject(wb, txtFax.getText(), 14, 4); inject(wb, txtEmail.getText(), 16, 2); inject(wb, txtPO.getText(), 15, 4); if (chkGST.isSelected()) { inject(wb, "Y", 36, 8); } else { inject(wb, "N", 36, 8); } if (chkPST.isSelected()) { inject(wb, "Y", 37, 8); } else { inject(wb, "N", 37, 8); } int nRow = 21; for (Object obj : tblItems.getItems()) { if (obj instanceof Item) { Item item = (Item) obj; inject(wb, item.getSdpQty(), nRow, 0); inject(wb, item.getSspMfr(), nRow, 1); inject(wb, item.getSspSKU(), nRow, 2); inject(wb, item.getSspDescrip(), nRow, 3); inject(wb, item.getSspSupplier(), nRow, 5); inject(wb, item.getSspSupPart(), nRow, 6); inject(wb, item.getSspSerial(), nRow, 7); inject(wb, item.getSdpSalePrice(), nRow, 8); inject(wb, item.getSdpEOS(), nRow++, 9); } } evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); wb.setForceFormulaRecalculation(true); try { File fTemp = createFilename("Q", txtCustomer.getText()); inject(wb, fTemp.getName().substring(0, 7), 46, 14); try (FileOutputStream fos = new FileOutputStream(fTemp)) { wb.write(fos); } Alert alert = new Alert(AlertType.INFORMATION, "Would you like to open the file? Choose no to print", ButtonType.YES, ButtonType.NO); alert.showAndWait().ifPresent(response -> { CustomerDB cust = new CustomerDB(); cust.setStrCustomer(txtCustomer.getText()); cust.setStrAddress(txtAddress.getText()); cust.setStrCity(txtCity.getText()); cust.setStrProvince(txtProvince.getText()); cust.setStrPhone(txtPhone.getText()); cust.setStrEmail(txtEmail.getText()); cust.setStrFax(txtFax.getText()); cust.setStrContact(txtContact.getText()); cust.setStrPO(txtPO.getText()); cust.setBooGST(chkGST.isSelected()); cust.setBooPST(chkPST.isSelected()); if ((aDataCust.query("txtCustomer", cust.getStrCustomer()).isEmpty()) && (aDataCust.query("txtPhone", cust.getStrPhone()).isEmpty())) { aDataCust.addItem(cust); CustData.add(cust.createCustomer()); } if (response == ButtonType.YES) { try { Desktop.getDesktop().open(fTemp); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } else { try { Desktop.getDesktop().print(fTemp); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } }); } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } private XSSFWorkbook createStockWorkbook() { try { XSSFWorkbook wbs; File fSTemplate = new File(fDataFiles, "StockTemplate.xlsx"); if (!fSTemplate.exists()) { try (InputStream in = this.getClass().getResourceAsStream("/files/StockTemplate.xlsx"); OutputStream out = new FileOutputStream(fSTemplate)) { byte[] buffer = new byte[1024]; int length; while ((length = in.read(buffer)) > 0) { out.write(buffer, 0, length); } } } File fSTemp = File.createTempFile("Temp", ".xlsx"); fSTemp.deleteOnExit(); /*if(fSTemp.exists()){ fSTemp.delete(); }*/ Files.copy(fSTemplate.toPath(), fSTemp.toPath(), StandardCopyOption.REPLACE_EXISTING); wbs = new XSSFWorkbook(fSTemp); return wbs; } catch (IOException | InvalidFormatException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); return null; } } private XSSFWorkbook createWorkbook() { XSSFWorkbook wb; try { File fTemplate = new File(fDataFiles, "Template.xlsx"); if (!fTemplate.exists()) { try (InputStream in = this.getClass().getResourceAsStream("/files/Template.xlsx"); OutputStream out = new FileOutputStream(fTemplate)) { byte[] buffer = new byte[1024]; int length; while ((length = in.read(buffer)) > 0) { out.write(buffer, 0, length); } } } File fTemp = File.createTempFile("Temp", ".xlsx"); if (fTemp.exists()) { fTemp.delete(); } Files.copy(fTemplate.toPath(), fTemp.toPath()); fTemp.deleteOnExit(); wb = new XSSFWorkbook(fTemp); return wb; } catch (IOException | InvalidFormatException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); return null; } } private File createFilename(String strPrefix, String strCustomer) { String strSales = ""; if (cmbSales.getSelectionModel().getSelectedItem().toString().equalsIgnoreCase("BrianG")) { strSales = "BG"; } else if (cmbSales.getSelectionModel().getSelectedItem().toString().equalsIgnoreCase("CherylD")) { strSales = "CD"; } else if (cmbSales.getSelectionModel().getSelectedItem().toString().equalsIgnoreCase("MelM")) { strSales = "MM"; } else if (cmbSales.getSelectionModel().getSelectedItem().toString().equalsIgnoreCase("BryanH")) { strSales = "BH"; } if (strPrefix.toUpperCase().endsWith("Q")) { if (!fQuoteDir.exists()) { fQuoteDir.mkdirs(); } if (!jReg.isbRegSetting("QuoteNumber")) { TextInputDialog tidSalesCount = new TextInputDialog("3000"); tidSalesCount.setTitle("Quote Number"); tidSalesCount.setContentText("Please enter your last quote number"); tidSalesCount.setHeaderText("What is the last quote number"); Optional<String> result = tidSalesCount.showAndWait(); result.ifPresent(qNumber -> { int nNumber = Integer.parseInt(qNumber); jReg.setStrRegSetting("QuoteNumber", String.valueOf(++nNumber)); }); } else { int nFQuote = Integer.parseInt(jReg.getStrRegSetting("QuoteNumber")); jReg.setStrRegSetting("QuoteNumber", String.valueOf(++nFQuote)); } String strTemp = strSales + "Q" + jReg.getStrRegSetting("QuoteNumber") + strCustomer + ".xlsx"; return new File(fQuoteDir, strTemp); } else if (strPrefix.toUpperCase().endsWith("STK")) { if (!fStockDir.exists()) { fStockDir.mkdirs(); } if (!jReg.isbRegSetting("StockNumber")) { TextInputDialog tidStockCount = new TextInputDialog("3100"); tidStockCount.setTitle("Stock Number"); tidStockCount.setContentText("Please enter your last stock number"); tidStockCount.setHeaderText("What is the last stock number"); Optional<String> result = tidStockCount.showAndWait(); result.ifPresent(sNumber -> { int nNumber = Integer.parseInt(sNumber); jReg.setStrRegSetting("StockNumber", String.valueOf(++nNumber)); }); } else { int nFStock = Integer.parseInt(jReg.getStrRegSetting("StockNumber")); jReg.setStrRegSetting("StockNumber", String.valueOf(++nFStock)); } String strTemp = strSales + "STK" + jReg.getStrRegSetting("StockNumber") + ".xlsx"; File fStocked = new File(fStockDir, strTemp); if (fStocked.exists()) { return new File(fStockDir, strTemp.replaceFirst("STK", "DUP")); } else { return new File(fStockDir, strTemp); } } else if (strPrefix.toUpperCase().endsWith("P")) { return null; } else { return null; } } private void inject(XSSFWorkbook wb, Object obj, int row, int col) { if (wb == null) { System.out.println("wb is null"); } XSSFSheet sheet = wb.getSheet("Digital Version"); Row rowed = sheet.getRow(row); Cell cell = rowed.getCell(col); CellStyle cellStyle = cell.getCellStyle(); XSSFFont font = sheet.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy")); cell.setCellValue((Date) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else if (obj instanceof Integer) { cell.setCellValue((int) obj); } cell.setCellStyle(cellStyle); } private void injectStock(XSSFWorkbook wbs, Object obj, int row, int col) { Row rowed = wbs.getSheet("Digital Version").getRow(row); Cell cell = rowed.getCell(col); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { CellStyle cellStyle = wbs.getCellStyleAt(col); CreationHelper createHelper = wbs.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy")); cell.setCellValue((Date) obj); cell.setCellStyle(cellStyle); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else if (obj instanceof Integer) { cell.setCellValue((int) obj); } } @Override public void initialize(URL url, ResourceBundle rb) { writeLog(); if (!fDataFiles.exists()) { fDataFiles.mkdirs(); } tblParts.setEditable(true); File fObjData = new File(fDataFiles, "products.odb"); File fObjDataCust = new File(fDataFiles, "customers.odb"); dteOrderDate.setValue(LocalDate.now()); cmbSales.setItems(options); cmbSales.getSelectionModel().selectedItemProperty() .addListener((ObservableValue observable, Object oldValue, Object newValue) -> { jReg.setStrRegSetting("Salesperson", newValue.toString()); }); if (jReg.isbRegSetting("Salesperson")) { cmbSales.setValue(jReg.getStrRegSetting("Salesperson")); } else { cmbSales.setValue(options.get(1)); } tcQty.setCellValueFactory(new PropertyValueFactory<>("sdpQty")); tcQty.setCellFactory(TextFieldTableCell.forTableColumn(new DoubleStringConverter())); tcQty.setOnEditStart((CellEditEvent<Item, Double> t) -> { if (bIsGood) { Item item = t.getTableView().getItems().get(t.getTablePosition().getRow()); lstStock = aDataStock.query("strSupPart", item.getSspSupPart()); if (!lstStock.isEmpty()) { StockDB stockdb = lstStock.get(0); Alert alert = new Alert(AlertType.INFORMATION, "Stock Received =" + stockdb.getDblQtyRec() + ", and stock ordered is " + stockdb.getDblQtyOrd(), ButtonType.CLOSE); alert.show(); } } }); tcQty.setOnEditCommit((CellEditEvent<Item, Double> t) -> { if (bIsGood) { logger.info("bIsGood"); Item item = t.getTableView().getItems().get(t.getTablePosition().getRow()); lstStock = aDataStock.query("strSupPart", item.getSspSupPart()); if (!lstStock.isEmpty()) { StockDB stockdb = lstStock.get(0); //logger.info("stock db is " + stockdb.toString()); logger.log(Level.INFO, "so umm what is nGood for {0}", aDataStock.update(stockdb, t.getNewValue())); } } else { t.getTableView().getItems().get(t.getTablePosition().getRow()).setSdpQty(t.getNewValue()); logger.log(Level.INFO, "qty commit out"); } }); tcDescrip.setCellValueFactory(new PropertyValueFactory<>("sspDescrip")); tcDescrip.setCellFactory(TextFieldTableCell.forTableColumn()); tcEOS.setCellValueFactory(new PropertyValueFactory<>("sdpEOS")); tcEOS.setCellFactory(TextFieldTableCell.forTableColumn(new DoubleStringConverter())); tcMfr.setCellValueFactory( (TableColumn.CellDataFeatures<Item, String> cellData) -> cellData.getValue().sspMfrProperty()); tcMfr.setCellFactory(TextFieldTableCell.forTableColumn()); tcSKU.setCellValueFactory(cellData -> cellData.getValue().sspSKUProperty()); tcSKU.setCellFactory(TextFieldTableCell.forTableColumn()); tcSalePrice.setCellFactory(tc -> new CurrencyCell<>());//TextFieldTableCell.forTableColumn(new DoubleStringConverter())); tcSalePrice.setCellValueFactory(new PropertyValueFactory<>("sdpSalePrice")); tcSerial.setCellValueFactory(new PropertyValueFactory<>("sspSerial")); tcSerial.setCellFactory(TextFieldTableCell.forTableColumn()); tcSupPart.setCellValueFactory(new PropertyValueFactory<>("sspSupPart")); tcSupPart.setCellFactory(TextFieldTableCell.forTableColumn()); tcSupplier.setCellValueFactory(new PropertyValueFactory<>("sspSupplier")); tcSupplier.setCellFactory(ComboBoxTableCell.forTableColumn(new DefaultStringConverter(), cbValues)); tcQty1.setCellValueFactory((TableColumn.CellDataFeatures<Item, Double> param) -> new SimpleObjectProperty<>( param.getValue().getSdpQty())); tcQty1.setCellFactory((TableColumn<Item, Double> param) -> new ButtonCell()); tcMfr1.setCellValueFactory(new PropertyValueFactory<>("sspMfr")); tcMfr1.setCellFactory(TextFieldTableCell.forTableColumn()); tcMfr1.setOnEditCommit((CellEditEvent<Item, String> t) -> { updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "strMfr", t.getNewValue()); }); tcDescrip1.setCellValueFactory(new PropertyValueFactory<>("sspDescrip")); tcDescrip1.setCellFactory(TextFieldTableCell.forTableColumn()); tcDescrip1.setOnEditCommit((CellEditEvent<Item, String> t) -> { updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "strDescrip", t.getNewValue()); }); tcSKU1.setCellValueFactory(new PropertyValueFactory<>("sspSKU")); tcSKU1.setCellFactory(TextFieldTableCell.forTableColumn()); tcSKU1.setOnEditCommit((CellEditEvent<Item, String> t) -> { logger.info("we are in the edit"); updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "strSKU", t.getNewValue()); /*Item item = t.getTableView().getItems().get(t.getTablePosition().getRow()); aData.update(item.createItemDB(), "strSKU", t.getNewValue());*/ }); tcSalePrice1.setCellValueFactory(new PropertyValueFactory<>("sdpSalePrice")); tcSalePrice1.setCellFactory(tc -> new CurrencyCell<>()); tcSalePrice1.setOnEditCommit((CellEditEvent<Item, Double> t) -> { updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "dblSalePrice", t.getNewValue()); }); tcEOS1.setCellValueFactory(new PropertyValueFactory<>("sdpEOS")); tcEOS1.setCellFactory(TextFieldTableCell.forTableColumn(new DoubleStringConverter())); tcEOS1.setOnEditCommit((CellEditEvent<Item, Double> t) -> { updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "dblEOS", t.getNewValue()); /*Item item = t.getTableView().getItems().get(t.getTablePosition().getRow()); aData.update(item.createItemDB(), "dblEOS", t.getNewValue()); System.out.println("EOS... " + item.createItemDB().toString());*/ }); tcSupPart1.setCellValueFactory(new PropertyValueFactory<>("sspSupPart")); tcSupPart1.setCellFactory(TextFieldTableCell.forTableColumn()); tcSupPart1.setOnEditCommit((CellEditEvent<Item, String> t) -> { Item item = t.getTableView().getItems().get(t.getTablePosition().getRow()); aData.update(item.createItemDB(), "strSupPart", t.getNewValue()); }); tcSupplier1.setCellValueFactory(new PropertyValueFactory<>("sspSupplier")); tcSupplier1.setCellFactory(ComboBoxTableCell.forTableColumn(new DefaultStringConverter(), cbValues)); tcSupplier1.setOnEditCommit((CellEditEvent<Item, String> t) -> { updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "strSupplier", t.getNewValue()); }); tcUPC1.setCellValueFactory(new PropertyValueFactory<>("sspUPC")); tcUPC1.setCellFactory(TextFieldTableCell.forTableColumn()); tcUPC1.setOnEditCommit((CellEditEvent<Item, String> t) -> { updateItem(t.getTableView().getItems().get(t.getTablePosition().getRow()), "strUPC", t.getNewValue()); /*Item item = t.getTableView().getItems().get(t.getTablePosition().getRow()); aData.update(item.createItemDB(), "strUPC", t.getNewValue());*/ }); tcCustomer.setCellValueFactory(new PropertyValueFactory<>("sspCustomer")); tcCustomer.setCellFactory(TextFieldTableCell.forTableColumn()); tcCustomer.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strCustomer", t.getNewValue()); }); tcAddress.setCellValueFactory(new PropertyValueFactory<>("sspAddress")); tcAddress.setCellFactory(TextFieldTableCell.forTableColumn()); tcAddress.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strAddress", t.getNewValue()); }); tcCity.setCellValueFactory(new PropertyValueFactory<>("sspCity")); tcCity.setCellFactory(TextFieldTableCell.forTableColumn()); tcCity.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strCity", t.getNewValue()); }); tcProvince.setCellValueFactory(new PropertyValueFactory<>("sspProvince")); tcProvince.setCellFactory(TextFieldTableCell.forTableColumn()); tcProvince.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strProvince", t.getNewValue()); }); tcPhone.setCellValueFactory(new PropertyValueFactory<>("sspPhone")); tcPhone.setCellFactory(TextFieldTableCell.forTableColumn()); tcPhone.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strPhone", t.getNewValue()); }); tcFax.setCellValueFactory(new PropertyValueFactory<>("sspFax")); tcFax.setCellFactory(TextFieldTableCell.forTableColumn()); tcFax.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strFax", t.getNewValue()); }); tcEmail.setCellValueFactory(new PropertyValueFactory<>("sspEmail")); tcEmail.setCellFactory(TextFieldTableCell.forTableColumn()); tcEmail.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strEmail", t.getNewValue()); }); tcContact.setCellValueFactory(new PropertyValueFactory<>("sspContact")); tcContact.setCellFactory(TextFieldTableCell.forTableColumn()); tcContact.setOnEditCommit((CellEditEvent<Customer, String> t) -> { Customer cust = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDataCust.update(cust.createCustomerDB(), "strContact", t.getNewValue()); }); tcCust.setCellValueFactory(new PropertyValueFactory<>("sspCustomer")); tcCust.setCellFactory(TextFieldTableCell.forTableColumn()); tcCustPhone.setCellValueFactory(new PropertyValueFactory<>("sspPhone")); tcCustPhone.setCellFactory(TextFieldTableCell.forTableColumn()); tcDate.setCellValueFactory(new PropertyValueFactory<>("sopDate")); tcDate.setCellFactory(column -> { return new TableCell<Quote, Object>() { @Override protected void updateItem(Object obj, boolean empty) { super.updateItem(obj, empty); if (obj == null || empty) { setText(""); setStyle(null); } else if (obj instanceof java.sql.Date) { java.sql.Date date = (java.sql.Date) obj; setText(date.toString()); } } }; }); tcQuote.setCellFactory(TextFieldTableCell.forTableColumn()); tcQuote.setCellValueFactory(new PropertyValueFactory<>("sspQuote")); tcStatus.setCellFactory(ComboBoxTableCell.forTableColumn(new DefaultStringConverter(), cbStatus)); tcStatus.setCellValueFactory(new PropertyValueFactory<>("sspStatus")); tcStatus.setOnEditCommit((CellEditEvent<Quote, String> t) -> { Quote quote = t.getTableView().getItems().get(t.getTablePosition().getRow()); aDatQuote.update(quote.createQuoteDB(), "strStatus", t.getNewValue()); }); tcStock.setCellFactory(TextFieldTableCell.forTableColumn()); tcStock.setCellValueFactory(new PropertyValueFactory<>("sspFStock")); tcStockStat.setCellFactory(TextFieldTableCell.forTableColumn()); tcStockStat.setCellValueFactory(new PropertyValueFactory<>("sspStatus")); tcStockDate.setCellValueFactory(new PropertyValueFactory<>("sopDate")); tcStockDate.setCellFactory(column -> { return new TableCell<Stock, Object>() { @Override protected void updateItem(Object obj, boolean empty) { super.updateItem(obj, empty); if (obj == null || empty) { setText(""); setStyle(null); } else if (obj instanceof java.sql.Date) { java.sql.Date date = (java.sql.Date) obj; setText(date.toString()); } } }; }); tblItems.setItems(ItemData); tblParts.setItems(PartsData); tblCust.setItems(CustData); tblQuotes.setItems(QuoteData); tblStock.setItems(StockData); tblQuotes.getSortOrder().add(tcDate); if (!fObjData.exists()) { aData = new AccessData(fObjData, logger); getData(); } else { aData = new AccessData(fObjData, logger); List<ItemDB> lstItem = aData.readAll(); lstItem.stream().forEach((item) -> { PartsData.add(item.createItem()); }); } if (fObjDataCust.exists()) { aDataCust = new AccessDataCustomer(fObjDataCust); List<CustomerDB> lstCust = aDataCust.readAll(); lstCust.stream().forEach((cust) -> { CustData.add(cust.createCustomer()); }); } else { aDataCust = new AccessDataCustomer(fObjDataCust); aDataCust.addItem(new CustomerDB()); } tpnAll.getSelectionModel().selectedItemProperty() .addListener((ObservableValue<? extends Tab> observable, Tab oldValue, Tab newValue) -> { if (newValue.getText().equalsIgnoreCase("Stock Tracking")) { bIsGood = true; } else { if (bIsGood) { ItemData.clear(); } bIsGood = false; } }); chbSupplier.setItems(cbValues); chbSupplier.setValue(cbValues.get(0)); } private void updateItem(Item item, String strField, String strNewValue) { aData.update(item.createItemDB(), strField, strNewValue); logger.log(Level.INFO, "past update {0}", strNewValue); List<ItemDB> lstDumb = aData.query("txtSupPart", item.getSspSupPart()); logger.log(Level.INFO, "past listItem {0}", lstDumb.toString()); if (!lstDumb.isEmpty()) { PartsData.clear(); lstDumb.stream().forEach(item2 -> { logger.log(Level.INFO, "in the stream {0}", item2.toString()); PartsData.add(item2.createItem()); }); } } private void updateItem(Item item, String strField, Double dblNewValue) { logger.log(Level.INFO, "in sale {0} and {1}", new Object[] { item.createItemDB().toString(), dblNewValue.toString() }); aData.update(item.createItemDB(), strField, dblNewValue); logger.log(Level.INFO, "past update {0}", dblNewValue); List<ItemDB> lstDumb = aData.query("txtSupPart", item.getSspSupPart()); if (!lstDumb.isEmpty()) { PartsData.clear(); lstDumb.stream().forEach(item2 -> { PartsData.add(item2.createItem()); }); } } private void getQuoteData() { FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return (!lowercaseName.startsWith("~$") && lowercaseName.endsWith(".xlsx")); }; File fQData = new File(fDataFiles, "quotes.odb"); aDatQuote = new AccessDataQuote(fQData); new Thread() { @Override public void run() { if (!fQuoteDir.exists()) { fQuoteDir.mkdirs(); } File[] fQuote = fQuoteDir.listFiles(xlsxFilter); List<File> lstQuote = new ArrayList<>(); lstQuote.addAll(Arrays.asList(fQuote)); Collections.sort(lstQuote, Collections.reverseOrder()); sdpPro = new SimpleDoubleProperty(0); pgbLoaded.progressProperty().bind(sdpPro); lstQuote.stream().forEach((File fFile) -> { sdpPro.set(dFcount++ / (double) lstQuote.size()); List<QuoteDB> lstQuoteDB = aDatQuote.query("txtQuote", getCellData(fFile, 46, 14).toString()); if (lstQuoteDB.isEmpty()) { QuoteDB quoteDB = new QuoteDB(); quoteDB.setStrCustomer(getCellData(fFile, 10, 2).toString()); Object objDate = getCellData(fFile, 3, 14); if (objDate instanceof Date) { java.sql.Date date = new java.sql.Date(((Date) objDate).getTime()); quoteDB.setDteDate(date); } else if (objDate instanceof Double) { Date date = new Date(Math.round((Double) objDate)); quoteDB.setDteDate(new java.sql.Date(date.getTime())); } else { quoteDB.setDteDate(new java.sql.Date(fFile.lastModified())); } quoteDB.setStrPhone(getCellData(fFile, 14, 2).toString()); quoteDB.setStrQuote(getCellData(fFile, 46, 14).toString()); quoteDB.setStrStatus("Created"); aDatQuote.addItem(quoteDB); QuoteData.add(quoteDB.createQuote()); } else { QuoteDB qdb = lstQuoteDB.get(0); QuoteData.add(qdb.createQuote()); } }); } }.start(); } private Object getCellData(File fSheet, int row, int coll) { try { XSSFWorkbook book = new XSSFWorkbook(fSheet); FormulaEvaluator eval = book.getCreationHelper().createFormulaEvaluator(); XSSFSheet xSheet = book.getSheet("Digital Version"); Cell celled = xSheet.getRow(row).getCell(coll); if (celled != null) { CellValue cellval = eval.evaluate(celled); if (cellval == null) { //System.out.println("cellval is null at line 918 " + fSheet.getAbsolutePath() + " " + row + " " + coll); return ""; } else { switch (cellval.getCellType()) { case Cell.CELL_TYPE_BLANK: logger.info("got a blank"); return ""; case Cell.CELL_TYPE_BOOLEAN: logger.info("got a boolean"); return cellval.getBooleanValue(); case Cell.CELL_TYPE_ERROR: return cellval.getErrorValue(); case Cell.CELL_TYPE_FORMULA: return cellval.getStringValue(); case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celled)) { return HSSFDateUtil.getJavaDate(cellval.getNumberValue()); } else { return cellval.getNumberValue(); } case Cell.CELL_TYPE_STRING: return cellval.getStringValue(); default: return ""; } } } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } return ""; } private void getData() { try { fStockDir = new File(fUserDir, "Stock"); if (!fStockDir.exists()) { fStockDir.mkdirs(); } FilenameFilter xlsxFilter = (File dir, String name) -> { String lowercaseName = name.toLowerCase(); return lowercaseName.endsWith(".xlsx"); }; File[] fStock = fStockDir.listFiles(xlsxFilter); for (File excel : fStock) { try (FileInputStream fis = new FileInputStream(excel); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); List<XSSFTable> lstTables = sheet.getTables(); if (!lstTables.isEmpty()) { shootTables(sheet, lstTables); } else { //System.out.println("we have one without a table"); Iterator<Row> itr = sheet.iterator(); boolean bData = false; int nRow = 0; while (itr.hasNext()) { Row row = itr.next(); int nCou = 0; Iterator<Cell> cellIterator = row.cellIterator(); List lstItem = new ArrayList(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); if (strCell.equalsIgnoreCase("Qty")) { nCou = nRow + 1; } if (bData) { lstItem.add(strCell); } break; case Cell.CELL_TYPE_NUMERIC: if (bData) { lstItem.add(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: if (bData) { lstItem.add(cell.getBooleanCellValue()); } break; case Cell.CELL_TYPE_BLANK: if (bData) { lstItem.add(""); } break; case Cell.CELL_TYPE_FORMULA: if (bData) { lstItem.add(cell.getCellFormula()); } break; default: } } if (bData) { System.out.print(lstItem); insertData(lstItem, true); } nRow++; if (nCou == nRow) { bData = true; } } //bIsGood = false; } } } } catch (FileNotFoundException fe) { //System.out.println("File not found exception " + fe.getMessage()); } catch (IOException ie) { //System.out.println("IO Exception " + ie.getMessage()); } } private void insertData(List list, boolean bData) { ItemDB item = new ItemDB(); Object[] objList = list.toArray(); if (objList.length > 5) { if (objList[1] != null) { item.setStrMfr(objList[1].toString()); } if (objList[2] != null) { item.setStrSKU(objList[2].toString()); } if (objList[3] != null) { item.setStrDescrip(objList[3].toString()); } if (objList[4] != null) { item.setStrSupplier(objList[4].toString()); } if (objList[5] != null) { if (objList[5] instanceof String) { item.setStrSupPart(objList[5].toString()); } else if (objList[5] instanceof Double) { Double dSupPart = (Double) objList[5]; item.setStrSupPart(dSupPart.toString()); } else { item.setStrSupPart(objList[5].toString()); } } if (item.getStrSKU() != null && item.getStrSupPart() != null) { if (!item.getStrSKU().isEmpty() || !item.getStrSupPart().isEmpty()) { if (bData) { aData.addItem(item); PartsData.add(item.createItem()); } else { ItemData.add(item.createItem()); } } } } } private List<ItemDB> createListFromTable(File fStock) { List<ItemDB> lstMain = new ArrayList<>(); logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath()); try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName()); FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator(); List lstItem; List<XSSFTable> lstTables = sheet.getTables(); logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size()); if (!lstTables.isEmpty()) { logger.log(Level.INFO, "the table is not empty"); for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0) .getEndCellReference().getRow(); j++) { ItemDB itmLine = new ItemDB(); lstItem = new ArrayList(); logger.log(Level.INFO, "we have some rows"); if (j > lstTables.get(0).getStartCellReference().getRow()) { Row row = sheet.getRow(j); if (row != null) { Iterator<Cell> cellIt = row.cellIterator(); logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext()); while (cellIt.hasNext()) { Cell cell = cellIt.next(); CellValue cellvalue = evaluator2.evaluate(cell); if (cellvalue == null) { lstItem.add(""); } else { switch (cellvalue.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cellvalue.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cellvalue.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cellvalue.getBooleanValue()); case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add("You found a formula"); break; default: } } } logger.log(Level.ALL, "lstItem is {0} and {1}", new Object[] { lstItem.size(), lstItem.toString() }); itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString())); logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString()); itmLine.setStrMfr(lstItem.get(1).toString()); itmLine.setStrSKU(lstItem.get(2).toString()); itmLine.setStrDescrip(lstItem.get(3).toString()); itmLine.setStrSupplier(lstItem.get(4).toString()); itmLine.setStrSupPart(lstItem.get(5).toString()); logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5)); if (lstItem.size() > 6) { if (!lstItem.get(6).toString().isEmpty()) { itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString())); } } //System.out.println("Added item " + itmLine.getStrDescrip()); lstMain.add(itmLine); } } } } } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } logger.log(Level.INFO, "returning {0}", lstMain.size()); return lstMain; } private void shootTables(XSSFSheet sheet, List<XSSFTable> list) { for (XSSFTable xTable : list) { for (int j = xTable.getStartCellReference().getRow(); j < xTable.getEndCellReference().getRow(); j++) { Row row = sheet.getRow(j + 1); List lstItem = new ArrayList(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String strCell = cell.getStringCellValue(); lstItem.add(strCell); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add(cell.getCellFormula()); break; default: } } insertData(lstItem, true); } } } /*public static void copyFile(File sourceFile, File destFile) throws IOException { if (!destFile.exists()) { destFile.createNewFile(); } FileChannel source = null; FileChannel destination = null; try { source = new FileInputStream(sourceFile).getChannel(); destination = new FileOutputStream(destFile).getChannel(); destination.transferFrom(source, 0, source.size()); } finally { if (source != null) { source.close(); } if (destination != null) { destination.close(); } } }*/ private void emailPurchasing(String strFilename) { Desktop desktop; if (Desktop.isDesktopSupported() && (desktop = Desktop.getDesktop()).isSupported(Desktop.Action.MAIL)) { try { if (!jReg.isbRegSetting("PurchasingEmail")) { } String strPDFile = strFilename.substring(0, strFilename.length() - 4) + "pdf"; String header = "mailto:seanc@wilson.ca?subject=Stock%20Order&body="; String body = "Hello Sean,\n\nCould you please order these items?\n\nDont forget to add this file" + strPDFile + "\n\nThank you\nBrian Green"; String urlBody = header + URLEncoder.encode(body, "UTF-8").replaceAll("\\+", "%20"); URI mailto = new URI(urlBody); desktop.mail(mailto); } catch (URISyntaxException | IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } else { // TODO fallback to some Runtime.exec(..) voodoo? throw new RuntimeException("desktop doesn't support mailto; mail is dead anyway ;)"); } } private void setMarkup() { TextInputDialog tidMarkup = new TextInputDialog(".80"); tidMarkup.setTitle("Markup Value"); tidMarkup.setContentText("Please enter the markup value between 0 and 1"); tidMarkup.setHeaderText("What is the Markup Value"); Optional<String> result = tidMarkup.showAndWait(); result.ifPresent(markup -> { jReg.setStrRegSetting("Markup", markup); }); } private class ButtonCell extends TableCell<Item, Double> { final Button cellButton = new Button("Add"); ButtonCell() { cellButton.setOnAction((ActionEvent event) -> { if (ButtonCell.this.getTableView().getItems().size() > ButtonCell.this.getIndex()) { Item item1 = ButtonCell.this.getTableView().getItems().get(ButtonCell.this.getIndex()); item1.setSdpQty(1); logger.log(Level.INFO, "and now item is... {0}", item1.createItemDB().toString()); ItemData.add(item1); } }); } @Override protected void updateItem(Double item, boolean empty) { super.updateItem(item, empty); if (!empty) { setGraphic(cellButton); } } } private void writeLog() { logger = Logger.getLogger("MyLog"); FileHandler fh; try { // This block configure the logger with handler and formatter fh = new FileHandler(new File(fUserDir, "MyLogFile.log").getAbsolutePath()); logger.addHandler(fh); SimpleFormatter formatter = new SimpleFormatter(); fh.setFormatter(formatter); // the following statement is used to log any messages logger.info("My first log"); } catch (SecurityException | IOException e) { logger.log(Level.INFO, "What we have here is a security risk {0}", e.getLocalizedMessage()); } } }