Whmcs Migrator (Beta) - Updated 2013-11-12

[tsiedsma]

So you think it should only import packages for TLDs that are assigned to a specific registrar? I wasn't sure if it was a requirement to assign a registrar to a specific TLD in order for the TLD to be orderable, but I guess it is.

I disagree that I was selling domain for different registrars.  I personally think you should only import active products and services.  There are some old expired enom domains and such.

 

I think each individual tld is a single product with 1-10 pricing configs and register / renew / transfer pricing options.

[Cody]

An updated version of the migrator is now available for download here.

This latest update should minimize the number of packages needed for registrars, as well as corrects issues with importing package pricing and 'autorelease' packages.

 

 

For users with a lot of data to import, you may benefit from changing (in /plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php):

 

        Configure::set("Whmcs.import_fetchall", false);

 

to:

 

        Configure::set("Whmcs.import_fetchall", true);

 

Also, it would be great to get feedback on execution time vs the two methods (Whmcs.import_fetchall set to true vs false). From my testing there was a 1% increase in speed when set to true, of course this method requires a lot more memory.

[FRH Dave]

This latest update should minimize the number of packages needed for registrars, as well as corrects issues with importing package pricing and 'autorelease' packages.

 

Restored my blank database and ran the import.  Huge improvement.  The packages have prices now, but many of them are incorrect.  This may be because the modules aren't importing the configurable options from WHMCS.

 

Quantity still shows as zero for the autorelease modules.  Actually, I'm fine with this.  These are legacy packages that nobody should ever be ordering, as they'll be replaced with modern ones.  Good show.

 

So a customer with a $50 VPS and a $20 control panel and a $30 management option is only being charged $50.  Coupons didn't copy over, either.

 

WHMCS must have a database field for storing the price, since admins can override the price simply by typing the number in the profile screen (see the "first payment amount" and "recurring amount" fields in the WHMCS products / services tab for a client).  Can you grab the value for the recurring amount and kludge it in as the product price?  That would take care of all the discounting / price override options we've been discussing, since the recurring amount is always the final amount after discounts, coupons, add-ons, etc.  It wouldn't be "proper", but it would work.  I could see how the client would lose that discount if they ever changed packages, but that's how out price grandfathering is going to work anyway.  Keep it till you change it.

[SkylarM]

Restored my blank database and ran the import.  Huge improvement.  The packages have prices now, but many of them are incorrect.  This may be because the modules aren't importing the configurable options from WHMCS.

 

Quantity still shows as zero for the autorelease modules.  Actually, I'm fine with this.  These are legacy packages that nobody should ever be ordering, as they'll be replaced with modern ones.  Good show.

 

So a customer with a $50 VPS and a $20 control panel and a $30 management option is only being charged $50.  Coupons didn't copy over, either.

 

WHMCS must have a database field for storing the price, since admins can override the price simply by typing the number in the profile screen (see the "first payment amount" and "recurring amount" fields in the WHMCS products / services tab for a client).  Can you grab the value for the recurring amount and kludge it in as the product price?  That would take care of all the discounting / price override options we've been discussing, since the recurring amount is always the final amount after discounts, coupons, add-ons, etc.  It wouldn't be "proper", but it would work.  I could see how the client would lose that discount if they ever changed packages, but that's how out price grandfathering is going to work anyway.  Keep it till you change it.

 

"Addons" as a PROPER addon and not a config option do not get factored into that value you're talking about. Configurable options do, but the actual product addon bit does not.

[DandyDandy]

So I've attempted to migrate my database from WHMCS to Blesta. So far am happy with Blesta but the Migrator isn't all that great.
I thought it would of had the option to manually import not entering database details and clicking "Continue"

 

Apart from having to contend with various issues, importer not connecting, timeout settings, last name fields it appears when having done a 15 minute audit on everything which has been imported there are no tickets and this is important for us that all tickets are moved since they provide a history of any problems clients have faced. The only error the importer really flaged a part from it not wanting to connect firstly but re-uploading the files fixed that it was "Please enter a last name." which on a closer inspection a test account within WHMCS didn't have a last name so droped the tables imported a backup Blesta database and started again which that time it completed fully without error but no tickets. I've tried it twice but still everything else is imported accept the tickets. Am at a lost why tickets are not being imported being it's a critical part of the system and client history. Any ideas?

