getweb

Along the same lines - when an account is Deleted is it completely gone? Specifically, will that change my historical reports?? I did try to pull up a known invoice number of a deleted account and it could not find it.

Oops... if ($kayakoDB) mysql_select_db($kayakoDB,$kDB); should be if ($kDB) mysql_select_db($kayakoDB,$kDB); ...a mere technicality, of course. That's what I get for trying to clean up my variable names. I'm really digging this integration though. I'm not even two weeks into WHMCS, married to Kayako for 3rd-party reasons, and things have never been so smooth. I would not have wanted to sit through the "beta" process for all this, but I seriously downloaded Kayako's latest CVS yesterday and was successfully testing integration not even an hour later. There's going to be lots of tweaks I'm sure, per other discussions, but with a fresh install of both applications (and tickets going back three years) the core functionality is working well, for me at least.

Something ugly like this? This is beta code, and I've never used SMARTY before, but if it helps somebody, great. (EDIT: By the way, I based the userid/API lookups on what a query log showed Kayako actually doing with WHMCS... it should be close to correct. Also, great to see the integration between them is NOT email address dependent!) While editing "clientareahome.tpl" replace the Support Tickets table with: {php} // It is probably not wise to put this here, but kind of a pain // to put anywhere else and call from template // Consider making a special read-only SQL user for this purpose? $kayakoHost = "localhost"; $kayakoUser = "dbuser"; $kayakoPass = "dbpass"; $kayakoDB = "dbname"; $kDB = mysql_connect($kayakoHost,$kayakoUser,$kayakoPass); if ($kayakoDB) mysql_select_db($kayakoDB,$kDB); else print '<p align="center" style="color: #CC0000">NOTE: Could not access ticket database.</p>'; // First, get the WHMCS user id $arrdetails = $this->get_template_vars('clientsdetails'); $whmcsID = $arrdetails['userid']; $kayakoID = ""; // Look up Kayako's userid searching the API userid field for the WHMCS id // Correct - this is not email based! if ($kDB) { $result = mysql_query("SELECT userid FROM swusers WHERE loginapi_moduleid = '300' AND loginapi_userid = '$whmcsID'",$kDB); $row = mysql_fetch_row($result); $kayakoID = $row[0]; } if ($kayakoID > '') { // Now, find the email address for this user (see why shortly) $result = mysql_query("SELECT email FROM swuseremails WHERE userid = '$kayakoID'",$kDB); $row = mysql_fetch_row($result); $kayakoEmail = $row[0]; // Finally, get the tickets based on email NOT by userid // This is because tickets can have multiple emails, and therefore users... (right?) // This may need revision or to hide closed tickets - I decided to show them $result = mysql_query("SELECT tickets.ticketid,tickets.lastactivity,tickets.ticketmaskid,tickets.subject," . "depts.title AS dept,status.title AS status,prio.title AS priority FROM swtickets tickets " . "LEFT JOIN swdepartments depts ON depts.departmentid = tickets.departmentid " . "LEFT JOIN swticketstatus status ON status.ticketstatusid = tickets.ticketstatusid " . "LEFT JOIN swticketpriorities prio ON prio.priorityid = tickets.priorityid " . "WHERE email IN ('$kayakoEmail') ORDER BY lastactivity DESC LIMIT 5", $kDB); if ($result) while ($row = mysql_fetch_array($result)) { $row["date"] = strftime("%d %b %Y %I:%M %p",$row["lastactivity"]); $this->append('tickets', $row); } } {/php} <table align="center" style="width:90%" class="clientareatable" cellspacing="1"> <tr class="clientareatableheading"><td>{$LANG.supportticketsdate}</td><td>{$LANG.supportticketssubject}</td> <td>{$LANG.supportticketsstatus}</td><td>{$LANG.supportticketsticketurgency}</td><td>Department</td></tr> {foreach key=cid item=ticket from=$tickets} <tr class="clientareatableactive"><td>{$ticket.date}</td> <td><div align="left"><img src="images/support/article.gif" hspace="5" align="middle" alt="" /><a href="viewticket.php?_m=tickets&_a=viewticket&ticketid={$ticket.ticketid}">#{$ticket.ticketmaskid} - {$ticket.subject}</a></div></td> <td width="60">{$ticket.status}</td> <td width="60">{$ticket.priority}</td> <td width="120">{$ticket.dept}</td></tr> {foreachelse} <tr class="clientareatableactive"><td colspan="5">{$LANG.supportticketsnoopentickets}</td></tr> {/foreach} </table>

Excellent idea, just what I needed. I actually took it further to get the ticket subject, author, and first bit of the message content into the txt message. My messages are limited to 160 characters so this format gives me most of what I need to know at a glance (so I can differentiate between "server down" tickets and "how do I change color?" ) {php} $subject = $_REQUEST["subject"]; $message = $_REQUEST["message"]; $arrdetail = $this->get_template_vars('clientsdetails'); $fullname = $arrdetail['firstname'] . " " . $arrdetail['lastname']; mail("pager@domain.tld", "$subject", "From $fullname: $message"); {/php}