Customer Tax Exempt v2 - Support Thread

[swguy]

Note: It could be that the correct fix for this issue (Sales Report shows sales tax on non-taxable orders) is a modification to Sales Report and not to this plugin.

[swguy]

It seems that when a newer MySQL database is in strict mode, the tax query on there's a bug in the tax query in TaxExemptCustomerObserver.php:258 can cause a fatal error:

[02-Sep-2021 18:35:54 America/New_York] Request URI: /store/index.php?main_page=shopping_cart, IP address: ::1
#1 trigger_error() called at [/Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php:170]
#2 queryFactory->show_error() called at [/Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php:142]
#3 queryFactory->set_error() called at [/Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php:269]
#4 queryFactory->Execute() called at [/Users/scott/Sites/store/includes/classes/observers/TaxExemptCustomerObserver.php:258]
#5 TaxExemptCustomerObserver->getCustomersTaxRatesSummed() called at [/Users/scott/Sites/store/includes/classes/observers/TaxExemptCustomerObserver.php:74]
#6 TaxExemptCustomerObserver->update() called at [/Users/scott/Sites/store/includes/classes/class.base.php:118]
#7 base->notify() called at [/Users/scott/Sites/store/includes/functions/functions_taxes.php:21]
#8 zen_get_tax_rate() called at [/Users/scott/Sites/store/includes/classes/shopping_cart.php:627]
#9 shoppingCart->calculate() called at [/Users/scott/Sites/store/includes/classes/shopping_cart.php:1385]
#10 shoppingCart->show_weight() called at [/Users/scott/Sites/store/includes/modules/pages/shopping_cart/header_php.php:30]
#11 require(/Users/scott/Sites/store/includes/modules/pages/shopping_cart/header_php.php) called at [/Users/scott/Sites/store/index.php:35]
--> PHP Fatal error: 1055:Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'store.tr.tax_description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by :: SELECT SUM(tax_rate) AS tax_rate_summed, tax_description, tax_priority
FROM tax_rates tr
LEFT JOIN zones_to_geo_zones za
ON tr.tax_zone_id = za.geo_zone_id
LEFT JOIN geo_zones tz
ON tz.geo_zone_id = tr.tax_zone_id
WHERE tr.tax_class_id = 1
AND tr.tax_description NOT IN ('California Sales Tax')
AND (za.zone_country_id IS NULL OR za.zone_country_id = 0 OR za.zone_country_id = 223)
AND (za.zone_id IS NULL OR za.zone_id = 0 OR za.zone_id = 12)
GROUP BY tr.tax_priority
ORDER BY tr.tax_priority ==> (as called by) /Users/scott/Sites/store/includes/classes/observers/TaxExemptCustomerObserver.php on line 258 <== in /Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php on line 170.

A temporary workaround for this issue is to change the mysql_mode but it would be good if we could refactor this query.

[lat9]

It seems that when a newer MySQL database is in strict mode, the tax query on there's a bug in the tax query in TaxExemptCustomerObserver.php:258 can cause a fatal error:

[02-Sep-2021 18:35:54 America/New_York] Request URI: /store/index.php?main_page=shopping_cart, IP address: ::1
#1 trigger_error() called at [/Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php:170]
#2 queryFactory->show_error() called at [/Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php:142]
#3 queryFactory->set_error() called at [/Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php:269]
#4 queryFactory->Execute() called at [/Users/scott/Sites/store/includes/classes/observers/TaxExemptCustomerObserver.php:258]
#5 TaxExemptCustomerObserver->getCustomersTaxRatesSummed() called at [/Users/scott/Sites/store/includes/classes/observers/TaxExemptCustomerObserver.php:74]
#6 TaxExemptCustomerObserver->update() called at [/Users/scott/Sites/store/includes/classes/class.base.php:118]
#7 base->notify() called at [/Users/scott/Sites/store/includes/functions/functions_taxes.php:21]
#8 zen_get_tax_rate() called at [/Users/scott/Sites/store/includes/classes/shopping_cart.php:627]
#9 shoppingCart->calculate() called at [/Users/scott/Sites/store/includes/classes/shopping_cart.php:1385]
#10 shoppingCart->show_weight() called at [/Users/scott/Sites/store/includes/modules/pages/shopping_cart/header_php.php:30]
#11 require(/Users/scott/Sites/store/includes/modules/pages/shopping_cart/header_php.php) called at [/Users/scott/Sites/store/index.php:35]
--> PHP Fatal error: 1055:Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'store.tr.tax_description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by :: SELECT SUM(tax_rate) AS tax_rate_summed, tax_description, tax_priority
FROM tax_rates tr
LEFT JOIN zones_to_geo_zones za
ON tr.tax_zone_id = za.geo_zone_id
LEFT JOIN geo_zones tz
ON tz.geo_zone_id = tr.tax_zone_id
WHERE tr.tax_class_id = 1
AND tr.tax_description NOT IN ('California Sales Tax')
AND (za.zone_country_id IS NULL OR za.zone_country_id = 0 OR za.zone_country_id = 223)
AND (za.zone_id IS NULL OR za.zone_id = 0 OR za.zone_id = 12)
GROUP BY tr.tax_priority
ORDER BY tr.tax_priority ==> (as called by) /Users/scott/Sites/store/includes/classes/observers/TaxExemptCustomerObserver.php on line 258 <== in /Users/scott/Sites/store/includes/classes/db/mysql/query_factory.php on line 170.

A temporary workaround for this issue is to change the mysql_mode but it would be good if we could refactor this query.

Does the following change correct the issue?

    
    protected function getCustomersTaxRatesSummed($tax_class_id, $country_id, $zone_id)
    {
        $tax_class_id = (int)$tax_class_id;
        if ($country_id == -1 && $zone_id == -1) {
            $country_id = $_SESSION['customer_country_id'];
            $zone_id = $_SESSION['customer_zone_id'];
        }
        $country_id = (int)$country_id;
        $zone_id = (int)$zone_id;

        $tax_info = $GLOBALS['db']->Execute(
            "SELECT SUM(tax_rate) AS tax_rate_summed, tax_description, tax_priority
              FROM " . TABLE_TAX_RATES . " tr
                    LEFT JOIN " . TABLE_ZONES_TO_GEO_ZONES . " za 
                        ON tr.tax_zone_id = za.geo_zone_id
                    LEFT JOIN " . TABLE_GEO_ZONES . " tz 
                        ON tz.geo_zone_id = tr.tax_zone_id
              WHERE tr.tax_class_id = $tax_class_id
                AND tr.tax_description NOT IN ({$this->exemptions_list})
                AND (za.zone_country_id IS NULL OR za.zone_country_id = 0 OR za.zone_country_id = $country_id)
                AND (za.zone_id IS NULL OR za.zone_id = 0 OR za.zone_id = $zone_id)
              GROUP BY tr.tax_priority, tr.tax_description
              ORDER BY tr.tax_priority"
        );
        return $tax_info;
    }

[swguy]

This seems like a good fix.

[swguy]

Note that the Customer Tax Exempt field will not show up in Admin > Customers > Customers unless Admin > Configuration > Customer Details > Company is set to true. This is probably not the desired behavior.

[swguy]

Seems like the case NOTIFY_ADMIN_CUSTOMERS_CUSTOMER_EDIT in admin/includes/classes/observers/CustomerTaxExemptAdminObserver.php also needs to return fieldname or you get

--> PHP Warning: Undefined array key "fieldname" in /Users/scott/sites/client/admin/customers.php on line 704.

in newer versions of PHP.

Change

'label' => ENTRY_TAX_EXEMPT,

to

'label' => ENTRY_TAX_EXEMPT,
'fieldname' => 'customers_tax_exempt',

on line 80 of admin/includes/classes/observers/CustomerTaxExemptAdminObserver.php

[lat9]

Note that the Customer Tax Exempt field will not show up in Admin > Customers > Customers unless Admin > Configuration > Customer Details > Company is set to true. This is probably not the desired behavior.

Doesn't a tax-exemption need to be associated with a company?

[lat9]

Seems like the case NOTIFY_ADMIN_CUSTOMERS_CUSTOMER_EDIT in admin/includes/classes/observers/CustomerTaxExemptAdminObserver.php also needs to return fieldname or you get

--> PHP Warning: Undefined array key "fieldname" in /Users/scott/sites/client/admin/customers.php on line 704.

in newer versions of PHP.

Change

'label' => ENTRY_TAX_EXEMPT,

to

'label' => ENTRY_TAX_EXEMPT,
'fieldname' => 'customers_tax_exempt',

on line 80 of admin/includes/classes/observers/CustomerTaxExemptAdminObserver.php

GitHub issue created: https://github.com/lat9/customer_tax_exempt_2/issues/5

[lat9]

v2.0.2 of Customer Tax-Exempt is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2259

This release contains corrections for the following GitHub issues:

#3: Correcting storefront query for MySQL 5.7 and later
#5: Correct undefined-array-key log during admin processing
#6: Correct PHP log when editing a customer with no previous tax-exemptions.

[swguy]

Great stuff! A very valuable addition to Zen Cart.