Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
90.53%
153 / 169
93.33%
14 / 15
 CRAP
0.00%
0 / 1
TimePunchRepository
90.53%
153 / 169
93.33%
14 / 15
 54.29
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 findById
100.00%
8 / 8
100.00%
1 / 1
 2
 findByEmployeeAndDateRange
100.00%
10 / 10
100.00%
1 / 1
 2
 findByDateRange
100.00%
9 / 9
100.00%
1 / 1
 2
 findByShiftId
100.00%
8 / 8
100.00%
1 / 1
 2
 getActiveSession
100.00%
8 / 8
100.00%
1 / 1
 2
 isOnBreak
100.00%
6 / 6
100.00%
1 / 1
 2
 create
100.00%
20 / 20
100.00%
1 / 1
 3
 update
100.00%
14 / 14
100.00%
1 / 1
 4
 softDelete
100.00%
7 / 7
100.00%
1 / 1
 1
 getClockedInEmployeeIds
100.00%
7 / 7
100.00%
1 / 1
 2
 getAllClockedInUsers
100.00%
1 / 1
100.00%
1 / 1
 1
 getActiveBreakStartPunch
100.00%
10 / 10
100.00%
1 / 1
 2
 calculateWorkedHours
68.63%
35 / 51
0.00%
0 / 1
 41.79
 findEditedPunches
100.00%
9 / 9
100.00%
1 / 1
 2
