How to easily export data from WHMCS using Laravel’s Eloquent

Background

A little known fact is that WHMCS uses an ORM that supports the Active Record implementation. To the rest of use those fancy words really just means that WHMCS uses Laravel’s Eloquent under the hood. It’s an outdated version, but all the beauty of Eloquent still exists, for example, relationship definitions and fluent queries.

Recently I had to create an application that exports WHMCS product data for use on a WordPress Elementor page. Instead of doing live database queries I decided to just produce the data in a flat format for consumption on the WordPress side. The Elementor side has some Geolocation built in, so the data had to be displayed in a specific format that recognizes the currency.

The end result needed to look like this:

$this->pricing[3]['Group’]          = ‘Shared Hosting Group’;
$this->pricing[3]['Name']           = ‘Hosting Product’;
$this->pricing[3]['ZAR']['setup']   = 110.00;
$this->pricing[3]['ZAR']['monthly'] = 100;
$this->pricing[3]['USD']['setup']   = 6.60;
$this->pricing[3]['USD']['monthly'] = 6.00;
$this->pricing[3]['GBP']['setup']   = 5.25;
$this->pricing[3]['GBP']['monthly'] = 4.77;

In the above example, 3 is the ID of the product. Then based on the two letter Geolocation code, a currency is deduced. From there the pricing can be determined and displayed.

Here is the source code. This file was saved to WHMCS_ROOT/modules/addons/price_table/export.php

<?php

use App\Currency;
use App\Pricing;
use App\Product;
use WHMCS\Product\Group;

require __DIR__ . '/../../../init.php';
require 'app/Currency.php';
require 'app/Pricing.php';
require 'app/Product.php';

$currencyCode = getCurrencyCodes();

$groups = Group::orderBy('order')->get();
foreach ($groups as $group) {

    $products = Product::whereGid($group->id)
        ->whereRetired(false)
        ->orderBy('order')
        ->get();

    foreach ($products as $product) {
        $data[$product->id]['group'] = $group->name;
        $data[$product->id]['name']  = $product->name;
        $pricing                     = Pricing::whereRelid($product->id)
            ->whereType('product')
            ->get();
        foreach ($pricing as $price) {
            $data[$product->id][$currencyCode[$price->currency]]['monthly_setup'] = $price->msetupfee;
            $data[$product->id][$currencyCode[$price->currency]]['monthly']       = $price->monthly;
            $data[$product->id][$currencyCode[$price->currency]]['annual_setup']  = $price->asetupfee;
            $data[$product->id][$currencyCode[$price->currency]]['annually']      = $price->annually;
        }
    }

    if ($data) {
        echo(json_encode($data, JSON_PRETTY_PRINT));
    }

}

function getCurrencyCodes()
{
    $currencyCode = [];
    $currencies   = Currency::all();
    foreach ($currencies as $currency) {
        $currencyCode[$currency->id] = $currency->code;
    }
    return $currencyCode;
}

Application Workflow

A brief over of the application is:

• Define Use statement for our own custom models
• Define one Use statement that uses WHMCS own Group model
• Initialize WHMCS to include it’s name spaces
• Import our models
• Loop Groups
  • Loops Products
    • Loop Pricing per Product
• Return JSON

Model Definition

The model definitions all take the same shape that should be highly familiar to a Laravel coder:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model {

    protected $table = "tblproducts";

}

As you can see all that is customized is the table name. In this example, I have not included the relationships – that will be up to the reader to include. It’s not terribly complicated to build the relationships but you will need a basic understanding of WHMCS’s database and how it’s IDs link up. WHMCS’s database is  well laid out bar a few unconventional names for fields. Their documentation is pretty spectacular when it comes to building sophisticated applications and you will find all the relationships there.

Anyhow, the final output is perfect for my application:

{
   "3": {
     "group": "Shared Hosting",
     "name": "Hosting Product",
     "ZAR": {
        "setup": "110.00",
        "monthly": "100.00"
      },
     "USD": {
        "setup": "6.60",
        "monthly": "6.00"
      },
      "GBP": {
         "setup": "5.25",
         "monthly": "4.77"
      }
   }
}

Conclusion

Well that’s it for now! I really like building this export prototype because working with WHMCS, Eloquent, WordPress and Elementor is really rewarding. There are so many possibilities. If you have any questions about this solution, please contact me on +27 82 309-6710.

-Eugene