1: <?php
2: namespace Opencart\Catalog\Model\Cms;
3: /**
4: * Class Article
5: *
6: * @package Opencart\Catalog\Model\Cms
7: */
8: class Article extends \Opencart\System\Engine\Model {
9: /**
10: * Get Article
11: *
12: * @param int $article_id
13: *
14: * @return array<string, mixed>
15: */
16: public function getArticle(int $article_id): array {
17: $query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "article` `a` LEFT JOIN `" . DB_PREFIX . "article_description` `ad` ON (`a`.`article_id` = `ad`.`article_id`) LEFT JOIN `" . DB_PREFIX . "article_to_store` `a2s` ON (`a`.`article_id` = `a2s`.`article_id`) WHERE `a`.`article_id` = '" . (int)$article_id . "' AND `ad`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND `a2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `a`.`status` = '1'");
18:
19: return $query->row;
20: }
21:
22: /**
23: * Get Articles
24: *
25: * @param array<string, mixed> $data
26: *
27: * @return array<int, array<string, mixed>>
28: */
29: public function getArticles(array $data = []): array {
30: $sql = "SELECT * FROM `" . DB_PREFIX . "article` `a` LEFT JOIN `" . DB_PREFIX . "article_description` `ad` ON (`a`.`article_id` = `ad`.`article_id`) LEFT JOIN `" . DB_PREFIX . "article_to_store` `a2s` ON (`a`.`article_id` = `a2s`.`article_id`) WHERE `ad`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND `a2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `a`.`status` = '1'";
31:
32: if (!empty($data['filter_search'])) {
33: $sql .= " AND (";
34:
35: $implode = [];
36:
37: $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_search'])));
38: $words = array_filter($words);
39:
40: foreach ($words as $word) {
41: $implode[] = "`ad`.`name` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
42: }
43:
44: if ($implode) {
45: $sql .= " (" . implode(" OR ", $implode) . ")";
46: }
47:
48: $sql .= " OR `ad`.`description` LIKE '" . $this->db->escape('%' . (string)$data['filter_search'] . '%') . "'";
49:
50: $implode = [];
51:
52: foreach ($words as $word) {
53: $implode[] = "`ad`.`tag` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
54: }
55:
56: if ($implode) {
57: $sql .= " OR (" . implode(" OR ", $implode) . ")";
58: }
59:
60: $sql .= ")";
61: }
62:
63: if (!empty($data['filter_topic_id'])) {
64: $sql .= " AND `a`.`topic_id` = '" . (int)$data['filter_topic_id'] . "'";
65: }
66:
67: if (!empty($data['filter_author'])) {
68: $sql .= " AND `a`.`author` = '" . $this->db->escape($data['filter_author']) . "'";
69: }
70:
71: $sort_data = [
72: 'rating',
73: 'date_added'
74: ];
75:
76: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
77: $sql .= " ORDER BY " . $data['sort'];
78: } else {
79: $sql .= " ORDER BY `date_added`";
80: }
81:
82: if (isset($data['order']) && ($data['order'] == 'DESC')) {
83: $sql .= " DESC";
84: } else {
85: $sql .= " ASC";
86: }
87:
88: if (isset($data['start']) || isset($data['limit'])) {
89: if ($data['start'] < 0) {
90: $data['start'] = 0;
91: }
92:
93: if ($data['limit'] < 1) {
94: $data['limit'] = 20;
95: }
96:
97: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
98: }
99:
100: $key = md5($sql);
101:
102: $article_data = $this->cache->get('article.' . $key);
103:
104: if (!$article_data) {
105: $query = $this->db->query($sql);
106:
107: $article_data = $query->rows;
108:
109: $this->cache->set('article.' . $key, $article_data);
110: }
111:
112: return $article_data;
113: }
114:
115: /**
116: * Edit Rating
117: *
118: * @param int $article_id
119: * @param int $rating
120: */
121: public function editRating(int $article_id, int $rating): void {
122: $this->db->query("UPDATE `" . DB_PREFIX . "article` SET `rating` = '" . (int)$rating . "' WHERE `article_id` = '" . (int)$article_id . "'");
123: }
124:
125: /**
126: * Get Total Articles
127: *
128: * @param array<string, mixed> $data
129: *
130: * @return int
131: */
132: public function getTotalArticles(array $data = []): int {
133: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "article` `a` LEFT JOIN `" . DB_PREFIX . "article_description` `ad` ON (`a`.`article_id` = `ad`.`article_id`) LEFT JOIN `" . DB_PREFIX . "article_to_store` `a2s` ON (`a`.`article_id` = `a2s`.`article_id`) WHERE `ad`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND `a2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `a`.`status` = '1'";
134:
135: if (!empty($data['filter_search'])) {
136: $sql .= " AND (";
137:
138: $implode = [];
139:
140: $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_search'])));
141: $words = array_filter($words);
142:
143: foreach ($words as $word) {
144: $implode[] = "`ad`.`name` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
145: }
146:
147: if ($implode) {
148: $sql .= " (" . implode(" OR ", $implode) . ")";
149: }
150:
151: $sql .= " OR `ad`.`description` LIKE '" . $this->db->escape('%' . (string)$data['filter_search'] . '%') . "'";
152:
153: $implode = [];
154:
155: foreach ($words as $word) {
156: $implode[] = "`ad`.`tag` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
157: }
158:
159: if ($implode) {
160: $sql .= " OR (" . implode(" OR ", $implode) . ")";
161: }
162:
163: $sql .= ")";
164: }
165:
166: if (!empty($data['filter_topic_id'])) {
167: $sql .= " AND `a`.`topic_id` = '" . (int)$data['filter_topic_id'] . "'";
168: }
169:
170: if (!empty($data['filter_author'])) {
171: $sql .= " AND `a`.`author` = '" . $this->db->escape($data['filter_author']) . "'";
172: }
173:
174: $query = $this->db->query($sql);
175:
176: return (int)$query->row['total'];
177: }
178:
179: /**
180: * Get Layout ID
181: *
182: * @param int $article_id
183: *
184: * @return int
185: */
186: public function getLayoutId(int $article_id): int {
187: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "article_to_layout` WHERE `article_id` = '" . (int)$article_id . "' AND `store_id` = '" . (int)$this->config->get('config_store_id') . "'");
188:
189: if ($query->num_rows) {
190: return (int)$query->row['layout_id'];
191: } else {
192: return 0;
193: }
194: }
195:
196: /**
197: * Add Comment
198: *
199: * @param int $article_id
200: * @param array<string, mixed> $data
201: *
202: * @return int
203: */
204: public function addComment(int $article_id, array $data): int {
205: $this->db->query("INSERT INTO `" . DB_PREFIX . "article_comment` SET `article_id` = '" . (int)$article_id . "', `parent_id` = '" . (int)$data['parent_id'] . "', `customer_id` = '" . (int)$this->customer->getId() . "', `author` = '" . $this->db->escape($data['author']) . "', `comment` = '" . $this->db->escape($data['comment']) . "', `ip` = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', `status` = '" . (bool)!empty($data['status']) . "', `date_added` = NOW()");
206:
207: $this->cache->delete('comment');
208:
209: return $this->db->getLastId();
210: }
211:
212: /**
213: * Edit Comment Rating
214: *
215: * @param int $article_id
216: * @param int $article_comment_id
217: * @param int $rating
218: */
219: public function editCommentRating(int $article_id, int $article_comment_id, int $rating): void {
220: $this->db->query("UPDATE `" . DB_PREFIX . "article_comment` SET `rating` = '" . (int)$rating . "' WHERE `article_comment_id` = '" . (int)$article_comment_id . "' AND `article_id` = '" . (int)$article_id . "'");
221: }
222:
223: /**
224: * Get Comment
225: *
226: * @param int $article_comment_id
227: *
228: * @return array<string, mixed>
229: */
230: public function getComment(int $article_comment_id): array {
231: $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "article_comment` WHERE `article_comment_id` = '" . (int)$article_comment_id . "' AND `status` = '1'");
232:
233: return $query->row;
234: }
235:
236: /**
237: * Get Comments
238: *
239: * @param int $article_id
240: * @param array<string, mixed> $data
241: *
242: * @return array<int, array<string, mixed>>
243: */
244: public function getComments(int $article_id, array $data = []): array {
245: $sql = "SELECT * FROM `" . DB_PREFIX . "article_comment` WHERE `article_id` = '" . (int)$article_id . "'";
246:
247: if (!empty($data['customer_id'])) {
248: $sql .= " AND `customer_id` = '" . (int)$data['customer_id'] . "'";
249: }
250:
251: if (isset($data['parent_id'])) {
252: $sql .= " AND `parent_id` = '" . (int)$data['parent_id'] . "'";
253: }
254:
255: $sql .= " AND `status` = '1'";
256:
257: $sort_data = [
258: 'rating',
259: 'date_added'
260: ];
261:
262: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
263: $sql .= " ORDER BY " . $data['sort'];
264: } else {
265: $sql .= " ORDER BY `date_added`";
266: }
267:
268: if (isset($data['order']) && ($data['order'] == 'DESC')) {
269: $sql .= " DESC";
270: } else {
271: $sql .= " ASC";
272: }
273:
274: if (isset($data['start']) || isset($data['limit'])) {
275: if ($data['start'] < 0) {
276: $data['start'] = 0;
277: }
278:
279: if ($data['limit'] < 1) {
280: $data['limit'] = 20;
281: }
282:
283: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
284: }
285:
286: $key = md5($sql);
287:
288: $comment_data = $this->cache->get('comment.' . $key);
289:
290: if (!$comment_data) {
291: $query = $this->db->query($sql);
292:
293: $comment_data = $query->rows;
294:
295: $this->cache->set('comment.' . $key, $comment_data);
296: }
297:
298: return $comment_data;
299: }
300:
301: /**
302: * Get Total Comments
303: *
304: * @param int $article_id
305: * @param array<string, mixed> $data
306: *
307: * @return int
308: */
309: public function getTotalComments(int $article_id, array $data = []): int {
310: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "article_comment` WHERE `article_id` = '" . (int)$article_id . "'";
311:
312: if (!empty($data['customer_id'])) {
313: $sql .= " AND `customer_id` = '" . (int)$data['customer_id'] . "'";
314: }
315:
316: if (isset($data['parent_id'])) {
317: $sql .= " AND `parent_id` = '" . (int)$data['parent_id'] . "'";
318: }
319:
320: $sql .= " AND `status` = '1'";
321:
322: $query = $this->db->query($sql);
323:
324: return (int)$query->row['total'];
325: }
326:
327: /**
328: * Add Rating
329: *
330: * @param int $article_id
331: * @param int $article_comment_id
332: * @param bool $rating
333: *
334: * @return void
335: */
336: public function addRating(int $article_id, int $article_comment_id, bool $rating): void {
337: $this->db->query("INSERT INTO `" . DB_PREFIX . "article_rating` SET `article_comment_id` = '" . (int)$article_comment_id . "', `article_id` = '" . (int)$article_id . "', `store_id` = '" . (int)$this->config->get('config_store_id') . "', `customer_id` = '" . (int)$this->customer->getId() . "', `rating` = '" . (bool)$rating . "', `ip` = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', `date_added` = NOW()");
338: }
339:
340: /**
341: * Delete Ratings
342: *
343: * @param int $article_id
344: * @param int $article_comment_id
345: *
346: * @return void
347: */
348: public function deleteRatings(int $article_id, int $article_comment_id): void {
349: $this->db->query("DELETE FROM `" . DB_PREFIX . "article_rating` WHERE `article_comment_id` = '" . (int)$article_comment_id . "' AND `article_id` = '" . (int)$article_id . "' AND `customer_id` = '" . (int)$this->customer->getId() . "'");
350: }
351:
352: /**
353: * Get Ratings
354: *
355: * @param int $article_id
356: * @param int $article_comment_id
357: *
358: * @return array<int, array<string, mixed>>
359: */
360: public function getRatings(int $article_id, int $article_comment_id = 0): array {
361: $sql = "SELECT rating, COUNT(*) AS total FROM `" . DB_PREFIX . "article_rating` WHERE `article_id` = '" . (int)$article_id . "'";
362:
363: if ($article_comment_id) {
364: $sql .= " AND `article_comment_id` = '" . (int)$article_comment_id . "'";
365: }
366:
367: $sql .= " GROUP BY rating";
368:
369: $query = $this->db->query($sql);
370:
371: return $query->rows;
372: }
373: }
374: