private void Deleteaccount_btn_Click(object sender, EventArgs e)
{
// get textbox value
string account_name = account_textbox.ForeColor == Color.Black ? account_textbox.Text : "";
int account_id = CURRENT_USER_ACCOUNTS[account_name];
// verify if account exists before trying to delete
var exists = DB_API.ExistsMoneyAccount(account_id);
if (!exists)
{
ErrorMessenger.Error("Account does not exist!");
return;
}
// delete money account
bool sure = ErrorMessenger.OKCancel("Deleting a Money Account will permanently" +
"delete all its transaction, categories," +
"loans, goals and stock. Are you sure you want to delete?");
if (sure)
{
DB_API.DeleteMoneyAccount(account_id);
CURRENT_USER_ACCOUNTS.Remove(account_name);
}
else
{
return;
}
// repopulate accounts_listbox
Populate_moneyAccounts_listBox(CURRENT_USER);
}
private void PopulateCategoriesListBox()
{
// get categories from DB
var rdr = DB_API.SelectAccountCategories(account_id);
// verify if account has no categories
if (!rdr.HasRows)
{
ErrorMessenger.Warning("Account has no categories!");
Categories_listbox.DataSource = new List <String>();
return;
}
// extract category names for listBox
this.categories = new Dictionary <string, int>();
var res = new Dictionary <string, int>();
while (rdr.Read())
{
string cat_name = rdr[DB_API.CategoryEnt.name.ToString()].ToString();
int cat_id = (int)rdr[DB_API.CategoryEnt.category_id.ToString()];
categories[cat_name] = cat_id;
if (cat_id % 100 > 0)
{
cat_name = " -> " + cat_name;
}
res[cat_name] = cat_id;
}
Categories_listbox.DataSource = new List <string>(res.Keys);
}
// -------------------------------------------------------------------
// BUTTONS -----------------------------------------------------------
// -------------------------------------------------------------------
private void Findme_btn_Click(object sender, EventArgs e)
{
// read value from textbox
string email = userfindme_textbox.ForeColor == Color.Black ? userfindme_textbox.Text : "";
CURRENT_USER = email;
// verify an email was given
if (email.Equals(""))
{
ErrorMessenger.EmptyField("Email");
return;
}
// verify if user exists
var exists = DB_API.ExistsUser(email);
if (!exists)
{
ErrorMessenger.Error("User does not exist!");
return;
}
// populate account_listBox
Populate_moneyAccounts_listBox(email);
}
private void Newaccount_btn_Click(object sender, EventArgs e)
{
// get textbox value
string account_name = account_textbox.ForeColor == Color.Black ? account_textbox.Text : "";
// verify that a name is given
if ("".Equals(account_name))
{
ErrorMessenger.EmptyField("Account name");
return;
}
// verify that a user is selected
if (CURRENT_USER.Equals(""))
{
ErrorMessenger.Error("User must be selected to attribute account to!");
return;
}
// verify if user already has same name account
if (new List <String>(CURRENT_USER_ACCOUNTS.Keys).Contains(account_name))
{
ErrorMessenger.Error("User already has an account with the same name!");
return;
}
// insert new account into 'money_accounts' and 'users_money_accounts' tables
DB_API.InsertMoneyAccount(CURRENT_USER, account_name);
Populate_moneyAccounts_listBox(CURRENT_USER);
}
// -------------------------------------------------------------------
// BUTTONS -----------------------------------------------------------
// -------------------------------------------------------------------
private void Save_btn_Click(object sender, EventArgs e)
{
// get categories fields info
int category_id = categoriesStrInt[category_comboBox.SelectedItem.ToString()];
int subcategory_id = subcategory_comboBox.SelectedItem.ToString().Equals(this.none) ||
subcategory_comboBox.SelectedItem.ToString().Equals("") ?
-1 : categoriesStrInt[subcategory_comboBox.SelectedItem.ToString()];
// get amount
string amt = amount_textBox.ForeColor == Color.Black ? amount_textBox.Text.Substring(1) : "";
if (amt.Equals(""))
{
ErrorMessenger.EmptyField("Amount");
return;
}
double amount = DB_API.UnMoneyfy(amount_textBox.Text);
// get date
DateTime date = dateTimePicker.Value;
// get notes and location
string notes = notes_textBox.ForeColor == Color.Black ? notes_textBox.Text : "";
string location = location_textBox.ForeColor == Color.Black ? location_textBox.Text : "";
// get transaction type
int transaction_type_id = -1;
transaction_type_id = DB_API.SelectTransactionTypeIdByName(type_comboBox.SelectedItem.ToString());
// get wallet
string from_wallet_name = wallet_comboBox.SelectedItem.ToString();
string to_wallet_name = wallet2_comboBox.SelectedItem.ToString();
int from_wallet_id = -1;
int to_wallet_id = -1;
var rdr = DB_API.SelectWalletByName(account_id, from_wallet_name);
while (rdr.Read())
{
from_wallet_id = (int)rdr[DB_API.WalletEnt.wallet_id.ToString()];
break;
}
rdr = DB_API.SelectWalletByName(account_id, to_wallet_name);
while (rdr.Read())
{
to_wallet_id = (int)rdr[DB_API.WalletEnt.wallet_id.ToString()];
break;
}
// before inserting change amount sign if it is an expense
// insert new transaction
int cat_id = subcategory_id != -1 ? subcategory_id : category_id;
DB_API.InsertTransaction(account_id, cat_id, from_wallet_id, to_wallet_id, transaction_type_id,
amount, date, location, notes);
// update transactions listBox
PopulateTransactionsListView();
}
private void Adduser_btn_Click(object sender, EventArgs e)
{
// get textbox value
string new_user = user_textbox.ForeColor == Color.Black ? user_textbox.Text : "";
string account_name = account_textbox.ForeColor == Color.Black ? account_textbox.Text : "";
int account_id = CURRENT_USER_ACCOUNTS[account_name];
// verify if account field is filled
if ("".Equals(account_name))
{
ErrorMessenger.EmptyField("Acount name");
return;
}
// verify if account exists
var exists = DB_API.ExistsMoneyAccount(account_id);
if (!exists)
{
ErrorMessenger.Error("Account does not exist!");
return;
}
// verify that a user is selected
if (new_user.Equals(""))
{
ErrorMessenger.Error("User must be selected to attribute account to!");
return;
}
// verify if user exists
exists = DB_API.ExistsUser(new_user);
if (!exists)
{
ErrorMessenger.Error("User does not exist!");
return;
}
// verify if user already is associated with account
var rdr = DB_API.SelectMoneyAccountUsers(account_id);
while (rdr.Read())
{
if (rdr[DB_API.MoneyAccountEnt.user_email.ToString()].Equals(new_user))
{
ErrorMessenger.Error("User already participates in account");
return;
}
}
// insert new account into 'users_money_accounts table
DB_API.MoneyAccountAddUser(account_id, new_user);
Populate_associatedUsers_listBox(account_id);
}
private void Save_btn_Click(object sender, EventArgs e)
{ //apaga este comentario
string loan_name = Name_textBox.ForeColor == Color.Black ? Name_textBox.Text : "";
string initial_amount = InitialAmount_textBox.ForeColor == Color.Black ? InitialAmount_textBox.Text : "";
string current_debt = CurrentDebt_textBox.ForeColor == Color.Black ? CurrentDebt_textBox.Text : "";
string interest_str = Interest_textBox.ForeColor == Color.Black ? Interest_textBox.Text : "";
DateTime term = DateTime.Parse(Enddate_dateTimePicker.Value.ToString());
// verify if mandatory fields are filled
if (loan_name.Equals("") || initial_amount.Equals("") || current_debt.Equals(""))
{
ErrorMessenger.EmptyField("Name, Initial Amount and Current Debt");
return;
}
// process inserted values
double init_amt = DB_API.UnMoneyfy(initial_amount);
double cur_debt = DB_API.UnMoneyfy(current_debt);
double interest = 0.0;
try
{
if (interest_str.Equals(""))
{
interest = 0.0;
}
else
{
interest = Double.Parse(interest_str);
}
}
catch
{
ErrorMessenger.WrongFormat("A numeric textBox");
return;
}
// add loan
try
{
DB_API.InsertLoan(account_id, loan_name, init_amt, cur_debt, term, interest);
}
catch (SqlException ex)
{
ErrorMessenger.Exception(ex);
return;
}
// upadte listBox with new user
PopulateLoansListView();
}
private void Update_btn_Click(object sender, EventArgs e)
{
// get values from text boxes if they attrValue inserted by user
string username = username_textbox.ForeColor == Color.Black ? username_textbox.Text : "";
string email = email_textbox.ForeColor == Color.Black ? email_textbox.Text : "";
string fname = firstname_textbox.ForeColor == Color.Black ? firstname_textbox.Text : "";
string mname = middlename_textbox.ForeColor == Color.Black ? middlename_textbox.Text : "";
string lname = lastname_textbox.ForeColor == Color.Black ? lastname_textbox.Text : "";
string cardNo = cardnumber_textbox.ForeColor == Color.Black ? cardnumber_textbox.Text : "";
int periodicity = DB_API.SelectRecurenceIdbyDesignation((string)Periodicity_comboBox.SelectedItem);
DateTime term = DateTime.Parse(term_dateTimePicker.Value.ToString());
// verify if email field is filled
if (email.Equals(""))
{
ErrorMessenger.EmptyField("Email");
return;
}
// verify that all mandatory fields are filled (if subscripition is checked)
if (active_checkBox.Checked)
{
if ("".Equals(fname) || "".Equals(lname) || "".Equals(cardNo) || "".Equals(periodicity))
{
ErrorMessenger.EmptyField("Every field marked (*)");
return;
}
}
// verify if user exists, so it can be updated
var exists = DB_API.ExistsUser(email);
if (!exists)
{
ErrorMessenger.Error("User does not exist. Unable to update!");
return;
}
// update user
DB_API.UpdateUser(username, email, fname, mname, lname, cardNo, periodicity, term, active_checkBox.Checked);
// update listBox
PopulateUsersListView();
}
private void Delete_btn_Click(object sender, EventArgs e)
{
string email = email_textbox.ForeColor == Color.Black ? email_textbox.Text : "";
// verify if email field is filled
if (email.Equals(""))
{
ErrorMessenger.EmptyField("Email");
return;
}
// delete user form 'users' table
DB_API.DeleteUser(email);
// update listBox
PopulateUsersListView();
DefaultTextboxes();
}
private void Delete_btn_Click(object sender, EventArgs e)
{
int transaction_id = 0;
try
{
transaction_id = (int)((Transaction)Transactions_listView.SelectedItems[0].Tag).TransactionID;
}
catch
{
ErrorMessenger.Warning("A transaction must be selected from the list");
return;
}
DB_API.DeleteTransaction(transaction_id);
// populate transactions listBox
PopulateTransactionsListView();
}
// ----------------------------------------------------------------------------------------------
// QUERY EXECUTION METHODS ----------------------------------------------------------------------
// ----------------------------------------------------------------------------------------------
private static int ExecuteNonQuery(SqlConnection cnx, SqlCommand cmd)
{
int rows = 0;
try
{
rows = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorMessenger.Exception(ex);
}
finally
{
DBdisconnect(cnx);
}
return(rows);
}
private static object ExecuteScalar(SqlConnection cnx, SqlCommand cmd)
{
object res = null;
try
{
res = cmd.ExecuteScalar();
}
catch (SqlException ex)
{
ErrorMessenger.Exception(ex);
}
finally
{
DBdisconnect(cnx);
}
return(res);
}
private void Deleteuser_btn_Click(object sender, EventArgs e)
{
// get textbox value
string user = user_textbox.ForeColor == Color.Black ? user_textbox.Text : "";
string account_name = account_textbox.ForeColor == Color.Black ? account_textbox.Text : "";
int account_id = CURRENT_USER_ACCOUNTS[account_name];
// verify if account field is filled
if ("".Equals(account_name))
{
ErrorMessenger.EmptyField("Account name");
return;
}
// verify if account exists
var exists = DB_API.ExistsMoneyAccount(account_id);
if (!exists)
{
ErrorMessenger.Error("Account does not exist!");
return;
}
// verify that a user is selected
if (user.Equals(""))
{
ErrorMessenger.Error("User must be selected to attribute account to!");
return;
}
// verify if user exists
exists = DB_API.ExistsUser(user);
if (!exists)
{
ErrorMessenger.Error("User does not exist!");
return;
}
// delete user access to account
DB_API.MoneyAccountRemoveUser(account_id, user);
// repopulate accounts_listbox
Populate_moneyAccounts_listBox(CURRENT_USER);
}
// ----------------------------------------------------------------------------------------------
// SQL SERVER AND DATABASE CONNECTION -----------------------------------------------------------
// ----------------------------------------------------------------------------------------------
private static SqlConnection DBconnect()
{
SqlConnection cnx = new SqlConnection("Data Source = " + dbServer + " ;" + "Initial Catalog = " + dbName +
"; uid = " + dbUserName + ";" + "password = "******"Could not connect to database!");
}
return(cnx);
}
private static DataTableReader ExecuteReader(SqlConnection cnx, SqlCommand cmd)
{
DataTable dt = new DataTable();
try
{
SqlDataReader rdr = cmd.ExecuteReader();
dt.Load(rdr);
rdr.Close();
}
catch (SqlException ex)
{
ErrorMessenger.Exception(ex);
}
finally
{
DBdisconnect(cnx);
}
return(dt.CreateDataReader());
}
private void Pay_btn_Click(object sender, EventArgs e)
{
// verify that
// get loan name and payment value
string name = Name_textBox.ForeColor == Color.Black ? Name_textBox.Text : "";
double payment = Pay_textBox.ForeColor == Color.Black ? DB_API.UnMoneyfy(Pay_textBox.Text) : 0;
Console.WriteLine(payment);
// verify that a name is given
if (name.Equals(""))
{
ErrorMessenger.EmptyField("Name");
return;
}
// make payment
DB_API.LoanPayment(this.account_id, name, payment);
// update Loans listView
PopulateLoansListView();
}
private void Save_btn_Click(object sender, EventArgs e)
{
// get values from textboxes
string goal_name = goalname_textBox.ForeColor == Color.Black ? goalname_textBox.Text : "";
string amount = goalamount_textBox.ForeColor == Color.Black ? goalamount_textBox.Text : "";
int cat_id = categories[Categories_comboBox.SelectedItem.ToString()];
DateTime term = term_dateTimePicker.Value;
// verify if field is filled
if (goal_name.Equals(""))
{
ErrorMessenger.EmptyField("Goal name");
return;
}
if (amount.Equals(""))
{
ErrorMessenger.EmptyField("Goal amount");
return;
}
double goal_amount = DB_API.UnMoneyfy(amount);
// add new goal
if (!Subcategories_comboBox.SelectedItem.ToString().Equals(this.none))
{
cat_id = categories[Subcategories_comboBox.SelectedItem.ToString()];
}
try
{
DB_API.InsertGoal(account_id, cat_id, goal_name, goal_amount, term);
}
catch (SqlException ex)
{
ErrorMessenger.Exception(ex);
}
PopulateGoalsListBox();
}
private void Login_btn_Click(object sender, EventArgs e)
{
string user = CURRENT_USER.Equals("") ? userfindme_textbox.Text : CURRENT_USER;
if (user.Equals(""))
{
ErrorMessenger.EmptyField("Email");
return;
}
string account_name = account_textbox.ForeColor == Color.Black ? account_textbox.Text : "";
if (account_name.Equals(""))
{
ErrorMessenger.Error("Account must be selected!");
return;
}
int account_id = CURRENT_USER_ACCOUNTS[account_name];
var exists = DB_API.ExistsMoneyAccount(account_id);
if (!exists)
{
ErrorMessenger.Error("Account does not exist!");
return;
}
var frm = new UserMenuForm(user, account_id)
{
Location = this.Location,
StartPosition = FormStartPosition.Manual
};
frm.FormClosing += delegate { this.Show(); };
frm.Show();
this.Hide();
}
private void Save_btn_Click(object sender, EventArgs e)
{
// get values from textboxes
string cat_name = Category_textBox.Text;
int cat_id;
string sub_cat_name = Subcategory_textBox.Text;
string type_name = Type_comboBox.SelectedItem.ToString();
int type_id = DB_API.SelectCategoryTypeIdByDesignation(type_name);
// save new category
if (!Category_textBox.ReadOnly)
{
// verify if field is filled
if (cat_name.Equals(""))
{
ErrorMessenger.EmptyField("Category");
return;
}
// verify if new name already exists
var rdr = DB_API.SelectAccountCategories(account_id);
while (rdr.Read())
{
if (cat_name.Equals(rdr[DB_API.CategoryEnt.name.ToString()].ToString()))
{
ErrorMessenger.Error("Category name already exists");
return;
}
}
// add new category
DB_API.AddCategoryToAccount(account_id, cat_name, type_id);
}
// save new sub category
if (!Subcategory_textBox.ReadOnly)
{
cat_id = this.categories[cat_name];
// verify if field is filled
if (sub_cat_name.Equals(""))
{
ErrorMessenger.EmptyField("Sub-category");
return;
}
// verify if new name already exists
var rdr = DB_API.SelectAccountCategories(account_id);
while (rdr.Read())
{
if (sub_cat_name.Equals(rdr[DB_API.CategoryEnt.name.ToString()].ToString()))
{
ErrorMessenger.Error("Category name already exists");
return;
}
}
// add new category
DB_API.AddSubCategoryToAccount(cat_id, account_id, sub_cat_name, type_id);
}
// save new budget
if (Category_textBox.ReadOnly && Subcategory_textBox.ReadOnly)
{
if (Budget_textBox.Text.Equals(""))
{
ErrorMessenger.EmptyField("Monthly Budget");
return;
}
if (StartMonth_comboBox.SelectedIndex == 0 || EndMonth_comboBox.SelectedIndex == 0)
{
ErrorMessenger.EmptyField("Start month and end month");
return;
}
cat_id = this.categories[cat_name];
if (!sub_cat_name.Equals(""))
{
cat_id = this.categories[sub_cat_name];
}
double amount = DB_API.UnMoneyfy(Budget_textBox.Text);
int startMonth = StartMonth_comboBox.SelectedIndex;
int startYear = (int)StartYear_numericBox.Value;
int endMonth = EndMonth_comboBox.SelectedIndex;
int endYear = (int)EndYear_numericBox.Value;
DateTime startDate = DateTime.Parse(startYear + "/" + startMonth + "/01");
DateTime endDate;
if (endMonth == 2)
{
endDate = DateTime.Parse(endYear + "/" + endMonth + "/28");
}
else
{
endDate = DateTime.Parse(endYear + "/" + endMonth + "/30");
}
// verify that endaDate is bigger than startDate
if (startDate.CompareTo(endDate) > 0)
{
ErrorMessenger.InvalidData("End date");
return;
}
// insert new budget
DB_API.InsertBudget(account_id, cat_id, amount, startDate, endDate);
}
PopulateCategoriesListBox();
}
private void Filter_btn_Click(object sender, EventArgs e)
{
// get info from textBoxes
// category and sub_category
int category_id = filtercategory_comboBox.SelectedItem.ToString().Equals(this.none) ?
-1 : categoriesStrInt[filtercategory_comboBox.SelectedItem.ToString()];
int subcategory_id = filtersubcategory_comboBox.SelectedItem.ToString().Equals(this.none) ?
-1 : categoriesStrInt[filtersubcategory_comboBox.SelectedItem.ToString()];
int?cat_id = subcategory_id == -1 ? category_id : subcategory_id;
cat_id = cat_id == -1 ? null : cat_id;
// start and end dates
DateTime start_date = filterstartdate_timePicker.Value;
start_date = new DateTime(start_date.Year, start_date.Month, start_date.Day, 0, 0, 0);
DateTime end_date = filterenddate_timePicker.Value;
end_date = new DateTime(end_date.Year, end_date.Month, end_date.Day, 0, 0, 0);
if (start_date.CompareTo(end_date) == 0)
{
end_date = new DateTime(end_date.Year, end_date.Month, end_date.Day + 1, 23, 59, 59, 999);
}
if (start_date.CompareTo(end_date) > 0)
{
ErrorMessenger.Error("End Date must be equal or greater than Start Date");
return;
}
// type and wallet
int?transaction_type_id = null;
if (!filtertype_comboBox.SelectedItem.ToString().Equals(this.none))
{
transaction_type_id = DB_API.SelectTransactionTypeIdByName(filtertype_comboBox.SelectedItem.ToString());
}
int?wallet_id = null;
if (!wallet_comboBox.SelectedItem.ToString().Equals(this.none))
{
var rdr = DB_API.SelectWalletByName(account_id, filterwallet_comboBox.SelectedItem.ToString());
while (rdr.Read())
{
wallet_id = (int)rdr[DB_API.WalletEnt.wallet_id.ToString()];
break;
}
}
// min and max amount
string minamt = filterminamount_textBox.ForeColor == Color.Black ? filterminamount_textBox.Text : "";
string maxamt = filtermaxamount_textBox.ForeColor == Color.Black ? filtermaxamount_textBox.Text : "";
double?minamount = null;
double?maxamount = null;
if (!minamt.Equals(""))
{
minamount = DB_API.UnMoneyfy(minamt);
}
if (!maxamt.Equals(""))
{
maxamount = DB_API.UnMoneyfy(maxamt);
}
if (minamount != null && maxamount != null && minamount > maxamount)
{
ErrorMessenger.Error("Min amount must be less or equal than Max amount");
return;
}
// apply filter
PopulateTransactionsListView(account_id, cat_id, wallet_id, transaction_type_id, minamount, maxamount,
start_date, end_date);
}
