#!/usr/bin/perl 

use lib "/www/opals/module";
use Opals::Context("/etc/opals/conf/sc_lc");
#use Opals::Context("/etc/opals/conf/sccs_hbhs");

use strict;
use DBI;
use Getopt::Std;
use MARC::Record;
use Time::localtime;
use Opals::MarcXml qw(
    mxml_recordPath
    mxml_del_rec_holding
);

use Opals::Tb_Record qw(

    tb_record_idGen
    tb_record_add
    tb_item_add

);

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 @orCond = (
        {3 => 'textbook%' }   ,
        {k => 'textbook%' }
    );
    my @andCond = ();
    my $ridList = libCollection_getRIdList($dbh, \@orCond);

    my $count = 0;
    my ($iCount,$tCount,$oCount,$count_move,$count_notmove,$loanCount,$tbLoanCount);
    my ($iCount_total,$tCount_total,$oCount_total,$count_move_total,$count_notmove_total,$count_loan_total,$count_tbloan_total) = (0,0,0,0,0,0,0);
    my ($case1,$case2,$case3,$case4, $tcase1,$tcase2,$tcase3,$tcase4) = (0,0,0,0,0,0,0,0);
    foreach my $rid (@{$ridList}){
        ($case1,$case2,$case3,$case4) = (0,0,0,0);
        $count ++;
        #last if ($count > 10);
        
        print "--------------------------------------------------------\n";
        print "RId    Holdings Textbook Others  Moved   Unmoved Loaned  TBLoaned  \n";
        ($iCount,$tCount,$oCount,$count_move,$count_notmove)= (0,0,0,0,0);
       ($iCount,$tCount,$oCount,$count_move,$count_notmove,$loanCount,$tbLoanCount,$case1,$case2,$case3,$case4) = do_transferring($dbh,$rid, \@orCond);
        $iCount_total += $iCount;
        $tCount_total += $tCount;
        $oCount_total += $oCount;
        $count_move_total += $count_move;
        $count_notmove_total += $count_notmove;
        $count_loan_total += $loanCount;
        $count_tbloan_total += $tbLoanCount;
        $tcase1 += $case1;
        $tcase2 += $case2;
        $tcase3 += $case3;
        $tcase4 += $case4;    

        print "\n--------------------------------------------------------\n\n";
    }
    $count --;
    print "\n";
    print <<L2;
    case1: move = no, all holdings are textbooks
    case2: move = no, holdings are mixed of textbooks and other types
    case3: move = yes, move more holdings to TB Collection, remove non-textbook holding(s) are required
    case4: move = yes, move less holdings to TB Collection, move textbook holding(s) are required
======================================================================    
TotRec TotHlds    Tbk     Others  Moved   Unmoved    Loans TBLoans   Case1   Case2   Case3   Case4
========================================================================
L2
    printf '%-8s %-8s %-8s %-8s %-8s %-8s %-8s  %-8s %-8s %-8s %-8s %-8s  %-8s',
        $count ,$iCount_total,$tCount_total,$oCount_total,$count_move_total,$count_notmove_total,$count_loan_total, $count_tbloan_total, $tcase1,$tcase2,$tcase3,$tcase4;
    print "\n";
# Codes end.

exit 0;
################################################################################
sub libCollection_getRIdList {
    my ($dbh, $orCond) = @_;
    my @rIdList = ();
    my @bindVal = ();
    my $sql = "select distinct i.rid from opl_item i inner join opl_itemInfo f using(barcode) 
                    inner join opl_marcRecord m on m.rid=i.rid " ;

    my $sql_where = "";       
    foreach my $c (@{$orCond}){
        $sql_where .= " || " if ( $sql_where ne "");
        foreach my $sfc (keys %{$c}){
            $sql_where .= " ( f.sf852Code = ? && f.sf852Data  like ?  ) " ;
            push (@bindVal, ($sfc,$c->{$sfc}));
        }
    }
                    

    my $sql_order = ' order by i.rid'; 
    $sql .=  ' where ' . $sql_where . $sql_order;

    my $sth = $dbh->prepare($sql);
    $sth->execute(@bindVal);
    while (my $rid = $sth->fetchrow_array){
            push @rIdList, $rid;
    }
    $sth->finish;
    return \@rIdList;
}



sub do_transferring {
    my ($dbh, $rid ,$orCond) = @_;
    my @bindVal = ();
    my $sql_lib = "select i.barcode, f.sf852Code , f.sf852Data as typeId from opl_item i inner join opl_itemInfo f using(barcode) where rid = ? && i.barcode not regexp '^\_\_\_' " ;
    my $sql_where = "";       
    foreach my $c (@{$orCond}){
        $sql_where .= " || " if ( $sql_where ne "");
        foreach my $sfc (keys %{$c}){
            $sql_where .= " ( f.sf852Code = ? && f.sf852Data  like ?  ) " ;
            push (@bindVal, ($sfc,$c->{$sfc}));
        }
    }
    $sql_lib .=  " && (" . $sql_where . " )" ;
    $sql_lib .= " group by i.barcode ";
   
    my $sql_tbk = "select count(*) from tb_items where barcode = ?";
    my $sql_iCount = "select count(*) from opl_item where rid = ? && barcode not regexp '^\_\_\_' " ;
    my $sql_hasLoanWtLib = "select count(*) from opl_loan where barcode = ? && dateReturn is null";
    my $sql_hasLoanTbkWtLib = "select count(*) from opl_loan inner join opl_item using (barcode) where barcode = ? && dateReturn is null ";
    my $sql_tbrid = "select rid from tb_items where barcode = ?" ; 

    my $sth_lib = $dbh->prepare($sql_lib);
    my $sth_iCount = $dbh->prepare($sql_iCount) ;
    my $sth_hasLoanWtLib = $dbh->prepare($sql_hasLoanWtLib);
    my $sth_hasLoanTbkWtLib = $dbh->prepare($sql_hasLoanTbkWtLib);
    my $sth_tbrid = $dbh->prepare($sql_tbrid);
    my $sth_tbk = $dbh->prepare($sql_tbk);

    my ($case1,$case2,$case3,$case4) = (0,0,0,0);
    
    $sth_iCount->execute($rid);
    
    my $iCount = $sth_iCount->fetchrow_array;
    my $tCount = 0;
    my $loanCount = 0;
    my $loanTbkCount = 0;
    my @tbkLoanList = ();
    
    $sth_lib->execute($rid,@bindVal );
    my ($count_move, $count_notmove) = (0,0);
    my $tbrid = 0;
    my (%hTB , %hOthers);
    while (my ($barcode, $sf852Code,$typeId) = $sth_lib->fetchrow_array){
        if ($typeId =~ m/textbook|textbooks/i){
            $hTB{$rid}{$barcode} = 1;
            $tCount++;
        }
        else{
            $hOthers{$rid}{$barcode} = 1;
        }
        $sth_tbk->execute($barcode);
        $sth_hasLoanWtLib->execute($barcode);
        $sth_hasLoanTbkWtLib->execute($barcode);
        my $count_tbk = $sth_tbk->fetchrow_array;



        my $hasLoan = $sth_hasLoanWtLib->fetchrow_array;
        my $hasLoanTbk = $sth_hasLoanTbkWtLib->fetchrow_array;
        if ($hasLoan && $hasLoan > 0){
            $loanCount++;
        }
        if ($hasLoanTbk && $hasLoanTbk > 0 ){
            $loanTbkCount++;
            push @tbkLoanList, $barcode;
        }
        if ($count_tbk && $count_tbk > 0){
            $count_move++;
            if (! $tbrid || $tbrid == 0 ){
                $sth_tbrid->execute($barcode);
                $tbrid = $sth_tbrid->fetchrow_array;
            }
        }
        else{
            $count_notmove++;
        }
    }
    my $oCount = $iCount - $tCount;
    printf '%-8s %-8s %-8s %-8s',$rid,$iCount,$tCount,$oCount ;

    my ($movediff, $notmovediff) = (0,0);
    $movediff = ($count_move - $tCount) * -1 ;
    $notmovediff = ($count_notmove - $oCount) * -1;
    printf '%-8s %-8s %-8s %-8s', $count_move , $count_notmove , $loanCount , $loanTbkCount;
    my $msg = "";
    my $tmp = 0;
    if ($count_move == 0 ){
        if ($tCount > 0 && $tCount == $iCount){
            $msg =  "c1: Move record & $tCount holding(s) to Textbook & remove record from Libray ";
            $case1++;
        }
        else{
            $msg = "c2: Move record info & $tCount holding(s) to Textbook && remove $tCount holding(s) from Library";
            $case2++;
        }
       
        if ($loanTbkCount && $loanTbkCount > 0){
            if ($msg eq ""){
                $msg  = "Move $loanTbkCount loan transaction(s) from Library to Textbook";
            }
            else {
                $msg  .= " & move $loanTbkCount loan transaction(s) from Library to Textbook";
            }
            moveLoanTransactionToTextBook($dbh,\@tbkLoanList)
        }
        moveRecordHoldingsToTBK($dbh,$rid, \%hTB);
        mxml_del_rec_holding($dbh,\%hTB,1,0);
        
        print $msg;
    }
    else{
        if ($count_move > $tCount){
            $msg =  "c3: Remove $oCount holding(s) from Textbook with trid : $tbrid";
            removeHoldingFromTBK($dbh,$rid, $tbrid, \%hOthers);
            $case3++;
        }
        elsif($count_move < $tCount) {
            $tmp = $tCount - $count_move;
            $msg = "c4: Move $tmp holdings to Textbook with trid : $tbrid ";
            moveHoldingToTBK($dbh,$rid,$tbrid);
            $case4++;
        }
        if ($loanTbkCount && $loanTbkCount > 0){
            if ($msg eq ""){
                $msg = "Move  $loanTbkCount loan transaction(s) from Library to Textbook";
            }
            else{
                $msg .= " & move  $loanTbkCount loan transaction(s) from Library to Textbook";
            }
            $msg  .= " barcode loaned list : " . @tbkLoanList ;
            moveLoanTransactionToTextBook($dbh,\@tbkLoanList);
        }
        mxml_del_rec_holding($dbh,\%hTB,1,0);
        print $msg , "\n";
    }
    $sth_lib->finish;
    $sth_tbrid->finish;
    return ($iCount,$tCount,$oCount,$count_move,$count_notmove,$loanCount, $loanTbkCount, $case1 , $case2 , $case3, $case4 );
}

sub moveRecordHoldingsToTBK {
    my ($dbh,$rid, $href) = @_;
    print "\n record title from DB => ";
    my $sql_record = "select * from opl_marcRecord where rid = ?";
    my $sth_record = $dbh->prepare($sql_record);
    $sth_record->execute($rid);
    while(my $rec = $sth_record->fetchrow_hashref()){
        print $rec->{'title'};
    }

    $sth_record->finish;

    my $marc = getMarcRecord($rid);
    print "\nRecord info .... \n";
    print "Title  : " , $marc->subfield('245',"a") , "  " , $marc->subfield('245',"b") , "\n";

    my $tbrid = addRecordInfo2TB($dbh, $marc);
    addHoldings2TB($dbh,$tbrid,$marc);
    print "Record holdings .... \n";
    my $i = 0;
    foreach my $f ($marc->field('852')) {
        
        $i++;
        print " $i) " , $f->subfield('p') ,  " 3: " , $f->subfield('3') , "\n";
    }
    print "\n";
    $i =0;
    foreach my $id (keys %$href) {
        foreach my $bc (sort keys %{$href->{$id}}){
            $i++;
            print " $i ) ", $bc , " \n";
        }
    }
}


sub isBarcodeExistinTBK{
    my($dbh,$bc)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select barcode from tb_items where barcode=?
_STH_
    $sth->execute($bc);
    my ($ret) = $sth->fetchrow_array;
    $sth->finish;
    if(!$ret){
        return 0;
    }
    return $ret; 
}

sub removeHoldingFromTBK {
    my ($dbh, $rid, $tbrid, $hbc) = @_;
    print "\n remove holdings from TBK with rid: $tbrid ";
    my $sql = "select i.* , s.status from opl_item i left join opl_itemstatus s using(barcode) where i.rid = ? && i.typeId <> 'Textbook' && i.barcode not regexp '^___'";
    my $sth =  $dbh->prepare($sql);
    $sth->execute($rid);
    my $i = 0;
    my $bc = 0;
    while(my $holding = $sth->fetchrow_hashref()){
        $bc = $holding->{'barcode'};
        if (isBarcodeExistinTBK($dbh,$holding->{'barcode'} )){
            $i++;
            my $del = $dbh->do("update tb_items set deleted = '1', barcode = CONCAT('___', barcode) where rid = $rid && barcode = $bc && deleted <> '1'");
            if ($del){
                print "\n $i)",$bc , " is removed " ;
            }
        }
    }
    $sth->finish;
    my $i = 0;
    foreach my $id (keys %$hbc) {
        foreach my $bc (sort keys %{$hbc->{$id}}){
            $i++;
            print " $i ) [", $bc , "] be removed from XML record \n";
        }
    }
    print "\n";
}

