Qt_script_frmamt

Definition

Language:	Qt Script
Plugin for language:	ScriptingQt
How to use:	Create custom SQL function, set it to "scalar" type. Suggested name: fmtAmt
Function arguments	Keep undefined
Function usage:	SELECT fmtAmt(colName) from tableName
Description:	Formats numbers like "123456789" into "123.456.789,00" or "23434.68" to "23.434,68". When using frmAmt() it is useful to change the character font for database results to a fixed width font such as "Lucida Sans Typewriter" or similar.
Author:	JrgMyr

Code

The code:

var t = arguments[0];
var lg = 17;

if (t > -9E11 && t < 1E12) {
    if (t >= 0.0)
        t += 0.005;
    else
        t -= 0.004;
    t = '                ' + t;
    var n = t.indexOf('.');

    if (n == -1)
        t = t + ',00'
    else {
        if (n == t.length - 1)
            t = t + '0';
        t = t.substr(0, n) + ',' + t.substr(n+1, 2);
    }

    t = t.substr(-lg);
    if (t.substr(lg-7, 1) >= '0')
        t = t.substr(1, lg-7) + '.' + t.substr(lg-6, 6);
    if (t.substr(lg-11, 1) >= '0')
        t = t.substr(1, lg-11) + '.' + t.substr(lg-10, 10);
    if (t.substr(lg-15, 1) >= '0')
        t = t.substr(1, lg-15) + '.' + t.substr(lg-14, 14);
}

return t;

Example usage:

SELECT Company, Year, frmInt(COUNT(*)) AS LineItems, frmAmt(SUM(amount)) AS TotalAmount FROM datatable GROUP BY Company, Year;