//Function Delete Type From database
        public bool DeleteType()
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                TypeOfProduct  a          = entity.TypeOfProducts.SqlQuery("select * from TypeOfProduct where Id=" + dgvTypeList.SelectedRows[0].Cells[0].Value.ToString()).FirstOrDefault();
                List <Product> lstProduct = new List <Product>();
                lstProduct = entity.Products.SqlQuery("select * from Product where TypeID=" + dgvTypeList.SelectedRows[0].Cells[0].Value.ToString()).ToList();
                foreach (Product x in lstProduct)
                {
                    List <BillInfo> lstBillInfo = new List <BillInfo>();
                    lstBillInfo = entity.BillInfoes.SqlQuery("select * from BillInfo where ProductID=" + x.Id).ToList();
                    foreach (BillInfo bill in lstBillInfo)
                    {
                        entity.BillInfoes.Remove(bill);
                        entity.SaveChanges();
                    }
                    entity.Products.Remove(x);
                    entity.SaveChanges();
                }
                entity.TypeOfProducts.Remove(a);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
        //Event payment
        private void btnPayment_Click(object sender,EventArgs e)
        {
            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                Bill bill = new Bill();
                bill.DateOfSale = dtpSaleDate.Value;
                SaveBill(bill);
                Bill tmp = new Bill();
                tmp = entity.Bills.SqlQuery("Select * from Bill order by Id DESC").FirstOrDefault();

                int idx = tmp.Id;

                int result = 0;



                for (int i = 0; i < dgvOrderList.Rows.Count - 1; i++)
                {
                    BillInfo        billInfo = new BillInfo();
                    DataGridViewRow row      = dgvOrderList.Rows[i];
                    billInfo.BillID     = idx;
                    billInfo.TypeID     = entity.TypeOfProducts.SqlQuery("Select * from TypeOfProduct where TypeName = N'" + row.Cells[0].Value.ToString() + "'").FirstOrDefault().Id;
                    billInfo.ProductID  = entity.Products.SqlQuery("Select * from Product where ProductName = N'" + row.Cells[1].Value.ToString() + "'").FirstOrDefault().Id;
                    billInfo.Amount     = Convert.ToInt32(row.Cells[3].Value);
                    billInfo.Discount   = Convert.ToInt32(row.Cells[5].Value);
                    billInfo.TotalPrice = Convert.ToInt32(row.Cells[6].Value);
                    result = SaveOrder(billInfo);

                    List <Product> t = new List <Product>();
                    t = entity.Products.SqlQuery("select * from Product where ProductName= N'" + row.Cells[1].Value.ToString() + "' " +
                                                 " and TypeID=" + billInfo.TypeID + " " +
                                                 " and Amount > 0").ToList();
                    foreach (Product ii in t)
                    {
                        if (billInfo.Amount <= ii.Amount)
                        {
                            entity.Database.ExecuteSqlCommand("update Product set Amount=" + (ii.Amount - billInfo.Amount) + " where Id=" + ii.Id);
                            entity.SaveChanges();
                            break;
                        }
                        entity.Database.ExecuteSqlCommand("update Product set Amount= 0 where Id=" + ii.Id);
                        billInfo.Amount -= ii.Amount;
                    }
                }

                if (result == 1)
                {
                    dgvOrderList.Rows.Clear();

                    MessageBox.Show("Pay successfully!","Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("Can not be paid!","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
                }

                FormSale_Load(sender,e);
            }
        }
 //Function save Bill onto database
 public void SaveBill(Bill bill)
 {
     using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
     {
         entity.Bills.Add(bill);
         entity.SaveChanges();
     }
 }
        //Function add product onto database
        public bool AddProduct(Product product)
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                Product test = new Product();
                test = entity.Products.Where(x => x.ProductName == product.ProductName && x.TypeID == product.TypeID && x.ProducerID == product.ProducerID).FirstOrDefault();
                if (test != null)
                {
                    entity.Database.ExecuteSqlCommand("update Product set " +
                                                      " Amount = " + Convert.ToInt32(product.Amount + test.Amount) + ", " +
                                                      " Price = " + Convert.ToInt32(product.Price) + ", " +
                                                      " Date = '" + (dtpDate.Value.Year + "/" + dtpDate.Value.Month + "/" + dtpDate.Value.Day) + "' " +
                                                      " where ProductName= N'" + product.ProductName + "' and " +
                                                      " TypeID = " + product.TypeID + " and" +
                                                      " ProducerID =  " + product.ProducerID);

                    entity.Database.ExecuteSqlCommand("update Product set " +
                                                      " Price = " + (product.Price) + " " +
                                                      " where ProductName = N'" + product.ProductName + "' and " +
                                                      " TypeID =" + product.TypeID);

                    entity.SaveChanges();
                    return(true);
                }

                test = entity.Products.Where(x => x.ProductName == product.ProductName && x.TypeID == product.TypeID).FirstOrDefault();
                if (test != null)
                {
                    entity.Database.ExecuteSqlCommand("update Product set " +
                                                      " Price = " + (product.Price) + " " +
                                                      " where ProductName= N'" + product.ProductName + "' and " +
                                                      " TypeID = " + product.TypeID);
                    entity.SaveChanges();
                }

                entity.Products.Add(product);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
        //Function Add Type onto database
        public bool AddType(TypeOfProduct typeOfProduct)
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.TypeOfProducts.Add(typeOfProduct);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
 private void btnUpdateType_Click(object sender,EventArgs e)
 {
     using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
     {
         entity.Database.ExecuteSqlCommand("update TypeOfProduct set " +
                                           "TypeName = N'" + txtTypeName.Text + "' " +
                                           " where Id=" + dgvTypeList.SelectedRows[0].Cells[0].Value.ToString());
         entity.SaveChanges();
         MessageBox.Show("Update Successed!");
         FormTypeManagement_Load(sender,e);
     }
 }
        //Function add producer onto database
        public bool AddProducer(Producer producer)
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.Producers.Add(producer);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
        //Function Add Account onto database
        public bool AddAccount(Account account)
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.Accounts.Add(account);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
        //Function Add Staff onto database
        public bool AddStaff(Staff staff)
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.Staffs.Add(staff);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
        //Function save orders onto database
        public int SaveOrder(BillInfo billInfo)
        {
            int result = 0;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.BillInfoes.Add(billInfo);
                entity.SaveChanges();
                result = 1;
            }
            return(result);
        }
        //Function Delete Staff From database
        public bool DeleteStaff()
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                Staff a = entity.Staffs.SqlQuery("select * from Staff where Id=" + dgvStaffList.SelectedRows[0].Cells[0].Value.ToString()).FirstOrDefault();
                entity.Staffs.Remove(a);
                entity.SaveChanges();
                result = true;
            }
            return(result);
        }
 private void btnUpdateProducer_Click(object sender,EventArgs e)
 {
     using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
     {
         entity.Database.ExecuteSqlCommand("update Producer set " +
                                           "Phone = '" + txtProducerPhone.Text + "', " +
                                           "ProducerName = N'" + txtProducerName.Text + "', " +
                                           "Address = N'" + txtProducerAddress.Text + "'" +
                                           " where Id = " + dgvProducerList.SelectedRows[0].Cells[0].Value);
         entity.SaveChanges();
         MessageBox.Show("Update Successed!");
         FormProducerManagement_Load(sender,e);
     }
 }
 private void btnUpdateAccount_Click(object sender,EventArgs e)
 {
     using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
     {
         entity.Database.ExecuteSqlCommand("update Account set " +
                                           "AccountName = N'" + txtAccountName.Text + "', " +
                                           "Password = N'" + txtPassword.Text + "', " +
                                           "Rank = N'" + cmbRank.Text + "' " +
                                           " where Id = " + dgvAccountList.SelectedRows[0].Cells[0].Value.ToString());
         entity.SaveChanges();
         MessageBox.Show("Update Successed!");
         FormAccountManagement_Load(sender,e);
     }
 }
        //Function delete product from database
        public bool DeleteProduct()
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                if (dgvProductList.SelectedRows.Count > 0)
                {
                    Product         a           = entity.Products.SqlQuery("select * from Product where Id = " + dgvProductList.SelectedRows[0].Cells[0].Value.ToString()).FirstOrDefault();
                    List <BillInfo> lstBillInfo = new List <BillInfo>();
                    lstBillInfo = entity.BillInfoes.SqlQuery("select * from BillInfo where ProductID = " + a.Id).ToList();
                    foreach (BillInfo x in lstBillInfo)
                    {
                        entity.BillInfoes.Remove(x);
                        entity.SaveChanges();
                    }
                    entity.Products.Remove(a);
                    entity.SaveChanges();
                    result = true;
                }
            }
            return(result);
        }
        //Function Delete Account From database
        public bool DeleteAccount()
        {
            bool result = false;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                if (dgvAccountList.SelectedRows.Count > 0)
                {
                    Account a = entity.Accounts.SqlQuery("select * from Account where Id = " + dgvAccountList.SelectedRows[0].Cells[0].Value.ToString()).FirstOrDefault();
                    entity.Accounts.Remove(a);
                    entity.SaveChanges();
                    result = true;
                }
            }
            return(result);
        }
        private void btnUpdateStaff_Click(object sender,EventArgs e)
        {
            string DOB = dtpDateOfBirth.Value.Day + "/" + dtpDateOfBirth.Value.Month + "/" + dtpDateOfBirth.Value.Year;

            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.Database.ExecuteSqlCommand("update Staff set " +
                                                  "Name = N'" + txtStaffName.Text + "', " +
                                                  "Gender = N'" + cmbGender.GetItemText(cmbGender.SelectedItem) + "', " +
                                                  "Email = N'" + txtStaffEmail.Text + "', " +
                                                  "Phone = N'" + txtStaffPhone.Text + "', " +
                                                  "Address = N'" + txtStaffAddress.Text + "', " +
                                                  "DateOfBirth = N'" + Convert.ToDateTime(DOB) + "' " +
                                                  " where Id=" + dgvStaffList.SelectedRows[0].Cells[0].Value.ToString());
                entity.SaveChanges();
                MessageBox.Show("Update Successed!");
                FormStaffManagement_Load(sender,e);
            }
        }
        private void btnUpdateProduct_Click(object sender,EventArgs e)
        {
            using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
            {
                entity.Database.ExecuteSqlCommand("update Product set " +
                                                  "TypeID = " + cmbType.SelectedValue + "," +
                                                  "ProducerID = " + cmbProducer.SelectedValue + "," +
                                                  "Price = " + txtPrice.Text + ", " +
                                                  "ProductName = N'" + txtProductName.Text + "'," +
                                                  " Amount = " + nmrAmount.Value + ", " +
                                                  " Date = '" + (dtpDate.Value.Year + "/" + dtpDate.Value.Month + "/" + dtpDate.Value.Day) + "', " +
                                                  "Status = N'" + cmbStatus.GetItemText(cmbStatus.SelectedItem) + "'" +
                                                  " where Id = " + dgvProductList.SelectedRows[0].Cells[0].Value);

                entity.Database.ExecuteSqlCommand("update Product set " +
                                                  " Price=" + txtPrice.Text + " " +
                                                  " where ProductName= N'" + txtProductName.Text + "' and " +
                                                  " TypeID =" + cmbType.SelectedValue);

                entity.SaveChanges();
                MessageBox.Show("Update Successed!");
                FormProductManagement_Load(sender,e);
            }
        }