Changing package price for multiple customers

[kagato]

Hi,

 

This is a bit long-winded, but it's a messy problem.

(I'll quote the background info, to separate it from the problem as it now stands)

We've been using ClientExec (ptui!) previously.

The prices for our packages for new customers went up, and ClientExec was adjusted... but come the next billing cycle, we found out changing the package price affected pricing for all customers, not just new purchases from that point on.

 

Correcting this within ClientExec looked too painful to be worthwhile (we have lots of packages), so I've migrated across to WHMCS -- which we planned to do anyway. (ClientExec is still live for now, until the WHMCS setup is ready.)

 

The ClientExec import worked well, no complaints there.

 

The intended solution was to:

• Move the packages (that had been purchased) to a hidden category, so existing customers kept the package link
  
• Create a duplicate package to replace it, for new customers
  
• Reprice the original packages to the old price
  
• Change the new customers created since the price change over to the new packages.

(We have over 60 'old' customers and less than 20 'new' customers, so this seemed to involve less work)

 

So that's all been done. (phew)

Only now, I check the customer records, and even though they all now point to the 'original' plans (which now have the old price), their products' pricing still have the new price!

 

If I understand the situation right, WHMCS works the way we'd thought ClientExec would work, and each client keeps the pricing they had when purchasing. Which would have been fine, if we'd been using WHMCS before the price change...

 

So what's the best solution here?

 

I now have 60+ customers assigned to the correct plan, but with the wrong pricing; and ~20 customers assigned to the wrong plan, but with the correct pricing.

 

Is there some sort of batch adjustment I can do (for either group, if not both), so I don't have to edit every single customer by hand?

[Matt]

It should be possible to do these batch changes with some MySQL update queries. For example:

 

UPDATE tblhosting SET recurringamount='6.95' WHERE billingcycle='Monthly' AND packageid='1' AND id<'50'

 

That would change everyone who signed up before client id 50 to package 1 who pays monthly to $6.95 for example.

 

If you are not sure how to use SQL then you contact me personally on matt@whmcs.com and we can discuss it furthur.

 

Matt

[kagato]

Thanks, once I knew the account prices were in tblhosting, it made working out the query pretty simple.

 

I don't expect too many other people will find themselves in the same situation, but I'll provide my solution for reference.

 

I corrected the new customers manually, so they were assigned to the correct packages (that was the long part), then I just had to run the following query to copy the price from the package into the customer's account:

update tblhosting, tblproducts
set tblhosting.amount = tblproducts.monthly
where tblhosting.packageid = tblproducts.id;

 

That should do it.

 

I'm assuming 'amount' is the recurring charge (as opposed to 'firstpaymentamount').

 

All our customers were monthly, so the query would need amending for different circumstances.