1: <?php
2: namespace Opencart\Admin\Model\Marketing;
3: /**
4: * Class Affiliate
5: *
6: * @package Opencart\Admin\Model\Marketing
7: */
8: class Affiliate extends \Opencart\System\Engine\Model {
9: /**
10: * Add Affiliate
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return void
15: */
16: public function addAffiliate(array $data): void {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "customer_affiliate` SET `customer_id` = '" . (int)$data['customer_id'] . "', `company` = '" . $this->db->escape((string)$data['company']) . "', `website` = '" . $this->db->escape((string)$data['website']) . "', `tracking` = '" . $this->db->escape((string)$data['tracking']) . "', `commission` = '" . (float)$data['commission'] . "', `tax` = '" . $this->db->escape((string)$data['tax']) . "', `payment_method` = '" . $this->db->escape((string)$data['payment_method']) . "', `cheque` = '" . $this->db->escape((string)$data['cheque']) . "', `paypal` = '" . $this->db->escape((string)$data['paypal']) . "', `bank_name` = '" . $this->db->escape((string)$data['bank_name']) . "', `bank_branch_number` = '" . $this->db->escape((string)$data['bank_branch_number']) . "', `bank_swift_code` = '" . $this->db->escape((string)$data['bank_swift_code']) . "', `bank_account_name` = '" . $this->db->escape((string)$data['bank_account_name']) . "', `bank_account_number` = '" . $this->db->escape((string)$data['bank_account_number']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `status` = '" . (bool)($data['status'] ?? 0) . "', `date_added` = NOW()");
18: }
19:
20: /**
21: * Edit Affiliate
22: *
23: * @param int $customer_id
24: * @param array<string, mixed> $data
25: *
26: * @return void
27: */
28: public function editAffiliate(int $customer_id, array $data): void {
29: $this->db->query("UPDATE `" . DB_PREFIX . "customer_affiliate` SET `company` = '" . $this->db->escape((string)$data['company']) . "', `website` = '" . $this->db->escape((string)$data['website']) . "', `tracking` = '" . $this->db->escape((string)$data['tracking']) . "', `commission` = '" . (float)$data['commission'] . "', `tax` = '" . $this->db->escape((string)$data['tax']) . "', `payment_method` = '" . $this->db->escape((string)$data['payment_method']) . "', `cheque` = '" . $this->db->escape((string)$data['cheque']) . "', `paypal` = '" . $this->db->escape((string)$data['paypal']) . "', `bank_name` = '" . $this->db->escape((string)$data['bank_name']) . "', `bank_branch_number` = '" . $this->db->escape((string)$data['bank_branch_number']) . "', `bank_swift_code` = '" . $this->db->escape((string)$data['bank_swift_code']) . "', `bank_account_name` = '" . $this->db->escape((string)$data['bank_account_name']) . "', `bank_account_number` = '" . $this->db->escape((string)$data['bank_account_number']) . "', `custom_field` = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : json_encode([])) . "', `status` = '" . (bool)($data['status'] ?? 0) . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
30: }
31:
32: /**
33: * Edit Balance
34: *
35: * @param int $customer_id
36: * @param float $amount
37: *
38: * @return void
39: */
40: public function editBalance(int $customer_id, float $amount): void {
41: $this->db->query("UPDATE `" . DB_PREFIX . "customer_affiliate` SET `balance` = '" . (float)$amount . "' WHERE `customer_id` = '" . (int)$customer_id . "'");
42: }
43:
44: /**
45: * Delete Affiliate
46: *
47: * @param int $customer_id
48: *
49: * @return void
50: */
51: public function deleteAffiliate(int $customer_id): void {
52: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_affiliate` WHERE `customer_id` = '" . (int)$customer_id . "'");
53:
54: $this->deleteReport($customer_id);
55: }
56:
57: /**
58: * Get Affiliate
59: *
60: * @param int $customer_id
61: *
62: * @return array<string, mixed>
63: */
64: public function getAffiliate(int $customer_id): array {
65: $query = $this->db->query("SELECT DISTINCT *, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `customer`, `ca`.`custom_field` FROM `" . DB_PREFIX . "customer_affiliate` `ca` LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (`ca`.`customer_id` = `c`.`customer_id`) WHERE `ca`.`customer_id` = '" . (int)$customer_id . "'");
66:
67: if ($query->num_rows) {
68: return $query->row + ['custom_field' => json_decode($query->row['custom_field'], true)];
69: } else {
70: return [];
71: }
72: }
73:
74: /**
75: * Get Affiliate By Tracking
76: *
77: * @param string $tracking
78: *
79: * @return array<string, mixed>
80: */
81: public function getAffiliateByTracking(string $tracking): array {
82: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_affiliate` WHERE `tracking` = '" . $this->db->escape($tracking) . "'");
83:
84: if ($query->num_rows) {
85: return $query->row + ['custom_field' => json_decode($query->row['custom_field'], true)];
86: } else {
87: return [];
88: }
89: }
90:
91: /**
92: * Get Affiliates
93: *
94: * @param array<string, mixed> $data
95: *
96: * @return array<int, array<string, mixed>>
97: */
98: public function getAffiliates(array $data = []): array {
99: $sql = "SELECT *, CONCAT(`c`.`firstname`, ' ', `c`.`lastname`) AS `name`, `ca`.`status` FROM `" . DB_PREFIX . "customer_affiliate` `ca` LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (`ca`.`customer_id` = `c`.`customer_id`)";
100:
101: $implode = [];
102:
103: if (!empty($data['filter_name'])) {
104: $implode[] = "LCASE(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) LIKE '" . $this->db->escape(oc_strtolower($data['filter_name']) . '%') . "'";
105: }
106:
107: if (!empty($data['filter_tracking'])) {
108: $implode[] = "LCASE(`ca`.`tracking`) = '" . $this->db->escape(oc_strtolower($data['filter_tracking'])) . "'";
109: }
110:
111: if (!empty($data['filter_payment_method'])) {
112: $implode[] = "LCASE(`ca`.`payment_method`) = '" . $this->db->escape(oc_strtolower($data['filter_payment_method'])) . "'";
113: }
114:
115: if (!empty($data['filter_commission'])) {
116: $implode[] = "`ca`.`commission` = '" . (float)$data['filter_commission'] . "'";
117: }
118:
119: if (!empty($data['filter_date_from'])) {
120: $implode[] = "DATE(`ca`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
121: }
122:
123: if (!empty($data['filter_date_to'])) {
124: $implode[] = "DATE(`ca`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
125: }
126:
127: if (isset($data['filter_status']) && $data['filter_status'] !== '') {
128: $implode[] = "`ca`.`status` = '" . (bool)$data['filter_status'] . "'";
129: }
130:
131: if ($implode) {
132: $sql .= " WHERE " . implode(" AND ", $implode);
133: }
134:
135: $sort_data = [
136: 'name',
137: 'ca.tracking',
138: 'ca.commission',
139: 'ca.status',
140: 'ca.date_added'
141: ];
142:
143: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
144: $sql .= " ORDER BY " . $data['sort'];
145: } else {
146: $sql .= " ORDER BY `name`";
147: }
148:
149: if (isset($data['order']) && ($data['order'] == 'DESC')) {
150: $sql .= " DESC";
151: } else {
152: $sql .= " ASC";
153: }
154:
155: if (isset($data['start']) || isset($data['limit'])) {
156: if ($data['start'] < 0) {
157: $data['start'] = 0;
158: }
159:
160: if ($data['limit'] < 1) {
161: $data['limit'] = 20;
162: }
163:
164: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
165: }
166:
167: $order_data = [];
168:
169: $query = $this->db->query($sql);
170:
171: foreach ($query->rows as $key => $result) {
172: $order_data[$key] = $result + ['custom_field' => json_decode($result['custom_field'], true)];
173: }
174:
175: return $order_data;
176: }
177:
178: /**
179: * Get Total Affiliates
180: *
181: * @param array<string, mixed> $data
182: *
183: * @return int
184: */
185: public function getTotalAffiliates(array $data = []): int {
186: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_affiliate` `ca` LEFT JOIN `" . DB_PREFIX . "customer` `c` ON (`ca`.`customer_id` = `c`.`customer_id`)";
187:
188: $implode = [];
189:
190: if (!empty($data['filter_name'])) {
191: $implode[] = "LCASE(CONCAT(`c`.`firstname`, ' ', `c`.`lastname`)) LIKE '" . $this->db->escape(oc_strtolower($data['filter_name']) . '%') . "'";
192: }
193:
194: if (!empty($data['filter_tracking'])) {
195: $implode[] = "LCASE(`ca`.`tracking`) = '" . $this->db->escape(oc_strtolower($data['filter_tracking'])) . "'";
196: }
197:
198: if (!empty($data['filter_payment_method'])) {
199: $implode[] = "LCASE(`ca`.`payment_method`) = '" . $this->db->escape(oc_strtolower($data['filter_payment_method'])) . "'";
200: }
201:
202: if (!empty($data['filter_commission'])) {
203: $implode[] = "`ca`.`commission` = '" . (float)$data['filter_commission'] . "'";
204: }
205:
206: if (!empty($data['filter_date_from'])) {
207: $implode[] = "DATE(`ca`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
208: }
209:
210: if (!empty($data['filter_date_to'])) {
211: $implode[] = "DATE(`ca`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
212: }
213:
214: if (isset($data['filter_status']) && $data['filter_status'] !== '') {
215: $implode[] = "`ca`.`status` = '" . (bool)$data['filter_status'] . "'";
216: }
217:
218: if ($implode) {
219: $sql .= " WHERE " . implode(" AND ", $implode);
220: }
221:
222: $query = $this->db->query($sql);
223:
224: return (int)$query->row['total'];
225: }
226:
227: /**
228: * Get Reports
229: *
230: * @param int $customer_id
231: * @param int $start
232: * @param int $limit
233: *
234: * @return array<int, array<string, mixed>>
235: */
236: public function getReports(int $customer_id, int $start = 0, int $limit = 10): array {
237: if ($start < 0) {
238: $start = 0;
239: }
240:
241: if ($limit < 1) {
242: $limit = 10;
243: }
244:
245: $query = $this->db->query("SELECT `ip`, `store_id`, `country`, `date_added` FROM `" . DB_PREFIX . "customer_affiliate_report` WHERE `customer_id` = '" . (int)$customer_id . "' ORDER BY `date_added` ASC LIMIT " . (int)$start . "," . (int)$limit);
246:
247: return $query->rows;
248: }
249:
250: /**
251: * Delete Reports
252: *
253: * @param int $customer_id
254: *
255: * @return void
256: */
257: public function deleteReports(int $customer_id): void {
258: $this->db->query("DELETE FROM `" . DB_PREFIX . "customer_affiliate_report` WHERE `customer_id` = '" . (int)$customer_id . "'");
259: }
260:
261: /**
262: * Get Total Reports
263: *
264: * @param int $customer_id
265: *
266: * @return int
267: */
268: public function getTotalReports(int $customer_id): int {
269: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "customer_affiliate_report` WHERE `customer_id` = '" . (int)$customer_id . "'");
270:
271: return (int)$query->row['total'];
272: }
273: }
274: