#!/usr/bin/perl 

use lib "/www/opals/module";;
use Opals::Context('/etc/opals/conf/alp_alp');

use strict;
use Opals::UrlRegistry qw(
    ureg_getUrlRegistry
    ureg_getMenu
);
use Time::HiRes qw( time );
use CGI;
use DBI;
use Text::CSV_XS;
use Opals::Circulation qw(
    circ_getItemInfo
);
use Opals::Date qw(
    date_parse
    date_deltaWorkDayHour
    dateTime_parse
);
use POSIX qw(
    ceil
    floor
);
use Digest::SHA qw(
    sha1_base64
);
use MARC::File::USMARC;
use MARC::File::XML;


use Opals::Transaction qw(
    trans_chargeOverdue
    trans_chargeLost
);
use JSON;
my $dbh = Opals::Context->dbh();
my $dbh = Opals::Context->dbh();
my $zRoot   = Opals::Context->config('zRoot');
my $zPort   = Opals::Context->config('zPort');
my $zDatabase = Opals::Context->config('zDatabase');



my $csv = Text::CSV_XS->new({ binary => 1 });
my $configFile =@ARGV[0];
if(!defined $configFile || !-f $configFile){
    print  "ERROR: Missing config file\n";
    exit 0;
}

my  $config =loadConfig($configFile);
my $errDir=$config->{'errReportDir'};
if(!$errDir || !-d $errDir){
    $errDir= system("mkdir /tmp/m3OpalsConvertErr");
    system("chmod 664 /tmp/m3OpalsConvertErr");
}
open TRANS_ERR_LOAN , ">$errDir/trans-err-loan.csv";
open TRANS_ERR_OD   , ">$errDir/trans-err-fine.csv";
open TRANS_ERR_LOST , ">$errDir/trans-err-charge.csv";
open TRANS_ERR_RESERVE, ">$errDir/trans-err-reserve.csv";
my($err_loan,$err_fine,$err_charge,$err_reserve)=(0,0,0,0);
my $typeMap=getUserTypeMap();
init_tables($dbh);
createM3rid_opalsRid_map($dbh);
convert_patron($dbh,$config->{'patronMarcFile'});
convert_trans($dbh,$config);
convert_reserve_trans($dbh,$config->{'reserveTransCsv'});
convert_circStats_item($dbh,$config->{'circStatsCsv'});
close TRANS_ERR_LOAN;
close TRANS_ERR_OD;
close TRANS_ERR_LOST;
print "Conversion completed.\n";
if(($err_loan + $err_fine + $err_charge +$err_reserve)>0 ){
    print "There are errors in the conversion process.\n";
    print "    loan:\t$err_loan\t\n" if($err_loan>0);
    print "    fine:\t$err_fine\n" if($err_fine>0);
    print "    lost:\t$err_charge\n" if($err_charge>0);
    print "    reserve:\t$err_reserve\n" if($err_reserve>0);
    print "For more info, please check the files in folder $errDir.\n\n"
}; 
############################################################
sub init_tables{
    my($dbh)=@_;
    print "initializing tables...\n";
    $dbh->do("truncate table opl_loan");
    $dbh->do("truncate table opl_odl");
    $dbh->do("truncate table opl_charge");
    $dbh->do("truncate table opl_transaction");
    $dbh->do("truncate table opl_paymentReceipt");
    $dbh->do("truncate table opl_reserve");
    $dbh->do("truncate table opl_hold");
    $dbh->do("truncate table opl_requestReserve");
}
############################################################
sub createM3rid_opalsRid_map{
    my($dbh)=@_;
    $dbh->do("DROP TABLE IF EXISTS `opals_m3_rid`");
    $dbh->do(<<_SQL_);
CREATE TABLE  opals_m3_rid(
  rid int(10) unsigned NOT NULL DEFAULT '0',
  m3Rid varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (rid),
  key m3rid(m3Rid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
_SQL_


    my $sth=$dbh->prepare("select distinct rid from opl_item order by rid ");
    $sth->execute();
    while(my($rid)=$sth->fetchrow_array){
        my $xml=getMarcXml($rid);
        if($xml && $xml ne "" && $xml =~ m/<controlfield tag=\"009\">(.*)<\/controlfield>/){
            $dbh->do("insert into opals_m3_rid set rid=?,m3Rid=?",undef,$rid,$1);
        }
    }
}

############################################################
sub convert_loan_trans{
    my($dbh,$fields)=@_;
    my $fieldMap={
    itemBc=>6,
    userBc=>4,
    dateLoan=>1,
    dateDue=>2};

    if(scalar(@$fields)>=6){
        my $userbarcode=@$fields[$fieldMap->{'userBc'}];
        my $barcode=@$fields[$fieldMap->{'itemBc'}];
        my $dateLoan=parseDateTime(@$fields[$fieldMap->{'dateLoan'}]);
        my $dateDue=parseDateTime(@$fields[$fieldMap->{'dateDue'}]);
        $dateDue=~ s/22:00:00$/23:59:59/g;
        my($uid)=$dbh->selectrow_array("select uid from opl_user where userbarcode=?",undef,$userbarcode);
        my($rid)=$dbh->selectrow_array("select rid from opl_item where barcode=?",undef,@$fields[$fieldMap->{'itemBc'}]);
        if($uid && $rid){
            $dbh->do("insert into opl_loan set uid=?,barcode=?,dateLoan=?,dateDue=?",undef,$uid,$barcode,$dateLoan,$dateDue);
        }
        else{ #report not invalid transactions
            my $n=scalar(@$fields);
            if(!defined $uid){
                @$fields[$n-1]="user barcode does not exist($userbarcode)";
            }
            else{
                @$fields[$n-1]="item barcode does not exist($barcode)";
            }
            $err_loan++;
            print TRANS_ERR_LOAN join(",",@$fields), "\n";
        }
    }

}
############################################################
sub convert_trans{
    my($dbh,$config)=@_;

    my @trans=();
    my @transCsv=readTransFile($config->{'loanTransCsv'});
    print TRANS_ERR_LOAN @transCsv[0] ,",ERROR\n";
    for (my $i=1; $i<scalar(@transCsv); $i++){
        $csv->parse(@transCsv[$i]);
        my @fields = $csv->fields();
        if(scalar(@fields)>=6){
            push @fields,"LOAN";
            push @trans,\@fields;
        }
    }

    @transCsv=readTransFile($config->{'fineTransCsv'});
    print TRANS_ERR_OD @transCsv[0] ,",ERROR\n";
    for (my $i=1; $i<scalar(@transCsv); $i++){
        $csv->parse(@transCsv[$i]);
        my @fields = $csv->fields();
        if(scalar(@fields)>=7){
            push @fields,"OD";
            push @trans,\@fields;
        }
    }
    @transCsv=readTransFile($config->{'chargeTransCsv'});
    print TRANS_ERR_LOST @transCsv[0] ,",ERROR\n";
    for (my $i=1; $i<scalar(@transCsv); $i++){
        $csv->parse(@transCsv[$i]);
        my @fields = $csv->fields();
        if(scalar(@fields)>=7){
            push @fields,"LOST";
            push @trans,\@fields;
        }
    }
    
    @trans=sort { @$a[2] cmp @$b[2]} @trans;
    print "Converting loan/charge/fine transactions...\n";
    foreach my $s(@trans){
        my $n=scalar(@$s);
        my $type=@$s[$n-1];
        if($type eq 'LOAN'){
            convert_loan_trans($dbh,$s);
        }
        elsif($type eq 'OD'){
            convert_odFine_trans($dbh,$s);
        }
        elsif($type eq 'LOST'){
            conver_lostCharge_trans($dbh,$s);
        }
    }
}
############################################################
sub convert_reserve_trans{
    my($dbh,$csvFile)=@_;
    my $fieldMap={
    userBc=>4,
    expiryDate=>2,
    requestDate=>1,
    m3_rid=>5,
    title=>6
    };
    print "Converting reserve transactions...\n";
    my @trans=readTransFile($csvFile);
    print TRANS_ERR_RESERVE @trans[0] ,",ERROR\n";
    my $i=0;
    for (my $i=1; $i<scalar(@trans); $i++){    
        $csv->parse(@trans[$i]);
        my @fields = $csv->fields();
        if(scalar(@fields)>=7){
            my $userbarcode=@fields[$fieldMap->{'userBc'}];
            my $m3_rid=@fields[$fieldMap->{'m3_rid'}];
            my $requestDate=parseDateTime(@fields[$fieldMap->{'requestDate'}]);
            my $expiryDate=parseDateTime(@fields[$fieldMap->{'expiryDate'}]);
            

            my($uid,$email,$phone)=$dbh->selectrow_array("select uid,email,phone from opl_user where userbarcode=?",undef,$userbarcode);
            my($rid)=$dbh->selectrow_array("select rid from opals_m3_rid where m3Rid=?",undef,@fields[$fieldMap->{'m3_rid'}]);
            if($uid && $rid){
                #print "$rid,$uid,$requestDate,$expiryDate,$email,$phone\n";
                $dbh->do("insert into opl_requestReserve 
                          set rid=?,uid=?,dateRequest=?, dateExpiry=?, email=?,phone=?, numCopyRequest=1",undef,$rid,$uid,$requestDate,$expiryDate,$email,$phone);
            }
            else{ #report not invalid transactions
                $err_reserve++;
                print TRANS_ERR_RESERVE  join(",",@fields);
                if(!defined $uid){
                    print TRANS_ERR_RESERVE ",user barcode does not exist($userbarcode)\n"
                }
                else{
                    print TRANS_ERR_RESERVE ",title does not exist\n";
                }

            }
        }
    }

}
############################################################
sub convert_odFine_trans{
    my($dbh,$fields)=@_;
    my $fieldMap={
    userBc=>4,
    dateDue=>2,
    dateReturn=>1,
    m3_rid=>5,
    barcode=>6,
    charge=>7
    };
    if(scalar(@$fields)>=7){
        my($userBc,$bc,$dateDue,$dateReturn,$amount)=(
                @$fields[$fieldMap->{'userBc'}],
                @$fields[$fieldMap->{'barcode'}],
                parseDateTime(@$fields[$fieldMap->{'dateDue'}]),
                parseDateTime(@$fields[$fieldMap->{'dateReturn'}]),
                @$fields[$fieldMap->{'charge'}]);
        $dateDue =~ s/22:00:00/23:59:59/g;
        my($uid)=$dbh->selectrow_array("select uid from opl_user where userbarcode=?",undef,$userBc);
        my($itemBc)=$dbh->selectrow_array("select barcode from opl_item where barcode=?",undef,$bc);
        if(defined $uid && $uid>0 && defined $itemBc){
            #print "convert_odFine_trans :: $userBc,$bc,$dateDue,$dateReturn,$amount\n";
            #get date loan based on userbarcode, holding barcode and dateDue
            my $dateLoan=getDateLoan($dbh,$userBc,$bc,$dateDue);
            my $itemInfo = circ_getItemInfo($dbh, $bc, $uid);
            my $fineRate= (defined $itemInfo->{'fine'} && $itemInfo->{'fine'} > 0)?$itemInfo->{'fine'}:0;

            #create loan transaction
            $dbh->do("insert into opl_loan set uid=?,barcode=?,dateLoan=?,dateDue=?,dateReturn=?,fineRate=?,maxRenewal=1",undef,
                        $uid,$bc,$dateLoan,$dateDue,$dateReturn,$fineRate);
            my $loanid = $dbh->{'mysql_insertid'};

            #create odl transaction
            my ($mumOfDaysOD, $mumOfHoursOD, $mumOfMinuteOD) =date_deltaWorkDayHour(dateTime_parse($dateDue), dateTime_parse($dateReturn));
            $mumOfHoursOD=0;
            $dbh->do("insert into opl_odl set idloan=?, type='overdue',days_overdue=?, hours_overdue=?, responsible=1,note='Back dated trans', ondate=?",undef,
                            $loanid,$mumOfDaysOD+1,$mumOfHoursOD,$dateReturn);
            #create OD fine transaction
            my $odl_id = $dbh->{'mysql_insertid'};
            trans_chargeOverdue($dbh,1,$uid,$odl_id,$fineRate,$mumOfDaysOD+1,0,"backdated trans",$amount,$dateReturn);
        }
        else{
            my $n=scalar(@$fields);
            if(!defined $uid){
                @$fields[$n-1]="user barcode does not exist($userBc)";
            }
            else{
                @$fields[$n-1]="Item barcode does not exist($bc)";
            }
            print TRANS_ERR_OD join(",",@$fields), "\n";
            $err_fine++;
        }
        
    }

    
}
############################################################

sub conver_lostCharge_trans{
    my($dbh,$fields)=@_;
    my $fieldMap={
    userBc=>4,
    dateDue=>2,
    dateReturn=>1,
    m3_rid=>5,
    barcode=>6,
    charge=>7
    };
     if(scalar(@$fields)>=7){
        my($userBc,$bc,$dateDue,$dateReturn,$amount)=(
                @$fields[$fieldMap->{'userBc'}],
                @$fields[$fieldMap->{'barcode'}],
                parseDateTime(@$fields[$fieldMap->{'dateDue'}]),
                parseDateTime(@$fields[$fieldMap->{'dateReturn'}]),
                @$fields[$fieldMap->{'charge'}]);
        $dateDue =~ s/22:00:00/23:59:59/g;
        my($uid)=$dbh->selectrow_array("select uid from opl_user where userbarcode=?",undef,$userBc);
        if(defined $uid && $uid>0){
            #print "conver_lostCharge_trans :: $userBc,$bc,$dateDue,$dateReturn,$amount\n";
            #get date loan based on userbarcode, holding barcode and dateDue
            my $dateLoan=getDateLoan($dbh,$userBc,$bc,$dateDue);
            my $itemInfo = circ_getItemInfo($dbh, $bc, $uid);
            my $fineRate= (defined $itemInfo->{'fine'} && $itemInfo->{'fine'} > 0)?$itemInfo->{'fine'}:0;

            #create loan transaction
            $dbh->do("insert into opl_loan set uid=?,barcode=?,dateLoan=?,dateDue=?,dateReturn=?,fineRate=?,maxRenewal=1",undef,
                        $uid,$bc,$dateLoan,$dateDue,$dateReturn,$fineRate);
            my $loanid = $dbh->{'mysql_insertid'};

            #create odl transaction
            my ($mumOfDaysOD, $mumOfHoursOD, $mumOfMinuteOD) =date_deltaWorkDayHour(dateTime_parse($dateDue), dateTime_parse($dateReturn));
            $mumOfHoursOD=0;
            $dbh->do("insert into opl_odl set idloan=?, type='lost',days_overdue=?, hours_overdue=?, responsible=1,note='Back dated trans', ondate=?",undef,
                            $loanid,$mumOfDaysOD+1,$mumOfHoursOD,$dateReturn);
            #create OD fine transaction
            my $odl_id = $dbh->{'mysql_insertid'};
            trans_chargeLost($dbh,1,$uid,$odl_id,$fineRate,0,0,"backdated trans",$amount,$dateReturn);
        }
        else{
            my $n=scalar(@$fields);
            @$fields[$n-1]="user barcode does not exist($userBc)";
            print TRANS_ERR_LOST join(",",@$fields), "\n";
            $err_charge++;
        }
        
    }

    
}

############################################################
sub getDateLoan{
    my($dbh,$userBc,$bc,$dateDue)=@_;
    my($p)=$dbh->selectrow_array("select loanPeriod from opl_user u inner join opl_itemTypeParam t on t.userTypeId=u.categorycode inner join opl_item i on i.typeId=t.itemTypeId where i.barcode=? && u.userbarcode=?",undef,$bc,$userBc);
    my $tUnit="day";
    if($p>24){
        $p=$p/24;
    }
    else{
        $tUnit="hour";
    }
    print "$userBc :: $bc :: $dateDue\n";
    my ($dateLoan)=$dbh->selectrow_array("select date_sub(?,interval $p $tUnit)",undef,$dateDue);
    return $dateLoan?$dateLoan:$dateDue;
    
}
############################################################
sub parseDateTime{
    my ($dtStr)=@_;
    my $ret="";
    if($dtStr =~ m/^\d{12}/){
       $ret=sprintf "%s-%s-%s %s:%s:%s",substr($dtStr,0,4),substr($dtStr,4,2),substr($dtStr,6,2),substr($dtStr,8,2),substr($dtStr,10,2),substr($dtStr,12,2);
    }
    return $ret;
}
############################################################
sub readTransFile{
    my ($csvFile)=@_;
    my @trans=();
    if(-f $csvFile){
        open IN,"<$csvFile";
        while(<IN>){
            chomp;
            push @trans,$_;
        }
    }
    return @trans;

}
############################################################
sub getMarcXml{
    my ($rid)=@_;
    my $dir  = "$zRoot/$zPort/record/$zDatabase/" . ceil($rid/1000);
    my $xml = '';
    if (-f "$dir/$rid.xml") {
        open  RECORD, "<$dir/$rid.xml";
        while (<RECORD>) {
            $xml .= $_;
        }
        close RECORD;
    }
    return $xml;
}


############################################################
sub convert_patron{
    my($dbh,$pMarcFile)=@_;
    my @fArr=();
    my $fieldDef_user=getUserFieldMap();
    my $fieldDef_circStats=getPatronCircStatsMap();
    foreach my $f(@$fieldDef_user){
        push @fArr,$f->{'name'} ."=?";
    }
    push @fArr,"circStats=?";
    my $fList= join ",",@fArr;
    my $sql="insert into opl_user set $fList";
    my $sql_update="update opl_user set $fList where uid=?";
    
    my $marcFile = MARC::File::USMARC->in($pMarcFile);
    print "importing patrons...\n";
    while (my $rec = $marcFile->next) {
        my @fValArr=();
        my $userbarcode="";
        #get patron demographic info
        foreach my $fd (@$fieldDef_user) {
            my $sfval =$fd->{'tag'} gt "009"? $rec->subfield($fd->{'tag'}, $fd->{'sfcode'}):$rec->field($fd->{'tag'})->data();
            ($sfval) || ($sfval = '');
            if($fd->{'name'} eq 'password' && $sfval ne ''){
                $sfval=sha1_base64($sfval);
            }
            if($fd->{'name'} eq 'categorycode' && $sfval ne ''){
                $sfval=getUserType($sfval);
            }
            if($fd->{'name'} eq 'gender' && $sfval ne ''){
                $sfval= ($sfval=~ m/^f/i)?0:1;
            }
            if($fd->{'name'} eq 'userbarcode'){
                $userbarcode=$sfval;
            }
            push @fValArr,$sfval;
        }
        # get patron loan stats/count
        my $circStats=getPatronCircStats($rec,$fieldDef_circStats);
        push  @fValArr,to_json($circStats);
        my($uid)=$dbh->selectrow_array("select uid from opl_user where userbarcode=?",undef,$userbarcode);
        if(!defined $uid){
            $dbh->do($sql,undef,@fValArr);
        }
        else{
            $dbh->do($sql_update,undef,@fValArr,$uid);
        }
    }
    $dbh->do("update opl_user u inner join opl_category c on u.categorycode=c.catid  set u.permissions=c.defaultPerm where u.permissions =''");
    $marcFile->close;
    
}
############################################################
sub getPatronCircStats{
    my($rec,$fieldDef_circStats)=@_;
    my $circStats={};
    foreach my $fd (@$fieldDef_circStats) {
       my $y= $fd->{'year'};
       my $c= $rec->subfield($fd->{'tag'}, $fd->{'sfcode'});
       $circStats->{$y}=$c if(defined$c && $c ne '');
    }
    return $circStats;

}
############################################################
sub getUserType{
    my($typeCode)=@_;
    if(defined $typeMap->{$typeCode}){
        if(!defined $typeMap->{$typeCode}->{'code'}){
            $dbh->do("insert into  opl_category set cattype=?, catname=?",undef,$typeCode,$typeMap->{$typeCode}->{'name'});
            $typeMap->{$typeCode}->{'code'}=$dbh->{'mysql_insertid'};
        }
    }
    else{
        $dbh->do("insert into  opl_category set cattype=?, catname=?",undef,$typeCode,$typeCode);

         $typeMap->{$typeCode}->{'name'}=$typeCode;
         $typeMap->{$typeCode}->{'code'}=$dbh->{'mysql_insertid'};
    }
    return $typeMap->{$typeCode}->{'code'};
}

############################################################
=item
[995#a] Loan Count 2005
[995#b] Loan Count 2006
[995#c] Loan Count 2007
[995#d] Loan Count 2008
[995#e] Loan Count 2009
[995#f] Loan Count 2010
[995#g] Loan Count 2011
[995#h] Loan Count 2012
[995#i] Loan Count 2013
=cut
############################################################
sub getPatronCircStatsMap{
    return [
        {year=>'2005',      tag=>'995', sfcode=>'a'},
        {year=>'2006',      tag=>'995', sfcode=>'b'},
        {year=>'2007',      tag=>'995', sfcode=>'c'},
        {year=>'2008',      tag=>'995', sfcode=>'d'},
        {year=>'2009',      tag=>'995', sfcode=>'e'},
        {year=>'2010',      tag=>'995', sfcode=>'f'},
        {year=>'2011',      tag=>'995', sfcode=>'g'},
        {year=>'2012',      tag=>'995', sfcode=>'h'},
        {year=>'2013',      tag=>'995', sfcode=>'i'},
        {year=>'2014-2016', tag=>'993', sfcode=>'a'}
    ];

}

############################################################
sub getUserFieldMap{
    return [
        {name => 'firstname',       tag => '100', sfcode  => 'a'},
        {name => 'middlename',      tag => '100', sfcode  => 'b' },
        {name => 'lastname',        tag => '100', sfcode  => 'c' },
        {name => 'nickname',        tag => '100', sfcode  => 'n' },
        {name => 'birthday',        tag => '100', sfcode  => 'k' },
        {name => 'gender',          tag => '100', sfcode  => 'f' },
        {name => 'identification',  tag => '100', sfcode  => 's' },

        {name => 'addrLine1',       tag => '110', sfcode  => 'a' },
        {name => 'addrLine2',       tag => '110', sfcode  => 'n' },
        {name => 'city',            tag => '110', sfcode  => 'b' },
        {name => 'state',           tag => '110', sfcode  => 'c' },
        {name => 'country',         tag => '110', sfcode  => 'd' },
        {name => 'zip',             tag => '110', sfcode  => 'e' },
        {name => 'neighborhood',    tag => '110', sfcode  => 'f' },
        {name => 'residency',       tag => '110', sfcode  => 'h' },
        {name => 'phone',           tag => '110', sfcode  => 'k' },
        {name => 'fax',             tag => '110', sfcode  => 'l' },
        {name => 'email',           tag => '110', sfcode  => 'm' },



        
        {name => 'addrLine1_alt',   tag => '111', sfcode  => 'a' },
        {name => 'city_alt',        tag => '111', sfcode  => 'b' },
        {name => 'state_alt',       tag => '111', sfcode  => 'c' },
        {name => 'country_alt',     tag => '111', sfcode  => 'd' },
        {name => 'zip_alt',         tag => '111', sfcode  => 'e' },
        {name => 'neighborhood_alt',tag => '111', sfcode  => 'f' },
     
        {name => 'issueBy',      tag => '852', sfcode  => 'a' },
        #{name => 'homeroom',        tag => '852', sfcode  => 'b' },
        {name => 'categorycode',    tag => '852', sfcode  => 'b' },
        {name => 'teacher',         tag => '852', sfcode  => 'c' },
        {name => 'pref_lang',       tag => '852', sfcode  => 'f' },
        {name => 'grade',           tag => '852', sfcode  => 'g' },
        {name => 'yeargraduation',  tag => '852', sfcode  => 'h' },
        {name => 'username',        tag => '852', sfcode  => 'o' },
        {name => 'userbarcode',     tag => '852', sfcode  => 'p' },
        {name => 'password',        tag => '852', sfcode  => 'q' },
        {name => 'notes',           tag => '852', sfcode  => 'z' },

        {name => 'created',         tag => '008'},
        {name => 'modified',        tag => '005'},
        {name => 'expirydate',      tag => '853', sfcode  => 'e' }

        ];
}
############################################################
sub getGuardianFieldMap{

   return [ 
        {name => 'addrLine1',       tag => '110', sfcode  => 'a' },
        {name => 'city',            tag => '110', sfcode  => 'b' },
        {name => 'state',           tag => '110', sfcode  => 'c' },
        {name => 'country',         tag => '110', sfcode  => 'd' },
        {name => 'zip',             tag => '110', sfcode  => 'e' },
        {name => 'neighborhood',    tag => '110', sfcode  => 'f' },
        {name => 'email',           tag => '120', sfcode  => 'c' },
        {name => 'phone',           tag => '120', sfcode  => 'a' },
        {name => 'fax',             tag => '120', sfcode  => 'b' }
    ]
}
############################################################
=item
[996#a] 2000-2004 loans
[996#b] 2005 loans
[996#c] 2006 loans
[996#d] 2007 loans
[996#e] 2008 loans
[996#f] 2009 loans
[996#g] 2010 loans
[996#h] 2011 loans
[996#i] 2012 loans
[996#j] 2013 loans

Field 993#a stores the current year (2014-2016) loan count.
=cut

sub convert_circStats_item{
    my($dbh,$csvFile)=@_;
    my $fiedMap={};
    my $fieldCfg={
            "852#p"=>"bc",
            "996#a"=>"2000-2004",
            "996#b"=>"2005",
            "996#c"=>"2006",
            "996#d"=>"2007",
            "996#e"=>"2008",
            "996#f"=>"2009",
            "996#g"=>"2010",
            "996#h"=>"2011",
            "996#i"=>"2012",
            "996#j"=>"2013",
            "993#a"=>"2014-2016"      
    };
    print "Converting loan stats...\n";
    my @trans=readTransFile($csvFile);
    print TRANS_ERR_LOAN_STATS @trans[0] ,",ERROR\n";
    $csv->parse(@trans[$0]);
    my @fields = $csv->fields();
    for(my $i=0;$i<scalar(@fields);$i++){
        my $c=@fields[$i];
        if(defined $fieldCfg->{$c}){
            $fiedMap->{$fieldCfg->{$c}}=$i;
        }
    }
    for (my $i=1; $i<scalar(@trans); $i++){ 
    #for (my $i=1; $i<4; $i++){ 
         $csv->parse(@trans[$i]);
         @fields = $csv->fields();
         my $stats={};
         my $bc=undef;
         while( my ($f,$index) = each %$fiedMap){
             my $v=@fields[$index];
             if($f eq 'bc'){
                $bc=$v;
             }
             elsif($v ne ''){
                 $stats->{$f}=$v;
             }
         }
         updateCurLoanCount($dbh,$stats,$bc);
         if(defined $bc){
             $dbh->do("update opl_item set circStats=? where barcode=?",undef,to_json($stats),$bc)
         }
         #print to_json($stats),"\n";
    }



}
############################################################
sub updateCurLoanCount{
    my($dbh,$stats,$bc) =@_;
    my $sth=$dbh->prepare("select idloan,uid,dateLoan from opl_loan where barcode= ?");
    $sth->execute($bc);
    
    while( my ($idloan,$uid,$dateLoan)=$sth->fetchrow_array){
        my $y=substr($dateLoan,0,4);
        if(defined $stats->{$y}){
             $stats->{$y} +=1;
        }
        else{
             $stats->{$y}=1;
        }
        #update user loan count
        my $uStats={};
        my ($s)=$dbh->selectrow_array("select circStats from opl_user where uid=?",undef,$uid);
        if($s && $s ne "{}"){
            $uStats=decode_json($s);
        }
        if(defined $uStats->{$y}){
            $uStats->{$y} +=1;
        }
        else{
            $uStats->{$y} =1;
        }
        $uStats->{"L"} =[] if(!defined $uStats->{"L"});
        push @{$uStats->{"L"}},$idloan;
        $dbh->do("update opl_user set circStats=? where uid=?",undef,to_json($uStats),$uid);


    }
}
############################################################
sub getUserTypeMap{
    my $userTypeMap= {
        "0000000002"=>{name=>"Adult (18+)"},
        "0000000003"=>{name=>"Administrators"},
        "0000000004"=>{name=>"Librarians"},
        "0000000012"=>{name=>"Library staff"},
        "0000000013"=>{name=>"ILL Institution"},
        "0000000015"=>{name=>"Outreach patron"},
        "0000000016"=>{name=>"Restricted status patron"},
        "0000000017"=>{name=>"Inactive patron"},
        "0000000019"=>{name=>"Temporary patron"},
        "0000000020"=>{name=>"Suspended patron"},
        "0000000026"=>{name=>"Unverified"},
        "0000000033"=>{name=>"Virtual service"},
        "0000000042"=>{name=>"SILS Lakeland region patron"},
        "0000000043"=>{name=>"Library volunteer"},
        "0000000044"=>{name=>"Child (0-13)"},
        "0000000045"=>{name=>"Outreach institution"},
        "0000000048"=>{name=>"Print disabled"},
        "0000000049"=>{name=>"Institution"},
        "0000000051"=>{name=>"Non-resident patron - Adult (18+)"},
        "0000000052"=>{name=>"Provincial library community"},
        "0000000053"=>{name=>"System functions"},
        "0000000054"=>{name=>"Young adult (14-17)"},
        "0000000055"=>{name=>"Non-resident patron - Young adult (14-17)"},
        "0000000056"=>{name=>"Non resident patron - Child (0-13)"},
        "0000000040"=>{name=>"New Special Reserve Group1"}
    };
    foreach my $cattype(keys %$userTypeMap){
        my ($catid)=$dbh->selectrow_array("select catid from opl_category where cattype=?",undef,$cattype);
        if(defined $catid && $catid){
            $userTypeMap->{$cattype}->{'code'}=$catid;
        }
    }
    return $userTypeMap;
}

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


sub loadConfig {
    my ($configFile) = @_;
#    print "Enter the config filename of Opals: ";
#    $configFile = <STDIN>;
    my $config = {};

    open CONF, $configFile || die "Cannot open file $configFile";
    while (<CONF>) {
        chomp;
        s/#.*//;                # remove comments
        next if /^\s*$/;        # ignore blank lines

        if (/^\s*(\w+)\s*=\s*(.*?)\s*$/) {
            $config->{$1} = $2;
        }
    }
    close CONF;

    return $config;
} 

exit 0;