Jump to content
MikeS24

Adding second currency in invoices

Recommended Posts

Hi everyone!

To be clear, I am not a programmer, so my knowledge about the programming logic, dependecies and all that is close to 0. I am just figuring out things that are necessary for me one at a time as they come.

As some of you may already know, there are some countries in this world that require local currencies to be displayed on invoices. In my case it is Croatia. I am sweating my abs off trying to figure out some way to add the purchase value in another currency to our invoices. I feel that I've spent way too much time on this and it already becomes painful.


This is what I was able to come up with:
------------------------------------------------------------
<td>{$item.description}{if $item.taxed eq "true"} *{/if}</td>
<td class="text-center">{$item.amount} {if $clientsdetails.currency eq "2"} ({math equation="x * y" x=$item.amount|regex_replace:"/[^.,0-9]+/":"" y=1.82828 format="%.2f"} HRK){/if}</td>
------------------------------------------------------------

The way I see it, there is a problem with including math equations within {if} tags. Without "ifs" the equation works as intended and returns the correct value, but with them the value is empty. Unfortunately, it seems to me that those {if} tags are necessary in this scenario, with multiple currencies available for our clients.

And here comes my question, or rather "cry for help"... Would anyone be willing and able to help me out with rewriting this code or at least point me in the right direction? Of course it would be best to include a "live" currency conversion rate in place of the fixed convestion value, but I wasn't able to find out how to handle this.

I will be very very very grateful for any assistance with this.

Best Regards,
Michael

Share this post


Link to post
Share on other sites

Hi Michael,

On 22/12/2018 at 05:08, MikeS24 said:

I feel that I've spent way too much time on this and it already becomes painful.

that's working with WHMCS for you - get used to the pain! 🙂

On 22/12/2018 at 05:08, MikeS24 said:

The way I see it, there is a problem with including math equations within {if} tags. Without "ifs" the equation works as intended and returns the correct value, but with them the value is empty. Unfortunately, it seems to me that those {if} tags are necessary in this scenario, with multiple currencies available for our clients.

I don't see any issue with the IF statements... if it's not working when using them, then check whether the client meets the conditions used - e.g is their currency really "2" ??

On 22/12/2018 at 05:08, MikeS24 said:

And here comes my question, or rather "cry for help"... Would anyone be willing and able to help me out with rewriting this code or at least point me in the right direction?

as I said, it should work... although it's not how I would have done it... to avoid using regex, I would have done this..

{if $clientsdetails.currency eq "2"} ({math equation="x * y" x=$item.amount->toNumeric() y=1.82828 format="%.2f"} HRK){/if}

if you really wanted to, you could cut that right down and not even using math and multiply the variable directly....

{if $clientsdetails.currency eq "2"} ({(($item.amount->toNumeric())* 1.82828)|string_format:"%.2f"} HRK){/if}

both versions give the same result...

7gV241S.png

On 22/12/2018 at 05:08, MikeS24 said:

Of course it would be best to include a "live" currency conversion rate in place of the fixed conversion value, but I wasn't able to find out how to handle this.

the information is out there...

the above is mainly concentrating on the PDF invoices, but it's the same principle with the HTML invoice... you are going to do one of two things - either you write a ClientAreaPageViewInvoice hook to query the correct database table to get the current exchange rate; calculate your 2nd currency amount by multiplying/dividing 1st currency amount by exchange rate and return the values to the template... or you just query the database for the current exchange rate, return that value as a new variable available to the template and then do your calculations in the template as per your previous Smarty code above.

<?php

# Get Croatian Currency Exchange Rate
# Written by brian!

use Illuminate\Database\Capsule\Manager as Capsule;

function hook_currency_exchange_rates($vars) 
{
	$exchangerate = Capsule::table('tblcurrencies')->where('code','HRK')->get();
	return array("exchangerate" => $exchangerate[0]->rate);
}
add_hook("ClientAreaPageViewInvoice", 1, "hook_currency_exchange_rates");
?>

and then in the invoice template, you should just need...

{if $clientsdetails.currency eq "2"} ({math equation="x * y" x=$item.amount->toNumeric() y=$exchangerate format="%.2f"} HRK){/if}

or...

{if $clientsdetails.currency eq "2"} ({(($item.amount->toNumeric())* $exchangerate)|string_format:"%.2f"} HRK){/if}

the GBP->HRK exchange rate is currently 8.22657, so when we view the invoice again, we get the correct conversion...

2FnPTkq.png

pTnmz3T.png

if you're going to do this in the invoice PDF template, then abandon Smarty and hooks and just think PHP - the above thread should help more with that. thanks.png

  • Thanks 1

Share this post


Link to post
Share on other sites

Hello Brian!

Big thanks to you for helping me out with this. And thank you for your time.
 

When it comes to my original idea with {if $clientsdetails.currency eq "2"} ({math equation="x * y" x=$item.amount|regex_replace:"/[^.,0-9]+/":"" y=1.82828 format="%.2f"} HRK){/if}, unfortuantely the result of this was as below:

5xI007D.jpg

The currency ID definitely wasn't the issue, so I have no idea what the problem was.

But... Thankfully, by using the first one of your suggested edits with $item.amount->toNumeric() instead of regex, it worked!

LgwMfMN.jpg

Darn, I knew it must've been something as simple as that! Thank you!


When it comes to snatching the actual exchange rate, of course this would be a much simpler solution overall, without the need for manual file updates, so I'll definitely try this out. However, I am going to make about 5 different currencies available to choose from for our clients, so I have a feeling that this may be a tidy bit more complicated, because if I understand it correctly, the hook example that you've given me above applies to a single currency, so I imagine that it would be necessary to create multiple hooks for different currencies... or this may be even more complicated than this, since the currency exchange rates between "non-default" currencies aren't actually stored anywhere within database. Or there's always a possibility that I am missing something very obvious, because my brain likes to trick me into overcomplicating simple things.
 

Anyway, sticking to the "manual way" satisfies my needs for the time being, so I'm ok with this method.

I am truly grateful for your assistance with this.

Have a great time and Merry Chrismas/Happy Holidays to you, Brian!

Best Regards,
Michael

Share this post


Link to post
Share on other sites
5 hours ago, MikeS24 said:

However, I am going to make about 5 different currencies available to choose from for our clients, so I have a feeling that this may be a tidy bit more complicated, because if I understand it correctly, the hook example that you've given me above applies to a single currency, so I imagine that it would be necessary to create multiple hooks for different currencies... or this may be even more complicated than this, since the currency exchange rates between "non-default" currencies aren't actually stored anywhere within database. Or there's always a possibility that I am missing something very obvious, because my brain likes to trick me into overcomplicating simple things.

oh if you were going to do this for multiple currencies, what you would do in the one hook is get the current currency and then lookup the exchange rate... now if they are both non-default currencies, then same thing applies but you will need to do a little bit of maths to get the correct figure... the info is there - effectively what you would do is convert it to the default currency and then convert it to the desired currency...

if you don't figure it out, contact me in the new year and i'll walk you through it.

  • Thanks 1

Share this post


Link to post
Share on other sites

Thanks Brian! That's pretty much what I was thinking. Converting the non-default currency into default and then to HRK. I'll run some tests and hopefully I'll come up with something.

Share this post


Link to post
Share on other sites

Hi Michael,

14 hours ago, MikeS24 said:

That's pretty much what I was thinking. Converting the non-default currency into default and then to HRK. I'll run some tests and hopefully I'll come up with something.

I thought it might be helpful if I mentioned two further things for clarification...

firstly, the WHMCS Exchange Rate updates basically work by pulling the daily ECB (European Central Bank) XML feed - that feed is a list of 30+ currencies and gives the daily equivalent value of 1 Euro in those currencies - so it's not loading 30 different currencies and all the exchange rates between each of them, just the Euro exchange rate with each and then it can easily calculate the others from those values.

<Cube><Cube time="2018-12-21"><Cube currency="USD" rate="1.1414"/><Cube currency="JPY" rate="126.98"/><Cube currency="BGN" rate="1.9558"/><Cube currency="CZK" rate="25.855"/><Cube currency="DKK" rate="7.4670"/><Cube currency="GBP" rate="0.90215"/><Cube currency="HUF" rate="321.97"/><Cube currency="PLN" rate="4.2851"/><Cube currency="RON" rate="4.6389"/><Cube currency="SEK" rate="10.2733"/><Cube currency="CHF" rate="1.1312"/><Cube currency="ISK" rate="134.10"/><Cube currency="NOK" rate="9.9428"/><Cube currency="HRK" rate="7.4216"/><Cube currency="RUB" rate="78.2103"/><Cube currency="TRY" rate="6.0546"/><Cube currency="AUD" rate="1.6081"/><Cube currency="BRL" rate="4.4099"/><Cube currency="CAD" rate="1.5459"/><Cube currency="CNY" rate="7.8825"/><Cube currency="HKD" rate="8.9368"/><Cube currency="IDR" rate="16696.34"/><Cube currency="ILS" rate="4.3045"/><Cube currency="INR" rate="80.0400"/><Cube currency="KRW" rate="1282.71"/><Cube currency="MXN" rate="22.7547"/><Cube currency="MYR" rate="4.7608"/><Cube currency="NZD" rate="1.6925"/><Cube currency="PHP" rate="60.546"/><Cube currency="SGD" rate="1.5641"/><Cube currency="THB" rate="37.267"/><Cube currency="ZAR" rate="16.4753"/></Cube></Cube>

as an example, today on the ECB list, 1 EUR = 0.90215 GBP - however, on my dev, my default currency is GBP, so WHMCS does the calculation of 1/0.90215 to give me the GBP->EUR exchange rate of 1.10846

PZQl6oD.png

so if I wanted to calculate the exchange rate between two non-default currencies, in my case that could be USD->HRK with a default currency of GBP, I know that the GBP->USD rate is 1.2652 today, so USD->GBP would therefore be 0.79039 .... GBP->HRK is still 8.22657, so if we multiply one by the other (0.79039*8.22657), we get a USD->HRK exchange rate of 6.50219

I can double-check this by setting USD as my default currency and updating the exchange rates again...

nboBsvs.png

secondly, when it comes to the hook, you basically need to know two things:

  1. what is the client's currency
  2. what is the secondary currency exchange rate that you want to return for the client's currency.

so 1️⃣ can be taken care of by adding the code below to your hook function...

$clientcurrency = $vars['clientsdetails']['currency'];

that gives you a new variable, $clientcurrency, than you can use in your conditions.

i'm assuming for 2️⃣ that it's always going to be HRK - though it wouldn't really matter if it wasn't, it would just make the hook code a little longer...

as an early Christmas present from me, here's a replacement hook that should work for you - you'll have to change the IF statements to match your own currency conditions though... 🎁

<?php

# Get Croatian Currency Exchange Rate
# Written by brian!

use Illuminate\Database\Capsule\Manager as Capsule;

function hook_currency_exchange_rates($vars) 
{
	$clientcurrency = $vars['clientsdetails']['currency'];
	if ($clientcurrency == "1") { // GBP
		$exchangerate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		return array("exchangerate" => $exchangerate);
	}
	elseif ($clientcurrency == "13") { // USD
		$usdrate = Capsule::table('tblcurrencies')->where('code','USD')->value('rate');
		$hrkrate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		$exchangerate = number_format(((1/$usdrate) * $hrkrate), 5);
		return array("exchangerate" => $exchangerate);
	}
	elseif ($clientcurrency == "14") { // EUR
		$eurrate = Capsule::table('tblcurrencies')->where('code','EUR')->value('rate');
		$hrkrate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		$exchangerate =  number_format(((1/$eurrate) * $hrkrate), 5);
		return array("exchangerate" => $exchangerate);
	}
	elseif ($clientcurrency == "15") { // HRK
		// do nothing - it is the secondary currency!
	}
}
add_hook("ClientAreaPageViewInvoice", 1, "hook_currency_exchange_rates");
?>

I should add that if I were writing this for a client, and if the second currency was always going to be HRK, I could cut this hook right down to it's bare bones (e.g remove all but one of these if statements) and it would be half the size... but as you said that you're not a programmer, and because I don't know exactly which currencies you have and which currency would be secondary for each of those currencies, i've tried to keep it simple and documented.

so in my dev, 1=GBP; 13=USD; 14=EUR and 15=HRK, and so what this hook will do is for GBP clients, return the HRK exchange rate (because GBP is the default currency, that's just a basic query to the database); for USD & EUR clients, it will calculate and return their HRK exchange rate; for HRK clients, assuming that Croatian invoice rules means that you only need to show HRK prices for non-HRK currencies, it does nothing... you don't really need it in the code, but I left it in in case you need to do something else for HRK clients.

in the template, you can also change your Smarty code from...

{if $clientsdetails.currency eq "2"} ({math equation="x * y" x=$item.amount->toNumeric() y=$exchangerate format="%.2f"} HRK){/if}

to...

{if $exchangerate} ({math equation="x * y" x=$item.amount->toNumeric() y=$exchangerate format="%.2f"} HRK){/if}

because now the logic of how to react to client currencies is in the hook and it is the hook that decides if and when the $exchangerate variable is returned to the template... so using the above hook as is, if the client used HRK, then $exchange rate would not be returned to the template and that Smarty if statement would be false... if they were USD customers, $exchange rate will be returned with the correct rate and the if statement true.

as I said in the other thread, using the above method, you're going to run into a potential issue with paid invoices using today's exchange rate rather than the rate on the date of purchase - that's fixable in the hook, but could get quite involved coding wise, so I may return to that in the New Year when the batteries are fully recharged... even with unpaid invoices, that's still an issue - but a harder one to fix.

hopefully the hook will give you the basis of a quieter seasonal break than you otherwise would have had... enjoy Christmas/New Year - I know that I intend to! 🎄

  • Thanks 1

Share this post


Link to post
Share on other sites

Hello Brian!

Please forgive me for responding to you just now, but I was really busy with setting all other things up before we go live, and I actually remembered about this little currency thingy just today.

First of all, you are a great guy, though I bet you hear this a lot. I am really thankful for all the time and energy you put into preparing the complete, ready to use code for my needs. Thanks for this wonderful gift!

I still have some more things I need to take care of today, but I will definitely let you know about how it went with implementing the code as soon as I'm done with it.

Once again, Big Thanks and Happy New Year!

Michael

Share this post


Link to post
Share on other sites

Hi again Brian!

The implementation of the code and hook for the viewinvoice.tpl went successfully. I have customized it with the currencies from my page and it works. Thank you for your generous help with this problem of mine.

Unfortunately, my lack of programming knowledge had hit me in the head once again and it really hurts me that I need to come here ask for help with yet another problem...

I've spent many hours already and I'm still trying to figure out how to handle the exchange code implementation in the invoicepdf.tpl. The way you explained it to me (and from I've learned from some of your other posts), the "inside" part of the hook code should be implemented into the PDF template directly and the conversion calculation formula should be placed in fields where it's necessary. I've learned that there must be some slight tweaking done to the code when we transfer it to the PDF template, but after trying couple different methods already I still don't get the correct secondary currency output on PDF invoices.

I feel that I've come really close to the correct format of the transferred code and calculation, but now I am kind of stuck with what I have and I'm not sure how to progress further. Below is the preview of the code the way I've implemented it inside invoicepdf.tpl.


 

$pdf->Ln(10);


###################################
##### currency_exchange_rates #####

$clientcurrency = $vars['clientsdetails']['currency'];
	if ($clientcurrency == "1") {
		$exchangerate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
	}
	elseif ($clientcurrency == "2") {
		$plnrate = Capsule::table('tblcurrencies')->where('code','PLN')->value('rate');
		$hrkrate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		$exchangerate = number_format(((1/$plnrate) * $hrkrate), 5);
	}
	elseif ($clientcurrency == "3") {
		$eurrate = Capsule::table('tblcurrencies')->where('code','EUR')->value('rate');
		$hrkrate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		$exchangerate =  number_format(((1/$eurrate) * $hrkrate), 5);
	}
	elseif ($clientcurrency == "4") {
		$gbprate = Capsule::table('tblcurrencies')->where('code','GBP')->value('rate');
		$hrkrate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		$exchangerate =  number_format(((1/$gbprate) * $hrkrate), 5);
	}
	elseif ($clientcurrency == "5") {
		$jpyrate = Capsule::table('tblcurrencies')->where('code','JPY')->value('rate');
		$hrkrate = Capsule::table('tblcurrencies')->where('code','HRK')->value('rate');
		$exchangerate =  number_format(((1/$jpyrate) * $hrkrate), 5);
	}
	elseif ($clientcurrency == "6") {
	}


##### currency_exchange_rates #####
###################################



foreach ($invoiceitems as $item) {
    $tblhtml .= '
    <tr bgcolor="#fff">
        <td align="left">' . nl2br($item['description']) . '<br /></td>
        <td align="center">' . $item['amount'] . ' ('. number_format($item['amount'] * $exchangerate, 2, '.', '').' HRK)</td>
    </tr>';
}

 

And this is the result it gives me...

ZKGHiMj.jpg

I'm not sure what else I could do with this. I'll be grateful if you could take a look at it in your spare moment and let me know what am I missing or doing wrong.

Best Regards,
Michael

Share this post


Link to post
Share on other sites

Hi Michael,

3 hours ago, MikeS24 said:

I feel that I've come really close to the correct format of the transferred code and calculation, but now I am kind of stuck with what I have and I'm not sure how to progress further.

you did come close - the line that is wrong is...

$clientcurrency = $vars['clientsdetails']['currency'];

$vars is used in the Smarty templates, not in the invoice file - so it should be referenced using...

$clientcurrency = $clientsdetails['currency'];

with that change made, the IF statements should work (assuming capsule is enabled in your template and you'd get an error if it wasn't)...

CcBUD7u.png

  • Thanks 1

Share this post


Link to post
Share on other sites

Thank you Brian.

I can't believe I have come this close to the solution, yet it would still take me eternity to figure this out on my own, haha...

Sadly, it seems that capsule might be indeed disabled in my template, because I am getting an "Oops! Something went wrong" page when trying to download PDF file. I had no luck trying to google how to enable capsule for the template (or it's possible that I simply don't know what to search for...). Is this an easy thing to do or am I totally... out of luck in this scenario?

Share this post


Link to post
Share on other sites

just add the code below to the top of the template (or at least before you try to use capsule in the code) - it's the same as the one from the hook...

use Illuminate\Database\Capsule\Manager as Capsule;
  • Thanks 1

Share this post


Link to post
Share on other sites

Of course, didn't I say I have a talent for overcomplicating things? I had this in mind, but it just seemed too simple of a solution to even consider this.

For some reason, it seems that this field with secondary currency on pdf invoices shows just the currency to currency exchange rate output, and not the actual calculation for of the specific price in the field. From what I see, it's the same way in the image you showed me with your GBP>HRK price output. The result is just the 1 GBP exchange rate.

CcBUD7u.png

 

I don't get why does it work this way, since the calculation formula seems to be formatted correctly and we actually see the correct exchange rate between two currencies being shown - it just doesn't seem to multiply the item price with this exchange rate.

('. number_format($item['amount'] * $exchangerate, 2, '.', '').' HRK)

 

I'm sorry for troubling you with this Brian, I know this might be getting annoying for you already.

Best Regards,
Michael

Share this post


Link to post
Share on other sites

Unbelievable... I've managed to solved it on my own!

It was necessary to get rid of the currency symbol before running the calculations. For some reason I thought the "number_format" thingy would do this, but it seems I was wrong.

Here's how I've done this (just in case anyone else would get stuck with the same problem):

Quote

$tempItemAmountSymbolRemove = preg_replace('/[\$]/', '', $item['amount']); 
$tempItemAmountFloat = floatval($tempItemAmountSymbolRemove);

    $tblhtml .= '
    <tr bgcolor="#fff">
        <td align="left">' . nl2br($item['description']) . '<br /></td>
        <td align="center">' . $item['amount'] . ' ('. number_format($tempItemAmountFloat * $exchangerate, 2, '.', '').' HRK)</td>
    </tr>';

 

Once again, thank you for your help and guidance Brian!

Share this post


Link to post
Share on other sites
On 11/01/2019 at 10:10, MikeS24 said:

I'm sorry for troubling you with this Brian, I know this might be getting annoying for you already.

oh no - I can manipulate Smarty and PHP for as long as necessary.. the day I grow tired of that, i'll go and do something else with my time!

i'd have replied before, but your posts must still be being moderated as there seems to be a delay in them being published and I didn't get any notification of the earlier post.

On 13/01/2019 at 19:42, MikeS24 said:

Unbelievable... I've managed to solved it on my own!

Of course, didn't I say I have a talent for overcomplicating things?

we can all be guilty of overcomplicating things... and I think you may have fallen into your own trap again. aaah.gif

On 13/01/2019 at 19:42, MikeS24 said:

It was necessary to get rid of the currency symbol before running the calculations. For some reason I thought the "number_format" thingy would do this, but it seems I was wrong.

the real problem (and I was an idiot for not noticing originally) is that $item['amount'] is a string - specifically, it's a price formatter string and so you can't really multiply it with a number.

so as per before, you can use toNumeric() to strip the currency prefix/suffix from the value and just return the actual number - that can be used in the calculation without the need for those two additional lines of code.

<td align="center">' . $item['amount'] . ' (' . number_format($item['amount']->toNumeric() * $exchangerate, 2, '.', '') . ' HRK)</td>

Share this post


Link to post
Share on other sites

Hey Brian,

Thank you for one another helpful input!

Yes, my posts are still getting through "purgatory" before they are released - it is a little annoying, but rules are the rules I guess.

I absolutely didn't assume "->toNumeric()" would actually work in this case, as I thought "number_format" is the PHP version of that, and the first one is only available in Smarty templates. Of course... assumptions... Thanks for pointing this out.

I think in a couple of days I will be trying to "save" the secondary currency value as a database entry, to solve the annoying problem with it changing every time the exchange rate gets updated. If I'm not mistaked, I have seen one of your posts where you explain how to do this with a hook. I'll try to figure it out, and hopefully I won't get stuck on my own stupidness again this time, haha...

Have a good time!

Best Regards,
Michael

Share this post


Link to post
Share on other sites
20 hours ago, MikeS24 said:

Yes, my posts are still getting through "purgatory" before they are released - it is a little annoying, but rules are the rules I guess.

I thought that stopped after you had made 5 posts... but ours is not to reason why. 🙄

20 hours ago, MikeS24 said:

I absolutely didn't assume "->toNumeric()" would actually work in this case, as I thought "number_format" is the PHP version of that, and the first one is only available in Smarty templates. Of course... assumptions... Thanks for pointing this out.

number_format is really just about defining the decimal places of the result and its general formatting...

20 hours ago, MikeS24 said:

I think in a couple of days I will be trying to "save" the secondary currency value as a database entry, to solve the annoying problem with it changing every time the exchange rate gets updated. If I'm not mistaken, I have seen one of your posts where you explain how to do this with a hook. I'll try to figure it out, and hopefully I won't get stuck on my own stupidity again this time, haha...

I think I explained roughly what needs to be done, but not necessarily how to do it lol.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

By using this site, you agree to our Terms of Use & Guidelines