141 lines
5.3 KiB
Perl
141 lines
5.3 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::Local;
|
|
use prv::Common;
|
|
use POSIX;
|
|
|
|
#my $db = "prod"; # Datenbankparameter
|
|
#my $user = "root"; # Datenbankuser
|
|
#my $pass = "jagger"; # Datenbankpasswort
|
|
#my $server = "ares"; # Datenbankserver
|
|
my $db = "prod"; # Datenbankparameter
|
|
my $user = "prv19"; # Datenbankuser
|
|
my $pass = "!nd!4n3R"; # Datenbankpasswort
|
|
my $server = "jupiter"; # Datenbankserver
|
|
STDOUT->autoflush(1);
|
|
|
|
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 $pids = $dbh->selectall_arrayref("SELECT pid, name from prv_product WHERE custid = ?", { Slice => {} }, 58 );
|
|
|
|
my %pidcnt;
|
|
my %pidmin;
|
|
my %pidsec;
|
|
my %pidmax;
|
|
|
|
my $datei = "Weidmüller.csv";
|
|
write_file($datei, "Funktionspruefung\n");
|
|
append_file($datei, "PID;Name;letzter Auftrag;Anzahl;min;avg;max\n" );
|
|
|
|
|
|
for my $pid ( @$pids ) {
|
|
$pidcnt{$pid->{pid}} = 0;
|
|
$pidmin{$pid->{pid}} = 999999999999;
|
|
$pidmax{$pid->{pid}} = 0;
|
|
|
|
# letzten Auftrag für die PID holen
|
|
my $auftr = $dbh->selectall_arrayref("SELECT anr from prv_master WHERE pid = ? GROUP BY anr ORDER BY anr desc", { Slice => {} }, $pid->{pid} );
|
|
|
|
my $cnt = @$auftr;
|
|
|
|
if ( $cnt > 0 ) {
|
|
#p @$auftr[0]->{anr};
|
|
my $cpids = $dbh->selectall_arrayref("SELECT p.pid, c.cpid, c.wid, c.phid FROM prv_master p
|
|
LEFT JOIN prv_chkpnt c ON c.wid=p.wid
|
|
WHERE p.anr = ? AND c.phid = ? AND c.result = ?", { Slice => {} }, @$auftr[0]->{anr}, 15, 1 );
|
|
my $cntcpid = @$cpids;
|
|
if ( $cntcpid > 0 ) {
|
|
my $problem = "";
|
|
for my $d ( @$cpids ) {
|
|
print "@$auftr[0]->{anr}: $pidcnt{$d->{pid}} - $cntcpid\r";
|
|
|
|
my ($note, $txt) = getXtxt($dbh, $d->{cpid} );
|
|
|
|
if ( $txt ) {
|
|
$pidcnt{$d->{pid}} += 1;
|
|
my $start;
|
|
my $end;
|
|
for my $l ( split ( /\n/, $txt ) ) {
|
|
# [HEADER];;;;;;;;
|
|
# ASCI_LOG_VER;9.002;
|
|
# TITLE;MINI-UTS-02 Messprotokoll;
|
|
# PRG_NAME;WDM_UR20_16DO_P_v1.0.0.seq;
|
|
# PRG_VER;v1.0.0;
|
|
# TEST_SPEC;V 2.3;
|
|
# STATION_ID;2108;
|
|
# BOARD;WDM_UR20_16DO_P;
|
|
# SNR;8020161118WDM00849;
|
|
# DATE;03.03.2017;
|
|
# TIME;13:35:02;
|
|
# [BODY];
|
|
# ID_FIELD;Code;Name;Value;L_Limit;U_Limit;Result;Unit;Step_Time_ms;Current_Time_ms;
|
|
# .......
|
|
# [FOOTER];
|
|
# TIME;13:35:35;
|
|
# OP_ID;PNR999999;
|
|
# MAIN_RESULT;PASS;
|
|
# [END]
|
|
|
|
# ^TIME;(..:..:..);
|
|
if ( $l =~ /^TIME/ and $l =~ /;/ ) {
|
|
my @zeitstelle = split ( /;/, $l );
|
|
my @zeit = split (/:/, $zeitstelle[1] );
|
|
if ( defined $start ) {
|
|
$end = timegm($zeit[2],$zeit[1],$zeit[0],20,11,2019);
|
|
if ( $start > $end ) {
|
|
$end = timegm($zeit[2],$zeit[1],$zeit[0],21,11,2019);
|
|
}
|
|
last;
|
|
} else {
|
|
$start = timegm($zeit[2],$zeit[1],$zeit[0],20,11,2019);
|
|
}
|
|
}
|
|
}
|
|
|
|
if ( $start and $end ) {
|
|
$pidsec{$d->{pid}} += ($end - $start);
|
|
if ( $pidmin{$d->{pid}} >= ($end - $start) ) {
|
|
$pidmin{$d->{pid}} = ($end - $start);
|
|
}
|
|
if ( $pidmax{$d->{pid}} <= ($end - $start) ) {
|
|
$pidmax{$d->{pid}} = ($end - $start);
|
|
}
|
|
} else {
|
|
$pidcnt{$d->{pid}} -= 1;
|
|
print "Probleme mit Zeit in Protokoll für cpid $d->{cpid}\n";
|
|
}
|
|
} else {
|
|
$pidcnt{$d->{pid}} -= 1;
|
|
print "Probleme mit protokoll für cpid $d->{cpid}\n";
|
|
}
|
|
}
|
|
#print "@$auftr[0]->{anr}: erledigt $problem\n";
|
|
} else {
|
|
#print $pid->{pid}." - ist wahrscheinlich eine Oberstufe\n" ;
|
|
}
|
|
if ($pidsec{$pid->{pid}} ) {
|
|
my $float = $pidsec{$pid->{pid}} / $pidcnt{$pid->{pid}};
|
|
my $round = ceil($float);
|
|
print $pid->{name}."\t\t $round \n" ;
|
|
append_file($datei, "$pid->{pid};$pid->{name};@$auftr[0]->{anr};$cntcpid;$pidmin{$pid->{pid}};$round;$pidmax{$pid->{pid}}\n" );
|
|
}
|
|
} else {
|
|
#print $pid->{pid}." - keine Aufträge\n" ;
|
|
}
|
|
}
|
|
|
|
print "\n";
|
|
|
|
$dbh->disconnect;
|
|
|
|
1; |