Coyne Technology Systems

WHMCS is a client management and billing support system primarily used as the back-end system for managing web hosting websites. Among other things, WHMCS includes the ability to send announcements to clients as messages in the hosting area, RSS feeds, or emails. Many articles have been written on how to include the announcements into custom developed webpages outside of the WHMCS billing system such as including them in a static home page for the site. In some cases you may wish to send an announcement about a potential change or upgrade that you would want to send to your clients, but may not be appropriate for your home page. This article will explain what is needed to selectively display announcements on the home page and create a WHMCS add-on module to allow control over which announcements are displayed on the external page.

The WHMCS system consists of code written in the PHP language with a MySQL database backend. The PHP code is obscured from viewing by a tool called Ioncube however the database is freely viewable and editable using standard MySQL tools such as phpMyAdmin. Normally the obscured code would cause a problem in creating modifications to databases and using the data, but the developers of WHMCS had the forethought to include the ability to create add-on modules within the WHMCS system. An add-on module is simply some PHP code that can be written and is called by the WHMCS system to display the information that the add-on provides.  Any information that needs to be displayed by the add-on module can be displayed within the WHMCS administration panels and can interact with the WHMCS database to retrieve and store information.

The add-on discussed in this article will add an additonal column to the WHMCS announcement table and create the code to interact with the column data.  When this add-on is installed, it is available in WHMCS under the ‘Utilities’ tab and selecting the ‘Addon Modules’ menu item.

Normally the PHP code to get the announcements from the database and show them on the home page looks something like this:

$result = mysql_query("SELECT * FROM tblannouncements WHERE published='on' ORDER BY id DESC LIMIT 3");
if (mysql_num_rows($result) == 0) {
echo '<p>There are no new announcements.</p>';
} else {
while($data = mysql_fetch_array ($result))
{
$id = $data['id'];
$date = $data['date'];
$announcement = $data['announcement'];
$title = $data['title'];
$date = formatdate($date);
echo'<div><span>'.$date.' - '.$title.' <a href="/hosting/announcements.php?id='.$id.'"><img src="/images/more.gif" width="30" height="15" alt="Display More News Detail" /></a></span></div>';
echo'<img align="middle" src="/images/news_sep.gif" alt="" width="237" height="2" />';
}
echo'<div><span>News &amp; announcements: <a href="/hosting/announcements.php">view more</a></span></div>';
}


This code will query the WHMCS database (connecting to the database not shown) and get all of the announcements from the ‘tblannoucements’ table.  It loops through each returned row and prints out the data from each row in HTML format to be displayed on the page.  Now let’s add our customization to the mix.
The first thing to do when creating the add-on is to BACKUP THE DATABASE!  This module will make modifications to the structure of the database and table data. Any incorrect code has the ability to wipe out the WHMCS tables or data, so you have been warned.

This WHMCS add-on has 4 overall stages:

1. Pre-install
2. Install
3. Operational
4. Uninstall

The WHMCS add-on system will check to see if the add-on is installed. If it is not, we are in the Pre-install stage. Our add-on will install an addtional column to the WHMCS MySQL announcement table, so the check to see if is installed will consists of querying the database for our column in the table:
if(!mysql_num_rows(mysql_query("SHOW COLUMNS FROM tblannouncements LIKE 'show_frontpage'")))

This simply asks the database to see if there are any columns named show_frontpage and if there is nothing returned, we complete the Pre-install stage. In the Pre-install stage, we show some HTML code to tell the user the add-on is not installed and display a button for them to click on to install the add-on:

if(!$_GET['install']){
  echo '<h1>Not Yet Installed</h1>
  <p>This add-on defines an additional field for the announcements table for display on external pages such as the home page.</p>
  <p>To install it, click on the button below.</p>
  <p><input type="button" value="Install Homepage Annoucements" onclick="window.location=\''.$modulelink.'&install=true\'"></p>';
}

This section of code is inclosed in a conditional block that checks the parameters passed to the page to determine if the user pressed the install button or not. If the install parameter is not set, the user has not requested installation of the add-on yet. The button has an onclick handler for the install button that when clicked, sends us back to this page with the install $_GET parameter set to true which will execute the else conditional block.  The $modulelink PHP variable is a pre-defined WHMCS name for our add-on.   Once this code is executed, when we return to this page, we will be in the Install stage and perform the the actual installation of the add-on which is defined by these lines:

} else {
  // request install, create the table and send us back to this file
  $query = "ALTER TABLE tblannouncements ADD `show_frontpage` BOOL NOT NULL DEFAULT 0";
  $result=mysql_query($query);
  header("Location: $modulelink");
  exit;
}

This code creates a MySQL query string and sends it to the MySQL database. The query creates a column called show_frontpage in the existing WHMCS table tblannouncements.  This field is a BOOL field that cannot be null and has a default of zero when new items are added to the table.  Once the query is executed, the header function sends us back to this file with no install parameter set. When we test if the column exists after running this code, it will be true, and we will continue on to the Operational phase.

The Operational phase is where we perform any work we want to do with the add-on. There are three conditional blocks within the Operational phase. The second which we will talk about first is where there are no POST variables passed to the add-on. In this case, we simply display a table with all of the announcements listed and a check box next to them that is filled in with the yes-no value on whether to display the announcement on the front page. We get this information by getting all the information out of the announcements table and loop through each row returned printing the data out to the client.  This is done whether we complete the first conditional block or not.

The first conditional block that we skipped over temporarily, is when the user makes changes to the check boxes to change the display of the annoucement. In this case there will be a $_POST variable called announcements_id with values that will be fill in to the show_frontpage column that is passed to the script. When this variable has data, the first thing we do is to go to the tblannouncments table and set each show_frontpage column value to zero.  This is because the array that is passed back to us in the $_POST variable does not include unset values, only set values, so we cannot determine which ones were set before showing the page, so we just quickly clear them all.  Next we loop through each returned checkbox array item and set the show_frontpage values to one where the user had selected values in the check boxes.  This is the code that performs these actions:

  // the table already exists in the database, get the values from the $_POST var if any
  if ($_POST["announcements_id"]) {
   $upquery = "UPDATE tblannouncements SET show_frontpage=0";
   $result = mysql_query($upquery);
   $announcements_id = sanitize($_POST["announcements_id"]);
   // create a query to update values in the column based on the passed parameters
   while (list($key,$val) = @each ($announcements_id)) {
    $newquery = "UPDATE tblannouncements SET show_frontpage=1 WHERE id=".$val;
    $result=mysql_query($newquery);
   }
  }

Now lets go back to our original code that displays the announcments in the home page and make the changes.  The only change necessary is how we select the items out of the database.  Using this new show_frontpage column we simply change the filter to only get the the results that have that column set:

$result = mysql_query("SELECT * FROM tblannouncements WHERE show_frontpage=1 ORDER BY id DESC LIMIT 3");

Now we can selectively display announcements in pages that are outside the WHMCS billing system.

The full source code for this article can be found in a zip file in our shop by following this link.

To install from the zip file, create a directory in the <WHMCS>/modules/admin/ directory called ‘homepage_announcements’ without the quotes and place the PHP file in this directory.  Log into your WHMCS and select ‘Addon Modules’ from the ‘Utilities’ Menu and you should see a item titled ‘Homepage Announcements’.  Clicking on that will allow the module to be installed.

Note that this script was built and tested to work with WHMCS version 4.1.1.  Any upgrades or changes to the WHMCS version may overwrite the changes here.  Ensure you backup your database before you install upgrades to WHMCS.