package Opals::Tb_Circulation;

require Exporter;
@ISA       = qw(Exporter);


@EXPORT_OK = qw(
    
    circ_infoRecord
    circ_getItemStatus
    circ_getItemInfo
    
    circ_getUserCircStatus
    circ_getUserListLoan
    circ_getUserListReserve
    circ_getUserListLost
    circ_getUserLoanHistory
    
    circ_getOnloanNumber
    circ_getOverdueNumber
    circ_getReserveNumber
    

    circ_processLoan
    circ_processReturn
    circ_processRenew
    circ_declareLost

    circ_setAvailable
    circ_updateItemStatus
    
    circ_getLoanListByRid
    circ_getActiveLoanId

    circ_record_ODL
    circ_userListLoanStat
    circ_userCircStatByRid
);

use Opals::Tb_Record qw( 
    
    tb_item_findByRId
    tb_item_findByBarcode
    tb_record_findByRId
);

use Opals::Date qw(
    
    date_addDeltaWorkday
    date_text
    date_getDeadLineDate

);

use Opals::Constant;

# Version number
$VERSION   = 0.01;  
    

#use utf8;
use strict;

sub circ_infoRecord{
    my ($dbh, $rid ) = @_;
    my ($numTotal, $numLoan, $numReserve, $numHold, $totalCirc,$numLost,$numDamaged,$numMissing) = (0,0,0,0,0,0);
    
    return if ($rid eq '');
    
    my $itemList = tb_item_findByRId($dbh, $rid);
    my $sth = $dbh->prepare(<<_STH_); 
    select barcode, available from tb_items where rid = ? && deleted <> '1' && barcode not regexp '^\_\_\_'
_STH_
    $sth->execute($rid);
    my $itemAvail;
    while (my ($bc, $avail) = $sth->fetchrow_array){
        $numTotal++ if   ($avail == 1);
    }

    my $sth_status = $dbh->prepare(<<_STH_);
select  barcode, status
from    tb_itemStatus
where   barcode = ? && status <> ?
        order by ondate desc
        limit 0,1
_STH_


#get numLoan
     $sth = $dbh->prepare(<<_STH_);
select  dateDue, TIMEDIFF(now(), dateDue) as deltaDateDue 
from    tb_loan where barcode = ? && 
        dateReturn is null 
_STH_

     my $ddd=0;
    my ($mBc, $status);
    foreach my $i(@$itemList){
        $sth_status->execute($i->{'barcode'},ITEM_NEW) || return;
        ($mBc, $status) = $sth_status->fetchrow_array;
        if ($mBc && $status == ITEM_INACTIVE){
            $i->{'missing'} = 1; 
            $numMissing++;   }
        elsif ($status == ITEM_DAMAGED){
            $i->{'damaged'} = 1;   
            $numDamaged++; 
            $sth->execute($i->{'barcode'}) || return;
            ($i->{'dateDue'}, $ddd) = $sth->fetchrow_array;
            if ($i->{'dateDue'}){
                $numLoan++;
                $i->{'dateDueText'} = date_text($i->{'dateDue'}, 0); 
                $i->{'overdue'}  = ($ddd > 0)? 1 : 0;     
                } 
            }
        elsif ($status == ITEM_LOST ){
            $i->{'lost'} = 1; 
            $numLost++;  }
        else{
            $sth->execute($i->{'barcode'}) || return;
            ($i->{'dateDue'}, $ddd) = $sth->fetchrow_array;
            if ($i->{'dateDue'}){
                $numLoan++;
                $i->{'dateDueText'} = date_text($i->{'dateDue'}, 0); 
                $i->{'overdue'}  = ($ddd > 0)? 1 : 0;     
            } 
        }
    }
    $sth_status->finish;
    $sth->finish;

    return ($numTotal, $numLoan, $numReserve, $numHold, $itemList, $totalCirc,$numLost,$numDamaged,$numMissing);
}

sub circ_getItemStatus{

    my ($dbh, $barcode) = @_;
    my $retVal = {
        status      => IT_STAT_FULL_AVAIL,
    };


    my $sql = $dbh->prepare(<<_SQL_);
select  i.rid, i.barcode, i.available,
        s.status, s.ondate
from tb_items as i
left outer join tb_itemStatus as s
on      i.barcode = s.barcode && status <> ?
where i.barcode = ?
order by s.ondate desc limit 0, 1
_SQL_

    my $item = $dbh->selectrow_hashref($sql, undef, ITEM_NEW, $barcode);

    if (!$item){
        $retVal->{'status'} = IT_STAT_NOEXIST;
        return $retVal;
    }

    my $rid = $item->{'rid'};
    $retVal->{'rid'} = $rid;

    if (!$item->{'available'}){
        if ($item->{'status'} == ITEM_DAMAGED){
            $retVal->{'status'} = IT_STAT_DAMAGED;
        }
        elsif ($item->{'status'} == ITEM_LOST){
            $retVal->{'status'} = IT_STAT_LOST;
        }
        elsif ($item->{'status'} == ITEM_INACTIVE){
            $retVal->{'status'} = IT_STAT_MISSING;
        }
        else{
            $retVal->{'status'} = IT_STAT_UNAVAIL;
        }
        return $retVal;
    }
    elsif ($item->{'restricted'}){
        $retVal->{'status'} = IT_STAT_RESTRICTED;
        return $retVal;
    }

#check if item is on loan

    $sql = <<_SQL_;
select  * 
from    tb_loan 
where   barcode = ? && 
        dateReturn is null
_SQL_
    $item = $dbh->selectrow_hashref($sql, undef, $barcode);
    if ($item){
        $retVal->{'l_idLoan'}       = $item->{'id'};
        $retVal->{'l_uid'}          = $item->{'uid'};
        $retVal->{'l_duedate'}      = $item->{'dateDue'};
        $retVal->{'status'}         = IT_STAT_ONLOAN;
    }
    return $retVal;

}

sub circ_getItemInfo{

    my ($dbh, $barcode, $uid) = @_;
    return undef if($barcode eq '');
    my ($itemInfo, $userTypeId);
    my $sql;
    $itemInfo   = tb_item_findByBarcode($dbh,$barcode);
    if (!$itemInfo){
        return;
    }
    return $itemInfo if (!$uid);

    $sql = <<_SQL_;
select  categorycode
from    opl_user
where   uid = ?
_SQL_

    ($userTypeId) = $dbh->selectrow_array($sql, undef, $uid);
    if (!$userTypeId) {
        return $itemInfo;
    }
    
$sql = <<_SQL_;
select  loanPeriod    div 24 as loanPeriod, 
        renewalPeriod div 24 as renewalPeriod, 
        reservePeriod div 24 as reservePeriod, 
        holdPeriod    div 24 as holdPeriod, 
        gracePeriod   div 24 as gracePeriod, 
        maxRenewal, fine
from    opl_itemTypeParam
where   itemTypeId = ? &&
        userTypeId = ?
_SQL_
    my $itemTypeParam = $dbh->selectrow_hashref(
        $sql, undef, $itemInfo->{'typeId'}, $userTypeId
    );
    foreach my $k (keys %{$itemTypeParam}) {
        $itemInfo->{$k} = $itemTypeParam->{$k};
    }
    return $itemInfo;
}

sub circ_getUserCircStatus{

    my ($dbh,$uid, $barcode) = @_;
    my $retVal = {status=>1, overdue=>0, maxLoan=>0,maxRenew=>0,maxReserve=>0};
#check if user INACTIVE, overdue, maxloan, maxRenew, maxReserve...

    return $retVal;

}

