private async Task DownloadCellar()
{
string sRequest = $"https://www.cellartracker.com/xlquery.asp?table=Inventory&User={m_username}&Password={m_password}";
String sHtml = null;
try
{
HttpResponseMessage response = await m_client.GetAsync(sRequest);
response.EnsureSuccessStatusCode();
sHtml = await response.Content.ReadAsStringAsync();
}
catch (Exception exc)
{
MessageBox.Show($"Couldn't get stream from cellartracker: {exc.Message}");
return;
}
HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
doc.LoadHtml(sHtml);
m_cellar = Cellar.BuildFromDocument(doc);
PopulateFilters();
}
public async Task <int> FindAndDrinkWines(Cellar cellar, CtSql ctsql, bool fPreflightOnly)
{
Dictionary <string, Bottle> bottles = await ctsql.GetBottlesToDrink();
// MessageBox.Show($"Bottles we drank: {bottles.Count}");
// now, how many wines are still in the cellar? (these are un-drunk on CT)
int count = 0;
foreach (Bottle bottle in bottles.Values)
{
if (cellar.Contains(bottle.Barcode))
{
count++;
}
}
if (!fPreflightOnly)
{
MessageBox.Show($"There are {count} bottles to drink on CellarTracker");
// m_ctWeb.Show();
m_ctWeb.EnsureLoggedIn();
m_ctWeb.Show();
foreach (Bottle bottle in bottles.Values)
{
if (cellar.Contains(bottle.Barcode))
{
DateTime dttm;
if (!DateTime.TryParse(
bottle.GetValueOrEmpty("Consumed"),
CultureInfo.InvariantCulture,
DateTimeStyles.AdjustToUniversal | DateTimeStyles.AssumeUniversal,
out dttm))
{
dttm = DateTime.UtcNow;
}
m_ctWeb.DrinkWine(bottle.Barcode, bottle.GetValueOrEmpty("Notes"), dttm);
}
}
}
return(count++);
}
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);
}
public async Task <int> FindAndRelocateWines(Cellar cellar, CtSql ctsql, bool fPreflightOnly)
{
// NOTE: this list INCLUDES bottles that are consumed. be careful.
Dictionary <string, Bottle> bottles = await ctsql.GetBottlesToRelocate();
if (!fPreflightOnly)
{
MessageBox.Show($"Bottles we have binned: {bottles.Count}");
}
// how many of these don't match cellartracker?
Dictionary <string, Bottle> bottlesToUpdateOnCT = new Dictionary <string, Bottle>();
// find bottles in our list that don't match CT
foreach (Bottle bottle in bottles.Values)
{
if (bottle.IsConsumed)
{
continue;
}
if (cellar.Contains(bottle.Barcode))
{
if (cellar[bottle.Barcode].Bin != bottle.Bin)
{
bottlesToUpdateOnCT.Add(bottle.Barcode, bottle);
}
}
else
{
MessageBox.Show(
$"Found a bottle in our inventory that's not on CT: {bottle.Barcode}: {bottle.Wine}. Need to update CT?");
}
}
// now go through all the bottles on CT that have a Bin and make sure its in our inventory
foreach (Bottle bottle in cellar.Bottles)
{
if (string.IsNullOrEmpty(bottle.Bin) || bottle.Bin == "BINLESS")
{
continue;
}
if (bottles.ContainsKey(bottle.Barcode))
{
if (bottles[bottle.Barcode].Bin != bottle.Bin)
{
if (!bottlesToUpdateOnCT.ContainsKey(bottle.Barcode))
{
bottlesToUpdateOnCT.Add(bottle.Barcode, bottle);
}
else
{
if (bottlesToUpdateOnCT[bottle.Barcode].Bin != bottles[bottle.Barcode].Bin)
{
throw new Exception("broken symmetry");
}
}
}
}
else
{
MessageBox.Show(
$"Found a bottle on cellar tracker that has a bin, but it isn't in our inventory anymore ({bottle.Barcode}: {bottle.Wine} {bottle.Bin}). Need to remove from inventory?");
// at the very least, should we unbin it (relocate it to nowhere?)
}
}
if (!fPreflightOnly)
{
MessageBox.Show($"There are {bottlesToUpdateOnCT.Count} bottles to relocate on CellarTracker");
m_ctWeb.EnsureLoggedIn();
m_ctWeb.Show();
foreach (Bottle bottle in bottlesToUpdateOnCT.Values)
{
m_ctWeb.RelocateWine(bottle.Barcode, bottle.Bin);
}
}
return(bottlesToUpdateOnCT.Count);
}
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);
}
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);
}