P.S: How big is the database your using to test the migrator on?

[KuJoe]

I'm getting a blank page when I click on the "5.2" link in the Importer plugin. I downloaded the latest plugin (1.0.3) and the latest whmcs_migrator as well as upgraded my clean install to 3.0.5 without any luck. I also cannot find any errors anywhere on the server.

[Michael]

I'm getting a blank page when I click on the "5.2" link in the Importer plugin. I downloaded the latest plugin (1.0.3) and the latest whmcs_migrator as well as upgraded my clean install to 3.0.5 without any luck. I also cannot find any errors anywhere on the server.

 

I believe the whmcs_migrator should be in the 5.2 folder. Could be wrong though.

[KuJoe]

Tried moving it there without any change, still a blank page.

[FRH Dave]

"Addons" as a PROPER addon and not a config option do not get factored into that value you're talking about. Configurable options do, but the actual product addon bit does not.

 

I'm using configurable options.  When I look at Client > (select the client) > (locate a dedicated server on which the user has bought recurring upgrades through configurable options)  > Manage, the price listed is just the base price of the service.  Same thing on VPSes using the SolusVM module.

[FRH Dave]

After migration, I logged in as a client who has quite a few services with us.  It shows the client has having a $311.93 outstanding balance, when in reality they should only have $114.97.  Here's what it shows:

 

Invoice # -- Amount -- Paid -- Due -- Date Billed -- Date Due
3133 -- $114.97 USD -- $0.00 USD -- $114.97 USD -- Oct 16, 2013 -- Oct 26, 2013
0 - $179.99 USD -- $19.02 USD -- $160.97 USD -- Aug 05, 2012 -- Aug 15, 2012
0 -- $97.98 USD -- $72.99 USD -- $24.99 USD -- Jul 13, 2012 -- Jul 18, 2012
0 -- $59.97 USD -- $48.97 USD -- $11.00 USD -- May 21, 2012 -- May 26, 2012

 

In WHMCS, the client only has the following invoices unpaid:

 

3190 10/25/2013 11/04/2013 - $189.99 USD PayPal Unpaid

3133 10/16/2013 10/26/2013 - $114.97 USD PayPal Unpaid    

 

I'm not concerned about the invoice from 10/25, as that was generated after I pulled the database over.  Several issues:

• The old invoice #s.  I'd like to have the old WHMCS invoice # copied over for old paid invoices.  If that's not possible, it's acceptable to use "0".  But there should be a sanity check that any time an invoice has a due balance, the invoice number should not be "0".
• The $179.99 invoice from 8/5 was paid in full and on time.  The client had a $160.97 account credit, and paid $19.02 via PayPal.
• The $97.98 invoice from 7/13 was paid in full and on time.  $24.99 credit, $72.99 via PayPal.
• The $59.97 invoice from 5/12 was paid in full and on time.  $11.00 credit, $48.97 via PayPal.
• Viewing the invoice for any of the payments does not show the "partial payments" (incorrect as they are) reflected above.  It shows the entire amount is due.  If the client legitimately made a partial payment, it should show the total balance, a line item for each partial payment, and the remaining balance.

Can someone else with a similar situation check your clients for phantom invoices?  I only checked one customer, but I'm sure there are more.

 

I'm posting this here since I know you're working hard on the importer, figured it would be easiest to keep everything in one spot for now.  Feel free to relocate to the bug forum if you want.

[EidolonHost]

Man, that is some beautiful work going on here, I'm seeing.

 

My gods, what a difference. Literally, what a difference. I'm seeing active support here on the module and genuine effort in making sure the importer/migrator works.

 

WHMCS does not do this publicly, whereas you guys do. Hell, you'd be very lucky to even get any support publicly. Such a world of difference. If I wasn't set on Blesta by this point, I am now. (but I already am, so no worries, there.)

 

Fortunately for me, I don't have to worry about importing from WHMCS... but still.

[Michael]

I'm getting a blank page when I click on the "5.2" link in the Importer plugin. I downloaded the latest plugin (1.0.3) and the latest whmcs_migrator as well as upgraded my clean install to 3.0.5 without any luck. I also cannot find any errors anywhere on the server.

 

Ok can you uninstall the importer, then re-install it, then go to the plugins folder public_html/plugins/

 

