public string GetUIDByEmail(string email)
{
string UID = "";
try
{
string query = "SELECT A.Id FROM [dbo].[AspNetUsers] A WHERE A.Email = '" + email + "';";
SqlDataProvider db = new SqlDataProvider();
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
UID = reader["Id"].ToString();
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(UID);
}
/// <summary>
/// Adds a list of budgets belonging to a given user. Adding to the [Budget] and the [User_Budget] table.
/// </summary>
/// <param name="budgets"></param>
/// <param name="UserEmail"></param>
public void AddBudgets(List <BudgetModel> budgets, string UserEmail)
{
try
{
string queryTemplate = "INSERT INTO [dbo].[Budget] VALUES ({0},'{1}',{2},{3},{4});";
string query = "";
List <int> NewBudgetIDs = new List <int>();
foreach (BudgetModel budget in budgets)
{
//Category_ID, Description, Account_ID, Amount, Frequency_ID
query += string.Format(queryTemplate, budget.Category_ID, budget.Description, budget.Account_ID, budget.Amount, budget.Frequency_ID);
}
//Returns the ID of the budgets we just created
query += " SELECT SCOPE_IDENTITY() AS [NewIDs];";
SqlDataProvider db = new SqlDataProvider();
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
NewBudgetIDs.Add(Utilities.ParseInt(reader["NewIDs"].ToString()));
}
reader.Close();
string template = "INSERT INTO [dbo].[User_Budget] VALUES('{0}',{1}); ";
string UTQuery = "";
string UserUID = Utilities.GetUsersUID(UserEmail);
foreach (int id in NewBudgetIDs)
{
UTQuery += string.Format(template, UserUID, id);
}
SqlCommand UTcommand = db.CreateCommand(UTQuery, connection);
UTcommand.ExecuteScalar();
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Adds a list of transactions belonging to a given user
/// </summary>
/// <param name="transactions"></param>
/// <param name="UserEmail"></param>
public void AddTransactions(List <TransactionModel> transactions, string UserEmail)
{
try
{
SqlDataProvider db = new SqlDataProvider();
string queryTemplate = "INSERT INTO [dbo].[Transaction] VALUES ('{0}','{1}',{2},{3},{4},'{5}');";
string query = "";
List <int> NewTransactionIDs = new List <int>();
foreach (TransactionModel transaction in transactions)
{
query += string.Format(queryTemplate, transaction.Category, transaction.Description, transaction.Amount, transaction.AccountType, (transaction.IsEssential) ? 1 : 0, transaction.TransactionDate);
}
//Returns the ID of the transactions we just created
query += " SELECT SCOPE_IDENTITY() AS [NewIDs];";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
NewTransactionIDs.Add(Utilities.ParseInt(reader["NewIDs"].ToString()));
}
reader.Close();
string template = "INSERT INTO [dbo].[User_Transactions] VALUES('{0}',{1}); ";
string UTQuery = "";
string UserUID = Utilities.GetUsersUID(UserEmail);
foreach (int id in NewTransactionIDs)
{
UTQuery += string.Format(template, UserUID, id);
}
SqlCommand UTcommand = db.CreateCommand(UTQuery, connection);
UTcommand.ExecuteScalar();
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns a list of transactions for a given user
/// </summary>
/// <param name="UID"></param>
/// <returns></returns>
public List <TransactionModel> GetTransactionsByUID(string UID)
{
List <TransactionModel> transactions = new List <TransactionModel>();
try
{
string query = "SELECT * FROM [dbo].[Transaction] t left join [dbo].[User_Transactions] ut on t.ID = ut.Transaction_ID Where ut.UID = '" + UID + "' order by t.ID desc;";
SqlDataProvider db = new SqlDataProvider();
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
TransactionModel transaction = new TransactionModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Description = reader["Description"].ToString(),
IsEssential = Utilities.ParseBool(reader["IsEssential"].ToString()),
Category = Utilities.ParseInt(reader["Category_ID"].ToString()),
Amount = Utilities.ParseDecimal(reader["Amount"].ToString()),
AccountType = Utilities.ParseInt(reader["Account_ID"].ToString()),
TransactionDate = Utilities.ParseDateTime(reader["Date"].ToString())
};
transactions.Add(transaction);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(transactions);
}
/// <summary>
/// Returns a list of budgets belonging to a given user
/// </summary>
/// <param name="UID"></param>
/// <returns></returns>
public List <BudgetModel> GetBudgetsByUID(string UID)
{
List <BudgetModel> budgets = new List <BudgetModel>();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT * FROM [dbo].[Budget] as b left join [dbo].[User_Budget] as ub on b.ID = ub.Budget_ID Where ub.UID = '" + UID + "';";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
BudgetModel budget = new BudgetModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Category_ID = Utilities.ParseInt(reader["Category_ID"].ToString()),
Description = reader["Description"].ToString(),
Account_ID = Utilities.ParseInt(reader["Account_ID"].ToString()),
Amount = Utilities.ParseDecimal(reader["Amount"].ToString()),
Frequency_ID = Utilities.ParseInt(reader["Frequency_ID"].ToString()),
};
budgets.Add(budget);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(budgets);
}
/// <summary>
/// Gets a Transaction by Transaction ID.
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public TransactionModel GetTransactionByID(long ID)
{
string query = "SELECT * FROM [dbo].[Transaction] t WHERE t.ID = " + ID + ";";
TransactionModel transaction = new TransactionModel();
try
{
SqlDataProvider db = new SqlDataProvider();
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
transaction = new TransactionModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Category = Utilities.ParseInt(reader["Category_ID"].ToString()),
Description = reader["Description"].ToString(),
Amount = Utilities.ParseDecimal(reader["Amount"].ToString()),
AccountType = Utilities.ParseInt(reader["Account_ID"].ToString()),
TransactionDate = Utilities.ParseDateTime(reader["Date"].ToString()),
IsEssential = Utilities.ParseBool(reader["IsEssential"].ToString())
};
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(transaction);
}
/// <summary>
/// Returns a single budget model belonging to a given user
/// </summary>
/// <param name="UID"></param>
/// <returns></returns>
public BudgetModel GetBudgetByID(long ID)
{
BudgetModel budget = new BudgetModel();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT * FROM [dbo].[Budget] B WHERE B.ID = " + ID + ";";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
budget = new BudgetModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Category_ID = Utilities.ParseInt(reader["Category_ID"].ToString()),
Description = reader["Description"].ToString(),
Account_ID = Utilities.ParseInt(reader["Account_ID"].ToString()),
Amount = Utilities.ParseDecimal(reader["Amount"].ToString()),
Frequency_ID = Utilities.ParseInt(reader["Frequency_ID"].ToString()),
};
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(budget);
}
/// <summary>
/// Deletes record from [User_Accounts] where the ID matches
/// ([User_Accounts] relates [Account] to [ASP_NET_User])
/// </summary>
/// <param name="ID"></param>
public void DeleteAccountTypeByID(long ID)
{
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "DELETE FROM [dbo].[User_Accounts] WHERE Account_ID = " + ID + ";";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <FrequencyModel> GetAllFrequencies()
{
List <FrequencyModel> frequencies = new List <FrequencyModel>();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT * FROM [dbo].[Frequency];";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
FrequencyModel frequency = new FrequencyModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Frequency = reader["Frequency"].ToString()
};
frequencies.Add(frequency);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(frequencies);
}
/// <summary>
/// Returns a list of unique categories in budgets belonging to a given user
/// </summary>
/// <param name="UID"></param>
/// <returns></returns>
public List <CategoryModel> GetUniqueCategoryByUID(string UID)
{
List <CategoryModel> Categories = new List <CategoryModel>();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT distinct Category_ID, c.Category FROM [dbo].[Budget] as b left join [dbo].[User_Budget] as ub on b.ID = ub.Budget_ID left join [dbo].[Category] as c on b.Category_ID = c.ID Where ub.UID = '" + UID + "';";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
CategoryModel category = new CategoryModel()
{
ID = Utilities.ParseInt(reader["Category_ID"].ToString()),
Category = reader["Category"].ToString()
};
Categories.Add(category);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(Categories);
}
/// <summary>
/// Returns a list of AccountTypes for a given user
/// </summary>
/// <param name="UID"></param>
/// <returns></returns>
public List <CategoryModel> GetCategories()
{
List <CategoryModel> Categories = new List <CategoryModel>();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT * from Category";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
CategoryModel Category = new CategoryModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Category = reader["Category"].ToString()
};
Categories.Add(Category);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(Categories);
}
/// <summary>
/// Deletes a list of transactions by transaction ID
/// </summary>
/// <param name="transactions"></param>
public void DeleteTransactions(List <TransactionModel> transactions)
{
try
{
//Parse each id into query
string template = "DELETE FROM [dbo].[Transaction] WHERE ID IN ({0});";
string IDs = "";
//Create a string full of all transaction IDs to delete
for (int i = 0; i < transactions.Count; i++)
{
IDs += transactions[i].ID.ToString();
if (i != transactions.Count - 1)
{
IDs += ',';
}
}
string query = string.Format(template, IDs);
SqlDataProvider db = new SqlDataProvider();
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns a list of AccountTypes for a given user
/// </summary>
/// <param name="UID"></param>
/// <returns></returns>
public List <AccountTypeModel> GetAccountTypesByUID(string UID)
{
List <AccountTypeModel> AccountTypes = new List <AccountTypeModel>();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT ID, Type FROM [dbo].[User_Accounts] UA left join [dbo].[Account] A on UA.Account_ID = A.ID Where UA.UID = '" + UID + "';";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
AccountTypeModel AccountType = new AccountTypeModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
AccountType = reader["Type"].ToString()
};
AccountTypes.Add(AccountType);
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(AccountTypes);
}
/// <summary>
/// Deletes records from [User_Accounts] where the ID matches
/// ([User_Accounts] relates [Account] to [ASP_NET_User])
/// </summary>
/// <param name="accountTypes"></param>
public void DeleteAccountTypes(List <AccountTypeModel> accountTypes)
{
try
{
SqlDataProvider db = new SqlDataProvider();
//Parse each id into query
string template = "DELETE FROM [dbo].[User_Accounts] UA WHERE UA.Account_ID IN ({0});";
string IDs = "";
//Create a string full of all Account Types IDs to delete
for (int i = 0; i < accountTypes.Count; i++)
{
IDs += accountTypes[i].ID.ToString();
if (i != accountTypes.Count - 1)
{
IDs += ',';
}
}
string query = string.Format(template, IDs);
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Deletes a Budget that belongs to specified user, taking in a model rather than an ID
/// </summary>
/// <param name="budget"></param>
public void DeleteBudget(BudgetModel budget)
{
try
{
SqlDataProvider db = new SqlDataProvider();
//Parse each id into query
string query = "DELETE FROM [dbo].[User_Budget] AS UB WHERE UB.UID = '" + budget.UID + "' AND UB.Budget_ID = " + budget.ID + "; DELETE FROM [dbo].[Budget] AS b WHERE b.ID = " + budget.ID;
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Deletes a Budget that belongs to specified user
/// </summary>
/// <param name="budget"></param>
public void DeleteBudgetByIDAndUID(long id, string UID)
{
try
{
SqlDataProvider db = new SqlDataProvider();
//Parse each id into query
string query = "DELETE FROM [dbo].[User_Budget] WHERE UID = '" + UID + "' AND Budget_ID = " + id + "; DELETE FROM [dbo].[Budget] WHERE ID = " + id;
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public FrequencyModel GetFrequencyByBudgetUID(string UID)
{
FrequencyModel frequency = new FrequencyModel();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT * FROM [dbo].[Frequency] Fr LEFT JOIN [dbo].[Budget] B ON Fr.ID WHERE Fr.ID = B.Frequency_ID WHERE B.ID = " + UID + ";";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
frequency = new FrequencyModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
Frequency = reader["Frequency"].ToString()
};
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(frequency);
}
/// <summary>
/// Gets record from [Account] where the Account Type ID matches.
/// (Gets from [Account] and not [User_Accounts] which relates [Account] to [ASP_NET_User])
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public AccountTypeModel GetAccountTypeByID(long ID)
{
AccountTypeModel accountType = new AccountTypeModel();
try
{
SqlDataProvider db = new SqlDataProvider();
string query = "SELECT * FROM [dbo].[Account] a WHERE a.ID = " + ID + ";";
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
accountType = new AccountTypeModel()
{
ID = Utilities.ParseInt(reader["ID"].ToString()),
AccountType = reader["AccountType"].ToString()
};
}
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(accountType);
}
/// <summary>
/// Deletes a Transaction by Transaction ID.
/// </summary>
/// <param name="ID"></param>
/// <param name="UserEmail"></param>
public void DeleteTransactionByID(long ID, string UserEmail)
{
string UserUID = Utilities.GetUsersUID(UserEmail);
try
{
string query = "DELETE FROM [dbo].[User_Transactions] WHERE Transaction_ID = " + ID + " AND UID = '" + UserUID + "'; DELETE FROM [dbo].[Transaction] WHERE ID = " + ID + ";";
SqlDataProvider db = new SqlDataProvider();
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
reader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Adds to [Account] if the type doesn't already exists.
/// Also adds reference to account type into [User_Accounts]. (Links the account type to the user)
/// </summary>
/// <param name="AccoutTypes"></param>
/// <param name="UserEmail"></param>
public void AddAccountTypes(List <AccountTypeModel> AccoutTypes, string UserEmail)
{
SqlDataProvider db = new SqlDataProvider();
try
{
using (SqlConnection connection = (SqlConnection)db.GetConnection())
{
connection.Open();
string preCheck = "SELECT ID FROM [dbo].[Account] WHERE Type LIKE {0};";
string preCheckConditions = "";
string query = "";
for (int i = 0; i < AccoutTypes.Count; i++)
{
preCheckConditions += ("'" + AccoutTypes[i].ID + "'");
if (AccoutTypes.Count > 1 && i < AccoutTypes.Count - 1)
{
preCheckConditions += " OR ";
}
}
query = string.Format(preCheck, preCheckConditions);
SqlCommand preCheckCommand = db.CreateCommand(query, connection);
SqlDataReader preCheckReader = db.ExecuteReader(preCheckCommand);
List <int> PotentialDuplicateAccountTypeIDs = new List <int>();
while (preCheckReader.Read())
{
PotentialDuplicateAccountTypeIDs.Add(Utilities.ParseInt(preCheckReader["ID"].ToString()));
}
preCheckReader.Close();
List <AccountTypeModel> NonDuplicateAccoutTypes = new List <AccountTypeModel>();
for (int i = 0; i < AccoutTypes.Count; i++)
{
if (!PotentialDuplicateAccountTypeIDs.Contains(AccoutTypes[i].ID))
{
NonDuplicateAccoutTypes.Add(AccoutTypes[i]);
}
}
List <int> AccountTypeIDs = new List <int>();
if (NonDuplicateAccoutTypes.Count > 0)
{
string queryTemplate = "INSERT INTO [dbo].[Account] VALUES ('{0}');";
query = "";
foreach (AccountTypeModel Account in NonDuplicateAccoutTypes)
{
//AccountType
query += string.Format(queryTemplate, Account.AccountType);
}
//Returns the ID of the AccoutTypes we just created
query += " SELECT SCOPE_IDENTITY() AS [NewIDs];";
SqlCommand command = db.CreateCommand(query, connection);
SqlDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
AccountTypeIDs.Add(Utilities.ParseInt(reader["NewIDs"].ToString()));
}
reader.Close();
}
string template = "INSERT INTO [dbo].[User_Accounts] VALUES('{0}',{1}); ";
string UAQuery = "";
string UserUID = Utilities.GetUsersUID(UserEmail);
foreach (int id in AccountTypeIDs)
{
UAQuery += string.Format(template, UserUID, id);
}
SqlCommand UTcommand = db.CreateCommand(UAQuery, connection);
UTcommand.ExecuteScalar();
preCheckReader.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}