Income by Product Annually or Total

[AHOYHOY]

How can I pull an income by product report, but pull all time data, or annual data?  The only way to do it right now is month by month.

 

 

[hostingct]

Following...

[nwd]

This would be nice, month to month is fine to see what product brought income in that month, but a data range is really needed. This would help see the big picture of what products and add-ons are bringing in the most, per quarter / year etc... 

Edited November 29, 2020 by nwd

[rockhost]

Totally agree

On 11/29/2020 at 8:10 AM, nwd said:

This would be nice, month to month is fine to see what product brought income in that month, but a data range is really needed. This would help see the big picture of what products and add-ons are bringing in the most, per quarter / year etc... 

 

[steven99]

With a bit of PHP work, you can rework the income by product report (/modules/reports/income_by_product.php) to be yearly or to not have any dates.   Seems most of the reports are designed around monthly.  The reports module from modulesgarden might be another option also. 

[mhermann]

I just fixed the issue for us. I will share the code with you. Just save it into an extra file income_by_product_yearly.php in the module/report folder

 

<?php
//Modifizierte income_by_product.php von Max
//Ausgabe nach Jahren nicht mehr nach Monaten 
//16.02.2021

use Illuminate\Database\Query\Builder;
use WHMCS\Carbon;
use WHMCS\Database\Capsule;

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

//$pmonth = str_pad((int)$month, 2, "0", STR_PAD_LEFT);
$year = $year;

$reportdata["title"] = "Income by Product for " . $year;
$reportdata["description"] = "This report provides a breakdown per product/service of invoices paid in a given month. Please note this excludes overpayments & other payments made to deposit funds (credit), and includes invoices paid from credit added in previous months, and thus may not match the income total for the month.";
$reportdata["currencyselections"] = true;

$reportdata["tableheadings"] = array("Product Name","Units Sold","Value");

$products = $addons = array();
$dateRange = Carbon::create(
    $year,
    1,
    1
);

# Loop Through Products
$result = Capsule::table('tblinvoiceitems')
    ->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
    ->join('tblhosting', 'tblhosting.id', '=', 'tblinvoiceitems.relid')
    ->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
    ->whereBetween(
        'tblinvoices.datepaid',
        [
            $dateRange->startOfYear()->toDateTimeString(),
            $dateRange->endOfYear()->toDateTimeString(),
        ]
    )
    ->where(function (Builder $query) {
        $query->where('tblinvoiceitems.type', 'Hosting')
            ->orWhere('tblinvoiceitems.type', 'Setup')
            ->orWhere('tblinvoiceitems.type', 'like', 'ProrataProduct%');
    })
    ->where('currency', $currencyid)
    ->groupBy('tblhosting.packageid')
    ->select(
        [
            Capsule::raw('tblhosting.packageid as packageId'),
            Capsule::raw('COUNT(*) as unitsSold'),
            Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
        ]
    )->get();

foreach ($result as $data) {
    $products[$data->packageId] = [
        'amount' => $data->amount,
        'unitssold' => $data->unitsSold,
    ];
}

$result = Capsule::table('tblinvoiceitems')
    ->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
    ->join('tblhosting', 'tblhosting.id', '=', 'tblinvoiceitems.relid')
    ->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
    ->whereBetween(
        'tblinvoices.datepaid',
        [
            $dateRange->startOfYear()->toDateTimeString(),
            $dateRange->endOfYear()->toDateTimeString(),
        ]
    )
    ->where('tblinvoiceitems.type', 'PromoHosting')
    ->where('currency', $currencyid)
    ->groupBy('tblhosting.packageid')
    ->select(
        [
            Capsule::raw('tblhosting.packageid as packageId'),
            Capsule::raw('COUNT(*) as unitsSold'),
            Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
        ]
    )
    ->get();

foreach ($result as $data) {
    $products[$data->packageId]["amount"] += $data->amount;
}

# Loop Through Addons
$result = Capsule::table('tblinvoiceitems')
    ->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
    ->join('tblhostingaddons', 'tblhostingaddons.id', '=', 'tblinvoiceitems.relid')
    ->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
    ->whereBetween(
        'tblinvoices.datepaid',
        [
            $dateRange->startOfYear()->toDateTimeString(),
            $dateRange->endOfYear()->toDateTimeString(),
        ]
    )
    ->where('tblinvoiceitems.type', 'Addon')
    ->where('currency', $currencyid)
    ->groupBy('tblhostingaddons.addonid')
    ->select(
        [
            Capsule::raw('tblhostingaddons.addonid as addonId'),
            Capsule::raw('COUNT(*) as unitsSold'),
            Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
        ]
    )->get()
    ->all();

foreach ($result as $data) {
    $addons[$data->addonId] = [
        'amount' => $data->amount,
        'unitssold' => $data->unitsSold,
    ];
}

$total = 0;
$itemtotal = 0;
$firstdone = false;
$result = Capsule::table('tblproducts')
    ->join(
        'tblproductgroups',
        'tblproductgroups.id',
        '=',
        'tblproducts.gid'
    )
    ->orderBy('tblproductgroups.order')
    ->orderBy('tblproducts.order')
    ->orderBy('tblproducts.name')
    ->get(
        [
            'tblproducts.id',
            'tblproducts.name',
            Capsule::raw('`tblproductgroups`.`name` as groupname')
        ]
    )
    ->all();
