1: <?php
2: namespace Opencart\Admin\Model\Extension\Opencart\Report;
3: /**
4: * Class CustomerTransaction
5: *
6: * @package Opencart\Admin\Model\Extension\Opencart\Report
7: */
8: class CustomerTransaction extends \Opencart\System\Engine\Model {
9: /**
10: * Get Transactions
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return array<int, array<string, mixed>>
15: */
16: public function getTransactions(array $data = []): array {
17: $sql = "SELECT ct.`customer_id`, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS customer, `c`.`email`, cgd.`name` AS customer_group, `c`.`status`, SUM(ct.`amount`) AS `total` FROM `" . DB_PREFIX . "customer_transaction` ct LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (ct.`customer_id` = `c`.`customer_id`) LEFT JOIN `" . DB_PREFIX . "customer_group_description` cgd ON (`c`.`customer_group_id` = cgd.`customer_group_id`) WHERE cgd.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
18:
19: if (!empty($data['filter_date_start'])) {
20: $sql .= " AND DATE(ct.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
21: }
22:
23: if (!empty($data['filter_date_end'])) {
24: $sql .= " AND DATE(ct.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
25: }
26:
27: if (!empty($data['filter_customer'])) {
28: $sql .= " AND CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
29: }
30:
31: $sql .= " GROUP BY ct.`customer_id` ORDER BY total DESC";
32:
33: if (isset($data['start']) || isset($data['limit'])) {
34: if ($data['start'] < 0) {
35: $data['start'] = 0;
36: }
37:
38: if ($data['limit'] < 1) {
39: $data['limit'] = 20;
40: }
41:
42: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
43: }
44:
45: $query = $this->db->query($sql);
46:
47: return $query->rows;
48: }
49:
50: /**
51: * Get Total Transactions
52: *
53: * @param array<string, mixed> $data
54: *
55: * @return int
56: */
57: public function getTotalTransactions(array $data = []): int {
58: $sql = "SELECT COUNT(DISTINCT ct.`customer_id`) AS `total` FROM `" . DB_PREFIX . "customer_transaction` ct LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (ct.`customer_id` = `c`.`customer_id`)";
59:
60: $implode = [];
61:
62: if (!empty($data['filter_date_start'])) {
63: $implode[] = "DATE(ct.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_start']) . "')";
64: }
65:
66: if (!empty($data['filter_date_end'])) {
67: $implode[] = "DATE(ct.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_end']) . "')";
68: }
69:
70: if (!empty($data['filter_customer'])) {
71: $implode[] = "CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) LIKE '" . $this->db->escape((string)$data['filter_customer']) . "'";
72: }
73:
74: if ($implode) {
75: $sql .= " WHERE " . implode(" AND ", $implode);
76: }
77:
78: $query = $this->db->query($sql);
79:
80: return (int)$query->row['total'];
81: }
82: }
83: