#!/usr/bin/perl

$use_mcm_lw_num = 1;


#use strict;
use Spreadsheet::ParseExcel;
use POSIX;
use DBI;

my $file = shift or die "No filename given.";

my $oBook = 
    Spreadsheet::ParseExcel::Workbook->Parse($file);

my($iR, $iC, $oWkS, $oWkC);

my %colNum;# = { 'mcm' => -1, 'trap' => -1, 'status' => -1 }; 


foreach my $oWkS (@{$oBook->{Worksheet}}) {
    print "--------- SHEET:", $oWkS->{Name}, "\n";

    my $iR = $oWkS->{MinRow}; 

    $colNum{'mcm'} = -1;
    $colNum{'trap'}= -1;
    $colNum{'status'} = -1;


    for(my $iC = $oWkS->{MinCol} ;
	defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {

	$oWkC = $oWkS->{Cells}[0][$iC];

	next if (not defined $oWkC);

	$colNum{'mcm'}    = $iC if ($oWkC->Value eq "MCM_T_NUM");
	$colNum{'mcmlw'}  = $iC if ($oWkC->Value eq "MCM_LW_NUM");
	$colNum{'trap'}   = $iC if ($oWkC->Value eq "BurnedID");
	$colNum{'status'} = $iC if ($oWkC->Value eq "CHIP_STAT_CODE");

    }

    if ( $colNum{'mcm'} == -1 ||
	 $colNum{'trap'} == -1 ||
	 $colNum{'status'} == -1 ) {

	print "No properly formatted table found. Skipping sheet.\n";
	next;

    }

    print "Reading table, MCM / TRAP / STATUS found in columns ";
    print $colNum{'mcm'}." / ";
    print $colNum{'trap'}." / ";
    print $colNum{'status'}."\n";
    

    my %entries;

    print $oWkS->{MinRow}."\n";
    print $oWkS->{MaxRow}."\n";

    for(my $iR = $oWkS->{MinRow} + 1 ; 
        defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {

	my %newentry;
	my $mcm;

	#print $oWkS->{Cells}[$iR][$colNum{'mcm'}]->Value . " --- ";
	#print floor($oWkS->{Cells}[$iR][$colNum{'mcmlw'}]->Value/10.). "\n";


	if ($use_mcm_lw_num == 0) {
	    $mcm    = $oWkS->{Cells}[$iR][$colNum{'mcm'}]->Value;
	}else {
	    $mcm = floor($oWkS->{Cells}[$iR][$colNum{'mcmlw'}]->Value/10.);
	}

	$trap   = $oWkS->{Cells}[$iR][$colNum{'trap'}]->Value;
	$status = $oWkS->{Cells}[$iR][$colNum{'status'}]->Value;


	if (! defined $entries{$mcm}) {

	    $entries{$mcm}= { 'mcm' => $mcm,
			      'trap' => $trap,
			      'status' => $status};
	    next;
	}

	if ($entries{$mcm}{'trap'} != 0 && $trap != 0 &&
	    $entries{$mcm}{'trap'} != $trap ) {
	    
	    
	    print "Conflicting TRAP IDs from different tests for MCM $mcm\n";
	}

	if ( $entries{$mcm}{'trap'} == 0 && $trap != 0 ) {
	    $entries{$mcm}= { 'mcm' => $mcm,
			      'trap' => $trap,
			      'status' => $status};
	    next;
	}




    }

    my $dbh = DBI->connect("DBI:mysql:database=trdcdb;host=localhost",
			   "alice", 'alice!@#',
			   {'RaiseError' => 1});

    
    # Warning: REPLACE is not official SQL!!!
    $sth = $dbh->prepare("REPLACE INTO mcm(mcmid, status, laserid) VALUES (?,?,?)");
    
    # Good SQL only works with mysql >= 4.1.0
    #$sth = $dbh->prepare("INSERT INTO mcm(mcmid, status, trap) VALUES (?,?,?) ON DUPLICATE KEY UPDATE");



    my $mcm;
    foreach $mcm (keys %entries) {
	
	#print "$mcm\n";
	#print $entries{$mcm}{'trap'}."\n";
	
	
	foreach $f (keys %{$entries{$mcm}}) {
	    #print "  $f = ".$entries{$mcm}{$f}."\n";
	}
	
	$sth->execute($entries{$mcm}{'mcm'},
		      $entries{$mcm}{'status'},
		      $entries{$mcm}{'trap'});
	
	
    }

    $dbh->disconnect;

    if (0) {
	for(my $iC = $oWkS->{MinCol} ;
	    defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {
	    
	    $oWkC = $oWkS->{Cells}[$iR][$iC];
	    
	    print "( $iR , $iC ) =>'", $oWkC->Value, "'\n" if($oWkC);
	
	}
    }
}
#foreach my $oWkS (@{$oBook->{Worksheet}}) {
#    print "--------- SHEET:", $oWkS->{Name}, "\n";
#    for(my $iR = $oWkS->{MinRow} ; 
#        defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
#        for(my $iC = $oWkS->{MinCol} ;
#            defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {
#            $oWkC = $oWkS->{Cells}[$iR][$iC];
#            print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
#        }
#    }
#}
