package Opals::Eq_Circulation;

require Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    
    circ_getItemInfo
    circ_getItemStatus
    circ_getNumItemOnReserve
    circ_getNumActiveItem
    circ_getNumItemsAvailable
    circ_getNumItemOnLoan
    circ_getNumItemOnHold
    
    circ_infoRecord
    circ_getRecordInfo

    circ_getUserCircStatus

    circ_processLoan
    circ_processReturn
    circ_processRenew

    circ_placeReserve
    circ_fillReserve
    circ_cancelReserve
    circ_resetReserve

    circ_placeHold
    circ_fillHold
    circ_cancelHold
    circ_cancelHold_all
    circ_isOnHold

    circ_getUserListLoan
    circ_getUserListReserve
    circ_getUserLoanHistory

    circ_getReserveId

    circ_declareLost
    circ_updateItemStatus
    circ_setAvailable

    circ_getLoanListByRid

    circ_record_ODL

) ;

use Opals::Constant;

use Opals::Equipment qw(
    eq_def_getFieldId
    eq_item_findByBarcode
    eq_item_findByRId
    eq_record_findByRId
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

sub circ_getItemInfo{
    
    my ($dbh, $barcode,$uid) = @_;
    return undef if ($barcode eq '');
    my ($itemInfo, $userTypeId);
    $itemInfo = Opals::Equipment::eq_item_findByBarcode($dbh, $barcode);
    if (!$itemInfo){
        return undef;
    }
    return $itemInfo if (!$uid);
    
    my $sql = <<_SQL_;
select categorycode 
from opl_user
where uid = ?
_SQL_

    ($userTypeId) = $dbh->selectrow_array($sql, undef,$uid);
    if (!$userTypeId){
        return $itemInfo;
    }

    $sql = <<_SQL_;
select  if(loanPeriod    div 24 >=1,loanPeriod div 24,loanPeriod)       as loanPeriod,    
        if(loanPeriod    div 24 >=1,'day','hour') as loanUnit,
        if(renewalPeriod div 24 >=1,renewalPeriod div 24,renewalPeriod) as renewalPeriod, 
        if(renewalPeriod div 24 >=1,'day','hour') as renewalUnit,
        if(reservePeriod div 24 >=1,reservePeriod div 24,reservePeriod) as reservePeriod, 
        if(reservePeriod div 24 >=1,'day','hour') as reserveUnit,
        if(holdPeriod    div 24 >=1,holdPeriod    div 24,holdPeriod)    as holdPeriod,    
        if(holdPeriod    div 24 >=1,'day','hour') as holdUnit,
        if(gracePeriod   div 24 >=1,gracePeriod   div 24,gracePeriod)   as gracePeriod,   
        if(gracePeriod   div 24 >=1,'day','hour') as graceUnit,
        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_getItemStatus{
    
    my($dbh,$params) = @_;
    my $retVal = {
        status      => IT_STAT_FULL_AVAIL,
    };
    return if ($params->{'barcode'} eq '');
    

    my  $sql = $dbh->prepare(<<_SQL_);
select  i.rid, i.barcode, i.available, s.status, s.ondate 
from    eq_items i left outer join eq_itemStatus s on s.barcode = i.barcode && s.status <> ?
where   i.barcode = ? 
order by s.ondate desc limit 0, 1
_SQL_
    
    my $item = $dbh->selectrow_hashref($sql, undef,ITEM_NEW , $params->{'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;
    }

#check if item is on loan?
    $sql = $dbh->prepare(<<_SQL_);
select l.*, u.firstname, u.lastname
from    eq_loan l left outer join opl_user u using(uid)
where   l.barcode = ? &&
        l.dateReturn is null
_SQL_

    my $item = $dbh->selectrow_hashref($sql, undef,$params->{'barcode'});
    if ($item){
        $retVal->{'l_idLoan'}       = $item->{'id'};
        $retVal->{'l_uid'}          = $item->{'uid'};
        $retVal->{'l_duedate'}      = $item->{'dateDue'};
        $retVal->{'maxRenewal'}     = $item->{'maxRenewal'};
        $retVal->{'renewalCount'}   = $item->{'renewalCount'};
        $retVal->{'status'}         = IT_STAT_ONLOAN;
        $retVal->{'patron_name'}    = $item->{'lastname'} . ", " . $item->{'firstname'};
    }
    
#get hold list
    my $sth = $dbh->prepare(<<_SQL_);
select  u.uid, u.userbarcode, u.firstname, u.lastname, h.idReserve, h.dateHold, h.dateExpiry, r.rid
from    eq_reserve as r inner join eq_hold as h on r.id = h.idReserve
                        inner join opl_user as u on r.uid = u.uid
where r.rid = ? && h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now()
order by h.dateHold
_SQL_
    $sth->execute($rid);
    my @holdList;
    while (my $h = $sth->fetchrow_hashref){
        push @holdList, $h;
    }
    $sth->finish;

    $retVal->{'holdList'} = \@holdList;

    if (circ_isOnHold($dbh, $rid) && $retVal->{'status'}!= IT_STAT_ONLOAN){
        $retVal->{'status'} = IT_STAT_ONHOLD;
    }
#check іf item is on reserve?
    
    #if (circ_getNumItemOnReserve($dbh, $rid))
    if (circ_isOnReserve($dbh, $rid) > 0){
        if ($retVal->{'status'} == IT_STAT_ONLOAN){
            $retVal->{'status'} = IT_STAT_HAVE_RSVR;
        }
        elsif($retVal->{'status'} != IT_STAT_ONHOLD){
            $retVal->{'status'} = IT_STAT_HOLD_EXPIRE;
        }
        $sth = $dbh->prepare(<<_SQL_);
select  u.uid, u.userbarcode ,u.firstname, u.lastname, r.rid, r.id as idReserve , r.dateReserve, r.dateExpiry
from    eq_reserve as r inner join opl_user as u on r.uid = u.uid
where   rid = ? && numCopyReserve >= 1 && dateCancel is null && dateExpiry > now()
order by r.id
_SQL_
        $sth->execute($rid);
        my @reserveList;
        while (my $r = $sth->fetchrow_hashref){
            push @reserveList, $r;
        }
        $sth->finish;
        $retVal->{'reserveList'} = \@reserveList;
    }
    return $retVal;
}

sub circ_getNumActiveItem{

    my ($dbh, $rid) = @_;
    my $displaySF_0 = 0;
    my $itemList = Opals::Equipment::eq_item_findByRId($dbh, $rid, $displaySF_0);
    if ($itemList){
        return scalar(@$itemList);
    }
    return 0;
}

sub circ_getNumItemOnLoan{
    my ($dbh, $rid) = @_;
    my $displaySF_0 = 0;
    my $itemList = eq_item_findByRId($dbh, $rid, $displaySF_0);
=item    
    #my $barcodeId = eq_def_getFieldId($dbh, {defType=>'item', fname=>'Bar Code'});
    #my $sql = "select sfValue as barcode from eq_item where rid = ? && iid = ? && sfId = ?";
    #my $sth = $dbh->prepare($sql);
    foreach my $i(@$itemList){
        $sth->execute($rid, $i->{'iid'}, $barcodeId);
        my ($barcode) = $sth->fetchrow_array;
        $i->{'barcode'} = $barcode;
    }
    $sth->finish;
=cut

    my $sth = $dbh->prepare(<<_SQL_);
select count(*) as count from eq_loan where barcode = ? && dateReturn is null
_SQL_

    my $totalLoan= 0;
    foreach my $i(@$itemList){
        $sth->execute($i->{'barcode'}) || return;
        my ($c ) = $sth->fetchrow_hashref;
        if ($c){
            $totalLoan += $c->{'count'};
        }
    }
    $sth->finish;
    return $totalLoan;
}

sub circ_getNumItemOnReserve{
    my ($dbh, $rid) =@_;
    my $sth = $dbh->prepare(<<_SQL_);
select  count(*) as count
from    eq_reserve
where   rid = ? && numCopyReserve > 0 && dateCancel is null && dateExpiry > now()
_SQL_
    $sth->execute($rid);
    my ($rec) = $sth->fetchrow_hashref;
    $sth->finish;
    if ($rec){
        return $rec->{'count'};
    }
    return 0;
}

sub circ_isOnReserve{
    my ($dbh, $rid) = @_;
    return circ_getNumItemOnReserve($dbh,$rid)>0?1:0;
}

sub circ_getNumItemOnHold{

    my ($dbh, $rid) = @_;
    my $sth = $dbh->prepare("select count(*) as count from eq_reserve as r inner join eq_hold as h on r.id = h.idReserve 
                            where r.rid = ? && h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now() ");
    $sth->execute($rid);
    my ($rec) = $sth->fetchrow_hashref;
    if ($rec){
        return $rec->{'count'};
    }
    return 0;
}

sub circ_isOnHold{
    my ($dbh, $rid) = @_;
    my $numOfHold   = circ_getNumItemOnHold($dbh, $rid);
    if (!$numOfHold){
        return 0;
    }
    my $numItemsAvailable = circ_getNumItemsAvailable($dbh, $rid);
    return $numItemsAvailable > 0 ? 0:1;
}

sub circ_getNumItemsAvailable{
    my ($dbh, $rid) = @_;
    my $numOfHolds          = circ_getNumItemOnHold($dbh, $rid);
    my $numOfActiveItems    = circ_getNumActiveItem($dbh,$rid);
    my $numOfLoans          = circ_getNumItemOnLoan($dbh, $rid);
    my $numItemsAvailable   = $numOfActiveItems - $numOfLoans - $numOfHolds;
    return $numItemsAvailable;
}
#---------------------------------------------
# funtion circ_getRecordInfo
# return record info + hold, reserve period based on user type
#---------------------------------------------
sub circ_getRecordInfo{
    my ($dbh, $rid, $uid) =@_;
    #my $eqNameId = eq_def_getFieldId($dbh, {defType=>'record', fname=>'Equipment Name'});
    my ($recId, $recInfo) = Opals::Equipment::eq_record_findByRId($dbh,{recordId=>$rid});
    my @recordInfo = ();
    my $rname = $recInfo->[0]->{'rname'};

=item
    foreach my $f (@$recInfo){
        if ($f->{'fId'} == $eqNameId){
            $rname = $f->{'fValue'};
            last;
        }
    }
=cut
    if ($uid && $uid >= 0){
        my $sql = <<_SQL_;
select  categorycode
from    opl_user
where   uid = ?
_SQL_
        my ($userTypeId) = $dbh->selectrow_array($sql, undef, $uid);
        if ($userTypeId){
            $sql = <<_SQL_;
select  holdPeriod , reservePeriod 
from    opl_itemTypeParam
where   itemTypeId = 'EQMNT' && 
        userTypeId = ?
_SQL_
        my $sth = $dbh->prepare($sql);
        $sth->execute($userTypeId);
        my ($infoPeriod) = $sth->fetchrow_hashref;
        $sth->finish;
        push @recordInfo, {
            rid             =>  $rid,
            rname           =>  $rname,
            holdPeriod      =>  $infoPeriod->{'holdPeriod'},
            reservePeriod   =>  $infoPeriod->{'reservePeriod'},
            };
        }
    }
    return $recordInfo[0];
}

sub circ_getUserCircStatus{
    my ($dbh, $uid, $barcode) = @_;
    my $retVal = {status=>1, overdue=>0, maxLoan=>0,maxRenew=>0,maxReserve=>0};
    
    my $sth = $dbh->prepare("select  u.*, c.maxloans, c.maxreserv
                                from    opl_user as u inner join opl_category as c 
                                on u.categorycode = c.catid where u.uid = $uid");
    $sth->execute();
    my ($rec) = $sth->fetchrow_hashref;
    $sth->finish;
    if (!$rec || !$rec->{'status'}){
        $retVal->{'status'} = USER_INACTIVE;
    }
my $circ_rec;
# have overdue
    $sth = $dbh->prepare("select * from eq_loan where uid=$uid && dateReturn is null && datedue < now()");
    $sth->execute();
    ($circ_rec) = $sth->fetchrow_hashref;
    $sth->finish;
    if ($circ_rec){
        $retVal->{'overdue'} = 1;
    }
#max loan reached
    $sth = $dbh->prepare("select count(*) as numOfLoan from eq_loan where uid=$uid && dateReturn is null && type is null");
    $sth->execute();
    ($circ_rec) = $sth->fetchrow_hashref;
    $sth->finish;
    if($circ_rec && $rec && $rec->{'maxloans'} <= $circ_rec->{'numOfLoan'}){
        $retVal->{'maxLoan'} = 1;
    }
#max renew reached
    $sth = $dbh->prepare("select maxRenewal, renewalCount from eq_loan where uid = $uid && barcode= '$barcode' && 
                            dateReturn is null");
    $sth->execute();
    ($circ_rec) = $sth->fetchrow_hashref;
    $sth->finish;
    if ($circ_rec && $rec && $circ_rec->{'maxRenewal'} <= $circ_rec->{'renewalCount'}){
        $retVal->{'maxRenew'} = 1;
    }

#max reserve reached..

#max hold reached...
    
    return $retVal;
}

#-------------------------------
#   function :circ_infoRecord (see also circ_getRecordInfo*)
#   return number of items, number of items on loan, on reserve, on hold, on circulation  
#------------------------------
sub circ_infoRecord {
    
    my ($dbh,$rid ) = @_;
    my ($numTotal,$numLoan,$numReserve,$numHold,$totalCirc,$totalMissing,$totalDamaged,$totalLost)=(0,0,0,0,0,0,0,0);
    my $itemList = Opals::Equipment::eq_item_findByRId($dbh, $rid);
    #my $itemList = eq_item_findByRId($dbh, $rid);
    my $sth = $dbh->prepare(<<_STH_);
select barcode, available from eq_items where rid = ? && barcode not regexp '^___'
_STH_
    $sth->execute($rid) || return;
    my $holdingAvail;
    while (my ($bc, $avail) = $sth->fetchrow_array) {
        $holdingAvail->{($bc)} = $avail;
        $numTotal++;
    }
    $sth->finish;
    foreach my $i (@$itemList) {
        $i->{'available'} = $holdingAvail->{$i->{'barcode'}};
    }

#get item status
    my $sth_status = $dbh->prepare(<<_STH_);
select  barcode, status
from    eq_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    eq_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;    
            $totalMissing++;
        }    
        elsif ($status == ITEM_DAMAGED){
            $i->{'damaged'} = 1;  
            $totalDamaged++;
        }
        elsif ($status == ITEM_LOST ){
            $i->{'lost'} = 1; 
            $totalLost++;  }
        else{
             $sth->execute($i->{'barcode'}) || return;
            ($i->{'dateDue'}, $ddd) = $sth->fetchrow_array;
            if ($i->{'dateDue'}){
                $numLoan++;
                $i->{'dateDueFormat'} = $i->{'dateDue'};
                $i->{'dateDueFormat'} =~ s/ / \/ /; 
                $i->{'overdue'}  = ($ddd > 0)? 1 : 0;     } 
        }
    }
    $sth_status->finish;
    $sth->finish;

#get numReserve
    
    $sth = $dbh->prepare(<<_STH_);
select  numCopyReserve
from    eq_reserve
where   rid = ? &&
        numCopyReserve > 0 &&
        dateCancel is null &&
        TIMESTAMPDIFF(MINUTE, now(), dateExpiry) >= 0
_STH_
    
    $sth->execute($rid) || return;
    while((my $nReserve) = $sth->fetchrow_array){
        $numReserve += $nReserve;
    }
    $sth->finish;

#get numHold
    $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    eq_hold as h inner join eq_reserve as r on r.id = h.idReserve 
where   r.rid   = ? &&
        h.dateLoan is null &&
        h.dateCancel is null &&
        TIMESTAMPDIFF(MINUTE, now(), h.dateExpiry) >= 0
_STH_
    
    $sth->execute($rid);
    ($numHold) = $sth->fetchrow_array;
    $sth->finish;

#get circulation
#
my $sql = <<_STH_;
select    count(i.rid) as totalCirc
from      eq_items i   
          inner  join eq_loan l on i.barcode=l.barcode  
where     i.rid= ? &&   
          i.barcode not regexp '^\_\_\_'
group by i.rid
_STH_
    $sth = $dbh->prepare($sql);
    $sth->execute($rid);
    ($totalCirc) = $sth->fetchrow_array;

    $sth->finish;
    return ($numTotal, $numLoan, $numReserve, $numHold, $itemList, $totalCirc,$totalMissing,$totalDamaged,$totalLost);
}

sub circ_processLoan {
    my ($dbh, $params) = @_;
  
    # Get item info i.e.: maxRenewal, fine...
    my $itemInfo = circ_getItemInfo($dbh, $params->{'barcode'}, $params->{'uid'});
    my $maxRenewal = $itemInfo->{'maxRenewal'};
    my $finerate   = ($itemInfo->{'fine'})?$itemInfo->{'fine'}:0;

     my $sql = <<_SQL_;
insert into eq_loan
set     uid         = ?,
        barcode     = ?,
        maxRenewal  = ?,
        dateLoan    = ?,
        dateDue     = ?,
        finerate    = ?     
_SQL_

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

sub circ_getUserListLoan{
    my ($dbh, $params) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select  barcode,dateLoan,dateDue,cast((timestamp(now())-timestamp(dateDue)) as signed integer) as deltaDateDue,
 to_days(now()) - to_days(dateDue) as deltaDayDue
from eq_loan where uid=? && dateReturn is null;
_STH_
    $sth->execute($params->{'uid'}) ;
    my @tmpList ;
    my @loanList;
    while (my $loan = $sth->fetchrow_hashref){
        $loan->{'overdue'} = ($loan->{'deltaDateDue'} > 0 ) ? 1:0 ;
        push @tmpList, $loan;
    }
    foreach my $loan (@tmpList){
        my $itemInfo = Opals::Equipment::eq_item_findByBarcode($dbh, $loan->{'barcode'}, 1);
        if ($itemInfo) {
            push @loanList, {
                barcode     => $loan->{'barcode'},
                dateLoan    => $loan->{'dateLoan'},
                dateDue     => $loan->{'dateDue'},
                overdue     => $loan->{'overdue'},
                rid         => $itemInfo->{'rid'},
                rname       => $itemInfo->{'rname'},
                iid         => $itemInfo->{'iid'},
                sfList      => $itemInfo->{'fields'},
                deltaTimeDue=> $loan->{'deltaDateDue'},
                deltaDayDue=> $loan->{'deltaDayDue'}
            };
        }
    }
    $sth->finish;
    return \@loanList;
}

sub circ_getUserListReserve{

    my ($dbh, $uid) = @_;
    my $sth = $dbh->prepare ("select distinct r.* 
                from    eq_reserve as r left outer join eq_hold as h on r.id = h.idReserve
                where   uid = ? && r.dateCancel is null && r.dateExpiry > now() &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))
                order by r.dateReserve desc"          
    );
    #my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});

    $sth->execute($uid) || return;
    my $sth_notExp = $dbh->prepare("select count(*) as nHold, min(dateExpiry) as holdExpiry
                from    eq_hold
                where   idReserve = ? && dateExpiry >= now() && dateCancel is null && dateLoan is null"
        );
    
    my $sth_Exp = $dbh->prepare("select count(*) as nHold
                from    eq_hold
                where   idReserve = ? && dateExpiry < now() && dateCancel is null && dateLoan is null"
        );

    my @reserveList;
    my $rname;
    while(my $reserve = $sth->fetchrow_hashref)    
    {
        my ($rid, $recInfo) = Opals::Equipment::eq_record_findByRId($dbh, {recordId => $reserve->{'rid'}});
        my ($nHold, $nHoldExp) = (0,0);
        $sth_notExp->execute($reserve->{'id'});
        my ($hRec) = $sth_notExp->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'} > 0){
            $nHold = $hRec->{'nHold'};
            $reserve->{'holdExpiry'} = $hRec->{'holdExpiry'};
            $reserve->{'dateExpiry'} = "";
            #$reserve->{'numHold'} = $nHold;
        }
        $sth_Exp->execute($reserve->{'id'});
        my ($hERec) = $sth_Exp->fetchrow_hashref;
        if ($hERec && $hERec->{'nHold'} >0){
            $nHoldExp = $hERec->{'nHold'};
        }

        if ($recInfo){
            $rname = $recInfo->[0]->{'rname'};
=item            
            foreach my $f(@$recInfo) {
                if ($f->{'fId'} == $eqNameId){
                    $rname = $f->{'fValue'};
                    last;
                }
            }
=cut
            push @reserveList, {
                idReserve       =>  $reserve->{'id'},
                rid             =>  $reserve->{'rid'},
                rname           =>  $rname,
                dateReserve     =>  $reserve->{'dateReserve'},
                dateExpiry      =>  $reserve->{'dateExpiry'},
                nReserve        =>  $reserve->{'numCopyReserve'},
                numHold         =>  $nHold,
                numHoldExp      =>  $nHoldExp,
                holdExpiry      =>  $reserve->{'holdExpiry'},
                numCopyReserve  =>  $reserve->{'numCopyReserve'},
            };
        }
    }
    
    $sth->finish;
    $sth_Exp->finish;
    $sth_notExp->finish;
    return \@reserveList;
}

sub circ_getUserLoanHistory{

    my ($dbh, $params) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select l.barcode,l.dateLoan,l.dateReturn  
    from eq_loan l  inner join eq_items i on l.barcode=i.barcode 
    inner join eq_records r on i.rid=r.rid 
    where uid=? && dateReturn is NOT NULL;
_STH_
    my @tmpList ;
    my @loanList;
    $sth->execute($params->{'uid'}) ;
    while (my $loan = $sth->fetchrow_hashref){
        push @tmpList, $loan;
    }
    foreach my $loan (@tmpList){
        my $itemInfo = Opals::Equipment::eq_item_findByBarcode($dbh, $loan->{'barcode'}, 1);
        if ($itemInfo) {
            push @loanList, {
                barcode     => $loan->{'barcode'},
                dateLoan    => $loan->{'dateLoan'},
                dateDue     => $loan->{'dateDue'},
                dateReturn  => $loan->{'dateReturn'},
                rid         => $itemInfo->{'rid'},
                rname       => $itemInfo->{'rname'},
            };
        }
    }
    $sth->finish;
    return \@loanList;
}

sub circ_processReturn {

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

    my $result = $sth->execute($params->{'uid'}, $params->{'barcode'});
    $sth->finish;

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

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

sub circ_placeReserve{
    my ($dbh, $uid, $rid, $numCopyReserve, $dateToday, $dateExpiry ) = @_;
    my $sql = <<_SQL_;
insert into eq_reserve
set rid             = ?,
    uid             = ?,
    numCopyReserve  = ?,
    dateReserve     = ?,
    dateExpiry      = ?
_SQL_

    my $sth = $dbh->prepare($sql);
    my $retVal = $sth->execute($rid, $uid, $numCopyReserve, $dateToday, $dateExpiry);
    $sth->finish;
    return $retVal eq '0E0' ? 0:1;
}

sub circ_fillReserve{
    my ($dbh, $uid, $rid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
update  eq_reserve
set     numCopyReserve = numCopyReserve - 1
where   rid = ? &&
        uid = ? &&
        numCopyReserve > 0 &&
        dateCancel is null &&
        TIMESTAMPDIFF(MINUTE, now(), dateExpiry) >= 0
_STH_
    my $result = $sth->execute($rid, $uid);
    $sth->finish;
    return $result eq '0E0'?0:1;
}

sub circ_cancelReserve{
    my ($dbh, $idReserve) = @_;
    my $sth = $dbh->prepare("Update eq_reserve set dateCancel = now() where id=?");
    my $result = $sth->execute($idReserve);
    $sth->finish;
    return $result eq '0E0'?0:1;
}

sub circ_resetReserve{
    my ($dbh, $idReserve) = @_;
    my $sth = $dbh->prepare("update eq_reserve set numCopyReserve = numCopyReserve + 1 where id=?");
    my $result = $sth->execute($idReserve);
    $sth->finish;
    return $result eq '0E0'?0:1;
}

sub circ_placeHold{
    my ($dbh, $idReserve, $dateHold, $dateExpiry) = @_;
    my $sth = $dbh->prepare("select count(*) as numOfHold from eq_hold where idReserve=$idReserve");
    $sth->execute();
    my ($rec) = $sth->fetchrow_hashref;
    my $timeDif=0;
    if ($rec){
        $timeDif = $rec->{'numOfHold'};
    }
=item    
    $sth = $dbh->prepare("insert into eq_hold (idReserve, dateHold, dateExpiry)
                            values (?, TIMESTAMP(?,CURTIME()) + INTERVAL ? SECOND , ?)");

    my $result = $sth->execute($idReserve, $dateHold, $timeDif, $dateExpiry);
=cut
    $sth = $dbh->prepare("insert into eq_hold (idReserve, dateHold, dateExpiry)
                            values (?, ? , ?)");
    my $result = $sth->execute($idReserve, $dateHold, $dateExpiry);
    $sth->finish;
    return $result eq '0E0'?0:1;
}


sub circ_fillHold{
    my ($dbh, $idReserve, $barcode) = @_;
    my $sth = $dbh->prepare(<<_SQL_);
select  dateHold
from    eq_hold
where   idReserve = ? &&
        dateLoan is null &&
        dateCancel is null &&
        TIMESTAMPDIFF(MINUTE , now(), dateExpiry) >=0
order by dateHold
limit 1
_SQL_

    $sth->execute($idReserve);
    my $rec = $sth->fetchrow_hashref;
    $sth->finish;
    if ($rec && $rec->{'dateHold'}){
        $sth = $dbh->prepare(<<_SQL_);
update  eq_hold
set     barcode     = ?,
        dateLoan    = now()
where   idReserve   = ? &&
        dateHold    = ? &&
        dateLoan is null &&
        dateCancel is null &&
        TIMESTAMPDIFF(MINUTE, now(), dateExpiry) >=0
_SQL_
        my $result = $sth->execute($barcode, $idReserve, $rec->{'dateHold'});
        $sth->finish;
        return $result eq '0E0'?0:1;
    }
    return 0;
}
sub circ_cancelHold_all{
   my ($dbh,$idReserve)=@_;
   my $sth = $dbh->prepare("update eq_hold set dateCancel=now() where idReserve=? ");
   my $result= $sth->execute($idReserve) ;
   $sth->finish;
   return $result eq '0E0'?0:1;
}

sub circ_cancelHold{
    my ($dbh, $idReserve) = @_;
    my $sth = $dbh->prepare(<<_SQL_);
select  * 
from    eq_hold 
where   dateCancel is null && dateLoan is null && idReserve=? order by dateHold limit 0,1
_SQL_
    $sth->execute($idReserve);
    my ($rec) = $sth->fetchrow_hashref;
    $sth->finish;
    if ($rec && $rec->{'idReserve'}){
        $sth = $dbh->prepare("update eq_hold set dateCancel = now() where idReserve=? && dateHold=?");
        my $result = $sth->execute($idReserve, $rec->{'dateHold'});
        $sth->finish;
        return $result eq '0E0'?0:1;
    }
    return 0;
}

sub circ_declareLost{
    
    my ($dbh,$uid,$barcode) = @_;
    my ($result, $retV_1, $retV_2,  $retV_3 ) = (0,0,0,0);
    #return item
    my $sql = "update eq_loan set dateReturn=now() where uid=? && barcode = ? && dateReturn is null";
    my $sth = $dbh->prepare($sql);
    $retV_1 = $sth->execute($uid, $barcode);
    $sth->finish;

    # Set item's status is not available
    $sql = "update eq_items set available=0 where barcode=?";
    $sth = $dbh->prepare($sql);
    $retV_2 = $sth->execute($barcode);
    $sth->finish;

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

    $sth->finish;
    
    $retV_1 = ($retV_1 eq '0E0' )? 0:1;
    $retV_2 = ($retV_2 eq '0E0' )? 0:1;
    $retV_3 = ($retV_3 eq '0E0' )? 0:1;

    $result = ($retV_1 && $retV_2 && $retV_3);
    return $result;
}

sub circ_setAvailable{
    
    my ($dbh, $barcode, $oldStatus) = @_;
    my $sql    = "insert into eq_itemStatus set barcode=?, ondate=now(), status=1";
    my $sth    = $dbh->prepare($sql);
    my $retVal = $sth->execute($barcode);
    $sth->finish;
    $dbh->do("update eq_items set available=1 where barcode=$barcode && available=0");
    return $retVal eq '0E0' ? 0:1;

}
sub circ_updateItemStatus{
    my ($dbh, $barcode, $status) = @_;
    my $sth = $dbh->prepare("insert into eq_itemStatus set barcode = ?, ondate=now(), status=?");
    $sth->execute($barcode, $status);
    $sth->finish;
    my $avail = ($status ==ITEM_LOST || $status ==ITEM_IN_REPAIR || $status == ITEM_INACTIVE || $status ==ITEM_WEED)?0:1;
    $sth = $dbh->prepare("update eq_items set available=? where barcode=?");
    $sth->execute($avail,$barcode);
    $sth->finish;
}

#-----------------------------------------------
# function circ_getReserveId
# check if user has reserve this record (any item of this record)
#------------------------------------------------
sub  circ_getReserveId{
    my ($dbh, $rid, $uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select  id 
from    eq_reserve 
where   rid = ? && uid = ? && numCopyReserve > 0 && dateCancel is null && dateExpiry > now()
_STH_
    $sth->execute($rid, $uid);
    my ($rec) = $sth->fetchrow_hashref;
    $sth->finish;
    return $rec->{'id'};
}

sub circ_getLoanListByRid{

    my ($dbh, $rid) =@_;
    my $sth = $dbh->prepare(<<_STH_);
SELECT  i.barcode, l.uid, l.dateDue
FROM    eq_items as i INNER JOIN eq_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_record_ODL{   
    
    my ($dbh, $idloan,$type,$days_overdue) = @_;
    my $szSQL = "insert into eq_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;

}



1;