Home > osTicket > Adding Ticket Aging, Ticket Summary, and Ticket for Staff Report Feature into osTicket v1.6 RC5

Adding Ticket Aging, Ticket Summary, and Ticket for Staff Report Feature into osTicket v1.6 RC5

I have successfully created a new report feature into osTicket v1.6 RC5 that contains three sub report. They are:
1) Ticket Aging (<= 3 Days, > 3 and <= 7 Days, > 7 and <= 14 Days, and > 14 Days),
2) Ticket Summary (Staff in Charge:, Total Number of Active Tickets:, Total Number of Closed Tickets:, Total Number of Unassigned Tickets: ), and
3) Ticket for Staff (Staff in Charge with the tickets that assigned to them).

You will see that there are three sub tabs under Dashboard tab in Admin Panel of your osTicket.

  1. Open your /scp/admin.php file, and find this code:

    523
    524
    525
    526
    527
    
        break;
        default:
            $errors['err']='Uknown command!';
        endswitch;
    endif;

    then replace with this following code:

    523
    524
    525
    526
    527
    528
    529
    530
    531
    532
    533
    
        break;
        case 'ticketaging':
     
        break;
        case 'ticketsummary':
     
        break;
        default:
            $errors['err']='Uknown command!';
        endswitch;
    endif;

    Find again this code:

    556
    557
    558
    559
    560
    561
    
        case 'dashboard':
        case 'syslog':
            $nav->setTabActive('dashboard');
            $nav->addSubMenu(array('desc'=>'System Logs','href'=>'admin.php?t=syslog','iconclass'=>'syslogs'));
            $page='syslogs.inc.php';
            break;

    then replace with this following code:

    556
    557
    558
    559
    560
    561
    562
    563
    564
    565
    566
    567
    568
    569
    570
    571
    572
    573
    574
    575
    576
    577
    578
    579
    580
    
        case 'dashboard':
        case 'syslog':
        case 'ticketaging':
        case 'ticketsummary':
        case 'ticketstaff':
            $nav->setTabActive('dashboard');
            $nav->addSubMenu(array('desc'=>'System Logs','href'=>'admin.php?t=syslog','iconclass'=>'syslogs'));
            $nav->addSubMenu(array('desc'=>'Ticket Aging','href'=>'admin.php?t=ticketaging','iconclass'=>'syslogs'));
            $nav->addSubMenu(array('desc'=>'Ticket Summary','href'=>'admin.php?t=ticketsummary','iconclass'=>'syslogs'));
            $nav->addSubMenu(array('desc'=>'Ticket for Staff','href'=>'admin.php?t=ticketstaff','iconclass'=>'syslogs'));
            switch($thistab):
            case 'dashboard':
            case 'syslog':
              $page='syslogs.inc.php';
              break;
            case 'ticketaging':
              $page='ticketaging.inc.php';
              break;
            case 'ticketsummary':
              $page='ticketsummary.inc.php';
              break;
            case 'ticketstaff':
              $page='ticketstaff.inc.php';
            endswitch;
            break;
  2. Create a new file named ticketaging.inc.php file, and copy paste this following code to that file, and put this file into your /include/staff/ sub-directory of your osTicket:

    <?php
    if(!defined('OSTSCPINC') || !@$thisuser->isStaff()) die('Access Denied');
     
    if($_REQUEST['staffid']) {
      $staffid = $_REQUEST['staffid'];
    }
     
    echo '<div class="msg">Ticket Aging</div>';
     
    $numtotalstaff=0;
    $sqltotalstaff='SELECT DISTINCT(t.staff_id), s.username, s.firstname, s.lastname '.
                   'FROM '.TICKET_TABLE.' t, '.STAFF_TABLE.' s '.
                   'WHERE t.staff_id <> "0" AND t.staff_id=s.staff_id';
    if(($restotalstaff=db_query($sqltotalstaff)) && ($numtotalstaff=db_num_rows($restotalstaff))) {
     $strtickettotalstaff = '';
     $itotalstaff = 1;
    ?>
    <form action="admin.php?t=ticketaging" method="post">
     <input type=hidden name='a' value='staff'>
     Staff Name:&nbsp;<select name="staffid" >
      <option value="" selected >Select One</option>
    <?
     while ($rowtotalstaff = db_fetch_array($restotalstaff)) {
       $selected = ($_REQUEST['staffid']==$rowtotalstaff['staff_id'])?'selected':''; ?>
       <option value="<?=$rowtotalstaff['staff_id']?>"<?=$selected?>><? echo $rowtotalstaff['username'].' ('.$rowtotalstaff['firstname'].' '.$rowtotalstaff['lastname'].')'; ?></option>
    <?
     }
    ?>
     </select>
     <input class="button" type="submit" value="View">
    </form>
    <?
    }
     
    echo '<div style="margin-bottom:20px; padding-top:0px;">';
    echo '<table width="100%" border="0" cellspacing=1 cellpadding=2>';
    echo '<tr><td>
           <table width="100%" border="0" cellspacing=0 cellpadding=2 class="logs" align="center">
           <tr><th>&nbsp;&nbsp;Ticket Aging and Its Count</th></tr>';
     
    $numtotal=0;
    if ($staffid) {
      $sqltotal='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = '.$staffid.' AND status = "open"';
    } else {
      $sqltotal='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id <> "0" AND status = "open"';
    }
    if(($restotal=db_query($sqltotal)) && ($numtotal=db_num_rows($restotal))) {
     $strtickettotal = '';
     $itotal = 1;
     while ($rowtotal = db_fetch_array($restotal)) {
       if ($itotal<$numtotal) {
         $strtickettotal .= '<a href="tickets.php?id='.$rowtotal['ticket_id'].'" target="_blank">'.$rowtotal['ticketID'].'</a>, ';
       } elseif ($itotal>=$numtotal) {
         $strtickettotal .=  '<a href="tickets.php?id='.$rowtotal['ticket_id'].'" target="_blank">'.$rowtotal['ticketID'].'</a>';
       }
       $itotal++;
     }
    }
    ?>
    <tr class="row1" id="1">
    <td>
    <a href="javascript:toggleMessage('1');">
    <img border="0" align="left" id="img_1" src="images/plus.gif">
    <span style="color:000; float: left; width:350px;"><? echo "Total Number of Active Tickets: "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b>'.$numtotal.'</b><br />'; ?></span></a>
       <div id="msg_1" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strtickettotal)?$strtickettotal:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    <?
    $numact3days=0;
    if ($staffid) {
      $sqlact3days='SELECT * FROM '.TICKET_TABLE.' '.
                   'WHERE staff_id <> "0" AND 3 >= TIMESTAMPDIFF(DAY,created,NOW()) '.
                   'AND status = "open" and staff_id = '.$staffid.'';
    } else {
      $sqlact3days='SELECT * FROM '.TICKET_TABLE.' '.
                   'WHERE staff_id <> "0" AND 3 >= TIMESTAMPDIFF(DAY,created,NOW()) '.
                   'AND status = "open"';
    }
    if(($resact3days=db_query($sqlact3days)) && ($numact3days=db_num_rows($resact3days))) {
     $strticketact3days = '';
     $iact3days = 1;
     while ($rowact3days = db_fetch_array($resact3days)) {
       if ($iact3days<$numact3days) {
         $strticketact3days .= '<a href="tickets.php?id='.$rowact3days['ticket_id'].'" target="_blank">'.$rowact3days['ticketID'].'</a>, ';
       } elseif ($iact3days>=$numact3days) {
         $strticketact3days .=  '<a href="tickets.php?id='.$rowact3days['ticket_id'].'" target="_blank">'.$rowact3days['ticketID'].'</a>';
       }
       $iact3days++;
     }
    }
    ?>
    <tr class="row2" id="2">
    <td>
    <a href="javascript:toggleMessage('2');">
    <img border="0" align="left" id="img_2" src="images/plus.gif">
    <span style="color:000; float: left; width:350px;"><? echo "Total Number of Active Tickets (<= 3 Days): "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b><span align="right">'.$numact3days.'</span></b><br />'; ?></span></a>
       <div id="msg_2" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strticketact3days)?$strticketact3days:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    <?
    $numact7days=0;
    if ($staffid) {
      $sqlact7days='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = '.$staffid.' AND (7 >= TIMESTAMPDIFF(DAY,created,NOW()) AND 3 < TIMESTAMPDIFF(DAY,created,NOW())) AND status = "open"';
    } else {
      $sqlact7days='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id <> "0" AND (7 >= TIMESTAMPDIFF(DAY,created,NOW()) AND 3 < TIMESTAMPDIFF(DAY,created,NOW())) AND status = "open"';
    }
     
    if(($resact7days=db_query($sqlact7days)) && ($numact7days=db_num_rows($resact7days))) {
     $strticketact7days = '';
     $iact7days = 1;
     while ($rowact7days = db_fetch_array($resact7days)) {
       if ($iact7days<$numact7days) {
         $strticketact7days .= '<a href="tickets.php?id='.$rowact7days['ticket_id'].'" target="_blank">'.$rowact7days['ticketID'].'</a>, ';
       } elseif ($iact7days>=$numact7days) {
         $strticketact7days .=  '<a href="tickets.php?id='.$rowact7days['ticket_id'].'" target="_blank">'.$rowact7days['ticketID'].'</a>';
       }
       $iact7days++;
     }
    }
    ?>
    <tr class="row1" id="3">
    <td>
    <a href="javascript:toggleMessage('3');">
    <img border="0" align="left" id="img_3" src="images/plus.gif">
    <span style="color:000; float: left; width:350px;"><? echo "Total Number of Active Tickets (> 3 Days and <= 7 Days): "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b><span align="right">'.$numact7days.'</span></b><br />'; ?></span></a>
       <div id="msg_3" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strticketact7days)?$strticketact7days:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    <?
    $numact14days=0;
    if ($staffid) {
      $sqlact14days='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = '.$staffid.' AND (14 >= TIMESTAMPDIFF(DAY,created,NOW()) AND 7 < TIMESTAMPDIFF(DAY,created,NOW())) AND status = "open"';
    } else {
      $sqlact14days='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id <> "0" AND (14 >= TIMESTAMPDIFF(DAY,created,NOW()) AND 7 < TIMESTAMPDIFF(DAY,created,NOW())) AND status = "open"';
    }
     
    if(($resact14days=db_query($sqlact14days)) && ($numact14days=db_num_rows($resact14days))) {
     $strticketact14days = '';
     $iact14days = 1;
     while ($rowact14days = db_fetch_array($resact14days)) {
       if ($iact14days<$numact14days) {
         $strticketact14days .= '<a href="tickets.php?id='.$rowact14days['ticket_id'].'" target="_blank">'.$rowact14days['ticketID'].'</a>, ';
       } elseif ($iact14days>=$numact14days) {
         $strticketact14days .=  '<a href="tickets.php?id='.$rowact14days['ticket_id'].'" target="_blank">'.$rowact14days['ticketID'].'</a>';
       }
       $iact14days++;
     }
    }
    ?>
    <tr class="row2" id="4">
    <td>
    <a href="javascript:toggleMessage('4');">
    <img border="0" align="left" id="img_4" src="images/plus.gif">
    <span style="color:000; float: left; width:350px;"><? echo "Total Number of Active Tickets (> 7 Days and <= 14 Days): "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b><span align="right">'.$numact14days.'</span></b><br />'; ?></span></a>
       <div id="msg_4" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strticketact14days)?$strticketact14days:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    <?
    $numactmore14days=0;
    if ($staffid) {
      $sqlactmore14days='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = '.$staffid.' AND 14 < TIMESTAMPDIFF(DAY,created,NOW()) AND status = "open"';
    } else {
      $sqlactmore14days='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id <> "0" AND 14 < TIMESTAMPDIFF(DAY,created,NOW()) AND status = "open"';
    }
     
    if(($resactmore14days=db_query($sqlactmore14days)) && ($numactmore14days=db_num_rows($resactmore14days))) {
     $strticketactmore14days = '';
     $iactmore14days = 1;
     while ($rowactmore14days = db_fetch_array($resactmore14days)) {
       if ($iactmore14days<$numactmore14days) {
         $strticketactmore14days .= '<a href="tickets.php?id='.$rowactmore14days['ticket_id'].'" target="_blank">'.$rowactmore14days['ticketID'].'</a>, ';
       } elseif ($iactmore14days>=$numactmore14days) {
         $strticketactmore14days .=  '<a href="tickets.php?id='.$rowactmore14days['ticket_id'].'" target="_blank">'.$rowactmore14days['ticketID'].'</a>';
       }
       $iactmore14days++;
     }
    }
    ?>
    <tr class="row1" id="5">
    <td>
    <a href="javascript:toggleMessage('5');">
    <img border="0" align="left" id="img_5" src="images/plus.gif">
    <span style="color:000; float: left; width:350px;"><? echo "Total Number of Active Tickets (> 14 Days): "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b><span align="right">'.$numactmore14days.'</span></b><br />'; ?></span></a>
       <div id="msg_5" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strticketactmore14days)?$strticketactmore14days:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    </table>
    </td></tr>
    </table>

    Ticket Aging Report
    Ticket Aging Report for The Certain Staff
    Ticket Aging for The Certain Staff with Detail Item Ticket

  3. Create a new file named ticketsummary.inc.php file, and copy paste this following code to that file, and put this file into your /include/staff/ sub-directory of your osTicket:

    <?php
    if(!defined('OSTSCPINC') || !@$thisuser->isStaff()) die('Access Denied');
     
    if($_REQUEST['staffid']) {
      $staffid = $_REQUEST['staffid'];
    }
     
    echo '<div class="msg">Ticket Summary</div>';
     
    $numtotalstaff=0;
    $sqltotalstaff='SELECT DISTINCT(t.staff_id), s.username, s.firstname, s.lastname '.
                   'FROM '.TICKET_TABLE.' t, '.STAFF_TABLE.' s '.
                   'WHERE t.staff_id <> "0" AND t.staff_id=s.staff_id';
    if(($restotalstaff=db_query($sqltotalstaff)) && ($numtotalstaff=db_num_rows($restotalstaff))) {
     $strtickettotalstaff = '';
     $itotalstaff = 1;
    ?>
    <form action="admin.php?t=ticketsummary" method="post">
     <input type=hidden name='a' value='staff'>
     Staff Name:&nbsp;<select name="staffid" >
      <option value="" selected >Select One</option>
    <?
     while ($rowtotalstaff = db_fetch_array($restotalstaff)) {
       $selected = ($_REQUEST['staffid']==$rowtotalstaff['staff_id'])?'selected':''; ?>
       <option value="<?=$rowtotalstaff['staff_id']?>"<?=$selected?>><? echo $rowtotalstaff['username'].' ('.$rowtotalstaff['firstname'].' '.$rowtotalstaff['lastname'].')'; ?></option>
    <?
     }
    ?>
     </select>
     <input class="button" type="submit" value="View">
    </form>
    <?
    }
     
    echo '<div style="margin-bottom:20px; padding-top:0px;">';
    echo '<table width="100%" border="0" cellspacing=1 cellpadding=2>';
    echo '<tr><td>
           <table width="100%" border="0" cellspacing=0 cellpadding=2 class="logs" align="center">
           <tr><th>&nbsp;&nbsp;Ticket Summary and Its Count</th></tr>';
     
    $numstaff=0;
    if ($staffid) {
      $sqlstaff='SELECT DISTINCT(t.staff_id), s.username FROM '.TICKET_TABLE.' t, '.STAFF_TABLE.' s '.
              'WHERE t.staff_id = '.$staffid.' AND t.staff_id=s.staff_id';
    } else {
      $sqlstaff='SELECT DISTINCT(t.staff_id), s.username FROM '.TICKET_TABLE.' t, '.STAFF_TABLE.' s '.
              'WHERE t.staff_id <> "0" AND t.staff_id=s.staff_id';
    }
     
    if(($resstaff=db_query($sqlstaff)) && ($numstaff=db_num_rows($resstaff))) {
     $strticketstaff = '';
     $istaff = 1;
     while ($rowstaff = db_fetch_array($resstaff)) {
       if ($istaff<$numstaff) {
         $strticketstaff .= '<a href="admin.php?t=staff&id='.$rowstaff['staff_id'].'" target="_blank">'.$rowstaff['username'].'</a>, ';
       } elseif ($istaff>=$numstaff) {
         $strticketstaff .=  '<a href="admin.php?t=staff&id='.$rowstaff['staff_id'].'" target="_blank">'.$rowstaff['username'].'</a>';
       }
       $istaff++;
     }
    }
    ?>
     
    <tr class="row1" id="1">
    <td>
    <a href="javascript:toggleMessage('1');">
    <img border="0" align="left" id="img_1" src="images/plus.gif">
    <span style="color:000; float: left; width:250px;"><? echo "Staff in Charge: "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b>'.$numstaff.'</b><br />'; ?></span></a>
       <div id="msg_1" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Staff in Charge Username: <i><? echo ($strticketstaff)?$strticketstaff:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    <?
    $numtotal=0;
    if ($staffid) {
      $sqltotal='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = '.$staffid.'';
    } else {
      $sqltotal='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id <> "0"';
    }
    if(($restotal=db_query($sqltotal)) && ($numtotal=db_num_rows($restotal))) {
     $strtickettotal = '';
     $itotal = 1;
     while ($rowtotal = db_fetch_array($restotal)) {
       if (($itotal<$numtotal)) {
         $strtickettotal .= '<a href="tickets.php?id='.$rowtotal['ticket_id'].'" target="_blank">'.$rowtotal['ticketID'].'</a>, ';
       } elseif ($itotal>=$numtotal) {
         $strtickettotal .=  '<a href="tickets.php?id='.$rowtotal['ticket_id'].'" target="_blank">'.$rowtotal['ticketID'].'</a>';
       }
       $itotal++;
     }
    }
    ?>
    <tr class="row2" id="2">
    <td>
    <a href="javascript:toggleMessage('2');">
    <img border="0" align="left" id="img_2" src="images/plus.gif">
    <span style="color:000; float: left; width:250px;"><? echo "Total Number of Active Tickets: "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b>'.$numtotal.'</b><br />'; ?></span></a>
       <div id="msg_2" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strtickettotal)?$strtickettotal:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
    <?
     
    $numtotalclosed=0;
    if ($staffid) {
      $sqltotalclosed='SELECT * FROM '.TICKET_TABLE.' WHERE status = "closed" and staff_id='.$staffid.'';
    } else {
      $sqltotalclosed='SELECT * FROM '.TICKET_TABLE.' WHERE status = "closed"';
    }
    if(($restotalclosed=db_query($sqltotalclosed)) && ($numtotalclosed=db_num_rows($restotalclosed))) {
     $strtickettotalclosed = '';
     $itotalclosed = 1;
     while ($rowtotalclosed = db_fetch_array($restotalclosed)) {
       if ($itotalclosed<$numtotalclosed) {
         $strtickettotalclosed .= '<a href="tickets.php?id='.$rowtotalclosed['ticket_id'].'" target="_blank">'.$rowtotalclosed['ticketID'].'</a>, ';
       } elseif ($itotalclosed>=$numtotalclosed) {
         $strtickettotalclosed .=  '<a href="tickets.php?id='.$rowtotalclosed['ticket_id'].'" target="_blank">'.$rowtotalclosed['ticketID'].'</a>';
       }
       $itotalclosed++;
     }
    }
    ?>
    <tr class="row1" id="3">
    <td>
    <a href="javascript:toggleMessage('3');">
    <img border="0" align="left" id="img_3" src="images/plus.gif">
    <span style="color:000; float: left; width:250px;"><? echo "Total Number of Closed Tickets: "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b>'.$numtotalclosed.'</b><br />'; ?></span></a>
       <div id="msg_3" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strtickettotalclosed)?$strtickettotalclosed:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
    <?
     
    $numtotalunassigned=0;
    if ($staffid) {
      $sqltotalunassigned='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = "0" and staff_id = '.$staffid.'';
    } else {
      $sqltotalunassigned='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id = "0"';
    }
    if(($restotalunassigned=db_query($sqltotalunassigned)) && ($numtotalunassigned=db_num_rows($restotalunassigned))) {
     $strtickettotalunassigned = '';
     $itotalunassigned = 1;
     while ($rowtotalunassigned = db_fetch_array($restotalunassigned)) {
       if ($itotalunassigned<$numtotalunassigned) {
         $strtickettotalunassigned .= '<a href="tickets.php?id='.$rowtotalunassigned['ticket_id'].'" target="_blank">'.$rowtotalunassigned['ticketID'].'</a>, ';
       } elseif ($itotalunassigned>=$numtotalunassigned) {
         $strtickettotalunassigned .=  '<a href="tickets.php?id='.$rowtotalunassigned['ticket_id'].'" target="_blank">'.$rowtotalunassigned['ticketID'].'</a>';
       }
       $itotalunassigned++;
     }
    }
    ?>
    <tr class="row2" id="4">
    <td>
    <a href="javascript:toggleMessage('4');">
    <img border="0" align="left" id="img_4" src="images/plus.gif">
    <span style="color:000; float: left; width:250px;"><? echo "Total Number of Unassigned Tickets: "; ?></span>
    &nbsp;&nbsp;
     <span class="Icon <?=$icon?>" align="right"><? echo '<b>'.$numtotalunassigned.'</b><br />'; ?></span></a>
       <div id="msg_4" class="hide">
           <hr>
           <span style="text-align:left;float:left;">Ticket Item(s): <i><? echo ($strtickettotalunassigned)?$strtickettotalunassigned:'-'; ?></i></span><br />
       </div>
    </td>
    </tr>
     
    </table>
    </td></tr>
    </table>

    Ticket Summary

  4. Create a new file named ticketstaff.inc.php file, and copy paste this following code to that file, and put this file into your /include/staff/ sub-directory of your osTicket:

    <?php
    if(!defined('OSTSCPINC') || !@$thisuser->isStaff()) die('Access Denied');
     
    $qselect='SELECT DISTINCT(t.staff_id), s.username, s.firstname, s.lastname ';
    $qfrom='FROM '.TICKET_TABLE.' t, '.STAFF_TABLE.' s ';
    $qwhere=' WHERE t.staff_id <> "0" AND t.staff_id=s.staff_id ';
    //get log count based on the query so far..
    $total=db_count("SELECT count(*) $qfrom $qwhere");
    $pagelimit=30;
    //pagenate
    $pageNav=new Pagenate($total,$page,$pagelimit);
    $pageNav->setURL('admin.php',$qstr);
    $query="$qselect $qfrom $qwhere ORDER BY t.created DESC LIMIT ".$pageNav->getStart().",".$pageNav->getLimit();
    //echo $query;
    $result = db_query($query);
    $showing=db_num_rows($result)?$pageNav->showing():"";
    ?>
    <div class="msg">Ticket for Staff</div>
    <div style="margin-bottom:20px; padding-top:0px;">
     <table width="100%" border="0" cellspacing=1 cellpadding=2>
        <form action="tickets.php" method="POST" name='tickets' onSubmit="return checkbox_checker(this,1,0);">
        <input type="hidden" name="a" value="mass_process" >
        <input type="hidden" name="status" value="<?=$statusss?>" >
        <tr><td>
           <table width="100%" border="0" cellspacing=0 cellpadding=2 class="logs" align="center">
            <tr><th>&nbsp;&nbsp;Staff Name and Tickets Count</th></tr>
            <?
            $class = "row1";
            $total=0;
            $i=1;
            if($result && ($num=db_num_rows($result))):
                while ($row = db_fetch_array($result)) {
                    ?>
                <tr class="<?=$class?> " id="<?=$i?>">
                    <td>
                      <a href="javascript:toggleMessage('<?=$i?>');">
                      <img border="0" align="left" id="img_<?=$i?>" src="images/plus.gif">
                      <span style="color:000; float: left; width:190px;"><?echo $row['username']." (".$row['firstname']." ".$row['lastname'].")";?></span>
     
                    <?
                    $sqln='SELECT * FROM '.TICKET_TABLE.' WHERE staff_id='.$row["staff_id"].'';
                    if(($resn=db_query($sqln)) && ($numn=db_num_rows($resn))) {
     
                    }
                    ?>
                      &nbsp;&nbsp;
                      <span class="Icon <?=$icon?>"><b><?=$numn?></b></span></a>
                        <div id="msg_<?=$i?>" class="hide">
                            <hr>
     
                    <?
     
                    $sqlt='SELECT ticketID, ticket_id FROM '.TICKET_TABLE.' WHERE staff_id='.$row["staff_id"].'';
                    if(($rest=db_query($sqlt)) && ($numt=db_num_rows($rest))) {
                        $strt='';
                        $it=1;
                        while ($rowt = db_fetch_array($rest)) {
                        if ($it<$numt) {
                            $strt.='<a href="tickets.php?id='.$rowt['ticket_id'].'" target="_blank">'.$rowt['ticketID'].'</a>, ';
                        } elseif ($it>=$numt) {
                            $strt.='<a href="tickets.php?id='.$rowt['ticket_id'].'" target="_blank">'.$rowt['ticketID'].'</a>';
                        }
                        $it++;
                        }
                    }
     
     
                    ?>
                      <span style="text-align:left;float:left;"><i><?=$strt?>&nbsp;&nbsp;</i></span>
                      </div>
                    </td>
                </tr>
                <?
                $class = ($class =='row2') ?'row1':'row2';
                $i++;
                } //end of while.
            else: //not tickets found!! ?>
                <tr class="<?=$class?>"><td><b>Query returned 0 results.</b></td></tr>
            <?
            endif; ?>
           </table>
        </td></tr>
        <?
        if($num>0){
        ?>
            <tr><td style="padding-left:20px">page:<?=$pageNav->getPageLinks()?></td></tr>
        <?} ?>
        </form>
     </table>
    </div>
    <?

    Ticket for Staff

  • Share/Bookmark
