public List <ReservationCollection> ReservationsByTime(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 == timeSlot.Hours
&& 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 <Waiter> Waiters_List()
{
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.Waiters.OrderBy(x => x.FirstName).ToList());
}
}
public List <ReservationByDate> GetReservationsByDate(string date)
{
if (date == "" || date == null)
{
return(new List <ReservationByDate>());
}
using (var context = new eRestaurantContext())
{
// remember linq does not like using datetime casting.
int year = (DateTime.Parse(date)).Year;
int month = (DateTime.Parse(date)).Month;
int day = (DateTime.Parse(date)).Day;
//query syntax
var results = from item in context.SpecialEvents
orderby item.Description
select new ReservationByDate()
{
Description = item.Description,
Reservations = from row in item.Reservations
where row.ReservationDate.Year == year &&
row.ReservationDate.Month == month &&
row.ReservationDate.Day == day
select new ReservationDetail() // POCO
{
CustomerName = row.CustomerName,
ReservationDate = row.ReservationDate,
NumberInParty = row.NumberInParty,
ContactPhone = row.ContactPhone,
ReservationStatus = row.ReservationStatus
}
};
return(results.ToList());
}
}
public Waiter Waiter_GetWaiterById(int id)
{
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.Waiters.Find(id));
}
}
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 <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 <Reservation> ReservationbyEvent(string eventcode)
{
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.Reservations.Where(anItem => anItem.Eventcode == eventcode).ToList());
}
}
public List <WaiterBilling> GetWaiterBillingReport()
{
using (eRestaurantContext context = new eRestaurantContext())
{
var results = from abillrow in context.Bills
where abillrow.BillDate.Month == 5
orderby abillrow.BillDate,
abillrow.Waiter.LastName,
abillrow.Waiter.FirstName
select new WaiterBilling()
{
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 =>
eachbillitemrow.Quantity * eachbillitemrow.SalePrice),
PartySize = abillrow.NumberInParty,
Contact = abillrow.Reservation.CustomerName
};
return(results.ToList());
}
}
public List <Reservations> Reservation_List()
{
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.Reservations.ToList());
}
}
public List <ReservationCollection> ReservationsByTime(DateTime date)
{
using (eRestaurantContext context = new eRestaurantContext())
{
var results = from data in context.Reservations
where data.ReservationDate.Year == date.Year &&
data.ReservationDate.Month == date.Month &&
data.ReservationDate.Day == date.Day &&
data.ReservationStatus == "B"
select new POCOs.ReservationSummary
{
Name = data.CustomerName,
Contact = data.ContactPhone,
Date = data.ReservationDate,
NumberInParty = data.NumberInParty,
Status = data.ReservationStatus,
EventType = data.SpecialEvent.Description
};
//Example of Grouping.
var finalResults = from data in results
orderby data.NumberInParty
group data by data.Date.TimeOfDay into itemGroup
select new DTOs.ReservationCollection
{
SeatingTime = itemGroup.Key.ToString(),
Reservations = itemGroup.ToList()
};
return(finalResults.ToList());
}
}
public List <ReservationCollection> ReservationsByTime(DateTime date)
{
using (eRestaurantContext context = new eRestaurantContext()) //remember to add e to eRestaurantContext and replace var with the eRestaurantContext datatype
{
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 == timeSlot.Hours
&& 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(); //causes execution of the query so that the retrieved data
//is in memory.
var finalResult = from item in result
orderby item.NumberInParty
group item by item.Date.Hour into itemGroup //temporary data collection
select new ReservationCollection() //DTO
{
Hour = itemGroup.Key,
Reservations = itemGroup.ToList()
};
return(finalResult.OrderBy(x => x.Hour).ToList()); //method syntax
}
}
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;
//query status
var results = from item in context.SpecialEvents
orderby item.Description
select new ReservationByDate() //DTO
{
EventDescription = item.Description,
Reservations = from row in item.Reservations //collection of navigated rows of ICollection in SpecialEvent entity
where row.ReservationDate.Year == theYear &&
row.ReservationDate.Month == theMonth &&
row.ReservationDate.Day == theDay
select new ReservationDetail() //POCO class
{
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 <ReservationCollection> ReservationsByTime(DateTime date)
{
using (var context = new eRestaurantContext())
{
var result = (from eachReservationRow in context.Reservations //take each row from the Reservation
where eachReservationRow.ReservationDate.Year == date.Year &&
eachReservationRow.ReservationDate.Month == date.Month &&
eachReservationRow.ReservationDate.Day == date.Day
// && data.ReservationDate.Hour == timeSlot.Hours
&& eachReservationRow.ReservationStatus == "B" //Reservation.Booked
select new ReservationSummary()
{
ID = eachReservationRow.ReservationID,
Name = eachReservationRow.CustomerName,
Date = eachReservationRow.ReservationDate,
NumberInParty = eachReservationRow.NumberInParty,
Status = eachReservationRow.ReservationStatus,
Event = eachReservationRow.Event.Description,
Contact = eachReservationRow.ContactPhone
}).ToList(); //causes execution of the query so
//that the retrieved data is in memory
//so as to be used in the following query
var finalResult = from item in result
orderby item.NumberInParty
group item by item.Date.Hour into itemGroup //Datetime is not liked for C#;
//temporary data collection, put result into new 'itemGroup' collection
select new ReservationCollection() //DTO
{
Hour = itemGroup.Key,
Reservations = itemGroup.ToList()
};
return(finalResult.OrderBy(x => x.Hour).ToList()); //method syntax
}
}
//seating of walkins
/// <summary>
/// Seats a customer that is a walk-in
/// </summary>
/// <param name="when">A mock value of the date/time (Temporary - see remarks)</param>
/// <param name="tableNumber">Table number to be seated</param>
/// <param name="customerCount">Number of customers being seated</param>
/// <param name="waiterId">Id of waiter that is serving</param>
public void SeatCustomer(DateTime when, byte tableNumber, int customerCount, int waiterId)
{
var availableSeats = AvailableSeatingByDateTime(when.Date, when.TimeOfDay);
using (var context = new eRestaurantContext())
{
List <string> errors = new List <string>();
// Rule checking:
// - Table must be available - typically a direct check on the table, but proxied based on the mocked time here
// - Table must be big enough for the # of customers
if (!availableSeats.Exists(x => x.Table == tableNumber))
{
errors.Add("Table is currently not available");
}
else if (!availableSeats.Exists(x => x.Table == tableNumber && x.Seating >= customerCount))
{
errors.Add("Insufficient seating capacity for number of customers.");
}
if (errors.Count > 0)
{
throw new BusinessRuleException("Unable to seat customer", errors);
}
Bill seatedCustomer = new Bill()
{
BillDate = when,
NumberInParty = customerCount,
WaiterID = waiterId,
TableID = context.Tables.Single(x => x.TableNumber == tableNumber).TableID
};
context.Bills.Add(seatedCustomer);
context.SaveChanges();
}
}
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,
Reservations = from row in item.Reservations
where row.ReservationDate.Year == theYear &&
row.ReservationDate.Month == theMonth &&
row.ReservationDate.Day == theDay
select new ReservationDetail()
{
CustomerName = row.CustomerName,
ReservationDate = row.ReservationDate,
NumberInParty = row.NumberInParty,
ContactPhone = row.ContactPhone,
ReservationStatus = row.ReservationStatus
}
};
return(results.ToList());
}
}
public List <MenuCategoryFoodItems> MenuCategoryFoodItemsDTO_Get()
{
//set up transaction area
using (var context = new eRestaurantContext())
{
var results = from food in context.Items
group food by new { food.MenuCategoryID, food.CurrentPrice } into tempdataset
orderby food.MenuCategory.Description
select new MenuCategoryFoodItems
{
MenuCategoryDescription = tempdataset.Key.Description,
FoodItems = (from x in tempdataset
)
};
//ItemID = food.ItemID,
//FoodDescription = food.Description,
//CurrentPrice = food.CurrentPrice
// TimeSaved = 10
// };
//from x in tempdataset
//select new
//{
// ItemID = x.ItemID,
// FoodDescription = x.Description,
// TimesServed = x.BillItems.Count()
//}
return(results.ToList());
}
}
}
public List <MenuCategoryFoodItemsDTO> MenuCategoryFoodItemsDTO_Get()
{
//Setup transaction area
using (var context = new eRestaurantContext()) //Goes to the appropiate context class
{
//Gets the data using the Linq query
//When you bring your query from Linqpad to your program you must change your reference(s) to the data source
//You may also need to change your navigation referencing use in Linqpad
//to the navigation properties you stated in the Entity class definitions
var results = from food in context.Items
group food by new { food.MenuCategory.Description } into tempdataset
select new MenuCategoryFoodItemsDTO
{
MenuCategoryDescription = tempdataset.Key.Description,
FoodItems = (from x in tempdataset
select new FoodItemCount
{
ItemID = x.ItemID,
FoodDescription = x.Description,
CurrentPrice = x.CurrentPrice,
TimesServed = x.BillItems.Count()
}).ToList()
};
//The following requires the query data in memory
//.ToList()
//At this point the query will actually execute
return(results.ToList());
}
}
public SpecialEvent SpecialEventByEventCode(string eventcode)
{
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.SpecialEvents.Find(eventcode));
}
}
public Waiter GetWaiter(int waiterid)
{
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.Waiters.Find(waiterid));
}
}
public DateTime GetLastBillDateTime()
{
using (eRestaurantContext context = new eRestaurantContext())
{
var result = context.Bills.Max(x => x.BillDate);
return(result);
}
}
public void Waiters_Update(Waiter item)
{
using (eRestaurantContext context = new eRestaurantContext())
{
context.Entry <Waiter>(context.Waiters.Attach(item)).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
}
public DateTime GetLastBillDateTime()
{
using (var context = new eRestaurantContext())
{
var result = context.Bills.Max(eachBillrow => eachBillrow.BillDate);
return(result);
}
}
public DateTime GetLastBillDate()
{
using (var context = new eRestaurantContext())
{
var results = context.Bill.Max(x => x.BillDate);
return(results);
}
}
public List <Reservation> Reservation_List()
{
//interfacing with our Context class
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.Reservations.ToList());
}
}
public List <Reservations> ReservationByEvent(string eventCode)
{
using (eRestaurantContext Context = new eRestaurantContext())
{
//Foreach rowIndicator the EventCode must equal the passed in event code.
return(Context.Reservations.Where(rowIndicator => rowIndicator.EventCode == eventCode).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 Waiter GetWaiter(int waiterID)
{
using (eRestaurantContext Context = new eRestaurantContext())
{
//Foreach rowIndicator the EventCode must equal the passed in event code.
return(Context.Waiter.Find(waiterID));
}
}
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 List <SpecialEvent> SpecialEvent_List()
{
//interfacing with our Context class
using (eRestaurantContext context = new eRestaurantContext())
{
return(context.SpecialEvents.ToList());
}
}
