public static int GetMenuIdByCompanyId(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
return(entities.Menus.SingleOrDefault(menu => menu.CreatorCompanyId == id).Id);
}
}
public static LoadCompanyViewModel GetCompanyToLoadById(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
var cmp = entities.Companies.Include(company => company.CompanyType)
.Include(company => company.CompanyCostType).SingleOrDefault(company => company.Id == id);
LoadCompanyViewModel result = new LoadCompanyViewModel();
result.Name = cmp.Name;
result.Description = cmp.Description;
result.LogoUrl = cmp.LogoUrl;
result.CompanyType = cmp.CompanyType;
result.CompanyCostType = cmp.CompanyCostType;
result.RegistrationDate = cmp.CreateDate;
var rating = (from r in entities.Ratings
join re in entities.Reviews on
r.Id equals re.RatingId
join o in entities.Orders on
re.OrderId equals o.Id
where o.CompanyId == id
select r.Value);
result.AvgRating = rating.Average();
result.RatingCount = rating.Count();
return(result);
}
}
public static IEnumerable <Product> GetProducts()
{
using (var entities = new ITowerCafeDBEntities())
{
return(entities.Products.ToList());
}
}
public static IEnumerable <CommentType> GetCommentTypes()
{
using (var entities = new ITowerCafeDBEntities())
{
return(entities.CommentTypes.ToList());
}
}
public static void AddOrderDetail(OrderDetail orderDetail)
{
using (var entities = new ITowerCafeDBEntities())
{
entities.OrderDetails.Add(orderDetail);
entities.SaveChanges();
}
}
public static void AddMenuMapping(MenuProductsMap menuProductsMap)
{
using (var entities = new ITowerCafeDBEntities())
{
entities.MenuProductsMaps.Add(menuProductsMap);
entities.SaveChanges();
}
}
public static Company GetCompanyById(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
return(entities.Companies.Include(company => company.CompanyType)
.Include(company => company.CompanyCostType).SingleOrDefault(company => company.Id == id));
}
}
public static int AddReview(Review review)
{
using (var entities = new ITowerCafeDBEntities())
{
var orders = entities.Reviews.Add(review);
entities.SaveChanges();
return(review.Id);
}
}
public static int Add(Company company)
{
using (var entities = new ITowerCafeDBEntities())
{
entities.Companies.Add(company);
entities.SaveChanges();
return(company.Id);
}
}
public static int AddComment(Comment comment)
{
using (var entities = new ITowerCafeDBEntities())
{
var orders = entities.Comments.Add(comment);
entities.SaveChanges();
return(comment.Id);
}
}
public static int AddProduct(MenuProduct menuProduct)
{
using (var entities = new ITowerCafeDBEntities())
{
entities.MenuProducts.Add(menuProduct);
entities.SaveChanges();
return(menuProduct.Id);
}
}
public static int AddMenu(Menu menu)
{
using (var entities = new ITowerCafeDBEntities())
{
entities.Menus.Add(menu);
entities.SaveChanges();
return(menu.Id);
}
}
public static int AddRating(Rating rating)
{
using (var entities = new ITowerCafeDBEntities())
{
var orders = entities.Ratings.Add(rating);
entities.SaveChanges();
return(rating.Id);
}
}
public static int AddOrder(Order order)
{
using (var entities = new ITowerCafeDBEntities())
{
entities.Orders.Add(order);
entities.SaveChanges();
return(order.Id);
}
}
public static IEnumerable <Order> GetOrdersByUserId(int userId)
{
using (var entities = new ITowerCafeDBEntities())
{
var orders = entities.Orders
.Include(order => order.Company)
.Where(order => order.UserId == userId);
return(orders.ToList());
}
}
public static IEnumerable <Company> GetCompanies()
{
using (var entities = new ITowerCafeDBEntities())
{
var companies = entities.Companies
.Include(company => company.CompanyType)
.Include(company => company.CompanyCostType);
return(companies.ToList());
}
}
public static List <Comment> GetCommentsByCompanyId(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
var joined = (from r in entities.Reviews
join c in entities.Comments on
r.CommentId equals c.Id
join o in entities.Orders on
r.OrderId equals o.Id
where o.CompanyId == id
select c);
return(joined.ToList());
}
}
public static IEnumerable <MenuProduct> GetOrderDetailsByOrderId(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
var joined = (from od in entities.OrderDetails
join mp in entities.MenuProducts on
od.ProductId equals mp.Id
where od.OrderId == id
select mp);
return(joined
.Include(product => product.ProductCategory)
.Include(product => product.Product)
.ToList());
}
}
public static DisplayMenuItemViewModel GetMenuItemByMenuProductId(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
var mp = entities.MenuProducts.SingleOrDefault(menuProduct => menuProduct.Id == id);
return(new DisplayMenuItemViewModel
{
Id = mp.Id,
Name = mp.Name,
PreparationTime = mp.PreparationTime,
Price = mp.Price,
Category = mp.ProductCategory.Name,
ProductCode = mp.Product.Name
});
}
}
public static double GetAverageRatingByCompanyId(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
var rating = (from r in entities.Ratings
join re in entities.Reviews on
r.Id equals re.RatingId
join o in entities.Orders on
re.OrderId equals o.Id
where o.CompanyId == id
select r.Value);
if (rating.Count() == 0)
{
return(0);
}
return(rating.Average());
}
}
public static void Add(SingleFormViewModel singleFormViewModel, int formId)
{
using (var entities = new ITowerCafeDBEntities())
{
switch (formId)
{
case 1:
CompanyType ct = new CompanyType
{
Name = singleFormViewModel.Name
};
entities.CompanyTypes.Add(ct);
entities.SaveChanges();
break;
case 2:
CompanyCostType cct = new CompanyCostType
{
Name = singleFormViewModel.Name
};
entities.CompanyCostTypes.Add(cct);
entities.SaveChanges();
break;
case 3:
CommentType coT = new CommentType
{
Name = singleFormViewModel.Name
};
entities.CommentTypes.Add(coT);
entities.SaveChanges();
break;
case 4:
ProductCategory pc = new ProductCategory
{
Name = singleFormViewModel.Name
};
entities.ProductCategories.Add(pc);
entities.SaveChanges();
break;
}
}
}
public static void Remove(int itemId, int formId)
{
using (var entities = new ITowerCafeDBEntities())
{
switch (formId)
{
case 1:
CompanyType ct = entities.CompanyTypes.SingleOrDefault(type => type.Id == itemId);
entities.CompanyTypes.Attach(ct);
entities.CompanyTypes.Remove(ct);
entities.SaveChanges();
break;
case 2:
CompanyCostType cct = entities.CompanyCostTypes.SingleOrDefault(type => type.Id == itemId);
entities.CompanyCostTypes.Attach(cct);
entities.CompanyCostTypes.Remove(cct);
entities.SaveChanges();
break;
case 3:
CommentType coT = entities.CommentTypes.SingleOrDefault(type => type.Id == itemId);
entities.CommentTypes.Attach(coT);
entities.CommentTypes.Remove(coT);
entities.SaveChanges();
break;
case 4:
ProductCategory pc = entities.ProductCategories.SingleOrDefault(type => type.Id == itemId);
entities.ProductCategories.Attach(pc);
entities.ProductCategories.Remove(pc);
entities.SaveChanges();
break;
}
}
}
public static List <DisplayMenuItemViewModel> GetMenuByCompanyId(int id)
{
using (var entities = new ITowerCafeDBEntities())
{
int menuId = entities.Menus.SingleOrDefault(menu => menu.CreatorCompanyId == id).Id;
var joined = (from mpm in entities.MenuProductsMaps
join mp in entities.MenuProducts on
mpm.MenuProductId equals mp.Id
where mpm.MenuId == menuId
select new DisplayMenuItemViewModel
{
Id = mp.Id,
Name = mp.Name,
PreparationTime = mp.PreparationTime,
Price = mp.Price,
Category = mp.ProductCategory.Name,
ProductCode = mp.Product.Name
});
return(joined.ToList());
}
}
public static ReportViewModel GetReports()
{
using (var entities = new ITowerCafeDBEntities())
{
ReportViewModel reportViewModel = new ReportViewModel();
reportViewModel.PersonalStatistics = (from o in entities.Orders
where o.UserId == 1
group o.Cost by new { id = o.CompanyId, name = o.Company.Name } into t
select new PersonalStatisticViewModel
{
CompanyId = t.Key.id,
CompanyName = t.Key.name,
TotalCost = t.Sum()
}).ToList();
int mostPopularProductId = entities.OrderDetails.GroupBy(od => od.ProductId).Select(g => new { key = g.Key, count = g.Count() }).
OrderByDescending(x => x.count).First().key;
reportViewModel.MostPopularProduct = entities.MenuProducts.Include(x => x.Product).SingleOrDefault(od => od.Id == mostPopularProductId);
return(reportViewModel);
}
}
