1: | <?php
|
2: | namespace Opencart\Admin\Model\Extension\Opencart\Report;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Sale extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: | |
12: | |
13: | |
14: | |
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: | |
30: | |
31: | |
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: | |
41: | |
42: | |
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: | |
74: | |
75: | |
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: | |
111: | |
112: | |
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: | |
146: | |
147: | |
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: | |
179: | |
180: | |
181: | |
182: | |
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: | |
244: | |
245: | |
246: | |
247: | |
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: | |
293: | |
294: | |
295: | |
296: | |
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: | |
356: | |
357: | |
358: | |
359: | |
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: | |
407: | |
408: | |
409: | |
410: | |
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: | |
470: | |
471: | |
472: | |
473: | |
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: | |