public function frontGetSpecialProducts($categoryIds = 0, $params = null, $id_lang, $p, $n, $beginning = null, $ending = null, $get_total = false, $active = true, Context $context = null)
{
if (!$categoryIds) {
return array();
}
if (!$context) {
$context = Context::getContext();
}
$where = "";
if ($params) {
if ($params->displayOnly == 'condition-new') {
$where .= " AND p.condition = 'new'";
} elseif ($params->displayOnly == 'condition-used') {
$where .= " AND p.condition = 'used'";
} elseif ($params->displayOnly == 'condition-refurbished') {
$where .= " AND p.condition = 'refurbished'";
}
}
$current_date = date('Y-m-d H:i:s');
$product_reductions = Product::_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true);
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') . ')';
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if ($p < 1) {
$p = 1;
}
$order_by = 'sp.reduction';
$order_way = 'DESC';
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 INNER JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.id_product = sp.id_product)
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` IN (' . implode(', ', $categoryIds) . ') ' . $where . ' AND product_shop.`visibility` IN ("both", "catalog")' . ' 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
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('-' . (Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int) Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY')) . '" as new,
product_shop.`on_sale`, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, product_shop.price AS orderprice, sp.reduction
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN (`' . _DB_PREFIX_ . 'product` p INNER JOIN `' . _DB_PREFIX_ . 'specific_price` sp On p.id_product = sp.id_product)
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') . '
' . 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 . ($ids_product ? $ids_product : '') . ' AND cp.`id_category` IN (' . implode(', ', $categoryIds) . ')' . $where . ' AND product_shop.`active` = 1' . ($ids_product ? $ids_product : '') . ' AND product_shop.`visibility` IN ("both", "catalog")' . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '') . ' GROUP BY product_shop.id_product' . ' ORDER BY `' . bqSQL($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 array();
}
return Product::getProductsProperties($id_lang, $result);
}
/**
* Get prices drop
*
* @param integer $id_lang Language id
* @param integer $pageNumber Start from (optional)
* @param integer $nbProducts Number of products to return (optional)
* @param boolean $count Only in order to get total number (optional)
* @return array Prices drop
*/
public static function getPricesDrop($id_lang, $page_number = 0, $nb_products = 10, $count = false, $order_by = null, $order_way = null, $beginning = false, $ending = false, Context $context = null)
{
if (!Validate::isBool($count)) {
die(Tools::displayError());
}
if (!$context) {
$context = Context::getContext();
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
if (empty($order_by) || $order_by == 'position') {
$order_by = 'price';
}
if (empty($order_way)) {
$order_way = 'DESC';
}
if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add') {
$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());
}
$current_date = date('Y-m-d H:i:s');
$ids_product = Product::_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context);
$tab_id_product = array();
foreach ($ids_product as $product) {
if (is_array($product)) {
$tab_id_product[] = (int) $product['id_product'];
} else {
$tab_id_product[] = (int) $product;
}
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
if ($count) {
$sql = 'SELECT COUNT(DISTINCT p.`id_product`) AS nb
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
WHERE product_shop.`active` = 1
AND product_shop.`show_price` = 1
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
' . (!$beginning && !$ending ? 'AND p.`id_product` IN(' . (is_array($tab_id_product) && count($tab_id_product) ? implode(', ', $tab_id_product) : 0) . ')' : '') . '
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` ' . $sql_groups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return (int) $result['nb'];
}
if (strpos($order_by, '.') > 0) {
$order_by = explode('.', $order_by);
$order_by = pSQL($order_by[0]) . '.`' . pSQL($order_by[1]) . '`';
}
$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`, i.`id_image`, il.`legend`, t.`rate`, m.`name` AS manufacturer_name,
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
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
' . Product::sqlStock('p', 0, false, $context->shop) . '
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` AND i.`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_ . 'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = ' . (int) Context::getContext()->country->id . '
AND tr.`id_state` = 0)
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
WHERE product_shop.`active` = 1
AND product_shop.`show_price` = 1
' . ($front ? ' AND p.`visibility` IN ("both", "catalog")' : '') . '
' . (!$beginning && !$ending ? ' AND p.`id_product` IN (' . (is_array($tab_id_product) && count($tab_id_product) ? implode(', ', $tab_id_product) : 0) . ')' : '') . '
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` ' . $sql_groups . '
)
ORDER BY ' . (isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . 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 ($order_by == 'price') {
Tools::orderbyPrice($result, $order_way);
}
if (!$result) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getRandomSpecial($id_lang, $beginning = false, $ending = false, Context $context = null)
{
if (!Module::isInstalled('agilemultipleseller')) {
return parent::getRandomSpecial($id_lang, $beginning, $ending, $context);
}
$agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p", true);
if (!$context) {
$context = Context::getContext();
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
$current_date = date('Y-m-d H:i:s');
$product_reductions = Product::_getProductIdByDate(!$beginning ? $current_date : $beginning, !$ending ? $current_date : $ending, $context, true);
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') . ')';
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
$sql = 'SELECT product_shop.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (product_shop.id_product = pa.id_product)
' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1') . '
' . $agile_sql_parts['joins'] . '
WHERE product_shop.`active` = 1
' . ($ids_product ? $ids_product : '') . '
' . $agile_sql_parts['wheres'] . '
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` ' . $sql_groups . '
)
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
GROUP BY product_shop.id_product
ORDER BY RAND()';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
if (!($id_product = $result['id_product'])) {
return false;
}
$sql = 'SELECT p.*, product_shop.*, stock.`out_of_stock` out_of_stock, pl.`description`, pl.`description_short`,
pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
p.`ean13`, p.`upc`, MAX(image_shop.`id_image`) id_image, il.`legend`,
DATEDIFF(product_shop.`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
FROM `' . _DB_PREFIX_ . 'product` p
LEFT 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') . '
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 . ')
' . Product::sqlStock('p', 0) . '
' . $agile_sql_parts['joins'] . '
WHERE p.id_product = ' . (int) $id_product . '
' . $agile_sql_parts['wheres'] . '
GROUP BY product_shop.id_product';
$row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
if (!$row) {
return false;
}
if ($result['id_product_attribute']) {
$row['id_product_attribute'] = $result['id_product_attribute'];
}
return Product::getProductProperties($id_lang, $row);
} else {
return false;
}
}
