public void printCellData(
List<List<String>> dataHolder) { // test function to print data to standard output
String name = null; // name of current item
String previousName = null; // name of previous item
String cell = null; // string to hold cell value
int size; // number of cells in row
int index; // index of start of white space and extra text
String line = null; // line of output for each row
String msrp = null; // value of msrp for row
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
line = "";
if (size > 8) { // if row has at least 8 cells
msrp = row.get(7); // get msrp column
if (msrp != null
&& (AdminUtilities.isNumeric(msrp.trim())
|| msrp.trim()
.equals(
"mkt"))) { // check to see if this is a row with an item in it, otherwise
// skip it
name = row.get(0); // get name from 1st cell
index = name.indexOf(" "); // index of extra white space and text
if (index > -1) { // if there is extra white space and text
name = name.substring(0, index); // strip extra white space and text from name
} else {
index = name.indexOf(" ("); // index of extra white space and parenthesis
if (index > -1) { // if there is extra white space and parenthesis
name = name.substring(0, index); // strip extra white space and parenthesis from name
}
}
if (name.length()
== 0) { // if name isn't there, this is a duplicate item of the previous row in case
// quantity
name = previousName;
}
line = AdminUtilities.toProperCase(name) + ", ";
for (int j = 1; j < row.size(); j++) { // for each cell in row
cell = row.get(j);
if (j == 4) {
try {
cell =
Integer.toString(
(int)
Double.parseDouble(
cell)); // if this has an extraneous .0 because of import from excel
// sheet, remove
} catch (NumberFormatException nfe) {
}
}
if (cell != null
&& cell.length() > 0
&& !cell.trim().equals("^ ^")
&& !cell.trim().equals("####")) {
line = line + cell.trim() + ", ";
}
}
System.out.println(line.substring(0, line.length() - 2));
previousName = name;
}
}
}
}
/** 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;
}
