Pulling active accounts out of WHMCS

[hireahit]

I'm a small hosting company floating between hobby and small business depending on how many clients pay their bills each month. I'm a techie by nature, and find running the servers more interesting then billing. Since my customers are willing to pay, I'm thinking I need an actual invoicing/billing system, and some automated way to process payments. WHMCS looks ideal.

 

I wrote my own server management scripts, and am quite happy with how they operate, so I am not looking for anything like cpanel or similar. My goal here would be to keep my existing custom written backend, but entirely replace the customer-facing front-end with WHMCS.

 

This means I will obviously need to rewrite enough of the backend to pull the information I need from WHMCS.

 

On the hosting side of things, all I really need is a front end that allows users to choose their package and optional add-ons. It looks like WHMCS will do this.

 

Next, I need to be able to pull a real time list of active domain names, usernames, passwords, and service levels in some format my scripts can easily parse.

 

I can either do HTTPS or SQL queries to get at this data, so what I'm wondering is about the SQL schema, is there a way to query the data I need fairly easily, or is the data encoded/encrypted some how?

 

I currently feed the server scripts a combination of CSV, XML and INI formatted data, depending on the script in question, but I'm happy to accept data in whatever format and adjust the "input" side of the scripts, but I want to avoid having to reverse-engineer the storage mechanism.

 

A glance at the API didn't reveal any easy way to answer this question, so I'm posting here instead.

 

So I think all I really need to know is does WHMCS store user data in an easily accessible, human readable format in the MySQL database? If so, is there any reason I can't just whip up some PHP to read the data and provide it to my existing backend scripts?

 

Hopefully this makes sense, but if not, I'll provide the excuse that it's 5am and I probably should be sleeping right now.

[handsonwebhosting]

Hi there,

 

Yeah, all the data (for the most part) is stored in the database and not encrypted. You should be able to use simple SQL Queries to extract the data that you're looking for. The only things encrypted would be the credit card information and possibly the passord (but even the password if I remember is in plain text).

[ur]

On the hosting side of things, all I really need is a front end that allows users to choose their package and optional add-ons. It looks like WHMCS will do this.

Yes, it does do this.

 

Next, I need to be able to pull a real time list of active domain names, usernames, passwords, and service levels in some format my scripts can easily parse.

You can get the list of hosting packages and domains from either an SQL query or using the WHMCS admin panel. Passwords for different services may not be available in WHMCS, or in certain cases not available in plain text. For instance hosting passwords are usually not stored, plus those may be changed at the hosting control panel, for instance. There are a few workarounds for that. Check the forums.

 

I can either do HTTPS or SQL queries to get at this data, so what I'm wondering is about the SQL schema, is there a way to query the data I need fairly easily, or is the data encoded/encrypted some how?

huh ... HTTPS or SQL ? oh well ... you will need to query the database using SQL. HTTPS won't give you what you need unless you have a script that you can access it over the HTTPS protocol. (Sorry, being facetious). The MySQL db tables are easy enough to understand if you take a look at them. Very little is encrypted other than certain passwords and cc numbers.

 

I currently feed the server scripts a combination of CSV, XML and INI formatted data, depending on the script in question, but I'm happy to accept data in whatever format and adjust the "input" side of the scripts, but I want to avoid having to reverse-engineer the storage mechanism.

You totally lost me here. But anyway, why reverse-engineer the storage mechanism. That is MySQL and it is available as open source (Sorry, being facetious). If you build your own SQL queries to get the data you need to pass to your scripts, then you should be all set.

 

A glance at the API didn't reveal any easy way to answer this question, so I'm posting here instead.

The API in it's current state is more to execute account functions, and not for exporting data, (other than client details and password). If you require more functionality, you will need to build it using your own scripts or contact the developers to do it for you.

 

So I think all I really need to know is does WHMCS store user data in an easily accessible, human readable format in the MySQL database? If so, is there any reason I can't just whip up some PHP to read the data and provide it to my existing backend scripts?

nope, no reason you shouldn't be able to do so.

[hireahit]

My fear about reverse engineering was more that some applications store data in a binary format within the MySQL database, so there is more work to do beyond just pulling information from the database. A simple example would be storing use permissions as a bitmask rather then in separate fields in the same table.

 

We're on the same page about HTTPS, I was thinking more that if WHMCS had a URL that would export the active accounts in XML/CSV/whatever, I could use that rather then writing my own scripts to query the database directly. Same difference in the end.

 

Either way I need to bridge the gap to get what I need in the format that I need it. I prefer to do this on the WHMCS server (with my own PHP code) so that I don't have to open my MySQL database up to the world, even if locked down to IPs I control, those IPs run customer sites and customer code, so must be considered hostile.

 

I think you answered all my questions, thanks for your time!

[ur]

Okay, I understand what you mean. There are some csv reports that you may be able to use or customize for your needs. I.e.: check out the Clients and Product/Services csv export. Good Luck!

[Nick]

You can also create your own server modules for WHMCS, so account creation/suspension/deletion etc can be automated.

[disgruntled]

so that I don't have to open my MySQL database up to the world, even if locked down to IPs I control, those IPs run customer sites and customer code, so must be considered hostile.

 

Well you know there is always what i would consider to be the prefered method of installation and one you should seriously be considering.

 

Get a hosting service away from your main datacentre. that way your own business website is not in direct contact with the client databases / sites / scripts. and in the even that there is an outage (which may be datacenter wide) your business site will always be available for your much to be valued clients to contact you.

 

It has to be said that this method will require some planning and careful implimentation.

 

Can i suggest taking a leased whmcs license for testing purposes, once you have it working how you want. (remote provisioning services / dns clustering / host server white listing in the WHM of each server) Then you migrate your business site to the new hosting. these are the basic steps but more thorough security and integrations may be needed.

 

 

I hope this post gives you some food for thought and good luck.

Edited September 16, 2010 by disgruntled