package Opals::Eq_Circulation;
use Opals::Date qw(
    date_time_text
);

require Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    
    circ_getItemInfo
    eq_circ_getItemInfo

    circ_getItemStatus
    eq_circ_getItemStatus
    
    _eq_circ_getItemStatus

    circ_getNumItemOnReserve
    
    circ_getUserListLost
    circ_getUserListNeverReturned
    circ_getUserListDamaged
    circ_getUserListLoan
    circ_getUserLoanList
    eq_circ_getUserLoanList
    circ_getUserListReserve
    circ_getUserLoanHistory
    eq_circ_getUserLoanHistory
    
    circ_getNumActiveItem
    circ_getNumItemsAvailable
    eq_circ_getNumItemsAvailable
    circ_getNumItemOnLoan
    circ_getNumItemOnHold

    circ_getActiveLoanId
    eq_circ_getActiveLoanId
    eq_circ_getRecCircStatus
    
    circ_infoRecord
    eq_circ_infoRecord

    circ_getRecordInfo
    eq_circ_getRecordInfo

    circ_getUserCircStatus
    eq_circ_getUserCircStatus

    eq_circ_processLoan
    circ_processLoan
    circ_processReturn
    eq_circ_processReturn

    circ_processRenew
    eq_circ_processRenew

    circ_placeReserve
    circ_fillReserve
    circ_cancelReserve
    circ_resetReserve

    circ_placeHold
    circ_fillHold
    circ_cancelHold
    circ_cancelHold_all
    circ_isOnHold

    circ_getReserveId

    circ_declareLost
    circ_updateItemStatus
    eq_circ_updateItemStatus

    circ_setAvailable

    circ_getLoanListByRid
    eq_circ_getLoanListByRid

    circ_record_ODL
    eq_circ_anonymizeLoan
    eq_circ_getOnLoanInfo
    eq_circ_consumableMgmt

) ;

use Opals::Constant;

use Opals::Equipment qw(
    eq_def_getFieldId
    eq_item_findByBarcode
    eq_item_findByRId
    eq_record_findByRId
);

use Opals::Date qw(
    date_now
    date_today
    date_validateWorkday
    date_addDeltaWorkday
    date_text
    date_getDeadLineDate
    dateTime_parse
    date_deltaWorkDayHour
    date_deltaWeek
    date_parse
    date_calcHourOverdue
);

# Version number
$VERSION   = 0.01;      
use JSON;
#use utf8;
use strict;

sub circ_getItemInfo {
  my ($dbh, $barcode,$uid) = @_;
  return eq_circ_getItemInfo($dbh,$barcode,$uid);
}

sub eq_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 _eq_circ_getItemStatus{
    my($dbh,$params) = @_;
    return if (!$params->{'barcode'} || $params->{'barcode'} eq '');
    my $retVal = {
        status      => IT_STAT_FULL_AVAIL,
    };
    my  $sql = $dbh->prepare(<<_SQL_);
select  i.rid,i.barcode,i.available,r.container,s.status,s.ondate,s.note
from    eq_item i 
    inner join eq_record r using(rid)
    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;
    $retVal->{'container'} = $item->{'container'};
    $retVal->{'notes'} = $item->{'note'};
    if (!$item->{'available'} ){
        if ($item->{'status'} == ITEM_LOST){
            $retVal->{'status'} = IT_STAT_LOST;
        } 
        elsif ($item->{'status'} == ITEM_INACTIVE) {
            $retVal->{'status'} = IT_STAT_MISSING;
        }
        elsif ($item->{'status'} == ITEM_IN_REPAIR) {
            $retVal->{'status'} = IT_STAT_IN_REPAIR;
        }
        elsif ($item->{'status'} == ITEM_IN_PROCESSING) {
            $retVal->{'status'} = IT_STAT_IN_PROCESSING;
        }
        elsif ($item->{'status'} == ITEM_WEED) {
            $retVal->{'status'} = IT_STAT_WEED;
        }
        else{
            $retVal->{'status'} = IT_STAT_UNAVAIL;
        }
        return $retVal;
    }
    if ($item->{'status'} == ITEM_DAMAGED ){
      $retVal->{'status'} = IT_STAT_DAMAGED;  
    }
    if ($item->{'status'} == ITEM_LOST){
      $sql = "select l.id,l.barcode, u.firstname, u.lastname,u.userbarcode from    eq_loan l left outer join opl_user u using(uid) 
        where l.barcode = ? order by id desc limit 1";
        my $itemLost = $dbh->selectrow_hashref($sql, undef,$params->{'barcode'});
        if ($itemLost){
          $retVal->{'itemLost'} = $itemLost;
        }
    }

#check if item is on loan?
    $sql = $dbh->prepare(<<_SQL_);
select l.*, u.firstname, u.lastname,u.userbarcode,u.grade
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'};
        $retVal->{'patron_barcode'} = $item->{'userbarcode'};
        $retVal->{'patron_grade'}   = $item->{'grade'};

    }
    
#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 eq_circ_getItemStatus{
    my($dbh,$p) = @_;
    return circ_getItemStatus($dbh,$p);
}
sub circ_getItemStatus{
    my($dbh,$params) = @_;
    return if (!$params->{'barcode'} || $params->{'barcode'} eq '');
    my $retVal = {
        status      => IT_STAT_FULL_AVAIL,
    };
    my  $sql = $dbh->prepare(<<_SQL_);
select  i.rid,i.barcode,i.available,r.container,s.status,s.ondate,s.note,i.consumable,i.qty
from    eq_items i 
    inner join eq_records r using(rid)
    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;
    $retVal->{'container'} = $item->{'container'};
    $retVal->{'notes'} = $item->{'note'};
    $retVal->{'consumable'} = $item->{'consumable'} || 0;
    
    if (!$item->{'available'} ){
        if ($item->{'status'} == ITEM_LOST){
            $retVal->{'status'} = IT_STAT_LOST;
        } 
        elsif ($item->{'status'} == ITEM_INACTIVE) {
            $retVal->{'status'} = IT_STAT_MISSING;
        }
        elsif ($item->{'status'} == ITEM_IN_REPAIR) {
            $retVal->{'status'} = IT_STAT_IN_REPAIR;
        }
        elsif ($item->{'status'} == ITEM_IN_PROCESSING) {
            $retVal->{'status'} = IT_STAT_IN_PROCESSING;
        }
        elsif ($item->{'status'} == ITEM_WEED) {
            $retVal->{'status'} = IT_STAT_WEED;
        }
        else{
            $retVal->{'status'} = IT_STAT_UNAVAIL;
        }
        return $retVal;
    }
    if ($item->{'status'} == ITEM_DAMAGED ){
      $retVal->{'status'} = IT_STAT_DAMAGED;  
    }
    if ($item->{'status'} == ITEM_LOST){
      $sql = "select l.id,l.barcode, u.firstname, u.lastname,u.userbarcode from eq_loan l left outer join opl_user u using(uid) 
        where l.barcode = ? order by id desc limit 1";
        my $itemLost = $dbh->selectrow_hashref($sql, undef,$params->{'barcode'});
        if ($itemLost){
          $retVal->{'itemLost'} = $itemLost;
        }
    }

#check if item is on loan?
    $sql = $dbh->prepare(<<_SQL_);
select l.*, u.firstname, u.lastname,u.userbarcode,u.grade
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'});
    $retVal->{'numLoan'} = 0;
    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'};
        $retVal->{'patron_barcode'} = $item->{'userbarcode'};
        $retVal->{'patron_grade'}   = $item->{'grade'};
        $retVal->{'numLoan'} = 1;
    }
   $sql= "select p.* from opl_itemTypeParam p inner join eq_items i on i.typeId=p.itemTypeId 
          inner join eq_loan l using(barcode) inner join opl_user u using(uid) 
          where l.barcode=? && u.categorycode=p.userTypeId";
   $item = $dbh->selectrow_hashref($sql, undef,$params->{'barcode'});
   if ($item){
       $retVal->{'maxRenewal'}     = $item->{'maxRenewal'};
   }

#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 $sth = $dbh->prepare(
      "select  count(*) as count from eq_items where available=1 && rid=?");
    $sth->execute($rid);
    my $rec = $sth->fetchrow_hashref;
    if ($rec) {
      return $rec->{'count'};
    }
    return 0;
}

sub circ_getNumItemOnLoan{
    my ($dbh, $rid,$onDate) = @_;
=item    
    #my $displaySF_0 = 0;
    my $itemList = Opals::Equipment::eq_item_findByRId($dbh, $rid, 0);

    my $sql = "select count(*) as count from eq_loan where barcode = ? && dateReturn is null " ;

    my $sth = $dbh->prepare($sql);
    my $totalLoan= 0;
    foreach my $i(@$itemList){
        $sth->execute($i->{'barcode'}) || return;
        my ($c ) = $sth->fetchrow_hashref;
        if ($c){
            $totalLoan += $c->{'count'};
        }
    }
=cut
    my $sql = "select count(*) as count from eq_loan as l inner join eq_items  as i on l.barcode = i.barcode 
                            where i.rid=$rid && datereturn is null ";
  
    if (defined $onDate
        && $onDate =~ m/^\d{4}-\d{1,2}-\d{1,2}( \d{1,2}:\d{1,2}:\d{1,2}){0,1}$/g) {
        $sql .= " && l.dateDue> '$onDate'";
    }
  
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $rec = $sth->fetchrow_hashref;
    if ($rec) {
        return $rec->{'count'};
    }
    
    $sth->finish;
    return 0;
}

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_getUserListLost{
    my ($dbh, $uid,$lost ) = @_;
    my $sql = "select l.barcode, max(odl.ondate) as lostdate,s.note
            from eq_odl odl 
            inner join eq_loan l on l.id = odl.idloan 
            inner join eq_itemStatus as s on (s.ondate=odl.ondate and l.barcode=s.barcode and s.status=?)
            where uid = ? && odl.type regexp 'lost'
            && l.barcode in(
		      	select i.barcode
            from eq_items i inner join
            (select ist1.* from
                (select * from eq_itemStatus ) as ist1
                left outer join
                (select * from eq_itemStatus )as ist2
            on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)
            as ist on i.barcode=ist.barcode )
            group by l.barcode  order by lostdate ";

   my @value =  ($lost,$uid,$lost);
   my $itemList =  $dbh->selectall_arrayref($sql, { Slice => {} }, @value);
   my @lostList=();
   foreach my $i (@$itemList) {
       my $itemInfo = eq_item_findByBarcode($dbh,$i->{'barcode'});
       push @lostList, {
            name    => $itemInfo->{'rname'},
            barcode => $i->{'barcode'},
            price   => $itemInfo->{'fieldById'}->{'6'}->{'value'},
            lostdate=> $i->{'lostdate'},
            dateLost=> date_text($i->{'lostdate'},1,'en'),
            notes   => $i->{'note'}
       };
   }
   return \@lostList;
}
sub circ_getUserListNeverReturned{
    my ($dbh, $uid,$neverReturned) = @_;
    my $sql = "select l.barcode, max(odl.ondate) as lostdate,s.note
            from eq_odl odl 
            inner join eq_loan l on l.id = odl.idloan 
            inner join eq_itemStatus as s on (s.ondate=odl.ondate and l.barcode=s.barcode and s.status=?)
            where uid = ? && odl.type regexp 'neverReturned'
            && l.barcode in(
		      	select i.barcode
            from eq_items i inner join
            (select ist1.* from
                (select * from eq_itemStatus ) as ist1
                left outer join
                (select * from eq_itemStatus )as ist2
            on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)
            as ist on i.barcode=ist.barcode )
            group by l.barcode  order by lostdate ";

   my @value =  ($neverReturned,$uid,$neverReturned);
   my $itemList =  $dbh->selectall_arrayref($sql, { Slice => {} }, @value);
   my @lostList=();
   foreach my $i (@$itemList) {
       my $itemInfo = eq_item_findByBarcode($dbh,$i->{'barcode'});
       push @lostList, {
            name    => $itemInfo->{'rname'},
            barcode => $i->{'barcode'},
            price   => $itemInfo->{'fieldById'}->{'6'}->{'value'},
            lostdate=> $i->{'lostdate'},
            dateLost=> date_text($i->{'lostdate'},1,'en'),
            notes   => $i->{'note'}
       };
   }
   return \@lostList;
}

sub circ_getUserListDamaged{
    my ($dbh, $uid,$damaged ) = @_;
=item
    my $sql = "select l.barcode, max(odl.ondate) as damageddate,s.note
            from eq_odl odl 
            inner join eq_loan l on l.id = odl.idloan 
            inner join eq_itemStatus as s on (TIME_TO_SEC(timediff(s.ondate,odl.ondate)<2) and l.barcode=s.barcode and s.status=?)
            where uid = ? && odl.type regexp 'damaged'
            && l.barcode in(
		      	select i.barcode
            from eq_items i inner join
            (select ist1.* from
                (select * from eq_itemStatus ) as ist1
                left outer join
                (select * from eq_itemStatus )as ist2
            on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status = ?)
            as ist on i.barcode=ist.barcode )
            group by l.barcode  order by damageddate ";
	 my @value =  ($damaged,$uid,$damaged);
=cut
#TIME_TO_SEC(timediff(its.ondate,odl.ondate)<2)
  my $sql="select l.barcode,its.note,its.ondate as damageddate from eq_odl odl 
    inner join eq_loan l on odl.idloan=l.id && odl.type='damaged' 
    inner join eq_itemStatus its on its.barcode=l.barcode && 
    ( TIME_TO_SEC(timediff(its.ondate,odl.ondate) between -10 and 10))
  where its.status=? && l.uid=?";
	 my @value =  ($damaged,$uid);
   my $itemList =  $dbh->selectall_arrayref($sql, { Slice => {} }, @value);
   my @damagedList=();
   foreach my $i (@$itemList) {
       my $itemInfo = eq_item_findByBarcode($dbh,$i->{'barcode'});
       push @damagedList, {
            name    => $itemInfo->{'rname'},
            barcode => $i->{'barcode'},
            price   => $itemInfo->{'fieldById'}->{'6'}->{'value'},
            damageddate=> $i->{'damageddate'},
            dateDamaged=> date_text($i->{'damageddate'},1,'en'),
            notes   => $i->{'note'}
       };
   }
   return \@damagedList;
}


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

sub circ_getNumItemOnHold{
    my ($dbh, $rid, $onDate) = @_;
    if (!defined $onDate || $onDate eq '') {
      $onDate = date_now();
    }
    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 > '$onDate' ");
    $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,$onDate) = @_;
    my $numOfHolds          = circ_getNumItemOnHold($dbh, $rid,$onDate);
    my $numOfActiveItems    = circ_getNumActiveItem($dbh,$rid);
    my $numOfLoans          = circ_getNumItemOnLoan($dbh, $rid,$onDate);
    my $numItemsAvailable   = $numOfActiveItems - $numOfLoans - $numOfHolds;
    return $numItemsAvailable;
}
sub eq_circ_getNumItemsAvailable{
  my ($dbh, $rid,$onDate) = @_;
  return circ_getNumItemsAvailable($dbh, $rid,$onDate);
}

#---------------------------------------------
# funtion circ_getRecordInfo
# return record info + hold, reserve period based on user type
#---------------------------------------------
sub eq_circ_getRecordInfo{
    my ($dbh, $rid, $uid) =@_;
    return circ_getRecordInfo($dbh,$rid,$uid);
}

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'};
    my $fieldId = [1,3];
    my ($manufacturer,$model)=("","");
    foreach my $r(@$recInfo) {
      if($r->{'fId'} == 1){
        $manufacturer = $r->{'fValue'};
      }
      if($r->{'fId'} == 3){
        $model = $r->{'fValue'};
      }
    }
=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){
=item
            $sql = <<_SQL_;
select  holdPeriod , reservePeriod 
from    opl_itemTypeParam
where   itemTypeId = 'EQMNT' && 
        userTypeId = ?
_SQL_
=cut
    $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    eq_items i , opl_itemTypeParam t
where   i.rid=? && t.itemTypeId = i.typeId && t.userTypeId = ?
_SQL_
        my $sth = $dbh->prepare($sql);
        $sth->execute($rid,$userTypeId);
        my ($infoPeriod) = $sth->fetchrow_hashref;
        $sth->finish;
        push @recordInfo, {
            rid             =>  $rid,
            rname           =>  $rname,
            manufacturer    =>  $manufacturer,
            model           =>  $model,
            holdPeriod      =>  $infoPeriod->{'holdPeriod'},
            reservePeriod   =>  $infoPeriod->{'reservePeriod'},
            };
        }
    }
    return $recordInfo[0];
}

sub eq_circ_getUserCircStatus {
  my ($dbh, $uid, $barcode) = @_;
  return circ_getUserCircStatus($dbh,$uid,$barcode);
}

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;
}

#2018-03-26 10:27:25 EDT, Mon Mar
sub eq_circ_getActiveLoanId {
  my ($dbh, $barcode, $uid) = @_;
  return circ_getActiveLoanId($dbh,$barcode,$uid);
}
sub circ_getActiveLoanId {
  my ($dbh, $barcode, $uid) = @_;
  my $sth = $dbh->prepare(
    "select id from eq_loan where barcode=? && uid=? && dateReturn is null"
  );
  $sth->execute($barcode, $uid);
  my $rec = $sth->fetchrow_hashref;
  $sth->finish;
  return $rec->{'id'};
}

sub eq_circ_getRecCircStatus {
  my ($dbh,$rid ) = @_;
  my ($numTotal,$numLoan,$numReserve,$numHold,$itemList,$totalCirc,$totalMissing,$totalDamaged,$totalLost)=circ_infoRecord($dbh,$rid);
  my $available=$numTotal-$numLoan-$numReserve-$numHold-$totalMissing-$totalLost;
  $available = 0 if ($available < 0);

  return {
    totalHolding      => $numTotal || 0,
    onLoan            => $numLoan || 0,
    onReserve         => $numReserve || 0,
    onHold            => $numHold || 0,
    numAvail          => $available || 0,
    countLost         => $totalLost || 0,
    countDamaged      => $totalDamaged || 0,
    countMissing      => $totalMissing || 0,
  };
}

