Report customers by states

[tiagocaus]

I need to mount a customer report by state, can you help me?

 

<?php

use WHMCS\Database\Capsule;
use WHMCS\Utility\State;

if (!defined("WHMCS")) {
    die("This file cannot be accessed directly");
}

$reportdata["title"] = "Clients by States";
$reportdata["description"] = "This report shows the total number of active services per state, as well as total active unique clients per state in the table below.";

$reportdata["tableheadings"] = array("State","Active Services","Active Clients");

$states = new State();
$states = $states->getStateNameArray();

$clientstats = array();

$results = Capsule::table('tblclients')
    ->select(Capsule::raw('state, count(*) as `count`'))
    ->where('Status', '=', 'Active')
    ->groupBy('state')
    ->orderBy('state', 'asc')
    ->get()
    ->all();
foreach ($results as $result) {
    $clientstats[$result->state] = $result->count;
}

$results = Capsule::table('tblhosting')
    ->select(Capsule::raw('tblclients.state, count(*) as `count`'))
    ->join('tblclients', 'tblclients.id', '=', 'tblhosting.userid')
    ->where('domainstatus', '=', 'Active')
    ->groupBy('state')
    ->orderBy('state', 'asc')
    ->get()
    ->all();
foreach ($results as $result) {
    $statename = $states[$result->state];
    if ($statename) {
        $reportdata["tablevalues"][] = [
            $statename,
            $result->count,
            $clientstats[$result->state],
        ];

        $chartdata['rows'][] = [
            'c' => [
                ['v' => $result->state],
                ['v' => $result->count],
                ['v' => $clientstats[$result->state]],
            ]
        ];

        unset($clientstats[$result->state]);
    }
}

foreach ($clientstats AS $state=>$activeclient) {

    $statename = $states[$state];
    if ($statename) {

    $reportdata["tablevalues"][] = array($statename,'0',$activeclient);

    $chartdata['rows'][] = array('c'=>array(array('v'=>$state),array('v'=>0),array('v'=>$activeclient)));

    }

}

$chartdata['cols'][] = array('label'=>'State','type'=>'string');
$chartdata['cols'][] = array('label'=>'Active Services','type'=>'number');

$args = array();
$args['legendpos'] = 'right';

$reportdata["headertext"] = $chart->drawChart('Geo',$chartdata,$args,'600px');