milad zenhari Posted May 16, 2018 Share Posted May 16, 2018 Hello I encountered a problem when I make a manual invoice and import it into a sum totaling more than 100,000,000, even if the total investment of 130,000,000 is still in the sum of 100,000 .000 is displayed. Why ? Please help Look at the picture that is attached 0 Quote Link to comment Share on other sites More sharing options...
gfserver999 Posted July 12, 2018 Share Posted July 12, 2018 Hello, I have a similar problem here, in products, domains prices and invoices I can't display an amount over 99.999.999. How to fix that? Thanks! Regards 0 Quote Link to comment Share on other sites More sharing options...
Kian Posted July 12, 2018 Share Posted July 12, 2018 (edited) It happens because WHMCS can't handle more than 10 digits including 2 decimals. In other words the biggest possible number is 99999999.99 (10 digits, 8 integers, 2 decimals). Before you ask there's no workaround unless you are willing to change the structure of all DECIMAL data types from 10,2 to 11,2 or even more. Anyway it's not recommended. Edited July 12, 2018 by Kian 0 Quote Link to comment Share on other sites More sharing options...
gfserver999 Posted July 12, 2018 Share Posted July 12, 2018 Thanks Kian, I see. I hope in any further updgrade, WHCMS developers keep in mind that in many countries is needed, 14,2 Kind Regards 0 Quote Link to comment Share on other sites More sharing options...
Kian Posted July 12, 2018 Share Posted July 12, 2018 I doubt that they will change it with an update. It's almost 10 years that I'm using WHMCS and all fields have always been 10,2 🙄 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted July 12, 2018 Share Posted July 12, 2018 1 hour ago, Kian said: Anyway it's not recommended. @Kian in your absence, one of the WHMCS guys did recommend it - technically, it's hearsay, but it's never been denied... Quote I raised a support ticket and they said I need to change all fields in the database from DECIMAL(10,2) to DECIMAL(12,2) (including tblinvoices and tblinvoiceitems). Now the problem is fixed. if support are now suggesting it in support tickets, i'd label that as "recommended"! anyway, it's not technically difficult to do - i've replied in numerous threads about it over the years explaining the changes required (including the one above)... just backup the database before making the changes. 25 minutes ago, gfserver999 said: I hope in any further upgrade, WHMCS developers keep in mind that in many countries is needed, 14,2 this would be a simple change for them to make, but i'd agree with Kian and not expect it any time soon. 26 minutes ago, Kian said: It's almost 10 years that I'm using WHMCS and all fields have always been 10,2 there's a 5-year old feature request to get tax rates changed from 2 to 3 decimal places (hardly a radical suggestion), it's got 45 votes and still nothing... if you want something doing with WHMCS, do it yourself... not the way it should be, just the way things are sadly. 0 Quote Link to comment Share on other sites More sharing options...
gfserver999 Posted July 13, 2018 Share Posted July 13, 2018 16 hours ago, brian! said: @Kian in your absence, one of the WHMCS guys did recommend it - technically, it's hearsay, but it's never been denied... if support are now suggesting it in support tickets, i'd label that as "recommended"! anyway, it's not technically difficult to do - i've replied in numerous threads about it over the years explaining the changes required (including the one above)... just backup the database before making the changes. this would be a simple change for them to make, but i'd agree with Kian and not expect it any time soon. there's a 5-year old feature request to get tax rates changed from 2 to 3 decimal places (hardly a radical suggestion), it's got 45 votes and still nothing... if you want something doing with WHMCS, do it yourself... not the way it should be, just the way things are sadly. Hello!, well I think this is a must have because the system is limiting administrators in the way we manage our products and services, I could do the fields change but I won't. Thanks for your answers guys Regards 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted July 14, 2018 Share Posted July 14, 2018 20 hours ago, gfserver999 said: I could do the fields change but I won't. any particular reason why not? it could potentially be years before WHMCS fix them and it's not the most extreme of solutions to do this. 0 Quote Link to comment Share on other sites More sharing options...
gfserver999 Posted July 14, 2018 Share Posted July 14, 2018 3 hours ago, brian! said: any particular reason why not? it could potentially be years before WHMCS fix them and it's not the most extreme of solutions to do this. Hello Brian! meant for now, but I know that in anytime I will need to do it. What I don't have much clear is in what tables I have to do the change for domains prices, products prices and invoices prices and totals, so I don't want to mess it up. Also you said "Anyway it's not recommended." I saw tblpricing, tblinvoices, tblinvoiceitems. That's all? In the other hand if there is an upgrade, Will I lose those changes? Apologize my poor english Thanks! 0 Quote Link to comment Share on other sites More sharing options...
Kian Posted July 15, 2018 Share Posted July 15, 2018 You can make this change in your database but it's not an "official" procedure. As brian reported, WHMCS team said in some tickets that it can be done. Personally I got the same suggestion more than 5 years ago when I opened a ticket about it but I preferred to use a different approach. I don't like changing default tables and data types because maybe one day when WHMCS v10 is out they'll change things breaking my WHMCS and/or <removed> up the information I stored. Who knows? Your choice. 0 Quote Link to comment Share on other sites More sharing options...
brian! Posted July 15, 2018 Share Posted July 15, 2018 On 15/07/2018 at 3:12 AM, gfserver999 said: Hello Brian! meant for now, but I know that in anytime I will need to do it. oh fair enough if you just meant for now... On 15/07/2018 at 3:12 AM, gfserver999 said: What I don't have much clear is in what tables I have to do the change for domains prices, products prices and invoices prices and totals, so I don't want to mess it up. I saw tblpricing, tblinvoices, tblinvoiceitems. That's all? basically, it should be any pricing-related table that has a 'decimal' type that currently uses 10,2 - if you write a SQL query to identify these tables, it will give you the following list (from v7.6b1 but I don't think there are any new tables in v7.6).. showing database table name and the decimal field(s) in that table to be edited. Quote table_name => column_name tblaccounts => amountin tblaccounts => fees tblaccounts => amountout tblaffiliates => payamount tblaffiliates => balance tblaffiliates => withdrawn tblaffiliateshistory => amount tblaffiliatespending => amount tblaffiliateswithdrawals => amount tblbillableitems => amount tblbundles => displayprice tblclientgroups => discountpercent tblclients => credit tblcredit => amount tbldomainpricing => grace_period_fee tbldomainpricing => redemption_grace_period_fee tbldomainpricing_premium => to_amount tbldomains => firstpaymentamount tbldomains => recurringamount tblhosting => firstpaymentamount tblhosting => amount tblhostingaddons => setupfee tblhostingaddons => recurring tblinvoiceitems => amount tblinvoices => subtotal tblinvoices => credit tblinvoices => tax tblinvoices => tax2 tblinvoices => total tblinvoices => taxrate tblinvoices => taxrate2 tblorders => amount tblpricing => msetupfee tblpricing => qsetupfee tblpricing => ssetupfee tblpricing => asetupfee tblpricing => bsetupfee tblpricing => tsetupfee tblpricing => monthly tblpricing => quarterly tblpricing => semiannually tblpricing => annually tblpricing => biennially tblpricing => triennially tblproducts => affiliatepayamount tblpromotions => value tblquoteitems => unitprice tblquoteitems => discount tblquotes => subtotal tblquotes => tax1 tblquotes => tax2 tblquotes => total tblservers => monthlycost tbltax => taxrate tblupgrades => amount tblupgrades => credit_amount tblupgrades => new_recurring_amount tblupgrades => recurringchange you could probably exclude changing tbltax => taxrate from the list - unless you need to add more decimal places to a tax rate e.g 17.555%... other than that reason, I don't think it's relevant to change tbltax => taxrate. On 15/07/2018 at 3:12 AM, gfserver999 said: Also you said "Anyway it's not recommended." no Kian said that, not me! On 15/07/2018 at 3:12 AM, gfserver999 said: In the other hand if there is an upgrade, Will I lose those changes? no guarantees, but you should not. On 15/07/2018 at 2:04 PM, Kian said: I don't like changing default tables and data types because maybe one day when WHMCS v10 is out they'll change things breaking my WHMCS and/or <removed> up the information I stored. Who knows? that's a legitimate concern, but if support have been suggesting this for years (unless there are elements in there going rogue lol), then I would think this to be low-risk in the long-term, but again no guarantees... although absolutely the best solution would be for WHMCS to do this themselves... I can't for the life of me see why they haven't done it already as they must be getting support tickets from users in countries where this would be applicable to their currencies. 1 Quote Link to comment Share on other sites More sharing options...
gfserver999 Posted July 16, 2018 Share Posted July 16, 2018 On 7/15/2018 at 9:41 AM, brian! said: oh fair enough if you just meant for now... basically, it should be any pricing-related table that has a 'decimal' type that currently uses 10,2 - if you write a SQL query to identify these tables, it will give you the following list (from v7.6b1 but I don't think there are any new tables in v7.6).. showing database table name and the decimal field(s) in that table to be edited. you could probably exclude changing tbltax => taxrate from the list - unless you need to add more decimal places to a tax rate e.g 17.555%... other than that reason, I don't think it's relevant to change tbltax => taxrate. no Kian said that, not me! no guarantees, but you should not. that's a legitimate concern, but if support have been suggesting this for years (unless there are elements in there going rogue lol), then I would think this to be low-risk in the long-term, but again no guarantees... although absolutely the best solution would be for WHMCS to do this themselves... I can't for the life of me see why they haven't done it already as they must be getting support tickets from users in countries where this would be applicable to their currencies. Thanks Brian! many many thanks, I really appreciate your answer. Kind Regards! 0 Quote Link to comment Share on other sites More sharing options...
zabihi.ir Posted October 6, 2020 Share Posted October 6, 2020 you can this script: SELECT concat('ALTER TABLE ', TABLE_NAME, ' CHANGE COLUMN `', COLUMN_NAME,'` `', COLUMN_NAME,'` DECIMAL(12,0) ',if(is_nullable = 'NO', ' NOT NULL ', 'NULL '),' ',if(isnull(column_default), ' ', concat(' DEFAULT ',column_default)),';') FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_SCHEMA = 'whmcs' AND column_type IN ('decimal(10,2)') ; 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.