and remove the import_manager

 

Download this: http://licensecart.com/public/import_manager_custom.zip

 

upload it to that folder and extract it. Then go to the import manager and click 5.2 does it come up white? If it does then it's a server issue.

 

Mine shows up as normal on my install.

[Matt]

***UPDATE***

I just queried the database and found that null country. 

 

SELECT * FROM `tblclients` WHERE customer = ""

 

 I am running the script again and it has ended yet.  I will update again, once its finished running.

 

Original post....

 

I just tried the updated import script and received the following errors.  It looks like other may have had similar issues.

 

The import completed but the following errors ocurred:
importClients: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'country' cannot be null on line 124
importContacts: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importCurrencies: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importInvoices: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importPackages: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124

[tsiedsma]

***UPDATE***

I just queried the database and found that null country. 

 

SELECT * FROM `tblclients` WHERE customer = ""

 

 I am running the script again and it has ended yet.  I will update again, once its finished running.

 

Original post....

 

I just tried the updated import script and received the following errors.  It looks like other may have had similar issues.

 

The import completed but the following errors ocurred:
importClients: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'country' cannot be null on line 124
importContacts: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importCurrencies: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importInvoices: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importPackages: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124

 

I had this issue where some client names were blank.  I just did a mass update UPDATE tblclients SET lastname = 'Smith' WHERE lastname = '';

That will work for country too,  UPDATE tblclients SET country='US' WHERE country='';

 

Edit: nevermind, I read your post wrong, you've already corrected the null country.

[DandyDandy]

Is anyone having issues with migrating tickets from WHMCS to Blesta?

Also I feel the developers should add a note about server settings when attempting to use the migrator as I had to edit the timeout settings to get the migrator to complete correctly. I also disabled mod_security since I didn't want that playing havoc with anything. I strong advise the developers to make people aware of this before they attempt any migration as it will save them time and help ensure a clean migration.

[tsiedsma]

None of my tickets came over, it didn't even enabled the support manager.

[Matt]

I am making some progress, but still have a few more bugs that i'm trying to resolve.

 

Just ran the import script again and got the following error.  I will try digging around more and find out which table contains "module_row_id".

 

The import completed but the following errors ocurred:
importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module_row_id' cannot be null on line 124
importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124
importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124

[DandyDandy]

None of my tickets came over, it didn't even enabled the support manager.

 

This needs working on pretty quick to be honest there's a growing number looking to jump ship from WHMCS I wanted to do that today but I can't without the tickets being imported. thanks

[tsiedsma]

This needs working on pretty quick to be honest there's a growing number looking to jump ship from WHMCS I wanted to do that today but I can't without the tickets being imported. thanks

Agreed!  What they need is for people like us to keep testing and finding issues so they can fix it.  As the thread title indicates, this is still in Beta.

[KuJoe]

Ok can you uninstall the importer, then re-install it, then go to the plugins folder public_html/plugins/...

 

No dice, guess I'll wait for a dev to chime in.

[tsiedsma]

No dice, guess I'll wait for a dev to chime in.

A white page generally means there was an error, check your error_log file or the Apache error log and see what it says.

[Cody]

For those of you not seeing tickets imported. Check the support_tickets table in Blesta to see if it's empty. Most likely the reason you don't see any tickets is because the migrator will import your WHMCS admin users and assign them to necessary departments (it does nothing with the user you are currently logged in as). Log out and log back in with your WHMCS admin user (that was imported).

 

Also, double check the Support Manager settings for Support Departments and ensure all of the correct staff users belong to the appropriate staff departments.

[Cody]

Restored my blank database and ran the import.  Huge improvement.  The packages have prices now, but many of them are incorrect.  This may be because the modules aren't importing the configurable options from WHMCS.

 

Quantity still shows as zero for the autorelease modules.  Actually, I'm fine with this.  These are legacy packages that nobody should ever be ordering, as they'll be replaced with modern ones.  Good show.

 

So a customer with a $50 VPS and a $20 control panel and a $30 management option is only being charged $50.  Coupons didn't copy over, either.

 

