Jump to content

[SOLVED] How to filter back office product list by supplier id

Riya

By Riya ,
in Addons, modules and themes developers

 Share

Recommended Posts

vekia Apprentice

vekia

Posted
Posted

I need this

 

How I made it ?

 

here is the solution:

 

open file:

controllers/admin/AdminProductsController.php

 

you can find there code:

$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
    LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
    LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.')
    LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')';

change it to:

$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
    LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
    LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.')
    LEFT JOIN `'._DB_PREFIX_.'supplier` supp ON (supp.id_supplier = a.`id_supplier`) 
    LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')';

below this code add this one:

$this->_select .= 'shop.name as shopname, supp.`name` AS `supp`, ';

and then, right after this:

$this->fields_list['id_product'] = array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'type' => 'int',
			'width' => 40
		);

add this one:

        $this->fields_list['supp'] = array(
			'title' => $this->l('Supplier'),
			'filter_key' => 'supp!name'
		);

Share this post

Link to post
Share on other sites
ventura Collaborator

ventura

Posted
Posted

No works 

 

Unknown column 'supp.name' in 'field list'

 

 

 

This is the code 

 

if (Shop::getContext() == Shop::CONTEXT_SHOP)
{
$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
                LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
                LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.')
                LEFT JOIN `'._DB_PREFIX_.'supplier` supp ON (supp.id_supplier = a.`id_supplier`) 
                LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')';
   $this->_select .= 'shop.name as shopname, supp.`name` AS `supp`, ';
}


else
{
$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)
LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)
LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = a.id_shop_default) 
LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop=a.id_shop_default)';
}
$this->_select .= 'shop.name as shopname, ';
}
else
{
$alias = 'a';

and then:

  

 

$this->_group = 'GROUP BY '.$alias.'.id_product';


$this->fields_list = array();
$this->fields_list['id_product'] = array(
'title' => $this->l('ID'),
'align' => 'center',
'width' => 20
);
 $this->fields_list['supp'] = array(
'title' => $this->l('Supplier'),
'filter_key' => 'supp!name'
); 
$this->fields_list['image'] = array(
'title' => $this->l('Photo'),
'align' => 'center',
'image' => 'p',
'width' => 70,
'orderby' => false,
'filter' => false,
'search' => false
);

where its the failure?

 

 

  • Like 1

Share this post

Link to post
Share on other sites
vekia Apprentice

vekia

Posted
Posted

ok,

 

code looks a bit different than code in 1.5.6.0

remove code that you added.

 

now right after:

if (Shop::isFeatureActive())
		{
			$alias = 'sa';
			$alias_image = 'image_shop';
			if (Shop::getContext() == Shop::CONTEXT_SHOP)
			{
				$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.(int)$this->context->shop->id.')
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.(int)$this->context->shop->id.')
				LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.(int)$this->context->shop->id.')  
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop='.(int)$this->context->shop->id.')';
			}
			else
			{
				$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)
				LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = a.id_shop_default)
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop=a.id_shop_default)';
			}
			$this->_select .= 'shop.name as shopname, ';
		}
		else
		{
			$alias = 'a';
			$alias_image = 'i';
			$this->_join .= 'LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = 1)';
		}

add this:

$this->_join .='LEFT JOIN `'._DB_PREFIX_.'supplier` supp ON (supp.id_supplier = a.`id_supplier`)';
$this->_select .= 'supp.`name` AS `supp`, ';

and then, right after this:

$this->fields_list['id_product'] = array(
            'title' => $this->l('ID'),
            'align' => 'center',
            'type' => 'int',
            'width' => 40
); 

add this one:

$this->fields_list['supp'] = array(
            'title' => $this->l('Supplier'),
            'filter_key' => 'supp!name'
);

 

 

effect:

wOLuNWX.png

 

  • Like 1

Share this post

Link to post
Share on other sites
  • 3 months later...
MGLimhamn Newbie

MGLimhamn

Posted
Posted

Hi and thanks for your solution vekia. However, if I filter by supplier on the product page then go to page 2 for example, the sorted list of suppliers doesn't work anymore. The URL changes to: &productOrderby=name&productOrderway=asc#product. Meaning that it changes the sort order from supplier to name.

 

How can I fix this?

Share this post

Link to post
Share on other sites
  • 2 weeks later...
bissio Newbie

bissio

Posted
Posted

Hi Vekia,

 

thanks for your usefull post about the supplier search. I have to search even for supplier reference... i mean if a have a supplier named supp01 and if this supplier has 3 reference codes, so how can i search for supplier code? I need to search for supplier reference.

 

Thanks

Share this post

Link to post
Share on other sites
  • 3 months later...
vekia Apprentice

vekia

Posted
Posted
after:
		$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
				LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.') 
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')';

add:

$this->_join .='LEFT JOIN `'._DB_PREFIX_.'supplier` AS supp ON (supp.id_supplier = a.`id_supplier`)';                
$this->_select .= 'supp.`name` AS `supp`, ';

and after:

		$this->fields_list['id_product'] = array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'class' => 'fixed-width-xs',
			'type' => 'int'
		);

add:

        $this->fields_list['supp'] = array(
            'title' => $this->l('Supplier'),
            'filter_key' => 'supp!name'
);
  • Like 2

Share this post

Link to post
Share on other sites
  • 2 months later...
subwayside Newbie

subwayside

Posted
Posted

hi,

 

i'm using ps1.6.0.9 and i want like when my employee login to back office then supplier filter will apply on it. 

so my employee can only see filtered by supplier product.

 

Like an x employe login to backoffice.

when he login and click on products then he will see only x supplier products.

 

same like other employee.

if y employe will login to BO then he only can see y supplier product on product list.

 

 

Please help how can i make that.

 

Thanks

Share this post

Link to post
Share on other sites
  • 1 month later...
  • 4 weeks later...
farrvince Newbie

farrvince

Posted
Posted

 

after:
		$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
				LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.') 
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')';

add:

$this->_join .='LEFT JOIN `'._DB_PREFIX_.'supplier` AS supp ON (supp.id_supplier = a.`id_supplier`)';                
$this->_select .= 'supp.`name` AS `supp`, ';

and after:

		$this->fields_list['id_product'] = array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'class' => 'fixed-width-xs',
			'type' => 'int'
		);

add:

        $this->fields_list['supp'] = array(
            'title' => $this->l('Supplier'),
            'filter_key' => 'supp!name'
);

 

 

Hello

How can I make this posible with Prestashop 1.6.0.9?

Share this post

Link to post
Share on other sites
  • 4 weeks later...
hrq1 Newbie

hrq1

Posted
Posted (edited)

<?php

 

/*

*** /public_html/yourshop/override/controllers/admin/AdminProductsController.php **

*/

 

class AdminProductsController extends AdminProductsControllerCore

{

public function __construct()

{

parent::__construct();

$this->fields_list['price_final'] = array(

'title' => $this->l('Final price'),

'width' => 90,

'type' => 'price',

'align' => 'right',

);

}

public function getList($id_lang, $orderBy = id_product, $orderWay = desc, $start = 0, $limit = null, $id_lang_shop = null)

{

$orderByPriceFinal = (empty($orderBy) ? ($this->context->cookie->__get($this->table.'Orderby') ? $this->context->cookie->__get($this->table.'Orderby') : 'id_'.$this->table) : $orderBy);

$orderWayPriceFinal = (empty($orderWay) ? ($this->context->cookie->__get($this->table.'Orderway') ? $this->context->cookie->__get($this->table.'Orderby') : 'ASC') : $orderWay);

AdminController::getList($id_lang, $orderBy, $orderWay, $start, $limit, $this->context->shop->id);

$nb = count($this->_list);

if ($this->_list)

{

for ($i = 0; $i < $nb; $i++)

{

$this->_list[$i]['price'] = Tools::convertPrice($this->_list[$i]['price'], $this->context->currency, true, $this->context);

$this->_list[$i]['price_tmp'] = Product::getPriceStatic($this->_list[$i]['id_product'], true, null, 2, null, false, true, 1, true);

}

}

if ($orderByPriceFinal == 'price_final')

{

if (strtolower($orderWayPriceFinal) == 'desc')

uasort($this->_list, 'cmpPriceDesc');

else

uasort($this->_list, 'cmpPriceAsc');

}

for ($i = 0; $this->_list && $i < $nb; $i++)

{

$this->_list[$i]['price_final'] = $this->_list[$i]['price_tmp'];

unset($this->_list[$i]['price_tmp']);

}

if ($orderByPriceFinal == 'price_final')

{

$order = (string)Tools::getValue('productOrderway');

usort($this->_list,

create_function('$a, $b','return $a["price_final"] ' . (strtolower($order) == 'desc' ? '<' : '>') . ' $b["price_final"];')

);

}

}

}

Edited by hrq1 (see edit history)

Share this post

Link to post
Share on other sites
  • 4 weeks later...
  • 6 months later...
  • 1 month later...
  • 2 months later...
  • 2 months later...
  • 2 months later...
  • 1 month later...
speedcrash2013 Newbie

speedcrash2013

Posted
Posted

 

after:
		$this->_join .= ' JOIN `'._DB_PREFIX_.'product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = '.$id_shop.')
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON ('.$alias.'.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = '.$id_shop.')
				LEFT JOIN `'._DB_PREFIX_.'shop` shop ON (shop.id_shop = '.$id_shop.') 
				LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = '.$id_shop.')';

add:

$this->_join .='LEFT JOIN `'._DB_PREFIX_.'supplier` AS supp ON (supp.id_supplier = a.`id_supplier`)';                
$this->_select .= 'supp.`name` AS `supp`, ';

and after:

		$this->fields_list['id_product'] = array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'class' => 'fixed-width-xs',
			'type' => 'int'
		);

add:

        $this->fields_list['supp'] = array(
            'title' => $this->l('Supplier'),
            'filter_key' => 'supp!name'
);

 

WOW

it's works perfectly on 1.6.0.and 1..6.1.5

Thanks VEKIA

Share this post

Link to post
Share on other sites
  • 6 months later...
ELITIV Newbie

ELITIV

Posted
Posted

Hi.

 

Any solution for - Available for order (status in list)

 

P.S. This, only show statuses but, filter_key and enable\disable not working.

        $this->fields_list['Available_for_order'] = array(
			'title' => $this->l('Available For order'),
			'width' => 90,
			'type' => 'bool',
			'align' => 'right',
            'active' => 'available_for_order',
			'filter_key' => $alias.'!available_for_order'
		);

Thank you for help.

Share this post

Link to post
Share on other sites
  • 1 month later...
Sergio Martins Newbie

Sergio Martins

Posted
Posted

Hi.

 

Any solution for - Available for order (status in list)

 

P.S. This, only show statuses but, filter_key and enable\disable not working.

        $this->fields_list['Available_for_order'] = array(
			'title' => $this->l('Available For order'),
			'width' => 90,
			'type' => 'bool',
			'align' => 'right',
            'active' => 'available_for_order',
			'filter_key' => $alias.'!available_for_order'
		);

Thank you for help.

Can anyone help? I would also like to know how to apply the enable\disable function.

Share this post

Link to post
Share on other sites
  • 10 months later...
Alex Sanchez Newbie

Alex Sanchez

Posted
Posted (edited)

Hello,

 

Can someone help to answer the questions above? 

On 30/12/2016 at 10:17 PM, Sergio Martins said:

Can anyone help? I would also like to know how to apply the enable\disable function.

 

On 7/11/2016 at 0:09 AM, ELITIV said:

Hi.

 

Any solution for - Available for order (status in list)

 

P.S. This, only show statuses but, filter_key and enable\disable not working. 

        $this->fields_list['Available_for_order'] = array(
			'title' => $this->l('Available For order'),
			'width' => 90,
			'type' => 'bool',
			'align' => 'right',
            'active' => 'available_for_order',
			'filter_key' => $alias.'!available_for_order'
		);

Thank you for help.

 

I have the same problem, the enable/disable function on the available for order button i have added.

Best regards

Edited by Alex Sanchez (see edit history)

Share this post

Link to post
Share on other sites
  • 2 months later...
decome2013 Newbie

decome2013

Posted
Posted

Hello !

I kow this topic is about product list ^^ however i would like to make exactly the same thing (display Supplier name) on Orders list page in BO.

In cas of several products for an order, does not matter if there is no information on line, it's very rare on my shop.

I tried to put the code on AdminOrdersController, but i get an error :(

 

Any chance to get the same code that Products list for Orders list on 1.6.0.14 ?
Regards,

Share this post

Link to post
Share on other sites
  • 2 years later...
  • 2 years later...
Tiburoncin23 Newbie

Tiburoncin23

Posted
Posted

I solved this issue in PS 1.7.8

I want to add a filter by brand_name so there are the steps to make this work

Pd: my store is configurated in spanish, so I don´t need translations for my_column. If you needed, have to modify the translate file.

I had to modify 4 files:

1 - HEADER

Search this file: 'your_dir/src/PrestaShopBundle/Resources/views/Admin/Product/CatalogPage/Lists/products_table.html.twig'

The first <tr> make reference to the tittle, so you have to add a <th> inside, in the position do you want: 

<!--  Nuevo filtro de marca -->
<th scope="col">
	{{ ps.sortable_column_header("Marca", 'brand_name', orderBy, sortOrder) }}
</th> 

In the next <tr>, add an input field in the same position as the tittle 

<!--  Nuevo filtro de marca -->
<th>
  <input
    type="text"
    class="form-control"
    placeholder="Buscar por marca"
    name="filter_column_brand_name"
    value="{{ filter_column_brand_name }}"
    aria-label="{{ "%inputId% input"|trans({'%inputId%': 'filter_column_brand_name'}, 'Admin.Global') }}"
    {% if filters_disabled %}disabled{% endif %}
  />
</th>

The property name is important, because the filters take this name and verify its contains 'filter_column' ins the string. Keep this format if you want to filter by this new column. 

2 - BODY

Search this file: 'your_dir/src/PrestaShopBundle/Resources/views/Admin/Product/CatalogPage/Lists/list.html.twig'

Add the column for the data in the same position as the filter 

<!--  Nuevo filtro de marca -->
<td>
	{{ product.brand_name|default('') }}
</td>

In my case, named the variable as 'brand_name', in the next step we are going to chance the query to add this variable.

3 - QUERY 

Search this file: 'your_dir/src/Adapter/Product/AdminProductDataProvider.php'

Search getCatalogProductList() function, there is a variable named $sqlSelect

Modify the arguments and add the brand_name variable:

$sqlSelect = [
            'id_product' => ['table' => 'p', 'field' => 'id_product', 'filtering' => ' %s '],
            'brand_name' => ['table' => 'mn', 'field' => 'name', 'filtering' => self::FILTERING_LIKE_BOTH],
            'reference' => ['table' => 'p', 'field' => 'reference', 'filtering' => self::FILTERING_LIKE_BOTH],
            . . . 
        ];

In the following variable $sqlTable add the reference for the manufactered table:

$sqlTable = [
            'p' => 'product',            
            'mn' => [
                    'table' => 'manufacturer',
                    'join' => 'JOIN',
                    'on' => 'mn.`id_manufacturer` = p.`id_manufacturer`',
            ],'pl' => [ . . .

4 - FILTER

Search this file 'your_dir/src/PrestaShopBundle/Entity/AdminFilter.php'

Search getProductCatalogEmptyFilter() function, in return array, add:

return [
            'filter_category' => '',
            'filter_column_brand_name' => '', . . . 

And finally,

Search sanitizeFilterParameters() function, in return array, add:

 return filter_var_array($filter, [
            'filter_category' => FILTER_SANITIZE_NUMBER_INT,
            'filter_column_id_product' => [
                'filter' => FILTER_CALLBACK,
                'options' => $filterMinMax(FILTER_SANITIZE_NUMBER_INT),
            ],
            'filter_column_brand_name' => FILTER_SANITIZE_STRING, . . .

 

5 - TEST

It works with the other filters:

image.png.ae1f7e0eb8be34127418d04cb17e6a6c.png

 

That's all, send me message if you have problems with this implementation.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More

  I accept