Hi there!
I'm currently trying to produce a table of charges for staff to use when providing costs to clients (currently use a very large, messy spreadsheet), and I went a-googling all over the place to try and find a way to round up the currency values to the nearest '5' whole units (pounds/dollars/euros) etc. I couldn't find much help (I was hoping for an excel-style ceiling(x, 5) kind of approach!) and found that PHP does not do this, and that MSExcel may be the odd one out because few other applications seem to have such a function.
I then went looking through the CouchCMS documentation, and saw the function "cms:mod" which returns the remainder after dividing a number by another number. So I thought, okay, that can tell us how far away from the next '5' we are. That is to say that <cms:mod 48 '5' /> will return 3, because we are 3 over a multiple of 5. I want to always round up, so I thought '5 minus <cms:mod 48 '5' />' will give 2, which is how much we want to round up by. Therefore 48 plus (5 minus <cms:mod 48 '5' /> will give 50 - the correct result! So I wrote the code
Now - that line of code seems to work. However, the overall code is a bit more complicated and I have got myself very confused by the outputs I am now getting. The costs are entered by the user by selecting a 'cost' currency (the currency we will be charged in) and entering that cost (allows us to keep costs up to date using a 'global' exchange rate which is set on a separate master page), and entering our 'charge' (the markup to add to the cost).
The page 'sets' a variable for each foreign currency exchange rate, based on an exchange rates masterpage, which is set in the top of the template.
In case it helps, the exchange rates currently populated are:
-EUR 1.061686
-USD 1.181700
-CHF 1.162555
The table then outputs the raw info (year, currency, cost, charge) with a little 'number formatting' for the different currencies, and then also displays 3 calculated totals - GBP excl. VAT, GBP incl. VAT and USD. VAT is only added to the charge, not to the cost, therefore in the table row that displays the charge I have slipped in the cms:set function to calculate the plus VAT amount (simply charge*1.2).
I have populated the table with some figures and I just cannot wrap my head around where these figures are coming from. The excl. VAT column seems to work perfectly, but the other two are a mess! For example:
The input for the first row is: Year 5; Currency GBP; 70; 109.
The outputs I get for each column are: £180.00 excl. VAT; £205.80 incl. VAT; $215.52
That first result is exactly what I'd expect, following the logic of:
total= 70+109 == 179
number_format: 179+(5-mod(179, '5')) == 179+(5-4) == 179+1 == 180
For the second result, I was expecting:
chargevat=109*1.2 == 130.8
totalvat = 70+130.8 == 200.8
number_format: 200.8+(5-mod(200.8 '5')) == 200.8+(5-0.8) == 200.8+4.2 == 205
So why has the 0.8 stuck around in the output?? Does mod() not handle decimals?
The third result follows the same problem. I was expecting:
total= 70+109 == 179
totalusd= 179 * usd_exch == 179*1.1817 == 211.5243
number_format: 211.5243+(5-mod(211.5243 '5')) == 211.5243+(5-1.5243) == 211.5243+3.4757 == 215
According to wolframalpha the mod() function ought to work the way I am expecting it to: (https://www.wolframalpha.com/input/?i=x ... 11.5243%29)
Frustratingly wolframalpha also allows ceiling(x, n) the same as MSExcel does: (https://www.wolframalpha.com/input/?i=c ... 43%2C+5%29)
Help?? Is the mod tag a bit broken? Can we fix it? Can we introduce a ceiling function? I feel like I could write some jquery, but given that it would have to go through each and every <td> to find the right ones, and to then calculate the correct amount, and then return it to the DOM, I just feel a bit overwhelmed by the thought of coding that right now.
Thank you (and Merry Christmas KK !!)
Anthony
I'm currently trying to produce a table of charges for staff to use when providing costs to clients (currently use a very large, messy spreadsheet), and I went a-googling all over the place to try and find a way to round up the currency values to the nearest '5' whole units (pounds/dollars/euros) etc. I couldn't find much help (I was hoping for an excel-style ceiling(x, 5) kind of approach!) and found that PHP does not do this, and that MSExcel may be the odd one out because few other applications seem to have such a function.
I then went looking through the CouchCMS documentation, and saw the function "cms:mod" which returns the remainder after dividing a number by another number. So I thought, okay, that can tell us how far away from the next '5' we are. That is to say that <cms:mod 48 '5' /> will return 3, because we are 3 over a multiple of 5. I want to always round up, so I thought '5 minus <cms:mod 48 '5' />' will give 2, which is how much we want to round up by. Therefore 48 plus (5 minus <cms:mod 48 '5' /> will give 50 - the correct result! So I wrote the code
- Code: Select all
<cms:number_format "<cms:add total "<cms:sub '5' "<cms:mod total '5' />" />" />" />
Now - that line of code seems to work. However, the overall code is a bit more complicated and I have got myself very confused by the outputs I am now getting. The costs are entered by the user by selecting a 'cost' currency (the currency we will be charged in) and entering that cost (allows us to keep costs up to date using a 'global' exchange rate which is set on a separate master page), and entering our 'charge' (the markup to add to the cost).
- Code: Select all
<cms:repeatable name='costs_table' label='Add the details of costs and charges for renewal'>
<cms:editable name="year" type='text' label="Enter the year of the renewal" required='1' searchable='0'/>
<cms:editable name="cost_currency" label="Select the Currency for the Official Fee (or Agent's Cost)" opt_values='GBP=£ | EUR=€ | CHF=CHF | USD=$' type='dropdown' searchable='0' />
<cms:editable name="cost" type='text' label="Enter the Official Fee or Agent's Cost Amount" required='1' validator='non_negative_decimal' searchable='0'/>
<cms:editable name="charge" type='text' label="Enter our GBP Service Charge Amount" required='1' validator='non_negative_decimal' searchable='0'/>
</cms:repeatable>
The page 'sets' a variable for each foreign currency exchange rate, based on an exchange rates masterpage, which is set in the top of the template.
- Code: Select all
<cms:pages masterpage='exchange-rates.php' >
<cms:set eur_exch=eur_exchange 'global' />
<cms:set chf_exch=chf_exchange 'global' />
<cms:set usd_exch=usd_exchange 'global' />
</cms:pages>
In case it helps, the exchange rates currently populated are:
-EUR 1.061686
-USD 1.181700
-CHF 1.162555
The table then outputs the raw info (year, currency, cost, charge) with a little 'number formatting' for the different currencies, and then also displays 3 calculated totals - GBP excl. VAT, GBP incl. VAT and USD. VAT is only added to the charge, not to the cost, therefore in the table row that displays the charge I have slipped in the cms:set function to calculate the plus VAT amount (simply charge*1.2).
- Code: Select all
<cms:show_repeatable 'costs_table' >
<tr>
<th style="text-align:center;" scope="row"><cms:show year /></th>
<td class="currency"><cms:show cost_currency /></td>
<td class="fee">
<cms:if cost_currency = '£'><cms:number_format cost />
<cms:set gbpcost=cost 'local'/>
<cms:else />
<cms:if cost_currency = '€'><cms:number_format cost decimal_character=',' thousands_separator='.' />
<cms:set gbpcost="<cms:mul cost eur_exch />" 'local' />
<cms:else />
<cms:if cost_currency = 'CHF'><cms:number_format cost decimal_character=',' thousands_separator='.' />
<cms:set gbpcost="<cms:mul cost chf_exch />" 'local' />
<cms:else />
<cms:if cost_currency = '$'><cms:number_format cost />'
<cms:set gbpcost="<cms:mul cost usd_exch />" 'local' />
</cms:if>
</cms:if>
</cms:if>
</cms:if>
</td>
<td class="currency">£</td>
<td class="charge"><cms:number_format charge /><cms:set chargevat="<cms:mul charge '1.2' />" 'local' /></td>
<td class="currency">£</td>
<td class="cost"><cms:set total="<cms:add gbpcost charge />" /><cms:number_format "<cms:add total "<cms:sub '5' "<cms:mod total '5' />" />" />" /></td> <!-- total excl. VAT -->
<td class="currency">£</td>
<td class="cost"><cms:set totalvat="<cms:add gbpcost chargevat />" /><cms:number_format "<cms:add totalvat "<cms:sub '5' "<cms:mod totalvat '5' />" />" />" /></td> <!-- total incl. VAT -->
<td class="currency">$</td>
<td class="cost"><cms:set totalusd="<cms:mul total usd_exch />" /><cms:number_format "<cms:add totalusd "<cms:sub '5' "<cms:mod totalusd '5' />" />" />" /></td> <!-- total USD excl. VAT -->
</tr>
</cms:show_repeatable>
I have populated the table with some figures and I just cannot wrap my head around where these figures are coming from. The excl. VAT column seems to work perfectly, but the other two are a mess! For example:
The input for the first row is: Year 5; Currency GBP; 70; 109.
The outputs I get for each column are: £180.00 excl. VAT; £205.80 incl. VAT; $215.52
That first result is exactly what I'd expect, following the logic of:
total= 70+109 == 179
number_format: 179+(5-mod(179, '5')) == 179+(5-4) == 179+1 == 180
For the second result, I was expecting:
chargevat=109*1.2 == 130.8
totalvat = 70+130.8 == 200.8
number_format: 200.8+(5-mod(200.8 '5')) == 200.8+(5-0.8) == 200.8+4.2 == 205
So why has the 0.8 stuck around in the output?? Does mod() not handle decimals?
The third result follows the same problem. I was expecting:
total= 70+109 == 179
totalusd= 179 * usd_exch == 179*1.1817 == 211.5243
number_format: 211.5243+(5-mod(211.5243 '5')) == 211.5243+(5-1.5243) == 211.5243+3.4757 == 215
According to wolframalpha the mod() function ought to work the way I am expecting it to: (https://www.wolframalpha.com/input/?i=x ... 11.5243%29)
Frustratingly wolframalpha also allows ceiling(x, n) the same as MSExcel does: (https://www.wolframalpha.com/input/?i=c ... 43%2C+5%29)
Help?? Is the mod tag a bit broken? Can we fix it? Can we introduce a ceiling function? I feel like I could write some jquery, but given that it would have to go through each and every <td> to find the right ones, and to then calculate the correct amount, and then return it to the DOM, I just feel a bit overwhelmed by the thought of coding that right now.
Thank you (and Merry Christmas KK !!)
Anthony