How to stop ticket age otrs 3.04

[cbravo]

First off I wanna say that I am modifying files that might get overriden during an update so do it at your own risk. I created a configuration management file with backups of every file I modded so I can apply it again if it gets changed by an update.

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} ],

modify that for this.

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} ],
    	);		
    }

what that is doing is everytime the ticket gets updated it asks: is it getting closed succesful or closed unsuccsesful? if so, save the time to the new columns you just added.

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,
    );

and change it to 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.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,
    );

Please note that I changed the freetext1 and freetext2 for close_time and close_time_unix. This because when I was adding the parameters I kept giving me an access denied when my customers tried to read their tickets. If you are not using them just change them with no fear. Scroll down a bit now, and you will find the following lines:

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];

right after these line:

$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};
		}

scroll down some more and comment out this line (to comment out something just add # at the beginning of the line.

# $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 ';

and change it 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.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 ';

please note that I again changed the st.freetext1 for st.close_time_unix.

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};
		}

Save your file and you should be done with it. Now everytime your client looks at a closed ticket it will stay still. Please do note that if you have old tickets closed before this, you will see negative time because the closed time for those tickets will be 0. Also, this was a solution someone posted for an older version (probably 2.xx) and I just ported it to the newest version. If someone has a better way to do this so it won't get overriden for every update please do tell so. Thank you.

[Mothra]

I couldn't work out from the explanation above: what use case or business requirement would you apply this solution to?

[cbravo]

I couldn't work out from the explanation above: what use case or business requirement would you apply this solution to?

What do you mean? I don't understand your question. Suppose the following scenario. Your client open a ticket at 10 am and you solve it at 11 am the same day. When he logs in 4 hours later to see the response, instead of seeing the closed ticket with 60 minutes, he sees it with 240 minutes (that's because the time keeps getting calculated from the open time until the current time). After he logs in again 2 hours later, he now sees it with 360 minutes. What that solution does is freezes the time so no matter when the client logs in using the customer interface he will see 60 minutes.

[Mothra]

I couldn't work out from the explanation above: what use case or business requirement would you apply this solution to?

What do you mean? I don't understand your question. Suppose the following scenario. Your client open a ticket at 10 am and you solve it at 11 am the same day. When he logs in 4 hours later to see the response, instead of seeing the closed ticket with 60 minutes, he sees it with 240 minutes (that's because the time keeps getting calculated from the open time until the current time). After he logs in again 2 hours later, he now sees it with 360 minutes.

I can't really see a problem with this - it allows the client to see his tickets listed in chronological order... in other words, the "Age" of his tickets.

What that solution does is freezes the time so no matter when the client logs in using the customer interface he will see 60 minutes.

It doesn't freeze time - it just records a new "close time" value whenever the ticket is closed. What this new time represents is simply the difference between the initial create time and the last close action performed on the ticket. I would be worried that clients may treat this value as how long the ticket took to solve, which would be a mistake.

[cbravo]

from my point of view, it is how long it took to solve the ticket, at least from my company point of view. If I pay you to fix all my problems within two hours, you can't say well it took me two hours to solve it but I have your answer 4 days later since I only work 30 minutes each day. We offer 24/7 support so we can't do that.

[Mothra]

from my point of view, it is how long it took to solve the ticket, at least from my company point of view. If I pay you to fix all my problems within two hours, you can't say well it took me two hours to solve it but I have your answer 4 days later since I only work 30 minutes each day. We offer 24/7 support so we can't do that.

I was thinking of the scenario in which a ticket is closed within 60 mins, but then re-opened 7 days later (perhaps the client has been away from work). If you then re-close it within 30 mins, the time shown on the ticket will not be 1h 30m, it will be 7d 1h 30m. But perhaps your ticket handling process doesn't allow tickets to be re-opened?

[cbravo]

from my point of view, it is how long it took to solve the ticket, at least from my company point of view. If I pay you to fix all my problems within two hours, you can't say well it took me two hours to solve it but I have your answer 4 days later since I only work 30 minutes each day. We offer 24/7 support so we can't do that.

I was thinking of the scenario in which a ticket is closed within 60 mins, but then re-opened 7 days later (perhaps the client has been away from work). If you then re-close it within 30 mins, the time shown on the ticket will not be 1h 30m, it will be 7d 1h 30m. But perhaps your ticket handling process doesn't allow tickets to be re-opened?

exactly, this solution only works if you don't allow customers to reopen their tickets.

[ircmatrix]

Thanks a lot. Good tutorials.
But in ticket zoom the age still running. can u teach how to stop it.

[cbravo]

haven't really looked at it before. I might make some time to do it.