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

mowax · August 7, 2015

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 · August 10, 2015

anybody help please?

herve25 · August 11, 2015

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 · August 12, 2015

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 · August 12, 2015

Hello,

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

and delete the file cache/class_index.php ?

mowax · August 12, 2015

Yup, I did all the above.

herve25 · August 13, 2015

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 · August 13, 2015

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 · August 14, 2015

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 · August 14, 2015

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 · August 14, 2015

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 · August 15, 2015

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

mowax · August 15, 2015

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 · August 15, 2015

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 · August 16, 2015

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 · August 17, 2015

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 · August 17, 2015

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 · October 14, 2015

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)

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in