// Show what the charity balance is.
private void displayCharityBalanceMessage()
{
try
{
// Show the charity budget.
double charityBalance = new MySQLConnection().GetCharityBalance();
charityBalanceLabel.Text = "Current charity balance: $" + Math.Round(charityBalance, 2);
}
catch (Exception e2)
{
Console.WriteLine(e2);
}
}
private DataTable getTotalWages()
{
DataTable dt = new DataTable();
string connString = new MySQLConnection().connection;
using (MySqlConnection con = new MySqlConnection(connString))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT gw.wage_year AS 'Year', ROUND(SUM(gw.amount) + IFNULL(SUM(tr.amount), 0) + IFNULL(SUM(oe.amount), 0), 2) AS 'Grand Total Earnings' FROM gross_wages gw LEFT JOIN ( SELECT tax_year, SUM(amount) AS 'amount' FROM tax_return GROUP BY tax_year) tr ON tr.tax_year = gw.wage_year LEFT JOIN ( SELECT YEAR(earning_date) AS 'year', SUM(amount) AS 'amount' FROM other_earnings GROUP BY YEAR(earning_date)) oe ON oe.year = gw.wage_year GROUP BY gw.wage_year;", con))
{
con.Open();
MySqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
}
}
return(dt);
}
private DataTable getTaxAndWageData()
{
DataTable dt = new DataTable();
string connString = new MySQLConnection().connection;
using (MySqlConnection con = new MySqlConnection(connString))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT gw.wage_year AS 'Year', ROUND(SUM(gw.amount), 0) AS 'Total Taxable Income', ROUND(IFNULL(total_tax.Taxes_Paid, 0) + IFNULL(tl.amount, 0), 2) AS 'Total Taxes Paid', IFNULL(ss_total_tax.amount, 0) AS 'Social Security', IFNULL(federal.amount, 0) AS 'Federal', IFNULL(state.amount, 0) AS 'State', IFNULL(tax_local.amount, 0) AS 'Local', ROUND(((IFNULL(tl.amount, 0) + IFNULL(total_tax.Taxes_Paid, 0) - IFNULL(tr.amount, 0))/SUM(gw.amount))*100, 2) AS 'Tax Rate' FROM gross_wages gw LEFT JOIN ( SELECT transaction_year, ROUND(SUM(amount), 2) AS 'Taxes_Paid' FROM taxation GROUP BY transaction_year) total_tax ON gw.wage_year = total_tax.transaction_year LEFT JOIN ( SELECT transaction_year, ROUND(SUM(amount), 2) AS 'amount' FROM taxation WHERE tax_type_name = 'Social Security' GROUP BY transaction_year) ss_total_tax ON gw.wage_year = ss_total_tax.transaction_year LEFT JOIN ( SELECT transaction_year, ROUND(SUM(amount), 2) AS 'amount' FROM taxation WHERE tax_type_name = 'Federal' GROUP BY transaction_year) federal ON gw.wage_year = federal.transaction_year LEFT JOIN ( SELECT transaction_year, ROUND(SUM(amount), 2) AS 'amount' FROM taxation WHERE tax_type_name = 'State' GROUP BY transaction_year) state ON gw.wage_year = state.transaction_year LEFT JOIN ( SELECT transaction_year, ROUND(SUM(amount), 2) AS 'amount' FROM taxation WHERE tax_type_name = 'Local' GROUP BY transaction_year) tax_local ON gw.wage_year = tax_local.transaction_year LEFT JOIN ( SELECT tax_year, ROUND(SUM(amount), 2) AS 'amount' FROM tax_return GROUP BY tax_year) tr ON tr.tax_year = gw.wage_year LEFT JOIN ( SELECT tax_year, ROUND(SUM(amount), 2) AS 'amount' FROM tax_liability GROUP BY tax_year) tl ON tl.tax_year = gw.wage_year GROUP BY gw.wage_year;", con))
{
con.Open();
MySqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
}
}
return(dt);
}
private void DisplayMonthTransactions()
{
//Remove the previous showing of this month's transactions (necessary because this method could be called after submitting a new transaction, which would then need to be added to the transactions list)
dataGridView1.Rows.Clear();
LinkedList <String[]> monthsTransactions = new MySQLConnection().GetCurrentMonthsTransactions();
if (monthsTransactions.Count == 0) // No transactions for the current month.
{
dataGridView1.Visible = false;
label1.Visible = false;
}
else
{
dataGridView1.Visible = true;
label1.Visible = true;
}
dataGridView1.Height = 28;
dataGridView1.Height += monthsTransactions.Count * 21;
for (int i = 0; i < monthsTransactions.Count; i++) //Even though there should be five transactions in the linked list, there might not be if the database has been swiped of data.
{
dataGridView1.Rows.Add(monthsTransactions.ElementAt(i)[0], monthsTransactions.ElementAt(i)[1], monthsTransactions.ElementAt(i)[2], monthsTransactions.ElementAt(i)[3]);
}
// Dynamically resize the data grid view, based on how many rows are in it (we don't want there to be unnecessary extra whitespace)
int size = (21 * dataGridView1.RowCount) + 7;
dataGridView1.SetBounds(610, 75, dataGridView1.Width, size);
if (dataGridView1.Height > 500) // Prevent the height from being more than 217 (otherwise, the datagridview will overlap)
{
dataGridView1.Height = 500;
dataGridView1.ScrollBars = ScrollBars.Vertical; // create vertical scrollbars so user can see all transaction overviews
}
else // remove the vertical scrollbars (may have been created earlier when there was need for them)
{
dataGridView1.ScrollBars = ScrollBars.None;
}
//TODO: Sort the transactions according to date, desc
//this.dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Descending);
}
public LinkedList <String[]> GetCurrentMonthsTransactions()
{
LinkedList <String[]> transactions = new LinkedList <String[]>();
string connStr = new MySQLConnection().connection;
MySqlConnection connection = new MySqlConnection(connStr);
string sql = "SELECT trans_date, description, amount, trans_id FROM expenses WHERE YEAR(trans_date) = YEAR(NOW()) AND MONTH(trans_date) = MONTH(NOW()) ORDER BY trans_date DESC; ";
connection = new MySqlConnection(connStr); //create the new connection using the parameters of connStr
try
{
connection.Open(); //open the connection
var cmd = new MySqlCommand(sql, connection); //create an executable command
var reader = cmd.ExecuteReader(); //execute the command
while (reader.Read())
{
String[] currentTransaction = new String[4];
DateTime dt = reader.GetDateTime(0); //Get the date.
string dtString = dt.Month + "/" + dt.Day + "/" + dt.Year;
currentTransaction[0] = dtString;
currentTransaction[1] = reader.GetString(1); //Get the description.
currentTransaction[2] = reader.GetString(2); //Get the amount.
currentTransaction[3] = reader.GetString(3); //Get the trans_id.
transactions.AddLast(currentTransaction);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
connection.Close();
return(transactions);
}
public void ModifyCharityBalance(string transDate, string description, double amount)
{
//Place the transaction in the database.
string connStr = new MySQLConnection().connection;
MySqlConnection connection = new MySqlConnection(connStr); //create the new connection using the parameters of connStr
string sql = "";
//Build the INSERT string.
sql = "INSERT INTO charity (trans_date, description, amount) VALUES ('" + transDate + "', '" + description + "', " + amount + ");";
try
{
connection.Open(); //open the connection
var cmd = new MySqlCommand(sql, connection); //create an executable command
var reader = cmd.ExecuteNonQuery(); //execute the command
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
connection.Close();
}
private void deleteTransactionButton1_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedCells.Count > 0)
{
int selectedrowindex = dataGridView1.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dataGridView1.Rows[selectedrowindex];
int trans_id = Int16.Parse(Convert.ToString(selectedRow.Cells["id"].Value));
string description = Convert.ToString(selectedRow.Cells["description"].Value);
bool successful = new MySQLConnection().DeleteTransaction(trans_id);
DisplayMonthTransactions();
unselectItems();
if (successful)
{
MessageBox.Show("The transaction \"" + description + "\" was successfully deleted.");
}
else
{
MessageBox.Show("There was an error deleting the transaction \"" + description + "\"");
}
}
}
private void updateDbButton_Click(object sender, EventArgs e)
{
bool success = false;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
try
{
// check for invalid data
if (dataGridView1.Rows[row.Index].Cells[0].Value?.ToString().Length == 0 ||
dataGridView1.Rows[row.Index].Cells[1].Value?.ToString().Length == 0 ||
dataGridView1.Rows[row.Index].Cells[2].Value?.ToString().Length == 0 ||
dataGridView1.Rows[row.Index].Cells[3].Value?.ToString().Length == 0)
{
continue;
}
int trans_id = Int16.Parse(dataGridView1.Rows[row.Index].Cells[3].Value?.ToString());
String trans_date = dataGridView1.Rows[row.Index].Cells[0].Value?.ToString();
double amount = Double.Parse(dataGridView1.Rows[row.Index].Cells[2].Value?.ToString());
String description = dataGridView1.Rows[row.Index].Cells[1].Value?.ToString();
// TODO: Need more input validation
success = new MySQLConnection().UpdateEntry(trans_date, description, amount, trans_id);
} catch (ArgumentNullException ane)
{
Console.WriteLine("ArgumentNullException : " + ane.Message);
}
}
if (success)
{
MessageBox.Show("Transaction(s) were successfully updated.");
}
}
private void DisplayTransactionsSummary()
{
categories.Clear();
categories = new MySQLConnection().GetBudgetReportCategories();
totals.Clear();
dataGridView1.Rows.Clear();
//Grab totals from the database.
//Get the selected dates and place them in proper MySQL format.
DateTime dt1 = Convert.ToDateTime(monthCalendar1.SelectionRange.Start.ToString());
DateTime dt2 = Convert.ToDateTime(monthCalendar2.SelectionRange.Start.ToString());
string date1 = dt1.Year + "-" + dt1.Month + "-" + dt1.Day;
string date2 = dt2.Year + "-" + dt2.Month + "-" + dt2.Day;
// Display the date as text.
date1Label.Text = dt1.Month + "/" + dt1.Day + "/" + dt1.Year;
date2Label.Text = dt2.Month + "/" + dt2.Day + "/" + dt2.Year;
if (!(dt1 > dt2))
{
warningLabel.Visible = false;
string connStr = new MySQLConnection().connection;
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
for (int i = 0; i < categories.Count; i++) //Loop through all categories, getting totals for each.
{
string sql = "SELECT SUM(amount) FROM expenses WHERE expense_type = '" + categories.ElementAt(i) + "' AND trans_date BETWEEN '" + date1 + "' AND '" + date2 + "'; ";
MySqlCommand cmd = new MySqlCommand(sql, conn);
var reader = cmd.ExecuteReader(); //execute the command
while (reader.Read())
{
if (reader.IsDBNull(0))
{
totals.AddLast(0);
}
else
{
totals.AddLast(Math.Abs(reader.GetDouble(0)));
}
}
reader.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
//Get the total amount spent.
double total = 0.0;
for (int i = 0; i < totals.Count; i++)
{
total += totals.ElementAt(i);
}
//Finally, display total amounts spent for each category on the gridview.
for (int i = 0; i < categories.Count; i++)
{
if (!(totals.ElementAt(i) == 0)) //Only show the category if it is nonzero.
{
//Calculate the percent weight of each transaction, put into format: XX.X%
string percent = ((totals.ElementAt(i) / total) * 100).ToString("#.#");
if (percent.Equals("") || Convert.ToDouble(percent) < 1) //Add a zero in front.
{
percent = "0" + percent;
}
else if (Convert.ToDouble(percent) == Math.Round(Convert.ToDouble(percent))) //Add a zero to the end to make it look professional.
{
percent += ".0";
}
dataGridView1.Rows.Add(categories.ElementAt(i), Math.Round(totals.ElementAt(i)), percent);
}
}
//Display expenses.
totalSpentLabel.Text = "$" + Math.Round(total);
}
else
{
warningLabel.Visible = true;
}
// Dynamically resize the data grid view, based on how many rows are in it (we don't want there to be unnecessary extra whitespace)
int size = (21 * dataGridView1.RowCount) + 7;
dataGridView1.SetBounds(260, 408, dataGridView1.Width, size);
if (dataGridView1.Height > 217) // Prevent the height from being more than 217 (otherwise, the datagridview will overlap)
{
dataGridView1.Height = 217;
dataGridView1.ScrollBars = ScrollBars.Vertical; // create vertical scrollbars so user can see all transaction overviews
}
else // remove the vertical scrollbars (may have been created earlier when there was need for them)
{
dataGridView1.ScrollBars = ScrollBars.None;
}
//Sort the transactions according to amount, in descending order.
this.dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Descending);
}
public bool UpdateEntry(String trans_date, String description, double amount, int trans_id)
{
string connStr = new MySQLConnection().connection;
MySqlConnection connection = new MySqlConnection(connStr);
String mySqlFixedDate = "";
try
{
if (trans_date[1] == '/') // Check m/dd/yyyy or m/d/yyyy
{
if (trans_date[4] == '/') // m/dd/yyyy
{
DateTime newDate = DateTime.ParseExact(trans_date, "M/dd/yyyy", null);
mySqlFixedDate = newDate.ToString("yyyy-MM-dd");
}
else if (trans_date[3] == '/') // m/d/yyyy
{
DateTime newDate = DateTime.ParseExact(trans_date, "M/d/yyyy", null);
mySqlFixedDate = newDate.ToString("yyyy-MM-dd");
}
}
else if (trans_date[2] == '/') // Check mm/dd/yyyy or mm/d/yyyy
{
if (trans_date[5] == '/') // mm/dd/yyyy
{
DateTime newDate = DateTime.ParseExact(trans_date, "MM/dd/yyyy", null);
mySqlFixedDate = newDate.ToString("yyyy-MM-dd");
}
else if (trans_date[4] == '/') // mm/d/yyyy
{
DateTime newDate = DateTime.ParseExact(trans_date, "MM/d/yyyy", null);
mySqlFixedDate = newDate.ToString("yyyy-MM-dd");
}
}
} catch (FormatException fe)
{
MessageBox.Show("The date " + trans_date + " could not be formatted correctly for the database: " + fe.Message);
}
if (mySqlFixedDate.Equals(""))
{
MessageBox.Show("The date " + trans_date + " could not be converted to a MySQL date.");
}
string sql = "UPDATE expenses SET trans_date = @trans_date, amount = @amount, description = @description WHERE trans_id = @trans_id;";
connection = new MySqlConnection(connStr); //create the new connection using the parameters of connStr
bool success = true;
try
{
connection.Open(); //open the connection
var cmd = new MySqlCommand(sql, connection); //create an executable command
cmd.Parameters.AddWithValue("@trans_date", mySqlFixedDate);
cmd.Parameters.AddWithValue("@amount", amount);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@trans_id", trans_id);
var reader = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
success = false;
MessageBox.Show("Error trying to update transaction(s): " + ex.Message);
}
finally
{
connection.Close();
}
return(success);
}
private void submitButton_Click(object sender, EventArgs e)
{
if (submitButton.Text.Equals("Submit"))
{
try
{
//Make sure the amount entered is valid.
double amount = getAmount();
confirmLabel.Visible = true;
submitButton.Text = "Confirm";
}
catch (FormatException fe)
{
Console.WriteLine("Error: " + fe);
MessageBox.Show("Invalid entry for amount.");
Clear();
}
catch (Exception e2)
{
// show what the unknown exception was...
amountCalculatedLabel.Visible = true;
amountCalculatedLabel.Text = "ERROR 182: " + e2.Message;
}
}
else
{
try
{
//Place the transaction in the database.
string connStr = new MySQLConnection().connection;
//Step 3: Create the SQL statement that deletes the notice.
string date = transactionDateTimePicker.Value.Year + "-" + transactionDateTimePicker.Value.Month + "-" + transactionDateTimePicker.Value.Day;
string description = new MySQLConnection().FixStringForMySQL(transactionDescriptionTextBox.Text);
// Calculate math expressions for the amount given. For example: users can enter 12.34+.87. The amount in this case would equal $13.21.
double amount = getAmount();
if (amount != 0.0)
{
//Figure out which category this transaction fits in.
bool[] vals = new bool[categories.Count];
amount = Math.Abs(amount); // make a positive number. The old version insisted that expenses be negative; however, under the new design, it is implied that anything in the expense table is negative
//Build the INSERT string. Place each possible category into it.
string sql = "";
double oldCharityBalance = 0.0;
bool showCharityBalanceChanges = false;
if (String.Equals(categoryComboBox.SelectedItem.ToString(), "Other Earnings")) // Type = Other Earnings
{
sql = "INSERT INTO other_earnings (earning_date, description, amount) VALUES ('" + date + "', '" + description + "', " + amount + ");";
// Check if user wants to apply 10% (rounded up) towards charity balance.
if (checkBox.Checked)
{
oldCharityBalance = new MySQLConnection().GetCharityBalance();
showCharityBalanceChanges = true;
amount = Math.Ceiling(amount * 0.1);
new MySQLConnection().ModifyCharityBalance(date, description + " (10+% applied to charity)", amount);
}
executeSql(sql);
}
else if (String.Equals(categoryComboBox.SelectedItem.ToString(), "Charity")) // Type = Charity
{
oldCharityBalance = new MySQLConnection().GetCharityBalance();
if (!checkBox.Checked) // This is a decrease to the charity balance.
{
amount = -amount; //make negative
}
// Increase to charity balance.
else
{
amount = Math.Ceiling(amount);
}
// Will need to decrease charity balance, since used some of the charity.
new MySQLConnection().ModifyCharityBalance(date, description, amount); // turn amount into a negative (decrease in charity balance)
showCharityBalanceChanges = true;
}
else // Some other expense type
{
sql = "INSERT INTO expenses (trans_date, description, amount, expense_type) VALUES ('" + date + "', '" + description + "', " + amount + ", '" + categoryComboBox.SelectedItem.ToString().ToLower() + "');";
executeSql(sql);
}
//Reset items.
Clear();
//Show previous charity balance and new charity balance.
if (showCharityBalanceChanges)
{
double charityBalance = new MySQLConnection().GetCharityBalance();
MessageBox.Show("Old charity balance: " + Math.Round(oldCharityBalance, 2) +
"\nNew charity balance: " + Math.Round(charityBalance, 2), "Charity Balance was Updated Successfully!");
}
//Set cursor to blinking in the description text box.
transactionDescriptionTextBox.Select();
//Add this item to the last five transactions.
DisplayMonthTransactions();
if (categoryComboBox.SelectedItem.Equals("Charity") || categoryComboBox.SelectedItem.Equals("Other Earnings"))
{
displayCharityBalanceMessage();
}
}
else
{
MessageBox.Show("Amount must be nonzero.");
}
}
catch (Exception e2)
{
MessageBox.Show("EXCEPTION CAUGHT (code 263): " + e2.Message);
}
}
unselectItems();
}
