Get Specific Range Record from Checkin/Checkout Dates

Question

We are developing booking software, and the situation is as follows:

There are 2 room pricings dependent on different dates. Each room has separate pricing for weekdays and weekends.

ROOM 1

Pricing 1: 1st sept - 3rd Nov, Weekday: $100, Weekend: $120 Pricing 2: 4th Nov - 29 Dec. Weekday: $110, Weekend: $130 Pricing 3: 30th Dec - 20 Jan, Weekday: $100, Weekend: $115 Now there is a booking Checkin Checkout date.

For example: Checkin: 1st Nov, Checkout: 10th Nov

Now the answers should be something like:

First System automatically pick the slots or ranges it comes in Calculate the days coming in that specific range, for example how many weekdays and weekends? Then multiply those weekdays and weekends with the particular pricing they have. And in result we achieved a List with Weekday and Weekend Rates as a list to show in HTML SELECT on client end.

This can be done in SQL Stored Procedure or ASP.NET CORE C#, help me to get this resolve. I tried a lot of methods but failed. Need assistance from the experts here.

Ruikai Feng · Answer

Assuming you could pass the order model to backend and get HotelPrice from database successfully, I tried as below：

public class Order
    {
        public int Id { get; set; }
        public DateTime CheckinTime { get; set; }
        public DateTime CheckoutTime { get; set; }
    }
    
    public class HotelPrice
    {
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
        public decimal WeekDayPrice { get; set; }
        public decimal WeekendPrice { get; set; }
    }

codes in service：

var order = new Order() { Id = 1, CheckinTime = new DateTime(2022, 10, 1), CheckoutTime = new DateTime(2022, 10, 10) };
            var hotelpricelist = new List()
            {
                new HotelPrice(){StartDate=new DateTime(2022, 9, 1),EndDate=new DateTime(2022, 10, 3),WeekDayPrice=100,WeekendPrice=120},
                new HotelPrice(){StartDate=new DateTime(2022, 10, 4),EndDate=new DateTime(2022, 10, 6),WeekDayPrice=110,WeekendPrice=130},
                new HotelPrice(){StartDate=new DateTime(2022, 10, 7),EndDate=new DateTime(2022, 12, 6),WeekDayPrice=140,WeekendPrice=160}
            };
            decimal totalweekdayprice=0;
            decimal totalweekendprice = 0;
            var weekdaylist = new List() { };
            var weekendlist = new List() { };
            var datearry = new int[] { 0, 6 };
            for (DateTime dt = order.CheckinTime; dt.CompareTo(order.CheckoutTime)!=1; dt=dt.AddDays(1))
            {
                var pricetemp = hotelpricelist.FirstOrDefault(x => x.StartDate.CompareTo(dt) != 1 && x.EndDate.CompareTo(dt) != -1);
                
                if (Array.IndexOf(datearry, (int)dt.DayOfWeek) == -1)
                {
                    totalweekdayprice += pricetemp.WeekDayPrice;
                    weekdaylist.Add(dt);
                }
                else 
                {
                    totalweekendprice += pricetemp.WeekendPrice;
                    weekendlist.Add(dt);
                }
                
            }

The Result：

And here's the document about EFCore and MVC,hope could help

Get Specific Range Record from Checkin/Checkout Dates

1 Answers