sub moveHoldingToTBK {
    my ($dbh,$rid,$tbrid) = @_;
    my $record = getMarcRecord($rid);
    my $emptyStr = "";
    my ($classno, $data);

    foreach my $f ($record->field('852')) {
        if ($f->subfield('k') =~ m/textbook/i || $f->subfield('3') =~ m/textbook/i ){
           if ( ! isBarcodeExistinTBK($dbh, $f->subfield('p'))){
                $classno = '';
                foreach my $code ('k', 'h', 'i', 'm') {
                    $data = $f->subfield($code);
                    $classno .= $data . ' ' if $data;
                }
                $classno =~ s/ +/ /g;
                $classno =~ s/(^ | $)//g;

                my $params = {
                    rid         => $tbrid,
                    barcode     => $f->subfield('p'),
                    typeId      => 'TBK',
                    lCode       => $f->subfield('b') ? $f->subfield('b') : "",
                    price       => $f->subfield('9') ? $f->subfield('9') : "",
                    classno     => $classno,
                    acqDate     => $emptyStr,
                    PONo        => $emptyStr,
                    distributor => $emptyStr,
                    regionCode  => $emptyStr,
                    districtCode=> $emptyStr,
                    buildingCode=> $emptyStr,
                    importDate  => $dateToday,
                };
                tb_item_add($dbh,$params);
            }
        }
    }
}


sub moveLoanTransactionToTextBook {
    my ($dbh,$bcList) = @_;

    my $sql = "select * from opl_loan where barcode = ? order by dateLoan";
    my $sth = $dbh->prepare($sql);
    foreach my $bc (@$bcList){
       $sth->execute($bc);
       while (my $loan = $sth->fetchrow_hashref){
            insertLoan2TB ($dbh,$loan);
            deleteLoanFromLibrary($dbh,$loan); 
       }
    }
    $sth->finish;
}

sub removeHoldingFromLibray {
    
   
    my ($dbh,$rid) = @_;   
    my $sql = "select barcode from opl_item where rid = ? && typeId = 'Textbook' && barcode not regexp '^___'";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    my $i = 0;
    print "\n";
    while (my $barcode = $sth->fetchrow_array){
        $i++;
        print  "$i) [$barcode] is removed from Library \n";     
    }
    $sth->finish;
}

sub removefromTBCollection {

    my ($dbh) = @_;
    my $sql_lib = "select I.barcode from opl_item I inner join opl_marcRecord R using(rid)  where I.barcode not regexp '^___' && I.typeId <> 'Textbook' && R.deleted = 0";
    my $sql_tbk = "select barcode from tb_items where barcode = ? && barcode not regexp '^___' && importDate < '2009-08-25 59:59:59'";
    my $sth_lib = $dbh->prepare($sql_lib);
    my $sth_tbk = $dbh->prepare($sql_tbk);
    my $remove = 0;
    $sth_lib->execute();
    my $count = 0;
    while (my $barcode = $sth_lib->fetchrow_array){
        $sth_tbk->execute($barcode);
        $remove = $sth_tbk->fetchrow_array;
        if ($remove){
            $count++;    
            print  "[$remove]", " ";
        }
    }
    print "\n" , $count , "\n";
}


sub getMarcRecord {

    my ($rid) = @_;
    my $zdbDir = mxml_recordPath($rid);
    my $record = '';
    if (! -f "$zdbDir/$rid.xml") {
        print "ERROR: $zdbDir/$rid.xml: not found.\n";
        return;
    }

    open MARCXML, "<$zdbDir/$rid.xml";
    while (<MARCXML>) {
        $record .= $_;
    }
    close MARCXML;

    my $marc = Opals::Marc::Record::newFromXml($record);
    return $marc;

}
sub addRecordInfo2TB {

    my ($dbh, $record) = @_;

    print 'call addRecordInfo2TB ... title : ', $record->subfield('245',"a"), "\n";

    my $rid = tb_record_idGen($dbh);
    print 'id : ', $rid ,  "\n";

    tb_record_add($dbh, $rid, '005', $dateToday);
    tb_record_add($dbh, $rid, '010_a', $record->subfield('010',"a"));
    foreach my $f ($record->field('020')) {
        tb_record_add($dbh, $rid, '020_a', $record->subfield('020',"a"));
    }
    tb_record_add($dbh, $rid, '022_a', $record->subfield('022',"a"));
    foreach my $f ($record->field('100')) {
        tb_record_add($dbh, $rid, '100_a', $record->subfield('100',"a"));
    }
    foreach my $f ($record->field('110')) {
        tb_record_add($dbh, $rid, '110_a', $record->subfield('110',"a"));
    }
    tb_record_add($dbh, $rid, '245_a', $record->subfield('245',"a"));
    tb_record_add($dbh, $rid, '245_b', $record->subfield('245',"b"));
    tb_record_add($dbh, $rid, '250_a', $record->subfield('250',"a"));
    tb_record_add($dbh, $rid, '260_a', $record->subfield('260',"a"));
    tb_record_add($dbh, $rid, '260_b', $record->subfield('260',"b"));
    tb_record_add($dbh, $rid, '260_c', $record->subfield('260',"c"));
    tb_record_add($dbh, $rid, '300_a', $record->subfield('300',"a"));
    tb_record_add($dbh, $rid, '490_a', $record->subfield('490',"a"));
    tb_record_add($dbh, $rid, '490_v', $record->subfield('490',"v"));
    tb_record_add($dbh, $rid, '500_a', $record->subfield('500',"a"));
    tb_record_add($dbh, $rid, '505_g', $record->subfield('505',"g"));
    tb_record_add($dbh, $rid, '520_a', $record->subfield('520',"a"));
    tb_record_add($dbh, $rid, '521_a', $record->subfield('521',"a"));
    foreach my $f ($record->field('650')) {
        tb_record_add($dbh, $rid, '650_a', $record->subfield('650',"a"));
    }
    foreach my $f ($record->field('856')) {
        tb_record_add($dbh, $rid, '856_u', $record->subfield('856',"u"));
    }

    return $rid;
}

sub addHoldings2TB {

    my ($dbh,$rid, $record) = @_;
    my $emptyStr = "";
    my ($classno, $data);
    print 'call addHoldings2TB ....rid ', $rid , "\n";

    foreach my $f ($record->field('852')) {
        if ($f->subfield('k') =~ m/Textbook/i){
            $classno = '';
            foreach my $code ('k', 'h', 'i', 'm') {
                $data = $f->subfield($code);
                $classno .= $data . ' ' if $data;
            }
            $classno =~ s/ +/ /g;
            $classno =~ s/(^ | $)//g;

            my $params = {
                rid         => $rid,
                barcode     => $f->subfield('p'),
                typeId      => 'TBK',
                lCode       => $f->subfield('b') ? $f->subfield('b') : "",
                price       => $f->subfield('9') ? $f->subfield('9') : "",
                classno     => $classno,
                acqDate     => $emptyStr,
                PONo        => $emptyStr,
                distributor => $emptyStr,
                regionCode  => $emptyStr,
                districtCode=> $emptyStr,
                buildingCode=> $emptyStr,
                importDate  => $dateToday,
            };
            tb_item_add($dbh,$params);
        }
    }
}
sub insertLoan2TB {

    my ($dbh,$loan) = @_;

    print "\n transfer loan to TB ..... \n";
    print " \tuid: " , $loan->{'uid'} , "\n";
    print " \tbarcode :" , $loan->{'barcode'} , "\n" ;
    print " \tdateLoan: ",  $loan->{'dateLoan'}," dateDue: ", $loan->{'dateDue'}, "\n";
    print " \tmaxRenewal : " , $loan->{'maxRenewal'} ,  " renewalCount : " , $loan->{'renewalCount'} , "\n";
    my $sql = <<_SQL_;
insert into tb_loan_fromOpl
set     uid         = ?,
        barcode     = ?,
        maxRenewal  = ?,
        dateLoan    = ?,
        dateDue     = ?,
        dateRenewal = ?,
        dateReturn  = ?,
        finerate    = ?,
        teacherScheduleId = ?,
        teacherName = ?,
        courseName  = ?

_SQL_
     my $sth = $dbh->prepare($sql);
     my $rv = $sth->execute($loan->{'uid'}, $loan->{'barcode'}, $loan->{'maxRenewal'}, $loan->{'dateLoan'}, $loan->{'dateDue'},  $loan->{'dateRenewal'}, $loan->{'dateReturn'} ,0, 0,'','');
    $sth->finish;  

}

sub deleteLoanFromLibrary {

    my ($dbh,$loan) = @_;

    print " delete loan from Library ......., \n" ;
    print " \tloan Id: " , $loan->{'idloan'} , "\n";
    print " \tuid: " , $loan->{'uid'} , "\n";
    print " \tbarcode :" , $loan->{'barcode'} , "\n" ;
    print " \tdateLoan: ",  $loan->{'dateLoan'}," dateDue: ", $loan->{'dateDue'}, "\n";
    print " \tmaxRenewal : " , $loan->{'maxRenewal'} ,  " renewalCount : " , $loan->{'renewalCount'} , "\n";
   
    my $sql = "delete from opl_loan where idloan = ?  && barcode = ? limit 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute($loan->{'idloan'}, $loan->{'barcode'});
    $sth->finish;

}