protected function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
{
global $cookie, $smarty;
$id_seller_country = (int) Tools::getValue('id_seller_country');
if ($p < 1) {
$p = 1;
}
if ($n <= 0) {
$n = 10;
}
if (empty($orderBy)) {
$orderBy = 'price';
} else {
$orderBy = strtolower($orderBy);
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$requiredcond = '';
if (intval(Configuration::get('AGILE_MS_PRODUCT_APPROVAL')) == 1) {
$requiredcond = ' AND po.approved = 1 ';
}
$joins = '';
$wheres = '';
if (Module::isInstalled('agilesellerlistoptions')) {
require_once _PS_ROOT_DIR_ . "/modules/agilesellerlistoptions/agilesellerlistoptions.php";
$joins = $joins . '
LEFT JOIN `' . _DB_PREFIX_ . 'seller_listoption` slb ON (p.id_product = slb.id_product AND slb.id_option = ' . AgileSellerListOptions::ASLO_OPTION_LIST . ')
';
$aslo_list_prod_id = intval(Configuration::get('ASLO_PROD_FOR_OPTION' . AgileSellerListOptions::ASLO_OPTION_LIST));
$wheres = $wheres . '
AND (slb.status = ' . AgileSellerListOptions::ASLO_STATUS_IN_EFFECT . ' OR IFNULL(po.id_owner,0) = 0 OR ' . $aslo_list_prod_id . '=' . AgileSellerListOptions::ASLO_ALWAYS_FREE . ')
';
}
$location_conditions = '';
switch ($this->location_level) {
case 'country':
if ((int) $this->id_location > 0) {
$location_conditions = ' AND si.id_country=' . (int) $this->id_location;
}
break;
case 'state':
if ((int) $this->id_location > 0) {
$location_conditions = ' AND si.id_state=' . (int) $this->id_location;
}
break;
case 'city':
if (!empty($this->id_location)) {
$location_conditions = ' AND sil.city=\'' . $this->id_location . '\'';
}
break;
case 'sellertype':
if (!empty($this->id_location)) {
$location_conditions = ' AND si.id_sellertype1=' . $this->id_location;
}
break;
case 'custom':
if (!empty($this->id_location)) {
if (AgileMultipleShop::SHOP_BY_CUSTOM_LANG) {
$location_conditions = ' AND sil.' . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . '=\'' . $this->id_location . '\'';
} else {
$location_conditions = ' AND si.' . AgileMultipleShop::SHOP_BY_CUSTOM_FIELD . '=\'' . $this->id_location . '\'';
}
}
break;
}
if ($getTotal) {
$sql = '
SELECT COUNT(po.`id_product`) AS total
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_owner` po ON p.`id_product` = po.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo` si ON si.`id_seller` = po.`id_owner`
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = ' . $cookie->id_lang . '
' . $joins . '
WHERE p.active=1
' . $location_conditions . '
' . ($active ? ' AND p.`active` = 1' : '') . '
' . $requiredcond . '
' . $wheres . '
';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return isset($result) ? $result['total'] : 0;
}
$sql = '
SELECT p.*, pa.`id_product_attribute`, 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`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, 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 AS new,
(p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice, si.`id_seller`
FROM `' . _DB_PREFIX_ . 'product_owner` po
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo` si ON si.`id_seller` = po.`id_owner`
LEFT JOIN `' . _DB_PREFIX_ . 'sellerinfo_lang` sil ON si.`id_sellerinfo` = sil.`id_sellerinfo` AND sil.id_lang = ' . $cookie->id_lang . '
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = po.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) (_PS_VERSION_ > '1.5' ? Context::getContext()->country->id : Country::getDefaultCountryId()) . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = ' . (int) $cookie->id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
' . $joins . '
WHERE p.active=1
' . $location_conditions . '
' . $requiredcond . '
' . $wheres . '
';
if ($random === true) {
$sql .= ' ORDER BY RAND()';
$sql .= ' LIMIT 0, ' . (int) $randomNumberProducts;
} else {
$sql .= ' ORDER BY ' . (isset($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . pSQL($orderBy) . '` ' . pSQL($orderWay) . '
LIMIT ' . ((int) $p - 1) * (int) $n . ',' . (int) $n;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
if ($orderBy == 'orderprice') {
Tools::orderbyPrice($result, $orderWay);
}
if (!$result) {
return false;
}
$last_result = Product::getProductsProperties($id_lang, $result);
foreach ($last_result as $key => $value) {
$seller_id = $value['id_seller'];
$flagIds = Product::getFlagsId($seller_id);
foreach ($flagIds as $k => $flgId) {
$last_result[$key]['img_exist'][$k] = file_exists(_PS_ROOT_DIR_ . DS . 'flag' . DS . 'mini' . DS . $flgId . '.jpg') ? 1 : 0;
$last_result[$key]['img_name'][$k] = $flgId . '.jpg';
}
}
// die('<pre>' . print_r($last_result, true));
return $last_result;
}
public function initContent()
{
parent::initContent();
$this->setTemplate(_PS_THEME_DIR_ . 'category.tpl');
if (!$this->customer_access) {
return;
}
if (isset($this->context->cookie->id_compare)) {
$this->context->smarty->assign('compareProducts', CompareProduct::getCompareProducts((int) $this->context->cookie->id_compare));
}
$this->productSort();
// Product sort must be called before assignProductList()
$this->assignScenes();
$this->assignSubcategories();
$this->assignProductList();
foreach ($this->cat_products as $key => $value) {
$seller_id = $value['id_seller'];
$flagIds = Product::getFlagsId($seller_id);
foreach ($flagIds as $k => $flgId) {
$this->cat_products[$key]['img_exist'][$k] = file_exists(_PS_ROOT_DIR_ . DS . 'flag' . DS . 'mini' . DS . $flgId . '.jpg') ? 1 : 0;
$this->cat_products[$key]['img_name'][$k] = $flgId . '.jpg';
}
}
$this->context->smarty->assign(array('category' => $this->category, 'description_short' => Tools::truncateString($this->category->description, 350), 'products' => isset($this->cat_products) && $this->cat_products ? $this->cat_products : null, 'id_category' => (int) $this->category->id, 'id_category_parent' => (int) $this->category->id_parent, 'return_category_name' => Tools::safeOutput($this->category->name), 'path' => Tools::getPath($this->category->id), 'add_prod_display' => Configuration::get('PS_ATTRIBUTE_CATEGORY_DISPLAY'), 'categorySize' => Image::getSize(ImageType::getFormatedName('category')), 'mediumSize' => Image::getSize(ImageType::getFormatedName('medium')), 'thumbSceneSize' => Image::getSize(ImageType::getFormatedName('m_scene')), 'homeSize' => Image::getSize(ImageType::getFormatedName('home')), 'allow_oosp' => (int) Configuration::get('PS_ORDER_OUT_OF_STOCK'), 'comparator_max_item' => (int) Configuration::get('PS_COMPARATOR_MAX_ITEM'), 'suppliers' => Supplier::getSuppliers(), 'body_classes' => array($this->php_self . '-' . $this->category->id, $this->php_self . '-' . $this->category->link_rewrite)));
}
