Hi there,
Because this isn't the first time such integration has been requested, I have taken a few minutes to create a script that will accomplish the basic integration of ticket counts in the client area page. If I see the demand for a more in depth integration and appreciation for the work below, I'll create scripts for native listing of open tickets on the client area page, and native ticket counts integration in the admin area.
For now...
CLIENT AREA:
- Create a PHP file in the /includes/hooks folder of your WHMCS installation, paste the following therein.
- Modify the $kayako_config_location variable to the location of your Kayako config.php file - this is very important.
<?php
/*
Script: Kayako support ticket counts (open/total) in WHMCS client area
Author: Victor Lugo (www.victorlugo.com)
Last edited: April 4, 2010
Version: 1.0
*/
add_hook("ClientAreaPage",1,"fixTicketCounts","");
function fixTicketCounts()
{
/************************EDIT*****************************/
$kayako_database_prefix = "sw"; //MODIFY ONLY IF DATABASE PREFIX DIFFERS FROM DEFAULT VALUE
$kayako_config_location = "/path/to/kayako/config/config.php"; //MODIFY TO KAYAKO CONFIG.PHP LOCATION
/************************EDIT*****************************/
global $smarty;
//GET THE CLIENT'S WHMCS USER ID FROM SMARTY...
$whmcs_user_id = $smarty->_tpl_vars['clientsdetails']['userid'];
//GET THE KAYAKO CONFIG.PHP FILE...
require($kayako_config_location);
//GIVE MATCHING VARIABLES TO THE DATABASE PROPERTIES... (SO THAT I STAY SANE)...
$kayako_database_hostname = $_DB["hostname"];$kayako_database_username = $_DB["username"];$kayako_database_password = $_DB["password"];$kayako_database_name = $_DB["name"];$kayako_database_users_table = $kayako_database_prefix . "users";$kayako_database_tickets_table = $kayako_database_prefix . "tickets";
//SET THE MYSQL CONNECTION TO THE KAYAKO DATABASE...
$kayako_database = mysql_connect($kayako_database_hostname, $kayako_database_username, $kayako_database_password);
//GET THE CLIENT'S KAYAKO USER ID...
$kayako_user_id = mysql_query("SELECT userid FROM $kayako_database_name.$kayako_database_users_table WHERE loginapi_userid = '$whmcs_user_id'", $kayako_database);$kayako_user_id = mysql_fetch_row($kayako_user_id);$kayako_user_id = $kayako_user_id[0];
//GET THE ROWS THAT MATCH THE CLIENT'S KAYAKO USER ID AND THAT ARE NOT CLOSED...
$result = mysql_query("SELECT * FROM $kayako_database_name.$kayako_database_tickets_table WHERE userid = '$kayako_user_id' AND ticketstatusid != '3'", $kayako_database);
//COUNT THOSE ROWS, GIVE THE RESULT A VARIABLE...
$number_open_tickets = mysql_num_rows($result);
//TRICK SMARTY, INJECT THE NEW NUMBER INTO THE VARIABLE BEFORE THE PAGE LOADS...
$smarty->_tpl_vars['clientsstats']['numactivetickets'] = $number_open_tickets;
//MUST NOT FORGET TO ALSO SET THE NUMBER OF TOTAL TICKETS (OPEN AND CLOSED)... GET THE ROWS THAT MATCH THAT CLIENT'S KAYAKO USER ID...
$result = mysql_query("SELECT * FROM $kayako_database_name.$kayako_database_tickets_table WHERE userid = '$kayako_user_id'", $kayako_database);
//COUNT THOSE ROWS, GIVE THE RESULT A VARIABLE...
$number_tickets = mysql_num_rows($result);
//TRICK SMARTY, INJECT THE NEW NUMBER OF TOTAL TICKETS INTO THE VARIABLE BEFORE THE PAGE LOADS...
$smarty->_tpl_vars['clientsstats']['numtickets'] = $number_tickets;
}
?>