Jump to content

Search the Community

Showing results for tags 'prices auto update'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


WHMCS.Community

  • The latest WHMCS Release
    • WHMCS 8.2 Release Discussion Board
  • WHMCS Beta Program
    • WHMCS Mobile App Technical Preview Feedback
  • The Latest from the WHMCS Team
    • News, Announcements & Blogs from WHMCS
  • WHMCS.Community
    • Community Announcements
    • Introduce Yourself
  • Using WHMCS
    • Pre-Sales Questions
    • Admin & Configuration Questions
    • Installation, Upgrade, and Import Support
    • Using WHMCS
    • Troubleshooting Issues
    • Vendor Discussions
  • WHMCS Showcase
    • Showcase Your Site
    • Share Your Best Practices & Tips
  • Developing & Extending WHMCS
    • Third Party Add-ons
    • Service Offers & Requests
    • Developer Corner
    • Building Modules
    • Share Ideas for WHMCS Modules
  • Community Competitions
    • Competitions
  • General Discussions
    • General Discussion
  • General Feedback & Assistance
    • Feedback
    • WHMCS.Community Tips & Tricks
  • Third Party Developers's Topics
  • Turkish International Discussions's Topics
  • Russian International Discussions's Topics
  • Spanish International Discussions's Topics
  • Portuguese International Discussions's Topics
  • French International Discussions's Topics
  • Italian International Discussions's Topics
  • German International Discussions's Topics
  • WHMCS Brasil's Topics
  • WHMCS Brasil's Tópicos
  • ModulesGarden Club's Topics
  • Hungarian International Discussions's Segítség
  • ThemeMetro Club's Topics
  • WHMCS Services Club's Topics
  • SwiftModders Club's Topics
  • WHMCS Global Services Club's Topics
  • Katamaze's Free Scripts
  • Katamaze's Modules
  • Zomex Club's Topics
  • 0100Dev Club's Topics

WHMCS Version

  • V8.1.x Hotfixes
  • V8.0.x Hotfixes
  • V7.10.x Hotfixes
  • V7.9.x Hotfixes

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


About Me

Found 2 results

  1. 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))) end 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!
  2. Hello I am not sure if this is specific for v7.10 but will post here. I have a client with addon assign, and want to change plan\upgrade. But it looks when I change plan, prices will not Recalculate automatically. Does it expected to work in that way? Steps reproduced: Go to client, with previous assigned addon, click to edit it as picture shown. Change predefined addon from 5 GB to 1 GB for example, later click save. Does the price will update later on automation? On my marketconnect I have it with different prices.
×
×
  • 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