Dot for thousands, comma for decimals and currency after the number

verzakes

First of all, I'd like to thank all Datatables developers for this splendid tool, it saved me countless hours of programming.
Here in Croatia, there is a different way of showing the price of a product, for example:
1.200,50 kn
which means one thousand kunas and 50 lipas.
So far, I managed to do the following:

Solved sorting with dot for thousands and commas for decimals, but without "kn" after the number (this doesn't work with "kn" after the number), this was set in language settings:
"decimal": ",",
"thousands": "."

Solved sorting with "kn" after number, but with commas for thousands and dot for decimals (this doesn't work with dot for thousands and commas for decimals), with setting custom datatables sort:
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"my-currency-pre": function(a) {
return parseFloat(a.replace(/ /gi, ''));
},
"my-currency-asc": function(a,b) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"my-currency-desc": function(a,b) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
and then in datatable class:
"columnDefs": [
{"sType": "my-currency", "aTargets": [0,1,2,3,4,5,6,7,8,9,10,11,12]}
],

But how do I combine this 2 mutually exclusive features into one feature that allows me to sort the numbers that have dot for thousands, comma for decimals and "kn" after the number?

Thanks for all help in advance.

allan

Hi,

The my-currency plug-in there doesn't take into account what the decimal character has been set as, which is why the two don't just automatically work together.

What you would need to do is modify the plug-in so that it will replace the dot's with nothing and the comma with a dot. Do that in the -pre function, before the parseFloat and it will work nicely.

Regards,
Allan

verzakes

Hi Allan,

Unfortunately, I haven't worked enough with javascript to understand your answer fully. Can you please try explaining it with a bit of code?

Thanks in advance for your time, it's greatly appreciated

rf1234

Hi verzakes,

I had the same problem. The Croatian way of displaying amounts in a currency is exactly the same as the German way. Most countries in Europe do it like this except for this island west of us in the North Sea - and of course Switzerland ... I have two languages on my site for the time being English (UK) and German.

The key difference for sorting is the different date format and the different number format. This is my solution; maybe helpful for you. For date sorting I use moment.js in combination with a data tables plugin. For the (formatted) amount sorting I coded the solution myself (reusing parts of an existing plugin). The advantage for you might be that column detection is fully automated. But you need to know the language the user has chosen. Otherwise it doesn't work. If you have the language you don't need to specify anthying in columnDefs because detection is automatic.

var cur = [  'kn', 'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
             'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];

//auto detection only works if ALL cells of a column comply with the criterion !!!
//fields must contain an ISO currency code or space to be detected as formatted-numbers
//since all table fields with ISO codes always contain a number this works.
//in addition German number fields should be detected as formatted-num regardless of
//whether they contain an ISO code or not
$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
    if (typeof data !== 'undefined') {
        if (data !== null) {
            var i=0;
            while ( cur[i] ) {
                if ( data.search( cur[i] ) > -1 )   {
                    return 'formatted-num';
                }
                i++;
            }
            if ( data === '') {
                return 'formatted-num';
            }                
            if (lang == 'de') {
//we are not using strict mode in moment.js so that 28.12.2017 09:42 is also
//recognized as a date!!
                if ( ! moment(data, 'L').isValid() ) {
                    if ( isNaN(data) ) {
                        data = data.replace( /[\.]/g, "" );
                        data = data.replace( /[\,]/g, "." );
                        if ( ! isNaN(data) ) {
                            return 'formatted-num';
                        }
                    }
                }
            }                    
        }
    }
    return null;
} );

//sorting of formatted numbers in English and German format
$.extend( $.fn.dataTable.ext.type.order, { 
    "formatted-num-pre": function ( a ) {
        if (lang == 'de') {
            a = a.replace( /[\.]/g, "" );
            a = a.replace( /[\,]/g, "." );
        } else {
            a = a.replace( /[\,]/g, "" );
        }
        a = a.replace( /[^\d.-]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
//14 digit negative number to make sure empty cells always stay at the bottom / top
            return -99999999999999;
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );

//should be positioned after the number formatting to make sure
//date columns don't accidentally are assigned formatted-num
//set parms for date sorting using moment.js
$.fn.dataTable.moment( 'L', momentLocale );

I added the Kunas to the currency array already. Good luck!! And this is the link to the data tables plugin for moment.js: https://datatables.net/plug-ins/sorting/datetime-moment