public void updateInvoiceItem()
{
//check whether data is valid, if it's not, will not go the following codes
if (isValidInvoiceItemData() == false)
{
return;
}
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = openSesame;
connection.Open();
if (isNewInvoiceItem == false)
{
currentInvoiceItem.itemName = txtItemName.Text;
currentInvoiceItem.itemDescription = txtItemDescreption.Text;
currentInvoiceItem.itemPrice = Convert.ToDecimal(txtItemPrice.Text);
currentInvoiceItem.itemQuatity = Convert.ToInt32(txtItemQuantity.Text);
string updateSesame = $"UPDATE InvoiceItem SET " +
$"InvoiceID = '{currentInvoice.invoiceId}'," +
$"ItemName = '{currentInvoiceItem.itemName}'," +
$"ItemDescription = '{currentInvoiceItem.itemDescription}'," +
$"ItemPrice = '{currentInvoiceItem.itemPrice}'" +
$"WHERE ItemID = {currentInvoiceItem.itemId};";
using (SqlCommand UpdateCommand = new SqlCommand(updateSesame, connection))
{
UpdateCommand.ExecuteNonQuery();
connection.Close();
}
loadInvoiceItem();
loadInvoice();
}
if (isNewInvoiceItem == true)
{
InvoiceItem newInvoiceItem = new InvoiceItem();
//calculate the default itemID for the new record
string findMax = $"SELECT MAX(ItemID) FROM InvoiceItem;";
using (SqlCommand SelectCommand = new SqlCommand(findMax, connection))
{
newInvoiceItem.itemId = Convert.ToInt32(SelectCommand.ExecuteScalar()) + 1;
}
newInvoiceItem.itemName = txtItemName.Text;
newInvoiceItem.itemDescription = txtItemDescreption.Text;
newInvoiceItem.itemPrice = Convert.ToDecimal(txtItemPrice.Text);
newInvoiceItem.itemQuatity = Convert.ToInt32(txtItemQuantity.Text);
newInvoiceItem.invoiceId = currentInvoice.invoiceId;
string insertSesame = $"INSERT INTO InvoiceItem " +
"(ItemID, InvoiceID, ItemName, ItemDescription, ItemPrice, ItemQuantity)" +
"VALUES " +
$"('{newInvoiceItem.itemId}', " +
$"'{newInvoiceItem.invoiceId}'," +
$"'{newInvoiceItem.itemName}'," +
$"'{newInvoiceItem.itemDescription}', " +
$"'{newInvoiceItem.itemPrice}', " +
$"'{newInvoiceItem.itemQuatity}');";
using (SqlCommand InsertCommand = new SqlCommand(insertSesame, connection))
{
InsertCommand.ExecuteNonQuery();
}
loadInvoiceItem();
loadInvoice();
isNewInvoiceItem = false;
}
}
}
public void loadInvoice()
{
invoiceList.Clear();
ListOfInvoice.Items.Clear();
isNewInvoice = false;
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = openSesame;
connection.Open();
//I use sql's syntax to create a new column which calculate the total price from item price, qantity, pst and gst.
string calcilateTotal = "Select i.*, (select sum(itemprice*itemquantity)+(sum(itemprice*itemquantity)*.06)+(sum(itemprice*itemquantity)*.05) from InvoiceItem where InvoiceID = i.InvoiceID) as total From Invoices i";
SqlCommand myCommand = new SqlCommand(calcilateTotal, connection);
using (SqlDataReader readInvoice = myCommand.ExecuteReader())
{
while (readInvoice.Read())
{
Invoice newInvoice = new Invoice();
newInvoice.invoiceId = (int)readInvoice["InvoiceID"];
newInvoice.customerName = (string)readInvoice["CustomerName"];
newInvoice.email = (string)readInvoice["CustomerEmail"];
newInvoice.date = (DateTime)readInvoice["InvoiceDate"];
newInvoice.adress = (string)readInvoice["CustomerAdress"];
if ((bool)readInvoice["Shipped"] == true)
{
shipped = "Yes";
}
else
{
shipped = "No";
}
newInvoice.shipped = shipped;
invoiceList.Add(newInvoice);
//when create a new record, in order to avoid the program from blowing up,
//the program will only read total column when it is not null.
if (readInvoice["total"] != DBNull.Value)
{
newInvoice.totalPrice = (decimal)readInvoice["total"];
}
}
}
string chooseInvoiceItem = "Select * From InvoiceItem";
SqlCommand myCommandItem = new SqlCommand(chooseInvoiceItem, connection);
using (SqlDataReader readInvoiceItem = myCommandItem.ExecuteReader())
{
while (readInvoiceItem.Read())
{
InvoiceItem newInvoiceItem = new InvoiceItem();
newInvoiceItem.itemId = (int)readInvoiceItem["ItemID"];
newInvoiceItem.invoiceId = (int)readInvoiceItem["InvoiceID"];
newInvoiceItem.itemName = (string)readInvoiceItem["ItemName"];
newInvoiceItem.itemDescription = (string)readInvoiceItem["ItemDescription"];
newInvoiceItem.itemPrice = (decimal)readInvoiceItem["ItemPrice"];
newInvoiceItem.itemQuatity = (int)readInvoiceItem["ItemQuantity"];
newInvoiceItem.itemTotalPrice = newInvoiceItem.itemPrice * newInvoiceItem.itemQuatity;
invoiceItemList.Add(newInvoiceItem);
}
}
for (int i = 0; i < invoiceList.Count; i++)
{
ListOfInvoice.Items.Add(invoiceList[i]);
}
}
}