Re: [otrs] report of time per agent // outside reporting

14 Aug 2008

      Creation of specific reports is relatively easy if you decide to use
OTRS framework for report generation.

For each specific report you need a module placed in
$OTRS_HOME/Kernel/System/Stats/Static

The module should have the following two methods:

Param method should get the time frame for the report and to return it
in an array. It should look like:
sub Param {
    my $Self = shift;
    my ($Select, %Agents, @row);

    # get current time
    my ($s, $m, $h, $D, $M, $Y);
    my ($Ymin, $Ymax, $Select, %Agents, @row);

    ($s, $m, $h, $D, $M, $Y) = $Self->{TimeObject}->SystemTime2Date(
        SystemTime => $Self->{TimeObject}->SystemTime(),
    );

    # get one month before
    if ($M == 1) {
        $M = 12;
        $Y = $Y - 1;
    }
    else {
        $M = $M -1;
    }

    # create possible time selections
    $Select = qq(SELECT  EXTRACT(YEAR FROM (MIN(change_time))),
                         EXTRACT(YEAR FROM (MAX(change_time)))
                 FROM    otrs.time_accounting
                );
    $Self->{DBObject}->Prepare( SQL => $Select);
    ($Ymin, $Ymax) = $Self->{DBObject}->FetchrowArray();

    my %Year = ();
    foreach ($Ymin..$Ymax) {
        $Year{$_} = $_;
    }
    my %Month = ();
    foreach (1..12) {
        $Month{$_} = sprintf("%02d", $_);
    }

    push (@$Params, {
            Frontend => 'Year',
            Name => 'Year',
            Multiple => 0,
            Size => 0,
            SelectedID => $Y,
            Data => {
                %Year,
            },
        },
    );
    push (@$Params, {
            Frontend => 'Month',
            Name => 'Month',
            Multiple => 0,
            Size => 0,
            SelectedID => $M,
            Data => {
                %Month,
            },
        },
    );

    return @Params;
}

Run method does the actual computation. It has to return "return
([$Title],[@HeadData], @Data)"

$Title holds the report title, @HeadData holds column headers and @Data
is an array of @Row, where each @Row is an array of row values. You can
get data from user input via %Param e.g.

sub Run {
    my ( $Self, %Param ) = @_;

    my @HeadData = ('Created', 'First name', 'Last name', 'Time units');

    #get the time period
    my $Year = $Param{Year};
    my $Month = $Param{Month};

    my ($DBObject, $Select );
    my (@Data, $Title);

    $DBObject = $Self->{DBObject};

    $Select = qq(SELECT
                           t.create_by,
                           u.first_name,
                           u.last_name,
                           Sum(t.time_unit)
                           FROM
                           time_accounting AS t
                           Left Join system_user AS u ON t.create_by =
u.id WHERE
                           month(t.create_time) = $Year AND
                           year(t.create_time) =  $Month
                           GROUP BY t.create_by
                        );
    $DBObject->Prepare( SQL => $Select);

    #Read SQL result and put it in @Data

    return ([$Title],[@HeadData], @Data);
}

These are just code fragments, not a real module, so you may need to fix
some little things. I didn't sent the modules we use because our OTRS
installation is integrated with custom CRM and the tables we have are
different.

After module creation you have to go Stats->New, to choose Static-File
and to select the module from select box.

I hope it will help.

Best,
Atanas Karashenski
BlueBoard LLC