Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 322
0.00%
0 / 18
 CRAP
0.00%
0 / 1
DashboardStats
0.00%
0 / 322
0.00%
0 / 18
 6972
0.00%
0 / 1
 __construct
0.00%
0 / 3
0.00%
0 / 1
 2
 setDates
0.00%
0 / 2
0.00%
0 / 1
 2
 getTopStats
0.00%
0 / 3
0.00%
0 / 1
 30
 getNewCustomerData
0.00%
0 / 23
0.00%
0 / 1
 42
 getReturnCustomerData
0.00%
0 / 21
0.00%
0 / 1
 20
 getSMSClubData
0.00%
0 / 25
0.00%
0 / 1
 20
 getEmailClubData
0.00%
0 / 26
0.00%
0 / 1
 42
 getBusyTimesArray
0.00%
0 / 24
0.00%
0 / 1
 90
 getBusyBuyDays
0.00%
0 / 37
0.00%
0 / 1
 56
 getCustomerData
0.00%
0 / 73
0.00%
0 / 1
 306
 getAverageInStoreWait
0.00%
0 / 7
0.00%
0 / 1
 6
 getCustReturnAverages
0.00%
0 / 24
0.00%
0 / 1
 42
 getTextPercentages
0.00%
0 / 3
0.00%
0 / 1
 2
 getMinMaxTime
0.00%
0 / 18
0.00%
0 / 1
 20
 getSortAndProcessAverages
0.00%
0 / 12
0.00%
0 / 1
 6
 getSMSResults
0.00%
0 / 10
0.00%
0 / 1
 12
 getEmailResults
0.00%
0 / 10
0.00%
0 / 1
 12
 checkTimesSet
0.00%
0 / 1
0.00%
0 / 1
 6
