Importer Issues

[Wild1145]

Hey Everyone,

 

I have been playing with Blesta previews for some time, and with the constant problems of WHMCS decided that a migration to Blesta would be the best thing I could do, however Im having one big problem with the migration, the support tickets. This happened the first time I did the migration so deleted it all and started again and enabled debugging.

 

This is what I got:

importStaff
-----------------
importStaff took: 0.4547 seconds
-----------------

importClients
-----------------
importClients took: 2.9709 seconds
-----------------

importContacts
-----------------
importContacts took: 0.0121 seconds
-----------------

importTaxes
-----------------
importTaxes took: 0.0022 seconds
-----------------

importCurrencies
-----------------
importCurrencies took: 0.0083 seconds
-----------------

importInvoices
-----------------
importInvoices took: 1.6565 seconds
-----------------

importTransactions
-----------------
importTransactions took: 2.7158 seconds
-----------------

importPackages
-----------------
importPackages took: 0.6502 seconds
-----------------

importPackageOptions
-----------------
importPackageOptions took: 0.7506 seconds
-----------------

importServices
-----------------
importSupportDepartments
-----------------
importSupportTickets
-----------------
importMisc
-----------------
decrypted 155 values using WHMCS' custom algorithm
decrypt took: 0.0134 seconds
total time took: 10.5719 seconds
Array
(
[error] => Array
(
[0] => The import completed but the following errors ocurred:
[1] => importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'option_pricing_id' cannot be null on line 124
[2] => importSupportDepartments: There is already an active transaction on line 163
[3] => importSupportTickets: There is already an active transaction on line 163
[4] => importMisc: There is already an active transaction on line 163
)

)

 

If there is no way to fix it then im stuck with WHMCS :/

 

Thanks

 

Ryan

[Michael]

There will be a way however Blesta is currently closed so you may need to wait for Monday for the fix. Are you using Blesta 3.3.0? As the importer has been improved since the old one on the forums.

[Wild1145]

I am running the latest version of Blesta from the website following the instructions on the documentation. 

[Michael]

I am running the latest version of Blesta from the website following the instructions on the documentation. 

 

Ok mate thank you so you are using the new importer anyway. Cody and Tyson might pop on today or tomorrow but they are open on Monday so hopefully you can get back on track before or on Monday .

[Wild1145]

Ok mate thank you so you are using the new importer anyway. Cody and Tyson might pop on today or tomorrow but they are open on Monday so hopefully you can get back on track before or on Monday .

 

Okay, thanks for your help

[Blesta Addons]

if i remeber well , this is was already fixed ..

i assume you are using a old importer . download the new importer and try again .

[Blesta Addons]

check this thread http://www.blesta.com/forums/index.php?/topic/3426-whmcs-import-error-option-pricing-id-cannot-be-nul-migration-from-whmcs-5310-to-blesta-32/?hl=option_pricing_id

[Wild1145]

Thanks, I will follow the instructions on that thread. I was just using the built in 3.3 importer as I assumed it would be the latest. 

[Wild1145]

Has not given any errors, but its done a lot of strange things.

 

1) No tickets were imported in

2) Random clients have credit

3) The transaction list is broken

4) The Revenue reports are incredibly inaccurate

 

and thats just what I can see... 

[Paul]

Has not given any errors, but its done a lot of strange things.

 

1) No tickets were imported in

2) Random clients have credit

3) The transaction list is broken

4) The Revenue reports are incredibly inaccurate

 

and thats just what I can see... 

 

1. Check that your staff account has been added under Support > Staff, and has access to all departments. If you don't have access to all departments, it's possible the tickets were imported and you just don't have access to them.

 

2. WHMCS allows you to create credits out of "thin air", and Blesta creates a transaction for the total amount of credit a client has in an effort to balance their account.

 

3. What's broken with the transaction list?

 

4. Revenue reports are based entirely on transactions. Since you're importing from WHMCS, if Blesta has to create transactions in place of manually entered "thin air" credits that existed in WHMCS, then this will skew revenue reports. Filter out transactions of type "In house credit" to get a more accurate picture.

 