sub circ_getUserListLoan{
    my ($dbh, $uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select id,barcode, dateLoan, dateDue, renewalCount, maxRenewal,
        to_days(now()) - to_days(dateDue) as deltaDueDate,
        to_days(now()) - to_days(dateLoan) as deltaDate
from    tb_loan
where   uid = ? && dateReturn is null
order by dateloan desc

_STH_
    
    $sth->execute($uid);
    my @tmpList ;
    while (my $loan = $sth->fetchrow_hashref){
        $loan->{'overdue'} = ($loan->{'deltaDueDate'} > 0)? 1:0 ;
        push @tmpList, $loan;
    }
    
    my @loanList;
    foreach my $loan(@tmpList){
        my $itemInfo = tb_item_findByBarcode($dbh, $loan->{'barcode'});
        if ($itemInfo) {
            push @loanList, {
                idLoan      => $loan->{'id'},
                rid         => $itemInfo->{'rid'},
                barcode     => $loan->{'barcode'},
                dateLoan    => $loan->{'dateLoan'},
                dateDue     => $loan->{'dateDue'},
                overdue     => $loan->{'overdue'},
                deltaDate   => $loan->{'deltaDueDate'},
                renewalCount=> $loan->{'renewalCount'},
                maxRenewal  => $loan->{'maxRenewal'},
                title       => $itemInfo->{'title'},
                subtitle    => $itemInfo->{'subTitle'},
                author      => $itemInfo->{'author'},
                isbn        => $itemInfo->{'isbn'} ,
                publisher   => $itemInfo->{'publisher'},
                pubDate     => $itemInfo->{'pubDate'},
                price       => $itemInfo->{'price'}, 
                classNumber => $itemInfo->{'classNumber'},
            };
        }
    }
    $sth->finish;
    return \@loanList;
}
sub circ_getUserListReserve{
    my ($dbh, $uid) = @_;

    my $sth = $dbh->prepare("select  distinct r.* 
                             from  tb_reserve as r left outer join tb_hold as h 
                             on r.id=h.idreserve
                             where r.uid = ? &&  r.dateCancel is null 
                                && r.dateExpiry >=substring(now(),1,10) 
                                && ((h.dateLoan is null 
                                && h.dateCancel is null && h.dateExpiry > substring(now(),1,10)) 
                                || (r.numCopyReserve >0 
                                && (h.dateCancel is not null ||h.idreserve is null)))
                             order by dateReserve desc"
                           );
    $sth->execute($uid) || return;
    my $sth_notExp = $dbh->prepare(
                           "select count(*) as nHold ,min(dateExpiry) as holdExpiry 
                           from tb_hold as h 
                           where idreserve=? 
                              && dateExpiry >=substring(now(),1,10) 
                              && h.dateCancel is null
                              && h.dateLoan is null"
                           );
    my $sth_Exp    = $dbh->prepare(
                           "select count(*) as nHold 
                           from tb_hold as h 
                           where idreserve=? 
                              && dateExpiry <substring(now(),1,10)  
                              && h.dateCancel is null
                              && h.dateLoan is null"
                           );

    my @reserveList;
    while (my $rsrv = $sth->fetchrow_hashref) 
    {
        my ($nHold,$nHoldExp) =(0,0);
        #my $nReserve = $rsrv->{'numCopyReserve'};
        #$sth_recInfo->execute($rsrv->{'rid'});
        my ($recId, $recInfo) = tb_record_findByRId($dbh,$rsrv->{'rid'});
        $sth_notExp->execute($rsrv->{'idReserve'});
        my ($hRec)=$sth_notExp->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'}>0){ 
            $nHold = $hRec->{'nHold'};
            $rsrv->{'holdExpiry'} = date_text($hRec->{'holdExpiry'},0);
        }
        $sth_Exp->execute($rsrv->{'idReserve'});
        my ($hERec)=$sth_Exp->fetchrow_hashref;
        if ($hERec && $hERec->{'nHold'}>0){ 
            $nHoldExp = $hERec->{'nHold'};
        }
        $rsrv->{'title'}      = $recInfo->{'title'};
        $rsrv->{'author'}     = $recInfo->{'author'};
        $rsrv->{'pubName'}    = $recInfo->{'publisher'};
        $rsrv->{'pubDate'}    = $recInfo->{'pubDate'};
        $rsrv->{'numHold'}    = $nHold ;
        #$rsrv->{'numCopyReserve'}    = $nReserve;
        $rsrv->{'numHoldExp'} = $nHoldExp;
        push @reserveList, $rsrv;
    }
    $sth_Exp->finish;
    $sth_notExp->finish;
    $sth->finish;
    return \@reserveList;

}

sub circ_getUserListLost{



}
sub circ_getUserLoanHistory{

    my ($dbh, $uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select id,barcode, dateLoan, dateDue, dateReturn
from    tb_loan
where   uid = ? && dateReturn is NOT null
order by dateloan desc
_STH_
    
    $sth->execute($uid);
    my @tmpList ;
    while (my $loan = $sth->fetchrow_hashref){
        push @tmpList, $loan;
    }
    my @loanHistory;
    foreach my $loan(@tmpList){
        my $itemInfo = tb_item_findByBarcode($dbh, $loan->{'barcode'});
        if ($itemInfo) {
            push @loanHistory, {
                idLoan      => $loan->{'id'},
                rid         => $itemInfo->{'rid'},
                barcode     => $loan->{'barcode'},
                dateLoan    => $loan->{'dateLoan'},
                dateDue     => $loan->{'dateDue'},
                dateReturn  => $loan->{'dateReturn'},
                title       => $itemInfo->{'title'},
                author      => $itemInfo->{'author'},
                isbn        => $itemInfo->{'isbn'} ,
                price       => $itemInfo->{'price'}, 
                classNumber => $itemInfo->{'classNumber'},
            };
        }
    }
    $sth->finish;
    return \@loanHistory;
}

sub circ_getUserListReserve{
    my ($dbh, $uid) = @_;

    my $sth = $dbh->prepare("select  distinct r.* 
                             from  tb_reserve as r left outer join tb_hold as h 
                             on r.id=h.idreserve
                             where r.uid = ? &&  r.dateCancel is null 
                                && r.dateExpiry >=substring(now(),1,10) 
                                && ((h.dateLoan is null 
                                && h.dateCancel is null && h.dateExpiry > substring(now(),1,10)) 
                                || (r.numCopyReserve >0 
                                && (h.dateCancel is not null ||h.idreserve is null)))
                             order by dateReserve desc"
                           );
    $sth->execute($uid) || return;
    my $sth_notExp = $dbh->prepare(
                           "select count(*) as nHold ,min(dateExpiry) as holdExpiry 
                           from tb_hold as h 
                           where idreserve=? 
                              && dateExpiry >=substring(now(),1,10) 
                              && h.dateCancel is null
                              && h.dateLoan is null"
                           );
    my $sth_Exp    = $dbh->prepare(
                           "select count(*) as nHold 
                           from tb_hold as h 
                           where idreserve=? 
                              && dateExpiry <substring(now(),1,10)  
                              && h.dateCancel is null
                              && h.dateLoan is null"
                           );

    my @reserveList;
    while (my $rsrv = $sth->fetchrow_hashref) 
    {
        my ($nHold,$nHoldExp) =(0,0);
        #my $nReserve = $rsrv->{'numCopyReserve'};
        #$sth_recInfo->execute($rsrv->{'rid'});
        my ($recId, $recInfo) = tb_record_findByRId($dbh,$rsrv->{'rid'});
        $sth_notExp->execute($rsrv->{'idReserve'});
        my ($hRec)=$sth_notExp->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'}>0){ 
            $nHold = $hRec->{'nHold'};
            $rsrv->{'holdExpiry'} = date_text($hRec->{'holdExpiry'},0);
        }
        $sth_Exp->execute($rsrv->{'idReserve'});
        my ($hERec)=$sth_Exp->fetchrow_hashref;
        if ($hERec && $hERec->{'nHold'}>0){ 
            $nHoldExp = $hERec->{'nHold'};
        }
        $rsrv->{'title'}      = $recInfo->{'title'};
        $rsrv->{'author'}     = $recInfo->{'author'};
        $rsrv->{'pubName'}    = $recInfo->{'publisher'};
        $rsrv->{'pubDate'}   

    }
}

sub circ_getOnloanNumber {

    my ($dbh,$uid) = @_;
    my $loanList = circ_getUserListLoan($dbh,$uid);
    my $loanCount = 0;
    if ($loanList){
        $loanCount = scalar(@{$loanList});
    }
    
    return $loanCount;

}

sub circ_getOverdueNumber {

    my ($dbh,$uid) = @_;
    my $odCount = 0;
    
    my $loanList = circ_getUserListLoan($dbh,$uid);
    foreach my $loan(@$loanList){
        if ($loan->{'overdue'}) {
            $odCount++;
         }
    }
    return $odCount;
}

sub circ_getReserveNumber {

    my ($dbh,$uid) = @_;
    my $reserveCount = 0;

    return $reserveCount;
}

