Upcoming automatic invoices

[swilders]

I've created a report that summarises upcoming renewals on WHMCS, I hope someone will find this useful.

 

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

if(!isset($calculate))
{
$startday = date("d");
$startmonth = date("m");
$startyear=date("Y");
$endday = date("d",mktime(0,0,0,date("m"),date("d")+7,date("Y")));
$endmonth = date("m",mktime(0,0,0,date("m"),date("d")+7,date("Y")));
$endyear=date("Y",mktime(0,0,0,date("m"),date("d")+7,date("Y")));
$startdate = $startyear.$startmonth.$startday;
$enddate=mktime(23,59,59,date("m"),date("d")+7,date("Y"));
$enddate = date("Y-m-d",$enddate);
}

$reportdata["title"] = "Upcoming Automatic Invoices";
$reportdata["description"] = "A summary of invoices that are due to be automatically created.";

$reportdata["headertext"] = "<form method=\"post\" action=\"$PHP_SELF?report=$report&calculate=true\"><center>Start Date: <select name=\"startday\">";

for ( $counter = 1; $counter <= 31; $counter += 1) {

$reportdata["headertext"] .= "<option";

if ($counter==$startday) { $reportdata["headertext"] .= " selected"; }

$reportdata["headertext"] .= ">$counter";

}

$reportdata["headertext"] .= "</select> <select name=\"startmonth\">";

for ( $counter = 1; $counter <= 12; $counter += 1) {

$reportdata["headertext"] .= "<option";

if ($counter==$startmonth) { $reportdata["headertext"] .= " selected"; }

$reportdata["headertext"] .= ">$counter";

}

$reportdata["headertext"] .= "</select> <select name=\"startyear\">";

for ( $counter = 1998; $counter <= 2010; $counter += 1) {

$reportdata["headertext"] .= "<option";

if ($counter==$startyear) { $reportdata["headertext"] .= " selected"; }

$reportdata["headertext"] .= ">$counter";

}

$reportdata["headertext"] .= "</select> End Date: <select name=\"endday\">";

for ( $counter = 1; $counter <= 31; $counter += 1) {

$reportdata["headertext"] .= "<option";

if ($counter==$endday) { $reportdata["headertext"] .= " selected"; }

$reportdata["headertext"] .= ">$counter";

}

$reportdata["headertext"] .= "</select> <select name=\"endmonth\">";

for ( $counter = 1; $counter <= 12; $counter += 1) {

$reportdata["headertext"] .= "<option";

if ($counter==$endmonth) { $reportdata["headertext"] .= " selected"; }

$reportdata["headertext"] .= ">$counter";

}

$reportdata["headertext"] .= "</select> <select name=\"endyear\">";

for ( $counter = date("Y"); $counter <= date("Y")+1; $counter += 1) {

$reportdata["headertext"] .= "<option";

if ($counter==$endyear) { $reportdata["headertext"] .= " selected"; }

$reportdata["headertext"] .= ">$counter";

}

$reportdata["headertext"] .= "</select> <input type=\"submit\" value=\"Generate Report\"></form>"; 





$startday = str_pad($startday,2,"0",STR_PAD_LEFT);

$startmonth = str_pad($startmonth,2,"0",STR_PAD_LEFT);

$endday = str_pad($endday,2,"0",STR_PAD_LEFT);

$endmonth = str_pad($endmonth,2,"0",STR_PAD_LEFT);



$startdate = $startyear."-".$startmonth."-".$startday;

$enddate = $endyear."-".$endmonth."-".$endday;

