#!/usr/bin/perl -w

use lib "/www/opals/module";


use strict;
#use Opals::Context;
use Opals::Context("/etc/opals/conf/onc_la");

use Text::CSV_XS;
use Getopt::Std;

my %options = ();
getopts("i:",\%options);
my $myFile = $options{i};
if (!$myFile || ! -f $myFile) {
    print "Usage: $0 -i IN_FILE\n";
    exit 1;
}

my $dbh = Opals::Context->dbh();;
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}
#$| = 1;
# Codes start...

#my $myFile = "/tmp/eq.csv
#$dbh->do("truncate table eq_records_tmp;");
#$dbh->do("truncate table eq_recordFields_tmp;");
#$dbh->do("truncate table eq_items_tmp;");
#$dbh->do("truncate table eq_itemFields_tmp;");
#$dbh->do("truncate table opl_idGen_tmp;");

my $csv = Text::CSV_XS->new({ binary => 1 });
my $myLine;
open TMPFILE, "$myFile";

my $max = 0;
my @headerList = ();
my $rid = 1;
my $header = <TMPFILE>;
$csv->parse($header);
my @field = $csv->fields();

foreach my $f (@field){
    $f =~ s/^\s+//;
    $f =~ s/\s+$//;
    print $f , "\n";
    push @headerList , $f;
}


my $curRecName = "";
my $iType = 'EQ';
my $iid;


my $dupNum = 1;

    my $maping = {
        'EqName' => 1,
        1       => 2,
        2       => 3,
        3       => 4,
        4       => 5,
        8       => 6,
        11      => 8,
        12      => 7,
        'bc'    => 9,
        'iType' => 10,
        13      => 11,
        14      => 12,
        15      => 13,
        17      => 18,
        18      => 14,
        19      => 16,
        20      => 17,
        21      => 15,
        23      => 19,
    };
        
    
my ($totalRecord,$totalItem) = (0,0);
my $curRId = 0;
my ($rname, $eqType, $manufacture, $model, $feature, $noteSupplies,$distributor,$manuURL) =("","","","","","","","") ;
my ($bc,$iType,$location,$dateAcuquired, $price, $dInv, $serialsNo, $insNo,$oncNo, $funding, $note );
my $curRecName = "";
my $rid_tmp = 0;
my $rid = 0;
while (<TMPFILE>){
    $myLine = $_;
    chomp;
    #print "record :" ,$myLine,"\n";
    $csv->parse($myLine);
    my @field = $csv->fields();
    #record fields
    $rid_tmp = $field[0];
    $rname = $field[1];
    $eqType = ($field[2])? $field[2]:"" ;
    $manufacture = ($field[3]) ? $field[3] : "";
    $model  = ($field[4])? $field[4]:"" ;
    $feature  = ($field[5])? $field[5]:"" ;
    $noteSupplies  = ($field[6])? $field[6]:"" ;
    $distributor = ($field[7])? $field[7]:"" ;
    $manuURL  = ($field[8])? $field[8]:"" ;
    #item fields
    $bc = $field[9];
    $iType =  $field[10];
    $location = ($field[11])? $field[11]:"" ;
    $price = ($field[12])? $field[12]:"" ;
    $dateAcuquired = ($field[13])? $field[13]:"" ;
    $dInv =  ($field[14])? $field[14]:"" ;
    $serialsNo =   ($field[15])? $field[15]:"" ;
    $insNo =  ($field[16])? $field[16]:"" ;
    $oncNo =  ($field[17])? $field[17]:"" ;
    $funding =  ($field[18])? $field[18]:"" ;
    $note =  ($field[19])? $field[19]:"" ;

    if ($curRId ne $rid_tmp){
        $curRId = $rid_tmp;

        $rid = eq_record_idGen($dbh);
        print "record ID $rid  vs  $curRId ";
        #print "record name: " , $rname , "\t";
        #print "eq type: " , $eqType , "\t";
        print "\n";
        $iid = 1;
        $totalRecord++;
        $curRId = $rid_tmp;
        eq_record_add($dbh,{rid=>$rid, fValue=>$rname,reqField=>1});
        eq_record_add($dbh,{rid=>$rid, fId=>1, fValue=>$eqType});
        eq_record_add($dbh,{rid=>$rid, fId=>2, fValue=>$manufacture });
        eq_record_add($dbh,{rid=>$rid, fId=>3, fValue=>$model});
        eq_record_add($dbh,{rid=>$rid, fId=>4, fValue=>$feature });
        eq_record_add($dbh,{rid=>$rid, fId=>8, fValue=>$noteSupplies});
        eq_record_add($dbh,{rid=>$rid, fId=>12, fValue=>$distributor});
        eq_record_add($dbh,{rid=>$rid, fId=>11, fValue=>$manuURL});
    }
    #print "item ID : $iid : ";
    #print "barcode: " , $bc, "\t";
    if (eq_isBarcodeExist($dbh,$bc)){
        print "duplicate barcode" ,$bc , "\n";
        $bc = "DUP_".$bc."_".$dupNum;
        $dupNum++;
    }
    eq_item_add($dbh, { rid => $rid,barcode => $bc,typeId  => $iType,available=>1,reqField=>1,iid=> $iid});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>13, sfValue=>$location});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>21,  sfValue=>$serialsNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>15, sfValue=>$dateAcuquired});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>14, sfValue=>$price});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>19, sfValue=>$insNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>20, sfValue=>$oncNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>17, sfValue=>$funding});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>23, sfValue=>$note});

    $iid++;
    $max++;
    $totalItem++;
    #last if ($max > 100);
}