foreach ($result as $data) {
    $pid = $data->id;
    $group = $data->groupname;
    $prodname = $data->name;

    if ($group!=$prevgroup) {
        $total += $itemtotal;
        if ($firstdone) {
            $reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
            $chartdata['rows'][] = array('c'=>array(array('v'=>$prevgroup),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));
        }
        $reportdata["tablevalues"][] = array("**<strong>$group</strong>");
        $itemtotal = 0;
    }

    $amount = $products[$pid]["amount"];
    $number = $products[$pid]["unitssold"];

    $itemtotal += $amount;

    if (!$amount) $amount="0.00";
    if (!$number) $number="0";
    $amount = formatCurrency($amount);

    $reportdata["tablevalues"][] = array($prodname,$number,$amount);

    $prevgroup = $group;
    $firstdone = true;

}

$total += $itemtotal;
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>$group),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));

$reportdata["tablevalues"][] = array("**<strong>Addons</strong>");

$itemtotal = 0;
$result = Capsule::table('tbladdons')
    ->orderBy('name')
    ->get(
        [
            'id',
            'name',
        ]
    )
    ->all();
foreach ($result as $data) {
    $addonid = $data->id;
    $prodname = $data->name;

    $amount = $addons[$addonid]["amount"];
    $number = $addons[$addonid]["unitssold"];

    $itemtotal += $amount;

    if (!$amount) $amount="0.00";
    if (!$number) $number="0";
    $amount = formatCurrency($amount);

    $reportdata["tablevalues"][] = array($prodname,$number,$amount);

    $prevgroup = $group;

}

$itemtotal += $addons[0]["amount"];
$number = $addons[0]["unitssold"];
$amount = $addons[0]["amount"];
if (!$amount) $amount="0.00";
if (!$number) $number="0";
$reportdata["tablevalues"][] = array('Miscellaneous Custom Addons',$number,formatCurrency($amount));

$total += $itemtotal;
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>"Addons"),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));

$itemtotal = 0;
$reportdata["tablevalues"][] = array("**<strong>Miscellaneous</strong>");

$data = Capsule::table('tblinvoiceitems')
    ->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
    ->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
    ->whereBetween(
        'tblinvoices.datepaid',
        [
            $dateRange->startOfYear()->toDateTimeString(),
            $dateRange->endOfYear()->toDateTimeString(),
        ]
    )
    ->where('tblinvoiceitems.type', 'Item')
    ->where('tblclients.currency', $currencyid)
    ->first(
        [
            Capsule::raw('COUNT(*) as number'),
            Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
        ]
    );

$itemtotal += $data->amount;
$number = $data->number;
$amount = $data->amount;
if (!$amount) $amount="0.00";
if (!$number) $number="0";
$reportdata["tablevalues"][] = array('Billable Items',$number,formatCurrency($amount));

$data = Capsule::table('tblinvoiceitems')
    ->join('tblinvoices', 'tblinvoices.id', '=', 'tblinvoiceitems.invoiceid')
    ->join('tblclients', 'tblclients.id', '=', 'tblinvoices.userid')
    ->whereBetween(
        'tblinvoices.datepaid',
        [
            $dateRange->startOfYear()->toDateTimeString(),
            $dateRange->endOfYear()->toDateTimeString(),
        ]
    )
    ->where('tblinvoiceitems.type', '')
    ->where('tblclients.currency', $currencyid)
    ->first(
        [
            Capsule::raw('COUNT(*) as number'),
            Capsule::raw('SUM(tblinvoiceitems.amount) as amount')
        ]
    );

$itemtotal += $data->amount;
$number = $data->number;
$amount = $data->amount;
$reportdata["tablevalues"][] = array('Custom Invoice Line Items',$number,formatCurrency($amount));

$total += $itemtotal;
$reportdata["tablevalues"][] = array('','<strong>Sub-Total</strong>','<strong>'.formatCurrency($itemtotal).'</strong>');
$chartdata['rows'][] = array('c'=>array(array('v'=>"Miscellaneous"),array('v'=>$itemtotal,'f'=>formatCurrency($itemtotal))));

$total = formatCurrency($total);

$chartdata['cols'][] = array('label'=>'Days Range','type'=>'string');
$chartdata['cols'][] = array('label'=>'Value','type'=>'number');

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

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

$reportdata["yearspagination"] = true;

 

[nwd]

@mhermann Thanks, just got a chance to try it out. Works really well to quickly see how much the individual products are brining in for the year, and past years. 

[Dgital Essence]

Thank you, the first properly useful report in WHMCS!

 

[Argoz]

@mhermann  A huge thank you for the code. That' s exactly what I was looking for!

[MBayDesign]

A belated thank you very much for this code!   

I don't know if this could be amended or if it needs a whole new piece of code, but I use Billable Items to create invoices for clients for items generally related to site maintenance, but also to create recurring billing for apps or services, entire site design billing, graphic design, etc. 

I use actual products I've created under a hidden group name so the line items are assigned to real products, but the units and values are lumped together and reported in a single sum under MISC/Billable Items on the report instead of filtering into the Products I created. It's impossible to break it down unless I go back through every invoice. 

Is there anyway these billable item charges can be routed to these products in this report? 

Thank you!

[TikTok]

@mhermann thank you for your code. As a nube I need to know which file to edit to add this code. Help?