1: <?php
2: namespace Opencart\Admin\Model\Extension\Opencart\Report;
3: /**
4: * Class Sale
5: *
6: * @package Opencart\Admin\Model\Extension\Opencart\Report
7: */
8: class Sale extends \Opencart\System\Engine\Model {
9: /**
10: * Get Total Sales
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return float
15: */
16: public function getTotalSales(array $data = []): float {
17: $sql = "SELECT SUM(`total`) AS `total` FROM `" . DB_PREFIX . "order` WHERE `order_status_id` > '0'";
18:
19: if (!empty($data['filter_date_added'])) {
20: $sql .= " AND DATE(`date_added`) = DATE('" . $this->db->escape((string)$data['filter_date_added']) . "')";
21: }
22:
23: $query = $this->db->query($sql);
24:
25: return (float)$query->row['total'];
26: }
27:
28: /**
29: * Get Total Orders By Country
30: *
31: * @return array<int, array<string, mixed>>
32: */
33: public function getTotalOrdersByCountry(): array {
34: $query = $this->db->query("SELECT COUNT(*) AS `total`, SUM(`o`.`total`) AS amount, c.`iso_code_2` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "country` c ON (`o`.`payment_country_id` = c.`country_id`) WHERE `o`.`order_status_id` > '0' GROUP BY `o`.`payment_country_id`");
35:
36: return $query->rows;
37: }
38:
39: /**
40: * Get Total Orders By Day
41: *
42: * @return array<int, array<string, int>>
43: */
44: public function getTotalOrdersByDay(): array {
45: $implode = [];
46:
47: foreach ($this->config->get('config_complete_status') as $order_status_id) {
48: $implode[] = "'" . (int)$order_status_id . "'";
49: }
50:
51: $order_data = [];
52:
53: for ($i = 0; $i < 24; $i++) {
54: $order_data[$i] = [
55: 'hour' => $i,
56: 'total' => 0
57: ];
58: }
59:
60: $query = $this->db->query("SELECT COUNT(*) AS `total`, HOUR(`date_added`) AS hour FROM `" . DB_PREFIX . "order` WHERE `order_status_id` IN(" . implode(",", $implode) . ") AND DATE(`date_added`) = DATE(NOW()) GROUP BY HOUR(`date_added`) ORDER BY `date_added` ASC");
61:
62: foreach ($query->rows as $result) {
63: $order_data[$result['hour']] = [
64: 'hour' => $result['hour'],
65: 'total' => $result['total']
66: ];
67: }
68:
69: return $order_data;
70: }
71:
72: /**
73: * Get Total Orders By Week
74: *
75: * @return array<int, array<string, int>>
76: */
77: public function getTotalOrdersByWeek(): array {
78: $implode = [];
79:
80: foreach ($this->config->get('config_complete_status') as $order_status_id) {
81: $implode[] = "'" . (int)$order_status_id . "'";
82: }
83:
84: $order_data = [];
85:
86: $date_start = strtotime('-' . date('w') . ' days');
87:
88: for ($i = 0; $i < 7; $i++) {
89: $date = date('Y-m-d', $date_start + ($i * 86400));
90:
91: $order_data[date('w', strtotime($date))] = [
92: 'day' => date('D', strtotime($date)),
93: 'total' => 0
94: ];
95: }
96:
97: $query = $this->db->query("SELECT COUNT(*) AS `total`, `date_added` FROM `" . DB_PREFIX . "order` WHERE `order_status_id` IN(" . implode(",", $implode) . ") AND DATE(`date_added`) >= DATE('" . $this->db->escape(date('Y-m-d', $date_start)) . "') GROUP BY DAYNAME(`date_added`)");
98:
99: foreach ($query->rows as $result) {
100: $order_data[date('w', strtotime($result['date_added']))] = [
101: 'day' => date('D', strtotime($result['date_added'])),
102: 'total' => $result['total']
103: ];
104: }
105:
106: return $order_data;
107: }
108:
109: /**
110: * Get Total Orders By Month
111: *
112: * @return array<int, array<string, int>>
113: */
114: public function getTotalOrdersByMonth(): array {
115: $implode = [];
116:
117: foreach ($this->config->get('config_complete_status') as $order_status_id) {
118: $implode[] = "'" . (int)$order_status_id . "'";
119: }
120:
121: $order_data = [];
122:
123: for ($i = 1; $i <= date('t'); $i++) {
124: $date = date('Y') . '-' . date('m') . '-' . $i;
125:
126: $order_data[date('j', strtotime($date))] = [
127: 'day' => date('d', strtotime($date)),
128: 'total' => 0
129: ];
130: }
131:
132: $query = $this->db->query("SELECT COUNT(*) AS `total`, `date_added` FROM `" . DB_PREFIX . "order` WHERE `order_status_id` IN(" . implode(",", $implode) . ") AND DATE(`date_added`) >= DATE('" . $this->db->escape(date('Y') . '-' . date('m') . '-1') . "') GROUP BY DATE(`date_added`)");
133:
134: foreach ($query->rows as $result) {
135: $order_data[date('j', strtotime($result['date_added']))] = [
136: 'day' => date('d', strtotime($result['date_added'])),
137: 'total' => $result['total']
138: ];
139: }
140:
141: return $order_data;
142: }
143:
144: /**
145: * Get Total Orders By Year
146: *
147: * @return array<int, array<string, int>>
148: */
149: public function getTotalOrdersByYear(): array {
150: $implode = [];
151:
152: foreach ($this->config->get('config_complete_status') as $order_status_id) {
153: $implode[] = "'" . (int)$order_status_id . "'";
154: }
155:
156: $order_data = [];
157:
158: for ($i = 1; $i <= 12; $i++) {
159: $order_data[$i] = [
160: 'month' => date('M', mktime(0, 0, 0, $i, 1)),
161: 'total' => 0
162: ];
163: }
164:
165: $query = $this->db->query("SELECT COUNT(*) AS `total`, `date_added` FROM `" . DB_PREFIX . "order` WHERE `order_status_id` IN(" . implode(",", $implode) . ") AND YEAR(`date_added`) = YEAR(NOW()) GROUP BY MONTH(`date_added`)");
166:
167: foreach ($query->rows as $result) {
168: $order_data[date('n', strtotime($result['date_added']))] = [
169: 'month' => date('M', strtotime($result['date_added'])),
170: 'total' => $result['total']
171: ];
172: }
173:
174: return $order_data;
175: }
176:
177: /**
178: * Get Orders
179: *
180: * @param array<string, mixed> $data
181: *
182: * @return array<int, array<string, mixed>>
183: */
184: public function getOrders(array $data = []): array {
185: $sql = "SELECT MIN(`o`.`date_added`) AS date_start, MAX(`o`.`date_added`) AS date_end, COUNT(*) AS `orders`, SUM((SELECT SUM(op.`quantity`) FROM `" . DB_PREFIX . "order_product` `op` WHERE `op`.`order_id` = `o`.`order_id` GROUP BY `op`.`order_id`)) AS `products`, SUM((SELECT SUM(`ot`.`value`) FROM `" . DB_PREFIX . "order_total` ot WHERE ot.`order_id` = `o`.`order_id` AND ot.`code` = 'tax' GROUP BY ot.`order_id`)) AS tax, SUM(`o`.`total`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
186:
187: if (!empty($data['filter_order_status_id'])) {
188: $sql .= " WHERE `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
189: } else {
190: $sql .= " WHERE `o`.`order_status_id` > '0'";
191: }
192:
193: if (!empty($data['filter_date_start'])) {
194: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
195: }
196:
197: if (!empty($data['filter_date_end'])) {
198: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
199: }
200:
201: if (!empty($data['filter_group'])) {
202: $group = $data['filter_group'];
203: } else {
204: $group = 'week';
205: }
206:
207: switch ($group) {
208: case 'day':
209: $sql .= " GROUP BY YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), DAY(`o`.`date_added`)";
210: break;
211: default:
212: case 'week':
213: $sql .= " GROUP BY YEAR(`o`.`date_added`), WEEK(`o`.`date_added`)";
214: break;
215: case 'month':
216: $sql .= " GROUP BY YEAR(`o`.`date_added`), MONTH(`o`.`date_added`)";
217: break;
218: case 'year':
219: $sql .= " GROUP BY YEAR(`o`.`date_added`)";
220: break;
221: }
222:
223: $sql .= " ORDER BY `o`.`date_added` DESC";
224:
225: if (isset($data['start']) || isset($data['limit'])) {
226: if ($data['start'] < 0) {
227: $data['start'] = 0;
228: }
229:
230: if ($data['limit'] < 1) {
231: $data['limit'] = 20;
232: }
233:
234: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
235: }
236:
237: $query = $this->db->query($sql);
238:
239: return $query->rows;
240: }
241:
242: /**
243: * Get Total Orders
244: *
245: * @param array<string, mixed> $data
246: *
247: * @return int
248: */
249: public function getTotalOrders(array $data = []): int {
250: if (!empty($data['filter_group'])) {
251: $group = $data['filter_group'];
252: } else {
253: $group = 'week';
254: }
255:
256: switch ($group) {
257: case 'day':
258: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`), MONTH(`date_added`), DAY(`date_added`)) AS `total` FROM `" . DB_PREFIX . "order`";
259: break;
260: default:
261: case 'week':
262: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`), WEEK(`date_added`)) AS `total` FROM `" . DB_PREFIX . "order`";
263: break;
264: case 'month':
265: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`), MONTH(`date_added`)) AS `total` FROM `" . DB_PREFIX . "order`";
266: break;
267: case 'year':
268: $sql = "SELECT COUNT(DISTINCT YEAR(`date_added`)) AS `total` FROM `" . DB_PREFIX . "order`";
269: break;
270: }
271:
272: if (!empty($data['filter_order_status_id'])) {
273: $sql .= " WHERE `order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
274: } else {
275: $sql .= " WHERE `order_status_id` > '0'";
276: }
277:
278: if (!empty($data['filter_date_start'])) {
279: $sql .= " AND DATE(`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
280: }
281:
282: if (!empty($data['filter_date_end'])) {
283: $sql .= " AND DATE(`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
284: }
285:
286: $query = $this->db->query($sql);
287:
288: return (int)$query->row['total'];
289: }
290:
291: /**
292: * Get Taxes
293: *
294: * @param array<string, mixed> $data
295: *
296: * @return array<int, array<string, mixed>>
297: */
298: public function getTaxes(array $data = []): array {
299: $sql = "SELECT MIN(`o`.`date_added`) AS date_start, MAX(`o`.`date_added`) AS date_end, ot.`title`, SUM(ot.`value`) AS `total`, COUNT(`o`.`order_id`) AS `orders` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (ot.`order_id` = `o`.`order_id`) WHERE ot.`code` = 'tax'";
300:
301: if (!empty($data['filter_order_status_id'])) {
302: $sql .= " AND `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
303: } else {
304: $sql .= " AND `o`.`order_status_id` > '0'";
305: }
306:
307: if (!empty($data['filter_date_start'])) {
308: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
309: }
310:
311: if (!empty($data['filter_date_end'])) {
312: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
313: }
314:
315: if (!empty($data['filter_group'])) {
316: $group = $data['filter_group'];
317: } else {
318: $group = 'week';
319: }
320:
321: switch ($group) {
322: case 'day':
323: $sql .= " GROUP BY YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), DAY(`o`.`date_added`), `ot`.`title`";
324: break;
325: default:
326: case 'week':
327: $sql .= " GROUP BY YEAR(`o`.`date_added`), WEEK(`o`.`date_added`), `ot`.`title`";
328: break;
329: case 'month':
330: $sql .= " GROUP BY YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), `ot`.`title`";
331: break;
332: case 'year':
333: $sql .= " GROUP BY YEAR(`o`.`date_added`), ot.`title`";
334: break;
335: }
336:
337: if (isset($data['start']) || isset($data['limit'])) {
338: if ($data['start'] < 0) {
339: $data['start'] = 0;
340: }
341:
342: if ($data['limit'] < 1) {
343: $data['limit'] = 20;
344: }
345:
346: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
347: }
348:
349: $query = $this->db->query($sql);
350:
351: return $query->rows;
352: }
353:
354: /**
355: * Get Total Taxes
356: *
357: * @param array<string, mixed> $data
358: *
359: * @return int
360: */
361: public function getTotalTaxes(array $data = []): int {
362: if (!empty($data['filter_group'])) {
363: $group = $data['filter_group'];
364: } else {
365: $group = 'week';
366: }
367:
368: switch ($group) {
369: case 'day':
370: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), DAY(`o`.`date_added`), `ot`.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
371: break;
372: default:
373: case 'week':
374: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), WEEK(`o`.`date_added`), `ot`.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
375: break;
376: case 'month':
377: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), `ot`.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
378: break;
379: case 'year':
380: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), `ot`.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
381: break;
382: }
383:
384: $sql .= " LEFT JOIN `" . DB_PREFIX . "order_total` `ot` ON (`o`.`order_id` = `ot`.`order_id`) WHERE `ot`.`code` = 'tax'";
385:
386: if (!empty($data['filter_order_status_id'])) {
387: $sql .= " AND `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
388: } else {
389: $sql .= " AND `o`.`order_status_id` > '0'";
390: }
391:
392: if (!empty($data['filter_date_start'])) {
393: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
394: }
395:
396: if (!empty($data['filter_date_end'])) {
397: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
398: }
399:
400: $query = $this->db->query($sql);
401:
402: return (int)$query->row['total'];
403: }
404:
405: /**
406: * Get Shipping
407: *
408: * @param array<string, mixed> $data
409: *
410: * @return array<int, array<string, mixed>>
411: */
412: public function getShipping(array $data = []): array {
413: $sql = "SELECT MIN(`o`.`date_added`) AS date_start, MAX(`o`.`date_added`) AS date_end, ot.`title`, SUM(ot.`value`) AS `total`, COUNT(`o`.`order_id`) AS `orders` FROM `" . DB_PREFIX . "order` `o` LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (`o`.`order_id` = ot.`order_id`) WHERE ot.`code` = 'shipping'";
414:
415: if (!empty($data['filter_order_status_id'])) {
416: $sql .= " AND `o`.`order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
417: } else {
418: $sql .= " AND `o`.`order_status_id` > '0'";
419: }
420:
421: if (!empty($data['filter_date_start'])) {
422: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
423: }
424:
425: if (!empty($data['filter_date_end'])) {
426: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
427: }
428:
429: if (!empty($data['filter_group'])) {
430: $group = $data['filter_group'];
431: } else {
432: $group = 'week';
433: }
434:
435: switch ($group) {
436: case 'day':
437: $sql .= " GROUP BY YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), DAY(`o`.`date_added`), `ot`.`title`";
438: break;
439: default:
440: case 'week':
441: $sql .= " GROUP BY YEAR(`o`.`date_added`), WEEK(`o`.`date_added`), `ot`.`title`";
442: break;
443: case 'month':
444: $sql .= " GROUP BY YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), `ot`.`title`";
445: break;
446: case 'year':
447: $sql .= " GROUP BY YEAR(`o`.`date_added`), `ot`.`title`";
448: break;
449: }
450:
451: if (isset($data['start']) || isset($data['limit'])) {
452: if ($data['start'] < 0) {
453: $data['start'] = 0;
454: }
455:
456: if ($data['limit'] < 1) {
457: $data['limit'] = 20;
458: }
459:
460: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
461: }
462:
463: $query = $this->db->query($sql);
464:
465: return $query->rows;
466: }
467:
468: /**
469: * Get Total Shipping
470: *
471: * @param array<string, mixed> $data
472: *
473: * @return int
474: */
475: public function getTotalShipping(array $data = []): int {
476: if (!empty($data['filter_group'])) {
477: $group = $data['filter_group'];
478: } else {
479: $group = 'week';
480: }
481:
482: switch ($group) {
483: case 'day':
484: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), DAY(`o`.`date_added`), ot.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
485: break;
486: default:
487: case 'week':
488: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), WEEK(`o`.`date_added`), ot.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
489: break;
490: case 'month':
491: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), MONTH(`o`.`date_added`), ot.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
492: break;
493: case 'year':
494: $sql = "SELECT COUNT(DISTINCT YEAR(`o`.`date_added`), ot.`title`) AS `total` FROM `" . DB_PREFIX . "order` `o`";
495: break;
496: }
497:
498: $sql .= " LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (`o`.`order_id` = ot.`order_id`) WHERE ot.`code` = 'shipping'";
499:
500: if (!empty($data['filter_order_status_id'])) {
501: $sql .= " AND `order_status_id` = '" . (int)$data['filter_order_status_id'] . "'";
502: } else {
503: $sql .= " AND `order_status_id` > '0'";
504: }
505:
506: if (!empty($data['filter_date_start'])) {
507: $sql .= " AND DATE(`o`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
508: }
509:
510: if (!empty($data['filter_date_end'])) {
511: $sql .= " AND DATE(`o`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
512: }
513:
514: $query = $this->db->query($sql);
515:
516: return (int)$query->row['total'];
517: }
518: }
519: