4 decimals in item price

guru · April 11, 2019, 10:19am

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.

crafter · April 11, 2019, 2:13pm

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);
}

guru · April 11, 2019, 2:26pm

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.

crafter · April 11, 2019, 2:57pm

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.

guru · April 11, 2019, 3:26pm

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?

crafter · April 11, 2019, 3:32pm

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    |                |
+----------------+---------------+------+-----+---------+----------------+

UnderDog · April 11, 2019, 3:42pm

@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?

crafter · April 11, 2019, 3:46pm

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,

guru · April 11, 2019, 3:56pm

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.

UnderDog · April 11, 2019, 4:01pm

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

guru · April 11, 2019, 4:17pm

@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

UnderDog · April 11, 2019, 4:27pm

application/modules/invoices/models/

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

crafter · April 11, 2019, 4:38pm

@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.

guru · April 11, 2019, 4:59pm

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.

UnderDog · April 11, 2019, 5:13pm

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

AskoKernel · January 13, 2022, 8:40pm

Hayy .all from here need to change and plus need in
,besause you make invoice from products ,in products need to allow to decimal 10,4 .

ip_products
product_price decimal(10,4)
purchase_price decimal(20,2

In application/helper/number_helper.php change:

if ($currency_symbol_placement == ‘before’) {
return $currency_symbol . number_format($amount, ($decimal_point) ? 2 : 0, $decimal_point, $thousands_separator);
} elseif ($currency_symbol_placement == ‘afterspace’) {
return number_format($amount, ($decimal_point) ? 6 : 0, $decimal_point, $thousands_separator) . ’ ’ . $currency_symbol;
} else {
return number_format($amount, ($decimal_point) ? 6 : 0, $decimal_point, $thousands_separator) . $currency_symbol;
}
}

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

enjoy

4 decimals in item price

Change the structure of these tables in the database:

In application/helper/number_helper.php change:

ip_products product_price decimal(10,4) purchase_price decimal(20,2

In application/helper/number_helper.php change:

ip_products
product_price decimal(10,4)
purchase_price decimal(20,2