Top 200 Income Report

[xlegends]

Hi,

I copied over the default top 10 clients by income report file and made a new one and edited it to pull top 200 clients by income. All works well.

I would like it to filter out the clients who are not active. Not sure how to do this. 

Im including the code I have for top 200 file. 

Would appreciate any help.

 

From /modules/reports/top_200_clients_by_income.php

<?php

use WHMCS\Database\Capsule;

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

$reportdata["title"] = "Top 200 Clients by Income";
$reportdata["description"] = "This report shows the 200 clients with the highest net income according to the transactions entered in WHMCS.";

$reportdata["tableheadings"] = array("Client ID","Client Name","Total Amount In","Total Fees","Total Amount Out","Balance");

$results = Capsule::table('tblaccounts')
    ->select(
        'tblclients.id',
        'tblclients.firstname',
        'tblclients.lastname',
        Capsule::raw('SUM(tblaccounts.amountin/tblaccounts.rate) AS amountIn'),
        Capsule::raw('SUM(tblaccounts.fees/tblaccounts.rate) AS fees'),
        Capsule::raw('SUM(tblaccounts.amountout/tblaccounts.rate) AS amountOut'),
        Capsule::raw('SUM((tblaccounts.amountin/tblaccounts.rate)-(tblaccounts.fees/tblaccounts.rate)-(tblaccounts.amountout/tblaccounts.rate)) AS balance'),
        'tblaccounts.rate'
    )
    ->join('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
    ->groupBy('userid')
    ->orderBy('balance', 'desc')
    ->take(200)
    ->get()
    ->all();

foreach ($results as $result) {
    $userid = $result->id;

    $currency = getCurrency();
    $rate = ($result->rate == "1.00000") ? '' : '*';

    $clientlink = '<a href="clientssummary.php?userid=' . $result->id . '">';

    $reportdata["tablevalues"][] = [
        $clientlink . $result->id . '</a>',
        $clientlink . $result->firstname . ' ' . $result->lastname . '</a>',
        formatCurrency($result->amountIn) . " $rate",
        formatCurrency($result->fees) . " $rate",
        formatCurrency($result->amountOut) . " $rate",
        formatCurrency($result->balance) . " $rate",
    ];

    $chartdata['rows'][] = [
        'c' => [
            [
                'v' => $result->firstname . ' ' . $result->lastname,
            ],
            [
                'v' => round($result->balance, 2),
                'f' => formatCurrency($result->balance),
            ]
        ]
    ];
}

$reportdata["footertext"] = "<p>* denotes converted to default currency</p>";

$chartdata['cols'][] = array('label'=>'Client','type'=>'string');
$chartdata['cols'][] = array('label'=>'Balance','type'=>'number');

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

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

 

[brian!]

4 hours ago, xlegends said:

I would like it to filter out the clients who are not active. Not sure how to do this. 

that's just a simple where condition added to your $results database query...

$results = Capsule::table('tblaccounts')
    ->select(
        'tblclients.id',
        'tblclients.firstname',
        'tblclients.lastname',
        Capsule::raw('SUM(tblaccounts.amountin/tblaccounts.rate) AS amountIn'),
        Capsule::raw('SUM(tblaccounts.fees/tblaccounts.rate) AS fees'),
        Capsule::raw('SUM(tblaccounts.amountout/tblaccounts.rate) AS amountOut'),
        Capsule::raw('SUM((tblaccounts.amountin/tblaccounts.rate)-(tblaccounts.fees/tblaccounts.rate)-(tblaccounts.amountout/tblaccounts.rate)) AS balance'),
        'tblaccounts.rate'
    )
    ->join('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
    ->where('tblclients.status','Active')
    ->groupBy('userid')
    ->orderBy('balance', 'desc')
    ->take(200)
    ->get()
    ->all();

[xlegends]

You the man. Thank you. 

[Magicklug]

This works alright. Thank you.

[xlegends]

How can I also add a category tab to display the clients company name?

[brian!]

2 hours ago, xlegends said:

How can I also add a category tab to display the clients company name?

first step would be to add 'tblclients.companyname' to the select list in the $results array.

then it's a case of working out how to display the company name - you seem to want the value to be in a separate column, so you next add "Company Name"  to the tableheadings array.

$reportdata["tableheadings"] = array("Client ID","Client Name","Company Name","Total Amount In","Total Fees","Total Amount Out","Balance");

then you add the company name to the output...

    $reportdata["tablevalues"][] = [
        $clientlink . $result->id . '</a>',
        $clientlink . $result->firstname . ' ' . $result->lastname . '</a>',
        $result->companyname,
        formatCurrency($result->amountIn) . " $rate",
        formatCurrency($result->fees) . " $rate",
        formatCurrency($result->amountOut) . " $rate",
        formatCurrency($result->balance) . " $rate",
    ];

[xlegends]

Outstanding Brian. Please pm me your btc addy. 

Edited April 27, 2021 by xlegends

[ManagedCloud-Hosting]

On 4/20/2021 at 2:23 PM, brian! said:

that's just a simple where condition added to your $results database query...

Hi @brian! can you please put the hook together, I am not able to make it work for me.

Also I want some reports generated and sent via email every month, is this possible ?

Thanks 

[brian!]

50 minutes ago, ManagedCloud-Hosting said:

can you please put the hook together, I am not able to make it work for me.

it's not a hook, it's a report. 🙂

<?php

use WHMCS\Database\Capsule;

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

$reportdata["title"] = "Top 200 Clients by Income";
$reportdata["description"] = "This report shows the 200 clients with the highest net income according to the transactions entered in WHMCS.";

$reportdata["tableheadings"] = array("Client ID","Client Name","Company Name","Total Amount In","Total Fees","Total Amount Out","Balance");

$results = Capsule::table('tblaccounts')
    ->select(
        'tblclients.id',
        'tblclients.firstname',
        'tblclients.lastname',
        'tblclients.companyname',
        Capsule::raw('SUM(tblaccounts.amountin/tblaccounts.rate) AS amountIn'),
        Capsule::raw('SUM(tblaccounts.fees/tblaccounts.rate) AS fees'),
        Capsule::raw('SUM(tblaccounts.amountout/tblaccounts.rate) AS amountOut'),
        Capsule::raw('SUM((tblaccounts.amountin/tblaccounts.rate)-(tblaccounts.fees/tblaccounts.rate)-(tblaccounts.amountout/tblaccounts.rate)) AS balance'),
        'tblaccounts.rate'
    )
    ->join('tblclients', 'tblclients.id', '=', 'tblaccounts.userid')
    ->where('tblclients.status','Active')
    ->groupBy('userid')
    ->orderBy('balance', 'desc')
    ->take(200)
    ->get()
    ->all();

foreach ($results as $result) {
    $userid = $result->id;

    $currency = getCurrency();
    $rate = ($result->rate == "1.00000") ? '' : '*';

    $clientlink = '<a href="clientssummary.php?userid=' . $result->id . '">';

    $reportdata["tablevalues"][] = [
        $clientlink . $result->id . '</a>',
        $clientlink . $result->firstname . ' ' . $result->lastname . '</a>',
        $result->companyname,
        formatCurrency($result->amountIn) . " $rate",
        formatCurrency($result->fees) . " $rate",
        formatCurrency($result->amountOut) . " $rate",
        formatCurrency($result->balance) . " $rate",
    ];

    $chartdata['rows'][] = [
        'c' => [
            [
                'v' => $result->firstname . ' ' . $result->lastname,
            ],
            [
                'v' => round($result->balance, 2),
                'f' => formatCurrency($result->balance),
            ]
        ]
    ];
}

$reportdata["footertext"] = "<p>* denotes converted to default currency</p>";

$chartdata['cols'][] = array('label'=>'Client','type'=>'string');
$chartdata['cols'][] = array('label'=>'Balance','type'=>'number');

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

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

50 minutes ago, ManagedCloud-Hosting said:

Also I want some reports generated and sent via email every month, is this possible ?

yes, but they wouldn't be reports in the above sense - they'd probably need to be a combination of cron hooks, SQL query and API.

I think there would be at least one third-party addon module for this, e.g MG's Report Generator - that has the option to send custom reports by email.

Edited April 27, 2021 by brian!

[ManagedCloud-Hosting]

10 minutes ago, brian! said:

it's not a hook, it's a report. 🙂

How to set it up ? Please help 🤔🤔 

13 minutes ago, brian! said:

MG's Report Generator -

Not for me you know that already  🙂😉🤣

[brian!]

6 minutes ago, ManagedCloud-Hosting said:

How to set it up ? Please help

https://docs.whmcs.com/Reports#Creating_your_own_Reports

Quote

• The report name you choose must be unique, all lowercase, and contain only letters and numbers (a–z, 0–9). It must start with a letter.
• Once your report is ready for upload, please upload it to the "/modules/reports" folder and it will become accessible under Reports > More in the admin area.

7 minutes ago, ManagedCloud-Hosting said:

Not for me you know that already

after 15000+ posts and thousands of PM's, I can't remember everything! 😎

[ManagedCloud-Hosting]

On 4/27/2021 at 10:20 PM, brian! said:

after 15000+ posts and thousands of PM's, I can't remember everything! 😎

👍👍