/**
* 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);
}
/**
* Gets the name of a given product, in the given lang
* HAI : override method to record product name with sort
*
* @since 1.5.0
* @param int $id_product
* @param int $id_product_attribute Optional
* @param int $id_lang Optional
* @return string
*/
public static function getProductName($id_product, $id_product_attribute = null, $id_lang = null)
{
// use the lang in the context if $id_lang is not defined
if (!$id_lang) {
$id_lang = (int) Context::getContext()->language->id;
}
// creates the query object
$query = new DbQuery();
// selects different names, if it is a combination
if ($id_product_attribute) {
$query->select('IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name ORDER BY agl.`name`, \' - \', al.name ASC SEPARATOR \', \')),pl.name) as name');
} else {
$query->select('DISTINCT pl.name as name');
}
// adds joins & where clauses for combinations
if ($id_product_attribute) {
$query->from('product_attribute', 'pa');
$query->join(Shop::addSqlAssociation('product_attribute', 'pa'));
$query->innerJoin('product_lang', 'pl', 'pl.id_product = pa.id_product AND pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
$query->leftJoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$query->leftJoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute');
$query->leftJoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . (int) $id_lang);
$query->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . (int) $id_lang);
$query->where('pa.id_product = ' . (int) $id_product . ' AND pa.id_product_attribute = ' . (int) $id_product_attribute);
} else {
$query->from('product_lang', 'pl');
$query->where('pl.id_product = ' . (int) $id_product);
$query->where('pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl'));
}
return Db::getInstance()->getValue($query);
}
public function renderList()
{
$this->_select = 'b.*';
$this->_join = 'INNER JOIN `' . _DB_PREFIX_ . 'carrier_lang` b ON a.id_carrier = b.id_carrier' . Shop::addSqlRestrictionOnLang('b') . ' AND b.id_lang = ' . $this->context->language->id . ' LEFT JOIN `' . _DB_PREFIX_ . 'carrier_tax_rules_group_shop` ctrgs ON (a.`id_carrier` = ctrgs.`id_carrier` AND ctrgs.id_shop=' . (int) $this->context->shop->id . ')';
$this->_use_found_rows = false;
return parent::renderList();
}
/**
* AdminController::renderList() override
* @see AdminController::renderList()
*/
public function renderList()
{
// removes toolbar btn
$this->toolbar_btn = array();
// overrides select
$this->_select = '
CONCAT(pl.name, \' \', GROUP_CONCAT(IFNULL(al.name, \'\'), \'\')) product_name,
CONCAT(a.employee_lastname, \' \', a.employee_firstname) as employee,
mrl.name as reason,
stock.reference as product_reference,
stock.ean13 as product_ean13,
stock.upc as product_upc,
w.id_currency as id_currency,
w.name as warehouse_name';
// overrides join
$this->_join = 'INNER JOIN ' . _DB_PREFIX_ . 'stock stock ON a.id_stock = stock.id_stock
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (
stock.id_product = pl.id_product
AND pl.id_lang = ' . (int) $this->context->language->id . Shop::addSqlRestrictionOnLang('pl') . '
)
LEFT JOIN `' . _DB_PREFIX_ . 'stock_mvt_reason_lang` mrl ON (
a.id_stock_mvt_reason = mrl.id_stock_mvt_reason
AND mrl.id_lang = ' . (int) $this->context->language->id . '
)
LEFT JOIN `' . _DB_PREFIX_ . 'warehouse` w ON (w.id_warehouse = stock.id_warehouse)
LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_combination` pac ON (pac.id_product_attribute = stock.id_product_attribute)
LEFT JOIN `' . _DB_PREFIX_ . 'attribute_lang` al ON (
al.id_attribute = pac.id_attribute
AND pac.id_product_attribute <> 0
AND al.id_lang = ' . (int) $this->context->language->id . '
)';
// overrides group
$this->_group = 'GROUP BY a.id_stock_mvt';
// overrides where depending on the warehouse
$id_warehouse = (int) $this->getCurrentWarehouseId();
if ($id_warehouse > 0) {
$this->_where = ' AND w.id_warehouse = ' . $id_warehouse;
self::$currentIndex .= '&id_warehouse=' . $id_warehouse;
}
// sets the current warehouse
$this->tpl_list_vars['current_warehouse'] = $this->getCurrentWarehouseId();
// sets the list of warehouses
$warehouses = Warehouse::getWarehouses(true);
array_unshift($warehouses, array('id_warehouse' => -1, 'name' => $this->l('All Warehouses')));
$this->tpl_list_vars['list_warehouses'] = $warehouses;
// sets toolbar
$this->initToolbar();
// renders list
$list = parent::renderList();
// if export requested
if (Tools::isSubmit('csv')) {
if (count($this->_list) > 0) {
$this->renderCSV();
die;
} else {
$this->displayWarning($this->l('There is nothing to export as a CSV.'));
}
}
return $list;
}
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 getSubCategories($id_lang, $active = true, $id_category = 2, $p = 0, $n = 6)
{
$sql_groups_where = '';
$sql_groups_join = '';
if (Group::isFeatureActive()) {
$sql_groups_join = 'LEFT JOIN `' . _DB_PREFIX_ . 'category_group` cg ON (cg.`id_category` = c.`id_category`)';
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups_where = 'AND cg.`id_group` ' . (count($groups) ? 'IN (' . pSQL(implode(',', $groups)) . ')' : '=' . (int) Group::getCurrent()->id);
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
FROM `' . _DB_PREFIX_ . 'category` c
' . Shop::addSqlAssociation('category', 'c') . '
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category`
AND `id_lang` = ' . (int) $id_lang . ' ' . Shop::addSqlRestrictionOnLang('cl') . ')
' . $sql_groups_join . '
WHERE `id_parent` = ' . (int) $id_category . '
' . ($active ? 'AND `active` = 1' : '') . '
' . $sql_groups_where . '
GROUP BY c.`id_category`
ORDER BY `level_depth` ASC, category_shop.`position` ASC
LIMIT ' . (int) $p . ', ' . (int) $n);
foreach ($result as &$row) {
$row['id_image'] = Tools::file_exists_cache(_PS_CAT_IMG_DIR_ . $row['id_category'] . '.jpg') ? (int) $row['id_category'] : Language::getIsoById($id_lang) . '-default';
$row['legend'] = 'no picture';
}
return $result;
}
public function renderList()
{
$this->displayInformation(' <b>' . $this->l('How do I create a new carrier?') . '</b>
<br />
<ul>
<li>' . $this->l('Click "Add New."') . '<br /></li>
<li>' . $this->l('Fill in the fields and click "Save."') . '</li>
<li>' . $this->l('You need to set a price range -- or weight range -- for which the new carrier will be available.') . ' ' . $this->l('Under the "Shipping" menu, click either "Price ranges" or "Weight ranges.".') . '
</li>
<li>' . $this->l('Click "Add New."') . '</li>
<li>' . $this->l('Select the name of the carrier before defining the price or weight range.') . ' ' . $this->l('For example, the carrier can be made available for a weight range between 0 and 5lbs. Another carrier can have a range between 5 and 10lbs.') . '
</li>
<li>' . $this->l('When you\'re done, click "Save."') . '</li>
<li>' . $this->l('Click on the "Shipping" menu.') . '</li>
<li>' . $this->l('You need to set the fees that will be applied for this carrier.') . ' ' . $this->l('At the bottom on the page -- in the "Fees" section -- select the name of the carrier.') . '
</li>
<li>' . $this->l('For each zone, enter a price and then click "Save."') . '</li>
<li>' . $this->l('You\'re all set! The new carrier will now be displayed to customers.') . '</li>
</ul>');
$this->_select = 'b.*';
$this->_join = 'LEFT JOIN `' . _DB_PREFIX_ . 'carrier_lang` b ON a.id_carrier = b.id_carrier' . Shop::addSqlRestrictionOnLang('b') . '
LEFT JOIN `' . _DB_PREFIX_ . 'carrier_tax_rules_group_shop` ctrgs ON (a.`id_carrier` = ctrgs.`id_carrier`
AND ctrgs.id_shop=' . (int) $this->context->shop->id . ')';
$this->_where = 'AND b.id_lang = ' . $this->context->language->id;
return parent::renderList();
}
/**
* Load default routes group by languages
*/
protected function loadRoutes($id_shop = null)
{
$context = Context::getContext();
// Load custom routes from modules
$modules_routes = Hook::exec('moduleRoutes', array('id_shop' => $id_shop), null, true, false);
if (is_array($modules_routes) && count($modules_routes)) {
foreach ($modules_routes as $module_route) {
if (is_array($module_route) && count($module_route)) {
foreach ($module_route as $route => $route_details) {
if (array_key_exists('controller', $route_details) && array_key_exists('rule', $route_details) && array_key_exists('keywords', $route_details) && array_key_exists('params', $route_details)) {
if (!isset($this->default_routes[$route])) {
$this->default_routes[$route] = array();
$this->default_routes[$route] = array_merge($this->default_routes[$route], $route_details);
}
}
}
}
}
}
// Set default routes
//new edit by Ha!*!*y :: Select only active languages
foreach (Language::getLanguages(TRUE) as $lang) {
foreach ($this->default_routes as $id => $route) {
$this->addRoute($id, $route['rule'], $route['controller'], $lang['id_lang'], $route['keywords'], isset($route['params']) ? $route['params'] : array(), $id_shop);
}
}
if ($this->use_routes) {
// Get iso lang
$iso_lang = Tools::getValue('isolang');
$id_lang = $context->language->id;
if (!empty($iso_lang)) {
$id_lang = Language::getIdByIso($iso_lang);
}
// Load routes from meta table
$sql = 'SELECT m.page, ml.url_rewrite, ml.id_lang
FROM `' . _DB_PREFIX_ . 'meta` m
LEFT JOIN `' . _DB_PREFIX_ . 'meta_lang` ml ON (m.id_meta = ml.id_meta' . Shop::addSqlRestrictionOnLang('ml', $id_shop) . ')
ORDER BY LENGTH(ml.url_rewrite) DESC';
if ($results = Db::getInstance()->executeS($sql)) {
foreach ($results as $row) {
if ($row['url_rewrite']) {
$this->addRoute($row['page'], $row['url_rewrite'], $row['page'], $row['id_lang'], array(), array(), $id_shop);
}
}
}
// Set default empty route if no empty route (that's weird I know)
if (!$this->empty_route) {
$this->empty_route = array('routeID' => 'index', 'rule' => '', 'controller' => 'index');
}
// Load custom routes
foreach ($this->default_routes as $route_id => $route_data) {
if ($custom_route = Configuration::get('PS_ROUTE_' . $route_id, null, null, $id_shop)) {
foreach (Language::getLanguages() as $lang) {
$this->addRoute($route_id, $custom_route, $route_data['controller'], $lang['id_lang'], $route_data['keywords'], isset($route_data['params']) ? $route_data['params'] : array(), $id_shop);
}
}
}
}
}
public static function getGroupReductions($id_group, $id_lang)
{
$lang = $id_lang . Shop::addSqlRestrictionOnLang('cl');
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT gr.`id_group_reduction`, gr.`id_group`, gr.`id_category`, gr.`reduction`, cl.`name` AS category_name
FROM `' . _DB_PREFIX_ . 'group_reduction` gr
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (cl.`id_category` = gr.`id_category` AND cl.`id_lang` = ' . (int) $lang . ')
WHERE `id_group` = ' . (int) $id_group);
}
/**
* Get the user's journey
*
* @param integer $id_category Category ID
* @param string $path Path end
* @param boolean $linkOntheLastItem Put or not a link on the current category
* @param string [optionnal] $categoryType defined what type of categories is used (products or cms)
*/
public static function getPath($id_category, $path = '', $link_on_the_item = false, $category_type = 'products', Context $context = null)
{
if (!$context) {
$context = Context::getContext();
}
$id_category = (int) $id_category;
if ($id_category == 1) {
return '<div itemscope itemtype="http://data-vocabulary.org/Breadcrumb"><span class="navigation_end">' . $path . '</span></div>';
}
$pipe = Configuration::get('PS_NAVIGATION_PIPE');
if (empty($pipe)) {
$pipe = '>';
}
$full_path = '';
if ($category_type === 'products') {
$interval = Category::getInterval($id_category);
$id_root_category = $context->shop->getCategory();
$interval_root = Category::getInterval($id_root_category);
if ($interval) {
$sql = 'SELECT c.id_category, cl.name, cl.link_rewrite
FROM ' . _DB_PREFIX_ . 'category c
LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = c.id_category' . Shop::addSqlRestrictionOnLang('cl') . ')
' . Shop::addSqlAssociation('category', 'c') . '
WHERE c.nleft <= ' . $interval['nleft'] . '
AND c.nright >= ' . $interval['nright'] . '
AND c.nleft >= ' . $interval_root['nleft'] . '
AND c.nright <= ' . $interval_root['nright'] . '
AND cl.id_lang = ' . (int) $context->language->id . '
AND c.active = 1
AND c.level_depth > ' . (int) $interval_root['level_depth'] . '
ORDER BY c.level_depth ASC';
$categories = Db::getInstance()->executeS($sql);
$n = 1;
$n_categories = count($categories);
foreach ($categories as $category) {
$full_path .= '<div itemscope itemtype="http://data-vocabulary.org/Breadcrumb">' . ($n < $n_categories || $link_on_the_item ? '<a href="' . Tools::safeOutput($context->link->getCategoryLink((int) $category['id_category'], $category['link_rewrite'])) . '" title="' . htmlentities($category['name'], ENT_NOQUOTES, 'UTF-8') . '" itemprop="url">' : '') . '<span itemprop="title">' . htmlentities($category['name'], ENT_NOQUOTES, 'UTF-8') . '</span>' . ($n < $n_categories || $link_on_the_item ? '</a>' : '') . '</div>' . ($n++ != $n_categories || !empty($path) ? '<span class="navigation-pipe">' . $pipe . '</span>' : '');
}
return $full_path . $path;
}
} else {
if ($category_type === 'CMS') {
$category = new CMSCategory($id_category, $context->language->id);
if (!Validate::isLoadedObject($category)) {
die(Tools::displayError());
}
$category_link = $context->link->getCMSCategoryLink($category);
if ($path != $category->name) {
$full_path .= '<div itemscope itemtype="http://data-vocabulary.org/Breadcrumb"><a href="' . Tools::safeOutput($category_link) . '" itemprop="url"><span itemprop="title">' . htmlentities($category->name, ENT_NOQUOTES, 'UTF-8') . '</span></a><span class="navigation-pipe">' . $pipe . '</span>' . $path;
} else {
$full_path = ($link_on_the_item ? '<a href="' . Tools::safeOutput($category_link) . '" itemprop="url">' : '') . '<span itemprop="title">' . htmlentities($path, ENT_NOQUOTES, 'UTF-8') . '</span>' . ($link_on_the_item ? '</a>' : '');
}
return Tools::getPath($category->id_parent, $full_path, $link_on_the_item, $category_type);
}
}
}
/**
* Get product width light information
*
* @param array $iIdProduct Product id
* @return array Product
*/
public static function getProductLight($iIdProduct)
{
$sql = 'SELECT p.`id_product`, p.`reference`, pl.`name`
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . '
)
WHERE p.`id_product` = ' . $iIdProduct;
return Db::getInstance()->getRow($sql);
}
public function hookRightColumn($params)
{
$productsViewed = isset($params['cookie']->viewed) && !empty($params['cookie']->viewed) ? array_slice(array_reverse(explode(',', $params['cookie']->viewed)), 0, Configuration::get('PRODUCTS_VIEWED_NBR')) : array();
if (count($productsViewed)) {
$defaultCover = Language::getIsoById($params['cookie']->id_lang) . '-default';
$productIds = implode(',', array_map('intval', $productsViewed));
$productsImages = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT MAX(image_shop.id_image) id_image, p.id_product, il.legend, product_shop.active, pl.name, pl.description_short, pl.link_rewrite, cl.link_rewrite AS category_rewrite
FROM ' . _DB_PREFIX_ . 'product p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = p.id_product' . 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 (il.id_image = image_shop.id_image AND il.id_lang = ' . (int) $params['cookie']->id_lang . ')
LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ')
WHERE p.id_product IN (' . $productIds . ')
AND pl.id_lang = ' . (int) $params['cookie']->id_lang . '
AND cl.id_lang = ' . (int) $params['cookie']->id_lang . '
GROUP BY product_shop.id_product');
$productsImagesArray = array();
foreach ($productsImages as $pi) {
$productsImagesArray[$pi['id_product']] = $pi;
}
$productsViewedObj = array();
foreach ($productsViewed as $productViewed) {
$obj = (object) 'Product';
if (!isset($productsImagesArray[$productViewed]) || !($obj->active = $productsImagesArray[$productViewed]['active'])) {
continue;
} else {
$obj->id = (int) $productsImagesArray[$productViewed]['id_product'];
$obj->id_image = (int) $productsImagesArray[$productViewed]['id_image'];
$obj->cover = (int) $productsImagesArray[$productViewed]['id_product'] . '-' . (int) $productsImagesArray[$productViewed]['id_image'];
$obj->legend = $productsImagesArray[$productViewed]['legend'];
$obj->name = $productsImagesArray[$productViewed]['name'];
$obj->description_short = $productsImagesArray[$productViewed]['description_short'];
$obj->link_rewrite = $productsImagesArray[$productViewed]['link_rewrite'];
$obj->category_rewrite = $productsImagesArray[$productViewed]['category_rewrite'];
// $obj is not a real product so it cannot be used as argument for getProductLink()
$obj->product_link = $this->context->link->getProductLink($obj->id, $obj->link_rewrite, $obj->category_rewrite);
if (!isset($obj->cover) || !$productsImagesArray[$productViewed]['id_image']) {
$obj->cover = $defaultCover;
$obj->legend = '';
}
$productsViewedObj[] = $obj;
}
}
if (!count($productsViewedObj)) {
return;
}
$this->smarty->assign(array('productsViewedObj' => $productsViewedObj, 'mediumSize' => Image::getSize('medium')));
return $this->display(__FILE__, 'blockviewed.tpl');
}
return;
}
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 Each parent category of this category until the root category
*
* @param integer $id_lang Language ID
* @return array Corresponding categories
*/
public function _getParentsCategories($id_current = NULL)
{
$context = Context::getContext()->cloneContext();
$context->shop = clone $context->shop;
$id_lang = $context->language->id;
$categories = null;
if (count(Category::getCategoriesWithoutParent()) > 1 && Configuration::get('PS_MULTISHOP_FEATURE_ACTIVE') && count(Shop::getShops(true, null, true)) != 1) {
$context->shop->id_category = Category::getTopCategory()->id;
} elseif (!$context->shop->id) {
$context->shop = new Shop(Configuration::get('PS_SHOP_DEFAULT'));
}
$id_shop = $context->shop->id;
while (true) {
$sql = '
SELECT c.*, cl.*
FROM `' . _DB_PREFIX_ . 'category` c
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
ON (c.`id_category` = cl.`id_category`
AND `id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')';
if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP) {
$sql .= '
LEFT JOIN `' . _DB_PREFIX_ . 'category_shop` cs
ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = ' . (int) $id_shop . ')';
}
$sql .= '
WHERE c.`id_category` = ' . (int) $id_current;
if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP) {
$sql .= '
AND cs.`id_shop` = ' . (int) $context->shop->id;
}
$root_category = Category::getRootCategory();
if (Shop::isFeatureActive() && Shop::getContext() == Shop::CONTEXT_SHOP && (!Tools::isSubmit('id_category') || (int) Tools::getValue('id_category') == (int) $root_category->id || (int) $root_category->id == (int) $context->shop->id_category)) {
$sql .= '
AND c.`id_parent` != 0';
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if (isset($result[0])) {
$categories[] = $result[0];
} else {
if (!$categories) {
$categories = array();
}
}
if (!$result || $result[0]['id_category'] == $context->shop->id_category) {
return $categories;
}
$id_current = $result[0]['id_parent'];
}
}
public function renderList()
{
$this->_select = 'b.*';
$this->_join = 'INNER JOIN `' . _DB_PREFIX_ . 'carrier_lang` b ON a.id_carrier = b.id_carrier' . Shop::addSqlRestrictionOnLang('b') . ' AND b.id_lang = ' . (int) $this->context->language->id . ' LEFT JOIN `' . _DB_PREFIX_ . 'carrier_tax_rules_group_shop` ctrgs ON (a.`id_carrier` = ctrgs.`id_carrier` AND ctrgs.id_shop=' . (int) $this->context->shop->id . ')';
$this->_use_found_rows = false;
// Removes the Recommended modules button
unset($this->page_header_toolbar_btn['modules-list']);
// test if need to show header alert.
$sql = 'SELECT COUNT(1) FROM `' . _DB_PREFIX_ . 'carrier` WHERE deleted = 0 AND id_reference > 2';
$showHeaderAlert = Db::getInstance()->query($sql)->fetchColumn(0) == 0;
// Assign them in two steps! Because renderModulesList needs it before to be called.
$this->context->smarty->assign('panel_title', $this->trans('Use one of our recommended carrier modules', array(), 'Admin.Shipping.Feature'));
$this->context->smarty->assign(array('showHeaderAlert' => $showHeaderAlert, 'modules_list' => $this->renderModulesList('back-office,AdminCarriers,new')));
return parent::renderList();
}
public function renderContent($setting)
{
$t = array('categ_root_category' => '1', 'categ_max_depth' => 4, 'categ_dhtml' => 1, 'categ_sort' => 0, 'categ_sort_way' => 0);
$setting = array_merge($t, $setting);
$context = Context::getContext();
$this->setLastVisitedCategory();
$max_depth = $setting['categ_max_depth'];
$result_ids = array();
$result_parents = array();
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
FROM `' . _DB_PREFIX_ . 'category` c
INNER JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (c.`id_category` = cl.`id_category`
AND cl.`id_lang` = ' . (int) $context->language->id . Shop::addSqlRestrictionOnLang('cl') . ')
INNER JOIN `' . _DB_PREFIX_ . 'category_shop` cs ON (cs.`id_category` = c.`id_category` AND cs.`id_shop` = ' . (int) $context->shop->id . ')
WHERE (c.`active` = 1 OR c.`id_category` = ' . (int) Configuration::get('PS_HOME_CATEGORY') . ')
AND c.`id_category` != ' . (int) Configuration::get('PS_ROOT_CATEGORY') . ((int) $max_depth != 0 ? ' AND `level_depth` <= ' . (int) $max_depth : '') . '
AND c.id_category IN (
SELECT id_category
FROM `' . _DB_PREFIX_ . 'category_group`
WHERE `id_group` IN (' . pSQL(implode(', ', Customer::getGroupsStatic((int) $context->customer->id))) . ')
)
ORDER BY `level_depth` ASC, ' . ($setting['categ_sort'] ? 'cl.`name`' : 'cs.`position`') . ' ' . ($setting['categ_sort_way'] ? 'DESC' : 'ASC'));
foreach ($result as &$row) {
$result_parents[$row['id_parent']][] =& $row;
$result_ids[$row['id_category']] =& $row;
}
$block_categ_tree = $this->getTree($result_parents, $result_ids, $max_depth, null);
$setting['blockCategTree'] = $block_categ_tree;
if ((Tools::getValue('id_product') || Tools::getValue('id_category')) && isset($context->cookie->last_visited_category) && $context->cookie->last_visited_category) {
$category = new Category($context->cookie->last_visited_category, $context->language->id);
if (Validate::isLoadedObject($category)) {
$setting['currentCategory'] = $category;
$setting['currentCategoryId'] = $category->id;
}
}
$setting['isDhtml'] = $setting['categ_dhtml'];
if (file_exists(_PS_THEME_DIR_ . 'modules/pspagebuilder/views/templates/front/widgets/sub/category-tree-branch.tpl')) {
$setting['branche_tpl_path'] = _PS_THEME_DIR_ . 'modules/pspagebuilder/views/templates/front/widgets/sub/category-tree-branch.tpl';
} else {
$setting['branche_tpl_path'] = _PS_MODULE_DIR_ . 'pspagebuilder/views/templates/front/widgets/sub/category-tree-branch.tpl';
}
$output = array('type' => 'categoriesblock', 'data' => $setting);
return $output;
}
/**
* 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 static function getFavoriteProducts($id_customer, $id_lang)
{
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT DISTINCT p.`id_product`, fp.`id_shop`, pl.`description_short`, pl.`link_rewrite`,
pl.`name`, i.`id_image`, CONCAT(p.`id_product`, \'-\', i.`id_image`) as image
FROM `' . _DB_PREFIX_ . 'favorite_product` fp
LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON (p.`id_product` = fp.`id_product`)
' . 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 OUTER JOIN `' . _DB_PREFIX_ . 'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
' . Shop::addSqlAssociation('product_attribute', 'pa', false) . '
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 . ')
WHERE product_shop.`active` = 1
' . ($id_customer ? ' AND fp.id_customer = ' . (int) $id_customer : '') . '
' . Shop::addSqlRestriction(false, 'fp'));
}
public static function getProductMetas($id_product, $id_lang, $page_name)
{
$context = Context::getContext();
$sql = 'SELECT `name`, `meta_title`, `meta_description`, `meta_keywords`, `description_short`
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.`id_product` = p.`id_product`' . Shop::addSqlRestrictionOnLang('pl') . ')
' . Shop::addSqlAssociation('product', 'p') . '
WHERE pl.id_lang = ' . (int) $id_lang . '
AND pl.id_product = ' . (int) $id_product . '
AND product_shop.active = 1';
if ($row = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($sql)) {
if ($context->language->id != 1) {
$row['meta_title'] = Tools::rus2translit($row['meta_title']);
$row['name'] = Tools::rus2translit($row['name']);
}
if (empty($row['meta_description'])) {
$row['meta_description'] = strip_tags($row['description_short']);
}
return Meta::completeMetaTags($row, $row['name']);
}
return Meta::getHomeMetas($id_lang, $page_name);
}
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 static function getPacksTable($id_product, $id_lang, $full = false, $limit = null)
{
if (!Pack::isFeatureActive()) {
return array();
}
$packs = Db::getInstance()->getValue('
SELECT GROUP_CONCAT(a.`id_product_pack`)
FROM `' . _DB_PREFIX_ . 'pack` a
WHERE a.`id_product_item` = ' . (int) $id_product);
if (!(int) $packs) {
return array();
}
$context = Context::getContext();
$sql = '
SELECT p.*, product_shop.*, pl.*, image_shop.`id_image` id_image, il.`legend`, IFNULL(product_attribute_shop.id_product_attribute, 0) id_product_attribute
FROM `' . _DB_PREFIX_ . 'product` p
NATURAL LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
' . 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_ . '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 . ')
WHERE pl.`id_lang` = ' . (int) $id_lang . '
' . Shop::addSqlRestrictionOnLang('pl') . '
AND p.`id_product` IN (' . $packs . ')
GROUP BY p.id_product';
if ($limit) {
$sql .= ' LIMIT ' . (int) $limit;
}
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if (!$full) {
return $result;
}
$array_result = array();
foreach ($result as $row) {
if (!Pack::isPacked($row['id_product'])) {
$array_result[] = Product::getProductProperties($id_lang, $row);
}
}
return $array_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;
}
/**
* Get all carriers in a given language
*
* @param int $id_lang Language id
* @param int $modules_filters Possible values:
* - PS_CARRIERS_ONLY
* - CARRIERS_MODULE
* - CARRIERS_MODULE_NEED_RANGE
* - PS_CARRIERS_AND_CARRIER_MODULES_NEED_RANGE
* - ALL_CARRIERS
* @param bool $active Returns only active carriers when true
*
* @return array Carriers
*/
public static function getCarriers($id_lang, $active = false, $delete = false, $id_zone = false, $ids_group = null, $modules_filters = self::PS_CARRIERS_ONLY)
{
// Filter by groups and no groups => return empty array
if ($ids_group && (!is_array($ids_group) || !count($ids_group))) {
return array();
}
$sql = '
SELECT c.*, cl.delay
FROM `' . _DB_PREFIX_ . 'carrier` c
LEFT JOIN `' . _DB_PREFIX_ . 'carrier_lang` cl ON (c.`id_carrier` = cl.`id_carrier` AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'carrier_zone` cz ON (cz.`id_carrier` = c.`id_carrier`)' . ($id_zone ? 'LEFT JOIN `' . _DB_PREFIX_ . 'zone` z ON (z.`id_zone` = ' . (int) $id_zone . ')' : '') . '
' . Shop::addSqlAssociation('carrier', 'c') . '
WHERE c.`deleted` = ' . ($delete ? '1' : '0');
if ($active) {
$sql .= ' AND c.`active` = 1 ';
}
if ($id_zone) {
$sql .= ' AND cz.`id_zone` = ' . (int) $id_zone . ' AND z.`active` = 1 ';
}
if ($ids_group) {
$sql .= ' AND EXISTS (SELECT 1 FROM ' . _DB_PREFIX_ . 'carrier_group
WHERE ' . _DB_PREFIX_ . 'carrier_group.id_carrier = c.id_carrier
AND id_group IN (' . implode(',', array_map('intval', $ids_group)) . ')) ';
}
switch ($modules_filters) {
case 1:
$sql .= ' AND c.is_module = 0 ';
break;
case 2:
$sql .= ' AND c.is_module = 1 ';
break;
case 3:
$sql .= ' AND c.is_module = 1 AND c.need_range = 1 ';
break;
case 4:
$sql .= ' AND (c.is_module = 0 OR c.need_range = 1) ';
break;
}
$sql .= ' GROUP BY c.`id_carrier` ORDER BY c.`position` ASC';
$cache_id = 'Carrier::getCarriers_' . md5($sql);
if (!Cache::isStored($cache_id)) {
$carriers = Db::getInstance()->executeS($sql);
Cache::store($cache_id, $carriers);
} else {
$carriers = Cache::retrieve($cache_id);
}
foreach ($carriers as $key => $carrier) {
if ($carrier['name'] == '0') {
$carriers[$key]['name'] = Carrier::getCarrierNameFromShopName();
}
}
return $carriers;
}
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 getProductRuleDisplay($product_rule_group_id, $product_rule_id, $product_rule_type, $selected = array())
{
Context::getContext()->smarty->assign(array('product_rule_group_id' => (int) $product_rule_group_id, 'product_rule_id' => (int) $product_rule_id, 'product_rule_type' => $product_rule_type));
switch ($product_rule_type) {
case 'attributes':
$attributes = array('selected' => array(), 'unselected' => array());
$results = Db::getInstance()->executeS('
SELECT CONCAT(agl.name, " - ", al.name) as name, a.id_attribute as id
FROM ' . _DB_PREFIX_ . 'attribute_group_lang agl
LEFT JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute_group = agl.id_attribute_group
LEFT JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON (a.id_attribute = al.id_attribute AND al.id_lang = ' . (int) Context::getContext()->language->id . ')
WHERE agl.id_lang = ' . (int) Context::getContext()->language->id . '
ORDER BY agl.name, al.name');
foreach ($results as $row) {
$attributes[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row;
}
Context::getContext()->smarty->assign('product_rule_itemlist', $attributes);
$choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch();
Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content);
break;
case 'products':
$products = array('selected' => array(), 'unselected' => array());
$results = Db::getInstance()->executeS('
SELECT DISTINCT name, p.id_product as id
FROM ' . _DB_PREFIX_ . 'product p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('pl') . ')
' . Shop::addSqlAssociation('product', 'p') . '
WHERE id_lang = ' . (int) Context::getContext()->language->id . '
ORDER BY name');
foreach ($results as $row) {
$products[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row;
}
Context::getContext()->smarty->assign('product_rule_itemlist', $products);
$choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch();
Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content);
break;
case 'manufacturers':
$products = array('selected' => array(), 'unselected' => array());
$results = Db::getInstance()->executeS('
SELECT name, id_manufacturer as id
FROM ' . _DB_PREFIX_ . 'manufacturer
ORDER BY name');
foreach ($results as $row) {
$products[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row;
}
Context::getContext()->smarty->assign('product_rule_itemlist', $products);
$choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch();
Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content);
break;
case 'suppliers':
$products = array('selected' => array(), 'unselected' => array());
$results = Db::getInstance()->executeS('
SELECT name, id_supplier as id
FROM ' . _DB_PREFIX_ . 'supplier
ORDER BY name');
foreach ($results as $row) {
$products[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row;
}
Context::getContext()->smarty->assign('product_rule_itemlist', $products);
$choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch();
Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content);
break;
case 'categories':
$categories = array('selected' => array(), 'unselected' => array());
$results = Db::getInstance()->executeS('
SELECT DISTINCT name, c.id_category as id
FROM ' . _DB_PREFIX_ . 'category c
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
ON (c.`id_category` = cl.`id_category`
AND cl.`id_lang` = ' . (int) Context::getContext()->language->id . Shop::addSqlRestrictionOnLang('cl') . ')
' . Shop::addSqlAssociation('category', 'c') . '
WHERE id_lang = ' . (int) Context::getContext()->language->id . '
ORDER BY name');
foreach ($results as $row) {
$categories[in_array($row['id'], $selected) ? 'selected' : 'unselected'][] = $row;
}
Context::getContext()->smarty->assign('product_rule_itemlist', $categories);
$choose_content = $this->createTemplate('controllers/cart_rules/product_rule_itemlist.tpl')->fetch();
Context::getContext()->smarty->assign('product_rule_choose_content', $choose_content);
break;
default:
Context::getContext()->smarty->assign('product_rule_itemlist', array('selected' => array(), 'unselected' => array()));
Context::getContext()->smarty->assign('product_rule_choose_content', '');
}
return $this->createTemplate('controllers/cart_rules/product_rule.tpl')->fetch();
}
public function getData()
{
$currency = new Currency(Configuration::get('PS_CURRENCY_DEFAULT'));
$date_between = $this->getDate();
$id_lang = $this->getLang();
// If a shop is selected, get all children categories for the shop
$categories = array();
if (Shop::getContext() != Shop::CONTEXT_ALL) {
$sql = 'SELECT c.nleft, c.nright
FROM ' . _DB_PREFIX_ . 'category c
WHERE c.id_category IN (
SELECT s.id_category
FROM ' . _DB_PREFIX_ . 'shop s
WHERE s.id_shop IN (' . implode(', ', Shop::getContextListShopID()) . ')
)';
if ($result = Db::getInstance()->executeS($sql)) {
$ntree_restriction = array();
foreach ($result as $row) {
$ntree_restriction[] = '(nleft >= ' . $row['nleft'] . ' AND nright <= ' . $row['nright'] . ')';
}
if ($ntree_restriction) {
$sql = 'SELECT id_category
FROM ' . _DB_PREFIX_ . 'category
WHERE ' . implode(' OR ', $ntree_restriction);
if ($result = Db::getInstance()->executeS($sql)) {
foreach ($result as $row) {
$categories[] = $row['id_category'];
}
}
}
}
}
// Get best categories
$this->query = '
SELECT SQL_CALC_FOUND_ROWS ca.`id_category`, CONCAT(parent.name, \' > \', calang.`name`) as name,
IFNULL(SUM(t.`totalQuantitySold`), 0) AS totalQuantitySold,
ROUND(IFNULL(SUM(t.`totalPriceSold`), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.`counter`), 0)
FROM `' . _DB_PREFIX_ . 'page` p
LEFT JOIN `' . _DB_PREFIX_ . 'page_viewed` pv ON p.`id_page` = pv.`id_page`
LEFT JOIN `' . _DB_PREFIX_ . 'date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `' . _DB_PREFIX_ . 'product` pr ON CAST(p.`id_object` AS UNSIGNED INTEGER) = pr.`id_product`
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr2 ON capr2.`id_product` = pr.`id_product`
WHERE capr.`id_category` = capr2.`id_category`
AND p.`id_page_type` = 1
AND dr.`time_start` BETWEEN ' . $date_between . '
AND dr.`time_end` BETWEEN ' . $date_between . '
) AS totalPageViewed
FROM `' . _DB_PREFIX_ . 'category` ca
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` calang ON (ca.`id_category` = calang.`id_category` AND calang.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('calang') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` parent ON (ca.`id_parent` = parent.`id_category` AND parent.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('parent') . ')
LEFT JOIN `' . _DB_PREFIX_ . 'category_product` capr ON ca.`id_category` = capr.`id_category`
LEFT JOIN (
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`
FROM `' . _DB_PREFIX_ . 'product` pr
LEFT JOIN (
SELECT pr.`id_product`,
IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
IFNULL(SUM(cp.`product_price` * cp.`product_quantity`), 0) / o.conversion_rate AS totalPriceSold
FROM `' . _DB_PREFIX_ . 'product` pr
LEFT OUTER JOIN `' . _DB_PREFIX_ . 'order_detail` cp ON pr.`id_product` = cp.`product_id`
LEFT JOIN `' . _DB_PREFIX_ . 'orders` o ON o.`id_order` = cp.`id_order`
WHERE o.valid = 1
AND o.invoice_date BETWEEN ' . $date_between . '
GROUP BY pr.`id_product`
) t ON t.`id_product` = pr.`id_product`
) t ON t.`id_product` = capr.`id_product`
' . ($categories ? 'WHERE ca.id_category IN (' . implode(', ', $categories) . ')' : '') . '
GROUP BY ca.`id_category`
HAVING ca.`id_category` != 1';
if (Validate::IsName($this->_sort)) {
$this->query .= ' ORDER BY `' . $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 ' . $this->_start . ', ' . $this->_limit;
}
$values = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($this->query);
foreach ($values as &$value) {
$value['totalPriceSold'] = Tools::displayPrice($value['totalPriceSold'], $currency);
}
$this->_values = $values;
$this->_totalCount = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('SELECT FOUND_ROWS()');
}
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);
}
private function getSimpleProducts($id_lang, $limit_from = false, $limit_to = 500)
{
if (version_compare(_PS_VERSION_, '1.5', '>')) {
$context = Context::getContext();
if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
} else {
$front = true;
}
$sql = 'SELECT p.`id_product`, pl.`name`
FROM `' . _DB_PREFIX_ . 'product` p
' . Shop::addSqlAssociation('product', 'p') . '
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` ' . Shop::addSqlRestrictionOnLang('pl') . ')
WHERE pl.`id_lang` = ' . (int) $id_lang . ' AND p.`active`= 1 AND p.`available_for_order`= 1
' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
ORDER BY pl.`name`';
if ($limit_from !== false) {
$sql .= ' LIMIT ' . (int) $limit_from . ', ' . (int) $limit_to;
}
} else {
$sql = 'SELECT p.`id_product`, pl.`name`
FROM `' . _DB_PREFIX_ . 'product` p
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product`)
WHERE pl.`id_lang` = ' . (int) $id_lang . ' AND p.`active`= 1 AND p.`available_for_order`= 1
ORDER BY pl.`name`';
}
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
}
public static function getCrossSells($id_product, $id_lang, $limit = 12)
{
if (!$id_product || !$id_lang) {
return;
}
$front = true;
if (!in_array(Context::getContext()->controller->controller_type, array('front', 'modulefront'))) {
$front = false;
}
$orders = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT o.id_order
FROM ' . _DB_PREFIX_ . 'orders o
LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.id_order = o.id_order)
WHERE o.valid = 1 AND od.product_id = ' . (int) $id_product);
if (count($orders)) {
$list = '';
foreach ($orders as $order) {
$list .= (int) $order['id_order'] . ',';
}
$list = rtrim($list, ',');
$order_products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT DISTINCT od.product_id, p.id_product, pl.name, pl.link_rewrite, p.reference, i.id_image, product_shop.show_price,
cl.link_rewrite category, p.ean13, p.out_of_stock, p.id_category_default ' . (Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute,0) id_product_attribute' : '') . '
FROM ' . _DB_PREFIX_ . 'order_detail od
LEFT JOIN ' . _DB_PREFIX_ . 'product p ON (p.id_product = od.product_id)
' . Shop::addSqlAssociation('product', 'p') . (Combination::isFeatureActive() ? 'LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=' . (int) Context::getContext()->shop->id . ')' : '') . '
LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (pl.id_product = od.product_id' . Shop::addSqlRestrictionOnLang('pl') . ')
LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = product_shop.id_category_default' . Shop::addSqlRestrictionOnLang('cl') . ')
LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = od.product_id)
WHERE od.id_order IN (' . $list . ')
AND pl.id_lang = ' . (int) $id_lang . '
AND cl.id_lang = ' . (int) $id_lang . '
AND od.product_id != ' . (int) $id_product . '
AND i.cover = 1
AND product_shop.active = 1' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') . '
ORDER BY RAND()
LIMIT ' . (int) $limit . '
', true, false);
$tax_calc = Product::getTaxCalculationMethod();
if (is_array($order_products)) {
foreach ($order_products as &$order_product) {
$order_product['image'] = Context::getContext()->link->getImageLink($order_product['link_rewrite'], (int) $order_product['product_id'] . '-' . (int) $order_product['id_image'], ImageType::getFormatedName('medium'));
$order_product['link'] = Context::getContext()->link->getProductLink((int) $order_product['product_id'], $order_product['link_rewrite'], $order_product['category'], $order_product['ean13']);
if ($tax_calc == 0 || $tax_calc == 2) {
$order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], true, null);
} elseif ($tax_calc == 1) {
$order_product['displayed_price'] = Product::getPriceStatic((int) $order_product['product_id'], false, null);
}
}
return Product::getProductsProperties($id_lang, $order_products);
}
}
}
private function getCrossSales($id_product, $id_lang)
{
$sql = 'SELECT pl.name as pname, pl.id_product, SUM(od.product_quantity) as pqty, AVG(od.product_price) as pprice
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order
LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (pl.id_product = od.product_id AND pl.id_lang = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
WHERE o.id_customer IN (
SELECT o.id_customer
FROM `' . _DB_PREFIX_ . 'orders` o
LEFT JOIN `' . _DB_PREFIX_ . 'order_detail` od ON o.id_order = od.id_order
WHERE o.date_add BETWEEN ' . $this->getDate() . '
AND o.valid = 1
AND od.product_id = ' . (int) $id_product . '
)
' . Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o') . '
AND o.date_add BETWEEN ' . $this->getDate() . '
AND o.valid = 1
AND od.product_id != ' . (int) $id_product . '
GROUP BY od.product_id
ORDER BY pqty DESC';
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
}
