Jump to content

Report: Projected Income Summary v0.1

othellotech

By othellotech
in Third Party Add-ons

 Share

Recommended Posts

othellotech Senior Member

othellotech

Posted
Posted

Similar to the WHMCS recurring income report with the following mods....

 

1. it includes biennial income column and 1/2 of that amount in the annual totals

2. it correctly shows the addons rather than repeating the last server over ad over

3. it gets the amounts for the domains rather than showing 0.00

4. it ignores products you've not sold any of

5. takes up less paper by having product group headings rather than duplicating the group on each line

 

save in your reports folder as projected_income.php

 

<?php

$reportdata["title"] = "Projected Income Summary ";
$reportdata["description"] = "Recurring Income by Active Product / Income Projection by Rob for www.OthelloTech.net";

$reportdata["tableheadings"] = array("Item Name","Monthly Revenue","Quarterly Revenue","Semi-Annual Revenue","Annual Revenue","Biennial Revenue","Total Yearly Revenue");

$reportdata["tablevalues"][] = array("**[b]Products/Services[/b]");

$query = "SELECT tblproducts.*,tblproductgroups.name AS groupname FROM tblproducts INNER JOIN tblproductgroups ON tblproductgroups.id = tblproducts.gid ORDER BY name ASC";
$result=mysql_query($query);

$lastgroup = "";

while ($data = mysql_fetch_array($result)) 
{
$id = $data["id"];
$groupname = $data["groupname"];
$productname = $data["name"];
$monthly = 0;
$monthlycost = 0;
$quarterly = 0;
$semiannually = 0;
$annually = 0;
$biannually = 0;
$total = 0;

$query2 = "SELECT * FROM tblhosting WHERE domainstatus!='Terminated' AND billingcycle!='Free Account' AND billingcycle!='One Time' AND packageid='$id'"; 
$result2 = mysql_query($query2);
while ($data2 = mysql_fetch_array($result2))
{
	$amount = $data2["amount"];
	$billingcycle = $data2["billingcycle"];

	if($billingcycle=="Monthly")
	{
		$monthly=$monthly+$amount;
	}
	elseif($billingcycle=="Quarterly")
	{
		$quarterly=$quarterly+$amount;
	}
	elseif($billingcycle=="Semi-Annually")
	{
		$semiannually=$semiannually+$amount;
	}
	elseif($billingcycle=="Annually")
	{
		$annually=$annually+$amount;
	}
	elseif($billingcycle=="Biennially")
	{
		$biannually=$biannually+$amount;
	}
}
$monthly=number_format($monthly,2,".","");
$quarterly=number_format($quarterly,2,".","");
$semiannually=number_format($semiannually,2,".","");
$annually=number_format($annually,2,".","");
$biannually=number_format($biannually,2,".","");

//RDG Note
//Where does it get $monthlycost from ???
$servertotal=number_format( ( ($monthly*12)+($quarterly*4)+($semiannually*2)+$annually+($biannually/2) - ($monthlycost*12) ) ,2,".","");

$overalltotal=$overalltotal+$servertotal;

if ($servertotal >= "0.01")
{
	if ($lastgroup != $groupname)
	{
		$lastgroup = $groupname ;	
		$reportdata["tablevalues"][] = array("**[i]$groupname[/i]");
	}
	$reportdata["tablevalues"][] = array("$productname",$CONFIG["CurrencySymbol"]."$monthly",$CONFIG["CurrencySymbol"]."$quarterly",$CONFIG["CurrencySymbol"]."$semiannually",$CONFIG["CurrencySymbol"]."$annually",$CONFIG["CurrencySymbol"]."$biannually",$CONFIG["CurrencySymbol"]."$servertotal");
}
}

$reportdata["tablevalues"][] = array("**[b]Products Addons[/b]");

$query = "SELECT DISTINCT name FROM tblhostingaddons WHERE status!='Terminated' AND billingcycle!='One Time' AND billingcycle!='Free'";  
$result = mysql_query($query);
while($data = mysql_fetch_array($result))
{
$name = $data["name"];
$monthly = 0;
$quarterly = 0;
$semiannually = 0;
$annually = 0;
$biannually = 0;
$annualtotal = 0;

$query2 = "SELECT * FROM tblhostingaddons WHERE name='".$name."' AND status!='Terminated' AND billingcycle!='One Time' AND billingcycle!='Free'";  
$result2 = mysql_query($query2);
while($data2 = mysql_fetch_array($result2))
{
	$amount = $data2["recurring"];
	$billingcycle = $data2["billingcycle"];

	if($billingcycle=="Monthly")
	{
		$monthly=$monthly+$amount;
	}
	elseif($billingcycle=="Quarterly")
	{
		$quarterly=$quarterly+$amount;
	}
	elseif($billingcycle=="Semi-Annually")
	{
		$semiannually=$semiannually+$amount;
	}
	elseif($billingcycle=="Annually")
	{
		$annually=$annually+$amount;
	}
	elseif($billingcycle=="Biennially")
	{
		$biannually=$biannually+$amount;
	}
}
$monthly=number_format($monthly,2,".","");
$quarterly=number_format($quarterly,2,".","");
$semiannually=number_format($semiannually,2,".","");
$annually=number_format($annually,2,".","");
$biannually=number_format($biannually,2,".","");
$servertotal=number_format( ( ($monthly*12)+($quarterly*4)+($semiannually*2)+$annually+($biannually/2) ) ,2,".","");

$overalltotal=$overalltotal+$servertotal;

if ($servertotal >= "0.01")
{
	$reportdata["tablevalues"][] = array("$name",$CONFIG["CurrencySymbol"]."$monthly",$CONFIG["CurrencySymbol"]."$quarterly",$CONFIG["CurrencySymbol"]."$semiannually",$CONFIG["CurrencySymbol"]."$annually",$CONFIG["CurrencySymbol"]."$biannually",$CONFIG["CurrencySymbol"]."$servertotal");
}
}

$reportdata["tablevalues"][] = array("**[b]Domains[/b]");

$annually = 0;
$query = "SELECT * FROM tbldomains WHERE status!='Expired'";  
$result=mysql_query($query);
while($data = mysql_fetch_array($result))
{
$amount = $data["recurringamount"];
$registrationperiod = $data["registrationperiod"];
if ($registrationperiod < 1) 
{
	$registrationperiod=1;
}
$annually=$annually+($amount/$registrationperiod);
}
$annually=number_format($annually,2,".","");
$servertotal=number_format($annually,2,".","");
$overalltotal=$overalltotal+$servertotal;

$reportdata["tablevalues"][] = array("Domains","-","-","-",$CONFIG["CurrencySymbol"]."$annually","-",$CONFIG["CurrencySymbol"]."$servertotal");

$overalltotal=number_format($overalltotal,2,".","");

$data["footertext"]="[b]Total Income:[/b] ".$CONFIG["CurrencySymbol"].$overalltotal;

?>

0
Link to comment
Share on other sites
  • 1 month later...
DavidJ Member

DavidJ

Posted
Posted

Very nice, thank you for the report :)

I must admit I came into the thread thinking it was some sort of 'projection' system that would calculate some averages & 'guess' how much you would be making by such-and-such a date.

 

But this is fairly nice, it's cool to have more details like on this report :)

0
Link to comment
Share on other sites
  • 2 weeks later...
G Swanepoel Junior Member

G Swanepoel

Posted
Posted

Hi Guys,

 

I posted a topic two weeks ago but no reply. Maybe you can help. I need a simple report. Well easy for you but impossible for me. All I want is the following query :SELECT * FROM `tblinvoices` WHERE `tax` <> 0 AND `status` = 'paid': with totals on 'subtotal' 'total' and 'tax' all in a nice report. Please note that I am a newby with no experience in php.

 

 

Any help will me apreciated.

 

 

Thanks

 

 

Swannie

0
Link to comment
Share on other sites
G Swanepoel Junior Member

G Swanepoel

Posted
Posted

