/* Returns a List of the PricePoints from the DB for a given Card for certain retailers retailers.*/
public List <PricePoint> GetPricePoints(MTGCard CardIn, List <string> RetailerList)
{
if (CardIn == null)
{
log.Error("SQLWrapper::GetPricePoints was given a null CardIn.");
return(null);
}
List <PricePoint> retPP = new List <PricePoint>();
try
{
using (SQLiteCommand cmd = new SQLiteCommand(MTGDB))
{
cmd.CommandText = "SELECT * FROM mtgPP WHERE cardName=@CNAME AND setName=@SNAME";
cmd.Parameters.AddWithValue("@SNAME", CardIn.SetName);
cmd.Parameters.AddWithValue("@CNAME", CardIn.CardName);
// If no retailers it will just select all, otherwise specify retailers.
if (RetailerList.Count > 0)
{
cmd.CommandText += " AND (";
for (int i = 0; i < RetailerList.Count; i++)
{
cmd.CommandText += "retailer = @RNAME" + i.ToString();
if (i < RetailerList.Count() - 1)
{
cmd.CommandText += " OR ";
} // Add OR for all statements except last one.
cmd.Parameters.AddWithValue("@RNAME" + i.ToString(), RetailerList[i]);
}
cmd.CommandText += ")";
}
cmd.CommandText += " ORDER BY priceDate DESC";
SQLiteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
PricePoint PP = new PricePoint();
PP.Date = (DateTime)rdr["priceDate"];
PP.Price = Convert.ToUInt64(rdr["price"]);
PP.Retailer = rdr["retailer"].ToString();
retPP.Add(PP);
}
}
}
catch (Exception err)
{
log.Warn("GetPricePoints() for card " + CardIn.CardName + " in set " + CardIn.SetName + " Err:", err);
}
return(retPP);
}
/*
* Add an average point for each unique datetime.
*/
private List <PricePoint> AddAverageFilter(ref List <PricePoint> DataIn)
{
DateTime curDate = DateTime.MinValue;
UInt64 sum = 0;
UInt64 count = 0;
List <PricePoint> listToAdd = new List <PricePoint>();
foreach (PricePoint pp in DataIn)
{
if (curDate == DateTime.MinValue)
{
// New day to get average for
curDate = pp.Date;
sum += pp.Price;
count = 1;
}
else if (curDate.Day == pp.Date.Day && curDate.Month == pp.Date.Month && curDate.Year == pp.Date.Year)
{
// Another PP for current day
sum += pp.Price;
count++;
}
else
{
// New day, so get average and insert it at appropriate place.
PricePoint newAvg = new PricePoint();
newAvg.Date = curDate;
newAvg.Price = (UInt64)(sum / count);
newAvg.Retailer = MTGUtilsAverage;
listToAdd.Add(newAvg);
curDate = pp.Date;
sum = pp.Price;
count = 1;
}
}
foreach (PricePoint pp in listToAdd)
{
DataIn.Add(pp);
}
DataIn = DataIn.OrderByDescending(pp => pp.Date).ToList();
return(DataIn);
}
/*
* JavaScript is used to populate the tbody, and the formatting on the data is odd,
* so pretty much just have to search for data and parse manually.
* Ignore all zero priced data as it's useless.
*/
public List <PricePoint> ParsePricePoints(string HTMLIn, MTGCard CardIn)
{
List <PricePoint> retPP = new List <PricePoint>();
// Double check formatting
if (HTMLIn.Contains("var results = [") == false || HTMLIn.Contains("var sellPriceData = [") == false)
{
log.Error("MTGPrice.com PricePoints formatting has been changed. Need to update the application.");
return(null);
}
if (CardIn.CardImageURL == null || CardIn.CardImageURL == "")
{
ParseCardImage(HTMLIn, CardIn);
}
int start = HTMLIn.IndexOf("var results = [");
int end = HTMLIn.IndexOf("var sellPriceData = [");
if (start == -1 || end == -1)
{
log.Error("MTGPrice.com PricePoints formatting has been changed. Need to update the application. (start = " + start + " end = " + end + ")");
return(null);
}
/*
* Formatting Example:
* {
* "color": "rgb(140,172,198)",
* "label": "HotSauce Games - $19.99",
* "lines": { "show": true, "fill": true },
* "data": [[1379804931252,29.99],[1379964574414,29.99]]
* },
*
* The above group repeats per Retailer
*/
string relevantData = HTMLIn.Substring(start, (end - start));
// Keep going until a '"label":' is not found.
string labelString = "\"label\": ";
string dataString = "\"data\": ";
while (true)
{
int labelIndex = relevantData.IndexOf(labelString);
if (labelIndex < 0)
{
// No more retailers, done!
break;
}
int commaIndex = relevantData.IndexOf(',', labelIndex);
string retailerName = relevantData.Substring((labelIndex + labelString.Length), commaIndex - labelIndex - labelString.Length);
// Format is now '"<NAME>" - $<PRICE>', remove quotes, and everything after name.
retailerName = retailerName.Substring(1, retailerName.IndexOf(" -") - 1);
int dataIndex = relevantData.IndexOf(dataString);
// Start at Index 1, as the 2nd-Xth strings will be "}DATA"
int endIndex = relevantData.IndexOf('}', dataIndex);
// Put just the price points into [DATE,PRICE],[DATE,PRICE] format and
// Trim the first/last '[' and ']'
string pureData = relevantData.Substring(dataIndex + dataString.Length, endIndex - dataIndex - dataString.Length);
pureData = pureData.Replace(" ", string.Empty);
pureData = pureData.Replace("\r", string.Empty);
pureData = pureData.Replace("\n", string.Empty);
if (pureData != null && pureData != "[]" && retailerName != "Best BUYLIST Price")
{
// Split based on delimiter "],[" and use "[[" and "]]" to remove first/last double brackets and then remove emptry entries
// The "[]" case takes care of empty brackets
string[] delim = new string[] { "],[", "[[", "]]", "[]" };
string[] splitData = pureData.Split(delim, StringSplitOptions.RemoveEmptyEntries);
// Data is now formatted as '[DATE,PRICE]'
foreach (string point in splitData)
{
string[] splitPoint = point.Split(',');
string stringPrice = splitPoint[1];
if (stringPrice == "0.0" || stringPrice == "0") // Ignore zero prices.
{
continue;
}
if (stringPrice.Contains('.'))
{
if (stringPrice.Length - stringPrice.LastIndexOf('.') == 2) // Adjust "1.X" to "1.X0"
{
stringPrice += "0";
}
else if (stringPrice.Length - stringPrice.LastIndexOf('.') > 3) // Adjust "1.XXxxxxxx" to "1.XX"
{
stringPrice = stringPrice.Substring(0, stringPrice.LastIndexOf('.') + 3);
}
stringPrice = stringPrice.Replace(".", string.Empty);
}
else
{
stringPrice = stringPrice + "00"; // Adjust "X" to "X00"
}
UInt64 Price = Convert.ToUInt64(stringPrice);
if (Price == 0)
{
log.Error(Price + " from '" + stringPrice + "'");
}
PricePoint tempPP = new PricePoint();
tempPP.Retailer = retailerName;
// Convert from Milliseconds since Unix Epoch to DateTime
tempPP.Date = new DateTime(1970, 1, 1, 0, 0, 0, 0);
tempPP.Date = tempPP.Date.AddSeconds(Convert.ToDouble(splitPoint[0]) / 1000).ToLocalTime();
tempPP.Price = Price;
retPP.Add(tempPP);
}
}
relevantData = relevantData.Substring(endIndex);
}
return(retPP);
}
