1: | <?php
|
2: | namespace Opencart\Catalog\Model\Catalog;
|
3: | |
4: | |
5: | |
6: | |
7: |
|
8: | class Product extends \Opencart\System\Engine\Model {
|
9: | |
10: | |
11: |
|
12: | protected array $statement = [];
|
13: |
|
14: | |
15: | |
16: | |
17: | |
18: |
|
19: | public function __construct(\Opencart\System\Engine\Registry $registry) {
|
20: | $this->registry = $registry;
|
21: |
|
22: |
|
23: | $this->statement['discount'] = "(SELECT `pd2`.`price` FROM `" . DB_PREFIX . "product_discount` `pd2` WHERE `pd2`.`product_id` = `p`.`product_id` AND `pd2`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "'AND `pd2`.`quantity` = '1' AND ((`pd2`.`date_start` = '0000-00-00' OR `pd2`.`date_start` < NOW()) AND (`pd2`.`date_end` = '0000-00-00' OR `pd2`.`date_end` > NOW())) ORDER BY `pd2`.`priority` ASC, `pd2`.`price` ASC LIMIT 1) AS `discount`";
|
24: | $this->statement['special'] = "(SELECT `ps`.`price` FROM `" . DB_PREFIX . "product_special` `ps` WHERE `ps`.`product_id` = `p`.`product_id` AND `ps`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((`ps`.`date_start` = '0000-00-00' OR `ps`.`date_start` < NOW()) AND (`ps`.`date_end` = '0000-00-00' OR `ps`.`date_end` > NOW())) ORDER BY `ps`.`priority` ASC, `ps`.`price` ASC LIMIT 1) AS `special`";
|
25: | $this->statement['reward'] = "(SELECT `pr`.`points` FROM `" . DB_PREFIX . "product_reward` `pr` WHERE `pr`.`product_id` = `p`.`product_id` AND `pr`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "') AS `reward`";
|
26: | $this->statement['review'] = "(SELECT COUNT(*) FROM `" . DB_PREFIX . "review` `r` WHERE `r`.`product_id` = `p`.`product_id` AND `r`.`status` = '1' GROUP BY `r`.`product_id`) AS `reviews`";
|
27: | }
|
28: |
|
29: | |
30: | |
31: | |
32: | |
33: | |
34: | |
35: |
|
36: | public function getProduct(int $product_id): array {
|
37: | $query = $this->db->query("SELECT DISTINCT *, `pd`.`name`, `p`.`image`, " . $this->statement['discount'] . ", " . $this->statement['special'] . ", " . $this->statement['reward'] . ", " . $this->statement['review'] . " FROM `" . DB_PREFIX . "product_to_store` `p2s` LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2s`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW()) LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`p`.`product_id` = `pd`.`product_id`) WHERE `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `p2s`.`product_id` = '" . (int)$product_id . "' AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'");
|
38: |
|
39: | if ($query->num_rows) {
|
40: | $product_data = $query->row;
|
41: |
|
42: | $product_data['variant'] = (array)json_decode($query->row['variant'], true);
|
43: | $product_data['override'] = (array)json_decode($query->row['override'], true);
|
44: | $product_data['price'] = (float)($query->row['discount'] ?: $query->row['price']);
|
45: | $product_data['rating'] = (int)$query->row['rating'];
|
46: | $product_data['reviews'] = (int)$query->row['reviews'] ? $query->row['reviews'] : 0;
|
47: |
|
48: | return $product_data;
|
49: | } else {
|
50: | return [];
|
51: | }
|
52: | }
|
53: |
|
54: | |
55: | |
56: | |
57: | |
58: | |
59: | |
60: |
|
61: | public function getProducts(array $data = []): array {
|
62: | $sql = "SELECT DISTINCT *, `pd`.`name`, `p`.`image`, " . $this->statement['discount'] . ", " . $this->statement['special'] . ", " . $this->statement['reward'] . ", " . $this->statement['review'];
|
63: |
|
64: | if (!empty($data['filter_category_id'])) {
|
65: | $sql .= " FROM `" . DB_PREFIX . "category_to_store` `c2s`";
|
66: |
|
67: | if (!empty($data['filter_sub_category'])) {
|
68: | $sql .= " LEFT JOIN `" . DB_PREFIX . "category_path` `cp` ON (`cp`.`category_id` = `c2s`.`category_id` AND `c2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "') LEFT JOIN `" . DB_PREFIX . "product_to_category` `p2c` ON (`p2c`.`category_id` = `cp`.`category_id`)";
|
69: | } else {
|
70: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_to_category` `p2c` ON (`p2c`.`category_id` = `c2s`.`category_id` AND `c2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "')";
|
71: | }
|
72: |
|
73: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_to_store` `p2s` ON (`p2s`.`product_id` = `p2c`.`product_id` AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "')";
|
74: |
|
75: | if (!empty($data['filter_filter'])) {
|
76: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_filter` `pf` ON (`pf`.`product_id` = `p2s`.`product_id`) LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `pf`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW())";
|
77: | } else {
|
78: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2s`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW())";
|
79: | }
|
80: | } else {
|
81: | $sql .= " FROM `" . DB_PREFIX . "product_to_store` `p2s` LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2s`.`product_id` AND `p`.`status` = '1' AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `p`.`date_available` <= NOW())";
|
82: | }
|
83: |
|
84: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`p`.`product_id` = `pd`.`product_id`) WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
|
85: |
|
86: | if (!empty($data['filter_category_id'])) {
|
87: | if (!empty($data['filter_sub_category'])) {
|
88: | $sql .= " AND `cp`.`path_id` = '" . (int)$data['filter_category_id'] . "'";
|
89: | } else {
|
90: | $sql .= " AND `p2c`.`category_id` = '" . (int)$data['filter_category_id'] . "'";
|
91: | }
|
92: |
|
93: | if (!empty($data['filter_filter'])) {
|
94: | $implode = [];
|
95: |
|
96: | $filters = explode(',', $data['filter_filter']);
|
97: |
|
98: | foreach ($filters as $filter_id) {
|
99: | $implode[] = (int)$filter_id;
|
100: | }
|
101: |
|
102: | $sql .= " AND `pf`.`filter_id` IN (" . implode(',', $implode) . ")";
|
103: | }
|
104: | }
|
105: |
|
106: | if (!empty($data['filter_search']) || !empty($data['filter_tag'])) {
|
107: | $sql .= " AND (";
|
108: |
|
109: | if (!empty($data['filter_search'])) {
|
110: | $implode = [];
|
111: |
|
112: | $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_search'])));
|
113: | $words = array_filter($words);
|
114: |
|
115: | foreach ($words as $word) {
|
116: | $implode[] = "`pd`.`name` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
|
117: | }
|
118: |
|
119: | if ($implode) {
|
120: | $sql .= " (" . implode(" OR ", $implode) . ")";
|
121: | }
|
122: |
|
123: | if (!empty($data['filter_description'])) {
|
124: | $sql .= " OR `pd`.`description` LIKE '" . $this->db->escape('%' . (string)$data['filter_search'] . '%') . "'";
|
125: | }
|
126: | }
|
127: |
|
128: | if (!empty($data['filter_search']) && !empty($data['filter_tag'])) {
|
129: | $sql .= " OR ";
|
130: | }
|
131: |
|
132: | if (!empty($data['filter_tag'])) {
|
133: | $implode = [];
|
134: |
|
135: | $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));
|
136: | $words = array_filter($words);
|
137: |
|
138: | foreach ($words as $word) {
|
139: | $implode[] = "`pd`.`tag` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
|
140: | }
|
141: |
|
142: | if ($implode) {
|
143: | $sql .= " (" . implode(" OR ", $implode) . ")";
|
144: | }
|
145: | }
|
146: |
|
147: | if (!empty($data['filter_search'])) {
|
148: | $sql .= " OR LCASE(`p`.`model`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
149: | $sql .= " OR LCASE(`p`.`sku`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
150: | $sql .= " OR LCASE(`p`.`upc`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
151: | $sql .= " OR LCASE(`p`.`ean`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
152: | $sql .= " OR LCASE(`p`.`jan`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
153: | $sql .= " OR LCASE(`p`.`isbn`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
154: | $sql .= " OR LCASE(`p`.`mpn`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
155: | }
|
156: |
|
157: | $sql .= ")";
|
158: | }
|
159: |
|
160: | if (!empty($data['filter_manufacturer_id'])) {
|
161: | $sql .= " AND `p`.`manufacturer_id` = '" . (int)$data['filter_manufacturer_id'] . "'";
|
162: | }
|
163: |
|
164: | $sort_data = [
|
165: | 'pd.name',
|
166: | 'p.model',
|
167: | 'p.quantity',
|
168: | 'p.price',
|
169: | 'rating',
|
170: | 'p.sort_order',
|
171: | 'p.date_added'
|
172: | ];
|
173: |
|
174: | if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
|
175: | if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
|
176: | $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
|
177: | } elseif ($data['sort'] == 'p.price') {
|
178: | $sql .= " ORDER BY (CASE WHEN `special` IS NOT NULL THEN `special` WHEN `discount` IS NOT NULL THEN `discount` ELSE `p`.`price` END)";
|
179: | } else {
|
180: | $sql .= " ORDER BY " . $data['sort'];
|
181: | }
|
182: | } else {
|
183: | $sql .= " ORDER BY `p`.`sort_order`";
|
184: | }
|
185: |
|
186: | if (isset($data['order']) && ($data['order'] == 'DESC')) {
|
187: | $sql .= " DESC, LCASE(`pd`.`name`) DESC";
|
188: | } else {
|
189: | $sql .= " ASC, LCASE(`pd`.`name`) ASC";
|
190: | }
|
191: |
|
192: | if (isset($data['start']) || isset($data['limit'])) {
|
193: | if ($data['start'] < 0) {
|
194: | $data['start'] = 0;
|
195: | }
|
196: |
|
197: | if ($data['limit'] < 1) {
|
198: | $data['limit'] = 20;
|
199: | }
|
200: |
|
201: | $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
|
202: | }
|
203: |
|
204: | $key = md5($sql);
|
205: |
|
206: | $product_data = $this->cache->get('product.' . $key);
|
207: |
|
208: | if (!$product_data) {
|
209: | $query = $this->db->query($sql);
|
210: |
|
211: | $product_data = $query->rows;
|
212: |
|
213: | $this->cache->set('product.' . $key, $product_data);
|
214: | }
|
215: |
|
216: | return $product_data;
|
217: | }
|
218: |
|
219: | |
220: | |
221: | |
222: | |
223: | |
224: | |
225: |
|
226: | public function getCategories(int $product_id): array {
|
227: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_to_category` WHERE `product_id` = '" . (int)$product_id . "'");
|
228: |
|
229: | return $query->rows;
|
230: | }
|
231: |
|
232: | |
233: | |
234: | |
235: | |
236: | |
237: | |
238: | |
239: |
|
240: | public function getCategoriesByCategoryId(int $product_id, int $category_id): array {
|
241: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_to_category` WHERE `product_id` = '" . (int)$product_id . "' AND `category_id` = '" . (int)$category_id . "'");
|
242: |
|
243: | return $query->row;
|
244: | }
|
245: |
|
246: | |
247: | |
248: | |
249: | |
250: | |
251: | |
252: |
|
253: | public function getAttributes(int $product_id): array {
|
254: | $product_attribute_group_data = [];
|
255: |
|
256: | $product_attribute_group_query = $this->db->query("SELECT `ag`.`attribute_group_id`, `agd`.`name` FROM `" . DB_PREFIX . "product_attribute` `pa` LEFT JOIN `" . DB_PREFIX . "attribute` `a` ON (`pa`.`attribute_id` = `a`.`attribute_id`) LEFT JOIN `" . DB_PREFIX . "attribute_group` `ag` ON (`a`.`attribute_group_id` = `ag`.`attribute_group_id`) LEFT JOIN `" . DB_PREFIX . "attribute_group_description` `agd` ON (`ag`.`attribute_group_id` = `agd`.`attribute_group_id`) WHERE `pa`.`product_id` = '" . (int)$product_id . "' AND `agd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' GROUP BY `ag`.`attribute_group_id` ORDER BY `ag`.`sort_order`, `agd`.`name`");
|
257: |
|
258: | foreach ($product_attribute_group_query->rows as $product_attribute_group) {
|
259: | $product_attribute_data = [];
|
260: |
|
261: | $product_attribute_query = $this->db->query("SELECT `a`.`attribute_id`, `ad`.`name`, `pa`.`text` FROM `" . DB_PREFIX . "product_attribute` `pa` LEFT JOIN `" . DB_PREFIX . "attribute` `a` ON (`pa`.`attribute_id` = `a`.`attribute_id`) LEFT JOIN `" . DB_PREFIX . "attribute_description` `ad` ON (`a`.`attribute_id` = `ad`.`attribute_id`) WHERE `pa`.`product_id` = '" . (int)$product_id . "' AND `a`.`attribute_group_id` = '" . (int)$product_attribute_group['attribute_group_id'] . "' AND `ad`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND `pa`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `a`.`sort_order`, `ad`.`name`");
|
262: |
|
263: | foreach ($product_attribute_query->rows as $product_attribute) {
|
264: | $product_attribute_data[] = [
|
265: | 'attribute_id' => $product_attribute['attribute_id'],
|
266: | 'name' => $product_attribute['name'],
|
267: | 'text' => $product_attribute['text']
|
268: | ];
|
269: | }
|
270: |
|
271: | $product_attribute_group_data[] = [
|
272: | 'attribute_group_id' => $product_attribute_group['attribute_group_id'],
|
273: | 'name' => $product_attribute_group['name'],
|
274: | 'attribute' => $product_attribute_data
|
275: | ];
|
276: | }
|
277: |
|
278: | return $product_attribute_group_data;
|
279: | }
|
280: |
|
281: | |
282: | |
283: | |
284: | |
285: | |
286: | |
287: |
|
288: | public function getOptions(int $product_id): array {
|
289: | $product_option_data = [];
|
290: |
|
291: | $product_option_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_option` `po` LEFT JOIN `" . DB_PREFIX . "option` `o` ON (`po`.`option_id` = `o`.`option_id`) LEFT JOIN `" . DB_PREFIX . "option_description` `od` ON (`o`.`option_id` = `od`.`option_id`) WHERE `po`.`product_id` = '" . (int)$product_id . "' AND `od`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `o`.`sort_order`");
|
292: |
|
293: | foreach ($product_option_query->rows as $product_option) {
|
294: | $product_option_value_data = [];
|
295: |
|
296: | $product_option_value_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_option_value` `pov` LEFT JOIN `" . DB_PREFIX . "option_value` `ov` ON (`pov`.`option_value_id` = `ov`.`option_value_id`) LEFT JOIN `" . DB_PREFIX . "option_value_description` `ovd` ON (`ov`.`option_value_id` = `ovd`.`option_value_id`) WHERE `pov`.`product_id` = '" . (int)$product_id . "' AND `pov`.`product_option_id` = '" . (int)$product_option['product_option_id'] . "' AND `ovd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' ORDER BY `ov`.`sort_order`");
|
297: |
|
298: | foreach ($product_option_value_query->rows as $product_option_value) {
|
299: | $product_option_value_data[] = [
|
300: | 'product_option_value_id' => $product_option_value['product_option_value_id'],
|
301: | 'option_value_id' => $product_option_value['option_value_id'],
|
302: | 'name' => $product_option_value['name'],
|
303: | 'image' => $product_option_value['image'],
|
304: | 'quantity' => $product_option_value['quantity'],
|
305: | 'subtract' => $product_option_value['subtract'],
|
306: | 'price' => $product_option_value['price'],
|
307: | 'price_prefix' => $product_option_value['price_prefix'],
|
308: | 'weight' => $product_option_value['weight'],
|
309: | 'weight_prefix' => $product_option_value['weight_prefix']
|
310: | ];
|
311: | }
|
312: |
|
313: | $product_option_data[] = [
|
314: | 'product_option_id' => $product_option['product_option_id'],
|
315: | 'product_option_value' => $product_option_value_data,
|
316: | 'option_id' => $product_option['option_id'],
|
317: | 'name' => $product_option['name'],
|
318: | 'type' => $product_option['type'],
|
319: | 'value' => $product_option['value'],
|
320: | 'required' => $product_option['required']
|
321: | ];
|
322: | }
|
323: |
|
324: | return $product_option_data;
|
325: | }
|
326: |
|
327: | |
328: | |
329: | |
330: | |
331: | |
332: | |
333: |
|
334: | public function getDiscounts(int $product_id): array {
|
335: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_discount` WHERE `product_id` = '" . (int)$product_id . "' AND `customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND `quantity` > 1 AND ((`date_start` = '0000-00-00' OR `date_start` < NOW()) AND (`date_end` = '0000-00-00' OR `date_end` > NOW())) ORDER BY `quantity` ASC, `priority` ASC, `price` ASC");
|
336: |
|
337: | return $query->rows;
|
338: | }
|
339: |
|
340: | |
341: | |
342: | |
343: | |
344: | |
345: | |
346: |
|
347: | public function getImages(int $product_id): array {
|
348: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_image` WHERE `product_id` = '" . (int)$product_id . "' ORDER BY `sort_order` ASC");
|
349: |
|
350: | return $query->rows;
|
351: | }
|
352: |
|
353: | |
354: | |
355: | |
356: | |
357: | |
358: | |
359: | |
360: |
|
361: | public function getSubscription(int $product_id, int $subscription_plan_id): array {
|
362: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_subscription` `ps` LEFT JOIN `" . DB_PREFIX . "subscription_plan` `sp` ON (`ps`.`subscription_plan_id` = `sp`.`subscription_plan_id`) WHERE `ps`.`product_id` = '" . (int)$product_id . "' AND `ps`.`subscription_plan_id` = '" . (int)$subscription_plan_id . "' AND `ps`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND `sp`.`status` = '1'");
|
363: |
|
364: | return $query->row;
|
365: | }
|
366: |
|
367: | |
368: | |
369: | |
370: | |
371: | |
372: | |
373: |
|
374: | public function getSubscriptions(int $product_id): array {
|
375: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_subscription` `ps` LEFT JOIN `" . DB_PREFIX . "subscription_plan` `sp` ON (`ps`.`subscription_plan_id` = `sp`.`subscription_plan_id`) LEFT JOIN `" . DB_PREFIX . "subscription_plan_description` `spd` ON (`sp`.`subscription_plan_id` = `spd`.`subscription_plan_id`) WHERE `ps`.`product_id` = '" . (int)$product_id . "' AND `ps`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND `spd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND `sp`.`status` = '1' ORDER BY `sp`.`sort_order` ASC");
|
376: |
|
377: | return $query->rows;
|
378: | }
|
379: |
|
380: | |
381: | |
382: | |
383: | |
384: | |
385: | |
386: |
|
387: | public function getLayoutId(int $product_id): int {
|
388: | $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_to_layout` WHERE `product_id` = '" . (int)$product_id . "' AND `store_id` = '" . (int)$this->config->get('config_store_id') . "'");
|
389: |
|
390: | if ($query->num_rows) {
|
391: | return (int)$query->row['layout_id'];
|
392: | } else {
|
393: | return 0;
|
394: | }
|
395: | }
|
396: |
|
397: | |
398: | |
399: | |
400: | |
401: | |
402: | |
403: |
|
404: | public function getRelated(int $product_id): array {
|
405: | $sql = "SELECT DISTINCT *, `pd`.`name` AS `name`, `p`.`image`, " . $this->statement['discount'] . ", " . $this->statement['special'] . ", " . $this->statement['reward'] . ", " . $this->statement['review'] . " FROM `" . DB_PREFIX . "product_related` `pr` LEFT JOIN `" . DB_PREFIX . "product_to_store` `p2s` ON (`p2s`.`product_id` = `pr`.`product_id` AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "') LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `pr`.`related_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW()) LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`p`.`product_id` = `pd`.`product_id`) WHERE `pr`.`product_id` = '" . (int)$product_id . "' AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
|
406: |
|
407: | $key = md5($sql);
|
408: |
|
409: | $product_data = $this->cache->get('product.' . $key);
|
410: |
|
411: | if (!$product_data) {
|
412: | $query = $this->db->query($sql);
|
413: |
|
414: | $product_data = $query->rows;
|
415: |
|
416: | $this->cache->set('product.' . $key, $product_data);
|
417: | }
|
418: |
|
419: | return (array)$product_data;
|
420: | }
|
421: |
|
422: | |
423: | |
424: | |
425: | |
426: | |
427: | |
428: |
|
429: | public function getTotalProducts(array $data = []): int {
|
430: | $sql = "SELECT COUNT(DISTINCT `p`.`product_id`) AS `total`";
|
431: |
|
432: | if (!empty($data['filter_category_id'])) {
|
433: | $sql .= " FROM `" . DB_PREFIX . "category_to_store` `c2s`";
|
434: |
|
435: | if (!empty($data['filter_sub_category'])) {
|
436: | $sql .= " LEFT JOIN `" . DB_PREFIX . "category_path` `cp` ON (`cp`.`category_id` = `c2s`.`category_id` AND `c2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "') LEFT JOIN `" . DB_PREFIX . "product_to_category` `p2c` ON (`p2c`.`category_id` = `cp`.`category_id`)";
|
437: | } else {
|
438: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_to_category` `p2c` ON (`p2c`.`category_id` = `c2s`.`category_id`)";
|
439: | }
|
440: |
|
441: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_to_store` `p2s` ON (`p2s`.`product_id` = `p2c`.`product_id`)";
|
442: |
|
443: | if (!empty($data['filter_filter'])) {
|
444: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_filter` `pf` ON (`pf`.`product_id` = `p2s`.`product_id`) LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `pf`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW())";
|
445: | } else {
|
446: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2s`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW() AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "')";
|
447: | }
|
448: | } else {
|
449: | $sql .= " FROM `" . DB_PREFIX . "product_to_store` `p2s` LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2s`.`product_id` AND `p`.`status` = '1' AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `p`.`date_available` <= NOW())";
|
450: | }
|
451: |
|
452: | $sql .= " LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`p`.`product_id` = `pd`.`product_id`) WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
|
453: |
|
454: | if (!empty($data['filter_category_id'])) {
|
455: | if (!empty($data['filter_sub_category'])) {
|
456: | $sql .= " AND `cp`.`path_id` = '" . (int)$data['filter_category_id'] . "'";
|
457: | } else {
|
458: | $sql .= " AND `p2c`.`category_id` = '" . (int)$data['filter_category_id'] . "'";
|
459: | }
|
460: |
|
461: | if (!empty($data['filter_filter'])) {
|
462: | $implode = [];
|
463: |
|
464: | $filters = explode(',', $data['filter_filter']);
|
465: |
|
466: | foreach ($filters as $filter_id) {
|
467: | $implode[] = (int)$filter_id;
|
468: | }
|
469: |
|
470: | $sql .= " AND `pf`.`filter_id` IN (" . implode(',', $implode) . ")";
|
471: | }
|
472: | }
|
473: |
|
474: | if (!empty($data['filter_search']) || !empty($data['filter_tag'])) {
|
475: | $sql .= " AND (";
|
476: |
|
477: | if (!empty($data['filter_search'])) {
|
478: | $implode = [];
|
479: |
|
480: | $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_search'])));
|
481: | $words = array_filter($words);
|
482: |
|
483: | foreach ($words as $word) {
|
484: | $implode[] = "`pd`.`name` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
|
485: | }
|
486: |
|
487: | if ($implode) {
|
488: | $sql .= " (" . implode(" OR ", $implode) . ")";
|
489: | }
|
490: |
|
491: | if (!empty($data['filter_description'])) {
|
492: | $sql .= " OR `pd`.`description` LIKE '" . $this->db->escape('%' . (string)$data['filter_search'] . '%') . "'";
|
493: | }
|
494: | }
|
495: |
|
496: | if (!empty($data['filter_search']) && !empty($data['filter_tag'])) {
|
497: | $sql .= " OR ";
|
498: | }
|
499: |
|
500: | if (!empty($data['filter_tag'])) {
|
501: | $implode = [];
|
502: |
|
503: | $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));
|
504: | $words = array_filter($words);
|
505: |
|
506: | foreach ($words as $word) {
|
507: | $implode[] = "`pd`.`tag` LIKE '" . $this->db->escape('%' . $word . '%') . "'";
|
508: | }
|
509: |
|
510: | if ($implode) {
|
511: | $sql .= " (" . implode(" OR ", $implode) . ")";
|
512: | }
|
513: | }
|
514: |
|
515: | if (!empty($data['filter_search'])) {
|
516: | $sql .= " OR LCASE(`p`.`model`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
517: | $sql .= " OR LCASE(`p`.`sku`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
518: | $sql .= " OR LCASE(`p`.`upc`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
519: | $sql .= " OR LCASE(`p`.`ean`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
520: | $sql .= " OR LCASE(`p`.`jan`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
521: | $sql .= " OR LCASE(`p`.`isbn`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
522: | $sql .= " OR LCASE(`p`.`mpn`) = '" . $this->db->escape(oc_strtolower($data['filter_search'])) . "'";
|
523: | }
|
524: |
|
525: | $sql .= ")";
|
526: | }
|
527: |
|
528: | if (!empty($data['filter_manufacturer_id'])) {
|
529: | $sql .= " AND `p`.`manufacturer_id` = '" . (int)$data['filter_manufacturer_id'] . "'";
|
530: | }
|
531: |
|
532: | $query = $this->db->query($sql);
|
533: |
|
534: | return (int)$query->row['total'];
|
535: | }
|
536: |
|
537: | |
538: | |
539: | |
540: | |
541: | |
542: | |
543: |
|
544: | public function getSpecials(array $data = []): array {
|
545: | $sql = "SELECT DISTINCT *, `pd`.`name`, `p`.`image`, `p`.`price`, " . $this->statement['discount'] . ", " . $this->statement['special'] . ", " . $this->statement['reward'] . ", " . $this->statement['review'] . " FROM `" . DB_PREFIX . "product_special` `ps2` LEFT JOIN `" . DB_PREFIX . "product_to_store` `p2s` ON (`ps2`.`product_id` = `p2s`.`product_id` AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `ps2`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((`ps2`.`date_start` = '0000-00-00' OR `ps2`.`date_start` < NOW()) AND (`ps2`.`date_end` = '0000-00-00' OR `ps2`.`date_end` > NOW()))) LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2s`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW()) LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`pd`.`product_id` = `p`.`product_id`) WHERE `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' GROUP BY `ps2`.`product_id`";
|
546: |
|
547: | $sort_data = [
|
548: | 'pd.name',
|
549: | 'p.model',
|
550: | 'p.price',
|
551: | 'rating',
|
552: | 'p.sort_order'
|
553: | ];
|
554: |
|
555: | if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
|
556: | if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
|
557: | $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
|
558: | } elseif ($data['sort'] == 'p.price') {
|
559: | $sql .= " ORDER BY (CASE WHEN `special` IS NOT NULL THEN `special` WHEN `discount` IS NOT NULL THEN `discount` ELSE `p`.`price` END)";
|
560: | } else {
|
561: | $sql .= " ORDER BY " . $data['sort'];
|
562: | }
|
563: | } else {
|
564: | $sql .= " ORDER BY `p`.`sort_order`";
|
565: | }
|
566: |
|
567: | if (isset($data['order']) && ($data['order'] == 'DESC')) {
|
568: | $sql .= " DESC, LCASE(`pd`.`name`) DESC";
|
569: | } else {
|
570: | $sql .= " ASC, LCASE(`pd`.`name`) ASC";
|
571: | }
|
572: |
|
573: | if (isset($data['start']) || isset($data['limit'])) {
|
574: | if ($data['start'] < 0) {
|
575: | $data['start'] = 0;
|
576: | }
|
577: |
|
578: | if ($data['limit'] < 1) {
|
579: | $data['limit'] = 20;
|
580: | }
|
581: |
|
582: | $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
|
583: | }
|
584: |
|
585: | $key = md5($sql);
|
586: |
|
587: | $product_data = $this->cache->get('product.' . $key);
|
588: |
|
589: | if (!$product_data) {
|
590: | $query = $this->db->query($sql);
|
591: |
|
592: | $product_data = $query->rows;
|
593: |
|
594: | $this->cache->set('product.' . $key, $product_data);
|
595: | }
|
596: |
|
597: | return (array)$product_data;
|
598: | }
|
599: |
|
600: | |
601: | |
602: | |
603: | |
604: |
|
605: | public function getTotalSpecials(): int {
|
606: | $query = $this->db->query("SELECT COUNT(DISTINCT `ps`.`product_id`) AS `total` FROM `" . DB_PREFIX . "product_special` `ps` LEFT JOIN `" . DB_PREFIX . "product_to_store` `p2s` ON (`p2s`.`product_id` = `ps`.`product_id` AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND `ps`.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((`ps`.`date_start` = '0000-00-00' OR `ps`.`date_start` < NOW()) AND (`ps`.`date_end` = '0000-00-00' OR `ps`.`date_end` > NOW()))) LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p2s`.`product_id` = `p`.`product_id` AND `p`.`status` = '1' AND `p`.`date_available` <= NOW())");
|
607: |
|
608: | if (isset($query->row['total'])) {
|
609: | return (int)$query->row['total'];
|
610: | } else {
|
611: | return 0;
|
612: | }
|
613: | }
|
614: |
|
615: | |
616: | |
617: | |
618: | |
619: | |
620: | |
621: | |
622: | |
623: |
|
624: | public function addReport(int $product_id, string $ip, string $country = ''): void {
|
625: | $this->db->query("INSERT INTO `" . DB_PREFIX . "product_report` SET `product_id` = '" . (int)$product_id . "', `store_id` = '" . (int)$this->config->get('config_store_id') . "', `ip` = '" . $this->db->escape($ip) . "', `country` = '" . $this->db->escape($country) . "', `date_added` = NOW()");
|
626: | }
|
627: | }
|
628: | |