public void ComboboxType()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <TypeOfProduct> type = new List <TypeOfProduct>();
type = entity.TypeOfProducts.ToList();
cmbType.DataSource = type;
cmbType.DisplayMember = "TypeName";
cmbType.ValueMember = "Id";
}
}
public void ComboboxProducer()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <Producer> producer = new List <Producer>();
producer = entity.Producers.ToList();
cmbProducer.DataSource = producer;
cmbProducer.DisplayMember = "ProducerName";
cmbProducer.ValueMember = "Id";
}
}
//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);
}
//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);
}
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 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 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 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 display information of product type from database to datagridview
public void DisplayType()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <TypeInfo> typeList = new List <TypeInfo>();
typeList = entity.TypeOfProducts.Select(x => new TypeInfo
{
Id = x.Id,
TypeName = x.TypeName
}).ToList();
dgvTypeList.DataSource = typeList;
}
}
//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);
}
//Function display Type on combobox
public void ComboboxProduct()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <Product> product = new List <Product>();
product = entity.Products.Where(x => x.TypeOfProduct.TypeName == cmbType.Text).ToList();
product = product.GroupBy(x => x.ProductName).Select(y => y.First()).OrderBy(z => z.ProductName).ToList();
cmbProduct.DataSource = product;
cmbProduct.DisplayMember = "ProductName";
cmbProduct.ValueMember = "Id";
}
}
//Check permission of person who are logged in
public bool CheckPermission(string accountName, string password)
{
bool result = false;
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
string check = entity.Accounts.Where(x => x.AccountName == accountName && x.Password == password).FirstOrDefault().Rank;
if (check == "Quản Lý")
{
result = true;
}
}
return(result);
}
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);
}
}
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);
}
}
//Function display information of producer onto datagridview
public void DisplayProducer()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <ProducerInfo> producerList = new List <ProducerInfo>();
producerList = entity.Producers.Select(x => new ProducerInfo
{
Id = x.Id,
ProducerName = x.ProducerName,
Address = x.Address,
Phone = x.Phone
}).ToList();
dgvProducerList.DataSource = producerList;
}
}
//Function display information of Account from database to datagridview
public void DisplayAccount()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <AccountInfo> accountList = new List <AccountInfo>();
accountList = entity.Accounts.Select(x => new AccountInfo
{
Id = x.Id,
AccountName = x.AccountName,
Password = x.Password,
Rank = x.Rank
}).ToList();
dgvAccountList.DataSource = accountList;
}
}
//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);
}
//Function display information of Staff from database to datagridview
public void DisplayStaff()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <StaffInfo> staffList = new List <StaffInfo>();
staffList = entity.Staffs.Select(x => new StaffInfo
{
Id = x.Id,
Name = x.Name,
DateOfBirth = x.DateOfBirth.Value.Day + "/" + x.DateOfBirth.Value.Month + "/" + x.DateOfBirth.Value.Year,
Gender = x.Gender,
Email = x.Email,
Phone = x.Phone,
Address = x.Address
}).ToList();
dgvStaffList.DataSource = staffList;
}
}
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);
}
}
//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 display product onto datagridview
public void DisplayProduct()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <ProductInfo> productList = new List <ProductInfo>();
productList = entity.Products.Select(x => new ProductInfo
{
Id = x.Id,
TypeName = x.TypeOfProduct.TypeName,
ProductName = x.ProductName,
Price = x.Price,
ProducerName = x.Producer.ProducerName,
Status = x.Status,
Amount = x.Amount,
Date = x.Date.Value.Day + "/" + x.Date.Value.Month + "/" + x.Date.Value.Year
}).ToList();
dgvProductList.DataSource = productList;
}
}
//Function check status of product
public bool CheckStatus(string product,int price)
{
bool result = false;
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <Product> tmp = new List <Product>();
tmp = entity.Products.Where(x => x.ProductName == cmbProduct.Text && x.Price == price).ToList();
int amountAll = 0;
foreach (Product ii in tmp)
{
amountAll += Convert.ToInt32(ii.Amount);
}
if (amountAll >= Convert.ToInt32(nmrAmount.Value))
{
result = true;
}
}
return(result);
}
//Function Display Bill onto database
public void DisplayBill()
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <BillInfo_Class> billList = new List <BillInfo_Class>();
billList = entity.BillInfoes.Select(x => new BillInfo_Class
{
Id = x.Id,
BillID = x.BillID,
SaleDate = x.Bill.DateOfSale.Value.Day + "/" + x.Bill.DateOfSale.Value.Month + "/" + x.Bill.DateOfSale.Value.Year,
Type = x.TypeOfProduct.TypeName,
Product = x.Product.ProductName,
Price = x.Product.Price,
Amount = x.Amount,
Discount = x.Discount,
TotalPrice = x.TotalPrice
}).ToList();
billList.Reverse();
dgvBillList.DataSource = billList;
}
}
public bool Login()
{
bool result = false;
try
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
var check = entity.Accounts.Where(x => x.AccountName == txtUser.Text && x.Password == txtPassword.Text).FirstOrDefault();
if (check != null)
{
result = true;
}
}
}
catch (Exception e2)
{
Console.WriteLine(e2.Message);
}
return(result);
}
//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);
}
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);
}
}
//Event add
private void btnAddProduct_Click(object sender,EventArgs e)
{
List <Product> tmp = new List <Product>();
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
int idx = Convert.ToInt32(cmbType.SelectedValue);
tmp = entity.Products.Where(x => x.ProductName == cmbProduct.Text && x.TypeID == idx).ToList();
}
int amountAll = 0;
foreach (Product ii in tmp)
{
amountAll += Convert.ToInt32(ii.Amount);
}
if (amountAll < Convert.ToInt32(nmrAmount.Value))
{
MessageBox.Show("Not Enough Amount! Available is " + amountAll,"Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
nmrAmount.Value = Convert.ToInt32(amountAll);
return;
}
if (cmbType.SelectedIndex == -1 || cmbProduct.SelectedIndex == -1 || nmrAmount.Value == 0)
{
return;
}
else
{
Product pro = new Product();
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
int idx = Convert.ToInt32(cmbType.SelectedValue);
pro = entity.Products.Where(x => x.ProductName == cmbProduct.Text && x.TypeID == idx).FirstOrDefault();
}
bool result = CheckStatus(cmbProduct.Text,Convert.ToInt32(pro.Price));
if (result)
{
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
var price = Convert.ToDouble(entity.Products.Where(x => x.ProductName == cmbProduct.Text).First().Price);
double totalPrice = (Convert.ToInt32(nmrAmount.Value) * price) - (Convert.ToInt32(nmrAmount.Value) * price * Convert.ToInt32(nmrDiscount.Value) / 100);
string[] order = new string[] { cmbType.Text,cmbProduct.Text,dtpSaleDate.Value.ToString("dd/MM/yyyy"),nmrAmount.Value.ToString(),price.ToString(),nmrDiscount.Value.ToString(),totalPrice.ToString() };
for (int i = 0; i <= dgvOrderList.Rows.Count - 1; i++)
{
DataGridViewRow row = dgvOrderList.Rows[i];
if (row.Cells[0].Value != null)
{
if (amountAll < Convert.ToInt32(nmrAmount.Value) + Convert.ToInt32(row.Cells[3].Value))
{
MessageBox.Show("Not Enough Amount! Available is " + (amountAll - Convert.ToInt32(row.Cells[3].Value)).ToString(),"Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
nmrAmount.Value = (amountAll - Convert.ToInt32(row.Cells[3].Value));
return;
}
if (row.Cells[1].Value.ToString() == pro.ProductName && row.Cells[0].Value.ToString() == cmbType.Text)
{
row.Cells[3].Value = Convert.ToInt32(row.Cells[3].Value) + Convert.ToInt32(nmrAmount.Value);
row.Cells[6].Value = Convert.ToInt32(row.Cells[3].Value) * Convert.ToInt32(row.Cells[4].Value);
row.Cells[6].Value = Convert.ToInt32(row.Cells[6].Value) - Convert.ToInt32(row.Cells[6].Value) * (Convert.ToDouble(row.Cells[5].Value) / 100.0);
FormSale_Load(sender,e);
return;
}
}
}
dgvOrderList.Rows.Add(order);
FormSale_Load(sender,e);
}
}
else
{
MessageBox.Show("Product is not enough!","Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
}
private void FormReport_Load(object sender, EventArgs e)
{
string query = "";
if (filter == "All")
{
query = "";
}
else if (filter == "This Year")
{
query = DateTime.Now.Year.ToString();
}
else if (filter == "This Month")
{
query = DateTime.Now.Month + "/" + DateTime.Now.Year;
}
else
{
query = DateTime.Now.Day + "/" + DateTime.Now.Month + "/" + DateTime.Now.Year;
}
List <Prod> lst = new List <Prod>();
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
List <Product> lstPro = new List <Product>();
lstPro = entity.Products.ToList();
foreach (Product p in lstPro)
{
List <BillInfo> bill = new List <BillInfo>();
bill = entity.BillInfoes.Where(x => x.ProductID == p.Id && x.TypeID == p.TypeID &&
(x.Bill.DateOfSale.Value.Day + "/" + x.Bill.DateOfSale.Value.Month + "/" + x.Bill.DateOfSale.Value.Year).Contains(query)).ToList();
int? totalAmount = 0;
double?totalPrice = 0;
foreach (BillInfo ii in bill)
{
totalAmount += ii.Amount;
totalPrice += ii.TotalPrice;
}
Prod tmp = new Prod();
tmp.id = p.Id;
tmp.typeID = p.TypeID;
tmp.amount = totalAmount;
tmp.price = totalPrice;
lst.Add(tmp);
}
}
using (ConvenienceShopEntities entity = new ConvenienceShopEntities())
{
lst.OrderBy(x => x.amount);
Product p = new Product();
p = entity.Products.SqlQuery("Select * from Product where Id = " + lst[0].id + " and TypeID=" + lst[0].typeID).FirstOrDefault();
lblLeastPro.Text = p.ProductName.ToString();
lblLeastProAmount.Text = Convert.ToDouble(lst[0].amount).ToString("N0", CultureInfo.CreateSpecificCulture("sv-SE"));
lblMostPro.Text = entity.Products.SqlQuery("Select * from Product where Id = " + lst[lst.Count() - 1].id + " and TypeID=" + lst[lst.Count() - 1].typeID).FirstOrDefault().ProductName;
lblMostProAmount.Text = Convert.ToDouble(lst[lst.Count() - 1].amount).ToString("N0", CultureInfo.CreateSpecificCulture("sv-SE"));
int? totalAmount = 0;
double?totalPrice = 0;
foreach (Prod i in lst)
{
totalAmount += i.amount;
totalPrice += i.price;
}
lblAmount.Text = Convert.ToDouble(totalAmount).ToString("N0", CultureInfo.CreateSpecificCulture("sv-SE"));
lblTotalPrice.Text = Convert.ToDouble(totalPrice).ToString("N", CultureInfo.CreateSpecificCulture("sv-SE"));
}
}
