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