Jump to content
AHOYHOY

Income by Product Annually or Total

Recommended Posts

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.

 

 

Share this post


Link to post
Share on other sites

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 by nwd

Share this post


Link to post
Share on other sites

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... 

 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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;

 

Share this post


Link to post
Share on other sites

@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. 

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
Reply to this topic...

×   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