Custom report: Monthly Income Forecast v0.1

[sgpaul]

Here is a report that list expected monthly renewals. Hope is useful for anyone.

 

Feel free to enhance/fix/contribute.

 

Pablo

 

<?php

//  Monthly Income Forecast v0.1 - Developed by sgpaul -- 2007-12-26

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

if ($year=="") {
$year=date("Y");
}

if ( !isset( $aggregate) ) $agregate = 0;

$reportdata["title"] = "Monthly Income Forecast for ".$year;
$reportdata["description"] = "This report shows forecast income for all year by month";

$reportdata["tableheadings"] = array("Month", "Renewals","Monthly","Annual","Biennially", "Other", "Total");

$grandtotal = 0;
$total_renewals = 0; 

$monthly = 0;

for ( $month = 1; $month <= 12; $month ++ )
{
$sql = 'select amount, billingcycle, nextduedate from tblhosting
where domainstatus="active" and nextduedate LIKE "'.$year.'-'.str_pad($month,2,"0",STR_PAD_LEFT).'-%"';

$result = mysql_query($sql);

$renewals = mysql_num_rows( $result );

if ( ! $aggregate ) $monthly  = 0;

$annually = 0;
$biennually = 0;
$other = 0;
$total = 0;
while ( $data = mysql_fetch_assoc( $result ) )
{
	switch( $data['billingcycle'] )
	{
		case 'Monthly':
			$monthly += $data['amount'];
			break;
		case 'Annually':
			$annually += $data['amount'];
			break;
		case 'Biennially':
			$biennually += $data['amount'];
			break;
		default:
			$other += $data['amount'];
			break;
	}
}

$total += $monthly + $annually + $biennually + $other;

$grandtotal += $total;

$total_renewals += $renewals;

$reportdata["tablevalues"][] = array($months[ $month ], number_format( $renewals, 0 ),number_format( $monthly, 2 ),number_format( $annually, 2 ), number_format( $biennually, 2 ),number_format( $other, 2 ), number_format( $total, 2 ) );


} //<--end of while

$reportdata["headertext"] = "Total Renewals: $total_renewals";
$reportdata["headertext"] = "<a href=\"$PHP_SELF?report=$report&year=$year&aggregate=".( ! $aggregate)."\">Toggle monthly aggregation</a>";

$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"].number_format( $grandtotal, 2 )."</span>
";
$data['footertext'] .= " </p>";

$data["footertext"].="<table width=90% align=center><tr><td>";

$data["footertext"].="<a href=\"$PHP_SELF?report=$report&aggregate=$aggregate&year=".($year-1)."\"><< ".($year-1)."</a>";

$data["footertext"].="</td><td align=right>";

$data["footertext"].="<a href=\"$PHP_SELF?report=$report&aggregate=$aggregate&year=".($year+1)."\"> ".($year+1)." >></a>";

$data["footertext"].="</td></tr></table>";

?>

[othellotech]

Surely all "monthly" hosting accounts should be included in *every* months projection ?

[quel]

Hi

 

It is a nice one. It is really good since we detected one account where billing was stopped because the due date was in the past.

 

I have taken the liberty to modify it a bit to enhance it and add a monthly detail with links. I think this makes the report very useful in order to analyze future income.

 

If you are lazy copying it, I have left a txt version here (wget it and change the file extension back to php)

http://www.comforthost.net/billing/modules/reports/Hosting_Forecast_monthly.txt

 

 

<?php

//  Monthly Income Forecast v0.1 - Developed by sgpaul -- 2007-12-26
//  Monthly Income Forecast v0.1 - 2007-12-29 Quel: corrected a couple of bugs. changed aggregation=on by default 
//  Monthly Income Forecast v0.2 - 2007-12-30 Quel: added monthly detail 

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

if ($year=="") {
$year=date("Y");
}

if ( !isset( $aggregate) ) $aggregate = true;

$reportdata["title"] = "Monthly Income Forecast for ".$year;
$reportdata["description"] = "This report shows forecast income for all year by month";
$reportdata["tableheadings"] = array("Month", "Renewals","Monthly","Annual","Biennially", "Other", "Total");

$grandtotal = 0;
$total_renewals = 0; 
$monthly = 0;

