mplode = array(); $filters = explode(',', $data['filter_filter']); foreach ($filters as $filter_id) { $implode[] = (int)$filter_id; } $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")"; $minq=$this->db->query(" SELECT id.product_id FROM(SELECT product_id, count(i.product_id) AS t, c.filter_group AS b FROM (SELECT f.filter_id, f.product_id, p.filter_group_id FROM ". DB_PREFIX ."product_filter f LEFT JOIN ". DB_PREFIX ."filter p ON p.filter_id = f.filter_id WHERE p.filter_id IN (" . implode(',', $implode) . ") GROUP BY product_id , filter_group_id) AS i, (SELECT COUNT(DISTINCT filter_group_id) AS filter_group FROM ". DB_PREFIX ."filter_description f_b WHERE f_b.filter_id in (" . implode(',', $implode) . ")) as c GROUP BY product_id HAVING t = b) AS id "); $min=array(); for($x=0;$xrows);$x++){ foreach($minq->rows[$x] as $value){ $min[]=$value; } } $imp=implode(',',$min); } } if (!empty($data['filter_name']) || !empty($data['filter_tag'])) { $sql .= " AND ("; if (!empty($data['filter_name'])) { $implode = array(); $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name']))); foreach ($words as $word) { if(!$this->config->get('config_increase_searching_accuracy')){ $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'"; }else{ $implode[] = "(pd.name LIKE '% " . $this->db->escape($word) . " %' OR pd.name LIKE '" . $this->db->escape($word) . " %' OR pd.name LIKE '% " . $this->db->escape($word) . "' OR pd.name = '" . $this->db->escape($word) . "')"; } } if ($implode) { $sql .= " " . implode(" AND ", $implode) . ""; } if (!empty($data['filter_description'])) { $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'"; } } if (!empty($data['filter_name']) && !empty($data['filter_tag'])) { $sql .= " OR "; } if (!empty($data['filter_tag'])) { $sql .= "lower(pd.tag) LIKE '%," . $this->db->escape(utf8_strtolower($data['filter_tag'])) . ",%' OR pd.tag LIKE '%, " . $this->db->escape($data['filter_tag']) . ",%' OR pd.tag LIKE '%, " . $this->db->escape($data['filter_tag']) . " ,%' OR pd.tag LIKE '" . $this->db->escape($data['filter_tag']) . "' OR pd.tag LIKE '%," . $this->db->escape($data['filter_tag']) . " ,%' OR pd.tag LIKE '" . $this->db->escape($data['filter_tag']) . " ,%' OR pd.tag LIKE '" . $this->db->escape($data['filter_tag']) . ",%' OR pd.tag LIKE '%, " . $this->db->escape($data['filter_tag']) . "' OR pd.tag LIKE '%," . $this->db->escape($data['filter_tag']) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } $sql .= ")"; } if (!empty($data['filter_manufacturer_id'])) { $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'"; } if(isset($filters)){ if(!count($min)){ $imp='0000000'; } $sql .= " AND p.product_id IN(".$imp.")"; } $sql .= " GROUP BY p.product_id"; if(!empty($data['filter_collections_id'])) { $sort_order = 'p2c.sort_order'; }else{ $sort_order = 'p.sort_order'; } $sort_data = array( 'pd.name', 'p.model', 'p.quantity', 'p.price', 'rating', $sort_order, 'p.date_added' ); if (isset($data['sort']) && in_array($data['sort'], $sort_data)) { if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') { $sql .= " ORDER BY LCASE(" . $data['sort'] . ")"; } elseif ($data['sort'] == 'p.price') { $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)"; } else { $sql .= " ORDER BY " . $data['sort']; } } else if(!empty($data['filter_collections_id'])) { $sql .= " ORDER BY p2c.sort_order"; } else { $sql .= " ORDER BY p.sort_order"; } if (isset($data['order']) && ($data['order'] == 'DESC')) { $sql .= " DESC, LCASE(pd.name) DESC"; } else { $sql .= " ASC, LCASE(pd.name) ASC"; } if (isset($data['start']) || isset($data['limit'])) { if ($data['start'] < 0) { $data['start'] = 0; } if ($data['limit'] < 1) { $data['limit'] = 20; } $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; } $product_data = array(); $query = $this->db->query($sql); foreach ($query->rows as $result) { $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } return $product_data; } public function getProductSpecials($data = array()) { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$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())) GROUP BY ps.product_id"; $sort_data = array( 'pd.name', 'p.model', 'ps.price', 'rating', 'p.sort_order', 'p.date_added' ); if (isset($data['sort']) && in_array($data['sort'], $sort_data)) { if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') { $sql .= " ORDER BY LCASE(" . $data['sort'] . ")"; } else { $sql .= " ORDER BY " . $data['sort']; } } else { $sql .= " ORDER BY p.sort_order"; } if (isset($data['order']) && ($data['order'] == 'DESC')) { $sql .= " DESC, LCASE(pd.name) DESC"; } else { $sql .= " ASC, LCASE(pd.name) ASC"; } if (isset($data['start']) || isset($data['limit'])) { if ($data['start'] < 0) { $data['start'] = 0; } if ($data['limit'] < 1) { $data['limit'] = 20; } $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit']; } $product_data = array(); $query = $this->db->query($sql); foreach ($query->rows as $result) { $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } return $product_data; } public function getLatestProducts($limit) { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $product_data = $this->cache->get('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $customer_group_id . '.' . (int)$limit); if (!$product_data) { $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit); foreach ($query->rows as $result) { $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } $this->cache->set('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit, $product_data); } return $product_data; } public function getPopularProducts($limit) { $product_data = array(); $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.viewed, p.date_added DESC LIMIT " . (int)$limit); foreach ($query->rows as $result) { $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } return $product_data; } public function getMostViewedProduct($limit) { $product_data = array(); $query = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product order by viewed DESC LIMIT " . (int)$limit); foreach ($query->rows as $result) { $product_data[] = $result['product_id']; } return $product_data; } public function getComingSoonProduct($limit) { $product_data = array(); $query = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product WHERE date_available > NOW() LIMIT " . (int)$limit); foreach ($query->rows as $result) { $product_data[] = $result['product_id']; } return $product_data; } public function getProductComingSoon($product_id) { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$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, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$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, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.visibility = '0' AND p.date_available > NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"); if ($query->num_rows) { return array( 'product_id' => $query->row['product_id'], 'name' => $query->row['name'], 'description' => $query->row['description'], 'highlight' => $query->row['highlight'], 'whatbox' => $query->row['whatbox'], 'meta_description' => $query->row['meta_description'], 'meta_keyword' => $query->row['meta_keyword'], 'page_title' => $query->row['page_title'], 'tag' => $query->row['tag'], 'model' => $query->row['model'], 'sku' => $query->row['sku'], 'upc' => $query->row['upc'], 'ean' => $query->row['ean'], 'jan' => $query->row['jan'], 'isbn' => $query->row['isbn'], 'mpn' => $query->row['mpn'], 'location' => $query->row['location'], 'quantity' => $query->row['quantity'], 'stock_status' => $query->row['stock_status'], 'image' => $query->row['image'], 'manufacturer_id' => $query->row['manufacturer_id'], 'manufacturer' => $query->row['manufacturer'], 'price' => ($query->row['discount'] ? $query->row['discount'] : $query->row['price']), 'special' => $query->row['special'], 'reward' => $query->row['reward'], 'points' => $query->row['points'], 'tax_class_id' => $query->row['tax_class_id'], 'date_available' => $query->row['date_available'], 'weight' => $query->row['weight'], 'weight_class_id' => $query->row['weight_class_id'], 'length' => $query->row['length'], 'width' => $query->row['width'], 'height' => $query->row['height'], 'length_class_id' => $query->row['length_class_id'], 'subtract' => $query->row['subtract'], 'rating' => round($query->row['rating']), 'reviews' => $query->row['reviews'] ? $query->row['reviews'] : 0, 'minimum' => $query->row['minimum'], 'maximum' => $query->row['maximum'], 'sort_order' => $query->row['sort_order'], 'status' => $query->row['status'], 'date_added' => $query->row['date_added'], 'date_modified' => $query->row['date_modified'], 'viewed' => $query->row['viewed'] ); } else { return false; } } public function getCollectionsProduct($collections_id) { $product_data = array(); $query_check = $this->db->query("SELECT * FROM " . DB_PREFIX . "collections WHERE collections_id = '" . (int)$collections_id . "' and status = 1"); $query_check->row['collections_id'] = ($query_check->num_rows) ? $query_check->row['collections_id'] : 0; $query = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "product_to_collections WHERE collections_id = '" . (int)$query_check->row['collections_id'] . "' order by sort_order ASC"); foreach ($query->rows as $result) { $product_data[] = $result['product_id']; } return $product_data; } public function getBestSellerProducts($limit) { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit); if (!$product_data) { $product_data = array(); $query = $this->db->query("SELECT op.product_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit); foreach ($query->rows as $result) { $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } $this->cache->set('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit, $product_data); } return $product_data; } public function getProductAttributes($product_id) { $product_attribute_group_data = array(); $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"); foreach ($product_attribute_group_query->rows as $product_attribute_group) { $product_attribute_data = array(); $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"); foreach ($product_attribute_query->rows as $product_attribute) { $product_attribute_data[] = array( 'attribute_id' => $product_attribute['attribute_id'], 'name' => $product_attribute['name'], 'text' => $product_attribute['text'] ); } $product_attribute_group_data[] = array( 'attribute_group_id' => $product_attribute_group['attribute_group_id'], 'name' => $product_attribute_group['name'], 'attribute' => $product_attribute_data ); } return $product_attribute_group_data; } public function getProductOptions($product_id) { if($this->config->get('sg_up_po')=="n") { return 0; } else { } $product_option_data = array(); $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"); foreach ($product_option_query->rows as $product_option) { if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') { $product_option_value_data = array(); if($this->config->get('config_default_opt_style')){ $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,ovd.name"); }else{ $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,ovd.name"); } foreach ($product_option_value_query->rows as $product_option_value) { $product_option_value_data[] = array( 'product_option_value_id' => $product_option_value['product_option_value_id'], 'option_value_id' => $product_option_value['option_value_id'], 'name' => $product_option_value['name'], 'image' => $product_option_value['image'], 'quantity' => $product_option_value['quantity'], 'subtract' => $product_option_value['subtract'], 'price' => $product_option_value['price'], 'price_prefix' => $product_option_value['price_prefix'], 'points' => $product_option_value['points'], 'points_prefix' => $product_option_value['points_prefix'], 'weight' => $product_option_value['weight'], 'weight_prefix' => $product_option_value['weight_prefix'] ); } $product_option_data[] = array( 'product_option_id' => $product_option['product_option_id'], 'option_id' => $product_option['option_id'], 'name' => $product_option['name'], 'type' => $product_option['type'], 'option_value' => $product_option_value_data, 'required' => $product_option['required'] ); } else { $product_option_data[] = array( 'product_option_id' => $product_option['product_option_id'], 'option_id' => $product_option['option_id'], 'name' => $product_option['name'], 'type' => $product_option['type'], 'option_value' => $product_option['option_value'], 'required' => $product_option['required'] ); } } return $product_option_data; } public function getProductDiscounts($product_id) { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$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 priority ASC,quantity ASC,price ASC"); return $query->rows; } public function getProductImages($product_id) { $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC"); return $query->rows; } public function getAIOProductImages($product_id) { $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' AND hide = '0' ORDER BY sort_order ASC"); return $query->rows; } public function getProductRelated($product_id) { $product_data = array(); $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = '" . (int)$product_id . "' AND p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY pr.sort_order ASC"); foreach ($query->rows as $result) { $product_data[$result['related_id']] = $this->getProduct($result['related_id']); } return $product_data; } public function getProductLayoutId($product_id) { $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') . "'"); if ($query->num_rows) { return $query->row['layout_id']; } else { return $this->config->get('config_layout_product'); } } public function getCategory($product_id) { $sql = "SELECT cd.name AS name FROM ". DB_PREFIX . "product_to_category AS p2c LEFT JOIN ". DB_PREFIX . "category_description AS cd ON cd.category_id = p2c.category_id WHERE p2c.product_id = '".(int)$product_id."' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; $query = $this->db->query($sql); $result = $query->row['name']; return $result; } public function getCategories($product_id) { $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'"); return $query->rows; } public function getTotalProducts($data = array()) { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $sql = "SELECT COUNT(DISTINCT p.product_id) AS total"; if (!empty($data['filter_category_id'])) { if (!empty($data['filter_sub_category'])) { $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)"; } else { $sql .= " FROM " . DB_PREFIX . "product_to_category p2c"; } if (!empty($data['filter_filter'])) { $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)"; } else { $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)"; } } else if (!empty($data['filter_collections_id'])) { $sql .= " FROM " . DB_PREFIX . "product_to_collections p2c"; if (!empty($data['filter_filter'])) { $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)"; } else { $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)"; } } else { $sql .= " FROM " . DB_PREFIX . "product p"; } $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"; if (!empty($data['filter_category_id'])) { if (!empty($data['filter_sub_category'])) { $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'"; } else { $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'"; } if (!empty($data['filter_filter'])) { $implode = array(); $filters = explode(',', $data['filter_filter']); foreach ($filters as $filter_id) { $implode[] = (int)$filter_id; } $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")"; $minq=$this->db->query(" SELECT id.product_id FROM(SELECT product_id, count(i.product_id) AS t, c.filter_group AS b FROM (SELECT f.filter_id, f.product_id, p.filter_group_id FROM ". DB_PREFIX ."product_filter f LEFT JOIN ". DB_PREFIX ."filter p ON p.filter_id = f.filter_id WHERE p.filter_id IN (" . implode(',', $implode) . ") GROUP BY product_id , filter_group_id) AS i, (SELECT COUNT(DISTINCT filter_group_id) AS filter_group FROM ". DB_PREFIX ."filter_description f_b WHERE f_b.filter_id in (" . implode(',', $implode) . ")) as c GROUP BY product_id HAVING t = b) AS id "); $min=array(); for($x=0;$xrows);$x++){ foreach($minq->rows[$x] as $value){ $min[]=$value; } } $imp=implode(',',$min); } } else if (!empty($data['filter_collections_id'])) { $sql .= " AND p2c.collections_id = '" . (int)$data['filter_collections_id'] . "'"; if (!empty($data['filter_filter'])) { $implode = array(); $filters = explode(',', $data['filter_filter']); foreach ($filters as $filter_id) { $implode[] = (int)$filter_id; } $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")"; $minq=$this->db->query(" SELECT id.product_id FROM(SELECT product_id, count(i.product_id) AS t, c.filter_group AS b FROM (SELECT f.filter_id, f.product_id, p.filter_group_id FROM ". DB_PREFIX ."product_filter f LEFT JOIN ". DB_PREFIX ."filter p ON p.filter_id = f.filter_id WHERE p.filter_id IN (" . implode(',', $implode) . ") GROUP BY product_id , filter_group_id) AS i, (SELECT COUNT(DISTINCT filter_group_id) AS filter_group FROM ". DB_PREFIX ."filter_description f_b WHERE f_b.filter_id in (" . implode(',', $implode) . ")) as c GROUP BY product_id HAVING t = b) AS id "); $min=array(); for($x=0;$xrows);$x++){ foreach($minq->rows[$x] as $value){ $min[]=$value; } } $imp=implode(',',$min); } } if (!empty($data['filter_name']) || !empty($data['filter_tag'])) { $sql .= " AND ("; if (!empty($data['filter_name'])) { $implode = array(); $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name']))); foreach ($words as $word) { if(!$this->config->get('config_increase_searching_accuracy')){ $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'"; }else{ $implode[] = "(pd.name LIKE '% " . $this->db->escape($word) . " %' OR pd.name LIKE '" . $this->db->escape($word) . " %' OR pd.name LIKE '% " . $this->db->escape($word) . "' OR pd.name = '" . $this->db->escape($word) . "')"; } } if ($implode) { $sql .= " " . implode(" AND ", $implode) . ""; } if (!empty($data['filter_description'])) { $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'"; } } if (!empty($data['filter_name']) && !empty($data['filter_tag'])) { $sql .= " OR "; } if (!empty($data['filter_tag'])) { $sql .= "lower(pd.tag) LIKE '%," . $this->db->escape(utf8_strtolower($data['filter_tag'])) . ",%' OR pd.tag LIKE '%, " . $this->db->escape($data['filter_tag']) . ",%' OR pd.tag LIKE '%, " . $this->db->escape($data['filter_tag']) . " ,%' OR pd.tag LIKE '" . $this->db->escape($data['filter_tag']) . "' OR pd.tag LIKE '%," . $this->db->escape($data['filter_tag']) . " ,%' OR pd.tag LIKE '" . $this->db->escape($data['filter_tag']) . " ,%' OR pd.tag LIKE '" . $this->db->escape($data['filter_tag']) . ",%' OR pd.tag LIKE '%, " . $this->db->escape($data['filter_tag']) . "' OR pd.tag LIKE '%," . $this->db->escape($data['filter_tag']) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } if (!empty($data['filter_name'])) { $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'"; } $sql .= ")"; } if (!empty($data['filter_manufacturer_id'])) { $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'"; } if(isset($filters)){ if(!count($min)){ $imp='0000000'; } $sql .= " AND p.product_id IN(".$imp.")"; } $query = $this->db->query($sql); return $query->row['total']; } //function to get product public function getAutoCompleteProduct($data = array()) { $query = $this->db->query("SELECT * FROM ". DB_PREFIX. "product_description AS prdes INNER JOIN ".DB_PREFIX."product AS prod ON prdes.product_id = prod.product_id WHERE prod.status = 1 AND prod.visibility = 0 AND prod.date_available <= NOW() AND prdes.name LIKE '%".$this->db->escape($data['search'])."%' LIMIT 5"); return $query->rows; } public function getOptionImage($data){ if($this->config->get('config_default_opt_style')){ $query = $this->db->query("SELECT product_id, image_path FROM " . DB_PREFIX . "product_option_value WHERE product_option_value_id='".$data['product_option_value_id']."'"); } else { $query = $this->db->query("SELECT pov.product_id,ov.image as image_path FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (ov.option_value_id = pov.option_value_id) WHERE pov.product_option_value_id='".$data['product_option_value_id']."'"); } if($this->config->get('config_template') == 'aio'){ $query_hide = $this->db->query("SELECT hide FROM " . DB_PREFIX . "product_image WHERE image='".$query->row['image_path']."' AND product_id ='".$query->row['product_id']."'"); if($query_hide->row['hide'] == 1){ $query->row['image_path'] = 'no_image.jpg'; } } if($query->row['image_path'] && $query->row['image_path'] != 'no_image.jpg'){ return $query->row['image_path']; }else{ $query = $this->db->query("SELECT p.image FROM " . DB_PREFIX . "product p LEFT JOIN ". DB_PREFIX . "product_option_value pov ON (p.product_id=pov.product_id) WHERE pov.product_option_value_id='".$data['product_option_value_id']."'"); return $query->row['image']; } } public function getDiscountCollections($product_id){ $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "collections c LEFT JOIN " . DB_PREFIX . "discount pr ON (c.collections_id = pr.collections_id) WHERE c.status = '1' AND c.discount_status = '1' AND ((pr.date_start = '0000-00-00 00:00:00' OR pr.date_start <= NOW()) AND (pr.date_end = '0000-00-00 00:00:00' OR pr.date_end >= NOW())) "); if($query->num_rows > 0) { foreach($query->rows as $row){ $active_promos[] = $row['collections_id']; } $active_promo = "'" . implode( "','", $active_promos ) . "'"; }else{ $active_promo = "'" .''. "'"; } $query =$this->db->query("SELECT pr.*, cd.name FROM " . DB_PREFIX . "product_to_collections p2c LEFT JOIN " . DB_PREFIX . "discount pr ON (p2c.collections_id = pr.collections_id) INNER JOIN " . DB_PREFIX . "collections_description cd ON (pr.collections_id = cd.collections_id) WHERE p2c.collections_id IN (".$active_promo.") AND p2c.product_id = '" . (int)$product_id . "'"); $customer_group = (unserialize($query->row['discount_customer'])); $show = 0; if($customer_group['every'] == 1){ $show = 1; } if($customer_group['group'] != ''){ $customer_group_arr = explode(',' ,$customer_group['group']); if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } if(in_array($customer_group_id, $customer_group_arr)){ $show = 1; } } if($customer_group['select'] != ''){ $customer_id_arr = explode(',' ,$customer_group['select']); if ($this->customer->isLogged()) { $customer_id = $this->customer->getId(); } else { $customer_id = 0; } if(in_array($customer_id, $customer_id_arr)){ $show = 1; } } if($query->num_rows > 0 && ($show == 1)) { $query_url = $this->url->link('product/collections', 'pathc=' . $query->row['collections_id'], 'SSL'); $label_bg_color = $this->config->get('discount_visual_bg_color'); $label_text_color = $this->config->get('discount_visual_text_color'); $discount_info = array( 'promo_label_bg_color' => $label_bg_color, 'promo_label_text_color' => $label_text_color, 'promo_label_title' => $query->row['name'], 'promo_url' => $query_url ); return $discount_info; } } public function getAllBundleProduct(){ $query = $this->db->query("SELECT * FROM ".DB_PREFIX."bundle WHERE status='1' AND ((date_start = '0000-00-00 00:00:00' OR date_start <= NOW()) AND (date_end = '0000-00-00 00:00:00' OR date_end >= NOW())) AND (bundle_child_product != '' OR bundle_child_product != 'a:0:{}') ORDER BY date_end"); return $query->rows; } public function getBundleProduct($product_id){ $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "bundle WHERE status = '1' AND ((date_start = '0000-00-00 00:00:00' OR date_start <= NOW()) AND (date_end = '0000-00-00 00:00:00' OR date_end >= NOW())) AND bundle_parent_product = '" . (int)$product_id . "' AND (bundle_child_product != '' OR bundle_child_product != 'a:0:{}')"); $bundle_info = array(); if($query->num_rows > 0) { $bundle_info = array( 'bundle_id' => $query->row['bundle_id'], 'bundle_title' => $query->row['bundle_name'], 'bundle_cond' => $query->row['bundle_cond'], 'bundle_amount' => $query->row['bundle_amount'] ); return $bundle_info; } } public function getBundleProductAll($bundle_id){ $query = $this->db->query("SELECT bundle_parent_product, bundle_child_product FROM " . DB_PREFIX . "bundle WHERE bundle_id = '" . (int)$bundle_id . "'"); if($query->num_rows > 0) { $child_products = unserialize($query->row['bundle_child_product']); array_unshift($child_products,$query->row['bundle_parent_product']); $bundle_products = $child_products; $bundle_prod = array(); $total_amount = 0; foreach($bundle_products as $product_id){ if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$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, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$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, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'"); if ($query->num_rows) { $this->load->model('tool/image'); $image = $this->model_tool_image->resize($query->row['image'], 100, 100); if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $price = $query->row['price']; $product_special_query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC LIMIT 1"); if ($product_special_query->num_rows) { $price = $product_special_query->row['price']; } $bundle_prod = array( 'product_id' => $query->row['product_id'], 'name' => $query->row['name'], 'description' => $query->row['description'], 'howmanybought' => $query->row['quantity_sold'], 'highlight' => $query->row['highlight'], 'whatbox' => $query->row['whatbox'], 'meta_description' => $query->row['meta_description'], 'meta_keyword' => $query->row['meta_keyword'], 'page_title' => $query->row['page_title'], 'tag' => $query->row['tag'], 'model' => $query->row['model'], 'sku' => $query->row['sku'], 'upc' => $query->row['upc'], 'ean' => $query->row['ean'], 'jan' => $query->row['jan'], 'isbn' => $query->row['isbn'], 'mpn' => $query->row['mpn'], 'location' => $query->row['location'], 'quantity' => $query->row['quantity'], 'stock_status' => $query->row['stock_status'], 'image' => $image, 'manufacturer_id' => $query->row['manufacturer_id'], 'manufacturer' => $query->row['manufacturer'], 'price' => $price, 'special' => $query->row['special'], 'reward' => $query->row['reward'], 'points' => $query->row['points'], 'tax_class_id' => $query->row['tax_class_id'], 'date_available' => $query->row['date_available'], 'weight' => $query->row['weight'], 'weight_class_id' => $query->row['weight_class_id'], 'length' => $query->row['length'], 'width' => $query->row['width'], 'height' => $query->row['height'], 'length_class_id' => $query->row['length_class_id'], 'subtract' => $query->row['subtract'], 'rating' => round($query->row['rating']), 'reviews' => $query->row['reviews'] ? $query->row['reviews'] : 0, 'minimum' => $query->row['minimum'], 'maximum' => $query->row['maximum'], 'sort_order' => $query->row['sort_order'], 'status' => $query->row['status'], 'date_added' => $query->row['date_added'], 'date_modified' => $query->row['date_modified'], 'viewed' => $query->row['viewed'], 'href' => $this->url->link('product/product', 'product_id=' . $query->row['product_id']), 'option' => $this->getProductOptions($query->row['product_id']) ); $bundle_prods[] = $bundle_prod; } $bundle_prod_arrs[] = $query->row['product_id']; $total_amount += $price; } } if($bundle_prod_arrs != $bundle_products){ return array(); }else{ $total_data = count($bundle_prods); return array('data' => $bundle_prods, 'total_bundle_price' => $total_amount, 'total_data' => $total_data); } } public function getTotalProductSpecials() { if ($this->customer->isLogged()) { $customer_group_id = $this->customer->getCustomerGroupId(); } else { $customer_group_id = $this->config->get('config_customer_group_id'); } $query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.visibility = '0' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$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()))"); if (isset($query->row['total'])) { return $query->row['total']; } else { return 0; } } } ?>