Advance search by price range error when show price with tax

[♥raiwa]

Hello,

 

Preparing a new shop by price add-on version for BS, I found the following problem for the advanced search query, which seems to exist in all versions at least since 2.2MS2 to 2.3.4 BS:

 

When "Display Prices with Tax" is switched to "true" in " My Store" and the tax  and tax zone is defined the following happens:

- search for price range in advanced search shows wrong results in the sense that if searching for range 35$-45$, the result include products with price 45$+tax.

 

You can see this example in my demo store (tax zone is set to all countries, all zones for testing):

http://www.sarfotostock.com/osCommerce234bs/advanced_search_result.php?keywords=%20&search_in_description=1&inc_subcat=1&pfrom=35&pto=45

 

You can observe that there are products with more than 45$ included in the result.

 

I tried to research in the SQL Query of advanced_search_result.php and found that the problem is here in the second line (line264-266):

  if (DISPLAY_PRICE_WITH_TAX == 'true') {
    if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";
    if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

The first line, lower limit seems to work correct, meanwhile in the second line for the upper limit, "gz.geo_zone_id" and "tr.tax_rate" does not pass a value at all. Only if I change "tr.tax_rate" to a fix numeric value and remove "if(gz.geo_zone_id is null, 1, " it gives a correct result.

Si it looks like this:

    if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * 1 + (7 / 100) <= " . (double)$pto . ")";

I tried all what I could without success.

Can anyone help, please

 

Thank you and kind regards

Rainer

[Bob Terveuren]

Hi

 

What do you get if you echo out the $listing_sql at line 326 or so?

 

$listing_sql = $select_str . $from_str . $where_str . $order_str;

[♥raiwa]

select distinct p.products_image, m.manufacturers_id, p.products_id, SUBSTRING_INDEX(pd.products_description, ' ', 20) as products_description, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price , SUM(tr.tax_rate) as tax_rate from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '223') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '18'), products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= 35) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 45) group by p.products_id, tr.tax_priority order by pd.products_name

[Bob Terveuren]

Hi

 

Ow my head hurts! Only thing I can suggest is that gz.geo_zone_id is coming down to your line

if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) 

as null so the 1 is always getting used rather than 1 + (tr.tax_rate / 100) - if you look at the lower end of the selection then I'd bet that a product which is $35 including tax is not being picked up for the same reason.

 

Unfortunatley I simply cannot get my head around the sql further up the listing but if I run the sql and include gz.geo_zone_id in the select part I get two results for each product - one has NULL for gz.geo_zone_id and the other has an integer - maybe that'll ring a bell for somebody else?

[♥raiwa]

I checked again on my live store which has more products to be sure.

 

Here the results, changes are always applied on both lines, lower limit and upper limit:

 

Price range: 60-70 EUR VAT=21%

original code: * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ):
lowest 60 EUR correct
highest 84,70 EUR wrong

 * if(gz.geo_zone_id is null, 1, 1 + (21 / 100) )
 
lowest 60 EUR correct
highest 84,70 EUR wrong

  * 1 + (tr.tax_rate / 100)
 
lowest 72,60 EUR wrong
highest 84,70 EUR wrong

  * 1.21
 
lowest 60 EUR correct
highest 70 EUR correct
 

 

For me both values, tr.tax_rate AND gz.geo_zone_id are not passed correct.

[♥raiwa]

@@Bob Terveuren,

 

I believe I found the fix:

in advanced_search_result.php line 193:

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

should be "innner join" instead of "left join":

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id inner join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

kind regards

Rainer

[♥raiwa]

@@Bob Terveuren,

 

I found now the reason for the wrong price range results with the core query:

Tax zones were setup in a "strange " way in both test store.

- in one store, EU tax zone with 21 % applied to all products, had 2 more zones for rest of the world set up with 0% tax.

- in another store default "Florida" zone had "All countries" added, so applied tax twice in the results.

 

However, I noticed that changing "left join" to " inner join" speeds up very much the query.

I then tried to find a configuration where "inner join" doesn't throw correct results and couldn't find it.

So, if there exist a tax configuration which needs "left join", maybe this could be checked in previous query and "left join" or "inner join" could be used depending on the tax setup.

"inner join" seems to make the query also more stable for "strange" setups like descibed at the beginning.

 

regards

Rainer