77 lines
2.2 KiB
Perl
77 lines
2.2 KiB
Perl
#!/usr/bin/perl -w
|
|
|
|
use strict;
|
|
use warnings;
|
|
use DBI;
|
|
use Data::Printer;
|
|
# use JSON;
|
|
use Getopt::Long;
|
|
use File::Slurp qw(:std);
|
|
use Term::ANSIColor;
|
|
use Time::localtime;
|
|
|
|
# TEST
|
|
# my $db = "prod"; # Datenbankparameter
|
|
# my $user = "root"; # Datenbankuser
|
|
# my $pass = "jagger"; # Datenbankpasswort
|
|
# my $server = "ares"; # Datenbankserver
|
|
|
|
# PRV LESEN
|
|
# my $db = "prod"; # Datenbankparameter
|
|
# my $user = "prv19"; # Datenbankuser
|
|
# my $pass = "!nd!4n3R"; # Datenbankpasswort
|
|
# my $server = "jupiter"; # Datenbankserver
|
|
|
|
# PRV SCHREIBEN
|
|
my $db = "prod"; # Datenbankparameter
|
|
my $user = "root"; # Datenbankuser
|
|
my $pass = "jagger"; # Datenbankpasswort
|
|
my $server = "zeus"; # Datenbankserver
|
|
|
|
STDOUT->autoflush(1);
|
|
my $t = ctime();
|
|
|
|
write_file('prv_anr_correction.log', "$t\n");
|
|
print "Start: $t\n";
|
|
|
|
my $dbh = DBI->connect("DBI:mysql:$db:$server", $user, $pass)
|
|
#my $dbh = DBI->connect('DBI:mysql:prod:ares', 'root', 'jagger')
|
|
or die "Couldn't connect to database: " . DBI->errstr;
|
|
|
|
my $anrs = $dbh->selectall_arrayref("SELECT anr, pid from prv_anr order by anr", { Slice => {} } );
|
|
for my $d ( @$anrs ) {
|
|
print "$d->{anr}\r";
|
|
my $zeiten = $dbh->selectrow_hashref("SELECT MIN(c.chktime) start, MAX(c.chktime) ende FROM .prv_master m LEFT JOIN .prv_chkpnt c ON c.wid=m.wid WHERE m.anr = ? and m.pid = ?", undef, $d->{anr}, $d->{pid});
|
|
|
|
my $s = "";
|
|
my $e = "";
|
|
|
|
if ( defined $zeiten->{start} ) {
|
|
$s = "first_chkpnt = '$zeiten->{start}',";
|
|
}
|
|
if ( defined $zeiten->{ende} ) {
|
|
$e = "last_chkpnt = '$zeiten->{ende}'";
|
|
}
|
|
|
|
if ( $s ne "" && $e ne "" ) {
|
|
my $zaehler = $dbh->selectall_arrayref("SELECT 1 AS cnt FROM .prv_master m LEFT JOIN .prv_chkpnt c ON c.wid=m.wid WHERE m.anr = ? and m.pid = ? GROUP BY c.wid", undef, $d->{anr}, $d->{pid});
|
|
|
|
my $cnt = 0;
|
|
for ( @$zaehler ) {
|
|
$cnt += 1;
|
|
}
|
|
|
|
$dbh->do("UPDATE prv_anr set prv_wids = ?, $s $e where anr = ?", undef, $cnt, $d->{anr} );
|
|
}
|
|
# $dbh->do("DELETE from prv_chkpnt where wid = ? and phid = ?", undef, $d->{wid}, 1 );
|
|
}
|
|
|
|
$t = ctime();
|
|
print "Ende: $t\n";
|
|
|
|
append_file('prv_anr_correction2.log', "$t\n");
|
|
|
|
$dbh->disconnect;
|
|
|
|
|
|
1; |