web/private_php/ams/autoload/ticket_queue.php

   1 <?php
   2 /**
   3 * Data class that holds a lot of queries that load specific tickets.
   4 * These queries are being used by the ticket_queue_handler class. An object of this class holds 2 attributes: the query and the params used for the query.
   5 * @author Daan Janssens, mentored by Matthew Lagoe
   6 */
   7 class Ticket_Queue{
   8
   9     private $query; /**< The query that loads specific tickets */
  10     private $params; /**< The parameter array that's being needed by the query */
  11
  12     /**
  13     * loads the not yet assigned tickets query into the objects attributes.
  14     */
  15     public function loadAllNotAssignedTickets(){
  16         $this->query =  "SELECT ticket . * FROM ticket LEFT JOIN assigned ON ticket.TId = assigned.Ticket WHERE assigned.Ticket IS NULL";
  17         $this->params = array();
  18     }
  19
  20     /**
  21     * loads the 'all' tickets query into the objects attributes.
  22     */
  23     public function loadAllTickets(){
  24         $this->query = "SELECT * FROM `ticket`";
  25         $this->params = array();
  26     }
  27
  28     /**
  29     * loads the 'all open' tickets query into the objects attributes.
  30     */
  31     public function loadAllOpenTickets(){
  32         $this->query = "SELECT * FROM ticket INNER JOIN ticket_user ON ticket.Author = ticket_user.TUserId and ticket.Status!=3";
  33         $this->params = array();
  34     }
  35
  36     /**
  37     * loads the 'closed' tickets query into the objects attributes.
  38     */
  39     public function loadAllClosedTickets(){
  40         $this->query = "SELECT * FROM ticket INNER JOIN ticket_user ON ticket.Author = ticket_user.TUserId and ticket.Status=3";
  41         $this->params = array();
  42     }
  43
  44     /**
  45     * loads the 'todo' tickets query & params into the objects attributes.
  46     * first: find the tickets assigned to the user with status = waiting on support,
  47     * second find all not assigned tickets that aren't forwarded yet.
  48     * find all tickets assigned to someone else witht status waiting on support,  with timestamp of last reply > 1 day,
  49     * find all non-assigned tickets forwarded to the support groups to which that user belongs
  50     * @param $user_id the user's id to whom the tickets should be assigned
  51     */
  52     public function loadToDoTickets($user_id){
  53
  54         $this->query = "SELECT * FROM `ticket` t LEFT JOIN `assigned` a ON t.TId = a.Ticket LEFT JOIN `ticket_user` tu ON tu.TUserId = a.User LEFT JOIN `forwarded` f ON t.TId = f.Ticket
  55         WHERE (tu.ExternId = :user_id AND t.Status = 1)
  56         OR (a.Ticket IS NULL AND f.Group IS NULL)
  57         OR (tu.ExternId != :user_id AND  t.Status = 1 AND (SELECT ticket_reply.Timestamp FROM `ticket_reply` WHERE Ticket =t.TId ORDER BY TReplyId DESC LIMIT 1)  < NOW() - INTERVAL 1 DAY )
  58         OR (a.Ticket IS NULL AND EXISTS (SELECT * FROM  `in_support_group` isg JOIN `ticket_user` tu2 ON isg.User = tu2.TUserId WHERE isg.Group = f.Group))
  59         ";
  60         $this->params = array('user_id' => $user_id);
  61     }
  62
  63     /**
  64     * loads the 'tickets asssigned to a user and waiting on support' query & params into the objects attributes.
  65     * @param $user_id the user's id to whom the tickets should be assigned
  66     */
  67     public function loadAssignedandWaiting($user_id){
  68         $this->query = "SELECT * FROM `ticket` t LEFT JOIN `assigned` a ON t.TId = a.Ticket LEFT JOIN `ticket_user` tu ON tu.TUserId = a.User
  69         WHERE (tu.ExternId = :user_id AND t.Status = 1)";
  70         $this->params = array('user_id' => $user_id);
  71     }
  72
  73
  74     /**
  75     * loads the 'created' query & params into the objects attributes.
  76     * This function creates dynamically a query based on the selected features.
  77     * @param $who specifies if we want to user the user_id or group_id to form the query.
  78     * @param $userid the user's id to whom the tickets should be assigned/not assigned
  79     * @param $groupid  the group's id to whom the tickets should be forwarded/not forwarded
  80     * @param $what specifies what kind of tickets we want to return: waiting for support, waiting on user, closed
  81     * @param $how specifies if the tickets should be or shouldn't be assigned/forwarded to the group/user selected.
  82     */
  83     public function createQueue($userid, $groupid, $what, $how, $who){
  84
  85         if($who == "user"){
  86             $selectfrom = "SELECT * FROM `ticket` t LEFT JOIN `assigned` a ON t.TId = a.Ticket LEFT JOIN `ticket_user` tu ON tu.TUserId = a.User";
  87             if ($how == "both"){
  88                 $assign = "";
  89             }else if ($how == "assigned"){
  90                 $assign = "tu.TUserId = :id" ;
  91             }else if ($how == "not_assigned"){
  92                 $assign = "(tu.TUserId != :id OR a.Ticket IS NULL)";
  93             }
  94         }else if ($who == "support_group"){
  95             $selectfrom = "SELECT * FROM `ticket` t LEFT JOIN `assigned` a ON t.TId = a.Ticket LEFT JOIN `ticket_user` tu ON tu.TUserId = a.User LEFT JOIN `forwarded` f ON t.TId = f.Ticket";
  96             if ($how == "both"){
  97                 $assign = "";
  98             }else if ($how == "assigned"){
  99                 $assign = "f.Group = :id";
 100             }else if ($how == "not_assigned"){
 101                 $assign = "(f.Group != :id  OR f.Ticket IS NULL)" ;
 102             }
 103
 104         }
 105
 106         if ($what == "waiting_for_support"){
 107             $status = "t.Status = 1";
 108         }else if ($what == "waiting_for_users"){
 109             $status = "t.Status = 0";
 110         }else if ($what == "closed"){
 111             $status = "t.Status = 3";
 112         }
 113
 114         if ($assign == "") {
 115             $query = $selectfrom;
 116             if(isset($status)){
 117                 $query = $query . " WHERE " . $status;
 118             }
 119         } else {
 120             $query = $selectfrom ." WHERE " . $assign;
 121             if(isset($status)){
 122                 $query = $query . " AND " . $status;
 123             }
 124         }
 125
 126
 127         if($who == "user"){
 128             $params = array('id' => $userid);
 129         }else if ($who == "support_group"){
 130             $params = array('id' => $groupid);
 131         }
 132
 133         $this->query = $query;
 134         $this->params = $params;
 135         //print_r($this);
 136     }
 137
 138
 139     ////////////////////////////////////////////Getters////////////////////////////////////////////////////
 140
 141     /**
 142     * get query attribute of the object.
 143     */
 144     public function getQuery(){
 145         return $this->query;
 146     }
 147
 148     /**
 149     * get params attribute of the object.
 150     */
 151     public function getParams(){
 152         return $this->params;
 153     }
 154 }