Help with a loop?

Hi all,

I'm trying to build a system that will allow our employees to clock in, lunch, out, etc.

I'm at the part that will allow an admin to download a CSV export of all user hours worked within a specific time frame they set in the form.

There's three users in the database currently for help testing. ID 3 has no records, ID 2 has records on one day, and ID 1 has a lot of records for two days.

What should happen is that this looks for an ID in the request, if it's not there then it'll push to the loop to run through and generate the rows for each user. However, when I do this it misses any records from the second user, who should have one days worth of records.

If I hardcode their ID in, fine no trouble it works, but if I let the loop handle it, nope their records just vanish and it just creates a CSV file containing only data for the user with ID 1.

Help, guidance anything appreciated. Really annoyed as I've not been using version control and I had a working version. Could it be caching of some sort? Am I mixing up my logic?

public function hoursWorked(Request $request) { $request->validate([ 'before' => ['required', 'date', 'after_or_equal:after', 'before:today'], 'after' => ['required', 'date', 'before_or_equal:before', 'before:today'] ]); $ids = array(); $before = $request->input('before'); $after = $request->input('after'); $period = CarbonPeriod::create($after, $before); $paid = MovementTypes::where('paid', '=', 1)->pluck('id'); if ($request->has('ids')) { $id = $request->input('ids'); $name = User::where('id', $id)->pluck('name')->first(); $rows = $this->getSingleHours($id, $name, $period, $paid); return $this->makeSheet($name, $after, $before, $rows); } else { $ids = Staff::latest()->pluck('id'); foreach($ids as $id){ $name = User::where('id', $id)->pluck('name')->first(); $rows = $this->getSingleHours($id, $name, $period, $paid); } return $this->makeSheet('All employee', $after, $before, $rows); } }

public function getSingleHours($id, $name, $period, $paid){
    $after = $period->startDate->format('Y-m-d');
    $before = $period->endDate->format('Y-m-d');
    $totalPaidRecs = Movements::where('staff_id', '=', $id)
        ->whereIn('movement_types_id', $paid)
        ->whereDate('created_at', '>=', $after)
        ->whereDate('created_at', '<=', $before)
        ->sum('hours');
    $totalPaidHours = CarbonInterval::seconds($totalPaidRecs)
        ->cascade()
        ->format('%H hours %I minutes');

    foreach($period as $day){
        $hasMovements = Movements::where('staff_id', '=', 2)
            ->whereDate('created_at', '=', $day)
            ->count();
        if($hasMovements){
            $daysWithMovements[] = $day;
        }
    }
    
    if($hasMovements > 0){
        foreach($daysWithMovements as $safe) {
            $clockIn = Movements::first()
                ->whereDate('created_at', '=', $safe)
                ->where('movement_types_id', '1')
                ->where('staff_id', '=', $id);

            $clockOut = Movements::first()
                ->whereDate('created_at', '=', $safe)
                ->where('movement_types_id', '2')
                ->where('staff_id', '=', $id)
                ->latest()
                ->value('created_at')
                ->format('H:i:s');

            $seconds = Movements::whereDate('created_at', '=', $safe)
                ->where('staff_id', '=', $id)
                ->whereIn('movement_types_id', $paid)
                ->sum('hours');

            $rows[] = [
                'date' => $safe->format('d/m/Y'),
                'name' => $name,
                'in' => $clockIn
                    ->value('created_at')
                    ->format('H:i:s'),
                'out' => $clockOut,
                'hours' => CarbonInterval::seconds($seconds)->cascade()->format('%H hours %I minutes'),
                'late' => $clockIn->value('late') == 0 ? 'No' : 'Yes'
            ];
        }
    } else {
        $rows = array();
    }

        return $rows;
}

LaryAI

2 years ago

Level 58

It seems like there are a couple of issues in the code provided. One of the main issues is that the $rows variable is being overwritten in each iteration of the loop, which means only the last user's data will be included in the CSV file. To fix this, you should initialize $rows as an array before the loop and then append to it within the loop.