#-------------------------------
#   function :circ_infoRecord (see also circ_getRecordInfo*)
#   return number of items, number of items on loan, on reserve, on hold, on circulation  
#------------------------------
sub eq_circ_infoRecord {
  my ($dbh,$rid ) = @_;
  my ($numTotal,$numLoan,$numReserve,$numHold,$itemList,$totalCirc,$totalMissing,$totalDamaged,$totalLost)=circ_infoRecord($dbh,$rid);
  my $available=$numTotal-$numLoan-$numReserve-$numHold-$totalMissing-$totalLost;
  $available = 0 if ($available < 0);

  return {
    totalHolding      => $numTotal || 0,
    onLoan            => $numLoan || 0,
    onReserve         => $numReserve || 0,
    onHold            => $numHold || 0,
    numAvail          => $available || 0,
    countLost         => $totalLost || 0,
    countDamaged      => $totalDamaged || 0,
    countMissing      => $totalMissing || 0,
    numLoan           => $numLoan || 0,
    numReserve        => $numReserve || 0,
    numHold           => $numHold || 0,
    numLost         => $totalLost || 0,
    numDamaged      => $totalDamaged || 0,
    numMissing      => $totalMissing || 0,
    #itemList          => $itemList
  };
}


sub circ_infoRecord {
    my ($dbh,$rid ) = @_;
    my ($numTotal,$numLoan,$numReserve,$numHold,$totalCirc,$totalMissing,$totalDamaged,$totalLost,$totalInRepair,$totalWeed,$totalDeleted,$totalNeverReturn)=(0,0,0,0,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 $quantity=0;
    my $sth = $dbh->prepare(<<_STH_);
select barcode, available,consumable,qty from eq_items where rid = ? && barcode not regexp '^___'
_STH_
    $sth->execute($rid) || return;
    my $holdingAvail;
    while (my ($bc, $avail,$consumable,$qty) = $sth->fetchrow_array) {
        $holdingAvail->{($bc)} = $avail;
        $numTotal++;
        if ($consumable){
          $quantity = $qty;
        }
    }
    $sth->finish;
    foreach my $i (@$itemList) {
        $i->{'available'} = $holdingAvail->{$i->{'barcode'}};
    }
#get item status
    my $sth_status = $dbh->prepare(<<_STH_);
select  barcode, status,note
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,$note);
    foreach my $i(@$itemList){
        $sth_status->execute($i->{'barcode'},ITEM_NEW) || return;
        ($mBc, $status,$note) = $sth_status->fetchrow_array;
		  $i->{'statusNote'} = $note;
        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++;  }
			elsif ($status == ITEM_IN_REPAIR){
            $i->{'inRepair'} = 1; 
            $totalInRepair++;  }		
      elsif ($status == ITEM_CLAIM_NEVER_RETURNED){
          $i->{'claimNeverReturned'} = 1; 
      $totalNeverReturn++; }
      elsif ($status == ITEM_WEED){
          $i->{'weed'} = 1; 
      $totalWeed++; }
      elsif ($status == ITEM_DELETED){
          $i->{'deleted'} = 1; 
      $totalDeleted++; }
      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;
    $numHold = $numHold>0?$numHold:0;
    $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,$totalInRepair,$totalWeed,$totalNeverReturn,$quantity);
}

sub eq_circ_processLoan {
    my ($dbh, $params) = @_;
    return circ_processLoan($dbh,$params);
}
sub circ_processLoan {
    my ($dbh, $params) = @_;
    # Get item info i.e.: maxRenewal, fine...
    return undef if ($params->{'barcode'} eq "");
    my $itemInfo = circ_getItemInfo($dbh, $params->{'barcode'}, $params->{'uid'});
    my $maxRenewal = $itemInfo->{'maxRenewal'};
    my $finerate   = ($itemInfo->{'fine'})?$itemInfo->{'fine'}:0;
    my $qty = $params->{'qty'} || 1;
    my $dayTimeNow = date_now();
    my $dateLoan = $params->{'dateLoan'} || $dayTimeNow;
    if ($dateLoan =~ m/(\d{4}-\d{1,2}-\d{1,2})( (\d{1,2}:\d{1,2}:\d{1,2})){0,1}/g)  {
    my $tmpDate = $1;
    if ($3 =~ m/0{1,2}:0{1,2}:0{1,2}/g || $3 eq '') {
      my $time = substr $dayTimeNow, 11, 8;
      $dateLoan = "$tmpDate $time";
      }
    }
    else {
      $dateLoan = $dayTimeNow;
    }
    my $sql = <<_SQL_;
insert into eq_loan
set     uid         = ?,
        barcode     = ?,
        maxRenewal  = ?,
        dateLoan    = ?,
        dateDue     = ?,
        finerate    = ?,
        qty         = ?
_SQL_
    my $sth = $dbh->prepare($sql);
    my $rv = $sth->execute($params->{'uid'},$params->{'barcode'},$maxRenewal,$dateLoan,$params->{'dateDue'}, $finerate,$qty);
    $sth->finish;  
    if($rv ne'0E0'){
      my $idloan=$dbh->{'mysql_insertid'};
      updateLoanStats($dbh,$params->{'uid'},$params->{'barcode'},$idloan,$params->{'dateLoan'});
    }
    return ($rv eq '0E0') ? LOAN_UNFINISH : LOAN_FINISH;           
}

