1: <?php
2: namespace Opencart\Admin\Model\Customer;
3: /**
4: * Class Customer
5: *
6: * @package Opencart\Admin\Model\Customer
7: */
8: class Customer extends \Opencart\System\Engine\Model {
9: /**
10: * Add Customer
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return int
15: */
16: public function addCustomer(array $data): int {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "customer` SET `store_id` = '" . (int)$data['store_id'] . "', `customer_group_id` = '" . (int)$data['customer_group_id'] . "', `firstname` = '" . $this->db->escape((string)$data['firstname']) . "', `lastname` = '" . $this->db->escape((string)$data['lastname']) . "', `email` = '" . $this->db->escape((string)$data['email']) . "', `telephone` = '" . $this->db->escape((string)$data['telephone']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `newsletter` = '" . (isset($data['newsletter']) ? (bool)$data['newsletter'] : 0) . "', `password` = '" . $this->db->escape(password_hash(html_entity_decode($data['password'], ENT_QUOTES, 'UTF-8'), PASSWORD_DEFAULT)) . "', `status` = '" . (isset($data['status']) ? (bool)$data['status'] : 0) . "', `safe` = '" . (isset($data['safe']) ? (bool)$data['safe'] : 0) . "', `commenter` = '" . (isset($data['commenter']) ? (bool)$data['commenter'] : 0) . "', `date_added` = NOW()");
18:
19: return $this->db->getLastId();
20: }
21:
22: /**
23: * Edit Customer
24: *
25: * @param int $customer_id
26: * @param array<string, mixed> $data
27: *
28: * @return void
29: */
30: public function editCustomer(int $customer_id, array $data): void {
31: $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `store_id` = '" . (int)$data['store_id'] . "', `customer_group_id` = '" . (int)$data['customer_group_id'] . "', `firstname` = '" . $this->db->escape((string)$data['firstname']) . "', `lastname` = '" . $this->db->escape((string)$data['lastname']) . "', `email` = '" . $this->db->escape((string)$data['email']) . "', `telephone` = '" . $this->db->escape((string)$data['telephone']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `newsletter` = '" . (isset($data['newsletter']) ? (bool)$data['newsletter'] : 0) . "', `status` = '" . (isset($data['status']) ? (bool)$data['status'] : 0) . "', `safe` = '" . (isset($data['safe']) ? (bool)$data['safe'] : 0) . "', `commenter` = '" . (isset($data['commenter']) ? (bool)$data['commenter'] : 0) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
32:
33: if ($data['password']) {
34: $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `password` = '" . $this->db->escape(password_hash(html_entity_decode($data['password'], ENT_QUOTES, 'UTF-8'), PASSWORD_DEFAULT)) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
35: }
36: }
37:
38: /**
39: * Edit Token
40: *
41: * @param int $customer_id
42: * @param string $token
43: *
44: * @return void
45: */
46: public function editToken(int $customer_id, string $token): void {
47: $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `token` = '" . $this->db->escape($token) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
48: }
49:
50: /**
51: * Edit Commenter
52: *
53: * @param int $customer_id
54: * @param bool $status
55: *
56: * @return void
57: */
58: public function editCommenter(int $customer_id, bool $status): void {
59: $this->db->query("UPDATE `" . DB_PREFIX . "customer` SET `commenter` = '" . (bool)$status . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
60: }
61:
62: /**
63: * Delete Customers
64: *
65: * @param int $customer_id
66: *
67: * @return void
68: */
69: public function deleteCustomer(int $customer_id): void {
70: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = '" . (int)$customer_id . "'");
71:
72: $this->deleteActivities($customer_id);
73:
74: $this->deleteAddresses($customer_id);
75: $this->deleteAuthorizes($customer_id);
76: $this->deleteHistories($customer_id);
77: $this->deleteRewards($customer_id);
78: $this->deleteTransactions($customer_id);
79: $this->deleteWishlists($customer_id);
80: $this->deleteIps($customer_id);
81:
82: $this->load->model('marketing/affiliate');
83:
84: $this->model_marketing_affiliate->deleteAffiliate($customer_id);
85:
86: $this->load->model('customer/customer_approval');
87:
88: $this->model_customer_customer_approval->deleteApprovalsByCustomerId($customer_id);
89: }
90:
91: /**
92: * Get Customer
93: *
94: * @param int $customer_id
95: *
96: * @return array<string, mixed>
97: */
98: public function getCustomer(int $customer_id): array {
99: $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = '" . (int)$customer_id . "'");
100:
101: if ($query->num_rows) {
102: return $query->row + ['custom_field' => json_decode($query->row['custom_field'], true)];
103: } else {
104: return [];
105: }
106: }
107:
108: /**
109: * Get Customer By Email
110: *
111: * @param string $email
112: *
113: * @return array<string, mixed>
114: */
115: public function getCustomerByEmail(string $email): array {
116: $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "customer` WHERE LCASE(`email`) = '" . $this->db->escape(oc_strtolower($email)) . "'");
117:
118: if ($query->num_rows) {
119: return $query->row + ['custom_field' => json_decode($query->row['custom_field'], true)];
120: } else {
121: return [];
122: }
123: }
124:
125: /**
126: * Get Customers
127: *
128: * @param array<string, mixed> $data
129: *
130: * @return array<int, array<string, mixed>>
131: */
132: public function getCustomers(array $data = []): array {
133: $sql = "SELECT *, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `name`, `cgd`.`name` AS `customer_group` FROM `" . DB_PREFIX . "customer` `c` LEFT JOIN `" . DB_PREFIX . "customer_group_description` `cgd` ON (`c`.`customer_group_id` = `cgd`.`customer_group_id`) WHERE `cgd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
134:
135: if (!empty($data['filter_name'])) {
136: $sql .= " AND LCASE(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_name']) . '%') . "'";
137: }
138:
139: if (!empty($data['filter_email'])) {
140: $sql .= " AND LCASE(`c`.`email`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_email']) . '%') . "'";
141: }
142:
143: if (isset($data['filter_newsletter']) && $data['filter_newsletter'] !== '') {
144: $sql .= " AND `c`.`newsletter` = '" . (int)$data['filter_newsletter'] . "'";
145: }
146:
147: if (!empty($data['filter_customer_group_id'])) {
148: $sql .= " AND `c`.`customer_group_id` = '" . (int)$data['filter_customer_group_id'] . "'";
149: }
150:
151: if (!empty($data['filter_ip'])) {
152: $sql .= " AND `c`.`customer_id` IN (SELECT `customer_id` FROM `" . DB_PREFIX . "customer_ip` WHERE `ip` = '" . $this->db->escape((string)$data['filter_ip']) . "')";
153: }
154:
155: if (isset($data['filter_status']) && $data['filter_status'] !== '') {
156: $sql .= " AND `c`.`status` = '" . (int)$data['filter_status'] . "'";
157: }
158:
159: if (!empty($data['filter_date_from'])) {
160: $sql .= " AND DATE(`c`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
161: }
162:
163: if (!empty($data['filter_date_to'])) {
164: $sql .= " AND DATE(`c`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
165: }
166:
167: $sort_data = [
168: 'name',
169: 'c.email',
170: 'customer_group',
171: 'c.status',
172: 'c.ip',
173: 'c.date_added'
174: ];
175:
176: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
177: $sql .= " ORDER BY " . $data['sort'];
178: } else {
179: $sql .= " ORDER BY `name`";
180: }
181:
182: if (isset($data['order']) && ($data['order'] == 'DESC')) {
183: $sql .= " DESC";
184: } else {
185: $sql .= " ASC";
186: }
187:
188: if (isset($data['start']) || isset($data['limit'])) {
189: if ($data['start'] < 0) {
190: $data['start'] = 0;
191: }
192:
193: if ($data['limit'] < 1) {
194: $data['limit'] = 20;
195: }
196:
197: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
198: }
199:
200: $customer_data = [];
201:
202: $query = $this->db->query($sql);
203:
204: foreach ($query->rows as $result) {
205: $customer_data[] = $result + ['custom_field' => json_decode($result['custom_field'], true)];
206: }
207:
208: return $customer_data;
209: }
210:
211: /**
212: * Get Total Customers
213: *
214: * @param array<string, mixed> $data
215: *
216: * @return int
217: */
218: public function getTotalCustomers(array $data = []): int {
219: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer` `c`";
220:
221: $implode = [];
222:
223: if (!empty($data['filter_name'])) {
224: $implode[] = "LCASE(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_name']) . '%') . "'";
225: }
226:
227: if (!empty($data['filter_email'])) {
228: $implode[] = "LCASE(`c`.`email`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_email']) . '%') . "'";
229: }
230:
231: if (isset($data['filter_newsletter']) && $data['filter_newsletter'] !== '') {
232: $implode[] = "`c`.`newsletter` = '" . (int)$data['filter_newsletter'] . "'";
233: }
234:
235: if (!empty($data['filter_customer_group_id'])) {
236: $implode[] = "`c`.`customer_group_id` = '" . (int)$data['filter_customer_group_id'] . "'";
237: }
238:
239: if (!empty($data['filter_ip'])) {
240: $implode[] = "`c`.`customer_id` IN (SELECT `customer_id` FROM `" . DB_PREFIX . "customer_ip` WHERE `ip` = '" . $this->db->escape((string)$data['filter_ip']) . "')";
241: }
242:
243: if (isset($data['filter_status']) && $data['filter_status'] !== '') {
244: $implode[] = "`c`.`status` = '" . (int)$data['filter_status'] . "'";
245: }
246:
247: if (!empty($data['filter_date_from'])) {
248: $implode[] = "DATE(`c`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
249: }
250:
251: if (!empty($data['filter_date_to'])) {
252: $implode[] = "DATE(`c`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
253: }
254:
255: if ($implode) {
256: $sql .= " WHERE " . implode(" AND ", $implode);
257: }
258:
259: $query = $this->db->query($sql);
260:
261: return (int)$query->row['total'];
262: }
263:
264: /**
265: * Get Total Customers By Customer Group ID
266: *
267: * @param int $customer_group_id
268: *
269: * @return int
270: */
271: public function getTotalCustomersByCustomerGroupId(int $customer_group_id): int {
272: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer` WHERE `customer_group_id` = '" . (int)$customer_group_id . "'");
273:
274: if ($query->num_rows) {
275: return (int)$query->row['total'];
276: } else {
277: return 0;
278: }
279: }
280:
281: /**
282: * Delete Activities
283: *
284: * @param int $customer_id
285: *
286: * @return void
287: */
288: public function deleteActivities(int $customer_id): void {
289: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_activity` WHERE `customer_id` = '" . (int)$customer_id . "'");
290: }
291:
292: /**
293: * Add Address
294: *
295: * @param int $customer_id
296: * @param array<string, mixed> $data
297: *
298: * @return int
299: */
300: public function addAddress(int $customer_id, array $data): int {
301: $this->db->query("INSERT INTO `" . DB_PREFIX . "address` SET `customer_id` = '" . (int)$customer_id . "', `firstname` = '" . $this->db->escape($data['firstname']) . "', `lastname` = '" . $this->db->escape($data['lastname']) . "', `company` = '" . $this->db->escape($data['company']) . "', `address_1` = '" . $this->db->escape($data['address_1']) . "', `address_2` = '" . $this->db->escape($data['address_2']) . "', `city` = '" . $this->db->escape($data['city']) . "', `postcode` = '" . $this->db->escape($data['postcode']) . "', `country_id` = '" . (int)$data['country_id'] . "', `zone_id` = '" . (int)$data['zone_id'] . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `default` = '" . (!empty($data['default']) ? (bool)$data['default'] : 0) . "'");
302:
303: $address_id = $this->db->getLastId();
304:
305: if (!empty($data['default'])) {
306: $this->db->query("UPDATE `" . DB_PREFIX . "address` SET `default` = '0' WHERE `customer_id` = '" . (int)$customer_id . "' AND `address_id` != '" . (int)$address_id . "'");
307: }
308:
309: return $address_id;
310: }
311:
312: /**
313: * Edit Address
314: *
315: * @param int $customer_id
316: * @param int $address_id
317: * @param array<string, mixed> $data
318: *
319: * @return void
320: */
321: public function editAddress(int $customer_id, int $address_id, array $data): void {
322: $this->db->query("UPDATE `" . DB_PREFIX . "address` SET `firstname` = '" . $this->db->escape($data['firstname']) . "', `lastname` = '" . $this->db->escape($data['lastname']) . "', `company` = '" . $this->db->escape($data['company']) . "', `address_1` = '" . $this->db->escape($data['address_1']) . "', `address_2` = '" . $this->db->escape($data['address_2']) . "', `city` = '" . $this->db->escape($data['city']) . "', `postcode` = '" . $this->db->escape($data['postcode']) . "', `country_id` = '" . (int)$data['country_id'] . "', `zone_id` = '" . (int)$data['zone_id'] . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `default` = '" . (!empty($data['default']) ? (bool)$data['default'] : 0) . "' WHERE `address_id` = '" . (int)$address_id . "'");
323:
324: if (!empty($data['default'])) {
325: $this->db->query("UPDATE `" . DB_PREFIX . "address` SET `default` = '0' WHERE `customer_id` = '" . (int)$customer_id . "' AND `address_id` != '" . (int)$address_id . "'");
326: }
327: }
328:
329: /**
330: * Delete Address
331: *
332: * @param int $customer_id
333: * @param int $address_id
334: *
335: * @return void
336: */
337: public function deleteAddresses(int $customer_id, int $address_id = 0): void {
338: $sql = "DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` = '" . (int)$customer_id . "'";
339:
340: if ($address_id) {
341: $sql .= " AND `address_id` = '" . (int)$address_id . "'";
342: }
343:
344: $this->db->query($sql);
345: }
346:
347: /**
348: * Get Address
349: *
350: * @param int $address_id
351: *
352: * @return array<string, mixed>
353: */
354: public function getAddress(int $address_id): array {
355: $address_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "address` WHERE `address_id` = '" . (int)$address_id . "'");
356:
357: if ($address_query->num_rows) {
358: $country_query = $this->db->query("SELECT *, `c`.`name` FROM `" . DB_PREFIX . "country` `c` LEFT JOIN `" . DB_PREFIX . "address_format` `af` ON (`c`.`address_format_id` = `af`.`address_format_id`) WHERE `country_id` = '" . (int)$address_query->row['country_id'] . "'");
359:
360: if ($country_query->num_rows) {
361: $country = $country_query->row['name'];
362: $iso_code_2 = $country_query->row['iso_code_2'];
363: $iso_code_3 = $country_query->row['iso_code_3'];
364: $address_format = $country_query->row['address_format'];
365: } else {
366: $country = '';
367: $iso_code_2 = '';
368: $iso_code_3 = '';
369: $address_format = '';
370: }
371:
372: $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE `zone_id` = '" . (int)$address_query->row['zone_id'] . "'");
373:
374: if ($zone_query->num_rows) {
375: $zone = $zone_query->row['name'];
376: $zone_code = $zone_query->row['code'];
377: } else {
378: $zone = '';
379: $zone_code = '';
380: }
381:
382: return [
383: 'address_id' => $address_query->row['address_id'],
384: 'customer_id' => $address_query->row['customer_id'],
385: 'firstname' => $address_query->row['firstname'],
386: 'lastname' => $address_query->row['lastname'],
387: 'company' => $address_query->row['company'],
388: 'address_1' => $address_query->row['address_1'],
389: 'address_2' => $address_query->row['address_2'],
390: 'postcode' => $address_query->row['postcode'],
391: 'city' => $address_query->row['city'],
392: 'zone_id' => $address_query->row['zone_id'],
393: 'zone' => $zone,
394: 'zone_code' => $zone_code,
395: 'country_id' => $address_query->row['country_id'],
396: 'country' => $country,
397: 'iso_code_2' => $iso_code_2,
398: 'iso_code_3' => $iso_code_3,
399: 'address_format' => $address_format,
400: 'custom_field' => json_decode($address_query->row['custom_field'], true),
401: 'default' => $address_query->row['default']
402: ];
403: }
404:
405: return [];
406: }
407:
408: /**
409: * Get Addresses
410: *
411: * @param int $customer_id
412: *
413: * @return array<int, array<string, mixed>>
414: */
415: public function getAddresses(int $customer_id): array {
416: $address_data = [];
417:
418: $query = $this->db->query("SELECT `address_id` FROM `" . DB_PREFIX . "address` WHERE `customer_id` = '" . (int)$customer_id . "'");
419:
420: foreach ($query->rows as $result) {
421: $address_info = $this->getAddress($result['address_id']);
422:
423: if ($address_info) {
424: $address_data[] = $address_info;
425: }
426: }
427:
428: return $address_data;
429: }
430:
431: /**
432: * Get Total Addresses
433: *
434: * @param int $customer_id
435: *
436: * @return int
437: */
438: public function getTotalAddresses(int $customer_id): int {
439: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "address` WHERE `customer_id` = '" . (int)$customer_id . "'");
440:
441: return (int)$query->row['total'];
442: }
443:
444: /**
445: * Get Total Address By Country ID
446: *
447: * @param int $country_id
448: *
449: * @return int
450: */
451: public function getTotalAddressesByCountryId(int $country_id): int {
452: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "address` WHERE `country_id` = '" . (int)$country_id . "'");
453:
454: return (int)$query->row['total'];
455: }
456:
457: /**
458: * Get Total Addresses By Zone ID
459: *
460: * @param int $zone_id
461: *
462: * @return int
463: */
464: public function getTotalAddressesByZoneId(int $zone_id): int {
465: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "address` WHERE `zone_id` = '" . (int)$zone_id . "'");
466:
467: return (int)$query->row['total'];
468: }
469:
470: /**
471: * Add History
472: *
473: * @param int $customer_id
474: * @param string $comment
475: *
476: * @return void
477: */
478: public function addHistory(int $customer_id, string $comment): void {
479: $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_history` SET `customer_id` = '" . (int)$customer_id . "', `comment` = '" . $this->db->escape(strip_tags($comment)) . "', `date_added` = NOW()");
480: }
481:
482: /**
483: * Delete Customer Histories
484: *
485: * @param int $customer_id
486: *
487: * @return void
488: */
489: public function deleteHistories(int $customer_id): void {
490: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = '" . (int)$customer_id . "'");
491: }
492:
493: /**
494: * Get Histories
495: *
496: * @param int $customer_id
497: * @param int $start
498: * @param int $limit
499: *
500: * @return array<int, array<string, mixed>>
501: */
502: public function getHistories(int $customer_id, int $start = 0, int $limit = 10): array {
503: if ($start < 0) {
504: $start = 0;
505: }
506:
507: if ($limit < 1) {
508: $limit = 10;
509: }
510:
511: $query = $this->db->query("SELECT `comment`, `date_added` FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
512:
513: return $query->rows;
514: }
515:
516: /**
517: * Get Total Histories
518: *
519: * @param int $customer_id
520: *
521: * @return int
522: */
523: public function getTotalHistories(int $customer_id): int {
524: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = '" . (int)$customer_id . "'");
525:
526: return (int)$query->row['total'];
527: }
528:
529: /**
530: * Delete Wishlists
531: *
532: * @param int $customer_id
533: *
534: * @return void
535: */
536: public function deleteWishlists(int $customer_id): void {
537: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_wishlist` WHERE `customer_id` = '" . (int)$customer_id . "'");
538: }
539:
540: /**
541: * Add Transaction
542: *
543: * @param int $customer_id
544: * @param string $description
545: * @param float $amount
546: * @param int $order_id
547: *
548: * @return void
549: */
550: public function addTransaction(int $customer_id, string $description = '', float $amount = 0, int $order_id = 0): void {
551: $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_transaction` SET `customer_id` = '" . (int)$customer_id . "', `order_id` = '" . (int)$order_id . "', `description` = '" . $this->db->escape($description) . "', `amount` = '" . (float)$amount . "', `date_added` = NOW()");
552: }
553:
554: /**
555: * Delete Transactions
556: *
557: * @param int $customer_id
558: *
559: * @return void
560: */
561: public function deleteTransactions(int $customer_id): void {
562: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "'");
563: }
564:
565: /**
566: * Delete Transactions By Order ID
567: *
568: * @param int $order_id
569: *
570: * @return void
571: */
572: public function deleteTransactionsByOrderId(int $order_id): void {
573: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `order_id` = '" . (int)$order_id . "'");
574: }
575:
576: /**
577: * Get Transactions
578: *
579: * @param int $customer_id
580: * @param int $start
581: * @param int $limit
582: *
583: * @return array<int, array<string, mixed>>
584: */
585: public function getTransactions(int $customer_id, int $start = 0, int $limit = 10): array {
586: if ($start < 0) {
587: $start = 0;
588: }
589:
590: if ($limit < 1) {
591: $limit = 10;
592: }
593:
594: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
595:
596: return $query->rows;
597: }
598:
599: /**
600: * Get Total Transactions
601: *
602: * @param int $customer_id
603: *
604: * @return int
605: */
606: public function getTotalTransactions(int $customer_id): int {
607: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "'");
608:
609: return (int)$query->row['total'];
610: }
611:
612: /**
613: * Get Transaction Total
614: *
615: * @param int $customer_id
616: *
617: * @return float
618: */
619: public function getTransactionTotal(int $customer_id): float {
620: $query = $this->db->query("SELECT SUM(`amount`) AS `total` FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = '" . (int)$customer_id . "'");
621:
622: return (float)$query->row['total'];
623: }
624:
625: /**
626: * Get Total Transactions By Order ID
627: *
628: * @param int $order_id
629: *
630: * @return int
631: */
632: public function getTotalTransactionsByOrderId(int $order_id): int {
633: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_transaction` WHERE `order_id` = '" . (int)$order_id . "'");
634:
635: return (int)$query->row['total'];
636: }
637:
638: /**
639: * Add Reward
640: *
641: * @param int $customer_id
642: * @param string $description
643: * @param int $points
644: * @param int $order_id
645: *
646: * @return void
647: */
648: public function addReward(int $customer_id, string $description = '', int $points = 0, int $order_id = 0): void {
649: $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_reward` SET `customer_id` = '" . (int)$customer_id . "', `order_id` = '" . (int)$order_id . "', `points` = '" . (int)$points . "', `description` = '" . $this->db->escape($description) . "', `date_added` = NOW()");
650: }
651:
652: /**
653: * Delete Rewards
654: *
655: * @param int $customer_id
656: *
657: * @return void
658: */
659: public function deleteRewards(int $customer_id): void {
660: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "'");
661: }
662:
663: /**
664: * Delete Rewards By Order ID
665: *
666: * @param int $order_id
667: *
668: * @return void
669: */
670: public function deleteRewardsByOrderId(int $order_id): void {
671: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `order_id` = '" . (int)$order_id . "' AND `points` > '0'");
672: }
673:
674: /**
675: * Get Rewards
676: *
677: * @param int $customer_id
678: * @param int $start
679: * @param int $limit
680: *
681: * @return array<int, array<string, mixed>>
682: */
683: public function getRewards(int $customer_id, int $start = 0, int $limit = 10): array {
684: if ($start < 0) {
685: $start = 0;
686: }
687:
688: if ($limit < 1) {
689: $limit = 10;
690: }
691:
692: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
693:
694: return $query->rows;
695: }
696:
697: /**
698: * Get Total Rewards
699: *
700: * @param int $customer_id
701: *
702: * @return int
703: */
704: public function getTotalRewards(int $customer_id): int {
705: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "'");
706:
707: return (int)$query->row['total'];
708: }
709:
710: /**
711: * Get Reward Total
712: *
713: * @param int $customer_id
714: *
715: * @return int
716: */
717: public function getRewardTotal(int $customer_id): int {
718: $query = $this->db->query("SELECT SUM(points) AS `total` FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = '" . (int)$customer_id . "'");
719:
720: return (int)$query->row['total'];
721: }
722:
723: /**
724: * Get Total Rewards By Order ID
725: *
726: * @param int $order_id
727: *
728: * @return int
729: */
730: public function getTotalRewardsByOrderId(int $order_id): int {
731: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_reward` WHERE `order_id` = '" . (int)$order_id . "' AND `points` > '0'");
732:
733: return (int)$query->row['total'];
734: }
735:
736: /**
737: * Delete Ips
738: *
739: * @param int $customer_id
740: *
741: * @return void
742: */
743: public function deleteIps(int $customer_id): void {
744: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = '" . (int)$customer_id . "'");
745: }
746:
747: /**
748: * Get Ips
749: *
750: * @param int $customer_id
751: * @param int $start
752: * @param int $limit
753: *
754: * @return array<int, array<string, mixed>>
755: */
756: public function getIps(int $customer_id, int $start = 0, int $limit = 10): array {
757: if ($start < 0) {
758: $start = 0;
759: }
760: if ($limit < 1) {
761: $limit = 10;
762: }
763:
764: $query = $this->db->query("SELECT `ip`, `store_id`, `country`, `date_added` FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
765:
766: return $query->rows;
767: }
768:
769: /**
770: * Get Total Ips
771: *
772: * @param int $customer_id
773: *
774: * @return int
775: */
776: public function getTotalIps(int $customer_id): int {
777: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = '" . (int)$customer_id . "'");
778:
779: return (int)$query->row['total'];
780: }
781:
782: /**
783: * Get Total Customers By Ip
784: *
785: * @param string $ip
786: *
787: * @return int
788: */
789: public function getTotalCustomersByIp(string $ip): int {
790: $query = $this->db->query("SELECT COUNT(DISTINCT `customer_id`) AS `total` FROM `" . DB_PREFIX . "customer_ip` WHERE `ip` = '" . $this->db->escape($ip) . "'");
791:
792: return (int)$query->row['total'];
793: }
794:
795: /**
796: * Get Total Login Attempts
797: *
798: * @param string $email
799: *
800: * @return array<string, mixed>
801: */
802: public function getTotalLoginAttempts(string $email): array {
803: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_login` WHERE `email` = '" . $this->db->escape(oc_strtolower($email)) . "'");
804:
805: return $query->row;
806: }
807:
808: /**
809: * Delete Login Attempts
810: *
811: * @param string $email
812: *
813: * @return void
814: */
815: public function deleteLoginAttempts(string $email): void {
816: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_login` WHERE `email` = '" . $this->db->escape(oc_strtolower($email)) . "'");
817: }
818:
819: /**
820: * Add Authorize
821: *
822: * @param int $customer_id
823: * @param array<string, mixed> $data
824: *
825: * @return void
826: */
827: public function addAuthorize(int $customer_id, array $data): void {
828: $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_authorize` SET `customer_id` = '" . (int)$customer_id . "', `token` = '" . $this->db->escape($data['token']) . "', `ip` = '" . $this->db->escape($data['ip']) . "', `user_agent` = '" . $this->db->escape($data['user_agent']) . "', `date_added` = NOW()");
829: }
830:
831: /**
832: * Edit Authorize Status
833: *
834: * @param int $customer_authorize_id
835: * @param bool $status
836: *
837: * @return void
838: */
839: public function editAuthorizeStatus(int $customer_authorize_id, bool $status): void {
840: $this->db->query("UPDATE `" . DB_PREFIX . "customer_authorize` SET `status` = '" . (bool)$status . "' WHERE `customer_authorize_id` = '" . (int)$customer_authorize_id . "'");
841: }
842:
843: /**
844: * Edit Authorize Total
845: *
846: * @param int $customer_authorize_id
847: * @param int $total
848: *
849: * @return void
850: */
851: public function editAuthorizeTotal(int $customer_authorize_id, int $total): void {
852: $this->db->query("UPDATE `" . DB_PREFIX . "customer_authorize` SET `total` = '" . (int)$total . "' WHERE `customer_authorize_id` = '" . (int)$customer_authorize_id . "'");
853: }
854:
855: /**
856: * Reset Authorizes
857: *
858: * @param int $customer_id
859: *
860: * @return void
861: */
862: public function resetAuthorizes(int $customer_id): void {
863: $this->db->query("UPDATE `" . DB_PREFIX . "customer_authorize` SET `total` = '0' WHERE `customer_id` = '" . (int)$customer_id . "'");
864: }
865:
866: /**
867: * Delete Authorizes
868: *
869: * @param int $customer_id
870: * @param int $customer_authorize_id
871: *
872: * @return void
873: */
874: public function deleteAuthorizes(int $customer_id, int $customer_authorize_id = 0): void {
875: $sql = "DELETE FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "'";
876:
877: if ($customer_authorize_id) {
878: $sql .= " AND `customer_authorize_id` = '" . (int)$customer_authorize_id . "'";
879: }
880:
881: $this->db->query($sql);
882: }
883:
884: /**
885: * Get Authorize By Token
886: *
887: * @param int $customer_id
888: * @param string $token
889: *
890: * @return array<string, mixed>
891: */
892: public function getAuthorizeByToken(int $customer_id, string $token): array {
893: $query = $this->db->query("SELECT *, (SELECT SUM(`total`) FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "') AS `attempts` FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "' AND `token` = '" . $this->db->escape($token) . "'");
894:
895: return $query->row;
896: }
897:
898: /**
899: * Get Authorizes
900: *
901: * @param int $customer_id
902: * @param int $start
903: * @param int $limit
904: *
905: * @return array<int, array<string, mixed>>
906: */
907: public function getAuthorizes(int $customer_id, int $start = 0, int $limit = 10): array {
908: if ($start < 0) {
909: $start = 0;
910: }
911:
912: if ($limit < 1) {
913: $limit = 10;
914: }
915:
916: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "' LIMIT " . (int)$start . "," . (int)$limit);
917:
918: if ($query->num_rows) {
919: return $query->rows;
920: } else {
921: return [];
922: }
923: }
924:
925: /**
926: * Get Total Authorizes
927: *
928: * @param int $customer_id
929: *
930: * @return int
931: */
932: public function getTotalAuthorizes(int $customer_id): int {
933: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_authorize` WHERE `customer_id` = '" . (int)$customer_id . "'");
934:
935: if ($query->num_rows) {
936: return (int)$query->row['total'];
937: } else {
938: return 0;
939: }
940: }
941: }
942: