4 decimals in item price


#1

We are using the latetst version of InvoicePlane in the Netherlands, but we encounter an issue which hopefully can be solved easily.
The ‘item price’ automatically is set to 2 decimals behind the comma. As in Holland we have a 21% VAT tarif, 2 decimals is not enough to correctly calcute the total price.
Most effective would be to have 4 digitis in item price.

Fe.
€ 8,2644 will be € 10,00 including VAT (4 digits)
but
€ 8,26 will be € 9,99 (2 digitis)

Is there a way to change this in the code?
I see some threads where they have some issue with the decimals in the quantity field, but cannot find anything about the item price field.
Any help would be appreciated.


#2

It depend where you want this conversion done.
For example, if you want an item to be stored on the db after conversion, then make this change to product model application/modules/products/models/Mdl_products.php

If you want the invoice to be changed, then change the appropriate invoice model (there are a few).

public function save($id = null, $db_array = null)
{
    $db_array['field_name'] = some_calculation_to_round( ...);
    parent::save($id, $db_array);
}

#3

If we create a new invoice, when I put in 8.2644, the system automatically changes it to 8.26.
This needs to be set with 4 decimals.

So I want to change the variable of 2 decimals to 4 decimals somewhere in the code.


#4

I see.

The decimal point is specified as 2 fractional numbers on the database. You will have to change the data type of the database to allow a 4 digit fractional part.

Have a look at the invoice_amounts table.


#5

I checked the database but I see no value in ‘invoice_amounts’ that will change the decimals from 2 to 4.
There are 2 instances that have ‘decimal’ as value, but those two can be configured in the settings of InvoicePlane (and are not the item price excluding VAT).
Am I missing something?


#6
describe ip_invoice_amounts;
    +------------------------+----------------+------+-----+---------+----------------+
    | Field                  | Type           | Null | Key | Default | Extra          |
    +------------------------+----------------+------+-----+---------+----------------+
    | invoice_amount_id      | int(11)        | NO   | PRI | NULL    | auto_increment |
    | invoice_id             | int(11)        | NO   | MUL | NULL    |                |
    | invoice_sign           | enum('1','-1') | NO   |     | 1       |                |
    | invoice_item_subtotal  | decimal(20,2)  | YES  |     | NULL    |                |
    | invoice_item_tax_total | decimal(20,2)  | YES  |     | NULL    |                |
    | invoice_tax_total      | decimal(20,2)  | YES  |     | NULL    |                |
    | invoice_total          | decimal(20,2)  | YES  |     | NULL    |                |
    | invoice_paid           | decimal(20,2)  | YES  | MUL | NULL    |                |
    | invoice_balance        | decimal(20,2)  | YES  |     | NULL    |                |
    +------------------------+----------------+------+-----+---------+----------------+


describe ip_invoice_item_amounts;;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| item_amount_id | int(11)       | NO   | PRI | NULL    | auto_increment |
| item_id        | int(11)       | NO   | MUL | NULL    |                |
| item_subtotal  | decimal(20,2) | YES  |     | NULL    |                |
| item_tax_total | decimal(20,2) | YES  |     | NULL    |                |
| item_discount  | decimal(20,2) | YES  |     | NULL    |                |
| item_total     | decimal(20,2) | YES  |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+

#7

@guru I know I don’t have the solution, but I’ll try to help as much as I can.
@crafter would this suffice?

alter table table_name change field decimal(10,4)

and that per field?
would 4 digits be good enough to handle all situations? :thinking:


#8

Thanks @UnderDog

That is the first step to changing the precision. Without that step, we can’t move forward.

f course, it has to be tested step by step to make sure nothing else is changing the value before being saved,


#9

I changed every value in ‘ip_invoice_amounts’ en ‘ip_invoice_item_amounts’ from (20,2) to (20,4).
Unfortunately this didn’t change anything. The software is holding on to the 2 decimals.


#10

We need to know if it stores 4 decimals in the database and my eduncared guess tells me it’s going to handle 2 decimals despite the 4 decimals in the database


#11

@UnderDog

I did a test with this.

The database now holds 4 decimals. But when you add an item it only stores 2.
So there is room for 4 decimals in the database now, but the system only saves the first 2.
8.2644 input results now in 8.2600


#12

application/modules/invoices/models/

in the save function does it round to 2 decimals before it saves? :blush:


#13

@guru mentoned that the db is correct. Look at the format_amount() function in the number_helper.php, It uses the settings value…

You need the change the settings value for decimal point. Go to the general settings tab.


#14

Thanks @crafter and @UnderDog for your help. Here is a summary of the solution.

Change the structure of these tables in the database:

  • ‘ip_invoice_amounts’
  • ‘ip_invoice_items_amounts’
  • ‘ip_invoice_items’

Change the values of ‘decimal(20,2)’ to ‘decimal(20,4)’.

In application/helper/number_helper.php change:

return number_format($amount, ($decimal_point) ? 2 : 0, $decimal_point, $thousands_separator);

to

    return number_format($amount, ($decimal_point) ? 4 : 0, $decimal_point, $thousands_separator);

This did the trick for me when manually adding a product line to an invoice.
I have to check if pre-set products will also hold the 4 decimals.


#15

I’m so glad it helped. With invoiceplane version2 we’re going to do this a little differently. If you want to help join us on the slack channel, so you can control the outcome