Add Merge Ticket Feature into osTicket v1.6 RC5

[beeman]

Hi Masino,

Thank you for the great MOD. Is there a possibility that you can enable the ticket Merge functionality for Department Managers. I just think it is going to be more productive that way. Thanks.

Regards,
beeman

[masino_sinaga]

Hi Beeman,

You're welcome.

Simply change this following code on the modification version:

PHP Code:

if( $thisuser->isadmin() &&  ($ticket->getStatus()!='closed') ) { 


become:

PHP Code:

if( ($thisuser->isadmin() || $thisuser->isManager() ) &&  ($ticket->getStatus()!='closed') ) { 


I had tested it before, and it worked fine.

Sincerely,
Masino Sinaga

[beeman]

Thanks Masino,

The modification works well, however only the ticket that is closed displays an internal comment showing that it was merged with another ticket (it shows the ticket of the ticket it was merged with. It would be nice if the currently open ticket also reflected this message as well, so that users or staff can see at a glance if a particular open ticket has been merged with another ticket which is now closed. I love and appreciate your MOD's Masino; keep up the good work.

[masino_sinaga]

Thanks for your feedback. Hm..., I think it does make sense too. Well, I will modify it for you as soon as possible.

Sincerely,
Masino Sinaga

[masino_sinaga]

Hi Beeman,

I have successfully modified my post on my blog regarding your request.

Simply change the mergeTicket in \include\class.ticket.php file become this following code below. This is the latest version in order to insert response message about merging ticket in the destination ticket (besides in the origin ticket):

PHP Code:

    //merge this ticket to another ticket
    function mergeTicket($keepticket,$notifycustomer,$email,$status) {  
    global $cfg;
    global $thisuser;     
        // update these tables...:
        // ost_ticket_attachment
        $sql1= 'UPDATE '.TICKET_ATTACHMENT_TABLE.' SET  '.
                ' ticket_id='.db_input(Format::striptags($keepticket)).
            ' WHERE '.
                'ticket_id='.db_input($this->getId());
        // ost_ticket_message
        $sql2= 'UPDATE '.TICKET_MESSAGE_TABLE.' SET  '.
                ' ticket_id='.db_input(Format::striptags($keepticket)).
            ' WHERE '.
                'ticket_id='.db_input($this->getId());
        // ost_ticket_note
        $sql3= 'UPDATE '.TICKET_NOTE_TABLE.' SET  '.
                ' ticket_id='.db_input(Format::striptags($keepticket)).
            ' WHERE '.
                'ticket_id='.db_input($this->getId());
        // ost_ticket_response
        $sql4= 'UPDATE '.TICKET_RESPONSE_TABLE.' SET  '.
                ' ticket_id='.db_input(Format::striptags($keepticket)).
            ' WHERE '.
                'ticket_id='.db_input($this->getId());
        // create message on this ticket about merge (ost_ticket_message)
        $sql5= 'INSERT INTO '.TICKET_MESSAGE_TABLE.' SET  '.
                ' ticket_id='.db_input($this->getId()).
                ', source="Web"'.
                ', created=NOW()'.
                ', message=CONCAT("Merged with ticket ",'.
                '  ( SELECT ticketID FROM '.TICKET_TABLE.' WHERE ticket_id='.db_input(Format::striptags($keepticket)).' ))'.
                ';';
        // close and unassign this ticket (ost_ticket)
        $sql6 = 'UPDATE '.TICKET_TABLE.' SET  '.
                ' staff_id = 0'.
                ', status = "closed"'.
                ', updated=NOW()'.
                ', closed=NOW()'.
            ' WHERE '.
                'ticket_id='.db_input($this->getId());
 
        if((db_query($sql1)) && (db_query($sql2)) &&
      (db_query($sql3)) && (db_query($sql4)) &&
      (db_query($sql6)) ){
            if ( db_query($sql5) && $msgid=db_insert_id() ); {  // && $msgid=db_insert_id($res5)
              $sql7= 'INSERT INTO '.TICKET_RESPONSE_TABLE.' SET  '.
                ' msg_id='.db_input($msgid).
                ', ticket_id='.db_input($this->getId()).
                ', staff_id='.db_input($thisuser->getId()).
                ', staff_name='.db_input($thisuser->getName()).
                ', ip_address=""'.
                ', created=NOW()'.
                ', response=CONCAT("Ticket closed for merging reason ",'.
                '  ( SELECT ticketID FROM '.TICKET_TABLE.' WHERE ticket_id='.db_input(Format::striptags($keepticket)).' ))'.
                ';';
              db_query($sql7);
 
              $sqlt = 'SELECT ticketID FROM '.TICKET_TABLE.' WHERE ticket_id='.db_input(Format::striptags($keepticket));
              $rest=db_query($sqlt);
              if($rest && db_num_rows($rest))
                list($ticketID)=db_fetch_row($rest);
                $sqlm = 'SELECT m.msgid FROM '.TICKET_MESSAGE_TABLE.' m, '.TICKET_TABLE.' t 
                WHERE t.ticketID='.$ticketID.' AND m.ticket_id=t.ticket_id ORDER BY msgid ASC LIMIT 1'; 
              $resm=db_query($sqlm);
              if($resm && db_num_rows($resm))
                list($msgid)=db_fetch_row($resm);
 
              $sql8= 'INSERT INTO '.TICKET_RESPONSE_TABLE.' SET  '.
                ' msg_id='.db_input($msgid).
                ', ticket_id='.db_input(Format::striptags($keepticket)).
                ', staff_id='.db_input($thisuser->getId()).
                ', staff_name='.db_input($thisuser->getName()).
                ', ip_address=""'.
                ', created=NOW()'.
                ', response=CONCAT("Ticket merged from ticket ",'.$this->getExtId().')';
              db_query($sql8);              
            }
 
            if ($notifycustomer && ($status!='closed')) {
          $helpdesk_email=$cfg->getDefaultEmail();
              $helpdesk_email->send($email,
            'Merging Ticket #'.$this->getExtId().' to Ticket #'.$ticketID,
            'Your ticket #'.$this->getExtId().' has been merged to ticket #'.$ticketID.'.');
           }                                             
       return true;
    } else {
       return false;
    }
    } 


Sincerely,
Masino Sinaga

[beeman]

Thanks for the MOD Masino.

There is more info on the closed ticket ("Ticket closed for merging with XXXXX) which is nice but I still don't see any comments on the open ticket showing that it was merged with another one. A comment like "Merged with ticket XXXXX which is now closed" for instance, on the currently open ticket would be useful.

I also get an e-mail with this database error;

[SELECT m.msgid FROM ost_ticket_message m, ost_ticket t
WHERE t.ticketID=235093 AND m.ticket_id=t.ticket_id ORDER BY msgid ASC LIMIT 1]

Unknown column 'm.msgid' in 'field list'



Thanks,
Beeman

[masino_sinaga]

Sorry for that error.

Please change this code:

PHP Code:

                $sqlm = 'SELECT m.msgid FROM '.TICKET_MESSAGE_TABLE.' m, '.TICKET_TABLE.' t 
                WHERE t.ticketID='.$ticketID.' AND m.ticket_id=t.ticket_id ORDER BY msgid ASC LIMIT 1'; 


become:

PHP Code:

                $sqlm = 'SELECT m.msg_id FROM '.TICKET_MESSAGE_TABLE.' m, '.TICKET_TABLE.' t 
                WHERE t.ticketID='.$ticketID.' AND m.ticket_id=t.ticket_id ORDER BY msg_id ASC LIMIT 1'; 


About a comment as you mentioned like "Merged with ticket XXXXX which is now closed" for instance, on the currently open ticket would be useful, actually I have added that part.

You should find this comment: "Ticket merged from ticket {old_ticketID}" on the currently open ticket.

Sincerely,
Masino Sinaga

[beeman]

Cool

It works wonderfully now. Thank you Masino, I appreciate your help.

Regards,
Beeman