PFC/Program/ClassRequests.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls.Primitives;
using System.Xml.Linq;

namespace PFC.Program
{
    /* Общий класс */
    public class CommonClass
    {
        // Метод возврата соединения к базе данных //
        public static SqlConnection GetConnection()
        {
            string connection = @"Data Source=(LocalDB)\MSSQLLocalDB;
                                  AttachDbFilename=|DataDirectory|\DB\PFC_DB.mdf;
                                  Integrated Security=True;
                                  Connect Timeout=30";
            SqlConnection conn = new SqlConnection(connection);
            return conn;
        }
    }

    /* Класс запросов */
    public class ClassRequests
    {
        /** МОДУЛЬ РЕГИСТРАЦИИ **/

        // Проверка на уникальность введенного логина
        public static SqlDataReader CheckUniqLogin_Registration()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = "SELECT login FROM Profiles";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Создание нового профиля
        public static void AddProfile_Registration(string login, string password, string secfrase)
        {
            string ExpressionAdd;

            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            if (secfrase == "")
            {
                ExpressionAdd = $@"INSERT INTO Profiles(login, password)
                                   VALUES (N'{login}', N'{password}')";
            }
            else {
                ExpressionAdd = $@"INSERT INTO Profiles(login, password, secPhrase)
                                   VALUES (N'{login}', N'{password}', N'{secfrase}')";
            }

            SqlCommand cmdExpressionAdd = new SqlCommand(ExpressionAdd, conn);
            cmdExpressionAdd.ExecuteNonQuery();
            conn.Close();
        }

        /** МОДУЛЬ АВТОРИЗАЦИИ **/

        // Получение данных для авторизации
        public static SqlDataReader GetReader_Authorization()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = "SELECT profile_id, login, password, secPhrase FROM Profiles";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Изменение пароля с помощью секретной фразы
        public static void ChangePassword_Authorization(string newPassword, string login)
        {
            string ExpressionAdd;

            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            ExpressionAdd = $@"UPDATE Profiles SET password = N'{newPassword}'
                               WHERE login = N'{login}'";
            

            SqlCommand cmdExpressionAdd = new SqlCommand(ExpressionAdd, conn);
            cmdExpressionAdd.ExecuteNonQuery();
            conn.Close();
        }

        /** МОДУЛЬ ПРОФИЛЯ **/

        // Изменение аватарки профиля
        public static void UpdateAva_Profile(int profile_ID, string path)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionCreateTable = $@"DECLARE @IMG VARBINARY(MAX)
                                              DECLARE @command NVARCHAR(1000)

                                              SET @command = N'SELECT @file1 = CAST(bulkcolumn AS VARBINARY(MAX))
                                              FROM OPENROWSET(BULK ''' + N'{path}' + ''',
                                              SINGLE_BLOB) ROW_SET'

                                              EXEC sp_executesql @command, N'@file1 VARBINARY(MAX) OUTPUT',@file1 =@IMG OUTPUT

                                              UPDATE Profiles
                                              SET avatar = @IMG
                                              WHERE (profile_ID = {profile_ID})";

            SqlCommand cmdExpressionCreate = new SqlCommand(ExpressionCreateTable, conn);
            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }

        // Удаление профиля
        public static bool DeleteProfile()
        {
            bool IsDelete = false;

            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionDelete = $@"DROP TABLE Txs_{GlobalVaribles.login}, 
                                                    Wallets_{GlobalVaribles.login};
                                         DELETE FROM Profiles WHERE profile_id = {GlobalVaribles.prof_ID};";
           
            SqlCommand cmdExpressionDelete = new SqlCommand(ExpressionDelete, conn);
            cmdExpressionDelete.ExecuteNonQuery();

            conn.Close();

            return IsDelete;
        }

        // Получение аватарки профиля
        public static SqlDataReader GetAva_Profile(int profile_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT avatar FROM Profiles
                                   WHERE (profile_ID = {profile_ID})";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }


        /** МОДУЛЬ КОШЕЛЬКА **/

        // Создание места для будущих кошельков при регистрации профиля
        public static void CreateSpaceWallets()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionCreateTable = $@"CREATE TABLE Wallets_{GlobalVaribles.login}
                                              (
                                                wallet_ID   INT IDENTITY(1,1) NOT NULL,
                                                profile_ID  INT               NOT NULL,
                                                name        NVARCHAR(50)      NOT NULL,
                                                balance     MONEY             NOT NULL,
                                                tCurrency   CHAR(3)           NULL
                                                
                                                CONSTRAINT PK_Wallets_{GlobalVaribles.login} PRIMARY KEY (wallet_ID),
                                                CONSTRAINT FK_Wallets_{GlobalVaribles.login} FOREIGN KEY (profile_ID) REFERENCES Profiles (profile_ID)
                                              )";

            SqlCommand cmdExpressionCreate = new SqlCommand(ExpressionCreateTable, conn);
            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }

        // Проверка на уникальность названия кошелька
        public static SqlDataReader CheckUniqNameWallet()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $"SELECT name FROM Wallets_{GlobalVaribles.login}";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Создание нового кошелька
        public static void CreateNewWallet(int profile_ID, string name, int balance)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            // TODO: в будущем добавить поле типа валюты
            string Expression = $@"INSERT INTO Wallets_{GlobalVaribles.login} (profile_ID, name, balance)
                                   VALUES ({profile_ID}, N'{name}', {balance})"; 

            SqlCommand cmdExpressionCreate = new SqlCommand(Expression, conn);
            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }

        // Передача данных о существующих кошельках текущего пользователя
        public static SqlDataReader GetInfoAboutWallets()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT name, balance, wallet_ID
                                   FROM Wallets_{GlobalVaribles.login}";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Получение кол-ва кошельков
        public static SqlDataReader GetCountWallets()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT COUNT(*) AS amount FROM Wallets_{GlobalVaribles.login}";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Удаление кошелька 
        public static bool DeleteWallet()
        {
            bool IsDelete = false;

            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionDelete = $@"DELETE FROM Wallets_{GlobalVaribles.login}
                                         WHERE (wallet_ID = {GlobalVaribles.selectWallet})";
            try
            {
                SqlCommand cmdExpressionDelete = new SqlCommand(ExpressionDelete, conn);
                cmdExpressionDelete.ExecuteNonQuery();
                IsDelete = true;
            }
            catch (SqlException)
            {
                MessageBox.Show("Стоп! Вы пытаетесь удалить кошелек, в котором имеются данные о расходах или доходах",
                                 "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }

            conn.Close();

            return IsDelete;
        }

        // Передача имени выбранного кошелька
        public static SqlDataReader GetNameChooseWallet(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT name FROM Wallets_{GlobalVaribles.login}
                                   WHERE ({wallet_ID} = Wallets_{GlobalVaribles.login}.wallet_ID)";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        /** МОДУЛЬ ТРАНЗАКЦИЙ **/

        // Создание таблицы транзакций для профиля
        public static void CreateTxsTable()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionCreateTable = $@"CREATE TABLE Txs_{GlobalVaribles.login}
                                              (
                                                tx_ID       INT IDENTITY(1,1) NOT NULL,
                                                wallet_ID   INT               NOT NULL,
                                                catTx_ID    INT               NOT NULL,
                                                sum         MONEY             NOT NULL,
                                                description NVARCHAR(250)     NULL,
                                                date        DATETIME          NOT NULL,

                                                CONSTRAINT PK_Txs_{GlobalVaribles.login} PRIMARY KEY (tx_ID),
                                                CONSTRAINT FK1_Txs_{GlobalVaribles.login}
                                                    FOREIGN KEY (wallet_ID)
                                                    REFERENCES Wallets_{GlobalVaribles.login} (wallet_ID),
                                                CONSTRAINT FK2_Txs_{GlobalVaribles.login}
                                                    FOREIGN KEY (catTx_ID)
                                                    REFERENCES CategoryTx (catTx_id)
                                              )";

            SqlCommand cmdExpressionCreate = new SqlCommand(ExpressionCreateTable, conn);
            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }
        
        // Полученике кол-ва транзакций в выбранном кошельке
        public static SqlDataReader GetCountTxs(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT COUNT(*) AS amount FROM Txs_{GlobalVaribles.login}
                                   WHERE ({wallet_ID} = Txs_{GlobalVaribles.login}.wallet_ID)";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Получение кол-ва категорий
        public static SqlDataReader GetCountCategories()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT COUNT(*) AS amount FROM CategoryTx";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Получение ID категорий
        public static SqlDataReader GetCategoriesID()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT catTx_id, name FROM CategoryTx";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Передача данных обо всех транзакциях текущего профиля
        public static SqlDataReader GetInfo_AboutTx(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT  CategoryTx.name, sum, description, date
                                   FROM CategoryTx, Txs_{GlobalVaribles.login}
                                   WHERE ({wallet_ID} = Txs_{GlobalVaribles.login}.wallet_ID) AND 
                                         (Txs_{GlobalVaribles.login}.catTx_ID = CategoryTx.catTx_id)
                                   ORDER BY date";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Удаление категории
        public static void DeleteCategory(int catTx_id)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionDelete = $@"DELETE FROM CategoryTx
                                         WHERE (catTx_id = {catTx_id})";
            try
            {
                SqlCommand cmdExpressionDelete = new SqlCommand(ExpressionDelete, conn);
                cmdExpressionDelete.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                MessageBox.Show("Стоп! Вы пытаетесь удалить категорию, которая уже используется в транзакциях" + 
                                "\nУдалите транзакции и повторите операцию снова.",
                                 "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }

            conn.Close();
        }

        // Добавление новой категории
        public static void CreateNewCategory(string name)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"INSERT INTO CategoryTx (name)
                                   VALUES (N'{name}')";

            SqlCommand cmdExpressionCreate = new SqlCommand(Expression, conn);
            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }

        // Передача имени категорий для сравнения
        public static SqlDataReader GetCategoryName()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $"SELECT name FROM CategoryTx";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Передача данных о транзакциях расхода
        public static SqlDataReader GetInfo_AboutExpTxs(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT CategoryTx.name, sum, description, date
                                   FROM CategoryTx, Txs_{GlobalVaribles.login}
                                   WHERE ({wallet_ID} = Txs_{GlobalVaribles.login}.wallet_ID) AND
                                   (Txs_{GlobalVaribles.login}.catTx_ID = CategoryTx.catTx_id) AND
                                   (sum < 0)";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Передача данных о транзакциях дохода
        public static SqlDataReader GetInfo_AboutPrfTxs(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT CategoryTx.name, sum, description, date
                                   FROM CategoryTx, Txs_{GlobalVaribles.login}
                                   WHERE ({wallet_ID} = Txs_{GlobalVaribles.login}.wallet_ID) AND
                                   (Txs_{GlobalVaribles.login}.catTx_ID = CategoryTx.catTx_id) AND
                                   (sum > 0)";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Получение reader'a для поиска транзакций расхода через ComboBox
        public static SqlDataReader GetReaderCbx_ExpTxs(int index)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT CategoryTx.name, sum, description, date
                                   FROM CategoryTx, Txs_{GlobalVaribles.login}
                                   WHERE ({index} = Txs_{GlobalVaribles.login}.catTx_id) AND 
                                         ({index} = CategoryTx.catTx_id) AND
                                         (sum < 0) AND ({GlobalVaribles.selectWallet} = wallet_ID)
                                   ORDER BY date";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Получение reader'a для поиска транзакций дохода через ComboBox
        public static SqlDataReader GetReaderCbx_PrfTxs(int index)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT CategoryTx.name, sum, description, date
                                   FROM CategoryTx, Txs_{GlobalVaribles.login}
                                   WHERE ({index} = Txs_{GlobalVaribles.login}.catTx_id) AND 
                                         ({index} = CategoryTx.catTx_id) AND
                                         (sum > 0) AND ({GlobalVaribles.selectWallet} = wallet_ID)
                                   ORDER BY date";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Получить сумму последней транзакции из таблицы 
        public static SqlDataReader GetLastSum_FromTxs(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT sum FROM Txs_{GlobalVaribles.login}
                                   WHERE tx_ID = (SELECT MAX(tx_ID) FROM Txs_{GlobalVaribles.login}
                                   WHERE wallet_ID = {wallet_ID})";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Удаление последней транзакции 
        public static void DeleteLastTx(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"DELETE FROM Txs_{GlobalVaribles.login}
                                   WHERE (tx_ID = (SELECT MAX(tx_ID) FROM Txs_{GlobalVaribles.login} 
                                   WHERE wallet_ID = {wallet_ID}))";

            SqlCommand cmdExpressionDelete = new SqlCommand(Expression, conn);
            cmdExpressionDelete.ExecuteNonQuery();

            conn.Close();
        }

        // Передача значений в ComboBox из категорий транзакций
        public static SqlDataReader GetInfo_FromCategoryTx()
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT name FROM CategoryTx";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Передача значений в таблицу транзакций
        public static void InsertData_Tx(int wallet_ID, int catTx_ID, int sum, string desc, string date)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"INSERT INTO Txs_{GlobalVaribles.login}
                                              (wallet_ID, catTx_ID, sum, description, date)
                                              VALUES
                                              ({wallet_ID}, {catTx_ID}, {sum}, N'{desc}', '{date}')";

            SqlCommand cmdExpressionCreate = new SqlCommand(Expression, conn);

            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }

        // Передача данных баланса
        public static SqlDataReader GetInfo_AboutBalance(int wallet_ID)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string Expression = $@"SELECT balance FROM Wallets_{GlobalVaribles.login}
                                   WHERE ({wallet_ID} = Wallets_{GlobalVaribles.login}.wallet_ID)";

            SqlCommand cmdExpression = new SqlCommand(Expression, conn);
            SqlDataReader reader = cmdExpression.ExecuteReader();
            return reader;
        }

        // Изменение баланса 
        public static void UpdateBalance(int wallet_ID, int newBalance)
        {
            SqlConnection conn = CommonClass.GetConnection();
            conn.Open();

            string ExpressionCreateTable = $@"UPDATE Wallets_{GlobalVaribles.login}
                                              SET balance = {newBalance}
                                              WHERE ({wallet_ID} = Wallets_{GlobalVaribles.login}.wallet_ID)";

            SqlCommand cmdExpressionCreate = new SqlCommand(ExpressionCreateTable, conn);
            cmdExpressionCreate.ExecuteNonQuery();
            conn.Close();
        }

    }
}