Additionally, there's a hardcoded value Movements::where('staff_id', '=', 2) in the getSingleHours method, which should be using the $id variable instead.

Here's the corrected code:

public function hoursWorked(Request $request)
{
    $request->validate([
        'before' => ['required', 'date', 'after_or_equal:after', 'before:today'],
        'after' => ['required', 'date', 'before_or_equal:before', 'before:today']
    ]);

    $before = $request->input('before');
    $after = $request->input('after');
    $period = CarbonPeriod::create($after, $before);
    $paid = MovementTypes::where('paid', '=', 1)->pluck('id');
    $rows = []; // Initialize rows as an array

    if ($request->has('ids')) {
        $id = $request->input('ids');
        $name = User::where('id', $id)->pluck('name')->first();
        $rows = $this->getSingleHours($id, $name, $period, $paid);
        return $this->makeSheet($name, $after, $before, $rows);
    } else {
        $ids = Staff::latest()->pluck('id');
        foreach ($ids as $id) {
            $name = User::where('id', $id)->pluck('name')->first();
            $userRows = $this->getSingleHours($id, $name, $period, $paid);
            $rows = array_merge($rows, $userRows); // Merge the user rows into the main rows array
        }
        return $this->makeSheet('All employee', $after, $before, $rows);
    }
}

public function getSingleHours($id, $name, $period, $paid)
{
    $after = $period->startDate->format('Y-m-d');
    $before = $period->endDate->format('Y-m-d');
    $totalPaidRecs = Movements::where('staff_id', '=', $id)
        ->whereIn('movement_types_id', $paid)
        ->whereDate('created_at', '>=', $after)
        ->whereDate('created_at', '<=', $before)
        ->sum('hours');
    $totalPaidHours = CarbonInterval::seconds($totalPaidRecs)->cascade()->format('%H hours %I minutes');

    $daysWithMovements = [];
    foreach ($period as $day) {
        $hasMovements = Movements::where('staff_id', '=', $id) // Use the $id variable instead of hardcoded value
            ->whereDate('created_at', '=', $day)
            ->count();
        if ($hasMovements) {
            $daysWithMovements[] = $day;
        }
    }

    $rows = []; // Initialize rows as an array
    foreach ($daysWithMovements as $safe) {
        $clockInQuery = Movements::whereDate('created_at', '=', $safe)
            ->where('movement_types_id', '1')
            ->where('staff_id', '=', $id);

        $clockIn = $clockInQuery->first();
        $clockOut = Movements::whereDate('created_at', '=', $safe)
            ->where('movement_types_id', '2')
            ->where('staff_id', '=', $id)
            ->latest()
            ->first();

        $seconds = Movements::whereDate('created_at', '=', $safe)
            ->where('staff_id', '=', $id)
            ->whereIn('movement_types_id', $paid)
            ->sum('hours');

        $rows[] = [
            'date' => $safe->format('d/m/Y'),
            'name' => $name,
            'in' => optional($clockIn)->created_at->format('H:i:s'),
            'out' => optional($clockOut)->created_at->format('H:i:s'),
            'hours' => CarbonInterval::seconds($seconds)->cascade()->format('%H hours %I minutes'),
            'late' => optional($clockIn)->late == 0 ? 'No' : 'Yes'
        ];
    }

    return $rows;
}

In this solution, I've made the following changes:

Initialized $rows as an array before the loop in the hoursWorked method.
Used array_merge to combine the rows from each user.
Corrected the hardcoded staff ID in the getSingleHours method to use the $id variable.
Used the optional helper function to avoid errors when $clockIn or $clockOut might be null.

Make sure to test this code thoroughly to ensure it works as expected with your application's logic and data.

2 likes

BusinessAlch

2 years ago

Level 3

@LaryAI You beautiful bot you!! That's the solution eeeesh!

Snapey

2 years ago

Level 122

@BusinessAlch please mark it answered, and next time, format the code in your question so that humans can answer

Please or to participate in this conversation.