[otrs] How to stop ticket AGE when ticket is closed

3 Feb 2006

      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] How to stop ticket AGE when ticket is closed

Simone Girlanda