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 }