public List<UploadLog> uploadInvoice(String fileName) { // upload products to database String line; List<String> dataHolder = pdfExtractorUtility.extractOrder(fileName); // extract data from given fileName int lineNum = 1; List<VendorInventory> inventory = new ArrayList<VendorInventory>(); List<VendorInventory> insertionErrors = new ArrayList<VendorInventory>(); List<UploadLog> errorLogs = new ArrayList<UploadLog>(); VendorOrder vendorOrder; Product product; String[] splitLine; String vendorId; int quantity; double weight = 0; double price; double credit = 0; double deliveryFee = 0; double totalPrice = 0; Date orderDate = null; Date deliveryDate = null; Date currentDate = null; String vendorOrderId = null; String weightString; SimpleDateFormat dateFormat; boolean found = false; boolean firstDate = true; for (int i = 0; i < dataHolder.size(); i++) { // for each row in sheet line = dataHolder.get(i).trim(); // get current row log.debug(line); splitLine = line.split("^" + lineNum + "\\s{4}"); if (splitLine.length > 1) { // this is an item line lineNum++; if (line.contains(" 8338 ")) { // ignore line for Oma's info packet continue; } if (line.contains(" SRVCRG ")) { // add this charge to order charges table splitLine = line.split("^.+?(?=\\d+\\.\\d{2}$)"); deliveryFee = Double.parseDouble(splitLine[1]); continue; } line = splitLine[1]; // set line equal to everything after line number splitLine = line.split("(?<=\\d{2,4}\\w{0,2})\\s+", 2); vendorId = splitLine[0]; // set vendor id to first piece of split line = splitLine[1]; // set line equal to rest of split splitLine = line.split("\\d+\\.\\d{2}\\s+", 2); line = splitLine[1]; // remove quantity ordered splitLine = line.split("(?<=\\d+\\.\\d{2})\\s", 2); quantity = (int) Double.parseDouble(splitLine[0]); // set quantity received to first piece of split line = splitLine[1]; // set line equal to rest of split splitLine = line.split("^.+?(?=\\d+\\.?\\d*\\s+-?\\d+\\.\\d{2})", 2); line = splitLine[1]; // remove bill by label and product name along with trailing spaces splitLine = line.split("\\s+(?=-?\\d+\\.\\d{2}\\s+-?\\d+\\.\\d{2}$)", 2); weightString = splitLine[0]; // number representing either quantity or exact weight if (weightString.contains(".")) { // this is an exact weight weight = Double.parseDouble(weightString); } else { weight = 0; } line = splitLine[1]; splitLine = line.split("(?<=\\d+\\.\\d{2})\\s+", 2); price = Double.parseDouble(splitLine[0]); // set price each if (price < 0) { // this is a credit, add to credits if (weight != 0) { credit = credit + (new BigDecimal(price * weight) .setScale(2, BigDecimal.ROUND_HALF_UP) .doubleValue()); } else { credit = credit + (new BigDecimal(price * quantity) .setScale(2, BigDecimal.ROUND_HALF_UP) .doubleValue()); } continue; } else { // this is a billed item, add to total bill if (weight != 0) { totalPrice = totalPrice + (new BigDecimal(price * weight) .setScale(2, BigDecimal.ROUND_HALF_UP) .doubleValue()); log.debug( "item price so far: " + (new BigDecimal(price * weight).setScale(2, BigDecimal.ROUND_HALF_UP))); } else { totalPrice = totalPrice + (new BigDecimal(price * quantity) .setScale(2, BigDecimal.ROUND_HALF_UP) .doubleValue()); log.debug( "item price so far: " + (new BigDecimal(price * quantity).setScale(2, BigDecimal.ROUND_HALF_UP))); } } log.debug("total price so far: " + totalPrice); product = new Product(); product.setVendorId(vendorId); inventory.add(new VendorInventory(product, quantity, weight, price, false)); } else { if (line.contains("JPEELE") && !found) { // this is line than contains order and delivery dates and order id dateFormat = new SimpleDateFormat("M/d/yy"); found = true; splitLine = line.trim().split("\\s+"); for (String split : splitLine) { log.debug(split); if (split.contains("/")) { try { currentDate = dateFormat.parse(split); } catch (ParseException pe) { log.error("Unable to parse date", pe); } if (firstDate) { firstDate = false; orderDate = currentDate; } else { deliveryDate = currentDate; } } else if (!firstDate) { // this is order id vendorOrderId = split; } } log.debug( "Order Date= " + orderDate + "Delivery Date= " + deliveryDate + "Vendor Order Id= " + vendorOrderId); } } } String logDescription; List<String> headers; List<List<String>> logData; String orderId; log.debug("credit: " + credit); log.debug("total price: " + (totalPrice + deliveryFee + credit)); vendorOrder = new VendorOrder( credit * -1, deliveryFee, totalPrice + deliveryFee + credit, ProductGroup.OMAS, "Received", orderDate, deliveryDate, vendorOrderId); if ((vendorOrder = orderUtility.updateVendorOrder(vendorOrder)) == null) { logDescription = "Unable to upload vendor order information."; headers = Arrays.asList("Credit", "Delivery Fee", "Total Cost", "Status"); logData = new ArrayList<List<String>>(); logData.add( Arrays.asList( String.format("$%.2f", credit), String.format("$%.2f", deliveryFee), String.format("$%.2f", totalPrice), "Received")); errorLogs.add(new UploadLog(logDescription, headers, logData)); } else if (vendorOrder.getId() == null) { logDescription = "You are attempting to upload a duplicate order."; headers = Arrays.asList("Vendor Order Id"); logData = new ArrayList<List<String>>(); logData.add(Arrays.asList(vendorOrderId)); errorLogs.add(new UploadLog(logDescription, headers, logData)); } else { orderId = vendorOrder.getId(); for (VendorInventory vendorInventory : inventory) { if (productUtility.updateVendorInventoryItems( Arrays.asList(vendorInventory), Integer.parseInt(orderId), null, VendorOrder.STATUS.get("Received")) == null) { insertionErrors.add(vendorInventory); } } if (insertionErrors.size() > 0) { logDescription = "Vendor order items that were not uploaded properly."; headers = Arrays.asList("Vendor Id", "Quantity", "Weight", "Cost"); logData = new ArrayList<List<String>>(); for (VendorInventory vendorInventory : insertionErrors) { logData.add( Arrays.asList( vendorInventory.getProduct().getVendorId(), Double.toString(vendorInventory.getQuantity()), vendorInventory.getCostFormatted())); } errorLogs.add(new UploadLog(logDescription, headers, logData)); } log.debug(credit + ", " + deliveryFee); } return errorLogs; // this should be report of orphaned products/inventory }
/** upload product data to database from String containing file and return report */ public List<UploadLog> uploadProducts(String fileName) { // upload products to database List<List<String>> dataHolder = excelExtractorUtility.extractProducts(fileName); // extract products String name = null; // name of current item String previousName = null; // name of previous item int size; // number of cells in row int index; // index of start of white space and extra text String msrp = null; // value of msrp for row String id = null; // id for item String billBy = null; // unit to bill by String orderBy = null; // unit to order by String estimatedWeight = null; // estimated weight of item String[] estimatedRange = null; // holds the two numbers for estimatedWeight if it's a range List<Product> errorProducts = new ArrayList<Product>(); // products that didn't upload List<Product> products = new ArrayList<Product>(); // list of products to upload Product product = null; String defaultWeight = "0"; String defaultCaseWeight = "0"; String[] estimatedQuantityWeight; String[] nameSplit; String remainingName; String[] remainingNameSplit; String[] fraction; boolean mix = false; String mixString = null; for (int i = 0; i < dataHolder.size(); i++) { // for each row in sheet List<String> row = dataHolder.get(i); // get current row size = row.size(); // number of cells name = row.get(0); // get name from 1st cell if (name != null) { if (name.contains("Freeze Dried Treats")) { defaultWeight = ".25"; defaultCaseWeight = "3.0"; } if (name.contains("TEMPTINGS")) { defaultWeight = ".125"; defaultCaseWeight = "0"; } if (name.contains("Dr. Harveys Treats")) { defaultWeight = "0"; defaultCaseWeight = "0"; } if (name.contains("MIXES")) { mix = true; } if (name.contains("CHICKEN")) { mix = false; } } if (size > 10) { // if row has at least 10 cells msrp = row.get(7); // get msrp column if (msrp != null && (AdminUtilities.isNumeric(msrp.trim()) || msrp.trim() .contains( "mkt"))) { // check to see if this is a row with an item in it, otherwise // skip it name = name.replaceAll( new Character((char) 8237).toString(), ""); // remove weird special character name = name.replaceAll(" +", " "); index = name.toUpperCase().indexOf("(GREAT"); if (index >= 0) { name = name.substring(0, index); } index = name.toUpperCase().indexOf("SPECIAL ORDER"); if (index >= 0) { name = name.substring(0, index); } index = name.toUpperCase().indexOf("ORDER BY THE TUBE"); if (index >= 0) { name = name.substring(0, index); } index = name.toUpperCase().indexOf("CALL FOR AVAILABILITY"); if (index >= 0) { name = name.substring(0, index); } if (name.length() == 0) { // if name isn't there, this is a duplicate item of the previous row in case // quantity name = previousName; } billBy = AdminUtilities.formatUnit(row.get(6)); // replace unit abbr if necessary orderBy = AdminUtilities.formatUnit(row.get(5)); // replace unit abbr if necessary // get estimated weight if applicable estimatedWeight = "0"; if (name.toUpperCase().contains("LB")) { if (name.matches("(?i)^.*\\s+\\d+\\s+\\d+/\\d+\\s*lb.*$")) { nameSplit = name.split("(?i)\\s*lbs?\\.?"); // remove lb and any leading spaces // nameSplit = name.split("\\s*lb(?!.*\\d+\\s*/\\s*\\d)"); //remove lb and any leading // spaces name = nameSplit[0]; estimatedWeight = name; // temporarily set estimated weight to name, we will trim off name shortly name = name.split("(?i)\\s+\\d+\\s+\\d+/\\d+.*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" +", " "); // remove white space from estimated weight estimatedWeight = estimatedWeight.trim(); estimatedRange = estimatedWeight.split(" "); fraction = estimatedRange[1].split("/"); estimatedWeight = Double.toString( Double.parseDouble(estimatedRange[0]) + Double.parseDouble(fraction[0]) / Double.parseDouble(fraction[1])); } else if (name.matches("(?i)^.*\\d+\\.?\\d*\\s*/\\s*\\d+\\.?\\d*\\s*lb.*$")) { nameSplit = name.split( "(?i)\\s*lbs?\\.?(?!.*\\d+\\s*/\\s*\\d\\.?\\d*)"); // remove lb and any // leading spaces // nameSplit = name.split("\\s*lb(?!.*\\d+\\s*/\\s*\\d)"); //remove lb and any leading // spaces name = nameSplit[0]; estimatedWeight = name; // temporarily set estimated weight to name, we will trim off name shortly name = name.split("(?i)\\s+\\d+\\.?\\d*\\s*/\\s*\\d*\\.?\\d*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" ", ""); // remove white space from estimated weight estimatedRange = estimatedWeight.split("/"); // split the two numbers into an array estimatedWeight = Double.toString( Double.parseDouble(estimatedRange[0]) * Double.parseDouble( estimatedRange[1])); // multiply the two numbers } else { nameSplit = name.split("(?i)\\s*lbs?\\.?"); // remove lb and any leading spaces // nameSplit = name.split("\\s*lb"); //remove lb and any leading spaces name = nameSplit[0]; estimatedWeight = name; // temporarily set estimated weight to name, we will trim off name shortly name = name.split("(?i)\\s+\\d+\\.?\\d*\\s*-?\\s*\\d*\\.?\\d*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" ", ""); // remove white space from estimated weight if (estimatedWeight.contains("-")) { // this is a range, average the two numbers estimatedRange = estimatedWeight.split("-"); // split the two numbers into an array estimatedWeight = Double.toString( ((Double.parseDouble(estimatedRange[0]) + Double.parseDouble(estimatedRange[1]))) / 2); // average the two numbers } if (nameSplit.length > 1) { remainingName = nameSplit[1]; } else { remainingName = ""; } if (mix) { remainingNameSplit = remainingName.trim().split(" "); if (remainingNameSplit.length > 1 && remainingNameSplit[0].equals(remainingNameSplit[1])) { // 1lb mix, ignore mixString = ""; } else if (remainingName.contains("(") && remainingName.contains("-")) { // 2lb mix with list of vegetables mixString = remainingName.trim(); product.setProductName( product.getProductName() + "- " + AdminUtilities.toProperCase( mixString)); // set name of 1lb package appropriately name = name + "- " + mixString; // set name of 2lb package } else if (remainingName.contains("BOX")) { // 10 lb box name = name + " " + remainingName + " " + mixString.substring(mixString.indexOf(" ")); } else { // 5 or 10 pound mix name = name + "- " + mixString; } } else { name = name + " " + remainingName.trim(); } } } else if (name.contains("#")) { nameSplit = name.split("\\s*#"); // remove lb and any leading spaces // nameSplit = name.split("\\s*lb(?!.*\\d+\\s*/\\s*\\d)"); //remove lb and any leading // spaces name = nameSplit[0]; estimatedWeight = name; // temporarily set estimated weight to name, we will trim off name shortly if (name.contains("/")) { name = name.split("\\s+\\d+\\.?\\d*\\s*/\\s*\\d*\\.?\\d*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" ", ""); // remove white space from estimated weight estimatedRange = estimatedWeight.split("/"); // split the two numbers into an array estimatedWeight = Integer.toString( Integer.parseInt(estimatedRange[0]) * Integer.parseInt(estimatedRange[1])); // multiply the two numbers } else { name = name.split("\\s+\\d+\\.?\\d*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" ", ""); // remove white space from estimated weight } if (nameSplit.length > 1) { name = name + " " + nameSplit[1].trim(); } } else if (name.toUpperCase().contains(" OZ")) { nameSplit = name.split("(?i)\\s*oz\\.?"); // remove lb and any leading spaces // nameSplit = name.split("\\s*lb(?!.*\\d+\\s*/\\s*\\d)"); //remove lb and any leading // spaces name = nameSplit[0]; estimatedWeight = name; // temporarily set estimated weight to name, we will trim off name shortly if (name.matches("^.*\\s+\\d+\\.?\\d*\\s*-\\s*\\d*\\.?\\d*$")) { name = name.split("\\s+\\d+\\.?\\d*\\s*-\\s*\\d*\\.?\\d*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" ", ""); // remove white space from estimated weight estimatedRange = estimatedWeight.split("-"); // split the two numbers into an array estimatedWeight = Double.toString( ((double) (Integer.parseInt(estimatedRange[0]) + Integer.parseInt(estimatedRange[1]))) / 32); // average two numbers } else { name = name.split("\\s+\\d+\\.?\\d*$")[ 0]; // split off estimated weight or weight range from rest of name estimatedWeight = estimatedWeight.substring(name.length()); // trim name from estimatedWeight estimatedWeight = estimatedWeight.replaceAll(" ", ""); // remove white space from estimated weight estimatedWeight = Double.toString(Double.parseDouble(estimatedWeight) / 16); } if (nameSplit.length > 1) { name = name + " " + nameSplit[1].trim(); } } else if (name.contains("mg. ")) { nameSplit = name.split("\\s+(?=\\d+-\\d+\\s+mg)"); // remove everything before weight estimatedWeight = nameSplit[1]; name = nameSplit[0]; estimatedWeight = estimatedWeight.split("\\s+mg")[0]; // remove everything after weight estimatedQuantityWeight = estimatedWeight.split("-"); estimatedWeight = Double.toString( ((double) (Integer.parseInt(estimatedQuantityWeight[0]) * Integer.parseInt(estimatedQuantityWeight[1]) / 4480)) / 100); } else if (name.contains("gm. ")) { nameSplit = name.split("\\s+(?=\\d+\\s+gm)"); estimatedWeight = nameSplit[1]; // remove everything before weight name = nameSplit[0]; estimatedWeight = estimatedWeight.split("\\s+gm")[0]; // remove everything after weight } else if (!defaultWeight.equals("0") && billBy != "Case" && estimatedWeight.equals( "0")) { // this is a set weight for items sold individually for the whole group of // products estimatedWeight = defaultWeight; } else if (!defaultCaseWeight.equals("0") && billBy == "Case" && estimatedWeight.equals( "0")) { // this is a set weight for items sold by the case for the whole group of // products estimatedWeight = defaultCaseWeight; } id = row.get(4); try { id = Integer.toString( (int) Double.parseDouble( id)); // if this has an extraneous .0 because of import from excel // sheet, remove } catch (NumberFormatException nfe) { } double msrpDouble = 0; if (!msrp.contains("mkt")) { msrpDouble = Double.parseDouble(msrp); } try { product = new Product( AdminUtilities.toProperCase(name), msrpDouble, orderBy, billBy, Double.parseDouble(estimatedWeight), "", id); } catch (Exception e) { log.error("Unable to add product " + name, e); } products.add(product); previousName = name; } } } for (Product uploadProduct : products) { if (productUtility.updateProduct(uploadProduct) == null) { // update product in database errorProducts.add(uploadProduct); } } List<UploadLog> uploadLogs = productUtility .generateProductErrorReport(); // report of items that don't have uploaded info from // Excel file if (errorProducts.size() != 0) { String logDescription = "Products that failed in upload to Database"; List<String> headings = Arrays.asList( "Vendor Id", "Name", "price", "Order By", "Bill By", "Estimated Weight", "Vendor"); List<List<String>> logRows = new ArrayList<List<String>>(); for (Product errorProduct : errorProducts) { logRows.add( Arrays.asList( errorProduct.getVendorId(), errorProduct.getProductName(), Double.toString(errorProduct.getPrice()), errorProduct.getOrderBy(), errorProduct.getBillBy(), Double.toString(errorProduct.getEstimatedWeight()), ProductGroup.OMAS)); // add information about each product with error to log } uploadLogs.add(new UploadLog(logDescription, headings, logRows)); } return uploadLogs; }