sub circ_getUserListLoan{
    my ($dbh, $params) = @_;
    my $sql = "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 inner join eq_items using(barcode) where uid=? && dateReturn is null";
    if ($params->{'type'} && $params->{'type'} eq "od"){
      $sql .= " && dateDue<now()";
    }
    my $sth = $dbh->prepare($sql);
    $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);
        my ($rid,$recFields) = Opals::Equipment::eq_record_findByRId($dbh,{recordId=>$itemInfo->{'rid'}});
        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'},
                rfList      => $recFields 
            };
        }
    }
    $sth->finish;
    return \@loanList;
}
sub eq_circ_getUserLoanList {
    my ($dbh, $p) = @_;
    return circ_getUserLoanList($dbh,$p);
}

sub circ_getUserLoanList{
    my ($dbh, $p) = @_;
    my $sqlCount = "select count(*) from eq_loan inner join eq_items using(barcode) where uid=? && dateReturn is null";
    my $sqlOdCount = "select count(*) from eq_loan inner join eq_items using(barcode) where uid=? && dateReturn is null && dateDue<=now();";
    my @bind_values = ();
    push @bind_values,$p->{'uid'};
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef,@bind_values);
    my ($odCount) = $dbh->selectrow_array($sqlOdCount, undef,@bind_values);
    my $sql = "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 inner join eq_items using(barcode) where uid=? && dateReturn is null";
    if ($p->{'type'} && $p->{'type'} eq "od"){
      $sql .= " && dateDue<now()";
    }
    $sql .= " order by dateLoan desc ";
    my $row_count= $p->{'pSize'}>0?$p->{'pSize'}:$resultSize;
    my $offset=0;
    if (defined $p->{'pNum'} && $p->{'pNum'}>0 && defined $p->{'pSize'} && $p->{'pSize'}>0 && $resultSize>$p->{'pSize'}){
      $offset = ($p->{'pNum'}-1) * $p->{'pSize'};
      ($offset >= 0) || ($offset=0);
      $offset = $offset>$resultSize? $resultSize:$offset;
      $sql .= " limit $offset, $row_count";
    }
    elsif(defined $p->{'offset'} && $p->{'offset'}>0){
      $sql .= " limit $p->{'offset'}";
    }
    else{
      $sql .= " limit $resultSize";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute($p->{'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);
        my ($rid,$recFields) = Opals::Equipment::eq_record_findByRId($dbh,{recordId=>$itemInfo->{'rid'}});
        if ($itemInfo) {
            push @loanList, {
                barcode     => $loan->{'barcode'},
                dateLoan    => $loan->{'dateLoan'},
                dateDue     => $loan->{'dateDue'},
                overdue     => $loan->{'overdue'},
                rid         => $itemInfo->{'rid'},
                rname       => $itemInfo->{'rname'},
                name        => $itemInfo->{'rname'},
                equipment   => $itemInfo->{'rname'},
                iid         => $itemInfo->{'iid'},
                sfList      => $itemInfo->{'fields'},
                deltaTimeDue=> $loan->{'deltaDateDue'},
                deltaDayDue => $loan->{'deltaDayDue'},
                rfList      => $recFields 
            };
        }
    }
    $sth->finish;
    return ({list=>\@loanList,size=>$resultSize,odCount=>$odCount});
}
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"          
    );
    $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'};
        }
        my ($manufacturer,$model)=("","");
        foreach my $r(@$recInfo) {
          if($r->{'fId'} == 1){
            $manufacturer = $r->{'fValue'};
          }
          if($r->{'fId'} == 3){
            $model = $r->{'fValue'};
          }
        }
        $rname = $recInfo->[0]->{'rname'};
            push @reserveList, {
                idReserve       =>  $reserve->{'id'},
                rid             =>  $reserve->{'rid'},
                rname           =>  $rname,
                name            =>  $rname,
                manufacturer    =>  $manufacturer,
                model           =>  $model,
                dateReserve     =>  $reserve->{'dateReserve'},
                dateReserve_txt =>  date_time_text($reserve->{'dateReserve'},0,'en'),
                dateExpiry      =>  $reserve->{'dateExpiry'},
                dateExpiry_txt  =>  date_time_text($reserve->{'dateExpiry'},0,'en'),
                nReserve        =>  $reserve->{'numCopyReserve'},
                numHold         =>  $nHold,
                numHoldExp      =>  $nHoldExp,
                holdExpiry      =>  $reserve->{'holdExpiry'},
                holdExpiry_txt  =>  date_time_text($reserve->{'holdExpiry'},0,'en'),
                numCopyReserve  =>  $reserve->{'numCopyReserve'},
                ncr  =>  $reserve->{'numCopyReserve'}
            };
    }
    
    $sth->finish;
    $sth_Exp->finish;
    $sth_notExp->finish;
    return \@reserveList;
}
sub circ_getUserLoanHistory{
    my ($dbh, $params) = @_;
    return eq_circ_getUserLoanHistory($dbh,$params);
}

