Updating UK VAT to 17.5% at 12.01am on Jan 1st

[Dunk]

Hi guys,

I have a couple of questions relating to the UK VAT rate increase on January 1st 2010 (increasing from 15% to 17.5%).

Firstly, we want to retain the same VAT inclusive prices, so need to drop our ex-VAT prices prior to upping the VAT rate. I found some code that I believe will do this...

update products set products_price = products_price*0.978723404255, products_price_sorter = products_price_sorter*0.978723404255

Do I need to enter this code into the box at Admin > Tools > Install SQL Patches? ....after backing up of course

Second question, can this process AND the increase of the VAT rate both be automated so it's implemented automatically at 12:01am on January 1st 2010. (i.e. so we don't have to ask someone to sit in and do it for for us!)

I wonder which bright spark in the British government though it'd be a good idea to increase our VAT rate at 12:01am on New Years whilst everyone's out partying!

Thanks!

[Absolute]

It depends on your server type, and how much access you have to control panels etc.

Our servers are all setup with CRON jobs to run a simple .sql batch file which contains a little update script. The code you have can either be put into the admin SQL updates page, or you can add it to a batch process script, and then run that at exactly midnight on the first. If you choose the second option, make sure that you rename the products table to include any table prefix. The SQL patch installer does this for you automatically.

As all servers are slightly different, a quick google for your server software version and MySQL batch file from command line should do the trick.

Absolute

[Dunk]

Hi Absolute,

Thanks very much for the info although I'm still a little confused. I should've given this job to our programmer a long long time ago! I've given him an email but I'm not expecting a reply as he's in Canada!

We use cPanel and Apache/2.0.63. We can create cron jobs but if I'm honest I know very little about them (although more than I did about 20 minutes ago) but we do have a dev site so if I can figure out what to do I can test it on there.

Can you give me any pointers? I have the code for dropping the ex-VAT rate but I don't know what the code is for increasing the VAT rate to 17.5%. I also don't know how to create a batch process script or how to rename table prefixes!

Any advice would be greatly received. I'm not sure if you offer your services on here but I'd happily pay to get this sorted.

Duncan

[cjhearn]

Hi Dunk,
Using the code you've got in your previous post will adjust the prices of all products in your table - you'll also need to add something like

WHERE products_tax_class_id = 1

to ensure only taxable products are modified (changing the tax class id to whatever tax class your products are in).

Don't forget if you've also got specials set up you'll need to update their base prices in products_specials and those for any product attributes that modify product pricing in products_attributes.

As for changing the tax rate, this depends on the tax zones you have set up. I would think you'd just have one tax rate currently set at 15% so something like

update tax_rates set tax_rate = 17.5000 where tax_rate = 15.0000;

would do the job. The tax rate description could also be updated here at the same time.

For me, personally, it's highly unlikely that our shop will generate a sale overnight on 31st Dec/1st Jan so I'm planning on working out the SQL statements I need to run in advance then copy-paste them in to the admin SQL patches probably first thing Friday morning (or whatever time I awake ). Likewise it *could* be done as late as you can Thursday night.

What I have so far is:

UPDATE products SET products_price = products_price*0.978723404255, products_price_sorter = products_price_sorter*0.978723404255 WHERE products_tax_class_id = 1;
UPDATE products_attributes SET options_values_price = options_values_price*0.978723404255;
UPDATE specials SET specials_new_products_price = specials_new_products_price*0.978723404255;
UPDATE tax_rates SET tax_rate = 17.5000,tax_description = "VAT included @ 17.5%" WHERE tax_rate = 15.0000;

Hope this helps in some way
Chris

[peanut77]

Hi Chris

I think it was your quick thinking and sql query that I ran the last time (and which saved a few sleepless price updating nights)

Just a really stupid question. But if I amend the vat rate just via the localisation panel from 15% to 17.5% will this not automatically update the vat for folk that don't need the showable inc vat prices to remain the same or do I need to run the query you have once again really kindly provided.

Just to check this is the correct query for folk who want the vat inc prices to remain the same :-

UPDATE products SET products_price = products_price*0.978723404255, products_price_sorter = products_price_sorter*0.978723404255 WHERE products_tax_class_id = 1;
UPDATE products_attributes SET options_values_price = options_values_price*0.978723404255;
UPDATE specials SET specials_new_products_price = specials_new_products_price*0.978723404255;
UPDATE tax_rates SET tax_rate = 17.5000,tax_description = "VAT included @ 17.5%" WHERE tax_rate = 15.0000;

and do I amend the vat rate in localisation after I have ran this query or before (Last time I think I ran this half way through - i.e I ran once query amending the price, then I adjusted the vat rat and then ran another)


Thanks again

[Absolute]

@peanut77 - your final line in the SQL statement you provided should do the same thing as updating teh VAT through the localization panel in the admin. This admin page only alters the tax_rate table.

Absolute

[Dunk]

Thank you! Our programmer did get in touch in the end, unfortunately the cron job (which I set up!) didn't run correctly so we've got quite a few incorrect invoices from earlier on today. All updated now though, thanks for the information!