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

Solution

Hi Beeman,

You're welcome. :)

Simply change this following code on the modification version:

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

become:

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

Done!

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):

//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:($keepticket)).

' WHERE '.

'ticket_id='.db_input($this->getId());

// ost_ticket_message

$sql2= 'UPDATE '.TICKET_MESSAGE_TABLE.' SET '.

' ticket_id='.db_input(Format:($keepticket)).

' WHERE '.

'ticket_id='.db_input($this->getId());

// ost_ticket_note

$sql3= 'UPDATE '.TICKET_NOTE_TABLE.' SET '.

' ticket_id='.db_input(Format:($keepticket)).

' WHERE '.

'ticket_id='.db_input($this->getId());

// ost_ticket_response

$sql4= 'UPDATE '.TICKET_RESPONSE_TABLE.' SET '.

' ticket_id='.db_input(Format:($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:($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:($keepticket)).' ))'.
                ';';
              db_query($sql7);
               $sqlt = 'SELECT ticketID FROM '.TICKET_TABLE.' WHERE ticket_id='.db_input(Format:($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:($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;

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

Thanks,

Beeman

masino_sinaga

Oooopsss...!

Sorry for that error.

Please change this 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:

$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

arihant2301

Can you please this mod to work with 1.6 ST Please

masino_sinaga

@[deleted]01,

Yep. It works fine in 1.6 ST too.

Sincerely,

Masino Sinaga

stopyelling

Keeping getting this error. Triple checked the code. Even tried on a new installation.

Table 'db388444217.ost_ticket' doesn't exist

Any ideas?

masino_sinaga

Yes. Simply change this code:

FROM ost_ticket

become:

FROM sfs_ticket

kiltym

Merge Ticket with Attachement

I have been using the Merge Ticket feature for a few months. Seems to work just fine.

However, I just had an incident where I was merging a ticket (ticket A). Ticket A had an attachment. It was a new ticket, nothing else but the initial message and the attachment. It worked fine from Ticket A, was able to download the attachment as normal.

I then merged Ticket A to Ticket B.

The entry from Ticket A is on Ticket B, it shows the message and the attachment. However, when I click on the Attachment osticket comes back with a page stating "No such file".

So, it seems the link between the attachment got messed up when I did the merge.

I have not had time to test it again, and see if it was a one off hiccup, but wanted to see if anyone knew about this, or saw the same issue.

Thanks for the support on this, it is appreciated.

And, great job with the mod, its very useful for us.

eric111

I have been using the Merge Ticket feature for a few months. Seems to work just fine.
However, I just had an incident where I was merging a ticket (ticket A). Ticket A had an attachment. It was a new ticket, nothing else but the initial message and the attachment. It worked fine from Ticket A, was able to download the attachment as normal.
I then merged Ticket A to Ticket B.
The entry from Ticket A is on Ticket B, it shows the message and the attachment. However, when I click on the Attachment osticket comes back with a page stating "No such file".
So, it seems the link between the attachment got messed up when I did the merge.
I have not had time to test it again, and see if it was a one off hiccup, but wanted to see if anyone knew about this, or saw the same issue.
Thanks for the support on this, it is appreciated.
And, great job with the mod, its very useful for us.

We have exactly the same problems when merging tickets which contain attachments!

Cheers,

Eric