//This loads the transactionListbox from the database, based off of AccountNumber that is assigned to the line item that
//was selected in the AccountListBox
private void LoadTransactions(int AccountNumber)
{
CustomersTransactionList.Clear(); //Empties out the List<>
transactionListbox.Items.Clear(); // Empties out the listbox
decimal SelectedCustomerBalance = 0;
balanceTextbox.Text = "";
//Adds Row Headers to the transactionListbox
transactionListbox.Items.Add(" Transaction Number Transaction Date Withdrawals Deposit");
//declare a connecton object
using (SqlConnection connection = new SqlConnection())
{
//declare a connectino object
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//opens the connection
connection.Open();
//Create a SQL command object.
string sql = $"SELECT * FROM AccountTransactions where Accountnumber = {AccountNumber} Order by TransactionDate;";
SqlCommand myCommand = new SqlCommand(sql, connection);
//Run the command: into a data reader using the ExecuteReader() Method.
using (SqlDataReader myDataReader = myCommand.ExecuteReader())
{
//Loop over the results. MyDataReader.Read returns false at the end of the records returned
while (myDataReader.Read())
{
//inserts the record that is contained in the myDataReader.Read and puts each column of the datareader into
//one property of a AccountTransaction Object.
AccountTransaction NewTransaction = new AccountTransaction((Int32)myDataReader["TransactionNumber"],
(Int32)myDataReader["AccountNumber"],
(DateTime)myDataReader["TransactionDate"],
(decimal)myDataReader["TransactionAmount"]);
//Add the Trandsaction to the List<Contact>
CustomersTransactionList.Add(NewTransaction);
// Add the contact to the listbox
transactionListbox.Items.Add(NewTransaction);
//Keeps a running balance of the contacts
SelectedCustomerBalance = SelectedCustomerBalance + NewTransaction.TransactionAmount;
}
}
}
//Inserts the balance total into the text box
balanceTextbox.Text = string.Format("{0:0.00}", SelectedCustomerBalance);
}
//Takes the inforamtion from the object and displays them in text boxes.
private void DisplayTransaction(AccountTransaction TransactionItem)
{
// This checks to see if a record was selected in the Transaction List Box. If no item was selected then all transaciton
// text boxes have a value of ""
if (transactionListbox.SelectedItem != null)
{
transactionNumbertextBox.Text = TransactionItem.TransactionNumber.ToString();
transactionDatetextBox.Text = TransactionItem.TransactionDate.ToString();
transactionAmountTextbox.Text = string.Format("{0:0.00}", TransactionItem.TransactionAmount);
}
else
{
transactionNumbertextBox.Text = "";
transactionDatetextBox.Text = "";
transactionAmountTextbox.Text = "";
}
}
//When ever the focus is changed within the TransactinoListbox
private void transactionListbox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
//if the title bar is selected
if (transactionListbox.SelectedIndex == 0)
{
transactionListbox.SelectedIndex = -1;
return;
}
//loads the selected record into the object list
CurrentTransaction = (AccountTransaction)transactionListbox.SelectedItem;
//sets the index value
CurrentselectedindexTransaction = transactionListbox.SelectedIndex;
//loads the selected row into the transaction text boxes
DisplayTransaction(CurrentTransaction);
NewFlag = false;
}
//Method is performed when the save button is clicked. Saves the record to the database
private void transactionSavebutton_Click(object sender, RoutedEventArgs e)
{
//sets the flag to true, this is used in the IsDataValid method so the method knows what values to check
TransactionSaveClick = true;
//checks to see if the data is valid
if (!IsDataValid())
{
return;
}
//Checks to see if a customer record was selected
if (AccountListbox.SelectedIndex == -1)
{
transactionErrorlabel.Content = "No Customer record is selected";
return;
}
//if a new record, or no transactions exist or if no transactions are selected
if (NewFlag || CustomersTransactionList.Count == 0 || transactionListbox.SelectedIndex == -1)
{
int Number = 0;
int CurrentAccountNumber = 0;
DateTime CurrentDateTime = DateTime.Now;
CurrentAccountNumber = CurrentCustomer.AccountNumber;
using (SqlConnection connection = new SqlConnection())
{
//point connection to the database
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//open the connection
connection.Open();
//sql command that will get the max transaction number from accounttransactions table
string sql = "SELECT MAX(TransactionNumber) as TransactionNumber FROM AccountTransactions;";
SqlCommand myCommand = new SqlCommand(sql, connection);
//If no transactions exist set the transaction number to 1
if (Convert.ToString(myCommand.ExecuteScalar()) == "")
{
Number = 1;
}
//grab the max transaction number and add one
else
{
Number = Convert.ToInt32(myCommand.ExecuteScalar()) + 1;
}
}
//creates a new list Object and inputs the number, accountnumber and current date
AccountTransaction NewTransaction = new AccountTransaction(Number, CurrentAccountNumber, CurrentDateTime, 0);
//adds the amount to the list
NewTransaction.TransactionAmount = Convert.ToDecimal(transactionAmountTextbox.Text);
using (SqlConnection connection = new SqlConnection())
{
//point connection to the database
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//open the connectino
connection.Open();
//sql comment that will be executed: inserts the values into the table
string sql = $"INSERT INTO AccountTransactions Values(" +
$"{NewTransaction.TransactionNumber}, " +
$"{NewTransaction.AccountNumber}, " +
$"'{NewTransaction.TransactionDate}', " +
$"{NewTransaction.TransactionAmount});";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
}
//loads the transactionlistbox
LoadTransactions(NewTransaction.AccountNumber);
//clears out the textbox
transactionAmountTextbox.Text = "";
NewFlag = false;
}
}
else //performs when the record was modified
{
if (transactionListbox.SelectedItem == null)
{
MessageBox.Show("No Records loaded");
transactionAmountTextbox.Text = "";
return;
}
CurrentTransaction.TransactionAmount = Convert.ToDecimal(transactionAmountTextbox.Text);
using (SqlConnection connection = new SqlConnection())
{
int IndextoReselect;
//stores the index value
IndextoReselect = CurrentselectedindexTransaction;
//CurrentSelectedIndexAccount;
//points connection to the database
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//open the connection
connection.Open();
//sql string to be executed to update the record
string sql = $"Update AccountTransactions Set " +
$"TransactionAmount = {CurrentTransaction.TransactionAmount} " +
$"Where TransactionNumber = '{CurrentTransaction.TransactionNumber}';";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery(); // we expect no records back
}
//refreshes the listbox
LoadTransactions(CurrentTransaction.AccountNumber);
//selects the record that was updated
transactionListbox.SelectedIndex = IndextoReselect;
}
}
}
