public async Task <ItemPriceHistoryModel[]> SelectItemPriceHistoriesAsync(PriceHistoryRequestModel[] models)
{
var ids = new List <string>();
foreach (var item in models)
{
ids.Add("'" + item.ItemID + item.Source + "'");
}
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {itemPriceHistorySelectColumns} FROM {itemPriceTable} WHERE CONCAT(itemid, source) IN ({String.Join(",", ids)})";
command.Parameters.AddWithValue("@itemids", ids.ToArray());
var savedSearches = new List <ItemPriceHistoryModel>();
await db.CommandReaderAsync(command, reader =>
{
var savedSearch = new ItemPriceHistoryModel();
int ordinal = 0;
savedSearch.ID = reader.GetInt32(ordinal++);
savedSearch.ItemID = reader.GetString(ordinal++);
savedSearch.Source = reader.GetString(ordinal++);
savedSearch.Title = reader.GetString(ordinal++);
savedSearch.Prices = JsonConvert.DeserializeObject <ItemPriceModel[]>(reader.GetString(ordinal++));
savedSearches.Add(savedSearch);
});
return(savedSearches.ToArray());
}
async Task job()
{
var newHistory = false;
var itemHistory = await db.PullTopItemHistoryAsync();
if (itemHistory == null)
{
return;
}
var items = await db.SelectItemsAsync(itemHistory);
var priceHistory = await db.SelectItemPriceHistoryAsync(new ItemPriceHistoryModel()
{
ItemID = itemHistory.ItemID,
Source = itemHistory.Source
});
if (priceHistory == null)
{
newHistory = true;
priceHistory = new ItemPriceHistoryModel()
{
ItemID = itemHistory.ItemID,
Source = itemHistory.Source,
Title = itemHistory.Title
};
}
var pricesModel = new List <ItemPriceModel>();
foreach (var item in items)
{
pricesModel.Add(new ItemPriceModel()
{
Date = item.CreatedDate,
Price = (item.Price.Length > 0) ? item.Price[0] : 0
});
}
if (priceHistory.Prices != null)
{
pricesModel.AddRange(priceHistory.Prices);
}
priceHistory.Prices = pricesModel.ToArray();
if (newHistory)
{
await db.AddItemPriceHistoryAsync(priceHistory);
}
else
{
await db.UpdateItemPriceHistoryAsync(priceHistory);
}
await db.DeleteItemHistoryByItemIDAsync(itemHistory);
}
public async Task AddItemPriceHistoryAsync(ItemPriceHistoryModel model)
{
await db.TransactionAsync(transaction =>
{
var cmdParameters = "@itemid, @source, @title, @history";
var cmd = new NpgsqlCommand();
cmd.Transaction = transaction;
cmd.Connection = transaction.Connection;
cmd.CommandText = $@"INSERT INTO {itemPriceTable} ({itemPriceHistoryInsertColumns}) VALUES ({cmdParameters});";
cmd.Parameters.AddWithValue("@itemid", model.ItemID);
cmd.Parameters.AddWithValue("@source", model.Source);
cmd.Parameters.AddWithValue("@title", model.Title);
cmd.Parameters.AddWithValue("@history", NpgsqlTypes.NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(model.Prices));
cmd.ExecuteNonQuery();
});
}
public async Task UpdateItemPriceHistoryAsync(ItemPriceHistoryModel model)
{
var parameters = "@history";
var command = new NpgsqlCommand();
string[] columnUpdates = itemPriceHistoryUpdateColumns.Replace(" ", "").Split(',');
string[] columnUpdatesParameters = parameters.Replace(" ", "").Split(',');
var columnUpdateCommand = new string[columnUpdates.Length];
for (int i = 0; i != columnUpdates.Length && i != columnUpdatesParameters.Length; i++)
{
columnUpdateCommand[i] = $"{columnUpdates[i]}={columnUpdatesParameters[i]}";
}
command.CommandText = $"UPDATE {itemPriceTable} SET {String.Join(",", columnUpdateCommand)} WHERE id=@id;";
command.Parameters.AddWithValue("@history", NpgsqlTypes.NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(model.Prices));
command.Parameters.AddWithValue("@id", model.ID);
await db.CommandNonqueryAsync(command);
}
public async Task <ItemPriceHistoryModel> SelectItemPriceHistoryAsync(ItemPriceHistoryModel model)
{
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {itemPriceHistorySelectColumns} FROM {itemPriceTable} WHERE itemid=@itemid AND source=@source";
command.Parameters.AddWithValue("@itemid", model.ItemID);
command.Parameters.AddWithValue("@source", model.Source);
ItemPriceHistoryModel savedSearch = null;
await db.CommandReaderAsync(command, reader =>
{
savedSearch = new ItemPriceHistoryModel();
int ordinal = 0;
savedSearch.ID = reader.GetInt32(ordinal++);
savedSearch.ItemID = reader.GetString(ordinal++);
savedSearch.Source = reader.GetString(ordinal++);
savedSearch.Title = reader.GetString(ordinal++);
savedSearch.Prices = JsonConvert.DeserializeObject <ItemPriceModel[]>(reader.GetString(ordinal++));
});
return(savedSearch);
}