Import From Freeside 3.4

[will]

Hey, we're considering purchasing Blesta. We're currently using the demo, and we'd like to attempt to import our old database - a fairly sizeable Postgresql database - from our old billing software, Freeside, to Blesta. I understand that you guys don't have a script available off the shelf for Freeside yet, but I was wondering if you could provide any guidance with regards to importing that data - any and all tips would be appreciated. 

 

I'm sure there's a lot of questions that I don't know enough to ask, but for starters, should I be trying to adapt the Import Manager plugin for use with Freeside?

 

Also, do you have an ER/schema diagram for Blesta handy?

 

I assume http://www.blesta.com/2008/04/28/writing-imports/ is no longer applicable.

[Michael]

That's the importer document for 2.5, you could do that and then import over to 3.0. Or you can work on the new importers, if you know PHP have a look at their WHM** importer and see how it's done, I'm working on a BoxBilling one myself but it's a big complicated for my skills. So it's taking me a bit longer to get to grips with BoxBilling.

 

Paul, Cody and Tyson can help you though. Good luck .

[Cody]

  On 11/26/2013 at 1:25 AM, will said:

Hey, we're considering purchasing Blesta. We're currently using the demo, and we'd like to attempt to import our old database - a fairly sizeable Postgresql database - from our old billing software, Freeside, to Blesta. I understand that you guys don't have a script available off the shelf for Freeside yet, but I was wondering if you could provide any guidance with regards to importing that data - any and all tips would be appreciated.

 

Well, it's pretty simple. Clients go in the clients/users table. Staff users belong to staff/users table. Invoices go in invoices/invoice_lines/invoice_line_taxes. Everything else follows the same pattern.

 

  On 11/26/2013 at 1:25 AM, will said:

I'm sure there's a lot of questions that I don't know enough to ask, but for starters, should I be trying to adapt the Import Manager plugin for use with Freeside?

You certainly can, and I would recommend it. Though admittedly, we haven't made any documentation available on how the plugin works as we never really anticipated others would want to extend it.

 

  On 11/26/2013 at 1:25 AM, will said:

Also, do you have an ER/schema diagram for Blesta handy?

We don't release this kind of information due to typical commercial/IP reasons.

 

  On 11/26/2013 at 1:25 AM, will said:

I assume http://www.blesta.com/2008/04/28/writing-imports/ is no longer applicable.

That's for version 2.

[will]

Where is the WHM** importer located?

[Cody]

You can get it here: http://www.blesta.com/forums/index.php?/topic/960-whmcs-migrator-beta-updated-2013-11-12/

[Larry]

I am in the process of building a migration from Freeside to Blesta. I have a couple of questions on how to migrate credits. Background: When we fall short of our SLA due to an outage we issue a full or partial credit for that period for all of our customers on the affected machines.

 

Can I simply insert these existing Freside credits into the transactions, log_transactions and transactions_applied tables when I do the migration?

 

Once we are in production on Blesta and we need to issue a batch of credits, can I programatically insert the credits into the transactions and log_transactions tables and let Blesta apply them?

 

Can I create a new transaction tyoe in the transaction_types table for this purpose?

 

Thanks,

Larry Crawford

prgmr.com

[Cody]

  On 12/31/2013 at 8:19 PM, Larry said:

I am in the process of building a migration from Freeside to Blesta. I have a couple of questions on how to migrate credits. Background: When we fall short of our SLA due to an outage we issue a full or partial credit for that period for all of our customers on the affected machines.

 

Can I simply insert these existing Freside credits into the transactions, log_transactions and transactions_applied tables when I do the migration?

 

Use the API. Specifically Transactions::add(). No need to apply the transactions explicitly as that will be done through the cron automatically when needed.

 

  On 12/31/2013 at 8:19 PM, Larry said:

Once we are in production on Blesta and we need to issue a batch of credits, can I programatically insert the credits into the transactions and log_transactions tables and let Blesta apply them?

 

Yes, using the API linked above. Blesta will automatically apply the credit as necessary.

 

  On 12/31/2013 at 8:19 PM, Larry said:

Can I create a new transaction tyoe in the transaction_types table for this purpose?

It sounds like you could use the existing "in house credit" transaction type, so no reason to create a new transaction type. Just set type to other, and type_id to the ID value of the in_house_credit transaction type (as returned by Transactions::getTypes()).

 

If you really wanted a different transaction type made special for this purpose, you'd use Transactions::addType() to add it, then specify that ID as the type_id instead when adding the transaction.

[Larry]

  On 12/31/2013 at 11:44 PM, Cody said:

 

Use the API. Specifically Transactions::add(). No need to apply the transactions explicitly as that will be done through the cron automatically when needed.

 

These tranasction have effective dates in the past. Over the last five years. If I use the api and then allow blesta to apply them these dates will reset to the current date, right? Actually this concerns all the old invoices, payments, credits and refunds as well. It seems I need to insert these all directly into the data base as the api does not appear to have the option of pre-dating these items and their application, or am I missing something?

[Larry]

OK, I see can I can pre-date invoices, and control which trasactions are apllied to what. via the apis. We are now debating wheather to bother to migrate the history or to just bring over the balances. When you've done other migrations did you bring over the history?

[Cody]

  On 1/2/2014 at 3:05 AM, Larry said:

OK, I see can I can pre-date invoices, and control which trasactions are apllied to what. via the apis. We are now debating wheather to bother to migrate the history or to just bring over the balances. When you've done other migrations did you bring over the history?

 

Yes. I'm not sure there would be much purpose in an import that didn't import transactions and invoices.

[Larry]

  On 1/2/2014 at 4:27 PM, Cody said:

Yes. I'm not sure there would be much purpose in an import that didn't import transactions and invoices.

Well, the purpose would be to just import the clients, their current balance and the package info  they are subscribed to.

 

It would remove the need to import the previous five years of transactions. This would simplify the migration considerably. It would eliminate the need to not only import old invoices, payments refunds, and credits, but also the need to then apply those transaction to the invoices as they had previously been applied.

 

The freeside database structure is more complex  than blesta's. (at least the financial portion).  Where blesta store's transactions and their application in just two tables freeside has seperate transaction and application tables for each transaction type. I think I can extract the freside financial info and programatically transform it into the blesta format and then call the blesta apis to insert and apply them, but it will be a challenge. I am not sure it is worth the extra programming effort as well as the additional testing required to confirm all transaction are applied as they were before and that everything else, including balances, match.

 

I've done two other major billing migrations for another company (different market type) and just brought forward the balances and it worked out nicely.

 

If we took this approach my main concern would be to get the billing cycles properly imported so blesta would no when the next invoice should be generated.

[Larry]

My skills are in SQL. I don't know php. So, modifying the whmcs migrator is not an option for me, though we could contract with a programmer who does know php. My approach thus far has been to build sql scripts for migrating the data. I know it is not the approach you recommend and I am concerned because of that. Don't know if you will really want to help bail me out if I run into problems I can't figure out. So, before I proceed further down this path I want to ask you if you have the resources to build a freeside migrator for us and if so, can you give me a ballpark idea of what you'd charge?

[Cody]

  On 1/2/2014 at 9:45 PM, Larry said:

My skills are in SQL. I don't know php. So, modifying the whmcs migrator is not an option for me, though we could contract with a programmer who does know php. My approach thus far has been to build sql scripts for migrating the data. I know it is not the approach you recommend and I am concerned because of that. Don't know if you will really want to help bail me out if I run into problems I can't figure out. So, before I proceed further down this path I want to ask you if you have the resources to build a freeside migrator for us and if so, can you give me a ballpark idea of what you'd charge?

 

We simply don't recommend direct SQL queries because they bypass the application layer which enforces rules on the data. While the database structure is designed to prevent anomalies, it can't prevent logical errors. That said, if you're diligent and aware of the potential issues I see no problem with it.

With regard to support for direct querying, we don't publish documentation on the schema so there's not much we can do there aside from explaining the relation between tables (which is more or less self-explanatory).

 

I really know nothing of Freeside, but to give you a rough idea the WHMCS migrator took ~60 hours start to finish (our rate is $95/hr). Your Freeside migrator may not be as complex, or may not need to be as complete.

[Larry]

Another approach we could take would be to present to the migrator the fields it expects by creating views on the freeside database.

 

I looked through the whmsc migrator code. I can't find where the whmcs database queries are. I think if I had those and the data types of the selected input variables, I could create the views.

[Cody]

  On 1/4/2014 at 2:04 AM, Larry said:

Another approach we could take would be to present to the migrator the fields it expects by creating views on the freeside database.

 

I looked through the whmsc migrator code. I can't find where the whmcs database queries are. I think if I had those and the data types of the selected input variables, I could create the views.

 

You won't find queries, per se, as they're all built programmatically. Nevertheless, queries pulled from WHMCS are in /plugins/import_manager/components/migrators/whmcs/5.2/models/. I'm not sure studying them would be of much use as those don't do much in terms of data translation. That's all handled by the migrator (/plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php).

[Larry]

Cody,

Can you clarify the relationship between the packages table and the services table? My impression is that  a row in the packages table represents a specific product and that a row in the services table represents an instance of that product which has been sold to a specif customer. However, I can't find a link between packages and services. It seems the services table should have a column called package_id. Can you explain for me?

Thanks,

Larry

[Cody]

In 3.0:

 

services.pricing_id -> package_pricing.id; package_pricing.package_id -> packages.id

[Larry]

We are up on Version 3.1.0-b1. I see that we now have a pricings table.

 

It looks like there can be more than one package_id for each pricing_id, perhaps it is one to one though. Would need to be to be able to uniquely identify the package id, no?

[Larry]

BTW, I like your DB design. It is well normalized with good and consistent naming conventions. That has made my job much easier. So, kudos to your design team.

[Cody]

  On 1/10/2014 at 10:49 PM, Larry said:

We are up on Version 3.1.0-b1. I see that we now have a pricings table.

In 3.1:

 

services.pricing_id -> package_pricing.id; package_pricing.pricing_id -> pricings.id; package_pricing.package_id -> packages.id

 

 

  On 1/10/2014 at 10:49 PM, Larry said:

It looks like there can be more than one package_id for each pricing_id, perhaps it is one to one though. Would need to be to be able to uniquely identify the package id, no?

 

There is a many to one relations between package_pricings and packages (e.g. a package can have many package_pricings).

 

Where -> denotes functional dependency...

 

(term, period, and service ID) -> (services.id, services.pricing_id, package_pricing.id, package_pricing.pricing_id, package_pricing.package_id, packages.id, pricings.id, pricings.term, pricings.period).

[Larry]

  On 1/10/2014 at 10:59 PM, Cody said:

In 3.1:

 

services.pricing_id -> package_pricing.id; package_pricing.pricing_id -> pricings.id; package_pricing.package_id -> packages.id

 

 

 

There is a many to one relations between package_pricings and packages (e.g. a package can have many package_pricings).

 

Where -> denotes functional dependency...

 

(term, period, and service ID) -> (services.id, services.pricing_id, package_pricing.id, package_pricing.pricing_id, package_pricing.package_id, packages.id, pricings.id, pricings.term, pricings.period).

Got it - thanks

[Larry]

I got called off the migration for a while but I'm back.

 

I am trying to figure out what the function of the invoices_recur and related tables are.

 

In my test system I created a customer with a package subscription. Blesta creates the recurring invoices correctly, but I don't see any rows in the ..._recur tables. 

 

Can I just ignore those tables when I migrate.

[Cody]

Recurring invoices are completely different than invoices created from services that recur.

 

A recurring invoice is simply an invoice (just like a one-time manually created invoice) that is automatically recreated at a fixed interval. It's useful primarily for users that don't need or want a service for a particular item being billed. If the system you're importing from doesn't support this feature then there is nothing you need to do.

[Larry]

We have clients with multiple accounts. Looks like the database will support that, but I don't see how to set it up in  the UI. Does Blesta support multiple accounts per client?

Thanks,

Larry

[Cody]

By multiple accounts do you mean multiple logins? As in two or more users can log in with separate credentials to the same client account?

 

Currently Blesta does not support the ability for multiple users to log into the same client account (also known as contact log ins). However we have plans to include that in a future release.

[Larry]

Are you going to be at the LA SCALE conference in Feb? If so one of my colleagues would like to email you. If you want, you can send your email to me (lcrawfor@yahoo.com) and I'll forward it to her.