Laravel获取工作日(星期一到星期五)，并从date_range中取消设置任何工作日

Question

在这里我有很多麻烦从date_range找到工作日的数据(星期一到星期五)，然后删除其中一个工作日。

$dateRange = $request->date_range; //25-12-2023 - 31-12-2023
$query = Presence::orderBy('start_time', 'DESC')
$dateSegments = explode(' - ', $dateRange);
if (count($dateSegments) == 2) {
$query->whereDate('date', '>=', Carbon::createFromFormat('d-m-Y', $dateSegments[0]));
$query->whereDate('date', '<=', Carbon::createFromFormat('d-m-Y', $dateSegments[1]));
}
$query->join('users', 'presences.user_id', 'users.id')
->groupBy('user_id')
->select(
'presences.user_id',
'users.name as user_name',
);
$perUser = $query->get();

输出这个查询:

[
[
'id' => 1,
'user_id' => 10
'date' => '2023-12-25' //Monday
],
[
'id' => 2,
'name' => 10
'date' => '2023-12-26' //Tuesday
],
[
'id' => 3,
'name' => 10
'date' => '2023-12-27' //Wednesday
],
[
'id' => 4,
'name' => 10
'date' => '2023-12-28' //Thursday
],
[
'id' => 5,
'name' => 10
'date' => '2023-12-31' //Sunday
],
];

我有节假日表的输出;

[
[
'id' => 1,
'name' => 'Christmas',
'date' => '2023-12-25' //Monday
],
[
'id' => 1,
'name' => 'Independent Day',
'date' => '2023-12-27' //Wednesday
],
];

地图$list = $perUser->map(function ($item) {//这是没有星期六+星期日+假日的计数日项→美元active_day =假日:whereDate("日期"、"祝辞=",碳::createFromFormat (d-m-Y, dateSegments美元[0]))→whereDate(‘日期’,‘& lt; =’,碳::createFromFormat (d-m-Y, dateSegments美元[1]))→count ();返回$项目;}(;返回列表$

输出我想要的:

[
{
"user_id": 10,
"user_name": "Brian",
"active_day": 2 //this is count day without Saturday + Sunday + holiday
}
]

Answer 1

我建议使用sql:

完成所有这些操作通过添加where条件直接从查询中排除星期六和星期日:

DAYOFWEEK(presence.date) IN (1, 7);

然后可以通过在子查询中添加以下where条件来排除假日:

presence.date NOT IN (SELECT date FROM holidays)

所有这些都是用Laravel查询生成器完成的:

->whereRaw('DAYOFWEEK(date) NOT IN (1,7)')
->whereNotIn('date', function ($query) {
$query->select('date')->from('holidays');
})

最后一件事，你可以用这个语句:

->whereBetween('date', [$dateSegments[0], $dateSegments[1]])

但我不能100%肯定它会像你期望的那样起作用。

Answer 2

获取要从列表中删除的日期，

$daysToRemove = $holiday->where('date', '>=', now()->parse($dateSegments[0])->format('d-m-Y'))->where('date', '<=', now()->parse($dateSegments[1])->format('d-m-Y'))->map(function($item){
return now()->parse($item['date'])->format('l');
})->merge(['Saturday','Sunday'])->toArray();

将列表和过滤器映射为需要删除的天数

$activedays = $perUser->map(function ($item) use ($daysToRemove) {
return (!in_array(now()->parse($item['date'])->format('l'), $daysToRemove)) ? $item : null;
})->filter()->countBy('name');

结果将是，

[
10 => 2, // active_day count
]