Laravel获取工作日(Monday-Friday)并从date_range中取消设置任何工作日

41zrol4v  于 2023-03-31  发布在  其他
关注(0)|答案(2)|浏览(180)

在这里,我很难从date_range中找到工作日数据(Monday - Friday),然后删除其中一个工作日。

$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
        ],
    ];

Map
$list = $perUser-〉map(函数($item){ //这是不包括星期六+星期日+假日的计数日$item-〉active_day =假日::whereDate('日期','〉=',复写::创建自格式('d-m-Y',$日期段[0]))-〉whereDate('日期','〈=',复写::创建自格式('d-m-Y',$日期段[1]))-〉count();返回$项目;}); return $list
输出我想要的:

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

myzjeezk1#

我可能会建议用SQL来做这一切:
通过添加以下where条件,直接从查询中排除星期六和星期日:

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

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

presence.date NOT IN (SELECT date FROM holidays)

所有这些都是用Laravel query builder完成的:

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

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

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

但我不能百分之百肯定它会像你期望的那样有效

omjgkv6w

omjgkv6w2#

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

$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();

Map列表并过滤需要删除的日期

$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
]

相关问题