#!/usr/bin/perl -w

use lib "/www/opals/module";

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

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 = "Calculator";
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 = "Calculator - Texas Instruments 184";
    $make = "Texas Instruments" ;
    $model = ($field[1]) ? $field[1]:"" ;

    #item fields
    $itemType = "Calculator";
    $dateAcquired = "";
    $districtInvNo = "";
    $serialNo = $field[3]?$field[3]:"";
    $bc = "0000" . $field[2];


    $rid = $field[0] ; #eq_record_idGen($dbh);
    print "saving record ID $rid  ";
    print "record name: " , $rname , "\t";
    print "\n";
   
    #eq_record_add($dbh,{rid=>$rid, fValue=>$rname,reqField=>1});
    #eq_record_add($dbh,{rid=>$rid, fId=>1, fValue=>"Calculator"});
    #eq_record_add($dbh,{rid=>$rid, fId=>2, fValue=>"HS Library, Main Area"});

=item
    $bc =  ($bc ne "")?$bc:"no_barcode_".$rid ;
    if (eq_isBarcodeExist($dbh,$bc)){
        print "***duplicate barcode*** : " ,$bc , "\n";
        $bc = "DUP_".$bc."_".$dupNum;
        $dupNum++;
    }
=cut    
    print $bc , "\t", $serialNo , "\n";

    #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=>3,  sfValue=>$serialNo});
    
}
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; 
}