First off, add two columns to your ticket table.
close_time_unix bigint(20) NOT NULL default 0
close_time datetime NOT NULL default '0000-00-00 00:00:00'
Next, we are gonna modify the files /{OTRSHOME}/Kernel/System/Ticket.pm
at line 5539 you should see something like this:
Code: Select all
# db update
return if !$Self->{DBObject}->Do(
SQL => 'UPDATE ticket SET ticket_state_id = ?, '
. ' change_time = current_timestamp, change_by = ? WHERE id = ?',
Bind => [ \$State{ID}, \$Param{UserID}, \$Param{TicketID} ],
Code: Select all
# db update
if (($State{ID} == 2) | ($State{ID} == 3))
{
return if !$Self->{DBObject}->Do(
SQL => 'UPDATE ticket SET ticket_state_id = ?, '
. ' change_time = current_timestamp, change_by = ?, close_time_unix = ?, close_time = current_timestamp WHERE id = ?',
Bind => [ \$State{ID}, \$Param{UserID}, \$Self->{TimeObject}->SystemTime(), \$Param{TicketID} ],
);
}
else
{
return if !$Self->{DBObject}->Do(
SQL => 'UPDATE ticket SET ticket_state_id = ?, '
. ' change_time = current_timestamp, change_by = ? WHERE id = ?',
Bind => [ \$State{ID}, \$Param{UserID}, \$Param{TicketID} ],
);
}
Next look for this:
Code: Select all
# fetch the result
return if !$Self->{DBObject}->Prepare(
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.customer_user_id, st.user_id, st.responsible_user_id, '
. ' st.until_time,'
. ' 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.freekey9, st.freetext9, st.freekey10, st.freetext10,'
. ' st.freekey11, st.freetext11, st.freekey12, st.freetext12,'
. ' st.freekey13, st.freetext13, st.freekey14, st.freetext14,'
. ' st.freekey15, st.freetext15, st.freekey16, st.freetext16,'
. ' st.freetime1, st.freetime2, st.freetime3, st.freetime4,'
. ' st.freetime5, st.freetime6,'
. ' st.change_time, st.title, st.escalation_update_time, st.timeout,'
. ' st.type_id, st.service_id, st.sla_id, st.escalation_response_time,'
. ' st.escalation_solution_time, st.escalation_time, st.archive_flag'
. ' FROM ticket st, ticket_priority sp, queue sq'
. ' WHERE sp.id = st.ticket_priority_id AND sq.id = st.queue_id AND st.id = ?',
Bind => [ \$Param{TicketID} ],
Limit => 1,
);
Code: Select all
# fetch the result
return if !$Self->{DBObject}->Prepare(
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.customer_user_id, st.user_id, st.responsible_user_id, '
. ' st.until_time,'
. ' st.freekey1, st.close_time, st.freekey2, st.close_time_unix,'
. ' 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.freekey9, st.freetext9, st.freekey10, st.freetext10,'
. ' st.freekey11, st.freetext11, st.freekey12, st.freetext12,'
. ' st.freekey13, st.freetext13, st.freekey14, st.freetext14,'
. ' st.freekey15, st.freetext15, st.freekey16, st.freetext16,'
. ' st.freetime1, st.freetime2, st.freetime3, st.freetime4,'
. ' st.freetime5, st.freetime6,'
. ' st.change_time, st.title, st.escalation_update_time, st.timeout,'
. ' st.type_id, st.service_id, st.sla_id, st.escalation_response_time,'
. ' st.escalation_solution_time, st.escalation_time, st.archive_flag'
. ' FROM ticket st, ticket_priority sp, queue sq'
. ' WHERE sp.id = st.ticket_priority_id AND sq.id = st.queue_id AND st.id = ?',
Bind => [ \$Param{TicketID} ],
Limit => 1,
);
Code: Select all
#$Ticket{TicketFreeText1} = defined $Row[17] ? $Row[17] : '';
#$Ticket{TicketFreeText2} = defined $Row[19] ? $Row[19] : '';
and change those lines for this:
Code: Select all
$Ticket{close_time_unix} = $Row[17];
$Ticket{close_time} = $Row[19];
$Ticket{TicketFreeTime6} = defined $Row[53] ? $Row[53] : '';
add the following:
Code: Select all
if (($Ticket{StateID} == 2) | ($Ticket{StateID} == 3))
{
$Ticket{Age} = $Ticket{close_time_unix} - $Ticket{CreateTimeUnix};
}
else
{
$Ticket{Age} = $Self->{TimeObject}->SystemTime() - $Ticket{CreateTimeUnix};
}
# $Ticket{Age} = $Self->{TimeObject}->SystemTime() - $Ticket{CreateTimeUnix};
Save that file and close. We should be done working with it.
Now open /{OTRSHOME}/Kernel/System/Ticket/Article.pm
in that file look for this:
Code: Select all
# sql query
my @Content;
my @Bind;
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_in_reply_to, sa.a_references, 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, '
. ' st.responsible_user_id, 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.freekey9, st.freetext9, st.freekey10, st.freetext10, '
. ' st.freekey11, st.freetext11, st.freekey12, st.freetext12, '
. ' st.freekey13, st.freetext13, st.freekey14, st.freetext14, '
. ' st.freekey15, st.freetext15, st.freekey16, st.freetext16, '
. ' st.ticket_lock_id, st.title, st.escalation_update_time, '
. ' st.freetime1 , st.freetime2, st.freetime3, st.freetime4, st.freetime5, st.freetime6, '
. ' st.type_id, st.service_id, st.sla_id, st.escalation_response_time, '
. ' st.escalation_solution_time, st.escalation_time, st.change_time '
. ' FROM article sa, ticket st WHERE ';
Code: Select all
# sql query
my @Content;
my @Bind;
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_in_reply_to, sa.a_references, 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, '
. ' st.responsible_user_id, 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.close_time_unix, 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.freekey9, st.freetext9, st.freekey10, st.freetext10, '
. ' st.freekey11, st.freetext11, st.freekey12, st.freetext12, '
. ' st.freekey13, st.freetext13, st.freekey14, st.freetext14, '
. ' st.freekey15, st.freetext15, st.freekey16, st.freetext16, '
. ' st.ticket_lock_id, st.title, st.escalation_update_time, '
. ' st.freetime1 , st.freetime2, st.freetime3, st.freetime4, st.freetime5, st.freetime6, '
. ' st.type_id, st.service_id, st.sla_id, st.escalation_response_time, '
. ' st.escalation_solution_time, st.escalation_time, st.change_time '
. ' FROM article sa, ticket st WHERE ';
Scroll down and find this line and comment it
# $Data{TicketFreeText1} = $Row[35];
and add this right below that
$Data{close_time_unix} = $Row[35];
$Ticket{close_time_unix} = $Row[35];
find this line and comment it too
# fill up dynamic varaibles
# $Data{Age} = $Self->{TimeObject}->SystemTime() - $Ticket{CreateTimeUnix};
and below that add this:
Code: Select all
if( ($Ticket{StateID} == 2) | ($Ticket{StateID} == 3))
{
$Data{Age} = $Ticket{close_time_unix} - $Ticket{CreateTimeUnix};
}
else
{
$Data{Age} = $Self->{TimeObject}->SystemTime() - $Ticket{CreateTimeUnix};
}