1: <?php
2: namespace Opencart\Admin\Model\Extension\Opencart\Report;
3: /**
4: * Class Customer
5: *
6: * @package Opencart\Admin\Model\Extension\Opencart\Report
7: */
8: class Customer extends \Opencart\System\Engine\Model {
9: /**
10: * Get Total Customers By Day
11: *
12: * @return array<int, array<string, int>>
13: */
14: public function getTotalCustomersByDay(): array {
15: $customer_data = [];
16:
17: for ($i = 0; $i < 24; $i++) {
18: $customer_data[$i] = [
19: 'hour' => $i,
20: 'total' => 0
21: ];
22: }
23:
24: $query = $this->db->query("SELECT COUNT(*) AS `total`, HOUR(`date_added`) AS `hour` FROM `" . DB_PREFIX . "customer` WHERE DATE(`date_added`) = DATE(NOW()) GROUP BY HOUR(`date_added`) ORDER BY `date_added` ASC");
25:
26: foreach ($query->rows as $result) {
27: $customer_data[$result['hour']] = [
28: 'hour' => $result['hour'],
29: 'total' => $result['total']
30: ];
31: }
32:
33: return $customer_data;
34: }
35:
36: /**
37: * Get Total Customers By Week
38: *
39: * @return array<int, array<string, int>>
40: */
41: public function getTotalCustomersByWeek(): array {
42: $customer_data = [];
43:
44: $date_start = strtotime('-' . date('w') . ' days');
45:
46: for ($i = 0; $i < 7; $i++) {
47: $date = date('Y-m-d', $date_start + ($i * 86400));
48:
49: $customer_data[date('w', strtotime($date))] = [
50: 'day' => date('D', strtotime($date)),
51: 'total' => 0
52: ];
53: }
54:
55: $query = $this->db->query("SELECT COUNT(*) AS `total`, `date_added` FROM `" . DB_PREFIX . "customer` WHERE DATE(`date_added`) >= DATE('" . $this->db->escape(date('Y-m-d', $date_start)) . "') GROUP BY DAYNAME(`date_added`)");
56:
57: foreach ($query->rows as $result) {
58: $customer_data[date('w', strtotime($result['date_added']))] = [
59: 'day' => date('D', strtotime($result['date_added'])),
60: 'total' => $result['total']
61: ];
62: }
63:
64: return $customer_data;
65: }
66:
67: /**
68: * Get Total Customers By Month
69: *
70: * @return array<int, array<string, int>>
71: */
72: public function getTotalCustomersByMonth(): array {
73: $customer_data = [];
74:
75: for ($i = 1; $i <= date('t'); $i++) {
76: $date = date('Y') . '-' . date('m') . '-' . $i;
77:
78: $customer_data[date('j', strtotime($date))] = [
79: 'day' => date('d', strtotime($date)),
80: 'total' => 0
81: ];
82: }
83:
84: $query = $this->db->query("SELECT COUNT(*) AS `total`, `date_added` FROM `" . DB_PREFIX . "customer` WHERE DATE(`date_added`) >= DATE('" . $this->db->escape(date('Y') . '-' . date('m') . '-1') . "') GROUP BY DATE(`date_added`)");
85:
86: foreach ($query->rows as $result) {
87: $customer_data[date('j', strtotime($result['date_added']))] = [
88: 'day' => date('d', strtotime($result['date_added'])),
89: 'total' => $result['total']
90: ];
91: }
92:
93: return $customer_data;
94: }
95:
96: /**
97: * Get Total Customers By Year
98: *
99: * @return array<int, array<string, int>>
100: */
101: public function getTotalCustomersByYear(): array {
102: $customer_data = [];
103:
104: for ($i = 1; $i <= 12; $i++) {
105: $customer_data[$i] = [
106: 'month' => date('M', mktime(0, 0, 0, $i, 1)),
107: 'total' => 0
108: ];
109: }
110:
111: $query = $this->db->query("SELECT COUNT(*) AS `total`, `date_added` FROM `" . DB_PREFIX . "customer` WHERE YEAR(`date_added`) = YEAR(NOW()) GROUP BY MONTH(`date_added`)");
112:
113: foreach ($query->rows as $result) {
114: $customer_data[date('n', strtotime($result['date_added']))] = [
115: 'month' => date('M', strtotime($result['date_added'])),
116: 'total' => $result['total']
117: ];
118: }
119:
120: return $customer_data;
121: }
122:
123: /**
124: * Get Customers
125: *
126: * @param array<string, mixed> $data
127: *
128: * @return array<int, array<string, mixed>>
129: */
130: public function getCustomers(array $data = []): array {
131: $sql = "SELECT MIN(`date_added`) AS `date_start`, MAX(`date_added`) AS `date_end`, COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer`";
132:
133: $implode = [];
134:
135: if (!empty($data['filter_date_start'])) {
136: $implode[] = "DATE(`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
137: }
138:
139: if (!empty($data['filter_date_end'])) {
140: $implode[] = "DATE(`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
141: }
142:
143: if ($implode) {
144: $sql .= " WHERE " . implode(" AND ", $implode);
145: }
146:
147: if (!empty($data['filter_group'])) {
148: $group = $data['filter_group'];
149: } else {
150: $group = 'week';
151: }
152:
153: switch ($group) {
154: case 'day':
155: $sql .= " GROUP BY YEAR(`date_added`), MONTH(`date_added`), DAY(`date_added`)";
156: break;
157: default:
158: case 'week':
159: $sql .= " GROUP BY YEAR(`date_added`), WEEK(`date_added`)";
160: break;
161: case 'month':
162: $sql .= " GROUP BY YEAR(`date_added`), MONTH(`date_added`)";
163: break;
164: case 'year':
165: $sql .= " GROUP BY YEAR(`date_added`)";
166: break;
167: }
168:
169: if (isset($data['start']) || isset($data['limit'])) {
170: if ($data['start'] < 0) {
171: $data['start'] = 0;
172: }
173:
174: if ($data['limit'] < 1) {
175: $data['limit'] = 20;
176: }
177:
178: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
179: }
180:
181: $query = $this->db->query($sql);
182:
183: return $query->rows;
184: }
185:
186: /**
187: * Get Total Customers
188: *
189: * @param array<string, mixed> $data
190: *
191: * @return int
192: */
193: public function getTotalCustomers(array $data = []): int {
194: if (!empty($data['filter_group'])) {
195: $group = $data['filter_group'];
196: } else {
197: $group = 'week';
198: }
199:
200: switch ($group) {
201: case 'day':
202: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`), MONTH(`date_added`), DAY(`date_added`)) AS `total` FROM `" . DB_PREFIX . "customer`";
203: break;
204: default:
205: case 'week':
206: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`), WEEK(`date_added`)) AS `total` FROM `" . DB_PREFIX . "customer`";
207: break;
208: case 'month':
209: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`), MONTH(`date_added`)) AS `total` FROM `" . DB_PREFIX . "customer`";
210: break;
211: case 'year':
212: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`)) AS `total` FROM `" . DB_PREFIX . "customer`";
213: break;
214: }
215:
216: $implode = [];
217:
218: if (!empty($data['filter_date_start'])) {
219: $implode[] = "DATE(`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
220: }
221:
222: if (!empty($data['filter_date_end'])) {
223: $implode[] = "DATE(`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
224: }
225:
226: if ($implode) {
227: $sql .= " WHERE " . implode(" AND ", $implode);
228: }
229:
230: $query = $this->db->query($sql);
231:
232: return (int)$query->row['total'];
233: }
234:
235: /**
236: * Get Orders
237: *
238: * @param array<string, mixed> $data
239: *
240: * @return array<int, array<string, mixed>>
241: */
242: public function getOrders(array $data = []): array {
243: $sql = "SELECT `c`.`customer_id`, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `customer`, `c`.`email`, cgd.`name` AS `customer_group`, `c`.`status`, `o`.`order_id`, SUM(op.`quantity`) AS `products`, `o`.`total` AS `total` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "order_product` op ON (`o`.`order_id` = op.`order_id`) LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (`o`.`customer_id` = `c`.`customer_id`) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (`c`.`customer_group_id` = cgd.`customer_group_id`) WHERE `o`.`customer_id` > '0' AND cgd.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
244:
245: if (!empty($data['filter_date_start'])) {
246: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
247: }
248:
249: if (!empty($data['filter_date_end'])) {
250: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
251: }
252:
253: if (!empty($data['filter_customer'])) {
254: $sql .= " AND CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
255: }
256:
257: if (!empty($data['filter_order_status_id'])) {
258: $sql .= " AND `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
259: } else {
260: $sql .= " AND `o`.`order_status_id` > '0'";
261: }
262:
263: $sql .= " GROUP BY `o`.`order_id`";
264:
265: $sql = "SELECT t.`customer_id`, t.`customer`, t.`email`, t.`customer_group`, t.`status`, COUNT(DISTINCT t.`order_id`) AS `orders`, SUM(t.`products`) AS `products`, SUM(t.`total`) AS `total` FROM (" . $sql . ") AS t GROUP BY t.`customer_id` ORDER BY `total` DESC";
266:
267: if (isset($data['start']) || isset($data['limit'])) {
268: if ($data['start'] < 0) {
269: $data['start'] = 0;
270: }
271:
272: if ($data['limit'] < 1) {
273: $data['limit'] = 20;
274: }
275:
276: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
277: }
278:
279: $query = $this->db->query($sql);
280:
281: return $query->rows;
282: }
283:
284: /**
285: * Get Total Orders
286: *
287: * @param array<string, mixed> $data
288: *
289: * @return int
290: */
291: public function getTotalOrders(array $data = []): int {
292: $sql = "SELECT COUNT(DISTINCT `o`.`customer_id`) AS `total` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (`o`.`customer_id` = `c`.`customer_id`) WHERE `o`.`customer_id` > '0'";
293:
294: if (!empty($data['filter_date_start'])) {
295: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
296: }
297:
298: if (!empty($data['filter_date_end'])) {
299: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
300: }
301:
302: if (!empty($data['filter_customer'])) {
303: $sql .= " AND CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
304: }
305:
306: if (!empty($data['filter_order_status_id'])) {
307: $sql .= " AND `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
308: } else {
309: $sql .= " AND `o`.`order_status_id` > '0'";
310: }
311:
312: $query = $this->db->query($sql);
313:
314: return (int)$query->row['total'];
315: }
316:
317: /**
318: * Get Reward Points
319: *
320: * @param array<string, mixed> $data
321: *
322: * @return array<int, array<string, mixed>>
323: */
324: public function getRewardPoints(array $data = []): array {
325: $sql = "SELECT cr.`customer_id`, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `customer`, `c`.`email`, cgd.`name` AS `customer_group`, `c`.`status`, SUM(cr.`points`) AS `points`, COUNT(`o`.`order_id`) AS `orders`, SUM(`o`.`total`) AS `total` FROM `" . DB_PREFIX . "customer_reward` cr LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (cr.`customer_id` = `c`.`customer_id`) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (`c`.`customer_group_id` = cgd.`customer_group_id`) LEFT JOIN `" . DB_PREFIX . "order` `o` ON (cr.`order_id` = `o`.`order_id`) WHERE cgd.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
326:
327: if (!empty($data['filter_date_start'])) {
328: $sql .= " AND DATE(cr.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
329: }
330:
331: if (!empty($data['filter_date_end'])) {
332: $sql .= " AND DATE(cr.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
333: }
334:
335: if (!empty($data['filter_customer'])) {
336: $sql .= " AND CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
337: }
338:
339: $sql .= " GROUP BY cr.`customer_id` ORDER BY `points` DESC";
340:
341: if (isset($data['start']) || isset($data['limit'])) {
342: if ($data['start'] < 0) {
343: $data['start'] = 0;
344: }
345:
346: if ($data['limit'] < 1) {
347: $data['limit'] = 20;
348: }
349:
350: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
351: }
352:
353: $query = $this->db->query($sql);
354:
355: return $query->rows;
356: }
357:
358: /**
359: * Get Total Reward Points
360: *
361: * @param array<string, mixed> $data
362: *
363: * @return int
364: */
365: public function getTotalRewardPoints(array $data = []): int {
366: $sql = "SELECT COUNT(DISTINCT cr.`customer_id`) AS `total` FROM `" . DB_PREFIX . "customer_reward` cr LEFT JOIN `" . DB_PREFIX . "customer` c ON (cr.`customer_id` = c.`customer_id`)";
367:
368: $implode = [];
369:
370: if (!empty($data['filter_date_start'])) {
371: $implode[] = "DATE(cr.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
372: }
373:
374: if (!empty($data['filter_date_end'])) {
375: $implode[] = "DATE(cr.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
376: }
377:
378: if (!empty($data['filter_customer'])) {
379: $implode[] = "CONCAT(c.`firstname`, ' ', c.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
380: }
381:
382: if ($implode) {
383: $sql .= " WHERE " . implode(" AND ", $implode);
384: }
385:
386: $query = $this->db->query($sql);
387:
388: return (int)$query->row['total'];
389: }
390:
391: /**
392: * Get Customer Activities
393: *
394: * @param array<string, mixed> $data
395: *
396: * @return array<int, array<string, mixed>>
397: */
398: public function getCustomerActivities(array $data = []): array {
399: $sql = "SELECT ca.`customer_activity_id`, ca.`customer_id`, ca.`key`, ca.`data`, ca.`ip`, ca.`date_added` FROM `" . DB_PREFIX . "customer_activity` ca LEFT JOIN `" . DB_PREFIX . "customer` c ON (ca.`customer_id` = c.`customer_id`)";
400:
401: $implode = [];
402:
403: if (!empty($data['filter_date_start'])) {
404: $implode[] = "DATE(ca.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
405: }
406:
407: if (!empty($data['filter_date_end'])) {
408: $implode[] = "DATE(ca.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
409: }
410:
411: if (!empty($data['filter_customer'])) {
412: $implode[] = "CONCAT(c.`firstname`, ' ', c.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
413: }
414:
415: if (!empty($data['filter_ip'])) {
416: $implode[] = "ca.`ip` LIKE '" . $this->db->escape((string)$data['filter_ip']) . "'";
417: }
418:
419: if ($implode) {
420: $sql .= " WHERE " . implode(" AND ", $implode);
421: }
422:
423: $sql .= " ORDER BY ca.`date_added` DESC";
424:
425: if (isset($data['start']) || isset($data['limit'])) {
426: if ($data['start'] < 0) {
427: $data['start'] = 0;
428: }
429:
430: if ($data['limit'] < 1) {
431: $data['limit'] = 20;
432: }
433:
434: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
435: }
436:
437: $query = $this->db->query($sql);
438:
439: return $query->rows;
440: }
441:
442: /**
443: * Get Total Customer Activities
444: *
445: * @param array<string, mixed> $data
446: *
447: * @return int
448: */
449: public function getTotalCustomerActivities(array $data = []): int {
450: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_activity` ca LEFT JOIN `" . DB_PREFIX . "customer` c ON (ca.`customer_id` = c.`customer_id`)";
451:
452: $implode = [];
453:
454: if (!empty($data['filter_date_start'])) {
455: $implode[] = "DATE(ca.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
456: }
457:
458: if (!empty($data['filter_date_end'])) {
459: $implode[] = "DATE(ca.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
460: }
461:
462: if (!empty($data['filter_customer'])) {
463: $implode[] = "CONCAT(c.`firstname`, ' ', c.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
464: }
465:
466: if (!empty($data['filter_ip'])) {
467: $implode[] = "ca.`ip` LIKE '" . $this->db->escape((string)$data['filter_ip']) . "'";
468: }
469:
470: if ($implode) {
471: $sql .= " WHERE " . implode(" AND ", $implode);
472: }
473:
474: $query = $this->db->query($sql);
475:
476: return (int)$query->row['total'];
477: }
478:
479: /**
480: * Get Customer Searches
481: *
482: * @param array<string, mixed> $data
483: *
484: * @return array<int, array<string, mixed>>
485: */
486: public function getCustomerSearches(array $data = []): array {
487: $sql = "SELECT cs.`customer_id`, cs.`keyword`, cs.`category_id`, cs.`products`, cs.`ip`, cs.`date_added`, CONCAT(c.`firstname`, ' ', c.`lastname`) AS `customer` FROM `" . DB_PREFIX . "customer_search` cs LEFT JOIN `" . DB_PREFIX . "customer` c ON (cs.`customer_id` = c.`customer_id`)";
488:
489: $implode = [];
490:
491: if (!empty($data['filter_date_start'])) {
492: $implode[] = "DATE(cs.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
493: }
494:
495: if (!empty($data['filter_date_end'])) {
496: $implode[] = "DATE(cs.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
497: }
498:
499: if (!empty($data['filter_keyword'])) {
500: $implode[] = "cs.`keyword` LIKE '" . $this->db->escape((string)$data['filter_keyword'] . '%') . "'";
501: }
502:
503: if (!empty($data['filter_customer'])) {
504: $implode[] = "CONCAT(c.`firstname`, ' ', c.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
505: }
506:
507: if (!empty($data['filter_ip'])) {
508: $implode[] = "cs.`ip` LIKE '" . $this->db->escape((string)$data['filter_ip']) . "'";
509: }
510:
511: if ($implode) {
512: $sql .= " WHERE " . implode(" AND ", $implode);
513: }
514:
515: $sql .= " ORDER BY cs.`date_added` DESC";
516:
517: if (isset($data['start']) || isset($data['limit'])) {
518: if ($data['start'] < 0) {
519: $data['start'] = 0;
520: }
521:
522: if ($data['limit'] < 1) {
523: $data['limit'] = 20;
524: }
525:
526: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
527: }
528:
529: $query = $this->db->query($sql);
530:
531: return $query->rows;
532: }
533:
534: /**
535: * Get Total Customer Searches
536: *
537: * @param array<string, mixed> $data
538: *
539: * @return int
540: */
541: public function getTotalCustomerSearches(array $data = []): int {
542: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_search` cs LEFT JOIN `" . DB_PREFIX . "customer` c ON (cs.`customer_id` = c.`customer_id`)";
543:
544: $implode = [];
545:
546: if (!empty($data['filter_date_start'])) {
547: $implode[] = "DATE(cs.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
548: }
549:
550: if (!empty($data['filter_date_end'])) {
551: $implode[] = "DATE(cs.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
552: }
553:
554: if (!empty($data['filter_keyword'])) {
555: $implode[] = "cs.`keyword` LIKE '" . $this->db->escape((string)$data['filter_keyword'] . '%') . "'";
556: }
557:
558: if (!empty($data['filter_customer'])) {
559: $implode[] = "CONCAT(c.`firstname`, ' ', c.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
560: }
561:
562: if (!empty($data['filter_ip'])) {
563: $implode[] = "cs.`ip` LIKE '" . $this->db->escape((string)$data['filter_ip']) . "'";
564: }
565:
566: if ($implode) {
567: $sql .= " WHERE " . implode(" AND ", $implode);
568: }
569:
570: $query = $this->db->query($sql);
571:
572: return (int)$query->row['total'];
573: }
574: }
575: