#!/usr/bin/perl -w

use lib "/www/opals/module";


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

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 = 'EQ';
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,$accessories,$manufacturerURL,$onlineManual) =("","","","","","") ;
    my ($bc,$room,$serialNo,$datePurchase, $price, $serviceNotes, $serviceCosts,$buildingCode,$acquiredBy,$PONumber,$distributor,$bocesId,$itemType);

    #record fields
    $rname = $field[1];
    $make = ($field[3])? $field[3]:"" ;
    $model = ($field[4]) ? $field[4]:"" ;
    $accessories = ($field[10]) ? $field[10]:"";
    $manufacturerURL = ($field[11]) ? $field[11]:"";
    $onlineManual = ($field[12]) ? $field[12]:"";

    #item fields
    $bc = $field[2];
    $room = $field[0]  ? $field[0]:"";
    $serialNo = $field[5]  ? $field[5]:"";
    $datePurchase= ($field[6]) ?$field[6]:"";
    $price = ($field[7]) ? $field[7] : "";
    $serviceNotes = ($field[8]) ? $field[8]:"";
    $serviceCosts = ($field[9]) ? $field[9]:"";
    $buildingCode = ($field[13]) ? $field[13]:"";
    $acquiredBy = ($field[14]) ? $field[14]:"";
    $PONumber = ($field[15]) ? $field[15]:"";
    $distributor = ($field[16]) ? $field[16]:"";
    $bocesId = ($field[17]) ? $field[17]:"";
    $itemType = ($field[18]) ? $field[18]:"";

    if ($curRecName ne $rname){
        $rid = eq_record_idGen($dbh);
        print "saving record ID $rid  ";
        print "record name: " , $rname , "\t";
=item        
        print "make: " , $make , "\t";
        print "model: " , $model , "\t";
        print "accessories: " , $accessories ,  "\t" ;
        print "manufacturer URL : " , $manufacturerURL ,  "\t" ;
        print "online manual: " , $onlineManual ,  "\t" ;
=cut        
        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=>2, fValue=>$model});
        eq_record_add($dbh,{rid=>$rid, fId=>5, fValue=>$accessories});
        eq_record_add($dbh,{rid=>$rid, fId=>6, fValue=>$manufacturerURL});
        eq_record_add($dbh,{rid=>$rid, fId=>8, fValue=>$onlineManual});
        $totalRec++; 
    }
=item    
    print "item ID : " , "\t";
    print "barcode: " , "\t";
    print "room: " ,  "\t";
    print "serial number: "  , "\t";
    print "date Purchase : "  , "\t";
    print "price : ",  "\t";
    print "service notes :", "\t";
    print "service costs: ", "\t";
    print "building code ", "\t";
    print "acquired",  "\t";
    print "PO Number" , "\t";
    print "distributor" ,  "\t";
    print "boce ID" , "\t";
    print "item Type" , "\t";
    print "\n";
    print $iid, "\t" , $bc, "\t", $room , "\t", $serialNo , "\t" ,$datePurchase , "\t", $price , "\t",$serviceNotes , "\t";
    print $serviceCosts, "\t" , $buildingCode , "\t", $acquiredBy,  "\t", $PONumber,  "\t" , $distributor, "\t", $bocesId, "\t", $itemType  ;
    print "\n";
=cut    
    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=>9,  sfValue=>$serialNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>10, sfValue=>$price});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>23, sfValue=>$bocesId});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>22, sfValue=>$buildingCode});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>13, sfValue=>$room});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>19, sfValue=>$acquiredBy});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>16, sfValue=>$distributor});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>20, sfValue=>$PONumber});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>15, sfValue=>$datePurchase});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>18, sfValue=>$serviceNotes});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>21, sfValue=> $serviceCosts});
    $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; 
}
################################################################################

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