1: <?php
2: namespace Opencart\Admin\Model\Sale;
3: /**
4: * Class Returns
5: *
6: * @package Opencart\Admin\Model\Sale
7: */
8: class Returns extends \Opencart\System\Engine\Model {
9: /**
10: * Add Return
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return int
15: */
16: public function addReturn(array $data): int {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "return` SET `order_id` = '" . (int)$data['order_id'] . "', `product_id` = '" . (int)$data['product_id'] . "', `customer_id` = '" . (int)$data['customer_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']) . "', `product` = '" . $this->db->escape((string)$data['product']) . "', `model` = '" . $this->db->escape((string)$data['model']) . "', `quantity` = '" . (int)$data['quantity'] . "', `opened` = '" . (int)$data['opened'] . "', `return_reason_id` = '" . (int)$data['return_reason_id'] . "', `return_action_id` = '" . (int)$data['return_action_id'] . "', `return_status_id` = '" . (int)$data['return_status_id'] . "', `comment` = '" . $this->db->escape((string)$data['comment']) . "', `date_ordered` = '" . $this->db->escape((string)$data['date_ordered']) . "', `date_added` = NOW(), `date_modified` = NOW()");
18:
19: return $this->db->getLastId();
20: }
21:
22: /**
23: * Edit Return
24: *
25: * @param int $return_id
26: * @param array<string, mixed> $data
27: *
28: * @return void
29: */
30: public function editReturn(int $return_id, array $data): void {
31: $this->db->query("UPDATE `" . DB_PREFIX . "return` SET `order_id` = '" . (int)$data['order_id'] . "', `product_id` = '" . (int)$data['product_id'] . "', `customer_id` = '" . (int)$data['customer_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']) . "', `product` = '" . $this->db->escape((string)$data['product']) . "', `model` = '" . $this->db->escape((string)$data['model']) . "', `quantity` = '" . (int)$data['quantity'] . "', `opened` = '" . (int)$data['opened'] . "', `return_reason_id` = '" . (int)$data['return_reason_id'] . "', `return_action_id` = '" . (int)$data['return_action_id'] . "', `comment` = '" . $this->db->escape((string)$data['comment']) . "', `date_ordered` = '" . $this->db->escape((string)$data['date_ordered']) . "', `date_modified` = NOW() WHERE `return_id` = '" . (int)$return_id . "'");
32: }
33:
34: /**
35: * Edit Return Status ID
36: *
37: * @param int $return_id
38: * @param int $return_status_id
39: *
40: * @return void
41: */
42: public function editReturnStatusId(int $return_id, int $return_status_id): void {
43: $this->db->query("UPDATE `" . DB_PREFIX . "return` SET `return_status_id` = '" . (int)$return_status_id . "', `date_modified` = NOW() WHERE `return_id` = '" . (int)$return_id . "'");
44: }
45:
46: /**
47: * Delete Return
48: *
49: * @param int $return_id
50: *
51: * @return void
52: */
53: public function deleteReturn(int $return_id): void {
54: $this->db->query("DELETE FROM `" . DB_PREFIX . "return` WHERE `return_id` = '" . (int)$return_id . "'");
55:
56: $this->deleteHistories($return_id);
57: }
58:
59: /**
60: * Get Return
61: *
62: * @param int $return_id
63: *
64: * @return array<string, mixed>
65: */
66: public function getReturn(int $return_id): array {
67: $query = $this->db->query("SELECT DISTINCT *, (SELECT CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) FROM `" . DB_PREFIX . "customer` `c` WHERE `c`.`customer_id` = `r`.`customer_id`) AS `customer`, (SELECT `c`.`language_id` FROM `" . DB_PREFIX . "customer` `c` WHERE `c`.`customer_id` = `r`.`customer_id`) AS `language_id`, (SELECT `rs`.`name` FROM `" . DB_PREFIX . "return_status` `rs` WHERE `rs`.`return_status_id` = `r`.`return_status_id` AND `rs`.`language_id` = '" . (int)$this->config->get('config_language_id') . "') AS `return_status` FROM `" . DB_PREFIX . "return` `r` WHERE `r`.`return_id` = '" . (int)$return_id . "'");
68:
69: return $query->row;
70: }
71:
72: /**
73: * Get Returns
74: *
75: * @param array<string, mixed> $data
76: *
77: * @return array<int, array<string, mixed>>
78: */
79: public function getReturns(array $data = []): array {
80: $sql = "SELECT *, CONCAT(`r`.`firstname`, ' ', `r`.`lastname`) AS `customer`, (SELECT `rs`.`name` FROM `" . DB_PREFIX . "return_status` `rs` WHERE `rs`.`return_status_id` = `r`.`return_status_id` AND `rs`.`language_id` = '" . (int)$this->config->get('config_language_id') . "') AS `return_status` FROM `" . DB_PREFIX . "return` `r`";
81:
82: $implode = [];
83:
84: if (!empty($data['filter_return_id'])) {
85: $implode[] = "`r`.`return_id` = '" . (int)$data['filter_return_id'] . "'";
86: }
87:
88: if (!empty($data['filter_order_id'])) {
89: $implode[] = "`r`.`order_id` = '" . (int)$data['filter_order_id'] . "'";
90: }
91:
92: if (!empty($data['filter_customer'])) {
93: $implode[] = "LCASE(CONCAT(`r`.`firstname`, ' ', `r`.`lastname`)) LIKE '" . $this->db->escape(oc_strtolower($data['filter_customer']) . '%') . "'";
94: }
95:
96: if (!empty($data['filter_product'])) {
97: $implode[] = "LCASE(`r`.`product` = '" . $this->db->escape(oc_strtolower($data['filter_product'])) . "'";
98: }
99:
100: if (!empty($data['filter_model'])) {
101: $implode[] = "LCASE(`r`.`model` = '" . $this->db->escape(oc_strtolower($data['filter_model'])) . "'";
102: }
103:
104: if (!empty($data['filter_return_status_id'])) {
105: $implode[] = "`r`.`return_status_id` = '" . (int)$data['filter_return_status_id'] . "'";
106: }
107:
108: if (!empty($data['filter_date_from'])) {
109: $implode[] = "DATE(`r`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
110: }
111:
112: if (!empty($data['filter_date_to'])) {
113: $implode[] = "DATE(`r`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
114: }
115:
116: if ($implode) {
117: $sql .= " WHERE " . implode(" AND ", $implode);
118: }
119:
120: $sort_data = [
121: 'r.return_id',
122: 'r.order_id',
123: 'customer',
124: 'r.product',
125: 'r.model',
126: 'return_status',
127: 'r.date_added',
128: 'r.date_modified'
129: ];
130:
131: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
132: $sql .= " ORDER BY " . $data['sort'];
133: } else {
134: $sql .= " ORDER BY `r`.`return_id`";
135: }
136:
137: if (isset($data['order']) && ($data['order'] == 'DESC')) {
138: $sql .= " DESC";
139: } else {
140: $sql .= " ASC";
141: }
142:
143: if (isset($data['start']) || isset($data['limit'])) {
144: if ($data['start'] < 0) {
145: $data['start'] = 0;
146: }
147:
148: if ($data['limit'] < 1) {
149: $data['limit'] = 20;
150: }
151:
152: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
153: }
154:
155: $query = $this->db->query($sql);
156:
157: return $query->rows;
158: }
159:
160: /**
161: * Get Total Returns
162: *
163: * @param array<string, mixed> $data
164: *
165: * @return int
166: */
167: public function getTotalReturns(array $data = []): int {
168: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "return` `r`";
169:
170: $implode = [];
171:
172: if (!empty($data['filter_return_id'])) {
173: $implode[] = "`r`.`return_id` = '" . (int)$data['filter_return_id'] . "'";
174: }
175:
176: if (!empty($data['filter_customer'])) {
177: $implode[] = "LCASE(CONCAT(`r`.`firstname`, ' ', `r`.`lastname`)) LIKE '" . $this->db->escape(oc_strtolower($data['filter_customer']) . '%') . "'";
178: }
179:
180: if (!empty($data['filter_order_id'])) {
181: $implode[] = "`r`.`order_id` = '" . $this->db->escape((string)$data['filter_order_id']) . "'";
182: }
183:
184: if (!empty($data['filter_product'])) {
185: $implode[] = "`r`.`product` = '" . $this->db->escape((string)$data['filter_product']) . "'";
186: }
187:
188: if (!empty($data['filter_model'])) {
189: $implode[] = "`r`.`model` = '" . $this->db->escape((string)$data['filter_model']) . "'";
190: }
191:
192: if (!empty($data['filter_return_status_id'])) {
193: $implode[] = "`r`.`return_status_id` = '" . (int)$data['filter_return_status_id'] . "'";
194: }
195:
196: if (!empty($data['filter_date_from'])) {
197: $implode[] = "DATE(`r`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
198: }
199:
200: if (!empty($data['filter_date_to'])) {
201: $implode[] = "DATE(`r`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
202: }
203:
204: if ($implode) {
205: $sql .= " WHERE " . implode(" AND ", $implode);
206: }
207:
208: $query = $this->db->query($sql);
209:
210: return (int)$query->row['total'];
211: }
212:
213: /**
214: * Get Total Returns By Return Status ID
215: *
216: * @param int $return_status_id
217: *
218: * @return int
219: */
220: public function getTotalReturnsByReturnStatusId(int $return_status_id): int {
221: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "return` WHERE `return_status_id` = '" . (int)$return_status_id . "'");
222:
223: return (int)$query->row['total'];
224: }
225:
226: /**
227: * Get Total Returns By Return Reason ID
228: *
229: * @param int $return_reason_id
230: *
231: * @return int
232: */
233: public function getTotalReturnsByReturnReasonId(int $return_reason_id): int {
234: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "return` WHERE `return_reason_id` = '" . (int)$return_reason_id . "'");
235:
236: return (int)$query->row['total'];
237: }
238:
239: /**
240: * Get Total Returns By Return Action ID
241: *
242: * @param int $return_action_id
243: *
244: * @return int
245: */
246: public function getTotalReturnsByReturnActionId(int $return_action_id): int {
247: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "return` WHERE `return_action_id` = '" . (int)$return_action_id . "'");
248:
249: return (int)$query->row['total'];
250: }
251:
252: /**
253: * Add History
254: *
255: * @param int $return_id
256: * @param int $return_status_id
257: * @param string $comment
258: * @param bool $notify
259: *
260: * @return void
261: */
262: public function addHistory(int $return_id, int $return_status_id, string $comment, bool $notify): void {
263: $this->editReturnStatusId($return_id, $return_status_id);
264:
265: $this->db->query("INSERT INTO `" . DB_PREFIX . "return_history` SET `return_id` = '" . (int)$return_id . "', `return_status_id` = '" . (int)$return_status_id . "', `notify` = '" . (int)$notify . "', `comment` = '" . $this->db->escape(strip_tags($comment)) . "', `date_added` = NOW()");
266: }
267:
268: /**
269: * Delete Return Histories
270: *
271: * @param int $return_id
272: *
273: * @return void
274: */
275: public function deleteHistories(int $return_id): void {
276: $this->db->query("DELETE FROM `" . DB_PREFIX . "return_history` WHERE `return_id` = '" . (int)$return_id . "'");
277: }
278:
279: /**
280: * Get Histories
281: *
282: * @param int $return_id
283: * @param int $start
284: * @param int $limit
285: *
286: * @return array<int, array<string, mixed>>
287: */
288: public function getHistories(int $return_id, int $start = 0, int $limit = 10): array {
289: if ($start < 0) {
290: $start = 0;
291: }
292:
293: if ($limit < 1) {
294: $limit = 10;
295: }
296:
297: $query = $this->db->query("SELECT `rh`.`date_added`, `rs`.`name` AS `status`, `rh`.`comment`, `rh`.`notify` FROM `" . DB_PREFIX . "return_history` `rh` LEFT JOIN `" . DB_PREFIX . "return_status` `rs` ON `rh`.`return_status_id` = `rs`.`return_status_id` WHERE `rh`.`return_id` = '" . (int)$return_id . "' AND `rs`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `rh`.`date_added` DESC LIMIT " . (int)$start . "," . (int)$limit);
298:
299: return $query->rows;
300: }
301:
302: /**
303: * Get Total Histories
304: *
305: * @param int $return_id
306: *
307: * @return int
308: */
309: public function getTotalHistories(int $return_id): int {
310: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "return_history` WHERE `return_id` = '" . (int)$return_id . "'");
311:
312: return (int)$query->row['total'];
313: }
314:
315: /**
316: * Get Total Histories By Return Status ID
317: *
318: * @param int $return_status_id
319: *
320: * @return int
321: */
322: public function getTotalHistoriesByReturnStatusId(int $return_status_id): int {
323: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "return_history` WHERE `return_status_id` = '" . (int)$return_status_id . "'");
324:
325: return (int)$query->row['total'];
326: }
327: }
328: