#!/usr/bin/perl -w

use lib "/www/opals/module";

use strict;

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

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 $totRec = 0;
my $totItem = 0;
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 $iid;
my $dupNum = 1;
while (<TMPFILE>) {
    $myLine = $_;
    chomp;

    #print "record :" ,$myLine,"\n";
    $csv->parse($myLine);
    my @field = $csv->fields();
    my ( $rname, $make, $model, $accessories, $manufacturer_url, $online_man )
        = ( "", "", "", "", "", "" );
    my ($bc,     $room,         $serialNo,     $datePurchase,
        $price,  $serviceNotes, $serviceCosts, $building_code,
        $acq_by, $po_number,    $distributor,  $item_type,$bocesId,$deparment,$id, $boceId
    );

    #record fields
    $rname            = $field[2];
    $make             = ( $field[4] ) ? $field[4] : "";
    $model            = ( $field[5] ) ? $field[5] : "";
    $accessories      = ( $field[9] ) ? $field[9] : "";
    $manufacturer_url = ( $field[12] ) ? $field[12] : "";
    $online_man       = ( $field[13] ) ? $field[13] : "";

    #item fields
    $bc            = $field[14];
    $room          = $field[0] ? $field[0] : "";
    $serialNo      = $field[6] ? $field[6] : "";
    $datePurchase  = ( $field[7] ) ? $field[7] : "";
    $price         = ( $field[8] ) ? $field[8] : "";
    $serviceNotes  = ( $field[10] ) ? $field[10] : "";
    $serviceCosts  = ( $field[11] ) ? $field[11] : "";
    $building_code = ( $field[17] ) ? $field[17] : "";
    $acq_by        = ( $field[18] ) ? $field[18] : "";
    $po_number     = ( $field[19] ) ? $field[19] : "";
    $distributor   = ( $field[20] ) ? $field[20] : "";
    $item_type     = ( $field[15] ) ? $field[15] : "EQ-G";
    $deparment     = ( $field[1] ) ? $field[1] : "";
    $id            = ( $field[3] ) ? $field[3] : "";
    $boceId        = ( $field[16] ) ? $field[16] : "";


    if ( $curRecName ne $rname ) {
        $rid = eq_record_idGen($dbh);
        $totRec++;
        print "saving record ID $rid  ";
        print "record name: ", $rname, "\t";
        print "make: ",        $make,  "\t";
        print "model: ",       $model, "\t";
        print "accessories: ", $accessories;
        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 => 2, fValue => $model } );
        eq_record_add( $dbh, { rid => $rid, fId => 3, fValue => $accessories } );
        eq_record_add( $dbh, { rid => $rid, fId => 4, fValue => $manufacturer_url } );
        eq_record_add( $dbh, { rid => $rid, fId => 5, fValue => $online_man } );
=cut        
    }
    
    $totItem++;
    print "item ID : $iid : ";
    $bc = ( $bc ne "" ) ? $bc : "no_barcode_" . $rid . "_" . $iid;
    print "barcode: ",        "[",$bc,"]",          "\t";
    #print "item type" ,       $item_type ,   "\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";
=item    
    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,
            available => 1,
            reqField  => 1,
            iid       => $iid
        }
    );
    eq_item_add( $dbh, { rid => $rid, iid => $iid, sfId => 6, sfValue => $boceId } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 7, sfValue => $room } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 8, sfValue => $serialNo } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 9, sfValue => $price } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 10, sfValue => $building_code } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 11, sfValue => $datePurchase } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 12, sfValue => $acq_by } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 13, sfValue => $po_number } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 14, sfValue => $distributor} );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 15, sfValue => $serviceNotes } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 16, sfValue => $serviceCosts } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 17, sfValue => $deparment } );
    eq_item_add( $dbh,  { rid => $rid, iid => $iid, sfId => 18, sfValue => $id } );
=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;
}