1: <?php
2: namespace Opencart\Admin\Model\Sale;
3: /**
4: * Class Order
5: *
6: * @package Opencart\Admin\Model\Sale
7: */
8: class Order extends \Opencart\System\Engine\Model {
9: /**
10: * Get Order
11: *
12: * @param int $order_id
13: *
14: * @return array<string, mixed>
15: */
16: public function getOrder(int $order_id): array {
17: $order_query = $this->db->query("SELECT *, (SELECT `os`.`name` FROM `" . DB_PREFIX . "order_status` `os` WHERE `os`.`order_status_id` = `o`.`order_status_id` AND `os`.`language_id` = '" . (int)$this->config->get('config_language_id') . "') AS `order_status` FROM `" . DB_PREFIX . "order` `o` WHERE `o`.`order_id` = '" . (int)$order_id . "'");
18:
19: if ($order_query->num_rows) {
20: $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE `country_id` = '" . (int)$order_query->row['payment_country_id'] . "'");
21:
22: if ($country_query->num_rows) {
23: $payment_iso_code_2 = $country_query->row['iso_code_2'];
24: $payment_iso_code_3 = $country_query->row['iso_code_3'];
25: } else {
26: $payment_iso_code_2 = '';
27: $payment_iso_code_3 = '';
28: }
29:
30: $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE `zone_id` = '" . (int)$order_query->row['payment_zone_id'] . "'");
31:
32: if ($zone_query->num_rows) {
33: $payment_zone_code = $zone_query->row['code'];
34: } else {
35: $payment_zone_code = '';
36: }
37:
38: $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE `country_id` = '" . (int)$order_query->row['shipping_country_id'] . "'");
39:
40: if ($country_query->num_rows) {
41: $shipping_iso_code_2 = $country_query->row['iso_code_2'];
42: $shipping_iso_code_3 = $country_query->row['iso_code_3'];
43: } else {
44: $shipping_iso_code_2 = '';
45: $shipping_iso_code_3 = '';
46: }
47:
48: $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE `zone_id` = '" . (int)$order_query->row['shipping_zone_id'] . "'");
49:
50: if ($zone_query->num_rows) {
51: $shipping_zone_code = $zone_query->row['code'];
52: } else {
53: $shipping_zone_code = '';
54: }
55:
56: $reward = 0;
57:
58: $order_product_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "'");
59:
60: foreach ($order_product_query->rows as $product) {
61: $reward += $product['reward'];
62: }
63:
64: $this->load->model('customer/customer');
65:
66: $affiliate_info = $this->model_customer_customer->getCustomer($order_query->row['affiliate_id']);
67:
68: if ($affiliate_info) {
69: $affiliate = $affiliate_info['firstname'] . ' ' . $affiliate_info['lastname'];
70: } else {
71: $affiliate = '';
72: }
73:
74: $this->load->model('localisation/language');
75:
76: $language_info = $this->model_localisation_language->getLanguage($order_query->row['language_id']);
77:
78: if ($language_info) {
79: $language_code = $language_info['code'];
80: } else {
81: $language_code = $this->config->get('config_language');
82: }
83:
84: return [
85: 'order_id' => $order_query->row['order_id'],
86: 'invoice_no' => $order_query->row['invoice_no'],
87: 'invoice_prefix' => $order_query->row['invoice_prefix'],
88: 'store_id' => $order_query->row['store_id'],
89: 'store_name' => $order_query->row['store_name'],
90: 'store_url' => $order_query->row['store_url'],
91: 'customer_id' => $order_query->row['customer_id'],
92: 'customer_group_id' => $order_query->row['customer_group_id'],
93: 'firstname' => $order_query->row['firstname'],
94: 'lastname' => $order_query->row['lastname'],
95: 'email' => $order_query->row['email'],
96: 'telephone' => $order_query->row['telephone'],
97: 'custom_field' => json_decode($order_query->row['custom_field'], true),
98: 'payment_address_id' => $order_query->row['payment_address_id'],
99: 'payment_firstname' => $order_query->row['payment_firstname'],
100: 'payment_lastname' => $order_query->row['payment_lastname'],
101: 'payment_company' => $order_query->row['payment_company'],
102: 'payment_address_1' => $order_query->row['payment_address_1'],
103: 'payment_address_2' => $order_query->row['payment_address_2'],
104: 'payment_postcode' => $order_query->row['payment_postcode'],
105: 'payment_city' => $order_query->row['payment_city'],
106: 'payment_zone_id' => $order_query->row['payment_zone_id'],
107: 'payment_zone' => $order_query->row['payment_zone'],
108: 'payment_zone_code' => $payment_zone_code,
109: 'payment_country_id' => $order_query->row['payment_country_id'],
110: 'payment_country' => $order_query->row['payment_country'],
111: 'payment_iso_code_2' => $payment_iso_code_2,
112: 'payment_iso_code_3' => $payment_iso_code_3,
113: 'payment_address_format' => $order_query->row['payment_address_format'],
114: 'payment_custom_field' => json_decode($order_query->row['payment_custom_field'], true),
115: 'payment_method' => json_decode($order_query->row['payment_method'], true),
116: 'shipping_address_id' => $order_query->row['shipping_address_id'],
117: 'shipping_firstname' => $order_query->row['shipping_firstname'],
118: 'shipping_lastname' => $order_query->row['shipping_lastname'],
119: 'shipping_company' => $order_query->row['shipping_company'],
120: 'shipping_address_1' => $order_query->row['shipping_address_1'],
121: 'shipping_address_2' => $order_query->row['shipping_address_2'],
122: 'shipping_postcode' => $order_query->row['shipping_postcode'],
123: 'shipping_city' => $order_query->row['shipping_city'],
124: 'shipping_zone_id' => $order_query->row['shipping_zone_id'],
125: 'shipping_zone' => $order_query->row['shipping_zone'],
126: 'shipping_zone_code' => $shipping_zone_code,
127: 'shipping_country_id' => $order_query->row['shipping_country_id'],
128: 'shipping_country' => $order_query->row['shipping_country'],
129: 'shipping_iso_code_2' => $shipping_iso_code_2,
130: 'shipping_iso_code_3' => $shipping_iso_code_3,
131: 'shipping_address_format' => $order_query->row['shipping_address_format'],
132: 'shipping_custom_field' => json_decode($order_query->row['shipping_custom_field'], true),
133: 'shipping_method' => json_decode($order_query->row['shipping_method'], true),
134: 'comment' => $order_query->row['comment'],
135: 'total' => $order_query->row['total'],
136: 'reward' => $reward,
137: 'order_status_id' => $order_query->row['order_status_id'],
138: 'order_status' => $order_query->row['order_status'],
139: 'affiliate_id' => $order_query->row['affiliate_id'],
140: 'affiliate' => $affiliate,
141: 'commission' => $order_query->row['commission'],
142: 'language_id' => $order_query->row['language_id'],
143: 'language_code' => $language_code,
144: 'currency_id' => $order_query->row['currency_id'],
145: 'currency_code' => $order_query->row['currency_code'],
146: 'currency_value' => $order_query->row['currency_value'],
147: 'ip' => $order_query->row['ip'],
148: 'forwarded_ip' => $order_query->row['forwarded_ip'],
149: 'user_agent' => $order_query->row['user_agent'],
150: 'accept_language' => $order_query->row['accept_language'],
151: 'date_added' => $order_query->row['date_added'],
152: 'date_modified' => $order_query->row['date_modified']
153: ];
154: } else {
155: return [];
156: }
157: }
158:
159: /**
160: * Get Orders
161: *
162: * @param array<string, mixed> $data
163: *
164: * @return array<int, array<string, mixed>>
165: */
166: public function getOrders(array $data = []): array {
167: $sql = "SELECT `o`.`order_id`, CONCAT(`o`.`firstname`, ' ', `o`.`lastname`) AS customer, (SELECT `os`.`name` FROM `" . DB_PREFIX . "order_status` `os` WHERE `os`.`order_status_id` = `o`.`order_status_id` AND `os`.`language_id` = '" . (int)$this->config->get('config_language_id') . "') AS order_status, `o`.`store_name`, `o`.`custom_field`, `o`.`payment_method`, `o`.`payment_custom_field`, `o`.`shipping_method`, `o`.`shipping_custom_field`, `o`.`total`, `o`.`currency_code`, `o`.`currency_value`, `o`.`date_added`, `o`.`date_modified` FROM `" . DB_PREFIX . "order` `o`";
168:
169: if (!empty($data['filter_order_status'])) {
170: $implode = [];
171:
172: $order_statuses = explode(',', $data['filter_order_status']);
173: $order_statuses = array_filter($order_statuses);
174:
175: foreach ($order_statuses as $order_status_id) {
176: $implode[] = "`o`.`order_status_id` = '" . (int)$order_status_id . "'";
177: }
178:
179: if ($implode) {
180: $sql .= " WHERE (" . implode(" OR ", $implode) . ")";
181: }
182: } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
183: $sql .= " WHERE `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
184: } else {
185: $sql .= " WHERE `o`.`order_status_id` > '0'";
186: }
187:
188: if (!empty($data['filter_order_id'])) {
189: $sql .= " AND `o`.`order_id` = '" . (int)$data['filter_order_id'] . "'";
190: }
191:
192: if (isset($data['filter_store_id']) && $data['filter_store_id'] !== '') {
193: $sql .= " AND `o`.`store_id` = '" . (int)$data['filter_store_id'] . "'";
194: }
195:
196: if (!empty($data['filter_customer_id'])) {
197: $sql .= " AND `o`.`customer_id` = '" . (int)$data['filter_customer_id'] . "'";
198: }
199:
200: if (!empty($data['filter_customer'])) {
201: $sql .= " AND LCASE(CONCAT(`o`.`firstname`, ' ', `o`.`lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_customer']) . '%') . "'";
202: }
203:
204: if (!empty($data['filter_email'])) {
205: $sql .= " AND LCASE(`o`.`email`) LIKE '" . $this->db->escape('%' . (string)$data['filter_email'] . '%') . "'";
206: }
207:
208: if (!empty($data['filter_date_from'])) {
209: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
210: }
211:
212: if (!empty($data['filter_date_to'])) {
213: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
214: }
215:
216: if (!empty($data['filter_total'])) {
217: $sql .= " AND `o`.`total` = '" . (float)$data['filter_total'] . "'";
218: }
219:
220: $sort_data = [
221: 'o.order_id',
222: 'o.store_name',
223: 'customer',
224: 'order_status',
225: 'o.date_added',
226: 'o.date_modified',
227: 'o.total'
228: ];
229:
230: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
231: $sql .= " ORDER BY " . $data['sort'];
232: } else {
233: $sql .= " ORDER BY `o`.`order_id`";
234: }
235:
236: if (isset($data['order']) && ($data['order'] == 'DESC')) {
237: $sql .= " DESC";
238: } else {
239: $sql .= " ASC";
240: }
241:
242: if (isset($data['start']) || isset($data['limit'])) {
243: if ($data['start'] < 0) {
244: $data['start'] = 0;
245: }
246:
247: if ($data['limit'] < 1) {
248: $data['limit'] = 20;
249: }
250:
251: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
252: }
253:
254: $order_data = [];
255:
256: $query = $this->db->query($sql);
257:
258: foreach ($query->rows as $key => $result) {
259: $order_data[$key] = $result;
260:
261: $order_data[$key]['custom_field'] = json_decode($result['custom_field'], true);
262: $order_data[$key]['payment_custom_field'] = json_decode($result['payment_custom_field'], true);
263: $order_data[$key]['payment_method'] = json_decode($result['payment_method'], true);
264: $order_data[$key]['shipping_custom_field'] = json_decode($result['shipping_custom_field'], true);
265: $order_data[$key]['shipping_method'] = json_decode($result['shipping_method'], true);
266: }
267:
268: return $order_data;
269: }
270:
271: /**
272: * Get Orders By Subscription ID
273: *
274: * @param int $subscription_id
275: *
276: * @return array<int, array<string, mixed>>
277: */
278: public function getOrdersBySubscriptionId(int $subscription_id): array {
279: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE `subscription_id` = '" . (int)$subscription_id . "'");
280:
281: return $query->rows;
282: }
283:
284: /**
285: * Get Total Orders By Subscription ID
286: *
287: * @param int $subscription_id
288: *
289: * @return int
290: */
291: public function getTotalOrdersBySubscriptionId(int $subscription_id): int {
292: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `subscription_id` = '" . (int)$subscription_id . "'");
293:
294: return (int)$query->row['total'];
295: }
296:
297: /**
298: * Get Products
299: *
300: * @param int $order_id
301: *
302: * @return array<int, array<string, mixed>>
303: */
304: public function getProducts(int $order_id): array {
305: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "' ORDER BY order_product_id ASC");
306:
307: return $query->rows;
308: }
309:
310: /**
311: * Get Total Products By Product ID
312: *
313: * @param int $product_id
314: *
315: * @return int
316: */
317: public function getTotalProductsByProductId(int $product_id): int {
318: $sql = "SELECT SUM(`op`.`quantity`) AS `total` FROM `" . DB_PREFIX . "order_product` `op` LEFT JOIN `" . DB_PREFIX . "order` `o` ON (`op`.`order_id` = `o`.`order_id`) WHERE `op`.`product_id` = '" . (int)$product_id . "' AND `order_status_id` > '0'";
319:
320: $query = $this->db->query($sql);
321:
322: return (int)$query->row['total'];
323: }
324:
325: /**
326: * Get Options
327: *
328: * @param int $order_id
329: * @param int $order_product_id
330: *
331: * @return array<int, array<string, mixed>>
332: */
333: public function getOptions(int $order_id, int $order_product_id): array {
334: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_option` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
335:
336: return $query->rows;
337: }
338:
339: /**
340: * Get Subscription
341: *
342: * @param int $order_id
343: * @param int $order_product_id
344: *
345: * @return array<string, mixed>
346: */
347: public function getSubscription(int $order_id, int $order_product_id): array {
348: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_subscription` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
349:
350: return $query->row;
351: }
352:
353: /**
354: * Get Vouchers
355: *
356: * @param int $order_id
357: *
358: * @return array<int, array<string, mixed>>
359: */
360: public function getVouchers(int $order_id): array {
361: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE `order_id` = '" . (int)$order_id . "'");
362:
363: return $query->rows;
364: }
365:
366: /**
367: * Get Voucher By Voucher ID
368: *
369: * @param int $voucher_id
370: *
371: * @return array<string, mixed>
372: */
373: public function getVoucherByVoucherId(int $voucher_id): array {
374: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE `voucher_id` = '" . (int)$voucher_id . "'");
375:
376: return $query->row;
377: }
378:
379: /**
380: * Get Totals
381: *
382: * @param int $order_id
383: *
384: * @return array<int, array<string, mixed>>
385: */
386: public function getTotals(int $order_id): array {
387: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_total` WHERE `order_id` = '" . (int)$order_id . "' ORDER BY `sort_order`");
388:
389: return $query->rows;
390: }
391:
392: /**
393: * Get Total Orders
394: *
395: * @param array<string, mixed> $data
396: *
397: * @return int
398: */
399: public function getTotalOrders(array $data = []): int {
400: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order`";
401:
402: if (!empty($data['filter_order_status'])) {
403: $implode = [];
404:
405: $order_statuses = explode(',', $data['filter_order_status']);
406: $order_statuses = array_filter($order_statuses);
407:
408: foreach ($order_statuses as $order_status_id) {
409: $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
410: }
411:
412: if ($implode) {
413: $sql .= " WHERE (" . implode(" OR ", $implode) . ")";
414: }
415: } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
416: $sql .= " WHERE `order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
417: } else {
418: $sql .= " WHERE `order_status_id` > '0'";
419: }
420:
421: if (!empty($data['filter_order_id'])) {
422: $sql .= " AND `order_id` = '" . (int)$data['filter_order_id'] . "'";
423: }
424:
425: if (isset($data['filter_store_id']) && $data['filter_store_id'] !== '') {
426: $sql .= " AND `store_id` = '" . (int)$data['filter_store_id'] . "'";
427: }
428:
429: if (!empty($data['filter_customer_id'])) {
430: $sql .= " AND `customer_id` = '" . (int)$data['filter_customer_id'] . "'";
431: }
432:
433: if (!empty($data['filter_customer'])) {
434: $sql .= " AND LCASE(CONCAT(`firstname`, ' ', `lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_customer']) . '%') . "'";
435: }
436:
437: if (!empty($data['filter_email'])) {
438: $sql .= " AND LCASE(`email`) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_email']) . '%') . "'";
439: }
440:
441: if (!empty($data['filter_date_from'])) {
442: $sql .= " AND DATE(`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
443: }
444:
445: if (!empty($data['filter_date_to'])) {
446: $sql .= " AND DATE(`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
447: }
448:
449: if (!empty($data['filter_total'])) {
450: $sql .= " AND `total` = '" . (float)$data['filter_total'] . "'";
451: }
452:
453: $query = $this->db->query($sql);
454:
455: return (int)$query->row['total'];
456: }
457:
458: /**
459: * Get Total Orders By Store ID
460: *
461: * @param int $store_id
462: *
463: * @return int
464: */
465: public function getTotalOrdersByStoreId(int $store_id): int {
466: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `store_id` = '" . (int)$store_id . "'");
467:
468: return (int)$query->row['total'];
469: }
470:
471: /**
472: * Get Total Orders By Order Status ID
473: *
474: * @param int $order_status_id
475: *
476: * @return int
477: */
478: public function getTotalOrdersByOrderStatusId(int $order_status_id): int {
479: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `order_status_id` = '" . (int)$order_status_id . "' AND `order_status_id` > '0'");
480:
481: return (int)$query->row['total'];
482: }
483:
484: /**
485: * Get Total Orders By Processing Status
486: *
487: * @return int
488: */
489: public function getTotalOrdersByProcessingStatus(): int {
490: $implode = [];
491:
492: $order_statuses = $this->config->get('config_processing_status');
493:
494: foreach ($order_statuses as $order_status_id) {
495: $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
496: }
497:
498: if ($implode) {
499: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode));
500:
501: return (int)$query->row['total'];
502: } else {
503: return 0;
504: }
505: }
506:
507: /**
508: * Get Total Orders By Complete Status
509: *
510: * @return int
511: */
512: public function getTotalOrdersByCompleteStatus(): int {
513: $implode = [];
514:
515: $order_statuses = $this->config->get('config_complete_status');
516:
517: foreach ($order_statuses as $order_status_id) {
518: $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
519: }
520:
521: if ($implode) {
522: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode) . "");
523:
524: return (int)$query->row['total'];
525: } else {
526: return 0;
527: }
528: }
529:
530: /**
531: * Get Total Orders By Language ID
532: *
533: * @param int $language_id
534: *
535: * @return int
536: */
537: public function getTotalOrdersByLanguageId(int $language_id): int {
538: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `language_id` = '" . (int)$language_id . "' AND `order_status_id` > '0'");
539:
540: return (int)$query->row['total'];
541: }
542:
543: /**
544: * Get Total Orders By Currency ID
545: *
546: * @param int $currency_id
547: *
548: * @return int
549: */
550: public function getTotalOrdersByCurrencyId(int $currency_id): int {
551: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `currency_id` = '" . (int)$currency_id . "' AND `order_status_id` > '0'");
552:
553: return (int)$query->row['total'];
554: }
555:
556: /**
557: * Get Total Sales
558: *
559: * @param array<string, mixed> $data
560: *
561: * @return float
562: */
563: public function getTotalSales(array $data = []): float {
564: $sql = "SELECT SUM(`total`) AS `total` FROM `" . DB_PREFIX . "order`";
565:
566: if (!empty($data['filter_order_status'])) {
567: $implode = [];
568:
569: $order_statuses = explode(',', $data['filter_order_status']);
570: $order_statuses = array_filter($order_statuses);
571:
572: foreach ($order_statuses as $order_status_id) {
573: $implode[] = "`order_status_id` = '" . (int)$order_status_id . "'";
574: }
575:
576: if ($implode) {
577: $sql .= " WHERE (" . implode(" OR ", $implode) . ")";
578: }
579: } elseif (isset($data['filter_order_status_id']) && $data['filter_order_status_id'] !== '') {
580: $sql .= " WHERE `order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
581: } else {
582: $sql .= " WHERE `order_status_id` > '0'";
583: }
584:
585: if (!empty($data['filter_order_id'])) {
586: $sql .= " AND `order_id` = '" . (int)$data['filter_order_id'] . "'";
587: }
588:
589: if (isset($data['filter_store_id']) && $data['filter_store_id'] !== '') {
590: $sql .= " AND `store_id` = '" . (int)$data['filter_store_id'] . "'";
591: }
592:
593: if (!empty($data['filter_customer_id'])) {
594: $sql .= " AND `customer_id` = '" . (int)$data['filter_customer_id'] . "'";
595: }
596:
597: if (!empty($data['filter_customer'])) {
598: $sql .= " AND LCASE(CONCAT(`firstname`, ' ', `lastname`)) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_customer']) . '%') . "'";
599: }
600:
601: if (!empty($data['filter_email'])) {
602: $sql .= " AND LCASE(`email`) LIKE '" . $this->db->escape('%' . oc_strtolower($data['filter_email']) . '%') . "'";
603: }
604:
605: if (!empty($data['filter_date_added'])) {
606: $sql .= " AND DATE(`date_added`) = DATE('" . $this->db->escape((string)$data['filter_date_added']) . "')";
607: }
608:
609: if (!empty($data['filter_date_modified'])) {
610: $sql .= " AND DATE(`date_modified`) = DATE('" . $this->db->escape((string)$data['filter_date_modified']) . "')";
611: }
612:
613: if (!empty($data['filter_total'])) {
614: $sql .= " AND `total` = '" . (float)$data['filter_total'] . "'";
615: }
616:
617: $query = $this->db->query($sql);
618:
619: return (int)$query->row['total'];
620: }
621:
622: /**
623: * Create Invoice No
624: *
625: * @param int $order_id
626: *
627: * @return string
628: */
629: public function createInvoiceNo(int $order_id): string {
630: $order_info = $this->getOrder($order_id);
631:
632: if ($order_info && !$order_info['invoice_no']) {
633: $query = $this->db->query("SELECT MAX(`invoice_no`) AS `invoice_no` FROM `" . DB_PREFIX . "order` WHERE `invoice_prefix` = '" . $this->db->escape($order_info['invoice_prefix']) . "'");
634:
635: if ($query->row['invoice_no']) {
636: $invoice_no = $query->row['invoice_no'] + 1;
637: } else {
638: $invoice_no = 1;
639: }
640:
641: $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `invoice_no` = '" . (int)$invoice_no . "', `invoice_prefix` = '" . $this->db->escape($order_info['invoice_prefix']) . "' WHERE `order_id` = '" . (int)$order_id . "'");
642:
643: return $order_info['invoice_prefix'] . $invoice_no;
644: }
645:
646: return '';
647: }
648:
649: /**
650: * Get Reward Total
651: *
652: * @param int $order_id
653: *
654: * @return int
655: */
656: public function getRewardTotal(int $order_id): int {
657: $query = $this->db->query("SELECT SUM(reward) AS `total` FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "'");
658:
659: return (int)$query->row['total'];
660: }
661:
662: /**
663: * Get Histories
664: *
665: * @param int $order_id
666: * @param int $start
667: * @param int $limit
668: *
669: * @return array<int, array<string, mixed>>
670: */
671: public function getHistories(int $order_id, int $start = 0, int $limit = 10): array {
672: if ($start < 0) {
673: $start = 0;
674: }
675:
676: if ($limit < 1) {
677: $limit = 10;
678: }
679:
680: $query = $this->db->query("SELECT `oh`.`date_added`, `os`.`name` AS `status`, `oh`.`comment`, `oh`.`notify` FROM `" . DB_PREFIX . "order_history` `oh` LEFT JOIN `" . DB_PREFIX . "order_status` `os` ON `oh`.`order_status_id` = `os`.`order_status_id` WHERE `oh`.`order_id` = '" . (int)$order_id . "' AND `os`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `oh`.`date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
681:
682: return $query->rows;
683: }
684:
685: /**
686: * Get Total Histories
687: *
688: * @param int $order_id
689: *
690: * @return int
691: */
692: public function getTotalHistories(int $order_id): int {
693: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order_history` WHERE `order_id` = '" . (int)$order_id . "'");
694:
695: return (int)$query->row['total'];
696: }
697:
698: /**
699: * Get Total Histories By Order Status ID
700: *
701: * @param int $order_status_id
702: *
703: * @return int
704: */
705: public function getTotalHistoriesByOrderStatusId(int $order_status_id): int {
706: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order_history` WHERE `order_status_id` = '" . (int)$order_status_id . "'");
707:
708: return (int)$query->row['total'];
709: }
710:
711: /**
712: * Get Emails By Products Ordered
713: *
714: * @param array<int> $products
715: * @param int $start
716: * @param int $end
717: *
718: * @return array<int, array<string, mixed>>
719: */
720: public function getEmailsByProductsOrdered(array $products, int $start, int $end): array {
721: $implode = [];
722:
723: foreach ($products as $product_id) {
724: $implode[] = "`op`.`product_id` = '" . (int)$product_id . "'";
725: }
726:
727: $query = $this->db->query("SELECT DISTINCT `o`.`email` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "order_product` `op` ON (`o`.`order_id` = `op`.`order_id`) WHERE (" . implode(" OR ", $implode) . ") AND `o`.`order_status_id` <> '0' LIMIT " . (int)$start . "," . (int)$end);
728:
729: return $query->rows;
730: }
731:
732: /**
733: * Get Total Emails By Products Ordered
734: *
735: * @param array<int> $products
736: *
737: * @return int
738: */
739: public function getTotalEmailsByProductsOrdered(array $products): int {
740: $implode = [];
741:
742: foreach ($products as $product_id) {
743: $implode[] = "`op`.`product_id` = '" . (int)$product_id . "'";
744: }
745:
746: $query = $this->db->query("SELECT COUNT(DISTINCT `o`.`email`) AS `total` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "order_product` `op` ON (`o`.`order_id` = `op`.`order_id`) WHERE (" . implode(" OR ", $implode) . ") AND `o`.`order_status_id` <> '0'");
747:
748: return (int)$query->row['total'];
749: }
750: }
751: