public override void InsertRealmReport(WarcraftAPI.AuctionApiResponse report)
{
lock (connection)
{
long timestamp = new DateTimeOffset(report.lastModified).ToUnixTimeSeconds();
long storedTimestamp = long.MinValue;
string sql = $@"SELECT * FROM ""last_modified"" WHERE realm_id = {report.realmId};";
NpgsqlCommand cmd = new NpgsqlCommand(sql, connection);
using (var reader = cmd.ExecuteReader())
if (reader.Read())
{
storedTimestamp = (long)reader[1];
}
if (storedTimestamp >= timestamp)
{
return;
}
sql = $@"INSERT INTO ""last_modified"" VALUES({report.realmId}, '{timestamp}') ON CONFLICT (realm_id) DO UPDATE SET timestamp = EXCLUDED.timestamp;";
cmd = new NpgsqlCommand(sql, connection);
cmd.ExecuteNonQuery();
foreach (var lot in report.auctions)
{
string values = $@"{timestamp}, {lot.id}, {lot.item.id}, '{(int)Enum.Parse(typeof(TimeLeft), lot.time_left)}', {lot.quantity}, {Math.Max(lot.buyout, lot.unit_price)}";
sql = $@"INSERT INTO ""{report.realmId}"" VALUES ({values});";
cmd = new NpgsqlCommand(sql, connection);
cmd.ExecuteNonQuery();
}
}
}
public override void InsertRealmReport(WarcraftAPI.AuctionApiResponse report)
{
long timestamp = new DateTimeOffset(report.lastModified).ToUnixTimeSeconds();
foreach (var lot in report.auctions)
{
string values = $@"{timestamp}, {lot.id}, {lot.item.id}, '{lot.time_left}', {lot.quantity}, {lot.unit_price}, {lot.buyout}, {lot.bid}";
string sql = $@"INSERT OR IGNORE INTO ""{report.realmId}"" VALUES ({values});";
SQLiteCommand sqlite_cmd = connection.CreateCommand();
sqlite_cmd.CommandText = sql;
sqlite_cmd.ExecuteNonQuery();
}
}
void AuctionNewReportHandler(object sender, AuctionReportEventArgs e)
{
WarcraftAPI.AuctionApiResponse report = e.Report;
string realms = "";
foreach (var realm in ConnectedRealms.GetRealmSlugsById(report.realmId))
{
realms += $@"""{realm}"" ";
}
realms = realms.Trim();
logger.Info($"Inserting new data into database for realm(s): {realms} ({report.realmId})");
DateTime starttime = DateTime.UtcNow;
db.InsertRealmReport(report);
logger.Info($"Insertion done for realm(s) {realms} ({report.realmId}) in {DateTime.UtcNow.Subtract(starttime)}");
}
public abstract void InsertRealmReport(WarcraftAPI.AuctionApiResponse report);
