I did it in the following way, I hope this can help other people.
I need to know the duration of a closed ticket because the quality policies
of my company evaluate it, but also my customers want to know it.
>From the customer point of view is not correct to have an endless time
counter.
1 - add fields to ticket table
`close_time_unix` bigint(20) NOT NULL default '0',
`close_time` datetime NOT NULL default '0000-00-00 00:00:00',
2 - change the stateset sub in ticket.pm
where the ticket state is updated I save also the close datetime
# db update
my $SQL = "UPDATE ticket SET ticket_state_id = $State{ID}, " .
" change_time = current_timestamp, change_by = $Param{UserID}
";
# new new new new new
if (($State{ID} eq 2) | ($State{ID} eq 3)) {
$SQL .= ", close_time = current_timestamp,
close_time_unix=".$Self->{TimeObject}->SystemTime();
}
# end end end
$SQL .= " WHERE id = $Param{TicketID} ";
if ($Self->{DBObject}->Do(SQL => $SQL)) {
3 - change the ticketget sub in ticket.pm
change the query to load also close_time and add a new way to calcolate
age for closed tickets
# db query
my $SQL = "SELECT st.id, st.queue_id, sq.name, st.ticket_state_id,
st.ticket_lock_id, ".
" sp.id, sp.name, st.create_time_unix, st.create_time, sq.group_id,
st.tn, ".
" st.customer_id, st.user_id,
su.$Self->{ConfigObject}->{DatabaseUserTableUserID}, ".
" su.$Self->{ConfigObject}->{DatabaseUserTableUser},
st.ticket_answered, st.until_time, ".
" st.customer_user_id, st.freekey1, st.freetext1, st.freekey2,
st.freetext2,".
" st.freekey3, st.freetext3, st.freekey4, st.freetext4,".
" st.freekey5, st.freetext5, st.freekey6, st.freetext6,".
" st.freekey7, st.freetext7, st.freekey8, st.freetext8, ".
" st.change_time, st.title, st.escalation_start_time, st.timeout, ".
# new new new new
" st.freetime1, st.freetime2, st.close_time, st.close_time_unix".
# end end end
" FROM ".
" ticket st, ticket_priority sp, ".
" queue sq, $Self->{ConfigObject}->{DatabaseUserTable} su ".
" WHERE ".
" sp.id = st.ticket_priority_id ".
" AND ".
" sq.id = st.queue_id ".
" AND ".
" st.user_id = su.$Self->{ConfigObject}->{DatabaseUserTableUserID}
".
" AND ".
" st.id = $Param{TicketID}";
$Self->{DBObject}->Prepare(SQL => $SQL);
while (my @Row = $Self->{DBObject}->FetchrowArray()) {
$Ticket{TicketID} = $Row[0];
$Ticket{Title} = $Row[35];
$Ticket{QueueID} = $Row[1];
$Ticket{Queue} = $Row[2];
$Ticket{StateID} = $Row[3];
$Ticket{LockID} = $Row[4];
$Ticket{PriorityID} = $Row[5];
$Ticket{Priority} = $Row[6];
# new new new new
if (($Ticket{StateID} == 2) | ($Ticket{StateID} == 3)) {
$Ticket{Age} = $Row[41] - $Row[7];
}
else {
$Ticket{Age} = $Self->{TimeObject}->SystemTime() -
$Row[7];
};
# end end end
# $Ticket{SLAAge} = $Self->{TimeObject}->SLATime(StartTime =>
$Row[7]);
$Ticket{CreateTimeUnix} = $Row[7];
$Ticket{Created} =
$Self->{TimeObject}->SystemTime2TimeStamp(SystemTime => $Row[7]);
$Ticket{Closed} = $Row[41];
$Ticket{Changed} = $Row[34];
4 - change the articleget sub in article.pm
same as above for article
# sql query
my @Content = ();
my $SQL = "SELECT sa.ticket_id, sa.a_from, sa.a_to, sa.a_cc,
sa.a_subject, ".
" sa.a_reply_to, sa. a_message_id, sa.a_body, ".
" st.create_time_unix, st.ticket_state_id, st.queue_id,
sa.create_time, ".
" sa.a_content_type, sa.create_by, st.tn, article_sender_type_id,
st.customer_id, ".
" st.until_time, st.ticket_priority_id, st.customer_user_id,
st.user_id, ".
" su.$Self->{ConfigObject}->{DatabaseUserTableUser},
sa.article_type_id, ".
" sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, ".
" sa.a_freekey3, sa.a_freetext3, st.ticket_answered, ".
" sa.incoming_time, sa.id, st.freekey1, st.freetext1, st.freekey2,
st.freetext2,".
" st.freekey3, st.freetext3, st.freekey4, st.freetext4,".
" st.freekey5, st.freetext5, st.freekey6, st.freetext6,".
" st.freekey7, st.freetext7, st.freekey8, st.freetext8, ".
" st.ticket_lock_id, st.title, st.escalation_start_time, ".
# new new new new
" st.freetime1 , st.freetime2, st.close_time_unix ".
# end end end
" FROM ".
" article sa, ticket st, ".
" $Self->{ConfigObject}->{DatabaseUserTable} su ".
" where ";
if ($Param{ArticleID}) {
$SQL .= " sa.id = $Param{ArticleID}";
}
else {
$SQL .= " sa.ticket_id = $Param{TicketID}";
}
$SQL .= " AND ".
" sa.ticket_id = st.id ";
# add article types
if ($ArticleTypeSQL) {
$SQL .= $ArticleTypeSQL;
}
$SQL .= " AND ".
" st.user_id = su.$Self->{ConfigObject}->{DatabaseUserTableUserID}
".
" ORDER BY sa.create_time, sa.id ASC";
$Self->{DBObject}->Prepare(SQL => $SQL);
my %Ticket = ();
while (my @Row = $Self->{DBObject}->FetchrowArray()) {
my %Data;
$Data{ArticleID} = $Row[31];
$Data{TicketID} = $Row[0];
$Ticket{TicketID} = $Data{TicketID};
$Data{Title} = $Row[49];
$Ticket{Title} = $Data{Title};
$Data{EscalationStartTime} = $Row[50];
$Ticket{EscalationStartTime} = $Data{EscalationStartTime};
$Data{From} = $Row[1];
$Data{To} = $Row[2];
$Data{Cc} = $Row[3];
$Data{Subject} = $Row[4];
$Data{ReplyTo} = $Row[5];
$Data{InReplyTo} = $Row[6];
$Data{Body} = $Row[7];
# new new new new
if (($Row[9] == 2) | ($Row[9] == 3)) {
$Data{Age} = $Row[53] - $Row[8];
}
else {
$Data{Age} = $Self->{TimeObject}->SystemTime() -
$Row[8];
}
# end end end
# $Data{Age} = $Self->{TimeObject}->SystemTime() - $Row[8];
$Ticket{CreateTimeUnix} = $Row[8];
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support oder Consulting für Ihr OTRS System?
=> http://www.otrs.de/