#!/usr/bin/perl -w

use lib "/www/opals/module";

use strict;

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

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...
=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();
print "field: ", @field, "\n";
my $i = 0;
foreach my $f (@field) {
    $f =~ s/^\s+//;
    $f =~ s/\s+$//;
    print $i , ": ",  $f , "\n";
    push @headerList, $f;
    $i++;
}
my $totRec = 0;
my $totItem = 0;

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

    print " \n record :" ,$myLine;
    $csv->parse($myLine);
    my @field = $csv->fields();
    my ( $rname, $make, $model,$general_notes, $warranty_notes,$accessories, $manufacturer_url, $online_man )
        = ( "", "", "", "", "", "" );
    my ($bc,     $room, $serialNo,     $dateAcq,
        $price,  $serviceNotes, $serviceCosts, $building_code, $district_code,
        $acq_by, $po_number,    $distributor,  $item_type, $item_statusNotes
    );

    #record fields
    $rname            = $field[0];
    $rname =~ s/^\s+//;
    $rname =~ s/\s+$//;
    $make             = ($field[1])? $field[1] : "";
    $model            = ($field[2])? $field[2] : "";
    $general_notes    = ($field[3])? $field[3] : "";
    $warranty_notes   = ($field[4])? $field[4] : "";
    
    #item fields
    $bc             = $field[5];
    $item_type      = $field[6]?$field[6]:"N/A";
    $serialNo       = $field[7] ? $field[7]:"";
    $price          = $field[8] ? $field[8]:"";
    $district_code  = $field[9] ? $field[9]:"";
    $building_code  = $field[10]? $field[10]:"";
    $room           = $field[11]? $field[11]:"";
    $dateAcq        = $field[12]? $field[12]:"";
    $item_statusNotes=$field[13]? $field[13]:"";

    if ( $curRecName ne $rname ) {
        $totRec++;
        $rid = eq_record_idGen($dbh);
        print " \n saving record ID : $rid  at $totItem ";
        print "record name: ", $rname, "\t";
        print "make: ",        $make,  "\t";
        print "model: ",       $model, "\t";
        print "general note:", $general_notes, "\t";
        print "warranty note:",$warranty_notes, "\t";
        $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 => $warranty_notes } );
        eq_record_add( $dbh, { rid => $rid, fId => 33, fValue => $general_notes } );
    }
    $totItem++;
    print " \n item ID :        $iid : ";
    print "barcode: ",          $bc,           "\t";
    print "item type:",         $item_type , "\t";
    print "room: ",             $room,         "\t";
    print "serial number: ",    $serialNo,     "\t";
    print "price : ",           $price,        "\t";
    print "building code: ",    $building_code,"\t";
    print "district code: ",    $district_code,"\t";
    print "date acquired: ",    $dateAcq,"\t";
    print "item Status notes:", $item_statusNotes,"\t";
    $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    => ($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 => $price } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 11, sfValue => $room } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 18, sfValue => $building_code } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 26, sfValue => $district_code } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 31, sfValue => $dateAcq } );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 38, sfValue => $item_statusNotes } );
    $iid++;
    $max++;

    #last if ($max > 100);
}
print " \n 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;
}