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

[Cody]

I just found an issue with invoices.

Apparently Blesta thinks an invoice is past due when it was marked as Refunded with WHMCS.

 

I was looking at my past due invoices in Blesta and noticed a couple of odd ones.  I looked them up in WHMCS and they were marked as Refunded.

 

The problem I see is that even a Paid invoice that was partially refunded is marked as Refunded.  So you can't just import refunded invoices as voided or paid, you have to look at each invoice and make that determination.

 

How do you expect such a case to be handled? What does it mean to "refund" an invoice? Is that equivalent to voiding it?

[Michael]

How do you expect such a case to be handled? What does it mean to "refund" an invoice? Is that equivalent to voiding it?

Yeah I would say it was voided if we refunded it mate .

[Cody]

Ok, here we go. Clean import, from the ground up (using b5). Guess what? We're back to all errors , once again

 

The import completed but the following errors ocurred:
importClients: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-40' for key 'PRIMARY' 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

 

What do you have in your users table and clients tables before running the import?

 

SELECT * FROM users

 

Should only have 1 record.

 

SELECT * FROM clients

 

Should be completely empty.

 

As far as needing a fresh install every time? Not so much. A simple php / sql query could figure out if X is in the database or not, and if not, insert it. If so, move on.

 

I can assure you it's not a simple as you think.

[FRH Dave]

I can assure you it's not a simple as you think.

 

I just submitted this as a feature request, but this really does need to be addressed in the not-too-distant future.  A blank database is fine for a one-time migration, but what happens when I buy a hosting company that uses WHMCS?  I'll need to import that data and combine it with my existing Blesta data.

[tsiedsma]

How do you expect such a case to be handled? What does it mean to "refund" an invoice? Is that equivalent to voiding it?

This is going to be complicated.  I'll give you my thoughts in a bit.

 

Yeah I would say it was voided if we refunded it mate .

If you've ever used WHMCS, you know this not to be true.

[Michael]

This is going to be complicated.  I'll give you my thoughts in a bit.

 

If you've ever used WHMCS, you know this not to be true.

 

I did use WHMCS (Bought it in 2010 & used it since 2008 [Was staff at my mates]), but hardly got refunded invoices to be honest, If I did I just did it via the PayPal refund tool.

[twhiting9275]

What do you have in your users table and clients tables before running the import?

 

 

SELECT * FROM users

 

Should only have 1 record.

 

 

SELECT * FROM clients

 

Should be completely empty.

Of course it's empty. I did a fresh import. 100% clean database, didn't setup anything

[Cody]

Of course it's empty. I did a fresh import. 100% clean database, didn't setup anything

 

Try the following. Edit /plugin/import_manager/components/migrators/whmcs/whmcs_migrator.php (line 76):

 

From:

                $errors[] = $action . ": " . $e->getMessage() . " on line " . $e->getLine();

 

To:

                $errors[] = $action . ": " . $e->getMessage() . " " . $e->getTraceAsString();

 

That should shed a little more light on the issue.

[twhiting9275]

The import completed but the following errors ocurred:

importClients: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-40' for key 'PRIMARY' #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(337): Record->insert('client_values', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importClients() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

