#!/usr/bin/perl -w

use lib "/www/opals/module";


use strict;
use Opals::Context("/etc/opals/conf/dps_ocpa");
#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...

$dbh->do("truncate table opl_idGen;");
$dbh->do("truncate table eq_records;");
$dbh->do("truncate table eq_recordFields;");
$dbh->do("truncate table eq_items;");
$dbh->do("truncate table eq_itemFields;");

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

my $max = 0;
my @headerList = ();
my $rid = 0;
my $header = <TMPFILE>;

my $curRecName = "";
my $iType = 'EQUIP';
my $iid;
my $catId;
my $dupNum = 1;
my $cat_map = {
    "computer equipment" => "3",  
    "cart"      => "3,4",
    "desktop"   => "3,7",
    "ipad"      => "3,8",
    "laptop"    => "3,9",  
    "monitor"   => "3,10",  
    "netbook, student" =>"3,11",  
    "netbook, teacher" =>"3,12",  
    "tablet computer" => "3,13",  
    "security equipment" =>"3,14",  
    "nook"               =>"3,15",  
    "printer - copier" => "16",  
    "cart printer"     => "16,17",  
    "copier"           => "16,18",  
    "printer"          => "16,19",  
    "plotter"          => "16,20",  
    "scanner"          => "16,21",  
    "interactive technology" =>"22",  
    "document camera"  => "22,23",  
    "projector"        => "22,24",  
    "interactive projector"=>"22,25",  
    "interactive board"    =>"22,26",  
    "av"               =>"27",  
    "pa system"        =>"27,28",  
    "network"          =>"29",  
    "access point, wireless" =>"29,30",  
    "chassis, network switch" =>"29,31"
} ;
=item
my $iType_map = {
    "Computer Equipment" => "CE",  
    "Audio Visual"       =>"AV",    
    "Interactive Technology" =>"Interactive Technology", 
    "Network Equipment"  =>"Network",
    "Printer/Copier" => "Printer/Copier", 
    "Software"           => "Software"
};
=cut

my $iType_map = {
    "Laptop" => "CE",  
    "Notebook" => "CE",  
    "Netbook, Teacher" =>"CE",
    "Netbook, Student" =>"CE",
    "Desktop"   =>"CE",
    "Monitor"   =>"CE",
    "PROJECTOR LCD"       =>"Interactive Technology",    
    "Interactive Technology" =>"Interactive Technology", 
    "Network Equipment"  =>"Network",
    "Printer" => "Printer/Copier", 
    "Printer/Copier" => "Printer/Copier",
    "Software"           => "Software"
};


while (<TMPFILE>){
    $myLine = $_;
    chomp;
    #print "record :" ,$myLine,"\n";
    $csv->parse($myLine);
    my @field = $csv->fields();
    my ($category, $rname, $make, $model, $accessories) =("","","","","") ;
    my ($bc,$serialNo,$macAddress,$locationCode,$AssignedFName,$AssignedLName,$AssignedDate, $itemStatus);
    #record fields
    my $itemType = $field[1];
    $category= ($field[1])?lc($field[1]):"";
    $catId = ($cat_map->{$category}) ? $cat_map->{$category}: "";
    $iType = ($iType_map->{$itemType})?$iType_map->{$itemType}:$itemType;
    $rname = $field[1]." - ".$field[2];
    $model = ($field[3]) ? $field[3]:"" ;
    #item fields
    $serialNo = $field[4]?$field[4]:"";
    $bc = $field[5];
    $accessories = ($field[6])?$field[6]:"";
    $macAddress = "";
    $locationCode = ($field[11])?$field[11]:"";
    $AssignedFName = ($field[12])?$field[12]:"";
    $AssignedLName = ($field[13])?$field[13]:"";
    $AssignedDate = ($field[14])?$field[14]:"";
    $itemStatus = ($field[16])?$field[16]:"";
    if ($curRecName ne $rname){
        $rid =  eq_record_idGen($dbh);
        #$rid++ ;# eq_record_idGen($dbh);
        print "R.$rid : name:$rname \t category:$category: $catId, model:$model \n";
        $iid = 1;
        $curRecName = $rname;
        eq_record_add($dbh,{rid=>$rid, fValue=>$rname,catId=>$catId, reqField=>1});
        eq_record_add($dbh,{rid=>$rid, fId=>3, fValue=>$model});
    }
    $bc =  ($bc ne "")?$bc:"no_barcode_".$rid ."_".$iid;
    print "I.$iid:barcode:$bc\tiType:$iType\tserial number:$serialNo\tlocation in building:$locationCode\tAssignedFN:$AssignedFName\tAssignedLN:$AssignedLName\tAssignedDate:$AssignedDate\tStatus:$itemStatus\t$macAddress";
    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=>5,  sfValue=>$serialNo});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>11,  sfValue=>$locationCode});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>31, sfValue=>$AssignedDate });
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>40, sfValue=> $AssignedFName  });
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>41, sfValue=> $AssignedLName  });
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>38, sfValue=>$itemStatus});
    eq_item_add($dbh,{ rid => $rid, iid=> $iid, sfId=>44, sfValue=> $macAddress});
    
    $iid++;
    $max++;
    print "\n";
    #last if ($max > 30);
}

close TMPFILE;

# Codes end.

exit 0;

=item
sub eq_record_add{ }
sub eq_item_add { }
sub eq_record_idGen { }
sub eq_isBarcodeExist{ }
=cut


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=?, category=?"; 
         $sth = $dbh->prepare($sql);
         $sth->execute($params->{'rid'}, $params->{'fValue'}, $params->{'catId'});
         $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; 
}