WOW do I feel like an IDIOT. The report I wanted help with is already in the report section under a different name. Changed it slightly and it works fine. I know now why no one replied.......

 

One thing though, is it possible to give my accountant access to the reports without giving him access to the rest of WHMCS?

 

 

Swannie

0
Link to comment
Share on other sites
  • 3 weeks later...
Tech Entrance Senior Member

Tech Entrance

Posted
Posted
WOW do I feel like an IDIOT. The report I wanted help with is already in the report section under a different name. Changed it slightly and it works fine. I know now why no one replied.......

 

One thing though, is it possible to give my accountant access to the reports without giving him access to the rest of WHMCS?

 

 

Swannie

 

Export the report in MS access file (CSV) or print it and hand it to him :wink:

0
Link to comment
Share on other sites
  • 2 months later...
  • 1 year later...
tqhosting Member

tqhosting

Posted
Posted

Hi, I've tried to modify this report, unfortunately I'm not familiar enough with the code to be able to do it successfully. This report is pretty much what we're looking for, but I would like it to display ONLY the product groups, not each individual product and only the product group's revenue total for each billing cycle (not just yearly total but broken down by each billing cycle).

 

I've attempted to take out this line:$productname = $data["name"]; but it still displays each line item just without the name.

 

Is this possible? I'm sure it is, I'm just too stupid to figure it out ;)

0
Link to comment
Share on other sites
tqhosting Member

tqhosting

Posted
Posted (edited)

OK, I've gotten it to sort by Product Group. I found an error in the way it calculated monthly totals. it added several hundred over what it should actually be, so I corrected that, and I also noticed the "Biennially" functions were all mis-spelled as "Biannually" so that field wasn't calculating at all. Here is the updated code for that:

<?php

$reportdata["title"] = "Product Group Income ";
$reportdata["description"] = "Modified by KT";

$reportdata["tableheadings"] = array("Product Group","Monthly","Quarterly","Semi-Annual","Annual","Biennial","Total Yearly Income");

$query = "SELECT tblproducts.*,tblproductgroups.name AS groupname FROM tblproducts INNER JOIN tblproductgroups ON tblproductgroups.id =
tblproducts.gid ORDER BY groupname ASC";
$result=mysql_query($query);

$lastgroup = "";

$result=mysql_query($query);
while ($data = mysql_fetch_array($result)) {
       $id = $data["id"];
       $groupname = $data["groupname"];
       $productname = $data["name"];
       $monthly = 0;
       $quarterly = 0;
       $semiannually = 0;
       $annually = 0;
       $biennially = 0;
       $total = 0;
       $query2 = "SELECT * FROM tblhosting WHERE (domainstatus='Active' OR domainstatus='Suspended') AND billingcycle!='Free Account' AND billingcycle!='One Time' AND packageid='$id'";
       $result2=mysql_query($query2);
       while($data = mysql_fetch_array($result2)){
               $amount = $data["amount"];
               $billingcycle = $data["billingcycle"];
               if($billingcycle=="Monthly"){
                       $monthly=$monthly+$amount;
               }elseif($billingcycle=="Quarterly"){
                       $quarterly=$quarterly+$amount;
               }elseif($billingcycle=="Semi-Annually"){
                       $semiannually=$semiannually+$amount;
               }elseif($billingcycle=="Annually"){
                       $annually=$annually+$amount;
               }elseif($billingcycle=="Biennially"){
                       $biennially=$biennially+$amount;
               }
       }
       $monthly=number_format($monthly,2,".","");
       $quarterly=number_format($quarterly,2,".","");
       $semiannually=number_format($semiannually,2,".","");
       $annually=number_format($annually,2,".","");
       $biennially=number_format($biennially,2,".","");

       //RDG Note
       //Where does it get $monthlycost from ???
       $servertotal=number_format( ( ($monthly*12)+($quarterly*4)+($semiannually*2)+$annually+($biennially/2) - ($monthlycost*12) ) ,2,".","");

       $overalltotal=$overalltotal+$servertotal;

       if ($servertotal >= "0.01")
       {
               if ($lastgroup != $groupname)
               {
                       $lastgroup = $groupname ;
                       $reportdata["tablevalues"][] = array("**$groupname");
               }
               $reportdata["tablevalues"][] =
array("$productname",$CONFIG["CurrencySymbol"]."$monthly",$CONFIG["CurrencySymbol"]."$quarterly",$CONFIG["CurrencySymbol"]."$semiannually",$CONFIG["CurrencySymbol"]."$annually",$CONFIG["CurrencySymbol"]."$biennially",$CONFIG["CurrencySymbol"]."$servertotal");
       }

}


