[Solved] Adding 'supplier_reference' column to the products table in Admin Search

[mowax]

Hi

 

Really hope someone can help, been trying to figure this out for so long. I need to insert a column into the products table on the search results page (displayed when you search for a product in the search box in the top of the adminpanel). In the column I need to display the supplier's product reference for each product in the table. I have modified AdminSearchController.php and inserted the column, but I can't get the values to display.

 

Please see the attached screenshot for clarification.

 

I think I have to modify AdminProductsController.php to get the values to display but I have no idea how to do it.

 

Any help would be really appreciated!

 

Edited August 17, 2015 by mowax (see edit history)

[mowax]

anybody help please?

[herve25]

Hello,

 

You can override the AdminProductController using the folowing code and it should works.
( But only for the products without combinations )

<?php
class AdminProductsController extends AdminProductsControllerCore
{
	
	public function __construct()
	{
		
		parent::__construct();
		
		// Join the table product supplier in order to get product supplier reference ( Only Work for products without combinations )
		$this->_join .= ' LEFT JOIN '._DB_PREFIX_.'product_supplier ps ON ( a.id_product = ps.id_product AND ps.id_product_attribute = 0 )';
		
		//Add new field supplier reference in field list
		$this->fields_list['product_supplier_reference'] = array(
			'title' => $this->l('Supplier Reference'),
			'align' => 'left',
			'filter_key' => 'ps!product_supplier_reference',
			'width' => 80
		);
	}
}

[mowax]

thanks for your reply herve

 

this override does not work for me. there is no change in the admin search page, and the admin products page is blank (broken)

 

have you any advice?

 

thank you so much for your time

[herve25]

Hello,

 

Do you put this file in the right place ? ( override/controllers/admin/AdminProductsController.php )

and delete the file cache/class_index.php ?

[mowax]

Yup, I did all the above.

[herve25]

So in order to know  the error you should enable display_errors.
I should help you to solve this problem.

 

I've successly done this override on a blank prestashop 1.5.4.1 version.
( And it works even on a 1.6.0.14 )

[mowax]

hi, i must have messed something up on the first attempt because it is working now    sorry for my mistake

 

but as you said it only works for products without combinations, is there any way to fix that? 

[mowax]

Ok i just figured out how to add the supplier reference for products with combinations - in the ps_products_supplier table in the database, if the product has combinations it will always have a row in this table where the id_product_attribute value is 0, and if you input something in the product_supplier_reference field in this row, it will be displayed in the back office products list page. So that's great!

 

However, I actually need this column in the admin search results page, that is displayed when you enter something in the search box at the very top of the BO page. Please see the attachment to see how I mean.

 

 

Thanks for your patience

[herve25]

Hello,

 

This search is managed in the controller AdminSearchController and as you can see in the file https://github.com/PrestaShop/PrestaShop/blob/1.5.4.1/controllers/admin/AdminSearchController.php#L169

It use the fonction Product::searchByName to get the function.

 

So you have to override this function by creating a new file in override/classes/Product.php with the folowing content :

<?php 
class Product extends ProductCore {


