float issue when used for financial matters (currency)

[v.lo...@ifbcon.de]

Hi,

we created a forecast and invoicing module for SGS which works really neat.
Only accuracy of numbers (currency, invoice) is an issue. using simple_type float leads to little errors.

It looks like if we put in numbers exceeding 10,000.00 (€) the last digit after the decimal point is rounded.

Input: 23,485.15

output from database after storing:
23485.1


Here is the field definition from the
  </field>   
   <field name="invoice_net" displayname="Rechnungsbetrag, netto" simple_type="float" sum="true">
  <!--
      <onlyin views="display|canceled|booked"/>
        -->
  </field>


At least we would like to show all of these values with 9 digits, 2 of them after the decimal point

We read about float precision default of MYSQL and this seems to be the spot where it happens.

Is there a way to increase the precison of float in the SGML module  or in the database?
Should we move from "float" to "decimal" and how exactly do you define a custom simple_type like decimal?

Can anybody help how to fix that

Thanks in advance for your help,

Volker

Simple Groupware version: 0.745p (Vlads patch)
Simple Groupware language: de
PHP Version: 5.4.36-0+deb7u3
Database + Version: mysql 554
Server OS: Linux svr-ifb 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u2 x86_64
Webserver: Apache/2.2.22 (Debian)
Webbrowser: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
APC cache usage: 30 MB / 32 MB

[Patrick Pliessnig]

Hello Volker

float is in most cases better for scientifique calculation. it does not preserve exact precision.
decimal or numeric data types preserve exact precision. they are better for monetary data

in sgs use the db_type="" [1] tag within the field tag to direct mysql to use the datatype you want.

also consider using store="" [2] and restore="" [3] tags to transform data between the database and the presentation. for example if you want to format the data.

[1] http://www.simple-groupware.de/cms/Manual#SgsMLExtendedAttributes
[2] http://www.simple-groupware.de/cms/Manual#store
[3] http://www.simple-groupware.de/cms/Manual#restore

Cheers
Patrick

[v.lo...@ifbcon.de]

Hello Patrick,

thanks for your hint.

to test if it works we added an additional field in our accounting module ( *.xml)

So now we have field with a float input and a field with a decimal input like this:

<field name="payed_net" displayname="Bezahlt, netto" simple_type="float" sum="true">
    <!--
      <onlyin views="display|completed|canceled|booked"/>
     -->
  </field>
<field name="d_payed_net" displayname="D-Bezahlt, netto" db_type="decimal(10,2)" sum="true">
    <!--
      <onlyin views="display|completed|canceled|booked"/>
     -->
  </field>

Unfortunately the float field name="payed_net" shows up in the browser, but the decimal field name="d_payed_net" is not displayed.
Checking the database the field "d_payed_net" is missing.

What did we miss or what did we do wrong?

Thanks for your support.

Cheers Volker

[Paul Zarucki]

Hi,

Just tried this

<field name="d_payed_net" displayname="D-Bezahlt, netto" db_type="decimal(10,2)" simple_type="float" sum="true">
</field>

and it works!

So it looks like you need both db_type and simple_type; though I haven't studied the code to find out why. In fact, you can even use simple_type="text".

Regards,
Paul

--
You received this message because you are subscribed to the Google Groups "Simple Groupware" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.
Visit this group at https://groups.google.com/group/simple-groupware.
For more options, visit https://groups.google.com/d/optout.

[v.lo...@ifbcon.de]

Hi Paul,

I tried it too and it actually works.

Great. But I don't understand why ... may be the parser expects a kind of expression consisting of a predefinde simple_xxx type which triggers the process and is done by simple_type="xxx" now.

Thanks, good to know you all are around somewhere!

Cheers, Volker

[Patrick Pliessnig]

Hi Paul, Volker

Paul thank you for answering this question.

The universe of sgs are the simple_types, the universe of the database are the db_types.
sgs always maps simple_types (used for presentation) to a db_type (used for persistence). this makes it possible to add properties to a simple_type in the user interface beyond database and it makes it also possible to solve storage issues like Volkers problem with money values.

