1: <?php
2: namespace Opencart\Admin\Model\Marketing;
3: /**
4: * Class Marketing
5: *
6: * @package Opencart\Admin\Model\Marketing
7: */
8: class Marketing extends \Opencart\System\Engine\Model {
9: /**
10: * Add Marketing
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return int
15: */
16: public function addMarketing(array $data): int {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "marketing` SET `name` = '" . $this->db->escape((string)$data['name']) . "', `description` = '" . $this->db->escape((string)$data['description']) . "', `code` = '" . $this->db->escape((string)$data['code']) . "', `date_added` = NOW()");
18:
19: return $this->db->getLastId();
20: }
21:
22: /**
23: * Edit Marketing
24: *
25: * @param int $marketing_id
26: * @param array<string, mixed> $data
27: *
28: * @return void
29: */
30: public function editMarketing(int $marketing_id, array $data): void {
31: $this->db->query("UPDATE `" . DB_PREFIX . "marketing` SET `name` = '" . $this->db->escape((string)$data['name']) . "', `description` = '" . $this->db->escape((string)$data['description']) . "', `code` = '" . $this->db->escape((string)$data['code']) . "' WHERE `marketing_id` = '" . (int)$marketing_id . "'");
32: }
33:
34: /**
35: * Delete Marketing
36: *
37: * @param int $marketing_id
38: *
39: * @return void
40: */
41: public function deleteMarketing(int $marketing_id): void {
42: $this->db->query("DELETE FROM `" . DB_PREFIX . "marketing` WHERE `marketing_id` = '" . (int)$marketing_id . "'");
43:
44: $this->deleteReports($marketing_id);
45: }
46:
47: /**
48: * Get Marketing
49: *
50: * @param int $marketing_id
51: *
52: * @return array<string, mixed>
53: */
54: public function getMarketing(int $marketing_id): array {
55: $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "marketing` WHERE `marketing_id` = '" . (int)$marketing_id . "'");
56:
57: return $query->row;
58: }
59:
60: /**
61: * Get Marketing By Code
62: *
63: * @param string $code
64: *
65: * @return array<string, mixed>
66: */
67: public function getMarketingByCode(string $code): array {
68: $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "marketing` WHERE `code` = '" . $this->db->escape($code) . "'");
69:
70: return $query->row;
71: }
72:
73: /**
74: * Get Marketing(s)
75: *
76: * @param array<string, mixed> $data
77: *
78: * @return array<int, array<string, mixed>>
79: */
80: public function getMarketings(array $data = []): array {
81: $implode = [];
82:
83: $order_statuses = $this->config->get('config_complete_status');
84:
85: foreach ($order_statuses as $order_status_id) {
86: $implode[] = "`o`.`order_status_id` = '" . (int)$order_status_id . "'";
87: }
88:
89: $sql = "SELECT *, (SELECT COUNT(*) FROM `" . DB_PREFIX . "order` `o` WHERE (" . implode(" OR ", $implode) . ") AND `o`.`marketing_id` = `m`.`marketing_id`) AS `orders` FROM `" . DB_PREFIX . "marketing` `m`";
90:
91: $implode = [];
92:
93: if (!empty($data['filter_name'])) {
94: $implode[] = "LCASE(`m`.`name`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_name']) . '%') . "'";
95: }
96:
97: if (!empty($data['filter_code'])) {
98: $implode[] = "LCASE(`m`.`code`) = '" . $this->db->escape(oc_strtolower($data['filter_code'])) . "'";
99: }
100:
101: if (!empty($data['filter_date_from'])) {
102: $implode[] = "DATE(`m`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
103: }
104:
105: if (!empty($data['filter_date_to'])) {
106: $implode[] = "DATE(`m`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
107: }
108:
109: if ($implode) {
110: $sql .= " WHERE " . implode(" AND ", $implode);
111: }
112:
113: $sort_data = [
114: 'm.name',
115: 'm.code',
116: 'm.date_added'
117: ];
118:
119: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
120: $sql .= " ORDER BY " . $data['sort'];
121: } else {
122: $sql .= " ORDER BY `m`.`name`";
123: }
124:
125: if (isset($data['order']) && ($data['order'] == 'DESC')) {
126: $sql .= " DESC";
127: } else {
128: $sql .= " ASC";
129: }
130:
131: if (isset($data['start']) || isset($data['limit'])) {
132: if ($data['start'] < 0) {
133: $data['start'] = 0;
134: }
135:
136: if ($data['limit'] < 1) {
137: $data['limit'] = 20;
138: }
139:
140: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
141: }
142:
143: $query = $this->db->query($sql);
144:
145: return $query->rows;
146: }
147:
148: /**
149: * Get Total Marketing(s)
150: *
151: * @param array<string, mixed> $data
152: *
153: * @return int
154: */
155: public function getTotalMarketings(array $data = []): int {
156: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "marketing`";
157:
158: $implode = [];
159:
160: if (!empty($data['filter_name'])) {
161: $implode[] = "LCASE(`name`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_name'])) . "'";
162: }
163:
164: if (!empty($data['filter_code'])) {
165: $implode[] = "LCASE(`code`) = '" . $this->db->escape(oc_strtolower($data['filter_code'])) . "'";
166: }
167:
168: if (!empty($data['filter_date_from'])) {
169: $implode[] = "DATE(`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
170: }
171:
172: if (!empty($data['filter_date_to'])) {
173: $implode[] = "DATE(`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
174: }
175:
176: if ($implode) {
177: $sql .= " WHERE " . implode(" AND ", $implode);
178: }
179:
180: $query = $this->db->query($sql);
181:
182: return (int)$query->row['total'];
183: }
184:
185: /**
186: * Delete Marketing Reports
187: *
188: * @param int $marketing_id
189: *
190: * @return void
191: */
192: public function deleteReports(int $marketing_id): void {
193: $this->db->query("DELETE FROM `" . DB_PREFIX . "marketing_report` WHERE `marketing_id` = '" . (int)$marketing_id . "'");
194: }
195:
196: /**
197: * Get Reports
198: *
199: * @param int $marketing_id
200: * @param int $start
201: * @param int $limit
202: *
203: * @return array<int, array<string, mixed>>
204: */
205: public function getReports(int $marketing_id, int $start = 0, int $limit = 10): array {
206: if ($start < 0) {
207: $start = 0;
208: }
209:
210: if ($limit < 1) {
211: $limit = 10;
212: }
213:
214: $query = $this->db->query("SELECT `ip`, `store_id`, `country`, `date_added` FROM `" . DB_PREFIX . "marketing_report` WHERE `marketing_id` = '" . (int)$marketing_id . "' ORDER BY `date_added` ASC LIMIT " . (int)$start . "," . (int)$limit);
215:
216: return $query->rows;
217: }
218:
219: /**
220: * Get Total Reports
221: *
222: * @param int $marketing_id
223: *
224: * @return int
225: */
226: public function getTotalReports(int $marketing_id): int {
227: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "marketing_report` WHERE `marketing_id` = '" . (int)$marketing_id . "'");
228:
229: return (int)$query->row['total'];
230: }
231: }
232: