/**
* Get product accessories
*
* @param integer $id_lang Language id
* @return array Product accessories
*/
public static function getItems($iIdProduct, $id_lang, $active = true, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
$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.`available_now`, pl.`available_later`,
MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` as manufacturer_name, 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
FROM `' . _DB_PREFIX_ . 'now_ideas_or_tips`
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = `id_product_2`
' . Shop::addSqlAssociation('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') . '
)
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_ . '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_ . 'manufacturer` m ON (p.`id_manufacturer`= m.`id_manufacturer`)
' . Product::sqlStock('p', 0) . '
WHERE `id_product_1` = ' . (int) $iIdProduct . ($active ? ' AND product_shop.`active` = 1 AND product_shop.`visibility` != \'none\'' : '') . '
GROUP BY product_shop.id_product';
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
foreach ($result as &$row) {
$row['id_product_attribute'] = Product::getDefaultAttribute((int) $row['id_product']);
}
return Product::getProductsProperties($id_lang, $result);
}
public function renderContent($setting)
{
$t = array('product_id' => 0, 'image_height' => '320', 'image_width' => 300);
$setting = array_merge($t, $setting);
$id_lang = (int) $this->lang_id;
$id_product = $setting['product_id'];
$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) . '
WHERE p.id_product = ' . (int) $id_product . '
GROUP BY product_shop.id_product';
$row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
if (!$row) {
return false;
}
if (isset($row['id_product_attribute']) && $row['id_product_attribute']) {
$row['id_product_attribute'] = $row['id_product_attribute'];
}
$p = Product::getProductProperties($id_lang, $row);
$setting['product'] = $p;
$output = array('type' => 'product', 'data' => $setting);
return $output;
}
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);
}
private function getProducts($id_lang)
{
$sql = 'SELECT p.`id_product`, p.reference, pl.`name`, IFNULL(stock.quantity, 0) as quantity
FROM `' . _DB_PREFIX_ . 'product` p
' . Product::sqlStock('p', 0) . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON p.`id_product` = pl.`id_product`' . Shop::addSqlRestrictionOnLang('pl') . '
' . Shop::addSqlAssociation('product', 'p') . '
' . (Tools::getValue('id_category') ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`' : '') . '
WHERE pl.`id_lang` = ' . (int) $id_lang . '
' . (Tools::getValue('id_category') ? 'AND cp.id_category = ' . (int) Tools::getValue('id_category') : '') . '
ORDER BY pl.`name`';
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
}
public function getData()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$date_between = $this->getDate();
$array_date_between = explode(' AND ', $date_between);
$this->query = 'SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, pl.name,
ROUND(AVG(od.product_price / o.conversion_rate), 2) as avgPriceSold,
IFNULL(stock.quantity, 0) as quantity,
IFNULL(SUM(od.product_quantity), 0) AS totalQuantitySold,
ROUND(IFNULL(IFNULL(SUM(od.product_quantity), 0) / (1 + LEAST(TO_DAYS(' . $array_date_between[1] . '), TO_DAYS(NOW())) - GREATEST(TO_DAYS(' . $array_date_between[0] . '), TO_DAYS(product_shop.date_add))), 0), 2) as averageQuantitySold,
ROUND(IFNULL(SUM((od.product_price * od.product_quantity) / o.conversion_rate), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.counter), 0)
FROM ' . _DB_PREFIX_ . 'page pa
LEFT JOIN ' . _DB_PREFIX_ . 'page_viewed pv ON pa.id_page = pv.id_page
LEFT JOIN ' . _DB_PREFIX_ . 'date_range dr ON pv.id_date_range = dr.id_date_range
WHERE pa.id_object = p.id_product AND pa.id_page_type = ' . (int) Page::getPageTypeByName('product') . '
AND dr.time_start BETWEEN ' . $date_between . '
AND dr.time_end BETWEEN ' . $date_between . '
) AS totalPageViewed,
product_shop.active
FROM ' . _DB_PREFIX_ . 'product p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->getLang() . ' ' . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON od.product_id = p.id_product
LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON od.id_order = o.id_order
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
' . Product::sqlStock('p', 0) . '
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $date_between . '
GROUP BY od.product_id';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . bqSQL($this->_sort) . '`';
if (isset($this->_direction) && Validate::isSortDirection($this->_direction)) {
$this->query .= ' ' . $this->_direction;
}
}
if (($this->_start === 0 || Validate::IsUnsignedInt($this->_start)) && Validate::IsUnsignedInt($this->_limit)) {
$this->query .= ' LIMIT ' . (int) $this->_start . ', ' . (int) $this->_limit;
}
$values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
foreach ($values as &$value) {
$value['avgPriceSold'] = Tools::displayPrice($value['avgPriceSold'], $currency);
$value['totalPriceSold'] = Tools::displayPrice($value['totalPriceSold'], $currency);
}
unset($value);
$this->_values = $values;
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
/**
* Get data source:
*/
function getProducts($where = '', $limiStart = 0, $limit = 10, $order = '')
{
global $cookie, $link;
$id_lang = intval($cookie->id_lang);
$context = Context::getContext();
$id_country = (int) $context->country->id;
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`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(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,
(product_shop.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) 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') . '
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) . '
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` 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->country->id . '
AND tr.`id_state` = 0
AND tr.`zipcode_from` = 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) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT JOIN `' . _DB_PREFIX_ . 'product_sale` ps ON ps.`id_product` = p.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'feature_product` fp ON fp.`id_product` = p.`id_product`
WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND ((product_attribute_shop.id_product_attribute IS NOT NULL OR pa.id_product_attribute IS NULL)
OR (product_attribute_shop.id_product_attribute IS NULL AND pa.default_on=1))
AND product_shop.`active` = 1' . $where . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ' ORDER BY ' . $order . ' LIMIT ' . $limiStart . ',' . $limit;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
return Product::getProductsProperties($id_lang, $result);
}
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;
}
/**
* Update product quantity
*
* @param integer $quantity Quantity to add (or substract)
* @param integer $id_product Product ID
* @param integer $id_product_attribute Attribute ID if needed
* @param string $operator Indicate if quantity must be increased or decreased
*/
public function updateQty($quantity, $id_product, $id_product_attribute = null, $id_customization = false, $operator = 'up', $id_address_delivery = 0, Shop $shop = null, $auto_add_cart_rule = true)
{
if (!$shop) {
$shop = Context::getContext()->shop;
}
if (Context::getContext()->customer->id) {
if ($id_address_delivery == 0 && (int) $this->id_address_delivery) {
// The $id_address_delivery is null, use the cart delivery address
$id_address_delivery = $this->id_address_delivery;
} elseif ($id_address_delivery == 0) {
// The $id_address_delivery is null, get the default customer address
$id_address_delivery = (int) Address::getFirstCustomerAddressId((int) Context::getContext()->customer->id);
} elseif (!Customer::customerHasAddress(Context::getContext()->customer->id, $id_address_delivery)) {
// The $id_address_delivery must be linked with customer
$id_address_delivery = 0;
}
}
$quantity = (int) $quantity;
$id_product = (int) $id_product;
$id_product_attribute = (int) $id_product_attribute;
$product = new Product($id_product, false, Configuration::get('PS_LANG_DEFAULT'), $shop->id);
if ($id_product_attribute) {
$combination = new Combination((int) $id_product_attribute);
if ($combination->id_product != $id_product) {
return false;
}
}
/* If we have a product combination, the minimal quantity is set with the one of this combination */
if (!empty($id_product_attribute)) {
$minimal_quantity = (int) Attribute::getAttributeMinimalQty($id_product_attribute);
} else {
$minimal_quantity = (int) $product->minimal_quantity;
}
if (!Validate::isLoadedObject($product)) {
die(Tools::displayError());
}
if (isset(self::$_nbProducts[$this->id])) {
unset(self::$_nbProducts[$this->id]);
}
if (isset(self::$_totalWeight[$this->id])) {
unset(self::$_totalWeight[$this->id]);
}
if ((int) $quantity <= 0) {
return $this->deleteProduct($id_product, $id_product_attribute, (int) $id_customization);
} elseif (!$product->available_for_order || Configuration::get('PS_CATALOG_MODE')) {
return false;
} else {
/* Check if the product is already in the cart */
$result = $this->containsProduct($id_product, $id_product_attribute, (int) $id_customization, (int) $id_address_delivery);
/* Update quantity if product already exist */
if ($result) {
if ($operator == 'up') {
$sql = 'SELECT stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM ' . _DB_PREFIX_ . 'product p
' . Product::sqlStock('p', $id_product_attribute, true, $shop) . '
WHERE p.id_product = ' . $id_product;
$result2 = Db::getInstance()->getRow($sql);
$product_qty = (int) $result2['quantity'];
// Quantity for product pack
if (Pack::isPack($id_product)) {
$product_qty = Pack::getQuantity($id_product, $id_product_attribute);
}
$new_qty = (int) $result['quantity'] + (int) $quantity;
$qty = '+ ' . (int) $quantity;
if (!Product::isAvailableWhenOutOfStock((int) $result2['out_of_stock'])) {
if ($new_qty > $product_qty) {
return false;
}
}
} else {
if ($operator == 'down') {
$qty = '- ' . (int) $quantity;
$new_qty = (int) $result['quantity'] - (int) $quantity;
if ($new_qty < $minimal_quantity && $minimal_quantity > 1) {
return -1;
}
} else {
return false;
}
}
/* Delete product from cart */
if ($new_qty <= 0) {
return $this->deleteProduct((int) $id_product, (int) $id_product_attribute, (int) $id_customization);
} else {
if ($new_qty < $minimal_quantity) {
return -1;
} else {
Db::getInstance()->execute('
UPDATE `' . _DB_PREFIX_ . 'cart_product`
SET `quantity` = `quantity` ' . $qty . ', `date_add` = NOW()
WHERE `id_product` = ' . (int) $id_product . (!empty($id_product_attribute) ? ' AND `id_product_attribute` = ' . (int) $id_product_attribute : '') . '
AND `id_cart` = ' . (int) $this->id . (Configuration::get('PS_ALLOW_MULTISHIPPING') && $this->isMultiAddressDelivery() ? ' AND `id_address_delivery` = ' . (int) $id_address_delivery : '') . '
LIMIT 1');
}
}
} elseif ($operator == 'up') {
$sql = 'SELECT stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM ' . _DB_PREFIX_ . 'product p
' . Product::sqlStock('p', $id_product_attribute, true, $shop) . '
WHERE p.id_product = ' . $id_product;
$result2 = Db::getInstance()->getRow($sql);
// Quantity for product pack
if (Pack::isPack($id_product)) {
$result2['quantity'] = Pack::getQuantity($id_product, $id_product_attribute);
}
if (!Product::isAvailableWhenOutOfStock((int) $result2['out_of_stock'])) {
if ((int) $quantity > $result2['quantity']) {
return false;
}
}
if ((int) $quantity < $minimal_quantity) {
return -1;
}
$result_add = Db::getInstance()->insert('cart_product', array('id_product' => (int) $id_product, 'id_product_attribute' => (int) $id_product_attribute, 'id_cart' => (int) $this->id, 'id_address_delivery' => (int) $id_address_delivery, 'id_shop' => $shop->id, 'quantity' => (int) $quantity, 'date_add' => date('Y-m-d H:i:s')));
if (!$result_add) {
return false;
}
}
}
// refresh cache of self::_products
$this->_products = $this->getProducts(true);
$this->update(true);
$context = Context::getContext()->cloneContext();
$context->cart = $this;
Cache::clean('getContextualValue_*');
if ($auto_add_cart_rule) {
CartRule::autoAddToCart($context);
}
if ($product->customizable) {
return $this->_updateCustomizationQuantity((int) $quantity, (int) $id_customization, (int) $id_product, (int) $id_product_attribute, (int) $id_address_delivery, $operator);
} else {
return true;
}
}
public static function getProducts($id_supplier, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $active_category = true)
{
$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());
}
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1';
/* Return only the number of products */
if ($get_total) {
$sql = '
SELECT 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_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp 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' : '') . '
WHERE cg.`id_group` ' . $sql_groups . '
)';
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
return (int) count($result);
}
$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.';
}
$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`,
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
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_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') . '
WHERE ps.`id_supplier` = ' . (int) $id_supplier . ($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_group` cg
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp 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' : '') . '
WHERE cg.`id_group` ' . $sql_groups . '
)
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 function hookDashboardData($params)
{
if (Tools::strlen($params['date_from']) == 10) {
$params['date_from'] .= ' 00:00:00';
}
if (Tools::strlen($params['date_to']) == 10) {
$params['date_to'] .= ' 23:59:59';
}
if (Configuration::get('PS_DASHBOARD_SIMULATION')) {
$days = (strtotime($params['date_to']) - strtotime($params['date_from'])) / 3600 / 24;
$online_visitor = rand(10, 50);
$visits = rand(200, 2000) * $days;
return array('data_value' => array('pending_orders' => round(rand(0, 5)), 'return_exchanges' => round(rand(0, 5)), 'abandoned_cart' => round(rand(5, 50)), 'products_out_of_stock' => round(rand(1, 10)), 'new_messages' => round(rand(1, 10) * $days), 'product_reviews' => round(rand(5, 50) * $days), 'new_customers' => round(rand(1, 5) * $days), 'online_visitor' => round($online_visitor), 'active_shopping_cart' => round($online_visitor / 10), 'new_registrations' => round(rand(1, 5) * $days), 'total_suscribers' => round(rand(200, 2000)), 'visits' => round($visits), 'unique_visitors' => round($visits * 0.6)), 'data_trends' => array('orders_trends' => array('way' => 'down', 'value' => 0.42)), 'data_list_small' => array('dash_traffic_source' => array('<i class="icon-circle" style="color:' . self::$colors[0] . '"></i> prestashop.com' => round($visits / 2), '<i class="icon-circle" style="color:' . self::$colors[1] . '"></i> google.com' => round($visits / 3), '<i class="icon-circle" style="color:' . self::$colors[2] . '"></i> Direct Traffic' => round($visits / 4))), 'data_chart' => array('dash_trends_chart1' => array('chart_type' => 'pie_chart_trends', 'data' => array(array('key' => 'prestashop.com', 'y' => round($visits / 2), 'color' => self::$colors[0]), array('key' => 'google.com', 'y' => round($visits / 3), 'color' => self::$colors[1]), array('key' => 'Direct Traffic', 'y' => round($visits / 4), 'color' => self::$colors[2])))));
}
$gapi = Module::isInstalled('gapi') ? Module::getInstanceByName('gapi') : false;
if (Validate::isLoadedObject($gapi) && $gapi->isConfigured()) {
$visits = $unique_visitors = $online_visitor = 0;
if ($result = $gapi->requestReportData('', 'ga:visits,ga:visitors', substr($params['date_from'], 0, 10), substr($params['date_to'], 0, 10), null, null, 1, 1)) {
$visits = $result[0]['metrics']['visits'];
$unique_visitors = $result[0]['metrics']['visitors'];
}
} else {
$row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT COUNT(*) as visits, COUNT(DISTINCT `id_guest`) as unique_visitors
FROM `' . _DB_PREFIX_ . 'connections`
WHERE `date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(false));
extract($row);
}
// Online visitors is only available with Analytics Real Time still in private beta at this time (October 18th, 2013).
// if ($result = $gapi->requestReportData('', 'ga:activeVisitors', null, null, null, null, 1, 1))
// $online_visitor = $result[0]['metrics']['activeVisitors'];
if ($maintenance_ips = Configuration::get('PS_MAINTENANCE_IP')) {
$maintenance_ips = implode(',', array_map('ip2long', array_map('trim', explode(',', $maintenance_ips))));
}
if (Configuration::get('PS_STATSDATA_CUSTOMER_PAGESVIEWS')) {
$sql = 'SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, pt.name as page
FROM `' . _DB_PREFIX_ . 'connections` c
LEFT JOIN `' . _DB_PREFIX_ . 'connections_page` cp ON c.id_connections = cp.id_connections
LEFT JOIN `' . _DB_PREFIX_ . 'page` p ON p.id_page = cp.id_page
LEFT JOIN `' . _DB_PREFIX_ . 'page_type` pt ON p.id_page_type = pt.id_page_type
INNER JOIN `' . _DB_PREFIX_ . 'guest` g ON c.id_guest = g.id_guest
WHERE (g.id_customer IS NULL OR g.id_customer = 0)
' . Shop::addSqlRestriction(false, 'c') . '
AND cp.`time_end` IS NULL
AND TIME_TO_SEC(TIMEDIFF(\'' . pSQL(date('Y-m-d H:i:00', time())) . '\', cp.`time_start`)) < 900
' . ($maintenance_ips ? 'AND c.ip_address NOT IN (' . preg_replace('/[^,0-9]/', '', $maintenance_ips) . ')' : '') . '
GROUP BY c.id_connections
ORDER BY c.date_add DESC';
} else {
$sql = 'SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, "-" as page
FROM `' . _DB_PREFIX_ . 'connections` c
INNER JOIN `' . _DB_PREFIX_ . 'guest` g ON c.id_guest = g.id_guest
WHERE (g.id_customer IS NULL OR g.id_customer = 0)
' . Shop::addSqlRestriction(false, 'c') . '
AND TIME_TO_SEC(TIMEDIFF(\'' . pSQL(date('Y-m-d H:i:00', time())) . '\', c.`date_add`)) < 900
' . ($maintenance_ips ? 'AND c.ip_address NOT IN (' . preg_replace('/[^,0-9]/', '', $maintenance_ips) . ')' : '') . '
ORDER BY c.date_add DESC';
}
$results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
$online_visitor = Db::getInstance()->NumRows();
$pending_orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_state` os ON (o.current_state = os.id_order_state)
WHERE os.paid = 1 AND os.shipped = 0
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$abandoned_cart = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'cart`
WHERE `date_upd` BETWEEN "' . pSQL(date('Y-m-d H:i:s', strtotime('-' . (int) Configuration::get('DASHACTIVITY_CART_ABANDONED_MAX') . ' MIN'))) . '" AND "' . pSQL(date('Y-m-d H:i:s', strtotime('-' . (int) Configuration::get('DASHACTIVITY_CART_ABANDONED_MIN') . ' MIN'))) . '"
AND id_cart NOT IN (SELECT id_cart FROM `' . _DB_PREFIX_ . 'orders`)
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$return_exchanges = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_return` or2 ON o.id_order = or2.id_order
WHERE or2.`date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o'));
$products_out_of_stock = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT SUM(IF(IFNULL(stock.quantity, 0) > 0, 0, 1))
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON p.id_product = pa.id_product
' . Product::sqlStock('p', 'pa'));
$new_messages = AdminStatsController::getPendingMessages();
$active_shopping_cart = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'cart`
WHERE date_upd > "' . pSQL(date('Y-m-d H:i:s', strtotime('-' . (int) Configuration::get('DASHACTIVITY_CART_ACTIVE') . ' MIN'))) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$new_customers = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE `date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$new_registrations = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE `newsletter_date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
AND newsletter = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$total_suscribers = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'customer`
WHERE newsletter = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
if (Module::isInstalled('blocknewsletter')) {
$new_registrations += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'newsletter`
WHERE active = 1
AND `newsletter_date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
$total_suscribers += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'newsletter`
WHERE active = 1
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
}
$product_reviews = 0;
if (Module::isInstalled('productcomments')) {
$product_reviews += Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
SELECT COUNT(*)
FROM `' . _DB_PREFIX_ . 'product_comment` pc
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (pc.id_product = p.id_product)
' . Shop::addSqlAssociation('product', 'p') . '
WHERE pc.deleted = 0
AND pc.`date_add` BETWEEN "' . pSQL($params['date_from']) . '" AND "' . pSQL($params['date_to']) . '"
' . Shop::addSqlRestriction(Shop::SHARE_ORDER));
}
return array('data_value' => array('pending_orders' => (int) $pending_orders, 'return_exchanges' => (int) $return_exchanges, 'abandoned_cart' => (int) $abandoned_cart, 'products_out_of_stock' => (int) $products_out_of_stock, 'new_messages' => (int) $new_messages, 'product_reviews' => (int) $product_reviews, 'new_customers' => (int) $new_customers, 'online_visitor' => (int) $online_visitor, 'active_shopping_cart' => (int) $active_shopping_cart, 'new_registrations' => (int) $new_registrations, 'total_suscribers' => (int) $total_suscribers, 'visits' => (int) $visits, 'unique_visitors' => (int) $unique_visitors), 'data_trends' => array('orders_trends' => array('way' => 'down', 'value' => 0.42)), 'data_list_small' => array('dash_traffic_source' => $this->getTrafficSources($params['date_from'], $params['date_to'])), 'data_chart' => array('dash_trends_chart1' => $this->getChartTrafficSource($params['date_from'], $params['date_to'])));
}
/**
* 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 static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
//Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)
WHERE cg.`id_group` ' . $sql_groups);
$ids = array();
foreach ($products as $product) {
$ids[$product['id_product']] = 1;
}
$ids = array_keys($ids);
sort($ids);
$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';
//Main query
$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`,
ps.`quantity` AS sales, 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
FROM `' . _DB_PREFIX_ . 'product_sale` ps
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
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) . '
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_ . 'category_lang` cl
ON cl.`id_category` = product_shop.`id_category_default`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . '
WHERE product_shop.`active` = 1
AND p.`visibility` != \'none\'
AND p.`id_product` IN (' . $ids . ')
GROUP BY product_shop.id_product
ORDER BY sales DESC
LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products;
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if ($page_number < 0) {
$page_number = 0;
}
if ($nb_products < 1) {
$nb_products = 10;
}
// 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.id_product, IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`,
image_shop.`id_image` id_image, il.`legend`,
ps.`quantity` AS sales, 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`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity
FROM `' . _DB_PREFIX_ . 'product_sale` ps
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON ps.`id_product` = p.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
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_attribute` pa ON (product_attribute_shop.id_product_attribute=pa.id_product_attribute)
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_ . 'category_lang` cl
ON cl.`id_category` = product_shop.`id_category_default`
AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . 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`)';
}
$sql .= '
ORDER BY ps.quantity DESC
LIMIT ' . (int) ($page_number * $nb_products) . ', ' . (int) $nb_products;
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
return Product::getProductsProperties($id_lang, $result);
}
public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1, $checkAccess = true, Context $context = null)
{
global $cookie;
if (!$checkAccess or !$this->checkAccess($cookie->id_customer)) {
return false;
}
if (!$context) {
$context = Context::getContext();
}
$front = true;
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
if (Module::isInstalled('agilemembership') and $this->id == Configuration::get('AGILE_MEMBERSHIP_CID')) {
return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess);
}
if (Module::isInstalled('agileprepaidcredit') and $this->id == Configuration::getGlobalValue('AGILE_PCREDIT_CID')) {
return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess);
}
if (Module::isInstalled('agilesellerlistoptions') and $this->id == Configuration::get('ASLO_CATEGORY_ID')) {
return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess);
}
$agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p");
if (empty($agile_sql_parts['selects']) and empty($agile_sql_parts['joins']) and empty($agile_sql_parts['wheres'])) {
return parent::getProducts($id_lang, $p, $n, $orderBy, $orderWay, $getTotal, $active, $random, $randomNumberProducts, $checkAccess);
}
if (Module::isInstalled('agilesellerlistoptions')) {
require_once _PS_ROOT_DIR_ . "/modules/agilesellerlistoptions/agilesellerlistoptions.php";
if ($this->id <= 1 or $this->id == 2 or $this->id == (int) Configuration::get('PS_HOME_CATEGORY')) {
return AgileSellerListOptions::get_home_products($id_lang, $p, $n);
}
if (empty($orderBy) || $orderBy == 'position') {
$orderBy = 'position2';
}
}
if ($p < 1) {
$p = 1;
}
if (empty($orderBy)) {
$orderBy = 'position';
} else {
$orderBy = strtolower($orderBy);
}
if (empty($orderWay)) {
$orderWay = 'ASC';
}
if ($orderBy == 'id_product' or $orderBy == 'date_add' or $orderBy == 'date_upd') {
$orderByPrefix = 'p';
} elseif ($orderBy == 'name') {
$orderByPrefix = 'pl';
} elseif ($orderBy == 'manufacturer') {
$orderByPrefix = 'm';
$orderBy = 'name';
} elseif ($orderBy == 'position') {
$orderByPrefix = 'cp';
}
if ($orderBy == 'price') {
$orderBy = 'orderprice';
}
if (!Validate::isBool($active) or !Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) {
die(Tools::displayError());
}
$id_supplier = (int) Tools::getValue('id_supplier');
if ($getTotal) {
$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`
' . $agile_sql_parts['joins'] . '
WHERE cp.`id_category` = ' . (int) $this->id . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($active ? ' AND product_shop.`active` = 1' : '') . $agile_sql_parts['wheres'] . ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : '');
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql);
return isset($result) ? $result['total'] : 0;
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) 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`, MAX(image_shop.`id_image`) id_image,
il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
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, product_shop.price AS orderprice
' . $agile_sql_parts['selects'] . '
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p
ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
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) . '
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`
' . $agile_sql_parts['joins'] . '
WHERE product_shop.`id_shop` = ' . (int) $context->shop->id . '
' . $agile_sql_parts['wheres'] . '
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 : '') . ' GROUP BY product_shop.id_product';
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 (!$result) {
return array();
}
$seller = array();
$id_seller = array();
$link_seller = array();
foreach ($result as $row) {
$pid = $row['id_product'];
$seller[$pid] = isset($row['seller']) ? $row['seller'] : '';
$id_seller[$pid] = isset($row['id_seller']) ? intval($row['id_seller']) : 0;
$link_seller[$pid] = isset($row['has_sellerlink']) ? $row['has_sellerlink'] : '';
}
$resultsArray = Product::getProductsProperties((int) $id_lang, $result);
for ($idx = 0; $idx < count($resultsArray); $idx++) {
$pid = $resultsArray[$idx]['id_product'];
$resultsArray[$idx]['seller'] = $seller[$pid];
$resultsArray[$idx]['id_seller'] = $id_seller[$pid];
$resultsArray[$idx]['has_sellerlink'] = $link_seller[$pid];
}
$resultsArray = AgileSellerManager::prepareSellerRattingInfo($resultsArray);
return $resultsArray;
}
public function getCustomListProductsNoStock()
{
if (!Configuration::get('PS_STOCK_MANAGEMENT')) {
return;
}
$this->table = 'product';
$this->list_id = 'no_stock_products';
$this->className = 'Product';
$this->lang = true;
$this->identifier = 'id_product';
$this->_orderBy = 'id_product';
$this->_orderWay = 'DESC';
$this->show_toolbar = false;
$this->_list_index = 'index.php?controller=AdminProducts';
$this->_list_token = Tools::getAdminTokenLite('AdminProducts');
$this->addRowAction('edit');
$this->addRowAction('delete');
$this->fields_list = array('id_product' => array('title' => $this->trans('ID', array(), 'Admin.Global'), 'class' => 'fixed-width-xs', 'align' => 'center'), 'reference' => array('title' => $this->trans('Reference', array(), 'Admin.Global')), 'name' => array('title' => $this->trans('Name', array(), 'Admin.Global')), 'active' => array('title' => $this->trans('Status', array(), 'Admin.Global'), 'type' => 'bool', 'active' => 'status', 'align' => 'center', 'class' => 'fixed-width-xs'));
$this->clearFilters();
$this->_join = Shop::addSqlAssociation('product', 'a');
$this->_filter = 'AND EXISTS (
SELECT 1
FROM `' . _DB_PREFIX_ . 'product` p
' . Product::sqlStock('p') . '
WHERE a.id_product = p.id_product AND NOT EXISTS (
SELECT 1
FROM `' . _DB_PREFIX_ . 'product_attribute` pa WHERE pa.id_product = p.id_product
)
AND IFNULL(stock.quantity, 0) <= 0
)';
$this->toolbar_title = $this->trans('List of products without combinations and without available quantities for sale:', array(), 'Admin.Catalog.Feature');
return $this->renderList();
}
/**
* Admin panel product search
*
* @param integer $id_lang Language id
* @param string $query Search query
* @return array Matching products
*/
public static function searchByName($id_lang, $query, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
$sql = new DbQuery();
$sql->select('p.`id_product`, pl.`name`, p.`active`, p.`reference`, m.`name` AS manufacturer_name, stock.`quantity`, product_shop.advanced_stock_management, p.`customizable`');
$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) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
$sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');
$where = 'pl.`name` LIKE \'%' . pSQL($query) . '%\' OR p.`reference` LIKE \'%' . pSQL($query) . '%\' OR p.`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);
$sql->join(Product::sqlStock('p', 'pa', false, $context->shop));
$result = Db::getInstance()->executeS($sql);
if (!$result) {
return false;
}
$results_array = array();
foreach ($result as $row) {
$row['price_tax_incl'] = Product::getPriceStatic($row['id_product'], true, null, 2);
$row['price_tax_excl'] = Product::getPriceStatic($row['id_product'], false, null, 2);
$results_array[] = $row;
}
return $results_array;
}
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);
}
public function getRecentProducts($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();
}
$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 {
/* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
$order_by = strtolower($order_by);
}
if (empty($order_way)) {
$order_way = 'ASC';
}
// $order_by = 'dateadd';
//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');
/* 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`
' . ($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);
}
$sql = 'SELECT DISTINCT p.id_product, p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`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`,
il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
(SUM(pc.`grade`) / COUNT(pc.`grade`)) AS avg,
pc.`date_add` as dateadd,
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, 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') . '
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) . '
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`
LEFT JOIN `' . _DB_PREFIX_ . 'product_comment` pc
ON p.`id_product` = pc.`id_product`
AND product_shop.`id_shop` = ' . (int) $context->shop->id . '
AND (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop=' . (int) $context->shop->id . ')
AND (i.id_image IS NULL OR image_shop.id_shop=' . (int) $context->shop->id . ')
' . ($active ? ' AND product_shop.`active` = 1' : '') . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '');
$sql .= $active ? ' WHERE p.`active` = 1' : '';
$sql .= ' GROUP BY pc.id_product ';
if ($random === true) {
$sql .= ' ORDER BY RAND()';
$sql .= ' LIMIT 0, ' . (int) $random_number_products;
} else {
$sql .= ' ORDER BY `' . 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 array();
}
/* Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
public function getCustomListProductsNoStock()
{
if (!Configuration::get('PS_STOCK_MANAGEMENT')) {
return;
}
$this->table = 'product';
$this->className = 'Product';
$this->lang = true;
$this->identifier = 'id_product';
$this->_orderBy = 'id_product';
$this->_orderWay = 'DESC';
$this->show_toolbar = false;
$this->_list_index = 'index.php?controller=AdminProducts';
$this->_list_token = Tools::getAdminTokenLite('AdminProducts');
$this->addRowAction('edit');
$this->addRowAction('delete');
$this->fields_list = array('id_product' => array('title' => $this->l('ID'), 'width' => 50), 'reference' => array('title' => $this->l('Reference'), 'width' => 150), 'name' => array('title' => $this->l('Name')), 'active' => array('title' => $this->l('Status'), 'type' => 'bool', 'active' => 'status', 'width' => 50));
$this->clearFilters();
$this->_join = Shop::addSqlAssociation('product', 'a');
$this->_filter = 'AND a.id_product IN (
SELECT p.id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . Product::sqlStock('p') . '
WHERE p.id_product NOT IN (
SELECT DISTINCT(id_product)
FROM `' . _DB_PREFIX_ . 'product_attribute`
)
AND IFNULL(stock.quantity, 0) <= 0
)';
$this->tpl_list_vars = array('sub_title' => $this->l('List of products without attributes and without available quantities for sale:'));
return $this->renderList();
}
/**
* Return current category products
*
* @param integer $id_lang Language ID
* @param integer $p Page number
* @param integer $n Number of products per page
* @param boolean $get_total return the number of results instead of the results themself
* @param boolean $active return only active products
* @param boolean $random active a random filter for returned products
* @param int $random_number_products number of products to return if random is activated
* @param boolean $check_access set to false to return all products (even if customer hasn't access)
* @return mixed Products or number of products
*/
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 = 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 {
/* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
$order_by = strtolower($order_by);
}
if (empty($order_way)) {
$order_way = 'ASC';
}
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_prefix = 'm';
$order_by = 'name';
} elseif ($order_by == 'position') {
$order_by_prefix = 'cp';
}
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');
/* 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);
}
$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`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(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,
(product_shop.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) 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') . '
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) . '
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`
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->country->id . '
AND tr.`id_state` = 0
AND tr.`zipcode_from` = 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) $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 ((product_attribute_shop.id_product_attribute IS NOT NULL OR pa.id_product_attribute IS NULL)
OR (product_attribute_shop.id_product_attribute IS NULL AND pa.default_on=1))
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()';
$sql .= ' LIMIT 0, ' . (int) $random_number_products;
} else {
$sql .= ' ORDER BY ' . (isset($order_by_prefix) ? $order_by_prefix . '.' : '') . '`' . pSQL($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 false;
}
/* Modify SQL result */
return Product::getProductsProperties($id_lang, $result);
}
public function hookAdminStatsModules()
{
if (Tools::isSubmit('submitCheckup')) {
$confs = array('CHECKUP_DESCRIPTIONS_LT', 'CHECKUP_DESCRIPTIONS_GT', 'CHECKUP_IMAGES_LT', 'CHECKUP_IMAGES_GT', 'CHECKUP_SALES_LT', 'CHECKUP_SALES_GT', 'CHECKUP_STOCK_LT', 'CHECKUP_STOCK_GT');
foreach ($confs as $confname) {
Configuration::updateValue($confname, (int) Tools::getValue($confname));
}
echo '<div class="conf confirm"> ' . $this->l('Configuration updated') . '</div>';
}
if (Tools::isSubmit('submitCheckupOrder')) {
$this->context->cookie->checkup_order = (int) Tools::getValue('submitCheckupOrder');
echo '<div class="conf confirm"> ' . $this->l('Configuration updated') . '</div>';
}
if (!isset($this->context->cookie->checkup_order)) {
$this->context->cookie->checkup_order = 1;
}
$db = Db::getInstance(_PS_USE_SQL_SLAVE_);
$employee = Context::getContext()->employee;
$prop30 = (strtotime($employee->stats_date_to . ' 23:59:59') - strtotime($employee->stats_date_from . ' 00:00:00')) / 60 / 60 / 24 / 30;
// Get languages
$sql = 'SELECT l.*
FROM ' . _DB_PREFIX_ . 'lang l' . Shop::addSqlAssociation('lang', 'l');
$languages = $db->executeS($sql);
$array_colors = array(0 => '<img src="../modules/' . $this->name . '/img/red.png" alt="' . $this->l('Bad') . '" />', 1 => '<img src="../modules/' . $this->name . '/img/orange.png" alt="' . $this->l('Average') . '" />', 2 => '<img src="../modules/' . $this->name . '/img/green.png" alt="' . $this->l('Good') . '" />');
$token_products = Tools::getAdminToken('AdminProducts' . (int) Tab::getIdFromClassName('AdminProducts') . (int) Context::getContext()->employee->id);
$divisor = 4;
$totals = array('products' => 0, 'active' => 0, 'images' => 0, 'sales' => 0, 'stock' => 0);
foreach ($languages as $language) {
$divisor++;
$totals['description_' . $language['iso_code']] = 0;
}
$order_by = 'p.id_product';
if ($this->context->cookie->checkup_order == 2) {
$order_by = 'pl.name';
} else {
if ($this->context->cookie->checkup_order == 3) {
$order_by = 'nbSales DESC';
}
}
// Get products stats
$sql = 'SELECT p.id_product, product_shop.active, pl.name, (
SELECT COUNT(*)
FROM ' . _DB_PREFIX_ . 'image i
' . Shop::addSqlAssociation('image', 'i') . '
WHERE i.id_product = p.id_product
) as nbImages, (
SELECT SUM(od.product_quantity)
FROM ' . _DB_PREFIX_ . 'orders o
LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON o.id_order = od.id_order
WHERE od.product_id = p.id_product
AND o.invoice_date BETWEEN ' . ModuleGraph::getDateBetween() . '
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
) as nbSales,
IFNULL(stock.quantity, 0) as stock
FROM ' . _DB_PREFIX_ . 'product p
' . Shop::addSqlAssociation('product', 'p') . '
' . Product::sqlStock('p', 0) . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl
ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl') . ')
ORDER BY ' . $order_by;
$result = $db->executeS($sql);
if (!$result) {
return $this->l('No product was found.');
}
$array_conf = array('DESCRIPTIONS' => array('name' => $this->l('Descriptions'), 'text' => $this->l('chars (without HTML)')), 'IMAGES' => array('name' => $this->l('Images'), 'text' => $this->l('images')), 'SALES' => array('name' => $this->l('Sales'), 'text' => $this->l('orders / month')), 'STOCK' => array('name' => $this->l('Available quantity for sale'), 'text' => $this->l('items')));
$this->html = '
<div class="panel-heading">' . $this->displayName . '
</div>
<form action="' . Tools::safeOutput(AdminController::$currentIndex . '&token=' . Tools::getValue('token') . '&module=' . $this->name) . '" method="post" class="checkup form-horizontal">
<table class="table checkup">
<thead>
<tr>
<th></th>
<th><span class="title_box active">' . $array_colors[0] . ' ' . $this->l('Not enough') . '</span></th>
<th><span class="title_box active">' . $array_colors[2] . ' ' . $this->l('Alright') . '</span></th>
</tr>
</thead>';
foreach ($array_conf as $conf => $translations) {
$this->html .= '
<tbody>
<tr>
<td>
<label class="control-label col-lg-12">' . $translations['name'] . '</label>
</td>
<td>
<div class="row">
<div class="col-lg-11 input-group">
<span class="input-group-addon">' . $this->l('Less than') . '</span>
<input type="text" name="CHECKUP_' . $conf . '_LT" value="' . Tools::safeOutput(Tools::getValue('CHECKUP_' . $conf . '_LT', Configuration::get('CHECKUP_' . $conf . '_LT'))) . '" />
<span class="input-group-addon">' . $translations['text'] . '</span>
</div>
</div>
</td>
<td>
<div class="row">
<div class="col-lg-12 input-group">
<span class="input-group-addon">' . $this->l('Greater than') . '</span>
<input type="text" name="CHECKUP_' . $conf . '_GT" value="' . Tools::safeOutput(Tools::getValue('CHECKUP_' . $conf . '_GT', Configuration::get('CHECKUP_' . $conf . '_GT'))) . '" />
<span class="input-group-addon">' . $translations['text'] . '</span>
</div>
</div>
</td>
</tr>
</tbody>';
}
$this->html .= '</table>
<button type="submit" name="submitCheckup" class="btn btn-default pull-right">
<i class="icon-save"></i> ' . $this->l('Save') . '
</button>
</form>
<form action="' . Tools::safeOutput(AdminController::$currentIndex . '&token=' . Tools::getValue('token') . '&module=' . $this->name) . '" method="post" class="form-horizontal alert">
<div class="row">
<div class="col-lg-12">
<label class="control-label pull-left">' . $this->l('Order by') . '</label>
<div class="col-lg-3">
<select name="submitCheckupOrder" onchange="this.form.submit();">
<option value="1">' . $this->l('ID') . '</option>
<option value="2" ' . ($this->context->cookie->checkup_order == 2 ? 'selected="selected"' : '') . '>' . $this->l('Name') . '</option>
<option value="3" ' . ($this->context->cookie->checkup_order == 3 ? 'selected="selected"' : '') . '>' . $this->l('Sales') . '</option>
</select>
</div>
</div>
</div>
</form>
<div style="overflow-x:auto">
<table class="table checkup2">
<thead>
<tr>
<th><span class="title_box active">' . $this->l('ID') . '</span></th>
<th><span class="title_box active">' . $this->l('Item') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Active') . '</span></th>';
foreach ($languages as $language) {
$this->html .= '<th><span class="title_box active">' . $this->l('Desc.') . ' (' . Tools::strtoupper($language['iso_code']) . ')</span></th>';
}
$this->html .= '
<th class="center"><span class="title_box active">' . $this->l('Images') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Sales') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Available quantity for sale') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Global') . '</span></th>
</tr>
</thead>
<tbody>';
foreach ($result as $row) {
$totals['products']++;
$scores = array('active' => $row['active'] ? 2 : 0, 'images' => $row['nbImages'] < Configuration::get('CHECKUP_IMAGES_LT') ? 0 : ($row['nbImages'] > Configuration::get('CHECKUP_IMAGES_GT') ? 2 : 1), 'sales' => $row['nbSales'] * $prop30 < Configuration::get('CHECKUP_SALES_LT') ? 0 : ($row['nbSales'] * $prop30 > Configuration::get('CHECKUP_SALES_GT') ? 2 : 1), 'stock' => $row['stock'] < Configuration::get('CHECKUP_STOCK_LT') ? 0 : ($row['stock'] > Configuration::get('CHECKUP_STOCK_GT') ? 2 : 1));
$totals['active'] += (int) $scores['active'];
$totals['images'] += (int) $scores['images'];
$totals['sales'] += (int) $scores['sales'];
$totals['stock'] += (int) $scores['stock'];
$descriptions = $db->executeS('
SELECT l.iso_code, pl.description
FROM ' . _DB_PREFIX_ . 'product_lang pl
LEFT JOIN ' . _DB_PREFIX_ . 'lang l
ON pl.id_lang = l.id_lang
WHERE id_product = ' . (int) $row['id_product'] . Shop::addSqlRestrictionOnLang('pl'));
foreach ($descriptions as $description) {
if (isset($description['iso_code']) && isset($description['description'])) {
$row['desclength_' . $description['iso_code']] = Tools::strlen(strip_tags($description['description']));
}
if (isset($description['iso_code'])) {
$scores['description_' . $description['iso_code']] = $row['desclength_' . $description['iso_code']] < Configuration::get('CHECKUP_DESCRIPTIONS_LT') ? 0 : ($row['desclength_' . $description['iso_code']] > Configuration::get('CHECKUP_DESCRIPTIONS_GT') ? 2 : 1);
$totals['description_' . $description['iso_code']] += $scores['description_' . $description['iso_code']];
}
}
$scores['average'] = array_sum($scores) / $divisor;
$scores['average'] = $scores['average'] < 1 ? 0 : ($scores['average'] > 1.5 ? 2 : 1);
$this->html .= '
<tr>
<td>' . $row['id_product'] . '</td>
<td><a href="' . Tools::safeOutput('index.php?tab=AdminProducts&updateproduct&id_product=' . $row['id_product'] . '&token=' . $token_products) . '">' . Tools::substr($row['name'], 0, 42) . '</a></td>
<td class="center">' . $array_colors[$scores['active']] . '</td>';
foreach ($languages as $language) {
if (isset($row['desclength_' . $language['iso_code']])) {
$this->html .= '<td class="center">' . (int) $row['desclength_' . $language['iso_code']] . ' ' . $array_colors[$scores['description_' . $language['iso_code']]] . '</td>';
} else {
$this->html .= '<td>0 ' . $array_colors[0] . '</td>';
}
}
$this->html .= '
<td class="center">' . (int) $row['nbImages'] . ' ' . $array_colors[$scores['images']] . '</td>
<td class="center">' . (int) $row['nbSales'] . ' ' . $array_colors[$scores['sales']] . '</td>
<td class="center">' . (int) $row['stock'] . ' ' . $array_colors[$scores['stock']] . '</td>
<td class="center">' . $array_colors[$scores['average']] . '</td>
</tr>';
}
$this->html .= '</tbody>';
$totals['active'] = $totals['active'] / $totals['products'];
$totals['active'] = $totals['active'] < 1 ? 0 : ($totals['active'] > 1.5 ? 2 : 1);
$totals['images'] = $totals['images'] / $totals['products'];
$totals['images'] = $totals['images'] < 1 ? 0 : ($totals['images'] > 1.5 ? 2 : 1);
$totals['sales'] = $totals['sales'] / $totals['products'];
$totals['sales'] = $totals['sales'] < 1 ? 0 : ($totals['sales'] > 1.5 ? 2 : 1);
$totals['stock'] = $totals['stock'] / $totals['products'];
$totals['stock'] = $totals['stock'] < 1 ? 0 : ($totals['stock'] > 1.5 ? 2 : 1);
foreach ($languages as $language) {
$totals['description_' . $language['iso_code']] = $totals['description_' . $language['iso_code']] / $totals['products'];
$totals['description_' . $language['iso_code']] = $totals['description_' . $language['iso_code']] < 1 ? 0 : ($totals['description_' . $language['iso_code']] > 1.5 ? 2 : 1);
}
$totals['average'] = array_sum($totals) / $divisor;
$totals['average'] = $totals['average'] < 1 ? 0 : ($totals['average'] > 1.5 ? 2 : 1);
$this->html .= '
<tfoot>
<tr>
<th colspan="2"></th>
<th class="center"><span class="title_box active">' . $this->l('Active') . '</span></th>';
foreach ($languages as $language) {
$this->html .= '<th class="center"><span class="title_box active">' . $this->l('Desc.') . ' (' . Tools::strtoupper($language['iso_code']) . ')</span></th>';
}
$this->html .= '
<th class="center"><span class="title_box active">' . $this->l('Images') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Sales') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Available quantity for sale') . '</span></th>
<th class="center"><span class="title_box active">' . $this->l('Global') . '</span></th>
</tr>
<tr>
<td colspan="2"></td>
<td class="center">' . $array_colors[$totals['active']] . '</td>';
foreach ($languages as $language) {
$this->html .= '<td class="center">' . $array_colors[$totals['description_' . $language['iso_code']]] . '</td>';
}
$this->html .= '
<td class="center">' . $array_colors[$totals['images']] . '</td>
<td class="center">' . $array_colors[$totals['sales']] . '</td>
<td class="center">' . $array_colors[$totals['stock']] . '</td>
<td class="center">' . $array_colors[$totals['average']] . '</td>
</tr>
</tfoot>
</table></div>';
return $this->html;
}
public static function searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false, $useCookie = true, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
// Only use cookie if id_customer is not present
if ($useCookie) {
$id_customer = (int) $context->customer->id;
} else {
$id_customer = 0;
}
if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag) || $orderBy && !$orderWay || $orderBy && !Validate::isOrderBy($orderBy) || $orderWay && !Validate::isOrderBy($orderWay)) {
return false;
}
if ($pageNumber < 1) {
$pageNumber = 1;
}
if ($pageSize < 1) {
$pageSize = 10;
}
$id = Context::getContext()->shop->id;
$id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
if ($count) {
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT COUNT(DISTINCT pt.`id_product`) nb
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')
' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)' : '') . '
WHERE product_shop.`active` = 1
AND p.visibility IN (\'both\', \'search\')
AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . '
' . $sql_groups . '
AND t.`name` LIKE \'%' . pSQL($tag) . '%\'');
}
$sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`,
MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, 1 position,
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
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', false) . '
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_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`)
' . (Group::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)' : '') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')
' . Product::sqlStock('p', 0) . '
WHERE product_shop.`active` = 1
AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . '
' . $sql_groups . '
AND t.`name` LIKE \'%' . pSQL($tag) . '%\'
GROUP BY product_shop.id_product
ORDER BY position DESC' . ($orderBy ? ', ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . '
LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize;
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
return Product::getProductsProperties((int) $id_lang, $result);
}
public static function getPercentProductStock()
{
$row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT SUM(IF(IFNULL(stock.quantity, 0) > 0, 1, 0)) as with_stock, COUNT(*) as products
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON p.id_product = pa.id_product
' . Product::sqlStock('p', 'pa'));
return round($row['products'] ? 100 * $row['with_stock'] / $row['products'] : 0, 2) . '%';
}
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);
}
public function getBestSales($where, $id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null)
{
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 == 'position' || $order_by == 'price') {
$order_by = 'sales';
}
if ($order_by == 'date_add' || $order_by == 'date_upd') {
$order_table = 'product_shop';
}
if (is_null($order_way) || $order_by == 'sales') {
$order_way = 'DESC';
}
$sql_groups = '';
if (Group::isFeatureActive()) {
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups = 'WHERE cp.`id_product` IS NOT NULL AND ' . $where . ' cg.`id_group` ' . (count($groups) ? 'IN (' . implode(',', $groups) . ')' : '= 1');
}
$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;
// Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT cp.`id_product`
FROM `' . _DB_PREFIX_ . 'category_group` cg
INNER JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`)
' . $sql_groups);
$ids = array();
foreach ($products as $product) {
if (Validate::isUnsignedId($product['id_product'])) {
$ids[$product['id_product']] = 1;
}
}
$ids = array_keys($ids);
$ids = array_filter($ids);
sort($ids);
$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';
//Main query
$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`,
m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
MAX(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(NOW(),
INTERVAL ' . $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) . '
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_ . '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::getContext()->country->id . '
AND tr.`id_state` = 0
LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (t.`id_tax` = tr.`id_tax`)
' . Product::sqlStock('p') . '
WHERE product_shop.`active` = 1
AND p.`visibility` != \'none\'
AND p.`id_product` IN (' . $ids . ')
GROUP BY product_shop.id_product
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);
}
/**
* 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 hookAdminStatsModules()
{
if (Tools::isSubmit('submitCategory')) {
$this->context->cookie->statsstock_id_category = Tools::getValue('statsstock_id_category');
}
$ru = AdminController::$currentIndex . '&module=' . $this->name . '&token=' . Tools::getValue('token');
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$filter = (int) $this->context->cookie->statsstock_id_category ? ' AND p.id_product IN (SELECT cp.id_product FROM ' . _DB_PREFIX_ . 'category_product cp WHERE cp.id_category = ' . (int) $this->context->cookie->statsstock_id_category . ')' : '';
$sql = 'SELECT p.id_product, p.reference, pl.name,
IFNULL((
SELECT AVG(product_attribute_shop.wholesale_price)
FROM ' . _DB_PREFIX_ . 'product_attribute pa
' . Shop::addSqlAssociation('product_attribute', 'pa') . '
WHERE p.id_product = pa.id_product
AND product_attribute_shop.wholesale_price != 0
), product_shop.wholesale_price) as wholesale_price,
IFNULL(stock.quantity, 0) as 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) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl') . ')
' . Product::sqlStock('p', 0) . '
WHERE 1 = 1
' . $filter;
$products = Db::getInstance()->executeS($sql);
foreach ($products as $key => $p) {
$products[$key]['stockvalue'] = $p['wholesale_price'] * $p['quantity'];
}
$this->html .= '
<script type="text/javascript">$(\'#calendar\').slideToggle();</script>
<div class="panel-heading">' . $this->l('Evaluation of available quantities for sale') . '</div>
<form action="' . Tools::safeOutput($ru) . '" method="post" class="form-horizontal">
<div class="row row-margin-bottom">
<label class="control-label col-lg-3">' . $this->l('Category') . '</label>
<div class="col-lg-6">
<select name="statsstock_id_category" onchange="this.form.submit();">
<option value="0">- ' . $this->l('All') . ' -</option>';
foreach (Category::getSimpleCategories($this->context->language->id) as $category) {
$this->html .= '<option value="' . (int) $category['id_category'] . '" ' . ($this->context->cookie->statsstock_id_category == $category['id_category'] ? 'selected="selected"' : '') . '>' . $category['name'] . '
</option>';
}
$this->html .= '
</select>
<input type="hidden" name="submitCategory" value="1" />
</div>
</div>
</form>';
if (!count($products)) {
$this->html .= '<p>' . $this->l('Your catalog is empty.') . '</p>';
} else {
$rollup = array('quantity' => 0, 'wholesale_price' => 0, 'stockvalue' => 0);
$this->html .= '
<table class="table">
<thead>
<tr>
<th><span class="title_box active">' . $this->l('ID') . '</span></th>
<th><span class="title_box active">' . $this->l('Ref.') . '</span></th>
<th><span class="title_box active">' . $this->l('Item') . '</span></th>
<th><span class="title_box active">' . $this->l('Available quantity for sale') . '</span></th>
<th><span class="title_box active">' . $this->l('Price*') . '</span></th>
<th><span class="title_box active">' . $this->l('Value') . '</span></th>
</tr>
</thead>
<tbody>';
foreach ($products as $product) {
$rollup['quantity'] += $product['quantity'];
$rollup['wholesale_price'] += $product['wholesale_price'];
$rollup['stockvalue'] += $product['stockvalue'];
$this->html .= '<tr>
<td>' . $product['id_product'] . '</td>
<td>' . $product['reference'] . '</td>
<td>' . $product['name'] . '</td>
<td>' . $product['quantity'] . '</td>
<td>' . Tools::displayPrice($product['wholesale_price'], $currency) . '</td>
<td>' . Tools::displayPrice($product['stockvalue'], $currency) . '</td>
</tr>';
}
$this->html .= '
</tbody>
<tfoot>
<tr>
<th colspan="3"></th>
<th><span class="title_box active">' . $this->l('Total quantities') . '</span></th>
<th><span class="title_box active">' . $this->l('Average price') . '</span></th>
<th><span class="title_box active">' . $this->l('Total value') . '</span></th>
</tr>
<tr>
<td colspan="3"></td>
<td>' . $rollup['quantity'] . '</td>
<td>' . Tools::displayPrice($rollup['wholesale_price'] / count($products), $currency) . '</td>
<td>' . Tools::displayPrice($rollup['stockvalue'], $currency) . '</td>
</tr>
</tfoot>
</table>
<i class="icon-asterisk"></i> ' . $this->l('This section corresponds to the default wholesale price according to the default supplier for the product. An average price is used when the product has attributes.');
return $this->html;
}
}
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);
if (!count($selected_filters['category'])) {
if (Configuration::get('PS_LAYERED_FULL_TREE')) {
$query_filters_from .= ' INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
ON p.id_product = cp.id_product
INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category AND
c.nleft >= ' . (int) $parent->nleft . ' AND c.nright <= ' . (int) $parent->nright . '
AND c.active = 1)
RIGHT JOIN ' . _DB_PREFIX_ . 'layered_category lc ON (lc.id_category = ' . (int) $id_parent . ' AND
lc.id_shop = ' . (int) Context::getContext()->shop->id . ')';
} else {
$query_filters_from .= ' INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
ON p.id_product = cp.id_product
INNER JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category
AND c.id_category = ' . (int) $id_parent . '
AND c.active = 1)';
}
}
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 AND sa.id_shop = ' . (int) Context::getContext()->shop->id . ') ';
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;
}
}
$id_currency = (int) Context::getContext()->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['min'] . '
AND psi.price_max <= ' . (int) $price_filter['max'] . '
AND psi.`id_product` = p.`id_product`
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_currency` = ' . $id_currency;
}
$query_filters_from .= Shop::addSqlAssociation('product', 'p');
$all_products_out = self::query('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . $price_filter_query_out . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY id_product');
$all_products_in = self::query('
SELECT p.`id_product` id_product
FROM `' . _DB_PREFIX_ . 'product` p
' . $price_filter_query_in . '
' . $query_filters_from . '
WHERE 1 ' . $query_filters_where . ' GROUP BY id_product');
$product_id_list = array();
while ($product = DB::getInstance()->nextRow($all_products_in)) {
$product_id_list[] = (int) $product['id_product'];
}
while ($product = DB::getInstance()->nextRow($all_products_out)) {
if (isset($price_filter) && $price_filter) {
$price = (int) Product::getPriceStatic($product['id_product'], Configuration::get('PS_LAYERED_FILTER_PRICE_USETAX'));
// Cast to int because we don't care about cents
if ($price < $price_filter['min'] || $price > $price_filter['max']) {
continue;
}
$product_id_list[] = (int) $product['id_product'];
}
}
$this->nbr_products = count($product_id_list);
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;
$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,
MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
DATEDIFF(' . $alias_where . '.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (int) $nb_day_new_product . ' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity
FROM `' . _DB_PREFIX_ . 'category_product` cp
LEFT JOIN ' . _DB_PREFIX_ . 'category c ON (c.id_category = cp.id_category)
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = cp.`id_product`
' . Shop::addSqlAssociation('product', 'p') . '
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::getContext()->shop) . '
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)
WHERE ' . $alias_where . '.`active` = 1 AND ' . $alias_where . '.`visibility` IN ("both", "catalog")
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
AND p.id_product IN (' . implode(',', $product_id_list) . ')
GROUP BY product_shop.id_product
ORDER BY ' . Tools::getProductsOrder('by', Tools::getValue('orderby'), true) . ' ' . Tools::getProductsOrder('way', Tools::getValue('orderway')) . ' 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;
}
public static function searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false, $useCookie = true, Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
if (!Module::isInstalled('agilemultipleseller') and !Module::isInstalled('agilesellerlistoptions')) {
return parent::searchTag($id_lang, $expr, $pageNumber, $pageSize, $orderBy, $orderWay, $ajax, $useCookie, $context);
}
$agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p");
if ($useCookie) {
$id_customer = (int) $context->customer->id;
} else {
$id_customer = 0;
}
if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag) || $orderBy && !$orderWay || $orderBy && !Validate::isOrderBy($orderBy) || $orderWay && !Validate::isOrderBy($orderWay)) {
return false;
}
if ($pageNumber < 1) {
$pageNumber = 1;
}
if ($pageSize < 1) {
$pageSize = 10;
}
$id = Context::getContext()->shop->id;
$id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');
if ($count) {
$sql = 'SELECT COUNT(DISTINCT pt.`id_product`) nb
FROM `' . _DB_PREFIX_ . 'product` p
' . $agile_sql_parts['joins'] . '
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)
WHERE product_shop.`active` = 1
' . $agile_sql_parts['wheres'] . '
AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . '
AND cg.`id_group` ' . (!$id_customer ? '= ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : 'IN (
SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group
WHERE id_customer = ' . (int) $id_customer . ')') . '
AND t.`name` LIKE \'%' . pSQL($tag) . '%\'';
return (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
}
$sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`,
MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name, 1 position,
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
' . $agile_sql_parts['selects'] . '
FROM `' . _DB_PREFIX_ . 'product` p
' . $agile_sql_parts['joins'] . '
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', false) . '
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_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
LEFT JOIN `' . _DB_PREFIX_ . 'product_tag` pt ON (p.`id_product` = pt.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = ' . (int) $id_lang . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_product` = p.`id_product`)
LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = cp.`id_category`)
LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cg.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')
' . Product::sqlStock('p', 0) . '
WHERE product_shop.`active` = 1
' . $agile_sql_parts['wheres'] . '
AND cs.`id_shop` = ' . (int) Context::getContext()->shop->id . '
AND cg.`id_group` ' . (!$id_customer ? '= ' . (int) Configuration::get('PS_UNIDENTIFIED_GROUP') : 'IN (
SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group
WHERE id_customer = ' . (int) $id_customer . ')') . '
AND t.`name` LIKE \'%' . pSQL($tag) . '%\'
GROUP BY product_shop.id_product
ORDER BY position DESC' . ($orderBy ? ', ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . '
LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize;
if (!($result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))) {
return false;
}
$results = Product::getProductsProperties((int) $id_lang, $result);
$results = AgileSellerManager::prepareSellerRattingInfo($results);
return $results;
}
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;
}
