Widget To Show Comparison of Monthly Income Totals From This Year/Last Year

[brian!]

Here is another widget that you can add to your Admin homepage if you wish - it will show a side-by-side comparison of your monthly income totals for the last 12 months against the previous 12 months.

 

this is a sample of how the widget will look - though obviously your widget will show upto 12 months!

 

 

the code for this widget, which i've called monthly_compare.php, is as follows:

 

    <?php

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

   function widget_monthly_compare() {

   $startdate = date('Y-m-01');
   $enddate = date('Y-m-t', strtotime($startdate));
   $previousyear = date('Y-m-01', strtotime('-1 year', strtotime($startdate)) );    
   $previousend = date('Y-m-t', strtotime($previousyear));    

   $content = '<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
   <tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td width="25%">Month</td><td width="25%">Income</td><td width="25%">Income</td><td width="25%">Month</td></tr>';

   $x=1;
   while($x<=12) {

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE date BETWEEN '$startdate' AND '$enddate'");
       while ($data = @mysql_fetch_array ($result)) {
          $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td>'.date('F Y', strtotime($startdate)).'</td><td>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</td>';
       }    

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE date BETWEEN '$previousyear' AND '$previousend'");
       while ($data = @mysql_fetch_array ($result)) {
          $content .= '<td>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</td><td>'.date('F Y', strtotime($previousyear)).'</td></tr>';
       }    

       $startdate = date('Y-m-d', strtotime('-1 month', strtotime($startdate)) );    
       $enddate = date('Y-m-t', strtotime($startdate));
       $previousyear = date('Y-m-d', strtotime('-1 month', strtotime($previousyear)) );    
       $previousend = date('Y-m-t', strtotime($previousyear));            
   $x++;
} 

   $content .= '</table>';

       return array( 'title' => 'Monthly Income Comparison', 'content' => $content );
   }

   add_hook("AdminHomeWidgets",1,"widget_monthly_compare");

   ?>

create a new file in your /modules/widgets/ directory called monthly_compare.php and paste the above code into it.

 

if you are unfamiliar with adding widgets to your Admin homepage, read the documentation to learn how...

 

http://docs.whmcs.com/Widgets

 

if you want to change how the month names and years are displayed, you can refer to the php date options I posted in the thread below...

 

http://forum.whmcs.com/showthread.php?91343-Widget-to-show-income-between-any-two-dates-e-g-Fiscal-Year-Quarter-etc

[blakeh]

Excellent.

 

It may be nice to tweak to show percent of increase or decrease for each period. I also think a YTD or rolling 12months to date with comparison to previous period showing increase or decrease could be cool.

 

Thank you, this is very useful.

 

bh

[brian!]

Hi Blake,

 

It may be nice to tweak to show percent of increase or decrease for each period.

that exact same thought crossed my mind as I was coding it, but as i've only been using WHMCS for around 18 months, my monthly values before that point are 0 - and if either month value is 0, then it makes a nonsense of the percentage increase/decrease calculation.

 

e.g., if the income for December 2013 is $1,000, but the value for December 2012 is $0.00 - what is the percentage increase? or if it's the first day of a new month, then income will be 0, and you can't calculate a % difference if one of the values is zero!

 

so to keep things simple, I just avoided crossing that bridge!

 

but that said, I don't think it would be too difficult to do - just create two new variables for the monthly values and then perform your percentage calculation using them.

 

I assumed dividing by 0 might cause a visible error, but it seems not to.. though i've added a condition to check if either month value is 0 - if it isn't, it will perform the calculation; if at lease one of them is 0, it doesn't attempt the calculation but makes the string empty - or you can alter this to use any text you wish here if you don't want the table cell blank).

 

    <?php

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

   function widget_monthly_compare() {

   $startdate = date('Y-m-01');
   $enddate = date('Y-m-t', strtotime($startdate));
   $previousyear = date('Y-m-01', strtotime('-1 year', strtotime($startdate)) );    
   $previousend = date('Y-m-t', strtotime($previousyear));    

   $content = '<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
   <tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td width="20%">Month</td><td width="20%">Income</td><td width="20%">Difference</td><td width="20%">Income</td><td width="20%">Month</td></tr>';

   $x=1;
   while($x<=12) {

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE date BETWEEN '$startdate' AND '$enddate'");
       while ($data = @mysql_fetch_array ($result)) {
            $month1 = $data['SUM(amountin-fees-amountout)'];
          $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td>'.date('F Y', strtotime($startdate)).'</td><td>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</td>';
       }    

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE date BETWEEN '$previousyear' AND '$previousend'");
       while ($data = @mysql_fetch_array ($result)) {
       $month2 = $data['SUM(amountin-fees-amountout)'];

       if (isset($month1,$month2)) {
           $diff = number_format(((($month1-$month2)/$month2)*100),1);
           $diff .= '%';
       }
       else {
           $diff = '';
       }

          $content .= '<td>'.$diff.'</td><td>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</td><td>'.date('F Y', strtotime($previousyear)).'</td></tr>';
       }    
       $startdate = date('Y-m-d', strtotime('-1 month', strtotime($startdate)) );    
       $enddate = date('Y-m-t', strtotime($startdate));
       $previousyear = date('Y-m-d', strtotime('-1 month', strtotime($previousyear)) );    
       $previousend = date('Y-m-t', strtotime($previousyear));            
   $x++;
} 

   $content .= '</table>';

       return array( 'title' => 'Monthly Income Comparison', 'content' => $content );
   }

   add_hook("AdminHomeWidgets",1,"widget_monthly_compare");

   ?>

