Jump to content

Report to display income forecast minus server expenses

Real Webspace
 Share

Is it of any value to you?  

16 members have voted

  1. 1. Is it of any value to you?


Recommended Posts

Real Webspace Senior Member

Real Webspace

Posted
Posted

I modified two existing reports to come up with the following.

 

"Gross Income Forecast

 

This report shows the projected income for each month of the year if all active services are renewed within that month but subtracts expenses as defined in server setup pages"

 

Please feel free to do what ever you need to change or modify the code. This is only my second report attempt and I don't know how to code.

 

I use this module to simply let me know my cash flow. By adding fake servers, I can add to the expenses like ISP expense.

gross_income_report.zip

0
Link to comment
Share on other sites
Real Webspace Senior Member

Real Webspace

Posted
  • Author
Posted

This should have probably been posted in user contributions right...

 

Incase you don't like the zip file, here is the code, simply place it in a php file named "gross_income_report.php" in /whmcs/modules/reports:

 

<?php

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

$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

$reportdata["title"] = "Gross Income Forecast";
$reportdata["description"] = "This report shows the projected income for each month of the year if all active services are renewed within that month but subtracts expenses as defined in server setup pages";

//////////////////////////////////BEGIN EXPENSE Gather and total server expenses
$currency = getCurrency('','1');

$query = "SELECT * FROM tblservers ORDER BY name ASC";
$result=mysql_query($query);
while ($data = mysql_fetch_array($result)) {
$name = $data["name"];
$monthlycost = $data["monthlycost"];
$totalexpense += $monthlycost;
$reportdata["tablevalues"][] = array("$name","$monthlycost");
}
$reportdata["tablevalues"][] = array("","","","","","","","");

$data["footertext"] = "";
/////////////////// END OF Expense report

$reportdata["currencyselections"] = true;

$reportdata["tableheadings"] = array("Month/Expense","Monthly","Quarterly","Semi-Annual","Annual","Biennial","Total Gross","Total Net");

$totals = array();

$result = select_query("tblhosting","",array("domainstatus"=>"Active","currency"=>$currencyid),"","","","tblclients ON tblclients.id=tblhosting.userid");
while ($data = mysql_fetch_array($result)) {
   $recurringamount = $data["amount"];
   $nextduedate = $data["nextduedate"];
   $billingcycle = $data["billingcycle"];
   $nextduedate = explode("-",$nextduedate);
   $year = $nextduedate[0];
   $month = $nextduedate[1];
   if ($billingcycle=="Monthly") {
       $recurrence = 1;
   } elseif ($billingcycle=="Quarterly") {
       $recurrence = 3;
   } elseif ($billingcycle=="Semi-Annually") {
       $recurrence = 6;
   } elseif ($billingcycle=="Annually") {
       $recurrence = 12;
   } elseif ($billingcycle=="Biennially") {
       $recurrence = 24;
   } else {
       $recurrence = 24;
   }
   $recurrences = 24/$recurrence;
   for ($i=0;$i<=24;$i+=$recurrence) {
       $new_time = mktime(0,0,0,$month+$i,1,$year);
       $totals[date("Y",$new_time)][date("m",$new_time)][$recurrence] += $recurringamount;
   }
}

$result = select_query("tbldomains","",array("tbldomains.status"=>"Active","currency"=>$currencyid),"","","","tblclients ON tblclients.id=tbldomains.userid");
while ($data = mysql_fetch_array($result)) {
   $recurringamount = $data["recurringamount"];
   $nextduedate = $data["nextduedate"];
   $regperiod = $data["registrationperiod"];
   $nextduedate = explode("-",$nextduedate);
   $year = $nextduedate[0];
   $month = $nextduedate[1];
   if (!$regperiod) $regperiod = 1;
   $recurrence = $regperiod*12;
   $recurrences = 24/$recurrence;
   for ($i=0;$i<=24;$i+=$recurrence) {
       $new_time = mktime(0,0,0,$month+$i,1,$year);
       $totals[date("Y",$new_time)][date("m",$new_time)][$recurrence] += $recurringamount;
   }
}

for ($i=0;$i<=24;$i++) {
   $new_time = mktime(0,0,0,date("m")+$i,1,date("Y"));
   $months_array[date("Y",$new_time)][date("m",$new_time)] = "x";
}

$overallincome = 0;
$netmonthlyincome = 0;

foreach ($months_array AS $year=>$month) {
   foreach ($month AS $mon=>$x) {
       $monthlyincome = $totals[$year][$mon][1]+$totals[$year][$mon][3]+$totals[$year][$mon][6]+$totals[$year][$mon][12]+$totals[$year][$mon][24];
	$netmonthlyincome = $monthlyincome - $totalexpense;
       $overallincome += $monthlyincome;
       $reportdata["tablevalues"][] = array($months[$mon-1]." ".$year,formatCurrency($totals[$year][$mon][1]),formatCurrency($totals[$year][$mon][3]),formatCurrency($totals[$year][$mon][6]),formatCurrency($totals[$year][$mon][12]),formatCurrency($totals[$year][$mon][24]),formatCurrency($monthlyincome),formatCurrency($netmonthlyincome));

   }
}

