#!/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
=item
$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");
=cut
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 $totRec = 0;
my $totItem = 0;

my $curRecName = "";
my $iid;
my $dupNum = 1;
while (<TMPFILE>) {
    $myLine = $_;
    chomp;

    #print "record :" ,$myLine,"\n";
    $csv->parse($myLine);
    my @field = $csv->fields();
    my ( $rname,$make,$model,$warranty,$manufacturer_url,$supplier_url,$online_man, $notes,$accessoryNote)
        = ( "", "", "", "", "", "","","","" );

    my ($bc,$item_type, $serialNo,$price,$location, $bCode,$dCode,$dateAcq, $iStatus,
        $vendorName, $assignedFName, $assignedLName, $po_number, $ipAddress, $macAddress,$softwareKey,
        $cartNumber, $slotNumber, $accessories);
    #record fields
    $rname              = ($field[0])?$field[0]:"";
    $make               = "Apple";
    $model              = ($field[0])? $field[0]:"";
    $warranty           = "";
    $manufacturer_url   = "";
    $supplier_url       = "";
    $online_man         = "";
    $notes              = "";
    $accessoryNote      = "";
    #item fields
    $item_type          = "EQMNT";
    $bc                 = $field[4];
    $serialNo           = $field[3] ? $field[3] : "";
    $price              = "";
    $location           = $field[2] ? $field[2] : "";
    $bCode              = ($field[1]) ? $field[1] : "";
    $dCode              = "";
    $dateAcq            = "";
    $iStatus            = "";
    $vendorName         = "";
    $assignedFName      = "";
    $assignedLName      = "";
    $po_number          = "";
    $ipAddress          = "";
    $macAddress         = "";
    $softwareKey        = "";
    $cartNumber         = "";
    $slotNumber         = "";
    $accessories        = "";
     
    print  " $curRecName  :: $rname \n";
    if ( $curRecName ne $rname ) {
        $totRec++;
        $rid = $totRec; # eq_record_idGen($dbh);
        print "saving record ID $rid  at $totItem ";
        print "record name: ", $rname, "\t";
        print "make: ",        $make,  "\t";
        print "model: ",       $model, "\t";
        print "accessories: ", $accessoryNote ;
        print "\n";
        $iid        = 1;
        $curRecName = $rname;
=item        
        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 => $warranty } );
        eq_record_add( $dbh, { rid => $rid, fId => 16, fValue => $manufacturer_url } );
        eq_record_add( $dbh, { rid => $rid, fId => 27, fValue => $supplier_url } );
        eq_record_add( $dbh, { rid => $rid, fId => 28, fValue => $online_man } );
        eq_record_add( $dbh, { rid => $rid, fId => 33, fValue => $notes} );
        eq_record_add( $dbh, { rid => $rid, fId => 36, fValue => $accessoryNote } );
=cut        
    }
    $totItem++;
=ite,    
    print "item ID : $iid : ";
    print "barcode: ",        $bc,           "\t";
    print "room: ",           $room,         "\t";
    print "serial number: ",  $serialNo,     "\t";
    print "date Purchase : ", $datePurchase, "\t";
    print "price : ",         $price,        "\t";
    print "service notes :",  $serviceNotes, "\t";
    print "service costs: ",  $serviceCosts, "\n";
    $bc = ( $bc ne "" ) ? $bc : "no_barcode_" . $rid . "_" . $iid;
=cut
    if ( eq_isBarcodeExist( $dbh, $bc ) ) {
        print "duplicate barcode", $bc, "\n";
        $bc = "DUP_" . $bc . "_" . $dupNum;
        $dupNum++;
    }
=item    
    eq_item_add(
        $dbh,
        {   rid       => $rid,
            barcode   => $bc,
            typeId    => ($item_type)?$item_type : "N/A",
            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 => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 11, sfValue => $location } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 18, sfValue => $bCode } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 26, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 31, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 38, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 39, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 40, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 41, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 42, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 43, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 44, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 45, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 46, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 47, sfValue => "" } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 48, sfValue => "" } );
=cut
    $iid++;
    $max++;

    #last if ($max > 100);
}
print " total :$totRec \t $totItem \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`;
    #my $checkSum = `uuidgen`;
    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;
}