Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

#1 08-02-2011 04:11:57

kev
Member
Registered: 03-16-2009
Posts: 399

A Solution to fix the VAT calculation errors in Europacart



In my experience with Europacart these are what I call the 'vat bugs' and how someone familiar with the Europacart app could fix those bugs.  I know this is a high level analysis but whoever wrote the app would know how to fix these bugs pretty quickly if they took the timeout to do it. (please don't be offended by the term bugs because I used to be a software developer myself some 10 years ago or so and we used to refer to things as not working properly as software bugs).

There are two fundamental vat issues as I see it:

Customer Historic Orders - item level vat amounts.  These are required for a HMRC valid vat sales receipt or invoice.

Europacart seems to be displaying historic orders using a calculation for the vat amount of the order summary.

If I go into 'Review Online Orders' - 'View Order' or click 'Click here to view a printable version of this order in a new window.'  the VAT amount seems to be using the current vat amount as entered into the Europacart Tax Settings section of the admin interface.

Vat does change in the UK 20%, 17.5%, 15% etc but Europacart seems to be storing and retreiving VAT amounts as a calculation in the order summary display not as an amount stored against an order in a database table.

There is a taxctotal field in the ecom_orders table which I assume contains the correct VAT amount for the total order, but for valid UK Vat order summaries you have to show the VAT breakdown per order item.

The ecom_orderitems table does not have a field to record the VAT amount for each order item.


Here's my quick solution:
provide a Europacart fix as scripts of code to run instead of a full cart updates because they change core php files that some of us have changed for our own businesses.

1. Add a new field to the ecom_orderitems table to be used to record the VAT amount for each order item at the point of sale (this will provide the correct figures for viewing historic orders even after the UK Vat rate has been changed in Europacart admin).

2. design a script that will look at the 'taxctotal' field in the ecom_orders table (which I assume contains the correct VAT amount for the total order), compare the 'taxctotal' value against the vat-less order total amount in order to calculate what the 'vat rate must have been at the time of order'.  join to the records in the ecom_orderitems table and then update the new 'vat per item field' in the ecom_orderitems table with an amount that is the cost of the item x the value of the 'vat rate must have been at the time of order'.

3. change the scripts responsible for 'View Order' and click 'Click here to view a printable version of this order in a new window.' displays to use values in the database ecom_orders and ecom_orderitems tables to show the correct vat figures for the order total summary and order items summary.       



VAT amounts being distorted by delivery costs.

Notice what happens to the Delivery 'Vat' value and 'Rate' value when I include a VAT FREE product to the checkout calculation totals:

Order Totals
Item Subtotal     £234.52
Delivery Total     £29.24
VAT Total     £49.30
Total     £313.06
VAT Summary
Item     Quantity     Total     VAT     Rate
665984 - 3L 3.5in Diskette Pocket Self-adhesive Without Flap Ref 10214 [Pack 100]     1     £14.84     £0.00     0.00%
921255 - 5 Star Business Paper Prestige Laid Finish Ream-Wrapped 100gsm A4 High White [500 Sheets]     2     £25.12     £5.02     20.00%
805426 - Adroit Executive Languedoc Armchair Back H720mm W550xD530xH480-560mm Black Ref 10488-01     1     £194.56     £38.91     20.00%
Delivery     1     £29.24     £5.37     13.33%

Incorrect Values displayed:
VAT Total     £49.30
Delivery     1     £29.24     £5.37     13.33% - should be £5.85 and 20.00%

Delivery in the UK will always incur vat for vat registered businesses because they will be charged vat by the courier company and can claim it back in their company vat return.  Therefore, a quick fix for the incorrect vat calculations may be achieved by simply not using the cost of delivery in the XHTML Order Summary php scripts vat calculations.  The display for the vat amount for the delivery would be just the cost of delivery i.e. 6.99 x vat rate set in cart admin system.


I hope that Kryptronic looks at this post and realises that fixing the vat bugs does not have to be taxing.  I also notice that other people have issues with vat calculations and may wish to add to this post any suggestion to help Kryptronic to pin point in their code what snippets of code to change in order to finally fix the problems with vat calculations and displays in Europacart 7.

Thanks for reading this post and I hope that this starts the ball rolling towards getting vat sorted out quickly.

Offline

 

#2 08-02-2011 04:41:51

121NetDesign
Member
From: Surrey, UK
Registered: 05-10-2007
Posts: 427

Re: A Solution to fix the VAT calculation errors in Europacart

Where exactly is the file that tells the cart to discount the shipping cost if there is a VAT free product in the cart?


Tommy Lee

Offline

 

#3 08-03-2011 08:52:05

jojo
Member
Registered: 05-02-2011
Posts: 25

Re: A Solution to fix the VAT calculation errors in Europacart

I won't be buying CCP/Europacart until such times as this issue is fixed by Kryptronic.
A shopping cart that cannot calculate VAT correctly has no merit.

Offline

 

#4 08-08-2011 03:26:12

ZipSkins
Member
From: United Kingdom
Registered: 01-15-2006
Posts: 822
Website

Re: A Solution to fix the VAT calculation errors in Europacart

kev wrote:

1. Add a new field to the ecom_orderitems table to be used to record the VAT amount for each order item at the point of sale (this will provide the correct figures for viewing historic orders even after the UK Vat rate has been changed in Europacart admin).

Having looked at the database, I think this would only work for orders placed after any such change to the system is implemented.

As far as I can see, as of now, some manual recalculation will be required for historic orders placed before the latest VAT rate change.

Nigel


| Professional Quality Customisable Skins for your ClickCartPro Powered Site


-----------------------------
Certified Support Partner

Offline

 

#5 08-08-2011 04:37:34

kev
Member
Registered: 03-16-2009
Posts: 399

Re: A Solution to fix the VAT calculation errors in Europacart

Tommy wrote:
"Where exactly is the file that tells the cart to discount the shipping cost if there is a VAT free product in the cart?"

The Order Summary XHTML scripts use global variable values to calculate vat per order item and without knowing what were those variables are getting their data from and what also would be impacted with possible code changes, I have stayed away from messing  the VAT calculation code in case my changes have a negative affect on something else.  You would need a development environment to be able to run the app and step through the code to see what's really going on before hacking with the code.  Alternatively, the person who wrote the app would be best placed to rectify the issues without impacting on other areas of the app.

Nigel wrote:
"Having looked at the database, I think this would only work for orders placed after any such change to the system is implemented.
As far as I can see, as of now, some manual recalculation will be required for historic orders placed before the latest VAT rate change."

It would be possible to write some scripts that would look at the 'taxctotal' field in the ecom_orders table, compare the 'taxctotal' value against the 'vat-less order total amount' in order to calculate what the vat rate must have been at the time 'vatatthetime' of order.  SQL join to the records in the ecom_orderitems table and then update the new 'vat per item' field in the ecom_orderitems table with an amount that is the:
cost of the item multiplied by the value of 'vatatthetime'. 

The above would then populate all historic order item records with the vat charged at the time of order.  Obviously, changes would need to be made to the current SQL order placement code that populates the ecom_orders and ecom_orderitems tables with order data.

If I had the time I would do the above myself but feel that it is Kryptronic's responsibility to provide Europacart with a correctly working vat implementation.

Offline

 

#6 08-08-2011 06:05:58

jojo
Member
Registered: 05-02-2011
Posts: 25

Re: A Solution to fix the VAT calculation errors in Europacart

This is without question Kryptronic's resposibility.

Offline

 

#7 08-08-2011 08:19:00

ZipSkins
Member
From: United Kingdom
Registered: 01-15-2006
Posts: 822
Website

Re: A Solution to fix the VAT calculation errors in Europacart

kev wrote:

It would be possible to write some scripts that would look at the 'taxctotal' field in the ecom_orders table, compare the 'taxctotal' value against the 'vat-less order total amount' in order to calculate what the vat rate must have been at the time 'vatatthetime' of order.  SQL join to the records in the ecom_orderitems table and then update the new 'vat per item' field in the ecom_orderitems table with an amount that is the:
cost of the item multiplied by the value of 'vatatthetime'.

I think this would be OK provided that all of the VAT-able items were at the same rate. I suspect there would be complications if some items were at reduced rate VAT.

Nigel


| Professional Quality Customisable Skins for your ClickCartPro Powered Site


-----------------------------
Certified Support Partner

Offline

 

#8 08-08-2011 09:49:25

121NetDesign
Member
From: Surrey, UK
Registered: 05-10-2007
Posts: 427

Re: A Solution to fix the VAT calculation errors in Europacart

Thanks for your input Nigel. I finally feel we might be getting somewhere.

Personally, I do not know of items that are 'reduced rate', 'mid rate' etc.

I thought items in the UK were either VAT or VAT Exempt.

Do you think you could come up with a step by step guide to fix this guys?

Thanks in Advance


Tommy Lee

Offline

 

#9 08-08-2011 10:34:17

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: A Solution to fix the VAT calculation errors in Europacart

Kryptronic will be releasing an update for ClickCartPro and EuropaCart which contains changes to the VAT system which changes how the VAT Summary is displayed and VAT is calculated and rounded within the system for items which are priced inclusive of VAT.  This update is expected to be released in September 2011.

Please note that in update 7.1.3 (released in January 2011) all VAT rounding and display issues were addressed with the VAT Summary.  The 7.1.3 update contained changes to store the actual VAT percentage for new orders.  It sounds like most users posting here have not applied that update.


Nick Hendler

Offline

 

#10 08-08-2011 14:05:56

121NetDesign
Member
From: Surrey, UK
Registered: 05-10-2007
Posts: 427

Re: A Solution to fix the VAT calculation errors in Europacart

hallelujah. Thanks for updating us Nick.

My faith in Kryptronic is restored and look forward to the update!

Tommy

Last edited by 121NetDesign (08-08-2011 14:07:26)


Tommy Lee

Offline

 

#11 08-08-2011 15:23:57

121NetDesign
Member
From: Surrey, UK
Registered: 05-10-2007
Posts: 427

Re: A Solution to fix the VAT calculation errors in Europacart

webmaster wrote:

Please note that in update 7.1.3 (released in January 2011) all VAT rounding and display issues were addressed with the VAT Summary.  The 7.1.3 update contained changes to store the actual VAT percentage for new orders.  It sounds like most users posting here have not applied that update.

I just ran the update again after reading your post Nick and the Europacart version being 'served' is 7.1.1. Not 7.1.3. Which module are you referring to?

Code:

Step 7 of 13: Core Installation
Thank you. System Component Joomla Connector version 7.1.2 was already installed. The module was not updated because it is currently up to date. Please continue through the multi-step installation process.

Continue clicking the 'Continue' button to complete the Core Installation on this webserver account (or wait for this form to submit itself if you selected the auto-submit option).

Installation Task Status
The progress meter below displays your progress during this multi-task interactive installation process.

Progress: |||||||||||||||||||||||||||||||||||||||||||||||||| [60%]
Installation Task Listing
The following installation task listing shows all modules to be installed during this multi-task interactive installation process as well as the installation status of each module.

Current Task:

System Component JavaScript Library 7.1.0
Pending Tasks:

System Component Mail 7.1.2
System Component Remote 7.1.1
System Component Session 7.1.1
System Component XML 7.1.0
Management Interface Skin 7.1.1
Default Skin 7.1.2
Joomla Skin 7.1.2
Mail Skin 7.0.0
Extension Module: EuropaCart 7.1.1
Completed Tasks:

System Component Base 7.1.3
System Component Application 7.1.3
System Component BackEnd 7.1.1
System Component BackEnd Icons 7.0.0
System Component Cron 7.1.3
System Component Encryption 7.0.0
System Component Database 7.1.2
System Component Database Tools 7.0.0
System Component Display 7.1.1
System Component Error 7.0.0
System Component File 7.0.0
System Component Form Handler 7.1.1
System Component FrontEnd 7.1.2
System Component Installer 7.0.0
System Component Joomla Connector 7.1.2

Last edited by 121NetDesign (08-08-2011 15:27:45)


Tommy Lee

Offline

 

#12 08-08-2011 16:27:35

magwa
Member
Registered: 09-22-2007
Posts: 321

Re: A Solution to fix the VAT calculation errors in Europacart

I too have **Extension Module: EuropaCart 7.1.1**

fyi

There are three rates of VAT in the UK

standard rate - 20 per cent
reduced rate - 5 per cent
zero rate - 0 per cent

Some reduced rate items and services are....

Mobility aids for the elderly.

Smoking cessation products - nicotine patches and gum.

Maternity pads.

Sanitary protection products.

Electricity for domestic and residential use or for non-business use by a charity.

Gas for domestic and residential use or for non-business use by a charity.

Heating oil for domestic and residential use or for non-business use by a charity.

Solid fuel for domestic and residential use or for non-business use by a charity.

Air source heat pumps

Boilers - wood fuelled

Central heating controls

Draught stripping

Energy saving materials in residential or charity premises

Insulation

Micro combined heat and power units

Solar panels

Water and wind turbines

Security goods (linked to the installation of energy-saving materials)

Security goods (linked to the installation of central heating systems)

Heating appliances - grant-funded

Ground source heat pumps    

Hot water system controls    

Micro combined heat and power units    

Wood fuelled boilers     -     Wood fuelled boilers - supply only - are standard-rated

Altering an empty residential building

Converting premises into different living accommodation

Renovating an empty residential building

Carrycots with restraint straps    

Children's car seats, booster seats and booster cushions    

Children's safety seats with bare wheeled framework

Offline

 

#13 08-09-2011 05:16:36

121NetDesign
Member
From: Surrey, UK
Registered: 05-10-2007
Posts: 427

Re: A Solution to fix the VAT calculation errors in Europacart

Wow! Thanks for enlightening me with your extensive reply. I had no idea.

I too have **Extension Module: EuropaCart 7.1.1**

Are you saying your cart works out the VAT correctly?


Tommy Lee

Offline

 

#14 08-09-2011 15:32:17

magwa
Member
Registered: 09-22-2007
Posts: 321

Re: A Solution to fix the VAT calculation errors in Europacart

I currently don't have any VAT free items in mine Tommy, but on adding a few to test I received the same errors as you.

Offline

 

#15 08-10-2011 03:58:50

kev
Member
Registered: 03-16-2009
Posts: 399

Re: A Solution to fix the VAT calculation errors in Europacart

Is it just my cart implementations or has anyone else with a vat registered business or client that is seeing incorrect vat (not percentage display, but pence amount on vat receipt) displayed in historic orders (orders placed before the current vat rate of 20%).

Customer Historic Orders - item level vat amounts.  These are required for a HMRC valid vat sales receipt or invoice.

Europacart seems to be displaying historic orders using a calculation for the vat amount of the order summary and not deriving this information from the original order.

If I go into 'Review Online Orders' - 'View Order' or click 'Click here to view a printable version of this order in a new window.'  the VAT amount seems to be using the current vat amount as entered into the Europacart Tax Settings section of the admin interface.

If anyone wishes to test this just change the vat rate in the admin system to say 30% then login to a test account through the website front-end  choose 'View Order' or click 'Click here to view a printable version of this order in a new window.'  and see what the item level vat amount (not percentage display, but pence amount on vat receipt) is per item - 20% as it should be or 30% due to the recent change in the vat rate in admin?

Last edited by kev (08-10-2011 04:00:28)

Offline

 

#16 08-10-2011 12:54:27

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: A Solution to fix the VAT calculation errors in Europacart

Please continue reading here for Kryptronic's response:

https://forum.kryptronic.com/viewtopic.php?pid=139360


Nick Hendler

Offline

 

#17 08-11-2011 12:09:40

arame08
Member
Registered: 08-11-2011
Posts: 4

Re: A Solution to fix the VAT calculation errors in Europacart

Having looked at the database, I think this would only work for orders placed after any such change to the system is implemented.

As far as I can see, as of now, some manual recalculation will be required for historic orders placed before the latest VAT rate change.

Offline

 

#18 08-13-2011 16:15:26

arame08
Member
Registered: 08-11-2011
Posts: 4

Re: A Solution to fix the VAT calculation errors in Europacart

The Order Summary XHTML scripts use global variable values to calculate vat per order item and without knowing what were those variables are getting their data from and what also would be impacted with possible code changes, I have stayed away from messing  the VAT calculation code in case my changes have a negative affect on something else.  You would need a development environment to be able to run the app and step through the code to see what's really going on before hacking with the code.  Alternatively, the person who wrote the app would be best placed to rectify the issues without impacting on other areas of the app.

Offline

 

Board footer