CODEWITHSUNDEEP
phplaravel
Toni Rhubanaga
Toni Rhubanaga

Reputation: 43

Laravel get weekday (Monday-Friday) and unset any weekday from date_range

here I have a lot of trouble finding weekday data (Monday - Friday) from date_range and then deleting one of those weekdays.

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

Output this query:

[
    [
        '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
    ],
];

I have output from holidays table;

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

Map

$list = $perUser->map(function ($item) { //this is count day without Saturday + Sunday + holiday $item->active_day = Holiday::whereDate('date', '>=', Carbon::createFromFormat('d-m-Y', $dateSegments[0]))->whereDate('date', '<=', Carbon::createFromFormat('d-m-Y', $dateSegments[1]))->count(); return $item; }); return $list

Output what i want:

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

Upvotes: 0

Views: 1142

Answers (2)

Packiyaraj Patchath
Packiyaraj Patchath

Reputation: 74

Fetch the days to remove from the list,

$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 the list and filter with condition that the days need to be removed

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

Result will be,

[
  10 => 2, // active_day count
]

Upvotes: 0

Valkoff
Valkoff

Reputation: 46

I might suggest to do all of this with sql:

Exclude saturday and sunday directly from the query by adding this where condition:

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

Then you can exclude holidays by adding the following where condition with a subquery:

presence.date NOT IN (SELECT date FROM holidays)

All of this made with Laravel query builder:

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

One last thing, you could use this statement:

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

but I'm not 100% sure it will work as you may expect.

Upvotes: 0

Related Questions