Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 143
0.00%
0 / 15
 CRAP
0.00%
0 / 1
TimesheetRepository
0.00%
0 / 143
0.00%
0 / 15
 930
0.00%
0 / 1
 __construct
0.00%
0 / 1
0.00%
0 / 1
 2
 findById
0.00%
0 / 8
0.00%
0 / 1
 6
 findByWeek
0.00%
0 / 8
0.00%
0 / 1
 6
 findByEmployeeAndWeek
0.00%
0 / 9
0.00%
0 / 1
 6
 findByStatus
0.00%
0 / 14
0.00%
0 / 1
 20
 findWithOvertime
0.00%
0 / 8
0.00%
0 / 1
 6
 updateOrCreate
0.00%
0 / 8
0.00%
0 / 1
 6
 create
0.00%
0 / 23
0.00%
0 / 1
 6
 update
0.00%
0 / 21
0.00%
0 / 1
 12
 approve
0.00%
0 / 5
0.00%
0 / 1
 6
 approveMultiple
0.00%
0 / 9
0.00%
0 / 1
 6
 unlock
0.00%
0 / 5
0.00%
0 / 1
 6
 markExported
0.00%
0 / 8
0.00%
0 / 1
 6
 getWeekSummary
0.00%
0 / 15
0.00%
0 / 1
 2
 findReadyForExport
0.00%
0 / 1
0.00%
0 / 1
 2
1<?php
2
3namespace BuyerKiosk\Scheduling\Repositories;
4
5use BuyerKiosk\Scheduling\Models\Timesheet;
6use DateTime;
7use PDO;
8
9/**
10 * TimesheetRepository
11 *
12 * Data access layer for weekly timesheets.
13 * Handles CRUD operations, approval workflow, and export tracking.
14 *
15 * @package BuyerKiosk\Scheduling\Repositories
16 */
17class TimesheetRepository
18{
19    private PDO $db;
20
21    public function __construct(PDO $db)
22    {
23        $this->db = $db;
24    }
25
26    /**
27     * Find a timesheet by ID
28     *
29     * @param int $timesheetId Timesheet ID
30     * @return Timesheet|null
31     */
32    public function findById(int $timesheetId): ?Timesheet
33    {
34        $stmt = $this->db->prepare("
35            SELECT
36                ts.*,
37                e.firstName as employeeFirstName,
38                e.lastName as employeeLastName,
39                CONCAT(u.firstName, ' ', u.lastName) as approverName
40            FROM scheduleTimesheets ts
41            INNER JOIN kiosk_users.users e ON ts.employeeId = e.id
42            LEFT JOIN kiosk_users.users u ON ts.approvedByUserId = u.id
43            WHERE ts.timesheetId = :timesheetId
44        ");
45        $stmt->bindValue(':timesheetId', $timesheetId, PDO::PARAM_INT);
46        $stmt->execute();
47
48        $row = $stmt->fetch(PDO::FETCH_ASSOC);
49        if (!$row) {
50            return null;
51        }
52
53        return Timesheet::fromRow($row);
54    }
55
56    /**
57     * Find timesheets for a specific week
58     *
59     * @param DateTime $weekStart Start of the week
60     * @return Timesheet[]
61     */
62    public function findByWeek(DateTime $weekStart): array
63    {
64        $stmt = $this->db->prepare("
65            SELECT
66                ts.*,
67                e.firstName AS employeeFirstName,
68                e.lastName AS employeeLastName,
69                CONCAT(u.firstName, ' ', u.lastName) as approverName
70            FROM scheduleTimesheets ts
71            INNER JOIN kiosk_users.users e ON ts.employeeId = e.id
72            LEFT JOIN kiosk_users.users u ON ts.approvedByUserId = u.id
73            WHERE ts.weekStartDate = :weekStart
74              AND e.enabled = 1
75            ORDER BY e.lastName ASC, e.firstName ASC
76        ");
77        $stmt->bindValue(':weekStart', $weekStart->format('Y-m-d'));
78        $stmt->execute();
79
80        $timesheets = [];
81        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
82            $timesheets[] = Timesheet::fromRow($row);
83        }
84
85        return $timesheets;
86    }
87
88    /**
89     * Find a timesheet for a specific employee and week
90     *
91     * @param int $employeeId Employee ID
92     * @param DateTime $weekStart Start of the week
93     * @return Timesheet|null
94     */
95    public function findByEmployeeAndWeek(int $employeeId, DateTime $weekStart): ?Timesheet
96    {
97        $stmt = $this->db->prepare("
98            SELECT
99                ts.*,
100                e.firstName AS employeeFirstName,
101                e.lastName AS employeeLastName,
102                CONCAT(u.firstName, ' ', u.lastName) as approverName
103            FROM scheduleTimesheets ts
104            INNER JOIN kiosk_users.users e ON ts.employeeId = e.id
105            LEFT JOIN kiosk_users.users u ON ts.approvedByUserId = u.id
106            WHERE ts.employeeId = :employeeId
107              AND ts.weekStartDate = :weekStart
108        ");
109        $stmt->bindValue(':employeeId', $employeeId, PDO::PARAM_INT);
110        $stmt->bindValue(':weekStart', $weekStart->format('Y-m-d'));
111        $stmt->execute();
112
113        $row = $stmt->fetch(PDO::FETCH_ASSOC);
114        if (!$row) {
115            return null;
116        }
117
118        return Timesheet::fromRow($row);
119    }
120
121    /**
122     * Find timesheets by status
123     *
124     * @param string $status Status to filter by
125     * @param DateTime|null $weekStart Optional week filter
126     * @return Timesheet[]
127     */
128    public function findByStatus(string $status, ?DateTime $weekStart = null): array
129    {
130        $sql = "
131            SELECT
132                ts.*,
133                e.firstName AS employeeFirstName,
134                e.lastName AS employeeLastName,
135                CONCAT(u.firstName, ' ', u.lastName) as approverName
136            FROM scheduleTimesheets ts
137            INNER JOIN kiosk_users.users e ON ts.employeeId = e.id
138            LEFT JOIN kiosk_users.users u ON ts.approvedByUserId = u.id
139            WHERE ts.status = :status
140              AND e.enabled = 1
141        ";
142
143        if ($weekStart !== null) {
144            $sql .= " AND ts.weekStartDate = :weekStart";
145        }
146
147        $sql .= " ORDER BY ts.weekStartDate DESC, e.lastName ASC";
148
149        $stmt = $this->db->prepare($sql);
150        $stmt->bindValue(':status', $status);
151
152        if ($weekStart !== null) {
153            $stmt->bindValue(':weekStart', $weekStart->format('Y-m-d'));
154        }
155
156        $stmt->execute();
157
158        $timesheets = [];
159        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
160            $timesheets[] = Timesheet::fromRow($row);
161        }
162
163        return $timesheets;
164    }
165
166    /**
167     * Find timesheets with overtime for a week
168     *
169     * @param DateTime $weekStart Start of the week
170     * @return Timesheet[]
171     */
172    public function findWithOvertime(DateTime $weekStart): array
173    {
174        $stmt = $this->db->prepare("
175            SELECT
176                ts.*,
177                e.firstName AS employeeFirstName,
178                e.lastName AS employeeLastName,
179                CONCAT(u.firstName, ' ', u.lastName) as approverName
180            FROM scheduleTimesheets ts
181            INNER JOIN kiosk_users.users e ON ts.employeeId = e.id
182            LEFT JOIN kiosk_users.users u ON ts.approvedByUserId = u.id
183            WHERE ts.weekStartDate = :weekStart
184              AND (ts.overtimeHours > 0 OR ts.doubletimeHours > 0)
185              AND e.enabled = 1
186            ORDER BY ts.overtimeHours DESC, ts.doubletimeHours DESC
187        ");
188        $stmt->bindValue(':weekStart', $weekStart->format('Y-m-d'));
189        $stmt->execute();
190
191        $timesheets = [];
192        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
193            $timesheets[] = Timesheet::fromRow($row);
194        }
195
196        return $timesheets;
197    }
198
199    /**
200     * Create or update a timesheet
201     *
202     * @param Timesheet $timesheet Timesheet to upsert
203     * @return Timesheet Upserted timesheet
204     */
205    public function updateOrCreate(Timesheet $timesheet): Timesheet
206    {
207        $existing = $this->findByEmployeeAndWeek(
208            $timesheet->getEmployeeId(),
209            $timesheet->getWeekStartDate()
210        );
211
212        if ($existing !== null) {
213            $timesheet->setTimesheetId($existing->getTimesheetId());
214            return $this->update($timesheet);
215        }
216
217        return $this->create($timesheet);
218    }
219
220    /**
221     * Create a new timesheet
222     *
223     * @param Timesheet $timesheet Timesheet to create
224     * @return Timesheet Created timesheet with ID
225     */
226    public function create(Timesheet $timesheet): Timesheet
227    {
228        $data = $timesheet->toDbArray();
229
230        $stmt = $this->db->prepare("
231            INSERT INTO scheduleTimesheets (
232                employeeId, weekStartDate, weekEndDate,
233                scheduledTotalHours, scheduledRegularHours, scheduledOvertimeHours, scheduledDoubletimeHours,
234                totalHours, regularHours, overtimeHours, doubletimeHours,
235                totalPay, status, approvedByUserId, approved_at, exported_at
236            ) VALUES (
237                :employeeId, :weekStartDate, :weekEndDate,
238                :scheduledTotalHours, :scheduledRegularHours, :scheduledOvertimeHours, :scheduledDoubletimeHours,
239                :totalHours, :regularHours, :overtimeHours, :doubletimeHours,
240                :totalPay, :status, :approvedByUserId, :approved_at, :exported_at
241            )
242        ");
243
244        $stmt->bindValue(':employeeId', $data['employeeId'], PDO::PARAM_INT);
245        $stmt->bindValue(':weekStartDate', $data['weekStartDate']);
246        $stmt->bindValue(':weekEndDate', $data['weekEndDate']);
247        $stmt->bindValue(':scheduledTotalHours', $data['scheduledTotalHours']);
248        $stmt->bindValue(':scheduledRegularHours', $data['scheduledRegularHours']);
249        $stmt->bindValue(':scheduledOvertimeHours', $data['scheduledOvertimeHours']);
250        $stmt->bindValue(':scheduledDoubletimeHours', $data['scheduledDoubletimeHours']);
251        $stmt->bindValue(':totalHours', $data['totalHours']);
252        $stmt->bindValue(':regularHours', $data['regularHours']);
253        $stmt->bindValue(':overtimeHours', $data['overtimeHours']);
254        $stmt->bindValue(':doubletimeHours', $data['doubletimeHours']);
255        $stmt->bindValue(':totalPay', $data['totalPay']);
256        $stmt->bindValue(':status', $data['status']);
257        $stmt->bindValue(':approvedByUserId', $data['approvedByUserId'], $data['approvedByUserId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
258        $stmt->bindValue(':approved_at', $data['approved_at']);
259        $stmt->bindValue(':exported_at', $data['exported_at']);
260
261        $stmt->execute();
262
263        $timesheetId = (int)$this->db->lastInsertId();
264        $timesheet->setTimesheetId($timesheetId);
265
266        return $this->findById($timesheetId) ?? $timesheet;
267    }
268
269    /**
270     * Update an existing timesheet
271     *
272     * @param Timesheet $timesheet Timesheet to update
273     * @return Timesheet Updated timesheet
274     */
275    public function update(Timesheet $timesheet): Timesheet
276    {
277        if ($timesheet->getTimesheetId() === null) {
278            throw new \InvalidArgumentException('Cannot update timesheet without timesheetId');
279        }
280
281        $data = $timesheet->toDbArray();
282
283        $stmt = $this->db->prepare("
284            UPDATE scheduleTimesheets
285            SET scheduledTotalHours = :scheduledTotalHours,
286                scheduledRegularHours = :scheduledRegularHours,
287                scheduledOvertimeHours = :scheduledOvertimeHours,
288                scheduledDoubletimeHours = :scheduledDoubletimeHours,
289                totalHours = :totalHours,
290                regularHours = :regularHours,
291                overtimeHours = :overtimeHours,
292                doubletimeHours = :doubletimeHours,
293                totalPay = :totalPay,
294                status = :status,
295                approvedByUserId = :approvedByUserId,
296                approved_at = :approved_at,
297                exported_at = :exported_at
298            WHERE timesheetId = :timesheetId
299        ");
300
301        $stmt->bindValue(':timesheetId', $timesheet->getTimesheetId(), PDO::PARAM_INT);
302        $stmt->bindValue(':scheduledTotalHours', $data['scheduledTotalHours']);
303        $stmt->bindValue(':scheduledRegularHours', $data['scheduledRegularHours']);
304        $stmt->bindValue(':scheduledOvertimeHours', $data['scheduledOvertimeHours']);
305        $stmt->bindValue(':scheduledDoubletimeHours', $data['scheduledDoubletimeHours']);
306        $stmt->bindValue(':totalHours', $data['totalHours']);
307        $stmt->bindValue(':regularHours', $data['regularHours']);
308        $stmt->bindValue(':overtimeHours', $data['overtimeHours']);
309        $stmt->bindValue(':doubletimeHours', $data['doubletimeHours']);
310        $stmt->bindValue(':totalPay', $data['totalPay']);
311        $stmt->bindValue(':status', $data['status']);
312        $stmt->bindValue(':approvedByUserId', $data['approvedByUserId'], $data['approvedByUserId'] === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
313        $stmt->bindValue(':approved_at', $data['approved_at']);
314        $stmt->bindValue(':exported_at', $data['exported_at']);
315
316        $stmt->execute();
317
318        return $this->findById($timesheet->getTimesheetId()) ?? $timesheet;
319    }
320
321    /**
322     * Approve a timesheet
323     *
324     * @param int $timesheetId Timesheet ID
325     * @param int $approverUserId User ID who approved
326     * @return Timesheet Updated timesheet
327     */
328    public function approve(int $timesheetId, int $approverUserId): Timesheet
329    {
330        $timesheet = $this->findById($timesheetId);
331        if ($timesheet === null) {
332            throw new \InvalidArgumentException('Timesheet not found');
333        }
334
335        $timesheet->approve($approverUserId);
336        return $this->update($timesheet);
337    }
338
339    /**
340     * Approve multiple timesheets at once
341     *
342     * @param array<int> $timesheetIds Timesheet IDs to approve
343     * @param int $approverUserId User ID who approved
344     * @return int Number of timesheets approved
345     */
346    public function approveMultiple(array $timesheetIds, int $approverUserId): int
347    {
348        if (empty($timesheetIds)) {
349            return 0;
350        }
351
352        $placeholders = implode(',', array_fill(0, count($timesheetIds), '?'));
353
354        $stmt = $this->db->prepare("
355            UPDATE scheduleTimesheets
356            SET status = 'approved',
357                approvedByUserId = ?,
358                approved_at = NOW()
359            WHERE timesheetId IN ({$placeholders})
360              AND status = 'pending'
361        ");
362
363        $params = array_merge([$approverUserId], $timesheetIds);
364        $stmt->execute($params);
365
366        return $stmt->rowCount();
367    }
368
369    /**
370     * Unlock a timesheet (set back to pending)
371     *
372     * @param int $timesheetId Timesheet ID
373     * @return Timesheet Updated timesheet
374     */
375    public function unlock(int $timesheetId): Timesheet
376    {
377        $timesheet = $this->findById($timesheetId);
378        if ($timesheet === null) {
379            throw new \InvalidArgumentException('Timesheet not found');
380        }
381
382        $timesheet->unlock();
383        return $this->update($timesheet);
384    }
385
386    /**
387     * Mark timesheets as exported
388     *
389     * @param array<int> $timesheetIds Timesheet IDs to mark
390     * @return int Number of timesheets marked
391     */
392    public function markExported(array $timesheetIds): int
393    {
394        if (empty($timesheetIds)) {
395            return 0;
396        }
397
398        $placeholders = implode(',', array_fill(0, count($timesheetIds), '?'));
399
400        $stmt = $this->db->prepare("
401            UPDATE scheduleTimesheets
402            SET status = 'exported',
403                exported_at = NOW()
404            WHERE timesheetId IN ({$placeholders})
405              AND status = 'approved'
406        ");
407
408        $stmt->execute($timesheetIds);
409
410        return $stmt->rowCount();
411    }
412
413    /**
414     * Get summary statistics for a week
415     *
416     * @param DateTime $weekStart Start of the week
417     * @return array{totalEmployees: int, pending: int, approved: int, exported: int, totalHours: float, overtimeHours: float, totalPay: float}
418     */
419    public function getWeekSummary(DateTime $weekStart): array
420    {
421        $stmt = $this->db->prepare("
422            SELECT
423                COUNT(*) as totalEmployees,
424                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
425                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved,
426                SUM(CASE WHEN status = 'exported' THEN 1 ELSE 0 END) as exported,
427                SUM(totalHours) as totalHours,
428                SUM(overtimeHours) as overtimeHours,
429                SUM(doubletimeHours) as doubletimeHours,
430                SUM(totalPay) as totalPay
431            FROM scheduleTimesheets ts
432            INNER JOIN kiosk_users.users e ON ts.employeeId = e.id
433            WHERE ts.weekStartDate = :weekStart
434              AND e.enabled = 1
435        ");
436        $stmt->bindValue(':weekStart', $weekStart->format('Y-m-d'));
437        $stmt->execute();
438
439        $row = $stmt->fetch(PDO::FETCH_ASSOC);
440
441        return [
442            'totalEmployees' => (int)($row['totalEmployees'] ?? 0),
443            'pending' => (int)($row['pending'] ?? 0),
444            'approved' => (int)($row['approved'] ?? 0),
445            'exported' => (int)($row['exported'] ?? 0),
446            'totalHours' => (float)($row['totalHours'] ?? 0),
447            'overtimeHours' => (float)($row['overtimeHours'] ?? 0),
448            'doubletimeHours' => (float)($row['doubletimeHours'] ?? 0),
449            'totalPay' => (float)($row['totalPay'] ?? 0),
450        ];
451    }
452
453    /**
454     * Get timesheets ready for export (approved status)
455     *
456     * @param DateTime $weekStart Start of the week
457     * @return Timesheet[]
458     */
459    public function findReadyForExport(DateTime $weekStart): array
460    {
461        return $this->findByStatus(Timesheet::STATUS_APPROVED, $weekStart);
462    }
463}