Wiki source for MultiLanguageNumbers


Show raw source

====Multi Language Number Formatting====

This work was completed in November 2011 and the first stable release containing this code was 4.06.2

===The Problem===
Some locales use different characters for the thousands separator and the decimal point. e.g. Europe tends to use a comma "," as a decimal point instead of the English full stop "." the French use a space rather than a comma for the thousands separator. e.g.

2,345.67

would be written

2 345,67

For people used to seeing and writing numbers in these alternative formats webERP is next to useless especially in the accounting area.

===The Solution===
In the includes/LanguageSetup.php script we have been using:

%%(php)
$Locale = setlocale (LC_NUMERIC, 'en_US');
%%

Initially I thought that for numbers to be recognised as numbers and for calculations to work with numbers formatted in the users locale with appropriate thousands separators and decimal points we must use either:
%%(php)
$Locale = setlocale (LC_NUMERIC, $_SESSION['Language']);
%%

OR

%%(php)
$Locale = setlocale (LC_ALL, $_SESSION['Language']);
%%

LC_ALL sets all settings for the locale to the user selected locale. However, it does not set LC_MESSAGES to the locale for some reason which I don't understand.
To use the locale to determine the format of numbers and return numbers with appropriate locale character separators and decimal points we would need to use:

%%(php)
$Locale = setlocale (LC_ALL, $_SESSION['Language']);
$LocaleInfo = localeconv();
if (defined('LC_MESSAGES')){
$Locale = setlocale (LC_MESSAGES, $_SESSION['Language']);
}
%%

The reason the locale for LC_MESSAGES is only set when it is defined is that it returns a message on some systems that are not compiled with libintl (in particular Windows)

However, some may wish to use translations but not actually have the locale required on the server. We only used to set the locale when the system was using the gettext for translations.

For maximum compatibility we need a solution which does not require the locale to be installed on the server or require the locale to be set..


===What If the Locale is Not Installed on the Web-Server===
We can actually do transalations without relying on having the necessary locale installed on the web-server using the clever php-gettext. Without the locale installed on the web-server then of course the characters for decimal_point and thousands_sep cannot be determined by reference to the locale_conv() array. So to enable a comprehensive solution that allows numbers to be formatted appropriately irrespective of the installed locales I have also extended the LanguagesArray in includes/LanguagesArray.php to have elements for Thousands_Separator and Decimal_Point. Rather than rely on the values returned from localeconv for thousands separator and decimal point we are using our own hard coded characters from the LanguagesArray.

Someone who knows the appropriate characters for each language will have to tell me - as I have guessed them at this stage.

Changing the LC_NUMERIC locale actually causes complications for the code as any calculations made returns values formatted in the locale and SQL can only use numbers formatted with no thousands separator and a period as a decimal point.

Originally I was thinking we should convert all these calculations back to the SQL format, but this is silly in my view best to adopt LC_NUMERIC locale that SQL likes (en_US - or windows english-us)and just filter input coming back from the user and display in the format the user likes.

===number_format function===
I had thought that the number_format function would be smart enough to display numbers in the user's locale correctly. However, it appears not. Since we now set the $DecimalPoint character and $ThousandsSeparator in the includes/LanguageSetup.php script we need to refer to these in the new function to format numbers as per the user's locale.

A new function locale_number_format uses the additional parameters to the number_format function to return numbers formatted appropriately for the locale. This is in the file includes/MiscFunctions.php

%%(php)

function locale_number_format($Number, $DecimalPlaces) {
global $DecimalPoint;
global $ThousandsSeparator;
return number_format($Number,$DecimalPlaces,$DecimalPoint,$ThousandsSeparator;
}

/* and to parse the input of the user into useable number */

function filter_number_format($Number) {
global $DecimalPoint;
global $ThousandsSeparator;
return str_replace($DecimalPoint,'.',str_replace($ThousandsSeparator,'',$Number));
}
%%

PHP actually does have a function already that does a similar thing for money formats called money_format - the syntax of this function is a bit weird though. The other stumbling block with this function that Tim noted was that it doesn't actually work under Windows!

Tim pointed out that some locales use a different characters again for formatting currency numbers as opposed to quantity numbers. However, due to the complexity this results in I decided not to go down this track ... just yet.

===Filtering Numbers Input===
Numbers input in the locale of the user could potentially cause issues, as it seems that all arithmetic in PHP takes place using full stops for decimal points and no thousands separator. As noted the results of any calculations seem to be returned in the format of the locale. To get numbers that are input converted to numbers that the system can use we need to pass them through a new function. If we were using a locale that did not have a period as a decimal point, we would also need to filter the results of all calculations through the filter_number_format().
Also critically, SQL does not deal with thousands separators at all and decimal points must be full stops as per ANSI SQL. All numbers going to SQL must be run though this filter_number_format() function.

In addition it is vital that a number is passed only once through this function. Consider the locale for French where the thousands separator is a "." and the decimal point is a ",". When we get numbers from a $_POST we assume they are entered the French way and replace all "." with nothing to eliminate the thousands separators altogether and replace the decimal point "," with the SQL and en_* standard ".". Now if this value goes through the filter_number_format() again the "." will be removed as the function will assume it is a thousands separator. This is the other advantage of setting the LC_NUMERIC locale to en_US as we can then focus on converting input from the user with filter_number_format() and ensuring output of numbers and values in $_POST variables echoed to users is in the locale_number_format()

===What is to be Done?===
I have converted all occurrences of number_format() in the scripts to use the locale_number_format() function. It is possible that there are other scripts that use numbers that don't have the locale_number_format in them - these are yet to be identified.

So the first step is to update your svn repository to the very latest trunk.

Each script that uses numbers (and hence has the locale_number_format() function in it needs to be gone through:
~1) Check all $_POST variables (I use find $_POST in my editor) that are meant to contain a number and ensure they are encapsulated inside the filter_number_format() function before they are used in any calculations or used in any SQL
~1) Test the script and make sure you can add records and delete records using input in the format of a different locale and that numbers are displayed correctly for the locale.
~1) Commit the changed script back to SVN

I think that's it!! However, there are a lot of scripts.... and a lot of numbers to filter :-(
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki