Can't get Bulk Pricing Updater to work

[aziom 0]

I've just changed my domain name pricing and I'm trying to update the recurring amounts on domain names.

 

I used the Bulk Pricing Updater but I can not get it to work. This is what I have selected:

 

Product: Domain - .com.au

Status: Active, Suspended

Billing Cycle: Domain: 2 year

Currency: AUD

Current Price:

New Recurring Price: 33.00

 

The results I get is:

 

Conditions

Statuses: 'Active','Suspended'

Billing Cycles: 2

Pricing Update Results

Updated Domains with TLD 0 - 0 Affected

 

All my pricing is on AUD and I have many .com.au domains.

 

What am I doing wrong? Or is there is bug in the updater?

 

Thanks

[harrip 0]

Same problem here. Does not appear to be working with 5.2.7

[aziom 0]

I'm using version 5.2.7 as well.

 

I've manually updated the recurring pricing.

[Chris74 11]

I can confirm that this is broken :-(

 

Would someone be kind enough to offer the mysql syntax to update a specific tld price for all customers if the renewal period is set to one year?

[aziom 0]

Would someone be kind enough to offer the mysql syntax to update a specific tld price for all customers if the renewal period is set to one year?

 

I haven't worked out the mysql syntax but I have about 100 domain names and this is what I did:

 

1. Make the changes to the domain name pricing first in "Setup > Products/Services > Domain Pricing".

2. Go to "Clients > Domain Registrations" and select a domain name.

3. Click on the checkbox next to "Auto Recalculate on Save" and click on "Save Changes".

4. Repeat step 3 for all domain names where the price has changed.

 

I have submitted a ticket on this issue to WHMCS Support. Hopefully they will fix it with the next update.

[cemoka 0]

I have same problem.

[WHMCS John 242]

Hi

As a workaround you could do this on a per-client basis using the mass update tool: http://docs.whmcs.com/Products_Management#Mass_Updating_Services.2FAddons.2FDomains

 

Or the SQL query would be:

 

UPDATE tbldomains SET recurringamount = "x.xx" WHERE domain like ".com" AND registrationperiod = "1";

 

Replace x.xx with the new price, .com with the TLD in question and "1" with the number of years the domain has been registered.

[airgead 0]

Hi,

 

It appears that this tool has been broken for at least two releases. Can you give any indication of when it is likely to be fixed? While you have provided two work-arounds, neither of them is ideal.

 

Thanks.

[imaticon 13]

Hi

As a workaround you could do this on a per-client basis using the mass update tool: http://docs.whmcs.com/Products_Management#Mass_Updating_Services.2FAddons.2FDomains

 

Or the SQL query would be:

 

UPDATE tbldomains SET recurringamount = "x.xx" WHERE domain like ".com" AND registrationperiod = "1";

 

Replace x.xx with the new price, .com with the TLD in question and "1" with the number of years the domain has been registered.

 

I suggest to place % in front of .com, otherwise, .com.au domains will be updated also:

 

UPDATE tbldomains SET recurringamount = "x.xx" WHERE domain like "%.com" AND registrationperiod = "1";

 

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

 

Regards,

Marco

[Juanzo 2]

I suggest to place % in front of .com, otherwise, .com.au domains will be updated also:

 

UPDATE tbldomains SET recurringamount = "x.xx" WHERE domain like "%.com" AND registrationperiod = "1";

 

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

 

Mind sharing the syntax to only update domains which pricing or currency is X? Hopefully the bulk pricing addon is fixed soon so we avoid messing with MySQL.

 

EDIT: Maybe there's a way via SQL to make sure all domain pricing match the current pricing configured at Domain Pricing? That way we should only care to have updated prices and all client's domains are updated.

Edited August 29, 2013 by Juanzo

[sahostking 1]

Hi

As a workaround you could do this on a per-client basis using the mass update tool: http://docs.whmcs.com/Products_Management#Mass_Updating_Services.2FAddons.2FDomains

 

Or the SQL query would be:

 

UPDATE tbldomains SET recurringamount = "x.xx" WHERE domain like ".com" AND registrationperiod = "1";

 

Replace x.xx with the new price, .com with the TLD in question and "1" with the number of years the domain has been registered.

 

I had the same issue though used this technique until the next version comes out with fix.

Thanks for the help.

[stormy 13]

So this is still not fixed? It's definitely not working for me...

[Reza 0]

Running 5.2.15 and still facing same issue!

[9DollarDomains 1]

Yes, I'm trying to update my domain prices and this tool isn't working for me either. It always says 'Updated Domains with TLD 0 - 0 Affected' and no change to domain pricing.

[Beekingo 1]

Im trying to do this and still not working.

[9DollarDomains 1]

Mind sharing the syntax to only update domains which pricing or currency is X?

 

This is part of your answer.

 

UPDATE tbldomains SET recurringamount = "x.xx" WHERE recurringamount = "y.yy" AND domain like "%.com" AND registrationperiod = "1";

 

That'll set pricing to $x.xx, but only on domains that are currently $y.yy, that are *.com and that are a 1 year renewal cycle.

 

I'm working on the Currency thing - it's a bit more complicated because the domain record itself doesn't know what currency it is - that info is on the client record - so the Query needs to take the clientid from the domain, and lookup the tblclients.id.currency.

 

Maybe there's a way via SQL to make sure all domain pricing match the current pricing configured at Domain Pricing? That way we should only care to have updated prices and all client's domains are updated.

I've asked for that exact thing - they need to fix the Bulk Pricing Updater addon, but ALSO they should have an 'update all now' type of button on the Price Edit screens for products/domains. That way, if you edited your .net pricing, you could click right there and have the .net prices updated for everyone right there. Edited December 27, 2013 by 9DollarDomains

[stormy 13]

I don't understand why WHMCS doesn't fix this, or release the module unencrypted so we can fix it ourselves.

 

Can anybody share some SQL to change hosting prices?

[9DollarDomains 1]

I don't understand why WHMCS doesn't fix this...

Stormy, I did talk to them a couple days ago - they say that they HAVE been able to duplicate this, and that a case# has been opened, and that they are going to fix this shortly. This is what they said:

 

I've opened case #3019 with our developers in order to have this resolved for future releases.

This has been scheduled for resolution in the 5.3 series of WHMCS.

Mind sharing the syntax to only update domains which pricing or currency is X?

 

Here are some examples of manual SQL Queries that should work as a work around until this is fixed by WHMCS.

 

1) This will set all you one year .com domains to renew at $12.99

 

UPDATE tbldomains SET recurringamount = "12.99" WHERE domain like "%.com" AND registrationperiod = "1";

 

2) This will set all your one year .net domains that are currently at $11.97, to a new renewal price of $13.49

 

UPDATE tbldomains SET recurringamount = "13.49" WHERE recurringamount = "11.97" AND domain like "%.net" AND registrationperiod = "1";

 

Juanzo, I don't know of an easy way to limit this to just certain currencies - that info is kinda spread across the tbldomains and the tblclients and the tblcurrencies tables, so I think this would be a complex 'join' query, that would take more brain cell's than I have - especially if WHMCS does in fact have a fix for the Bulk Pricing Updater on the way very shortly. However, at least for the time being, these examples should hopefully help folks out.

[dpatricio 1]

Insted of touching database, which is dangerous, check this module instead:

http://www.whmcs.com/appstore/2590/Prices-AutoCalc.html

[dpatricio 1]

There is a new version 1.2 of "Prices AutoCalc" addon, which corrects some bugs and add some features.