$data["footertext"] = "<p><b>Total expenses monthly: ".formatCurrency($totalexpense)."</b></p>;

?>

0
Link to comment
Share on other sites
Inetbiz Senior Member

Inetbiz

Posted
Posted
This should have probably been posted in user contributions right...

 

Incase you don't like the zip file, here is the code, simply place it in a php file named "gross_income_report.php" in /whmcs/modules/reports:

 

<?php

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

$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

$reportdata["title"] = "Gross Income Forecast";
$reportdata["description"] = "This report shows the projected income for each month of the year if all active services are renewed within that month but subtracts expenses as defined in server setup pages";

//////////////////////////////////BEGIN EXPENSE Gather and total server expenses
$currency = getCurrency('','1');

$query = "SELECT * FROM tblservers ORDER BY name ASC";
$result=mysql_query($query);
while ($data = mysql_fetch_array($result)) {
$name = $data["name"];
$monthlycost = $data["monthlycost"];
$totalexpense += $monthlycost;
$reportdata["tablevalues"][] = array("$name","$monthlycost");
}
$reportdata["tablevalues"][] = array("","","","","","","","");

$data["footertext"] = "";
/////////////////// END OF Expense report

$reportdata["currencyselections"] = true;

$reportdata["tableheadings"] = array("Month/Expense","Monthly","Quarterly","Semi-Annual","Annual","Biennial","Total Gross","Total Net");

$totals = array();

$result = select_query("tblhosting","",array("domainstatus"=>"Active","currency"=>$currencyid),"","","","tblclients ON tblclients.id=tblhosting.userid");
while ($data = mysql_fetch_array($result)) {
   $recurringamount = $data["amount"];
   $nextduedate = $data["nextduedate"];
   $billingcycle = $data["billingcycle"];
   $nextduedate = explode("-",$nextduedate);
   $year = $nextduedate[0];
   $month = $nextduedate[1];
   if ($billingcycle=="Monthly") {
       $recurrence = 1;
   } elseif ($billingcycle=="Quarterly") {
       $recurrence = 3;
   } elseif ($billingcycle=="Semi-Annually") {
       $recurrence = 6;
   } elseif ($billingcycle=="Annually") {
       $recurrence = 12;
   } elseif ($billingcycle=="Biennially") {
       $recurrence = 24;
   } else {
       $recurrence = 24;
   }
   $recurrences = 24/$recurrence;
   for ($i=0;$i<=24;$i+=$recurrence) {
       $new_time = mktime(0,0,0,$month+$i,1,$year);
       $totals[date("Y",$new_time)][date("m",$new_time)][$recurrence] += $recurringamount;
   }
}

$result = select_query("tbldomains","",array("tbldomains.status"=>"Active","currency"=>$currencyid),"","","","tblclients ON tblclients.id=tbldomains.userid");
while ($data = mysql_fetch_array($result)) {
   $recurringamount = $data["recurringamount"];
   $nextduedate = $data["nextduedate"];
   $regperiod = $data["registrationperiod"];
   $nextduedate = explode("-",$nextduedate);
   $year = $nextduedate[0];
   $month = $nextduedate[1];
   if (!$regperiod) $regperiod = 1;
   $recurrence = $regperiod*12;
   $recurrences = 24/$recurrence;
   for ($i=0;$i<=24;$i+=$recurrence) {
       $new_time = mktime(0,0,0,$month+$i,1,$year);
       $totals[date("Y",$new_time)][date("m",$new_time)][$recurrence] += $recurringamount;
   }
}

for ($i=0;$i<=24;$i++) {
   $new_time = mktime(0,0,0,date("m")+$i,1,date("Y"));
   $months_array[date("Y",$new_time)][date("m",$new_time)] = "x";
}

$overallincome = 0;
$netmonthlyincome = 0;

foreach ($months_array AS $year=>$month) {
   foreach ($month AS $mon=>$x) {
       $monthlyincome = $totals[$year][$mon][1]+$totals[$year][$mon][3]+$totals[$year][$mon][6]+$totals[$year][$mon][12]+$totals[$year][$mon][24];
	$netmonthlyincome = $monthlyincome - $totalexpense;
       $overallincome += $monthlyincome;
       $reportdata["tablevalues"][] = array($months[$mon-1]." ".$year,formatCurrency($totals[$year][$mon][1]),formatCurrency($totals[$year][$mon][3]),formatCurrency($totals[$year][$mon][6]),formatCurrency($totals[$year][$mon][12]),formatCurrency($totals[$year][$mon][24]),formatCurrency($monthlyincome),formatCurrency($netmonthlyincome));

   }
}

$data["footertext"] = "<p><b>Total expenses monthly: ".formatCurrency($totalexpense)."</b></p>;

?>

 

And if an installation has taken currencies other than their own? I don't see this in your formula.

0
Link to comment
Share on other sites
  • 1 month later...
Real Webspace Senior Member

Real Webspace

Posted
  • Author
