public List<Reservation> ReservationsByEvent(string eventcode)
{
using (eRestaurantContext context = new eRestaurantContext())
{
return context.Reservations.Where(anItem => anItem.EventCode == eventcode).ToList();
}
}
public List<CategoryMenuItems> CategoryMenuItems_List()
{
using (var context = new eRestaurantContext())
{
//query status
var results = from category in context.MenuCategories
orderby category.Description
select new CategoryMenuItems() //DTO
{
Description = category.Description,
MenuItems = from row in category.MenuItems //collection of navigated rows of ICollection
//in Special Event entity
select new MenuItem() //POCO class
{
Description = row.Description,
Price =row.CurrentPrice,
Calories =row.Calories,
Comment = row.Comment
}
};
return results.ToList();
}
}
public List<ReservationsByDate> GetReservationsByDate(string reservationdate)
{
using (var context = new eRestaurantContext())
{
//Linq is not very playful or cooperative with
//DateTime
//extract the year, month and day ourselves out
//of the passed parameter value
int theYear = (DateTime.Parse(reservationdate)).Year;
int theMonth = (DateTime.Parse(reservationdate)).Month;
int theDay = (DateTime.Parse(reservationdate)).Day;
var results = from eventitem in context.SpecialEvents
orderby eventitem.Description
select new ReservationsByDate() //a new instance for each specialevent row on the table
{
Description = eventitem.Description,
Reservations = from row in eventitem.Reservations
where row.ReservationDate.Year == theYear
&& row.ReservationDate.Month == theMonth
&& row.ReservationDate.Day == theDay
select new ReservationDetail() // a new for each reservation of a particular specialevent code
{
CustomerName = row.CustomerName,
ReservationDate = row.ReservationDate,
NumberInParty = row.NumberInParty,
ContactPhone = row.ContactPhone,
ReservationStatus = row.ReservationStatus
}
};
return results.ToList();
}
}
public List<ReservationByDate> GetReservationsByDate(string reservationdate)
{
using (var context = new eRestaurantContext())
{
int theYear =(DateTime.Parse(reservationdate)).Year;
int theMonth =(DateTime.Parse(reservationdate)).Month;
int theDay =(DateTime.Parse(reservationdate)).Day;
//query syntax
var results = from item in context.SpecialEvents
orderby item.Description
select new ReservationByDate()
{
Description = item.Description,
Reservations = from r in item.Reservations
where r.ReservationDate.Year == theYear
&& r.ReservationDate.Month == theMonth
&& r.ReservationDate.Day == theDay
select new ReservationDetail()
{
CustomerName = r.CustomerName,
ReservationDate = r.ReservationDate,
NumberInParty = r.NumberInParty,
ContactPhone = r.ContactPhone,
ReservationStatus = r.ReservationStatus,
}
};
return results.ToList();
}
}
public List<eRestaurantSystem.DAL.Entities.DTOs.CategoryMenuItems> CategoryMenuItems_List()
{
using (var context = new eRestaurantContext())
{
//Query syntax
var result = from category in context.MenuCategories
orderby category.Description
select new eRestaurantSystem.DAL.Entities.DTOs.CategoryMenuItems() //DTO
{
Description = category.Description,
MenuItems = from row in category.MenuItems //virtual property ......collection of navigated rows of ICollection in SpecialEvent
select new MenuItem() //POCO
{
Description = row.Description,
Price = row.CurrentPrice,
Calories = row.Calories,
Comment = row.Comment
}
};
return result.ToList();
}
}
public SpecialEvent SpecialEventByEventCode(string eventCode)
{
using (eRestaurantContext context = new eRestaurantContext())
{
return context.SpecialEvents.Find(eventCode);
}
}
public List<ReservationByDate> GetReservationsByDate(string reservationdate)
{
using (var context = new eRestaurantContext())
{
//remember Linq does not like using DateTime casting
int theYear = (DateTime.Parse(reservationdate)).Year;
int theMonth = (DateTime.Parse(reservationdate)).Month;
int theDay = (DateTime.Parse(reservationdate)).Day;
var results = from item in context.SpecialEvents
orderby item.Description
select new ReservationByDate()//DTO
{
Description = item.Description,
Reservation = from row in item.Reservations //collection of navigated rows of collection
where row.ReservationDate.Year == theYear
&& row.ReservationDate.Month == theMonth
&& row.ReservationDate.Day == theDay
select new ReservationDetail() //POCO
{
CustomerName = row.CustomerName,
ReservationDate = row.ReservationDate,
NumberInParty = row.NumberInParty,
ContactPhone = row.ContactPhone,
ReservationStatus = row.ReservationStatus
}
};
return results.ToList();
}
}
public List<eRestaurantSystem.Entities.DTOs.CategoryMenuItems> GetCategoryMenuItems_List()
{
using (var context = new eRestaurantContext())
{
//remember LINQ does not like using DateTime casting
//Query syntax
var results = from category in context.MenuCategories
orderby category.Description
select new eRestaurantSystem.Entities.DTOs.CategoryMenuItems() //DTO
{
Description = category.Description,
MenuItems = from row in category.MenuItems
select new MenuItem()
{
Description = row. Description,
Price = row.CurrentPrice,
Calories = row.Calories,
Comment = row.Comment
}
};
return results.ToList();
}
}
public List<WaiterBilling>GetWaiterBillReport()
{
using (eRestaurantContext context = new eRestaurantContext())
{
var results = from abillrow in context.Bills//context? 1. put context
where abillrow.BillDate.Month == 5
orderby abillrow.BillDate,
abillrow.Waiter.LastName,
abillrow.Waiter.FirstName
select new WaiterBilling() //3.put class name
{
BillDate = abillrow.BillDate.Year +"/"+
abillrow.BillDate.Month +"/"+
abillrow.BillDate.Day,
WaiterName = abillrow.Waiter.LastName + ", " +
abillrow.Waiter.FirstName,
BillID = abillrow.BillID,
BillTotal = abillrow.Items.Sum(eachbillitemrow => //billitem=>item?
eachbillitemrow.Quantity * eachbillitemrow.SalePrice),
PartySize = abillrow.NumberInParty,
Contact = abillrow.Reservation.CustomerName
}; //2. put ;
return results.ToList();
}
}
public List<ReservationCollection> ReservationByTime(DateTime date)
{
using (var context = new eRestaurantContext())
{
var result = (from data in context.Reservations
where data.ReservationDate.Year == date.Year &&
data.ReservationDate.Month == date.Month
&& data.ReservationDate.Day == date.Day
//&& data.ReservationDate.Hour == ti
&& data.ReservationStatus == Reservation.Booked
select new ReservationSummary()
{
ID = data.ReservationID,
Name = data.CustomerName,
Date = data.ReservationDate,
NumberInParty = data.NumberInParty,
Status = data.ReservationStatus,
Event = data.Event.Description,
Contact = data.ContactPhone
}).ToList();
var finalREsult = from item in result
orderby item.NumberInParty
group item by item.Date.Hour into itemGroup
select new ReservationCollection()
{
Hour = itemGroup.Key,
Reservations = itemGroup.ToList()
};
return finalREsult.OrderBy(x => x.Hour).ToList();
}
}
public List<Reservation> Reservations_List()
{
//interfacing with our context class.DbContext, ties into entity framework.
using (eRestaurantContext context = new eRestaurantContext())
{
return context.Reservations.ToList();
}
}
public List<SpecialEvent> SpecialEvent_List()
{
//interfacing with our context class.DbContext, ties into entity framework.
using (eRestaurantContext context = new eRestaurantContext())
{
return context.SpecialEvents.ToList();
}
}
public List<SpecialEvent> SpecialEvent_List()
{
//interfacing with our Context class
using(eRestaurantContext context = new eRestaurantContext())
{
return context.SpecialEvents.ToList();
}
}
public List<SpecialEvent> Reservation_List()
{
//interfacing with our context class which inherits from DbContext
using (eRestaurantContext context = new eRestaurantContext())
{
return context.Reservations.ToList();
}
}
public DateTime GetLastBillDateTime()
{
using (var context = new eRestaurantContext())
{
var result = context.Bills.Max(x => x.BillDate);
return result;
}
}
public List<Reservation> Reservation_List()
{
//interfacing with our Context class
using (eRestaurantContext context = new eRestaurantContext())
{
return context.Reservations.ToList();
}
}
public void SpecialEvents_Update(SpecialEvent item)
{
using (eRestaurantContext context = new eRestaurantContext())
{
context.Entry<SpecialEvent>(context.SpecialEvents.Attach(item)).State =System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public DateTime GetLastBillDateTime()
{
using (eRestaurantContext context = new eRestaurantContext())
{
var result = context.Bills.Max(eachBillrow => eachBillrow.BillDate);
return result;
}
}
public void SpecialEvents_Delete(SpecialEvent item)
{
using (eRestaurantContext context = new eRestaurantContext())
{
SpecialEvent existing = context.SpecialEvents.Find(item.EventCode);
context.SpecialEvents.Remove(existing);
context.SaveChanges();
}
}
public List<Waiter> Waiters_List()
{
using (var context = new eRestaurantContext())
{
var results = from item in context.Waiters
orderby item.LastName, item.FirstName
select item;
return results.ToList(); //none, 1 or more rows
}
}
public Waiter GetWaiterByID(int waiterid)
{
using (var context = new eRestaurantContext())
{
var results = from item in context.Waiters
where item.WaiterID == waiterid
select item;
return results.FirstOrDefault(); //one row at most
}
}
public void SpecialEvents_Add(SpecialEvent item)
{
using(eRestaurantContext context = new eRestaurantContext())
{
SpecialEvent added = null;
added = context.SpecialEvents.Add(item);
context.SaveChanges(); //Commits the add to the database.
//Furthermore, this evaluates the annotations (validates) on the entity.
//Included: [Required], [StringLength], [Range], etc.
}
}
public List<SpecialEvent> SpecialEvent_List()
{
//interfacing with our Context class
using (eRestaurantContext context = new eRestaurantContext())
{
// return context.SpecialEvents.ToList();
var results = from item in context.SpecialEvents select item;
return results.ToList();
}
}
public List<SpecialEvent> SpecialEvents_List()
{
//connect to our DbContext class in the DAL
//create an instance of the class
//we will use a transaction to hold our query
using (var context = new eRestaurantContext())
{
//method syntax
return context.SpecialEvents.OrderBy(x => x.Description).ToList();
}
}
public List<Reservation> GetReservationsByEventCode(string eventcode)
{
using (var context = new eRestaurantContext())
{
//query syntax
var results = from item in context.Reservations
where item.EventCode.Equals(eventcode)
orderby item.CustomerName, item.ReservationDate
select item;
return results.ToList();
}
}
public void SpecialEvents_Add(SpecialEvent item)
{
using (eRestaurantContext context = new eRestaurantContext())
{
SpecialEvent added = null;
added = context.SpecialEvents.Add(item);
// commits the add to the database
// evaluates the annotations (validations) on your entity
// [Required],[StringLength],[Range],...
context.SaveChanges();
}
}
public List<Table> Table_List()
{
using (var context = new eRestaurantContext())
{
//method syntax
//return context.SpecialEvents.OrderBy(x => x.Description).ToList();
//query syntax
var results = from item in context.Tables
select item;
return results.ToList();
}
}
public List<SpecialEvent> SpecialEvent_List ()
{
//Interfacing with the Context class which inherits
using (eRestaurantContext context = new eRestaurantContext())
{
//Using Context DbSet to get entity data.
//CODE: return context.SpecialEvents.ToList();
//Get a list of instances for entity using LINQ
var results = from item in context.SpecialEvents
select item;
return results.ToList();
}
}
public List<ItemRecipes> GetItemRecipes()
{
using (var context = new eRestaurantContext())
{
var results = from row in context.Recipes
orderby row.Item.Description
select new ItemRecipes
{
ItemName = row.Item.Description,
PrepTime = row.PrepTime,
Instructions = row.Instructions,
Ingredients = row.RecipeIngredients
};
return results.ToList();
}
}
public List<POCOs.CategoryMenuItems> GetReportCategoryMenuItems()
{
using (eRestaurantContext context = new eRestaurantContext())
{
var results = from data in context.Items
select new POCOs.CategoryMenuItems
{
CategoryDescription = data.MenuCategory.Description,
ItemDescription = data.Description,
Price = data.CurrentPrice,
Calories = data.Calories,
Comment = data.Comment
};
return results.ToList();
}
}
