If this is your first visit, check our documentation. To post, register or login.
Forums / Bug Reports / Problematic code in advanced search result ?

Problematic code in advanced search result ?

Started by angeloio on 13 Jul 2010, 9:09 AM · Views: 2,952

Results 1 to 7 of 7
13 Jul 2010, 9:09 AM
#1
angeloio avatar

angeloio

New Zenner

Join Date:
Nov 2008
Posts:
9
Plugin Contributions:
0

Problematic code in advanced search result ?

The Zen Cart version is 1.3.8a but from what I checked it is the same code in the newest release.

The code part I am talking about is in includes\modules\pages\advanced_search_result\header_php.php lines 231-236

  $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 = :zoneCountryID)
                 AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = :zoneID)";

The above is executed when ```php
DISPLAY_PRICE_WITH_TAX == 'true'


OK the comments now:

1. First of all the search function by prices does not work correctly in my shop. Products with prices outside of the searched range are returned....so I did a little debugging and found out the following:

2. The conditional statement in the JOIN syntax above is a little strange :blink:. Granted you can have LEFT JOIN ... ON ... AND ... AND... but it does produce weird results :
(*I am living in Greece so we have a simple 23% VAT on top of all net prices, for all country so: net*1.23=final(displayable) price*)

Take for example the following query (84 is the ID of Greece):

SELECT * FROM tax_rates tr 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 = 84)
AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 0)

It produces the very weird results (see clearly attached doc) 

tax_rates_id tax_zone_id geo_zone_id zone_country_id
3 	                4 	              4 	84
4 	                5 	           NULL 	NULL
5 	                6 	           NULL 	NULL

Now, on line 365+ the returned products are found based on the :
```php
    $where_str .= " AND (p.products_price_sorter * IF(gz.geo_zone_id IS null, 1, 1 + (tr.tax_rate / 100)) >= :price)";

which (because of the IS null condition and the NULL lines above) for some products DOES NOT add the VAT on top of the net price and hence produces WRONG results!!!

The normal according to my view would have been a more "logical SQL":

SELECT * FROM `tax_rates` tr, `zones_to_geo_zones` gz [B]WHERE[/B]  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 = 84)
                 AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 0)

which DOES RETURN ONLY ONE LINE, the correct one:

tax_rates_id tax_zone_id geo_zone_id zone_country_id
3 4 4 84

and everything works correctly !

Any ideas if this is really a bug, or if there is some logic behind the NULL values that I could not see ???

Many thanks to all for your time !

Kind Regards
John

13 Jul 2010, 2:18 PM
#2
blindside avatar

blindside

Totally Zenned

Join Date:
Sep 2004
Posts:
1,237
Plugin Contributions:
1

Re: Problematic code in advanced search result ?

Could be a smaller typo than you imagine. Try this modification:

SELECT * FROM `tax_rates` tr 
LEFT JOIN `zones_to_geo_zones` gz ON  tr.tax_zone_id = gz.geo_zone_id 
[B]WHERE[/B] (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = 84)
AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 0)

Frank Koehl
"Cleverly Disguised as a Responsible Adult"

frankkoehl.com

14 Jul 2010, 8:01 AM
#3
angeloio avatar

angeloio

New Zenner

Join Date:
Nov 2008
Posts:
9
Plugin Contributions:
0

Re: Problematic code in advanced search result ?

Yes ! That works too !

I just wanted to be as....analytic as I could ...:blush:

Can the members of the development team verify that is indeed a typo and not some hidden logic we fail to comprehend ?

Many thanks BlindSide for looking into this :smile:

14 Jul 2010, 9:20 AM
#4
angeloio avatar

angeloio

New Zenner

Join Date:
Nov 2008
Posts:
9
Plugin Contributions:
0

Re: Problematic code in advanced search result ?

Hmmm...another one....

Just before the problematic (?) SELECT, there is another strange thing...

SUM(tr.tax_rate) AS tax_rate

I have no idea why it sums the tax rate, especially since this returned column (tax_rate) is not used anywhere below :unsure:

Any ideas to close this are most welcomed....

John

15 Jul 2010, 2:19 PM
#5
blindside avatar

blindside

Totally Zenned

Join Date:
Sep 2004
Posts:
1,237
Plugin Contributions:
1

Re: Problematic code in advanced search result ?

You're welcome! I have passed along your thread to the team to make sure they see it, in order to confirm it's a bug.

On your second one, I'd wager a guess that a whoever did that was copy/pasting code from another section, and simply left it alone. In a project of this size, reusing code in this fashion is dogma. Why run the risk of introducing a problem when you borrow something that you know already works?

Frank Koehl
"Cleverly Disguised as a Responsible Adult"

frankkoehl.com

1 Mar 2011, 8:26 PM
#6
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,512
Plugin Contributions:
176

Re: Problematic code in advanced search result ?

Are you still seeing this on v1.3.9?

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

3 Mar 2011, 3:52 PM
#7
wilt avatar

wilt

Oji-san

Join Date:
Jun 2003
Location:
Newcastle UK
Posts:
1,950
Plugin Contributions:
3

Re: Problematic code in advanced search result ?

Also, could you share some more details of your exact tax_rate setup.

From the details you posted you have multiple tax rates, even though you should need only one ?