	/**
	* 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).'%\'
		OR  p.`id_product` IN (SELECT id_product FROM '._DB_PREFIX_.'product_supplier sp WHERE `product_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));
			
			//Join Product Supplier Table
			$sql->leftJoin('product_supplier', 'sp', 'sp.`id_product` = p.`id_product`');
			
			//Change Where condition in order to search supplier reference
			$where .= ' OR pa.`reference` LIKE \'%'.pSQL($query).'%\'
					    OR p.`supplier_reference` LIKE \'%'.pSQL($query).'%\'
					    OR `product_supplier_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;
	}

}

Then your search will look into the suppliers references.

[mowax]

thank you, but it didn't work for me. the column i inserted still displays '--' for all the rows. do i need to modify adminsearchcontroller.php somehow?

[herve25]

Can you show me the change you have done ?
My modification only change the results of the search, not the display.

[mowax]

in adminsearchcontroller.php, I added the 'supplier_reference' line below to the initProductList function:

	protected function initProductList()
	{
		$this->show_toolbar = false;
		$this->fields_list['products'] = array(
			'id_product' => array('title' => $this->l('ID'), 'width' => 25),
			'manufacturer_name' => array('title' => $this->l('Supplier'), 'align' => 'center', 'width' => 250),
			'supplier_reference' => array('title' => $this->l('Supplier Ref.'), 'align' => 'center', 'width' => 125),
			'reference' => array('title' => $this->l('Our Ref.'), 'align' => 'center', 'width' => 125),
			'name' => array('title' => $this->l('Name'), 'width' => 'auto'),
			'price_tax_excl' => array('title' => $this->l('Price (tax excl.)'), 'align' => 'right', 'type' => 'price', 'width' => 60),
			'price_tax_incl' => array('title' => $this->l('Price (tax incl.)'), 'align' => 'right', 'type' => 'price', 'width' => 60),
			'active' => array('title' => $this->l('Active'), 'width' => 70, 'active' => 'status', 'align' => 'center', 'type' => 'bool')
		);
	}

Edited August 15, 2015 by mowax (see edit history)

[herve25]

The way you have done it, it's normal that nothing appears.
As you're trying to display the field supplier_reference from the table product.
Whereas the content you're looking for in the field product_supplier_reference from the table product_supplier

 

[mowax]

Ah ok. Sorry, I'm not much of a programmer. As per your suggestion I added this to the bottom of the initProductList function:

 

        $this->fields_list['product_supplier'] = array(
            'product_supplier_reference' => array('title' => $this->l('Supplier Ref.'), 'align' => 'center', 'width' => 125),
        );

 

But that is not working. The column does not display at all, now that I created a new fields_list for the product_supplier table. Do I need to create a new function instead? 

	protected function initProductList()
	{
		$this->show_toolbar = false;
		$this->fields_list['products'] = array(
			'id_product' => array('title' => $this->l('ID'), 'width' => 25),
			'reference' => array('title' => $this->l('Our Ref.'), 'align' => 'center', 'width' => 125),
			'name' => array('title' => $this->l('Name'), 'width' => 'auto'),
			'price_tax_excl' => array('title' => $this->l('Price (tax excl.)'), 'align' => 'right', 'type' => 'price', 'width' => 60),
			'price_tax_incl' => array('title' => $this->l('Price (tax incl.)'), 'align' => 'right', 'type' => 'price', 'width' => 60),
			'active' => array('title' => $this->l('Active'), 'width' => 70, 'active' => 'status', 'align' => 'center', 'type' => 'bool'),
			'manufacturer_name' => array('title' => $this->l('Supplier'), 'align' => 'center', 'width' => 250),
		);

		$this->fields_list['product_supplier'] = array(
			'product_supplier_reference' => array('title' => $this->l('Supplier Ref.'), 'align' => 'center', 'width' => 125),
		);
	}

[herve25]

It's almost good, the problem is that you have no data to display as the field product_supplier_reference is not selected in the product query.

So you juste have to add it in the product override

<?php 
class Product extends ProductCore {


	/**
	* 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).'%\'
		OR  p.`id_product` IN (SELECT id_product FROM '._DB_PREFIX_.'product_supplier sp WHERE `product_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));

//Select the product supplier reference			
$sql->select('sp.product_supplier_reference');

			//Join Product Supplier Table
			$sql->leftJoin('product_supplier', 'sp', 'sp.`id_product` = p.`id_product`');
			
			//Change Where condition in order to search supplier reference
			$where .= ' OR pa.`reference` LIKE \'%'.pSQL($query).'%\'
					    OR p.`supplier_reference` LIKE \'%'.pSQL($query).'%\'
					    OR `product_supplier_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;
	}

}

Then you can do your override in the AdminSearchProduct Controller :

<?php
class AdminSearchController extends AdminSearchControllerCore
{
	protected function initProductList()
	{
		parent::initProductList();
		//Add new field to display
		$this->fields_list['products']['product_supplier_reference'] = array('title' => $this->l('Supplier reference'), 'width' => 'auto');
	}
}

Don't forget to clear the file class_index, then everything should works.

[mowax]

Yesssss! It finally works    such a relief

 

Thank you so much for your help with this, you are great! 

 

Now I have to manually input all those missing references in the database for every product... fun fun fun 

 

All the best to you herve25

[shakir.app]

how can i add two column namely ( supplier reference  and  supplier name ) in product list  in presta shop 1.6

Edited October 14, 2015 by shakir.app (see edit history)