Jump to content
  • 0
xlegends

Top 200 Income Report

Question

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');

 

Share this post


Link to post
Share on other sites

11 answers to this question

Recommended Posts

  • 0
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();

Share this post


Link to post
Share on other sites
  • 0
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",
    ];

Share this post


Link to post
Share on other sites
  • 0
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 

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)
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 by brian!

Share this post


Link to post
Share on other sites
  • 0
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! 😎

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated