public async Task <UserOverviewModel> GetUserOverview(string username)
{
UserOverviewModel overview = new UserOverviewModel();
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {savedSearchSelectColumns} FROM {savedSearchTable} WHERE username=@username;";
command.Parameters.AddWithValue("username", username);
var savedSearches = new List <SavedSearchModel>();
await db.CommandReaderAsync(command, reader =>
{
int ordinal = 0;
var savedSearch = new SavedSearchModel();
savedSearch.Created = reader.GetDateTime(ordinal++);
savedSearch.Criteria = JsonConvert.DeserializeObject <SearchCriteria>(reader.GetString(ordinal++));
savedSearch.Username = reader.GetString(ordinal++);
savedSearch.ID = reader.GetInt32(ordinal++);
savedSearches.Add(savedSearch);
});
overview.Username = username;
overview.SavedSearches = savedSearches.ToArray();
return(overview);
}
public async Task AddSavedSearchAsync(SavedSearchModel search)
{
await db.TransactionAsync(transaction =>
{
var cmdParameters = "@username, @criteria";
var cmd = new NpgsqlCommand();
cmd.Transaction = transaction;
cmd.Connection = transaction.Connection;
cmd.CommandText = $@"INSERT INTO {searchSaveTable} ({searchSaveInsertColumns}) VALUES ({cmdParameters});";
cmd.Parameters.AddWithValue("@username", search.Username);
cmd.Parameters.AddWithValue("@criteria", NpgsqlTypes.NpgsqlDbType.Jsonb, (search.Criteria == null) ? "" : JsonConvert.SerializeObject(search.Criteria));
cmd.ExecuteNonQuery();
});
}
public async Task <SavedSearchModel> SaveSearch(SavedSearchModel model)
{
//Only allow certain numbers of saves per user
if (await db.CountSavedSearchs(model.Username) > maxSavedSearchesPerUser)
{
var saves = await db.SelectSaveSearches(model.Username);
await db.DeleteSavedSearch(model.Username, saves.FirstOrDefault().ID);
}
await db.AddSavedSearchAsync(model);
return(model);
}
public IActionResult SaveSearch([FromBody] SearchCriteria criteria)
{
var model = new SavedSearchModel()
{
Criteria = criteria
};
if (HttpContext.User.Identity.IsAuthenticated)
{
model.Username = HttpContext.User.Identity.Name;
}
this.search.SaveSearch(model);
return(Ok());
}
public async Task <SavedSearchModel[]> SelectSaveSearches(string username)
{
var command = new NpgsqlCommand();
command.CommandText = $"SELECT {searchSaveSelectColumns} FROM {searchSaveTable} WHERE username=@username";
command.Parameters.AddWithValue("username", username);
var saves = new List <SavedSearchModel>();
await db.CommandReaderAsync(command, reader =>
{
var ord = 0;
var save = new SavedSearchModel();
save.ID = reader.GetInt32(ord++);
save.Username = reader.GetString(ord++);
save.Criteria = JsonConvert.DeserializeObject <SearchCriteria>(reader.GetString(ord++));
save.Created = reader.GetDateTime(ord++);
saves.Add(save);
});
return(saves.ToArray());
}
