#!/usr/bin/perl -w

# Use PERL5LIB instead of
#use lib "/www/opals/module";
# For example:
# PERL5LIB=/www/opals/module OPALS_CONF=/etc/opals/conf/$SITECODE \
#        $SCRIPT_NAME param1 param2 ...

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

use Opals::Context;
use Text::CSV_XS;
use Getopt::Std;
use Time::localtime;

use strict;

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 $tm = localtime;
my $dateToday = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday, $tm->hour, $tm->min, $tm->sec);

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

my $firstLine = <TMPFILE>;
my ($rCount,$iCount) = (0,0);
my ($mCount, $tCount, $kCount, $miCount, $tiCount, $kiCount, $totICount) = (0,0,0,0,0,0,0,);

my %materialType = ();
my %grade = ();
my %budgetCategory = ();
my %vendorCatalogNumber = ();
my %stateAdoption = ();
my %districtAdoption = ();
my %basicSupplement = ();
my %endAdoption = ();
my %publisherCatalogNumber = ();
my %userField = ();

my ($materialType, $distributor, $budgetCategory,$districtCode,$vendorCatalogNumber) = ("","","","","");
my $line = 1;
my @arr = ();

my $curType = "";
my $rid = 0;
my $grade = "";
    while (<TMPFILE>) {
        $myLine = $_;
        $grade = " ";
        $csv->parse($myLine);
        my @fields = $csv->fields();
        $line++;
        if ($fields[0] =~ m/^ME$/ ){
            $rCount++;
            ($materialType, $distributor, $budgetCategory,$districtCode,$vendorCatalogNumber) = ("","","","","");
            if ($fields[0] =~ m/^ME$/){
                $mCount++;
                $curType = "ME";
            }
            

            $rid = tb_record_idGen($dbh);
            print "rid: ", $rid , "\n";
            tb_record_add($dbh,$rid,'005', $dateToday);
            tb_record_add($dbh,$rid,'245_a',$fields[1]);
            tb_record_add($dbh,$rid,'100_a',$fields[3]);
            tb_record_add($dbh,$rid,'020_a',$fields[4]);
            tb_record_add($dbh,$rid,'260_c',$fields[5]);
            tb_record_add($dbh,$rid,'260_b',$fields[7]);
            $iCount = 0;
        }
        elsif ($fields[0] =~ m/^COPY$/ ){ 
            $totICount++;
            my $emptyStr = "";
            $fields[1] =~ s/\s+//;
            my $params = {
                rid             => $rid,
                barcode         => $fields[1],
                typeId          => "TBK",
                lCode           => "",
                price           => $fields[3],
                classno         => "",
                acqDate         => $fields[2],
                PONo            => $emptyStr,
                distributor     => $emptyStr,
                regionCode      => $emptyStr,
                districtCode    => $emptyStr, 
                buildingCode    => $emptyStr,
                importDate      => $fields[2],
                budgetCategory  => $emptyStr ,
                vendorCatalogNumber=> $emptyStr,
            };
            tb_item_add($dbh,$params);

        }
        else {
            print  $line, " :***[",$fields[0] , "]*** \n";
            exit;
        }
    }

    print "total record:" , $rCount, "\t total copy: ", $totICount , "\n";

  
close TMPFILE;
# Codes end.

exit 0;
################################################################################
sub tb_record_add{
    my ($dbh, $rid, $fId, $fVal) = @_;
    return -1 if ($rid eq '');
    my($sql, $sth, $id);
    $sql = "insert into tb_records set rid=?, fId=?, fVal=?";    
    $sth = $dbh->prepare($sql);
    my $result = $sth->execute($rid,$fId,$fVal);
    $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;

}
sub tb_item_add{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'rid'} eq '');
    my ($sql, $sth, $id);
        $sql = <<_SQL_;
            insert into tb_items set 
                rid=?, 
                barcode=?, 
                available=1,
                typeId=?, 
                locationCode=?, 
                price=?,
                classNumber=?,
                acquisitionDate=?,
                PONumber = ?,
                distributor = ?,
                regionCode = ?,
                districtCode = ?,
                buildingCode = ?,
                importDate=?,
                budgetCategory=?,
                vendorCatalogNumber=?
_SQL_

        $sth = $dbh->prepare($sql);
        my @param = (
                    $params->{'rid'}, 
                    $params->{'barcode'}, 
                    $params->{'typeId'},
                    $params->{'lCode'},
                    $params->{'price'},
                    $params->{'classno'},
                    $params->{'acqDate'},
                    $params->{'PONo'},
                    $params->{'distributor'},
                    $params->{'regionCode'},
                    $params->{'districtCode'},
                    $params->{'buildingCode'}, 
                    $params->{'importDate'},
                    $params->{'budgetCategory'}, 
                    $params->{'vendorCatalogNumber'},   
        );
        $sth->execute(@param) || die $params->{'rid'}, "\n";
        $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;
}

################################################################################
sub tb_record_idGen {
    
    my ($dbh) = @_;
    my $checkSum = `date +\%Y\%m\%d\%H\%M\%S\%N`;
    chomp $checkSum;

    my $sql = "insert into opl_idGen set type = 'textbook', 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('textbook',$checkSum);
    my ($id) = $sth->fetchrow_array();
    $sth->finish;
    return $id;
}


 ############################################################