[Proposal] Support for more decimals in price fields

[Chinmay Patidar]

Hello Devs,

Ofbiz places a restriction on saving more than 3 decimal places in price
related entity-fields. But there can be a number of use cases where a user
needs to store more than 2 or 3 decimal places in the currency related
entity-fields.

I even saw some discussions related to this but didn't found any
conclusions from them. Even one issue
<https://issues.apache.org/jira/browse/OFBIZ-494> has been created but for
limited fields. So, I would like to propose support for multiple or more
than 2/3 decimals in price related fields.

Following are some findings related to the currency fields which would be
helpful to examine the requirement:
Ofbiz uses two field types to store the currency related entity-fields.
These two types are 'currency-amount' and 'currency-precise' with their
respective types being NUMBER(18,2) and NUMBER(18,3).

Upon initial research, one can conclude that changing the field definitions
of 'currency-amount' and 'currency-precise' would achieve the requirement.
But doing so will raise following questions which need to be answered. Feel
free to add in them.

   - What would be the precise value of precision(number of decimals)?
   - Will these changes can make the system inconsistent?

In addition, I would like to know the significance of having two separate
field types, i.e. 'currency-amount' and 'currency-precise'.

Also, I have marked one improvement which will be needed to realize the
solution. There are multiple occurrences where hardcoded scaling of 2 has
been set to either display or store a currency field. This needs to be
changed and must be set dynamically.

I'd like to hear your thoughts.

Thanks,
*Chinmay Patidar*

[Aishwary Shrivastava]

+1 Chinmay Patidar,
As it will be good for users who want to use Bitcoins as a mode of Payments.

On Wed, Sep 27, 2017 at 6:12 PM, Chinmay Patidar <
[hidden email]> wrote:

> Hello Devs,
>
> Ofbiz places a restriction on saving more than 3 decimal places in price
> related entity-fields. But there can be a number of use cases where a user
> needs to store more than 2 or 3 decimal places in the currency related
> entity-fields.
>
> I even saw some discussions related to this but didn't found any
> conclusions from them. Even one issue
> <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but for
> limited fields. So, I would like to propose support for multiple or more
> than 2/3 decimals in price related fields.
>
> Following are some findings related to the currency fields which would be
> helpful to examine the requirement:
> Ofbiz uses two field types to store the currency related entity-fields.
> These two types are 'currency-amount' and 'currency-precise' with their
> respective types being NUMBER(18,2) and NUMBER(18,3).
>
> Upon initial research, one can conclude that changing the field definitions
> of 'currency-amount' and 'currency-precise' would achieve the requirement.
> But doing so will raise following questions which need to be answered. Feel
> free to add in them.
>
>    - What would be the precise value of precision(number of decimals)?
>    - Will these changes can make the system inconsistent?
>
> In addition, I would like to know the significance of having two separate
> field types, i.e. 'currency-amount' and 'currency-precise'.
>
> Also, I have marked one improvement which will be needed to realize the
> solution. There are multiple occurrences where hardcoded scaling of 2 has
> been set to either display or store a currency field. This needs to be
> changed and must be set dynamically.
>
> I'd like to hear your thoughts.
>
> Thanks,
> *Chinmay Patidar*
>

--
Thanks and Regards,
*Aishwary Shrivastava* | Enterprise Software Engineer
HotWax Commerce <http://www.hotwax.co/> by HotWax Systems
<http://www.hotwaxsystems.com/>

[Jacques Le Roux]

Thanks Chinmay, Aishwary,

There are 2 aspects:

 1. the underneath calculations where IMO we should use as mush as possible decimals.
    To be pragmatic I think in DB 6 is enough. This number was used in Europe when converting from all other currencies to Euro, with a not so bad
    result. We could use more for cryptocurrencies if needed
 2. And then showing result in UI, where most of the time 2 decimals are enough, but you can set the number of decimals you want using properties
    (from the top of my head)

Now about currency-precise vs currency-amount, you can start with http://markmail.org/message/sv7kcso4su7qguxe and go ahead with new improvements,
after discussing it here please.

HTH

Jacques


Le 27/09/2017 à 15:48, Aishwary Shrivastava a écrit :

> +1 Chinmay Patidar,
> As it will be good for users who want to use Bitcoins as a mode of Payments.
>
> On Wed, Sep 27, 2017 at 6:12 PM, Chinmay Patidar <
> [hidden email]> wrote:
>
>> Hello Devs,
>>
>> Ofbiz places a restriction on saving more than 3 decimal places in price
>> related entity-fields. But there can be a number of use cases where a user
>> needs to store more than 2 or 3 decimal places in the currency related
>> entity-fields.
>>
>> I even saw some discussions related to this but didn't found any
>> conclusions from them. Even one issue
>> <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but for
>> limited fields. So, I would like to propose support for multiple or more
>> than 2/3 decimals in price related fields.
>>
>> Following are some findings related to the currency fields which would be
>> helpful to examine the requirement:
>> Ofbiz uses two field types to store the currency related entity-fields.
>> These two types are 'currency-amount' and 'currency-precise' with their
>> respective types being NUMBER(18,2) and NUMBER(18,3).
>>
>> Upon initial research, one can conclude that changing the field definitions
>> of 'currency-amount' and 'currency-precise' would achieve the requirement.
>> But doing so will raise following questions which need to be answered. Feel
>> free to add in them.
>>
>>     - What would be the precise value of precision(number of decimals)?
>>     - Will these changes can make the system inconsistent?
>>
>> In addition, I would like to know the significance of having two separate
>> field types, i.e. 'currency-amount' and 'currency-precise'.
>>
>> Also, I have marked one improvement which will be needed to realize the
>> solution. There are multiple occurrences where hardcoded scaling of 2 has
>> been set to either display or store a currency field. This needs to be
>> changed and must be set dynamically.
>>
>> I'd like to hear your thoughts.
>>
>> Thanks,
>> *Chinmay Patidar*
>>
>
>

[taher]

So this is one area where I faced many problems in the past, with
units generally and money specifically. You could be surprised how
many times something like rounding and accuracy can end up being a
_big_ problem in systems.

I think to really achieve flexibility (which is one of the strong
points of OFBiz) then we should have a general purpose accuracy
system. We can perhaps implement it as follows:
- Amount could be a free field with no restriction on accuracy (it
goes as far as the environment provides)
- Accuracy should be a parameter with a default value. However, the
parameter should be customizable to each unit of measurement
(currency) separately. So for example, you can set USD to 2, KWD to 3,
etc ...
- Rounding method should be a parameter with a default value. Examples
could be ROUND_UP, ROUND_DOWN, ROUND_NEAREST
- Rounding time should be a parameter with a default value. Perhaps
something like DURING_CALCULATION, AFTER_CALCULATION
- Then we implement all basic calculations in services or utility
methods that fully utilize the above parameters and settings.

Sorry if I went overboard :) But I usually always lean towards root
solutions that solve many problems at once. Not sure if any folks are
interested but if you like the idea I'd be willing to help with it.

On Wed, Sep 27, 2017 at 3:42 PM, Chinmay Patidar
<[hidden email]> wrote:

> Hello Devs,
>
> Ofbiz places a restriction on saving more than 3 decimal places in price
> related entity-fields. But there can be a number of use cases where a user
> needs to store more than 2 or 3 decimal places in the currency related
> entity-fields.
>
> I even saw some discussions related to this but didn't found any
> conclusions from them. Even one issue
> <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but for
> limited fields. So, I would like to propose support for multiple or more
> than 2/3 decimals in price related fields.
>
> Following are some findings related to the currency fields which would be
> helpful to examine the requirement:
> Ofbiz uses two field types to store the currency related entity-fields.
> These two types are 'currency-amount' and 'currency-precise' with their
> respective types being NUMBER(18,2) and NUMBER(18,3).
>
> Upon initial research, one can conclude that changing the field definitions
> of 'currency-amount' and 'currency-precise' would achieve the requirement.
> But doing so will raise following questions which need to be answered. Feel
> free to add in them.
>
>    - What would be the precise value of precision(number of decimals)?
>    - Will these changes can make the system inconsistent?
>
> In addition, I would like to know the significance of having two separate
> field types, i.e. 'currency-amount' and 'currency-precise'.
>
> Also, I have marked one improvement which will be needed to realize the
> solution. There are multiple occurrences where hardcoded scaling of 2 has
> been set to either display or store a currency field. This needs to be
> changed and must be set dynamically.
>
> I'd like to hear your thoughts.
>
> Thanks,
> *Chinmay Patidar*

[Jacques Le Roux]

Hi Taher,

That would be perfect :)

Jacques


Le 01/10/2017 à 10:48, Taher Alkhateeb a écrit :

> So this is one area where I faced many problems in the past, with
> units generally and money specifically. You could be surprised how
> many times something like rounding and accuracy can end up being a
> _big_ problem in systems.
>
> I think to really achieve flexibility (which is one of the strong
> points of OFBiz) then we should have a general purpose accuracy
> system. We can perhaps implement it as follows:
> - Amount could be a free field with no restriction on accuracy (it
> goes as far as the environment provides)
> - Accuracy should be a parameter with a default value. However, the
> parameter should be customizable to each unit of measurement
> (currency) separately. So for example, you can set USD to 2, KWD to 3,
> etc ...
> - Rounding method should be a parameter with a default value. Examples
> could be ROUND_UP, ROUND_DOWN, ROUND_NEAREST
> - Rounding time should be a parameter with a default value. Perhaps
> something like DURING_CALCULATION, AFTER_CALCULATION
> - Then we implement all basic calculations in services or utility
> methods that fully utilize the above parameters and settings.
>
> Sorry if I went overboard :) But I usually always lean towards root
> solutions that solve many problems at once. Not sure if any folks are
> interested but if you like the idea I'd be willing to help with it.
>
> On Wed, Sep 27, 2017 at 3:42 PM, Chinmay Patidar
> <[hidden email]> wrote:
>> Hello Devs,
>>
>> Ofbiz places a restriction on saving more than 3 decimal places in price
>> related entity-fields. But there can be a number of use cases where a user
>> needs to store more than 2 or 3 decimal places in the currency related
>> entity-fields.
>>
>> I even saw some discussions related to this but didn't found any
>> conclusions from them. Even one issue
>> <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but for
>> limited fields. So, I would like to propose support for multiple or more
>> than 2/3 decimals in price related fields.
>>
>> Following are some findings related to the currency fields which would be
>> helpful to examine the requirement:
>> Ofbiz uses two field types to store the currency related entity-fields.
>> These two types are 'currency-amount' and 'currency-precise' with their
>> respective types being NUMBER(18,2) and NUMBER(18,3).
>>
>> Upon initial research, one can conclude that changing the field definitions
>> of 'currency-amount' and 'currency-precise' would achieve the requirement.
>> But doing so will raise following questions which need to be answered. Feel
>> free to add in them.
>>
>>     - What would be the precise value of precision(number of decimals)?
>>     - Will these changes can make the system inconsistent?
>>
>> In addition, I would like to know the significance of having two separate
>> field types, i.e. 'currency-amount' and 'currency-precise'.
>>
>> Also, I have marked one improvement which will be needed to realize the
>> solution. There are multiple occurrences where hardcoded scaling of 2 has
>> been set to either display or store a currency field. This needs to be
>> changed and must be set dynamically.
>>
>> I'd like to hear your thoughts.
>>
>> Thanks,
>> *Chinmay Patidar*

[Swapnil Mane]

Thanks Chinmay for bringing this up, +1 from my side.

Liked all the points discussed by Taher.


Thanks Aishwary and Jacques too for the inputs :)


- Best Regards,
Swapnil M Mane

On Sun, Oct 1, 2017 at 2:18 PM, Taher Alkhateeb <[hidden email]>
wrote:

> So this is one area where I faced many problems in the past, with
> units generally and money specifically. You could be surprised how
> many times something like rounding and accuracy can end up being a
> _big_ problem in systems.
>
> I think to really achieve flexibility (which is one of the strong
> points of OFBiz) then we should have a general purpose accuracy
> system. We can perhaps implement it as follows:
> - Amount could be a free field with no restriction on accuracy (it
> goes as far as the environment provides)
> - Accuracy should be a parameter with a default value. However, the
> parameter should be customizable to each unit of measurement
> (currency) separately. So for example, you can set USD to 2, KWD to 3,
> etc ...
> - Rounding method should be a parameter with a default value. Examples
> could be ROUND_UP, ROUND_DOWN, ROUND_NEAREST
> - Rounding time should be a parameter with a default value. Perhaps
> something like DURING_CALCULATION, AFTER_CALCULATION
> - Then we implement all basic calculations in services or utility
> methods that fully utilize the above parameters and settings.
>
> Sorry if I went overboard :) But I usually always lean towards root
> solutions that solve many problems at once. Not sure if any folks are
> interested but if you like the idea I'd be willing to help with it.
>
> On Wed, Sep 27, 2017 at 3:42 PM, Chinmay Patidar
> <[hidden email]> wrote:
> > Hello Devs,
> >
> > Ofbiz places a restriction on saving more than 3 decimal places in price
> > related entity-fields. But there can be a number of use cases where a
> user
> > needs to store more than 2 or 3 decimal places in the currency related
> > entity-fields.
> >
> > I even saw some discussions related to this but didn't found any
> > conclusions from them. Even one issue
> > <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but
> for
> > limited fields. So, I would like to propose support for multiple or more
> > than 2/3 decimals in price related fields.
> >
> > Following are some findings related to the currency fields which would be
> > helpful to examine the requirement:
> > Ofbiz uses two field types to store the currency related entity-fields.
> > These two types are 'currency-amount' and 'currency-precise' with their
> > respective types being NUMBER(18,2) and NUMBER(18,3).
> >
> > Upon initial research, one can conclude that changing the field
> definitions
> > of 'currency-amount' and 'currency-precise' would achieve the
> requirement.
> > But doing so will raise following questions which need to be answered.
> Feel
> > free to add in them.
> >
> >    - What would be the precise value of precision(number of decimals)?
> >    - Will these changes can make the system inconsistent?
> >
> > In addition, I would like to know the significance of having two separate
> > field types, i.e. 'currency-amount' and 'currency-precise'.
> >
> > Also, I have marked one improvement which will be needed to realize the
> > solution. There are multiple occurrences where hardcoded scaling of 2 has
> > been set to either display or store a currency field. This needs to be
> > changed and must be set dynamically.
> >
> > I'd like to hear your thoughts.
> >
> > Thanks,
> > *Chinmay Patidar*
>

[Deepak Dixit-3]

Looks good Taher.


On Sun, Oct 1, 2017 at 2:18 PM, Taher Alkhateeb <[hidden email]>
wrote:

> So this is one area where I faced many problems in the past, with
> units generally and money specifically. You could be surprised how
> many times something like rounding and accuracy can end up being a
> _big_ problem in systems.
>
> I think to really achieve flexibility (which is one of the strong
> points of OFBiz) then we should have a general purpose accuracy
> system. We can perhaps implement it as follows:
> - Amount could be a free field with no restriction on accuracy (it
> goes as far as the environment provides)
> - Accuracy should be a parameter with a default value. However, the
> parameter should be customizable to each unit of measurement
> (currency) separately. So for example, you can set USD to 2, KWD to 3,
> etc ...
>

https://www.currency-iso.org/en/home/tables/table-a1.html



Thanks & Regards
--
Deepak Dixit
www.hotwaxsystems.com
www.hotwax.co

> - Rounding method should be a parameter with a default value. Examples
> could be ROUND_UP, ROUND_DOWN, ROUND_NEAREST
> - Rounding time should be a parameter with a default value. Perhaps
> something like DURING_CALCULATION, AFTER_CALCULATION
> - Then we implement all basic calculations in services or utility
> methods that fully utilize the above parameters and settings.
>
> Sorry if I went overboard :) But I usually always lean towards root
> solutions that solve many problems at once. Not sure if any folks are
> interested but if you like the idea I'd be willing to help with it.
>
> On Wed, Sep 27, 2017 at 3:42 PM, Chinmay Patidar
> <[hidden email]> wrote:
> > Hello Devs,
> >
> > Ofbiz places a restriction on saving more than 3 decimal places in price
> > related entity-fields. But there can be a number of use cases where a
> user
> > needs to store more than 2 or 3 decimal places in the currency related
> > entity-fields.
> >
> > I even saw some discussions related to this but didn't found any
> > conclusions from them. Even one issue
> > <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but
> for
> > limited fields. So, I would like to propose support for multiple or more
> > than 2/3 decimals in price related fields.
> >
> > Following are some findings related to the currency fields which would be
> > helpful to examine the requirement:
> > Ofbiz uses two field types to store the currency related entity-fields.
> > These two types are 'currency-amount' and 'currency-precise' with their
> > respective types being NUMBER(18,2) and NUMBER(18,3).
> >
> > Upon initial research, one can conclude that changing the field
> definitions
> > of 'currency-amount' and 'currency-precise' would achieve the
> requirement.
> > But doing so will raise following questions which need to be answered.
> Feel
> > free to add in them.
> >
> >    - What would be the precise value of precision(number of decimals)?
> >    - Will these changes can make the system inconsistent?
> >
> > In addition, I would like to know the significance of having two separate
> > field types, i.e. 'currency-amount' and 'currency-precise'.
> >
> > Also, I have marked one improvement which will be needed to realize the
> > solution. There are multiple occurrences where hardcoded scaling of 2 has
> > been set to either display or store a currency field. This needs to be
> > changed and must be set dynamically.
> >
> > I'd like to hear your thoughts.
> >
> > Thanks,
> > *Chinmay Patidar*
>

