List <Dictionary <string, int> > GetALUSets(ExcelQuery query, bool compareRows, List <string> searchTerms, List <string> excludes) { List <Dictionary <string, int> > set = new List <Dictionary <string, int> >(); List <List <string> > list = new List <List <string> >(); string err = string.Empty; //Get 1st sheet string[] sheets = query.GetSheetNames(); if (sheets == null || sheets.Length > 1) { Debug.LogError("Sheet missing or more than one sheet! Quitting"); return(null); } //use first sheet in our query, whatever it is named query.GetNewSheet(sheets[0]); //Get titles string[] titles = query.GetTitle(0, ref err); if (titles == null) { Debug.Log("No titles found! Quitting"); return(null); } int len = titles.Length; int uid_col = SheetUtils.FindMatchIndexesInStringArr(titles, UID_ColumnNames.ToArray())[0]; if (uid_col < 0) { Debug.LogError(string.Format("couldn't find UID column")); return(null); } else { Debug.Log(string.Format("Found uid_column at {0} which is header {1}", uid_col, query.GetHeaderColumnName(uid_col))); } HashSet <int> skips = new HashSet <int>(SheetUtils.FindMatchIndexesInStringArr(titles, UPC_ColumnNames.ToArray())); //search titles for "UPC" or "Barcode" for (int col = 0; col < len; col++) { foreach (string heading in searchTerms) { if (titles[col].Contains(heading, StringComparison.OrdinalIgnoreCase)) { bool exclude = false; foreach (string exclusion in excludes) { if (titles[col].Contains(exclusion, StringComparison.OrdinalIgnoreCase)) { exclude = true; } } if (exclude) { continue; } Dictionary <string, int> column = new Dictionary <string, int>(); set.Add(column); List <string> sublist = new List <string>(); list.Add(sublist); //add all values for that column to dictionary with row as value string[] vals = query.GetColumn <string>(col, "(_._)"); for (int row = 0; row < vals.Length; row++) { string val = vals[row]; if (val == "(_._)") { continue; //skip our null sentinel } sublist.Add(val); if (column.ContainsKey(val)) { if (compareRows) { LogResult(string.Format("found {0} misses between row {1} and row {2}", Compare_Rows_Skip_Mismatched_UPC_If_ItemIDs_Equal(query, row + 1, column[val] + 1, titles.Length, uid_col, skips), column[val] + 1, row + 1)); } else { LogResult(string.Format("Current row:{0} Subset for column:{1} already contains val:{2} at row:{3}", row + 2, query.GetHeaderColumnName(col), val, column[val] + 2)); } } else { column.Add(val, row); } } } } } aluColumns.Add(list); return(set); }
void ColumnCheck(ExcelQuery query, string heading) { string err = string.Empty; //use first sheet in our query, whatever it is named query.GetNewSheet(query.GetSheetNames()[0]); //Get titles string[] titles = query.GetTitle(0, ref err); if (titles == null) { Debug.Log("No titles found! Quitting"); return; } int len = titles.Length; int uid_col = SheetUtils.FindMatchIndexesInStringArr(titles, UID_ColumnNames.ToArray())[0]; if (uid_col < 0) { Debug.LogError(string.Format("couldn't find UID column")); return; } else { Debug.Log(string.Format("Found uid_column at {0} which is header {1}", uid_col, query.GetHeaderColumnName(uid_col))); } for (int col = 0; col < len; col++) { if (titles[col].Equals(heading, StringComparison.OrdinalIgnoreCase)) { Dictionary <long, int> column = new Dictionary <long, int>(); //add all values for that column to dictionary with row as value long[] vals = query.GetColumn <long>(col, -1); for (int row = 0; row < vals.Length; row++) { long val = vals[row]; if (val == -1) { continue; //skip our null sentinel } if (column.ContainsKey(val)) { string[] duplicate = query.GetRow(row + 1, "(_._)", len); string[] original = query.GetRow(column[val] + 1, "(_._)", len); int result = CompareRows(duplicate, original); if (result < 3 && original[uid_col] != duplicate[uid_col]) { LogResult(string.Format("Problem! found almost duplicate upc and row (misses:{0}) with non-identical itemids: row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); continue; } if (result > 2 && original[uid_col] == duplicate[uid_col]) { LogResult(string.Format("Problem! found duplicate upc with non-duplicate rows (misses:{0}) but with identical itemids: row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); continue; } if (result < 3 && result > 0) { LogResult(string.Format("found almost duplicate upc/itemid (misses:{0}) we expect item:{1} at row:{2} will not be added because item:{3} exists at row:{4}", result, duplicate[uid_col], row + 1, original[uid_col], column[val] + 1)); continue; } if (result < 1) { LogResult(string.Format("found duplicate upc/itemid (misses:{0}) we expect item:{1} at row:{2} will not be added because item:{3} exists at row:{4}", result, duplicate[uid_col], row + 1, original[uid_col], column[val] + 1)); continue; } if (result > 2) { LogResult(string.Format("found non-duplicate items with the same UPC: {0} misses between row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); continue; } LogResult(string.Format("somehow managed to miss a case: found {0} misses between row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col])); } else { column.Add(val, row); } } } } return; }