Beispiel #1
0
        public static Cellar BuildFromDocument(HtmlDocument doc)
        {
            BottleBuilder builder = new BottleBuilder();

            builder.SetColumns(doc);

            HtmlNodeCollection nodes  = doc.DocumentNode.SelectNodes("//tr");
            Cellar             cellar = new Cellar();

            foreach (HtmlNode node in nodes)
            {
                if (node.ChildNodes[0].Name == "th")
                {
                    continue;
                }

                Bottle bottle = builder.BuildBottleFromRow(node);

                cellar.m_bottles.Add(bottle.Barcode, bottle);
            }
            return(cellar);
        }
Beispiel #2
0
        Bottle BottleFromReader(SqlReader sqlr)
        {
            Bottle bottle = new Bottle();

            int i = 0;

            SetValueFromSqlString(bottle, sqlr, i++, "Barcode");
            SetValueFromSqlString(bottle, sqlr, i++, "Wine");
            SetValueFromSqlString(bottle, sqlr, i++, "Vintage");
            SetValueFromSqlString(bottle, sqlr, i++, "Locale");
            SetValueFromSqlString(bottle, sqlr, i++, "Country");
            SetValueFromSqlString(bottle, sqlr, i++, "Region");
            SetValueFromSqlString(bottle, sqlr, i++, "SubRegion");
            SetValueFromSqlString(bottle, sqlr, i++, "Appellation");
            SetValueFromSqlString(bottle, sqlr, i++, "Producer");
            SetValueFromSqlString(bottle, sqlr, i++, "Type");
            SetValueFromSqlString(bottle, sqlr, i++, "Color");
            SetValueFromSqlString(bottle, sqlr, i++, "Category");
            SetValueFromSqlString(bottle, sqlr, i++, "Varietal");
            SetValueFromSqlString(bottle, sqlr, i++, "Designation");
            SetValueFromSqlString(bottle, sqlr, i++, "Vineyard");
            SetValueFromSqlString(bottle, sqlr, i++, "Score");
            SetValueFromSqlString(bottle, sqlr, i++, "Begin");
            SetValueFromSqlString(bottle, sqlr, i++, "End");
            SetValueFromSqlString(bottle, sqlr, i++, "iWine");
            SetValueFromSqlString(bottle, sqlr, i++, "Notes");
            SetValueFromSqlString(bottle, sqlr, i++, "Bin");
            SetValueFromSqlString(bottle, sqlr, i++, "Location");
            bottle.SetValue("UpdatedCT", sqlr.Reader.GetBoolean(i++).ToString());
            // consumed is a date...
            if (!sqlr.Reader.IsDBNull(i))
            {
                bottle.SetValue("Consumed", sqlr.Reader.GetDateTime(i).ToString("MM/dd/yyyy"));
            }
            i++;


            return(bottle);
        }
Beispiel #3
0
 public static int SortByColor(Bottle left, Bottle right)
 {
     return(CompareBottle(left, right, false /*fVarietal*/));
 }
Beispiel #4
0
 public static int SortByVarietal(Bottle left, Bottle right)
 {
     return(CompareBottle(left, right, true /*fVarietal*/));
 }
Beispiel #5
0
        public static int CompareBottle(Bottle left, Bottle right, bool fVarietal)
        {
            int n = 0;

            if (fVarietal)
            {
                n = String.Compare(left.Varietal, right.Varietal, StringComparison.OrdinalIgnoreCase);
                if (n != 0)
                {
                    return(n);
                }
            }
            else
            {
                n = String.Compare(left.Color, right.Color, StringComparison.OrdinalIgnoreCase);
                if (n != 0)
                {
                    return(n);
                }
            }

            // first, compare the country
            n = String.Compare(left.Country, right.Country, StringComparison.OrdinalIgnoreCase);
            if (n != 0)
            {
                return(n);
            }

            n = String.Compare(left.SubRegion, right.SubRegion, StringComparison.OrdinalIgnoreCase);
            if (n != 0)
            {
                return(n);
            }

            n = String.Compare(left.Appellation, right.Appellation, StringComparison.OrdinalIgnoreCase);
            if (n != 0)
            {
                return(n);
            }

            if (!Int32.TryParse(left.Vintage, out int leftVintage))
            {
                leftVintage = 0;
            }

            if (!Int32.TryParse(right.Vintage, out int rightVintage))
            {
                rightVintage = 0;
            }

            if (leftVintage != rightVintage)
            {
                return(leftVintage - rightVintage);
            }

            n = String.Compare(left.Wine, right.Wine, StringComparison.OrdinalIgnoreCase);
            if (n != 0)
            {
                return(n);
            }

            return(String.Compare(left.Location, right.Location, StringComparison.OrdinalIgnoreCase));
        }
Beispiel #6
0
 public Bottle(Bottle bottle)
 {
     m_bottleValues = new Dictionary <string, string>(bottle.m_bottleValues);
 }
