#!/usr/bin/perl -w

use lib "/www/opals/module";

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

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 opl_idGen_vcs;");
#$dbh->do("truncate table eq_records_vcs;");
#$dbh->do("truncate table eq_recordFields_vcs;");
#$dbh->do("truncate table eq_items_vcs;");
#$dbh->do("truncate table eq_itemFields_vcs;");

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

my $max = 0;
my @headerList = ();
my $rid;
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 = "IT DEPT";
my $iid;
my $dupNum = 1;
my ($totalRec, $totalItem ) = (0,0);
while (<TMPFILE>){
    $myLine = $_;
    chomp;
    #print "record :" ,$myLine,"\n";
    $csv->parse($myLine);
    my @field = $csv->fields();
    my ($rname, $make, $model) =("","","") ;
    my ($itemType, $dateAcquired ,$districtInvNo, $serialNo,$bc,$dCode,$bCode);

#   record fields
    $rname = $field[2];
    $make = ($field[3])? $field[3]:"" ;
    $model = ($field[4]) ? $field[4]:"" ;

    #item fields
    $itemType = ($field[6]) ? $field[6]:"";
    $dateAcquired = ($field[1]) ? $field[1]:"";
    $districtInvNo = ($field[7]) ? $field[7]:"";
    $serialNo = $field[5]  ? $field[5]:"";
    $bc = $field[0];

    $bCode = "BIS";
    $dCode = "BIS";

    if ($curRecName ne $rname){
        $rid = eq_record_idGen($dbh);
        print "saving record ID $rid  ";
        print "record name: " , $rname , "\t";
        print "\n";
        $iid = 1;
        $curRecName = $rname;
        eq_record_add($dbh,{rid=>$rid, fValue=>$rname,reqField=>1});
        eq_record_add($dbh,{rid=>$rid, fId=>1, fValue=>$make});
        eq_record_add($dbh,{rid=>$rid, fId=>3, fValue=>$model});
        eq_record_add($dbh,{rid=>$rid, fId=>14,fValue=>""});
        eq_record_add($dbh,{rid=>$rid, fId=>16,fValue=>""});
        eq_record_add($dbh,{rid=>$rid, fId=>27,fValue=>""});
        eq_record_add($dbh,{rid=>$rid, fId=>28,fValue=>""});
        eq_record_add($dbh,{rid=>$rid, fId=>33,fValue=>""});
        eq_record_add($dbh,{rid=>$rid, fId=>36,fValue=>""});

        $totalRec++; 
    }
    print $iid  ," .barcode :" , $bc , "\n";

    $totalItem++;
    $bc =  ($bc ne "")?$bc:"no_barcode_".$rid ."_".$iid;
    if (eq_isBarcodeExist($dbh,$bc)){
        print "***duplicate barcode*** : " ,$bc , "\n";
        $bc = "DUP_".$bc."_".$dupNum;
        $dupNum++;
    }
    eq_item_add($dbh,{ rid => $rid,barcode => $bc,typeId  => $itemType,available=>1,reqField=>1,iid=> $iid});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>5,  sfValue=>$serialNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>6,  sfValue=>""});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>7,  sfValue=>$districtInvNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>11, sfValue=>""});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>18, sfValue=>"BIS"});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>26, sfValue=>"BIS"});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>31, sfValue=>$dateAcquired});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>38, sfValue=> ""});
    $iid++;
    $max++;
    #last if ($max > 100);
}
print "Total Record : " , $totalRec , "\t", "Total Items : " , $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; 
}