1: | <?php
|
2: | namespace Opencart\Admin\Model\Extension\Opencart\Report;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Customer extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: | |
12: | |
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: | |
38: | |
39: | |
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: | |
69: | |
70: | |
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: | |
98: | |
99: | |
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: | |
125: | |
126: | |
127: | |
128: | |
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: | |
188: | |
189: | |
190: | |
191: | |
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: | |
237: | |
238: | |
239: | |
240: | |
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: | |
286: | |
287: | |
288: | |
289: | |
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: | |
319: | |
320: | |
321: | |
322: | |
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: | |
360: | |
361: | |
362: | |
363: | |
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: | |
393: | |
394: | |
395: | |
396: | |
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: | |
444: | |
445: | |
446: | |
447: | |
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: | |
481: | |
482: | |
483: | |
484: | |
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: | |
536: | |
537: | |
538: | |
539: | |
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: | |