public IList <Product> GetProductsByCategory(int id)
{
using (var context = new CoronaPageContext())
{
return(context.Products.Where(p => p.CategoryID == id).ToList());
}
}
public IList <Sale> GetAllSales()
{
using (var ctx = new CoronaPageContext())
{
return(ctx.Sales.ToList());
}
}
public Product UpdateProduct(Product product)
{
using (var context = new CoronaPageContext())
{
var targetProduct = context.Products.Where(p => p.ProductID == product.ProductID).FirstOrDefault();
targetProduct.Name = product.Name;
targetProduct.Description = product.Description;
targetProduct.Price = product.Price;
targetProduct.CategoryID = product.CategoryID;
if (product.Image != null)
{
targetProduct.Image = product.Image;
}
try
{
context.Products.Update(targetProduct);
context.SaveChanges();
}
catch (Exception)
{
return(null);
}
return(targetProduct);
}
}
public IList <Category> GetAllAndNullCategories()
{
using (var context = new CoronaPageContext())
{
return(context.Categories.ToList());
}
}
public User GetUser(string userName)
{
using (var context = new CoronaPageContext())
{
return(context.Users.FirstOrDefault(u => u.Username == userName));
}
}
public LoginResult AttemptLogin(LoginDetails input)
{
if (input == null)
{
return(LoginResult.Failed);
}
using (var context = new CoronaPageContext())
{
var userDetails = context.Users.Where(p => p.Username == input.Username).FirstOrDefault();
if (userDetails == null || input.Password == null || input.Username == null)
{
return(LoginResult.Failed);
}
var encryptedPassword = convertToMd5(input.Password);
var token = convertToMd5(DateTime.Now.ToString());
if (!encryptedPassword.Equals(userDetails.Password))
{
return(LoginResult.Failed);
}
return(new LoginResult()
{
LoginSucceeded = true,
Token = token
});
}
}
public bool BuyProduct(int UserId, int id)
{
using (var context = new CoronaPageContext())
{
var targetInventory = context.Inventory.Where(p => p.ProductID == id).FirstOrDefault();
if (targetInventory is null)
{
return(false);
}
targetInventory.Quantity--;
Sale saleToAdd = new Sale()
{
ProductID = id,
UserID = UserId
};
try
{
context.Inventory.Update(targetInventory);
context.Sales.Add(saleToAdd);
context.SaveChanges();
}
catch (Exception e)
{
return(false);
}
}
return(true);
}
public IList <CategoryResult> GetAllCategories()
{
using (var context = new CoronaPageContext())
{
var categories = context.Categories.ToList();
var productsCount = context.Products.GroupBy(p => p.CategoryID)
.Select(group => new
{
categoryId = group.FirstOrDefault().CategoryID,
count = group.Count()
}).ToList();
return(categories.Join(productsCount,
c => c.CategoryID,
pc => pc.categoryId,
(c, pc) => new CategoryResult()
{
CategoryID = c.CategoryID,
Name = c.Name,
Count = pc.count
})
.ToList());
}
}
public Product AddProduct(Product pProduct)
{
using (var context = new CoronaPageContext())
{
Product newProd;
try
{
pProduct.SupplierID = 2;
context.Products.Add(pProduct);
context.SaveChanges();
newProd = context.Products.Where(p => p == pProduct).FirstOrDefault();
//Inventory newProdInventory = new Inventory()
//{
// ProductID = newProd.ProductID,
// Quantity = 100
//};
//context.Inventory.Add(newProdInventory);
//context.SaveChanges();
}
catch (Exception e)
{
return(null);
}
return(newProd);
}
}
public bool DoesUserExists(string username)
{
using (var context = new CoronaPageContext())
{
return(context.Users.Count(u => u.Username == username) > 0);
}
}
private string GetCategoryName(int key)
{
using (var context = new CoronaPageContext())
{
return(context.Categories.Where(c => c.CategoryID == key).FirstOrDefault().Name);
}
}
public IList <Product> GetAllProductsFromInventory()
{
using (var context = new CoronaPageContext())
{
return(context.Products.ToList());
}
}
private string GetUserName(int key)
{
using (var context = new CoronaPageContext())
{
var query = context.Users.Where(u => u.UserID == key).FirstOrDefault();
return(query.FirstName);
}
}
public dynamic GetAllSalesByUser()
{
using (var ctx = new CoronaPageContext())
{
return(ctx.Sales.GroupBy(s => s.UserID,
s => s.ProductID,
(key, g) => new { UserID = key, Products = g.ToList() }).ToList());
}
}
public ProductResult GetProduct(int id)
{
using (var context = new CoronaPageContext())
{
return(context.Products
.Join(context.Categories, p => p.CategoryID, c => c.CategoryID, (product, category) => new ProductResult(product, category))
.GroupJoin(context.Inventory, p => p.ProductID, i => i.ProductID, (product, inventory) => new ProductResult(product, inventory))
.FirstOrDefault(p => p.ProductID == id));
}
}
public IList <Location> SearchLocations(string name, int?population)
{
using (var context = new CoronaPageContext())
{
IQueryable <Location> locations = context.Locations.Where(p => p.City.ToLower().Contains(name.ToLower()));
if (population != null)
{
locations = locations.Where(p => p.Population <= population);
}
return(locations.ToList());
}
}
public IList <ProductResult> getUserProducts(int UserId)
{
IList <ProductResult> listToReturn = new List <ProductResult>();
using (var context = new CoronaPageContext())
{
var sales = context.Sales.Where(u => u.UserID == UserId);
foreach (var sale in sales)
{
listToReturn.Add(GetProduct(sale.ProductID));
}
}
return(listToReturn);
}
public bool UpdateInventory(Inventory inventory)
{
using (var context = new CoronaPageContext())
{
try
{
context.Inventory.Add(inventory);
context.SaveChanges();
}
catch (Exception)
{
return(false);
}
return(true);
}
}
public IList <Product> SearchProducts(string term, int?price)
{
using (var context = new CoronaPageContext())
{
if (price != null)
{
return(context.Products
.Where(p => (p.Name.Contains(term) || p.Description.Contains(term)) && p.Price <= price).ToList());
}
else
{
return(context.Products
.Where(p => p.Name.Contains(term) || p.Description.Contains(term)).ToList());
}
}
}
public ProductStockResult[] GetProductsStock()
{
using (var context = new CoronaPageContext())
{
return(context.Inventory.Join(context.Products, i => i.ProductID, p => p.ProductID, (inventory, product) => new
{
Product = product,
Inventory = inventory
})
.GroupBy(x => x.Product.Name)
.Select(x => new ProductStockResult()
{
ProductName = x.Key,
Count = x.Sum(y => y.Inventory.Quantity)
})
.ToArray());
}
}
public IList <SalesCategory> SalesPerCategory()
{
using (var context = new CoronaPageContext())
{
return(context.Sales.Join(context.Products, s => s.ProductID, p => p.ProductID, (sale, product) => new
{
Product = product,
Sale = sale
})
.GroupBy(x => x.Product.CategoryID)
.Select(x => new SalesCategory()
{
CategoryName = GetCategoryName(x.Key),
SalesSum = (int)x.Sum(y => y.Product.Price)
})
.ToList());
}
}
public object UpdateLocation(Location location)
{
using (var context = new CoronaPageContext())
{
var targetLocation = context.Locations.Where(c => c.LocationID == location.LocationID).FirstOrDefault();
try
{
targetLocation.Population = location.Population;
context.Locations.Update(targetLocation);
context.SaveChanges();
}
catch (Exception)
{
return(false);
}
}
return(true);
}
public bool DeleteProduct(int id)
{
using (var context = new CoronaPageContext())
{
var targetProduct = context.Products.Where(p => p.ProductID == id).FirstOrDefault();
context.Products.Remove(targetProduct);
try
{
context.SaveChanges();
}
catch (Exception)
{
return(false);
}
return(true);
}
}
public IList <Product> SearchProducts(string term, int?price, int categoryId)
{
using (var context = new CoronaPageContext())
{
var products = context.Products.Where(p => (p.Name.ToLower().Contains(term.ToLower()) || p.Description.ToLower().Contains(term.ToLower())));
if (price != null)
{
products = products.Where(p => p.Price <= price);
}
if (categoryId > 0)
{
products = products.Where(p => p.CategoryID == categoryId);
}
return(products.ToList());
}
}
public bool DeleteLocation(int id)
{
using (var context = new CoronaPageContext())
{
var targetLocation = context.Locations.Where(c => c.LocationID == id).FirstOrDefault();
try
{
context.Locations.Remove(targetLocation);
context.SaveChanges();
}
catch (Exception)
{
return(false);
}
return(true);
}
}
public IList <ProductsUser> ProductsByUser()
{
using (var context = new CoronaPageContext())
{
var prodByUser = context.Sales.Join(context.Products, s => s.ProductID, p => p.ProductID, (sale, product) => new
{
Product = product,
Sale = sale
})
.GroupBy(x => x.Sale.UserID)
.Select(x => new ProductsUser()
{
UserName = GetUserName(x.Key),
UserExpense = (int)x.Sum(y => y.Product.Price)
})
.OrderByDescending(p => p.UserExpense).ToList();
prodByUser.RemoveRange(6, prodByUser.Count - 6);
return(prodByUser);
}
}
public bool Register(RegisterDetails UserInput)
{
if (UserInput is null || CheckInputValid(UserInput))
{
return(false);
}
using (var Context = new CoronaPageContext())
{
User newUser = new User()
{
Address = UserInput.Address,
FirstName = UserInput.FirstName,
IsAdmin = 0,
LastName = UserInput.LastName,
Password = convertToMd5(UserInput.Password),
Phone = UserInput.Phone,
Username = UserInput.Username
};
Context.Users.Add(newUser);
Context.SaveChanges();
}
return(true);
}
