Re: [dev] Stoping of Ticket age when status is closed

11 Jan 2006

      Hi :-)

Yes, I can.
But I have to say that it is not proffesionaly written, because we 
needed it under extreme time-pressure.
But it work fine for us.

At the moment i display the Ticket Runtime on the 
"AgentTicketZoom"-Frontend.

In "AgentTicketZoom.pm" I've added the following Code:

#################################################
#################################################
# runtime design - here we get the Ticket Runtime form "create_time" zu 
the last "TicketClose".
#################################################
#################################################          
        $Param{Runtime} = "<font color='red'>Error, something is 
wrong!?</font>";

# SQL-Query
# define actual Ticket-ID
          my $actticketid = $Self->{TicketID};
# END define act ticketid

# Get Time during create and close
             my $SQL = "SELECT timediff(ticket_history.create_time, 
ticket.create_time) AS 'diff' from ticket LEFT JOIN ticket_history ON 
ticket.id=ticket_history.ticket_id WHERE ticket.id = $actticketid AND 
ticket_history.history_type_id = 15 AND ticket_history.name = '%%close' 
AND ticket_history.ticket_id = $actticketid ORDER BY 
ticket_history.create_time DESC LIMIT 1";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @Row = $Self->{DBObject}->FetchrowArray();
             my $Runtime = $Row[0];
# END Get Time during create and close

# When there is no result  (Ticket isn't close now) get the time during 
create an NOW()
          if ($Runtime == "") {
          my $SQL = "SELECT timediff(NOW(), ticket.create_time) AS 
'diff' from ticket WHERE ticket.id = $actticketid";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @Row = $Self->{DBObject}->FetchrowArray();
          $Runtime = $Row[0];
          }
# END when there is no result

# define Param for Frontend-DTL
        $Param{Runtime} = $Runtime;

# END of Runtime Design

# Runtime 2 (Runtime accept pending-states and only duriing working-times)

# Create Object
  use Kernel::Config;
  use Kernel::System::Time;

  my $ConfigObject = Kernel::Config->new();

  my $TimeObject = Kernel::System::Time->new(
      ConfigObject => $ConfigObject,
  );

# SQL-Query for Create-Time and Close-Time
          my $SQL = "SELECT create_time FROM ticket WHERE id = 
$actticketid ORDER BY create_time DESC LIMIT 1";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
           my @Row = $Self->{DBObject}->FetchrowArray();
                my $TicketStartTime = $Row[0];

          my $SQL = "SELECT create_time FROM ticket_history WHERE 
history_type_id = 15 AND name = '%%close' AND ticket_id = $actticketid 
ORDER BY create_time DESC LIMIT 1";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @Row = $Self->{DBObject}->FetchrowArray();
                my $TicketEndTime = $Row[0];
          my $TicketEndFront = $TicketEndTime;
$Param{TicketEndFront} = $TicketEndFront;

          if ($TicketEndTime == "") {
          my $SQL = "SELECT NOW() AS 'now'";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @Row = $Self->{DBObject}->FetchrowArray();
          $TicketEndTime = $Row[0];
# Define frontend-param
          my $TicketEndFront = '<strong><font color="red">-</font></stong>';

$Param{TicketEndFront} = $TicketEndFront;
          }

# convert SQL-Results DateTime to Systemtime
my $UnixStartTime = $TimeObject->TimeStamp2SystemTime(

     String => $TicketStartTime,

    );

my $UnixEndTime = $TimeObject->TimeStamp2SystemTime(
        String => $TicketEndTime,
    );

# Giving the Systemtimes to the object WorkingTime (get the real time 
during the working-ours)
    my $WorkingTime = $TimeObject->WorkingTime(
        StartTime => $UnixStartTime,
        StopTime => $UnixEndTime,
    );

# PENDING-STATES

my $SQL = "SELECT id FROM ticket_state WHERE type_id = '4' OR type_id = 
'5' ORDER BY id";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @pending_states = $Self->{DBObject}->FetchrowArray();
          my $z=0;
          my @Test;
          while (@pending_states)
          {
            $Test[$z] = $pending_states[0];
            $z=$z+1;
            @pending_states = $Self->{DBObject}->FetchrowArray();
          }

$SQL = "SELECT state_id, create_time FROM ticket_history WHERE 
ticket_id='$actticketid' ORDER BY id";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @ticket_history = $Self->{DBObject}->FetchrowArray();