$reportdata["tableheadings"] = array("Next Invoice Due","Client","Product/Service","OrderID","Total");
$reportdata["tablevalues"][] = array("**<B>Upcoming/Active</B>");
$cancelrequests=mysql_query("SELECT relid FROM tblcancelrequests");
while($cancelrequestsrow=mysql_fetch_array($cancelrequests))
{
$cancellations[]=$cancelrequestsrow[0];
}
$sql="SELECT DATE_FORMAT(tblhosting.nextinvoicedate,'%d/%m/%Y') AS NextDue, tblclients.id, tblclients.firstname, tblclients.lastname, tblproducts.name, tblhosting.orderid, tblhosting.amount, tblhosting.id AS hostingid FROM tblhosting, tblclients, tblproducts WHERE tblhosting.userid = tblclients.id AND tblhosting.packageid = tblproducts.id AND tblhosting.billingcycle != 'Free Account' AND tblhosting.nextinvoicedate>='$startdate' AND tblhosting.nextinvoicedate<='$enddate' AND (tblhosting.domainstatus='Active' OR tblhosting.domainstatus='Pending' OR tblhosting.domainstatus='Suspended') ORDER BY tblhosting.nextinvoicedate";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
if(!in_array($row[7],$cancellations))
{
	if($row['orderid'])
		$reportdata["tablevalues"][] = array($row['NextDue'],'<a href="clientssummary.php?userid='.$row['id'].'">'.$row['firstname']." ".$row['lastname'].'</a>','<a href="clientshosting.php?userid='.$row['id'].'&hostingid='.$row['hostingid'].'">'.$row['name'].'</a>','<a href="orders.php?action=view&id='.$row['orderid'].'">'.$row['orderid'].'</a>',$CONFIG["CurrencySymbol"].format_as_currency($row['amount']));
	else
		$reportdata["tablevalues"][] = array($row['NextDue'],'<a href="clientssummary.php?userid='.$row['id'].'">'.$row['firstname']." ".$row['lastname'].'</a>','<a href="clientshosting.php?userid='.$row['id'].'&hostingid='.$row['hostingid'].'">'.$row['name'],'-',$CONFIG["CurrencySymbol"].format_as_currency($row['amount']));
	$amount+=$row['amount'];
}
}
$reportdata["tablevalues"][] = array("**<B>Overdue/Pending</B>");
$sql="SELECT DATE_FORMAT(tblhosting.nextinvoicedate,'%d/%m/%Y') AS NextDue, tblclients.id, tblclients.firstname, tblclients.lastname, tblproducts.name, tblhosting.orderid, tblhosting.amount, tblhosting.id AS hostingid FROM tblhosting, tblclients, tblproducts WHERE tblhosting.userid = tblclients.id AND tblhosting.packageid = tblproducts.id AND tblhosting.billingcycle != 'Free Account' AND tblhosting.nextinvoicedate<='$startdate' AND (tblhosting.domainstatus='Active' OR tblhosting.domainstatus='Pending' OR tblhosting.domainstatus='Suspended') ORDER BY tblhosting.nextinvoicedate";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
if(!in_array($row[7],$cancellations))
{
	if($row['orderid'])
		$reportdata["tablevalues"][] = array($row['NextDue'],'<a href="clientssummary.php?userid='.$row['id'].'">'.$row['firstname']." ".$row['lastname'].'</a>','<a href="clientshosting.php?userid='.$row['id'].'&hostingid='.$row['hostingid'].'">'.$row['name'].'</a>','<a href="orders.php?action=view&id='.$row['orderid'].'">'.$row['orderid'].'</a>',$CONFIG["CurrencySymbol"].format_as_currency($row['amount']));
	else
		$reportdata["tablevalues"][] = array($row['NextDue'],'<a href="clientssummary.php?userid='.$row['id'].'">'.$row['firstname']." ".$row['lastname'].'</a>','<a href="clientshosting.php?userid='.$row['id'].'&hostingid='.$row['hostingid'].'">'.$row['name'],'-',$CONFIG["CurrencySymbol"].format_as_currency($row['amount']));
	$amount+=$row['amount'];
}
}
$data["footertext"]="<p align=\"right\"><b>Total Income:</b> ".$CONFIG["CurrencySymbol"].format_as_currency($amount)."</p>";
?>

[Vincent Vega]

An adittion to this, add on line 134 the following for none recurring items;

AND tblhosting.nextinvoicedate != '0000-00-00 00:00:00'

[readywire]

Thanks, Swilders! This report is fantastic.

[stormy]

This is great, I could use more renewal-related reports!

[netwood]

Excuse the newbie question but how do I run this report?

How do I add it to WHMCS?

[sohouk]

Excuse the newbie question but how do I run this report?

How do I add it to WHMCS?

 

To create a report:

 

Copy all the code into a new blank php document.

 

If you do not have a web editor, you can just copy and paste into Notepad.

 

Save the file with a meaningful name, using underscores instead of spaces and with a .php extension (be careful to erase the .txt extension if using Notepad)

 

Upcoming_automatic_invoices.php

 

Upload your file to whmcs/modules/reports/ and thats it!

 

You can then check the Reports tab in WHMCS and if its all worked it will appear there, though for some reason its not an alphabetical listing so you have to look for it.

 

Trevor

[flyride]

Very handy, thank you!