#!/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 = "";
my $gradeList = {
    ""=>"",
    "Third Level" => "3",
    "Tenth" => "10",
    "12?" => "12",
    "Eleventh" => "11",
    "Twelfth" => "12",
    "12" => "12",
    "Ninth 05/06" => "5,6,9",
    "Third level" => "3",
    "10" => "10",
    "Fourth" => "4",
    "Ninth" => "9",
};
    while (<TMPFILE>) {
        $myLine = $_;
        $grade = " ";
        $csv->parse($myLine);
        my @fields = $csv->fields();
        $line++;
        if ($fields[0] =~ m/^ME$/ || $fields[0] =~ m/^TE$/ || $fields[0] =~ m/^KIT$/){
            $rCount++;
            ($materialType, $distributor, $budgetCategory,$districtCode,$vendorCatalogNumber) = ("","","","","");

            if ($fields[0] =~ m/^ME$/){
                $mCount++;
                $curType = "ME";
            }
            elsif($fields[0] =~ m/^TE$/) {
                $tCount++;
                $curType = "TE";    
            }
            elsif($fields[0] =~ m/^KIT$/) {
                $kCount++;
                $curType = "KIT";
            }
            if ($fields[17] !~ m/" "/ ){
                push @{$grade{$fields[17]}} , 1;
                $grade = $gradeList->{$fields[17]};
            }
            print "rid: ", $rid ;
            print " grade " , $grade ;
            print "\n";


=item
            $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,'250_a',$fields[2]);
            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]);
            tb_record_add($dbh,$rid,'500_a',$fields[16]);
            tb_record_add($dbh,$rid,'900_a',$fields[17]);
            $materialType = $fields[6];
            $distributor = $fields[8];
            $budgetCategory= $fields[9];
            $districtCode = $fields[10];
            $vendorCatalogNumber = $fields[13];
=cut            
            $iCount = 0;
        }
        elsif ($fields[0] =~ m/^COPY$/ ){ 
            $totICount++;
            if ($curType =~ m/^ME$/){
                $miCount++;
            }
            elsif ($curType =~ m/^TE$/){
                $tiCount++;
            }
            elsif ($curType =~ m/^KIT$/){
                $kiCount++;
            }
            my $emptyStr = "";
            $fields[1] =~ s/\s+//;
            my $params = {
                rid             => $rid,
                barcode         => $fields[1],
                typeId          => $curType,
                lCode           => "",
                price           => $fields[3],
                classno         => $curType,
                acqDate         => $fields[2],
                PONo            => $emptyStr,
                distributor     => $distributor,
                regionCode      => $emptyStr,
                districtCode    => $districtCode, 
                buildingCode    => $emptyStr,
                importDate      => $fields[2],
                budgetCategory  =>$budgetCategory,
                vendorCatalogNumber=>$vendorCatalogNumber,
            };
            #tb_item_add($dbh,$params);

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

    print "total record:" , $rCount, "\t total copy: ", $totICount , "\n";
    print "ME count:", $mCount , "\t ME Icount: ", $miCount, "\t TE count:", $tCount , "\t tE Icount: ", $tiCount;
    print "\t KIT count:", $kCount , "\t KIT Icount: ", $kiCount ,  "\n";
    print "\n";
    print "\n Grade : \n";
    @arr = keys(%grade);
    foreach $a (@arr) {
        print "[", $a ,"]", " \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;
}

=item
sub blahblahblah {
 if ($fields[6] !~ m/" "/ || $fields[6] ne "" ){
                push @{$materialType{$fields[6]}} , 1;
            }
            if ($fields[9] !~ m/" "/ ) {
                push @{$budgetCategory{$fields[9]}}, 1;
            }
            if ($fields[13] !~ m/" "/ ) {
                 push @{$vendorCatalogNumber{$fields[13]}}, 1;
            }
            if ($fields[17] !~ m/" "/ ){
                push @{$grade{$fields[17]}} , 1;
            }
            if ($fields[11] !~ m/" "/ ){
                push @{$stateAdoption{$fields[11]}} , 1;
            }
            if ($fields[12] !~ m/" "/ ){
                push @{$districtAdoption{$fields[12]}} , 1;
            }
            if ($fields[15] !~ m/" "/ ){
                push @{$basicSupplement{$fields[15]}} , 1;
            }
            if ($fields[18] !~ m/" "/ ){
                push @{$endAdoption{$fields[18]}} , 1;
            }
            if ($fields[19] !~ m/" "/ ){
                push @{$publisherCatalogNumber{$fields[19]}} , 1;
            }
            if ($fields[20] !~ m/" "/ ){
                push @{$userField{$fields[20]}} , 1;
            }

   print "material type : \n";
    my @arr = keys(%materialType);
    foreach my $a (@arr) {
        print "[", $a ,"]", " \n"   ;
    }
    print "\n Grade : \n";

    @arr = keys(%grade);
    foreach $a (@arr) {
        print "[", $a ,"]", " \n"   ;
    }

    print "\n Budget catetory : \n";
    @arr = keys(%budgetCategory);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }
    print "\n vendor catalog number : \n";
    @arr = keys(%vendorCatalogNumber);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }
    print "\n State Adoption \n";
    @arr = keys(%stateAdoption);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }
    print "\n District Adoption \n";
    @arr = keys(%districtAdoption);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }
    print "\n Basic Supplement\n";
    @arr = keys(%basicSupplement);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }
 print "\n endAdoption\n";
    @arr = keys(%endAdoption);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }

 print "\n publisherCatalogNumber\n";
    @arr = keys(%publisherCatalogNumber);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }
 print "\n userField\n";
    @arr = keys(%userField);
    foreach $a (@arr) {
         print "[", $a ,"]", " \n"   ;
    }




}
=cut
############################################################