WHMCS must have a database field for storing the price, since admins can override the price simply by typing the number in the profile screen (see the "first payment amount" and "recurring amount" fields in the WHMCS products / services tab for a client).  Can you grab the value for the recurring amount and kludge it in as the product price?  That would take care of all the discounting / price override options we've been discussing, since the recurring amount is always the final amount after discounts, coupons, add-ons, etc.  It wouldn't be "proper", but it would work.  I could see how the client would lose that discount if they ever changed packages, but that's how out price grandfathering is going to work anyway.  Keep it till you change it.

 

I think your idea is a good one. I think WHMCS might store the price for each service on the service record itself. If so we could import every unique pricing used by a service as a package price, that way all service pricings remains accurate.

[DandyDandy]

For those of you not seeing tickets imported. Check the support_tickets table in Blesta to see if it's empty. Most likely the reason you don't see any tickets is because the migrator will import your WHMCS admin users and assign them to necessary departments (it does nothing with the user you are currently logged in as). Log out and log back in with your WHMCS admin user (that was imported).

 

Also, double check the Support Manager settings for Support Departments and ensure all of the correct staff users belong to the appropriate staff departments.

 

I get you now I'll give it a whirl later and report back. Would it be possible to make an offical page with all this information listed with also a link back here so people can ask questions it'll be a great help for others.

[Cody]

After migration, I logged in as a client who has quite a few services with us.  It shows the client has having a $311.93 outstanding balance, when in reality they should only have $114.97.  Here's what it shows:

 

Invoice # -- Amount -- Paid -- Due -- Date Billed -- Date Due

3133 -- $114.97 USD -- $0.00 USD -- $114.97 USD -- Oct 16, 2013 -- Oct 26, 2013

0 - $179.99 USD -- $19.02 USD -- $160.97 USD -- Aug 05, 2012 -- Aug 15, 2012

0 -- $97.98 USD -- $72.99 USD -- $24.99 USD -- Jul 13, 2012 -- Jul 18, 2012

0 -- $59.97 USD -- $48.97 USD -- $11.00 USD -- May 21, 2012 -- May 26, 2012

 

Ah, I think I know what's happened there. Should be fixed in the next update.

 

In WHMCS, the client only has the following invoices unpaid:

 

3190 10/25/2013 11/04/2013 - $189.99 USD PayPal Unpaid

3133 10/16/2013 10/26/2013 - $114.97 USD PayPal Unpaid    

 

I'm not concerned about the invoice from 10/25, as that was generated after I pulled the database over.  Several issues:

• The old invoice #s.  I'd like to have the old WHMCS invoice # copied over for old paid invoices.  If that's not possible, it's acceptable to use "0".  But there should be a sanity check that any time an invoice has a due balance, the invoice number should not be "0".
• The $179.99 invoice from 8/5 was paid in full and on time.  The client had a $160.97 account credit, and paid $19.02 via PayPal.

 

Could you send me the invoice record as well as the transactions that apply to the invoice? Use the following queries (replace INVOICEID with the proper invoice ID):

 

 

SELECT * FROM `tblinvoices` WHERE `id`='INVOICEID'

 

SELECT * FROM `tblaccounts` WHERE `invoiceid`='INVOICEID'

 

• The $97.98 invoice from 7/13 was paid in full and on time.  $24.99 credit, $72.99 via PayPal.
• The $59.97 invoice from 5/12 was paid in full and on time.  $11.00 credit, $48.97 via PayPal.

Feel free to do the same for these as well.

 

• Viewing the invoice for any of the payments does not show the "partial payments" (incorrect as they are) reflected above.  It shows the entire amount is due.  If the client legitimately made a partial payment, it should show the total balance, a line item for each partial payment, and the remaining balance.

 

Does Blesta show a partial payment applied to the invoice? If so it should be reflected in Blesta.

 

Can someone else with a similar situation check your clients for phantom invoices?  I only checked one customer, but I'm sure there are more.

 

I'm posting this here since I know you're working hard on the importer, figured it would be easiest to keep everything in one spot for now.  Feel free to relocate to the bug forum if you want.

 

This is the funny thing about WHMCS, it plain sucks at keeping track of money. An invoice can be marked as paid without any transactions being applied. Credits can be added to a client's account from thin air (no transaction). And to pay multiple invoices with a single payment requires creating new a invoice(????). It's weird, but we want to try to work around the anomalies as best we can so we really appreciate any insight into your invoices and transactions.