Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        /// <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);
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
        /// <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);
        }
Esempio n. 6
0
        /// <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);
        }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        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
        }