The error: Integrity constraint violation: 1048 Column 'option_pricing_id' cannot be null on line 124 sounds like there was a missing price in the data being imported. Cody or Tyson may be able to provide more information on how to identify any such records in WHMCS and correct them prior to performing a new, fresh import.

[Wild1145]

Thanks for the reply.

 

1) There are no departments imported in, its one of the problems.

 

2) Sadly this balancing has given some of my clients hundreds of pounds in free credit... Im looking for a way to only have to do minimal manual work... I had hoped I wouldnt have to manually edit each client

 

3) Its showing a lot of transactions that never happend, and doesnt seem to show any paying out.

 

4) I guessed it was linked to the transaction and credits issue, but still dont really have any idea on a fix for it...

[Michael]

2) Sadly this balancing has given some of my clients hundreds of pounds in free credit... Im looking for a way to only have to do minimal manual work... I had hoped I wouldnt have to manually edit each client

 

 

Shouldn't have used WHMCS before haha nah it's just WHMCS doesn't know how to do accounting. They also pay two invoices with another invoice, which doesn't help. The best way I would suggest is try and sort them out on WHMCS before importing.

[Paul]

 

 

[1] => importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'option_pricing_id' cannot be null on line 124

[2] => importSupportDepartments: There is already an active transaction on line 163

[3] => importSupportTickets: There is already an active transaction on line 163

[4] => importMisc: There is already an active transaction on line 163

 

 

Thanks for the reply.

 

1) There are no departments imported in, its one of the problems.

 

2) Sadly this balancing has given some of my clients hundreds of pounds in free credit... Im looking for a way to only have to do minimal manual work... I had hoped I wouldnt have to manually edit each client

 

3) Its showing a lot of transactions that never happend, and doesnt seem to show any paying out.

 

4) I guessed it was linked to the transaction and credits issue, but still dont really have any idea on a fix for it...

 

 

Ok, I think your support department/ticket issue is related to a transaction lock, which may be because of the missing pricing option in [1] above. The first step would be to fix that and try a new fresh import, things will probably look much better at that point. The importer is trying to import a null price, which means that price is likely null in whmcs and Blesta requires a value. I'll ask Cody or Tyson to take a look at the error and see if they can provide instructions for identifying the record that's causing the issue.

[Cody]

 

importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'option_pricing_id' cannot be null on line 124

 

This is the result of the migrator not being able to find the pricing ID for the configurable option imported from WHMCS, when attempting to add the configurable option for a service.

 

This could mean that either:

 

• The service has a config option set that doesn't exist

  SELECT tblhostingconfigoptions.* FROM tblhostingconfigoptions
  INNER JOIN tblhosting ON tblhosting.id=tblhostingconfigoptions.relid
  LEFT JOIN tblproductconfigoptions ON tblproductconfigoptions.id=tblhostingconfigoptions.configid
  LEFT JOIN tblproductconfigoptionssub ON tblproductconfigoptionssub.id=tblhostingconfigoptions.optionid
  WHERE tblproductconfigoptions.id IS NULL OR tblproductconfigoptionssub.id IS NULL;
  

• The config option exists, but the pricing doesn't

  -- pricing set but no config value defined
  SELECT tblpricing.*
  FROM tblpricing
  LEFT JOIN tblproductconfigoptionssub ON tblproductconfigoptionssub.id=tblpricing.relid
  WHERE tblpricing.type='configoptions' AND tblproductconfigoptionssub.id IS NULL;
  

• Pricing exists, but the config option doesn't

  -- config value set but no pricing defined for one or more currencies
  SELECT tblproductconfigoptionssub.*, tblcurrencies.code AS currency_code, tblcurrencies.id AS currency_id
  FROM tblcurrencies
  JOIN tblproductconfigoptionssub
  LEFT JOIN tblpricing ON tblpricing.relid=tblproductconfigoptionssub.id AND tblpricing.type='configoptions' AND tblcurrencies.id=tblpricing.currency
  WHERE tblpricing.id IS NULL;