1: <?php
2: namespace Opencart\Admin\Model\Catalog;
3: /**
4: * Class Review
5: *
6: * @package Opencart\Admin\Model\Catalog
7: */
8: class Review extends \Opencart\System\Engine\Model {
9: /**
10: * Add Review
11: *
12: * @param array<string, mixed> $data
13: *
14: * @return int
15: */
16: public function addReview(array $data): int {
17: $this->db->query("INSERT INTO `" . DB_PREFIX . "review` SET `author` = '" . $this->db->escape((string)$data['author']) . "', `product_id` = '" . (int)$data['product_id'] . "', `text` = '" . $this->db->escape(strip_tags((string)$data['text'])) . "', `rating` = '" . (int)$data['rating'] . "', `status` = '" . (bool)($data['status'] ?? 0) . "', `date_added` = '" . $this->db->escape((string)$data['date_added']) . "'");
18:
19: $review_id = $this->db->getLastId();
20:
21: // Update product rating
22: $this->load->model('catalog/product');
23:
24: $this->model_catalog_product->editRating($data['product_id'], $this->model_catalog_review->getRating($data['product_id']));
25:
26: $this->cache->delete('product');
27:
28: return $review_id;
29: }
30:
31: /**
32: * Edit Review
33: *
34: * @param int $review_id
35: * @param array<string, mixed> $data
36: *
37: * @return void
38: */
39: public function editReview(int $review_id, array $data): void {
40: $this->db->query("UPDATE `" . DB_PREFIX . "review` SET `author` = '" . $this->db->escape((string)$data['author']) . "', `product_id` = '" . (int)$data['product_id'] . "', `text` = '" . $this->db->escape(strip_tags((string)$data['text'])) . "', `rating` = '" . (int)$data['rating'] . "', `status` = '" . (bool)($data['status'] ?? 0) . "', `date_added` = '" . $this->db->escape((string)$data['date_added']) . "', `date_modified` = NOW() WHERE `review_id` = '" . (int)$review_id . "'");
41:
42: // Update product rating
43: $this->load->model('catalog/product');
44:
45: $this->model_catalog_product->editRating($data['product_id'], $this->model_catalog_review->getRating($data['product_id']));
46:
47: $this->cache->delete('product');
48: }
49:
50: /**
51: * Delete Review
52: *
53: * @param int $review_id
54: *
55: * @return void
56: */
57: public function deleteReview(int $review_id): void {
58: $review_info = $this->getReview($review_id);
59:
60: if ($review_info) {
61: $this->db->query("DELETE FROM `" . DB_PREFIX . "review` WHERE `review_id` = '" . (int)$review_info['review_id'] . "'");
62:
63: // Update product rating
64: $this->load->model('catalog/product');
65:
66: $this->model_catalog_product->editRating($review_info['product_id'], $this->model_catalog_review->getRating($review_info['product_id']));
67:
68: $this->cache->delete('product');
69: }
70: }
71:
72: /**
73: * Delete Reviews By Product ID
74: *
75: * @param int $product_id
76: *
77: * @return void
78: */
79: public function deleteReviewsByProductId(int $product_id): void {
80: $this->db->query("DELETE FROM `" . DB_PREFIX . "review` WHERE `product_id` = '" . (int)$product_id . "'");
81:
82: $this->cache->delete('product');
83: }
84:
85: /**
86: * Get Review
87: *
88: * @param int $review_id
89: *
90: * @return array<string, mixed>
91: */
92: public function getReview(int $review_id): array {
93: $query = $this->db->query("SELECT DISTINCT *, (SELECT pd.`name` FROM `" . DB_PREFIX . "product_description` pd WHERE pd.`product_id` = r.`product_id` AND pd.`language_id` = '" . (int)$this->config->get('config_language_id') . "') AS product FROM `" . DB_PREFIX . "review` r WHERE r.`review_id` = '" . (int)$review_id . "'");
94:
95: return $query->row;
96: }
97:
98: /**
99: * Get Rating
100: *
101: * @param int $product_id
102: *
103: * @return int
104: */
105: public function getRating(int $product_id): int {
106: $query = $this->db->query("SELECT AVG(`rating`) AS `total` FROM `" . DB_PREFIX . "review` WHERE `product_id` = '" . (int)$product_id . "' AND `status` = '1'");
107:
108: if ($query->num_rows) {
109: return (int)$query->row['total'];
110: } else {
111: return 0;
112: }
113: }
114:
115: /**
116: * Get Reviews
117: *
118: * @param array<string, mixed> $data
119: *
120: * @return array<int, array<string, mixed>>
121: */
122: public function getReviews(array $data = []): array {
123: $sql = "SELECT `r`.`review_id`, `pd`.`name`, `r`.`author`, `r`.`rating`, `r`.`status`, `r`.`date_added` FROM `" . DB_PREFIX . "review` `r` LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`r`.`product_id` = `pd`.`product_id`) WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
124:
125: if (!empty($data['filter_product'])) {
126: $sql .= " AND LCASE(`pd`.`name`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_product']) . '%') . "'";
127: }
128:
129: if (!empty($data['filter_author'])) {
130: $sql .= " AND LCASE(`r`.`author`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_author']) . '%') . "'";
131: }
132:
133: if (isset($data['filter_status']) && $data['filter_status'] !== '') {
134: $sql .= " AND `r`.`status` = '" . (bool)$data['filter_status'] . "'";
135: }
136:
137: if (!empty($data['filter_date_from'])) {
138: $sql .= " AND DATE(`r`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
139: }
140:
141: if (!empty($data['filter_date_to'])) {
142: $sql .= " AND DATE(`r`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
143: }
144:
145: $sort_data = [
146: 'pd.name',
147: 'r.author',
148: 'r.rating',
149: 'r.status',
150: 'r.date_added'
151: ];
152:
153: if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
154: $sql .= " ORDER BY " . $data['sort'];
155: } else {
156: $sql .= " ORDER BY `r`.`date_added`";
157: }
158:
159: if (isset($data['order']) && ($data['order'] == 'DESC')) {
160: $sql .= " DESC";
161: } else {
162: $sql .= " ASC";
163: }
164:
165: if (isset($data['start']) || isset($data['limit'])) {
166: if ($data['start'] < 0) {
167: $data['start'] = 0;
168: }
169:
170: if ($data['limit'] < 1) {
171: $data['limit'] = 20;
172: }
173:
174: $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
175: }
176:
177: $query = $this->db->query($sql);
178:
179: return $query->rows;
180: }
181:
182: /**
183: * Get Total Reviews
184: *
185: * @param array<string, mixed> $data
186: *
187: * @return int
188: */
189: public function getTotalReviews(array $data = []): int {
190: $sql = "SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "review` `r` LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`r`.`product_id` = `pd`.`product_id`) WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
191:
192: if (!empty($data['filter_product'])) {
193: $sql .= " AND LCASE(`pd`.`name`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_product']) . '%') . "'";
194: }
195:
196: if (!empty($data['filter_author'])) {
197: $sql .= " AND LCASE(`r`.`author`) LIKE '" . $this->db->escape(oc_strtolower($data['filter_author']) . '%') . "'";
198: }
199:
200: if (isset($data['filter_status']) && $data['filter_status'] !== '') {
201: $sql .= " AND `r`.`status` = '" . (bool)$data['filter_status'] . "'";
202: }
203:
204: if (!empty($data['filter_date_from'])) {
205: $sql .= " AND DATE(`r`.`date_added`) >= DATE('" . $this->db->escape((string)$data['filter_date_from']) . "')";
206: }
207:
208: if (!empty($data['filter_date_to'])) {
209: $sql .= " AND DATE(`r`.`date_added`) <= DATE('" . $this->db->escape((string)$data['filter_date_to']) . "')";
210: }
211:
212: $query = $this->db->query($sql);
213:
214: return (int)$query->row['total'];
215: }
216:
217: /**
218: * Get Total Reviews Awaiting Approval
219: *
220: * @return int
221: */
222: public function getTotalReviewsAwaitingApproval(): int {
223: $query = $this->db->query("SELECT COUNT(*) AS `total` FROM `" . DB_PREFIX . "review` WHERE `status` = '0'");
224:
225: return (int)$query->row['total'];
226: }
227: }
228: