blocklayered min_price missing vat

hannhimhe · February 12, 2013

Yet another blocklayered issue

I am running Prestashop 1.5.3.1 and with that blocklayered 1.8.9

The price slider shows min_price without vat and max_price with vat. I've isolated the problem to:

'.(int)$min_price[$currency['id_currency']].',
'.(int)Tools::ps_round($max_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).')';

and changed it to:

'.(int)Tools::ps_round($min_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).',
'.(int)Tools::ps_round($max_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).')';

I rebuilt the price index and FO shows the slider as it should be but when I go to visit Modules in BO I can no longer access the blocklayered module. Instead I am, at the top of the page, told that the module could not be loaded, read error, class is missing, bla bla bla.

I have changed one row, nothing else, in blocklayered.php, what more is needed for BO to accept this?

I have reinstalled blocklayered.php and made sure I did not change anything but the row mentioned above.

TL;DR;

I changed a row in blocklayered.php and now BO tells me blocklayered is missing a class. What did I forget to do or did I screw something up?

Edited February 12, 2013 by hannhimhe (see edit history)

hannhimhe · February 20, 2013

The issue was with the editor.

Tax should still be added to $min_price though.

morso · July 22, 2013

Hi

Sorry for my english

I have a similar problem, but with maximum and mimimum price.

I solved problem with minimum price using this:

"'.(int)Tools::ps_round($min_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).',<br>'.(int)Tools::ps_round($max_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).')';"

Big Thanks! But I still have problem with maximum price in table "ps_layered_price_index"

The problem occurs only when I use specific price.

I'm working on prestashop 1.5.4.1 and blocklayered 1.8.9 (fresh, clean install)

I created new product with price 10 000PLN (with 23% tax) and added specific price -5000PLN (PLN - polish currency)

I don't know why, but it looks like blocklayered adding 23% tax to specific price

10000 - (5000 + 23%*5000) = 10000 - 6150 = 3850

and it should be 5000

If I turn off tax in product everything works fine

I tried delete file "cache/class_index.php and edit line:

LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = p.id_tax_rules_group AND p.id_shop = '.(int)$shop_list.')

- did not help

I'm not turned on multishop

Anybody know how to fix this?

spc · August 6, 2013

Hello

I have the same problem...

in my blocklayered.php i got :

'.(int)$minPrice[$currency['id_currency']].',
'.(int)Tools::ps_round($maxPrice[$currency['id_currency']] * (100 + $maxTaxRate) / 100, 0).')';

in the BO i got " Use tax to filter price " my i se the price without tax...

Hope that som one can help me.

avionicom · September 20, 2013

Same problem for me with PS 1.5.5 also with the default theme.

Furthermore the price slider do not take care of price reduction (other than the taxes)!

Anyone solved definitely?

avionicom · September 20, 2013

Summarizing the posts of hannhimhe and ITManager, considering that I'm not running a Multishop, to solve the issue related to the taxes not included in the price filter, I followed these steps:

1. In the file blocklayered.php, changed these lines:

'.(int)$min_price[$currency['id_currency']].',
'.(int)Tools::ps_round($max_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).')';

to:

'.(int)Tools::ps_round($min_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).',
'.(int)Tools::ps_round($max_price[$currency['id_currency']] * (100 + $max_tax_rate) / 100, 0).')';

2. Deleted the file "cache/class_index.php"

3. Edited the file blocklayered.php from:

LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = p.id_tax_rules_group AND p.id_shop = '.(int)$shop_list.')

to:

LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = p.id_tax_rules_group AND p.id_shop = '.(int)$id_shop.')

Now I have another problem: the filter do not works with special prices. Infact, for example, if I filter with a range o prices from 0 to 90€, and there is a product that cost 100€ but was reducted by 30% discount, it should be in the list. Instead, it appears only if the filter for the max_price is >=100. In other words the filter is set on the normal price only!

tomerg3 · September 20, 2013

I suggest you post this on the bug tracker, as it is a problem that would likely require pretty big code changes.

Hopefully the team would be able to address this quickly.

I believe many other filters would have this issue as well, although I have not tested it.

If you need an immediate solution, you can take a look at our Ajax Filter which handles discounts, taxes and customer specific pricing correctly.

http://www.prestashop.com/forums/topic/118467-module-ajax-filter-ajax-based-filter-using-price-supplier-man-category-feature-attribute/

avionicom · September 21, 2013

Hi tomerg3, I already reported yesterday to bug tracker hoping that they will solve this issue.

Your filter is very good, but at the moment is not in our budget for this project. Thank you.

annashial · October 4, 2015

Hi, I know this is an old topic but I have this issue and I couldn't solve it. I followed the steps are summarizing by avionicom but nothing changed. Any ideas? Somebody who fixed that issue?

Derres · December 31, 2015

x

Edited December 31, 2015 by Derres (see edit history)

Derres · December 31, 2015

Any way for fix the price filter with the items in promotion ?

Thanks

-iD- · November 7, 2016

Prestashop 1.6.0.1 - multishop

This change is for prices including tax

My problem was like this :

- I have product list with price including tax

- filtering price : with the range set between 2,00 € - 15.60 € , and I was having some products with price 19 €.

- even if I was having "Use tax to filter price" set ON in blocklayered configuration

So what I did is to add 2 conditions in WHERE statement for $this->products inside function getProductByFilters in \modules\blocklayred\blocklayred.php

Around line 220 turn this :

if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)
			{
				$this->products = Db::getInstance()->executeS('
				SELECT
					p.*,
					'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
					'.$alias_where.'.id_category_default,
					pl.*,
					image_shop.`id_image` id_image,
					il.legend,
					m.name manufacturer_name,
					'.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
					DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
					stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
				FROM '._DB_PREFIX_.'cat_filter_restriction cp
				LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
				'.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->shop->id.')':'').'
				LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
				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)$cookie->id_lang.')
				LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
				'.Product::sqlStock('p', 0).'
				WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
				ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
				' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false);
			}
			else
			{
				$this->products = Db::getInstance()->executeS('
				SELECT
					p.*,
					'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
					'.$alias_where.'.id_category_default,
					pl.*,
					MAX(image_shop.`id_image`) id_image,
					il.legend,
					m.name manufacturer_name,
					'.(Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '').'
					DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
					stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '').'
				FROM '._DB_PREFIX_.'cat_filter_restriction cp
				LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').
				(Combination::isFeatureActive() ?
				'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 AND product_attribute_shop.id_shop='.(int)$context->shop->id):'').'
				LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
				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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
				LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
				'.Product::sqlStock('p', 0).'
				WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
				GROUP BY product_shop.id_product
				ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
				' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false); 
			}

into this

