vTiger 5.1 – Number of decimals in prices

We sell items in large quantities at very low unit price like 1,000,000 items at $0.1234, so a variation of $0.0001 is worth $100.
Out of the box, vTiger has a mix of 2 and 3 decimals to handle unit prices. Do not ask me why, but for example:

  • The product unit price has 2 decimals: decimal(25,2)
  • The price book unit price has 3 decimals: decimal(25,3)
  • Discount is decimal(7,3) which is fine for percentage, but problematic for amounts.

In order to extend the number of decimals, it is necessary to modify the type of the fields that store the price information in the database.
The example below is based on a standard installation under windows.
Open a cmd window.
Change directory to mysql.
cd C:\Program Files\vtigercrm-5.1.0\mysql\bin
In the line below, replace xxxxxx with your password to start mysql.
mysql -u root -pxxxxxx
Look at which databases are available and select the vtiger one.
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| vtigercrm510       |
+--------------------+
mysql>use vtigercrm510

List the tables available:
mysql>show tables;
The one we are interested in is the products table called vtiger_products.
We can find a short description of the vtiger data model in http://wiki.vtiger.com/index.php/Database_schema. A PDF file can be downloaded, but seems incomplete. Better information can be found here: http://forums.vtiger.com/download.php?id=1755
mysql> describe vtiger_products;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| productid        | int(11)       | NO   | PRI | NULL    |       |
| product_no       | varchar(100)  | NO   |     | NULL    |       |
| productname      | varchar(50)   | NO   |     | NULL    |       |
| productcode      | varchar(40)   | YES  |     | NULL    |       |
| productcategory  | varchar(200)  | YES  |     | NULL    |       |
| manufacturer     | varchar(200)  | YES  |     | NULL    |       |
| qty_per_unit     | decimal(11,2) | YES  |     | 0.00    |       |
| unit_price       | decimal(25,2) | YES  |     | NULL    |       |
| weight           | decimal(11,3) | YES  |     | NULL    |       |
| pack_size        | int(11)       | YES  |     | NULL    |       |
| sales_start_date | date          | YES  |     | NULL    |       |
| sales_end_date   | date          | YES  |     | NULL    |       |
| start_date       | date          | YES  |     | NULL    |       |
| expiry_date      | date          | YES  |     | NULL    |       |
| cost_factor      | int(11)       | YES  |     | NULL    |       |
| commissionrate   | decimal(7,3)  | YES  |     | NULL    |       |
| commissionmethod | varchar(50)   | YES  |     | NULL    |       |
| discontinued     | int(1)        | NO   |     | 0       |       |
| usageunit        | varchar(200)  | YES  |     | NULL    |       |
| handler          | int(11)       | YES  |     | NULL    |       |
| reorderlevel     | int(11)       | YES  |     | NULL    |       |
| website          | varchar(100)  | YES  |     | NULL    |       |
| taxclass         | varchar(200)  | YES  |     | NULL    |       |
| mfr_part_no      | varchar(200)  | YES  |     | NULL    |       |
| vendor_part_no   | varchar(200)  | YES  |     | NULL    |       |
| serialno         | varchar(200)  | YES  |     | NULL    |       |
| qtyinstock       | decimal(25,3) | YES  |     | NULL    |       |
| productsheet     | varchar(200)  | YES  |     | NULL    |       |
| qtyindemand      | int(11)       | YES  |     | NULL    |       |
| glacct           | varchar(200)  | YES  |     | NULL    |       |
| vendor_id        | int(11)       | YES  |     | NULL    |       |
| imagename        | text          | YES  |     | NULL    |       |
| currency_id      | int(19)       | NO   |     | 1       |       |
+------------------+---------------+------+-----+---------+-------+
33 rows in set (0.28 sec)

The field that we need to change in this table is unit_price, so we issue the following command:

mysql>ALTER TABLE vtiger_products MODIFY unit_price  DECIMAL(25,4);

In each price book, we have a specific price for each product.

mysql> describe vtiger_pricebookproductrel;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| pricebookid  | int(19)       | NO   | PRI | NULL    |       |
| productid    | int(19)       | NO   | PRI | NULL    |       |
| listprice    | decimal(25,3) | YES  |     | NULL    |       |
| usedcurrency | int(11)       | NO   |     | 1       |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

mysql>ALTER TABLE vtiger_pricebookproductrel MODIFY listprice  DECIMAL(25,4);

We have changed the fields in the database, so the price information will be stored with the proper number of decimals for our application.

The fields that we found are:

vtiger_inventoryproductrel: listprice decimal(25,3)
vtiger_inventoryproductrel: discount_amount decimal(7,3)
vtiger_productcurrencyrel: converted_price decimal(25,2)
vtiger_productcurrencyrel: actual_price decimal(25,2)

vtiger_productcurrencyrel:
adjustment       | decimal(25,3)
salescommission  | decimal(25,3)
exciseduty       | decimal(25,3)
total            | decimal(25,3)
subtotal         | decimal(25,3)
discount_percent | decimal(25,3)
discount_amount  | decimal(25,3)
s_h_amount       | decimal(25,3)

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • CRM  On February 11, 2013 at 11:21 pm

    Very well done! Thanks for sharing, this was bothering us as well.

  • Rick Leir  On February 15, 2013 at 5:01 pm

    Thanks for this article, it was helpful! Furthermore, in vtiger 5.4, in include/fields/CurrencyField.php line 40

    var $numberOfDecimal = 3;

    Change this to 4 and we have the right precision.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: