How to define a new function in AdminTrackingController.php

[Zhivko]

Hi,

 

I'm trying to add a new Monitoring option to Catalog -> Monitoring screen. The goal is to display all products with Specific Price end date defined.

 

I override AdminTrackingController.php in /override/controllers/admin by defining a new function

public function getCustomListSpecialPrices()
{
 if (!Configuration::get('PS_STOCK_MANAGEMENT'))
  return;

 $this->table = 'product';
 $this->lang = true;
 $this->identifier = 'id_product';
 $this->_orderBy = 'id_product';
 $this->_orderWay = 'ASC';
 $this->className = 'Product';
 $this->_list_index = 'index.php?controller=AdminProducts';
 $this->_list_token = Tools::getAdminTokenLite('AdminProducts');
 $this->show_toolbar = false;
 $this->addRowAction('edit');
 $this->addRowAction('delete');
 $this->fields_list = array(
  'id_product' => array('title' => $this->l('ID'), 'width' => 50),
  'name' => array('title' => $this->l('Name'), 'filter_key' => 'b!name'),
  'specific_end' => array('title' => $this->l('Specific Price End')),
  'active' => array('title' => $this->l('Status'), 'type' => 'bool', 'active' => 'status', 'width' => 50)
 );

 $this->clearFilters();

 $this->_select = 'user_alias.`to` AS specific_end';
 $this->_join = $this->_join = Shop::addSqlAssociation('product', 'a').
' LEFT JOIN `'._DB_PREFIX_.'specific_price` user_alias ON (a.`id_product` = user_alias.`id_product`)';
 $this->_filter = 'AND user_alias.`to` IS NOT NULL';
 $this->tpl_list_vars = array('sub_title' => $this->l('List of products with stored special price end date:'));
 return $this->renderList();
}

 

I need to add a _select clause in order to retrieve Specific Price end date and display it in the result list. There are two issues with this:

1. $this->_select = 'user_alias.`to` AS specific_end';

the same select clause is added to core functions SQL requests thus causing an SQL error.

Example:

SELECT SQL_CALC_FOUND_ROWS
 b.*, a.*
 , user_alias.`to` AS specific_end
 FROM `ps_category` a
 LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
  INNER JOIN ps_category_shop category_shop
 ON (category_shop.id_category = a.id_category AND category_shop.id_shop = 1)

 WHERE 1   AND a.`id_category` NOT IN (
  SELECT DISTINCT(cp.id_category)
  FROM `ps_category_product` cp
 )
 AND a.`id_category` != 1


 ORDER BY name desc LIMIT 0,50

 

Workaround: I solved this by adding $this->_select = false; to all core AdminTrackingController functions and in AdminController.php I added a check

(isset($this->_select) ? ', '.$this->_select : '')

becomes

((isset($this->_select) && $this->_select != false) ? ', '.$this->_select : '')

 

2. When a sort by filter is selected the same sort order is applied to all results. Example: if I select to sort by product name "List of products with attributes and without available quantities for sale:" => the same sort criteria is applied to all product related lists.

The problem: either after I succeed to display Specific Price end date it is not possible to sort results by this filter. This sort order will trigger SQL errors in 'order by' clause for all other lists.

 

Workaround: 'specific_end' field can be defined as 'orderby' => false to avoid ordering

 

3. The same pb appears if results are filtered by this field: there is a SQL error in WHERE clause

 

Workaround: 'specific_end' field can be defined as 'search' => false to avoid filtering

 

Please advise how these issues can be solved in a proper way.

Edited November 9, 2012 by Zhivko (see edit history)