sub eq_circ_getUserLoanHistory{

    my ($dbh, $params) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select l.barcode,l.dateLoan,l.dateReturn,l.dateDue,s.note  
    from eq_loan l  inner join eq_items i on l.barcode=i.barcode
    inner join eq_records r on i.rid=r.rid 
    left outer join eq_itemStatus s on s.barcode=i.barcode and s.ondate=l.dateReturn
    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) {
            $itemInfo->{'status'} = circ_getItemStatus($dbh,{barcode=>$loan->{'barcode'}});
            push @loanList, {
                barcode     => $loan->{'barcode'},
                dateLoan    => $loan->{'dateLoan'},
                dateDue     => $loan->{'dateDue'},
                dateReturn  => $loan->{'dateReturn'},
                notes       => $loan->{'note'},
                rid         => $itemInfo->{'rid'},
                rname       => $itemInfo->{'rname'},
                name        => $itemInfo->{'name'},
                itemStatus  => $itemInfo->{'status'},
            };
        }
    }
    $sth->finish;
    return \@loanList;
}

sub eq_circ_processReturn {
    my ($dbh, $params) = @_;
    return circ_processReturn($dbh,$params);
}
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 eq_circ_processRenew {
    my ($dbh, $params) = @_;
    return circ_processRenew($dbh, $params);
}

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,$notes) = @_;
    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=?,note=?";
    $sth    = $dbh->prepare($sql);
    $retV_3 = $sth->execute($barcode, ITEM_LOST,$notes);

    $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,$note) = @_;
	 $note =  $note||"";
    my $sql    = "insert into eq_itemStatus set barcode=?, ondate=now(), status=1, note=?";
    my $sth    = $dbh->prepare($sql);
    my $retVal = $sth->execute($barcode,$note);
    #$dbh->do("update eq_items set available=1 where barcode=$barcode && available=0");
    $sth = $dbh->prepare("update eq_items set available=1 where barcode=? && available=0");
	 $sth->execute($barcode);
    $sth->finish;
    return $retVal eq '0E0' ? 0:1;

}
sub eq_circ_updateItemStatus{
    my ($dbh,$barcode,$status,$note) = @_;
    circ_updateItemStatus($dbh,$barcode,$status,$note);
}

