Better support ticket overview on left

[twikamltd]

Been looking to shut down Kayako, but hated the ticket overview page. This will simply give you a more Kayako style department listing on the left hand sidebar so you can see tickets by department and their statuses quickly. First, create a hook:

 

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function get_support_depts_tickets_hook($vars) 
{
   $ticketdeptstats = Capsule::table('tbltickets')
                       ->select('did', 'status', Capsule::raw('count(*) as numtickets'))
                       ->groupBy('did', 'status')
                       ->get();

   $encodedata = json_encode($ticketdeptstats);
   $decodedata = json_decode($encodedata, true);

   return array("ticketdepartmentstats" => $decodedata);
}
add_hook("AdminAreaPage", 1, "get_support_depts_tickets_hook");
?>

 

Modify /admin/templates/blend/sidebar.tpl and add this code around line 189:

 

<span class="header"><img src="images/icons/tickets.png" alt="Filter Tickets" width="16" height="16" class="absmiddle" />{$_ADMINLANG.support.department}s</span>
<ul class="menu">
{foreach from=$ticketdepts item=dept}
   <li>{$dept.name} {$_ADMINLANG.support.department}</li>
   {* Now display direct links to the department tickets of each status *}
     <ul class="menu" style="margin-left: 10px">
         {foreach from=$ticketstatuses item=status}
             <li> - <a href="supporttickets.php?deptid={$dept.id}&view={$status.title}">{$status.title}</a>
               {foreach from=$ticketdepartmentstats item=ticketdepartmentstat}

                   {if $dept.id eq $ticketdepartmentstat.did && $ticketdepartmentstat.status eq $status.title}


                       ({$ticketdepartmentstat.numtickets})

                   {/if}

               {/foreach}
             </li>
         {/foreach}
     </ul>
{/foreach}
</ul>

Create a hook:

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function get_support_depts_tickets_hook($vars) 
{
   $ticketdeptstats = Capsule::table('tbltickets')
                       ->select('did', 'status', Capsule::raw('count(*) as numtickets'))
                       ->groupBy('did', 'status')
                       ->get();

   $encodedata = json_encode($ticketdeptstats);
   $decodedata = json_decode($encodedata, true);

   return array("ticketdepartmentstats" => $decodedata);
}
add_hook("AdminAreaPage", 1, "get_support_depts_tickets_hook");
?>

 

Modify /admin/templates/blend/sidebar.tpl and add this code around line 189:

 

<span class="header"><img src="images/icons/tickets.png" alt="Filter Tickets" width="16" height="16" class="absmiddle" />{$_ADMINLANG.support.department}s</span>
<ul class="menu">
{foreach from=$ticketdepts item=dept}
   <li>{$dept.name} {$_ADMINLANG.support.department}</li>
   {* Now display direct links to the department tickets of each status *}
     <ul class="menu" style="margin-left: 10px">
         {foreach from=$ticketstatuses item=status}
             <li> - <a href="supporttickets.php?deptid={$dept.id}&view={$status.title}">{$status.title}</a>
               {foreach from=$ticketdepartmentstats item=ticketdepartmentstat}

                   {if $dept.id eq $ticketdepartmentstat.did && $ticketdepartmentstat.status eq $status.title}


                       ({$ticketdepartmentstat.numtickets})

                   {/if}

               {/foreach}
             </li>
         {/foreach}
     </ul>
{/foreach}
</ul>

 

Enjoy. Hopefully WHMCS will implement something similar soon by default.

 

Credit to brian! for helping finish this up

Edited October 4, 2016 by WHMCS ChrisD
Edited at user request

[brian!]

tweaking this to use capsule, the hook should be...

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function get_support_depts_tickets_hook($vars) 
{
   $ticketdeptstats = Capsule::table('tbltickets')
                       ->select('did', 'status', Capsule::raw('count(*) as numtickets'))
                       ->groupBy('did', 'status')
                       ->get();

   $encodedata = json_encode($ticketdeptstats);
   $decodedata = json_decode($encodedata, true);

   return array("ticketdepartmentstats" => $decodedata);
}
add_hook("AdminAreaPage", 1, "get_support_depts_tickets_hook");
?>