if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)
			{
				$this->products = Db::getInstance()->executeS('
				SELECT
					p.*,
					'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
					'.$alias_where.'.id_category_default,
					pl.*,
					image_shop.`id_image` id_image,
					il.legend,
					m.name manufacturer_name,
					'.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
					DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
					stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
				FROM '._DB_PREFIX_.'cat_filter_restriction cp
				LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
				'.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->shop->id.')':'').'
				LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
				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)$cookie->id_lang.')
				LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
				'.Product::sqlStock('p', 0).'
				WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")

                                '.(isset($price_filter['max'])?' AND ('.$alias_where.'.`price` * ((100 +(SELECT max(t.rate) max_rate
					FROM `'._DB_PREFIX_.'product_shop` pcustom
					LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = pcustom.id_tax_rules_group AND pcustom.id_shop = '.(int)$context->shop->id.')
					LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (tr.id_tax_rules_group = trg.id_tax_rules_group)
					LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.id_tax = tr.id_tax AND t.active = 1)
					WHERE pcustom.id_product = product_shop.id_product GROUP BY pcustom.id_product))/100)) <= '.$price_filter['max'].' ':'').'
				'.(isset($price_filter['min'])?' AND ('.$alias_where.'.`price` * ((100 + (SELECT max(t.rate) max_rate
					FROM `'._DB_PREFIX_.'product_shop` pcustom
					LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = pcustom.id_tax_rules_group AND pcustom.id_shop = '.(int)$context->shop->id.')
					LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (tr.id_tax_rules_group = trg.id_tax_rules_group)
					LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.id_tax = tr.id_tax AND t.active = 1)
					WHERE pcustom.id_product = product_shop.id_product GROUP BY pcustom.id_product)) / 100) ) >= '.$price_filter['min'].' ':'').'


				ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
				' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false);
			}
			else
			{
				$this->products = Db::getInstance()->executeS('
				SELECT
					p.*,
					'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
					'.$alias_where.'.id_category_default,
					pl.*,
					MAX(image_shop.`id_image`) id_image,
					il.legend,
					m.name manufacturer_name,
					'.(Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '').'
					DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
					stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '').'
				FROM '._DB_PREFIX_.'cat_filter_restriction cp
				LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').
				(Combination::isFeatureActive() ?
				'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 AND product_attribute_shop.id_shop='.(int)$context->shop->id):'').'
				LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
				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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
				LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
				'.Product::sqlStock('p', 0).'
				WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")

                                '.(isset($price_filter['max'])?' AND ('.$alias_where.'.`price` * ((100 +(SELECT max(t.rate) max_rate
					FROM `'._DB_PREFIX_.'product_shop` pcustom
					LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = pcustom.id_tax_rules_group AND pcustom.id_shop = '.(int)$context->shop->id.')
					LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (tr.id_tax_rules_group = trg.id_tax_rules_group)
					LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.id_tax = tr.id_tax AND t.active = 1)
					WHERE pcustom.id_product = product_shop.id_product GROUP BY pcustom.id_product))/100)) <= '.$price_filter['max'].' ':'').'
				'.(isset($price_filter['min'])?' AND ('.$alias_where.'.`price` * ((100 + (SELECT max(t.rate) max_rate
					FROM `'._DB_PREFIX_.'product_shop` pcustom
					LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = pcustom.id_tax_rules_group AND pcustom.id_shop = '.(int)$context->shop->id.')
					LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (tr.id_tax_rules_group = trg.id_tax_rules_group)
					LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.id_tax = tr.id_tax AND t.active = 1)
					WHERE pcustom.id_product = product_shop.id_product GROUP BY pcustom.id_product)) / 100) ) >= '.$price_filter['min'].' ':'').'


				GROUP BY product_shop.id_product
				ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
				' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false); 
			}

So the conditions force my result to get only those products where their prices includes tax and their prices are between my interval.

Conditions :

                                '.(isset($price_filter['max'])?' AND ('.$alias_where.'.`price` * ((100 +(SELECT max(t.rate) max_rate
					FROM `'._DB_PREFIX_.'product_shop` pcustom
					LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = pcustom.id_tax_rules_group AND pcustom.id_shop = '.(int)$context->shop->id.')
					LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (tr.id_tax_rules_group = trg.id_tax_rules_group)
					LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.id_tax = tr.id_tax AND t.active = 1)
					WHERE pcustom.id_product = product_shop.id_product GROUP BY pcustom.id_product))/100)) <= '.$price_filter['max'].' ':'').'
				'.(isset($price_filter['min'])?' AND ('.$alias_where.'.`price` * ((100 + (SELECT max(t.rate) max_rate
					FROM `'._DB_PREFIX_.'product_shop` pcustom
					LEFT JOIN `'._DB_PREFIX_.'tax_rules_group` trg ON (trg.id_tax_rules_group = pcustom.id_tax_rules_group AND pcustom.id_shop = '.(int)$context->shop->id.')
					LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (tr.id_tax_rules_group = trg.id_tax_rules_group)
					LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.id_tax = tr.id_tax AND t.active = 1)
					WHERE pcustom.id_product = product_shop.id_product GROUP BY pcustom.id_product)) / 100) ) >= '.$price_filter['min'].' ':'').'

Hope it helps you guys. Edited November 7, 2016 by -iD- (see edit history)

Attrexx · May 31, 2017

My site crashes when I do the trick, -iD-

blocklayered min_price missing vat

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

Create an account or sign in to comment

Create an account

Sign in