/// <summary>
/// Deletes a specific ShopList in the ShoppingList (SQLite) database table, if it exists.
/// </summary>
/// <param name="shopList"></param>
/// <returns>
/// Returns a Tuple<bool, string>, with a processing message.
/// </returns>
public static Tuple <bool, string> DeleteShopList(ShopList shopList)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite))
{
try
{
if (SADatabaseReader.DoesShopListExist(shopList))
{
_ = connection.Delete(shopList);
if (!(SADatabaseReader.DoesShopListExist(shopList)))
{
result = new Tuple <bool, string>(true, $"ShopList {shopList.Name}({shopList.ShopListID}) has been deleted.");
}
else
{
result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.ShopListID}) still exists.");
}
}
else
{
result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.ShopListID}) hasn't been saved yet or doesn't exist .");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.DeleteShopList: Error logging naar log.db
throw;
}
}
return(result);
}
/// <summary>
/// Deletes a ShopList member from the ShopListMember table.
/// </summary>
/// <param name="shopList"></param>
/// <param name="user"></param>
/// <returns>
/// Returns a Tuple<bool, string>, with a processing message.
/// </returns>
public static Tuple <bool, string> DeleteShopListMember(ShopList shopList, AppUser user)
{
var result = new Tuple <bool, string>(false, "");
using (var connection = new SQLiteConnection(LoadConnectionString()))
{
try
{
connection.Execute($"DELETE FROM ShopListMember WHERE AppUser_ID = {user.ID} AND ShopList_ID = {shopList.SHOPLISTID}");
if (!(SADatabaseReader.DoesShopListMemberExist(shopList, user)))
{
result = new Tuple <bool, string>(true, $"ShopListMember {user.UserName}({user.ID}) is verwijderd.");
}
else
{
result = new Tuple <bool, string>(false, $"ShopListMember {user.UserName}({user.ID}) bestaat nog.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.DeleteShopListMember: Error logging naar log.db
throw;
}
}
return(result);
}
/// <summary>
/// Saves the changes made on the passed ShopList object to the ShoppingList (SQLite) database table. And if the shoplist already exists, that list will get updated instead of insterted.
/// </summary>
/// <param name="shopList"></param>
/// <returns>
/// Returns a Tuple<bool, string>, with a processing message.
/// </returns>
public static Tuple <bool, string> SaveShopList(ShopList shopList)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite))
{
try
{
if (SADatabaseReader.DoesShopListExist(shopList))
{
_ = connection.Update(shopList);
result = new Tuple <bool, string>(true, $"ShopList {shopList.Name}({shopList.ShopListID}) has been updated.");
}
else
{
_ = connection.Insert(shopList);
result = new Tuple <bool, string>(true, $"ShopList {shopList.Name}({shopList.ShopListID}) has been added.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.SaveShopList: Error logging naar log.db
throw;
}
}
return(result);
}
/// <summary>
/// Updates or creates a shoplist owner record in the ShopListOwner table.
/// </summary>
/// <param name="shopList"></param>
/// <param name="user"></param>
/// <returns>
/// Returns a Tuple<bool, string> with a processing message.
/// </returns>
public static Tuple <bool, string> UpdateShopListOwner(ShopList shopList, AppUser user)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite))
{
try
{
if (SADatabaseReader.DoesShopListExist(shopList))
{
connection.Execute(
"INSERT OR REPLACE INTO ShopListOwner (AppUser_ID, ShopList_ID, DateTimeModified) " +
$"VALUES ({user.ID}, {shopList.SHOPLISTID}, '{DateTimeStamp.Stamp()}')"
);
result = new Tuple <bool, string>(false, $"De owner van shoplist {shopList.Name}({shopList.SHOPLISTID}) is bijgewerkt.");
}
else
{
result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.UpdateShopListOwner: Error logging naar log.db
throw;
}
}
return(result);
}
/// <summary>
/// Deletes a Shoplist item from the ShopListItem table.
/// </summary>
/// <param name="shopList"></param>
/// <param name="item"></param>
/// <returns>
/// Returns a Tuple<bool, string> with a processing message.
/// </returns>
public static Tuple <bool, string> DeleteShopListItem(ShopList shopList, ShopListItem item)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString()))
{
try
{
if (SADatabaseReader.DoesShopListItemExist((int)shopList.SHOPLISTID, item))
{
connection.Execute($"DELETE FROM ShopListItem WHERE ShopList_ID = {shopList.SHOPLISTID} AND ShopListProduct_ID = {item.ShopListProductID}");
result = new Tuple <bool, string>(true, $"ShopList item {item.ProductName}({item.ShopListProductID}) succesvol verwijderd.");
}
else
{
result = new Tuple <bool, string>(false, $"ShopList item {item.ProductName}({item.ShopListProductID}) bestaat niet.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.DeleteShopListItem: Error logging naar log.db
throw;
}
}
return(result);
}
/// <summary>
/// Adds a record to the ShopListMember table, with a foreign keys to the AppUser and ShopList tables.
/// </summary>
/// <param name="shopList"></param>
/// <param name="user"></param>
/// <returns>
/// Returns a Tuple<bool, string>, with a processing message.
/// </returns>
public static Tuple <bool, string> AddShopListMember(ShopList shopList, AppUser user)
{
var result = new Tuple <bool, string>(false, "");
using (var connection = new SQLiteConnection(LoadConnectionString()))
{
try
{
if (SADatabaseReader.DoesShopListExist(shopList))
{
var AppUserExists = connection.Query <AppUser>($"SELECT * FROM AppUser WHERE ID = {user.ID}").Count;
if (AppUserExists > 0)
{
if (!(SADatabaseReader.DoesShopListMemberExist(shopList, user)))
{
connection.Execute(
"INSERT OR REPLACE INTO ShopListMember (AppUser_ID, ShopList_ID, DateTimeAdded) " +
$"VALUES ({user.ID}, {shopList.SHOPLISTID}, '{DateTimeStamp.Stamp()}')"
);
result = new Tuple <bool, string>(false, $"Member {user.UserName}({user.ID}) is toegevoegd aan shoplist {shopList.Name}({shopList.SHOPLISTID}).");
}
else
{
result = new Tuple <bool, string>(false, $"Gebruiker {user.UserName}({user.ID}) bestaat al.");
}
}
else
{
result = new Tuple <bool, string>(false, $"Gebruiker {user.UserName}({user.ID}) bestaat niet of is nog niet opgeslagen.");
}
}
else
{
result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet of is nog niet opgeslagen.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.AddShopListMember: Error logging naar log.db
throw;
}
}
return(result);
}
/// <summary>
/// Saves or updates a shopList item in the ShopListItem table.
/// </summary>
/// <param name="shopList"></param>
/// <param name="item"></param>
/// <returns>
/// Returns a Tuple<bool, string>, with a processing message.
/// </returns>
public static Tuple <bool, string> SaveShopListItem(ShopList shopList, ShopListItem item)
{
Tuple <bool, string> result = new Tuple <bool, string>(false, "");
using (SQLiteConnection connection = new SQLiteConnection(LoadConnectionString(), SQLiteOpenFlags.ReadWrite))
{
try
{
if (SADatabaseReader.DoesShopListExist(shopList))
{
if (SADatabaseReader.DoesShopListItemExist((int)shopList.SHOPLISTID, item))
{
string updateQuery =
$"UPDATE ShopListItem SET Unit = {item.Unit}, Amount = {item.Amount}, ItemAcquired = {item.Acquired}, DateTimeModified = '{item.ITEMUPDATED}' " +
$"WHERE ShopList_ID = {shopList.SHOPLISTID} AND ShopListProduct_ID = {item.ShopListProductID}";
connection.Execute(updateQuery);
result = new Tuple <bool, string>(true, $"ShopList item {item.ShopListProductID} is bijgewerkt.");
}
else
{
string insertQuery =
"INSERT INTO ShopListItem (ShopList_ID, ShopListProduct_ID, Unit, Amount, ItemAcquired, DateTimeAdded) " +
$"VALUES ({shopList.SHOPLISTID}, {item.ShopListProductID}, {item.Unit}, {item.Amount}, {item.Acquired}, '{item.ItemAdded}')";
connection.Execute(insertQuery);
result = new Tuple <bool, string>(true, $"ShopList item {item.ShopListProductID} is toegevoegd aan {shopList.Name}({shopList.SHOPLISTID}).");
}
}
else
{
result = new Tuple <bool, string>(false, $"ShopList {shopList.Name}({shopList.SHOPLISTID}) bestaat niet of is nog niet opgeslagen.");
}
}
catch (SQLiteException ex)
{
// TODO : SADatabaseReader.SaveShopListItem: Error logging naar log.db
throw;
}
}
return(result);
}
static void Main()
{
// TODO : Integreer in ShopAssist app.
#region Reader
Console.WriteLine("Products:");
IList <Products> products = SADatabaseReader.LoadProducts();
foreach (Products product in products)
{
Console.WriteLine(product.ToString());
}
Console.WriteLine("\nAppUsers:");
IList <AppUser> users = SADatabaseReader.LoadAppUsers();
foreach (AppUser user in users)
{
Console.WriteLine(user.ToString());
}
Console.WriteLine("\nBrands:");
IList <Brand> brands = SADatabaseReader.LoadBrands();
foreach (Brand brand in brands)
{
Console.WriteLine(brand.ToString());
}
Console.WriteLine("\nShoppinglists:");
IList <ShopList> shopLists = SADatabaseReader.LoadShopLists();
foreach (ShopList shopList in shopLists)
{
Console.WriteLine(shopList.ToString());
}
Console.WriteLine("\nShoppinglist members:");
IList <ShopListMember> shopListMembers = SADatabaseReader.LoadShopListMembers(1);
foreach (ShopListMember shopListMember in shopListMembers)
{
Console.WriteLine(shopListMember.ToString());
}
Console.WriteLine("\nShoppinglist content:");
IList <ShopListItem> items = SADatabaseReader.LoadShopListItems(1);
foreach (ShopListItem item in items)
{
Console.WriteLine(item.ToString());
}
#endregion
#region UpdateShopList test
/*
* ShopList list = new ShopList();
* //list.ShopListID = SADatabaseReader.GetNewShopListID();
* list.Name = "Kerst kerkdienst middelen";
* list.ListCategory = 2;
* list.DateTimeAdded = "00:00:00 03-01-2021"; // TODO : Method voor het vullen van een datetime in de voorgestelde format;
* // TODO : DateTimeModified instellen, mits changed property op true staat;
*
* var result = SADatabaseWriter.SaveShopList(list);
* Console.WriteLine($"\nUpdate result is: {result.Item2}");
*
* Console.WriteLine("\nUpdated shoppinglists:");
* shopLists = SADatabaseReader.LoadShopLists();
*
* foreach (ShopList shopList in shopLists)
* {
* Console.WriteLine(shopList.ToString());
* }
*/
#endregion
#region DeleteShopList test
/*
* ShopList list = new ShopList();
* list.ShopListID = 2;
*
* var result = SADatabaseWriter.DeleteShopList(list);
* Console.WriteLine($"\nDelete result is: {result.Item2}");
*
* Console.WriteLine("\nUpdated shoppinglists:");
* shopLists = SADatabaseReader.LoadShopLists();
*
* foreach (ShopList shopList in shopLists)
* {
* Console.WriteLine(shopList.ToString());
* }
*/
#endregion
#region SaveShopListItem test
/*
* ShopList list = new ShopList();
* list.ShopListID = 3;
*
* ShopListItem shopListItem = new ShopListItem();
* shopListItem.ShopListProductID = 1;
* shopListItem.Unit = 2;
* shopListItem.Amount = 10;
* shopListItem.Acquired = 0;
* shopListItem.ItemAdded = DateTimeStamp.Stamp();
* shopListItem.ShopListItemChanged = true;
*
* var result = SADatabaseWriter.SaveShopListItem(list, shopListItem);
* Console.WriteLine($"\nShopListItem Save result is: {result.Item2}");
*
* Console.WriteLine("\nShoppinglist content:");
* IList<ShopListItem> shopListItems = SADatabaseReader.LoadShopListItems(3);
*
* foreach (ShopListItem item in shopListItems)
* {
* Console.WriteLine(item.ToString());
* }
*/
#endregion
#region DeleteShopListItem test
/*
* ShopList list = new ShopList();
* list.ShopListID = 4;
*
* ShopListItem shopListItem = new ShopListItem();
* shopListItem.ShopListProductID = 1;
*
* var result = SADatabaseWriter.DeleteShopListItem(list, shopListItem);
* Console.WriteLine($"\nShopListItem Delete result is: {result.Item2}");
*
* Console.WriteLine("\nShoppinglist content:");
* IList<ShopListItem> shopListItems = SADatabaseReader.LoadShopListItems(3);
*
* foreach (ShopListItem item in shopListItems)
* {
* Console.WriteLine(item.ToString());
* }
*/
#endregion
#region Save ShopListOwner test
/*
* AppUser user1 = new AppUser();
* user1.ID = 2;
*
* AppUser user2 = new AppUser();
* user2.ID = 1;
*
* ShopList list = new ShopList();
* list.ShopListID = 1;
*
* var result = SADatabaseWriter.UpdateShopListOwner(list, user1);
* Console.WriteLine($"\nShopList owner save result: {result.Item2}");
*
* var IsUser1ListOwner = SADatabaseReader.GetShopListOwnerID(list, user1);
* Console.WriteLine($"Is user1 the listOwner: {IsUser1ListOwner}");
*
* var IsUser2ListOwner = SADatabaseReader.GetShopListOwnerID(list, user2);
* Console.WriteLine($"Is user2 the listOwner: {IsUser2ListOwner}");
*/
#endregion
#region Delete ShopList owner
/*
* AppUser user1 = new AppUser();
* user1.ID = 2;
* user1.UserName = "******";
*
* ShopList list = new ShopList();
* list.ShopListID = 1;
*
* var result = SADatabaseWriter.DeleteShopListOwner(list, user1);
* Console.WriteLine($"\nShopList owner delete result: {result.Item2}");
*
* var IsUser1ListOwner = SADatabaseReader.GetShopListOwnerID(list, user1);
* Console.WriteLine($"Is user1 nog steeds een listOwner: {IsUser1ListOwner}");
*/
#endregion
#region Add ShopList member test
/*
* AppUser user1 = new AppUser();
* user1.ID = 2;
* user1.UserName = "******";
*
* AppUser user2 = new AppUser();
* user2.ID = 3;
* user2.UserName = "******";
*
* ShopList list = new ShopList();
* list.ShopListID = 1;
*
* var result = SADatabaseWriter.AddShopListMember(list, user1);
* Console.WriteLine($"\nShopList member add result: {result.Item2}");
*
* var IsUser1ListMember = SADatabaseReader.GetShopListMemberID(list, user1);
* Console.WriteLine($"Is user1 the listmember: {IsUser1ListMember}");
*
* var IsUser2ListMember = SADatabaseReader.GetShopListMemberID(list, user2);
* Console.WriteLine($"Is user2 the listmember: {IsUser2ListMember}");
*/
#endregion
#region Delete ShopList member
/*
* AppUser user1 = new AppUser();
* user1.ID = 2;
* user1.UserName = "******";
*
* ShopList list = new ShopList();
* list.ShopListID = 1;
*
* var result = SADatabaseWriter.DeleteShopListMember(list, user1);
* Console.WriteLine($"\nShopList member delete result: {result.Item2}");
*
* var IsUser1ListMember = SADatabaseReader.GetShopListMemberID(list, user1);
* Console.WriteLine($"Is user1 nog steeds een listmember: {IsUser1ListMember}");
*/
#endregion
}