and also there is an error in the foreach loop...

 

                {foreach from=$ticketdepartmentstats item=ticketdepartmentstat}
                   {if $dept.id eq $ticketdepartmentstat.did && $ticketdepartmentstat.status eq $status.title}
                       ({$ticketdepartmentstat.numtickets})
                   {/if}
               {/foreach}

[twikamltd]

Thank you for the capsule update, I can't spot the error in the loop though - the code I posted does work?

[brian!]

the error was in "item=ticketdepartmentstat" - you added a $ to it that stopped the loop output from working.

[twikamltd]

Ah, thank you! Didn't stop it from working though

[twikamltd]

Final version, thanks to brian's help is:

 

Create a hook:

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function get_support_depts_tickets_hook($vars) 
{
   $ticketdeptstats = Capsule::table('tbltickets')
                       ->select('did', 'status', Capsule::raw('count(*) as numtickets'))
                       ->groupBy('did', 'status')
                       ->get();

   $encodedata = json_encode($ticketdeptstats);
   $decodedata = json_decode($encodedata, true);

   return array("ticketdepartmentstats" => $decodedata);
}
add_hook("AdminAreaPage", 1, "get_support_depts_tickets_hook");
?>

 

Modify /admin/templates/blend/sidebar.tpl and add this code around line 189:

 

<span class="header"><img src="images/icons/tickets.png" alt="Filter Tickets" width="16" height="16" class="absmiddle" />{$_ADMINLANG.support.department}s</span>
<ul class="menu">
{foreach from=$ticketdepts item=dept}
   <li>{$dept.name} {$_ADMINLANG.support.department}</li>
   {* Now display direct links to the department tickets of each status *}
     <ul class="menu" style="margin-left: 10px">
         {foreach from=$ticketstatuses item=status}
             <li> - <a href="supporttickets.php?deptid={$dept.id}&view={$status.title}">{$status.title}</a>
               {foreach from=$ticketdepartmentstats item=ticketdepartmentstat}

                   {if $dept.id eq $ticketdepartmentstat.did && $ticketdepartmentstat.status eq $status.title}


                       ({$ticketdepartmentstat.numtickets})

                   {/if}

               {/foreach}
             </li>
         {/foreach}
     </ul>
{/foreach}
</ul>

[Atomic]

I've been wanting this for years! Thank you!

 

One question, is it possible to remove "Department" from the title? So it says "Linux Support" instead of "Linux Support Department" just because I want it on one line and I'm pedantic.

[twikamltd]

Just take out {$_ADMINLANG.support.department} near the top of the template mod

[Atomic]

Outstanding. Also commented out the Support links above it. Perfection.

[CavalloComm]

Can this be worked out on the V4 template instead of "Blend"?

[Atomic]

We switched our WHMCS installation to a new domain on a new server and now we are seeing some inconsistencies with the amount of tickets being shown as "Open" and tickets that are actually open. So in Linux Support it says (7) but we have 3 Open tickets and in Windows Support it also says (7) but we have 2 open tickets. Also both departments show (1) for In Progress but it's empty. Any ideas on how to resolve?

[Atomic]

I love it when I make a post and then automatically get an idea and fix the problem. Looked in the database and found a bunch of ghost tickets.

[twikamltd]

Right, after upgradeing to 8.0.4, was thoroughly disappointed to see the pending orders, tickets and overdue invoices disappear, so here's the solution:

Hook:

<?php 

use Illuminate\Database\Capsule\Manager as Capsule; 

function get_support_depts_tickets_hook($vars)  
{ 

    // Query for ticket counts
    $ticketdeptstats = Capsule::table('tbltickets') 
                        ->select('did', 'status', Capsule::raw('count(*) as numtickets')) 
                        ->groupBy('did', 'status') 
                        ->get(); 

    $encodedata = json_encode($ticketdeptstats); 
    $decodedata = json_decode($encodedata, true);
    
    // Query for number of pending orders
    $pendingordernum = Capsule::table('tblorders') 
                        ->where('status','Pending')
                        ->count();
    
    // Query for number of pending tickets
    $pendingticketnum = Capsule::table('tbltickets') 
                        ->where('status','Customer-Reply')
                        ->count();
                        
    // Query for number of overdue tickets
    $overdueinvnum = Capsule::table('tblinvoices') 
                        ->where('status','Unpaid')
                        ->whereDate('duedate','<',date('Y-m-d'))
                        ->count();
     
    return array("ticketdepartmentstats" => $decodedata, "pendingordernum" => $pendingordernum, "pendingticketnum" => $pendingticketnum, 'overdueinvnum' => $overdueinvnum); 
} 
add_hook("AdminAreaPage", 1, "get_support_depts_tickets_hook"); 