for ( $month = 1; $month <= 12; $month ++ )
{
$sql = 'select id,userid, domain,amount, billingcycle, nextduedate from tblhosting
where domainstatus="active" and nextduedate LIKE "'.$year.'-'.str_pad($month,2,"0",STR_PAD_LEFT).'-%"';

$result = mysql_query($sql);

$renewals = mysql_num_rows( $result ) ;

if ( ! $aggregate ) $monthly  = 0;

$annually = 0;
$biennually = 0;
$other = 0;
$total = 0;
if($openmonth==$month){
	$reportdata["tablevalues"][] = array('','',	'',	'',	'',	'',	'');
}
	while ( $data = mysql_fetch_assoc( $result ) )
{
	$thisdomainMonthly=0;
	$thisdomainAnnually=0;
	$thisdomainBiannually=0;
	$thisdomainOther=0;
	switch( $data['billingcycle'] )
	{
		case 'Monthly':
			$monthly += $data['amount']; $thisdomainMonthly=$data['amount'];
			break;
		case 'Annually':
			$annually += $data['amount'];$thisdomainAnnually=$data['amount'];
			break;
		case 'Biennially':
			$biennually += $data['amount'];$thisdomainBiannually=$data['amount'];
			break;
		default:
			$other += $data['amount'];$thisdomainOther=$data['amount'];
			break;
	}
	if($openmonth==$month){
		$reportdata["tablevalues"][] = array($months[ $month ],
			"<a href=".substr($_SERVER['PHP_SELF'], 0, strrpos($_SERVER['PHP_SELF'], '/'))."/clientshosting.php?userid=".$data['userid']."&hostingid=".$data['id'].">".$data['domain'].'</a>',
			number_format($thisdomainMonthly,2),
			number_format($thisdomainAnnually,2),
			number_format($thisdomainBiannually,2),
			number_format($thisdomainOther,2),
			'');
	}
}

$total += $monthly + $annually + $biennually + $other;

$grandtotal += $total;

$total_renewals += $renewals;

if($openmonth==$month){
	$reportdata["tablevalues"][] = array('','',	'',	'',	'',	'',	'');
}
$reportdata["tablevalues"][] = array("<b>".$months[ $month ]."</b>",
	"<a href=$PHP_SELF?report=$report&aggregate=$aggregate&year=$year&openmonth=".$month .">".number_format($renewals,0).'</a>',
	number_format($monthly,2),
	number_format($annually,2),
	number_format($biennually,2),
	number_format($other,2),
	number_format($total,2));

} //<--end of while

$reportdata["headertext"] = "Total Renewals: $total_renewals";
$reportdata["headertext"] .= "<br><a href=\"$PHP_SELF?report=$report&year=$year&aggregate=".( ! $aggregate)."\">Toggle monthly aggregation</a>";
	if($openmonth>0){
$reportdata["headertext"] .= "     <a href=\"$PHP_SELF?report=$report&year=$year&aggregate=".( ! $aggregate)."&openmonth=0\">Close Monthly detail</a>";
	}
$data['footertext'] = "<p style=\"text-align:center\"><span style=\"font-size:2em; margin:10px auto; font-weight:bold;\">Total: ".$CONFIG["CurrencySymbol"].number_format( $grandtotal, 2 )."</span>
";
$data['footertext'] .= " </p>";

$data["footertext"].="<table width=90% align=center><tr><td>";

$data["footertext"].="<a href=\"$PHP_SELF?report=$report&aggregate=$aggregate&year=".($year-1)."\"><< ".($year-1)."</a>";

$data["footertext"].="</td><td align=right>";

$data["footertext"].="<a href=\"$PHP_SELF?report=$report&aggregate=$aggregate&year=".($year+1)."\"> ".($year+1)." >></a>";

$data["footertext"].="</td></tr></table>";

?>  

[Tech Entrance]

It shows "No Data for the Report"

 

I copied the one is the last post

[aXeR]

Nice report, am using this myself now

[redrat]

Thank you. The spirit of non-predatory community contributions is alive and kicking.

[AHATEMA]

returns a blank page to me any suggestions?

[stormy]

Nice! I wish someone could write a report like this, for past renewals. I'm looking into it but I don't even know where to start.

[mtk]

Hi

 

It is a nice one. It is really good since we detected one account where billing was stopped because the due date was in the past.

 

I have taken the liberty to modify it a bit to enhance it and add a monthly detail with links. I think this makes the report very useful in order to analyze future income.

 

If you are lazy copying it, I have left a txt version here (wget it and change the file extension back to php)

http://www.comforthost.net/billing/modules/reports/Hosting_Forecast_monthly.txt

 

 

how about a similar report for past/history analysis?

I want to know income only from hosting, and not other services...

[suhailc]

Hi,

 

I've just been introduced to the delightful world of custom WHMCS reports!

 

This one looks interesting. What exactly does the "Toggle monthly aggregation" do?

 

Regards,

Suhail.