public void QueryProductWithName(string name)
{
using (_context = new DepartmentalStoreContext())
{
var product = _context.Product.Where(p => p.Name == name).FirstOrDefault();
}
}
public void QueryStaffWithPhoneNumber(string phone)
{
using (_context = new DepartmentalStoreContext())
{
var staff = _context.Staff.Where(s => s.FirstName == phone).FirstOrDefault();
}
}
public void QueryStaffWithRole(string role)
{
using (_context = new DepartmentalStoreContext())
{
var staff = _context.Staff.Include(s => s.Role).Where(p => p.Role.Name.ToLower() == role.ToLower()).ToList();
}
}
public void AddMultipleCategories()
{
var categories = new List <Category> {
new Category {
Name = "Dairy", ShortCode = "DRY"
},
new Category {
Name = "Cosmetics", ShortCode = "COMS"
},
new Category {
Name = "Electronics", ShortCode = "ECTR"
},
new Category {
Name = "Furniture", ShortCode = "FRNT"
},
new Category {
Name = "Stationary", ShortCode = "STNY"
}
};
using (_context = new DepartmentalStoreContext())
{
_context.Category.AddRange(categories);
_context.SaveChanges();
}
}
public void QueryStaffWithFirstName(string firstname)
{
using (_context = new DepartmentalStoreContext())
{
var staff = _context.Staff.Where(s => s.FirstName == firstname).FirstOrDefault();
}
}
public void AddAddress(Address address)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext()){
context.Address.Add(address);
context.SaveChanges();
}
}
public void AddSuppliersWithAddress()
{
var suppliers = new List <Supplier> {
new Supplier {
FirstName = "Celeste", LastName = "Gilbert", PhoneNumber = "7475913258", Email = "*****@*****.**", GenderId = 1,
Address = new Address {
AddressLine1 = "3492 Elmwood Avenue", City = "Scottsdale", State = "Arizona", Pincode = "85251"
}
},
new Supplier {
FirstName = "Ross", LastName = "Matthews", PhoneNumber = "9147539286", Email = "*****@*****.**", GenderId = 1,
Address = new Address {
AddressLine1 = "4835 Crim Lane", City = "Medway", State = "Ohio", Pincode = "45341"
}
},
new Supplier {
FirstName = "Nina", LastName = "Wells", PhoneNumber = "6789451752", Email = "*****@*****.**", GenderId = 2,
Address = new Address {
AddressLine1 = "3637 Clover Drive", City = "Colorado Springs", State = "Colorado", Pincode = "45341"
}
}
};
using (_context = new DepartmentalStoreContext())
{
_context.Supplier.AddRange(suppliers);
_context.SaveChanges();
}
}
public void QuerySupplierWithFirstName(string firstname)
{
using (_context = new DepartmentalStoreContext())
{
var result = _context.Supplier.Where(s => s.FirstName == firstname).FirstOrDefault();
}
}
public void QuerySupplierWithPhone(string phone)
{
using (_context = new DepartmentalStoreContext())
{
var result = _context.Supplier.Where(s => s.PhoneNumber == phone).FirstOrDefault();
}
}
public void AddRole(Role role)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.Role.Add(role);
context.SaveChanges();
}
}
public void AddInventory(Inventory inventory)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.Inventory.Add(inventory);
context.SaveChanges();
}
}
public void AddOrderDetail(OrderDetail order)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.OrderDetail.Add(order);
context.SaveChanges();
}
}
public void AddCustomer(Customer customer)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.Customer.Add(customer);
context.SaveChanges();
}
}
public void AddPrice(ProductPrice price)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.ProductPrice.Add(price);
context.SaveChanges();
}
}
public void AddStaff(Staff staff)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.Staff.Add(staff);
context.SaveChanges();
}
}
public void AddProductCategory(ProductCategory productcategory)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.ProductCategory.Add(productcategory);
context.SaveChanges();
}
}
public void AddProduct(Product product)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.Product.Add(product);
context.SaveChanges();
}
}
public void AddSupplier(Supplier supplier)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
context.Supplier.Add(supplier);
context.SaveChanges();
}
}
public void AddMultipleStaffWithRoleAndAddress()
{
var staffs = new List <Staff> {
new Staff {
FirstName = "Bryant", LastName = "Miller", PhoneNumber = "9456517812", Email = "*****@*****.**", GenderId = 1, Salary = 25000,
Address = new Address {
AddressLine1 = "140 Werninger Street", City = "Houston", State = "Texas", Pincode = "77032"
},
Role = new Role {
Name = "Cashier", Description = "Manages bills and cash"
}
},
new Staff {
FirstName = "Chris", LastName = "Badman", PhoneNumber = "7845912634", Email = "*****@*****.**", GenderId = 1, Salary = 50000,
Address = new Address {
AddressLine1 = "1440 Eagle Drive", City = "Detroit", State = "Michigan", Pincode = "48226"
},
Role = new Role {
Name = "Store Manager", Description = "Manager of the store"
}
},
new Staff {
FirstName = "Georgie", LastName = "Nguyen", PhoneNumber = "6578412937", Email = "*****@*****.**", GenderId = 2, Salary = 18000,
Address = new Address {
AddressLine1 = "706 Flint Street", AddressLine2 = "Near St. Cruiz Church", City = "Atlanta", State = "Georgia", Pincode = "30303"
},
Role = new Role {
Name = "Inventory Controller", Description = "Controls the inventory"
}
},
new Staff {
FirstName = "Stewart", LastName = "Dittman", PhoneNumber = "8789456123", Email = "*****@*****.**", GenderId = 1, Salary = 30000,
Address = new Address {
AddressLine1 = "3459 Zappia Drive", City = "Lakeside Park", State = "Kentucky", Pincode = "41017"
},
Role = new Role {
Name = "Sales Associate", Description = "Makes sure sales must go high"
}
},
new Staff {
FirstName = "Christine", LastName = "Malone", PhoneNumber = "9247561438", Email = "*****@*****.**", GenderId = 2, Salary = 12000,
Address = new Address {
AddressLine1 = "28 Ross Street", City = "Belleville", State = "Illinois", Pincode = "62220"
},
Role = new Role {
Name = "Gaurd", Description = "For security of the store"
}
},
};
using (_context = new DepartmentalStoreContext())
{
_context.Staff.AddRange(staffs);
_context.SaveChanges();
}
}
public void QuerySupplierWithAddress(string state, string city)
{
using (_context = new DepartmentalStoreContext())
{
var result = _context.Supplier
.Include(s => s.Address)
.Where(s => s.Address.State == state || s.Address.City == city)
.ToList();
}
}
public List <Product> GetProductByName(string name)
{
//Query Product based on - Name
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
var products = context.Product.Where(x => x.Product_Name == name).ToList();
return(products);
}
}
public List <Staff> GetStaffByRole(string value)
{
//Query Staff using Role_Name
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
List <Staff> staffs = context.Staff.Include(s => s.Role).Where(x => x.Role.Role_Name.Contains(value)).ToList <Staff>();
return(staffs);
}
}
public void QueryNumberOfProductsInCategory()
{
using (_context = new DepartmentalStoreContext())
{
var result = _context.Category
.Include(x => x.ProductCategories)
.Select(x => new { x, x.ProductCategories.Count })
.ToList();
}
}
public void QueryProductOrderWithSupplier()
{
using (_context = new DepartmentalStoreContext())
{
var result = _context.Order
.Include(o => o.OrderDetails)
.ThenInclude(x => x.Product)
.Include(o => o.Supplier)
.ToList();
}
}
//
public List <Staff> GetStaffByNameOrNumber(string value)
{
//Query Staff using name or phone number or both
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
List <Staff> staffs = context.Staff.Where <Staff>(x => x.First_Name.Contains(value) || x.Phone_Number.Contains(value)).ToList <Staff>();
return(staffs);
}
}
public void QueryProductOrderWithSupplierWithProductName(string name)
{
using (_context = new DepartmentalStoreContext())
{
var result = _context.Order
.Include(o => o.OrderDetails)
.ThenInclude(x => x.Product)
.Include(o => o.Supplier)
.Where(x => x.OrderDetails.Any(o => o.Product.Name == name)).ToList();
}
}
public List <OrderDetail> OrdersProductSuppliedBeforeDate(DateTime datebefore)
{
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
var query = context.OrderDetail.Where(x => x.Date_Of_Delivery < datebefore);
return(null);
}
}
public List <Product> GetProductInStock()
{
//Query Product based on - Instock
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
var products = context.Product.Include(x => x.Inventory).Where(x => x.Inventory.InStock == true).ToList();
return(products);
}
}
public List <Product> GetProductByCategory(string cname)
{
//Query Product based on - Category_Name
using (DepartmentalStoreContext context = new DepartmentalStoreContext())
{
var products = context.Product.Include(x => x.ProductCategory).Where(x => x.ProductCategory.Category_Name.Contains(cname)).ToList();
return(products);
}
}
public void QueryProductWithCategory(string category)
{
using (_context = new DepartmentalStoreContext())
{
var products = _context.Product
.Include(x => x.ProductCategories)
.ThenInclude(x => x.Category)
.Where(x => x.ProductCategories.Any(y => y.Category.Name.ToLower() == category.ToLower()))
.ToList();
}
}