sub circ_processLoan {
    my ($dbh, $uid, $barcode, $dateLoan, $dateDue, $maxRenewal, $finerate, $teacherScheduleId,$teacherName,$courseName ) = @_;
  
    # Get the current finerate
    #my $itemInfo = circ_getItemInfo($dbh, $barcode, $uid);
    #my $maxRenewal = $itemInfo->{'maxRenewal'};
    #my $finerate   = $itemInfo->{'fine'};
    my $sql = <<_SQL_;
insert into tb_loan
set     uid         = ?,
        barcode     = ?,
        maxRenewal  = ?,
        dateLoan    = timestamp(?, curtime()),
        dateDue     = ?,
        finerate    = ?,
        teacherScheduleId = ?,
        teacherName = ?,
        courseName  = ?

_SQL_
    
    my $sth = $dbh->prepare($sql);
    my $rv = $sth->execute($uid, $barcode, $maxRenewal, $dateLoan, $dateDue, $finerate, $teacherScheduleId,$teacherName,$courseName);
    $sth->finish;  
    return ($rv eq '0E0') ? LOAN_UNFINISH : LOAN_FINISH;           


}

sub circ_processReturn {

    my ($dbh, $uid, $barcode, $dateReturn) = @_;
    my $sth = $dbh->prepare(<<_STH_);
update tb_loan set dateReturn=timestamp(?, curtime())
where uid = ? && barcode = ? && dateReturn is null
_STH_

    my $result = $sth->execute($dateReturn, $uid, $barcode);
    $sth->finish;

    return $result eq '0E0' ? 0:1;

}

sub circ_processRenew {
    my ($dbh,$uid,$barcode,$dateDue)=@_;
    my $sth = $dbh->prepare("update  tb_loan set renewalCount = renewalCount + 1, dateDue =?, dateRenewal = now()
                             where   uid = ? && barcode = ? && dateReturn is null");
    my $result= $sth->execute($dateDue, $uid, $barcode);
    $sth->finish;
    return $result eq '0E0'?0:1;
}

sub circ_declareLost{
    my ($dbh, $barcode) = @_;
    my ($result, $retVal1, $retVal2, $retVal3) = (0,0,0,0);

    #return
    my $sql     = "update tb_loan set dateReturn = now() where barcode =?  && dateReturn is null";
    my $sth     = $dbh->prepare($sql);
    $retVal1    = $sth->execute($barcode);
    
    # Set item's status is not available
    $sql        = "update tb_items set available=0 where barcode=?";
    $sth        = $dbh->prepare($sql);
    $retVal2    = $sth->execute($barcode);

    #record item status (ITEM_LOST) to eq_itemStatus
    $sql        = "insert into tb_itemStatus set barcode=?, ondate=now(), status=?";
    $sth        = $dbh->prepare($sql);
    $retVal3    = $sth->execute($barcode, ITEM_LOST);
    $sth->finish;

    $retVal1 = ($retVal1 eq '0E0' )? 0:1;
    $retVal2 = ($retVal2 eq '0E0' )? 0:1;
    $retVal3 = ($retVal3 eq '0E0' )? 0:1;
    $result = ($retVal1 && $retVal2 && $retVal3);
    return $result;
    
}

sub circ_setAvailable{
    my ($dbh, $barcode,$oldStatus) = @_;
    my $sth = $dbh->prepare("update tb_items set available = 1 where barcode=?");
    $sth->execute($barcode);

    $sth = $dbh->prepare ("insert into tb_itemStatus set barcode=? , ondate=now(), status=1 ");
    my $retVal = $sth->execute($barcode);
    $sth->finish;

    return $retVal eq '0E0' ? 0:1;

}

sub circ_updateItemStatus{
    my ($dbh, $barcode, $status,$note) = @_;
    my $sth = $dbh->prepare("insert into tb_itemStatus set barcode = ?, ondate=now(), status=?, note=?");
    $sth->execute($barcode, $status,$note);
    $sth->finish;
}


sub circ_getLoanListByRid{

    my ($dbh, $rid) =@_;
    my $sth = $dbh->prepare(<<_STH_);
SELECT  i.barcode, l.uid, l.dateDue
FROM    tb_items as i INNER JOIN tb_loan as l ON i.barcode = l.barcode 
WHERE   i.rid = ? && l.dateReturn is null
_STH_
    $sth->execute($rid);
    my @loanList;
    while(my $l = $sth->fetchrow_hashref){
        push @loanList , $l;
    }
    $sth->finish;
    return \@loanList;

}

sub circ_getActiveLoanId{
    my ($dbh,$barcode,$uid)= @_;
    my $sth = $dbh->prepare("select id from tb_loan where barcode=? && uid=? && dateReturn is null");
    $sth->execute($barcode,$uid);
    my ($rec) = $sth->fetchrow_hashref;
    $sth->finish;
    return $rec->{'id'};
}

sub circ_record_ODL{   

    my ($dbh, $idloan,$type,$days_overdue) = @_;
       # Insert new item's status
    my $szSQL = "insert into tb_odl set idloan=?, type=?,days_overdue=?, ondate=now()";
    my $sth = $dbh->prepare($szSQL);
    my $bResult = $sth->execute($idloan,$type,$days_overdue);
    my $odl_id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $odl_id;

}


sub circ_userListLoanStat 
{
    my ($dbh, $uid) = @_;

    my $lastLoan_sth = $dbh->prepare("
select      dateLoan, dateDue, dateReturn 
from        tb_loan l inner join tb_items i on i.barcode=l.barcode 
where       i.rid=?  && uid=? 
order by    l.id desc limit 1 ");


    my $sth = $dbh->prepare("
select    l.uid,i.rid, i.classNumber ,rTitle.fVal as title, group_concat(distinct rAuthor.fVal order by rAuthor.fVal SEPARATOR ';' ) as author, 
          l.dateLoan, l.dateDue, l.dateReturn, count(distinct l.id) as totalCirc 
from      tb_items i 
          inner join tb_records rTitle on i.rid = rTitle.rid && rTitle.fId = '245_a'
          inner join tb_records rAuthor on i.rid = rAuthor.rid && rAuthor.fId = '100_a'
          inner  join tb_loan l on i.barcode=l.barcode   
where     uid=?    && l.dateReturn is not null            
group by  i.rid 
order by  totalCirc desc");


    $sth->execute($uid) || return;
   
    my @loanList;

    while(my $loan = $sth->fetchrow_hashref) 
    {  
        $lastLoan_sth->execute($loan->{'rid'},$uid);
        my ($dateLoan,$dateDue, $dateReturn) = $lastLoan_sth->fetchrow_array;
        if($dateLoan,$dateDue, $dateReturn){
            $loan->{'dateLoan'}   = $dateLoan;
            $loan->{'dateDue'}    = $dateDue;
            $loan->{'dateReturn'} = $dateReturn;
        }
        $loan->{'overdue'} = ($loan->{'deltaDueDate'} > 0) ? 1 : 0;
        push @loanList, $loan;
    }

    $sth->finish;
    $lastLoan_sth->finish;
    return \@loanList;
}

sub circ_userCircStatByRid{
    my ($dbh,$uid,$rid) =@_;

    my $sth = $dbh->prepare("
select   i.rid, i.barcode, i.classNumber ,
         rTitle.fVal as title, group_concat(distinct rAuthor.fVal order by rAuthor.fVal SEPARATOR ';' ) as author,
         l.dateLoan, l.dateDue, l.dateReturn  
from     tb_items i 
         inner join tb_records rTitle on i.rid = rTitle.rid && rTitle.fId = '245_a'
         inner join tb_records rAuthor on i.rid = rAuthor.rid && rAuthor.fId = '100_a'
         inner  join tb_loan l on i.barcode=l.barcode   
where    uid=?  && i.rid =?                
group by l.id
order by  l.id desc");
    $sth->execute($uid,$rid) || return;

  
    my @loanList;
    while(my $loan = $sth->fetchrow_hashref) 
    {
       if($loan->{'barcode'} =~m/^\_\_\_(.*)/){
            $loan->{'org_barcode'} =$1;
        }
        else{
            $loan->{'org_barcode'} =$loan->{'barcode'};
        }
         push @loanList, $loan;

    } 

    $sth->finish;
    return \@loanList;

}

1;