1: <?php
2: namespace Opencart\Catalog\Model\Checkout;
3: /**
4: * Class Order
5: *
6: * @package Opencart\Catalog\Model\Checkout
7: */
8: class Order extends \Opencart\System\Engine\Model {
9: /**
10: * Add Order
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return int
15: */
16: public function addOrder(array $data): int {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "order` SET `invoice_prefix` = '" . $this->db->escape($data['invoice_prefix']) . "', `store_id` = '" . (int)$data['store_id'] . "', `store_name` = '" . $this->db->escape($data['store_name']) . "', `store_url` = '" . $this->db->escape($data['store_url']) . "', `customer_id` = '" . (int)$data['customer_id'] . "', `customer_group_id` = '" . (int)$data['customer_group_id'] . "', `firstname` = '" . $this->db->escape($data['firstname']) . "', `lastname` = '" . $this->db->escape($data['lastname']) . "', `email` = '" . $this->db->escape($data['email']) . "', `telephone` = '" . $this->db->escape($data['telephone']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : '') . "', `payment_address_id` = '" . (int)$data['payment_address_id'] . "', `payment_firstname` = '" . $this->db->escape($data['payment_firstname']) . "', `payment_lastname` = '" . $this->db->escape($data['payment_lastname']) . "', `payment_company` = '" . $this->db->escape($data['payment_company']) . "', `payment_address_1` = '" . $this->db->escape($data['payment_address_1']) . "', `payment_address_2` = '" . $this->db->escape($data['payment_address_2']) . "', `payment_city` = '" . $this->db->escape($data['payment_city']) . "', `payment_postcode` = '" . $this->db->escape($data['payment_postcode']) . "', `payment_country` = '" . $this->db->escape($data['payment_country']) . "', `payment_country_id` = '" . (int)$data['payment_country_id'] . "', `payment_zone` = '" . $this->db->escape($data['payment_zone']) . "', `payment_zone_id` = '" . (int)$data['payment_zone_id'] . "', `payment_address_format` = '" . $this->db->escape($data['payment_address_format']) . "', `payment_custom_field` = '" . $this->db->escape(isset($data['payment_custom_field']) ? json_encode($data['payment_custom_field']) : '') . "', `payment_method` = '" . $this->db->escape($data['payment_method'] ? json_encode($data['payment_method']) : '') . "', `shipping_address_id` = '" . (int)$data['shipping_address_id'] . "', `shipping_firstname` = '" . $this->db->escape($data['shipping_firstname']) . "', `shipping_lastname` = '" . $this->db->escape($data['shipping_lastname']) . "', `shipping_company` = '" . $this->db->escape($data['shipping_company']) . "', `shipping_address_1` = '" . $this->db->escape($data['shipping_address_1']) . "', `shipping_address_2` = '" . $this->db->escape($data['shipping_address_2']) . "', `shipping_city` = '" . $this->db->escape($data['shipping_city']) . "', `shipping_postcode` = '" . $this->db->escape($data['shipping_postcode']) . "', `shipping_country` = '" . $this->db->escape($data['shipping_country']) . "', `shipping_country_id` = '" . (int)$data['shipping_country_id'] . "', `shipping_zone` = '" . $this->db->escape($data['shipping_zone']) . "', `shipping_zone_id` = '" . (int)$data['shipping_zone_id'] . "', `shipping_address_format` = '" . $this->db->escape($data['shipping_address_format']) . "', `shipping_custom_field` = '" . $this->db->escape(isset($data['shipping_custom_field']) ? json_encode($data['shipping_custom_field']) : '') . "', `shipping_method` = '" . $this->db->escape($data['shipping_method'] ? json_encode($data['shipping_method']) : '') . "', `comment` = '" . $this->db->escape($data['comment']) . "', `total` = '" . (float)$data['total'] . "', `affiliate_id` = '" . (int)$data['affiliate_id'] . "', `commission` = '" . (float)$data['commission'] . "', `marketing_id` = '" . (int)$data['marketing_id'] . "', `tracking` = '" . $this->db->escape($data['tracking']) . "', `language_id` = '" . (int)$data['language_id'] . "', `currency_id` = '" . (int)$data['currency_id'] . "', `currency_code` = '" . $this->db->escape($data['currency_code']) . "', `currency_value` = '" . (float)$data['currency_value'] . "', `ip` = '" . $this->db->escape((string)$data['ip']) . "', `forwarded_ip` = '" . $this->db->escape((string)$data['forwarded_ip']) . "', `user_agent` = '" . $this->db->escape((string)$data['user_agent']) . "', `accept_language` = '" . $this->db->escape((string)$data['accept_language']) . "', `date_added` = NOW(), `date_modified` = NOW()");
18:
19: $order_id = $this->db->getLastId();
20:
21: // Products
22: if (isset($data['products'])) {
23: foreach ($data['products'] as $product) {
24: $this->model_checkout_order->addProduct($order_id, $product);
25: }
26: }
27:
28: // Vouchers
29: if (isset($data['vouchers'])) {
30: foreach ($data['vouchers'] as $voucher) {
31: $this->model_checkout_order->addVoucher($order_id, $voucher);
32: }
33: }
34:
35: // Totals
36: if (isset($data['totals'])) {
37: foreach ($data['totals'] as $total) {
38: $this->model_checkout_order->addTotal($order_id, $total);
39: }
40: }
41:
42: return $order_id;
43: }
44:
45: /**
46: * Edit Order
47: *
48: * @param int $order_id
49: * @param array<string, mixed> $data
50: *
51: * @return void
52: */
53: public function editOrder(int $order_id, array $data): void {
54: // 1. Void the order first
55: $this->addHistory($order_id, 0);
56:
57: $order_info = $this->getOrder($order_id);
58:
59: if ($order_info) {
60: // 2. Merge the old order data with the new data
61: foreach ($order_info as $key => $value) {
62: if (!isset($data[$key])) {
63: $data[$key] = $value;
64: }
65: }
66:
67: $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `invoice_prefix` = '" . $this->db->escape((string)$data['invoice_prefix']) . "', `store_id` = '" . (int)$data['store_id'] . "', `store_name` = '" . $this->db->escape((string)$data['store_name']) . "', `store_url` = '" . $this->db->escape((string)$data['store_url']) . "', `customer_id` = '" . (int)$data['customer_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(json_encode($data['custom_field'])) . "', `payment_address_id` = '" . (int)$data['payment_address_id'] . "', `payment_firstname` = '" . $this->db->escape((string)$data['payment_firstname']) . "', `payment_lastname` = '" . $this->db->escape((string)$data['payment_lastname']) . "', `payment_company` = '" . $this->db->escape((string)$data['payment_company']) . "', `payment_address_1` = '" . $this->db->escape((string)$data['payment_address_1']) . "', `payment_address_2` = '" . $this->db->escape((string)$data['payment_address_2']) . "', `payment_city` = '" . $this->db->escape((string)$data['payment_city']) . "', `payment_postcode` = '" . $this->db->escape((string)$data['payment_postcode']) . "', `payment_country` = '" . $this->db->escape((string)$data['payment_country']) . "', `payment_country_id` = '" . (int)$data['payment_country_id'] . "', `payment_zone` = '" . $this->db->escape((string)$data['payment_zone']) . "', `payment_zone_id` = '" . (int)$data['payment_zone_id'] . "', `payment_address_format` = '" . $this->db->escape((string)$data['payment_address_format']) . "', `payment_custom_field` = '" . $this->db->escape(isset($data['payment_custom_field']) ? json_encode($data['payment_custom_field']) : '') . "', `payment_method` = '" . $this->db->escape($data['payment_method'] ? json_encode($data['payment_method']) : '') . "', `shipping_address_id` = '" . (int)$data['shipping_address_id'] . "', `shipping_firstname` = '" . $this->db->escape((string)$data['shipping_firstname']) . "', `shipping_lastname` = '" . $this->db->escape((string)$data['shipping_lastname']) . "', `shipping_company` = '" . $this->db->escape((string)$data['shipping_company']) . "', `shipping_address_1` = '" . $this->db->escape((string)$data['shipping_address_1']) . "', `shipping_address_2` = '" . $this->db->escape((string)$data['shipping_address_2']) . "', `shipping_city` = '" . $this->db->escape((string)$data['shipping_city']) . "', `shipping_postcode` = '" . $this->db->escape((string)$data['shipping_postcode']) . "', `shipping_country` = '" . $this->db->escape((string)$data['shipping_country']) . "', `shipping_country_id` = '" . (int)$data['shipping_country_id'] . "', `shipping_zone` = '" . $this->db->escape((string)$data['shipping_zone']) . "', `shipping_zone_id` = '" . (int)$data['shipping_zone_id'] . "', `shipping_address_format` = '" . $this->db->escape((string)$data['shipping_address_format']) . "', `shipping_custom_field` = '" . $this->db->escape(isset($data['shipping_custom_field']) ? json_encode($data['shipping_custom_field']) : '') . "', `shipping_method` = '" . $this->db->escape($data['shipping_method'] ? json_encode($data['shipping_method']) : '') . "', `comment` = '" . $this->db->escape((string)$data['comment']) . "', `total` = '" . (float)$data['total'] . "', `affiliate_id` = '" . (int)$data['affiliate_id'] . "', `commission` = '" . (float)$data['commission'] . "', `date_modified` = NOW() WHERE `order_id` = '" . (int)$order_id . "'");
68:
69: // Products
70: $this->model_checkout_order->deleteProducts($order_id);
71:
72: if (isset($data['products'])) {
73: foreach ($data['products'] as $product) {
74: $this->model_checkout_order->addProduct($order_id, $product);
75: }
76: }
77:
78: // Vouchers
79: $this->model_checkout_order->deleteVouchers($order_id);
80:
81: if (isset($data['vouchers'])) {
82: foreach ($data['vouchers'] as $voucher) {
83: $this->model_checkout_order->addVoucher($order_id, $voucher);
84: }
85: }
86:
87: // Totals
88: $this->model_checkout_order->deleteTotals($order_id);
89:
90: if (isset($data['totals'])) {
91: foreach ($data['totals'] as $total) {
92: $this->model_checkout_order->addTotal($order_id, $total);
93: }
94: }
95: }
96: }
97:
98: /**
99: * Edit Transaction ID
100: *
101: * @param int $order_id
102: * @param string $transaction_id
103: *
104: * @return void
105: */
106: public function editTransactionId(int $order_id, string $transaction_id): void {
107: $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `transaction_id` = '" . $this->db->escape($transaction_id) . "' WHERE `order_id` = '" . (int)$order_id . "'");
108: }
109:
110: /**
111: * Edit Order Status ID
112: *
113: * @param int $order_id
114: * @param int $order_status_id
115: *
116: * @return void
117: */
118: public function editOrderStatusId(int $order_id, int $order_status_id): void {
119: $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `order_status_id` = '" . (int)$order_status_id . "' WHERE `order_id` = '" . (int)$order_id . "'");
120: }
121:
122: /**
123: * Edit Comment
124: *
125: * @param int $order_id
126: * @param string $comment
127: *
128: * @return void
129: */
130: public function editComment(int $order_id, string $comment): void {
131: $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `comment` = '" . $this->db->escape($comment) . "' WHERE `order_id` = '" . (int)$order_id . "'");
132: }
133:
134: /**
135: * Delete Order
136: *
137: * @param int $order_id
138: *
139: * @return void
140: */
141: public function deleteOrder(int $order_id): void {
142: // Void the order first so it restocks products
143: $this->model_checkout_order->addHistory($order_id, 0);
144:
145: $this->db->query("DELETE FROM `" . DB_PREFIX . "order` WHERE `order_id` = '" . (int)$order_id . "'");
146:
147: $this->model_checkout_order->deleteProducts($order_id);
148: $this->model_checkout_order->deleteVouchers($order_id);
149: $this->model_checkout_order->deleteTotals($order_id);
150: $this->model_checkout_order->deleteHistories($order_id);
151:
152: $this->load->model('account/transaction');
153:
154: $this->model_account_transaction->deleteTransactionsByOrderId($order_id);
155:
156: $this->load->model('account/reward');
157:
158: $this->model_account_reward->deleteRewardsByOrderId($order_id);
159:
160: // Gift Voucher
161: $this->load->model('checkout/voucher');
162:
163: $this->model_checkout_voucher->deleteVouchersByOrderId($order_id);
164: }
165:
166: /**
167: * Get Order
168: *
169: * @param int $order_id
170: *
171: * @return array<string, mixed>
172: */
173: public function getOrder(int $order_id): array {
174: $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` = `o`.`language_id`) AS order_status FROM `" . DB_PREFIX . "order` `o` WHERE `o`.`order_id` = '" . (int)$order_id . "'");
175:
176: if ($order_query->num_rows) {
177: $order_data = $order_query->row;
178:
179: $this->load->model('localisation/country');
180: $this->load->model('localisation/zone');
181:
182: $order_data['custom_field'] = json_decode($order_query->row['custom_field'], true);
183:
184: foreach (['payment', 'shipping'] as $column) {
185: $country_info = $this->model_localisation_country->getCountry($order_query->row[$column . '_country_id']);
186:
187: if ($country_info) {
188: $order_data[$column . '_iso_code_2'] = $country_info['iso_code_2'];
189: $order_data[$column . '_iso_code_3'] = $country_info['iso_code_3'];
190: } else {
191: $order_data[$column . '_iso_code_2'] = '';
192: $order_data[$column . '_iso_code_3'] = '';
193: }
194:
195: $zone_info = $this->model_localisation_zone->getZone($order_query->row[$column . '_zone_id']);
196:
197: if ($zone_info) {
198: $order_data[$column . '_zone_code'] = $zone_info['code'];
199: } else {
200: $order_data[$column . '_zone_code'] = '';
201: }
202:
203: $order_data[$column . '_custom_field'] = json_decode($order_query->row[$column . '_custom_field'], true);
204:
205: $order_data[$column . '_custom_field'] = json_decode($order_query->row[$column . '_custom_field'], true);
206:
207: // Payment and shipping method details
208: $order_data[$column . '_method'] = json_decode($order_query->row[$column . '_method'], true);
209: }
210:
211: return $order_data;
212: }
213:
214: return [];
215: }
216:
217: /**
218: * Add Product
219: *
220: * @param int $order_id
221: * @param array<string, mixed> $data
222: *
223: * @return int
224: */
225: public function addProduct(int $order_id, array $data): int {
226: $this->db->query("INSERT INTO `" . DB_PREFIX . "order_product` SET `order_id` = '" . (int)$order_id . "', `product_id` = '" . (int)$data['product_id'] . "', `master_id` = '" . (int)$data['master_id'] . "', `name` = '" . $this->db->escape($data['name']) . "', `model` = '" . $this->db->escape($data['model']) . "', `quantity` = '" . (int)$data['quantity'] . "', `price` = '" . (float)$data['price'] . "', `total` = '" . (float)$data['total'] . "', `tax` = '" . (float)$data['tax'] . "', `reward` = '" . (int)$data['reward'] . "'");
227:
228: $order_product_id = $this->db->getLastId();
229:
230: foreach ($data['option'] as $option) {
231: $this->model_checkout_order->addOption($order_id, $order_product_id, $option);
232: }
233:
234: // If subscription add details
235: if ($data['subscription']) {
236: $this->model_checkout_order->addSubscription($order_id, $order_product_id, $data['subscription']);
237: }
238:
239: return $this->db->getLastId();
240: }
241:
242: /**
243: * Delete Products
244: *
245: * @param int $order_id
246: * @param int $order_product_id
247: *
248: * @return void
249: */
250: public function deleteProducts(int $order_id, int $order_product_id = 0): void {
251: $sql = "DELETE FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "'";
252:
253: if ($order_product_id) {
254: $sql .= " AND `order_product_id` = '" . (int)$order_product_id . "'";
255: }
256:
257: $this->db->query($sql);
258:
259: $this->deleteOptions($order_id, $order_product_id);
260: $this->deleteSubscription($order_id, $order_product_id);
261: }
262:
263: /**
264: * Get Product
265: *
266: * @param int $order_id
267: * @param int $order_product_id
268: *
269: * @return array<int, array<string, mixed>>
270: */
271: public function getProduct(int $order_id, int $order_product_id): array {
272: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
273:
274: return $query->rows;
275: }
276:
277: /**
278: * Get Products
279: *
280: * @param int $order_id
281: *
282: * @return array<int, array<string, mixed>>
283: */
284: public function getProducts(int $order_id): array {
285: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` WHERE `order_id` = '" . (int)$order_id . "'");
286:
287: return $query->rows;
288: }
289:
290: /**
291: * Add Option
292: *
293: * @param int $order_id
294: * @param int $order_product_id
295: * @param array<string, mixed> $data
296: *
297: * @return void
298: */
299: public function addOption(int $order_id, int $order_product_id, array $data): void {
300: $this->db->query("INSERT INTO `" . DB_PREFIX . "order_option` SET `order_id` = '" . (int)$order_id . "', `order_product_id` = '" . (int)$order_product_id . "', `product_option_id` = '" . (int)$data['product_option_id'] . "', `product_option_value_id` = '" . (int)$data['product_option_value_id'] . "', `name` = '" . $this->db->escape($data['name']) . "', `value` = '" . $this->db->escape($data['value']) . "', `type` = '" . $this->db->escape($data['type']) . "'");
301: }
302:
303: /**
304: * Delete Options
305: *
306: * @param int $order_id
307: * @param int $order_product_id
308: *
309: * @return void
310: */
311: public function deleteOptions(int $order_id, int $order_product_id = 0): void {
312: $sql = "DELETE FROM `" . DB_PREFIX . "order_option` WHERE `order_id` = '" . (int)$order_id . "'";
313:
314: if ($order_product_id) {
315: $sql .= " AND `order_product_id` = '" . (int)$order_product_id . "'";
316: }
317:
318: $this->db->query($sql);
319: }
320:
321: /**
322: * Get Options
323: *
324: * @param int $order_id
325: * @param int $order_product_id
326: *
327: * @return array<int, array<string, mixed>>
328: */
329: public function getOptions(int $order_id, int $order_product_id): array {
330: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_option` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
331:
332: return $query->rows;
333: }
334:
335: /**
336: * Add Subscription
337: *
338: * @param int $order_id
339: * @param int $order_product_id
340: * @param array<string, mixed> $data
341: *
342: * @return void
343: */
344: public function addSubscription(int $order_id, int $order_product_id, array $data): void {
345: $this->db->query("INSERT INTO `" . DB_PREFIX . "order_subscription` SET `order_id` = '" . (int)$order_id . "', `order_product_id` = '" . (int)$order_product_id . "', `subscription_plan_id` = '" . (int)$data['subscription_plan_id'] . "', `trial_price` = '" . (float)$data['trial_price'] . "', `trial_tax` = '" . (float)$data['trial_tax'] . "', `trial_frequency` = '" . $this->db->escape($data['trial_frequency']) . "', `trial_cycle` = '" . (int)$data['trial_cycle'] . "', `trial_duration` = '" . (int)$data['trial_duration'] . "', `trial_remaining` = '" . (int)$data['trial_remaining'] . "', `trial_status` = '" . (int)$data['trial_status'] . "', `price` = '" . (float)$data['price'] . "', `tax` = '" . (float)$data['tax'] . "', `frequency` = '" . $this->db->escape($data['frequency']) . "', `cycle` = '" . (int)$data['cycle'] . "', `duration` = '" . (int)$data['duration'] . "'");
346: }
347:
348: /**
349: * Delete Subscription
350: *
351: * @param int $order_id
352: * @param int $order_product_id
353: *
354: * @return void
355: */
356: public function deleteSubscription(int $order_id, int $order_product_id = 0): void {
357: $sql = "DELETE FROM `" . DB_PREFIX . "order_subscription` WHERE `order_id` = '" . (int)$order_id . "'";
358:
359: if ($order_product_id) {
360: $sql .= " AND `order_product_id` = '" . (int)$order_product_id . "'";
361: }
362:
363: $this->db->query($sql);
364: }
365:
366: /**
367: * Get Subscription
368: *
369: * @param int $order_id
370: * @param int $order_product_id
371: *
372: * @return array<string, mixed>
373: */
374: public function getSubscription(int $order_id, int $order_product_id): array {
375: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_subscription` WHERE `order_id` = '" . (int)$order_id . "' AND `order_product_id` = '" . (int)$order_product_id . "'");
376:
377: return $query->row;
378: }
379:
380: /**
381: * Get Subscriptions
382: *
383: * @param array<string, mixed> $data
384: *
385: * @return array<int, array<string, mixed>>
386: */
387: public function getSubscriptions(array $data): array {
388: $sql = "SELECT * FROM `" . DB_PREFIX . "subscription`";
389:
390: $implode = [];
391:
392: if (!empty($data['filter_date_next'])) {
393: $implode[] = "DATE(`date_next`) <= DATE('" . $this->db->escape($data['filter_date_next']) . "')";
394: }
395:
396: if (!empty($data['filter_subscription_status_id'])) {
397: $implode[] = "`subscription_status_id` = '" . (int)$data['filter_subscription_status_id'] . "'";
398: }
399:
400: if ($implode) {
401: $sql .= " WHERE " . implode(" AND ", $implode);
402: }
403:
404: $sort_data = [
405: 'pd.name',
406: 'p.model',
407: 'p.price',
408: 'p.quantity',
409: 'p.status',
410: 'p.sort_order'
411: ];
412:
413: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
414: $sql .= " ORDER BY " . $data['sort'];
415: } else {
416: $sql .= " ORDER BY `o`.`order_id`";
417: }
418:
419: if (isset($data['order']) && ($data['order'] == 'DESC')) {
420: $sql .= " DESC";
421: } else {
422: $sql .= " ASC";
423: }
424:
425: if (isset($data['start']) || isset($data['limit'])) {
426: if ($data['start'] < 0) {
427: $data['start'] = 0;
428: }
429:
430: if ($data['limit'] < 1) {
431: $data['limit'] = 20;
432: }
433:
434: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
435: }
436:
437: $query = $this->db->query($sql);
438:
439: return $query->rows;
440: }
441:
442: /**
443: * Get Total Orders By Subscription ID
444: *
445: * @param int $subscription_id
446: *
447: * @return int
448: */
449: public function getTotalOrdersBySubscriptionId(int $subscription_id): int {
450: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "order` WHERE `subscription_id` = '" . (int)$subscription_id . "' AND `customer_id` = '" . (int)$this->customer->getId() . "'");
451:
452: return (int)$query->row['total'];
453: }
454:
455: /**
456: * Add Voucher
457: *
458: * @param int $order_id
459: * @param array<string, mixed> $data
460: *
461: * @return int
462: */
463: public function addVoucher(int $order_id, array $data): int {
464: $this->db->query("INSERT INTO `" . DB_PREFIX . "order_voucher` SET `order_id` = '" . (int)$order_id . "', `description` = '" . $this->db->escape($data['description']) . "', `code` = '" . $this->db->escape($data['code']) . "', `from_name` = '" . $this->db->escape($data['from_name']) . "', `from_email` = '" . $this->db->escape($data['from_email']) . "', `to_name` = '" . $this->db->escape($data['to_name']) . "', `to_email` = '" . $this->db->escape($data['to_email']) . "', `voucher_theme_id` = '" . (int)$data['voucher_theme_id'] . "', `message` = '" . $this->db->escape($data['message']) . "', `amount` = '" . (float)$data['amount'] . "'");
465:
466: $order_voucher_id = $this->db->getLastId();
467:
468: $this->load->model('checkout/voucher');
469:
470: $voucher_id = $this->model_checkout_voucher->addVoucher($order_id, $data);
471:
472: $this->db->query("UPDATE `" . DB_PREFIX . "order_voucher` SET `voucher_id` = '" . (int)$voucher_id . "' WHERE `order_voucher_id` = '" . (int)$order_voucher_id . "'");
473:
474: return $order_voucher_id;
475: }
476:
477: /**
478: * Delete Vouchers
479: *
480: * @param int $order_id
481: * @param int $order_voucher_id
482: *
483: * @return void
484: */
485: public function deleteVouchers(int $order_id, int $order_voucher_id = 0): void {
486: $sql = "DELETE FROM `" . DB_PREFIX . "order_voucher` WHERE `order_id` = '" . (int)$order_id . "'";
487:
488: if ($order_voucher_id) {
489: $sql .= " AND `order_voucher_id` = '" . (int)$order_voucher_id . "'";
490: }
491:
492: $this->db->query($sql);
493: }
494:
495: /**
496: * Get Voucher By Voucher ID
497: *
498: * @param int $order_id
499: * @param int $voucher_id
500: *
501: * @return array<int, array<string, mixed>>
502: */
503: public function getVoucherByVoucherId(int $order_id, int $voucher_id): array {
504: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE `order_id` = '" . (int)$order_id . "' AND `voucher_id` = '" . (int)$voucher_id . "'");
505:
506: return $query->rows;
507: }
508:
509: /**
510: * Get Vouchers
511: *
512: * @param int $order_id
513: *
514: * @return array<int, array<string, mixed>>
515: */
516: public function getVouchers(int $order_id): array {
517: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE `order_id` = '" . (int)$order_id . "'");
518:
519: return $query->rows;
520: }
521:
522: /**
523: * Add Total
524: *
525: * @param int $order_id
526: * @param array<string, mixed> $data
527: *
528: * @return void
529: */
530: public function addTotal(int $order_id, array $data): void {
531: $this->db->query("INSERT INTO `" . DB_PREFIX . "order_total` SET `order_id` = '" . (int)$order_id . "', `extension` = '" . $this->db->escape($data['extension']) . "', `code` = '" . $this->db->escape($data['code']) . "', `title` = '" . $this->db->escape($data['title']) . "', `value` = '" . (float)$data['value'] . "', `sort_order` = '" . (int)$data['sort_order'] . "'");
532: }
533:
534: /**
535: * Delete Totals
536: *
537: * @param int $order_id
538: */
539: public function deleteTotals(int $order_id): void {
540: $this->db->query("DELETE FROM `" . DB_PREFIX . "order_total` WHERE `order_id` = '" . (int)$order_id . "'");
541: }
542:
543: /**
544: * Get Totals
545: *
546: * @param int $order_id
547: *
548: * @return array<int, array<string, mixed>>
549: */
550: public function getTotals(int $order_id): array {
551: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_total` WHERE `order_id` = '" . (int)$order_id . "' ORDER BY `sort_order` ASC");
552:
553: return $query->rows;
554: }
555:
556: /**
557: * Add History
558: *
559: * @param int $order_id
560: * @param int $order_status_id
561: * @param string $comment
562: * @param bool $notify
563: * @param bool $override
564: *
565: * @return void
566: */
567: public function addHistory(int $order_id, int $order_status_id, string $comment = '', bool $notify = false, bool $override = false): void {
568: $order_info = $this->model_checkout_order->getOrder($order_id);
569:
570: if ($order_info) {
571: // Load subscription model
572: $this->load->model('account/customer');
573:
574: $customer_info = $this->model_account_customer->getCustomer($order_info['customer_id']);
575:
576: // Fraud Detection Enable / Disable
577: if ($customer_info && $customer_info['safe']) {
578: $safe = true;
579: } else {
580: $safe = false;
581: }
582:
583: // Only do the fraud check if the customer is not on the safe list and the order status is changing into the complete or process order status
584: if (!$safe && !$override && in_array($order_status_id, (array)$this->config->get('config_processing_status') + (array)$this->config->get('config_complete_status'))) {
585: // Anti-Fraud
586: $this->load->model('setting/extension');
587:
588: $extensions = $this->model_setting_extension->getExtensionsByType('fraud');
589:
590: foreach ($extensions as $extension) {
591: if ($this->config->get('fraud_' . $extension['code'] . '_status')) {
592: $this->load->model('extension/' . $extension['extension'] . '/fraud/' . $extension['code']);
593:
594: $model_extension_fraud = ($this->{'model_extension_' . $extension['extension'] . '_fraud_' . $extension['code']}) ?? null;
595:
596: if ($model_extension_fraud && isset($model_extension_fraud->check)) {
597: $fraud_status_id = $model_extension_fraud->check($order_info);
598:
599: if ($fraud_status_id) {
600: $order_status_id = $fraud_status_id;
601: }
602: }
603: }
604: }
605: }
606:
607: // Products
608: $order_products = $this->model_checkout_order->getProducts($order_id);
609:
610: // Totals
611: $order_totals = $this->model_checkout_order->getTotals($order_id);
612:
613: // If current order status is not processing or complete but new status is processing or complete then commence completing the order
614: if (!in_array($order_info['order_status_id'], (array)$this->config->get('config_processing_status') + (array)$this->config->get('config_complete_status')) && in_array($order_status_id, (array)$this->config->get('config_processing_status') + (array)$this->config->get('config_complete_status'))) {
615: // Redeem coupon, vouchers and reward points
616: foreach ($order_totals as $order_total) {
617: $this->load->model('extension/' . $order_total['extension'] . '/total/' . $order_total['code']);
618:
619: $model_extension_total = $this->{'model_extension_' . $order_total['extension'] . '_total_' . $order_total['code']} ?? null;
620:
621: if ($model_extension_total && isset($model_extension_total->confirm)) {
622: // Confirm coupon, vouchers and reward points
623: $fraud_status_id = $model_extension_total->confirm($order_info, $order_total);
624:
625: // If the balance on the coupon, vouchers and reward points is not enough to cover the transaction or has already been used then the fraud order status is returned.
626: if ($fraud_status_id) {
627: $order_status_id = $fraud_status_id;
628: }
629: }
630: }
631:
632: foreach ($order_products as $order_product) {
633: // Stock subtraction
634: $this->db->query("UPDATE `" . DB_PREFIX . "product` SET `quantity` = (`quantity` - " . (int)$order_product['quantity'] . ") WHERE `product_id` = '" . (int)$order_product['product_id'] . "' AND `subtract` = '1'");
635:
636: // Stock subtraction from master product
637: if ($order_product['master_id']) {
638: $this->db->query("UPDATE `" . DB_PREFIX . "product` SET `quantity` = (`quantity` - " . (int)$order_product['quantity'] . ") WHERE `product_id` = '" . (int)$order_product['master_id'] . "' AND `subtract` = '1'");
639: }
640:
641: $order_options = $this->getOptions($order_id, $order_product['order_product_id']);
642:
643: foreach ($order_options as $order_option) {
644: $this->db->query("UPDATE `" . DB_PREFIX . "product_option_value` SET `quantity` = (`quantity` - " . (int)$order_product['quantity'] . ") WHERE `product_option_value_id` = '" . (int)$order_option['product_option_value_id'] . "' AND `subtract` = '1'");
645: }
646: }
647: }
648:
649: // If order status becomes complete status
650: if (!in_array($order_info['order_status_id'], (array)$this->config->get('config_complete_status')) && in_array($order_status_id, (array)$this->config->get('config_complete_status'))) {
651: // Affiliate add commission if complete status
652: if ($order_info['affiliate_id'] && $this->config->get('config_affiliate_auto')) {
653: // Add commission if sale is linked to affiliate referral.
654: $this->load->model('account/customer');
655:
656: if (!$this->model_account_customer->getTotalTransactionsByOrderId($order_id)) {
657: $this->model_account_customer->addTransaction($order_info['affiliate_id'], $this->language->get('text_order_id') . ' #' . $order_id, $order_info['commission'], $order_id);
658: }
659: }
660:
661: // Add subscription
662: $this->load->model('checkout/subscription');
663:
664: foreach ($order_products as $order_product) {
665: // Subscription
666: $order_subscription_info = $this->model_checkout_order->getSubscription($order_id, $order_product['order_product_id']);
667:
668: if ($order_subscription_info) {
669: // Add options for subscription
670: $option_data = [];
671:
672: $options = $this->getOptions($order_id, $order_product['order_product_id']);
673:
674: foreach ($options as $option) {
675: if ($option['type'] == 'text' || $option['type'] == 'textarea' || $option['type'] == 'file' || $option['type'] == 'date' || $option['type'] == 'datetime' || $option['type'] == 'time') {
676: $option_data[$option['product_option_id']] = $option['value'];
677: } elseif ($option['type'] == 'select' || $option['type'] == 'radio') {
678: $option_data[$option['product_option_id']] = $option['product_option_value_id'];
679: } elseif ($option['type'] == 'checkbox') {
680: $option_data[$option['product_option_id']][] = $option['product_option_value_id'];
681: }
682: }
683:
684: // Add subscription if one is not setup
685: $subscription_info = $this->model_checkout_subscription->getSubscriptionByOrderProductId($order_id, $order_product['order_product_id']);
686:
687: if ($subscription_info) {
688: $subscription_id = $subscription_info['subscription_id'];
689: } else {
690: $subscription_id = $this->model_checkout_subscription->addSubscription($order_subscription_info + $order_product + $order_info + ['option' => $option_data]);
691: }
692:
693: // Add history and set active subscription
694: $this->model_checkout_subscription->addHistory($subscription_id, (int)$this->config->get('config_subscription_active_id'));
695: }
696: }
697: }
698:
699: // If old order status is the processing or complete status but new status is not then commence restock, and remove coupon, voucher and reward history
700: if (in_array($order_info['order_status_id'], (array)$this->config->get('config_processing_status') + (array)$this->config->get('config_complete_status')) && !in_array($order_status_id, (array)$this->config->get('config_processing_status') + (array)$this->config->get('config_complete_status'))) {
701: // Restock
702: foreach ($order_products as $order_product) {
703: $this->db->query("UPDATE `" . DB_PREFIX . "product` SET `quantity` = (`quantity` + " . (int)$order_product['quantity'] . ") WHERE `product_id` = '" . (int)$order_product['product_id'] . "' AND `subtract` = '1'");
704:
705: // Restock the master product stock level if product is a variant
706: if ($order_product['master_id']) {
707: $this->db->query("UPDATE `" . DB_PREFIX . "product` SET `quantity` = (`quantity` + " . (int)$order_product['quantity'] . ") WHERE `product_id` = '" . (int)$order_product['master_id'] . "' AND `subtract` = '1'");
708: }
709:
710: $order_options = $this->model_checkout_order->getOptions($order_id, $order_product['order_product_id']);
711:
712: foreach ($order_options as $order_option) {
713: $this->db->query("UPDATE `" . DB_PREFIX . "product_option_value` SET `quantity` = (`quantity` + " . (int)$order_product['quantity'] . ") WHERE `product_option_value_id` = '" . (int)$order_option['product_option_value_id'] . "' AND `subtract` = '1'");
714: }
715: }
716:
717: // Remove coupon, vouchers and reward points history
718: foreach ($order_totals as $order_total) {
719: $this->load->model('extension/' . $order_total['extension'] . '/total/' . $order_total['code']);
720:
721: $model_extension_total = $this->{'model_extension_' . $order_total['extension'] . '_total_' . $order_total['code']} ?? null;
722:
723: if ($model_extension_total && isset($model_extension_total->unconfirm)) {
724: $model_extension_total->unconfirm($order_info);
725: }
726: }
727: }
728:
729: // If order status is no longer complete status
730: if (in_array($order_info['order_status_id'], (array)$this->config->get('config_complete_status')) && !in_array($order_status_id, (array)$this->config->get('config_complete_status'))) {
731: // Suspend subscription
732: $this->load->model('checkout/subscription');
733:
734: foreach ($order_products as $order_product) {
735: // Subscription status set to suspend
736: $subscription_info = $this->model_checkout_subscription->getSubscriptionByOrderProductId($order_id, $order_product['order_product_id']);
737:
738: if ($subscription_info) {
739: // Add history and set suspended subscription
740: $this->model_checkout_subscription->addHistory($subscription_info['subscription_id'], (int)$this->config->get('config_subscription_suspended_status_id'));
741: }
742: }
743:
744: // Affiliate remove commission.
745: if ($order_info['affiliate_id']) {
746: $this->load->model('account/transaction');
747:
748: $this->model_account_transaction->deleteTransaction($order_info['customer_id'], $order_id);
749: }
750: }
751:
752: // Update the DB with the new statuses
753: $this->model_checkout_order->editOrderStatusId($order_id, $order_status_id);
754:
755: $this->db->query("INSERT INTO `" . DB_PREFIX . "order_history` SET `order_id` = '" . (int)$order_id . "', `order_status_id` = '" . (int)$order_status_id . "', `notify` = '" . (int)$notify . "', `comment` = '" . $this->db->escape($comment) . "', `date_added` = NOW()");
756:
757: $this->cache->delete('product');
758: }
759: }
760:
761: /**
762: * Delete Order History
763: *
764: * @param int $order_id
765: *
766: * @return void
767: */
768: public function deleteHistory(int $order_id): void {
769: $this->db->query("DELETE FROM `" . DB_PREFIX . "order_history` WHERE `order_id` = '" . (int)$order_id . "'");
770: }
771: }
772: