// 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(); }