[Vaibhav Jain]

+1 Taher

Vaibhav Jain
Hotwax Systems,
[hidden email]

On Tue, Oct 3, 2017 at 1:09 PM, Deepak Dixit <[hidden email]
> wrote:

> Looks good Taher.
>
>
> On Sun, Oct 1, 2017 at 2:18 PM, Taher Alkhateeb <
> [hidden email]>
> wrote:
>
> > So this is one area where I faced many problems in the past, with
> > units generally and money specifically. You could be surprised how
> > many times something like rounding and accuracy can end up being a
> > _big_ problem in systems.
> >
> > I think to really achieve flexibility (which is one of the strong
> > points of OFBiz) then we should have a general purpose accuracy
> > system. We can perhaps implement it as follows:
> > - Amount could be a free field with no restriction on accuracy (it
> > goes as far as the environment provides)
> > - Accuracy should be a parameter with a default value. However, the
> > parameter should be customizable to each unit of measurement
> > (currency) separately. So for example, you can set USD to 2, KWD to 3,
> > etc ...
> >
>
>
> https://www.currency-iso.org/en/home/tables/table-a1.html
>
>
>
> Thanks & Regards
> --
> Deepak Dixit
> www.hotwaxsystems.com
> www.hotwax.co
>
>
>
>
>
> > - Rounding method should be a parameter with a default value. Examples
> > could be ROUND_UP, ROUND_DOWN, ROUND_NEAREST
> > - Rounding time should be a parameter with a default value. Perhaps
> > something like DURING_CALCULATION, AFTER_CALCULATION
> > - Then we implement all basic calculations in services or utility
> > methods that fully utilize the above parameters and settings.
> >
> > Sorry if I went overboard :) But I usually always lean towards root
> > solutions that solve many problems at once. Not sure if any folks are
> > interested but if you like the idea I'd be willing to help with it.
> >
> > On Wed, Sep 27, 2017 at 3:42 PM, Chinmay Patidar
> > <[hidden email]> wrote:
> > > Hello Devs,
> > >
> > > Ofbiz places a restriction on saving more than 3 decimal places in
> price
> > > related entity-fields. But there can be a number of use cases where a
> > user
> > > needs to store more than 2 or 3 decimal places in the currency related
> > > entity-fields.
> > >
> > > I even saw some discussions related to this but didn't found any
> > > conclusions from them. Even one issue
> > > <https://issues.apache.org/jira/browse/OFBIZ-494> has been created but
> > for
> > > limited fields. So, I would like to propose support for multiple or
> more
> > > than 2/3 decimals in price related fields.
> > >
> > > Following are some findings related to the currency fields which would
> be
> > > helpful to examine the requirement:
> > > Ofbiz uses two field types to store the currency related entity-fields.
> > > These two types are 'currency-amount' and 'currency-precise' with their
> > > respective types being NUMBER(18,2) and NUMBER(18,3).
> > >
> > > Upon initial research, one can conclude that changing the field
> > definitions
> > > of 'currency-amount' and 'currency-precise' would achieve the
> > requirement.
> > > But doing so will raise following questions which need to be answered.
> > Feel
> > > free to add in them.
> > >
> > >    - What would be the precise value of precision(number of decimals)?
> > >    - Will these changes can make the system inconsistent?
> > >
> > > In addition, I would like to know the significance of having two
> separate
> > > field types, i.e. 'currency-amount' and 'currency-precise'.
> > >
> > > Also, I have marked one improvement which will be needed to realize the
> > > solution. There are multiple occurrences where hardcoded scaling of 2
> has
> > > been set to either display or store a currency field. This needs to be
> > > changed and must be set dynamically.
> > >
> > > I'd like to hear your thoughts.
> > >
> > > Thanks,
> > > *Chinmay Patidar*
> >
>