Manually Fixing Duplicate Orders In The Database

[WebGuru]

Ok, I know I messed up, I know what I did wrong, and I know how to handle this in the future, but it took two months to find out there was a problem, so for the present, I'd like to fix the problem without starting over.

 

This is all regarding one client, which should have one order, one invoice, and one hosting account.

 

If you want to skip the story... scroll down.

 

Two months ago...

 

Since I'm still learning all the in's and out's of WHMCS, I sometimes will set things up with my own email and generate invoices just to make sure everything works as expected, emails look good, etc.

 

I created an account, no problems there. I placed an order and generated the invoice, activate the hosting, all is good. I deleted the order and the invoice, and if the hosting account was not automatically deleted then I deleted that too (I don't remember). Now I change the account's email address to the client's email address and repeat the process, the invoice is sent to the client and I activate the hosting account - all is good so far.

 

I knew the client was mailing a check the next week and that it could be up to two weeks before I received the payment, but I trust the client and wanted to get started. So in addition to activating the hosting account, I used the Override Auto-Suspend to not suspend the account until the end of the next month, May 31st, plenty of extra time.

 

Then the client says the billing name needs to be addressed to the company name instead of the client's name. So I delete the invoice, make the changes, change the contact info back to my email just to make sure everything still works as expected after deleting the invoice, and place a new order and activate hosting. Everything appears fine, so I delete the invoice again, change the email address back, and place the order, now for the fourth time.

 

Here is where I messed up - the last two times I only deleted the invoices, I didn't cancel the invoices, and I did not delete the orders. I'm pretty sure I did not delete the hosting account either (if I did I don't think I'd have this problem right now).

 

Up to that point, I am not aware of any problems. The payment comes in, I mark the invoice paid, everything is finished, I'm done.

 

Then the end of the month comes... yesterday... and this morning the hosting was suspended with the reason being it was overdue on payment. I quickly re-activate the account in WHM, apologize to the client, and start digging in WHMCS to find out what happened.

 

If you skipped the story, start here.

 

So, after some digging, I find out I have: four orders... 1st one cancelled and the other three active; one invoice associated with the 4th order (the other three were deleted); four invoice items... one for each invoice; and one hosting account... associated with the 2nd order.

 

If you can follow all that...

 

I don't want to delete everything and start over with a new order because that would be complicated to get all the dates right (if getting the date's to match the originals is even possible) and I don't want the hosting account on WHM to be changed at all (I've even disconnected WHMCS from WHM for the moment).

 

Could I simply make everything match up in the database? Can I simply delete the other orders and invoice items in the database? If so, I see four tables where things would need to be changed: orders, invoices, invoice items, and hosting. Am I missing anything?

 

Where are the transaction details stored? The invoice I marked paid says the payment method is PayPal, but the transaction payment method says Mail In Payment. I assume the invoice payment method is just the default method and doesn't reflect how the invoice was paid. Is that correct?

 

If that won't work, what is the best course of action?

[WHMCS John]

Hi,

Assuming the client has only one hosting account, you only need one active service in WHMCS. So cancel all but one (just change the status dropdown). You should probably cancel the ones without any paid invoices, and keep the one with the paid invoice active.

 

Then adjust the Next Due Date to the date at which their next payment is due.

 

Done.

[WebGuru]

Hi John, thanks for your reply. The only problem I see with canceling the other active services (assuming that means the orders) is that one order has the paid invoice and another order has the active hosting account.

 

Just for clarity, the client only has one product in the Products dropdown on the Products/Services tab.

 

If I cancel the order associated with the hosting account, isn't that going to cancel the hosting account product? If I cancel the order with the invoice, isn't that going to delete the record of payment (in which case I would need to regenerate the invoice and mark it paid)?

 

If editing the database is possible, I'd like to do that so that nothing needs to be re-done.

[WHMCS John]

Hi,

Don't cancel the order, just change the status of the superfluous services to cancelled. Do this using the "Status" dropdown under their Products/Services tab.

[WebGuru]

Thanks John, but...

 

"Just for clarity, the client only has one product in the Products dropdown on the Products/Services tab."

 

There is only one service, attached to the 2nd order, which has no invoice. The fourth order has the invoice, which has been paid. Obviously I can delete the first and third order, but what do I do to keep the paid invoice from the 4th order and the active hosting service from the second order?

[WHMCS John]

Hi,

I'm afraid I don't follow how there could be 4 orders but only one service? Some screenshots would probably help us understand exactly what's going on here.

[WebGuru]

Here are screenshots of the WHMCS pages...

 

Manage Orders page:

 

The Last Invoice (the other three were deleted):

 

The Products/Services tab's Products drop-down menu (there is only one product):

 

I can only attach six images, and there are four database tables, so I'll put those in the next post...

[WebGuru]

...and the database tables...

 

Orders:

[ATTACH=CONFIG]6667[/ATTACH]

 

Invoices:

[ATTACH=CONFIG]6671[/ATTACH]

 

Invoice Items:

[ATTACH=CONFIG]6674[/ATTACH]

 

Hosting:

[WHMCS Ryan]

I think it would best if you could open a ticket and give access to one of our Techs to your install so we can investigate this further. Each order should have created a new service if the order was created from within the client profile, if not then there would be separate clients for each order. If you use the link in my signature to submit a ticket we should be able to assist you further.

 

If you could reply back with the ticket ID so I can post the outcome in this thread, I would appreciate it.

 

--Thanks

[WebGuru]

I opened a ticket, #UKY-349294.

 

The orders were all added using the Add New Order link in the Orders sub-menu. Then I selected the client, set the order status to Active, and set the Product/Service and domain. Each order was for the same domain, same login name, and each invoice was deleted before placing the next order.