sub circ_updateItemStatus{
    my ($dbh, $barcode, $status,$note) = @_;
    my $sth = $dbh->prepare("insert into eq_itemStatus set barcode=?,ondate=now(),status=?,note=?");
    $sth->execute($barcode, $status,$note);
    $sth->finish;
    my $avail=($status==ITEM_LOST||$status==ITEM_IN_REPAIR||$status==ITEM_INACTIVE||$status==ITEM_WEED||$status==ITEM_DELETED||$status==ITEM_CLAIM_NEVER_RETURNED)?0:1;
    $sth = $dbh->prepare("update eq_items set available=?,note=? where barcode=?");
    $sth->execute($avail,$note,$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 eq_circ_getLoanListByRid{
    my ($dbh, $rid) =@_;
    return circ_getLoanListByRid($dbh,$rid);
}

sub circ_getLoanListByRid{
    my ($dbh, $rid) =@_;
    my $sth = $dbh->prepare(<<_STH_);
SELECT  u.uid,u.firstname,u.lastname,u.buildingcode,u.homeroom,u.teacher,u.grade,i.barcode, l.uid, l.dateDue
FROM    eq_items as i 
				inner join eq_loan as l on i.barcode = l.barcode 
				inner join opl_user u on u.uid=l.uid
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;
}

sub eq_circ_anonymizeLoan{
    my($dbh)=@_;
    #my ($valStr)=$dbh->selectrow_array("select val from opl_preference where var='anonymizeLoan'");
    my ($valStr)=$dbh->selectrow_array("select val from opl_preference where var='eqAnonymizeLoan'");
    if(defined $valStr && $valStr ne''){
        my @dayArr=split(',',$valStr);
        foreach my $ndays (sort @dayArr){
            if($ndays>0){ 
                my $sql="update eq_loan l inner join  opl_user u using(uid)  set l.uid=0 
                         where dateReturn is not null && u.keepLoanHistory=0 ";
                if($ndays>50*365){
                    my ($dateFirst)=$dbh->selectrow_array("select val from opl_preference where var='dateFirst'");
                    $sql .=" && dateLoan<'$dateFirst' ";
                }
                else{
                    $sql .=" && date_add(dateLoan, interval $ndays day) <now()";
                }
                $dbh->do($sql);
            }
            else{
                last;
            }
        }
    }
}
sub updateLoanStats{
    my($dbh,$uid,$barcode,$idloan,$dateLoan)=@_;
    #keep loan stats
    my $sth = $dbh->prepare("insert into opl_circArchive set idloan=?,uid=?,kind='EQ'");
    my $rv = $sth->execute($idloan,$uid);
    $sth->finish;  
}


sub eq_circ_getOnLoanInfo {
  my ($dbh, $barcode, $ondate) = @_;

  my $sql = <<_SQL_;
select  l.*,u.firstname,u.lastname
from    eq_loan l left outer join opl_user u using(uid)
where   barcode = ?
     && dateReturn is null
_SQL_

  my @val = ($barcode);
  my $loan = $dbh->selectrow_hashref($sql, undef, @val) || return;
  my $idloan   = $loan->{'id'};
  my $notes    = $loan->{'notes'}||{};
  my $dateRet  = dateTime_parse($ondate);
  my $dateDue  = dateTime_parse($loan->{'dateDue'});
  my $timeDd   = "23:59:59";
  my $loanType = 'daily';
  my ($mumOfWeeksOD,$mumOfDaysOD, $mumOfHoursOD, $mumOfMinuteOD) =(0,0,0,0);
  if ($loan->{'dateDue'} =~ m/(\d\d\d\d-\d\d-\d\d) (\d\d:\d\d:\d\d)/g) {
    $timeDd = $2;
  }
  if ($timeDd ne "23:59:59") {    #Case: Hourly Circ
    $loanType = 'hourly';
    ($mumOfHoursOD, $mumOfMinuteOD) = date_calcHourOverdue($dateDue, $dateRet);
  }
  else{
      ($mumOfDaysOD, $mumOfHoursOD, $mumOfMinuteOD) = date_deltaWorkDayHour($dateDue, $dateRet);
  }
  $mumOfWeeksOD=date_deltaWeek($dateDue,$dateRet);

  my $odl_id = 0;

  # check grace period
  my $itemInfo = circ_getItemInfo($dbh, $barcode, $loan->{'uid'});
  my ($graceDay, $graceHour, $graceiMinute) = (0, 0, 0);
  if ( defined $itemInfo->{'gracePeriod'}
    && defined $itemInfo->{'graceUnit'}) {
    if ($itemInfo->{'graceUnit'} eq 'day') {
      $graceDay = $itemInfo->{'gracePeriod'};
    }
    elsif ($itemInfo->{'graceUnit'} eq 'minute') {
      $graceiMinute = $itemInfo->{'gracePeriod'};
    }
    else {
      $graceHour = $itemInfo->{'gracePeriod'};
    }
  }

    if (60 * (24 * $mumOfDaysOD + $mumOfHoursOD) + $mumOfMinuteOD >
        (60 * (24 * $graceDay + $graceHour) + $graceiMinute)) {
        $mumOfHoursOD += 1 if ($mumOfMinuteOD > 0);
        if ($loanType ne 'hourly') {
            $mumOfDaysOD += 1 if ($mumOfHoursOD > 0);
            $mumOfHoursOD = 0;
        }
    }
    else {
        $mumOfDaysOD  = 0;
        $mumOfHoursOD = 0;
    }
    $loan->{'notes'}=$notes ;
  #/check grace period
  $loan->{'lid'}          = $loan->{'idloan'};
  $loan->{'loanType'}     = $loanType;
  $loan->{'daysOverdue'}  = $mumOfDaysOD;
  $loan->{'hoursOverdue'} = $mumOfHoursOD;
  $loan->{'weeksOverdue'} = $mumOfWeeksOD>0?$mumOfWeeksOD:0;

  return $loan;
}
sub eq_circ_consumableMgmt {
  my ($dbh,$p)= @_;
  my $sql = <<_SQL_;
insert into eq_consumableMgmt
set     loanId      = ?,
        barcode     = ?,
        rid         = ?,
        qty         = ?,
        unit        = ?,
        type        = ?,
        note        = ?
_SQL_
  $p->{'loanId'} = $p->{'loanId'} || 0;
  my $unit=$p->{'unit'} ;
  if ($p->{'type'} eq "received" || $p->{'type'} eq "distributed"){
    $unit = ($p->{'unitPerItem'} && $p->{'unitPerItem'}>0)?$p->{'qty'} * $p->{'unitPerItem'}:$p->{'qty'};
  }
  my $sth = $dbh->prepare($sql);
    my $rv = $sth->execute($p->{'loanId'},$p->{'barcode'},$p->{'rid'},$p->{'qty'},$unit,$p->{'type'},$p->{'note'}||'');
    $sth->finish;  
    if($rv ne'0E0'){ }
    return ($rv eq '0E0') ? 0 : 1; 
}


1;