At the top of sidebar.tpl (literally line 1):

<div class="sidebar-header">
 Overview
</div>

<ul class="menu">
  <li><a href="orders.php?status=Pending">{$pendingordernum} {$_ADMINLANG.stats.pendingorders}</a></li>
  <li><a href="supporttickets.php">{$pendingticketnum} {$_ADMINLANG.stats.ticketsawaitingreply}</a></li>
  <li><a href="invoices.php?status=Overdue">{$overdueinvnum} {$_ADMINLANG.stats.overdueinvoices}</a></li> 
</ul>

Then put the slightly updated Kayako style ticket view code starting at line 137:

<div class="sidebar-header"><img src="images/icons/tickets.png" alt="Filter Tickets" width="16" height="16" class="absmiddle" /> {$_ADMINLANG.support.department}s</div>
<ul class="menu">
{foreach from=$ticketdepts item=dept}
   <li style="margin-left: 10px;">{$dept.name}</li>
   {* Now display direct links to the department tickets of each status *}
     <ul class="menu" style="margin-left: 13px">
         {foreach from=$ticketstatuses item=status}
             <li> -<a style="display: inline;" href="supporttickets.php?deptid={$dept.id}&view={$status.title}">{$status.title}</a>
               {foreach from=$ticketdepartmentstats item=ticketdepartmentstat}

                   {if $dept.id eq $ticketdepartmentstat.did && $ticketdepartmentstat.status eq $status.title}


                       ({$ticketdepartmentstat.numtickets})

                   {/if}

               {/foreach}
             </li>
         {/foreach}
     </ul>
{/foreach}
</ul>

 

(goes inbetween the elseif if)

 

Good luck, WHMCS, can we make this standard?!

[twikamltd]

Forgot to count Open tickets as well as Customer-Reply:

<?php 

use Illuminate\Database\Capsule\Manager as Capsule; 

function get_support_depts_tickets_hook($vars)  
{ 

    // Query for ticket counts
    $ticketdeptstats = Capsule::table('tbltickets') 
                        ->select('did', 'status', Capsule::raw('count(*) as numtickets')) 
                        ->groupBy('did', 'status') 
                        ->get(); 

    $encodedata = json_encode($ticketdeptstats); 
    $decodedata = json_decode($encodedata, true);
    
    // Query for number of pending orders
    $pendingordernum = Capsule::table('tblorders') 
                        ->where('status','Pending')
                        ->count();
    
    // Query for number of pending tickets
    $pendingticketnum = Capsule::table('tbltickets') 
                        ->where('status','Customer-Reply')
                        ->orWhere('status','Open')
                        ->count();
                        
    // Query for number of overdue tickets
    $overdueinvnum = Capsule::table('tblinvoices') 
                        ->where('status','Unpaid')
                        ->whereDate('duedate','<',date('Y-m-d'))
                        ->count();
     
    return array("ticketdepartmentstats" => $decodedata, "pendingordernum" => $pendingordernum, "pendingticketnum" => $pendingticketnum, 'overdueinvnum' => $overdueinvnum); 
} 
add_hook("AdminAreaPage", 1, "get_support_depts_tickets_hook"); 

 

[brian!]

35 minutes ago, twikamltd said:

Right, after upgrading to 8.0.4, was thoroughly disappointed to see the pending orders, tickets and overdue invoices disappear, so here's the solution:

or you could use... 😎

technically, those 3 queries might give wrong (or at least inconsistent with how WHMCS calculates them elsewhere) results.

that Kayako code could be hooked as well if necessary I think.

[twikamltd]

2 hours ago, brian! said:

or you could use... 😎

technically, those 3 queries might give wrong (or at least inconsistent with how WHMCS calculates them elsewhere) results.

that Kayako code could be hooked as well if necessary I think.

Damn it! Didn't see that!

Any pointers for how to hook the ticket dept layout instead of altering the template file please? Will save updating the file on each upgrade. I can't see any obvious hook that'll do it.

[brian!]

assuming you're using Blend, can you PM me your modified sidebar.tpl for me to take a look at.