Beispiel #7
0
        public static WineList BuildFromCellar(Cellar cellar, string[] rgsLocations, string[] rgsColor, bool fGroupByVarietal)
        {
            WineList list = new WineList();
            Dictionary <string, int> bottlesSeen = new Dictionary <string, int>();
            StringBuilder            sbListName  = new StringBuilder();

            foreach (string sLocation in rgsLocations)
            {
                sbListName.Append($"_{sLocation}");
            }

            foreach (string sColor in rgsColor)
            {
                sbListName.Append($"_{sColor}");
            }

            if (fGroupByVarietal)
            {
                sbListName.Append("_Varietal");
            }

            list.ListName    = sbListName.ToString();
            list.BottleCount = 0;

            // collect the number of bottles seen
            foreach (Bottle bottle in cellar.Bottles)
            {
                if (bottle.Bin == "BINLESS")
                {
                    continue;
                }

                if (rgsLocations != null)
                {
                    bool fMatchLocation = false;

                    foreach (string sLocation in rgsLocations)
                    {
                        if (string.Compare(bottle.Location, sLocation, true) == 0)
                        {
                            fMatchLocation = true;
                            break;
                        }
                    }

                    if (!fMatchLocation)
                    {
                        continue;
                    }
                }

                list.BottleCount++;
                if (bottlesSeen.ContainsKey(bottle.Wine))
                {
                    bottlesSeen[bottle.Wine]++;
                }
                else
                {
                    bottlesSeen.Add(bottle.Wine, 1);
                }
            }

            foreach (Bottle bottle in cellar.Bottles)
            {
                if (bottle.Bin == "BINLESS")
                {
                    continue;
                }

                if (rgsLocations != null)
                {
                    bool fMatchLocation = false;

                    foreach (string sLocation in rgsLocations)
                    {
                        if (string.Compare(bottle.Location, sLocation, true) == 0)
                        {
                            fMatchLocation = true;
                            break;
                        }
                    }

                    if (!fMatchLocation)
                    {
                        continue;
                    }
                }

                if (rgsColor != null)
                {
                    bool fMatchColor = false;

                    foreach (string sColor in rgsColor)
                    {
                        if (string.Compare(bottle.Color, sColor, true) == 0)
                        {
                            fMatchColor = true;
                            break;
                        }
                    }

                    if (!fMatchColor)
                    {
                        continue;
                    }
                }

                if (bottlesSeen[bottle.Wine] == 0)
                {
                    continue; // we already added this bottle to the list
                }
                Bottle bottleNew = new Bottle(bottle)
                {
                    Count = bottlesSeen[bottle.Wine]
                };

                bottlesSeen[bottle.Wine] = 0;   // we've added it to the list, so don't add again...

                list.m_bottles.Add(bottleNew);
            }

            if (fGroupByVarietal)
            {
                list.m_bottles.Sort(Bottle.SortByVarietal);
            }
            else
            {
                list.m_bottles.Sort(Bottle.SortByColor);
            }

            return(list);
        }
