void LoadItems()
{
//empty all display data
InvoiceItemsList.Clear();
InvoiceItemListbox.Items.Clear();
SubTotTxtbox.Clear();
PstTxtbox.Clear();
GstTxtbox.Clear();
TotalTxtbox.Clear();
//to create first list as a heading column
InvoiceItemListbox.Items.Add(String.Format("{0,5}{1,30}{2,50}{3,60}{4,30}", "Item ID ", "Item Name", "Item Description", "Item Price", "Quantity"));
decimal SubTotal = 0;//assign subtotal
//Create and open a connection
using (SqlConnection connection = new SqlConnection())
{
if (CurrentSelectedInvoice != null)
{
// replace absolute file path with |DataDirectory|
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
connection.Open();
//Create SQL command object
string sqlCommand = $"Select * From InvoiceItems WHERE InvoiceID = {CurrentSelectedInvoice.InvoiceID}";
SqlCommand myCommand = new SqlCommand(sqlCommand, connection);
using (SqlDataReader Reader = myCommand.ExecuteReader())
{
while (Reader.Read())
{
//Create new InvoiceItems Object from the record
InvoiceItems newInvoiceItems = new InvoiceItems((int)Reader[0], (int)Reader[1], (String)Reader[2], (string)Reader[3], (decimal)Reader[4], (int)Reader[5]);
//Add to list
InvoiceItemsList.Add(newInvoiceItems);
//Add to listbox
InvoiceItemListbox.Items.Add(newInvoiceItems);
//when items are loaded(loop), multiply quantity and price and add up in subtoal
SubTotal += newInvoiceItems.ItemPrice * newInvoiceItems.ItemQuantity;
//calculate based on subtotal
decimal PST = SubTotal * 6 / 100;
decimal GST = SubTotal * 5 / 100;
decimal Total = SubTotal + PST + GST;
//display in textboxes
SubTotTxtbox.Text = SubTotal.ToString("C2");
PstTxtbox.Text = PST.ToString("C2");
GstTxtbox.Text = GST.ToString("C2");
TotalTxtbox.Text = Total.ToString("C2");
}
}
}
}
}
void LoadItems()
{
//emply all display data
InvoiceItemsList.Clear();
InvoiceItemListbox.Items.Clear();
SubTotTxtbox.Clear();
PstTxtbox.Clear();
GstTxtbox.Clear();
TotalTxtbox.Clear();
InvoiceItemListbox.Items.Add(String.Format("{0,5}{1,20}{2,40}{3,80}{4,40}", "Item ID ", "Item Name", "Item Description", "Item Price", "Price"));
decimal SubTotal = 0;//assign subtotal
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
connection.Open();
string sqlCommand = $"Select * From InvoiceItems WHERE InvoiceID = {CurrentSelectedInvoice.InvoiceID}";
SqlCommand myCommand = new SqlCommand(sqlCommand, connection);
using (SqlDataReader Reader = myCommand.ExecuteReader())
{
while (Reader.Read())
{
InvoiceItems newInvoiceItems = new InvoiceItems((int)Reader[0], (int)Reader[1], (String)Reader[2], (string)Reader[3], (decimal)Reader[4], (int)Reader[5]);
InvoiceItemsList.Add(newInvoiceItems);
InvoiceItemListbox.Items.Add(newInvoiceItems);
//when items are loaded, multiply quantity and price and add up in subtoal
SubTotal += newInvoiceItems.ItemPrice * newInvoiceItems.ItemQuantity;
//calculate based on subtotal
decimal PST = SubTotal * 6 / 100;
decimal GST = SubTotal * 5 / 100;
decimal Total = SubTotal + PST + GST;
//display in textboxes
SubTotTxtbox.Text = Convert.ToString(SubTotal);
PstTxtbox.Text = Convert.ToString(PST);
GstTxtbox.Text = Convert.ToString(GST);
TotalTxtbox.Text = Convert.ToString(Total);
}
}
}
}