1<?php
2
3namespace BuyerKiosk\Scheduling\Repositories;
4
5use BuyerKiosk\Scheduling\Models\TimePunch;
6use DateTime;
7use PDO;
8
9/**
10 * TimePunchRepository
11 *
12 * Data access layer for time punches.
13 * Handles CRUD operations and active session tracking.
14 *
15 * @package BuyerKiosk\Scheduling\Repositories
16 */
17class TimePunchRepository
18{
19    private PDO $db;
20
21    public function __construct(PDO $db)
22    {
23        $this->db = $db;
24    }
25
26    /**
27     * Find a punch by ID
28     *
29     * @param int $punchId Punch ID
30     * @return TimePunch|null
31     */
32    public function findById(int $punchId): ?TimePunch
33    {
34        $stmt = $this->db->prepare("
35            SELECT
36                tp.*,
37                u.firstName AS employeeFirstName,
38                u.lastName AS employeeLastName
39            FROM scheduleTimePunches tp
40            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
41            WHERE tp.punchId = :punchId
42              AND tp.deleted_at IS NULL
43        ");
44        $stmt->bindValue(':punchId', $punchId, PDO::PARAM_INT);
45        $stmt->execute();
46
47        $row = $stmt->fetch(PDO::FETCH_ASSOC);
48        if (!$row) {
49            return null;
50        }
51
52        return TimePunch::fromRow($row);
53    }
54
55    /**
56     * Find punches for an employee within a date range
57     *
58     * @param int $employeeId Employee ID
59     * @param DateTime $start Range start
60     * @param DateTime $end Range end
61     * @return TimePunch[]
62     */
63    public function findByEmployeeAndDateRange(int $employeeId, DateTime $start, DateTime $end): array
64    {
65        $stmt = $this->db->prepare("
66            SELECT
67                tp.*,
68                u.firstName AS employeeFirstName,
69                u.lastName AS employeeLastName
70            FROM scheduleTimePunches tp
71            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
72            WHERE tp.employeeId = :employeeId
73              AND tp.punchTime >= :start
74              AND tp.punchTime < :end
75              AND tp.deleted_at IS NULL
76            ORDER BY tp.punchTime ASC
77        ");
78        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
79        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
80        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
81        $stmt->execute();
82
83        $punches = [];
84        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
85            $punches[] = TimePunch::fromRow($row);
86        }
87
88        return $punches;
89    }
90
91    /**
92     * Find all punches within a date range (all employees)
93     *
94     * @param DateTime $start Range start
95     * @param DateTime $end Range end
96     * @return TimePunch[]
97     */
98    public function findByDateRange(DateTime $start, DateTime $end): array
99    {
100        $stmt = $this->db->prepare("
101            SELECT
102                tp.*,
103                u.firstName AS employeeFirstName,
104                u.lastName AS employeeLastName
105            FROM scheduleTimePunches tp
106            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
107            WHERE tp.punchTime >= :start
108              AND tp.punchTime < :end
109              AND tp.deleted_at IS NULL
110              AND u.enabled = 1
111            ORDER BY tp.punchTime ASC, u.lastName ASC
112        ");
113        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
114        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
115        $stmt->execute();
116
117        $punches = [];
118        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
119            $punches[] = TimePunch::fromRow($row);
120        }
121
122        return $punches;
123    }
124
125    /**
126     * Find punches for a specific shift
127     *
128     * @param int $shiftId Shift ID
129     * @return TimePunch[]
130     */
131    public function findByShiftId(int $shiftId): array
132    {
133        $stmt = $this->db->prepare("
134            SELECT
135                tp.*,
136                u.firstName AS employeeFirstName,
137                u.lastName AS employeeLastName
138            FROM scheduleTimePunches tp
139            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
140            WHERE tp.shiftId = :shiftId
141              AND tp.deleted_at IS NULL
142            ORDER BY tp.punchTime ASC
143        ");
144        $stmt->bindValue(':shiftId', $shiftId, PDO::PARAM_INT);
145        $stmt->execute();
146
147        $punches = [];
148        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
149            $punches[] = TimePunch::fromRow($row);
150        }
151
152        return $punches;
153    }
154
155    /**
156     * Get the currently active session for an employee (clocked in but not out)
157     *
158     * @param int $employeeId Employee ID
159     * @return TimePunch|null The clock-in punch if active session exists
160     */
161    public function getActiveSession(int $employeeId): ?TimePunch
162    {
163        // Find the most recent clock in that doesn't have a corresponding clock out
164        $stmt = $this->db->prepare("
165            SELECT
166                tp.*,
167                u.firstName AS employeeFirstName,
168                u.lastName AS employeeLastName
169            FROM scheduleTimePunches tp
170            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
171            WHERE tp.employeeId = :employeeId
172              AND tp.punchType = 'clockIn'
173              AND tp.deleted_at IS NULL
174              AND NOT EXISTS (
175                  SELECT 1 FROM scheduleTimePunches tp2
176                  WHERE tp2.employeeId = tp.employeeId
177                    AND tp2.punchType = 'clockOut'
178                    AND tp2.punchTime > tp.punchTime
179                    AND tp2.deleted_at IS NULL
180              )
181            ORDER BY tp.punchTime DESC
182            LIMIT 1
183        ");
184        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
185        $stmt->execute();
186
187        $row = $stmt->fetch(PDO::FETCH_ASSOC);
188        if (!$row) {
189            return null;
190        }
191
192        return TimePunch::fromRow($row);
193    }
194
195    /**
196     * Check if employee is currently on break
197     *
198     * @param int $employeeId Employee ID
199     * @return bool True if on break
200     */
201    public function isOnBreak(int $employeeId): bool
202    {
203        $stmt = $this->db->prepare("
204            SELECT punchType
205            FROM scheduleTimePunches
206            WHERE employeeId = :employeeId
207              AND deleted_at IS NULL
208            ORDER BY punchTime DESC
209            LIMIT 1
210        ");
211        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
212        $stmt->execute();
213
214        $row = $stmt->fetch(PDO::FETCH_ASSOC);
215        return $row && $row['punchType'] === TimePunch::TYPE_BREAK_START;
216    }
217
218    /**
219     * Create a new time punch
220     *
221     * @param TimePunch $punch Punch to create
222     * @return TimePunch Created punch with ID
223     */
224    public function create(TimePunch $punch): TimePunch
225    {
226        $data = $punch->toDbArray();
227
228        $stmt = $this->db->prepare("
229            INSERT INTO scheduleTimePunches (
230                employeeId, shiftId, punchType, punchTime, breakType,
231                isManualEntry, manualEntryNote, isUnscheduled, isManagerOverride,
232                approvedByUserId, approved_at, approvalNote, enteredByUserId
233            ) VALUES (
234                :employeeId, :shiftId, :punchType, :punchTime, :breakType,
235                :isManualEntry, :manualEntryNote, :isUnscheduled, :isManagerOverride,
236                :approvedByUserId, :approved_at, :approvalNote, :enteredByUserId
237            )
238        ");
239
240        $stmt->bindValue(':employeeId', $data['employeeId'], PDO::PARAM_INT);
241        $stmt->bindValue(':shiftId', $data['shiftId'], $data['shiftId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
242        $stmt->bindValue(':punchType', $data['punchType']);
243        $stmt->bindValue(':punchTime', $data['punchTime']);
244        $stmt->bindValue(':breakType', $data['breakType']);
245        $stmt->bindValue(':isManualEntry', $data['isManualEntry'], PDO::PARAM_INT);
246        $stmt->bindValue(':manualEntryNote', $data['manualEntryNote']);
247        $stmt->bindValue(':isUnscheduled', $data['isUnscheduled'], PDO::PARAM_INT);
248        $stmt->bindValue(':isManagerOverride', $data['isManagerOverride'], PDO::PARAM_INT);
249        $stmt->bindValue(':approvedByUserId', $data['approvedByUserId'], $data['approvedByUserId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
250        $stmt->bindValue(':approved_at', $data['approved_at']);
251        $stmt->bindValue(':approvalNote', $data['approvalNote']);
252        $stmt->bindValue(':enteredByUserId', $data['enteredByUserId'], PDO::PARAM_INT);
253
254        $stmt->execute();
255
256        $punchId = (int)$this->db->lastInsertId();
257        $punch->setPunchId($punchId);
258
259        return $this->findById($punchId) ?? $punch;
260    }
261
262    /**
263     * Update an existing punch (with edit tracking)
264     *
265     * @param TimePunch $punch Punch with edits applied
266     * @return TimePunch Updated punch
267     */
268    public function update(TimePunch $punch): TimePunch
269    {
270        if ($punch->getPunchId() === null) {
271            throw new \InvalidArgumentException('Cannot update punch without punchId');
272        }
273
274        $data = $punch->toDbArray();
275
276        $stmt = $this->db->prepare("
277            UPDATE scheduleTimePunches
278            SET punchTime = :punchTime,
279                shiftId = :shiftId,
280                breakType = :breakType,
281                edited_at = :edited_at,
282                editedByUserId = :editedByUserId,
283                editNote = :editNote
284            WHERE punchId = :punchId
285              AND deleted_at IS NULL
286        ");
287
288        $stmt->bindValue(':punchId', $punch->getPunchId(), PDO::PARAM_INT);
289        $stmt->bindValue(':punchTime', $data['punchTime']);
290        $stmt->bindValue(':shiftId', $data['shiftId'], $data['shiftId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
291        $stmt->bindValue(':breakType', $data['breakType']);
292        $stmt->bindValue(':edited_at', $data['edited_at']);
293        $stmt->bindValue(':editedByUserId', $data['editedByUserId'], $data['editedByUserId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
294        $stmt->bindValue(':editNote', $data['editNote']);
295
296        $stmt->execute();
297
298        return $this->findById($punch->getPunchId()) ?? $punch;
299    }
300
301    /**
302     * Soft delete a punch (mark as deleted with note)
303     *
304     * Punches are soft-deleted via the `deleted_at`/`deletedByUserId` columns.
305     * The record remains for audit and payroll history.
306     *
307     * @param int $punchId Punch ID
308     * @param int $deletedByUserId User who deleted
309     * @param string $note Deletion reason
310     * @return bool Success status
311     */
312    public function softDelete(int $punchId, int $deletedByUserId, string $note): bool
313    {
314        $stmt = $this->db->prepare("
315            UPDATE scheduleTimePunches
316            SET deleted_at = NOW(),
317                deletedByUserId = :deletedByUserId,
318                edited_at = NOW(),
319                editedByUserId = :deletedByUserId,
320                editNote = :note
321            WHERE punchId = :punchId
322              AND deleted_at IS NULL
323        ");
324        $stmt->bindValue(':punchId', $punchId, PDO::PARAM_INT);
325        $stmt->bindValue(':deletedByUserId', $deletedByUserId, PDO::PARAM_INT);
326        $stmt->bindValue(':note', 'DELETED: ' . $note);
327
328        $stmt->execute();
329
330        return $stmt->rowCount() > 0;
331    }
332
333    /**
334     * Get all employees currently clocked in
335     *
336     * @return array<int> Array of employee IDs
337     */
338    public function getClockedInEmployeeIds(): array
339    {
340        $stmt = $this->db->prepare("
341            SELECT DISTINCT tp1.employeeId
342            FROM scheduleTimePunches tp1
343            WHERE tp1.punchType = 'clockIn'
344              AND tp1.deleted_at IS NULL
345              AND NOT EXISTS (
346                  SELECT 1 FROM scheduleTimePunches tp2
347                  WHERE tp2.employeeId = tp1.employeeId
348                    AND tp2.punchType = 'clockOut'
349                    AND tp2.punchTime > tp1.punchTime
350                    AND tp2.deleted_at IS NULL
351              )
352        ");
353        $stmt->execute();
354
355        $ids = [];
356        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
357            $ids[] = (int)$row['employeeId'];
358        }
359
360        return $ids;
361    }
362
363    /**
364     * Get all users (by employeeId/userId) currently clocked in
365     *
366     * Alias for getClockedInEmployeeIds for semantic clarity in controller code.
367     *
368     * @return array<int> Array of user IDs (employeeId in scheduleTimePunches)
369     */
370    public function getAllClockedInUsers(): array
371    {
372        return $this->getClockedInEmployeeIds();
373    }
374
375    /**
376     * Get the active break start punch for an employee
377     *
378     * Returns the most recent break_start punch that hasn't been ended,
379     * used to track break duration and status.
380     *
381     * @param int $employeeId Employee/User ID
382     * @return TimePunch|null The break start punch if on break, null otherwise
383     */
384    public function getActiveBreakStartPunch(int $employeeId): ?TimePunch
385    {
386        // Find the most recent break start that doesn't have a corresponding break end
387        $stmt = $this->db->prepare("
388            SELECT
389                tp.*,
390                u.firstName AS employeeFirstName,
391                u.lastName AS employeeLastName
392            FROM scheduleTimePunches tp
393            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
394            WHERE tp.employeeId = :employeeId
395              AND tp.punchType = :breakStartType
396              AND tp.deleted_at IS NULL
397              AND NOT EXISTS (
398                  SELECT 1 FROM scheduleTimePunches tp2
399                  WHERE tp2.employeeId = tp.employeeId
400                    AND tp2.punchType = :breakEndType
401                    AND tp2.punchTime > tp.punchTime
402                    AND tp2.deleted_at IS NULL
403              )
404            ORDER BY tp.punchTime DESC
405            LIMIT 1
406        ");
407        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
408        $stmt->bindValue(':breakStartType', TimePunch::TYPE_BREAK_START);
409        $stmt->bindValue(':breakEndType', TimePunch::TYPE_BREAK_END);
410        $stmt->execute();
411
412        $row = $stmt->fetch(PDO::FETCH_ASSOC);
413        if (!$row) {
414            return null;
415        }
416
417        return TimePunch::fromRow($row);
418    }
419
420    /**
421     * Calculate worked hours from punches for an employee in a date range
422     *
423     * @param int $employeeId Employee ID
424     * @param DateTime $start Range start
425     * @param DateTime $end Range end
426     * @param bool $excludeUnpaidBreaks Whether to subtract unpaid break time
427     * @return float Total worked hours
428     */
429    public function calculateWorkedHours(
430        int $employeeId,
431        DateTime $start,
432        DateTime $end,
433        bool $excludeUnpaidBreaks = true,
434        bool $closeOpenSessionAtRangeEnd = false
435    ): float {
436        $punches = $this->findByEmployeeAndDateRange($employeeId, $start, $end);
437
438        $totalSeconds = 0;
439        $clockInTime = null;
440        $unpaidBreakStartTime = null;
441        $unpaidBreakSeconds = 0;
442
443        foreach ($punches as $punch) {
444            switch ($punch->getPunchType()) {
445                case TimePunch::TYPE_CLOCK_IN:
446                    $clockInTime = $punch->getPunchTime();
447                    $unpaidBreakStartTime = null;
448                    $unpaidBreakSeconds = 0;
449                    break;
450
451                case TimePunch::TYPE_CLOCK_OUT:
452                    if ($clockInTime !== null) {
453                        $clockOutTime = $punch->getPunchTime();
454
455                        if ($excludeUnpaidBreaks && $unpaidBreakStartTime !== null) {
456                            $unpaidBreakSeconds += max(
457                                0,
458                                $clockOutTime->getTimestamp() - $unpaidBreakStartTime->getTimestamp()
459                            );
460                            $unpaidBreakStartTime = null;
461                        }
462
463                        $sessionSeconds = max(0, $clockOutTime->getTimestamp() - $clockInTime->getTimestamp());
464                        $workedSeconds = $excludeUnpaidBreaks ? max(0, $sessionSeconds - $unpaidBreakSeconds) : $sessionSeconds;
465                        $totalSeconds += $workedSeconds;
466                        $clockInTime = null;
467                        $unpaidBreakStartTime = null;
468                        $unpaidBreakSeconds = 0;
469                    }
470                    break;
471
472                case TimePunch::TYPE_BREAK_START:
473                    if (
474                        $clockInTime !== null
475                        && $excludeUnpaidBreaks
476                        && $punch->getBreakType() === TimePunch::BREAK_TYPE_UNPAID
477                        && $unpaidBreakStartTime === null
478                    ) {
479                        $unpaidBreakStartTime = $punch->getPunchTime();
480                    }
481                    break;
482
483                case TimePunch::TYPE_BREAK_END:
484                    if ($clockInTime !== null && $excludeUnpaidBreaks && $unpaidBreakStartTime !== null) {
485                        $unpaidBreakSeconds += max(
486                            0,
487                            $punch->getPunchTime()->getTimestamp() - $unpaidBreakStartTime->getTimestamp()
488                        );
489                        $unpaidBreakStartTime = null;
490                    }
491                    break;
492            }
493        }
494
495        if ($closeOpenSessionAtRangeEnd && $clockInTime !== null) {
496            $nowUtc = new DateTime('now', new \DateTimeZone('UTC'));
497            $rangeEnd = $end < $nowUtc ? $end : $nowUtc;
498
499            if ($rangeEnd > $clockInTime) {
500                if ($excludeUnpaidBreaks && $unpaidBreakStartTime !== null) {
501                    $unpaidBreakSeconds += max(
502                        0,
503                        $rangeEnd->getTimestamp() - $unpaidBreakStartTime->getTimestamp()
504                    );
505                }
506
507                $sessionSeconds = $rangeEnd->getTimestamp() - $clockInTime->getTimestamp();
508                $workedSeconds = $excludeUnpaidBreaks ? max(0, $sessionSeconds - $unpaidBreakSeconds) : max(0, $sessionSeconds);
509                $totalSeconds += $workedSeconds;
510            }
511        }
512
513        return $totalSeconds / 3600;
514    }
515
516    /**
517     * Get punches that have been edited (for audit display)
518     *
519     * @param DateTime $start Range start
520     * @param DateTime $end Range end
521     * @return TimePunch[]
522     */
523    public function findEditedPunches(DateTime $start, DateTime $end): array
524    {
525        $stmt = $this->db->prepare("
526            SELECT
527                tp.*,
528                u.firstName AS employeeFirstName,
529                u.lastName AS employeeLastName
530            FROM scheduleTimePunches tp
531            INNER JOIN kiosk_users.users u ON tp.employeeId = u.id
532            WHERE tp.edited_at IS NOT NULL
533              AND tp.punchTime >= :start
534              AND tp.punchTime < :end
535              AND tp.deleted_at IS NULL
536            ORDER BY tp.edited_at DESC
537        ");
538        $stmt->bindValue(':start', $start->format('Y-m-d H:i:s'));
539        $stmt->bindValue(':end', $end->format('Y-m-d H:i:s'));
540        $stmt->execute();
541
542        $punches = [];
543        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
544            $punches[] = TimePunch::fromRow($row);
545        }
546
547        return $punches;
548    }
549}