Consolidating a List of Dates with 2 columns being equal-CodePudding

I have a List of that comes from a sql table, This is what the model looks like below:

public class ReservationReport
    {
        public string HotelName { get; set; }
        public string First_Name { get; set; }
        public string Last_Name { get; set; }
        public string Gender { get; set; }
        public DateTime Arrive { get; set; }
        public DateTime AMDepart { get; set; }
        public int companypay { get; set; }
        public int selfpay { get; set; }
        public int guestID { get; set; }
        public string RoomType { get; set; }
        public int Inventory_ID { get; set; }
        public int HotelID { get; set; }
    }

The list is currently all separated by each day. I need to combine it in a way that shows Hotel and which members stayed by how many days for example:

List coming in from sql will look like this

-Hotel 1 John smith, Feb 1 - Feb 2
-Hotel 1 John smith, Feb 2 - Feb 3
-Hotel 1 Someone Else, Feb 1 - Feb2
-Hotel 1 John smith , Feb 10- Feb 11

List should show something like this:

Hotel 1 John smith, Feb 1- Feb3 (Stayed 2 days)
Hotel 1 Someone else, Feb 1- Feb2 (stayed 1 day)
Hotel 1 John Smith, Feb10- Feb11 (Stayed 1 day)

What I have currently is the following code. The problem I have is when a person has stayed in the same hotel but on days outside of touching they are only put once in the combined list. This is what I have currently

CombinedList = temp2.GroupBy(m => new { m.guestID, m.HotelID }).Select(group => group.First()).ToList();
                foreach (var item in CombinedList)
                {
                    // disposable list to get values for only the correct records 
                    List<ReservationReport> dispose = temp2.Where(x => x.HotelID == item.HotelID && x.guestID  == item.guestID ).ToList();
                    item.Arrive = dispose.Min(i => i.Arrive);
                    item.AMDepart = dispose.Max(i => i.AMDepart);
                    item.selfpay = dispose.Sum(i => i.selfpay);
                    item.companypay = dispose.Sum(i => i.companypay);
                }

After I run the above code my Combined List looks like the below

Hotel 1 Someone else, Feb 1- Feb2 (stayed 1 day)
Hotel 1 John Smith, Feb1- Feb11 (Stayed 10 day)

My combinedList call is only giving me 2 rows when it should contain 3, the problem is I am not sure how to lambda or linq with the 2 variables and where the dates are next to each other in this manner.

any help would be appreciated. Thank you.

CodePudding user response：

I modified the codes you've shown in your controller,and it seems work well:

public IActionResult Index()
        {
            var reservationreport1 = new ReservationReport()
            {
                Report_ID = 1,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 1),
                AMDepart = new DateTime(2021, 1, 2)
            };
            var reservationreport2 = new ReservationReport()
            {
                Report_ID = 2,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 2),
                AMDepart = new DateTime(2021, 1, 3)
            };
            var reservationreport3 = new ReservationReport()
            {
                Report_ID = 3,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 3),
                AMDepart = new DateTime(2021, 1, 4)
            };

            var reservationreport4 = new ReservationReport()
            {
                Report_ID = 4,
                guestID = 2,
                Name = "a2",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 4),
                AMDepart = new DateTime(2021, 1, 5)
            };
            var reservationreport5 = new ReservationReport()
            {
                Report_ID = 5,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 6),
                AMDepart = new DateTime(2021, 1, 7)
            };
            var reportlist = new List<ReservationReport>() { };
            reportlist.Add(reservationreport1);
            reportlist.Add(reservationreport2);
            reportlist.Add(reservationreport3);
            reportlist.Add(reservationreport4);
            reportlist.Add(reservationreport5);
            var templist = new List<ReservationReport>();
            reportlist.ForEach(m => { templist.Add(m); });
            foreach (var item in reportlist)
            {
                Ressetlist(templist, item);
            }
            var targetlist = templist.OrderBy(m => m.guestID).ThenBy(m => m.HotelID).ThenBy(m => m.Report_ID).ToList();
            return View();
        }




public List<ReservationReport> Ressetlist(List<ReservationReport> reservationreports, ReservationReport item)
        {
            List<ReservationReport> dispose = reservationreports.Where(x => x.HotelID == item.HotelID && x.guestID == item.guestID).ToList();
            var tempitem = dispose.FirstOrDefault(m => m.Arrive == item.AMDepart);
            if(tempitem!=null)
            {
                reservationreports.Remove(item);
                reservationreports.Remove(tempitem);
                item.AMDepart = tempitem.AMDepart;
                reservationreports.Add(item);
                Ressetlist( reservationreports,item);
            }
            return reservationreports;
        }

Result：

CodePudding user response：

(I'm still searching for the post where I learned about this approach; will refer to it in this post when I find it.)

You could use .Aggregate() with a special accumulator to achieve your desired results.

List<ReservationReport> summary = temp2
    .OrderBy(r => r.Arrive)
    .GroupBy(r => ( r.guestID, r.HotelID ))
    .Select(gr => gr.Skip(1).Aggregate(
        ( Visits: new List<ReservationReport>(), LatestStay: new ReservationReport(gr.First()) ),
        ( guestReport, reservation ) => {
            if (reservation.Arrive.Date == guestReport.LatestStay.AMDepart.Date) 
            {
                guestReport.LatestStay.ExtendWith(reservation);
            }
            else
            {
                guestReport.Visits.Add(guestReport.LatestStay);
                guestReport.LatestStay = new ReservationReport(reservation);
            }
            
            return guestReport;
        },
        guestReport => guestReport.Visits.Concat(new[] { guestReport.LatestStay })))
    .SelectMany(visit => visit)
    .ToList();

First, the reservation reports are ordered chronologically by Arrival. This is necessary to make sure .Aggregate() will work.
Then, the reservation reports are grouped (as OP has suggested) by guestID and HotelID
Now, .Aggregate() is used to combine reservations that are connected to each other for guest X at hotel Y:
- the special accumulator is a named tuple ( Visits, LatestStay ) which is referred to in the code as guestReport
- Visits is a list containing the accumulated visits / consecutive stays of guest X at hotel Y. Initially, this is empty.
- for each reservation made by guest X at hotel Y, its Arrive value is compared to the current accumulated stay (LatestStay)'s AMDepart value; if they match, LatestStay is prolonged.
- at the end, .Aggregate() returns all the separate visits made by guest X at hotel Y (by returning Visits concatenated with LatestStay).

The ReservationReport class is extended as follows for this purpose:

public class ReservationReport
{
    public ReservationReport() { }

    public ReservationReport(ReservationReport existing)
    {
        HotelName = existing.HotelName;
        // Copying all property values...
        HotelID = existing.HotelID;
    }

    // Properties
    
    public void ExtendWith(ReservationReport other)
    {
        AMDepart = other.AMDepart;
        selfpay  = other.selfpay;
        companypay  = other.companypay;
    }
}

Example fiddle here.