Problems, need help? Have a tip or advice? Post it here.
9 posts Page 1 of 1
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
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
Merry Christmas to you too Anthony :)

Math makes my mind go numb. Perhaps adding a little <cms:ceiling> tag of our own would make things a little easier?
Please add the following to your 'couch/addons/kfunctions.php' file -
Code: Select all
$FUNCS->register_tag( 'ceiling', 'ceiling_handler', 0, 0 );
function ceiling_handler( $params, $node ){
    global $CTX, $FUNCS;
    if( count($node->children) ) {die("ERROR: Tag \"".$node->name."\" is a self closing tag");}

    extract( $FUNCS->get_named_vars(
       array(
             'number'=>'',
             'significance'=>'1',
            ),
       $params)
    );
    if( !is_numeric($number) || !is_numeric($significance) ) return 'NaN';

    $result = ceil( $number/$significance ) * $significance;
    return $result;
}

And now you can use <cms:ceiling /> in your code e.g. as follows -
Code: Select all
<pre>
// Rounds a given number up to the nearest specified multiple
<cms:ceiling '10'  '3' />  // Result '12' (Rounded up to nearest 3)
<cms:ceiling '36'  '7' />  // Result '42' (Rounded up to nearest 7)
<cms:ceiling '610'  '100' />  // Result '700' (Rounded up to nearest 100)
<cms:ceiling '5.37'  '0.05' />  // Result '5.4' (Rounded up to nearest 0.05)
<cms:ceiling '5.37'  '1' />  // Result '6' (Rounded up to nearest 1)
<cms:ceiling '-5.5'  '1' />  // Result '-5' (Rounded up toward zero)
<cms:ceiling '-5.5'  '-1' /> // Result '-6' (Rounded up away from zero)
</pre>

Does this help? Do let me know.
Woah! KK, thank you so much! That's amazing, and dealt with so quickly! I shall consider this an early Christmas present :lol:

This looks perfect - I will implement it, and if I encounter any issues I will let you know.

I did manage an unpleasant workaround (which made the code even longer / more complicated) which was when setting the initial value (this section of code):
Code: Select all
<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 -->

I added another 'number_format' to the 'total' variables which had decimal_precision of '0', which in effect rounds down the number to the nearest whole. At first I was worried, because I thought this might mean that where we were just a few pence/cents over a '5 (e.g. 195.80) it would get rounded down (to 195) first and then, being divisible by 5, would not get rounded up meaning that in a few situations we would be under-quoting. However, on reflection, I realised that the add 5 minus difference in those situations would be add 5 minus 0, which means it would add 5 - thus, rounding up. Code below:
Code: Select all
<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:number_format "<cms:add gbpcost charge />" decimal_precision='0' />" /><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:number_format "<cms:add gbpcost chargevat />" decimal_precision='0' />" /><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:number_format "<cms:mul total usd_exch />" decimal_precision='0' />" /><cms:number_format "<cms:add totalusd "<cms:sub '5' "<cms:mod totalusd '5' />" />" />" /></td> <!-- total USD excl. VAT -->


It works, but regardless I don't like the mess and therefore much prefer your new ceiling function!

Thank you again!!

Anthony
Hey KK

Two things...

Firstly, thank you so much for the ceiling function!! Each iteration of my table code now looks like this:
Code: Select all
<td class="currency">£</td>
<td class="cost"><cms:set total="<cms:add gbpcost charge />" /><cms:number_format "<cms:ceiling "<cms:get 'total' />" '5' />" /></td> <!-- total excl. VAT -->
<td class="currency">£</td>
<td class="cost"><cms:set totalvat="<cms:add gbpcost chargevat />" /><cms:number_format "<cms:ceiling "<cms:get '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:ceiling "<cms:get 'totalusd' />" '5' />" /></td> <!-- total USD excl. VAT -->

And it works a dream!! I hope this function makes it into future releases of couch - there seem to be a lot of online posts trying to work out how to achieve 'ceiling' functions in various coding languages, so I think there must be a need for it out there!

Secondly, just as a follow up to my concern that the cms:mod tag doesn't work properly, I need to point out that my dirty workaround (of decimal_precision='0') actually did not work at all, and in fact the code I posted resulted in the (even more) bizarre behaviour that any number over 1,000 going in, simply came out as "5". So I ended up with tables where the 'Total' column would read; "495.00", "765.00", "5.00", "5.00", "5.00", "5.00", "5.00", "5.00", and so on. Not sure what was going on there, but I think that the mod tag struggles with decimals and large numbers. Or maybe there was some other bug in my code?

Anyway - thanks again for your help! :D

Anthony
Hi,
I believe my post would be hepful in future. Some time ago I raised a concern to @KK about tag 'number_format'. In short, it does rounds the value, not only formats it. PHP function behind the tag rounds alike -
0.001->0.00
0.002->0.00
0.003->0.00
0.004->0.00
0.005->0.01
0.006->0.01
0.007->0.01
0.008->0.01
0.009->0.01
P.S. Found my old post in Tips and Tricks about the same - viewtopic.php?f=8&t=11423#p30295
Hey trendoman

Merry Christmas to you! Quick thank you from me - your various posts on this forum have been a massive help to me!!

I appreciate that the number_format has some quirks, but having gone back over some code I am now convinced that the <cms:mod 'x' 'y' /> tag is dysfunctional.

I created a test page and threw some numbers through the tag, and it appears that it only handles non-negative whole-numbers correctly. It seems to completely ignore decimals (it does not round them, it just loses them - 12.01, 12.4, 12.73 and 12.99 all get handled the same as 12 - (so, maybe, we could say that it rounds them down?!)) which is not correct.

My understanding is that mod(x,y) (also often written x mod y) should give the 'remainder' of x when dividing x by y into whole numbers. 12/3 = 4 (whole numbers), so mod(12, 3) is 0 (no remainder). 13/3 = 4 (whole numbers) with a remainder of 1, so mod(13,3) is 1.

In my test page I, for example, provided mod(12.4, 3), which should produce 0.4 because "3 goes into 12" 4 whole times, with a remainder of 0.4. With negatives, it seems to fall into the 'logical trap' that many humans would when trying to work out, say mod(-15, 4) must be -3 because, using 4's working downwards, I can do -4, -8, -12 and no further, so -3 is remaining. However, my understanding is that with negatives we have to go 'past' the number to get the mod, so we go to -16, and say that 1 is remaining (because we are 1 past the answer or, since 'past' is a 'negative', we are -1 before the answer i.e. one remaining)

I think we can re-write mod(x y) as x-(floor(x/y)*y)
That is to say floor(x/y) finds the 'whole' number of times y goes into x

This seems to work for negatives and decimals. Compare these:

https://www.wolframalpha.com/input/?i=mod%28-17%2C+3%29
https://www.wolframalpha.com/input/?i=x ... 7%2C+y%3D3

and these

https://www.wolframalpha.com/input/?i=m ... .4%2C+3%29
https://www.wolframalpha.com/input/?i=x ... 4%2C+y%3D3

Not sure if that helps?! Again, I am no mathematician, so my explanation may be all kinds of wrong - but using x-(floor(x/y)*y) seems to work consistently, and the current mod() tag is definitely not providing the correct answer when using decimals or negatives.

Thanks

Anthony
Couch internally relies on PHP's Integer modulus (%). PHP's doc quote -
Operands of modulo are converted to integers (by stripping the decimal part) before processing.


I believe therefore Couch's documentation is slightly misleading -
The mod tag accepts two parameters and returns as its output the remainder obtained after dividing the first parameter by the second.


So, I think it is best to follow the same route (as I did with <cms:number_format>) and create a modification of <cms:mod> tag that works correctly for floats as well.
Interesting. Thank you trendoman.

As you say, the PHP documentation for Arithmetic Operators lists % as the operator for Modulo, but then PHP documentation for Math Functions lists the function fmod() for returning the floating point remainder (modulo) of the division of the arguments. fmod() will handle decimal values, % will not. Neither will handle negative numbers (mathematically) correctly.

Reading through discussions on these two approaches suggests that neither is perfect, but that if there is a chance that large numbers will be passed through, then fmod() is better because % uses integer wrap? Apparently "echo 3333333333 % 3" will output -1 instead of 0 due to the overflow?!

Either way, it appears that 'computing modulo' is subtly different to 'mathematical modulo', which explains some of the confusion I had when googling various sources. Apparently in mathematical modulo the result is always a positive number and apparently modulo should only be expected to handle positive numbers, since it works on the basis of 'wraparound' numbers. A nice explanation I found used degrees in a circle, which can be thought of as being modulo 360. 1 degree is the same as 361 degree, because the 360 'wraps around' and becomes 1 again. -1 degree doesn't really exist - it's just 359 degrees. So mod(365, 360) = 5, which tells us that in a modulo 360 system 365 and 5 are the same thing. Apparently we use this all the time in real life without realising. Feet and Inches is a modulo 12 system, and our analog clock faces are modulo 12.

Can you tell I've fallen down a wiki- rabbit hole now?

I'm going to go and have a lie down in a darkened room. :lol:

Thanks for the help - now that I can use the nice 'ceiling' function that KK made I can stop worrying about modular maths...
9 posts Page 1 of 1