public function getStaticblockLists($id_shop = NULL, $hook_position = 'top') { if (!Combination::isFeatureActive()) { return array(); } $id_lang = (int) Context::getContext()->language->id; $object = Db::getInstance()->executeS(' SELECT * FROM ' . _DB_PREFIX_ . 'pos_staticblock AS psb LEFT JOIN ' . _DB_PREFIX_ . 'pos_staticblock_lang AS psl ON psb.id_posstaticblock = psl.id_posstaticblock LEFT JOIN ' . _DB_PREFIX_ . 'pos_staticblock_shop AS pss ON psb.id_posstaticblock = pss.id_posstaticblock WHERE id_shop =' . $id_shop . ' AND id_lang =' . $id_lang . ' AND `hook_position` = "' . $hook_position . '" AND `showhook` = 1 ORDER BY `posorder` ASC '); $newObject = array(); if (count($newObject > 0)) { foreach ($object as $key => $ob) { $nameModule = $ob['name_module']; $hookModule = $ob['hook_module']; $blockModule = $this->getModuleAssign($nameModule, $hookModule); $ob['block_module'] = $blockModule; $description = $ob['description']; $description = str_replace('/pos_hitstore/', __PS_BASE_URI__, $description); $ob['description'] = $description; // array_push($ob, $blockModule); $newObject[$key] = $ob; } return $newObject; } return null; }
public function initFieldsetFeaturesDetachables() { $this->fields_form[2]['form'] = array('legend' => array('title' => $this->l('Optional features'), 'icon' => 'icon-puzzle-piece'), 'description' => $this->l('Some features can be disabled in order to improve performance.'), 'input' => array(array('type' => 'hidden', 'name' => 'features_detachables_up'), array('type' => 'switch', 'label' => $this->l('Combinations'), 'name' => 'combination', 'is_bool' => true, 'disabled' => Combination::isCurrentlyUsed(), 'values' => array(array('id' => 'combination_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'combination_0', 'value' => 0, 'label' => $this->l('No'))), 'hint' => $this->l('Choose "No" to disable Product Combinations.'), 'desc' => Combination::isCurrentlyUsed() ? $this->l('You cannot set this parameter to No when combinations are already used by some of your products') : null), array('type' => 'switch', 'label' => $this->l('Features'), 'name' => 'feature', 'is_bool' => true, 'values' => array(array('id' => 'feature_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'feature_0', 'value' => 0, 'label' => $this->l('No'))), 'hint' => $this->l('Choose "No" to disable Product Features.')), array('type' => 'switch', 'label' => $this->l('Customer Groups'), 'name' => 'customer_group', 'is_bool' => true, 'disabled' => Group::isCurrentlyUsed(), 'values' => array(array('id' => 'group_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'group_0', 'value' => 0, 'label' => $this->l('No'))), 'hint' => $this->l('Choose "No" to disable Customer Groups.'))), 'submit' => array('title' => $this->l('Save'))); $this->fields_value['combination'] = Combination::isFeatureActive(); $this->fields_value['feature'] = Feature::isFeatureActive(); $this->fields_value['customer_group'] = Group::isFeatureActive(); }
public static function getProducts($product_ids, $p = 1, $n, $active = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } $id_lang = $context->language->id; $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if ($p < 1) { $p = 1; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . ', pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') . ' DAY)) > 0 AS new, product_shop.price AS orderprice FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . ' ' . Product::sqlStock('p', 0) . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($product_ids ? ' AND p.id_product IN (0, ' . pSQL(implode(',', $product_ids)) . ')' : '') . ' GROUP BY cp.id_product'; if ($n) { $sql .= ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql, true, false); if (!$result) { return array(); } /* Modify SQL result */ return Product::getProductsProperties($id_lang, $result); }
public function initFormQuantities($obj) { if (!$this->default_form_language) { $this->getLanguages(); } $data = $this->createTemplate($this->tpl_form); $data->assign('default_form_language', $this->default_form_language); if ($obj->id) { if ($this->product_exists_in_shop) { // Get all id_product_attribute $attributes = $obj->getAttributesResume($this->context->language->id); if (empty($attributes)) { $attributes[] = array('id_product_attribute' => 0, 'attribute_designation' => ''); } // Get available quantities $available_quantity = array(); $product_designation = array(); foreach ($attributes as $attribute) { // Get available quantity for the current product attribute in the current shop $available_quantity[$attribute['id_product_attribute']] = StockAvailable::getQuantityAvailableByProduct((int) $obj->id, $attribute['id_product_attribute']); // Get all product designation $product_designation[$attribute['id_product_attribute']] = rtrim($obj->name[$this->context->language->id] . ' - ' . $attribute['attribute_designation'], ' - '); } $show_quantities = true; $shop_context = Shop::getContext(); $shop_group = new ShopGroup((int) Shop::getContextShopGroupID()); // if we are in all shops context, it's not possible to manage quantities at this level if (Shop::isFeatureActive() && $shop_context == Shop::CONTEXT_ALL) { $show_quantities = false; } elseif (Shop::isFeatureActive() && $shop_context == Shop::CONTEXT_GROUP) { // if quantities are not shared between shops of the group, it's not possible to manage them at group level if (!$shop_group->share_stock) { $show_quantities = false; } } else { // if quantities are shared between shops of the group, it's not possible to manage them for a given shop if ($shop_group->share_stock) { $show_quantities = false; } } $data->assign('ps_stock_management', Configuration::get('PS_STOCK_MANAGEMENT')); $data->assign('has_attribute', $obj->hasAttributes()); // Check if product has combination, to display the available date only for the product or for each combination if (Combination::isFeatureActive()) { $data->assign('countAttributes', (int) Db::getInstance()->getValue('SELECT COUNT(id_product) FROM ' . _DB_PREFIX_ . 'product_attribute WHERE id_product = ' . (int) $obj->id)); } else { $data->assign('countAttributes', false); } // if advanced stock management is active, checks associations $advanced_stock_management_warning = false; if (Configuration::get('PS_ADVANCED_STOCK_MANAGEMENT') && $obj->advanced_stock_management) { $p_attributes = Product::getProductAttributesIds($obj->id); $warehouses = array(); if (!$p_attributes) { $warehouses[] = Warehouse::getProductWarehouseList($obj->id, 0); } foreach ($p_attributes as $p_attribute) { $ws = Warehouse::getProductWarehouseList($obj->id, $p_attribute['id_product_attribute']); if ($ws) { $warehouses[] = $ws; } } $warehouses = Tools::arrayUnique($warehouses); if (empty($warehouses)) { $advanced_stock_management_warning = true; } } if ($advanced_stock_management_warning) { $this->displayWarning($this->l('If you wish to use the advanced stock management, you must:')); $this->displayWarning('- ' . $this->l('associate your products with warehouses.')); $this->displayWarning('- ' . $this->l('associate your warehouses with carriers.')); $this->displayWarning('- ' . $this->l('associate your warehouses with the appropriate shops.')); } $pack_quantity = null; // if product is a pack if (Pack::isPack($obj->id)) { $items = Pack::getItems((int) $obj->id, Configuration::get('PS_LANG_DEFAULT')); // gets an array of quantities (quantity for the product / quantity in pack) $pack_quantities = array(); foreach ($items as $item) { if (!$item->isAvailableWhenOutOfStock((int) $item->out_of_stock)) { $pack_id_product_attribute = Product::getDefaultAttribute($item->id, 1); $pack_quantities[] = Product::getQuantity($item->id, $pack_id_product_attribute) / ($item->pack_quantity !== 0 ? $item->pack_quantity : 1); } } // gets the minimum if (count($pack_quantities)) { $pack_quantity = $pack_quantities[0]; foreach ($pack_quantities as $value) { if ($pack_quantity > $value) { $pack_quantity = $value; } } } if (!Warehouse::getPackWarehouses((int) $obj->id)) { $this->displayWarning($this->l('You must have a common warehouse between this pack and its product.')); } } $data->assign(array('attributes' => $attributes, 'available_quantity' => $available_quantity, 'pack_quantity' => $pack_quantity, 'stock_management_active' => Configuration::get('PS_ADVANCED_STOCK_MANAGEMENT'), 'product_designation' => $product_designation, 'product' => $obj, 'show_quantities' => $show_quantities, 'order_out_of_stock' => Configuration::get('PS_ORDER_OUT_OF_STOCK'), 'token_preferences' => Tools::getAdminTokenLite('AdminPPreferences'), 'token' => $this->token, 'languages' => $this->_languages, 'id_lang' => $this->context->language->id)); } else { $this->displayWarning($this->l('You must save the product in this shop before managing quantities.')); } } else { $this->displayWarning($this->l('You must save this product before managing quantities.')); } $this->tpl_form_vars['custom_form'] = $data->fetch(); }
public function postProcess() { if (!Combination::isFeatureActive()) { return; } if (!Tools::getValue($this->identifier) && Tools::getValue('id_attribute') && !Tools::getValue('attributeOrderby')) { // Override var of Controller $this->table = 'attribute'; $this->className = 'Attribute'; $this->identifier = 'id_attribute'; } // If it's an attribute, load object Attribute() if (Tools::getValue('updateattribute') || Tools::isSubmit('deleteattribute') || Tools::isSubmit('submitAddattribute')) { if ($this->tabAccess['edit'] !== '1') { $this->errors[] = Tools::displayError('You do not have permission to edit this.'); } else { if (!($object = new Attribute((int) Tools::getValue($this->identifier)))) { $this->errors[] = Tools::displayError('An error occurred while updating the status for an object.') . ' <b>' . $this->table . '</b> ' . Tools::displayError('(cannot load object)'); } } if (Tools::getValue('position') !== false && Tools::getValue('id_attribute')) { $_POST['id_attribute_group'] = $object->id_attribute_group; if (!$object->updatePosition((int) Tools::getValue('way'), (int) Tools::getValue('position'))) { $this->errors[] = Tools::displayError('Failed to update the position.'); } else { Tools::redirectAdmin(self::$currentIndex . '&conf=5&token=' . Tools::getAdminTokenLite('AdminAttributesGroups') . '#details_details_' . $object->id_attribute_group); } } else { if (Tools::isSubmit('deleteattribute') && Tools::getValue('id_attribute')) { if (!$object->delete()) { $this->errors[] = Tools::displayError('Failed to delete the attribute.'); } else { Tools::redirectAdmin(self::$currentIndex . '&conf=1&token=' . Tools::getAdminTokenLite('AdminAttributesGroups')); } } else { if (Tools::isSubmit('submitAddattribute')) { Hook::exec('actionObjectAttributeAddBefore'); $this->action = 'save'; $id_attribute = (int) Tools::getValue('id_attribute'); // Adding last position to the attribute if not exist if ($id_attribute <= 0) { $sql = 'SELECT `position`+1 FROM `' . _DB_PREFIX_ . 'attribute` WHERE `id_attribute_group` = ' . (int) Tools::getValue('id_attribute_group') . ' ORDER BY position DESC'; // set the position of the new group attribute in $_POST for postProcess() method $_POST['position'] = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } $_POST['id_parent'] = 0; $this->processSave($this->token); } } } } else { if (Tools::getValue('submitDel' . $this->table)) { if ($this->tabAccess['delete'] === '1') { if (isset($_POST[$this->table . 'Box'])) { $object = new $this->className(); if ($object->deleteSelection($_POST[$this->table . 'Box'])) { Tools::redirectAdmin(self::$currentIndex . '&conf=2' . '&token=' . $this->token); } $this->errors[] = Tools::displayError('An error occurred while deleting this selection.'); } else { $this->errors[] = Tools::displayError('You must select at least one element to delete.'); } } else { $this->errors[] = Tools::displayError('You do not have permission to delete this.'); } // clean position after delete AttributeGroup::cleanPositions(); } else { if (Tools::isSubmit('submitAdd' . $this->table)) { Hook::exec('actionObjectAttributeGroupAddBefore'); $id_attribute_group = (int) Tools::getValue('id_attribute_group'); // Adding last position to the attribute if not exist if ($id_attribute_group <= 0) { $sql = 'SELECT `position`+1 FROM `' . _DB_PREFIX_ . 'attribute_group` ORDER BY position DESC'; // set the position of the new group attribute in $_POST for postProcess() method $_POST['position'] = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } // clean \n\r characters foreach ($_POST as $key => $value) { if (preg_match('/^name_/Ui', $key)) { $_POST[$key] = str_replace('\\n', '', str_replace('\\r', '', $value)); } } parent::postProcess(); } else { parent::postProcess(); } } } }
public static function getAttributes($db, $id_product, $id_lang) { if (!Combination::isFeatureActive()) { return ''; } $attributes = ''; $attributesArray = $db->executeS(' SELECT al.name FROM ' . _DB_PREFIX_ . 'product_attribute pa INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac ON pa.id_product_attribute = pac.id_product_attribute INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang = ' . (int) $id_lang . ') ' . Shop::addSqlAssociation('product_attribute', 'pa') . ' WHERE pa.id_product = ' . (int) $id_product); foreach ($attributesArray as $attribute) { $attributes .= $attribute['name'] . ' '; } return $attributes; }
/** * Get all attributes for a given language / group * * @param integer $id_lang Language id * @param boolean $id_attribute_group Attribute group id * @return array Attributes */ public static function getAttributes($id_lang, $id_attribute_group) { if (!Combination::isFeatureActive()) { return array(); } return Db::getInstance()->executeS(' SELECT * FROM `' . _DB_PREFIX_ . 'attribute` a ' . Shop::addSqlAssociation('attribute', 'a') . ' LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute` AND al.`id_lang` = ' . (int) $id_lang . ') WHERE a.`id_attribute_group` = ' . (int) $id_attribute_group . ' ORDER BY `position` ASC '); }
public function getNewProducts($where, $id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, Context $context = null) { if (!$context) { $context = Context::getContext(); } $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } if (empty($order_by) || $order_by == 'position') { $order_by = 'date_add'; } if (empty($order_way)) { $order_way = 'DESC'; } if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd') { $order_by_prefix = 'p'; } else { if ($order_by == 'name') { $order_by_prefix = 'pl'; } } if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'AND p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ' )'; } if (strpos($order_by, '.') > 0) { $order_by = explode('.', $order_by); $order_by_prefix = $order_by[0]; $order_by = $order_by[1]; } if ($count) { $sql = 'SELECT COUNT(p.`id_product`) AS nb FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' WHERE product_shop.`active` = 1 AND product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ' . $sql_groups; return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } $sql = new DbQuery(); $sql->select('p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new'); $sql->from('product', 'p'); $sql->join(Shop::addSqlAssociation('product', 'p')); $sql->leftJoin('product_lang', 'pl', ' p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl')); $sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`'); $sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1')); $sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang); $sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`'); $sql->where('product_shop.`active` = 1'); if ($front) { $sql->where('product_shop.`visibility` IN ("both", "catalog")'); } $sql->where('product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '"'); if (Group::isFeatureActive()) { $sql->where('p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE ' . $where . ' cg.`id_group` ' . $sql_groups . ' )'); } $sql->groupBy('product_shop.id_product'); $sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way)); $sql->limit($nb_products, $page_number * $nb_products); if (Combination::isFeatureActive()) { $sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute'); $sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`'); $sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1')); } $sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0)); $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($order_by == 'price') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return false; } $products_ids = array(); foreach ($result as $row) { $products_ids[] = $row['id_product']; } // Thus you can avoid one query per product, because there will be only one query for all the products of the cart Product::cacheFrontFeatures($products_ids, $id_lang); return Product::getProductsProperties((int) $id_lang, $result); }
public static function getCrossSells($id_product, $id_lang, $limit = 12) { if (!$id_product || !$id_lang) { return; } $front = true; if (!in_array(Context::getContext()->controller->controller_type, array('front', 'modulefront'))) { $front = false; } $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order) WHERE o.valid = 1 AND od.product_id = ' . (int) $id_product); if (count($orders)) { $list = ''; foreach ($orders as $order) { $list .= (int) $order['id_order'] . ','; } $list = rtrim($list, ','); $order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT DISTINCT od.product_id, p.id_product, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, cl.link_rewrite category, p.ean13, p.out_of_stock, p.id_category_default ' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute' : '') . ' FROM ' . _DB_PREFIX_ . 'order_detail od LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id) ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) Context::getContext()->shop->id . ')' : '') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id) WHERE od.id_order IN (' . $list . ') AND pl.id_lang = ' . (int) $id_lang . ' AND cl.id_lang = ' . (int) $id_lang . ' AND od.product_id != ' . (int) $id_product . ' AND i.cover = 1 AND product_shop.active = 1' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ORDER BY RAND() LIMIT ' . (int) $limit . ' ', true, false); $tax_calc = Product::getTaxCalculationMethod(); if (is_array($order_products)) { foreach ($order_products as &$order_product) { $order_product['image'] = Context::getContext()->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('medium')); $order_product['link'] = Context::getContext()->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']); if ($tax_calc == 0 || $tax_calc == 2) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null); } elseif ($tax_calc == 1) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null); } } return Product::getProductsProperties($id_lang, $order_products); } } }
public function initContent() { if (!Combination::isFeatureActive()) { $url = '<a href="index.php?tab=AdminPerformance&token=' . Tools::getAdminTokenLite('AdminPerformance') . '#featuresDetachables">' . $this->trans('Performance', array(), 'Admin.Global') . '</a>'; $this->displayWarning(sprintf($this->trans('This feature has been disabled. You can activate it here: %s.', array('%s' => $url), 'Admin.Catalog.Notification'))); return; } // Init toolbar $this->initPageHeaderToolbar(); $this->initGroupTable(); $attributes = Attribute::getAttributes(Context::getContext()->language->id, true); $attribute_js = array(); foreach ($attributes as $k => $attribute) { $attribute_js[$attribute['id_attribute_group']][$attribute['id_attribute']] = $attribute['name']; } $attribute_groups = AttributeGroup::getAttributesGroups($this->context->language->id); $this->product = new Product((int) Tools::getValue('id_product')); $this->context->smarty->assign(array('tax_rates' => $this->product->getTaxesRate(), 'generate' => isset($_POST['generate']) && !count($this->errors), 'combinations_size' => count($this->combinations), 'product_name' => $this->product->name[$this->context->language->id], 'product_reference' => $this->product->reference, 'url_generator' => self::$currentIndex . '&id_product=' . (int) Tools::getValue('id_product') . '&attributegenerator&token=' . Tools::getValue('token'), 'attribute_groups' => $attribute_groups, 'attribute_js' => $attribute_js, 'toolbar_btn' => $this->toolbar_btn, 'toolbar_scroll' => true, 'show_page_header_toolbar' => $this->show_page_header_toolbar, 'page_header_toolbar_title' => $this->page_header_toolbar_title, 'page_header_toolbar_btn' => $this->page_header_toolbar_btn)); }
public function searchProducts($query) { if (version_compare(_PS_VERSION_, '1.5', '<')) { $sql = ' SELECT p.`id_product`, pl.`name`, p.`weight` FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (p.`id_product` = cp.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = "' . (int) $this->context->language->id . '") WHERE pl.`name` LIKE \'%' . pSQL($query) . '%\' OR p.`ean13` LIKE \'%' . pSQL($query) . '%\' OR p.`upc` LIKE \'%' . pSQL($query) . '%\' OR p.`reference` LIKE \'%' . pSQL($query) . '%\' OR p.`supplier_reference` LIKE \'%' . pSQL($query) . '%\' GROUP BY `id_product` ORDER BY pl.`name` ASC '; } else { $sql = new DbQuery(); $sql->select('p.`id_product`, pl.`name`, p.`weight`'); $sql->from('category_product', 'cp'); $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`'); $sql->join(Shop::addSqlAssociation('product', 'p')); $sql->leftJoin('product_lang', 'pl', ' p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl')); $where = 'pl.`name` LIKE \'%' . pSQL($query) . '%\' OR p.`ean13` LIKE \'%' . pSQL($query) . '%\' OR p.`upc` LIKE \'%' . pSQL($query) . '%\' OR p.`reference` LIKE \'%' . pSQL($query) . '%\' OR p.`supplier_reference` LIKE \'%' . pSQL($query) . '%\' OR p.`id_product` IN (SELECT id_product FROM ' . _DB_PREFIX_ . 'product_supplier sp WHERE `product_supplier_reference` LIKE \'%' . pSQL($query) . '%\')'; $sql->groupBy('`id_product`'); $sql->orderBy('pl.`name` ASC'); if (Combination::isFeatureActive()) { $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product` = p.`id_product`'); $sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false)); $where .= ' OR pa.`reference` LIKE \'%' . pSQL($query) . '%\''; } $sql->where($where); } $result = Db::getInstance()->executeS($sql); if (!$result) { return array('found' => false, 'notfound' => $this->l('No product has been found.')); } foreach ($result as &$product) { $product['id_product_attribute'] = Product::getDefaultAttribute($product['id_product']); $product['weight_numeric'] = $product['weight']; $product['weight'] = sprintf('%.3f', $product['weight']) . ' ' . _DPDPOLAND_DEFAULT_WEIGHT_UNIT_; } return array('products' => $result, 'found' => true); }
public function initFieldsetFeaturesDetachables() { $this->fields_form[2]['form'] = array('legend' => array('title' => $this->l('Optional features'), 'image' => '../img/admin/tab-plugins.gif'), 'desc' => $this->l('Some features can be disabled in order to improve performance.'), 'input' => array(array('type' => 'hidden', 'name' => 'features_detachables_up'), array('type' => 'radio', 'label' => $this->l('Combinations'), 'name' => 'combination', 'class' => 't', 'is_bool' => true, 'disabled' => Combination::isCurrentlyUsed(), 'values' => array(array('id' => 'combination_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'combination_0', 'value' => 0, 'label' => $this->l('No'))), 'desc' => $this->l('These features will be disabled')), array('type' => 'radio', 'label' => $this->l('Features'), 'name' => 'feature', 'class' => 't', 'is_bool' => true, 'values' => array(array('id' => 'feature_1', 'value' => 1, 'label' => $this->l('Yes')), array('id' => 'feature_0', 'value' => 0, 'label' => $this->l('No'))), 'desc' => $this->l('These features will be disabled')))); $this->fields_value['combination'] = Combination::isFeatureActive(); $this->fields_value['feature'] = Feature::isFeatureActive(); }
/** * Get all attributes for a given language * * @param integer $id_lang Language id * @param boolean $notNull Get only not null fields if true * @return array Attributes */ public static function getAttributes($id_lang, $not_null = false) { if (!Combination::isFeatureActive()) { return array(); } return Db::getInstance()->executeS(' SELECT DISTINCT ag.*, agl.*, a.`id_attribute`, al.`name`, agl.`name` AS `attribute_group` FROM `' . _DB_PREFIX_ . 'attribute_group` ag LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group` AND agl.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'attribute` a ON a.`id_attribute_group` = ag.`id_attribute_group` LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute` AND al.`id_lang` = ' . (int) $id_lang . ') ' . Shop::addSqlAssociation('attribute_group', 'ag') . ' ' . Shop::addSqlAssociation('attribute', 'a') . ' ' . ($not_null ? 'WHERE a.`id_attribute` IS NOT NULL AND al.`name` IS NOT NULL' : '') . ' ORDER BY agl.`name` ASC, a.`position` ASC '); }
public static function getProductProperties($id_lang, $row, Context $context = null) { if (!$row['id_product']) { return false; } if ($context == null) { $context = Context::getContext(); } // Product::getDefaultAttribute is only called if id_product_attribute is missing from the SQL query at the origin of it: // consider adding it in order to avoid unnecessary queries $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']); if (Combination::isFeatureActive() && (!isset($row['id_product_attribute']) || !$row['id_product_attribute']) && (isset($row['cache_default_attribute']) && ($ipa_default = $row['cache_default_attribute']) !== null || ($ipa_default = Product::getDefaultAttribute($row['id_product'], !$row['allow_oosp'])))) { $row['id_product_attribute'] = $ipa_default; } if (!Combination::isFeatureActive() || !isset($row['id_product_attribute'])) { $row['id_product_attribute'] = 0; } // Tax $usetax = Tax::excludeTaxeOption(); $cache_key = $row['id_product'] . '-' . $row['id_product_attribute'] . '-' . $id_lang . '-' . (int) $usetax; if (isset($row['id_product_pack'])) { $cache_key .= '-pack' . $row['id_product_pack']; } if (isset(self::$producPropertiesCache[$cache_key])) { return self::$producPropertiesCache[$cache_key]; } // Datas $row['category'] = Category::getLinkRewrite((int) $row['id_category_default'], (int) $id_lang); $row['link'] = $context->link->getProductLink((int) $row['id_product'], $row['link_rewrite'], $row['category'], $row['ean13']); $row['attribute_price'] = 0; if (isset($row['id_product_attribute']) && $row['id_product_attribute']) { $row['attribute_price'] = (double) Product::getProductAttributePrice($row['id_product_attribute']); } $row['price_tax_exc'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) && !empty($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, self::$_taxCalculationMethod == PS_TAX_EXC ? 2 : 6); if (self::$_taxCalculationMethod == PS_TAX_EXC) { $row['price_tax_exc'] = Tools::ps_round($row['price_tax_exc'], 2); $row['price'] = Product::getPriceStatic((int) $row['id_product'], true, isset($row['id_product_attribute']) && !empty($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 6); $row['price_without_reduction'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) && !empty($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 2, null, false, false); } else { $row['price'] = Tools::ps_round(Product::getPriceStatic((int) $row['id_product'], true, isset($row['id_product_attribute']) && !empty($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 2), 2); $row['price_without_reduction'] = Product::getPriceStatic((int) $row['id_product'], true, isset($row['id_product_attribute']) && !empty($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 6, null, false, false); } $row['reduction'] = Product::getPriceStatic((int) $row['id_product'], (bool) $usetax, (int) $row['id_product_attribute'], 6, null, true, true, 1, true, null, null, null, $specific_prices); $row['specific_prices'] = $specific_prices; if ($row['id_product_attribute']) { $row['quantity_all_versions'] = $row['quantity']; $row['quantity'] = Product::getQuantity((int) $row['id_product'], $row['id_product_attribute'], isset($row['cache_is_pack']) ? $row['cache_is_pack'] : null); } else { $row['quantity'] = Product::getQuantity((int) $row['id_product']); } $row['id_image'] = Product::defineProductImage($row, $id_lang); $row['features'] = Product::getFrontFeaturesStatic((int) $id_lang, $row['id_product']); $row['attachments'] = array(); if (!isset($row['cache_has_attachments']) || $row['cache_has_attachments']) { $row['attachments'] = Product::getAttachmentsStatic((int) $id_lang, $row['id_product']); } $row['virtual'] = !isset($row['is_virtual']) || $row['is_virtual'] ? 1 : 0; // Pack management $row['pack'] = !isset($row['cache_is_pack']) ? Pack::isPack($row['id_product']) : (int) $row['cache_is_pack']; $row['packItems'] = $row['pack'] ? Pack::getItemTable($row['id_product'], $id_lang) : array(); $row['nopackprice'] = $row['pack'] ? Pack::noPackPrice($row['id_product']) : 0; if ($row['pack'] && !Pack::isInStock($row['id_product'])) { $row['quantity'] = 0; } self::$producPropertiesCache[$cache_key] = $row; return self::$producPropertiesCache[$cache_key]; }
/** * @param array $products_id an array of product ids * @return array */ protected function getOrderProducts(array $products_id) { $q_orders = 'SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order) WHERE o.valid = 1 AND od.product_id IN (' . implode(',', $products_id) . ')'; $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($q_orders); $final_products_list = array(); if (count($orders) > 0) { $list = ''; foreach ($orders as $order) { $list .= (int) $order['id_order'] . ','; } $list = rtrim($list, ','); $list_product_ids = join(',', $products_id); if (Group::isFeatureActive()) { $sql_groups_join = ' LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = product_shop.id_category_default AND cp.id_product = product_shop.id_product) LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)'; $groups = FrontController::getCurrentCustomerGroups(); $sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '=' . (int) Group::getCurrent()->id); } $order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT DISTINCT od.product_id, pl.name, pl.description_short, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM ' . _DB_PREFIX_ . 'order_detail od LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id) ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $this->context->shop) : Product::sqlStock('p', 'product', false, $this->context->shop)) . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id) ' . (Group::isFeatureActive() ? $sql_groups_join : '') . ' WHERE od.id_order IN (' . $list . ') AND pl.id_lang = ' . (int) $this->context->language->id . ' AND cl.id_lang = ' . (int) $this->context->language->id . ' AND od.product_id NOT IN (' . $list_product_ids . ') AND i.cover = 1 AND product_shop.active = 1 ' . (Group::isFeatureActive() ? $sql_groups_where : '') . ' ORDER BY RAND() LIMIT ' . (int) Configuration::get('CROSSSELLING_NBR')); $tax_calc = Product::getTaxCalculationMethod(); foreach ($order_products as &$order_product) { $order_product['id_product'] = (int) $order_product['product_id']; $order_product['image'] = $this->context->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('home')); $order_product['link'] = $this->context->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']); if (Configuration::get('CROSSSELLING_DISPLAY_PRICE') && ($tax_calc == 0 || $tax_calc == 2)) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null); } elseif (Configuration::get('CROSSSELLING_DISPLAY_PRICE') && $tax_calc == 1) { $order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null); } $order_product['allow_oosp'] = Product::isAvailableWhenOutOfStock((int) $order_product['out_of_stock']); if (!isset($final_products_list[$order_product['product_id'] . '-' . $order_product['id_image']])) { $final_products_list[$order_product['product_id'] . '-' . $order_product['id_image']] = $order_product; } } } return $final_products_list; }
protected function _getProducts($categoryIds = array(), $on_condition = 'all', $on_sale = 2, $on_new = 2, $on_discount = 2, $id_lang, $p, $n, $order_by = null, $order_way = null, $beginning = null, $ending = null, $deal = false, $get_total = false, $active = true, $random = false, $random_number_products = 1, Context $context = null) { if (!$categoryIds) { return array(); } $where = ""; if ($on_condition != 'all') { $where .= " AND p.condition = '" . $on_condition . "' "; } if ($on_sale != 2) { $where .= " AND p.on_sale = '" . $on_sale . "' "; } Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? $PS_NB_DAYS_NEW_PRODUCT = (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : ($PS_NB_DAYS_NEW_PRODUCT = 20); if ($on_new == 0) { $where .= " AND product_shop.`date_add` <= '" . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . "' "; } elseif ($on_new == 1) { $where .= " AND product_shop.`date_add` > '" . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . "' "; } $ids_product = ''; if ($on_discount == 0) { $current_date = date('Y-m-d H:i:s'); $product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal); if ($product_reductions) { $ids_product = ' AND ('; foreach ($product_reductions as $product_reduction) { $ids_product .= '( product_shop.`id_product` != ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' OR product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') AND'; } $ids_product = rtrim($ids_product, 'AND') . ')'; } } elseif ($on_discount == 1) { $current_date = date('Y-m-d H:i:s'); $product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal); if ($product_reductions) { $ids_product = ' AND ('; foreach ($product_reductions as $product_reduction) { $ids_product .= '( product_shop.`id_product` = ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' AND product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') OR'; } $ids_product = rtrim($ids_product, 'OR') . ')'; } else { if ($deal == true) { return array(); } } } else { if ($order_by == 'discount') { $current_date = date('Y-m-d H:i:s'); $product_reductions = $this->_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true, 0, $deal); if ($product_reductions) { $ids_product = ' AND ('; foreach ($product_reductions as $product_reduction) { $ids_product .= '( product_shop.`id_product` = ' . (int) $product_reduction['id_product'] . ($product_reduction['id_product_attribute'] ? ' AND product_attribute_shop.`id_product_attribute`=' . (int) $product_reduction['id_product_attribute'] : '') . ') OR'; } $ids_product = rtrim($ids_product, 'OR') . ')'; } } } if ($ids_product) { $where .= $ids_product; } if (!$context) { $context = Context::getContext(); } $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if ($p < 1) { $p = 1; } if (empty($order_by)) { $order_by = 'position'; } else { $order_by = strtolower($order_by); } if (empty($order_way)) { $order_way = 'ASC'; } $order_by_prefix = false; $addJoin = ''; $addSelect = ''; if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') { $order_by_prefix = 'p'; } elseif ($order_by == 'name') { $order_by_prefix = 'pl'; } elseif ($order_by == 'manufacturer' || $order_by == 'manufacturer_name') { $order_by_prefix = 'm'; $order_by = 'name'; } elseif ($order_by == 'position') { $order_by_prefix = 'cp'; } elseif ($order_by == 'discount') { $order_by_prefix = 'sp'; $order_by = 'reduction'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.`id_product` = sp.`id_product` '; $addSelect = ', sp.reduction, sp.`from`, sp.`to`'; } elseif ($order_by == 'review') { $order_by_prefix = ''; $order_by = 'total_review'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pr ON pr.`id_product` = p.`id_product` '; $addSelect = ', COUNT(pr.grade) as total_review'; } elseif ($order_by == 'view') { $order_by_prefix = ''; $order_by = 'total_view'; $addJoin = ' LEFT JOIN ' . _DB_PREFIX_ . 'simplecategory_product_view as pv ON pv.`product_id` = p.`id_product` '; $addSelect = ', pv.total as total_view'; } elseif ($order_by == 'rate') { $order_by_prefix = ''; $order_by = 'total_avg'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pr ON pr.`id_product` = p.`id_product` '; $addSelect = ', (SUM(pr.`grade`) / COUNT(pr.`grade`)) AS total_avg'; } elseif ($order_by == 'seller') { $order_by_prefix = ''; $order_by = 'sales'; $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'product_sale` ps ON ps.`id_product` = p.`id_product` '; $addSelect = ', ps.`quantity` AS sales'; } if ($order_by != 'reduction' && $on_discount != 2) { $addJoin = ' LEFT JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.`id_product` = sp.`id_product` '; $addSelect = ', sp.reduction, sp.`from`, sp.`to`'; } if ($order_by == 'price') { $order_by = 'orderprice'; } if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } $id_supplier = (int) Tools::getValue('id_supplier'); if ($get_total) { $sql = 'SELECT COUNT(cp.`id_product`) AS total FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' ' . $addJoin . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product` WHERE cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . ($ids_product ? $ids_product : '') . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : ''); return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } $sql = 'SELECT DISTINCT p.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`, MAX(image_shop.`id_image`) id_image, il.`legend`, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable, IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock, product_shop.`date_add` > "' . date('Y-m-d', strtotime('-' . $PS_NB_DAYS_NEW_PRODUCT . ' DAY')) . '" as `new`, product_shop.`on_sale`, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, product_shop.price AS orderprice ' . $addSelect . ' FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . $addJoin . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) : Product::sqlStock('p', 'product', false, Context::getContext()->shop)) . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`) ' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . ' AND cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '') . ' GROUP BY product_shop.id_product'; if ($random === true) { $sql .= ' ORDER BY RAND() LIMIT ' . (int) $random_number_products; } else { $sql .= ' ORDER BY ' . (!empty($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . bqSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($order_by == 'orderprice') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return array(); } return Product::getProductsProperties($id_lang, $result); }
public function getAttributesGroups($id_lang) { if (!Combination::isFeatureActive()) { return array(); } $sql = 'SELECT ag.`id_attribute_group`, ag.`is_color_group`, agl.`name` AS group_name, agl.`public_name` AS public_group_name, a.`id_attribute`, al.`name` AS attribute_name, a.`color` AS attribute_color, product_attribute_shop.`id_product_attribute`, IFNULL(stock.quantity, 0) + IFNULL(stock.quantity_remainder, 0) as quantity, product_attribute_shop.`price`, product_attribute_shop.`ecotax`, product_attribute_shop.`weight`, product_attribute_shop.`default_on`, pa.`reference`, product_attribute_shop.`unit_price_impact`, product_attribute_shop.`minimal_quantity`, product_attribute_shop.`minimal_quantity_fractional`, product_attribute_shop.`available_date`, ag.`group_type` FROM `' . _DB_PREFIX_ . 'product_attribute` pa ' . Shop::addSqlAssociation('product_attribute', 'pa') . ' ' . Product::sqlStock('pa', 'pa') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`) LEFT JOIN `' . _DB_PREFIX_ . 'attribute` a ON (a.`id_attribute` = pac.`id_attribute`) LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group` ag ON (ag.`id_attribute_group` = a.`id_attribute_group`) LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute`) LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group`) ' . Shop::addSqlAssociation('attribute', 'a') . ' WHERE pa.`id_product` = ' . (int) $this->id . ' AND al.`id_lang` = ' . (int) $id_lang . ' AND agl.`id_lang` = ' . (int) $id_lang . ' GROUP BY id_attribute_group, id_product_attribute ORDER BY ag.`position` ASC, a.`position` ASC, agl.`name` ASC'; return Db::getInstance()->executeS($sql); }
/** * Check if the given name is an Attribute within the given AttributeGroup * * @param int $idAttributeGroup AttributeGroup * @param string $name Attribute name * @param int $idLang Language ID * * @return array|bool */ public static function isAttribute($idAttributeGroup, $name, $idLang) { if (!Combination::isFeatureActive()) { return array(); } $result = Db::getInstance()->getValue(' SELECT COUNT(*) FROM `' . _DB_PREFIX_ . 'attribute_group` ag LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group` AND agl.`id_lang` = ' . (int) $idLang . ') LEFT JOIN `' . _DB_PREFIX_ . 'attribute` a ON a.`id_attribute_group` = ag.`id_attribute_group` LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute` AND al.`id_lang` = ' . (int) $idLang . ') ' . Shop::addSqlAssociation('attribute_group', 'ag') . ' ' . Shop::addSqlAssociation('attribute', 'a') . ' WHERE al.`name` = \'' . pSQL($name) . '\' AND ag.`id_attribute_group` = ' . (int) $idAttributeGroup . ' ORDER BY agl.`name` ASC, a.`position` ASC '); return (int) $result > 0; }
public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null) { $context = Context::getContext(); if ($page_number < 0) { $page_number = 0; } if ($nb_products < 1) { $nb_products = 10; } $final_order_by = $order_by; $order_table = ''; if (is_null($order_by)) { $order_by = 'quantity'; $order_table = 'ps'; } if ($order_by == 'date_add' || $order_by == 'date_upd') { $order_table = 'product_shop'; } if (is_null($order_way) || $order_by == 'sales') { $order_way = 'DESC'; } $interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20; // no group by needed : there's only one attribute with default_on=1 for a given id_product + shop // same for image with cover=1 $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, ' . (Combination::isFeatureActive() ? 'product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute,' : '') . ' pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer, image_shop.`id_image` id_image, il.`legend`, ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`, DATEDIFF(p.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) $interval . ' DAY)) > 0 AS new' . ' FROM `' . _DB_PREFIX_ . 'product_sale` ps LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product` ' . Shop::addSqlAssociation('product', 'p', false); if (Combination::isFeatureActive()) { $sql .= ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')'; } $sql .= ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`) AND tr.`id_country` = ' . (int) $context->country->id . ' AND tr.`id_state` = 0 LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`) ' . Product::sqlStock('p', 0); $sql .= ' WHERE product_shop.`active` = 1 AND p.`visibility` != \'none\''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql .= ' AND EXISTS(SELECT 1 FROM `' . _DB_PREFIX_ . 'category_product` cp JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ') WHERE cp.`id_product` = p.`id_product`)'; } if ($final_order_by != 'price') { $sql .= ' ORDER BY ' . (!empty($order_table) ? '`' . pSQL($order_table) . '`.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if ($final_order_by == 'price') { Tools::orderbyPrice($result, $order_way); } if (!$result) { return false; } return Product::getProductsProperties($id_lang, $result); }
public function getProductByFilters($selected_filters = array()) { global $cookie; if (!empty($this->products)) { return $this->products; } $home_category = Configuration::get('PS_HOME_CATEGORY'); /* If the current category isn't defined or if it's homepage, we have nothing to display */ $id_parent = (int) Tools::getValue('id_category', Tools::getValue('id_category_layered', $home_category)); if ($id_parent == $home_category) { return false; } $alias_where = 'p'; if (version_compare(_PS_VERSION_, '1.5', '>')) { $alias_where = 'product_shop'; } $query_filters_where = ' AND ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")'; $query_filters_from = ''; $parent = new Category((int) $id_parent); foreach ($selected_filters as $key => $filter_values) { if (!count($filter_values)) { continue; } preg_match('/^(.*[^_0-9])/', $key, $res); $key = $res[1]; switch ($key) { case 'id_feature': $sub_queries = array(); foreach ($filter_values as $filter_value) { $filter_value_array = explode('_', $filter_value); if (!isset($sub_queries[$filter_value_array[0]])) { $sub_queries[$filter_value_array[0]] = array(); } $sub_queries[$filter_value_array[0]][] = 'fp.`id_feature_value` = ' . (int) $filter_value_array[1]; } foreach ($sub_queries as $sub_query) { $query_filters_where .= ' AND p.id_product IN (SELECT `id_product` FROM `' . _DB_PREFIX_ . 'feature_product` fp WHERE '; $query_filters_where .= implode(' OR ', $sub_query) . ') '; } break; case 'id_attribute_group': $sub_queries = array(); foreach ($filter_values as $filter_value) { $filter_value_array = explode('_', $filter_value); if (!isset($sub_queries[$filter_value_array[0]])) { $sub_queries[$filter_value_array[0]] = array(); } $sub_queries[$filter_value_array[0]][] = 'pac.`id_attribute` = ' . (int) $filter_value_array[1]; } foreach ($sub_queries as $sub_query) { $query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product` FROM `' . _DB_PREFIX_ . 'product_attribute_combination` pac LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (pa.`id_product_attribute` = pac.`id_product_attribute`)' . Shop::addSqlAssociation('product_attribute', 'pa') . ' WHERE ' . implode(' OR ', $sub_query) . ') '; } break; case 'category': $query_filters_where .= ' AND p.id_product IN (SELECT id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE '; foreach ($selected_filters['category'] as $id_category) { $query_filters_where .= 'cp.`id_category` = ' . (int) $id_category . ' OR '; } $query_filters_where = rtrim($query_filters_where, 'OR ') . ')'; break; case 'quantity': if (count($selected_filters['quantity']) == 2) { break; } $query_filters_where .= ' AND sa.quantity ' . (!$selected_filters['quantity'][0] ? '<=' : '>') . ' 0 '; $query_filters_from .= 'LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa ON (sa.id_product = p.id_product ' . StockAvailable::addSqlShopRestriction(null, null, 'sa') . ') '; break; case 'manufacturer': $query_filters_where .= ' AND p.id_manufacturer IN (' . implode($selected_filters['manufacturer'], ',') . ')'; break; case 'condition': if (count($selected_filters['condition']) == 3) { break; } $query_filters_where .= ' AND ' . $alias_where . '.condition IN ('; foreach ($selected_filters['condition'] as $cond) { $query_filters_where .= '\'' . pSQL($cond) . '\','; } $query_filters_where = rtrim($query_filters_where, ',') . ')'; break; case 'weight': if ($selected_filters['weight'][0] != 0 || $selected_filters['weight'][1] != 0) { $query_filters_where .= ' AND p.`weight` BETWEEN ' . (double) ($selected_filters['weight'][0] - 0.001) . ' AND ' . (double) ($selected_filters['weight'][1] + 0.001); } break; case 'price': if (isset($selected_filters['price'])) { if ($selected_filters['price'][0] !== '' || $selected_filters['price'][1] !== '') { $price_filter = array(); $price_filter['min'] = (double) $selected_filters['price'][0]; $price_filter['max'] = (double) $selected_filters['price'][1]; } } else { $price_filter = false; } break; } } $context = Context::getContext(); $id_currency = (int) $context->currency->id; $price_filter_query_in = ''; // All products with price range between price filters limits $price_filter_query_out = ''; // All products with a price filters limit on it price range if (isset($price_filter) && $price_filter) { $price_filter_query_in = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi ON ( psi.price_min <= ' . (int) $price_filter['max'] . ' AND psi.price_max >= ' . (int) $price_filter['min'] . ' AND psi.`id_product` = p.`id_product` AND psi.`id_shop` = ' . (int) $context->shop->id . ' AND psi.`id_currency` = ' . $id_currency . ' )'; $price_filter_query_out = 'INNER JOIN `' . _DB_PREFIX_ . 'layered_price_index` psi ON ((psi.price_min < ' . (int) $price_filter['min'] . ' AND psi.price_max > ' . (int) $price_filter['min'] . ') OR (psi.price_max > ' . (int) $price_filter['max'] . ' AND psi.price_min < ' . (int) $price_filter['max'] . ')) AND psi.`id_product` = p.`id_product` AND psi.`id_shop` = ' . (int) $context->shop->id . ' AND psi.`id_currency` = ' . $id_currency; } $query_filters_from .= Shop::addSqlAssociation('product', 'p'); Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DROP TEMPORARY TABLE IF EXISTS ' . _DB_PREFIX_ . 'cat_filter_restriction', false); if (empty($selected_filters['category'])) { /* Create the table which contains all the id_product in a cat or a tree */ Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('CREATE TEMPORARY TABLE ' . _DB_PREFIX_ . 'cat_filter_restriction ENGINE=MEMORY SELECT cp.id_product, MIN(cp.position) position FROM ' . _DB_PREFIX_ . 'category_product cp INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category AND ' . (Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= ' . (int) $parent->nleft . ' AND c.nright <= ' . (int) $parent->nright : 'c.id_category = ' . (int) $id_parent) . ' AND c.active = 1) JOIN `' . _DB_PREFIX_ . 'product` p USING (id_product) ' . $price_filter_query_in . ' ' . $query_filters_from . ' WHERE 1 ' . $query_filters_where . ' GROUP BY cp.id_product ORDER BY position, id_product', false); } else { $categories = array_map('intval', $selected_filters['category']); Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('CREATE TEMPORARY TABLE ' . _DB_PREFIX_ . 'cat_filter_restriction ENGINE=MEMORY SELECT cp.id_product, MIN(cp.position) position FROM ' . _DB_PREFIX_ . 'category_product cp JOIN `' . _DB_PREFIX_ . 'product` p USING (id_product) ' . $price_filter_query_in . ' ' . $query_filters_from . ' WHERE cp.`id_category` IN (' . implode(',', $categories) . ') ' . $query_filters_where . ' GROUP BY cp.id_product ORDER BY position, id_product', false); } Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('ALTER TABLE ' . _DB_PREFIX_ . 'cat_filter_restriction ADD PRIMARY KEY (id_product), ADD KEY (position, id_product) USING BTREE', false); if (isset($price_filter) && $price_filter) { static $ps_layered_filter_price_usetax = null; static $ps_layered_filter_price_rounding = null; if ($ps_layered_filter_price_usetax === null) { $ps_layered_filter_price_usetax = Configuration::get('PS_LAYERED_FILTER_PRICE_USETAX'); } if ($ps_layered_filter_price_rounding === null) { $ps_layered_filter_price_rounding = Configuration::get('PS_LAYERED_FILTER_PRICE_ROUNDING'); } if (empty($selected_filters['category'])) { $all_products_out = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT p.`id_product` id_product FROM `' . _DB_PREFIX_ . 'product` p JOIN ' . _DB_PREFIX_ . 'category_product cp USING (id_product) INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category AND ' . (Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= ' . (int) $parent->nleft . ' AND c.nright <= ' . (int) $parent->nright : 'c.id_category = ' . (int) $id_parent) . ' AND c.active = 1) ' . $price_filter_query_out . ' ' . $query_filters_from . ' WHERE 1 ' . $query_filters_where . ' GROUP BY cp.id_product'); } else { $all_products_out = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT p.`id_product` id_product FROM `' . _DB_PREFIX_ . 'product` p JOIN ' . _DB_PREFIX_ . 'category_product cp USING (id_product) ' . $price_filter_query_out . ' ' . $query_filters_from . ' WHERE cp.`id_category` IN (' . implode(',', $categories) . ') ' . $query_filters_where . ' GROUP BY cp.id_product'); } /* for this case, price could be out of range, so we need to compute the real price */ foreach ($all_products_out as $product) { $price = Product::getPriceStatic($product['id_product'], $ps_layered_filter_price_usetax); if ($ps_layered_filter_price_rounding) { $price = (int) $price; } if ($price < $price_filter['min'] || $price > $price_filter['max']) { // out of range price, exclude the product $product_id_delete_list[] = (int) $product['id_product']; } } if (!empty($product_id_delete_list)) { Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('DELETE FROM ' . _DB_PREFIX_ . 'cat_filter_restriction WHERE id_product IN (' . implode(',', $product_id_delete_list) . ')'); } } $this->nbr_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'cat_filter_restriction'); if ($this->nbr_products == 0) { $this->products = array(); } else { $n = (int) Tools::getValue('n', Configuration::get('PS_PRODUCTS_PER_PAGE')); $nb_day_new_product = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20; if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true) { $this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT p.*, ' . ($alias_where == 'p' ? '' : 'product_shop.*,') . ' ' . $alias_where . '.id_category_default, pl.*, image_shop.`id_image` id_image, il.legend, m.name manufacturer_name, ' . (Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '') . ' DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity' . (Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . ' FROM ' . _DB_PREFIX_ . 'cat_filter_restriction cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ' AND pl.id_lang = ' . (int) $cookie->id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) ' . Product::sqlStock('p', 0) . ' WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog") ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' , cp.id_product' . ' LIMIT ' . (((int) $this->page - 1) * $n . ',' . $n)); } else { $this->products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(' SELECT p.*, ' . ($alias_where == 'p' ? '' : 'product_shop.*,') . ' ' . $alias_where . '.id_category_default, pl.*, MAX(image_shop.`id_image`) id_image, il.legend, m.name manufacturer_name, ' . (Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '') . ' DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' FROM ' . _DB_PREFIX_ . 'cat_filter_restriction cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id) : '') . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . Shop::addSqlRestrictionOnLang('pl') . ' AND pl.id_lang = ' . (int) $cookie->id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ') LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) ' . Product::sqlStock('p', 0) . ' WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' , cp.id_product' . ' LIMIT ' . (((int) $this->page - 1) * $n . ',' . $n)); } } if (Tools::getProductsOrder('by', Tools::getValue('orderby'), true) == 'p.price') { Tools::orderbyPrice($this->products, Tools::getProductsOrder('way', Tools::getValue('orderway'))); } return $this->products; }
/** * Returns category products * * @param int $id_lang Language ID * @param int $p Page number * @param int $n Number of products per page * @param string|null $order_by ORDER BY column * @param string|null $order_way Order way * @param bool $get_total If set to true, returns the total number of results only * @param bool $active If set to true, finds only active products * @param bool $random If true, sets a random filter for returned products * @param int $random_number_products Number of products to return if random is activated * @param bool $check_access If set tot rue, check if the current customer * can see products from this category * @param Context|null $context * * @return array|int|false Products, number of products or false (no access) * @throws PrestaShopDatabaseException */ public function getProducts($id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } if ($check_access && !$this->checkAccess($context->customer->id)) { return false; } $front = in_array($context->controller->controller_type, array('front', 'modulefront')); $id_supplier = (int) Tools::getValue('id_supplier'); /** Return only the number of products */ if ($get_total) { $sql = 'SELECT COUNT(cp.`id_product`) AS total FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product` WHERE cp.`id_category` = ' . (int) $this->id . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : ''); return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql); } if ($p < 1) { $p = 1; } /** Tools::strtolower is a fix for all modules which are now using lowercase values for 'orderBy' parameter */ $order_by = Validate::isOrderBy($order_by) ? Tools::strtolower($order_by) : 'position'; $order_way = Validate::isOrderWay($order_way) ? Tools::strtoupper($order_way) : 'ASC'; $order_by_prefix = false; if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd') { $order_by_prefix = 'p'; } elseif ($order_by == 'name') { $order_by_prefix = 'pl'; } elseif ($order_by == 'manufacturer' || $order_by == 'manufacturer_name') { $order_by_prefix = 'm'; $order_by = 'name'; } elseif ($order_by == 'position') { $order_by_prefix = 'cp'; } if ($order_by == 'price') { $order_by = 'orderprice'; } $nb_days_new_product = Configuration::get('PS_NB_DAYS_NEW_PRODUCT'); if (!Validate::isUnsignedInt($nb_days_new_product)) { $nb_days_new_product = 20; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '') . ', pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image, il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB("' . date('Y-m-d') . ' 00:00:00", INTERVAL ' . (int) $nb_days_new_product . ' DAY)) > 0 AS new, product_shop.price AS orderprice FROM `' . _DB_PREFIX_ . 'category_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product` ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? ' LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) $context->shop->id . ')' : '') . ' ' . Product::sqlStock('p', 0) . ' LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $context->shop->id . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . ' AND cp.`id_category` = ' . (int) $this->id . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : ''); if ($random === true) { $sql .= ' ORDER BY RAND() LIMIT ' . (int) $random_number_products; } else { $sql .= ' ORDER BY ' . (!empty($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . bqSQL($order_by) . '` ' . pSQL($order_way) . ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n; } $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql, true, false); if (!$result) { return array(); } if ($order_by == 'orderprice') { Tools::orderbyPrice($result, $order_way); } /** Modify SQL result */ return Product::getProductsProperties($id_lang, $result); }
public function initContent() { if (!Combination::isFeatureActive()) { $this->displayWarning($this->l('This feature has been disabled, you can activate it at:') . ' <a href="index.php?tab=AdminPerformance&token=' . Tools::getAdminTokenLite('AdminPerformance') . '#featuresDetachables">' . $this->l('Performance') . '</a>'); return; } // Init toolbar $this->initToolbarTitle(); $this->initToolbar(); $this->initGroupTable(); $js_attributes = AdminAttributeGeneratorController::displayAndReturnAttributeJs(); $attribute_groups = AttributeGroup::getAttributesGroups($this->context->language->id); $this->product = new Product((int) Tools::getValue('id_product')); $this->context->smarty->assign(array('tax_rates' => $this->product->getTaxesRate(), 'generate' => isset($_POST['generate']) && !count($this->errors), 'combinations_size' => count($this->combinations), 'product_name' => $this->product->name[$this->context->language->id], 'product_reference' => $this->product->reference, 'url_generator' => self::$currentIndex . '&id_product=' . (int) Tools::getValue('id_product') . '&attributegenerator&token=' . Tools::getValue('token'), 'attribute_groups' => $attribute_groups, 'attribute_js' => $js_attributes, 'toolbar_btn' => $this->toolbar_btn, 'toolbar_scroll' => true, 'title' => $this->toolbar_title)); }
public static function getProducts($id_supplier, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $active_category = true) { /* * EU-Legal * get standard shipping time from database pl.* */ $context = Context::getContext(); $front = true; if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) { $front = false; } if ($p < 1) { $p = 1; } if (empty($order_by) || $order_by == 'position') { $order_by = 'name'; } if (empty($order_way)) { $order_way = 'ASC'; } if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { die(Tools::displayError()); } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'WHERE cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'); } /* Return only the number of products */ if ($get_total) { return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue(' SELECT COUNT(DISTINCT ps.`id_product`) FROM `' . _DB_PREFIX_ . 'product_supplier` ps JOIN `' . _DB_PREFIX_ . 'product` p ON (ps.`id_product`= p.`id_product`) ' . Shop::addSqlAssociation('product', 'p') . ' WHERE ps.`id_supplier` = ' . (int) $id_supplier . ' AND ps.id_product_attribute = 0 ' . ($active ? ' AND product_shop.`active` = 1' : '') . ' ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' AND p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_product` cp ' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)' : '') . ' ' . ($active_category ? ' INNER JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1' : '') . ' ' . $sql_groups . ' )'); } $nb_days_new_product = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20; if (strpos('.', $order_by) > 0) { $order_by = explode('.', $order_by); $order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`'; } $alias = ''; if (in_array($order_by, array('price', 'date_add', 'date_upd'))) { $alias = 'product_shop.'; } elseif ($order_by == 'id_product') { $alias = 'p.'; } elseif ($order_by == 'manufacturer_name') { $order_by = 'name'; $alias = 'm.'; } /* * EU-Legal * get standard shipping time from database pl.* */ $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`delivery_now`, pl.`delivery_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, s.`name` AS supplier_name, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . $nb_days_new_product . ' DAY)) > 0 AS new, m.`name` AS manufacturer_name' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' FROM `' . _DB_PREFIX_ . 'product` p ' . Shop::addSqlAssociation('product', 'p') . ' JOIN `' . _DB_PREFIX_ . 'product_supplier` ps ON (ps.id_product = p.id_product AND ps.id_product_attribute = 0) LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . (Combination::isFeatureActive() ? Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') : '') . ' LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'supplier` s ON s.`id_supplier` = p.`id_supplier` LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` ' . Product::sqlStock('p', 0); if (Group::isFeatureActive() || $active_category) { $sql .= 'JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (p.id_product = cp.id_product)'; if (Group::isFeatureActive()) { $sql .= 'JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category` AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1') . ')'; } if ($active_category) { $sql .= 'JOIN `' . _DB_PREFIX_ . 'category` ca ON cp.`id_category` = ca.`id_category` AND ca.`active` = 1'; } } $sql .= ' WHERE ps.`id_supplier` = ' . (int) $id_supplier . ' ' . ($active ? ' AND product_shop.`active` = 1' : '') . ' ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' GROUP BY product_shop.id_product ORDER BY ' . $alias . pSQL($order_by) . ' ' . pSQL($order_way) . ' LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n; $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql); if (!$result) { return false; } if ($order_by == 'price') { Tools::orderbyPrice($result, $order_way); } return Product::getProductsProperties($id_lang, $result); }
public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position', $order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null) { if (!$context) { $context = Context::getContext(); } $db = Db::getInstance(_PS_USE_SQL_SLAVE_); if ($page_number < 1) { $page_number = 1; } if ($page_size < 1) { $page_size = 1; } if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) { return false; } $intersect_array = array(); $score_array = array(); $words = explode(' ', Search::sanitize($expr, $id_lang, false, $context->language->iso_code)); foreach ($words as $key => $word) { if (!empty($word) && strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $start_search = Configuration::get('PS_SEARCH_START') ? '%' : ''; $end_search = Configuration::get('PS_SEARCH_END') ? '' : '%'; $intersect_array[] = 'SELECT si.id_product FROM ' . _DB_PREFIX_ . 'search_word sw LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word WHERE sw.id_lang = ' . (int) $id_lang . ' AND sw.id_shop = ' . $context->shop->id . ' AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . $start_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $start_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\''); if ($word[0] != '-') { $score_array[] = 'sw.word LIKE \'' . $start_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\''; } } else { unset($words[$key]); } } if (!count($words)) { return $ajax ? array() : array('total' => 0, 'result' => array(), 'painters' => array()); } $score = ''; if (count($score_array)) { $score = ',( SELECT SUM(weight) FROM ' . _DB_PREFIX_ . 'search_word sw LEFT JOIN ' . _DB_PREFIX_ . 'search_index si ON sw.id_word = si.id_word WHERE sw.id_lang = ' . (int) $id_lang . ' AND sw.id_shop = ' . $context->shop->id . ' AND si.id_product = p.id_product AND (' . implode(' OR ', $score_array) . ') ) position'; } $sql_groups = ''; if (Group::isFeatureActive()) { $groups = FrontController::getCurrentCustomerGroups(); $sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1'); } $results = $db->executeS(' SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_product` cp ' . (Group::isFeatureActive() ? 'INNER JOIN `' . _DB_PREFIX_ . 'category_group` cg ON cp.`id_category` = cg.`id_category`' : '') . ' INNER JOIN `' . _DB_PREFIX_ . 'category` c ON cp.`id_category` = c.`id_category` INNER JOIN `' . _DB_PREFIX_ . 'product` p ON cp.`id_product` = p.`id_product` ' . Shop::addSqlAssociation('product', 'p', false) . ' WHERE c.`active` = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "search") AND product_shop.indexed = 1 ' . $sql_groups); $eligible_products = array(); foreach ($results as $row) { $eligible_products[] = $row['id_product']; } foreach ($intersect_array as $query) { $eligible_products2 = array(); foreach ($db->executeS($query) as $row) { $eligible_products2[] = $row['id_product']; } $eligible_products = array_intersect($eligible_products, $eligible_products2); if (!count($eligible_products)) { return $ajax ? array() : array('total' => 0, 'result' => array(), 'painters' => array()); } } $eligible_products = array_unique($eligible_products); $product_pool = ''; foreach ($eligible_products as $id_product) { if ($id_product) { $product_pool .= (int) $id_product . ','; } } if (empty($product_pool)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $product_pool = strpos($product_pool, ',') === false ? ' = ' . (int) $product_pool . ' ' : ' IN (' . rtrim($product_pool, ',') . ') '; if ($ajax) { $sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname, cl.link_rewrite crewrite, pl.link_rewrite prewrite ' . $score . ' FROM ' . _DB_PREFIX_ . 'product p INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON ( product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ' ) WHERE p.`id_product` ' . $product_pool . ' ORDER BY position DESC LIMIT 10'; return $db->executeS($sql); } if (strpos($order_by, '.') > 0) { $order_by = explode('.', $order_by); $order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`'; } $alias = ''; if ($order_by == 'price') { $alias = 'product_shop.'; } elseif (in_array($order_by, array('date_upd', 'date_add'))) { $alias = 'p.'; } $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name ' . $score . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute' : '') . ', DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 new' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' FROM ' . _DB_PREFIX_ . 'product p ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) ' . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) : Product::sqlStock('p', 'product', false, Context::getContext()->shop)) . ' LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') WHERE p.`id_product` ' . $product_pool . ' GROUP BY product_shop.id_product ' . ($order_by ? 'ORDER BY ' . $alias . $order_by : '') . ($order_way ? ' ' . $order_way : '') . ' LIMIT ' . (int) (($page_number - 1) * $page_size) . ',' . (int) $page_size; $result = $db->executeS($sql); $helper = new Helper(); $categoryTree = $helper->renderCategoryTree(); $sql = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'product p ' . Shop::addSqlAssociation('product', 'p') . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ' ) LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` WHERE p.`id_product` ' . $product_pool; $total = $db->getValue($sql); if (!$result) { $result_properties = false; } else { $result_properties = Product::getProductsProperties((int) $id_lang, $result); } $sql = 'SELECT c.*, c_lang.* FROM ' . _DB_PREFIX_ . 'category c INNER JOIN ' . _DB_PREFIX_ . 'category_lang c_lang ON ( c.id_category = c_lang.id_category) WHERE c_lang.name LIKE ' . ($word[0] == '-' ? ' \'' . $end_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $end_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'') . 'AND c.id_parent = 14'; $painters = $db->executeS($sql); return array('total' => $total, 'result' => $result_properties, 'painters' => $painters); }
/** * Return cart weight * @return float Cart weight */ public function getTotalWeight($products = null) { if (!is_null($products)) { $total_weight = 0; foreach ($products as $product) { if (!isset($product['weight_attribute']) || is_null($product['weight_attribute'])) { $total_weight += $product['weight'] * $product['cart_quantity']; } else { $total_weight += $product['weight_attribute'] * $product['cart_quantity']; } } return $total_weight; } if (!isset(self::$_totalWeight[$this->id])) { if (Combination::isFeatureActive()) { $weight_product_with_attribute = Db::getInstance()->getValue(' SELECT SUM((p.`weight` + pa.`weight`) * cp.`quantity`) as nb FROM `' . _DB_PREFIX_ . 'cart_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (cp.`id_product` = p.`id_product`) LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (cp.`id_product_attribute` = pa.`id_product_attribute`) WHERE (cp.`id_product_attribute` IS NOT NULL AND cp.`id_product_attribute` != 0) AND cp.`id_cart` = ' . (int) $this->id); } else { $weight_product_with_attribute = 0; } $weight_product_without_attribute = Db::getInstance()->getValue(' SELECT SUM(p.`weight` * cp.`quantity`) as nb FROM `' . _DB_PREFIX_ . 'cart_product` cp LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (cp.`id_product` = p.`id_product`) WHERE (cp.`id_product_attribute` IS NULL OR cp.`id_product_attribute` = 0) AND cp.`id_cart` = ' . (int) $this->id); self::$_totalWeight[$this->id] = round((double) $weight_product_with_attribute + (double) $weight_product_without_attribute, 3); } return self::$_totalWeight[$this->id]; }
/** * Returns module content for left column */ public function hookProductFooter($params) { $cache_id = 'crossselling_mod|productfooter|' . (int) $params['product']->id; if (!$this->isCached('crossselling_mod.tpl', $this->getCacheId($cache_id))) { $orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order) WHERE o.valid = 1 AND od.product_id = ' . (int) $params['product']->id); if (count($orders)) { $list = ''; foreach ($orders as $order) { $list .= (int) $order['id_order'] . ','; } $list = rtrim($list, ','); if (Group::isFeatureActive()) { $sql_groups_join = ' LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = product_shop.id_category_default AND cp.id_product = product_shop.id_product) LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cp.`id_category` = cg.`id_category`)'; $groups = FrontController::getCurrentCustomerGroups(); $sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '=' . (int) Group::getCurrent()->id); } $order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT DISTINCT od.product_id, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price, product_shop.id_category_default, p.available_for_order, p.customizable, cl.link_rewrite category, p.ean13, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM ' . _DB_PREFIX_ . 'order_detail od LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id) ' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $this->context->shop) : Product::sqlStock('p', 'product', false, $this->context->shop)) . ' LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ') LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id) ' . (Group::isFeatureActive() ? $sql_groups_join : '') . ' WHERE od.id_order IN (' . $list . ') AND pl.id_lang = ' . (int) $this->context->language->id . ' AND cl.id_lang = ' . (int) $this->context->language->id . ' AND od.product_id != ' . (int) $params['product']->id . ' AND i.cover = 1 AND product_shop.active = 1 ' . (Group::isFeatureActive() ? $sql_groups_where : '') . ' ORDER BY RAND() LIMIT ' . (int) Configuration::get('CROSSSELLING_NBR_M')); $tax_calc = Product::getTaxCalculationMethod(); foreach ($order_products as &$order_product) { $order_product['id_product'] = (int) $order_product['product_id']; $order_product['image'] = $this->context->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('home')); $order_product['link'] = $this->context->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']); if ($tax_calc == 0 || $tax_calc == 2) { $order_product['price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null); } elseif ($tax_calc == 1) { $order_product['price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null); } $order_product['allow_oosp'] = Product::isAvailableWhenOutOfStock((int) $order_product['out_of_stock']); } $order_products = Product::getProductsProperties((int) $this->context->language->id, $order_products); $this->smarty->assign(array('order' => false, 'orderProducts' => $order_products, 'homeSize' => Image::getSize(ImageType::getFormatedName('home')), 'middlePosition_crossselling' => round(count($order_products) / 2, 0), 'crossDisplayPrice' => Configuration::get('CROSSSELLING_DISPLAY_PRICE_M'))); } } return $this->display(__FILE__, 'crossselling_mod.tpl', $this->getCacheId($cache_id)); }
public function getProducts($refresh = false, $id_product = false, $id_country = null) { /* * EU-Legal * 1) correct calculation of prices -> Problem with inaccuracy at high number of items * 2) assign standard delivery times to products */ if (!$this->id) { return array(); } // Product cache must be strictly compared to NULL, or else an empty cart will add dozens of queries if ($this->_products !== null && !$refresh) { // Return product row with specified ID if it exists if (is_int($id_product)) { foreach ($this->_products as $product) { if ($product['id_product'] == $id_product) { return array($product); } } return array(); } return $this->_products; } // Build query $sql = new DbQuery(); // Build SELECT $sql->select('cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`delivery_now`, pl.`delivery_later`, product_shop.`id_category_default`, p.`id_supplier`, p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`, product_shop.`available_for_order`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`, stock.`quantity` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`, p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category, CONCAT(LPAD(cp.`id_product`, 10, 0), LPAD(IFNULL(cp.`id_product_attribute`, 0), 10, 0), IFNULL(cp.`id_address_delivery`, 0)) AS unique_id, cp.id_address_delivery, product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference, IFNULL(sp.`reduction_type`, 0) AS reduction_type'); // Build FROM $sql->from('cart_product', 'cp'); // Build JOIN $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`'); $sql->innerJoin('product_shop', 'product_shop', '(product_shop.`id_shop` = cp.`id_shop` AND product_shop.`id_product` = p.`id_product`)'); $sql->leftJoin('product_lang', 'pl', ' p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('pl', 'cp.id_shop')); $sql->leftJoin('category_lang', 'cl', ' product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('cl', 'cp.id_shop')); $sql->leftJoin('product_supplier', 'ps', 'ps.`id_product` = cp.`id_product` AND ps.`id_product_attribute` = cp.`id_product_attribute` AND ps.`id_supplier` = p.`id_supplier`'); $sql->leftJoin('specific_price', 'sp', 'sp.`id_product` = cp.`id_product`'); // AND 'sp.`id_shop` = cp.`id_shop` // @todo test if everything is ok, then refactorise call of this method $sql->join(Product::sqlStock('cp', 'cp')); // Build WHERE clauses $sql->where('cp.`id_cart` = ' . (int) $this->id); if ($id_product) { $sql->where('cp.`id_product` = ' . (int) $id_product); } $sql->where('p.`id_product` IS NOT NULL'); // Build GROUP BY $sql->groupBy('unique_id'); // Build ORDER BY $sql->orderBy('cp.`date_add`, p.`id_product`, cp.`id_product_attribute` ASC'); if (Customization::isFeatureActive()) { $sql->select('cu.`id_customization`, cu.`quantity` AS customization_quantity'); $sql->leftJoin('customization', 'cu', 'p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.`id_product_attribute` AND cu.`id_cart` = ' . (int) $this->id); } else { $sql->select('NULL AS customization_quantity, NULL AS id_customization'); } if (Combination::isFeatureActive()) { $sql->select(' product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr, IF (IFNULL(pa.`reference`, \'\') = \'\', p.`reference`, pa.`reference`) AS reference, (p.`weight`+ pa.`weight`) weight_attribute, IF (IFNULL(pa.`ean13`, \'\') = \'\', p.`ean13`, pa.`ean13`) AS ean13, IF (IFNULL(pa.`upc`, \'\') = \'\', p.`upc`, pa.`upc`) AS upc, pai.`id_image` as pai_id_image, il.`legend` as pai_legend, IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity, IF(product_attribute_shop.wholesale_price > 0, product_attribute_shop.wholesale_price, product_shop.`wholesale_price`) wholesale_price '); $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product_attribute` = cp.`id_product_attribute`'); $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', '(product_attribute_shop.`id_shop` = cp.`id_shop` AND product_attribute_shop.`id_product_attribute` = pa.`id_product_attribute`)'); $sql->leftJoin('product_attribute_image', 'pai', 'pai.`id_product_attribute` = pa.`id_product_attribute`'); $sql->leftJoin('image_lang', 'il', 'il.`id_image` = pai.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang); } else { $sql->select('p.`reference` AS reference, p.`ean13`, p.`upc` AS upc, product_shop.`minimal_quantity` AS minimal_quantity, product_shop.`wholesale_price` wholesale_price'); } $result = Db::getInstance()->executeS($sql); // Reset the cache before the following return, or else an empty cart will add dozens of queries $products_ids = array(); $pa_ids = array(); if ($result) { foreach ($result as $row) { $products_ids[] = $row['id_product']; $pa_ids[] = $row['id_product_attribute']; } } // Thus you can avoid one query per product, because there will be only one query for all the products of the cart Product::cacheProductsFeatures($products_ids); Cart::cacheSomeAttributesLists($pa_ids, $this->id_lang); $this->_products = array(); if (empty($result)) { return array(); } $cart_shop_context = Context::getContext()->cloneContext(); foreach ($result as &$row) { if (isset($row['ecotax_attr']) && $row['ecotax_attr'] > 0) { $row['ecotax'] = (double) $row['ecotax_attr']; } $row['stock_quantity'] = (int) $row['quantity']; // for compatibility with 1.2 themes $row['quantity'] = (int) $row['cart_quantity']; if (isset($row['id_product_attribute']) && (int) $row['id_product_attribute'] && isset($row['weight_attribute'])) { $row['weight'] = (double) $row['weight_attribute']; } if (Configuration::get('PS_TAX_ADDRESS_TYPE') == 'id_address_invoice') { $address_id = (int) $this->id_address_invoice; } else { $address_id = (int) $row['id_address_delivery']; } if (!Address::addressExists($address_id)) { $address_id = null; } if ($cart_shop_context->shop->id != $row['id_shop']) { $cart_shop_context->shop = new Shop((int) $row['id_shop']); } $address = Address::initialize($address_id, true); $id_tax_rules_group = Product::getIdTaxRulesGroupByIdProduct((int) $row['id_product'], $cart_shop_context); $tax_calculator = TaxManagerFactory::getManager($address, $id_tax_rules_group)->getTaxCalculator(); $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 6, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, $address_id, $specific_price_output, false, true, $cart_shop_context); switch (Configuration::get('PS_ROUND_TYPE')) { case Order::ROUND_TOTAL: $row['total'] = $row['price'] * (int) $row['cart_quantity']; $row['total_wt'] = $tax_calculator->addTaxes($row['price']) * (int) $row['cart_quantity']; break; case Order::ROUND_LINE: $row['total'] = Tools::ps_round($row['price'] * (int) $row['cart_quantity'], _PS_PRICE_COMPUTE_PRECISION_); $row['total_wt'] = Tools::ps_round($tax_calculator->addTaxes($row['price']) * (int) $row['cart_quantity'], _PS_PRICE_COMPUTE_PRECISION_); break; case Order::ROUND_ITEM: default: $row['total'] = Tools::ps_round($row['price'], _PS_PRICE_COMPUTE_PRECISION_) * (int) $row['cart_quantity']; $row['total_wt'] = Tools::ps_round($tax_calculator->addTaxes($row['price']), _PS_PRICE_COMPUTE_PRECISION_) * (int) $row['cart_quantity']; break; } $row['price_wt'] = $tax_calculator->addTaxes($row['price']); $row['description_short'] = Tools::nl2br($row['description_short']); if (!isset($row['pai_id_image']) || $row['pai_id_image'] == 0) { $cache_id = 'Cart::getProducts_' . '-pai_id_image-' . (int) $row['id_product'] . '-' . (int) $this->id_lang . '-' . (int) $row['id_shop']; if (!Cache::isStored($cache_id)) { $row2 = Db::getInstance()->getRow(' SELECT image_shop.`id_image` id_image, il.`legend` FROM `' . _DB_PREFIX_ . 'image` i JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (i.id_image = image_shop.id_image AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $row['id_shop'] . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang . ') WHERE i.`id_product` = ' . (int) $row['id_product'] . ' AND image_shop.`cover` = 1'); Cache::store($cache_id, $row2); } $row2 = Cache::retrieve($cache_id); if (!$row2) { $row2 = array('id_image' => false, 'legend' => false); } else { $row = array_merge($row, $row2); } } else { $row['id_image'] = $row['pai_id_image']; $row['legend'] = $row['pai_legend']; } $row['reduction_applies'] = $specific_price_output && (double) $specific_price_output['reduction']; $row['quantity_discount_applies'] = $specific_price_output && $row['cart_quantity'] >= (int) $specific_price_output['from_quantity']; $row['id_image'] = Product::defineProductImage($row, $this->id_lang); $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']); $row['features'] = Product::getFeaturesStatic((int) $row['id_product']); if (array_key_exists($row['id_product_attribute'] . '-' . $this->id_lang, self::$_attributesLists)) { $row = array_merge($row, self::$_attributesLists[$row['id_product_attribute'] . '-' . $this->id_lang]); } /* * EU-Legal * assign standard delivery times to products */ $row['delivery_now'] = !empty($row['delivery_now']) ? $row['delivery_now'] : Configuration::get('LEGAL_DELIVERY_NOW', $this->id_lang); $row['delivery_later'] = !empty($row['delivery_later']) ? $row['delivery_later'] : Configuration::get('LEGAL_DELIVERY_LATER', $this->id_lang); $row = Product::getTaxesInformations($row, $cart_shop_context); $this->_products[] = $row; } return $this->_products; }
/** * Get all attributes groups for a given language * * @param integer $id_lang Language id * @return array Attributes groups */ public static function getAttributesGroups($id_lang) { if (!Combination::isFeatureActive()) { return array(); } return Db::getInstance()->executeS(' SELECT DISTINCT agl.`name`, ag.*, agl.* FROM `' . _DB_PREFIX_ . 'attribute_group` ag ' . Shop::addSqlAssociation('attribute_group', 'ag') . ' LEFT JOIN `' . _DB_PREFIX_ . 'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group` AND `id_lang` = ' . (int) $id_lang . ') ORDER BY `name` ASC '); }
/** * Returns Packs that conatins the given product in the right declinaison. * * @param integer $id_item Product item id that could be contained in a|many pack(s) * @param integer $id_attribute_item The declinaison of the product * @param integer $id_lang * @return array[Product] Packs that contains the given product */ public static function getPacksContainingItem($id_item, $id_attribute_item, $id_lang) { if (!Pack::isFeatureActive() || !$id_item) { return array(); } $query = 'SELECT `id_product_pack`, `quantity` FROM `' . _DB_PREFIX_ . 'pack` WHERE `id_product_item` = ' . (int) $id_item; if (Combination::isFeatureActive()) { $query .= ' AND `id_product_attribute_item` = ' . (int) $id_attribute_item; } $result = Db::getInstance()->executeS($query); $array_result = array(); foreach ($result as $row) { $p = new Product($row['id_product_pack'], true, $id_lang); $p->loadStockData(); $p->pack_item_quantity = $row['quantity']; // Specific need from StockAvailable::updateQuantity() $array_result[] = $p; } return $array_result; }
public function getProducts($refresh = false, $id_product = false, $id_country = null) { if (!$this->id) { return array(); } // Product cache must be strictly compared to NULL, or else an empty cart will add dozens of queries if ($this->_products !== null && !$refresh) { // Return product row with specified ID if it exists if (is_int($id_product)) { foreach ($this->_products as $product) { if ($product['id_product'] == $id_product) { return array($product); } } return array(); } return $this->_products; } // Build query $sql = new DbQuery(); // Build SELECT $sql->select('cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`, pl.`description_short`, pl.`available_now`, pl.`available_later`, product_shop.`id_category_default`, p.`id_supplier`, p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`, product_shop.`available_for_order`, product_shop.`price`, product_shop.`active`, product_shop.`unity`, product_shop.`unit_price_ratio`, stock.`quantity` AS quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, p.`weight`, p.`date_add`, p.`date_upd`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category, CONCAT(LPAD(cp.`id_product`, 10, 0), LPAD(IFNULL(cp.`id_product_attribute`, 0), 10, 0), IFNULL(cp.`id_address_delivery`, 0)) AS unique_id, cp.id_address_delivery, product_shop.`wholesale_price`, product_shop.advanced_stock_management, ps.product_supplier_reference supplier_reference'); // Build FROM $sql->from('cart_product', 'cp'); // Build JOIN $sql->leftJoin('product', 'p', 'p.`id_product` = cp.`id_product`'); $sql->innerJoin('product_shop', 'product_shop', '(product_shop.`id_shop` = cp.`id_shop` AND product_shop.`id_product` = p.`id_product`)'); $sql->leftJoin('product_lang', 'pl', ' p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('pl', 'cp.id_shop')); $sql->leftJoin('category_lang', 'cl', ' product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $this->id_lang . Shop::addSqlRestrictionOnLang('cl', 'cp.id_shop')); $sql->leftJoin('product_supplier', 'ps', 'ps.`id_product` = cp.`id_product` AND ps.`id_product_attribute` = cp.`id_product_attribute` AND ps.`id_supplier` = p.`id_supplier`'); // @todo test if everything is ok, then refactorise call of this method $sql->join(Product::sqlStock('cp', 'cp')); // Build WHERE clauses $sql->where('cp.`id_cart` = ' . (int) $this->id); if ($id_product) { $sql->where('cp.`id_product` = ' . (int) $id_product); } $sql->where('p.`id_product` IS NOT NULL'); // Build GROUP BY $sql->groupBy('unique_id'); // Build ORDER BY $sql->orderBy('p.`id_product`, cp.`id_product_attribute`, cp.`date_add` ASC'); if (Customization::isFeatureActive()) { $sql->select('cu.`id_customization`, cu.`quantity` AS customization_quantity'); $sql->leftJoin('customization', 'cu', 'p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.`id_product_attribute` AND cu.`id_cart` = ' . (int) $this->id); } else { $sql->select('NULL AS customization_quantity, NULL AS id_customization'); } if (Combination::isFeatureActive()) { $sql->select(' product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr, IF (IFNULL(pa.`reference`, \'\') = \'\', p.`reference`, pa.`reference`) AS reference, (p.`weight`+ pa.`weight`) weight_attribute, IF (IFNULL(pa.`ean13`, \'\') = \'\', p.`ean13`, pa.`ean13`) AS ean13, IF (IFNULL(pa.`upc`, \'\') = \'\', p.`upc`, pa.`upc`) AS upc, pai.`id_image` as pai_id_image, il.`legend` as pai_legend, IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity '); $sql->leftJoin('product_attribute', 'pa', 'pa.`id_product_attribute` = cp.`id_product_attribute`'); $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', '(product_attribute_shop.`id_shop` = cp.`id_shop` AND product_attribute_shop.`id_product_attribute` = pa.`id_product_attribute`)'); $sql->leftJoin('product_attribute_image', 'pai', 'pai.`id_product_attribute` = pa.`id_product_attribute`'); $sql->leftJoin('image_lang', 'il', 'il.`id_image` = pai.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang); } else { $sql->select('p.`reference` AS reference, p.`ean13`, p.`upc` AS upc, product_shop.`minimal_quantity` AS minimal_quantity'); } $result = Db::getInstance()->executeS($sql); // Reset the cache before the following return, or else an empty cart will add dozens of queries $products_ids = array(); $pa_ids = array(); if ($result) { foreach ($result as $row) { $products_ids[] = $row['id_product']; $pa_ids[] = $row['id_product_attribute']; } } // Thus you can avoid one query per product, because there will be only one query for all the products of the cart Product::cacheProductsFeatures($products_ids); Cart::cacheSomeAttributesLists($pa_ids, $this->id_lang); $this->_products = array(); if (empty($result)) { return array(); } $cart_shop_context = Context::getContext()->cloneContext(); foreach ($result as &$row) { // $quantityDiscount = SpecificPrice::getQuantityDiscount((int)$row['id_product'], $row['id_shop'], // (int)$cart->id_currency, (int)$this->vat_address->id_country, // (int)$this->customer->id_default_group, (int)$row['cart_quantity'], false, null, null, $null, true, true, $this->context); // // echo '<pre>'; // print_r($quantityDiscount); // echo '</pre>'; if (isset($row['ecotax_attr']) && $row['ecotax_attr'] > 0) { $row['ecotax'] = (double) $row['ecotax_attr']; } $row['stock_quantity'] = (int) $row['quantity']; // for compatibility with 1.2 themes $row['quantity'] = (int) $row['cart_quantity']; if (isset($row['id_product_attribute']) && (int) $row['id_product_attribute'] && isset($row['weight_attribute'])) { $row['weight'] = (double) $row['weight_attribute']; } if (Configuration::get('PS_TAX_ADDRESS_TYPE') == 'id_address_invoice') { $address_id = (int) $this->id_address_invoice; } else { $address_id = (int) $row['id_address_delivery']; } if (!Address::addressExists($address_id)) { $address_id = null; } if ($cart_shop_context->shop->id != $row['id_shop']) { $cart_shop_context->shop = new Shop((int) $row['id_shop']); } if ($this->_taxCalculationMethod == PS_TAX_EXC) { $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 2, null, false, true, (int) $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $specific_price_output, true, true, $cart_shop_context); // Here taxes are computed only once the quantity has been applied to the product price $row['price_wt'] = Product::getPriceStatic((int) $row['id_product'], true, isset($row['id_product_attribute']) ? (int) $row['id_product_attribute'] : null, 2, null, false, true, (int) $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $null, true, true, $cart_shop_context); $tax_rate = Tax::getProductTaxRate((int) $row['id_product'], (int) $address_id); $row['total_wt'] = Tools::ps_round($row['price'] * (double) $row['cart_quantity'] * (1 + (double) $tax_rate / 100), 2); $row['total'] = $row['price'] * (int) $row['cart_quantity']; } else { $row['price'] = Product::getPriceStatic((int) $row['id_product'], false, (int) $row['id_product_attribute'], 2, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $specific_price_output, true, true, $cart_shop_context); $row['price_wt'] = Product::getPriceStatic((int) $row['id_product'], true, (int) $row['id_product_attribute'], 2, null, false, true, $row['cart_quantity'], false, (int) $this->id_customer ? (int) $this->id_customer : null, (int) $this->id, (int) $address_id ? (int) $address_id : null, $null, true, true, $cart_shop_context); // In case when you use QuantityDiscount, getPriceStatic() can be return more of 2 decimals $row['price_wt'] = Tools::ps_round($row['price_wt'], 2); $row['total_wt'] = $row['price_wt'] * (int) $row['cart_quantity']; $row['total'] = Tools::ps_round($row['price'] * (int) $row['cart_quantity'], 2); $row['description_short'] = Tools::nl2br($row['description_short']); } if (!isset($row['pai_id_image']) || $row['pai_id_image'] == 0) { $cache_id = 'Cart::getProducts_' . '-pai_id_image-' . (int) $row['id_product'] . '-' . (int) $this->id_lang . '-' . (int) $row['id_shop']; if (!Cache::isStored($cache_id)) { $row2 = Db::getInstance()->getRow(' SELECT image_shop.`id_image` id_image, il.`legend` FROM `' . _DB_PREFIX_ . 'image` i JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (i.id_image = image_shop.id_image AND image_shop.cover=1 AND image_shop.id_shop=' . (int) $row['id_shop'] . ') LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $this->id_lang . ') WHERE i.`id_product` = ' . (int) $row['id_product'] . ' AND image_shop.`cover` = 1'); Cache::store($cache_id, $row2); } $row2 = Cache::retrieve($cache_id); if (!$row2) { $row2 = array('id_image' => false, 'legend' => false); } else { $row = array_merge($row, $row2); } } else { $row['id_image'] = $row['pai_id_image']; $row['legend'] = $row['pai_legend']; } $row['reduction_applies'] = $specific_price_output && (double) $specific_price_output['reduction']; $row['quantity_discount_applies'] = $specific_price_output && $row['cart_quantity'] >= (int) $specific_price_output['from_quantity']; $row['id_image'] = Product::defineProductImage($row, $this->id_lang); $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']); $row['features'] = Product::getFeaturesStatic((int) $row['id_product']); if (array_key_exists($row['id_product_attribute'] . '-' . $this->id_lang, self::$_attributesLists)) { $row = array_merge($row, self::$_attributesLists[$row['id_product_attribute'] . '-' . $this->id_lang]); } if (Context::getContext()->language->id != 1) { $row['name'] = Tools::rus2translit($row['name']); } $row = Product::getTaxesInformations($row, $cart_shop_context); $this->_products[] = $row; } return $this->_products; }