//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); } }