1<?php
2
3namespace BuyerKiosk\Core;
4
5class DashboardStats extends Store {
6
7    public $newCustomers;
8    public $newCustomersTrend;
9    public $returnCustomers;
10    public $returnCustomersTrend;
11    public $smsClub;
12    public $smsClubTrend;
13    public $emailClub;
14    public $emailClubTrend;
15    private $db;
16    private $dateStart;
17    private $dateEnd;
18
19    public function __construct($typeNum) {
20        parent::__construct();
21        $this->createStore($typeNum);
22        $this->db = dbConnectByName($this->getDbName());
23    }
24    public function setDates($dateStart, $dateEnd) {
25        $this->dateStart = getDateRange($dateStart);
26        $this->dateEnd = getDateRange($dateEnd);
27    }
28
29    public function getTopStats() {
30        if($this->getNewCustomerData() && $this->getReturnCustomerData() && $this->getSMSClubData() && $this->getEmailClubData()) {
31            return true;
32        }
33        return false;
34    }
35    public function getNewCustomerData() {
36        if($this->checkTimesSet()) {
37            //Get number of customers for current date range
38            $query = "SELECT COUNT(*) FROM customers WHERE dateAdded BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."'";
39
40            $dateStart = getDateRange($this->dateStart['dateStart']->format("Y-m-d H:i:s"));
41            $dateEnd = getDateRange($this->dateEnd['dateEnd']->format("Y-m-d H:i:s"));
42            //Get the dates for the month prior to this date range
43            $pMonthStart = getDateRange($dateStart['dateStart']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
44            $pMonthEnd = getDateRange($dateEnd['dateEnd']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
45
46            //Number of customers for the month prior
47            $pQuery = "SELECT COUNT(*) FROM customers WHERE dateAdded BETWEEN '".$pMonthStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$pMonthEnd['dateEnd']->format("Y-m-d H:i:s")."'";
48
49            $ncQuery = $this->db->query($query);
50
51            $pmQuery = $this->db->query($pQuery);
52
53            if($ncQuery && $pmQuery) {
54                $row = $ncQuery->fetch(\PDO::FETCH_ASSOC);
55                $this->newCustomers = $row['COUNT(*)'];
56
57                $row = $pmQuery->fetch(\PDO::FETCH_ASSOC);
58                $pCustomers = $row['COUNT(*)'];
59
60                //Check which direction we are trending in
61                if($this->newCustomers > $pCustomers) {
62                    $trend = round(1 - ($pCustomers / $this->newCustomers), 2)*100;
63                    $this->newCustomersTrend = "+".$trend;
64                } else {
65                    if($pCustomers > 0) {
66                        $trend = round(1 - ($this->newCustomers / $pCustomers),2)*100;
67                        $this->newCustomersTrend = "-".$trend;
68                    }
69                }
70            } else {
71                return false;
72            }
73        } else {
74            return false;
75        }
76        return true;
77    }
78    public function getReturnCustomerData() {
79        if($this->checkTimesSet()) {
80            //Get the number of returning customers from the buyQueue table
81            $query = "SELECT COUNT(DISTINCT customerID) as count  FROM buyQueue WHERE timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."' AND isNewCustomer = 0";
82
83            //Get the dates for the month prior to this date range
84            $dateStart = getDateRange($this->dateStart['dateStart']->format("Y-m-d H:i:s"));
85            $dateEnd = getDateRange($this->dateEnd['dateEnd']->format("Y-m-d H:i:s"));
86            //Get the dates for the month prior to this date range
87            $pMonthStart = getDateRange($dateStart['dateStart']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
88            $pMonthEnd = getDateRange($dateEnd['dateEnd']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
89
90            //Query for the number of return customers in the month prior
91            $pQuery = "SELECT COUNT(DISTINCT customerID) as count  FROM buyQueue WHERE timeEntered BETWEEN '".$pMonthStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$pMonthEnd['dateEnd']->format("Y-m-d H:i:s")."' AND isNewCustomer = 0";
92
93            $rcQuery = $this->db->query($query);
94
95            $pmQuery = $this->db->query($pQuery);
96
97                $row = $rcQuery->fetch(\PDO::FETCH_ASSOC);
98                $this->returnCustomers = $row['count'];
99
100                $row = $pmQuery->fetch(\PDO::FETCH_ASSOC);
101                $pCustomers = $row['count'];
102
103                //check which direction we are trending in
104                if($this->returnCustomers > $pCustomers) {
105                    $trend = round(1 - ($pCustomers / $this->returnCustomers), 2)*100;
106                    $this->returnCustomersTrend = "+".$trend;
107                } else {
108                    if($pCustomers > 0) {
109                        $trend = round(1 - ($this->returnCustomers / $pCustomers),2)*100;
110                        $this->returnCustomersTrend = "-".$trend;
111                    }
112
113                }
114
115        } else {
116            return false;
117
118        }
119        return true;
120    }
121    public function getSMSClubData() {
122        if($this->checkTimesSet()) {
123            $log = new \KLogger($_ENV['LOG_DIR']."dev_log2.txt",\KLogger::DEBUG);
124            //Get the number of returning customers from the buyQueue table
125            $log->LogDebug($this->dateStart['dateStart']->format("Y-m-d H:i:s"));
126            $log->LogDebug($this->dateEnd['dateEnd']->format("Y-m-d H:i:s"));
127            $query = "SELECT COUNT(*) FROM customers WHERE (dateAdded BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."') AND onSMS = 1";
128
129            $dateStart = getDateRange($this->dateStart['dateStart']->format("Y-m-d H:i:s"));
130            $dateEnd = getDateRange($this->dateEnd['dateEnd']->format("Y-m-d H:i:s"));
131            //Get the dates for the month prior to this date range
132            $pMonthStart = getDateRange($dateStart['dateStart']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
133            $pMonthEnd = getDateRange($dateEnd['dateEnd']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
134
135            //Query for the number of return customers in the month prior
136            $pQuery = "SELECT COUNT(*) FROM customers WHERE dateAdded BETWEEN '".$pMonthStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$pMonthEnd['dateEnd']->format("Y-m-d H:i:s")."' AND onSMS = 1";
137
138            $log->LogDebug($query);
139            $smsQuery = $this->db->query($query);
140
141            $pmQuery = $this->db->query($pQuery);
142
143
144            $row = $smsQuery->fetch(\PDO::FETCH_ASSOC);
145            $this->smsClub = $row['COUNT(*)'];
146
147            $row = $pmQuery->fetch(\PDO::FETCH_ASSOC);
148            $pCustomers = $row['COUNT(*)'];
149
150            //check which direction we are trending in
151            if($this->smsClub > $pCustomers) {
152                $trend = round(1 - ($pCustomers / $this->smsClub), 2)*100;
153                $this->smsClubTrend = "+".$trend;
154            } else {
155                if($pCustomers > 0) {
156                    $trend = round(1 - ($this->smsClub / $pCustomers), 2) * 100;
157                    $this->smsClubTrend = "-" . $trend;
158                }
159            }
160        } else {
161            return false;
162
163        }
164        return true;
165    }
166    public function getEmailClubData() {
167        if($this->checkTimesSet()) {
168            $log = new \KLogger($_ENV['LOG_DIR']."dev_log2.txt",\KLogger::DEBUG);
169            //Get the number of returning customers from the buyQueue table
170            $log->LogDebug("Email: ".$this->dateStart['dateStart']->format("Y-m-d H:i:s"));
171            $log->LogDebug("Email: ".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s"));
172            //Get the number of returning customers from the buyQueue table
173            $query = "SELECT COUNT(*) FROM customers WHERE dateAdded BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."' AND onEmail = 1";
174
175            $dateStart = getDateRange($this->dateStart['dateStart']->format("Y-m-d H:i:s"));
176            $dateEnd = getDateRange($this->dateEnd['dateEnd']->format("Y-m-d H:i:s"));
177            //Get the dates for the month prior to this date range
178            $pMonthStart = getDateRange($dateStart['dateStart']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
179            $pMonthEnd = getDateRange($dateEnd['dateEnd']->sub(new \DateInterval('P1M'))->format("Y-m-d H:i:s"));
180
181            //Query for the number of return customers in the month prior
182            $pQuery = "SELECT COUNT(*) FROM customers WHERE dateAdded BETWEEN '".$pMonthStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$pMonthEnd['dateEnd']->format("Y-m-d H:i:s")."' AND onEmail = 1";
183
184            $emailQuery = $this->db->query($query);
185
186            $pmQuery = $this->db->query($pQuery);
187
188            if($emailQuery && $pmQuery) {
189                $row = $emailQuery->fetch(\PDO::FETCH_ASSOC);
190                $this->emailClub= $row['COUNT(*)'];
191
192                $row = $pmQuery->fetch(\PDO::FETCH_ASSOC);
193                $pCustomers = $row['COUNT(*)'];
194
195                //check which direction we are trending in
196                if($this->emailClub > $pCustomers) {
197                    $trend = round(1 - ($pCustomers / $this->emailClub), 2)*100;
198                    $this->emailClubTrend = "+".$trend;
199                } else {
200                    if($pCustomers > 0) {
201                        $trend = round(1 - ($this->emailClub/ $pCustomers),2)*100;
202                        $this->emailClubTrend = "-".$trend;
203                    }
204
205                }
206            } else {
207                return false;
208            }
209        } else {
210            return false;
211
212        }
213        return true;
214    }
215    public function getBusyTimesArray() {
216        $log = new \KLogger($_ENV['LOG_DIR']."dev_log.txt",\KLogger::DEBUG);
217        $timesArray = [];
218        if($this->checkTimesSet()) {
219            $diff = $this->dateStart['dateStart']->diff($this->dateEnd['dateEnd']);
220            $numDays = (int)$diff->format("%a");
221
222            /*$timesArray['numDays'] = $numDays;
223            $timesArray['dateStart'] = $this->dateStart['dateStart']->format("Y-m-d H:i:s");
224            $timesArray['dateEnd'] = $this->dateEnd['dateEnd']->format("Y-m-d H:i:s");*/
225
226            //Build our array with the first time being our minimum time and max time being the maximum time there was a buy in any given day in this period
227            $minMax = $this->getMinMaxTime();
228            for($i = 0; $i <= 23; $i++) {
229                $timesArray[$i] = 0;
230            }
231
232            $dayCount = 0;
233            $query = $this->db->query("SELECT timeEntered, buyID FROM buyQueue WHERE timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."'");
234            while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
235                $timeEntered = new \DateTime($row['timeEntered'], new \DateTimeZone('utc'));
236                $timeEntered->setTimezone(new \DateTimeZone($this->getTimeZone()));
237                $thisHour = (int)$timeEntered->format("H");
238                $thisDay = $timeEntered->format("Y-m-d");
239                if(!isset($currentDay) || $currentDay !== $thisDay) {
240                    $currentDay = $thisDay;
241                    $dayCount++;
242                }
243
244
245                if(isset($timesArray[$thisHour])) {
246                    $timesArray[$thisHour]++;
247                }
248            }
249
250            //$log->LogDebug("Day Count: ".$dayCount." - ".$timesArray[17]);
251            for($i = 0; $i <= 23; $i++) {
252                if($dayCount > 0) {
253                    $timesArray[$i] = round($timesArray[$i]/$dayCount,1);
254                }
255
256            }
257        }
258        return $timesArray;
259
260    }
261    public function getBusyBuyDays() {
262        $log = new \KLogger($_ENV['LOG_DIR']."dev_log.txt",\KLogger::DEBUG);
263        $daysArray = [
264            'Mon' => 0,
265            'Tue' => 0,
266            'Wed' => 0,
267            'Thu' => 0,
268            'Fri' => 0,
269            'Sat' => 0,
270            'Sun' => 0,
271        ];
272        $dayOccurrencesArray = [
273            'Mon' => 0,
274            'Tue' => 0,
275            'Wed' => 0,
276            'Thu' => 0,
277            'Fri' => 0,
278            'Sat' => 0,
279            'Sun' => 0,
280        ];
281        $lastDayofWeek = null;
282        if($this->checkTimesSet()) {
283            $diff = $this->dateStart['dateStart']->diff($this->dateEnd['dateEnd']);
284            $numDays = (int)$diff->format("%a");
285
286            $query = $this->db->query("SELECT timeEntered FROM buyQueue WHERE timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."' ORDER BY timeEntered DESC");
287            while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
288
289                //$log->LogDebug($row['timeEntered']);
290                $timeEntered = new \DateTime($row['timeEntered']);
291                $thisDay = $timeEntered->format("D");
292                if(!isset($lastDayofWeek)) {
293                    $lastDayofWeek = $thisDay;
294                }
295                if($thisDay !== $lastDayofWeek) {
296                    $dayOccurrencesArray[$thisDay]++;
297                }
298               // $log->LogDebug($thisDay);
299                $daysArray[$thisDay]++;
300                $lastDayofWeek = $thisDay;
301            }
302            foreach ($daysArray as $key => $value) {
303                if($dayOccurrencesArray[$key] > 0) {
304                    $daysArray[$key] = round($value/$dayOccurrencesArray[$key],1);
305                }
306
307            }
308        }
309        return $daysArray;
310    }
311    public function getCustomerData() {
312        $log = new \KLogger($_ENV['LOG_DIR']."dev_log2.txt",\KLogger::DEBUG);
313        if($this->checkTimesSet()) {
314            $query = "SELECT buyQueue.customerID, customers.onSMS, customers.onEmail,buyQueue.twilioSent,buyQueue.isNewCustomer  FROM buyQueue JOIN customers ON buyQueue.customerID = customers.customerID WHERE timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."' ORDER BY timeEntered ASC";
315            $log->LogDebug($query);
316            // Debug: Check if query string is empty before execution
317            if (empty(trim($query))) {
318                error_log("EMPTY QUERY STRING DETECTED in DashboardStats::getCustomerData() before execution");
319                error_log("Stack trace: " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS), true));
320                return false;
321            }
322            $queryResult = $this->db->query($query);
323            $newCustomerCount = 0;
324            $oldCustomerCount = 0;
325            $newCustomerIDArray = [];
326            $oldCustomerIDArray = [];
327            $newCustSMS = 0;
328            $newCustEmail = 0;
329            $newCustText = 0;
330            $oldCustSMS = 0;
331            $oldCustEmail = 0;
332            $oldCustText = 0;
333            $newSMSPercent=0;$newEmailPercent=0;$newTextPercent=0;
334            $oldSMSPercent=0;$oldEmailPercent=0;$oldTextPercent=0;
335            while ($row = $queryResult->fetch(\PDO::FETCH_ASSOC)) {
336                if($row['isNewCustomer']) {
337                    $newCustomerIDArray[$row['customerID']] = 1;
338                    $newCustomerCount++;
339                    if($row['onSMS']) {
340                        $newCustSMS++;
341                    }
342                    if($row['onEmail']) {
343                        $newCustEmail++;
344                    }
345                    if($row['twilioSent']) {
346                        $newCustText++;
347                    }
348                } else {
349                    if(!in_array($row['customerID'], $oldCustomerIDArray)) {
350                        $oldCustomerCount++;
351                        $oldCustomerIDArray[$row['customerID']] = 1;
352                        if($row['onSMS'] == 1) {
353                            $oldCustSMS++;
354                        }
355                        if($row['onEmail'] == 1) {
356                            $oldCustEmail++;
357                        }
358                        if($row['twilioSent'] == 1) {
359                            $oldCustText++;
360                        }
361                    } else {
362                        $oldCustomerCount++;
363                        if (isset($oldCustomerIDArray[$row['customerID']])) {
364                            $oldCustomerIDArray[$row['customerID']]++;
365                        } else {
366                            $oldCustomerIDArray[$row['customerID']] = 1;
367                        }
368                        if($row['twilioSent'] == 1) {
369                            $oldCustText++;
370                        }
371                    }
372
373                }
374            }
375            $uniqueOldCustomers = count($oldCustomerIDArray);
376            $uniqueNewCustomers = count($newCustomerIDArray);
377
378            if($uniqueNewCustomers > 0) {
379                $newSMSPercent = round($newCustSMS/$uniqueNewCustomers*100);
380                $newEmailPercent = round($newCustEmail/$uniqueNewCustomers*100);
381                $newTextPercent = round($newCustText/$newCustomerCount*100);
382            }
383            if($uniqueOldCustomers > 0) {
384                $oldSMSPercent = round($oldCustSMS/$uniqueOldCustomers*100);
385                $oldEmailPercent = round($oldCustEmail/$uniqueOldCustomers*100);
386                $oldTextPercent = round($oldCustText/$oldCustomerCount*100);
387            }
388            $averageInStoreWait = $this->getAverageInStoreWait();
389            $txtPercentages = $this->getTextPercentages();
390
391            $temp = [];
392            $temp['newCustomers'] = $uniqueNewCustomers;
393            $temp['oldCustomers'] = $uniqueOldCustomers;
394            $temp['newSMS'] = $newSMSPercent;
395            $temp['newEmail'] = $newEmailPercent;
396            $temp['newText'] = $newTextPercent;
397            $temp['oldSMS'] = $oldSMSPercent;
398            $temp['oldEmail'] = $oldEmailPercent;
399            $temp['oldText'] = $oldTextPercent;
400            $temp['inStoreWait'] = $averageInStoreWait;
401            $temp['visitData'] = $this->getCustReturnAverages();
402            if($txtPercentages['totalBuys'] > 0) {
403                $temp['textPercent'] = round(($txtPercentages['textsSent']/$txtPercentages['totalBuys'])*100);
404            } else {
405                $temp['textPercent'] = 0;
406
407            }
408
409            return $temp;
410        }
411    }
412    public function getAverageInStoreWait() {
413        $query = $this->db->query("SELECT SUM( UNIX_TIMESTAMP( timeCompleted ) - UNIX_TIMESTAMP( timeEntered ) ) AS inStoreTime, COUNT( * ) AS total
414                                    FROM buyQueue
415                                    WHERE inStore =1
416                                    AND timeCompleted >  '0000-00-00 00:00:00'
417                                    AND timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."'
418                                    ");
419        $row = $query->fetch(\PDO::FETCH_ASSOC);
420        if($row['total'] > 0) {
421            return secondsToReadable(round($row['inStoreTime']/$row['total']));
422        } else {
423            return 0;
424        }
425
426    }
427    public function getCustReturnAverages() {
428        if($this->checkTimesSet()) {
429            $query = $this->db->query("SELECT COUNT(*) AS count FROM buyQueue");
430            $row = $query->fetch(\PDO::FETCH_ASSOC);
431            $totalBuys = (int)$row['count'];
432            $query = $this->db->query("SELECT COUNT(*) AS count FROM customers");
433            $row = $query->fetch(\PDO::FETCH_ASSOC);
434            $totalCustomers = (int)$row['count'];
435            $query = $this->db->query("SELECT MIN(DATE_FORMAT(timeEntered, '%H')) AS minTime, MAX(DATE_FORMAT(timeEntered, '%H')) AS maxTime, count(*) AS count
436                          FROM buyQueue
437                          WHERE timeEntered
438                          BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."'
439                          AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."'");
440
441            if ($query === false) {
442                // Handle the error - log it or return default values
443                $minMax['min'] = 0;
444                $minMax['max'] = 23;
445                return $minMax;
446            }
447            $totalReturnVisits=0;$count=0;
448            while($row=$query->fetch(\PDO::FETCH_ASSOC)) {
449                $totalReturnVisits += (int)$row['count'];
450                $count++;
451            }
452
453            if($count > 0 && $totalCustomers > 0) {
454                $temp['returnAvgVisits'] = round($totalReturnVisits/$count,2);
455                $temp['avgVisits'] = round($totalBuys/$totalCustomers,2);
456            } else {
457                $temp['returnAvgVisits'] =0;
458                $temp['avgVisits'] = 0;
459            }
460
461
462
463        }
464        return $temp;
465    }
466    public function getTextPercentages() {
467        $query = $this->db->query("SELECT (
468                                    SELECT COUNT( * )
469                                    FROM buyQueue
470                                    ) AS totalBuys, (
471                                     SELECT COUNT( * )
472                                    FROM buyQueue
473                                    WHERE textMe =1
474                                    ) AS textsSent");
475        return $query->fetch(\PDO::FETCH_ASSOC);
476    }
477    public function getMinMaxTime() {
478        $minMax = [];
479        if($this->checkTimesSet()) {
480            $query = $this->db->query(" SELECT MIN( DATE_FORMAT( timeEntered,  '%H' ) ) AS minTime, MAX( DATE_FORMAT( timeEntered,  '%H' ) ) AS maxTime
481                                    FROM buyQueue
482                                    WHERE timeEntered
483                                    BETWEEN  '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."'
484                                    AND  '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."'
485                                    ");
486
487            // Check if query was successful
488            if($query !== false) {
489                $row = $query->fetch(\PDO::FETCH_ASSOC);
490
491                // Check if row contains data
492                if($row) {
493                    $minMax['min'] = $row['minTime'];
494                    $minMax['max'] = $row['maxTime'];
495                } else {
496                    // Default values if no data found
497                    $minMax['min'] = 0;
498                    $minMax['max'] = 23;
499                }
500            } else {
501                // Default values if query failed
502                $minMax['min'] = 0;
503                $minMax['max'] = 23;
504
505                // Optionally log the error
506                $log = new \KLogger("../logs/dev_log.txt", \KLogger::DEBUG);
507                $log->LogDebug("Query failed in getMinMaxTime: " . $this->db->errorInfo()[2]);
508            }
509        }
510        return $minMax;
511    }
512
513
514    function getSortAndProcessAverages()
515    {
516        $log = new \KLogger($_ENV['LOG_DIR']."dev_log.txt",\KLogger::DEBUG);
517        $out = [];
518        if ($this->checkTimesSet()) {
519            $query = "SELECT
520                    FLOOR(0_sortDelay/totalSortCount) as sortDelayAvg,
521                    FLOOR(0_sortTime/totalSortCount) as sortTimeAvg,
522                    FLOOR(0_delayFromSortToProcess/totalSortCount) as sortToProcessDelayAvg,
523                    FLOOR(0_processTime/totalSortCount) as processTimeAvgWithSorter,
524                    FLOOR(1_processDelay/totalNonSortCount) as processDelayAvgWithoutSorter,
525                    FLOOR(1_processTime/totalNonSortCount) as processTimeAvgWithoutSorter
526                FROM
527                    (SELECT
528                    SUM(UNIX_TIMESTAMP( sortStarted ) - UNIX_TIMESTAMP( timeEntered )) AS 0_sortDelay,
529                    SUM(UNIX_TIMESTAMP( sortCompleted ) - UNIX_TIMESTAMP( sortStarted )) AS 0_sortTime,
530                    SUM(UNIX_TIMESTAMP( timeStarted ) - UNIX_TIMESTAMP( sortCompleted )) AS 0_delayFromSortToProcess,
531                    SUM(UNIX_TIMESTAMP( timeCompleted ) - UNIX_TIMESTAMP( timeStarted )) AS 0_processTime,
532                    COUNT(*) as totalSortCount
533                        FROM buyQueue
534                        WHERE (timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."')
535                        AND sorterID >0
536                        AND sortCompleted !=  '0000-00-00 00:00:00'
537                        AND isProcessed !=0
538                        AND (UNIX_TIMESTAMP( sortStarted ) - UNIX_TIMESTAMP( timeEntered ) > 0)
539                        AND (UNIX_TIMESTAMP( sortCompleted ) - UNIX_TIMESTAMP( sortStarted ) > 0)
540                        AND (UNIX_TIMESTAMP( timeStarted ) - UNIX_TIMESTAMP( sortCompleted ) > 0)
541                        AND (UNIX_TIMESTAMP( timeCompleted ) - UNIX_TIMESTAMP( timeStarted ) > 0)) AS A,
542                    (SELECT
543                        SUM(UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeEntered)) as 1_processDelay,
544                        SUM(UNIX_TIMESTAMP(timeCompleted) - UNIX_TIMESTAMP(timeStarted)) as 1_processTime,
545                         COUNT(*) as totalNonSortCount
546                    FROM buyQueue
547                        WHERE
548                        (timeEntered BETWEEN '".$this->dateStart['dateStart']->format("Y-m-d H:i:s")."' AND '".$this->dateEnd['dateEnd']->format("Y-m-d H:i:s")."')
549                        AND sorterID = 0
550                        AND isProcessed !=0
551                        ) AS B";
552            $log->LogDebug($query);
553            $db = dbConnectByName($this->getDbName());
554            $query = $db->query($query);
555            $out = $query->fetch(\PDO::FETCH_ASSOC);
556        }
557        return $out;
558    }
559    public function getSMSResults() {
560        $monthsArray = [];
561        $query =  $this->db->query("SELECT dateAdded FROM customers WHERE onSMS = 1 ORDER BY dateAdded ASC");
562        while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
563            $date = new \DateTime($row['dateAdded'], new \DateTimeZone('utc'));
564            $date->setTimezone(new \DateTimeZone($this->getTimeZone()));
565            $thisMonth = $date->format('M Y');
566            if(!array_key_exists($thisMonth, $monthsArray)) {
567                $monthsArray[$thisMonth] = 1;
568            } else {
569                $monthsArray[$thisMonth]++;
570            }
571
572        }
573        return $monthsArray;
574    }
575    public function getEmailResults() {
576        $monthsArray = [];
577        $query =  $this->db->query("SELECT dateAdded FROM customers WHERE onEmail = 1 ORDER BY dateAdded ASC");
578        while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
579            $date = new \DateTime($row['dateAdded'], new \DateTimeZone('utc'));
580            $date->setTimezone(new \DateTimeZone($this->getTimeZone()));
581            $thisMonth = $date->format('M Y');
582            if(!array_key_exists($thisMonth, $monthsArray)) {
583                $monthsArray[$thisMonth] = 1;
584            } else {
585                $monthsArray[$thisMonth]++;
586            }
587        }
588        return $monthsArray;
589    }
590    public function checkTimesSet()
591    {
592        return isset($this->dateStart) && isset($this->dateEnd);
593    }
594
595}