Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 45
0.00%
0 / 7
 CRAP
0.00%
0 / 1
TriggerStats
0.00%
0 / 45
0.00%
0 / 7
 182
0.00%
0 / 1
 __construct
0.00%
0 / 9
0.00%
0 / 1
 12
 getGroupUsageNumbers
0.00%
0 / 15
0.00%
0 / 1
 12
 getStoreUsageNumbers
0.00%
0 / 15
0.00%
0 / 1
 12
 getStartDate
0.00%
0 / 1
0.00%
0 / 1
 2
 setStartDate
0.00%
0 / 2
0.00%
0 / 1
 2
 getEndDate
0.00%
0 / 1
0.00%
0 / 1
 2
 setEndDate
0.00%
0 / 2
0.00%
0 / 1
 2
1<?php
2
3namespace BuyerKiosk\Core;
4
5/**
6 * TriggerStats
7 *
8 * Generates statistics for loyalty trigger campaigns.
9 *
10 * @package BuyerKiosk\Core
11 */
12class TriggerStats {
13
14    private $startDate;
15    private $endDate;
16
17    private $groupID;
18    private $typeNum;
19
20    private $db;
21
22    private $log;
23
24    public function __construct($groupID = null, $typeNum = null, $startDate, $endDate)
25    {
26        global $db_name;
27
28        $this->setEndDate($endDate);
29        $this->setStartDate($startDate);
30        $this->log = new \KLogger($_ENV['LOG_DIR']."dev_log.log", \KLogger::DEBUG);
31        $this->db = dbConnectByName($db_name);
32
33
34        if($groupID !== null) {
35            $this->groupID = $groupID;
36        } elseif($typeNum !== null) {
37            $this->typeNum = $typeNum;
38        }
39
40
41    }
42    public function getGroupUsageNumbers() {
43        //Begin BEASTQUERY
44        //Currently the longest and most complex query I've written. Let's see if it stays that way.
45        $stmt = $this->db->prepare("SELECT
46    B.triggerID,
47    ROUND( totalVisited / totalSent * 100, 1 ) AS visitRate,
48    totalSent,
49    totalVisited,
50    totalViews,
51    totalClicked,
52    totalUsed,
53    ROUND( totalViews / totalSent * 100, 1 ) AS ctr,
54    ROUND( totalUsed / totalSent * 100, 1 ) AS usageRate,
55    ROUND( totalUsed / totalViews * 100, 1 ) AS clickedUsedRate,
56    salesTotal,
57    transTotal,
58    couponSales,
59    couponDiscounts,
60    buyVisits,
61    loyaltyTriggers.shortName AS triggerName,
62    loyaltyTriggers.couponID,
63    loyaltyCoupons.shortName AS couponName,
64    loyaltySMSMessages.shortName AS messageName,
65    loyaltyOutgoing.message AS recentMessage
66FROM
67    (
68    SELECT
69        count( * ) AS totalSent,
70        SUM( IF ( visitedAfterText = 1, 1, 0 ) ) AS totalVisited,
71        SUM( IF ( viewed > 0, 1, 0 ) ) AS totalViews,
72        SUM( IF ( clicked > 0, 1, 0 ) ) AS totalClicked,
73        SUM( IF ( used > 0, 1, 0 ) ) AS totalUsed,
74        triggerID,
75        MAX( outID ) AS maxID
76    FROM
77        (
78        SELECT
79            loyaltyOutgoing.triggerID,
80            loyaltyCouponUUID.viewed,
81            loyaltyCouponUUID.clicked,
82            loyaltyCouponUUID.used,
83            loyaltyOutgoing.id AS outID,
84        IF
85            ( UNIX_TIMESTAMP( loyaltyData.date ) > UNIX_TIMESTAMP( loyaltyOutgoing.dateAdded ), 1, 0 ) AS visitedAfterText
86        FROM
87            loyaltyCouponUUID
88            LEFT OUTER JOIN loyaltyOutgoing ON loyaltyCouponUUID.UUID = loyaltyOutgoing.couponUUID
89            LEFT OUTER JOIN loyaltyData ON loyaltyCouponUUID.loyaltyID = loyaltyData.loyaltyID
90        WHERE
91            delivered = 1
92            AND loyaltyOutgoing.triggerID IS NOT NULL
93            AND loyaltyOutgoing.storeGroupFrom = :storeGroupNum
94            AND timeFrame BETWEEN :start
95            AND :end
96            AND loyaltyData.id = ( SELECT MAX( id ) FROM loyaltyData WHERE loyaltyData.loyaltyID = loyaltyCouponUUID.loyaltyID )
97        ORDER BY
98            loyaltyData.date
99        ) AS A
100    GROUP BY
101        triggerID
102    ) AS B
103    JOIN loyaltyTriggers ON B.triggerID = loyaltyTriggers.id
104    JOIN (
105    SELECT
106        couponData.triggerID,
107        couponSales,
108        couponDiscounts,
109        couponsUsed,
110        salesTotal,
111        transTotal,
112        buyVisits
113    FROM
114        (
115        SELECT
116            triggerID,
117            SUM( couponSales ) AS couponSales,
118            SUM( couponDiscounts ) AS couponDiscounts,
119            SUM( couponsUsed ) AS couponsUsed
120        FROM
121            (
122            SELECT
123                UUID,
124                ROUND( SUM( IF ( loyaltyCouponUUID.used = 1, loyaltyCouponUUID.sale, 0 ) ), 2 ) couponSales,
125                ROUND( SUM( IF ( loyaltyCouponUUID.used = 1, loyaltyCouponUUID.discount, 0 ) ), 2 ) AS couponDiscounts,
126            IF
127                ( loyaltyCouponUUID.used = 1, 1, 0 ) AS couponsUsed,
128                triggerID
129            FROM
130                loyaltyCouponUUID
131                JOIN loyaltyOutgoing ON loyaltyCouponUUID.UUID = loyaltyOutgoing.couponUUID
132                JOIN loyaltyData ON loyaltyCouponUUID.loyaltyID = loyaltyData.loyaltyID
133            WHERE
134                UNIX_TIMESTAMP( loyaltyData.date ) > UNIX_TIMESTAMP( loyaltyOutgoing.dateAdded )
135                AND timeFrame BETWEEN :start
136            AND :end
137                AND triggerID IS NOT NULL
138                AND loyaltyOutgoing.storeGroupFrom = :storeGroupNum
139            GROUP BY
140                UUID
141            ORDER BY
142                loyaltyOutgoing.dateAdded
143            ) AS couponData1
144        GROUP BY
145            triggerID
146        ) AS couponData
147        JOIN (
148        SELECT
149            triggerID,
150            SUM( salesTotal ) AS salesTotal,
151            SUM( IF ( transTotal > 0, transTotal, 0 ) ) AS transTotal,
152            SUM( buyVisits ) AS buyVisits
153        FROM
154            (
155            SELECT
156                UUID,
157                dateAdded,
158                ROUND( SUM( IF ( loyaltyData.type = 1, salesTotal, 0 ) ), 2 ) AS salesTotal,
159                SUM( IF ( loyaltyData.type = 1, 1, 0 ) ) AS transTotal,
160                SUM( IF ( loyaltyData.type = 3, 1, 0 ) ) AS buyVisits,
161                triggerID
162            FROM
163                loyaltyCouponUUID
164                JOIN loyaltyOutgoing ON loyaltyCouponUUID.UUID = loyaltyOutgoing.couponUUID
165                JOIN loyaltyData ON loyaltyCouponUUID.loyaltyID = loyaltyData.loyaltyID
166                JOIN loyaltyGroups ON loyaltyData.typeNum = loyaltyGroups.store
167            WHERE
168                UNIX_TIMESTAMP( loyaltyData.date ) > UNIX_TIMESTAMP( loyaltyOutgoing.dateAdded )
169                AND timeFrame BETWEEN :start
170            AND :end
171                AND triggerID IS NOT NULL
172                AND groupID = :storeGroupNum
173            GROUP BY
174                UUID
175            ORDER BY
176                loyaltyOutgoing.dateAdded
177            ) AS salesData1
178        GROUP BY
179            triggerID
180        ) AS salesData ON salesData.triggerID = couponData.triggerID
181    ) AS salesNumbers ON salesNumbers.triggerID = B.triggerID
182    JOIN loyaltyCoupons ON loyaltyTriggers.couponID = loyaltyCoupons.id
183    JOIN loyaltySMSMessages ON loyaltyTriggers.messageID = loyaltySMSMessages.id
184    JOIN loyaltyOutgoing ON B.maxID = loyaltyOutgoing.id");
185
186        $this->log->LogDebug($this->groupID);
187        $this->log->LogDebug($this->startDate['dateStart']->format("Y-m-d H:i:s"));
188        $this->log->LogDebug($this->endDate['dateEnd']->format("Y-m-d H:i:s"));
189
190        $stmt->bindValue(":storeGroupNum", $this->groupID);
191        $stmt->bindValue(":start", $this->startDate['dateStart']->format("Y-m-d H:i:s"));
192        $stmt->bindValue(":end", $this->endDate['dateEnd']->format("Y-m-d H:i:s"));
193
194        if($stmt->execute()) {
195            $data = [];
196            while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
197                $data[] = $row;
198            }
199            return $data;
200        } else {
201            $this->log->LogError(print_r($stmt->errorInfo()));
202        }
203        return null;
204    }
205    public function getStoreUsageNumbers() {
206        $stmt = $this->db->prepare("SELECT
207    B.triggerID,
208    ROUND( totalVisited / totalSent * 100, 1 ) AS visitRate,
209    totalSent,
210    totalVisited,
211    totalViews,
212    totalClicked,
213    totalUsed,
214    ROUND( totalViews / totalSent * 100, 1 ) AS ctr,
215    ROUND( totalUsed / totalSent * 100, 1 ) AS usageRate,
216    ROUND( totalUsed / totalViews * 100, 1 ) AS clickedUsedRate,
217    salesTotal,
218    transTotal,
219    couponSales,
220    couponDiscounts,
221    buyVisits,
222    loyaltyTriggers.shortName AS triggerName,
223    loyaltyTriggers.couponID,
224    loyaltyCoupons.shortName AS couponName,
225    loyaltySMSMessages.shortName AS messageName,
226    loyaltyOutgoing.message AS recentMessage
227FROM
228    (
229    SELECT
230        count( * ) AS totalSent,
231        SUM( IF ( visitedAfterText = 1, 1, 0 ) ) AS totalVisited,
232        SUM( IF ( viewed > 0, 1, 0 ) ) AS totalViews,
233        SUM( IF ( clicked > 0, 1, 0 ) ) AS totalClicked,
234        SUM( IF ( used > 0, 1, 0 ) ) AS totalUsed,
235        triggerID,
236        MAX( outID ) AS maxID
237    FROM
238        (
239        SELECT
240            loyaltyOutgoing.triggerID,
241            loyaltyCouponUUID.viewed,
242            loyaltyCouponUUID.clicked,
243            loyaltyCouponUUID.used,
244            loyaltyOutgoing.id AS outID,
245        IF
246            ( UNIX_TIMESTAMP( loyaltyData.date ) > UNIX_TIMESTAMP( loyaltyOutgoing.dateAdded ), 1, 0 ) AS visitedAfterText
247        FROM
248            loyaltyCouponUUID
249            LEFT OUTER JOIN loyaltyOutgoing ON loyaltyCouponUUID.UUID = loyaltyOutgoing.couponUUID
250            LEFT OUTER JOIN loyaltyData ON loyaltyCouponUUID.loyaltyID = loyaltyData.loyaltyID
251        WHERE
252            delivered = 1
253            AND loyaltyOutgoing.triggerID IS NOT NULL
254            AND loyaltyOutgoing.storeFrom = :typeNum
255            AND timeFrame BETWEEN :start
256            AND :end
257            AND loyaltyData.id = ( SELECT MAX( id ) FROM loyaltyData WHERE loyaltyData.loyaltyID = loyaltyCouponUUID.loyaltyID )
258        ORDER BY
259            loyaltyData.date
260        ) AS A
261    GROUP BY
262        triggerID
263    ) AS B
264    JOIN loyaltyTriggers ON B.triggerID = loyaltyTriggers.id
265    JOIN (
266    SELECT
267        couponData.triggerID,
268        couponSales,
269        couponDiscounts,
270        couponsUsed,
271        salesTotal,
272        transTotal,
273        buyVisits
274    FROM
275        (
276        SELECT
277            triggerID,
278            SUM( couponSales ) AS couponSales,
279            SUM( couponDiscounts ) AS couponDiscounts,
280            SUM( couponsUsed ) AS couponsUsed
281        FROM
282            (
283            SELECT
284                UUID,
285                ROUND( SUM( IF ( loyaltyCouponUUID.used = 1, loyaltyCouponUUID.sale, 0 ) ), 2 ) couponSales,
286                ROUND( SUM( IF ( loyaltyCouponUUID.used = 1, loyaltyCouponUUID.discount, 0 ) ), 2 ) AS couponDiscounts,
287            IF
288                ( loyaltyCouponUUID.used = 1, 1, 0 ) AS couponsUsed,
289                triggerID
290            FROM
291                loyaltyCouponUUID
292                JOIN loyaltyOutgoing ON loyaltyCouponUUID.UUID = loyaltyOutgoing.couponUUID
293                JOIN loyaltyData ON loyaltyCouponUUID.loyaltyID = loyaltyData.loyaltyID
294            WHERE
295                UNIX_TIMESTAMP( loyaltyData.date ) > UNIX_TIMESTAMP( loyaltyOutgoing.dateAdded )
296                AND timeFrame BETWEEN :start
297            AND :end
298                AND triggerID IS NOT NULL
299                AND loyaltyCouponUUID.storeUsed = :typeNum
300            GROUP BY
301                UUID
302            ORDER BY
303                loyaltyOutgoing.dateAdded
304            ) AS couponData1
305        GROUP BY
306            triggerID
307        ) AS couponData
308        JOIN (
309        SELECT
310            triggerID,
311            SUM( salesTotal ) AS salesTotal,
312            SUM( IF ( transTotal > 0, transTotal, 0 ) ) AS transTotal,
313            SUM( buyVisits ) AS buyVisits
314        FROM
315            (
316            SELECT
317                UUID,
318                dateAdded,
319                ROUND( SUM( IF ( loyaltyData.type = 1, salesTotal, 0 ) ), 2 ) AS salesTotal,
320                SUM( IF ( loyaltyData.type = 1, 1, 0 ) ) AS transTotal,
321                SUM( IF ( loyaltyData.type = 3, 1, 0 ) ) AS buyVisits,
322                triggerID
323            FROM
324                loyaltyCouponUUID
325                JOIN loyaltyOutgoing ON loyaltyCouponUUID.UUID = loyaltyOutgoing.couponUUID
326                JOIN loyaltyData ON loyaltyCouponUUID.loyaltyID = loyaltyData.loyaltyID
327            WHERE
328                UNIX_TIMESTAMP( loyaltyData.date ) > UNIX_TIMESTAMP( loyaltyOutgoing.dateAdded )
329                AND timeFrame BETWEEN :start
330            AND :end
331                AND triggerID IS NOT NULL
332                AND typeNum = :typeNum
333            GROUP BY
334                UUID
335            ORDER BY
336                loyaltyOutgoing.dateAdded
337            ) AS salesData1
338        GROUP BY
339            triggerID
340        ) AS salesData ON salesData.triggerID = couponData.triggerID
341    ) AS salesNumbers ON salesNumbers.triggerID = B.triggerID
342    JOIN loyaltyCoupons ON loyaltyTriggers.couponID = loyaltyCoupons.id
343    JOIN loyaltySMSMessages ON loyaltyTriggers.messageID = loyaltySMSMessages.id
344    JOIN loyaltyOutgoing ON B.maxID = loyaltyOutgoing.id");
345
346        $this->log->LogDebug($this->typeNum);
347        $this->log->LogDebug($this->startDate['dateStart']->format("Y-m-d H:i:s"));
348        $this->log->LogDebug($this->endDate['dateEnd']->format("Y-m-d H:i:s"));
349
350        $stmt->bindValue(":typeNum", $this->typeNum);
351        $stmt->bindValue(":start", $this->startDate['dateStart']->format("Y-m-d H:i:s"));
352        $stmt->bindValue(":end", $this->endDate['dateEnd']->format("Y-m-d H:i:s"));
353
354        if($stmt->execute()) {
355            $data = [];
356            while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
357                $data[] = $row;
358            }
359            return $data;
360        } else {
361            $this->log->LogError(print_r($stmt->errorInfo()));
362        }
363        return null;
364    }
365
366    /**
367     * @return mixed
368     */
369    public function getStartDate()
370    {
371        return $this->startDate;
372    }
373
374    /**
375     * @param mixed $startDate
376     */
377    public function setStartDate($startDate)
378    {
379        $startDate = getDateRange($startDate);
380        $this->startDate = $startDate;
381    }
382
383    /**
384     * @return mixed
385     */
386    public function getEndDate()
387    {
388        return $this->endDate;
389    }
390
391    /**
392     * @param mixed $endDate
393     */
394    public function setEndDate($endDate)
395    {
396        $endDate = getDateRange($endDate);
397        $this->endDate = $endDate;
398    }
399
400}