Posted
Not bad! :) But there is a quote (") miss on the end of the last line. It should read:

 

Thanks :)

 

TO ANYONE USING: PLEASE ADD THE QUOTATION MARK or copy the below fixed version (Thanks to AeroWeb):

 

<?php

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

$months = array('January','February','March','April','May','June','July','August','September','October','November','December');

$reportdata["title"] = "Gross Income Forecast";
$reportdata["description"] = "This report shows the projected income for each month of the year if all active services are renewed within that month but subtracts expenses as defined in server setup pages";

//////////////////////////////////BEGIN EXPENSE Gather and total server expenses
$currency = getCurrency('','1');

$query = "SELECT * FROM tblservers ORDER BY name ASC";
$result=mysql_query($query);
while ($data = mysql_fetch_array($result)) {
$name = $data["name"];
$monthlycost = $data["monthlycost"];
$totalexpense += $monthlycost;
$reportdata["tablevalues"][] = array("$name","$monthlycost");
}
$reportdata["tablevalues"][] = array("","","","","","","","");

$data["footertext"] = "";
/////////////////// END OF Expense report

$reportdata["currencyselections"] = true;

$reportdata["tableheadings"] = array("Month/Expense","Monthly","Quarterly","Semi-Annual","Annual","Biennial","Total Gross","Total Net");

$totals = array();

$result = select_query("tblhosting","",array("domainstatus"=>"Active","currency"=>$currencyid),"","","","tblclients ON tblclients.id=tblhosting.userid");
while ($data = mysql_fetch_array($result)) {
   $recurringamount = $data["amount"];
   $nextduedate = $data["nextduedate"];
   $billingcycle = $data["billingcycle"];
   $nextduedate = explode("-",$nextduedate);
   $year = $nextduedate[0];
   $month = $nextduedate[1];
   if ($billingcycle=="Monthly") {
       $recurrence = 1;
   } elseif ($billingcycle=="Quarterly") {
       $recurrence = 3;
   } elseif ($billingcycle=="Semi-Annually") {
       $recurrence = 6;
   } elseif ($billingcycle=="Annually") {
       $recurrence = 12;
   } elseif ($billingcycle=="Biennially") {
       $recurrence = 24;
   } else {
       $recurrence = 24;
   }
   $recurrences = 24/$recurrence;
   for ($i=0;$i<=24;$i+=$recurrence) {
       $new_time = mktime(0,0,0,$month+$i,1,$year);
       $totals[date("Y",$new_time)][date("m",$new_time)][$recurrence] += $recurringamount;
   }
}

$result = select_query("tbldomains","",array("tbldomains.status"=>"Active","currency"=>$currencyid),"","","","tblclients ON tblclients.id=tbldomains.userid");
while ($data = mysql_fetch_array($result)) {
   $recurringamount = $data["recurringamount"];
   $nextduedate = $data["nextduedate"];
   $regperiod = $data["registrationperiod"];
   $nextduedate = explode("-",$nextduedate);
   $year = $nextduedate[0];
   $month = $nextduedate[1];
   if (!$regperiod) $regperiod = 1;
   $recurrence = $regperiod*12;
   $recurrences = 24/$recurrence;
   for ($i=0;$i<=24;$i+=$recurrence) {
       $new_time = mktime(0,0,0,$month+$i,1,$year);
       $totals[date("Y",$new_time)][date("m",$new_time)][$recurrence] += $recurringamount;
   }
}

for ($i=0;$i<=24;$i++) {
   $new_time = mktime(0,0,0,date("m")+$i,1,date("Y"));
   $months_array[date("Y",$new_time)][date("m",$new_time)] = "x";
}

$overallincome = 0;
$netmonthlyincome = 0;

foreach ($months_array AS $year=>$month) {
   foreach ($month AS $mon=>$x) {
       $monthlyincome = $totals[$year][$mon][1]+$totals[$year][$mon][3]+$totals[$year][$mon][6]+$totals[$year][$mon][12]+$totals[$year][$mon][24];
	$netmonthlyincome = $monthlyincome - $totalexpense;
       $overallincome += $monthlyincome;
       $reportdata["tablevalues"][] = array($months[$mon-1]." ".$year,formatCurrency($totals[$year][$mon][1]),formatCurrency($totals[$year][$mon][3]),formatCurrency($totals[$year][$mon][6]),formatCurrency($totals[$year][$mon][12]),formatCurrency($totals[$year][$mon][24]),formatCurrency($monthlyincome),formatCurrency($netmonthlyincome));

   }
}

$data["footertext"] = "<p><b>Total expenses monthly: ".formatCurrency($totalexpense)."</b></p>";

?>

0
Link to comment
Share on other sites
Real Webspace Senior Member

Real Webspace

Posted
  • Author
Posted

To those getting a blank page, are you placing it in a file named:

 

gross_income_report.php

 

in the folder

 

/whmcs_installation/modules/reports/

 

It should appear under the reports tab i.e. go to Reports > Gross Income Forecast, there's no need to navigate directly to it.

0
Link to comment
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.

×
 Share
Go to topic listing

  • 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

  I accept