Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 357
0.00%
0 / 17
 CRAP
0.00%
0 / 1
KPIService
0.00%
0 / 357
0.00%
0 / 17
 5852
0.00%
0 / 1
 __construct
0.00%
0 / 11
0.00%
0 / 1
 30
 getTodayKPIs
0.00%
0 / 34
0.00%
0 / 1
 72
 getDisplayKPIs
0.00%
0 / 30
0.00%
0 / 1
 42
 getLiveFinancials
0.00%
0 / 14
0.00%
0 / 1
 12
 getLaborData
0.00%
0 / 26
0.00%
0 / 1
 20
 getTransactionCount
0.00%
0 / 10
0.00%
0 / 1
 6
 getBuysData
0.00%
0 / 14
0.00%
0 / 1
 6
 getBackstockData
0.00%
0 / 12
0.00%
0 / 1
 6
 getYearOverYearComps
0.00%
0 / 19
0.00%
0 / 1
 12
 calculateDerivedKPIs
0.00%
0 / 4
0.00%
0 / 1
 6
 formatKPIValue
0.00%
0 / 9
0.00%
0 / 1
 30
 getKPIStatus
0.00%
0 / 8
0.00%
0 / 1
 30
 getDetailedKPIs
0.00%
0 / 55
0.00%
0 / 1
 110
 getDetailedBackstockData
0.00%
0 / 31
0.00%
0 / 1
 56
 getRecentBackstockActivity
0.00%
0 / 28
0.00%
0 / 1
 56
 getDetailedBuysData
0.00%
0 / 47
0.00%
0 / 1
 6
 getTradePercentStatus
0.00%
0 / 5
0.00%
0 / 1
 12
1<?php
2
3namespace BuyerKiosk\Workbook;
4
5use PDO;
6use PDOException;
7use DateTime;
8use Exception;
9
10/**
11 * KPIService - Aggregates and calculates KPI data from multiple sources
12 *
13 * Data sources:
14 * - LiveFinancials table (sales, buys goals)
15 * - FinancialsController (WhenIWork labor data)
16 * - buyQueue table (transaction counts)
17 * - bsActions table (backstock metrics)
18 * - closeSalesReport (year-over-year comparisons)
19 */
20class KPIService
21{
22    protected $store;
23    protected $storeDB;
24    protected $redis;
25    protected $cacheEnabled;
26    protected $cacheTTL = 30; // 30 seconds
27
28    /**
29     * Constructor
30     *
31     * @param \Store $store Store object
32     */
33    public function __construct(\Store $store)
34    {
35        $this->store = $store;
36        $this->storeDB = dbConnectByName($this->store->getDbName());
37
38        // Set up Redis caching
39        $this->cacheEnabled = false;
40        if (class_exists('Redis')) {
41            try {
42                $this->redis = new \Redis();
43                $redisHost = getenv('REDIS_HOST') ?: 'localhost';
44                $redisPort = getenv('REDIS_PORT') ?: 6379;
45                $this->cacheEnabled = $this->redis->connect($redisHost, $redisPort);
46            } catch (Exception $e) {
47                error_log("KPIService: Redis connection failed - " . $e->getMessage());
48                $this->cacheEnabled = false;
49            }
50        }
51    }
52
53    /**
54     * Get today's KPIs with caching
55     *
56     * @return array Aggregated KPI data
57     */
58    public function getTodayKPIs(): array
59    {
60        $date = new DateTime('now', new \DateTimeZone($this->store->getTimezone()));
61        $cacheKey = $this->store->getTypeNum() . '_workbook_kpis_' . $date->format('Y-m-d');
62
63        // Try cache first
64        if ($this->cacheEnabled) {
65            try {
66                $cached = $this->redis->get($cacheKey);
67                if ($cached !== false) {
68                    return json_decode($cached, true);
69                }
70            } catch (Exception $e) {
71                error_log("KPIService: Cache read error - " . $e->getMessage());
72            }
73        }
74
75        // Aggregate from all sources
76        $kpis = [];
77
78        try {
79            // Get live financials (sales and buys goals)
80            $financials = $this->getLiveFinancials($date);
81            $kpis['sales'] = $financials['salesCurrent'] ?? 0;
82            $kpis['salesGoal'] = $financials['salesGoal'] ?? 0;
83            $kpis['buys'] = $financials['buysCurrent'] ?? 0;
84            $kpis['buysGoal'] = $financials['buysGoal'] ?? 0;
85
86            // Get transaction count
87            $kpis['transactions'] = $this->getTransactionCount($date);
88
89            // Calculate average transaction
90            $kpis['avgTrans'] = $kpis['transactions'] > 0
91                ? $kpis['sales'] / $kpis['transactions']
92                : 0;
93
94            // Get labor data (if WhenIWork enabled)
95            $laborData = $this->getLaborData();
96            $kpis['laborHours'] = $laborData['hr'] ?? 0;
97            $kpis['laborPercent'] = $laborData['laborPercentRaw'] ?? 0;
98            $kpis['salesPerLaborHour'] = $laborData['salesPerLaborHour'] ?? 0;
99            $kpis['totalWages'] = $laborData['dollars'] ?? 0;
100
101            // Get year-over-year comps
102            $comps = $this->getYearOverYearComps($date);
103            $kpis['comps'] = $comps;
104
105            // Calculate derived KPIs
106            $kpis = $this->calculateDerivedKPIs($kpis);
107
108            // Cache the result
109            if ($this->cacheEnabled) {
110                try {
111                    $this->redis->setex($cacheKey, $this->cacheTTL, json_encode($kpis));
112                } catch (Exception $e) {
113                    error_log("KPIService: Cache write error - " . $e->getMessage());
114                }
115            }
116
117            return $kpis;
118
119        } catch (Exception $e) {
120            error_log("KPIService: Error aggregating KPIs - " . $e->getMessage());
121            return [];
122        }
123    }
124
125    /**
126     * Get display-ready KPIs based on visibility configuration
127     *
128     * @return array Formatted KPI array for API response
129     */
130    public function getDisplayKPIs(): array
131    {
132        $rawKPIs = $this->getTodayKPIs();
133        $config = new KPIConfig($this->store);
134        $visibleKPIs = $config->getVisible();
135
136        $date = new DateTime('now', new \DateTimeZone($this->store->getTimezone()));
137        $displayKPIs = [];
138
139        foreach ($visibleKPIs as $kpiConfig) {
140            $key = $kpiConfig['kpiKey'];
141            $value = $rawKPIs[$key] ?? 0;
142            $goal = $rawKPIs[$key . 'Goal'] ?? null;
143            $comp = $rawKPIs['comps'][$key] ?? null;
144
145            $kpi = [
146                'key' => $key,
147                'name' => $kpiConfig['displayName'],
148                'value' => $value,
149                'formatted' => $this->formatKPIValue($key, $value)
150            ];
151
152            // Add goal if configured
153            if ($kpiConfig['showGoal'] && $goal !== null) {
154                $kpi['goal'] = $goal;
155                $kpi['goalFormatted'] = $this->formatKPIValue($key, $goal);
156                $kpi['status'] = $this->getKPIStatus($value, $goal);
157            } else {
158                $kpi['status'] = 'neutral';
159            }
160
161            // Add year-over-year comp if configured
162            if ($kpiConfig['showComps'] && $comp !== null) {
163                $kpi['comp'] = $comp;
164                $kpi['compFormatted'] = $this->formatKPIValue($key, $comp);
165            }
166
167            $displayKPIs[] = $kpi;
168        }
169
170        return [
171            'success' => true,
172            'date' => $date->format('Y-m-d'),
173            'kpis' => $displayKPIs
174        ];
175    }
176
177    /**
178     * Get live financials data from LiveFinancials table
179     *
180     * @param DateTime $date Date to query
181     * @return array Financial data
182     */
183    protected function getLiveFinancials(DateTime $date): array
184    {
185        try {
186            $stmt = $this->storeDB->prepare("SELECT * FROM LiveFinancials WHERE date = :date");
187            $stmt->execute([':date' => $date->format('Y-m-d')]);
188            $row = $stmt->fetch(PDO::FETCH_ASSOC);
189
190            if ($row) {
191                return $row;
192            }
193
194            return [
195                'salesCurrent' => 0,
196                'salesGoal' => 0,
197                'buysCurrent' => 0,
198                'buysGoal' => 0
199            ];
200        } catch (PDOException $e) {
201            error_log("KPIService: Error fetching LiveFinancials - " . $e->getMessage());
202            return [];
203        }
204    }
205
206    /**
207     * Get labor data from WhenIWork via FinancialsController
208     *
209     * @return array Labor metrics
210     */
211    protected function getLaborData(): array
212    {
213        // Check if WhenIWork is enabled
214        if ($this->store->getWiwEnable() <= 0) {
215            return [
216                'hr' => 0,
217                'dollars' => 0,
218                'laborPercentRaw' => 0,
219                'salesPerLaborHour' => 0
220            ];
221        }
222
223        try {
224            $fc = new \BuyerKiosk\WhenIWork\FinancialsController($GLOBALS['app'], $this->store);
225            $laborTotals = $fc->getLaborTotals();
226
227            // Parse percentage string to raw value
228            $laborPercentRaw = 0;
229            if (isset($laborTotals['laborPercent'])) {
230                $laborPercentRaw = (float) str_replace('%', '', $laborTotals['laborPercent']);
231            }
232
233            return [
234                'hr' => $laborTotals['hr'] ?? 0,
235                'dollars' => $laborTotals['dollars'] ?? 0,
236                'laborPercentRaw' => $laborPercentRaw,
237                'salesPerLaborHour' => $laborTotals['salesPerLaborHour'] ?? 0
238            ];
239        } catch (Exception $e) {
240            error_log("KPIService: Error fetching labor data - " . $e->getMessage());
241            return [
242                'hr' => 0,
243                'dollars' => 0,
244                'laborPercentRaw' => 0,
245                'salesPerLaborHour' => 0
246            ];
247        }
248    }
249
250    /**
251     * Get transaction count from buyQueue
252     *
253     * @param DateTime $date Date to query
254     * @return int Transaction count
255     */
256    protected function getTransactionCount(DateTime $date): int
257    {
258        try {
259            $stmt = $this->storeDB->prepare(
260                "SELECT COUNT(*) as cnt
261                 FROM buyQueue
262                 WHERE DATE(timeCompleted) = :date
263                 AND isProcessed = 1"
264            );
265            $stmt->execute([':date' => $date->format('Y-m-d')]);
266            $row = $stmt->fetch(PDO::FETCH_ASSOC);
267
268            return (int) ($row['cnt'] ?? 0);
269        } catch (PDOException $e) {
270            error_log("KPIService: Error counting transactions - " . $e->getMessage());
271            return 0;
272        }
273    }
274
275    /**
276     * Get buys data from buyQueue
277     *
278     * @param DateTime $date Date to query
279     * @return array Buys metrics
280     */
281    protected function getBuysData(DateTime $date): array
282    {
283        try {
284            // Get completed buys count from buyQueue
285            $stmt = $this->storeDB->prepare(
286                "SELECT COUNT(*) as count
287                 FROM buyQueue
288                 WHERE DATE(timeCompleted) = :date
289                 AND isProcessed = 1"
290            );
291            $stmt->execute([':date' => $date->format('Y-m-d')]);
292            $row = $stmt->fetch(PDO::FETCH_ASSOC);
293
294            // Get buy total from LiveFinancials (more accurate)
295            $buyTotal = $this->getLiveFinancials($date)['buysCurrent'] ?? 0;
296
297            return [
298                'count' => (int) ($row['count'] ?? 0),
299                'total' => (float) $buyTotal
300            ];
301        } catch (PDOException $e) {
302            error_log("KPIService: Error fetching buys data - " . $e->getMessage());
303            return ['count' => 0, 'total' => 0];
304        }
305    }
306
307    /**
308     * Get backstock data from bsActions table
309     *
310     * @param DateTime $date Date to query
311     * @return array Backstock metrics
312     */
313    protected function getBackstockData(DateTime $date): array
314    {
315        try {
316            $stmt = $this->storeDB->prepare(
317                "SELECT COUNT(*) as count
318                 FROM bsActions
319                 WHERE DATE(date) = :date"
320            );
321            $stmt->execute([':date' => $date->format('Y-m-d')]);
322            $row = $stmt->fetch(PDO::FETCH_ASSOC);
323
324            return [
325                'count' => (int) ($row['count'] ?? 0)
326            ];
327        } catch (PDOException $e) {
328            error_log("KPIService: Error fetching backstock data - " . $e->getMessage());
329            return ['count' => 0];
330        }
331    }
332
333    /**
334     * Get year-over-year comparison data from closeSalesReport
335     *
336     * @param DateTime $date Current date
337     * @return array Comparison metrics
338     */
339    protected function getYearOverYearComps(DateTime $date): array
340    {
341        try {
342            $lastYear = clone $date;
343            $lastYear->modify('-1 year');
344
345            $stmt = $this->storeDB->prepare(
346                "SELECT netSalesRetail, buysCost, averageRetail, salesCount
347                 FROM closeSalesReport
348                 WHERE date = :date"
349            );
350            $stmt->execute([':date' => $lastYear->format('Y-m-d')]);
351            $row = $stmt->fetch(PDO::FETCH_ASSOC);
352
353            if ($row) {
354                return [
355                    'sales' => (float) ($row['netSalesRetail'] ?? 0),
356                    'buys' => (float) ($row['buysCost'] ?? 0),
357                    'avgTrans' => (float) ($row['averageRetail'] ?? 0),
358                    'transactions' => (int) ($row['salesCount'] ?? 0)
359                ];
360            }
361
362            return [];
363        } catch (PDOException $e) {
364            error_log("KPIService: Error fetching YoY comps - " . $e->getMessage());
365            return [];
366        }
367    }
368
369    /**
370     * Calculate derived KPI values
371     *
372     * @param array $kpis Raw KPI data
373     * @return array KPIs with calculated values
374     */
375    protected function calculateDerivedKPIs(array $kpis): array
376    {
377        // Trade Percent = (buys / sales) * 100
378        if ($kpis['sales'] > 0) {
379            $kpis['tradePercent'] = ($kpis['buys'] / $kpis['sales']) * 100;
380        } else {
381            $kpis['tradePercent'] = 0;
382        }
383
384        return $kpis;
385    }
386
387    /**
388     * Format KPI value for display
389     *
390     * @param string $key KPI key
391     * @param mixed $value Raw value
392     * @return string Formatted value
393     */
394    protected function formatKPIValue(string $key, $value): string
395    {
396        // Currency values
397        if (in_array($key, ['sales', 'buys', 'avgTrans', 'totalWages', 'salesPerLaborHour'])) {
398            return '$' . number_format($value, 2);
399        }
400
401        // Percentage values
402        if (in_array($key, ['tradePercent', 'laborPercent'])) {
403            return number_format($value, 1) . '%';
404        }
405
406        // Hours
407        if ($key === 'laborHours') {
408            return number_format($value, 1) . ' hrs';
409        }
410
411        // Integer values
412        if (in_array($key, ['transactions', 'buysCount'])) {
413            return (string) (int) $value;
414        }
415
416        // Default: 2 decimal places
417        return number_format($value, 2);
418    }
419
420    /**
421     * Determine KPI status based on goal achievement
422     *
423     * @param float $value Current value
424     * @param float $goal Goal value
425     * @return string Status: 'good', 'warning', 'danger', 'neutral'
426     */
427    protected function getKPIStatus($value, $goal): string
428    {
429        if ($goal === null || $goal <= 0) {
430            return 'neutral';
431        }
432
433        $percentage = ($value / $goal);
434
435        if ($percentage >= 1.0) {
436            return 'good';
437        } elseif ($percentage >= 0.9) {
438            return 'warning';
439        } else {
440            return 'danger';
441        }
442    }
443
444    /**
445     * Get detailed KPI data for expanded view
446     *
447     * @return array Detailed KPI data including backstock and buys breakdown
448     */
449    public function getDetailedKPIs(): array
450    {
451        $date = new DateTime('now', new \DateTimeZone($this->store->getTimezone()));
452        $rawKPIs = $this->getTodayKPIs();
453        $comps = $rawKPIs['comps'] ?? [];
454
455        // Calculate variance from goal
456        $salesVariance = 0;
457        if (isset($rawKPIs['salesGoal']) && $rawKPIs['salesGoal'] > 0) {
458            $salesVariance = $rawKPIs['sales'] - $rawKPIs['salesGoal'];
459        }
460
461        // Calculate comp trade %
462        $compTradePercent = 0;
463        if (!empty($comps['sales']) && $comps['sales'] > 0 && !empty($comps['buys'])) {
464            $compTradePercent = ($comps['buys'] / $comps['sales']) * 100;
465        }
466
467        // Get detailed backstock data
468        $backstockDetailed = $this->getDetailedBackstockData($date);
469
470        // Get detailed buys data
471        $buysDetailed = $this->getDetailedBuysData($date, $comps);
472
473        // Build structured response matching the UI design
474        return [
475            'success' => true,
476            'date' => $date->format('Y-m-d'),
477
478            // Main KPIs with goal/comp (top row when expanded)
479            'sales' => [
480                'current' => $rawKPIs['sales'] ?? 0,
481                'formatted' => '$' . number_format($rawKPIs['sales'] ?? 0, 2),
482                'goal' => $rawKPIs['salesGoal'] ?? 0,
483                'goalFormatted' => '$' . number_format($rawKPIs['salesGoal'] ?? 0, 2),
484                'variance' => $salesVariance,
485                'varianceFormatted' => ($salesVariance >= 0 ? '+' : '-') . '$' . number_format(abs($salesVariance), 2),
486                'comp' => $comps['sales'] ?? 0,
487                'compFormatted' => '$' . number_format($comps['sales'] ?? 0, 2),
488                'status' => $this->getKPIStatus($rawKPIs['sales'] ?? 0, $rawKPIs['salesGoal'] ?? 0)
489            ],
490
491            'avgTrans' => [
492                'current' => $rawKPIs['avgTrans'] ?? 0,
493                'formatted' => '$' . number_format($rawKPIs['avgTrans'] ?? 0, 2),
494                'goal' => 0, // No goal for avg trans typically
495                'goalFormatted' => '--',
496                'comp' => $comps['avgTrans'] ?? 0,
497                'compFormatted' => '$' . number_format($comps['avgTrans'] ?? 0, 2),
498                'status' => 'neutral'
499            ],
500
501            'tradePercent' => [
502                'current' => $rawKPIs['tradePercent'] ?? 0,
503                'formatted' => number_format($rawKPIs['tradePercent'] ?? 0, 2) . '%',
504                'goal' => 15, // Typical trade % goal
505                'goalFormatted' => '15.00%',
506                'comp' => $compTradePercent,
507                'compFormatted' => number_format($compTradePercent, 2) . '%',
508                'status' => $this->getTradePercentStatus($rawKPIs['tradePercent'] ?? 0)
509            ],
510
511            // Sales & Labor section
512            'salesLabor' => [
513                'transactions' => $rawKPIs['transactions'] ?? 0,
514                'laborHours' => $rawKPIs['laborHours'] ?? 0,
515                'laborHoursFormatted' => $rawKPIs['laborHours'] > 0 ? number_format($rawKPIs['laborHours'], 1) : '--',
516                'salesPerLaborHour' => $rawKPIs['salesPerLaborHour'] ?? 0,
517                'salesPerLaborHourFormatted' => $rawKPIs['salesPerLaborHour'] > 0 ? '$' . number_format($rawKPIs['salesPerLaborHour'], 2) : '--',
518                'totalWages' => $rawKPIs['totalWages'] ?? 0,
519                'totalWagesFormatted' => $rawKPIs['totalWages'] > 0 ? '$' . number_format($rawKPIs['totalWages'], 2) : '--'
520            ],
521
522            // Backstock section
523            'backstock' => $backstockDetailed,
524
525            // Buys section
526            'buys' => $buysDetailed
527        ];
528    }
529
530    /**
531     * Get detailed backstock data for today
532     *
533     * @param DateTime $date Date to query
534     * @return array Backstock metrics and recent activity
535     */
536    protected function getDetailedBackstockData(DateTime $date): array
537    {
538        try {
539            // Get counts by action type
540            // Action types: 0=empty, 1=add, 2=remove, 3=remove all of category
541            $stmt = $this->storeDB->prepare(
542                "SELECT action, COUNT(*) as count
543                 FROM bsActions
544                 WHERE DATE(timePerformed) = :date
545                 GROUP BY action"
546            );
547            $stmt->execute([':date' => $date->format('Y-m-d')]);
548            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
549
550            $pulled = 0;
551            $added = 0;
552            foreach ($rows as $row) {
553                switch ((int)$row['action']) {
554                    case 1: // add
555                        $added += (int)$row['count'];
556                        break;
557                    case 2: // remove some
558                    case 3: // remove all of category
559                    case 0: // empty bin
560                        $pulled += (int)$row['count'];
561                        break;
562                }
563            }
564
565            // Get recent activity (last 10 actions today)
566            $recentActivity = $this->getRecentBackstockActivity($date, 10);
567
568            return [
569                'pulled' => $pulled,
570                'added' => $added,
571                'recentActivity' => $recentActivity
572            ];
573        } catch (PDOException $e) {
574            error_log("KPIService: Error fetching detailed backstock data - " . $e->getMessage());
575            return [
576                'pulled' => 0,
577                'added' => 0,
578                'recentActivity' => []
579            ];
580        }
581    }
582
583    /**
584     * Get recent backstock activity
585     *
586     * @param DateTime $date Date to query
587     * @param int $limit Number of items to return
588     * @return array Recent backstock actions
589     */
590    protected function getRecentBackstockActivity(DateTime $date, int $limit = 10): array
591    {
592        try {
593            $stmt = $this->storeDB->prepare(
594                "SELECT a.id, a.binID, a.action, a.categoryID, a.timePerformed,
595                        b.name as binName,
596                        c.name as categoryName
597                 FROM bsActions a
598                 LEFT JOIN bsBins b ON a.binID = b.id
599                 LEFT JOIN bsCategories c ON a.categoryID = c.id
600                 WHERE DATE(a.timePerformed) = :date
601                 ORDER BY a.timePerformed DESC
602                 LIMIT :limit"
603            );
604            $stmt->bindValue(':date', $date->format('Y-m-d'));
605            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
606            $stmt->execute();
607            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
608
609            $activity = [];
610            foreach ($rows as $row) {
611                $actionType = 'Other';
612                switch ((int)$row['action']) {
613                    case 0: $actionType = 'Emptied'; break;
614                    case 1: $actionType = 'New Product'; break;
615                    case 2: $actionType = 'Pulled'; break;
616                    case 3: $actionType = 'Cleared Category'; break;
617                }
618
619                $time = new DateTime($row['timePerformed'], new \DateTimeZone('UTC'));
620                $time->setTimezone(new \DateTimeZone($this->store->getTimezone()));
621
622                $activity[] = [
623                    'time' => $time->format('H:i'),
624                    'bin' => $row['binName'] ?? 'Unknown',
625                    'action' => $actionType,
626                    'category' => $row['categoryName'] ?? ''
627                ];
628            }
629
630            return $activity;
631        } catch (PDOException $e) {
632            error_log("KPIService: Error fetching recent backstock activity - " . $e->getMessage());
633            return [];
634        }
635    }
636
637    /**
638     * Get detailed buys data
639     *
640     * @param DateTime $date Date to query
641     * @param array $comps Year-over-year comparison data
642     * @return array Buys metrics
643     */
644    protected function getDetailedBuysData(DateTime $date, array $comps): array
645    {
646        try {
647            // Get completed buys count from buyQueue
648            $stmt = $this->storeDB->prepare(
649                "SELECT COUNT(*) as buysCount
650                 FROM buyQueue
651                 WHERE DATE(timeCompleted) = :date
652                 AND isProcessed = 1"
653            );
654            $stmt->execute([':date' => $date->format('Y-m-d')]);
655            $row = $stmt->fetch(PDO::FETCH_ASSOC);
656
657            $buysCount = (int)($row['buysCount'] ?? 0);
658
659            // Get buy cost from LiveFinancials
660            $financials = $this->getLiveFinancials($date);
661            $buysCost = (float)($financials['buysCurrent'] ?? 0);
662
663            // Trade data typically comes from LiveFinancials or a separate trades table
664            // For now, we'll estimate based on typical trade percentage
665            $tradeCount = 0;
666            $tradeTotal = 0;
667            $percentTraded = 0;
668
669            // Comp data
670            $compsAvailable = !empty($comps);
671            $compBuys = $comps['buys'] ?? 0;
672            $compTradePercent = 0;
673
674            return [
675                'count' => $buysCount,
676                'countFormatted' => (string)$buysCount,
677                'cost' => $buysCost,
678                'costFormatted' => '$' . number_format($buysCost, 2),
679                'tradeCount' => $tradeCount,
680                'tradeTotal' => $tradeTotal,
681                'tradeTotalFormatted' => '$' . number_format($tradeTotal, 2),
682                'percentTraded' => $percentTraded,
683                'percentTradedFormatted' => number_format($percentTraded, 2) . '%',
684                'compBuys' => $compBuys,
685                'compBuysFormatted' => '$' . number_format($compBuys, 2),
686                'compTradePercent' => $compTradePercent,
687                'compTradePercentFormatted' => number_format($compTradePercent, 2) . '%'
688            ];
689        } catch (PDOException $e) {
690            error_log("KPIService: Error fetching detailed buys data - " . $e->getMessage());
691            return [
692                'count' => 0,
693                'countFormatted' => '0',
694                'cost' => 0,
695                'costFormatted' => '$0.00',
696                'tradeCount' => 0,
697                'tradeTotal' => 0,
698                'tradeTotalFormatted' => '$0.00',
699                'percentTraded' => 0,
700                'percentTradedFormatted' => '0.00%',
701                'compBuys' => 0,
702                'compBuysFormatted' => '$0.00',
703                'compTradePercent' => 0,
704                'compTradePercentFormatted' => '0.00%'
705            ];
706        }
707    }
708
709    /**
710     * Get trade percent status (green if under goal, red if over)
711     * Trade % is better when lower (buying less relative to sales)
712     *
713     * @param float $tradePercent Current trade percent
714     * @return string Status
715     */
716    protected function getTradePercentStatus(float $tradePercent): string
717    {
718        if ($tradePercent <= 12) {
719            return 'good';
720        } elseif ($tradePercent <= 15) {
721            return 'warning';
722        } else {
723            return 'danger';
724        }
725    }
726}