Improving the 7 day stats graph of the dashboard

[Pirx Danford]

Hi,

in our company we were very unhappy about the way the "Closed" Tickets are counted by default for the 7 day stats.

I solved the problem, but probably not in an optimal manner.
I'll post the code I implemented here and hope someone might suggest improvements.

So I modified Kernel/System/Ticket.pm (in version 1.416.2.10) from line 4756 on (it says # database query there).
I put everything from there in an else branch until the commented lines with "#=item _TicketSearchSqlAndStringCreate()" and put this if branch above it:

Code: Select all

    if ( $Result eq 'COUNT' && ( $Param{TicketCloseTimeNewerDate} || $Param{TicketCloseTimeOlderDate} ) )
    {
        my $NOTHING;
        my $AltSQL;
        $AltSQL = $SQL;
        $SQL =~ s/SELECT DISTINCT count\(\*\)/SELECT */g;
        my %Tickets;
        my @TicketIDs;
        my $Count;
        return if !$Self->{DBObject}->Prepare( SQL => $SQL . $SQLExt . ' GROUP BY th.ticket_id', Limit => $Limit );
        while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
            $Count = $Count + 1;
        }
        $Tickets{ $Count } = $NOTHING;
        push @TicketIDs, $Count;
        # fill cache
        if ($CacheObject) {
            $CacheObject->Set(
                Type  => 'TicketSearch',
                Key   => $AltSQL . $SQLExt . $Result . $Limit,
                Value => $Count,
                TTL   => $Param{CacheTTL} || 60 * 4,
            );
        }
        return $Count;
    }

Now only one single close action on a ticket is counted each day.

What I would love to have is a third graph with reopened tickets and then the 7 day stats would be perfect!

Any feedback is welcome

[Pirx Danford]

Sadly we have quite a lot of errors like this in the log now

Code: Select all

Thu Oct 14 17:12:05 2010 	error 	OTRS-CGI 	Need Data!
Thu Oct 14 17:12:05 2010 	error 	OTRS-CGI 	Need Data!
Thu Oct 14 17:12:05 2010 	error 	OTRS-CGI 	Need Value!
Thu Oct 14 17:12:05 2010 	error 	OTRS-CGI 	Need Value!

I fear the changes explained in my posting before are to blame, any hint how to fix this is appreciated!

[Pirx Danford]

Well I toyed a bit around and this code seems to be working much better without throwing log errors:

Code: Select all

    if ( $Result eq 'COUNT' && ( $Param{TicketCloseTimeNewerDate} || $Param{TicketCloseTimeOlderDate} ) )
    {
        my $NOTHING = '';
        my $AltSQL = $SQL;
        $SQL =~ s/SELECT DISTINCT count\(\*\)/SELECT */g;
        my %Tickets;
        my @TicketIDs;
        my $Count = 0;
        return if !$Self->{DBObject}->Prepare( SQL => $SQL . $SQLExt . ' GROUP BY th.ticket_id', Limit => $Limit );
        while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
            $Count = $Count + 1;
        }
        $Tickets{ $Count } = $NOTHING;
        push @TicketIDs, $Count;
        # fill cache
        if ($CacheObject) {
            $CacheObject->Set(
                Type  => 'TicketSearch',
                Key   => $AltSQL . $SQLExt . $Result . $Limit,
                Value => $Count,
                TTL   => $Param{CacheTTL} || 60 * 4,
            );
        }
        return $Count;
    }

Our agents feel the graph information is much more useful this way.
Now if someone could find a way to introduce 2 new graph lines I would be very happy!

We are looking for a graph that displays the reopened tickets and a graph that shows how many tickets were closed more than once.