1: | <?php
|
2: | namespace Opencart\Admin\Model\Sale;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Order extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: | |
12: | |
13: | |
14: | |
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: | |
161: | |
162: | |
163: | |
164: | |
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: | |
273: | |
274: | |
275: | |
276: | |
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: | |
286: | |
287: | |
288: | |
289: | |
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: | |
299: | |
300: | |
301: | |
302: | |
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: | |
312: | |
313: | |
314: | |
315: | |
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: | |
327: | |
328: | |
329: | |
330: | |
331: | |
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: | |
341: | |
342: | |
343: | |
344: | |
345: | |
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: | |
355: | |
356: | |
357: | |
358: | |
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: | |
368: | |
369: | |
370: | |
371: | |
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: | |
381: | |
382: | |
383: | |
384: | |
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: | |
394: | |
395: | |
396: | |
397: | |
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: | |
460: | |
461: | |
462: | |
463: | |
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: | |
473: | |
474: | |
475: | |
476: | |
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: | |
486: | |
487: | |
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: | |
509: | |
510: | |
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: | |
532: | |
533: | |
534: | |
535: | |
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: | |
545: | |
546: | |
547: | |
548: | |
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: | |
558: | |
559: | |
560: | |
561: | |
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: | |
624: | |
625: | |
626: | |
627: | |
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: | |
651: | |
652: | |
653: | |
654: | |
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: | |
664: | |
665: | |
666: | |
667: | |
668: | |
669: | |
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: | |
687: | |
688: | |
689: | |
690: | |
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: | |
700: | |
701: | |
702: | |
703: | |
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: | |
713: | |
714: | |
715: | |
716: | |
717: | |
718: | |
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: | |
734: | |
735: | |
736: | |
737: | |
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: | |