11th Feb2013

WHMCS: Round up product prices for secondary currencies

by Gyro

Today, I actually assisted my hosting provider in setting up secondary currencies in WHMCS, the billing and support system for/from cpanel! :)

They only wanted full numbers for the monthly breakdowns, so when they click "Update Prices" in the Currency Settings and a product would cost 1233.37 the price gets automatically rounded up to 1234.00.

Unfortunately, WHMCS doesn't offer this feature, so I suggested to setup a script on a secure location that they can trigger after they update the currency conversion rates and product prices. Below is the sql query that goes through all secondary currencies (1 = default currency) and rounds up the values, so that the setup fee and all monthly prices are full numbers.

The script itself would run a simple MySQL query:

UPDATE tblpricing SET
msetupfee = ceil(msetupfee),
qsetupfee = ceil(qsetupfee),
ssetupfee = ceil(ssetupfee),
asetupfee = ceil(asetupfee),
bsetupfee = ceil(bsetupfee),
tsetupfee = ceil(tsetupfee),
monthly = ceil(monthly),
quarterly = ceil(quarterly/3)*3,
semiannually = ceil(semiannually/6)*6,
annually = ceil(annually/12)*12,
biennially = ceil(biennially/24)*24,
triennially = ceil(triennially/36)*36
currency != 1 AND
id = id

you could replace

currency != 1 AND

currency = 2 AND

currency IN(2,3,6) AND


Enjoy :enjoy: