#!/usr/bin/perl

use DBI;

($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();

$jahr=$year;
$monat=$mon+1;
$tag=$mday;
$jahr=$year +1900;

if($tag >= 25) {
        if($monat == 1) { $jahr--; $monat = 12; }
        else { $monat--; }
}

if(length($monat) == 1) { $monat="0$monat"; }
if(length($tag) == 1) { $tag="0$tag"; }

$von = $jahr."-".$monat."-26 00:00:00";


#Verbindung herstellen
my $dbh = DBI->connect("DBI:mysql:callcenter",web5,al56sk);

my $help2 = "SELECT MA_ID,
                (SELECT COUNT(*) FROM produktion WHERE (VK_DATUM BETWEEN '$von' AND CURRENT_DATE)
                        AND QCSTATUS='OK' AND MA_ID = mitarbeiter.MA_ID AND VK_DATUM!=CURRENT_DATE) /
                (SELECT (SUM(TIME_TO_SEC(TIME(SUBTIME(SUBTIME(ENDE,BEGINN), PAUSE))))/3600)
                  FROM login WHERE MA_ID = mitarbeiter.MA_ID AND DATUM BETWEEN '$von' AND SUBDATE(CURRENT_DATE,1)) AS SCHNITT
                FROM mitarbeiter ORDER BY SCHNITT DESC LIMIT 0,3";

$sql = qq { $help2 };
my $sth = $dbh->prepare($sql);

$sth->execute();

my $i = 0;

while(@r=$sth->fetchrow_array) {
                $i++;
                print "\n".$r[1];
                $dbh->do( qq { UPDATE highscore SET SCHNITT = '$r[1]', MA_ID = '$r[0]', DATE = NOW() WHERE ID = $i} );
}


$sth->finish();
$dbh->disconnect();

print "\n ------------ Highscore upgedated... --------------\n";