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/

Reply via email to