Display user's tickets using a hook

[Dimitra]

Hello,

I want to display a user's recent support tickets in some client area pages. I have created a hook following the instructions here and changes the variables. The hook works fine and it displays the results I need. However, it displays the recent tickets of all users.

 

here is my code

<?php

function hook_displaysupporttickets($vars){

$output = "";
$query = "SELECT * FROM tbltickets ORDER BY date DESC LIMIT 0,6"; 
$result = mysql_query($query);
while ($data = mysql_fetch_array($result)) {
   $tid = $data["tid"];
   $date = $data["date"];
   $replyid = $data["replyid"];
   $title = $data["title"];
   $department = $data["department"];
   $date = fromMySQLDate($date);
   $output .= '<p class="mytitleannoun"><i class="fa fa-file-text-o" aria-hidden="true" style="margin-right:15px;"></i><a style="margin-right:15px;" href="/viewticket.php?tid='.$tid.'">'.$title.'</a><span>'.$department.'</span><span>'.$tid.'</span><font color="#b3b3b3">'.$date.'</font></p>';
}
return array("displaysupporttickets" => $output);
}
add_hook("ClientAreaPage", 1, "hook_displaysupporttickets");

 

I need to change the code so that it displays only the tickets of the logged in user (each user will see only his own tickets).

Any ideas?

 

Thank you.

[brian!]

you should just need to add the code to access the clients ID and then add that to your query...

 

<?php

function hook_displaysupporttickets($vars){

   $client = Menu::context('client');

   $output = "";
   $query = "SELECT * FROM tbltickets WHERE userid = $client->id ORDER BY date DESC LIMIT 0,6"; 
   $result = mysql_query($query);
   while ($data = mysql_fetch_array($result)) {
       $tid = $data["tid"];
       $date = $data["date"];
       $replyid = $data["replyid"];
       $title = $data["title"];
       $department = $data["department"];
       $date = fromMySQLDate($date);
       $output .= '<p class="mytitleannoun"><i class="fa fa-file-text-o" aria-hidden="true" style="margin-right:15px;"></i><a style="margin-right:15px;" href="/viewticket.php?tid='.$tid.'">'.$title.'</a><span>'.$department.'</span><span>'.$tid.'</span><font color="#b3b3b3">'.$date.'</font></p>';
   }
   return array("displaysupporttickets" => $output);
}

add_hook("ClientAreaPage", 1, "hook_displaysupporttickets"); 

though bear in mind that mysql_query will be removed from WHMCS at some point - so it will still work for now, but ultimately you'll need to rewrite this using Capsule... untested, but probably along the lines of...

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function hook_displaysupporttickets($vars){

   $client = Menu::context('client');
   $output = "";

   $tickets = Capsule::table('tbltickets')
                   ->where('userid', $client->id)
                   ->orderBy('date', 'desc')
                   ->take(6)
                   ->get();

   foreach ($tickets as $ticket) {    
       $output .= '<p class="mytitleannoun"><i class="fa fa-file-text-o" aria-hidden="true" style="margin-right:15px;"></i><a style="margin-right:15px;" href="/viewticket.php?tid='.$ticket->tid.'">'.$ticket->title.'</a><span>'.$ticket->department.'</span><span>'.$ticket->tid.'</span><font color="#b3b3b3">'.fromMySQLDate($ticket->date).'</font></p>';
   }

   return array("displaysupporttickets" => $output);
}

add_hook("ClientAreaPage", 1, "hook_displaysupporttickets");

your path to viewticket may not be correct, but I guess will likely depend on which pages are going to use this code...

[Dimitra]

Both solutions work great. I kept the second one with Capsule.

 

I have another question. Is there a place where i can find the variables that are used? eg the ticketid, userid etc. I am looking for a table similar to this one

 

Thank you very much.

[brian!]

Both solutions work great. I kept the second one with Capsule.

a wise choice.

 

I have another question. Is there a place where i can find the variables that are used? eg the ticketid, userid etc. I am looking for a table similar to this one

when you're querying a database table, these variables are the column headings of the table...

 

[Dimitra]

That was really helpful. Thank you.

[steven99]

Using the API to get the tickets would be another method. http://docs.whmcs.com/API:Get_Tickets Though you may need to sort through them to get recent ones.

[hooter]

There is no reason to go through all that rigarmole as quoted above https://forum.whmcs.com/showthread.php?121074-Display-user-s-tickets-using-a-hook&p=487678#post487678 - @steven99 has the correct answer