It looks like you're new here. If you want to get involved, click one of these buttons!
} elseif(!$errors['transfer']) {
$errors['err'] = 'Unable to complete the ticket transfer';
$errors['transfer']='Correct the error(s) below and try again!';
}
}
break;
case 'mergeticket':
if( $ticket->mergeTicket($_POST['keepticket'],
$_POST['notifycustomer'],
$_POST['email'],
$_POST['status'] )){
$title='Merged Tickets';
$msg='Ticket Has Been Sucessfully Merged.';
}else{
$errors['err']=$errors['err']?$errors['err']:'Unable to merge tickets or the ticket has been closed.';
}
break;
//Insert Internal Notes
function logNote($title, $note, $poster='SYSTEM', $alert=true) {
$errors = array();
return $this->postNote(
array('title' => $title, 'note' => $note),
$errors,
$poster,
$alert);
}
//merge this ticket to another ticket
function mergeTicket($keepticket,$notifycustomer,$email,$status) {
global $cfg;
global $thisstaff;
// 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_THREAD_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_THREAD_TABLE.' SET '.
' ticket_id='.db_input($this->getId()).
', source=\"Web\"'.
', created=NOW()'.
', body=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_THREAD_TABLE.' SET '.
//' id='.db_input($msgid).
' ticket_id='.db_input($this->getId()).
', staff_id='.db_input($thisstaff->getId()).
', poster='.db_input($thisstaff->getName()).
', ip_address=\"\"'.
', created=NOW()'.
', body=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 id FROM '.TICKET_THREAD_TABLE.' m, '.TICKET_TABLE.' t
WHERE t.ticketID='.$ticketID.' AND m.ticket_id=t.ticket_id ORDER BY id ASC LIMIT 1';
$resm=db_query($sqlm);
if($resm && db_num_rows($resm))
list($msgid)=db_fetch_row($resm);
$sql8= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
//' id='.db_input($msgid).
' ticket_id='.db_input(Format::striptags($keepticket)).
', staff_id='.db_input($thisstaff->getId()).
', poster='.db_input($thisstaff->getName()).
', ip_address=\"\"'.
', created=NOW()'.
', body=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;
}
}
if($thisstaff->canBanEmails()) {
if(!$emailBanned) {?>
<li><a id=\"ticket-banemail\" href=\"#banemail\"><i class=\"icon-ban-circle\"></i> Ban Email (<?php echo $ticket->getEmail(); ?>)</a></li>
<?php
} elseif($unbannable) { ?>
<li><a id=\"ticket-banemail\" href=\"#unbanemail\"><i class=\"icon-undo\"></i> Unban Email (<?php echo $ticket->getEmail(); ?>)</a></li>
<?php
}
}?>
</ul>
</div>
</td>
</tr>
</table>
<table class=\"mergetickets\" cellspacing=\"0\" cellpadding=\"0\" width=\"940\" border=\"0\">
<tr>
<td width=\"50\">
<table border=\"0\" cellspacing=\"\" cellpadding=\"4\" width=\"100%\">
<tr>
<th width=\"100\">Merge Ticket?:</th>
<?
$sql = 'SELECT ticket_id, concat(ticketid,\": \",subject) AS label FROM '.TICKET_TABLE.' WHERE email='.
'(SELECT email FROM ost_ticket WHERE ticket_id = '.$id.') AND status '.
'= \"open\" AND ticket_id <> '.$id.' ORDER BY `created` DESC ';
$lookuptickets = db_query($sql);
if (db_num_rows($lookuptickets)) {
?>
<p>
<form action=\"tickets.php?id=<?=$id?>#mergeticket\" name=\"notes\" method=\"post\" enctype=\"multipart/form-data\">
<?php csrf_token(); ?>
<input type=\"hidden\" name=\"ticket_id\" value=\"<?=$id?>\">
<input type=\"hidden\" name=\"a\" value=\"mergeticket\">
<input type=\"hidden\" name=\"email\" value=\"<?php echo $ticket->getEmail(); ?>\">
<input type=\"hidden\" name=\"status\" value=\"<?php echo $ticket->getStatus(); ?>\">
Merge this current ticket into ticket # <select id=\"keepticket\" name=\"keepticket\">
<?php
while (list($ticket_id,$label) = db_fetch_row($lookuptickets)){
?>
<option value=\"<?=$ticket_id?>\"><?=$label?></option>
<?
}?>
</select>
<div><input type=\"checkbox\" value=\"1\" name=\"notifycustomer\" />Send Email to Customer inform the Ticket Merging</div>
<div style=\"margin-left: 50px; margin-top: 5px; margin-bottom: 10px;border: 0px;\" align=\"left\">
<input class=\"button\" type='submit' value='Merge Ticket'/>
<input class=\"button\" type='reset' value='Reset' />
<input class=\"button\" type='button' value='Cancel' onClick=\"history.go(-1)\" />
</div>
<?php } else {
echo \"Sorry, this ticket cannot be merged into another since there is no ticket has the same email address with this.\";
}
?>
</p>
</form>
</p>
</div>
</tr>
<tr>
</tr>
</table>
Comments
Got a bunch of SQL errors as a result. (I've got a Fresh install of 1.7 Stable)
Example of some of them:
I'll give it a shot tomorrow .. Thanks so much!!
Works well now.
No errors, no duplication errors.
In case this is breaking someone's ticket view, there are PHP short tags in the mod which will cause hiccups if your php.ini doesn't allow them. Simply replace all instances of <? with <?php
Is there something that I am missing?
This is an awesome MOD, exactly what I needed. One question, is there any way to add the link to the new ticket in the email sent to the customer?
Like in the other emails sent out:
You can view this ticket's progress online here: http://link.to.new.ticket
Thanks!
Andrew
Thanks for the great MOD, I fancied it up a bit. Here is the screenshot and code.
NOTE: I only tested it in 1.7RC5
change the first two files like shown.
but change the ticket-view-inc.php to this:
After:
Add:
After:
Add:
It should work in 1.7 STABLE with this change:
Change:
To:
I also moved my response options to the left, because I have a "View Attachements" tab, by changing the scp css file here:
There is a spelling error in the original MOD also... \include\class.ticket.php Here:
(In my application I also have it look for duplicate phone numbers, to list available tickets to merge with, in case one person sends in two tickets from different emails.)
Hope people find this useful, and I'm not trying to highjack your thread just add to the great MOD.
Sorry, this ticket cannot be merged into another since there is no ticket has the same email address with this.
Thanks. It was awesome and working well. :p
Thanks.It was awesome and working well.You saved my time.
I just added it without too much problems.
Best regards
Jazz
See the attachment.
Any idea? For the moment we use OST 1.7.3
Thanks Martin
I will try to create a function to reverse the merger
I was thinking to add a merged_from field in the thread table, containing a comma-separated list of former ticket IDs.
In closed tickets, I was thinking to add a form (activated only in case the ticket id is found in any merged_from field in thread table) to restore the ticket from merger.
Any suggestion or any better ideas?
Ok, I have done it!
The following "mod of the mod" supports unmerging tickets, even if there are multiple sequential mergers (in such a case, one may unmerge the last merged ticket first and then go backwards through the merger sequence)
Moreover, if one ticket has been closed because of merger, one cannot simply reopen it, but must instead unmerge and reopen it (I changed the button).
I also added a check so only staff that can close tickets can merge-unmerge them.
Finally, I added a link to the merge-unmerge messages, so one can open the other merged-unmerged ticket-
That said, we are ready to go. (I have to split the post in more than one comment)
First, modify the files as explained by tristannovak above.
Then, add a varchar column named 'merged_from' to the ost_ticket_thread table.
Then, in staff/tickets.php, after:
add:
in include/class.ticket.php, change:
to:
$sql5= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
[.......]
', body=CONCAT("Merged with ticket ",'.
' ( SELECT ticketID FROM '.TICKET_TABLE.' WHERE ticket_id='.db_input(Format::striptags($keepticket)).' ))'.
';';
to:
$sql5= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
[.....]
', body=CONCAT("Merged with ticket ",'.
' ( SELECT ticketID FROM '.TICKET_TABLE.' WHERE ticket_id='.db_input(Format::striptags($keepticket)).' ), '.
db_input(' '.$cfg->getBaseUrl().'/scp/tickets.php?id='.$keepticket).')'.
';';
change:
$sql8= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
[.....]
', body=CONCAT("Ticket merged from ticket ",'.$this->getExtId().')';
to:
$sql8= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
[.....]
', body=CONCAT("Ticket merged from ticket ",'.$this->getExtId().', '.
db_input(' '.$cfg->getBaseUrl().'/scp/tickets.php?id='.$this->getId()).')';
'Merging Ticket #'.$this->getExtId().' to Ticket #'.$ticketID,
'Your ticket #'.$this->getExtId().' has been merged to ticket #'.$ticketID.'.');
}
return true;
} else {
return false;
}
}
add:
function unmergeTicket(){
global $thisstaff;
global $cfg;
// get ticket id and no. of the ticket which this ticket has been merged to
$sql="SELECT ticket_id, ticketID FROM ".TICKET_TABLE.
" WHERE ticket_id=".
"(SELECT ticket_id FROM ".TICKET_THREAD_TABLE.
" WHERE merged_from LIKE ".db_input("%/".$this->getId()."/")." LIMIT 1)";
if(($res=db_query($sql)) && db_num_rows($res))
list($mergedTo_ticket_id, $mergedTo_ticketID)=db_fetch_row($res);
// restore ticket thread AND attachments AND reopen ticket in one mysql transaction
db_query("START TRANSACTION");
$restoreAttachments = db_query("UPDATE ".TICKET_ATTACHMENT_TABLE." SET ticket_id=".db_input($this->getId()).
" WHERE ref_id IN (SELECT id FROM ".TICKET_THREAD_TABLE." WHERE merged_from LIKE ".db_input("%/".$this->getId()."/").")");
$restoreThread = db_query("UPDATE ".TICKET_THREAD_TABLE." SET ticket_id=".db_input($this->getId()).
", merged_from = REPLACE (merged_from, ".db_input("/".$this->getId()."/").", '') ".
"WHERE merged_from LIKE ".db_input("%/".$this->getId()."/"));
$reopenTicket = db_query("UPDATE ".TICKET_TABLE." SET status='open' WHERE ticket_id=".db_input($this->getId()));
if ($restoreAttachments && $restoreThread && $reopenTicket) {
db_query("COMMIT");
//add message to this ticket
$sql= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
' ticket_id='.db_input($this->getId()).
', staff_id='.db_input($thisstaff->getId()).
', poster='.db_input($thisstaff->getName()).
', ip_address=""'.
', created=NOW()'.
', body=CONCAT("Ticket unmerged from Ticket ", '.db_input($mergedTo_ticketID).', '.
db_input(' '.$cfg->getBaseUrl().'/scp/tickets.php?id='.$mergedTo_ticket_id).')';
db_query($sql);
//add message to the ticket which this ticket had been merged to
$sql= 'INSERT INTO '.TICKET_THREAD_TABLE.' SET '.
' ticket_id='.db_input($mergedTo_ticket_id).
', staff_id='.db_input($thisstaff->getId()).
', poster='.db_input($thisstaff->getName()).
', ip_address=""'.
', created=NOW()'.
', body=CONCAT("Ticket ", '.db_input($this->getExtId()).', '.
db_input(' ('.$cfg->getBaseUrl().'/scp/tickets.php?id='.$this->getId().')').', " unmerged from this ticket")';
db_query($sql);
return true;
} else {
db_query("ROLLBACK");
return false;
}
}
if($thisstaff->canCloseTickets()) {
if($ticket->isOpen()) {?>
<a id="ticket-close" class="action-button" href="#close"><i class="icon-remove-circle"></i>Close</a>
<?php
} else { ?>
<a id="ticket-reopen" class="action-button" href="#reopen"><i class="icon-undo"></i>Reopen</a>
<?php
} ?>
<?php
} ?>
to:
if($thisstaff->canCloseTickets()) {
$sql="SELECT * FROM ".TICKET_THREAD_TABLE." WHERE merged_from LIKE ".db_input("%/".$ticket->getId()."/");
$sql2="SELECT * FROM ".TICKET_THREAD_TABLE." WHERE merged_from LIKE ".db_input("%/".$ticket->getId()."/%");
if($ticket->isOpen()) {?>
<a id="ticket-close" class="action-button" href="#close"><i class="icon-remove-circle"></i>Close</a>
<?php
} elseif(($res=db_query($sql)) && db_num_rows($res)) { //closed after merge ?>
<a id="ticket-unmerge" class="action-button" href="#unmerge"><i class="icon-undo"></i>Unmerge</a>
<?php
} elseif(!$res=db_query($sql2) || !db_num_rows($res)) { //not in a previous merge ?>
<a id="ticket-reopen" class="action-button" href="#reopen"><i class="icon-undo"></i>Reopen</a>
<?php
} ?>
<?php
} ?>
change:
if (db_num_rows($lookuptickets_email) && $ticket->isOpen()) {?>
<li><a id="merge_tab" href="#merge">Merge</a></li>
to:
if (db_num_rows($lookuptickets_email) && $ticket->isOpen() && $thisstaff->canCloseTickets()) {?>
<li><a id="merge_tab" href="#merge">Merge</a></li>
change:
<form id="merge" action="tickets.php?id=<?php echo $ticket->getId(); ?>#mergeticket" name="notes" method="post" enctype="multipart/form-data">
to:
<form id="merge" onsubmit="return confirm('This ticket will be merged and closed. Are you sure you want to proceed?');" action="tickets.php?id=<?php echo $ticket->getId(); ?>#mergeticket" name="merge" method="post" enctype="multipart/form-data">
<p class="confirm-action" style="display:none;" id="claim-confirm">Are you sure want to <b>claim</b> (self assign) this ticket?
</p>
add:
<p class="confirm-action" style="display:none;" id="unmerge-confirm">
Are you sure you want to unmerge and reopen this ticket?
</p>
Finally, in staff/js/Tickets.js, change:
//ticket actions confirmation - Delete + more
$('a#ticket-delete, a#ticket-claim, #action-dropdown-more li a').click(function(e) {
to
//ticket actions confirmation - Delete + more
$('a#ticket-delete, a#ticket-claim, a#ticket-unmerge, #action-dropdown-more li a').click(function(e) {
You're done, it took a little time, but it was worth it! :)
I have the newest version, v1.8.0.2, installed on our site.
Does this work with the newest version?
Is there an easy to upload mod or fresh instructions for this version?
Thanks in advance!
:)
I am planning to move to 1.8, when I will migrate I will port this mod, however, if someone else is willing to help me is welcome :)