| 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: | |