1: | <?php
|
2: | namespace Opencart\Admin\Model\Marketing;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Affiliate extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: | |
12: | |
13: | |
14: | |
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: | |
22: | |
23: | |
24: | |
25: | |
26: | |
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: | |
34: | |
35: | |
36: | |
37: | |
38: | |
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: | |
46: | |
47: | |
48: | |
49: | |
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: | |
59: | |
60: | |
61: | |
62: | |
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: | |
76: | |
77: | |
78: | |
79: | |
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: | |
93: | |
94: | |
95: | |
96: | |
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: | |
180: | |
181: | |
182: | |
183: | |
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: | |
229: | |
230: | |
231: | |
232: | |
233: | |
234: | |
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: | |
252: | |
253: | |
254: | |
255: | |
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: | |
263: | |
264: | |
265: | |
266: | |
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: | |