docs/api/source-catalog.model.catalog.product.html
| 1: | <?php |
| 2: | namespace Opencart\Catalog\Model\Catalog; |
| 3: | /** |
| 4: | * Class Product |
| 5: | * |
| 6: | * @package Opencart\Catalog\Model\Catalog |
| 7: | */ |
| 8: | class Product extends \Opencart\System\Engine\Model { |
| 9: | /** |
| 10: | * @var array<string, string> |
| 11: | */ |
| 12: | protected array $statement = []; |
| 13: | |
| 14: | /** |
| 15: | * Constructor |
| 16: | * |
| 17: | * @param \Opencart\System\Engine\Registry $registry |
| 18: | */ |
| 19: | public function __construct(\Opencart\System\Engine\Registry $registry) { |
| 20: | $this->registry = $registry; |
| 21: | |
| 22: | // Storing some sub queries so that we are not typing them out multiple times. |
| 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: | * Get Product |
| 31: | * |
| 32: | * @param int $product_id |
| 33: | * |
| 34: | * @return array<string, mixed> |
| 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: | * Get Products |
| 56: | * |
| 57: | * @param array<string, mixed> $data |
| 58: | * |
| 59: | * @return array<int, array<string, mixed>> |
| 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: | * Get Categories |
| 221: | * |
| 222: | * @param int $product_id |
| 223: | * |
| 224: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Categories By Category ID |
| 234: | * |
| 235: | * @param int $product_id |
| 236: | * @param int $category_id |
| 237: | * |
| 238: | * @return array<string, mixed> |
| 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: | * Get Attributes |
| 248: | * |
| 249: | * @param int $product_id |
| 250: | * |
| 251: | * @return array<int, array<string, mixed>> |
| 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: | * Get Options |
| 283: | * |
| 284: | * @param int $product_id |
| 285: | * |
| 286: | * @return array<int, array<string, mixed>> |
| 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: | * Get Discounts |
| 329: | * |
| 330: | * @param int $product_id |
| 331: | * |
| 332: | * @return array<int, array<string, mixed>> |
| 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: | * Get Images |
| 342: | * |
| 343: | * @param int $product_id |
| 344: | * |
| 345: | * @return array<int, array<string, mixed>> |
| 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: | * Get Subscription |
| 355: | * |
| 356: | * @param int $product_id |
| 357: | * @param int $subscription_plan_id |
| 358: | * |
| 359: | * @return array<string, mixed> |
| 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: | * Get Subscriptions |
| 369: | * |
| 370: | * @param int $product_id |
| 371: | * |
| 372: | * @return array<int, array<string, mixed>> |
| 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: | * Get Layout ID |
| 382: | * |
| 383: | * @param int $product_id |
| 384: | * |
| 385: | * @return int |
| 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: | * Get Related |
| 399: | * |
| 400: | * @param int $product_id |
| 401: | * |
| 402: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Products |
| 424: | * |
| 425: | * @param array<string, mixed> $data |
| 426: | * |
| 427: | * @return int |
| 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: | * Get Specials |
| 539: | * |
| 540: | * @param array<string, mixed> $data |
| 541: | * |
| 542: | * @return array<int, array<string, mixed>> |
| 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: | * Get Total Specials |
| 602: | * |
| 603: | * @return int |
| 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: | * Add Report |
| 617: | * |
| 618: | * @param int $product_id |
| 619: | * @param string $ip |
| 620: | * @param string $country |
| 621: | * |
| 622: | * @return void |
| 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: | |
OpenCart API API documentation generated by ApiGen dev-master