Laravel 7: Find days and dates between two dates with condition

Question

When the user submits a leave application, needs to select the start and end date. But before saving it needs to check is there any holiday or weekly holiday in between two dates. If a holiday or weekly holiday matches then the total number of holidays will be deducted from the total days.

Weekly Holiday:

id	day	working_status
1	Fri	1

Holiday Table: (Model Name: Holiday)

id	date	publication_status
1	2022-05-26	1

Leave Table:

id	start_date	end_date	total_days
1	2022-05-25	2022-05-28	2

Controller:

$leave = new User;
  $leave->start_date = $request->start_date;
  $leave->end_date = $request->end_date;
  
  //get and convert day name for weekly holiday compare like Fri, Sat etc.
  $start_day = date("D", strtotime($request->start_date));
  $end_day = date("D", strtotime($request->end_date));

  // get and convert date for monthly holiday compare
  $start = strtotime($request->start_date);
  $end = strtotime($request->end_date);
  
  $diff = $end - $start;
  $diff_in_days = floor($diff/(60*60*24)) + 1;
  // Suppose  Fri is holiday now we have to count how many Fri day in between start and end date. 
  here need help
   $weekly_holidays = WorkingDay::where('working_status', 1)
         ->get(['day'])->count();

    // we have to count how many Monthly holiday in between start and end date. here need help
   $monthly_holidays= Holiday::where('publication_status', 1)->get(['date'])->count();
    
   $total_days = $diff_in_days - ($weekly_holidays + $monthly_holidays);
       if($request->halfday == 1){
                $leave->total_days = 0.5;
            }
            else{
                $leave->total_days = $total_days;
            }

Example:

 WorkingDay = day = Fri
 Holiday = date = 2022-05-26
 start_date = 2022-05-25
 end_date = 2022-05-28
 total_days = 2 
 // from 25 to 28, the total days are 4, but 26 is a holiday, and 27 is Friday. Holidays can be multiple dates. If there is multiple holidays between the start and end date, it will calculate according to this. 

Answer 1

It's actually easier. You don't need to complicate it. Carbon comes to the rescue here.

$startDate = Carbon::parse('start date here');
$endDate = Carbon::parse('date string here');

$weekdays = $startDate->diffInWeekdays($endDate);

This will give you the day difference in weekdays excluding any weekends.

Now whichever database you use, you would have a function to get the day of the week in number where 0 stands for Sunday and 6 for Saturday. For Postgres, I will give you an example

weekdays_holiday_count = select count(*) from holidays where date_part('dow', date) not in (0, 6) # 0 for Sunday and 6 for Saturday

This query is counting holidays which came on weekdays. I give you the raw query here. however, the point here is to under the magic of the function.

Now you can do the math easily

$weekdays - $weekdays_holiday_count

Make sure to import Carbon\Carbon at the top.