Coyne Technology Systems

WHMCS is a client management, billing, and support solution and is widely used as the back end system to manage Web Hosting sites. WHMCS includes a built in knowledgebase system where the administrator can create articles to support clients in performing common topics. Since the knowledgebase is the first area of support for the users, It would be helpful to have a report on the knowledgebase articles to see the most used articles. This article walks through creating a custom report to show the most used articles in the knowledgebase.

WHMCS has a quick and easy way to generate reports as add-ons to the core WHMCS functionality. They are implemented by creating a PHP script file and setting some predefined variables within the script. Uploading the file to the correct directory installs the file to be used in the WHMCS admin area. The hardest part is getting the correct data out of the WHMCS database for use.

The requirement for this report was to display the top 20 articles from the knowledgebase along with their category and the additional information that is stored for articles (number of votes cast, number of people that found it useful, and whether it is a private article only visible to logged in clients).
Start by creating a empty PHP file and giving it a useful file name. For this report we named this file knowledgebase_usage.php. We then copied the contents of the sample given under the documentation for the WHMCS reports into the empty file and fill in the obvious information for the description, header text, and footer text.

Next, we needed to determine what fields we would like to display for each article and the titles for them. Looking at the WHMCS database structure with a database tool such as phpMyAdmin, the categories are stored in a separate table from the articles and are linked with a link table, so we will have to join these tables to get all of the information about the articles.

The articles table stores the number of views, the total number of votes on whether the user found it useful, the number of those votes that found it useful , and whether the article is for private clients only. We will display each of these values in our report.

Now that we have this information we can create the table headings. Back in the knowledgebase_usage.php, we create the array for the headings:

$reportdata["tableheadings"] = array("Category","Title","Views","Stats","Is Private");

We also have the information to create our SQL query statement. We have to select the title,views, useful, votes, and private columns from the tblknowledgebase table and the name column from the tblknowledgebasecats table and join them through the tblknowedgebaselinks table. Also we want to sort them by the most views and only show the top 20 articles. Our SQL statement looks like this:

SELECT `title`, `views`, `useful`, `votes`, `private`, `name` FROM `tblknowledgebase` LEFT JOIN `tblknowledgebaselinks` on `tblknowledgebase`.`id`=`tblknowledgebaselinks`.`articleid` LEFT JOIN `tblknowledgebasecats` on `tblknowledgebaselinks`.`categoryid`=`tblknowledgebasecats`.`id` ORDER BY `views` DESC, name LIMIT 0,20

We execute the query and for each row that is returned, extract the data, perform some formatting and put the values we want displayed into the existing report data variable:

$result=mysql_query($query);
while($data = mysql_fetch_array($result)) {
$category = $data["name"];
$title = $data["title"];
$views = $data["views"];
$useful = $data["useful"];
$votes = $data["votes"];
$stats = $useful." (".$votes.")";
$private = $data["private"];
$reportdata["tablevalues"][] = array($category,$title,$views,$stats,$private);
}

Upload this file to the WHMCS/modules/reports directory and the report will appear when logged in to the admin area and clicking on the reports tab. The name of the report on the Reports page will be the file name with underscores turned into spaces and each word capitalized so for this report it will take knowledgebase_usage.php and title it ‘Knowledgebase Usage’ in the Report list.

The source code for this report is free and can be found as a downloadable zip file product in our shop.