Laravel 7: Calculate days between two dates with condition

I'm stuck in a complex calculation. I want to calculate days between two days. But it needs to check date column from other two tables that in between two dates the date is exist or not. The concept is: While the user submitting a leave application needs to select the start and end date. And the days between two days will save in the leaves table. But before saving it needs to check is there any holiday or weekly holiday in between two dates. If holiday or weekly holiday match then the total number of holiday will be deducted from total days.

Weekly Holiday: (Model Name: WorkingDay)

Holiday Table: (Model Name: Holiday)

Leave Table:

Controller:

   $leave = new Leave;
   $leave->start_date = $request->start_date;
   $leave->end_date = $request->end_date;

   $start_day = date("D", strtotime($request->start_date));
   $end_day = date("D", strtotime($request->end_date));

   $start = Carbon::parse($request->start_date);
   $end = Carbon::parse($request->end_date);
   $total_days = $end->diffInDays($start);

   
   $weekly_holidays = WorkingDay::where('working_status', 0)
   ->get(['day']);

   $monthly_holidays= Holiday::where('publication_status', 1)->get(['date']);
    $total_days = //need solution here

   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, total days is 4, but 26 is a holiday, and 27 is friday. Holiday can be multiple dates. If there is multiple holidays between start and end date, it will calculate according to this. 

I'm not sure how to write code for validating these conditions. Please help me.

I have tried to inspect exactly what you want and made this answer for you. Hope it's what you are trying to explain

Table: working_days

Controller:

public function insertLeaves(Request $request) {

    $leave = new Leave;
    $leave->start_date = $request->start_date;
    $leave->end_date = $request->end_date;

    $start_day = date("D", strtotime($request->start_date));
    $end_day = date("D", strtotime($request->end_date));

    $start = Carbon::parse($request->start_date);
    $end = Carbon::parse($request->end_date);
    $total_days = $end->diffInDays($start);

    // Gets the working days in comma seperated without the key from the database [Example: Sat, Sun, Mon, Tue, Wed, Thu] in array
    $working_days = WorkingDay::where('working_status', 0)
    ->get(['day'])->map(function($day) {
        return $day->day;
    })->toArray();

    // Gets the holidays in comma seperated dates without the key from the database [Example: 2022-05-26, 2022-05-28] in array
    $holidays= Holiday::where('publication_status', 1)->get(['date'])->map(function($date) {
        return date('Y-m-d', strtotime($date->date));
    })->toArray();

    // Get the weekend holidays we get between the start date and end date by the helper function we created 
    $weekend_holidays = $this->sumHolidays($working_days, $request->start_date , $total_days, 'weekends');

    // Get the holidays if have any between the start date and end date by the helper function we created 
    $monthly_holidays = $this->sumHolidays($holidays, $request->start_date , $total_days, 'holidays');
    
    $total_leaves = $total_days - $weekend_holidays - $monthly_holidays + 1; //need solution here;

    if($request->halfday == 1){
        $leave->total_days = 0.5;
    }
    else{
        $leave->total_days = $total_leaves;
    }
    $leave->save();

}

function sumHolidays($days, $start_date, $diff, $type) {
    $total_days = 0;
    $i = 0;
    while ($i <= $diff) {
        $tsDate = strtotime($start_date . ' ' .'+ '.$i.' days');
        if($type == 'weekends') {
            $day = date('D', $tsDate);
            if(!in_array($day, $days)) {
                $total_days++;
            }
        } elseif($type == 'holidays') {
            $date = date('Y-m-d', $tsDate);
            if(in_array($date, $days)) {
                $total_days++;
            }
        }
        $i++;
    }
    return $total_days;
}

Outputs Example:

Start Date = 2022-05-26
End Date = 2022-05-28
Working Days = day = ["Sat","Sun","Mon","Tue","Wed","Thu"]
Holidays = date = ["2022-05-26","2022-05-29"]
Weekend Holidays = 1
Monthly Holidays = 1
Total Days = 4
Total Leaves = 2 //As there is 1 Friday and 1 Holiday at 2022-05-26