TicketSmallView sorted by time till escalation / pendingtime

[tdeklein]

Hi,

I wanted to see the time left until a ticket escalates/reached the end of its pending state in the TicketSmallView, so Agents would now how much time is left untill they have to tend to a ticket.
So far I have added a new theme an changed AgentTicketOverviewSmall.dtl there. Instead of the column "Age" (which does not interest me at all) I now have a column "Escalation in". I copied TicketOverviewSmall.pm to ~otrs/Custom/Kernel/Output/HTML and modiefied it so that in my "Escalation in" column the time left until escalation/end of pending in hours,mins and the date and time at which this is reached is show.

### MOD START
### Always show EscalationTimeSpalte, not only in the Eskalationsview. Dont show Age
# Original
#my @Col = (qw(TicketNumber Age State Lock Queue Owner CustomerID));
# No Age, show EscalationTime
my @Col = (qw(TicketNumber EscalationTime State Lock Queue Owner CustomerID));

# Since EscalationTime is already in there, we dont need to put in in here
# show escalation
#if ( $Param{Escalation} ) {
# push @Col, 'EscalationTime';
#}

### MOD STOP
...
### MOD START
### Original
# # escalation human times
# if ( $Article{EscalationTime} ) {
# $Article{EscalationTimeHuman} = $Self->{LayoutObject}->CustomerAgeInHours(
# Age => $Article{EscalationTime},
# Space => ' ',
# );
# $Article{EscalationTimeWorkingTime} = $Self->{LayoutObject}->CustomerAgeInHours(
# Age => $Article{EscalationTimeWorkingTime},
# Space => ' ',
# );
# }
# escalation human times
#if ( $Article{EscalationTime} ) {
$Article{EscalationTimeHuman} = $Self->{LayoutObject}->CustomerAgeInHours(
Age => $Article{EscalationTime},
Space => ' ',
);
$Article{EscalationTimeWorkingTime} = $Self->{LayoutObject}->CustomerAgeInHours(
Age => $Article{EscalationTimeWorkingTime},
Space => ' ',
);
#}
# Falls Eskalationszeit = 0 (weil z. B. Ticket pending), dann pending Zeit anzeigen
if ($Article{EscalationTimeHuman} == 0) {
$Article{EscalationTimeHuman} = $Self->{LayoutObject}->CustomerAgeInHours(
Age => $Article{UntilTime},
Space => ' ',
);
# If the rest of the pending time is not negative (still pending), don't show
# EscalationTime in red in condition check later.
# > 0 = Rot wenn Ablauf
# > 3600 = Rot eine Stunde vor Ablauf
if ($Article{UntilTime} > 3600) {
$Article{EscalationTime} = 60 * 60 * 1;
}

# Time till end of pending
my $DestDate = $Self->{TimeObject}->SystemTime() + $Article{UntilTime};
my $TimeStamp = $Self->{TimeObject}->SystemTime2TimeStamp(SystemTime => $DestDate,);
# Seconds of worktime till escalation
my $WorkingTimeTillEscalation = $Self->{TimeObject}->WorkingTime(
StartTime => $Self->{TimeObject}->SystemTime(),
StopTime => $Self->{TimeObject}->SystemTime() + $Article{UntilTime},
);
$Article{EscalationTimeWorkingTime} = $WorkingTimeTillEscalation/60/60 ." h"; # (seconds of working/service time till escalation, e. g. "1800")
$Article{EscalationDestinationDate} = $TimeStamp;
$Article{UpdateTimeDestinationDate} = $TimeStamp;
}
### MOD STOP
...
### MOD START
### Original
### # show escalation
### if ( $Param{Escalation} ) {
### if ( $Article{EscalationTime} < 60 * 60 * 1 ) {
### $Article{EscalationClass} = 'Warning';
### }
### $Self->{LayoutObject}->Block(
### Name => 'RecordEscalationTime',
### Data => { %Article, %UserInfo },
### );
### }
# show escalation
#if ( $Param{Escalation} ) {
if ( $Article{EscalationTime} < 60 * 60 * 1 ) {
$Article{EscalationClass} = 'Warning';
}
$Self->{LayoutObject}->Block(
Name => 'RecordEscalationTime',
Data => { %Article, %UserInfo },
);
#}
### MOD STOP
...
### MOD START
### Original
### # show escalation
### if ( $Param{Escalation} ) {
### if ( $Article{EscalationTime} < 60 * 60 * 1 ) {
### $Article{EscalationClass} = 'Warning';
### }
### $Self->{LayoutObject}->Block(
### Name => 'RecordEscalationTime',
### Data => { %Article, %UserInfo },
### );
### }
# show escalation
#if ( $Param{Escalation} ) {
if ( $Article{EscalationTime} < 60 * 60 * 1 ) {
$Article{EscalationClass} = 'Warning';
}
$Self->{LayoutObject}->Block(
Name => 'RecordEscalationTime',
Data => { %Article, %UserInfo },
);
#}
### MOD STOP

So far so good. I get information I wanted. But I expected to get a sorted view, but instead I get an (AFAIS) arbitrary ordered view.

Does anyone have an idea why this is not sorted / how I can get a sorted view?

Cheers
Thomas

[KhaledBlah]

Thanks a lot for this!

[crythias]

How to sort things that may or may not have an escalation (but really, you want the escalations to sort first:future, now, old, none)

Modify Kernel/System/Ticket.pm (better to copy Ticket.pm to /opt/otrs/Custom/Kernel/System/Ticket.pm and modify it there.)

in the my %SortOptions (around line 3826), you'll see all the fields that are used to sort.
You have two options: Modify an existing field or add a fake field. Your choice, but if you add a fake field, you'll need to add that fake field (sort option) to the .xml file and select it from SysConfig.

The magic:

TicketEscalation => 'st.escalation_time',
becomes
TicketEscalation => 'CASE WHEN st.escalation_time is null THEN 1 ELSE 0 END, st.escalation_time',

Then (I think) Order by Desc. You may need to play with the 1 and 0 and asc/desc to make sense for your sort.

[tpsupport]

Hi tdeklein,

How did you change the AgentTicketOverviewSmall.dtl so that the View is displayed correctly? I changed the TicketOverviewSmall.pm as per your description and this is what I get.

queue-view.PNG

It seems that beginning from the row "Escalation in" everything is moved to the right. As it seems that Age is still displayed i tried to remove it but with no success. Do you you have an idea?

BTW: Thanks a lot for your post!


Regards, Rick