# Here the While thorough the Ticket History
# $now = 0 --> prev entry has state=open
# $now = 1 --> we are now in a pending-state
# @PendingTime --> every element of this array contents the seconds of 
one pending-state
# $pending_zaehler gives you the number of elements in @PendingTime

          my $now=0;
          my $UnixPendingStart=0;
          my @PendingTime={0};
          my $pending_zaehler=0;
          while (@ticket_history)
          {
            if ($now==0)
            {
                for (my $i=0; $i<$z; $i++)           
                {
                    if ($ticket_history[0]==$Test[$i])
                    {

                        $UnixPendingStart = 
$TimeObject->TimeStamp2SystemTime(
                         String => $ticket_history[1],
    );                       
                        $now=1;
                        $i=9999;
                    }
                }
            }
            if ($now==1)
            {
                    my $pend=0;
                    for (my $i=0; $i<$z; $i++)           
                    {
                        if ($ticket_history[0]==$Test[$i])
                        {    $pend=1;        }
                    }
                    if ($pend==0)
                    {
                        my $UnixPendingEnd = 
$TimeObject->TimeStamp2SystemTime(
                             String => $ticket_history[1],
      );
                           $PendingTime[$pending_zaehler] = 
$TimeObject->WorkingTime(
                               StartTime => $UnixPendingStart,
                                  StopTime => $UnixPendingEnd,
    );   

                        $pending_zaehler++;
                        $now=0;
                    }
            }
          @ticket_history = $Self->{DBObject}->FetchrowArray();
          }

# if the actual state ist "pending" we use the NOW()-Time

          if ($now==1)
          {
            my $SQL = "SELECT NOW() AS 'now'";
              $Self->{DBObject}->Prepare(SQL => $SQL, Limit => 
$Self->{Limit});
                  my @Row = $Self->{DBObject}->FetchrowArray();
              my $end_time_now = $Row[0];

            my $UnixPendingEnd = $TimeObject->TimeStamp2SystemTime(
                 String => $end_time_now, );

            $PendingTime[$pending_zaehler] = $TimeObject->WorkingTime(
                      StartTime => $UnixPendingStart,
                StopTime => $UnixPendingEnd, );
            $pending_zaehler++;
          }

# Now calculate the whole thing

          my $all_pendings=0;
          for (my $i=0; $i<$pending_zaehler; $i++)
          {    $all_pendings=$all_pendings+$PendingTime[$i];    }

# Now we convert the seconds that we have via an SQL-Statement in a 
Date-Format (sec_to_time)

        $WorkingTime=$WorkingTime-$all_pendings;

          my $SQL = "SELECT sec_to_time($WorkingTime)";
          $Self->{DBObject}->Prepare(SQL => $SQL, Limit => $Self->{Limit});
          my @Row = $Self->{DBObject}->FetchrowArray();
                $WorkingTime = $Row[0];

# Define the param for the frontend
$Param{WorkingTimeRuntime} = $WorkingTime;

################################
################################
# END of Runtime 2
################################
################################

In "AgentTicketZoom.dtl" you have to add the Frontend-Param (that you've 
defined in the .pm:) on the place where it should be displayd on the 
web-interface

##############################
##############################
$Env{"Box0"}$Text{"Runtime"}: $Data{"WorkingTimeRuntime"}$Env{"Box1"}<br />
##############################
##############################

Hope, that will help you.

Regards,
Felix
...
Can you share that script?  I have seen many request for that type of
function.
Gary Davis
PSIC Lab Manager
-----Original Message-----
From: dev-bounces@otrs.org [mailto:dev-bounces@otrs.org] On Behalf Of
Felix Lenz
Sent: Wednesday, January 11, 2006 7:24 AM
To: Development community of OTRS.org
Subject: Re: [dev] Stoping of Ticket age when status is closed
Hi Veena,
i've scripted something, to get a Ticket "Runtime".
It shows the ticket-age during the working-time (you can change them in
"SysConfig"), accept the "pending"-states. And this runtime stops, when
the ticket-state was set to a "close"-state.
But it only will work if your ticket doesn't reopen, when a a customer
sendy an follow-up, when the ticket have already been closed.
For a followup must be open a new ticket.
Is this something you would need?
Regards
Felix Lenz
...
Hi Veena,
vee na schrieb:
...
One of my clients wants me to stop the aging process of a ticket when 
it is closed. Can any body of you point me to the code where I can 
modify to get the requirement fulfilled.
Any inputs regarding the matter will be a welcome.
Per default the age of a ticket is now()-create_time().
Done in Kernel/System/Ticket.pm -> TicketGet() and 
Kernel/System/Ticket/Article.pm -> ArticleGet().
You need to look there to change it.
...
Regards:
Veena. A
Martin
((otrs)) :: OTRS GmbH :: Europaring 4 :: D - 94315 Straubing
Fon: +49 (0) 9421 1862 760 :: Fax: +49 (0) 9421 1862 769
  http://www.otrs.com/ :: Communication with success!
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev