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)
| 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 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.
CodePudding user response：
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
CodePudding user response：
Welcome to StackOverflow community
You have to get a count of working days, holidays, and leave days.
Try this code
// I assume you are getting start_date and end_date in $request.
$request->start_date = '2022-05-25';
$request->end_date = '2022-05-28';
$leave = new User;
$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));
// this is change
$start = strtotime($request->start_date);
$end = strtotime($request->end_date);
$diff = $end - $start;
// This will calculate days between two days including start date and end date
$diff_in_days = floor($diff/(60*60*24)) 1;
// assuming Fri is holiday and get count of it
$weekly_holidays = WorkingDay::where('working_status', 1)
->get(['day'])->count();
// Get count of monthly holidays
$monthly_holidays= Holiday::where('publication_status', 1)->get(['date'])->count();
// your result will be 4
$total_days = $diff_in_days - $weekly_holidays - $monthly_holidays;
if($request->halfday == 1){
$leave->total_days = 0.5;
}
else{
$leave->total_days = $total_days;
}
Hope this help!!