Widget To Display A Summary Of Fiscal Income For Multiple Periods

[brian!]

In a previous contribution, I posted the code that created an Admin widget to show income between two specified dates, e.g a Fiscal year instead of the Calendar year currently used by WHMCS.

 

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

 

i've now updated the widget coding in the above thread so that it only needs a starting day and month - it can then calculate the start of the current Fiscal year, the end of the Fiscal year, and automatically recalculate these values when a new fiscal year is started... additionally, there is a version that can show your Fiscal income for the last ten years.

 

note: i'm referring to Fiscal years, but it could equally be used to show months, quarters or whatever period you wish.

 

however, this post is about another widget that I have written, called "Fiscal Summary" - while similar in some ways to the "Fiscal Income" widget, it's output is more flexible and so in case of specific questions, i've put it in its own thread.

 

at the top right of the Admin Area homepage, your income is shown for three specific periods - Today, This Month and This Year - the latter refers to a calendar year (January - December).

 

those details are coded in an encrypted file and therefore cannot easily be modified - so this new widget currently displays the same three columns - but instead of displaying the income for the calendar year, it displays the income for the current Fiscal year.

 

 

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

 

    <?php

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

   function widget_fiscal_summary($vars) {
       global $_ADMINLANG;

   $startday = 1;
   $startmonth = 4;
   $currentyear = date('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)) );    

   $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="33%">'.$_ADMINLANG['billing']['incometoday'].'</td><td width="33%">'.$_ADMINLANG['billing']['incomethismonth'].'</td><td width="33%">'.$_ADMINLANG['billing']['incomethisyear'].'</td></tr>';

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE date = CURDATE()");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td class="textgreen"><b>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</b></td>';
       }

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<td class="textred"><b>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</td>';
       }

       $result = mysql_query("SELECT SUM(amountin-fees-amountout) FROM tblaccounts WHERE date BETWEEN '$startdate' AND '$enddate'");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<td class="textblack"><b>'.formatCurrency($data['SUM(amountin-fees-amountout)']).'</td>';
       }

       $content .= '</tr></table>';
       return array( 'title' => 'Fiscal Summary', 'content' => $content );

   }

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

   ?>

create a new file in your /modules/widgets/ directory called fiscal_summary.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 working correctly, the first two columns should currently match the output from the Income section of the Admin Area - only the third figure, This Year, should be different.

 

the only two variables that you will need to modify are the startday and startmonth of your fiscal year - so for 1st April, you would use...

 

    $startday = 1;
   $startmonth = 4;

now, refresh the admin homepage, and you should see the income for your Fiscal year.

 

ok, we now have a widget showing your income Today, This Month and This (Fiscal) Year - possibly useful, but nothing exciting...

 

but unlike the default income output on the admin homepage which can't currently be changed, this widget code can be modified to display income from any period(s) you want - so perhaps instead of showing income from "Today" and "This Month" (Calendar) which are already available from the Admin homepage, you might want to show income from "This Week" (the last 7 days), "This Month" (either last 4 weeks or last month from today, not calendar month) or maybe even the last quarter (last 3 months from today).

 

all that can be achieved by simply modifying the SQL queries in the widget code - exactly how will depend on what what period you want to use.

 

additionally, its display can be altered too - currently it just uses the same colours as the current Admin income summary output, but you could tweak this by altering the code and/or using css.

[djpete]

Thanks for your work Brian.

Greatly appreciated

[brian!]

minor update...

 

<?php

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

   function widget_fiscal_summary($vars) {
       global $_ADMINLANG;

   $startday = 1;
   $startmonth = 4;
   $currentyear = date('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)) );    

   $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="33%">'.$_ADMINLANG['billing']['incometoday'].'</td><td width="33%">'.$_ADMINLANG['billing']['incomethismonth'].'</td><td width="33%">'.$_ADMINLANG['billing']['incomethisyear'].'</td></tr>';

       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date = CURDATE()");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td class="textgreen"><b>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</b></td>';
       }

       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<td class="textred"><b>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</td>';
       }

       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date BETWEEN '$startdate' AND '$enddate'");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<td class="textblack"><b>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</td></tr></table>';
       }

       return array( 'title' => 'Fiscal Summary', 'content' => $content );

   }

   add_hook("AdminHomeWidgets",1,"widget_fiscal_summary");
?>

[brian!]

just a minor tweak to one of the SQL queries to make it more accurate / inclusive...

 

<?php

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

   function widget_fiscal_summary($vars) {
       global $_ADMINLANG;

   $startday = 1;
   $startmonth = 4;
   $currentyear = date('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)) );    

   $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="33%">'.$_ADMINLANG['billing']['incometoday'].'</td><td width="33%">'.$_ADMINLANG['billing']['incomethismonth'].'</td><td width="33%">'.$_ADMINLANG['billing']['incomethisyear'].'</td></tr>';

       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date = CURDATE()");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<tr bgcolor="#ffffff" style="text-align:center;"><td class="textgreen"><b>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</b></td>';
       }

       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<td class="textred"><b>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</td>';
       }

       $result = mysql_query("SELECT SUM((amountin-fees-amountout)/rate) FROM tblaccounts WHERE date BETWEEN '$startdate 00:00:00' AND '$enddate 23:59:59'");
       while ($data = @mysql_fetch_array ($result)) {
           $content .= '<td class="textblack"><b>'.formatCurrency($data['SUM((amountin-fees-amountout)/rate)']).'</td></tr></table>';
       }

       return array( 'title' => 'Fiscal Summary', 'content' => $content );

   }

   add_hook("AdminHomeWidgets",1,"widget_fiscal_summary");
?>  

[shadowtek]

@brian!  This is exactly what i have been looking for, unfortunately, doesn't seem to still work 😞

If you get a chance could you please take a look and see if you can get this working again for WHMCS v7.7.x

As always i appreciate your valuable input around the community

[brian!]

11 hours ago, shadowtek said:

This is exactly what i have been looking for, unfortunately, doesn't seem to still work 😞

WHMCS v7.7.1 isn't the problem - it works in v7.7.1, but what it won't like are those SQL queries which will be incompatible with PHP7 - that's simple enough to fix.

11 hours ago, shadowtek said:

If you get a chance could you please take a look and see if you can get this working again for WHMCS v7.7.x

i'll try and have a widget weekend and get my widgets updated for compatibility with the latest releases - it should be easier to do them all in go once I get my head in "widget mode". 🙂

[shadowtek]

On 7/03/2019 at 3:04 AM, brian! said:

WHMCS v7.7.1 isn't the problem - it works in v7.7.1, but what it won't like are those SQL queries which will be incompatible with PHP7 - that's simple enough to fix.

i'll try and have a widget weekend and get my widgets updated for compatibility with the latest releases - it should be easier to do them all in go once I get my head in "widget mode". 🙂

Heya @brian!

Did you ever manage to get to your "Widget Weekend"? Still, need a financial year widget  🙂

[arachon]

Hi,

unfortunately this widget does not work with version 7.8.3 (and we use PHP 5.6, not PHP 7).

Would be great if we could use this widget with the latest version !

 

THANKS !

 

[nikitas]

Still working 😉 thanks @brian!