/**
* {@inheritdoc}
*/
public function getCatalogProductList($offset, $limit, $orderBy, $sortOrder, $post = array(), $avoidPersistence = false, $formatCldr = true)
{
$filterParams = $this->combinePersistentCatalogProductFilter(array_merge($post, ['last_offset' => $offset, 'last_limit' => $limit, 'last_orderBy' => $orderBy, 'last_sortOrder' => $sortOrder]), $avoidPersistence);
$filterParams = AdminFilter::sanitizeFilterParameters($filterParams);
$showPositionColumn = $this->isCategoryFiltered();
if ($orderBy == 'position_ordering' && $showPositionColumn) {
foreach ($filterParams as $key => $param) {
if (strpos($key, 'filter_column_') === 0) {
$filterParams[$key] = '';
}
}
}
if ($orderBy == 'position_ordering') {
$orderBy = 'position';
}
$idShop = \ContextCore::getContext()->shop->id;
$idLang = \ContextCore::getContext()->language->id;
$sqlSelect = array('id_product' => array('table' => 'p', 'field' => 'id_product', 'filtering' => ' %s '), 'reference' => array('table' => 'p', 'field' => 'reference', 'filtering' => self::FILTERING_LIKE_BOTH), 'price' => array('table' => 'p', 'field' => 'price', 'filtering' => ' %s '), 'id_shop_default' => array('table' => 'p', 'field' => 'id_shop_default'), 'is_virtual' => array('table' => 'p', 'field' => 'is_virtual'), 'name' => array('table' => 'pl', 'field' => 'name', 'filtering' => self::FILTERING_LIKE_BOTH), 'active' => array('table' => 'sa', 'field' => 'active', 'filtering' => self::FILTERING_EQUAL_NUMERIC), 'shopname' => array('table' => 'shop', 'field' => 'name'), 'id_image' => array('table' => 'image_shop', 'field' => 'id_image'), 'name_category' => array('table' => 'cl', 'field' => 'name', 'filtering' => self::FILTERING_LIKE_BOTH), 'price_final' => '0', 'nb_downloadable' => array('table' => 'pd', 'field' => 'nb_downloadable'), 'sav_quantity' => array('table' => 'sav', 'field' => 'quantity', 'filtering' => ' %s '), 'badge_danger' => array('select' => 'IF(sav.`quantity`<=0, 1, 0)', 'filtering' => 'IF(sav.`quantity`<=0, 1, 0) = %s'));
$sqlTable = array('p' => 'product', 'pl' => array('table' => 'product_lang', 'join' => 'LEFT JOIN', 'on' => 'pl.`id_product` = p.`id_product` AND pl.`id_lang` = ' . $idLang . ' AND pl.`id_shop` = ' . $idShop), 'sav' => array('table' => 'stock_available', 'join' => 'LEFT JOIN', 'on' => 'sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = ' . $idShop), 'sa' => array('table' => 'product_shop', 'join' => 'JOIN', 'on' => 'p.`id_product` = sa.`id_product` AND sa.id_shop = ' . $idShop), 'cl' => array('table' => 'category_lang', 'join' => 'LEFT JOIN', 'on' => 'sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . $idLang . ' AND cl.id_shop = ' . $idShop), 'c' => array('table' => 'category', 'join' => 'LEFT JOIN', 'on' => 'c.`id_category` = cl.`id_category`'), 'shop' => array('table' => 'shop', 'join' => 'LEFT JOIN', 'on' => 'shop.id_shop = ' . $idShop), 'image_shop' => array('table' => 'image_shop', 'join' => 'LEFT JOIN', 'on' => 'image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = ' . $idShop), 'i' => array('table' => 'image', 'join' => 'LEFT JOIN', 'on' => 'i.`id_image` = image_shop.`id_image`'), 'pd' => array('table' => 'product_download', 'join' => 'LEFT JOIN', 'on' => 'pd.`id_product` = p.`id_product`'));
$sqlWhere = array('AND', 1);
foreach ($filterParams as $filterParam => $filterValue) {
if (!$filterValue && $filterValue !== '0') {
continue;
}
if (strpos($filterParam, 'filter_column_') === 0) {
$filterValue = \Db::getInstance()->escape($filterValue, in_array($filterParam, ['filter_column_id_product', 'filter_column_sav_quantity', 'filter_column_price']), true);
$field = substr($filterParam, 14);
// 'filter_column_' takes 14 chars
if (isset($sqlSelect[$field]['table'])) {
$sqlWhere[] = $sqlSelect[$field]['table'] . '.`' . $sqlSelect[$field]['field'] . '` ' . sprintf($sqlSelect[$field]['filtering'], $filterValue);
} else {
$sqlWhere[] = '(' . sprintf($sqlSelect[$field]['filtering'], $filterValue) . ')';
}
}
// for 'filter_category', see next if($showPositionColumn) block.
}
$sqlWhere[] = 'state = ' . \Product::STATE_SAVED;
$sqlOrder = array($orderBy . ' ' . $sortOrder);
if ($orderBy != 'id_product') {
$sqlOrder[] = 'id_product asc';
// secondary order by (useful when ordering by active, quantity, price, etc...)
}
$sqlLimit = $offset . ', ' . $limit;
// Column 'position' added if filtering by category
if ($showPositionColumn) {
$filteredCategoryId = (int) $filterParams['filter_category'];
$sqlSelect['position'] = array('table' => 'cp', 'field' => 'position');
$sqlTable['cp'] = array('table' => 'category_product', 'join' => 'INNER JOIN', 'on' => 'cp.`id_product` = p.`id_product` AND cp.`id_category` = ' . $filteredCategoryId);
} elseif ($orderBy == 'position') {
// We do not show position column, so we do not join the table, so we do not order by position!
$sqlOrder = array('id_product ASC');
}
// exec legacy hook but with different parameters (retro-compat < 1.7 is broken here)
\HookCore::exec('actionAdminProductsListingFieldsModifier', array('_ps_version' => _PS_VERSION_, 'sql_select' => &$sqlSelect, 'sql_table' => &$sqlTable, 'sql_where' => &$sqlWhere, 'sql_order' => &$sqlOrder, 'sql_limit' => &$sqlLimit));
$sql = $this->compileSqlQuery($sqlSelect, $sqlTable, $sqlWhere, $sqlOrder, $sqlLimit);
$products = \Db::getInstance()->executeS($sql, true, false);
$total = \Db::getInstance()->executeS('SELECT FOUND_ROWS();', true, false);
$total = $total[0]['FOUND_ROWS()'];
// post treatment
$currency = new \CurrencyCore(\Configuration::get('PS_CURRENCY_DEFAULT'));
foreach ($products as &$product) {
$product['total'] = $total;
// total product count (filtered)
$product['price_final'] = \ProductCore::getPriceStatic($product['id_product'], true, null, (int) \Configuration::get('PS_PRICE_DISPLAY_PRECISION'), null, false, true, 1, true, null, null, null, $nothing, true, true);
if ($formatCldr) {
$product['price'] = \ToolsCore::displayPrice($product['price'], $currency);
$product['price_final'] = \ToolsCore::displayPrice($product['price_final'], $currency);
}
$product['image'] = $this->imageManager->getThumbnailForListing($product['id_image']);
}
// post treatment by hooks
// exec legacy hook but with different parameters (retro-compat < 1.7 is broken here)
\HookCore::exec('actionAdminProductsListingResultsModifier', array('_ps_version' => _PS_VERSION_, 'products' => &$products, 'total' => $total));
return $products;
}