importContacts: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(390): Record->insert('contacts', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importContacts() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

importCurrencies: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(459): Record->insert('currencies', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importCurrencies() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

importInvoices: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(654): Model->query('SELECT * FROM `...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1417): Record->fetch() #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(486): WhmcsMigrator->getTaxRule(1, '0.00') #4 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importInvoices() #5 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #6 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #7 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #8 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #9 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #10 {main}

importPackages: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(654): Model->query('SELECT *, `id` ...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1617): Record->fetch() #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1555): WhmcsMigrator->installModule('generic_server', Array) #4 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(740): WhmcsMigrator->installModuleRow(Array) #5 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(767): WhmcsMigrator->importModules() #6 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importPackages() #7 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #8 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #9 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #10 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #11 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #12 {main}

importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1036): Record->insert('support_departm...', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importSupportDepartments() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1133): Record->insert('support_tickets', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importSupportTickets() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1244): Record->insert('log_emails', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importMisc() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

More verbose, but still the same problem

There are no duplicate clients or admins. Something is wrong here

[tsiedsma]

How do you expect such a case to be handled? What does it mean to "refund" an invoice? Is that equivalent to voiding it?

 

Take a look at tblaccounts.  The refundid is the id of the original transaction id that was refunded.  The amountout is the amount of the refund.

This means that an invoice can be paid in full by one transaction which is what you would expect, but another transaction could be a partial refund.

 

This happens all the time, an invoice for $100 could be paid by one transaction but then a refund of $25.00 was issued on that original payment and refunded back to their credit card or paypal account.

This could be for any reason, but the issue here is that the invoice is considered paid in full but shows a status of refunded.

 

WHMCS uses refunded for a status whether the invoice is 100% refunded or just partially refunded.

 

You're going to have to look at transactions that have a refundid and process them differently.  Same with the invoices they are tied to.

 

Here's a potential and rough work flow.

1. Is transaction a refund?
2. What is the invoice amount due
3. Is this a full refund?
   1. If yes, void the invoice
   2. If no, the invoice is paid

[tsiedsma]

Finished importing again, still no luck with any kind of an error, just a redirect back to this page with no status:

 

 

Still missing the previously mentioned items.

[Michael]

Take a look at tblaccounts.  The refundid is the id of the original transaction id that was refunded.  The amountout is the amount of the refund.

This means that an invoice can be paid in full by one transaction which is what you would expect, but another transaction could be a partial refund.

 

This happens all the time, an invoice for $100 could be paid by one transaction but then a refund of $25.00 was issued on that original payment and refunded back to their credit card or paypal account.

This could be for any reason, but the issue here is that the invoice is considered paid in full but shows a status of refunded.

 

WHMCS uses refunded for a status whether the invoice is 100% refunded or just partially refunded.

 

You're going to have to look at transactions that have a refundid and process them differently.  Same with the invoices they are tied to.

 

Here's a potential and rough work flow.

1. Is transaction a refund?
2. What is the invoice amount due
3. Is this a full refund?
   1. If yes, void the invoice
   2. If no, the invoice is paid

 

I like the yes and no options

[tsiedsma]

Try the following. Edit /plugin/import_manager/components/migrators/whmcs/whmcs_migrator.php (line 76):

 

From:

 

                $errors[] = $action . ": " . $e->getMessage() . " on line " . $e->getLine();

 

To:

 

                $errors[] = $action . ": " . $e->getMessage() . " " . $e->getTraceAsString();

 

That should shed a little more light on the issue.

 

FYI, mine is line 78.  I know this wasn't directed to me but I'll see if it makes mine work better with showing errors.

[Cody]

The import completed but the following errors ocurred:
importClients: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-40' for key 'PRIMARY' #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(337): Record->insert('client_values', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importClients() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}
importContacts: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(390): Record->insert('contacts', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importContacts() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}
importCurrencies: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(459): Record->insert('currencies', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importCurrencies() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}
importInvoices: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(654): Model->query('SELECT * FROM `...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1417): Record->fetch() #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(486): WhmcsMigrator->getTaxRule(1, '0.00') #4 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importInvoices() #5 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #6 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #7 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #8 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #9 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #10 {main}
importPackages: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(654): Model->query('SELECT *, `id` ...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1617): Record->fetch() #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1555): WhmcsMigrator->installModule('generic_server', Array) #4 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(740): WhmcsMigrator->installModuleRow(Array) #5 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(767): WhmcsMigrator->importModules() #6 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importPackages() #7 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #8 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #9 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #10 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #11 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #12 {main}
importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1036): Record->insert('support_departm...', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importSupportDepartments() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}
importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1133): Record->insert('support_tickets', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importSupportTickets() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}
importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens #0 /home/<username>/blesta2/lib/model.php(124): PDOStatement->execute(Array) #1 /home/<username>/blesta2/components/record/record.php(215): Model->query('INSERT INTO `cl...', Array) #2 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(1244): Record->insert('log_emails', Array) #3 /home/<username>/blesta2/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php(73): WhmcsMigrator->importMisc() #4 /home/<username>/blesta2/plugins/import_manager/models/import_manager_importer.php(113): WhmcsMigrator->import() #5 /home/<username>/blesta2/plugins/import_manager/controllers/admin_manage_plugin.php(110): ImportManagerImporter->runMigrator('whmcs', '5.2', Array) #6 /home/<username>/blesta2/app/controllers/admin_company_plugins.php(104): AdminManagePlugin->import() #7 /home/<username>/blesta2/lib/dispatcher.php(111): AdminCompanyPlugins->manage() #8 /home/<username>/blesta2/index.php(21): Dispatcher::dispatch('/index.php/admi...') #9 {main}

More verbose, but still the same problem

There are no duplicate clients or admins. Something is wrong here

 

The issue you have is related to custom client fields. Somehow you have a custom field assigned to a client multiple times in WHMCS (or your client_values table in Blesta is not empty).

 

Try changing line 334 from:

 

                $this->local->insert("client_values", $vars);

 

to:

                $this->local->duplicate("value", "=", $vars['value'])->insert("client_values", $vars);

[FRH Dave]

Weighing in on refunds:

 

Usually when we refund an invoice, it's a courtesy because a customer forgot to do something important.  Maybe it's a customer with a dozen VPSes who forgot to cancel one -- we can make a one-time exception to the TOS and issue a refund.  Sometimes we'll get double payments on invoices, especially with annual packages.  A customer gets the renewal notice, forgets they created a PayPal subscription, and pays their $1500 for a year of service ... and then their subscription goes through and pays again.  We'll refund the second transaction, but in this case, the invoice remains paid in full.

 

I cringe saying this.  It's just a bad practice and a sloppy hack fix.  But it might be best to just take WHMCS's word on the state of the invoice.  I think WHMCS considers a refunded invoice as "not due", but who knows.  WHMCS is not good with numbers.

[Cody]

Take a look at tblaccounts.  The refundid is the id of the original transaction id that was refunded.  The amountout is the amount of the refund.

This means that an invoice can be paid in full by one transaction which is what you would expect, but another transaction could be a partial refund.

 

There's no link in WHMCS between the refunded transactions and the original. Refunds appear to be handled simply as negative credits, unless I'm mistaken and WHMCS only uses them for display purposes, not for accounting?

 

This happens all the time, an invoice for $100 could be paid by one transaction but then a refund of $25.00 was issued on that original payment and refunded back to their credit card or paypal account.

This could be for any reason, but the issue here is that the invoice is considered paid in full but shows a status of refunded.

 

I'm seeing something different. Considering the following scenario:

 

An invoice is due for $100. The client pays $50, then $25 is refunded. The new due amount for the invoice is $75. The invoice is still considered unpaid.

 

WHMCS uses refunded for a status whether the invoice is 100% refunded or just partially refunded.

 

You're going to have to look at transactions that have a refundid and process them differently.  Same with the invoices they are tied to.

 

Here's a potential and rough work flow.

1. Is transaction a refund?
2. What is the invoice amount due
3. Is this a full refund?
   1. If yes, void the invoice
   2. If no, the invoice is paid

 

It would appear to me that if an invoice is "refunded" that means it's considered paid in full. The problem with that there may not be any transactions that make that a true statement.

 

Moreover, taking my example above. If I refund the additional $25, the invoice is now considered "Refunded" even though it still has $100 due. That makes no sense to me.

[tsiedsma]

There's no link in WHMCS between the refunded transactions and the original. Refunds appear to be handled simply as negative credits, unless I'm mistaken and WHMCS only uses them for display purposes, not for accounting?

This is not true, the refundid is the id of the original transaction. When you refund a payment either partially or full in WHMCS, it is always tied to a payment transaction.

 

 

I'm seeing something different. Considering the following scenario:

 

An invoice is due for $100. The client pays $50, then $25 is refunded. The new due amount for the invoice is $75. The invoice is still considered unpaid.

I don't know if it will let you issue a refund on an unpaid invoice (not paid in full).

I've only ever issued a refund if a customer needed an addon removed after paying the invoice, or if there was a double payment on the invoice.

 

 

It would appear to me that if an invoice is "refunded" that means it's considered paid in full. The problem with that there may not be any transactions that make that a true statement.

 

Moreover, taking my example above. If I refund the additional $25, the invoice is now considered "Refunded" even though it still has $100 due. That makes no sense to me.

Because I believe that you cannot refund on an unpaid invoice, this may be true. The problem is obviously that a refunded invoice could be considered cancelled (voided) or paid in full since the original payment paid the invoice but part was refunded for whatever reason.

It's messy yes, but if you don't handle refunded invoices and I import my clients, a bunch of them are going to get late notices and I'm going to be very confused.

Any thoughts on my other issues? Why is the importer redirecting when completed?

[twhiting9275]

The issue you have is related to custom client fields. Somehow you have a custom field assigned to a client multiple times in WHMCS (or your client_values table in Blesta is not empty).

 

Exactly what it was.  Removed all custom client fields, and importing (again). Sadly, this is taking way , way too long. With as few clients as I have in the system (150 clients, 1100 invoices, only 2300 tickets), there's no reason this should be taking over 10 minutes to do here. This needs to be cleaned up massively, as it's triggering 500's every time.

 

[Tue Oct 29 19:11:25 2013] [error] [client ] Script timed out before returning headers: index.php, referer: http:/domain/index.php/admin/settings/company/plugins/manage/7/whmcs/5.2/?action=import
[Tue Oct 29 19:16:25 2013] [error] [client ] File does not exist: /home/<user>/blesta2/500.shtml, referer: http:/domain/index.php/admin/settings/company/plugins/manage/7/whmcs/5.2/?action=import

[tsiedsma]

 

Exactly what it was.  Removed all custom client fields, and importing (again). Sadly, this is taking way , way too long. With as few clients as I have in the system (150 clients, 1100 invoices, only 2300 tickets), there's no reason this should be taking over 10 minutes to do here. This needs to be cleaned up massively, as it's triggering 500's every time.

 

[Tue Oct 29 19:11:25 2013] [error] [client ] Script timed out before returning headers: index.php, referer: http:/domain/index.php/admin/settings/company/plugins/manage/7/whmcs/5.2/?action=import
[Tue Oct 29 19:16:25 2013] [error] [client ] File does not exist: /home/<user>/blesta2/500.shtml, referer: http:/domain/index.php/admin/settings/company/plugins/manage/7/whmcs/5.2/?action=import

 

You need to change your php settings, specifically max_execution_time, set it to a crazy large number.

Mine takes over 2 hours to complete, I am writing a very long query to prune my WHMCS database so that I can have a smaller test import using real client data. Deleting all but 2,000 clients.

Writing this query really has me going WTF??? Why do some WHMCS tables use clientid while others use userid? This is such a cluster...

[twhiting9275]

You need to change your php settings, specifically max_execution_time, set it to a crazy large number.

I hate to say it, but no I don't need to do this.

I shouldn't have to set an insecure setting like this.

Re read what I posted. There's  very little data to be migrated over, we're talking < 15,000 rows/fields all total. There's no reason this should be taking as long as it is

[tsiedsma]

I hate to say it, but no I don't need to do this.

I shouldn't have to set an insecure setting like this.

Re read what I posted. There's  very little data to be migrated over, we're talking < 15,000 rows/fields all total. There's no reason this should be taking as long as it is

There is a lot going on behind the scenes, take a look at the code. Also, max_execution_time is not a security concern, although leaving the setting high on a production server can cause issues if a script doesn't stop executing. Making the exception for a script like this is exactly the right time to do it.

Since you are convinced that this script can be better and faster, why not put your skills to use and help Cody improve the importer if you know it can be done more efficiently. I'm sure he'd be grateful, I know I'd love to not take 2 hours on an import only to have it fail and have to try it again.

[twhiting9275]

I don't work for blesta, I'm not going to contribute anything. There's nothing in it for me.

As far as 'behind the scenes', clearly there's too much going on back there when it takes more than 10 minutes to import a database that is barely 20 meg

[Michael]

I don't work for blesta, I'm not going to contribute anything. There's nothing in it for me.

As far as 'behind the scenes', clearly there's too much going on back there when it takes more than 10 minutes to import a database that is barely 20 meg

 

Nothing in for you? Well for starters you'd be moved over faster, if everyone thought the way you did, there wouldn't be any plugins / modules, we are "a community" they don't have to make the script work for you, it works for 90% of people. They are doing it for you because they want to help you.

[tsiedsma]

I think one of the issues with the importer taking so long has to do with the transactions.

It appears to pull them all over, then loop through each one and apply payments to invoices.

I monitored mysql and noticed that this took a significant amount of time as temp tables were created and queries performed.

Is there anyway to simplify or dumb down this process? It seems like there are a ton of queries for each transaction. After reducing my test database for WHMCS to 2,000 clients, it still took over an hour which tells me my initial import was dying after 2 and a half hours before ever getting to the packages.

[Ken]

Nothing in it for you? Well for starters you'd be moved over faster, if everyone thought the way you did, there wouldn't be any plugins /modules, we are "a community" they don't have to make the script work for you, it works for 90% of people.  They are doing it for you because they want to help you.

 

 

Not to mention he wouldn't be on the internet right now.