public List <Account> GetAll()
{
var accounts = new List <Account>();
MySqlDatabase.ExecuteReader("select * from accounts", reader => {
accounts.Add(NewAccountFromReader(reader));
});
return(accounts);
}
public int?GetAmountOfReviewsByRecipeId(object id)
{
int amount = 0;
MySqlDatabase.ExecuteReader($"select count(*) amount from reviews where recipeId = {id}", reader =>
{
amount = (int)MySqlDatabase.GetValueOrNull <long>(reader, "amount").Value;
});
return(amount);
}
public Recipe GetRecipeById(int id)
{
Recipe recipe = null;
MySqlDatabase.ExecuteReader($"select recipes.* accounts.username from recipes join accounts on accounts.id = recipes.accountId where id = {id}", reader =>
{
recipe = NewRecipeFromReader(reader);
});
return(recipe);
}
public List <Review> GetAllByRecipeId(int id)
{
var reviews = new List <Review>();
MySqlDatabase.ExecuteReader($"select reviews.*, accounts.username from reviews join accounts on accounts.id = reviews.accountId where recipeId = {id}", reader =>
{
reviews.Add(NewReviewFromReader(reader));
});
return(reviews);
}
public List <Recipe> GetAllByAccountId(int accountId)
{
var recipes = new List <Recipe>();
MySqlDatabase.ExecuteReader($"select recipes.*, accounts.username from recipes join accounts on accounts.id = recipes.accountId where accounts.id = {accountId}", reader =>
{
recipes.Add(NewRecipeFromReader(reader));
});
return(recipes);
}
public int?Create(PostReviewPostData data)
{
int?id = null;
MySqlDatabase.ExecuteReader($"select insertReview({data.AccountId},{data.RecipeId},'{data.Text}',{data.Rating}) id;", reader =>
{
if (reader.HasRows)
{
id = reader.GetInt32("id");
}
});
return(id);
}
/// <summary>
/// Returns all recipes that were eaten by an account today
/// </summary>
/// <param name="accountId">The id of the account</param>
/// <returns></returns>
public List <EatenRecipe> GetCurrentByAccountId(int accountId)
{
var eatenRecipes = new List <EatenRecipe>();
MySqlDatabase.ExecuteReader($"select eatenRecipes.*, recipes.calories, recipes.title, recipes.avgRating, recipes.reviewCount from eatenRecipes " +
$"join recipes on recipes.id = eatenRecipes.recipeId " +
$"where eatenRecipes.accountId = {accountId} and eatenRecipes.date = current_date()",
reader => {
eatenRecipes.Add(NewEatenRecipeFromReader(reader));
}
);
return(eatenRecipes);
}
public List <EatenRecipe> GetByAccountId(int accountId)
{
var eatenRecipes = new List <EatenRecipe>();
MySqlDatabase.ExecuteReader(
$"select eatenRecipes.*, recipes.calories, recipes.title, recipes.image from eatenRecipes " +
$"join recipes on recipes.id = eatenRecipes.recipeId " +
$"where eatenRecipes.accountId = {accountId}",
reader => {
eatenRecipes.Add(NewEatenRecipeFromReader(reader));
}
);
return(eatenRecipes);
}
private Account GetByParameter <T>(string columnName, T value)
{
Account acc = null;
// sql requires '' around strings
var query = typeof(T) == typeof(string)
? $"'{value}'"
: value.ToString();
MySqlDatabase.ExecuteReader(
$"select * from accounts where {columnName}={query}",
reader => {
acc = NewAccountFromReader(reader);
}
);
return(acc);
}
public object GetAllByRecipeId(int id, int offset, int length, string sortText, bool isAscending)
{
var reviews = new List <Review>();
var sortType = isAscending ? "ASC" : "DESC";
var columnName = "createdAt";
if (sortText.ToUpper() == "RATING")
{
columnName = "rating";
}
MySqlDatabase.ExecuteReader($"select reviews.*, accounts.username from reviews join accounts on accounts.id = reviews.accountId " +
$"where recipeId = {id} order by {columnName} {sortType} limit {offset}, {length}", reader =>
{
reviews.Add(NewReviewFromReader(reader));
});
return(reviews);
}
public List <Recipe> GetAll(int accountId, int pageNumber, bool isAscending, string query, string sortText)
{
var sortType = isAscending ? "ASC" : "DESC";
var length = 13;
var offset = length * (pageNumber - 1);
var sortColumn = "avgRating";
var recipes = new List <Recipe>();
if (sortText.ToUpper() == "TITLE")
{
sortColumn = "title";
}
var sql = $"select recipes.*, accounts.username from recipes join accounts on accounts.id = recipes.accountId where accountId = {accountId}" +
(query == "" ? " " : $" title like '%{query}%' ") +
$"order by {sortColumn} {sortType} limit {offset}, {length}";
MySqlDatabase.ExecuteReader(sql, reader =>
{
recipes.Add(NewRecipeFromReader(reader));
});
return(recipes);
}