1: | <?php
|
2: | namespace Opencart\Admin\Model\Customer;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Customer extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: | |
12: | |
13: | |
14: | |
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: | |
24: | |
25: | |
26: | |
27: | |
28: | |
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: | |
40: | |
41: | |
42: | |
43: | |
44: | |
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: | |
52: | |
53: | |
54: | |
55: | |
56: | |
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: | |
64: | |
65: | |
66: | |
67: | |
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: | |
93: | |
94: | |
95: | |
96: | |
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: | |
110: | |
111: | |
112: | |
113: | |
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: | |
127: | |
128: | |
129: | |
130: | |
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: | |
213: | |
214: | |
215: | |
216: | |
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: | |
266: | |
267: | |
268: | |
269: | |
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: | |
283: | |
284: | |
285: | |
286: | |
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: | |
294: | |
295: | |
296: | |
297: | |
298: | |
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: | |
314: | |
315: | |
316: | |
317: | |
318: | |
319: | |
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: | |
331: | |
332: | |
333: | |
334: | |
335: | |
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: | |
349: | |
350: | |
351: | |
352: | |
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: | |
410: | |
411: | |
412: | |
413: | |
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: | |
433: | |
434: | |
435: | |
436: | |
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: | |
446: | |
447: | |
448: | |
449: | |
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: | |
459: | |
460: | |
461: | |
462: | |
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: | |
472: | |
473: | |
474: | |
475: | |
476: | |
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: | |
484: | |
485: | |
486: | |
487: | |
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: | |
495: | |
496: | |
497: | |
498: | |
499: | |
500: | |
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: | |
518: | |
519: | |
520: | |
521: | |
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: | |
531: | |
532: | |
533: | |
534: | |
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: | |
542: | |
543: | |
544: | |
545: | |
546: | |
547: | |
548: | |
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: | |
556: | |
557: | |
558: | |
559: | |
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: | |
567: | |
568: | |
569: | |
570: | |
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: | |
578: | |
579: | |
580: | |
581: | |
582: | |
583: | |
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: | |
601: | |
602: | |
603: | |
604: | |
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: | |
614: | |
615: | |
616: | |
617: | |
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: | |
627: | |
628: | |
629: | |
630: | |
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: | |
640: | |
641: | |
642: | |
643: | |
644: | |
645: | |
646: | |
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: | |
654: | |
655: | |
656: | |
657: | |
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: | |
665: | |
666: | |
667: | |
668: | |
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: | |
676: | |
677: | |
678: | |
679: | |
680: | |
681: | |
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: | |
699: | |
700: | |
701: | |
702: | |
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: | |
712: | |
713: | |
714: | |
715: | |
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: | |
725: | |
726: | |
727: | |
728: | |
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: | |
738: | |
739: | |
740: | |
741: | |
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: | |
749: | |
750: | |
751: | |
752: | |
753: | |
754: | |
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: | |
771: | |
772: | |
773: | |
774: | |
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: | |
784: | |
785: | |
786: | |
787: | |
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: | |
797: | |
798: | |
799: | |
800: | |
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: | |
810: | |
811: | |
812: | |
813: | |
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: | |
821: | |
822: | |
823: | |
824: | |
825: | |
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: | |
833: | |
834: | |
835: | |
836: | |
837: | |
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: | |
845: | |
846: | |
847: | |
848: | |
849: | |
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: | |
857: | |
858: | |
859: | |
860: | |
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: | |
868: | |
869: | |
870: | |
871: | |
872: | |
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: | |
886: | |
887: | |
888: | |
889: | |
890: | |
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: | |
900: | |
901: | |
902: | |
903: | |
904: | |
905: | |
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: | |
927: | |
928: | |
929: | |
930: | |
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: | |