to simplify the field definitions, for each simple_type there is a default mapping into a db_type.
In mysql for example the default mapping for simple_type="float" is db_type="float".

so these 2 expressions are strictly equivalent:

<field name="a_field" displayname="a_name" simple_type="float"></field>
<field name="a_field" displayname="a_name" simple_type="float" db_type="float"></field>

some default mappings for mysql:
simple_type float = db_type float
simple_type date = db_type decimal(10,0)
simple_type text = db_type varchar(255)
simple_type select = db_type varchar(255)
simple_type files = db_type text

so, with the db_type tag, you simply override the default mapping.
related to this topic are the store and restore tags which allows you to convert or manipulate data before storing in the database or after restoring from database.

Cheers
Patrick

[Patrick Pliessnig]

I forgot to say:

One of the widely used design principles in sgs is the so called convention over configuration (konvention vor konfiguration) [1]. this simply means, where ever a convention is possible there is a default configuration and you don't need to configure things yourself unless the convention is not suitable in which case you need to configure your own decision.

this principle is one among others that make usage of sgs so easy by decreasing considerably the number of decisions you need to make. on the other side, in non-conventional situations with sgs you need to remember again to make more decisions yourself.

[1] https://en.wikipedia.org/wiki/Convention_over_configuration

[Paul Zarucki]

Hi Patrick,

Many thanks for your clear and in depth explanations, they are very helpful.

Regards,
Paul

[v.lo...@ifbcon.de]

Hi Patrick,

thanks for the explanation, very helpful. It looks like a very consequent and well arranged conception.

Maybe you could give me some additional hint on the stor and restor functions:

If I wanted to have an input for the decimal filed like 10.000,00 (where the comma "," represents the decimal point in Germany) and to have a readout from the database similar to the input, what would I have to do.
I read about it in the manual, but because of my lack in php programming I don't know how the function looks like and where to put it:"--
To collect these functions in a central place, all store and restore functions are kept in core/functions_user.php and have a "modify_" prefix (because they modify data), e.g. "modify_datetime_to_int" converts a date given as string to a timestamp using integer representation. To define a store or restore function in a sgsML file, write <store function="datetime_to_int"/> and <restore function="dateformat||d.m.Y"/> between the <field>-tags to work with a date-field using the functions modify_datetime_to_int and modify_dateformat. --"

At the time being using float or decimal we have to use an input format like 10000.00 for the above example and the view in SGS is just the same. And, funny thing, the excel spreadsheet export from SGS looks like 10000,00, which is almost what we prefer to have.




Cheers Volker

Am Montag, 17. Oktober 2016 18:43:43 UTC+2 schrieb v.lo...@ifbcon.de:

[Patrick Pliessnig]

Hi Volker

At the time being using float or decimal we have to use an input format like 10000.00 for the above example and the view in SGS is just the same. And, funny thing, the excel spreadsheet export from SGS looks like 10000,00, which is almost what we prefer to have.

the different forms of a value object with storage are always like this:

1. storage value: db_type = "x" (for sgs: a database field / for excel: a cell in the file on the harddisk )
2: program value: simple_type = "y" (for sgs: some simple_type / for excel: a cell in the RAM/Arbeitsspeicher)
3: display form: for sgs according to a view template / for excel according to cell format

you can modify the value between storage [1] and program [2] with the store and restore flag. this is useful for example for value conversions. anyway these two forms are generally not readable by humans.

you need to format the program value [2] for display [3]. you do this with the filter flag. if the value is editable on the display you need to validate it with the validate flag. again sgs does this normally automatically for you with its own rules. excel does this also by using information from the country settings of your operation system and particular format settings for a particular cell.

so, the program value 10000.00 in sgs is the same as 10000,00 in excel, just the display formats are different. you can apply number formatting to a excel cell to display the value as 10'000,00.