#!/usr/bin/perl

#use strict;
use POSIX;
use DBI;


$confirm_new_rob = 0;

$loc = "HD.ROB_Test";


my $logfile = shift or die "No log file given.";
my $pdffile = shift or die "No pdf file given.";
my $tgzfile = shift or die "No tgz file given.";

open IN, "<$logfile";

while (<IN>) {

    if ( /^Name:\s*F(\d+)_?T(\d\w)_?V(\d+)_?N(\d+)/ ) {
	$form = $1;
	$type = $2;
	$ver  = $3;
	$id   = $4;

    }

    if ( /^Name:\s*([1234][AB])_(\d*)/ ) {
	$form = 0;
	$type = $1;
	$ver  = 2;
	$id   = $2;

    }

    if ( /^Date:\s*(.*)$/ ) {
	$date = $1;
    }
	
    if ( /^Result:\s*(\d*)$/ ) {
	$result = $1;
    }
	
    if ( /^SCSN:\s*(.*)$/ ) {
	$scsnerr = $1;
    }
	
    if ( /^TRAP:\s*(.*)$/ ) {
	$traperr = $1;
    }
	
    if ( /^PASA:\s*(.*)$/ ) {
	$pasaerr = $1;
    }
	
    if ( /^NI:\s*(.*)$/ ) {
	$nierr = $1;
    }
	
    if ( /^ORI:\s*(.*)$/ ) {
	$orierr = $1;
    }
	
    if ( /^Tester:\s*(.*)$/ ) {
	$person = $1;
    }
	
    if ( /^Comment:\s*(.*)$/ ) {
	$comment = $1;

	while(<IN>) {
	    $comment .= $_;
	}
    }
	
    


}

print "Name: F$form T$type V$ver N$id\n";
print "Date: $date\n";
print "Tester: $person\n";
print "Result: $result\n";
print "SCSN: $scsnerr\n";
print "TRAP: $traperr\n";
print "PASA: $pasaerr\n";
print "NI: $nierr\n";
print "ORI: $orierr\n";
print "Comment: $comment\n";

$pdf_loid = -1;
$tgz_loid = -1;


undef ($scsnerr) if ($scsnerr =~ /^\s*$/);
undef ($traperr) if ($traperr =~ /^\s*$/);
undef ($pasaerr) if ($pasaerr =~ /^\s*$/);
undef ($nierr)   if ($nierr   =~ /^\s*$/);
undef ($orierr)  if ($orierr  =~ /^\s*$/);



my $dbh = DBI->connect("DBI:Pg:dbname=gateDB;host=alice.physi.uni-heidelberg.de",
		       "alice", 'alice!@#',
		       {'RaiseError' => 1});

$dbh->begin_work();

print "$id'n";

$sth = $dbh->prepare("SELECT * FROM rob WHERE rob_id=$id;");
$sth->execute();

if ( ! $sth->fetchrow_array() ) {

    if ($confirm_new_rob) {
	print "ROB not found, do you want to insert it [Y/n]? ";
	$yn = <STDIN>;
	chop $yn;

	if ($yn ne 'y' and $yn ne 'Y' and $yn ne '') {
	    print "\n...then I'm out of here!\n";
	    exit;
	}
    }


    if (!defined $person) {
	print "Enter your initials: ";
	$person = <STDIN>;
	chop $person;
    }

    if (!defined $loc) {
	print "Your location [SMint]: ";
	$loc = <STDIN>; chop $loc;
	
	$loc = 'SMint' if ($loc eq '');
    }

    $sth = $dbh->prepare("INSERT INTO rob(form,type,ver,rob_id,person,loc_id) ".
			 "VALUES (?,?,?,?,?,(SELECT loc_id FROM loc ".
			 "                   WHERE name=?));");
    $sth->execute($form,$type,$ver,$id,$person,$loc);
    
}


$pdf_loid = upload_file($dbh,$pdffile,"","application/pdf");
$tgz_loid = upload_file($dbh,$tgzfile,"","application/gzip");

$sth = $dbh->prepare("INSERT INTO rob_test ".
		     "VALUES (?,?,?,?,?,?,?,?,?,".
		     "        (SELECT file_id FROM file WHERE loid=?),".
		     "        (SELECT file_id FROM file WHERE loid=?));");

$sth->execute($id,$date,$result,
	      $scsnerr,$traperr,$pasaerr,$nierr,$orierr,
	      $comment, $pdf_loid, $tgz_loid);


$sth = $dbh->prepare("UPDATE rob SET status=? WHERE rob_id=?");
$sth->execute($result,$id);


$dbh->commit;


$dbh->disconnect;




sub upload_file () {

    my $dbh      = $_[0];
    my $filename = $_[1];
    my $dbname   = $_[2];
    my $mimetype = $_[3];

    my $nbytes;

    open INFILE, "<$filename";
    binmode INFILE;

    my $buf;
    my $len = read INFILE,$buf,100000000;

    close INFILE;


    my $lobjId = $dbh->func($dbh->{pg_INV_WRITE}, 'lo_creat');
    my $lobj_fd = $dbh->func($lobjId, $dbh->{pg_INV_WRITE} , 'lo_open');
    my $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_write');

    my $sth = $dbh->prepare("INSERT INTO file(name,mime_type,loid) ".
			 "VALUES (?,?,?);");
    $sth->execute($filename, $mimetype, $lobjId );

    return $lobjId;


};


# This script updates the gateDB. Start with ROB teser file summary.log
# It parses the summary file from the ROB test.
# Uses the extended mcmdb from Venelin. 
# currently running on db01.trd.net at Physikalisches Institut of the university of Heidelberg
# run after the files have been uploaded to the gateDB with Toms script, since this script
# also makes the first entries for the ROBs. 

use DBI;

# open the log file from the ROB tester
open(IN, "<$logfile") or die "Unable to open file $filename";
@lines = <IN>;
close(IN);


# establish connections to the databases
my $dbh_gatedb = DBI->connect("DBI:Pg:dbname=gateDB;host=alice00.physi.uni-heidelberg.de",
                              "alice", 'alice!@#');
my $dbh_mcmdb = DBI->connect("DBI:mysql:dbname=mcmdb;host=db01.trd.net","mcm", 'mcm123');

$flag = 0; #for comment recognition
foreach $line (@lines){
    
    # extract Infos from log file
    # comments can be distributed over several lines; 
    # it finishes with an empty line or with the ====== line
    if($line =~ /=====.*/){$flag = 0;} #comment recognition terminated by an ===== line
    if($flag == 1 ){
	if($line =~ /(.*)/ and $flag == 1){$rob_comment=$rob_comment."\n".$1;}
    }
    if($line =~ /Comment: (.*\w.*)/){$rob_comment=$1; $flag = 1;}
    
    if($line =~ /^Name:.*_([0-9]*)/){$rob_id=$1;}
    if($line =~ /^Tester: (.*)/){$person = $1;}
    
#line describes a chip
    if($line =~ /^Slave .*\(MCM ([0-9]+)\) has CHIPID=(.*) = ([0-9]*),.*/){
	$pos_on_rob = $1;
	$laser_id_hex = $2;
	$laser_id_hex =~ tr/a-f/A-F/;
	$laser_id_dec = &hex2dec($laser_id_hex);
	
# find mcm_id in Venelins DB according to LaserID
	$sql =    "SELECT MCM_id,yymmdd,id FROM mcmtbl
                   WHERE ChipId = \'$laser_id_hex\'
                   ORDER BY yymmdd";
	$sth = $dbh_mcmdb->prepare($sql);
	$sth->execute();
	$ii = 0;
	while(@{$ergebnis[$ii]}=$sth->fetchrow_array and $ii<6 )
	{
          #cannot exclude MCMs which are allready in the DB, since ROB may be tested again after have been repaired
          $ii = $ii + 1;
        }
	$sth->finish();
	## unique take it
	if($ii == 0){
	    print "could not find laser_id in database. 
                   Normally this means that Venelins 
                   database at db02.trd.net has to be updated.";
            &Error();
	}elsif($ii == 1){ 
	    $mcm_id = ${$ergebnis[0]}[0];
            $id_in_mcmtbl = ${$ergebnis[0]}[2];
	## several ask with menu
	}elsif($ii < 5){ 
	    $jj=0;
	    for($jj=0; $jj<$ii;$jj++) {
		print $jj."...........".${$ergebnis[$jj]}[0]."\n";
	    }
	    print "please select the mcm_id of chip at pos $pos_on_rob: \n"; 
	    chop($input = <STDIN>);
	    while($input =~ /[^0-4]/ or $input > $ii - 1){
		print "please chose one of the options above \n";
		chop($input = <STDIN>);
	    }
	    print "selected ${$ergebnis[$input]}[0] as MCMId \n";
	    $mcm_id = ${$ergebnis[$input]}[0];
	    $id_in_mcmtbl = ${$ergebnis[$input]}[2];    
      
	## many ask without menu, but check if mcmid is in database
	}else{
	    $flag = 0;
	    while($flag == 0){
		print "please type the laser_id of Chip at Position $pos \n";
		$input = <STDIN>;
		$hex_burned_id = &dec2hex($input);
		$sql = "SELECT MCM_id,id FROM mcmtbl WHERE MCM_id = \'$laser_id_hex\'";
		$sth = $dbh_mcmdb->prepare($sql);
		$sth->execute();
		if(@tmp = $sth->fetchrow_array){
                   $mcm_id = $input;
                   $flag = 1;
                   $id_in_mcmtbl = $tmp[1];    
                }
		else{print "could not find LaserId in Database mcmdb in table  waftbl. 
                            Please try again: \n"}
		$sth->finish();
	    }
	}
	
# write data to mcmdb.identtbl
	$sql = "INSERT INTO identtbl (mcm_id, laser_id_hex,laser_id_dec, rob_id, rob_pos, id_in_mcmtbl)
                VALUES (\'$mcm_id\',\'$laser_id_hex\',$laser_id_dec,$rob_id,$pos_on_rob, $id_in_mcmtbl)";
	$sth = $dbh_mcmdb->prepare($sql);
	$sth->execute();
	$sth->finish();

#write data to gateDB
# check if mcmid is allready in the gatedb
        $sql =    "SELECT mcm_id,laserid FROM mcm
                   WHERE mcm_id = \'$mcm_id\'";
	$sth = $dbh_gatedb->prepare($sql);
	$sth->execute();
	$ii = 0;
	@tmp = $sth->fetchrow_array;
	$sth->finish();

# if yes with same laser_id: ROB and MCM is tested for the second time UPDATE the entry
        if($tmp[0]== $mcm_id and $tmp[1] == $laser_id_dec){
           $sql = "UPDATE mcm SET rob_id = $rob_id,rob_pos= $pos_on_rob, person = \'$person\'
                   WHERE mcm_id = $mcm_id";
	   $sth = $dbh_gatedb->prepare($sql);
	   $sth->execute();
	   $sth->finish();
        } 

# if yes with different laser_id: we have the same mcm_id for the second time MAKE IT UNIQUE put 9# in front
        elsif($tmp[0]== $mcm_id and $tmp[1] != $laser_id_dec){
           $ii=0;        
           while(@tmp > -1) {
              $mcm_id_tmp = '9'.$ii.$mcm_id; 
              $sql =    "SELECT mcm_id,laserid FROM mcm
                         WHERE mcm_id = \'$mcm_id_tmp\'";
	      $sth = $dbh_gatedb->prepare($sql);
	      $sth->execute();
              @tmp = $sth->fetchrow_array;
	      $sth->finish();
              $ii++;
	  }
	  $mcm_id = $mcm_id_tmp;
          $sql = "INSERT INTO mcm (mcm_id,laserid,rob_id,rob_pos,person)
                  VALUES ($mcm_id ,$laser_id_dec,$rob_id,$pos_on_rob,\'$person\'); ";
          $sth = $dbh_gatedb->prepare($sql);
          $sth->execute();
          $sth->finish();
       }
	
# if no: INSERT new entry	 
	else{$sql = "INSERT INTO mcm (mcm_id,laserid,rob_id,rob_pos,person)
                     VALUES ($mcm_id ,$laser_id_dec,$rob_id,$pos_on_rob,\'$person\'); ";
	    $sth = $dbh_gatedb->prepare($sql);
	    $sth->execute();
	    $sth->finish();
	 }

# do check for Venelin, if necessary write email
# check weather all chips have the functionallity for their position
	$sql = "SELECT Function FROM mcmtbl WHERE MCM_id = \'$mcm_id\'";
	$sth = $dbh_mcmdb->prepare($sql);
	$sth->execute();
	@tmp = $sth->fetchrow_array;
	$sth->finish();
	
	if($tmp[0] =~ /.*1.*/ ){
        }elsif($tmp[0] =~ /.*2.*/ and $pos < 16){
        }elsif($tmp[0] =~ /.*3.*/ and ($pos == 16 or $pos == 17)){ 
        }elsif($tmp[0] =~ /.*4.*/ and ($pos == 0  or $pos == 1 or $pos == 3 or
	                               $pos == 4 or $pos == 5 or $pos == 7 or 
		                       $pos == 8 or  $pos == 9 or $pos == 11 or 
				       $pos == 12 or $pos == 13 or $pos == 15 )){
	}else{ #write email to Venelin
	    if($mcm_id != ''){ # we don't write emails if the script was invoked 2 times by
                               # accident, or if it nees update
                &Error();
                &WriteEmail('Stefan_Zimmer@gmx.de',
                            'szimmer@physi.uni-heidelberg.de',
                            "Chips on wrong possition detected during ROB test",
                            "Hallo Venelin,\n
                             my script has detected wrong assembeled chips \n
                             therefore this email was automatically created \n 
                             on board: $rob_id \n
                             MCM with id: $mcm_id \n
                             has Function: @tmp[0] \n
                             is on wrong position: $pos_on_rob \n
                             Best regards Stefan");
	   }
	}
        print "insert MCM with id $mcm_id with laserId $laser_id_dec 
               at ROB $rob_id at Pos $pos_on_rob\n\n";
    } #if line is line describing a mcm-chip
} #foreeach line in the log file

# write comment to the gateDB
if($rob_comment){
    # create a new comment id
    $sql = "SELECT cmnt_id FROM comment ORDER BY cmnt_id desc";
    $sth = $dbh_gatedb->prepare($sql);
    $sth->execute();
    @tmp = $sth->fetchrow_array;
    $sth->finish();    
    $cmnt_id = $tmp[0];
    $cmnt_id ++;
    
    #write comment to gateDB
    $sql = "INSERT INTO comment(cmnt_id, person, body, title)
            VALUES ($cmnt_id,\'$person\',\'$rob_comment\', \'$person\')";
    $sth = $dbh_gatedb->prepare($sql);
    $sth->execute();
    $sth->finish();
    
    # assign comment to rob
    $sql = "INSERT INTO rob_cmnt_link (rob_id,cmnt_id)
            VALUES ($rob_id, $cmnt_id) ";
    $sth = $dbh_gatedb->prepare($sql);
    $sth->execute();
    $sth->finish();
    
}

# close connection to the database
$dbh_mcmdb->disconnect();
$dbh_gatedb->disconnect();


# functions
#convert an hex number
sub hex2dec {
    $input = $_[0];
    $ii = 0;
    $input =~ s/0x//;
    while ($input){
        $tmp=chop($input);
        $tmp =~ s/a/10/gi;
        $tmp =~ s/b/11/gi;
        $tmp =~ s/c/12/gi;
        $tmp =~ s/d/13/gi;
        $tmp =~ s/e/14/gi;
        $tmp =~ s/f/15/gi;
        $zahl = $zahl + $tmp*(16**$ii);
        $ii = $ii + 1;
    }
    return $zahl;
}

## inform the user about the upload

print "\n\n =================================================
        \n\n  data uploaded, please press Enter to proceed";
       $input = <STDIN>;

sub Error{
    print "\n\n ERROR ERROR ERROR ERROR ERROR ERROR ERROR
           \n\n there was an ERROR, please check the output
           \n press Enter to exit the unsuccessfull  script";
    $input = <STDIN>;
    die;
}

# Send an email via the PI Server
# (adress_recipient, adress_sender, subject, message)
sub WriteEmail{
    use Net::SMTP;                          # Einbinden des NET:SMTP Moduls
    $mailServer  = "pi0.physi.uni-heidelberg.de";
    $nachricht   = $_[3];  
    $betreff     = $_[2];    
    $empfaenger  = $_[0];  
    $subject     = $_[2];  
    $absender    = $_[1];
       
    $smtp = Net::SMTP->new($mailServer);        
    $smtp->mail($absender);                 
    $smtp->to($empfaenger);             
    $smtp->data();                  
    $smtp->datasend("Subject: $betreff\n");
    $smtp->datasend("To: $empfaenger\n");
    $smtp->datasend("\n");     
    $smtp->datasend($nachricht);
    $smtp->dataend();  
    $smtp->quit;   
}