$reportdata["tablevalues"][] = array("**Products Addons");

$query = "SELECT DISTINCT name FROM tblhostingaddons WHERE status!='Terminated' AND billingcycle!='One Time' AND billingcycle!='Free'";
$result = mysql_query($query);
while($data = mysql_fetch_array($result))
{
       $name = $data["name"];
       $monthly = 0;
       $quarterly = 0;
       $semiannually = 0;
       $annually = 0;
       $biennially = 0;
       $annualtotal = 0;

       $query2 = "SELECT * FROM tblhostingaddons WHERE name='".$name."' AND status!='Terminated' AND billingcycle!='One Time' AND
billingcycle!='Free'";
       $result2 = mysql_query($query2);
       while($data2 = mysql_fetch_array($result2))
       {
               $amount = $data2["recurring"];
               $billingcycle = $data2["billingcycle"];

               if($billingcycle=="Monthly")
               {
                       $monthly=$monthly+$amount;
               }
               elseif($billingcycle=="Quarterly")
               {
                       $quarterly=$quarterly+$amount;
               }
               elseif($billingcycle=="Semi-Annually")
               {
                       $semiannually=$semiannually+$amount;
               }
               elseif($billingcycle=="Annually")
               {
                       $annually=$annually+$amount;
               }
               elseif($billingcycle=="Biennially")
               {
                       $biennially=$biennially+$amount;
               }
       }
       $monthly=number_format($monthly,2,".","");
       $quarterly=number_format($quarterly,2,".","");
       $semiannually=number_format($semiannually,2,".","");
       $annually=number_format($annually,2,".","");
       $biennially=number_format($biennially,2,".","");
       $servertotal=number_format( ( ($monthly*12)+($quarterly*4)+($semiannually*2)+$annually+($biennially/2) ) ,2,".","");

       $overalltotal=$overalltotal+$servertotal;

       if ($servertotal >= "0.01")
       {
               $reportdata["tablevalues"][] =
array("$name",$CONFIG["CurrencySymbol"]."$monthly",$CONFIG["CurrencySymbol"]."$quarterly",$CONFIG["CurrencySymbol"]."$semiannually",$CONFIG["CurrencySymbol"]."$annually",$CONFIG["CurrencySymbol"]."$biennially",$CONFIG["CurrencySymbol"]."$servertotal");
       }

}

$reportdata["tablevalues"][] = array("**Domains");

$annually = 0;
$query = "SELECT * FROM tbldomains WHERE status!='Expired'";
$result=mysql_query($query);
while($data = mysql_fetch_array($result))
{
       $amount = $data["recurringamount"];
       $registrationperiod = $data["registrationperiod"];
       if ($registrationperiod < 1)
       {
               $registrationperiod=1;
       }
       $annually=$annually+($amount/$registrationperiod);
}
$annually=number_format($annually,2,".","");
$servertotal=number_format($annually,2,".","");
$overalltotal=$overalltotal+$servertotal;

$reportdata["tablevalues"][] =
array("Domains","-","-","-",$CONFIG["CurrencySymbol"]."$annually","-",$CONFIG["CurrencySymbol"]."$servertotal");

$overalltotal=number_format($overalltotal,2,".","");

$data["footertext"]="Total Income: ".$CONFIG["CurrencySymbol"].$overalltotal;

?>

 

The only thing I would like to see now is a total of each group column:

 

Eg:

 

Product Group Monthly

service $9.00

service $9.00

Totals $18.00

 

 

can someone help with this?

Edited by tqhosting
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