Widget with monthly comparison + percentages

[Chris74]

Hi I'm referring to this older forum post that can't be replied to...

 

https://forums.whmcs.com/showthread.php?94902-Widget-To-Show-Comparison-of-Monthly-Income-Totals-From-This-Year-Last-Year

 

Brian very kindly provided a widget for comparing 12 months of income with the previous 12 months in a widget which is really great. In the thread he provides a version of it with percentage differences between each month and the last, which I find very useful - however, I think that version of the widget calculates the months into only 30 days and the revised version at the bottom of the thread contains the fixed dates but not the percentages. The percentage version also causes a slight display problem at the top of the page.

 

I'm not a developer and although I've tried to compare the two scripts and make the changes myself, sadly I couldn't do it.

 

I wonder if anyone would be kind enough to provide a new version combining the functionality of the two scripts pasted below...

 

<?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");

   ?>

 

 <?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");
?> 

Edited August 13, 2016 by Chris74

[brian!]

if you run them side-by-side, are they giving different values?

I would have thought the first would be calculating the number of days correctly - certainly the second will.

 

do you just want to adapt the second to have percentages?

[Chris74]

if you run them side-by-side, are they giving different values?

I would have thought the first would be calculating the number of days correctly - certainly the second will.

 

do you just want to adapt the second to have percentages?

 

Thanks for replying Brian.

 

Each script produces completely different results to the other - apart from the current month total which is the same. Comparing the totals in both widgets to the WHMCS income report, the script you created without the percentages matches that data, so I'd say that is the accurate one.

 

As you suggest, I would like to use that widget, but with the percentage difference between each month and the previous month. So to answer your question, yes, I would very much like like to adapt that script to have percentages.

[brian!]

there's only two real differences between the scripts - i) how it queries the db for dates and ii) the formula used - and I probably pulled the updated formula from the income report anyway, so it's no surprise they may match!

 

assuming that ii) is the issue for you (e.g you have a multi-currency setup), it might be easier to try (that's code for quicker!) to use the first script (the one with percentages already), but change all references of...

 

SUM(amountin-fees-amountout)

to...

 

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

they should then match the income report values... if they don't, then there is an issue with the date method used in the first script, and i'll rewrite as necessary.

[Chris74]

there's only two real differences between the scripts - i) how it queries the db for dates and ii) the formula used - and I probably pulled the updated formula from the income report anyway, so it's no surprise they may match!

 

assuming that ii) is the issue for you (e.g you have a multi-currency setup), it might be easier to try (that's code for quicker!) to use the first script (the one with percentages already), but change all references of...

 

SUM(amountin-fees-amountout)

to...

 

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

they should then match the income report values... if they don't, then there is an issue with the date method used in the first script, and i'll rewrite as necessary.

 

Thanks Brian, I appreciate your reply. I only have one currency enabled and don't use any conversion rates or anything like that. I recall in the original thread you updated the formulas because the first script was producing incorrect totals for you.

[brian!]

I recall in the original thread you updated the formulas because the first script was producing incorrect totals for you.

absolutely spot on Chris, there is an error - only when running them side by side did I see the error... the infamous 31st March 2015 invoice!

 

try the following...

 

<?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="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)/rate) FROM tblaccounts WHERE date LIKE '".$startmonth."-%'");
       while ($data = @mysql_fetch_array ($result)) {
           $month1 = $data['SUM((amountin-fees-amountout)/rate)'];
           $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td>'.date('F Y', strtotime($startmonth)).'</td><td>'.formatCurrency($month1).'</td>';
       }    
       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date LIKE '".$previousyear."-%'");

       while ($data = @mysql_fetch_array ($result)) {
           $month2 = $data['SUM((amountin-fees-amountout)/rate)'];

           if (isset($month1,$month2)) {
               $diff = number_format(((($month1-$month2)/$month2)*100),1);
               if ($diff < 0) {
                   $diff = '<font color=red>'.$diff.'%';
               }
               else {
                   $diff .= '%';
               }
           }
           else {
               $diff = '';
           }
           $content .= '<td>'.$diff.'</td><td>'.formatCurrency($month2).'</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");
?>

[Chris74]

Absolutely perfect Brian - thank you so much!