Email Marketer: send price with VAT included

[jasurdeury]

Hello everyone,

 

i'm currently using the Email Marketer feature to send different reminders before the expiring date of different services.

I use the merge tag

 

{$service_recurring_amount}

 

to show the price of the service, but it won't add the VAT value in the email. So for example if the service costs 200 Euros, it should show 244€ (200 Euros + 22% VAT).

 

Do you know a way to achieve this?

 

Thank you very much!

[brian!]

I believe {$service_recurring_amount} just pulls the "Recurring Amount" value for the product page of the client's profile, so if it says 200€ in the profile, it will use 200€ in the email template.

 

I can't think of a way to fully add the VAT to the amount without using action hooks and even then you might have to query a few database tables to get all the information you need.

 

if you need to specify the appropriate VAT rate for the client's country, and don't want to use a hook to get that rate, then perhaps you could use a series of {if} statements to display the correct VAT rate - though that will depend on what variables you have access to in the particular template you're using, e.g if you have {$client.country}, perhaps you could use...

 

{$service_recurring_amount} {if $client.country eq "Italy"}(+22% VAT){elseif $client.country eq "Spain"}(+21% VAT){/if}

though that's an inelegant (ugly!) solution and would require updating with each tax rate change - but that won't happen too often... for those countries where you don't charge VAT, the SRA will be correct.

 

personally, i'd just add a message saying that VAT will be added if it is applicable to your country.

 

I would hope that the features of Email Marketer will be improved in v6 - at the very least, it should handle domains as an option.

[jasurdeury]

First, thank you for your answer!

 

Second: that's a possible solution but it would still show the price and the VAT separated (example: 200€ + 22% VAT).

When creating a new product for a client, it will give the correct price + VAT. Isn't there a way to retrieve that value?

 

Or, another, isn't there a way to insert a tag to make it calculate the VAT via some magical PHP code?

[brian!]

First, thank you for your answer!

Second: that's a possible solution but it would still show the price and the VAT separated (example: 200€ + 22% VAT).

if SRA was an integer, rather than a string, you could do some of the calculations using Smarty in the email template if you had to - though that's by far not the best way to go about it... and I doubt you would have access to all the information required without querying the database with an action hook anyway.

 

for example, if you stripped the currency prefixes/suffixes from SRA, you should be left with an integer that you could use for calculations.

 

{assign var="SRA" value=$service_recurring_amount|replace:'€':''}
{$SRA}
{math equation="x * y" x=$SRA y=1.22 format="%.2f"}

the first line creates a new variable {$SRA} which strips the € currency symbol from {$service_recurring_amount} - the value of {$SRA} should be an integer (if all currency suffixes/prefixes have been removed); NB: if you use multiple currencies, you'll need to use multiple replaces.

 

the second line outputs {$SRA} in the email.

 

the third line performs a multiplication of {$SRA} with a VAT Rate (in this case 22%) and outputs the result.

 

this is just a quick example of how it could be done - it would need to be polished before use!

 

When creating a new product for a client, it will give the correct price + VAT. Isn't there a way to retrieve that value?

but the initial amount might not be the same as the recurring amount - the purchase price may have been discounted... also, VAT rates may have changed between the initial order and when the recurring price kicks in.

 

not to mention the potential added complication of the VAT rate being different on the day you send this reminder, than on the later date when the invoice is automatically generated.

 

Or, another, isn't there a way to insert a tag to make it calculate the VAT via some magical PHP code?

yes an action hook - that could contain your magical code, and then assign it to a email mergefield tag for use in the email templates... but it's not necessarily a simple calculation!

 

e.g you might have to query the database to find out if the product/service is tax enabled; query to find out if the client is tax-exempt; get the VAT rate for the client's country; get the client's currency settings; perform the calculation; format the result and assign it to a mergefield... there might be further steps that haven't even occurred to me!

 

the problem with the Smarty in-template calculation method is that you won't automatically know the above conditions.

 

however, if the statements below are all true, then the Smarty solution might work for you.

 

• all your products are tax enabled.
  
• none of your clients are tax exempt.
  
• all clients use the same currency.
  

if so, you'd just need to add a series of {if} statements to assign values (to y) for VAT Rates for EU countries - with those outside EU set to 1 (the final {else} statement).

[jasurdeury]

{assign var="SRA" value=$service_recurring_amount|replace:'€':''}
{$SRA}
{math equation="x * y" x=$SRA y=1.22 format="%.2f"}  

 

Sorry i'm not a pro with PHP (i'm a complete noob actually), but this string

{assign var="SRA" value=$service_recurring_amount|replace:'€':''}

replaces the suffix, but not the prefix, isn't that right?

 

Also the SRA var, can be used with something like

$SRA *= 1.22;

?

 

Maybe it's easier like that?

[brian!]

Sorry i'm not a pro with PHP (i'm a complete noob actually), but this string

{assign var="SRA" value=$service_recurring_amount|replace:'€':''}

replaces the suffix, but not the prefix, isn't that right?

No - what it will do is replace the string in the first single quotes, with the string in the second single quotes anywhere in the $service_recurring_amount variable and assign the result to a new variable - doesn't matter if it's prefix, suffix or both... in other words, it will replace all instances of € with an empty string (effectively deleting it).

 

Maybe it's easier like that?

it's easier, but it won't work!

 

you can't use PHP in the email templates, you need to use Smarty - http://docs.whmcs.com/Template_Syntax

 

in theory, you could use {$SRA*1.22} in the email template and that would likely give you an answer, but it wouldn't be set to 2 decimal places (which my code is) - though you could easily tweak it to be 2 dp.

 

much more importantly, it won't work for multiplying variables, e.g {$SRA*$vatrate} will just output {$SRA*$vatrate}.

 

you can multiply variables with Smarty v3 (rumoured to be introduced in WHMCS v6), but WHMCS currently uses Smarty v2 and to multiply variables with that, you will need to use {math}.

 

just thinking off the top of my head (untried but should work), one way to do this might be....

 

{if $client.country eq "IT"}{assign var="vatrate" value=1.22}{elseif $client.country eq "GB"}{assign var="vatrate" value=1.20}{else}{assign var="vatrate" value=1.00}{/if}
{assign var="SRA" value=$service_recurring_amount|replace:'€':''}
{math equation="x * y" x=$SRA y=$vatrate format="%.2f"}€

that's one possible way to assign a tax rate to each country and use it in a formula - you could assign the math formula to a variable and output it anywhere you wish on the template.

[jasurdeury]

Great, i wll try this.

The $service_recurring_amount variable outputs like this: €500,00 EUR

So there's the € as prefix, and EUR as suffix. How can i get rid of both, to make the calculations with the {math} tag?

[brian!]

Great, i wll try this.

The $service_recurring_amount variable outputs like this: €500,00 EUR

So there's the € as prefix, and EUR as suffix. How can i get rid of both, to make the calculations with the {math} tag?

you will need to use multiple replaces...

 

{assign var="SRA" value=$service_recurring_amount|replace:'€':''|replace:' EUR':''}
{math var="recuramount" equation="x * y" x=$SRA y=$vatrate}
€{$recuramount|number_format:2:",":"."} EUR

i've tweaked the code to assign the result to a variable {$recuramount} and formatted the number with a comma replacing the decimal point... untried, but should work.

[jasurdeury]

Hello, i have currently set my email template like this:

 

{assign var="SRA" value=$service_recurring_amount|replace:'€':''|replace:' EUR':''}
{assign var="vatrate" value=1.22}
{math var="recuramount" equation="x * y" x=$SRA y=$vatrate}
€{$recuramount|number_format:2:",":"."} EUR

 

The {$service_recurring_amount} value is €1,00 EUR .

The problem is that the result, in the email, says €0,00 EUR

 

It should be €1,22 EUR (since "x * y" is "1 * 1.22" ), right? Do you think there is a problem here somewhere? Unfortunately i can't seem to find it.

[brian!]

Hi,

 

It should be €1,22 EUR (since "x * y" is "1 * 1.22" ), right? Do you think there is a problem here somewhere? Unfortunately i can't seem to find it.

interesting... perhaps it sees the comma and therefore still considers it to be a string and not an integer... one way to test this, try the following line in your template...

 

{assign var="SRA" value=$service_recurring_amount|replace:'€':''|replace:' EUR':''|replace:',':'.'}

that will replace the comma with a decimal and should allow calculations to be made on the value... it could cause a problem if you use a decimal as a thousand separator (e.g 1.234,56)... you might need to replace the decimal with nothing, but try it on your above example first and see if replacing the comma works - if so, we can deal with the decimal point later if we have to.