public List<DailyReservation> ListUpcomingReservations(string eventCode)
{
using (var context = new RestaurantContext())
{
// step1 will be an object that generates SQL to run on the database
var step1 = from eachRow in context.Reservations
where eachRow.ReservationStatus == "B"
// TBA - && eachRow has the correct EventCode...
orderby eachRow.ReservationDate
// select eachRow
group eachRow by new { eachRow.ReservationDate.Month, eachRow.ReservationDate.Day };
// by calling step1.ToList() , the results of step1 are brought back into RAM(memory)
// for us to query as LINQ-to-Objects.
var result = from dailyReservation in step1.ToList()
select new DailyReservation() // Create a DTO class called DailyReservation
{
Month = dailyReservation.Key.Month,
Day = dailyReservation.Key.Day,
Reservations = from booking in dailyReservation
select new Booking() // Create a Booking POCO class
{
Name = booking.CustomerName,
Time = booking.ReservationDate.TimeOfDay,
NumberInParty = booking.NumberInParty,
Phone = booking.ContactPhone,
Event = booking.SpecialEvent == null
? (string)null
: booking.SpecialEvent.Description
}
};
return result.ToList();
}
}
public List<CategoryDTO> ListMenuItems()
{
using (var context = new RestaurantContext())
{
var data = from cat in context.MenuCategories
orderby cat.Description
select new CategoryDTO() // use the DTO
{
Description = cat.Description,
MenuItems = from item in cat.Items
where item.Active
orderby item.Description
select new MenuItemDTO
{
Description = item.Description,
Price = item.CurrentPrice,
Calories = item.Calories,
Comment = item.Comment
}
};
return data.ToList();
// Note: To use the Lamba or Method style of Include(),
// you need to have the following namespace reference:
// use System.Data.Entity;
// The Include() method on the DbSet<T> class performs
// "eager loading" of the data.
// return context.Items.Include(it => it.MenuCategory).ToList();
}
}
public List<ReservationCollection> ReservationsForDate(DateTime date)
{
using (var context = new RestaurantContext())
{
var result = from eachRow in context.Reservations
where eachRow.ReservationDate.Year == date.Year
&& eachRow.ReservationDate.Month == date.Month
&& eachRow.ReservationDate.Day == date.Day
&& eachRow.ReservationStatus == Reservation.Booked
select new ReservationSummary() //DTOs
{
Name = eachRow.CustomerName,
Date = eachRow.ReservationDate,
NumberInParty = eachRow.NumberInParty,
Status = eachRow.ReservationStatus.ToString(),
Event = eachRow.SpecialEvent.Description,
Contact = eachRow.ContactPhone
};
var finalResult = from eachItem in result
group eachItem by eachItem.Date.Hour into itemGroup
select new ReservationCollection()
{
Hour = itemGroup.Key,
Reservations = itemGroup.ToList()
};
return finalResult.ToList();
}
}
public List<CategoryDTO> ListMenuItem()
{
using(var context = new RestaurantContext())
{
//return context.Items.ToList();
var data = from cat in context.MenuCategories
orderby cat.Description
select new CategoryDTO() // use the DTO
{
Description = cat.Description,
MenuItems = from item in cat.Items
where item.Active
orderby item.Description
select new MenuItemDTO() //use the DTO
{
Description = item.Description,
Price = item.CurrentPrice,
Calories = item.Calories,
Comment = item.Comment
}
};
return data.ToList();
}
}
public void getSpecialEvent(int ID)
{
using (RestaurantContext specialEventDBContext = new RestaurantContext())
{
specialEventDBContext.SpecialEvents.Find(ID);
}
}
public List<DailyReservation> ListUpcomingReservations(string eventCode)
{
using (var context = new RestaurantContext())
{
//Step 1 will be an object that generates SQL to run on the database
var step1 = from eachRow in context.Reservations
where eachRow.ReservationStatus == "B"
orderby eachRow.ReservationDate
group eachRow by new { eachRow.ReservationDate.Month, eachRow.ReservationDate.Day };
//By calling step1.ToList(), the results are brought into RAM (memory) for us to query as LINQ-To-Objects
var result = from dailyReservation in step1.ToList()
select new DailyReservation()
{
Month = dailyReservation.Key.Month,
Day = dailyReservation.Key.Day,
Reservations = from booking in dailyReservation
select new Booking()
{
Name = booking.CustomerName,
NumberInParty = booking.NumberInParty,
Time = booking.ReservationDate.TimeOfDay,
Phone = booking.ContactPhone,
Event = booking.SpecialEvent == null ? (string)null : booking.SpecialEvent.Description //we are doing it in memory
}
};
return result.ToList();
}
}
public List<ReservationCollection> ReservationsByTime(DateTime date)
{
using(var context = new RestaurantContext())
{
var result = (from data in context.Reservations
where data.ReservationDate.Year == date.Year
&& data.ReservationDate.Month == date.Month
&& data.ReservationDate.Day == date.Day
&& data.ReservationStatus == Reservation.Booked
select new ReservationSummary()
{
ID = data.ReservationID,
Name = data.CustomerName,
Date = data.ReservationDate,
NumberInParty = data.NumberinParty,
Status = data.ReservationStatus,
Event = data.SpecialEvent.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();
// return finalResult.ToList<dynamic>();
}
}
public List<DailyReservation> ListUpcomingReservations(string eventCode)
{
using (var context = new RestaurantContext())
{
var step1 = from eachRow in context.Reservations
where eachRow.ReservationStatus == "B"
// TBA - && eachRow has the correct EventCode...
orderby eachRow.ReservationDate
//select eachRow
group eachRow by new { eachRow.ReservationDate.Month, eachRow.ReservationDate.Day };
var result = from dailyReservation in step1.ToList()
select new DailyReservation() // Create a DTO class called DailyReservation
{
Month = dailyReservation.Key.Month,
Day = dailyReservation.Key.Day,
Reservations = from booking in dailyReservation
select new Booking() // Create a Booking POCO Class
{
Name = booking.CustomerName,
Time = booking.ReservationDate.TimeOfDay,
NumberInParty = booking.NumberInParty,
Phone = booking.ContactPhone,
Event = booking.SpecialEvent == null
? (string)null
: booking.SpecialEvent.Description
}
};
return result.ToList();
}
}
public DateTime getLastBillDateTime()
{
using (var context = new RestaurantContext())
{
var result = context.Bills.Max(row => row.BillDate);
return result;
};
}
public DateTime GetLastBillDateTime()
{
using (var context = new RestaurantContext())
{
var result = context.Bills.Max(x => x.BillDate);
return result;
}
}
public void SplitBill(int billid, List<OrderItem> updatesToOriginalBillItems, List<OrderItem> newBillItems)
{
//split the bill in two
using(var context = new RestaurantContext())
{
//TODO: 0)Validation
//1) Get the Bill
var bill = context.Bills.Find(billid);
if (bill == null) throw new ArgumentException("Invalid Bill ID - does not exist");
//2) Loop through bill items, if item not in original, remove
List<BillItem> toRemove = new List<BillItem>();
foreach(var item in bill.BillItems) // the items already in the DB
{
bool inOriginal = updatesToOriginalBillItems.Any(
x => x.ItemName == item.Items.Description);
bool inNewItems = newBillItems.Any(x => x.ItemName == item.Items.Description);
if(!inOriginal)
{
if (!inNewItems)
throw new Exception("Bill needs to be paid");
toRemove.Add(item);
}
}
foreach (var item in toRemove)
context.BillItems.Remove(item);
//3) Make a new bill
var newBill = new Bill()
{
BillDate = bill.BillDate, //some info from original bill
Comment = "Split from bill# " + bill.BillID,
NumberInParty = bill.NumberInParty,
OrderPlaced = bill.OrderPlaced,
OrderReady = bill.OrderReady,
OrderServed = bill.OrderServed,
WaiterID = bill.WaiterID
//TO DO:splitting a bill for a single table vs. reservation?
};
//4) Add the new moved items to the new bill
foreach(var item in toRemove)
{
newBill.BillItems.Add(new BillItem()
{
ItemID = item.ItemID,
Notes = item.Notes,
Quantity = item.Quantity,
SalePrice = item.SalePrice,
UnitCost = item.UnitCost
});
}
//5) Add the new bill to the context
context.Bills.Add(newBill);
//6) Save the changes...
context.SaveChanges(); // call this only ONCE at the end - TRANSACTION
}
}
public void UpdateSpecialEvent(SpecialEvent Entities)
{
using(RestaurantContext specialEventDBContext = new RestaurantContext())
{ var updating = specialEventDBContext.SpecialEvents.Attach(Entities);
var matchingWithExistingValues = specialEventDBContext.Entry<SpecialEvent>(updating);
matchingWithExistingValues.State = System.Data.Entity.EntityState.Modified;
specialEventDBContext.SaveChanges();
}
}
public List<MenuCategory> ListMenuCategories()
{
using(var context = new RestaurantContext())
{
var data = from category in context.MenuCategories
select category;
return data.ToList();
}
}
public List<SpecialEvent> ListAllSpecialEvents()
{
// This using statement ensures that our connection to the database is
// properly "closed" once we are done "using" our DAL object.
// (context is our DAL object)
using (RestaurantContext context = new RestaurantContext())
{
return context.SpecialEvents.ToList();
}
}
public List<Reservation> getReservationByEventCode(string eventCode)
{
using(var context = new RestaurantContext())
{
var data = from info in context.Reservations
where info.EventCode == eventCode
select info;
return data.ToList();
}
}
public void DeleteSpecialEvent(SpecialEvent Entities)
{
using(RestaurantContext specialEventDBContext = new RestaurantContext())
{
var existingvalue = specialEventDBContext.SpecialEvents.Find(Entities.EventCode);
// remember to add the DataKeyNames for delete and update
specialEventDBContext.SpecialEvents.Remove(existingvalue);
specialEventDBContext.SaveChanges();
}
}
public List<Item> ListMenuItems()
{
using (var Context = new RestaurantContext())
{
// Note: To use thr Lambda or Method style of Include(),
//you need to have the following namespace reference:
//use System.Data.Entity;
//The .Include() method on the DbSet<T> class performs
//"eager lading" of the data.
return Context.Items.Include(it=> it.MenuCategory).ToList();
}
}
public void InsertWaiter(Waiter item)
{
using (RestaurantContext context = new RestaurantContext())
{
//Add the item to the DBcontext
var added = context.Waiters.Add(item);
//PS we arent really going to do anything with the variable 'added', I just want you to be aware that the Add() method will return the newly added object. (This can be useful in other situations, which we will see later)
//Save the changes to the database
context.SaveChanges();
}
}
public void AddSpecialEvent(SpecialEvent item)
{
using (var context = new RestaurantContext())
{
//add the item to dbcontext
var added = context.SpecialEvents.Add(item);
//p.s. - we aren't really going to do anything with the variable 'added;
// i just want you to be aware that the Add() METHOD will return the
//newly added object. (this can be useful in other situations, which we will see later)
//save changes
context.SaveChanges();
}
}
public List<ActiveEvent> ListActiveEvents()
{
using (var context = new RestaurantContext())
{
var results = from eachRow in context.SpecialEvents
where eachRow.Active
select new ActiveEvent()
{
Code = eachRow.EventCode,
Description = eachRow.Description
};
return results.ToList();
}
}
public List<WaiterOnDuty> ListWaiters()
{
using (var context = new RestaurantContext())
{
var result = from person in context.Waiters
where person.ReleaseDate == null
select new WaiterOnDuty()
{
WaiterId = person.WaiterID,
FullName = person.FirstName + " " + person.LastName
};
return result.ToList();
}
}
public void DeleteWaiter(Waiter item)
{
using (RestaurantContext context = new RestaurantContext())
{
//First give a reference to the actual item in the DB
//Find() is a method to look up an item by its primary key
var existing = context.Waiters.Find(item.WaiterID);
//Second, remove the item from the DB context
context.Waiters.Remove(existing);
//Lastly, save the changes to the database
context.SaveChanges();
}
}
public void DeleteSpecialEvent(SpecialEvent item)
{
using (var context = new RestaurantContext())
{
// First, get a reference to the actual item in the Db
// Find() is a method to look up an item by it's primary key.
var existing = context.SpecialEvents.Find(item.EventCode);
// Second, remove the item from the database context
context.SpecialEvents.Remove(existing);
// Lastly, save the changes to the database
context.SaveChanges();
}
}
public void AddSpecialEvent(SpecialEvent item)
{
using (var context = new RestaurantContext())
{
// Add the item to the dbContext
var added = context.SpecialEvents.Add(item);
// p.s. - we aren't really going to do anything with the variable 'added'
// I just want you to be aware that the Add() method will return the
// newly added object. (This can be useful in other situations, which
// we will see later.)
// Save the changes to the database
context.SaveChanges();
}
}
public List<UnpaidBill> ListUnpaidBills()
{
using(var context = new RestaurantContext())
{
var result = from data in context.Bills
where !data.PaidStatus
&& data.Items.Count() > 0
select new UnpaidBill()
{
DisplayText = "Bill " + data.BillID.ToString(),
BillId = data.BillID
};
return result.ToList();
}
}
public void DeleteSpecialEvent(SpecialEvent item)
{
using (var context = new RestaurantContext())
{
//first get a reference to the actual item by its primary key
//find() is a METHOD to look up an utem by its primary key.
var existing = context.SpecialEvents.Find(item.EventCode);
//second, remove the item from the database context
context.SpecialEvents.Remove(existing);
//lastly, save the changes to the database
context.SaveChanges();
}
}
public void UpdateSpecialEvent(SpecialEvent item)
{
using ( RestaurantContext context = new RestaurantContext())
{
//First attach the item to the dbContext collection
var attached = context.SpecialEvents.Attach(item);
//Second,get the entry for existing data that should match for
//this specific special event
var existing = context.Entry<SpecialEvent>(attached);
//Third,mark that the object's values have changed
existing.State = System.Data.Entity.EntityState.Modified;
//Lastly,save the changes in the database
context.SaveChanges();
}
}
public List<CategoryMenuItem> GetReportCategoryMenuItems()
{
using(var context = new RestaurantContext())
{
var results = from cat in context.Items
orderby cat.MenuCategory.Description, cat.Description
select new CategoryMenuItem()
{
CategoryDescription = cat.MenuCategory.Description,
ItemDescription = cat.Description,
Price = cat.CurrentPrice,
Calories = cat.Calories,
Comment = cat.Comment
};
return results.ToList();
}
}
public void DeactivateSpecialEvent(SpecialEvent item)
{
using (var context = new RestaurantContext())
{
// First, get a reference to the actual item in the Db
// Find() is a method to look up an item by it's primary key.
var existing = context.SpecialEvents.Find(item.EventCode);
// Second, remove the item from the database context
existing.Active = false; // Modifies the property on the SpecialEvent
var updatable = context.Entry(existing); // Get a reference to the special event as an Entity in the database context
// Specify a particular property as being changed.
updatable.Property(x => x.Active).IsModified = true;
// Lastly, save the changes to the database
context.SaveChanges();
}
}
public void UpdateSpecialEvent(SpecialEvent item)
{
using (RestaurantContext context = new RestaurantContext())
{
//First attach the item to the dbContext collection
var attached = context.SpecialEvents.Attach(item);
// var matchingWithExistingValues = context.Entry<SpecialEvent>(attached);
//matchingWithExistingValues.State = System.Data.Entity.EntityState.Modified;
//context.SaveChanges();
var existing = context.Entry<SpecialEvent>(attached);
existing.State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
}
//First attacj the item to the dbContext collection
}
