Upcoming product/service renewals

swilders · March 29, 2010

WHMCS does a nice job of letting customers know when domains are coming up for renewal but I've always been a little baffled why we don't also have a feature to let them know when a product or service is coming up for renewal.

I wrote my own solution below, it isn't the cleanest code in the world but it works. Our emails are all formatted nicely in line with our regular emails but I've removed this formatting from the script below.

All you need to do is set this up as a daily cron job and it will send a nominated person an email 6 weeks before the renewal and the customer 4 weeks before. I've set the customer email to go to the nominated person in the code below so you don't send your customers anything until you have it working exactly as you want. This script will also place entries in the email and activity logs, and the email will also show up on the customer's account in both the admin and customer portals.

I hope someone else finds this as useful as we do. Our customers certainly appreciate being pre-warned of an invoice being raised - some even ask us to send it early as a result of this email (bonus!). The 6 week warning gives us chance to check with suppliers if the price is still valid (e.g. for antivirus renewals).

Maybe we'll see something like this in a future release, if I had more time I'd have put the emails into the WHMCS email templates but for now they're just coded in below.

<?
$hostname = "localhost";
$username = "";
$password = "";
$database = "";

// SET PERSON TO RECEIVE EARLY WARNING OF UPCOMING RENEWALS
// We have WHMCS tell us about a renewal 6 weeks away so we can check the details of the service (e.g. price) are correct and up-to-date
$today=date("Y-m-d",strtotime("+6 weeks"));
$earlywarnings="Account Manager <test@example.com>";
$whmcsinstall="http://www.mywhmcsinstall.com/";

$db = mysql_connect($hostname, $username, $password) OR die("Could not connect to database server.");
mysql_select_db($database,$db) OR die("Could not find database.");
$sql="SELECT DATE_FORMAT(tblhosting.nextinvoicedate,'%d/%m/%Y') AS NextDue, tblclients.id, tblclients.companyname, tblclients.firstname, tblclients.lastname, tblclients.email, tblproducts.name, tblhosting.domain, tblhosting.amount, tblhosting.id AS hostingid,tblproducts.gid FROM tblhosting, tblclients, tblproducts WHERE tblhosting.userid = tblclients.id AND tblhosting.packageid = tblproducts.id AND tblhosting.billingcycle != 'Free Account' AND tblhosting.nextinvoicedate='$today' 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($row['domain'])
	$extratext=" (".$row['domain'].")";
else
	$extratext="";
$to=$earlywarnings;
$subject="Upcoming service renewal";
$message="<html><head><title>".$subject."</title></head><body><p>Hi,</p><p>The following service is coming up for renewal on ".$row['NextDue'].":</p>";
$message.="
<p><strong>Customer</strong><br />
<a href=\"".$whmcsinstall."admin/clientssummary.php?userid=".$row['id']."\">".$row['companyname']."</a><br />
".$row['firstname']." ".$row['lastname']." (".$row['email'].")</p>
<p><strong>Service</strong><br />
<a href=\"".$whmcsinstall."admin/clientshosting.php?userid=".$row['id']."&hostingid=".$row['hostingid']."\">".$row['name']."$extratext</a><br />
£".$row['amount']."+VAT</p>

";
//We have a slight variation on the message if the service is in the IT support group
/*if($row['gid']==4)
	$extratext="pack needs to go out";
else*/
	$extratext="will be going out";
$message.="<p style=\"color:red;\">The renewal $extratext to the customer in 2 weeks so check all details (mainly the price) are correct as soon as possible.</p>
</body></html>";
$headers="MIME-Version: 1.0\r\n";
$headers.="From: $to\r\n";
//$headers.="To: ".stripslashes($row['FirstName'])." ".stripslashes($row['LastName'])." <".$row['Email'].">\r\n";
$headers.="Reply-To: $to\r\n";
$headers.="X-Mailer: PHP/".phpversion()."\r\n";
$headers.="Content-type: text/html; charset=iso-8859-1\r\n";
mail($to,$subject,$message,$headers,'-f'.$to);
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////
$today=date("Y-m-d",strtotime("+4 weeks"));
$sql="SELECT DATE_FORMAT(tblhosting.nextinvoicedate,'%d/%m/%Y') AS NextDue, tblclients.id AS clientid, tblclients.companyname, tblclients.firstname, tblclients.lastname, tblclients.email, tblproducts.name, tblhosting.domain, tblhosting.amount, tblhosting.id AS hostingid,tblproducts.gid FROM tblhosting, tblclients, tblproducts WHERE tblhosting.userid = tblclients.id AND tblhosting.packageid = tblproducts.id AND tblhosting.billingcycle != 'Free Account' AND tblhosting.billingcycle != 'Monthly' AND tblhosting.nextinvoicedate='$today' 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))
{
//$to=trim(stripslashes($row['firstname'])." ".stripslashes($row['lastname'])." <".stripslashes($row['email']).">");
$to=$earlywarnings;
$subject="Upcoming service renewal";
$message="<html><head><title>".$subject."</title></head><body><p>Hi ".$row['firstname'].",</p>";
/*if($row['gid']==4)
{
	if($row['domain'])
		$extratext=" for ".$row['domain'];
	else
		$extratext="";
	$message.="
<p>Your ".$row['name']." contract$extratext is due to renew next month.</p>
<p>Your contract will automatically renew on ".$row['NextDue'].", please look out for your renewal pack which I will be sending out by post in the next few days.</p>
<p>Please don't hesitate to contact me on XXXXX XXXXXX if you have any questions.</p>";
}
else
{*/
	if($row['domain'])
		$extratext=" (".$row['domain'].")";
	else
		$extratext="";
	$message.="
<p>The service listed below is due to renew next month:</p>
<p>".$row['name']."$extratext - £".$row['amount']."+VAT</p>
<p>Your contract will automatically renew on ".$row['NextDue']." but please don't hesitate to contact me on XXXXX XXXXXX if you have any questions.</p>";
/*}*/
$message.="</body></html>";
$dbmessage="<p>Hi ".$row['firstname'].",</p>";
/*if($row['gid']==4)
{
	if($row['domain'])
		$extratext=" for ".$row['domain'];
	else
		$extratext="";
	$dbmessage.="
<p>Your ".$row['name']." contract$extratext is due to renew next month.</p>
<p>Your contract will automatically renew on ".$row['NextDue'].", please look out for your renewal pack which I will be sending out by post in the next few days.</p>
<p>Please don't hesitate to contact me on XXXXX XXXXXX if you have any questions.</p>";
}
else
{*/
	if($row['domain'])
		$extratext=" (".$row['domain'].")";
	else
		$extratext="";
	$dbmessage.="
<p>The service listed below is due to renew next month:</p>
<p>".$row['name']."$extratext - £".$row['amount']."+VAT</p>
<p>Your contract will automatically renew on ".$row['NextDue']." but please don't hesitate to contact me on XXXXX XXXXXX if you have any questions.</p>";
/*}*/
$headers="MIME-Version: 1.0\r\n";
$headers.="From: $earlywarnings\r\n";
$headers.="To: $to\r\n";
$headers.="Reply-To: $earlywarnings\r\n";
$headers.="BCC: $earlywarnings\r\n";
$headers.="X-Mailer: PHP/".phpversion()."\r\n";
$headers.="Content-type: text/html; charset=iso-8859-1\r\n";
mail($to,$subject,$message,$headers,'-f'.$earlywarnings);
//mysql_query("INSERT INTO tblemails (`userid`,`subject`,`message`,`date`,`to`) VALUES ('".$row['clientid']."','$subject','".addslashes($dbmessage)."','".date("Y-m-d H:i:s")."','$to')");
//mysql_query("INSERT INTO tblactivitylog (date,description,user) VALUES ('".date("Y-m-d H:i:s")."','Email sent to ".$row['firstname']." ".$row['lastname']." ($subject)','System')");
}
?>

Edited March 29, 2010 by swilders

Sign In

Upcoming product/service renewals

Recommended Posts

swilders

Link to comment

Share on other sites

Join the conversation

Recently Browsing 0 members

Browse

Latest Activity

Blog

Technical Support

Important Information