Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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;
        }