Jump to content

SQL to keep tbldomains pricing in sync

Recommended Posts

Posted (edited)

If you use a supported registrar, WHMCS' Registrar TLD Sync can keep your domain pricing up to date without a lot of effort. However, it does NOT sync the pricing to your existing clients' domains. The Bulk Pricing Updater isn't suited to this task either, requiring many steps to update pricing for different TLDs, billing cycles and domain addons.

I put together a SQL query that will set the recurring amount of all client domain names in tbldomains with your current domain pricing, and figured I'd share it for those whom it might benefit.

This was written for WHMCS 8.1.3 and MariaDB 10.3. I recommend testing it on a copy of your data, and I make no warranties regarding it's suitability to your particular environment, nor claim that it is free of error. That said, it works well for me.

Here are some suggestions for testing this on a copy of your data, assuming you have access to the server shell as root:

1. Export a copy of the relevant tables and import into a work database:

mysqldump <your-whmcs-db-name> tblclients tbldomains tbldomainpricing tblpricing > ~/workdb.sql
mysqladmin create workdb
mysql workdb < ~/workdb.sql

2. Get an idea of the current recurring amount for all your active domains (to give you something to compare against after running the sql script):

mysql workdb
MariaDB [workdb]> select sum(recurringamount) from tbldomains where status = 'Active';
| sum(recurringamount) |
|             50029.21 |
1 row in set (0.000 sec)

3. Create a file to hold the SQL that you can run against the database:

nano ~/domain_pricing_sync.sql

The SQL to put in the file:

SET @idp = (select ssetupfee from tblpricing where type = 'domainaddons');
SET @dns = (select msetupfee from tblpricing where type = 'domainaddons');
SET @fwd = (select qsetupfee from tblpricing where type = 'domainaddons');

update	tbldomains d, 
	tbldomainpricing dp, 
	tblpricing p, 
	tblclients c 
	set d.recurringamount = 
		case d.registrationperiod
			when 1 then p.msetupfee + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 2 then p.qsetupfee + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 3 then p.ssetupfee + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 4 then p.asetupfee + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 5 then p.bsetupfee + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 6 then p.monthly + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 7 then p.quarterly + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 8 then p.semiannually + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
			when 9 then p.annually + (d.registrationperiod * ((d.idprotection * @idp) + (d.dnsmanagement * @dns) + (d.emailforwarding * @fwd)))
	where d.is_premium != 1
	and dp.extension = right(d.domain, length(d.domain) - locate('.', d.domain) + 1)
	and p.type = 'domainrenew'
	and p.relid = dp.id
	and c.id = d.userid
	and p.currency = c.currency;

4. Save the file and the run it against your work database:

mysql workdb < ~/domain_pricing_sync.sql

5. Repeat step 2 and see what your total looks like after the update.

Once you're comfortable the SQL works for you,  you can run it against your production database manually after you update domain pricing, or even schedule it via cron if you want to automatically keep pricing in sync. There probably isn't a good reason for setting it up to run via cron, unless like me you don't like the way WHMCS allows a user to register or manually renew a domain for X number of years, and then assumes the next renewal should be for that same number of years. I personally run this SQL daily via cron, with this line added to the very top of the SQL:

update tbldomains set registrationperiod = 1;

This way if a client has registered or manually renewed for longer than a year, this will reset the domain to a 1 year registration/renewal with the current pricing for one year. It can certainly be argued that it's a bit of an overkill to run it daily just to keep a few clients' domains who occasionally renew for longer periods reset to 1 year, but I do it anyway. For me it takes a little under a minute to run this against 5,135 domain names.

The SQL is written to handle those who use multiple currencies (I do not), and does NOT adjust the pricing for any premium domain names that are properly set (tbldomains.is_premium = 1). It does update ALL domains regardless of status. An additional where clause to only update domains with certain statuses (i.e. where status = 'Active' or where status not in ('Expired', 'Cancelled', 'Fraud', 'Transferred Away')) can easily be added to limit the amount of records updated if you wish. This does not update any open invoices, so it's conceivable a client with an open renewal invoice will still pay an outdated price and have the domain renewed.

WHMCS doesn't store the domain extension separately in the tbldomains table, so it has to be calculated on the fly, hence "right(d.domain, length(d.domain) - locate('.', d.domain) + 1)". The assumption is that everything including and to the right of the first instance of a period in the domain name represents the extension. I can't think of a case where this would present a problem, but it's possible and something to consider before deciding to use this.

Someone who is more motivated than I could take this concept and build an addon module with more flexibility, such as nice UI providing the ability to only update selected TLDs with selected Status values or some such. Feel free to do so if you are so inclined.

Use at your own risk and test thoroughly before running against your production db!

Edited by Troy

Share this post

Link to post
Share on other sites
On 4/14/2021 at 10:12 PM, Troy said:

update tbldomains set registrationperiod = 1;

You'll want to check there are no unpaid invoices for that domain _before_ changing the period, otherwise when the invoice gets paid it only adds 1 year not the number of years they paid for.

You also want to check that the domain TLD is in 1 year increments (rather than say 2 for .gr) - really you want to set it to the min-years you've set a price for 🙂

Share this post

Link to post
Share on other sites
Posted (edited)
20 hours ago, othellotech said:

You'll want to check there are no unpaid invoices for that domain _before_ changing the period, otherwise when the invoice gets paid it only adds 1 year not the number of years they paid for.

You also want to check that the domain TLD is in 1 year increments (rather than say 2 for .gr) - really you want to set it to the min-years you've set a price for 🙂

Good points for anyone who decides to mess with the registrationperiod value. The rest of it is fine as-is if you don't, but you're right that those two possibilities should be considered before you do.

I wasn't sure if the outstanding invoice thing could be an issue, because in my case it would only apply if a client manually renewed a domain name (because of the registrationperiod being set to 1 on a daily basis right before auto-renewal invoices are generated), and I figured WHMCS wouldn't change the registrationperiod in tbldomains until the invoice was paid. However, a quick test confirmed that WHMCS does indeed update the registrationperiod when the renewal order is placed, even if the invoice is left unpaid. IMO that's bad form. It's already bad form to pre-determine that the next auto-renewal should renew for the same term as a manual renewal, but to change the registrationperiod before the invoice is paid is bad form on top of bad form.

This makes me wonder...is there a potential loophole that can be exploited by a client? Consider the following:

1. WHMCS is configured to generate auto-renewal invoices 2 weeks before expiration, with the invoice due in 7 days.
2. WHMCS generates an auto-renewal invoice for 1 year.
3. Client orders a 3 year renewal manually before the auto-renewal invoice is due, and either pays the invoice or doesn't.
4. tbldomains.registrationperiod is changed to 3 whether or not the manual renewal order invoice is paid.
5. Auto-renewal 1 year invoice is paid during the daily cron when it comes due.

It seems to me that auto-renewal invoice payment under these conditions would renew the domain name for 3 years when the client has only been charged for 1 year.

Edited by Troy

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By SD-Reg
      hello all,
      We have successfully setup WHMCS and have a few hosting accounts running well, and are now testing out domain management. We are currently configured to use eNom as the registrar, sync appears to be working, and even the pricing sync is OK. We have initiated our first domain transfer away from GoDaddy into our own WHMCS environment.
      GoDaddy side is showing "transfer pending". It's now been past the waiting period and date they gave in email communication for able to reject the transfer, so we are anticipating the transfer to now go through. In an effort to research and follow it along to see where in the process it is, our WHMCS (both as an admin and also as the user who is transferring the domain) is showing "transferred away", and eNom does not list the domain. Are there any other troubleshooting steps to check?
      I guess we were expecting eNom to possibly have a "pending incoming" or something to show what is currently in process. Then the other confusing part is that WHMCS is showing transferred away - that makes sense from the GoDaddy side (even though it's not showing completed), but as far as our WHMCS and eNom is concerned, it should be incoming, and we don't appear to have a way to investigate further.
      thanks in advance!
    • By kevinsheahan
      Is it possible to get the domain addon prices through the API? 
      GetTLDPricing does not give this information.
      Thanks in advance.
      Kevin Sheahan
    • By thisismatt
      Hey all
      On my WHMCS portal homepage beneath the domain search box I have 3 domain logos showing (ignore the sizing issue!) as per the below screenshot.

      However, when I click through to the pricing page I have all of my Spotlight TLDs visible, as per the second screenshot.

      My question is what controls which TLDs are shown on the homepage? Why is WHMCS showing only 3 (and those particular 3) on the homepage?
    • By Pieter Rubeus
      Hi WHMCS
      Can you please add domain search in the header of the admin area next to the intelligent search.
    • By saywork
      Hello every one,
      I just started my iptv business and i am trying to understand few things .
      I am using smarters addon , and when i add a reseller i have to enter the reseller domain or register with a new one.
      my question is , how does the server connects to the domain? it means that when the reseller sell product the system automatically detect that this domain sold something and connects it to the reseller user from WHMCS?
      Thanks alot,

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated