Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 88
0.00%
0 / 3
 CRAP
0.00%
0 / 1
LoyaltyPointReport
0.00%
0 / 88
0.00%
0 / 3
 240
0.00%
0 / 1
 getCustomerPointsLog
0.00%
0 / 28
0.00%
0 / 1
 12
 getPointLog
0.00%
0 / 53
0.00%
0 / 1
 90
 createTypeNumArrayString
0.00%
0 / 7
0.00%
0 / 1
 12
1<?php
2
3namespace BuyerKiosk\Core\Loyalty;
4
5use BuyerKiosk\Core\Store;
6
7class LoyaltyPointReport {
8
9    public static function getCustomerPointsLog($typeNum, $type = "group", $dateStart, $dateEnd) {
10
11        //$log = new \KLogger("/home/buyerkiosk/logs/loyalty.log", \KLogger::DEBUG);
12        $loyaltyGroup = new LoyaltyGroup(null, $typeNum);
13        $typeNumArray = [];
14
15        $store = new Store();
16        $store->createStore($typeNum);
17
18        global $db_name;
19        $db = dbConnectByName($db_name);
20
21        //If we need all the stores in the loyalty group or just one
22        foreach($loyaltyGroup->getStoresArray() as $store) {
23            $typeNumArray[] = $store->getTypeNum();
24            //$log->LogDebug($store->getTypeNum());
25        }
26
27        $string = self::createTypeNumArrayString($typeNumArray);
28
29        $query = $db->prepare("SELECT
30    *
31FROM
32    (
33        SELECT
34            loyaltyData.loyaltyID,
35            items,
36            firstName,
37            lastName,
38            count(loyaltyID) AS cNumTransactions,
39            SUM(IF(type = 1, FLOOR(salesTotal), 0)) AS cTotalSales,
40            SUM(IF(type = 1, 1, 0)) AS cSalesTransactions,
41            SUM(redeemedPoints) AS cTotalRedeemed,
42            count(redeemedPoints) AS cNumRedeems,
43            ROUND(SUM(discountReceived)) AS cTotalDiscounts,
44            count(discountReceived) AS cNumDiscounts,
45            SUM(subPoints) AS cTotalSubPoints,
46            count(subPoints) AS cNumSubPoints,
47            SUM(FLOOR(subSalesTotal)) AS cTotalSubSales,
48            count(subSalesTotal) AS cNumSubSales,
49            SUM(addPoints) AS cTotalAddedPoints,
50            count(addPoints) AS cNumAddedPoints,
51            SUM(items) AS cTotalItems,
52            COUNT(DISTINCT(typeNum)) AS storesShopped
53        FROM
54            loyaltyData
55        JOIN loyaltyCustomers ON loyaltyData.loyaltyID = loyaltyCustomers.id
56        WHERE
57            typeNum IN (".$string.")
58        AND date BETWEEN :dateStart
59        AND :dateEnd
60        GROUP BY
61            loyaltyID
62    ) AS C
63JOIN (
64    SELECT
65        loyaltyData.loyaltyID,
66        count(loyaltyID) AS iNumTransactions,
67        SUM(IF(type = 1, FLOOR(salesTotal), 0)) AS iTotalSales,
68        SUM(IF(type = 1, 1, 0)) AS iSalesTransactions,
69        SUM(redeemedPoints) AS iTotalRedeemed,
70        count(redeemedPoints) AS iNumRedeems,
71        ROUND(SUM(discountReceived)) AS iTotalDiscounts,
72        count(discountReceived) AS iNumDiscounts
73    FROM
74        loyaltyData
75    JOIN loyaltyCustomers ON loyaltyData.loyaltyID = loyaltyCustomers.id
76    WHERE
77        typeNum IN (:origin)
78    AND date BETWEEN :dateStart
79    AND :dateEnd
80    GROUP BY
81        loyaltyID
82) AS I ON (C.loyaltyID = I.loyaltyID)
83JOIN
84    (SELECT
85        loyaltyData.loyaltyID,
86            count(loyaltyID) AS oNumTransactions,
87            SUM(IF(type = 1, FLOOR(salesTotal), 0)) AS oTotalSales,
88            SUM(IF(type = 1, 1, 0)) AS oSalesTransactions,
89            SUM(redeemedPoints) AS oTotalRedeemed,
90            count(redeemedPoints) AS oNumRedeems,
91            ROUND(SUM(discountReceived)) AS oTotalDiscounts,
92            count(discountReceived) AS oNumDiscounts,
93            SUM(subPoints) AS oTotalSubPoints,
94            count(subPoints) AS oNumSubPoints,
95            SUM(FLOOR(subSalesTotal)) AS oTotalSubSales,
96            count(subSalesTotal) AS oNumSubSales,
97            SUM(addPoints) AS oTotalAddedPoints,
98            count(addPoints) AS oNumAddedPoints,
99            SUM(items) AS oTotalItems,
100            COUNT(DISTINCT(typeNum)) AS oStoresShopped
101    FROM
102        loyaltyData
103    JOIN loyaltyCustomers ON loyaltyData.loyaltyID = loyaltyCustomers.id
104    WHERE
105        typeNum IN (".$string.")
106    GROUP BY
107        loyaltyID
108) AS O ON I.loyaltyID = O.loyaltyID
109ORDER BY oSalesTransactions DESC");
110        //$log->LogDebug($typeNum);
111        $query->bindValue(":origin", $typeNum);
112        $query->bindValue(":dateStart", $dateStart);
113        $query->bindValue(":dateEnd", $dateEnd);
114        $query->execute();
115        $tempArray = [];
116        $loyaltyConfig = $store->getLoyaltyConfig();
117        while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
118            $temp['data'] = $row;
119            $temp['loyaltyID'] = $row['loyaltyID'];
120            $temp['customerName'] = $row['firstName']." ".$row['lastName'];
121            $rewardPoints = ((floor($row['oTotalSales']) - floor($row['oTotalSubSales']))) * floatval($loyaltyConfig->d2p);
122            $remainingPoints =  ($rewardPoints + $row['oTotalAddedPoints']) - $row['oTotalRedeemed'] - $row['oTotalSubPoints'];
123            $temp['balance'] = $remainingPoints;
124            $tempArray[] = $temp;
125        }
126        return $tempArray;
127    }
128
129    public static function getPointLog($typeNum, $type = "group", $dateStart, $dateEnd) {
130        $loyaltyGroup = new LoyaltyGroup(null, $typeNum);
131        $typeNumArray = [];
132
133        $store = new Store();
134        $store->createStore($typeNum);
135
136        global $db_name;
137        $db = dbConnectByName($db_name);
138
139        //If we need all the stores in the loyalty group or just one
140        if($type == "group") {
141            foreach($loyaltyGroup->getStoresArray() as $groupStore) {
142                $typeNumArray[] = $groupStore->getTypeNum();
143            }
144        } else {
145            $typeNumArray[] = $typeNum;
146        }
147        $string = self::createTypeNumArrayString($typeNumArray);
148        error_log($string);
149
150
151        //Build Query
152        $query = $db->prepare("SELECT loyaltyData.loyaltyID, date, type, loyaltyData.id, salesTotal, items, redeemedPoints, discountReceived, loyaltyCustomers.storeGroup, subPoints,subSalesTotal,addPoints, firstName, lastName FROM loyaltyData JOIN loyaltyCustomers ON loyaltyData.loyaltyID = loyaltyCustomers.id WHERE typeNum IN(".$string.") AND `date` BETWEEN :dateStart AND :dateEnd");
153        $query->bindValue(":dateStart", $dateStart);
154        $query->bindValue(":dateEnd", $dateEnd);
155        $query->execute();
156        $logArray = [];
157        while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
158            $temp['customerName'] = $row['firstName']." ".$row['lastName'];
159            switch ((int)$row['type']) {
160                case 1:
161                    $temp['type'] = "Sale";
162                    $temp['amount'] = "$".$row['salesTotal'];
163                    $temp['rowClass'] = "success";
164                    break;
165                case 2:
166                    $temp['type'] = "Redemption";
167                    $temp['amount'] = $row['redeemedPoints'];
168                    $temp['rowClass'] = "info";
169                    break;
170        case 3:
171                    $temp['type'] = "Buy";
172                    $temp['amount'] = "";
173                    $temp['rowClass'] = "default";
174                    break;
175                case 4:
176                    $temp['type'] = "Subtract Points";
177                    $temp['amount'] = $row['subPoints'];
178                    $temp['rowClass'] = "danger";
179                    break;
180                case 6:
181                    $temp['type'] = "Add Points";
182                    $temp['amount'] = $row['addPoints'];
183                    $temp['rowClass'] = "success";
184                    break;
185            }
186            $date = new \DateTime($row['date'], new \DateTimeZone('utc'));
187            $date->setTimezone(new \DateTimeZone('America/Chicago'));
188            $temp['date'] = $date->format("Y, M jS  g:i a");
189            $temp['timestamp'] = $date->getTimestamp();
190            $temp['id'] = $row['id'];
191            $temp['loyaltyID'] = $row['loyaltyID'];
192            $logArray[] = $temp;
193        }
194
195        return $logArray;
196    }
197    private static function createTypeNumArrayString($typeNumArray) {
198        $string = ""; $count = 0;
199        foreach($typeNumArray as $typeNum) {
200            if($count < count($typeNumArray)-1) {
201                $string .= "'".$typeNumArray[$count]."',";
202            } else {
203                $string .= "'".$typeNumArray[$count]."'";
204            }
205            $count++;
206        }
207        return $string;
208    }
209}