Beispiel #8
0
        public async Task <(int, int, int, int)> UpdateLocalDatabaseFromDownloadedCellar(Cellar cellar, bool fFixLeadingZeros, bool fPreflightOnly)
        {
            await EnsureSqlConnectionString();

            // loop over all of our bottles and add the ones that are missing, and/or fix the scancodes for those
            // that have missing leading zeros
            SR sr;

            sr = TCore.Sql.OpenConnection(out Sql sql, sSqlConnectionString);

            if (!sr.Succeeded)
            {
                throw new Exception($"can't open SQL connection: {sr.Reason}");
            }

            string sSelect = "select ScanCode from upc_wines";

            sql.ExecuteReader(sSelect, out SqlReader sqlr, null);
            HashSet <string> hashOurBottles = new HashSet <string>();

            while (sqlr.Reader.Read())
            {
                string s = sqlr.Reader.GetString(0);
                hashOurBottles.Add(s);
            }

            sqlr.Close();

            // now we have all of the bottles we know about
            // build all the bottles from cellartracker

            HashSet <string> hashTheirBottles = new HashSet <string>();

            foreach (Bottle bottle in cellar.Bottles)
            {
                hashTheirBottles.Add(bottle.Barcode);
            }

            // now, which bottles do they have, but we don't
            HashSet <string> hashBottlesOnlyInCellarTracker     = new HashSet <string>();
            HashSet <string> hashBottlesWithMissingLeadingZero  = new HashSet <string>();
            HashSet <string> hashBottlesWithMissingLeadingZero2 = new HashSet <string>();
            HashSet <string> hashBottlesWithMissingLeadingZero3 = new HashSet <string>();
            HashSet <string> hashBottlesOnlyInOurCellar         = new HashSet <string>();

            foreach (string s in hashTheirBottles)
            {
                if (!hashOurBottles.Contains(s))
                {
                    // bottle is missing. check to see if there's a missing leading zero
                    if (s.StartsWith("0") && hashOurBottles.Contains(s.Substring(1)))
                    {
                        hashBottlesWithMissingLeadingZero.Add(s);
                    }
                    else if (s.StartsWith("00") && hashOurBottles.Contains(s.Substring(2)))
                    {
                        hashBottlesWithMissingLeadingZero2.Add(s);
                    }
                    else if (s.StartsWith("000") && hashOurBottles.Contains(s.Substring(3)))
                    {
                        hashBottlesWithMissingLeadingZero3.Add(s);
                    }

                    else
                    {
                        hashBottlesOnlyInCellarTracker.Add(s);
                    }
                }
            }

            // now, let's not have the jurassic park problem...check the other direction too
            foreach (string s in hashOurBottles)
            {
                if (!hashTheirBottles.Contains(s))
                {
                    // bottle is not on cellar tracker...check to see if missing leading zero
                    if (hashTheirBottles.Contains($"0{s}"))
                    {
                        if (!hashBottlesWithMissingLeadingZero.Contains($"0{s}"))
                        {
                            MessageBox.Show($"Strange. We had a missing leading zero, didn't find it in the first pass... ({s})");
                            hashBottlesWithMissingLeadingZero.Add($"0{s}");
                        }
                    }
                    else
                    {
                        // CT only tells us about undrunk wines...so maybe this is one we already drank?
                        // or its one that we knew about at one point, but not later...
                        // let's not worry about them
                        // hashBottlesOnlyInOurCellar.Add(s);
                    }
                }
            }

            if (!fPreflightOnly)
            {
                // at this point, we know what we have to add to our cellar, and what we have to fix
                MessageBox.Show(
                    $"BrokenZeros1: {hashBottlesWithMissingLeadingZero.Count}, BrokenZeros2: {hashBottlesWithMissingLeadingZero2.Count}, BrokenZeros3: {hashBottlesWithMissingLeadingZero3.Count}. {hashBottlesOnlyInOurCellar.Count} only in our cellar, and {hashBottlesOnlyInCellarTracker.Count} only in CellarTracker");


                if (fFixLeadingZeros && (hashBottlesWithMissingLeadingZero.Count > 0 || hashBottlesWithMissingLeadingZero2.Count > 0))
                {
                    FixLeadingZeros(sql, hashBottlesWithMissingLeadingZero, hashBottlesWithMissingLeadingZero2);
                }

                // now add any bottles that haven't been added yet...
                //
                sql.BeginTransaction();

                foreach (string s in hashBottlesOnlyInCellarTracker)
                {
                    Bottle bottle = cellar[s];

                    string sInsert =
                        "insert into upc_wines (ScanCode, Wine, Vintage, Locale, Country, Region, SubRegion, Appelation, Producer, [Type], Color, Category, Varietal, Designation, Vineyard, Score, [Begin], [End], iWine, Consumed, Notes, UpdatedCT, Bin, Location)"
                        + " VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',{21},'{22}','{23}')";

                    string sConsumed = bottle.GetValueOrEmpty("Consumed");

                    if (sConsumed.Length == 0)
                    {
                        sConsumed = "1900-01-01 00";
                    }

                    string sUpdatedCT = bottle.GetValueOrEmpty("UpdatedCT");

                    if (sUpdatedCT.Length == 0)
                    {
                        sUpdatedCT = "0";
                    }

                    string sQuery = String.Format(
                        sInsert,
                        Sql.Sqlify(bottle.GetValueOrEmpty("Barcode")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Wine")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Vintage")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Locale")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Country")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Region")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("SubRegion")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Appellation")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Producer")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Type")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Color")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Category")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Varietal")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Designation")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Vineyard")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Score")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Begin")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("End")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("iWine")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Consumed")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Notes")),
                        Sql.Sqlify(sUpdatedCT),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Bin")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Location")));

                    string sResult = sql.SExecuteScalar(sQuery);

                    sQuery = String.Format(
                        "insert into upc_codes (ScanCode, DescriptionShort, FirstScanDate,LastScanDate) VALUES ('{0}','{1}','{2}','{3}')",
                        Sql.Sqlify(bottle.GetValueOrEmpty("Barcode")),
                        Sql.Sqlify(bottle.GetValueOrEmpty("Wine")),
                        "1900-01-01 00:00:00.000",
                        "1900-01-01 00:00:00.000");

                    sResult = sql.SExecuteScalar(sQuery);
                }
                sql.Commit();
            }
            return(hashBottlesWithMissingLeadingZero.Count, hashBottlesWithMissingLeadingZero2.Count, hashBottlesOnlyInOurCellar.Count, hashBottlesOnlyInCellarTracker.Count);
        }