print "total Record :" , $totalRecord , " total Item : ", $totalItem , "\n";

close TMPFILE;

# Codes end.

exit 0;
sub eq_record_add{
   
    my ($dbh, $params) = @_;
    return -1 if ($params->{'rid'} eq '');
    my ($sql, $sth, $id);
    if ($params->{'reqField'}){
         $sql = "insert into eq_records set rid=?, rname=?"; 
         $sth = $dbh->prepare($sql);
         $sth->execute($params->{'rid'}, $params->{'fValue'});
         $id = $dbh->{'mysql_insertid'};

        }
    else{
        $sql = "insert into eq_recordFields set rid=?, fId=?, fValue=?";    
        $sth = $dbh->prepare($sql);
        $sth->execute($params->{'rid'}, $params->{'fId'}, $params->{'fValue'});
        $id = $dbh->{'mysql_insertid'};
    }

    $sth->finish;
    return $id;

}
sub eq_item_add{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'iid'} eq '');
    my ($sql, $sth, $id);
    if ($params->{'reqField'}){
        $sql = "insert into eq_items set iid=?, rid=?, barcode=?, typeId=?, Available=1, createdDate=now()";
        $sth = $dbh->prepare($sql);
        $sth->execute($params->{'iid'}, $params->{'rid'}, $params->{'barcode'}, $params->{'typeId'});
        $id = $dbh->{'mysql_insertid'};
    }
    else{
        $sql = "insert into eq_itemFields set iid=?, rid=?, sfId=?, sfValue=?";
        $sth = $dbh->prepare($sql);
        $sth->execute($params->{'iid'}, $params->{'rid'}, $params->{'sfId'}, $params->{'sfValue'});
        $id = $dbh->{'mysql_insertid'};
    }
    $sth->finish;
    return $id;
}


sub eq_record_idGen {
    
    my ($dbh) = @_;
    my $checkSum = `date +\%Y\%m\%d\%H\%M\%S\%N`;
    chomp $checkSum;
    my $sql = "insert into opl_idGen set type = 'equipment', checkSum=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($checkSum);
    $sql = "select id from opl_idGen where type = ? and checkSum=? ";
    $sth = $dbh->prepare($sql);
    $sth->execute('equipment',$checkSum);
    my ($id) = $sth->fetchrow_array();
    $sth->finish;
    return $id;
}

sub eq_isBarcodeExist{

    my($dbh,$bc)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select barcode from eq_items where barcode=? 
_STH_
    $sth->execute($bc);
    my ($ret) = $sth->fetchrow_array;
    $sth->finish;
    if(!$ret){
        return 0;
    }
    return $ret; 
}
################################################################################

=item
sub blahblahblah {
}
=cut
############################################################