1,301 views Print This Post Print This Post

  1. Thun Chropeckorn
    February 9th, 2010 at 08:35 | #1

    Hello Masino,
    I’ve been looking for this kind of script for OSticket however I cannot decide if I should use RC5 or RC4. I’ve been using a reporting panel (http://osticket.com/forums/showthread.php?t=1537) to generate statistical data and it has worked well. But your work has lots of potential. Can you improve this script to display ticket summary per month of every staff and department? Also it would be better to include a statistical count of help topics. For example: Auto Paint Department Aug 2010, Paint chipping 3, Paint discoloration 14 etc.

    Thanks and God Bless You.

  2. February 9th, 2010 at 11:56 | #2

    @Thun Chropeckorn

    Thanks for your feedback. I will try to implement it whenever I had enough time to do that. God bless you, too. :-)

  3. Shinshigumi
    February 22nd, 2010 at 08:52 | #3

    Helo sir,

    Thank you very much for this mod, as Thun Cropeckorr said, it has more potential so i haven’t try to apply that mod cause i’m waiting for your update which includes graphical representation of data. THANKS! kip safe!

  4. April 1st, 2010 at 00:26 | #4

    Hi, I have implemented this and notice that any tickets that are assigned to somebody, show up under the active tickets in the ticket summary, even though they have been closed..

    is that taken care of in the updated versions?

    thanks..

    and thanks again for your mods… makes osticket a lot more functional

  5. Cuong
    May 28th, 2010 at 10:06 | #5

    Hi Masino,
    Please add one more MOD such as ” Post Internal Note” to Dashboard?
    Thank you for your help,
    Cuong

  6. Cuong
    May 29th, 2010 at 07:00 | #6

    Corrections:
    Hi Masino,
    I need a Mod audit ticket and Dashboard to show under Dashboard. ( forget the “Post Internal Note”)
    Thank you for your help,

  7. Nino
    August 24th, 2010 at 02:38 | #7

    Masino,

    I have tried to implement this, but get errors. I have noticed that the “lines” you indicate is different on my side. I do not know if this is because you have other mods done. Anyway, you specify ticket aging in terms of days… Is there a way I can have the aging options in hours? Example,
    <=1 hour
    <=2 hours
    <=5 hours
    <=8 hours
    <= 24 hours
    Select end date -> Preview or export to csv
    Closed Tickets : Select start date -> Select end date -> Preview or export to csv
    Stale Tickets : Select start date -> Select end date -> Preview or export to csv
    etc.

    This seems like a much easier option, because then the admin can select the dates/hours.

    Your help on this one will be appreciated.

    Regards,

  1. No trackbacks yet.