# --
# Kernel/System/Stats/State_Changes_Specific.pm - stats module
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (GPL). If you
# did not receive this file, see http://www.gnu.org/licenses/gpl.txt.
# --

##_
package Kernel::System::Stats::State_Changes_Specific;

use strict;
use Date::Pcalc qw(Days_in_Month);
use Kernel::System::CustomerUser;

use vars qw($VERSION);
$VERSION = '$Revision: 1.3 $ ';
$VERSION =~ s/^\$.*:\W(.*)\W.+?$/$1/;

# --
sub new {
    my $Type = shift;
    my %Param = @_;

    # allocate new hash for object
    my $Self = {};
    bless ($Self, $Type);

    # get common opjects
    foreach (keys %Param) {
        $Self->{$_} = $Param{$_};
    }

    # check all needed objects
    foreach (qw(DBObject ConfigObject LogObject)) {
        die "Got no $_" if (!$Self->{$_});
    }

    return $Self;
}
# --
sub Param {
    my $Self = shift;
    my @Params = ();
    # get current time
    my ($s,$m,$h, $D,$M,$Y) = $Self->{TimeObject}->SystemTime2Date(
        SystemTime => $Self->{TimeObject}->SystemTime(),
    );
    # get one month bevore
    if ($M == 1) {
        $M = 12;
        $Y = $Y - 1;
    }
    else {
        $M = $M -1;
    }
    # create possible time selections
    my %Year = ();
    foreach ($Y-10..$Y+1) {
        $Year{$_} = $_;
    }
    my %Month = ();
    foreach (1..12) {
        my $Tmp = sprintf("%02d", $_);
        $Month{$_} = $Tmp;
    }

    # get hash of all possible states and rewrite hash
    my %States = $Self->_GetStateTypes();
    foreach my $State (keys %States) {
        $States{$State} = $State;
    }
    push (@Params, {
            Frontend => 'State',
            Name => 'State',
            Multiple => 0,
            Size => 0,
            Data => {
                %States,
            },
        },
    );

    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;
}
# --
sub Run {
    my $Self = shift;
    my %Param = @_;
    $Param{Month} = sprintf("%02d", $Param{Month});
    my $Title = "$Param{Year}-$Param{Month} $Param{State}";
    my @HeadData = ('next state', 'Ticket#', 'Customer#', '24hdiff', 'Servicediff');
    my @Data = ();
    # get accounted time
    my @Tickets = ();

    my %StatesNumber = $Self->_GetStateTypes();
    my %States24h = %StatesNumber;
    my %StatesService = %StatesNumber;

    my $SQL = <<ENDE;
SELECT
 SUBSTRING_INDEX(TRIM(BOTH '%%' FROM paare.name), '%%', -1) AS Folgestatus,
 (SELECT tn FROM ticket WHERE id = th.ticket_id) AS Ticket,
 (SELECT customer_id FROM ticket WHERE id = th.ticket_id) AS CustomerID,
 th.create_time AS Von, paare.time2 AS Bis,
 (UNIX_TIMESTAMP(paare.time2)-UNIX_TIMESTAMP(th.create_time))/3600 AS Stunden
FROM
( /* History-Paare der aufeinanderfolgenden Statusaenderungen */
 SELECT
 th2.name,
 (SELECT MAX(id) FROM ticket_history WHERE ticket_id = th2.ticket_id AND history_type_id IN (1, 27) AND id < th2.id) AS id1,
 th2.id AS id2,
 th2.create_time AS time2
FROM ticket_history th2
WHERE th2.history_type_id = 27 /*  Tickets oder State-Updates */
 AND YEAR(th2.create_time) = $Param{Year}
 AND MONTH(th2.create_time) = $Param{Month}
 AND SUBSTRING_INDEX(TRIM(BOTH '%%' FROM th2.name), '%%', 1) = '$Param{'State'}'
) AS paare
INNER JOIN ticket_history th
ON paare.id1 = th.id
ORDER BY Stunden;
ENDE

    $Self->{DBObject}->Prepare(SQL => $SQL);
    while (my @Row = $Self->{DBObject}->FetchrowArray()) {
        # Zeitspanne innerhalb der Service-Zeit berechnen
        my $diffservice = sprintf('%0.2f', $Self->{TimeObject}->WorkingTime(
            StartTime => $Self->{TimeObject}->TimeStamp2SystemTime(String => $Row[3]),
            StopTime => $Self->{TimeObject}->TimeStamp2SystemTime(String => $Row[4]),
        ) / 3600);

        my $diff24h = sprintf('%0.2f', $Row[5]);
        push (@Data, [$Row[0], $Row[1], $Row[2], $diff24h, $diffservice]);
    }

    return ([$Title],[@HeadData], @Data);
}
# --
sub _GetStateTypes {
    my $Self = shift;
    my %Param = @_;
    my %Stats;
    my $SQL = "SELECT id, name FROM ticket_state";
    $Self->{DBObject}->Prepare(SQL => $SQL);
    while (my @RowTmp = $Self->{DBObject}->FetchrowArray()) {
        $Stats{$RowTmp[1]} = 0;
    }
    return %Stats;
}
1;