1: <?php
2: namespace Opencart\Admin\Model\Customer;
3: /**
4: * Class Custom Field
5: *
6: * @package Opencart\Admin\Model\Customer
7: */
8: class CustomField extends \Opencart\System\Engine\Model {
9: /**
10: * Add Custom Field
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return int
15: */
16: public function addCustomField(array $data): int {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "custom_field` SET `type` = '" . $this->db->escape((string)$data['type']) . "', `value` = '" . $this->db->escape((string)$data['value']) . "', `validation` = '" . $this->db->escape((string)$data['validation']) . "', `location` = '" . $this->db->escape((string)$data['location']) . "', `status` = '" . (bool)($data['status'] ?? 0) . "', `sort_order` = '" . (int)$data['sort_order'] . "'");
18:
19: $custom_field_id = $this->db->getLastId();
20:
21: foreach ($data['custom_field_description'] as $language_id => $custom_field_description) {
22: $this->addDescription($custom_field_id, $language_id, $custom_field_description);
23: }
24:
25: if (isset($data['custom_field_customer_group'])) {
26: foreach ($data['custom_field_customer_group'] as $custom_field_customer_group) {
27: if (isset($custom_field_customer_group['customer_group_id'])) {
28: $this->addCustomerGroup($custom_field_id, $custom_field_customer_group);
29: }
30: }
31: }
32:
33: if (isset($data['custom_field_value'])) {
34: foreach ($data['custom_field_value'] as $custom_field_value) {
35: $this->addValue($custom_field_id, $custom_field_value);
36: }
37: }
38:
39: return $custom_field_id;
40: }
41:
42: /**
43: * Edit Custom Field
44: *
45: * @param int $custom_field_id
46: * @param array<string, mixed> $data
47: *
48: * @return void
49: */
50: public function editCustomField(int $custom_field_id, array $data): void {
51: $this->db->query("UPDATE `" . DB_PREFIX . "custom_field` SET `type` = '" . $this->db->escape((string)$data['type']) . "', `value` = '" . $this->db->escape((string)$data['value']) . "', `validation` = '" . $this->db->escape((string)$data['validation']) . "', `location` = '" . $this->db->escape((string)$data['location']) . "', `status` = '" . (bool)($data['status'] ?? 0) . "', `sort_order` = '" . (int)$data['sort_order'] . "' WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
52:
53: $this->deleteDescriptions($custom_field_id);
54:
55: foreach ($data['custom_field_description'] as $language_id => $custom_field_description) {
56: $this->addDescription($custom_field_id, $language_id, $custom_field_description);
57: }
58:
59: $this->deleteCustomerGroups($custom_field_id);
60:
61: if (isset($data['custom_field_customer_group'])) {
62: foreach ($data['custom_field_customer_group'] as $custom_field_customer_group) {
63: if (isset($custom_field_customer_group['customer_group_id'])) {
64: $this->addCustomerGroup($custom_field_id, $custom_field_customer_group);
65: }
66: }
67: }
68:
69: $this->deleteValues($custom_field_id);
70:
71: if (isset($data['custom_field_value'])) {
72: foreach ($data['custom_field_value'] as $custom_field_value) {
73: $this->addValue($custom_field_id, $custom_field_value);
74: }
75: }
76: }
77:
78: /**
79: * Delete Custom Field
80: *
81: * @param int $custom_field_id
82: *
83: * @return void
84: */
85: public function deleteCustomField(int $custom_field_id): void {
86: $this->db->query("DELETE FROM `" . DB_PREFIX . "custom_field` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
87:
88: $this->deleteDescriptions($custom_field_id);
89: $this->deleteCustomerGroups($custom_field_id);
90: $this->deleteValues($custom_field_id);
91: }
92:
93: /**
94: * Get Custom Field
95: *
96: * @param int $custom_field_id
97: *
98: * @return array<string, mixed>
99: */
100: public function getCustomField(int $custom_field_id): array {
101: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field` `cf` LEFT JOIN `" . DB_PREFIX . "custom_field_description` `cfd` ON (`cf`.`custom_field_id` = `cfd`.`custom_field_id`) WHERE `cf`.`custom_field_id` = '" . (int)$custom_field_id . "' AND `cfd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'");
102:
103: return $query->row;
104: }
105:
106: /**
107: * Get Custom Fields
108: *
109: * @param array<string, mixed> $data
110: *
111: * @return array<int, array<string, mixed>>
112: */
113: public function getCustomFields(array $data = []): array {
114: if (empty($data['filter_customer_group_id'])) {
115: $sql = "SELECT * FROM `" . DB_PREFIX . "custom_field` `cf` LEFT JOIN `" . DB_PREFIX . "custom_field_description` `cfd` ON (`cf`.`custom_field_id` = `cfd`.`custom_field_id`) WHERE `cfd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
116: } else {
117: $sql = "SELECT * FROM `" . DB_PREFIX . "custom_field_customer_group` `cfcg` LEFT JOIN `" . DB_PREFIX . "custom_field` `cf` ON (`cfcg`.`custom_field_id` = `cf`.`custom_field_id`) LEFT JOIN `" . DB_PREFIX . "custom_field_description` `cfd` ON (`cf`.`custom_field_id` = `cfd`.`custom_field_id`) WHERE `cfd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
118: }
119:
120: if (!empty($data['filter_name'])) {
121: $sql .= " AND LCASE(`cfd`.`name`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_name']) . '%') . "'";
122: }
123:
124: if (isset($data['filter_status'])) {
125: $sql .= " AND `cf`.`status` = '" . (bool)$data['filter_status'] . "'";
126: }
127:
128: if (isset($data['filter_location'])) {
129: $sql .= " AND `cf`.`location` = '" . $this->db->escape((string)$data['filter_location']) . "'";
130: }
131:
132: if (!empty($data['filter_customer_group_id'])) {
133: $sql .= " AND `cfcg`.`customer_group_id` = '" . (int)$data['filter_customer_group_id'] . "'";
134: }
135:
136: $sort_data = [
137: 'cfd.name',
138: 'cf.type',
139: 'cf.location',
140: 'cf.status',
141: 'cf.sort_order'
142: ];
143:
144: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
145: $sql .= " ORDER BY " . $data['sort'];
146: } else {
147: $sql .= " ORDER BY `cfd`.`name`";
148: }
149:
150: if (isset($data['order']) && ($data['order'] == 'DESC')) {
151: $sql .= " DESC";
152: } else {
153: $sql .= " ASC";
154: }
155:
156: if (isset($data['start']) || isset($data['limit'])) {
157: if ($data['start'] < 0) {
158: $data['start'] = 0;
159: }
160:
161: if ($data['limit'] < 1) {
162: $data['limit'] = 20;
163: }
164:
165: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
166: }
167:
168: $query = $this->db->query($sql);
169:
170: return $query->rows;
171: }
172:
173: /**
174: * Get Total Custom Fields
175: *
176: * @return int
177: */
178: public function getTotalCustomFields(): int {
179: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "custom_field`");
180:
181: return (int)$query->row['total'];
182: }
183:
184: /**
185: * Add Description
186: *
187: * @param int $custom_field_id primary key of the custom field record to be fetched
188: * @param int $language_id
189: * @param array<string, mixed> $data
190: *
191: * @return void
192: */
193: public function addDescription(int $custom_field_id, int $language_id, array $data): void {
194: $this->db->query("INSERT INTO `" . DB_PREFIX . "custom_field_description` SET `custom_field_id` = '" . (int)$custom_field_id . "', `language_id` = '" . (int)$language_id . "', `name` = '" . $this->db->escape($data['name']) . "'");
195: }
196:
197: /**
198: * Delete Description
199: *
200: * @param int $custom_field_id primary key of the custom field record to be fetched
201: *
202: * @return void
203: */
204: public function deleteDescriptions(int $custom_field_id): void {
205: $this->db->query("DELETE FROM `" . DB_PREFIX . "custom_field_description` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
206: }
207:
208: /**
209: * Delete Descriptions By Language ID
210: *
211: * @param int $language_id
212: *
213: * @return void
214: */
215: public function deleteDescriptionsByLanguageId(int $language_id): void {
216: $this->db->query("DELETE FROM `" . DB_PREFIX . "custom_field_description` WHERE `language_id` = '" . (int)$language_id . "'");
217: }
218:
219: /**
220: * Get Descriptions
221: *
222: * @param int $custom_field_id
223: *
224: * @return array<int, array<string, string>>
225: */
226: public function getDescriptions(int $custom_field_id): array {
227: $custom_field_data = [];
228:
229: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_description` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
230:
231: foreach ($query->rows as $result) {
232: $custom_field_data[$result['language_id']] = ['name' => $result['name']];
233: }
234:
235: return $custom_field_data;
236: }
237:
238: /**
239: * Get Descriptions By Language ID
240: *
241: * @param int $language_id
242: *
243: * @return array<int, array<string, string>>
244: */
245: public function getDescriptionsByLanguageId(int $language_id): array {
246: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_description` WHERE `language_id` = '" . (int)$language_id . "'");
247:
248: return $query->rows;
249: }
250:
251: /**
252: * Add Customer Group
253: *
254: * @param int $custom_field_id
255: * @param array<string, mixed> $data
256: *
257: * @return void
258: */
259: public function addCustomerGroup(int $custom_field_id, array $data): void {
260: $this->db->query("INSERT INTO `" . DB_PREFIX . "custom_field_customer_group` SET `custom_field_id` = '" . (int)$custom_field_id . "', `customer_group_id` = '" . (int)$data['customer_group_id'] . "', `required` = '" . (int)(isset($data['required']) ? 1 : 0) . "'");
261: }
262:
263: /**
264: * Delete Customer Groups
265: *
266: * @param int $custom_field_id
267: *
268: * @return void
269: */
270: public function deleteCustomerGroups(int $custom_field_id): void {
271: $this->db->query("delete FROM `" . DB_PREFIX . "custom_field_customer_group` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
272: }
273:
274: /**
275: * Get Customer Groups
276: *
277: * @param int $custom_field_id
278: *
279: * @return array<int, array<string, mixed>>
280: */
281: public function getCustomerGroups(int $custom_field_id): array {
282: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_customer_group` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
283:
284: return $query->rows;
285: }
286:
287: /**
288: * Add Value
289: *
290: * @param int $custom_field_id
291: * @param array<string, mixed> $data
292: *
293: * @return int
294: */
295: public function addValue(int $custom_field_id, array $data): int {
296: if ($data['custom_field_value_id']) {
297: $this->db->query("INSERT INTO `" . DB_PREFIX . "custom_field_value` SET `custom_field_value_id` = '" . (int)$data['custom_field_value_id'] . "', `custom_field_id` = '" . (int)$custom_field_id . "', `sort_order` = '" . (int)$data['sort_order'] . "'");
298: } else {
299: $this->db->query("INSERT INTO `" . DB_PREFIX . "custom_field_value` SET `custom_field_id` = '" . (int)$custom_field_id . "', `sort_order` = '" . (int)$data['sort_order'] . "'");
300: }
301:
302: $custom_field_value_id = $this->db->getLastId();
303:
304: foreach ($data['custom_field_value_description'] as $language_id => $custom_field_value_description) {
305: $this->addValueDescription($custom_field_value_id, $custom_field_id, $language_id, $custom_field_value_description);
306: }
307:
308: return $custom_field_value_id;
309: }
310:
311: /**
312: * Delete Values
313: *
314: * @param int $custom_field_id
315: *
316: * @return void
317: */
318: public function deleteValues(int $custom_field_id): void {
319: $this->db->query("DELETE FROM `" . DB_PREFIX . "custom_field_value` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
320:
321: $this->deleteValueDescriptions($custom_field_id);
322: }
323:
324: /**
325: * Get Value
326: *
327: * @param int $custom_field_value_id
328: *
329: * @return array<string, mixed>
330: */
331: public function getValue(int $custom_field_value_id): array {
332: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_value` `cfv` LEFT JOIN `" . DB_PREFIX . "custom_field_value_description` `cfvd` ON (`cfv`.`custom_field_value_id` = `cfvd`.`custom_field_value_id`) WHERE `cfv`.`custom_field_value_id` = '" . (int)$custom_field_value_id . "' AND `cfvd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'");
333:
334: return $query->row;
335: }
336:
337: /**
338: * Get Values
339: *
340: * @param int $custom_field_id
341: *
342: * @return array<int, array<string, mixed>>
343: */
344: public function getValues(int $custom_field_id): array {
345: $custom_field_value_data = [];
346:
347: $custom_field_value_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_value` `cfv` LEFT JOIN `" . DB_PREFIX . "custom_field_value_description` `cfvd` ON (`cfv`.`custom_field_value_id` = `cfvd`.`custom_field_value_id`) WHERE `cfv`.`custom_field_id` = '" . (int)$custom_field_id . "' AND `cfvd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `cfv`.`sort_order` ASC");
348:
349: foreach ($custom_field_value_query->rows as $custom_field_value) {
350: $custom_field_value_data[$custom_field_value['custom_field_value_id']] = [
351: 'custom_field_value_id' => $custom_field_value['custom_field_value_id'],
352: 'name' => $custom_field_value['name']
353: ];
354: }
355:
356: return $custom_field_value_data;
357: }
358:
359: /**
360: * Add Value Description
361: *
362: * @param int $custom_field_value_id
363: * @param int $custom_field_id
364: * @param int $language_id
365: * @param array<string, mixed> $custom_field_value_description
366: *
367: * @return void
368: */
369: public function addValueDescription(int $custom_field_value_id, int $custom_field_id, int $language_id, array $custom_field_value_description): void {
370: $this->db->query("INSERT INTO `" . DB_PREFIX . "custom_field_value_description` SET `custom_field_value_id` = '" . (int)$custom_field_value_id . "', `language_id` = '" . (int)$language_id . "', `custom_field_id` = '" . (int)$custom_field_id . "', `name` = '" . $this->db->escape($custom_field_value_description['name']) . "'");
371: }
372:
373: /**
374: * Delete Value Descriptions
375: *
376: * @param int $custom_field_id
377: *
378: * @return void
379: */
380: public function deleteValueDescriptions(int $custom_field_id): void {
381: $this->db->query("DELETE FROM `" . DB_PREFIX . "custom_field_value_description` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
382: }
383:
384: /**
385: * Delete Value Descriptions By Language ID
386: *
387: * @param int $language_id
388: *
389: * @return void
390: */
391: public function deleteValueDescriptionsByLanguageId(int $language_id): void {
392: $this->db->query("DELETE FROM `" . DB_PREFIX . "custom_field_value_description` WHERE `language_id` = '" . (int)$language_id . "'");
393: }
394:
395: /**
396: * Get Value Descriptions
397: *
398: * @param int $custom_field_id
399: *
400: * @return array<int, array<string, mixed>>
401: */
402: public function getValueDescriptions(int $custom_field_id): array {
403: $custom_field_value_data = [];
404:
405: $custom_field_value_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_value` WHERE `custom_field_id` = '" . (int)$custom_field_id . "'");
406:
407: foreach ($custom_field_value_query->rows as $custom_field_value) {
408: $custom_field_value_description_data = [];
409:
410: $custom_field_value_description_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_value_description` WHERE `custom_field_value_id` = '" . (int)$custom_field_value['custom_field_value_id'] . "'");
411:
412: foreach ($custom_field_value_description_query->rows as $custom_field_value_description) {
413: $custom_field_value_description_data[$custom_field_value_description['language_id']] = ['name' => $custom_field_value_description['name']];
414: }
415:
416: $custom_field_value_data[] = [
417: 'custom_field_value_id' => $custom_field_value['custom_field_value_id'],
418: 'custom_field_value_description' => $custom_field_value_description_data,
419: 'sort_order' => $custom_field_value['sort_order']
420: ];
421: }
422:
423: return $custom_field_value_data;
424: }
425:
426: /**
427: * Get Value Descriptions By Language ID
428: *
429: * @param int $language_id
430: *
431: * @return array<int, array<string, string>>
432: */
433: public function getValueDescriptionsByLanguageId(int $language_id): array {
434: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "custom_field_value_description` WHERE `language_id` = '" . (int)$language_id . "'");
435:
436: return $query->rows;
437: }
438: }
439: