Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 495
0.00%
0 / 13
 CRAP
0.00%
0 / 1
ReportService
0.00%
0 / 495
0.00%
0 / 13
 7482
0.00%
0 / 1
 getSummary
0.00%
0 / 39
0.00%
0 / 1
 42
 getAgeDistribution
0.00%
0 / 19
0.00%
0 / 1
 6
 getAgingByCategory
0.00%
0 / 52
0.00%
0 / 1
 132
 getAgingSummary
0.00%
0 / 33
0.00%
0 / 1
 30
 getAgingByLocation
0.00%
0 / 22
0.00%
0 / 1
 12
 getCategoryHealth
0.00%
0 / 30
0.00%
0 / 1
 42
 getLocationUtilization
0.00%
0 / 28
0.00%
0 / 1
 20
 getStaleInventory
0.00%
0 / 55
0.00%
0 / 1
 72
 getBinsByCategory
0.00%
0 / 31
0.00%
0 / 1
 20
 getActivitySummary
0.00%
0 / 53
0.00%
0 / 1
 156
 getActivityByCategory
0.00%
0 / 15
0.00%
0 / 1
 12
 getEventReadiness
0.00%
0 / 38
0.00%
0 / 1
 20
 exportToCsv
0.00%
0 / 80
0.00%
0 / 1
 342
1<?php
2
3namespace BuyerKiosk\Backstock;
4
5/**
6 * ReportService
7 *
8 * Provides analytics and reporting functionality for the Backstock system.
9 * Generates dashboard summaries, aging analysis, category/location reports,
10 * activity metrics, and export capabilities.
11 */
12class ReportService extends Backstock
13{
14    /**
15     * Get dashboard summary data
16     *
17     * @return array Dashboard metrics including totals, averages, and health score
18     */
19    public function getSummary()
20    {
21        try {
22            $summary = [];
23
24            // Get overall bin statistics
25            $stmt = $this->storeDB->prepare("
26                SELECT
27                    COUNT(*) as totalBins,
28                    COALESCE(AVG(DATEDIFF(NOW(), ageDate)), 0) as avgAge,
29                    COALESCE(MAX(DATEDIFF(NOW(), ageDate)), 0) as maxAge,
30                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) > 180 THEN 1 ELSE 0 END) as staleBins
31                FROM bsBins
32                WHERE deleted = 0
33            ");
34            $stmt->execute();
35            $stats = $stmt->fetch(\PDO::FETCH_ASSOC);
36
37            $summary['totalBins'] = (int)$stats['totalBins'];
38            $summary['avgAge'] = round($stats['avgAge'], 1);
39            $summary['maxAge'] = (int)$stats['maxAge'];
40            $summary['staleBins'] = (int)$stats['staleBins'];
41
42            // Get location-based statistics
43            $stmt = $this->storeDB->prepare("
44                SELECT
45                    l.onsite,
46                    COUNT(b.id) as binCount
47                FROM bsBins b
48                LEFT JOIN bsLocations l ON b.location = l.id
49                WHERE b.deleted = 0
50                GROUP BY l.onsite
51            ");
52            $stmt->execute();
53
54            $summary['offSiteBins'] = 0;
55            $summary['onSiteBins'] = 0;
56
57            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
58                if ($row['onsite'] == 1) {
59                    $summary['onSiteBins'] = (int)$row['binCount'];
60                } else {
61                    $summary['offSiteBins'] = (int)$row['binCount'];
62                }
63            }
64
65            // Get category count
66            $stmt = $this->storeDB->prepare("SELECT COUNT(*) as count FROM bsCategories");
67            $stmt->execute();
68            $summary['categoryCount'] = (int)$stmt->fetch(\PDO::FETCH_ASSOC)['count'];
69
70            // Get location count
71            $stmt = $this->storeDB->prepare("SELECT COUNT(*) as count FROM bsLocations");
72            $stmt->execute();
73            $summary['locationCount'] = (int)$stmt->fetch(\PDO::FETCH_ASSOC)['count'];
74
75            // Get recent actions (last 7 days)
76            $stmt = $this->storeDB->prepare("
77                SELECT COUNT(*) as count
78                FROM bsActions
79                WHERE timePerformed >= DATE_SUB(NOW(), INTERVAL 7 DAY)
80            ");
81            $stmt->execute();
82            $summary['recentActions'] = (int)$stmt->fetch(\PDO::FETCH_ASSOC)['count'];
83
84            // Calculate health score (0-100 based on stale percentage)
85            if ($summary['totalBins'] > 0) {
86                $stalePercent = ($summary['staleBins'] / $summary['totalBins']) * 100;
87                $summary['healthScore'] = max(0, round(100 - $stalePercent));
88            } else {
89                $summary['healthScore'] = 100;
90            }
91
92            // Calculate offsite percentage
93            if ($summary['totalBins'] > 0) {
94                $summary['offSitePercent'] = round(($summary['offSiteBins'] / $summary['totalBins']) * 100, 1);
95            } else {
96                $summary['offSitePercent'] = 0;
97            }
98
99            return $summary;
100
101        } catch (\Exception $e) {
102            $this->log->logError("Error in getSummary: " . $e->getMessage());
103            throw $e;
104        }
105    }
106
107    /**
108     * Get age distribution for pie/doughnut chart
109     *
110     * @return array Age distribution with labels, data, and colors
111     */
112    public function getAgeDistribution()
113    {
114        try {
115            $stmt = $this->storeDB->prepare("
116                SELECT
117                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) <= 30 THEN 1 ELSE 0 END) as '0-30',
118                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 31 AND 60 THEN 1 ELSE 0 END) as '31-60',
119                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 61 AND 90 THEN 1 ELSE 0 END) as '61-90',
120                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as '91-180',
121                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as '181-365',
122                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) > 365 THEN 1 ELSE 0 END) as '366+'
123                FROM bsBins
124                WHERE deleted = 0
125            ");
126            $stmt->execute();
127            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
128
129            return [
130                'labels' => ['0-30 days', '31-60 days', '61-90 days', '91-180 days', '181-365 days', '366+ days'],
131                'data' => [
132                    (int)$result['0-30'],
133                    (int)$result['31-60'],
134                    (int)$result['61-90'],
135                    (int)$result['91-180'],
136                    (int)$result['181-365'],
137                    (int)$result['366+']
138                ],
139                'colors' => ['#4CAF50', '#8BC34A', '#FFC107', '#FF9800', '#FF5722', '#D32F2F']
140            ];
141
142        } catch (\Exception $e) {
143            $this->log->logError("Error in getAgeDistribution: " . $e->getMessage());
144            throw $e;
145        }
146    }
147
148    /**
149     * Get aging breakdown by category for stacked bar chart
150     *
151     * @param int|null $locationId Filter by location ID
152     * @param string $sortBy Sort method: name, oldest, stale, total
153     * @return array Aging data by category formatted for Chart.js
154     */
155    public function getAgingByCategory($locationId = null, $sortBy = 'total')
156    {
157        try {
158            $sql = "
159                SELECT
160                    c.name as categoryName,
161                    c.color,
162                    COUNT(b.id) as total,
163                    COALESCE(AVG(DATEDIFF(NOW(), b.ageDate)), 0) as avgAge,
164                    COALESCE(MAX(DATEDIFF(NOW(), b.ageDate)), 0) as maxAge,
165                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) <= 90 THEN 1 ELSE 0 END) as '0-90',
166                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as '91-180',
167                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as '181-365',
168                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 365 THEN 1 ELSE 0 END) as '366+'
169                FROM bsCategories c
170                LEFT JOIN bsBins b ON c.id = b.mainCategory AND b.deleted = 0
171            ";
172
173            if ($locationId !== null) {
174                $sql .= " AND b.location = :locationId";
175            }
176
177            $sql .= " GROUP BY c.id, c.name, c.color";
178
179            // Add sorting
180            switch ($sortBy) {
181                case 'name':
182                    $sql .= " ORDER BY c.name ASC";
183                    break;
184                case 'oldest':
185                    $sql .= " ORDER BY maxAge DESC";
186                    break;
187                case 'stale':
188                    $sql .= " ORDER BY (SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 180 THEN 1 ELSE 0 END)) DESC";
189                    break;
190                case 'total':
191                default:
192                    $sql .= " ORDER BY total DESC";
193                    break;
194            }
195
196            $stmt = $this->storeDB->prepare($sql);
197
198            if ($locationId !== null) {
199                $stmt->bindValue(':locationId', $locationId, \PDO::PARAM_INT);
200            }
201
202            $stmt->execute();
203            $results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
204
205            $categories = [];
206            $datasets = [
207                ['label' => '0-90 days', 'data' => [], 'backgroundColor' => '#4CAF50'],
208                ['label' => '91-180 days', 'data' => [], 'backgroundColor' => '#FFC107'],
209                ['label' => '181-365 days', 'data' => [], 'backgroundColor' => '#FF5722'],
210                ['label' => '366+ days', 'data' => [], 'backgroundColor' => '#D32F2F']
211            ];
212
213            $details = [];
214
215            foreach ($results as $row) {
216                if ($row['total'] > 0) {
217                    $categories[] = $row['categoryName'];
218                    $datasets[0]['data'][] = (int)$row['0-90'];
219                    $datasets[1]['data'][] = (int)$row['91-180'];
220                    $datasets[2]['data'][] = (int)$row['181-365'];
221                    $datasets[3]['data'][] = (int)$row['366+'];
222
223                    $details[] = [
224                        'category' => $row['categoryName'],
225                        'color' => $row['color'],
226                        'total' => (int)$row['total'],
227                        'avgAge' => round($row['avgAge'], 1),
228                        'maxAge' => (int)$row['maxAge']
229                    ];
230                }
231            }
232
233            return [
234                'labels' => $categories,
235                'datasets' => $datasets,
236                'details' => $details
237            ];
238
239        } catch (\Exception $e) {
240            $this->log->logError("Error in getAgingByCategory: " . $e->getMessage());
241            throw $e;
242        }
243    }
244
245    /**
246     * Get overall aging summary statistics
247     *
248     * @param int|null $locationId Filter by location ID
249     * @return array Aging statistics with bucket counts and percentages
250     */
251    public function getAgingSummary($locationId = null)
252    {
253        try {
254            $sql = "
255                SELECT
256                    COUNT(*) as totalBins,
257                    COALESCE(AVG(DATEDIFF(NOW(), ageDate)), 0) as avgAge,
258                    COALESCE(MAX(DATEDIFF(NOW(), ageDate)), 0) as maxAge,
259                    COALESCE(MIN(DATEDIFF(NOW(), ageDate)), 0) as minAge,
260                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) <= 90 THEN 1 ELSE 0 END) as fresh,
261                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as aging,
262                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as stale,
263                    SUM(CASE WHEN DATEDIFF(NOW(), ageDate) > 365 THEN 1 ELSE 0 END) as veryStale
264                FROM bsBins
265                WHERE deleted = 0
266            ";
267
268            if ($locationId !== null) {
269                $sql .= " AND location = :locationId";
270            }
271
272            $stmt = $this->storeDB->prepare($sql);
273
274            if ($locationId !== null) {
275                $stmt->bindValue(':locationId', $locationId, \PDO::PARAM_INT);
276            }
277
278            $stmt->execute();
279            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
280
281            $totalBins = (int)$result['totalBins'];
282
283            $summary = [
284                'totalBins' => $totalBins,
285                'avgAge' => round($result['avgAge'], 1),
286                'maxAge' => (int)$result['maxAge'],
287                'minAge' => (int)$result['minAge'],
288                'fresh' => (int)$result['fresh'],
289                'aging' => (int)$result['aging'],
290                'stale' => (int)$result['stale'],
291                'veryStale' => (int)$result['veryStale']
292            ];
293
294            // Calculate percentages
295            if ($totalBins > 0) {
296                $summary['freshPercent'] = round(($summary['fresh'] / $totalBins) * 100, 1);
297                $summary['agingPercent'] = round(($summary['aging'] / $totalBins) * 100, 1);
298                $summary['stalePercent'] = round(($summary['stale'] / $totalBins) * 100, 1);
299                $summary['veryStalePercent'] = round(($summary['veryStale'] / $totalBins) * 100, 1);
300            } else {
301                $summary['freshPercent'] = 0;
302                $summary['agingPercent'] = 0;
303                $summary['stalePercent'] = 0;
304                $summary['veryStalePercent'] = 0;
305            }
306
307            return $summary;
308
309        } catch (\Exception $e) {
310            $this->log->logError("Error in getAgingSummary: " . $e->getMessage());
311            throw $e;
312        }
313    }
314
315    /**
316     * Get aging breakdown by location
317     *
318     * @return array Aging data by location
319     */
320    public function getAgingByLocation()
321    {
322        try {
323            $stmt = $this->storeDB->prepare("
324                SELECT
325                    l.id,
326                    l.name as locationName,
327                    l.onsite,
328                    COUNT(b.id) as total,
329                    COALESCE(AVG(DATEDIFF(NOW(), b.ageDate)), 0) as avgAge,
330                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) <= 90 THEN 1 ELSE 0 END) as '0-90',
331                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 91 AND 180 THEN 1 ELSE 0 END) as '91-180',
332                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) as '181-365',
333                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 365 THEN 1 ELSE 0 END) as '366+'
334                FROM bsLocations l
335                LEFT JOIN bsBins b ON l.id = b.location AND b.deleted = 0
336                GROUP BY l.id, l.name, l.onsite
337                ORDER BY total DESC
338            ");
339            $stmt->execute();
340
341            $locations = [];
342
343            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
344                $locations[] = [
345                    'id' => (int)$row['id'],
346                    'name' => $row['locationName'],
347                    'onsite' => (bool)$row['onsite'],
348                    'total' => (int)$row['total'],
349                    'avgAge' => round($row['avgAge'], 1),
350                    'buckets' => [
351                        '0-90' => (int)$row['0-90'],
352                        '91-180' => (int)$row['91-180'],
353                        '181-365' => (int)$row['181-365'],
354                        '366+' => (int)$row['366+']
355                    ]
356                ];
357            }
358
359            return $locations;
360
361        } catch (\Exception $e) {
362            $this->log->logError("Error in getAgingByLocation: " . $e->getMessage());
363            throw $e;
364        }
365    }
366
367    /**
368     * Get health metrics per category
369     *
370     * @return array Category health data with status indicators
371     */
372    public function getCategoryHealth()
373    {
374        try {
375            $stmt = $this->storeDB->prepare("
376                SELECT
377                    c.name as categoryName,
378                    c.color,
379                    COUNT(b.id) as binCount,
380                    COALESCE(AVG(DATEDIFF(NOW(), b.ageDate)), 0) as avgAge,
381                    COALESCE(MAX(DATEDIFF(NOW(), b.ageDate)), 0) as maxAge,
382                    SUM(CASE WHEN DATEDIFF(NOW(), b.ageDate) > 180 THEN 1 ELSE 0 END) as staleBins,
383                    SUM(CASE WHEN l.onsite = 1 THEN 1 ELSE 0 END) as onSite,
384                    SUM(CASE WHEN l.onsite = 0 THEN 1 ELSE 0 END) as offSite
385                FROM bsCategories c
386                LEFT JOIN bsBins b ON c.name = b.mainCategory AND b.deleted = 0
387                LEFT JOIN bsLocations l ON b.location = l.id
388                GROUP BY c.id, c.name, c.color
389                HAVING binCount > 0
390                ORDER BY binCount DESC
391            ");
392            $stmt->execute();
393
394            $categories = [];
395
396            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
397                $binCount = (int)$row['binCount'];
398                $staleBins = (int)$row['staleBins'];
399
400                // Calculate health score (0-100)
401                $stalePercent = $binCount > 0 ? ($staleBins / $binCount) * 100 : 0;
402                $healthScore = max(0, round(100 - $stalePercent));
403
404                // Determine status
405                if ($healthScore >= 80) {
406                    $status = 'healthy';
407                } elseif ($healthScore >= 50) {
408                    $status = 'warning';
409                } else {
410                    $status = 'critical';
411                }
412
413                $categories[] = [
414                    'category' => $row['categoryName'],
415                    'color' => $row['color'],
416                    'binCount' => $binCount,
417                    'avgAge' => round($row['avgAge'], 1),
418                    'maxAge' => (int)$row['maxAge'],
419                    'staleBins' => $staleBins,
420                    'onSite' => (int)$row['onSite'],
421                    'offSite' => (int)$row['offSite'],
422                    'healthScore' => $healthScore,
423                    'status' => $status
424                ];
425            }
426
427            return $categories;
428
429        } catch (\Exception $e) {
430            $this->log->logError("Error in getCategoryHealth: " . $e->getMessage());
431            throw $e;
432        }
433    }
434
435    /**
436     * Get utilization metrics per location
437     *
438     * @return array Location utilization data with category breakdown
439     */
440    public function getLocationUtilization()
441    {
442        try {
443            $stmt = $this->storeDB->prepare("
444                SELECT
445                    l.id,
446                    l.name as locationName,
447                    l.onsite,
448                    COUNT(b.id) as binCount,
449                    COALESCE(AVG(DATEDIFF(NOW(), b.ageDate)), 0) as avgAge
450                FROM bsLocations l
451                LEFT JOIN bsBins b ON l.id = b.location AND b.deleted = 0
452                GROUP BY l.id, l.name, l.onsite
453                ORDER BY binCount DESC
454            ");
455            $stmt->execute();
456
457            $locations = [];
458
459            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
460                $locationId = (int)$row['id'];
461
462                // Get category breakdown for this location
463                $catStmt = $this->storeDB->prepare("
464                    SELECT
465                        mainCategory,
466                        COUNT(*) as count
467                    FROM bsBins
468                    WHERE location = :locationId AND deleted = 0
469                    GROUP BY mainCategory
470                    ORDER BY count DESC
471                ");
472                $catStmt->bindValue(':locationId', $locationId, \PDO::PARAM_INT);
473                $catStmt->execute();
474
475                $categoryBreakdown = [];
476                while ($catRow = $catStmt->fetch(\PDO::FETCH_ASSOC)) {
477                    $categoryBreakdown[] = [
478                        'category' => $catRow['mainCategory'],
479                        'count' => (int)$catRow['count']
480                    ];
481                }
482
483                $locations[] = [
484                    'id' => $locationId,
485                    'name' => $row['locationName'],
486                    'onsite' => (bool)$row['onsite'],
487                    'binCount' => (int)$row['binCount'],
488                    'avgAge' => round($row['avgAge'], 1),
489                    'categoryBreakdown' => $categoryBreakdown
490                ];
491            }
492
493            return $locations;
494
495        } catch (\Exception $e) {
496            $this->log->logError("Error in getLocationUtilization: " . $e->getMessage());
497            throw $e;
498        }
499    }
500
501    /**
502     * Get stale inventory report
503     *
504     * @param int $daysThreshold Days to consider as stale (default: 180)
505     * @return array Stale bins grouped by category with summary
506     */
507    public function getStaleInventory($daysThreshold = 180)
508    {
509        try {
510            $stmt = $this->storeDB->prepare("
511                SELECT
512                    b.id,
513                    b.uuid,
514                    b.name,
515                    b.mainCategory,
516                    b.subCat1,
517                    b.subCat2,
518                    b.subCat3,
519                    b.ageDate,
520                    DATEDIFF(NOW(), b.ageDate) as age,
521                    l.name as locationName,
522                    l.onsite,
523                    c.color as categoryColor
524                FROM bsBins b
525                LEFT JOIN bsLocations l ON b.location = l.id
526                LEFT JOIN bsCategories c ON b.mainCategory = c.name
527                WHERE b.deleted = 0 AND DATEDIFF(NOW(), b.ageDate) > :threshold
528                ORDER BY age DESC, b.mainCategory, b.name
529            ");
530            $stmt->bindValue(':threshold', $daysThreshold, \PDO::PARAM_INT);
531            $stmt->execute();
532
533            $bins = [];
534            $byCategory = [];
535
536            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
537                $bin = [
538                    'id' => (int)$row['id'],
539                    'uuid' => $row['uuid'],
540                    'name' => $row['name'],
541                    'mainCategory' => $row['mainCategory'],
542                    'subCat1' => $row['subCat1'],
543                    'subCat2' => $row['subCat2'],
544                    'subCat3' => $row['subCat3'],
545                    'ageDate' => $row['ageDate'],
546                    'age' => (int)$row['age'],
547                    'location' => $row['locationName'],
548                    'onsite' => (bool)$row['onsite'],
549                    'categoryColor' => $row['categoryColor']
550                ];
551
552                $bins[] = $bin;
553
554                // Group by category
555                $category = $row['mainCategory'];
556                if (!isset($byCategory[$category])) {
557                    $byCategory[$category] = [
558                        'category' => $category,
559                        'color' => $row['categoryColor'],
560                        'bins' => [],
561                        'count' => 0,
562                        'totalAge' => 0
563                    ];
564                }
565
566                $byCategory[$category]['bins'][] = $bin;
567                $byCategory[$category]['count']++;
568                $byCategory[$category]['totalAge'] += (int)$row['age'];
569            }
570
571            // Calculate averages
572            foreach ($byCategory as &$cat) {
573                if ($cat['count'] > 0) {
574                    $cat['avgAge'] = round($cat['totalAge'] / $cat['count'], 1);
575                }
576                unset($cat['totalAge']);
577            }
578
579            // Sort by count descending
580            uasort($byCategory, function($a, $b) {
581                return $b['count'] - $a['count'];
582            });
583
584            $summary = [
585                'count' => count($bins),
586                'oldestAge' => count($bins) > 0 ? $bins[0]['age'] : 0,
587                'avgAge' => count($bins) > 0 ? round(array_sum(array_column($bins, 'age')) / count($bins), 1) : 0,
588                'threshold' => $daysThreshold
589            ];
590
591            return [
592                'bins' => $bins,
593                'byCategory' => array_values($byCategory),
594                'summary' => $summary
595            ];
596
597        } catch (\Exception $e) {
598            $this->log->logError("Error in getStaleInventory: " . $e->getMessage());
599            throw $e;
600        }
601    }
602
603    /**
604     * Get all bins for a specific category
605     *
606     * @param int $categoryId Category ID (not used directly, using name instead)
607     * @return array Bins with age and location info
608     */
609    public function getBinsByCategory($categoryId)
610    {
611        try {
612            // First get the category name
613            $stmt = $this->storeDB->prepare("SELECT name FROM bsCategories WHERE id = :categoryId");
614            $stmt->bindValue(':categoryId', $categoryId, \PDO::PARAM_INT);
615            $stmt->execute();
616            $category = $stmt->fetch(\PDO::FETCH_ASSOC);
617
618            if (!$category) {
619                return [];
620            }
621
622            $categoryName = $category['name'];
623
624            $stmt = $this->storeDB->prepare("
625                SELECT
626                    b.id,
627                    b.uuid,
628                    b.name,
629                    b.mainCategory,
630                    b.subCat1,
631                    b.subCat2,
632                    b.subCat3,
633                    b.ageDate,
634                    DATEDIFF(NOW(), b.ageDate) as age,
635                    l.name as locationName,
636                    l.onsite,
637                    CASE
638                        WHEN DATEDIFF(NOW(), b.ageDate) <= 90 THEN 'fresh'
639                        WHEN DATEDIFF(NOW(), b.ageDate) <= 180 THEN 'aging'
640                        WHEN DATEDIFF(NOW(), b.ageDate) <= 365 THEN 'stale'
641                        ELSE 'very-stale'
642                    END as ageStatus
643                FROM bsBins b
644                LEFT JOIN bsLocations l ON b.location = l.id
645                WHERE b.deleted = 0 AND b.mainCategory = :categoryName
646                ORDER BY age DESC, b.name
647            ");
648            $stmt->bindValue(':categoryName', $categoryName, \PDO::PARAM_STR);
649            $stmt->execute();
650
651            $bins = [];
652
653            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
654                $bins[] = [
655                    'id' => (int)$row['id'],
656                    'uuid' => $row['uuid'],
657                    'name' => $row['name'],
658                    'mainCategory' => $row['mainCategory'],
659                    'subCat1' => $row['subCat1'],
660                    'subCat2' => $row['subCat2'],
661                    'subCat3' => $row['subCat3'],
662                    'ageDate' => $row['ageDate'],
663                    'age' => (int)$row['age'],
664                    'location' => $row['locationName'],
665                    'onsite' => (bool)$row['onsite'],
666                    'ageStatus' => $row['ageStatus']
667                ];
668            }
669
670            return $bins;
671
672        } catch (\Exception $e) {
673            $this->log->logError("Error in getBinsByCategory: " . $e->getMessage());
674            throw $e;
675        }
676    }
677
678    /**
679     * Get activity summary for specified time period
680     *
681     * Action types:
682     * - 0 = "removed everything" (bins emptied)
683     * - 1 = "added some" (items added)
684     * - 2 = "removed some" (items removed)
685     * - 3 = "removed all of [category]" (category removed)
686     *
687     * @param int $days Number of days to look back (default: 30)
688     * @return array Activity metrics including actions by type, employee, and category
689     */
690    public function getActivitySummary($days = 30)
691    {
692        try {
693            // Get actions by type
694            $stmt = $this->storeDB->prepare("
695                SELECT
696                    action,
697                    COUNT(*) as count
698                FROM bsActions
699                WHERE timePerformed >= DATE_SUB(NOW(), INTERVAL :days DAY)
700                GROUP BY action
701                ORDER BY count DESC
702            ");
703            $stmt->bindValue(':days', $days, \PDO::PARAM_INT);
704            $stmt->execute();
705
706            $actionsByType = [];
707            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
708                $actionsByType[$row['action']] = (int)$row['count'];
709            }
710
711            // Map action types to readable names for frontend
712            // Action 0 = removed everything (bins emptied)
713            // Action 1 = added some (items added)
714            // Action 2 = removed some (items removed)
715            // Action 3 = removed all of [category] (also items removed)
716            $binsEmptied = isset($actionsByType[0]) ? $actionsByType[0] : 0;
717            $itemsAdded = isset($actionsByType[1]) ? $actionsByType[1] : 0;
718            $itemsRemoved = (isset($actionsByType[2]) ? $actionsByType[2] : 0) +
719                            (isset($actionsByType[3]) ? $actionsByType[3] : 0);
720
721            // Get bins created count (from bsBins table, not actions)
722            $stmt = $this->storeDB->prepare("
723                SELECT COUNT(*) as count
724                FROM bsBins
725                WHERE dateCreated >= DATE_SUB(NOW(), INTERVAL :days DAY)
726                  AND deleted = 0
727            ");
728            $stmt->bindValue(':days', $days, \PDO::PARAM_INT);
729            $stmt->execute();
730            $binsCreated = (int)$stmt->fetch(\PDO::FETCH_ASSOC)['count'];
731
732            // Get bins deleted count - we don't have a deleted timestamp,
733            // so we'll count bins that were deleted (deleted=1)
734            // This is a rough approximation since we can't know when they were deleted
735            $binsDeleted = 0;
736
737            // Get actions by employee (top 5)
738            $stmt = $this->storeDB->prepare("
739                SELECT
740                    CONCAT(e.employeeFirstName, ' ', e.employeeLastName) as employeeName,
741                    COUNT(*) as count
742                FROM bsActions a
743                LEFT JOIN employees e ON a.employeeID = e.employeeID
744                WHERE a.timePerformed >= DATE_SUB(NOW(), INTERVAL :days DAY)
745                GROUP BY a.employeeID, e.employeeFirstName, e.employeeLastName
746                ORDER BY count DESC
747                LIMIT 5
748            ");
749            $stmt->bindValue(':days', $days, \PDO::PARAM_INT);
750            $stmt->execute();
751
752            $topEmployees = [];
753            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
754                $topEmployees[] = [
755                    'name' => $row['employeeName'] ?: 'Unknown',
756                    'actionCount' => (int)$row['count']
757                ];
758            }
759
760            // Get most active categories (top 5)
761            $stmt = $this->storeDB->prepare("
762                SELECT
763                    b.mainCategory as categoryName,
764                    c.color as categoryColor,
765                    COUNT(*) as actionCount
766                FROM bsActions a
767                JOIN bsBins b ON a.binID = b.id
768                LEFT JOIN bsCategories c ON b.mainCategory = c.name
769                WHERE a.timePerformed >= DATE_SUB(NOW(), INTERVAL :days DAY)
770                GROUP BY b.mainCategory, c.color
771                ORDER BY actionCount DESC
772                LIMIT 5
773            ");
774            $stmt->bindValue(':days', $days, \PDO::PARAM_INT);
775            $stmt->execute();
776
777            $activeCategories = [];
778            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
779                $activeCategories[] = [
780                    'name' => $row['categoryName'] ?: 'Unknown',
781                    'color' => $row['categoryColor'] ?: 'cccccc',
782                    'actionCount' => (int)$row['actionCount']
783                ];
784            }
785
786            // Get total actions
787            $totalActions = array_sum($actionsByType);
788
789            return [
790                'binsCreated' => $binsCreated,
791                'binsDeleted' => $binsDeleted,
792                'itemsAdded' => $itemsAdded,
793                'itemsRemoved' => $itemsRemoved,
794                'binsEmptied' => $binsEmptied,
795                'topEmployees' => $topEmployees,
796                'activeCategories' => $activeCategories,
797                'totalActions' => $totalActions,
798                'period' => $days
799            ];
800
801        } catch (\Exception $e) {
802            $this->log->logError("Error in getActivitySummary: " . $e->getMessage());
803            throw $e;
804        }
805    }
806
807    /**
808     * Get action counts per category
809     *
810     * @param int $days Number of days to look back (default: 30)
811     * @return array Action counts by category
812     */
813    public function getActivityByCategory($days = 30)
814    {
815        try {
816            $stmt = $this->storeDB->prepare("
817                SELECT
818                    b.mainCategory,
819                    COUNT(*) as actionCount,
820                    c.color as categoryColor
821                FROM bsActions a
822                JOIN bsBins b ON a.binID = b.id
823                LEFT JOIN bsCategories c ON b.mainCategory = c.name
824                WHERE a.timePerformed >= DATE_SUB(NOW(), INTERVAL :days DAY)
825                GROUP BY b.mainCategory, c.color
826                ORDER BY actionCount DESC
827            ");
828            $stmt->bindValue(':days', $days, \PDO::PARAM_INT);
829            $stmt->execute();
830
831            $categories = [];
832
833            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
834                $categories[] = [
835                    'category' => $row['mainCategory'],
836                    'color' => $row['categoryColor'],
837                    'actionCount' => (int)$row['actionCount']
838                ];
839            }
840
841            return $categories;
842
843        } catch (\Exception $e) {
844            $this->log->logError("Error in getActivityByCategory: " . $e->getMessage());
845            throw $e;
846        }
847    }
848
849    /**
850     * Get readiness metrics for upcoming events
851     *
852     * @return array Event readiness data
853     */
854    public function getEventReadiness()
855    {
856        try {
857            // Get active or upcoming events
858            $stmt = $this->storeDB->prepare("
859                SELECT
860                    id,
861                    name,
862                    eventType,
863                    startDate,
864                    endDate,
865                    buildUpDays,
866                    windDownDays,
867                    color,
868                    icon
869                FROM bsEvents
870                WHERE isActive = 1
871                   OR (startDate >= CURDATE() AND startDate <= DATE_ADD(CURDATE(), INTERVAL 60 DAY))
872                ORDER BY startDate ASC
873            ");
874            $stmt->execute();
875
876            $events = [];
877
878            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
879                $eventId = (int)$row['id'];
880
881                // Get linked categories for this event (would need to query event-category mapping)
882                // For now, we'll use eventType to match categories
883                $eventType = $row['eventType'];
884
885                // Get bin counts for categories matching this event type
886                $binStmt = $this->storeDB->prepare("
887                    SELECT
888                        COUNT(*) as binCount,
889                        AVG(DATEDIFF(NOW(), ageDate)) as avgAge,
890                        SUM(CASE WHEN l.onsite = 1 THEN 1 ELSE 0 END) as onSiteBins,
891                        SUM(CASE WHEN l.onsite = 0 THEN 1 ELSE 0 END) as offSiteBins
892                    FROM bsBins b
893                    LEFT JOIN bsLocations l ON b.location = l.id
894                    WHERE b.deleted = 0
895                      AND (b.mainCategory LIKE :eventType1
896                           OR b.subCat1 LIKE :eventType2
897                           OR b.subCat2 LIKE :eventType3)
898                ");
899                $searchTerm = '%' . $eventType . '%';
900                $binStmt->bindValue(':eventType1', $searchTerm, \PDO::PARAM_STR);
901                $binStmt->bindValue(':eventType2', $searchTerm, \PDO::PARAM_STR);
902                $binStmt->bindValue(':eventType3', $searchTerm, \PDO::PARAM_STR);
903                $binStmt->execute();
904                $binData = $binStmt->fetch(\PDO::FETCH_ASSOC);
905
906                $binCount = (int)$binData['binCount'];
907                $onSiteBins = (int)$binData['onSiteBins'];
908
909                // Calculate readiness percentage (based on bins being onsite)
910                $readinessPercent = $binCount > 0 ? round(($onSiteBins / $binCount) * 100, 1) : 0;
911
912                $events[] = [
913                    'id' => $eventId,
914                    'name' => $row['name'],
915                    'eventType' => $eventType,
916                    'startDate' => $row['startDate'],
917                    'endDate' => $row['endDate'],
918                    'buildUpDays' => (int)$row['buildUpDays'],
919                    'windDownDays' => (int)$row['windDownDays'],
920                    'color' => $row['color'],
921                    'icon' => $row['icon'],
922                    'binCount' => $binCount,
923                    'avgAge' => round($binData['avgAge'], 1),
924                    'onSiteBins' => $onSiteBins,
925                    'offSiteBins' => (int)$binData['offSiteBins'],
926                    'readinessPercent' => $readinessPercent
927                ];
928            }
929
930            return $events;
931
932        } catch (\Exception $e) {
933            $this->log->logError("Error in getEventReadiness: " . $e->getMessage());
934            throw $e;
935        }
936    }
937
938    /**
939     * Export report data to CSV format
940     *
941     * @param string $reportType Type of report: aging, stale, activity, categories, locations
942     * @param array $options Optional parameters specific to each report type
943     * @return string CSV data
944     */
945    public function exportToCsv($reportType, $options = [])
946    {
947        try {
948            $csv = '';
949
950            switch ($reportType) {
951                case 'aging':
952                    $locationId = $options['locationId'] ?? null;
953                    $summary = $this->getAgingSummary($locationId);
954
955                    $csv .= "Aging Report\n";
956                    $csv .= "Generated: " . date('Y-m-d H:i:s') . "\n\n";
957                    $csv .= "Total Bins," . $summary['totalBins'] . "\n";
958                    $csv .= "Average Age," . $summary['avgAge'] . " days\n";
959                    $csv .= "Max Age," . $summary['maxAge'] . " days\n\n";
960                    $csv .= "Age Bucket,Count,Percentage\n";
961                    $csv .= "0-90 days," . $summary['fresh'] . "," . $summary['freshPercent'] . "%\n";
962                    $csv .= "91-180 days," . $summary['aging'] . "," . $summary['agingPercent'] . "%\n";
963                    $csv .= "181-365 days," . $summary['stale'] . "," . $summary['stalePercent'] . "%\n";
964                    $csv .= "366+ days," . $summary['veryStale'] . "," . $summary['veryStalePercent'] . "%\n";
965                    break;
966
967                case 'stale':
968                    $threshold = $options['threshold'] ?? 180;
969                    $data = $this->getStaleInventory($threshold);
970
971                    $csv .= "Stale Inventory Report (>" . $threshold . " days)\n";
972                    $csv .= "Generated: " . date('Y-m-d H:i:s') . "\n\n";
973                    $csv .= "UUID,Bin Name,Category,Subcategory 1,Subcategory 2,Subcategory 3,Location,Onsite,Age (days),Age Date\n";
974
975                    foreach ($data['bins'] as $bin) {
976                        $csv .= '"' . $bin['uuid'] . '",';
977                        $csv .= '"' . $bin['name'] . '",';
978                        $csv .= '"' . $bin['mainCategory'] . '",';
979                        $csv .= '"' . ($bin['subCat1'] ?: '') . '",';
980                        $csv .= '"' . ($bin['subCat2'] ?: '') . '",';
981                        $csv .= '"' . ($bin['subCat3'] ?: '') . '",';
982                        $csv .= '"' . $bin['location'] . '",';
983                        $csv .= ($bin['onsite'] ? 'Yes' : 'No') . ',';
984                        $csv .= $bin['age'] . ',';
985                        $csv .= $bin['ageDate'] . "\n";
986                    }
987                    break;
988
989                case 'activity':
990                    $days = $options['days'] ?? 30;
991                    $data = $this->getActivitySummary($days);
992
993                    $csv .= "Activity Report (Last " . $days . " days)\n";
994                    $csv .= "Generated: " . date('Y-m-d H:i:s') . "\n\n";
995                    $csv .= "Total Actions," . $data['totalActions'] . "\n\n";
996
997                    $csv .= "Actions by Type\n";
998                    $csv .= "Action,Count\n";
999                    foreach ($data['actionsByType'] as $action => $count) {
1000                        $csv .= '"' . $action . '",' . $count . "\n";
1001                    }
1002
1003                    $csv .= "\nActions by Employee\n";
1004                    $csv .= "Employee,Count\n";
1005                    foreach ($data['actionsByEmployee'] as $emp) {
1006                        $csv .= '"' . $emp['employee'] . '",' . $emp['count'] . "\n";
1007                    }
1008                    break;
1009
1010                case 'categories':
1011                    $data = $this->getCategoryHealth();
1012
1013                    $csv .= "Category Health Report\n";
1014                    $csv .= "Generated: " . date('Y-m-d H:i:s') . "\n\n";
1015                    $csv .= "Category,Total Bins,Avg Age,Max Age,Stale Bins,Onsite,Offsite,Health Score,Status\n";
1016
1017                    foreach ($data as $cat) {
1018                        $csv .= '"' . $cat['category'] . '",';
1019                        $csv .= $cat['binCount'] . ',';
1020                        $csv .= $cat['avgAge'] . ',';
1021                        $csv .= $cat['maxAge'] . ',';
1022                        $csv .= $cat['staleBins'] . ',';
1023                        $csv .= $cat['onSite'] . ',';
1024                        $csv .= $cat['offSite'] . ',';
1025                        $csv .= $cat['healthScore'] . ',';
1026                        $csv .= $cat['status'] . "\n";
1027                    }
1028                    break;
1029
1030                case 'locations':
1031                    $data = $this->getLocationUtilization();
1032
1033                    $csv .= "Location Utilization Report\n";
1034                    $csv .= "Generated: " . date('Y-m-d H:i:s') . "\n\n";
1035                    $csv .= "Location,Onsite,Total Bins,Avg Age\n";
1036
1037                    foreach ($data as $loc) {
1038                        $csv .= '"' . $loc['name'] . '",';
1039                        $csv .= ($loc['onsite'] ? 'Yes' : 'No') . ',';
1040                        $csv .= $loc['binCount'] . ',';
1041                        $csv .= $loc['avgAge'] . "\n";
1042                    }
1043                    break;
1044
1045                default:
1046                    throw new \Exception("Unknown report type: " . $reportType);
1047            }
1048
1049            return $csv;
1050
1051        } catch (\Exception $e) {
1052            $this->log->logError("Error in exportToCsv: " . $e->getMessage());
1053            throw $e;
1054        }
1055    }
1056}