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 SaveItems()
{
string sql;
//create a new object from form data
InvoiceItems newItems = new InvoiceItems();
//get changed data from form
newItems.ItemName = ItemNameTxtbox.Text;
newItems.ItemDescription = ItemDescTxtbox.Text;
newItems.ItemPrice = Convert.ToDecimal(ItemPriceTxtbox.Text);
newItems.ItemQuantity = Convert.ToInt32(ItemQuantTxtbox.Text);
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
//open connection
connection.Open();
//find the last primary key used
sql = $"Select MAX(ItemID) FROM InvoiceItems;";
int NewItemID;
using (SqlCommand Selection = new SqlCommand(sql, connection))
{
NewItemID = Convert.ToInt32(Selection.ExecuteScalar()) + 1;
newItems.ItemID = NewItemID;
}
sql = $"INSERT INTO InvoiceItems " +
"(ItemID,InvoiceID,ItemName,ItemDescription, ItemPrice, ItemQuantity) " +
"VALUES " +
$"('{NewItemID}'," +
$"'{CurrentSelectedInvoice.InvoiceID}'," +
$"'{newItems.ItemName}'," +
$"'{newItems.ItemDescription}'," +
$"'{newItems.ItemPrice.ToString()}'," +
$"'{newItems.ItemQuantity.ToString()}')";
using (SqlCommand InsertCommand = new SqlCommand(sql, connection))
{
InsertCommand.ExecuteNonQuery();
}
LoadItems();
int NewItemIndex = InvoiceItemListbox.Items.IndexOf(newItems);
InvoiceItemListbox.SelectedIndex = NewItemIndex;
InvoiceItemListbox.ScrollIntoView(newItems);
}
isNewRecord = false;
}
private void InvoiceItemListbox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (InvoiceItemListbox.SelectedIndex == 0)
{
InvoiceItemListbox.SelectedIndex = 1;
}
CurrentSeledtedItems = (InvoiceItems)InvoiceItemListbox.SelectedItem;
CurrentSelectedItemIndex = InvoiceItemListbox.SelectedIndex;
DisplayItems();
isNewRecord = false;
}
private void InvoiceItemListbox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
//To fix first item as heading column int the item listbox
if (InvoiceItemListbox.SelectedIndex == 0)
{
InvoiceItemListbox.SelectedIndex = 1;
}
CurrentSeledtedItems = (InvoiceItems)InvoiceItemListbox.SelectedItem;
CurrentSelectedItemIndex = InvoiceItemListbox.SelectedIndex;
DisplayItems();
isNewRecord = false;
}
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);
}
}
}
}
void SaveItems()
{
string sql;
//create a new object from form data
InvoiceItems newItems = new InvoiceItems();
//get changed data from form
newItems.ItemName = ItemNameTxtbox.Text;
newItems.ItemDescription = ItemDescTxtbox.Text;
newItems.ItemPrice = Convert.ToDecimal(ItemPriceTxtbox.Text);
newItems.ItemQuantity = Convert.ToInt32(ItemQuantTxtbox.Text);
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename =|DataDirectory|\Invoices.mdf; Integrated Security = True";
//open connection
connection.Open();
//find the last primary key used
sql = $"Select MAX(ItemID) FROM InvoiceItems;";
//assing new item ID
int NewItemID;
//to create new primary key(item ID)
using (SqlCommand Selection = new SqlCommand(sql, connection))
{
NewItemID = Convert.ToInt32(Selection.ExecuteScalar()) + 1;
newItems.ItemID = NewItemID;
}
//insert new data into database
sql = $"INSERT INTO InvoiceItems " +
"(ItemID,InvoiceID,ItemName,ItemDescription, ItemPrice, ItemQuantity) " +
"VALUES " +
$"('{NewItemID}'," +
$"'{CurrentSelectedInvoice.InvoiceID}'," +
$"'{newItems.ItemName}'," +
$"'{newItems.ItemDescription}'," +
$"'{newItems.ItemPrice.ToString()}'," +
$"'{newItems.ItemQuantity.ToString()}')";
using (SqlCommand InsertCommand = new SqlCommand(sql, connection))
{
InsertCommand.ExecuteNonQuery();
}
//Update List<InvoiceItemsList>
InvoiceItemsList.Add(newItems);
//clear all data displayed and update data
InvoiceItemListbox.Items.Clear();
LoadItems();
//finde index of new item
int NewItemIndex = InvoiceItemListbox.Items.IndexOf(newItems);
//selec the new item
InvoiceItemListbox.SelectedIndex = NewItemIndex;
InvoiceItemListbox.ScrollIntoView(newItems);
}
//close saving function until users click add button
isNewRecord = false;
}