I also think a YTD or rolling 12months to date with comparison to previous period showing increase or decrease could be cool.

I would be slightly more inclined to use of my other widgets for this as they might be more suitable...

 

http://forum.whmcs.com/showthread.php?94823-Widget-To-Display-A-Summary-Of-Fiscal-Income-For-Multiple-Periods

http://forum.whmcs.com/showthread.php?91343-Widget-to-show-income-between-any-two-dates-e-g-Fiscal-Year-Quarter-etc

 

either could probably be adapted for your needs - though the second has a 10-year option which will display your annual fiscal income for the last ten years (unless income is 0 for any year).

 

normally you would need to give it the start of your financial year (day & month) - so if you wanted to use YTD, you would make startday = 1; $startmonth = 1;

 

... if you wanted a rolling 12 month comparison, you would replace the code below in fiscal_income.php

 

    $startday = 1;
   $startmonth = 4;
   $currentyear = date('Y');    
   $dateformat = 'd/m/Y';
   $startdate = date_create($currentyear.'-'.$startmonth.'-'.$startday);
   $today = date_create(date('Y-m-d'));
   $interval = date_diff($startdate, $today);
   $days = $interval->format('%r%a');

       $dateformat = 'd/m/Y';

       if($days>=0)
       {
       $startdate = $currentyear.'-'.$startmonth.'-'.$startday;
       }
       else
       {
       $startdate = ($currentyear-1).'-'.$startmonth.'-'.$startday;
       } 

   $enddate = date('Y-m-d', strtotime('+1 year -1 day', strtotime($startdate)) );    

with...

 

    $dateformat = 'd/m/Y';
   $enddate = date('Y-m-d');
   $startdate = date('Y-m-d', strtotime('-1 year +1 day', strtotime($enddate)) );    

getting a percentage difference might be slightly trickier though as this widget is using just one loop and not two as this monthly widget does... it could be done, but if percentages are important to you, then you could try tweaking this monthly widget instead.

[blakeh]

Great, thank you for the info!

[othellotech]

We have month-on-month, quarter-on-quarter, year-on-year comparison widgets using almost identical code except:

SUM((amountin-fees-amountout)/rate) AS balance

to get the values correct when using multi-ccy implementations of WHMCS

[brian!]

just a minor update to correct an error I found when comparing the income figures against another widget output (there was a transaction missing from 31st March)...

 

<?php

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

   function widget_monthly_compare() {

   $startmonth = date('Y-m');
   $previousyear = date('Y-m', strtotime('-1 year', strtotime($startmonth)) );    

   $content = '<table bgcolor="#cccccc" align="center" style="margin-bottom:5px;width:100%;" cellspacing="1">
   <tr bgcolor="#efefef" style="text-align:center;font-weight:bold;"><td width="25%">Month</td><td width="25%">Income</td><td width="25%">Income</td><td width="25%">Month</td></tr>';

   $x=1;
   while($x<=12) {
       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date LIKE '".$startmonth."-%'");
       while ($data = @mysql_fetch_array ($result)) {
          $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td>'.date('F Y', strtotime($startmonth)).'</td><td>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</td>';
       }    
       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date LIKE '".$previousyear."-%'");
       while ($data = @mysql_fetch_array ($result)) {
          $content .= '<td>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</td><td>'.date('F Y', strtotime($previousyear)).'</td></tr>';
       }    
       $startmonth = date('Y-m', strtotime('-1 month', strtotime($startmonth)) );    
       $previousyear = date('Y-m', strtotime('-1 month', strtotime($previousyear)) );    
   $x++;
   } 
   $content .= '</table>';
       return array( 'title' => 'Monthly Income Comparison', 'content' => $content );
   }
   add_hook("AdminHomeWidgets",1,"widget_monthly_compare");
?>