Exemplo n.º 1
0
        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;
                }
            }
        }
Exemplo n.º 2
0
        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]);
                }
            }
        }