=head1 NAME

Opals::Circulation

=cut

package Opals::Circulation;

require Exporter;
@ISA = qw(Exporter);

# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
  circ_infoRecord
  circ_getUserCircStatus
  circ_getItemStatus
  circ_getItemInfo
  circ_getRecInfo
  circ_getRecID
  circ_getReserveID
  circ_getActiveLoanId
  circ_processLoan
  circ_processReturn
  circ_processReturn_ILL
  circ_processRenew
  circ_placeHold
  circ_fillHold
  circ_setHoldIdLoan
  circ_cancelHold
  circ_cancelHold_all
  circ_placeReserve
  circ_fillReserve
  circ_fillReserveById
  circ_resetReserve
  circ_cancelReserve
  circ_updateItemStatus
  circ_setAvailable
  circ_lostDeclarating
  circ_record_ODL
  circ_record_ODL_ext
  circ_claimReturned
  circ_claimNeverLoan

  circ_userListLoan
  circ_userClaimList
  circ_userListReserve
  circ_GetReserveList
  circ_GetLostNumber
  circ_GetDamagedNumber
  circ_GetOnloanNumber
  circ_GetOverdueNumber
  circ_GetReserveNumber
  circ_numActiveItem
  circ_numItemOnHold
  circ_numItemOnHold4Uid
  circ_numItemOnLoan
  circ_numItemOnReserve
  circ_numItemsAvailable
  circ_isOnReserve
  circ_isOnHold

  Circ_validateFollettBarcode
  Circ_validateSpectrumBarcode
  Circ_validateLeadingZeroBc
  Circ_validateSagebrushBarcode

  circ_getDuedayList
  circ_userListLoanStat

  circ_getReserveInfo
  circ_getLoanListByRid
  circ_getHoldListByRid
  circ_getReserveListByRid
  circ_getNextReserveByRid
  circ_cancelHoldReserve

  circ_cancelReserveByRid
  circ_cancelHoldByRid

  circ_itemList_byTempOrILL

  circ_userCircStatByRid

  circ_adjustHoldReserve
  circ_updateReserve

  circ_saveBooking
  circ_cancelBooking
  circ_getBookingUserList
  circ_isReserve4booking

  circ_getRecCircStatus
  circ_getRecCircStatus_book
  circ_getRecCircStatus_ebook
  circ_infoRecord_ebook
  circ_getItemOnLoan

  eCirc_processLoan
  eCirc_getActiveLoanId
  eCirc_processReturn
  eCirc_getUserLoanList
  eCirc_isOnLoanTo
  eCirc_isReserveTo
  eCirc_getTotalOnLoan
  eCirc_processReserve
  eCirc_getNextReserveByBid
  eCirc_updateReserveById
  eCirc_updateReserve
  eCirc_getUserReserveList
  eCirc_getTotalOnReserve
  eCirc_getTotalAvailable

  circ_placeReqRsv
  circ_updateReqRsvStatus
  circ_setHoldExpiry
  circ_suspendReserve

  circ_anonymizeLoan
);
use JSON;

use Opals::Ebook qw(
  eb_getMarcXmlRid
  eb_getBidByMarcXmlRid
  eb_getLicense
);

# Version number
$VERSION = 0.01;

use Time::localtime;

#use utf8;
use strict;
use Date::Calc::Object qw(
  :all
);

#use Opals::Context;
#use Opals::User qw(
#    user_getInformation
#);
use Opals::Date qw(
  date_now
  date_today
  date_validateWorkday
  date_addDeltaWorkday
  date_text
  date_getDeadLineDate
  dateTime_parse
  date_deltaWorkDayHour
);
use Opals::Constant;

use Opals::User qw(
  user_status
  user_getInformationById
);

use Opals::BookingSystem qw(
  bs_getBookingById
  bs_saveBooking
  bs_setIdReserve2Booking
  bs_cancelBooking
);
use Opals::MarcXml qw(
  mxml_updateItemStatus
);
################################################################################

sub circ_getItemInfo {
  my ($dbh, $barcode, $uid) = @_;
  my ($itemInfo, $userTypeId);
  my $sql;

  if ($barcode) {
    $sql = <<_SQL_;
select  i.*,
        m.title, m.author, m.pubPlace, m.pubName, m.pubDate
from    opl_item as i, opl_marcRecord as m
where   i.barcode = ? &&
        i.rid = m.rid
_SQL_
    $itemInfo = $dbh->selectrow_hashref($sql, undef, $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  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(gracePeriodUnit <> 'minute',if(gracePeriod   div 24 >=1,gracePeriod   div 24,gracePeriod),gracePeriod)   as gracePeriod,   
        if(gracePeriodUnit <> 'minute',if(gracePeriod   div 24 >=1,'day','hour'),'minute') 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};
  }

  #Thu, Dec 05, 2013 @ 09:54:31 EST
  #check and get loan period set for reserve shelf

  my $rsLoanPeriod = getRSLoanPeriod($dbh, $uid, $itemInfo->{"rid"});
  if ($rsLoanPeriod != -1) {
    $itemInfo->{'loanPeriod'} = $rsLoanPeriod;
  }

  return $itemInfo;
}

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

sub circ_getDuedayList {
  my ($dbh, $uid) = @_;
  my @ddList = ();
  my $sql;

  if ($uid) {
    $sql = <<_SQL_;
select  categorycode
from    opl_user
where   uid = ?
_SQL_
    my ($userTypeId) = $dbh->selectrow_array($sql, undef, $uid);
    if ($userTypeId) {
      my $sth = $dbh->prepare(<<_SQL_);
select  itemTypeId,
        if(loanPeriod div 24 >=1,loanPeriod div 24,loanPeriod) as loanPeriod,
        if(loanPeriod div 24 >=1,'day','hour') as loanUnit
        from    opl_itemTypeParam
where   userTypeId = ?
_SQL_

      $sth->execute($userTypeId);
      while (my $rec = $sth->fetchrow_hashref) {
        my $ddate = date_today();
        $ddate = date_addDeltaWorkday($ddate, $rec->{'loanPeriod'});
        $ddate = date_validateWorkday($ddate, $rec->{'loanPeriod'});

        my ($year, $month, $day) = $ddate->date();
        my $retDate = sprintf("%04d-%02d-%02d ", $year, $month, $day);
        $rec->{'duedate'} = $retDate;
        push @ddList, $rec;
      }
      $sth->finish;
    }
  }

  return \@ddList;

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

#    my $sth = $dbh->prepare("select m.rid, title, author, pubName, pubDate ,
#                               max(t.holdPeriod) as holdPeriod,min(reservePeriod) as reservePeriod
#                               from opl_item as i inner join opl_marcRecord as m ON i.rid=m.rid
#                               inner join opl_typeCirc as t ON t.id = i.typeCircId where i.rid=$rid group by m.rid");
sub circ_getRecInfo {
  my ($dbh, $rid, $uid) = @_;

  my $sql = <<_SQL_;
select  *
from    opl_marcRecord
where   rid = ?
_SQL_
  my $recInfo = $dbh->selectrow_hashref($sql, undef, $rid);

  if ($uid && $uid >= 0) {
    my ($userInfo, $guardian) =
      Opals::User::user_getInformationById($dbh, $uid);

    # Currently only day is supported.

=item
        my $sth = $dbh->prepare(<<_SQL_);
select  max(holdPeriod)    div 24 as holdPeriod,
        max(reservePeriod) div 24 as reservePeriod
from    opl_item as i, opl_itemTypeParam as t
where   i.rid = ? &&
        t.userTypeId = ? &&
        i.typeId = t.itemTypeId
=cut

    my $sth = $dbh->prepare(<<_SQL_);
select  if(max(holdPeriod)  div 24 >=1,max(holdPeriod) div 24,max(holdPeriod)) as holdPeriod,    
        if(max(holdPeriod)  div 24 >=1,'day','hour')                           as holdUnit,
        if(max(reservePeriod) div 24 >=1,max(reservePeriod) div 24,max(reservePeriod)) as reservePeriod, 
        if(max(reservePeriod) div 24 >=1,'day','hour')                                 as reserveUnit   
from    opl_item as i, opl_itemTypeParam as t
where   i.rid = ? &&
        t.userTypeId = ? &&
        i.typeId = t.itemTypeId                     
_SQL_
    $sth->execute($rid, $userInfo->{'categorycode'});
    my $itemInfo = $sth->fetchrow_hashref;
    $sth->finish;

    $recInfo->{'holdPeriod'}    = $itemInfo->{'holdPeriod'};
    $recInfo->{'holdPeriod'}    = 1 if ($recInfo->{'holdPeriod'} == 0);
    $recInfo->{'reservePeriod'} = $itemInfo->{'reservePeriod'};

  }

  return $recInfo;
}
################################################################################

sub _getLoan {
  my ($dbh, $barcode, $dateRenew) = @_;

  my $sql = <<_SQL_;
select  *
from    opl_loan
where   barcode = ?
     && dateReturn is null
_SQL_

  my @val = ($barcode);
  my $loan = $dbh->selectrow_hashref($sql, undef, @val) || return;

  my $idloan   = $loan->{'idloan'};
  my $dateRet  = dateTime_parse($dateRenew);
  my $dateDue  = dateTime_parse($loan->{'dateDue'});
  my $timeDd   = "23:59:59";
  my $loanType = 'daily';
  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';
  }

  my ($mumOfDaysOD, $mumOfHoursOD, $mumOfMinuteOD) =
    date_deltaWorkDayHour($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;
  }

  #/check grace period
  $loan->{'lid'}          = $loan->{'idloan'};
  $loan->{'loanType'}     = $loanType;
  $loan->{'daysOverdue'}  = $mumOfDaysOD;
  $loan->{'hoursOverdue'} = $mumOfHoursOD;

  return $loan;
}
################################################################################

#    my $sth = $dbh->prepare("select m.rid, title, author, pubName, pubDate ,t.*
#                               from opl_item as i inner join opl_marcRecord as m ON i.rid=m.rid
#                               inner join opl_typeCirc as t ON t.id = i.typeCircId where i.barcode=? group by m.rid");
#sub circ_getItemInfo{
#    my ($dbh, $barcode, $uid)= @_;
#
#    my ($userInfo, $guardian) = user_getInformationById($dbh, $uid);
#
#    my $sth = $dbh->prepare(<<_SQL_);
#select      m.*
#from        opl_item as i
#inner join  opl_marcRecord as m
#         on i.rid=m.rid
#where       i.barcode = ?
#group by    m.rid
#_SQL_
#
#    $sth->execute($userInfo->{'categorycode'}, $barcode);
#    my $itemInfo = $sth->fetchrow_hashref;
#    $sth->finish;
#    return  $itemInfo;
#}
#-------------------------------------------------------------------------------------------
sub circ_getRecID {
  my ($dbh, $barcode) = @_;

  my $sth = $dbh->prepare("select rid from opl_item where barcode=?");
  $sth->execute($barcode);
  my $rec = $sth->fetchrow_hashref;
  $sth->finish;
  return $rec->{'rid'};
}

#-------------------------------------------------------------------------------------------
sub circ_getReserveID {
  my ($dbh, $rid, $uid) = @_;
  my $sth = $dbh->prepare(
"select idReserve from opl_reserve where rid=? && uid=? && numCopyReserve>0 && dateCancel is null && dateExpiry>now()"
  );
  $sth->execute($rid, $uid);
  my ($idReserve) = $sth->fetchrow_array;
  if (!$idReserve) {
    $sth = $dbh->prepare(
"select idReserve from opl_reserve r inner join opl_hold h using(idReserve) where h.dateCancel is null && h.dateLoan is null && h.dateExpiry>now() && rid=? && uid=?"
    );
    $sth->execute($rid, $uid);

    ($idReserve) = $sth->fetchrow_array;

  }

  $sth->finish;
  return $idReserve;
}

#-------------------------------------------------------------------------------------------
# Wed, Jun 10, 2009 @ 11:23:01 EDT
sub circ_getActiveLoanId {
  my ($dbh, $barcode, $uid) = @_;
  my $sth = $dbh->prepare(
    "select idloan from opl_loan where barcode=? && uid=? && dateReturn is null"
  );
  $sth->execute($barcode, $uid);
  my $rec = $sth->fetchrow_hashref;
  $sth->finish;
  return $rec->{'idloan'};
}

#-----------------------------------------------------------------------------------------------
sub circ_getUserCircStatus {
  my ($dbh, $uid, $barcode) = @_;
  my $retval = {
    status          => 1,
    overdue         => 0,
    maxLoan         => 0,
    maxRenew        => 0,
    maxReserve      => 0,
    maxItemTypeLoan => 0
  };
  my $sth = $dbh->prepare(
    "select u.*,c.maxloans,c.maxreserv,c.maxOverdue
        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 || 1 != $rec->{'status'}) {
    $retval->{'status'} = USER_INACTIVE;
  }
  my $circ_rec;

  #have overdue
  $sth = $dbh->prepare(
"select count(*) as nOd from opl_loan where uid=$uid && dateReturn is null && datedue < now() "
  );
  $sth->execute();
  $circ_rec = $sth->fetchrow_hashref;
  $sth->finish;
  if ($circ_rec && $circ_rec->{'nOd'} >$rec->{'maxOverdue'}) {
    $retval->{'overdue'} = 1;
  }

  #max loan reach
  $sth = $dbh->prepare(
    "select sum(numOfLoan) as numOfLoan from(
                                 select count(*) as numOfLoan from opl_loan where uid=$uid && dateReturn is null &&(type ='Library' || type is null)
                                 union 
                                 select count(*) as numOfLoan from opl_ebLoan where dateReturn <now() && uid=$uid ) t1"
  );
  $sth->execute();
  $circ_rec = $sth->fetchrow_hashref;
  $sth->finish;
  if ($circ_rec && $rec && $rec->{'maxloans'} <= $circ_rec->{'numOfLoan'}) {
    $retval->{'maxLoan'} = 1;
  }

  #max renew reach
  if (defined $barcode && $barcode ne "") {
    $sth = $dbh->prepare(
"select maxRenewal,renewalCount  from opl_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;
    }
    $retval->{'maxItemTypeLoan'} = _checkMaxItemTypeLoan($dbh, $uid, $barcode);
  }

#max reserve reach -- count on number of titles allowed (should be count on number of copies??)
  $sth = $dbh->prepare(
    "select sum(numOfReserve) as numOfReserve from(
                               select count(*) as numOfReserve from opl_reserve where numCopyReserve >=1 
                                           && dateExpiry >now() && dateCancel is null  && uid=$uid
                               union
                               select count(*) as numOfReserve from opl_ebReserve where  dateExpiry >now() &&  dateCancel is null && idloan = 0 && uid=$uid) t2      
                           "
  );
  $sth->execute();
  $circ_rec = $sth->fetchrow_hashref;
  $sth->finish;

#if($circ_rec && $rec  && $rec->{'maxreserv'}>0 && $rec->{'maxreserv'} <= $circ_rec->{'numOfReserve'}){
  if ($circ_rec && $rec && $rec->{'maxreserv'} <= $circ_rec->{'numOfReserve'}) {
    $retval->{'maxReserve'} = 1;
  }

  #max hold reach ???1

  return $retval;
}
################################################################################
sub _checkMaxItemTypeLoan {
  my ($dbh, $uid, $bc) = @_;
  my $ret     = 0;
  my $sth_max = $dbh->prepare(
"select maxItemLoan, i.typeId from opl_item i inner join opl_itemTypeParam t on i.typeId = itemTypeId inner 
                          join opl_user u on t.userTypeId=u.categoryCode
                           where barcode=? && uid=?"
  );
  my $sth_count = $dbh->prepare(
"select count(*) from opl_loan l inner join opl_item i using(barcode) where l.dateReturn is null && i.typeId=? && uid=?"
  );
  $sth_max->execute($bc, $uid);
  my ($maxItemLoan, $typeId) = $sth_max->fetchrow_array;
  if (defined $maxItemLoan && defined $typeId) {
    $sth_count->execute($typeId, $uid);
    my ($count) = $sth_count->fetchrow_array;

    #$ret= $maxItemLoan>=$count?0:1;
    $ret = $maxItemLoan <= $count ? 1 : 0;
  }
  return $ret;

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

#    my $sth=$dbh->prepare("select i.rid,i.barcode,i.available,t.*,s.status,s.ondate
#                from (opl_item as i inner join opl_typeCirc as t on i.typeCircId= t.id)
#                left outer join opl_itemstatus as s on i.barcode=s.barcode
#                where i.barcode='$barcode' order by s.ondate desc");
sub circ_getItemStatus {
  my ($dbh, $barcode) = @_;

  my $sth = $dbh->prepare(
"select bid from opl_item i inner join opl_ridBid r using(rid) where i.barcode=?"
  );
  $sth->execute($barcode);
  if (my ($bid) = $sth->fetchrow_array) {
    return circ_getItemStatus_ebook($dbh, $bid);
  }
  else {
    return circ_getItemStatus_book($dbh, $barcode);
  }
}

#===============================================================================
sub circ_getItemStatus_ebook {
  my ($dbh, $bid) = @_;
  my $retVal = { status => IT_STAT_FULL_AVAIL, loanUidList => {} };

  my $numLicAvail = eCirc_getTotalAvailable($dbh, $bid);
  $retVal->{'status'} = IT_STAT_ONLOAN if ($numLicAvail == 0);

  my $sth = $dbh->prepare(
    "select uid from opl_ebLoan where dateReturn >now() && bid=?");
  $sth->execute($bid);
  while (my ($uid) = $sth->fetchrow_array) {
    $retVal->{'loanUidList'}->{$uid} = 1;
  }
  return $retVal;
}

#===============================================================================
#
my $ITEM_DAMAGED          = ITEM_DAMAGED;
my $ITEM_LOST             = ITEM_LOST;
my $ITEM_INACTIVE         = ITEM_INACTIVE;
my $ITEM_ON_ORDER         = ITEM_ON_ORDER;
my $ITEM_IN_PROCESSING    = ITEM_IN_PROCESSING;
my $ITEM_IN_REPAIR        = ITEM_IN_REPAIR;
my $ITEM_CLAIM_RETURN     = ITEM_CLAIM_RETURN;
my $ITEM_CLAIM_NEVER_LOAN = ITEM_CLAIM_NEVER_LOAN;
my $ITEM_WEED             = ITEM_WEED;

my $itemStatusMap = {
  $ITEM_DAMAGED          => IT_STAT_DAMAGED,
  $ITEM_LOST             => IT_STAT_LOST,
  $ITEM_INACTIVE         => IT_STAT_MISSING,
  $ITEM_ON_ORDER         => IT_STAT_ON_ORDER,
  $ITEM_IN_PROCESSING    => IT_STAT_IN_PROCESSING,
  $ITEM_IN_REPAIR        => IT_STAT_IN_REPAIR,
  $ITEM_CLAIM_RETURN     => IT_STAT_CLAIM_RETURN,
  $ITEM_CLAIM_NEVER_LOAN => IT_STAT_CLAIM_NEVER_LOAN,
  $ITEM_WEED             => IT_STAT_WEED
};

#===============================================================================
sub circ_getItemStatus_book {
  my ($dbh, $barcode) = @_;
  my $retVal = {
    status    => IT_STAT_FULL_AVAIL,
    inLibrary => 1,
  };

  # check if item is on loan?
  my $sql = "";
  $sql = <<_SQL_;
select  *
from    opl_loan
where   barcode = ? &&
        dateReturn is null
_SQL_

  my $item = $dbh->selectrow_hashref($sql, undef, $barcode);
  if ($item) {
    $retVal->{'inLibrary'}    = 0;
    $retVal->{'l_idloan'}     = $item->{'idloan'};
    $retVal->{'l_uid'}        = $item->{'uid'};
    $retVal->{'l_dateLoan'}   = $item->{'dateLoan'};
    $retVal->{'l_duedate'}    = $item->{'dateDue'};
# 2016-10-31 10:18:13 EDT, Mon Oct. Thien Ho
    # l_maxRenewal is favorable since it's loan specific. It may be different 
    # from max renewal limit based on item+user types.
    $retVal->{'l_maxRenewal'}   = $item->{'maxRenewal'};
#/2016-10-31 10:18:13 EDT, Mon Oct. Thien Ho
    $retVal->{'maxRenewal'}   = $item->{'maxRenewal'};
    $retVal->{'renewalCount'} = $item->{'renewalCount'};
    $retVal->{'status'}       = IT_STAT_ONLOAN;
  }

  $sql = $dbh->prepare(<<_SQL_);
select  i.rid, i.barcode, i.available,
        s.status, s.ondate 
from    opl_item as i 
left outer join opl_itemstatus as s 
on      i.barcode = s.barcode &&  s.status <> ? && (s.note is null || s.note<>'merged') 
where   i.barcode = ?
order by s.ondate desc limit 1
_SQL_
  $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'} && $retVal->{'status'} != IT_STAT_ONLOAN) {
    if ( defined $item->{'status'}
      && defined $itemStatusMap->{ $item->{'status'} })
    {
      $retVal->{'status'} = $itemStatusMap->{ $item->{'status'} };
    }
    else {
      $retVal->{'status'} = IT_STAT_UNAVAIL;
    }
    return $retVal;
  }
  elsif ($item->{'restricted'}) {
    $retVal->{'status'} = IT_STAT_RESTRICTED;

    return $retVal;
  }

  # Get hold list -- item may be on hold even if it's in library.
  $retVal->{'holdList'} = circ_getHoldListByRid($dbh, $rid);

  # check if item is on hold?
  if (circ_isOnHold($dbh, $rid) && $retVal->{'status'} != IT_STAT_ONLOAN) {
    $retVal->{'status'} = IT_STAT_ONHOLD;
  }

  $retVal->{'reserveList'} = [];
  # check if item is on reserve?
  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;
    }
    $retVal->{'reserveList'} = circ_getReserveListByRid($dbh, $rid);
  }

  # check if item is temporary or ILL ?
  # Mon, Mar 02, 2009 @ 10:20:36 EST
  #
  #
  my $tempIll = 'temporary,ILL';
  my $temILLList = circ_itemList_byTempOrILL($dbh, $barcode, $tempIll);
  $retVal->{'isTempILL'} = scalar(@$temILLList) > 0 ? 1 : 0;
  return $retVal;
}

#-----------------------------------------------------------------------------------------------
sub circ_getLoanListByRid {

  # Thu, Feb 26, 2009 @ 12:12:27 EST
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select  i.barcode,l.uid, cast(l.dateDue as DATE) dateDue
from    opl_item i inner join opl_loan l on i.barcode=l.barcode
where   i.rid= ? && l.dateReturn is null
_SQL_
  $sth->execute($rid);
  my @loanList;
  while (my $h = $sth->fetchrow_hashref) {
    push @loanList, $h;
  }
  $sth->finish;

  return \@loanList;
}

#-----------------------------------------------------------------------------------------------
sub circ_getHoldListByRid {

  #Mon, Feb 23, 2009 @ 16:45:31 EST
  my ($dbh, $rid) = @_;

  # Get hold list -- item may be on hold even it may be in library.
  my $sth = $dbh->prepare(<<_SQL_);
select  u.uid,u.firstname,u.lastname,u.buildingcode,u.homeroom,u.teacher,u.grade,
        h.idReserve, h.datehold, h.dateExpiry,
        r.rid, count(r.rid) as numOfHold
from    (opl_reserve as r
         inner join opl_hold as h 
         on         r.idReserve = 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()
group by u.uid,r.rid        
order by h.datehold
_SQL_
  $sth->execute($rid);

  my @holdList;
  while (my $h = $sth->fetchrow_hashref) {
    push @holdList, $h;
  }
  $sth->finish;

  return \@holdList;
}

#-----------------------------------------------------------------------------------------------
# circ_getBookingUserList
#   return list of users which reserved for form a booking but not yet loan.
#
sub circ_getBookingUserList {
  my ($dbh, $idReserve) = @_;
  my @userList = ();
  my $sth      = $dbh->prepare(<<_SQL_);

    select u.uid, u.firstname, u.lastname,u.buildingcode,u.homeroom,u.teacher,u.grade
    from opl_user u inner join 
         (select f.uid from opl_bookingFor f inner join opl_bookingItem i using(bookingId) 
             where i.idReserve =? ) t1 on t1.uid=u.uid
         left outer join 
         (select l.uid from opl_hold h inner join opl_loan l using(idloan) 
              where l.dateLoan between h.dateHold and h.dateExpiry && h.idReserve=?) t2  
            on t1.uid=t2.uid
     where t2.uid is null     
_SQL_

  $sth->execute($idReserve, $idReserve);

  while (my $user = $sth->fetchrow_hashref) {
    push @userList, $user;
  }
  return \@userList;
}

sub circ_isReserve4booking {
  my ($dbh, $idReserve) = @_;
  my $retval = 0;
  my $sth =
    $dbh->prepare("select bookingId from opl_bookingItem where idReserve=?");
  $sth->execute($idReserve);
  if (my $bookingId = $sth->fetchrow_hashref) {
    $retval = 1;
  }
  $sth->finish;
  return $retval;

}

#-----------------------------------------------------------------------------------------------
sub circ_getReserveListByRid {

  #Mon, Feb 23, 2009 @ 16:45:31 EST
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select  u.uid, u.firstname, u.lastname,u.buildingcode,u.homeroom,u.teacher,u.grade,
        r.rid, r.idReserve, r.datereserve, r.dateExpiry ,r.priority
from    opl_reserve as r inner join opl_user as u on r.uid=u.uid 
where   rid = ? && 
        dateCancel is null && 
        numCopyReserve >=1 && 
        dateExpiry > now() &&
        (suspendFrom is null ||(now() not between suspendFrom and suspendUntil))
order by r.priority,idReserve
_SQL_
  $sth->execute($rid);
  my @reserveList = ();
  while (my $rec = $sth->fetchrow_hashref) {
    push @reserveList, $rec;
  }
  $sth->finish;

  return \@reserveList;
}

#-----------------------------------------------------------------------------------------------
sub circ_getNextReserveByRid {

  #Mon, Feb 23, 2009 @ 16:45:31 EST
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select  u.uid, u.firstname, u.lastname,
        r.rid, r.idReserve, r.datereserve, r.dateExpiry,r.priority 
from    opl_reserve as r inner join opl_user as u on r.uid=u.uid 
where   rid = ? && 
        dateCancel is null && 
        numCopyReserve >=1 && 
        dateExpiry > now() &&
        (suspendFrom is null ||(now() not between suspendFrom and suspendUntil))
order by r.priority, idReserve
limit 1
_SQL_
  $sth->execute($rid);
  my $reserve;
  if (my $r = $sth->fetchrow_hashref) {
    $reserve = $r;
  }
  $sth->finish;

  return $reserve;
}

#-----------------------------------------------------------------------------------------------
# Fri, May 08, 2009 @ 11:58:04 EDT
sub circ_cancelReserveByRid {
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(
    "update opl_reserve set dateCancel=now()
                             where rid=?  && dateCancel is null && dateExpiry >now() 
                             && numCopyReserve>0"
  );
  my $result = $sth->execute($rid);
  $sth->finish;
  return $result eq '0E0' ? 0 : 1;

}

#-----------------------------------------------------------------------------------------------
# Fri, May 08, 2009 @ 11:58:04 EDT
sub circ_cancelHoldByRid {
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(
    "update opl_hold h inner join opl_reserve r
                             set h.dateCancel=now()
                             where r.rid=?  && h.dateCancel is null && h.dateExpiry >now() 
                             && h.dateLoan is null"
  );
  my $result = $sth->execute($rid);
  $sth->finish;
  return $result eq '0E0' ? 0 : 1;

}

#-----------------------------------------------------------------------------------------------
sub circ_numActiveItem {
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(
    "select  count(*) as count from opl_item where available=1 && rid=?");
  $sth->execute($rid);
  my $rec = $sth->fetchrow_hashref;
  if ($rec) {
    return $rec->{'count'};
  }
  return 0;
}

#-----------------------------------------------------------------------------------------------
sub circ_numItemOnHold {
  my ($dbh, $rid, $onDate) = @_;

  if (!defined $onDate || $onDate eq '') {
    $onDate = date_now();
  }

  my $sth = $dbh->prepare(
"select  count(*) as count from opl_reserve as r inner join opl_hold as h on r.idreserve=h.idreserve 
                             where r.rid=$rid && h.dateLoan is null && h.dateCancel is null && h.dateExpiry > '$onDate' "
  );
  $sth->execute();
  my $rec = $sth->fetchrow_hashref;
  if ($rec) {
    return $rec->{'count'};
  }
  return 0;
}

#-----------------------------------------------------------------------------------------------
sub circ_numItemOnHold4Uid {
  my ($dbh, $rid, $uid, $onDate) = @_;
  my $retval = 0;
  if (!defined $onDate || $onDate eq '') {
    $onDate = date_now();
  }
  my $sth = $dbh->prepare(
"select  count(*) as count from opl_reserve as r inner join opl_hold as h on r.idreserve=h.idreserve 
                             where r.rid=$rid && r.uid=$uid &&h.dateLoan is null && h.dateCancel is null && h.dateExpiry > '$onDate' "
  );
  $sth->execute();
  my $rec = $sth->fetchrow_hashref;
  if ($rec) {
    $retval = $rec->{'count'};
  }
  return $retval;
}

#-----------------------------------------------------------------------------------------------
sub circ_numItemOnLoan {
  my ($dbh, $rid, $onDate) = @_;
  my $sql =
"select  count(*) as count from opl_loan as l inner join opl_item  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'};
  }
  return 0;
}

#-----------------------------------------------------------------------------------------------
sub circ_numItemOnReserve {
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(
"select  count(*) as count from opl_reserve r inner join opl_user u using(uid)
                           where rid=$rid && numCopyReserve >0 && dateCancel is null && dateExpiry > now()
                           && (suspendFrom is null || (now() not between suspendFrom and suspendUntil))"
  );
  $sth->execute();
  my $rec = $sth->fetchrow_hashref;
  if ($rec) {
    return $rec->{'count'};
  }
  return 0;
}

#-----------------------------------------------------------------------------------------------
sub circ_numItemsAvailable {
  my ($dbh, $rid, $onDate) = @_;
  my $numOfHold = circ_numItemOnHold($dbh, $rid, $onDate);
  my $numOfActiveItems = circ_numActiveItem($dbh, $rid);
  my $numOfLoans = circ_numItemOnLoan($dbh, $rid, $onDate);
  my $numHoldingAvailable = $numOfActiveItems - $numOfLoans - $numOfHold;
  return $numHoldingAvailable;
}

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

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

  # Mon, Mar 02, 2009 @ 09:39:41 EST
  my ($dbh, $barcode, $tempIll) = @_;
  my $sth;
  my $sql = <<_SQL_;
select    m.rid, m.incomplete, m.title,m.tempIll 
from      opl_marcRecord m inner join opl_item i on m.rid=i.rid 
where     m.incomplete ='true' 
_SQL_

  if ($barcode && $barcode ne '') {
    $sql .= " && i.barcode = '$barcode'";
  }
  if ($tempIll && $tempIll ne '') {
    my @arrType = split /,/, $tempIll;
    my $val = '';
    for (my $i = 0 ; $i < scalar(@arrType) ; $i++) {
      $val .= ",'" . @arrType[$i] . "'";

    }
    $val =~ s/^,//g;
    $sql .= " && m.tempIll in ($val)";
  }
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  my @inconpleteItemList = ();
  while (my $h = $sth->fetchrow_hashref) {
    push @inconpleteItemList, $h;
  }
  $sth->finish;
  return \@inconpleteItemList;
}
###############################################################################

#my $sth = $dbh->prepare("select fine,maxRenewal from opl_typeCirc as t, opl_item as i
#                            where i.typeCircId = t.id && i.barcode ='$barcode'");
sub circ_processLoan {
  my ($dbh, $uid, $barcode, $dateLoan, $dateDue, $type) = @_;

  # Get the current finerate
  my $itemInfo = circ_getItemInfo($dbh, $barcode, $uid);
  my $maxRenewal = $itemInfo->{'maxRenewal'} || 0;
  my $finerate = 0;
  $finerate = $itemInfo->{'fine'}
    if (defined $itemInfo->{'fine'} && $itemInfo->{'fine'} > 0);

  my $dayTimeNow = date_now();
  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 opl_loan
set     uid         = ?,
        barcode     = ?,
        maxRenewal  = ?,
        dateLoan    = ?,
        dateDue     = ?,
        finerate    = ?
_SQL_

  if ($type && $type ne '') {
    $sql .= ", type= '$type'";
  }
  my $sth = $dbh->prepare($sql);

  # Insert a new record for this transaction

=item
    my $sth = $dbh->prepare(<<_SQL);
insert into opl_loan
set     uid         = ?,
        barcode     = ?,
        maxRenewal  = ?,
        dateLoan    = timestamp(?, curtime()),
        dateDue     = ?,
        finerate    = ?
_SQL_

=cut

  my $rv = $sth->execute($uid, $barcode, $maxRenewal, $dateLoan, $dateDue, $finerate);
  $sth->finish;
#  Wed, Sep 28, 2016 @ 14:58:11 EDT

  if($rv ne'0E0'){
      my $idloan=$dbh->{'mysql_insertid'};
      updateLoanStats($dbh,$uid,$barcode,$idloan,$dateLoan);
  }

  return ($rv eq '0E0') ? LOAN_UNFINISH : LOAN_FINISH;

}

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

#-----------------------------------------------------------------------------------------------
sub circ_fillReserveById {
  my ($dbh, $idReserve) = @_;
  my $sth = $dbh->prepare(<<_STH_);
update  opl_reserve
set     numCopyReserve = numCopyReserve - 1
where    idReserve= ?
_STH_
  my $result = $sth->execute($idReserve);
  $sth->finish;
  return $result eq '0E0' ? 0 : 1;
}

#-----------------------------------------------------------------------------------------------
sub circ_placeReserve {
  my ($dbh, $uid, $rid, $numCopyReserve, $dateReserve, $dateExpiry, $respUid,
    $reqId)
    = @_;
  my $sth = $dbh->prepare(
    "insert into opl_reserve(rid, uid, numCopyReserve, dateReserve, dateExpiry)
                             values (?,?,?,?,?)"
  );
  my $result =
    $sth->execute($rid, $uid, $numCopyReserve, $dateReserve, $dateExpiry);
  my $reserveId = $dbh->{'mysql_insertid'};

  $sth->finish;

  #Fri, Dec 04, 2015 @ 12:06:57 EST
  #
  if (!defined $reqId) {
    my ($userInfo, $guardian) =
      Opals::User::user_getInformationById($dbh, $uid);
    my $email      = $userInfo->{'email'};
    my $phone      = $userInfo->{'phone'};
    my $noticeType = "email";
    if ($email eq '' && $phone ne '') {
      $noticeType = "phone";
    }
    $reqId =
      circ_placeReqRsv($dbh, $uid, $rid, $numCopyReserve, $dateExpiry, $email,
      $phone, $noticeType);
  }
  circ_updateReqRsvStatus($dbh, $reqId, 'reserve', $reserveId, $respUid);
  return $result eq '0E0' ? 0 : $reserveId;
}

#-----------------------------------------------------------------------------------------------
sub circ_placeReqRsv {
  my ($dbh, $uid, $rid, $numCopyReserve, $dateExpiry, $email, $phone,
    $noticeType)
    = @_;
  my $sth = $dbh->prepare(
    "insert into opl_requestReserve 
                        set rid=?,uid=?,dateRequest=now(), email=?,phone=?,noticeType=?, numCopyRequest=?,dateExpiry=?"
  );
  $sth->execute($rid, $uid, $email, $phone, $noticeType, $numCopyReserve,
    $dateExpiry);
  my $reqId = $dbh->{'mysql_insertid'};
  return $reqId;
}

#-----------------------------------------------------------------------------------------------
sub circ_updateReqRsvStatus {
  my ($dbh, $reqId, $status, $idreserve, $respUid, $dateExpiry, $rNotes) = @_;
  my ($todayStr) = $dbh->selectrow_array("select now()");

  if ($status ne 'reserve' || (defined $idreserve && $idreserve > 0)) {
    if ($status eq 'reserve') {
      $dbh->do(
        "update opl_requestReserve 
                      set status=?,idReserve=? where id=?", undef, $status,
        $idreserve, $reqId
      );
    }
    elsif ($status eq 'reactivate') {
      $dbh->do(
        "update opl_requestReserve 
                      set status='waiting',dateExpiry=?,idReserve=null where id=?",
        undef, $dateExpiry, $reqId
      );
    }
    else {
      $dbh->do(
        "update opl_requestReserve set status=? 
                      where  id=?", undef, $status, $reqId
      );
    }
    my $log = to_json({ notes => $rNotes || '', dateProcess => $todayStr });
    $dbh->do(
      "insert into opl_reqReserveHistory set reqId=?,uid=?,action=?,log=?",
      undef, $reqId, $respUid, $status, $log);

  }

}

# logstatus: reserve reject rejectEmail ready holdReadyEmail reactivate reactivateEmail cancel cancelReserveEmail
#-----------------------------------------------------------------------------------------------
sub circ_fillHold {
  my ($dbh, $idReserve, $barcode) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select  idHold,dateHold
from    opl_hold 
where   idReserve = ?
     && dateLoan is null
     && to_days(now()) - to_days(dateExpiry) <= 0
     && dateCancel is null
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  opl_hold
set     barcode     = ?,
        dateLoan    = now()
where   idHold   = ?
     && dateHold    = ?
     && dateLoan is null
     && dateCancel is null
     && to_days(now()) - to_days(dateExpiry) <= 0
_SQL_
    my $result = $sth->execute($barcode,$rec->{'idHold'}, $rec->{'dateHold'});
    $sth->finish;
    alignBookingStatusByIdReserve($dbh, $idReserve);
    return $result eq '0E0' ? 0 : 1;
  }
  return 0;

}

#-----------------------------------------------------------------------------------------------
sub circ_setHoldIdLoan {
  my ($dbh, $idReserve, $barcode, $idloan,$respUid) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
update  opl_hold
set     idloan     = ?
where   idReserve   = ? &&
        barcode= ? 
_SQL_

  $sth->execute($idloan, $idReserve, $barcode);
  $sth->finish;
  $respUid = (defined $respUid && $respUid > 0) ? $respUid : 1;
  my ($reqId) = $dbh->selectrow_array(
    "select id from opl_requestReserve  where idReserve=?",
    undef, $idReserve);
  if ($reqId) {
    circ_updateReqRsvStatus($dbh, $reqId, 'loan', $idReserve, $respUid);
  }

}

#-----------------------------------------------------------------------------------------------
sub circ_placeHold {
  my ($dbh, $idReserve, $dateHold, $dateExpiry, $respUid) = @_;
  my $timeHold = "";
  if ($dateHold =~ m/(\d{4}-\d{1,2}-\d{1,2}) (\d{1,2}:\d{1,2}:\d{1,2})/g) {
    $dateHold = $1;
    $timeHold = $2;
  }
  my $sth = $dbh->prepare(
    "select count(*) as numOfHold from opl_hold where idreserve=$idReserve");
  $sth->execute();
  my $rec = $sth->fetchrow_hashref;
  my $timeDif = 0;
  if ($rec) {
    $timeDif = $rec->{'numOfHold'};
  }

  #Tue, Apr 27, 2010 @ 11:37:17 EDT
  my $b = getBookingInfo($dbh, $idReserve);
  if (defined $b && $b->{'reserveTo'}) {
    $dateExpiry = $b->{'reserveTo'};
  }

  #//
  if ($timeHold eq '') {
    $sth = $dbh->prepare(
      "insert into opl_hold (idReserve, dateHold, dateExpiry)
                         values (?,TIMESTAMP(?,CURTIME()) + INTERVAL ? SECOND ,?)"
    );
  }
  else {
    $sth = $dbh->prepare(
      "insert into opl_hold (idReserve, dateHold, dateExpiry)
                         values (?,TIMESTAMP(?) + INTERVAL ? SECOND ,?)"
    );
  }
  my $result = $sth->execute($idReserve, $dateHold, $timeDif, $dateExpiry);
  $sth->finish;
  alignBookingStatusByIdReserve($dbh, $idReserve);
  $respUid = (defined $respUid && $respUid > 0) ? $respUid : 1;
  my ($reqId) = $dbh->selectrow_array(
    "select id from opl_requestReserve  where idReserve=?",
    undef, $idReserve);
  if ($reqId) {
    circ_updateReqRsvStatus($dbh, $reqId, 'hold', $idReserve, $respUid);
  }
  return $result eq '0E0' ? 0 : 1;
}

#-----------------------------------------------------------------------------------------------
sub circ_setHoldExpiry {
  my ($dbh, $idReserve, $dateExpiry, $respUid) = @_;

  my ($reqId, $prevHoldExpiry) = $dbh->selectrow_array(
    "select r.id, h.dateExpiry 
                                                       from opl_requestReserve r inner join opl_hold h using(idReserve) 
                                                       where idReserve=?",
    undef, $idReserve
  );
  if ($reqId) {
    $dbh->do("update opl_hold set dateExpiry=? where idReserve=?",
      undef, $dateExpiry, $idReserve);
    $respUid = (defined $respUid && $respUid > 0) ? $respUid : 1;
    my $log = to_json(
      { preExpiryDate => $prevHoldExpiry, newExpiryDate => $dateExpiry });
    $dbh->do(
      "insert into opl_reqReserveHistory set reqId=?,uid=?,action=?,log=?",
      undef, $reqId, $respUid, "Change hold expiry date", $log);
  }
}

#-----------------------------------------------------------------------------------------------
sub circ_suspendReserve {
  my ($dbh, $idReserve, $suspendFrom, $suspendUntil, $notes, $respUid) = @_;

  my ($reqId) =
    $dbh->selectrow_array("select id from opl_requestReserve where idReserve=?",
    undef, $idReserve);
  if ($reqId) {
    $dbh->do(
      "update opl_reserve set suspendFrom=? ,suspendUntil=? where idReserve=?",
      undef, $suspendFrom, $suspendUntil, $idReserve
    );
    $respUid = (defined $respUid && $respUid > 0) ? $respUid : 1;
    my $log = to_json(
      {
        notes        => $notes,
        suspendFrom  => $suspendFrom,
        suspendUntil => $suspendUntil
      }
    );
    $dbh->do(
      "insert into opl_reqReserveHistory set reqId=?,uid=?,action=?,log=?",
      undef, $reqId, $respUid, "suspend", $log);
  }
}

#Tue, Apr 27, 2010 @ 11:37:17 EDT

#-----------------------------------------------------------------------------------------------
sub getBookingInfo {
  my ($dbh, $idReserve) = @_;
  my $ret = {};
  my $sth = $dbh->prepare(
"select r.* from opl_bookingRequest r inner join opl_bookingItem i using(bookingId)
                            where i.idReserve=?"
  );
  $sth->execute($idReserve);
  if (my $rec = $sth->fetchrow_hashref) {
    $ret = $rec;
  }
  return $ret;

  #/Tue, Apr 27, 2010 @ 11:37:17 EDT
}

#-----------------------------------------------------------------------------------------------

=item
        This routine only cancels one hold at a time.
=cut

sub circ_cancelHold {
  my ($dbh, $idReserve) = @_;

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

#-----------------------------------------------------------------------------------------------
sub circ_cancelHold_all {
  my ($dbh, $idReserve) = @_;

  my $sth =
    $dbh->prepare("update opl_hold set dateCancel=now() where idReserve=?");
  my $result = $sth->execute($idReserve);
  $sth->finish;
  alignBookingStatusByIdReserve($dbh, $idReserve);
  return $result eq '0E0' ? 0 : 1;

}

#-----------------------------------------------------------------------------------------------
# Tue, Feb 24, 2009 @ 13:58:14 EST
sub circ_getReserveInfo {
  my ($dbh, $idReserve) = @_;
  my ($rid, $uid, $noh) = (0, 0, 0);
  my $reserveInfo;

  # 1 get record id of this reserve
  my $sth_rid =
    $dbh->prepare("select rid,uid from opl_reserve where idReserve = ?");
  $sth_rid->execute($idReserve);
  ($rid, $uid) = $sth_rid->fetchrow_array;
  $sth_rid->finish;
  $reserveInfo->{'uid'} = $uid;
  $reserveInfo->{'rid'} = $rid;

  # 2 get number of holds of this reserve
  $noh = getNumberOfHoldOfReserve($dbh, $idReserve);
  $reserveInfo->{'numOfHolds'} = $noh;

  return $reserveInfo;
}

#-----------------------------------------------------------------------------------------------
# Tue, Feb 24, 2009 @ 13:58:14 EST
sub circ_cancelHoldReserve {
  my ($dbh, $idReserve, $todayStr) = @_;

# 1 cancel reserve if any
# 2 get info of this reserve
# 3 if number of holds>0; then for(i=0; i<numOfHolds; i++) get next reserve  and fill
# 4 cancel hold
# 5 return list of users have hold for this reserve title.

  circ_cancelReserve($dbh, $idReserve);
  my @userHoldList = ();
  my $nextReserve;
  my $reserveInfo = circ_getReserveInfo($dbh, $idReserve);
  my $rid         = $reserveInfo->{'rid'};
  my $noh         = $reserveInfo->{'numOfHolds'};
  for (my $i = 0 ; $i < $noh ; $i++) {
    if ($nextReserve = circ_getNextReserveByRid($dbh, $rid)) {
      push @userHoldList, $nextReserve;
      my $idReserve     = $nextReserve->{'idReserve'};
      my $uidReserve    = $nextReserve->{'uid'};
      my $reserveExpiry = $nextReserve->{'dateExpiry'};

      my $recInfo = circ_getRecInfo($dbh, $rid, $uidReserve);
      my $dateExpiry =
        date_getDeadLineDate($recInfo->{'holdPeriod'}, $todayStr);
      if ($dateExpiry > $reserveExpiry) {
        $dateExpiry = $reserveExpiry;
      }

      circ_fillReserve($dbh, $uidReserve, $rid);
      my $putOnHold = circ_placeHold($dbh, $idReserve, $todayStr, $dateExpiry);
    }
  }
  circ_cancelHold_all($dbh, $idReserve);
  return \@userHoldList;
}

#-----------------------------------------------------------------------------------------------
sub getNumberOfHoldOfReserve {
  my ($dbh, $idReserve) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select count(*)
from opl_hold 
where idReserve=? && dateCancel is null && dateLoan is null
_SQL_
  $sth->execute($idReserve);
  my ($numOfHolds) = $sth->fetchrow_array;
  $sth->finish;
  return $numOfHolds;

}

#/ Tue, Feb 24, 2009 @ 13:58:31 EST
#-----------------------------------------------------------------------------------------------
sub circ_cancelReserve {
  my ($dbh, $idReserve) = @_;
  my $sth =
    $dbh->prepare("update opl_reserve set dateCancel=now() where idReserve=? ");
  my $result = $sth->execute($idReserve);
  $sth->finish;
  return $result eq '0E0' ? 0 : 1;

}

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

}

#-----------------------------------------------------------------------------------------------
sub getTimeOnShelvingCart {
  my ($dbh, $barcode) = @_;
  my ($t) = $dbh->selectrow_array(<<_SQL_);
     select timeOnShelvingCart 
     from opl_item i inner join opl_itemType t on t.id=i.typeId
     where barcode="$barcode"
_SQL_

  return (defined $t) ? $t : 0;
}

#-----------------------------------------------------------------------------------------------
#to be expanded when doing return ....

sub circ_processReturn {
  my ($dbh, $barcode, $dateReturn) = @_;
  my $dayTimeNow = date_now();
  if (
    $dateReturn =~ m/(\d{4}-\d{1,2}-\d{1,2})( (\d{1,2}:\d{1,2}:\d{1,2})){0,1}/g)
  {
    if ($3 =~ m/0{1,2}:0{1,2}:0{1,2}/g || $3 eq '') {
      my $time = substr $dayTimeNow, 11, 8;
      $dateReturn = "$1 $time";
    }
  }
  else {
    $dateReturn = $dayTimeNow;
  }
  my $timeOnShelvingCart = getTimeOnShelvingCart($dbh, $barcode);

  my $sth = $dbh->prepare(
    "update opl_loan set dateReturn=if(dateLoan<'$dateReturn','$dateReturn','$dayTimeNow') ,timeOnShelvingCart=?
     where barcode =? && dateReturn is null"
  );
  my $result = $sth->execute($timeOnShelvingCart, $barcode);
  $sth->finish;
  return $result eq '0E0' ? 0 : 1;

}

#-----------------------------------------------------------------------------------------------

sub circ_processReturn_ILL {
  my ($dbh, $ill_loanId, $barcode, $dateReturn) = @_;

  my $idLoan = _illLoanLookup($dbh, $ill_loanId, $barcode);
  return if (!defined $idLoan || !$idLoan);

  my $dayTimeNow = date_now();
  if (
    $dateReturn =~ m/(\d{4}-\d{1,2}-\d{1,2})( (\d{1,2}:\d{1,2}:\d{1,2})){0,1}/g)
  {
    if ($3 =~ m/0{1,2}:0{1,2}:0{1,2}/g || $3 eq '') {
      my $time = substr $dayTimeNow, 11, 8;
      $dateReturn = "$1 $time";
    }
  }
  else {
    $dateReturn = $dayTimeNow;
  }

  my $sth = $dbh->prepare(
    "update opl_loan set dateReturn=? 
                             where idloan= ? && dateReturn is null "
  );
  my $result = $sth->execute($dateReturn, $idLoan);
  $sth->finish;
  return $result eq '0E0' ? 0 : 1;

}

#-----------------------------------------------------------------------------------------------
sub _illLoanLookup {
  my ($dbh, $ill_loanId, $barcode) = @_;
  my ($idLoan) = $dbh->selectrow_array(
    "select l.idloan from opl_loan l inner join opl_ILL_out o using(barcode) 
                                        inner join opl_ILL_user u on u.lid= o.ILL_lid 
                                        where l.dateReturn is null && o.ILL_loanId=$ill_loanId"
  );
  if (!$idLoan) {
    ($idLoan) = $dbh->selectrow_array(
      "select l.idloan from opl_loan l inner join opl_user u using(uid) 
                                        inner join opl_category c on c.catid=u.categoryCode where l.barcode='$barcode' 
                                        && l.dateReturn is null && c.catname regexp 'ILL|Inter library loan'"
    );
  }
  return $idLoan;
}

#-----------------------------------------------------------------------------------------------
sub calFine_lost {
  my ($dbh, $barcode) = @_;
  my $reVal = 0;
  return $reVal;
}

#-----------------------------------------------------------------------------------------------
sub calFine_damage {
  my ($dbh, $barcode) = @_;
  my $reVal = 0;
  return $reVal;
}

#-----------------------------------------------------------------------------------------------
sub calFine_overdue {
  my ($dbh, $uid, $barcode) = @_;
  my $reVal = 0;

  return $reVal;
}

#-----------------------------------------------------------------------------------------------
sub circ_setAvailable {
  my ($dbh, $barcode, $oldStatus) = @_;

  my $sth = $dbh->prepare("update opl_item set available=1 where barcode=?");
  $sth->execute($barcode);
  $sth->finish;

#$sth = $dbh->prepare("insert into opl_itemstatus set barcode=?, ondate=now(), status=1");
#$sth->execute($barcode);
#$sth->finish;
  mxml_updateItemStatus($dbh, $barcode, 1, '');

  if ($oldStatus == IT_STAT_LOST) {
    $sth = $dbh->prepare("insert into opl_found set barcode=?, ondate=now()");
    $sth->execute($barcode);
    $sth->finish;
  }

  return TRUE;
}

#-----------------------------------------------------------------------------------------------
sub circ_updateItemStatus {
  my ($dbh, $barcode, $status) = @_;

#my $sth = $dbh->prepare("insert into opl_itemstatus set barcode=?, ondate=now(), status=? ");
#$sth->execute($barcode,$status);
#$sth->finish;
  mxml_updateItemStatus($dbh, $barcode, $status, '');
  if ($status == ITEM_DAMAGED || $status == ITEM_LOST) {
    my $sth = $dbh->prepare("update opl_item set available=? where barcode=?");
    $sth->execute(ITEM_INACTIVE, $barcode);
    $sth->finish;
  }

}

#-----------------------------------------------------------------------------------------------
#sub circ_processRenew_obsolete {
#    my ($dbh,$uid,$barcode,$dateDue)=@_;
#    my $sth = $dbh->prepare("update  opl_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_processRenew {
  my ($dbh, $uid, $barcode, $dateDue, $third_party_allowed) = @_;

  ($third_party_allowed && $third_party_allowed eq 'Y')
    || ($third_party_allowed = 'N');

  my $dateRenew = date_now();
  my $loan = _getLoan($dbh, $barcode, $dateRenew);

  if (!$loan || ($uid != $loan->{'uid'} && $third_party_allowed ne 'Y')) {
    return { renewal_ok => 0 };
  }
  if ($loan->{'daysOverdue'} > 0 || $loan->{'hoursOverdue'} > 0) {
    my $odl_id = circ_record_ODL_ext(
      $dbh, $loan->{'lid'}, 'overdue',
      $loan->{'daysOverdue'},
      $loan->{'hoursOverdue'}
    );
    $loan->{'odl_id'} = $odl_id;
  }

  my $renewal = $loan;
  my $sql     = <<_SQL_;
update  opl_loan
set     renewalCount = renewalCount + 1,
        dateDue = ?,
        dateRenewal = now()
where   idloan = ?
_SQL_

  my @val = ($dateDue, $loan->{'idloan'});
  my $rv = $dbh->do($sql, undef, @val);

  $renewal->{'renewal_ok'} = (!$rv || $rv eq '0E0') ? 0 : 1;

  return $renewal;
}

#-----------------------------------------------------------------------------------------------
sub circ_userListLoan {
  my ($dbh, $idNumber, $sort) = @_;

  my $qry = "
select  i.rid,i.price,idloan,i.callNumber,
        m.title, m.titleSort,m.author, m.pubName, m.pubDate,m.tempIll,
        l.dateDue,l.dateLoan,l.barcode,l.renewalCount,l.maxRenewal,
        to_days(now()) - to_days(dateDue) as deltaDueDate,
        to_days(now()) - to_days(dateLoan) as deltaDate
from    opl_loan        as l,
        opl_item        as i,
        opl_marcRecord  as m
where   uid = ?            &&
        dateReturn is null      &&
        l.barcode = i.barcode   &&
        i.rid = m.rid";
  if (defined $sort && $sort ne '') {
    $qry .= " order by  " . $sort;
  }
  else {
    $qry .= " order by dateLoan desc";
  }

  my $sth = $dbh->prepare($qry);
  $sth->execute($idNumber) || return;

  my @loanList;
  while (my $loan = $sth->fetchrow_hashref) {
    $loan->{'overdue'} = ($loan->{'deltaDueDate'} > 0) ? 1 : 0;
    push @loanList, $loan;
  }
  $sth->finish;

  return \@loanList;
}

#-----------------------------------------------------------------------------------------------
sub circ_userClaimList {
  my ($dbh, $uid) = @_;

  my $sth = $dbh->prepare(<<_SQL_);
select  i.rid,idloan,i.callNumber,
        m.title, m.author, m.pubName, m.pubDate,m.tempIll,
        l.dateDue,l.dateLoan,l.barcode,
        o.type,o.ondate dateClaim
from    opl_loan l inner join opl_odl o using(idloan) 
        inner join opl_item  i using(barcode)
        inner join  opl_marcRecord m using(rid) 
where   (o.type ='claimNeverLoaned' || o.type ='claimReturned')
        && uid=?
order by onDate desc
    
_SQL_

  $sth->execute($uid) || return;

  my @claimList;
  while (my $claim = $sth->fetchrow_hashref) {
    $claim->{'type'} = 11 if ($claim->{'type'} eq "claimReturned");
    $claim->{'type'} = 12 if ($claim->{'type'} eq "claimNeverLoaned");
    push @claimList, $claim;
  }
  $sth->finish;

  return \@claimList;
}

#-----------------------------------------------------------------------------------------------
# Add Feb 29,2008
sub circ_userListLoanStat {
  my ($dbh, $idNumber, $sort) = @_;

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

  my $qry =
"select    l.uid,i.rid, i.callNumber ,m.title, m.author, max(l.dateLoan) dateLoan, 
          max(l.dateDue) dateDue,
          max(l.dateReturn) dateReturn , count(i.rid) as totalCirc 
from      opl_marcRecord m     inner join opl_item i on i.rid=m.rid             
          inner  join opl_loan l on i.barcode=l.barcode   
where     uid=?    && l.dateReturn is not null            
group by  i.rid ";

  if (defined $sort && $sort ne '') {
    $qry .= "order by  " . $sort;
  }
  else {
    $qry .= "order by  totalCirc desc";
  }

  my $sth = $dbh->prepare($qry);
  $sth->execute($idNumber) || return;
  my @loanList;

  while (my $loan = $sth->fetchrow_hashref) {
    $lastLoan_sth->execute($loan->{'rid'}, $idNumber);
    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_userListReserve {
  my ($dbh, $uid, $sort) = @_;

# my $sth = $dbh->prepare("select  distinct r.*
#                             from  opl_reserve as r left outer join opl_hold as h
#                             on r.idreserve=h.idreserve
#                             where r.uid = ? &&  r.dateCancel is null
#                                &&(  (r.dateExpiry >=substring(now(),1,10) && r.numCopyReserve >0)
#                                   ||(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"
#                        );

  my $qry = "select distinct r.* 
              from opl_reserve r left outer join opl_hold h using(idReserve) 
              where uid=? && (
                   (r.dateCancel is null && r.dateExpiry >now() && numCopyReserve>0) 
                || (h.dateLoan is null && h.dateExpiry >now() && h.dateCancel is null)
                )";
  if (defined $sort && $sort ne '') {
    $qry .= " order by " . $sort;
  }
  else {
    $qry .= " order by dateReserve desc";
  }
  my $sth = $dbh->prepare($qry);
  $sth->execute($uid) || return;
  my $sth_recInfo = $dbh->prepare(
    "select m.title,m.titleSort, m.author, m.pubName, m.pubDate,i.callNumber   
                           from opl_marcRecord as m left outer join opl_item as i on m.rid=i.rid 
                            where i.barcode not regexp '^\_\_\_' && m.rid=? limit 1"
  );
  my $sth_notExp = $dbh->prepare(
    "select count(*) as nHold ,min(dateExpiry) as holdExpiry 
                           from opl_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 opl_hold as h 
                           where idreserve=? 
                              && dateExpiry <substring(now(),1,10)  
                              && h.dateCancel is null
                              && h.dateLoan is null"
  );

  my $sth_rsvPriority = $dbh->prepare(
"select count(*) from opl_reserve r left outer join opl_hold h using(idReserve) 
                                           where rid=? && r.dateExpiry>now() && h.dateExpiry is null 
                                           && r.dateCancel is null && idReserve<=?"
  );

  my @reserveList;
  while (my $rsrv = $sth->fetchrow_hashref) {
    my ($nHold, $nHoldExp) = (0, 0);

    #my $nReserve = $rsrv->{'numCopyReserve'};
    $sth_recInfo->execute($rsrv->{'rid'});
    my ($iRec) = $sth_recInfo->fetchrow_hashref;
    $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);
      $rsrv->{'holdExpiry'} = $hRec->{'holdExpiry'};
    }
    $sth_Exp->execute($rsrv->{'idReserve'});
    my ($hERec) = $sth_Exp->fetchrow_hashref;
    if ($hERec && $hERec->{'nHold'} > 0) {
      $nHoldExp = $hERec->{'nHold'};
    }
    $sth_rsvPriority->execute($rsrv->{'rid'}, $rsrv->{'idReserve'});
    my ($priority) = $sth_rsvPriority->fetchrow_array;
    if ($priority && $priority > 0) {
      $rsrv->{'priority'} = $priority;
    }

    $rsrv->{'title'}      = $iRec->{'title'};
    $rsrv->{'titleSort'}  = $iRec->{'titleSort'};
    $rsrv->{'author'}     = $iRec->{'author'};
    $rsrv->{'pubName'}    = $iRec->{'pubName'};
    $rsrv->{'pubDate'}    = $iRec->{'pubDate'};
    $rsrv->{'callNumber'} = $iRec->{'callNumber'};
    $rsrv->{'numHold'}    = $nHold;

    #$rsrv->{'numCopyReserve'}    = $nReserve;
    $rsrv->{'numHoldExp'} = $nHoldExp;
    push @reserveList, $rsrv;
  }
  $sth_recInfo->finish;
  $sth_Exp->finish;
  $sth_notExp->finish;
  $sth->finish;
  return \@reserveList;
}

#----------------------------------------------------------
sub circ_GetLostNumber {
  my ($dbh, $uid) = @_;
  my $sth = $dbh->prepare(
    "select count(*) from opl_loan as l, opl_lost as b 
                    where uid=? && l.idloan=b.idloan"
  );
  $sth->execute($uid);
  my ($rec) = $sth->fetchrow_array;
  $sth->finish;
  return $rec;
}

#----------------------------------------------------------
sub circ_GetOnloanNumber {
  my ($dbh, $uid) = @_;
  my $sth = $dbh->prepare(
    "select count(*) from opl_loan as l 
                    where uid=? and dateReturn is null"
  );
  $sth->execute($uid);
  my ($rec) = $sth->fetchrow_array;
  $sth->finish;
  return $rec;
}

#----------------------------------------------------------
sub circ_GetOverdueNumber {
  my ($dbh, $uid) = @_;
  my $sth = $dbh->prepare(
    "select count(*) from opl_loan as l 
                    where uid=?  && dateReturn is null && dateDue < now()"
  );
  $sth->execute($uid);
  my ($rec) = $sth->fetchrow_array;
  $sth->finish;
  return $rec;
}

#----------------------------------------------------------
sub circ_GetReserveNumber {
  my ($dbh, $uid) = @_;
  my $retval = 0;
  my $sth    = $dbh->prepare(
    "select sum(numCopyReserve) from opl_reserve
                            where uid=? && dateExpiry>now() && dateCancel is null
                            "
  );
  $sth->execute($uid);
  if (my ($nr) = $sth->fetchrow_array) {
    $retval = $nr;
  }
  $sth = $dbh->prepare(
    "select count(*) 
                        from opl_hold h inner join opl_reserve r on r.idReserve=h.idReserve                            
                        where r.uid=? && h.dateExpiry>now() && h.dateCancel is null && h.dateLoan is null
                            "
  );
  $sth->execute($uid);
  if (my ($nr) = $sth->fetchrow_array) {
    $retval += $nr;
  }

  $sth->finish;
  return $retval;
}

#----------------------------------------------------------
sub circ_GetDamagedNumber {
  my ($dbh, $uid) = @_;
  my $nr  = 0;
  my $sth = $dbh->prepare(
    "select count(*) from opl_loan as l, opl_damage as d 
                    where uid=? && l.idloan=d.idloan"
  );
  $sth->execute($uid);
  my ($rec) = $sth->fetchrow_array;
  $sth->finish;
  return $rec;
}

#-----------------------------------------------------------------------------------------------
sub circ_claimNeverLoan {
  my ($dbh, $barcode) = @_;

  # Remove claim items from loan list
  my $szSQL =
"update opl_loan set dateReturn=now() where barcode=? && dateReturn is null";
  my $sth     = $dbh->prepare($szSQL);
  my $bResult = 0;
  $bResult = $sth->execute($barcode);
  $sth->finish;

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

  # Insert new item's status
  mxml_updateItemStatus($dbh, $barcode, ITEM_CLAIM_NEVER_LOAN, '');

}

#-----------------------------------------------------------------------------------------------
sub circ_claimReturned {
  my ($dbh, $barcode) = @_;

  # Remove claim items from loan list
  my $szSQL =
"update opl_loan set dateReturn=now() where barcode=? && dateReturn is null";
  my $sth     = $dbh->prepare($szSQL);
  my $bResult = 0;
  $bResult = $sth->execute($barcode);
  $sth->finish;

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

  # Insert new item's status
  mxml_updateItemStatus($dbh, $barcode, ITEM_CLAIM_RETURN, '');

}

#-----------------------------------------------------------------------------------------------
sub circ_lostDeclarating {
  my ($dbh, $barcode) = @_;

  # Remove lost items from loan list
  my $szSQL =
"update opl_loan set dateReturn=now() where barcode=? && dateReturn is null";
  my $sth     = $dbh->prepare($szSQL);
  my $bResult = 0;
  $bResult = $sth->execute($barcode);
  $sth->finish;

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

  # Insert new item's status
  #$szSQL = "insert into opl_itemstatus set barcode=?, ondate=now(), status=?";
  #$sth = $dbh->prepare($szSQL);
  #$bResult = $sth->execute($barcode, ITEM_LOST);
  #$sth->finish;
  mxml_updateItemStatus($dbh, $barcode, ITEM_LOST, '');

}

#-----------------------------------------------------------------------------------------------
sub circ_record_ODL {
  my ($dbh, $idloan, $type, $days_overdue) = @_;
  circ_record_ODL_ext($dbh, $idloan, $type, $days_overdue, 0);

}

#-----------------------------------------------------------------------------------------------
sub circ_record_ODL_ext {
  my ($dbh, $idloan, $type, $days_overdue, $hours_overdue, $resp, $note) = @_;

  # Insert new item's status
  $resp = 1  if (!defined $resp);
  $note = "" if (!defined $note);
  my $szSQL =
"insert into opl_odl set idloan=?, type=?,days_overdue=?, hours_overdue=?, responsible=?,note=?, ondate=now()";
  my $sth = $dbh->prepare($szSQL);
  my $bResult =
    $sth->execute($idloan, $type, $days_overdue, $hours_overdue, $resp, $note);
  my $odl_id = $dbh->{'mysql_insertid'};
  $sth->finish;
  return $odl_id;
}

#--------------------------------------------------
#
sub circ_GetReserveList {
  my ($dbh, $idNumber) = @_;

  my $reserveList      = circ_userListReserve($dbh, $idNumber);
  my $bZebraServerDown = 0;
  my $NumOfReserves    = 0;

  foreach my $rec (@$reserveList) {
    $rec->{'dateReserve'} = date_text($rec->{'dateReserve'}, 0);
    $rec->{'dateExpiry'}  = date_text($rec->{'dateExpiry'},  0);

    $NumOfReserves += $rec->{'numCopyReserve'};
  }

  # Sort result and set into template
  @$reserveList = sort { $a->{'title'} cmp $b->{'title'} } @$reserveList;

  return ($bZebraServerDown, \@$reserveList, $NumOfReserves);
}

#------------------------------------------------
#Fri, Aug 13, 2010 @ 11:54:11 EDT
sub circ_getRecCircStatus {
  my ($dbh, $rid) = @_;
  my $bid = eb_getBidByMarcXmlRid($dbh, $rid);
  my $retVal = {};
  if ($bid && $bid > 0) {
    $retVal = circ_getRecCircStatus_ebook($dbh, $bid);
  }
  else {
    $retVal = circ_getRecCircStatus_book($dbh, $rid);
  }
  return $retVal;
}

#------------------------------------------------
sub circ_getRecCircStatus_ebook {
  my ($dbh, $bid) = @_;
  my ($numTotal, $numLoan, $numReserve, $numHold, $available) = (0, 0, 0, 0, 0);
  my $ebLicense = eb_getLicense($dbh, $bid);
  $numTotal = $ebLicense->{'totalLicense'};
  my $licenseType = $ebLicense->{'licenseType'};
  $numLoan = eCirc_getTotalOnLoan($dbh, $bid);
  $numReserve = eCirc_getTotalOnReserve($dbh, $bid);
  $available = eCirc_getTotalAvailable($dbh, $bid);
  return {
    totalHolding => $numTotal,
    onLoan       => $numLoan,
    onReserve    => $numReserve,
    onHold       => $numHold,
    available    => $available,
    countLost    => 0,
    countDamaged => 0,
    countMissing => 0,
    ebookLicType => $licenseType,
    bid          => $bid
  };

}

#------------------------------------------------
sub circ_getRecCircStatus_book {
  my ($dbh, $rid) = @_;
  my ($numTotal, $numLoan, $numReserve, $numHold, $available, $numOnShevingCart)
    = (0, 0, 0, 0, 0, 0);

  # Total active holdings
  ($numTotal) = $dbh->selectrow_array(<<_STH_);
select  count(barcode)
from    opl_item
where   rid = $rid && available=1
_STH_

  ($numLoan) = $dbh->selectrow_array(<<_STH_);
select  count(l.barcode)
from    opl_item i inner join opl_loan l using(barcode)
where   rid = $rid && dateReturn is null
_STH_

  ($numReserve) = $dbh->selectrow_array(<<_STH_);
select  sum(numCopyReserve)
from    opl_reserve r inner join opl_user u using(uid)
where   rid = $rid && dateExpiry >now() && dateCancel is null
        && (suspendFrom is null ||(now() not between suspendFrom and suspendUntil))
_STH_

  #open debug , "/tmp/vv"; print debug "numReserve:$numReserve";close debug;
  ($numHold) = $dbh->selectrow_array(<<_STH_);
select  count(*)
from    opl_reserve r inner join opl_hold h using(idReserve)
        inner join opl_user u using(uid)
where   r.rid = $rid && h.dateExpiry >now() &&
        h.dateLoan is null && h.dateCancel is null 
_STH_

  ($numOnShevingCart) = $dbh->selectrow_array(<<_STH_);
select count(*) 
from opl_item i inner join opl_loan l using(barcode) 
where rid=$rid && l.dateReturn>now()- INTERVAL timeOnShelvingCart minute

_STH_

  $available = $numTotal - $numLoan - $numReserve - $numHold;
  $available = 0 if ($available < 0);

  #Mon, Jan 31, 2011 @ 12:01:03 EST
  # adding lost/damaged/mssing cound into record info
  my $lmd = getRecordLMD_count($dbh, $rid);

  #/Mon, Jan 31, 2011 @ 12:01:49 EST

  return {
    totalHolding      => $numTotal,
    onLoan            => $numLoan,
    onReserve         => $numReserve,
    onHold            => $numHold,
    available         => $available,
    countLost         => $lmd->{'lost'},
    countDamaged      => $lmd->{'damaged'},
    countMissing      => $lmd->{'missing'},
    countOnOrder      => $lmd->{'onOrder'},
    countInProcessing => $lmd->{'inProcessing'},
    countInRepair     => $lmd->{'inRepair'},
    countWeed         => $lmd->{'weed'},
    onShelvingCart    => $numOnShevingCart
  };
}

#------------------------------------------------
sub getRecordLMD_count {
  my ($dbh, $rid) = @_;
  my ($lost, $damaged, $missing, $onOrder, $inProcessing, $inRepair,$weed) =
    (0, 0, 0, 0, 0, 0,0);
  my $sth = $dbh->prepare(
"select barcode from opl_item where rid= ? && available=0 && barcode not regexp '^\_\_\_'"
  );
  my $sth_status = $dbh->prepare(
"select status from opl_itemstatus where barcode=? && status <>6  && (note is null || note<>'merged') order by id desc limit 1"
  );
  $sth->execute($rid);
  while (my ($bc) = $sth->fetchrow_array) {
    $sth_status->execute($bc);

    if (my $s = $sth_status->fetchrow_hashref) {
      if ($s->{'status'} == ITEM_DAMAGED) {
        $damaged++;
      }
      if ( $s->{'status'} == ITEM_INACTIVE
        || $s->{'status'} == ITEM_CLAIM_RETURN
        || $s->{'status'} == ITEM_CLAIM_NEVER_LOAN)
      {
        $missing++;
      }
      if ($s->{'status'} == ITEM_LOST) {
        $lost++;
      }
      if ($s->{'status'} == ITEM_ON_ORDER) {
        $onOrder++;
      }
      if ($s->{'status'} == ITEM_IN_PROCESSING) {
        $inProcessing++;
      }
      if ($s->{'status'} == ITEM_IN_REPAIR) {
        $inRepair++;
      }
      if ($s->{'status'} == ITEM_WEED) {
        $weed++;
      }
    }
  }
  return {
    lost         => $lost,
    damaged      => $damaged,
    missing      => $missing,
    onOrder      => $onOrder,
    inProcessing => $inProcessing,
    inRepair     => $inRepair,
    weed         =>$weed
  };
}

#------------------------------------------------
# Mon, Apr 08, 2013 @ 11:02:39 EDT

sub circ_infoRecord {
  my ($dbh, $rid, $itemList) = @_;
  my ($numTotal, $numLoan, $numReserve, $numHold, $totalCirc, $bid) =
    (0, 0, 0, 0, 0, 0);
  my $bid = eb_getBidByMarcXmlRid($dbh, $rid);
  if ($bid && $bid > 0) {
    ($numTotal, $numLoan, $numReserve, $numHold, $itemList, $totalCirc, $bid) =
      circ_infoRecord_ebook($dbh, $bid, $itemList);
  }
  else {
    ($numTotal, $numLoan, $numReserve, $numHold, $itemList, $totalCirc) =
      circ_infoRecord_book($dbh, $rid, $itemList);
  }
  return ($numTotal, $numLoan, $numReserve, $numHold, $itemList, $totalCirc,
    $bid);
}

#------------------------------------------------

=item circ_

The circ_() function
# of copies = # of loans + # of holdings + # of availables
copies: opl_item
loan: opl_loan
holding: opl_reserve

Input: $itms is an array reference

Only reserves with status of 1 or 2 are checked.

=cut

sub circ_infoRecord_book {
  my ($dbh, $rid, $itemList) = @_;
  my ($numTotal, $numLoan, $numReserve, $numHold, $totalCirc) = (0, 0, 0, 0, 0);

  #    $numTotal = scalar(@$itemList);

  my ($sth, $sth_status);

  # Total holdings for circulation
  $sth = $dbh->prepare(<<_STH_);
select  upper(barcode) as barcode, available
from    opl_item
where   rid = ?
_STH_
  $sth->execute($rid) || return;
  my $holdingAvail;
  while (my ($bc, $avail) = $sth->fetchrow_array) {
    $holdingAvail->{ uc($bc) } = $avail;
    $numTotal++ if ($avail);
  }
  $sth->finish;
  foreach my $i (@$itemList) {
    $i->{'available'} = $holdingAvail->{ $i->{'barcode'} };
  }

  # Number of loan
  $sth = $dbh->prepare(<<_STH_);
select  dateDue,
        to_days(now()) - to_days(dateDue) as deltaDateDue
from    opl_loan
where   barcode = ? &&
        dateReturn is null
_STH_

  # --------------------------------------------------
  # Modified on 2006-01-18
  #       Add: item status --damaged;lost;missing
  # --------------------------------------------------
  $sth_status = $dbh->prepare(<<_STH_);
select  barcode,status
from    opl_itemstatus
where   barcode = ?  && status <> ? && (note is null || note<>'merged') 
        order by ondate desc
        limit 0,1
_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;
    }
    elsif ($status == ITEM_DAMAGED) {
      $i->{'damaged'} = 1;
    }
    elsif ($status == ITEM_LOST) {
      $i->{'lost'} = 1;
    }
    else {
      $sth->execute($i->{'barcode'}) || return;
      ($i->{'dateDue'}, $ddd) = $sth->fetchrow_array;
      if ($i->{'dateDue'}) {
        $numLoan++;
        $i->{'dateDue'} = date_text($i->{'dateDue'}, 0);
        $i->{'overdue'} = ($ddd > 0) ? 1 : 0;
      }
    }
  }
  $sth->finish;
  $sth_status->finish;

  # Number of reserves
  $sth = $dbh->prepare(<<_STH_);
select  numCopyReserve
from    opl_reserve r inner join opl_user u using(uid)
where   rid = ? &&
        numCopyReserve > 0 &&
        dateCancel is null &&
        to_days(now()) - to_days(dateExpiry) <= 0
        && (suspendFrom is null ||(now() not between suspendFrom and suspendUntil))
_STH_
  $sth->execute($rid) || return;
  while ((my $nReserve) = $sth->fetchrow_array) {
    $numReserve += $nReserve;
  }
  if (!$numReserve) {
    $numReserve = 0;
  }
  $sth->finish;

  # Number of full holds (reserve ready)
  $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_hold    as H,
        opl_reserve as R inner join opl_user U using(uid)
where   R.rid = ? &&
        R.idReserve = H.idReserve &&
        H.dateLoan is null &&
        H.dateCancel is null &&
        to_days(now()) - to_days(H.dateExpiry) <= 0
_STH_
  $sth->execute($rid) || return;
  ($numHold) = $sth->fetchrow_array;
  $sth->finish;

  # --------------------------------------------------
  # Added on 2008-03-07
  #     Get number of circulation for the record
  # --------------------------------------------------
  my $sql = <<_STH_;
select    count(i.rid) as totalCirc
from      opl_item i   
          inner  join opl_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;
  my $nAvailable = $numTotal - $numLoan - $numHold;
  if ($numReserve > 0 && $nAvailable) {
    circ_adjustHoldReserve($dbh);
    if ($nAvailable > $numReserve) {
      $nAvailable -= $numReserve;
      $numHold    = $numReserve;
      $numReserve = 0;
    }
    else {
      $numHold = $nAvailable;
      $numReserve -= $nAvailable;
      $nAvailable = 0;

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

#-----------------------------------------------------
# added on Mon, Aug 13, 2012 @ 12:28:14 EDT
sub Circ_validateSagebrushBarcode {
  my ($dbh, $bc) = @_;
  $bc =~ s/ +//g;
  my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");

  $sth->execute($bc) || return;
  my ($retBc) = $sth->fetchrow_array;
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
    if ($retBc =~ m/^0/gi) {
      $retBc =~ s/^0+//g;
      $retBc =~ s/0\d\d$//g;
      $sth->execute($retBc) || return;
      ($retBc) = $sth->fetchrow_array;
    }
  }
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
  }
  $sth->finish;
  return $retBc;

}

#########################################################################
# Wed, Sep 12, 2012 @ 08:36:19 EDT
# FORMAT: length:14
#         1st char:  TYPE
#           (For Code 39 Mod 10 : 3 = library books, 4 = textbooks, and 2 = patrons OR
#            For Follett Classic: T = library books, X = textbooks, and P = patrons)
#         4   chars: INDICATOR LOCATION
#         8   chars: CODE ITEM OR PATRON (these are numeric)
#         1   char:  NUMBER CHECK DIGIT
#            (For Mod 10 this character is numeric (digits 0-9).
#             For Mod 43 this character is alphanumeric  (characters A-Z
#                  plus the symbols “-“, “.”, blank space,“$”, ”/”, “+”, “%”)
#
sub Circ_validateFollettBarcode {
  my ($dbh, $bc) = @_;
  $bc =~ s/ +//g;

  my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");
  $sth->execute($bc) || return;
  my ($retBc) = $sth->fetchrow_array;

  if (!$retBc || $retBc eq '') {
    my $fBc = $bc;
    if ($fBc =~ s/^3([\w]{4})*([\w]{8})[A-Z\-\.\s\$\/\+\%]{0,1}//g) {
      $fBc = $2;
      $fBc =~ s/^[0]+//g;
      $sth->execute($fBc) || return;
      ($retBc) = $sth->fetchrow_array;
    }
    else {
      return Circ_validateFollettBarcode_classic($dbh, $bc);
    }
  }
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
  }
  $sth->finish;
  return $retBc;

}

#-----------------------------------------------------
# added on 2007-09-13
sub Circ_validateFollettBarcode_classic {
  my ($dbh, $bc) = @_;
  $bc =~ s/ +//g;
  my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");

  $sth->execute($bc) || return;
  my ($retBc) = $sth->fetchrow_array;
  if (!$retBc || $retBc eq '') {
    my $fBc = folletize($bc);
    $sth->execute($fBc) || return;
    ($retBc) = $sth->fetchrow_array;

  }
  if (!$retBc || $retBc eq '') {
    my $fBc = deFolletize($bc);
    $sth->execute($fBc) || return;
    ($retBc) = $sth->fetchrow_array;

  }
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
  }
  $sth->finish;
  return $retBc;

=item    
    my $fBc=folletize($bc);
    my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");
    
    $sth->execute($fBc) || return;
    my ($retBc) = $sth->fetchrow_array;
    if(!$retBc ||$retBc eq ''){
        $retBc =deFolletize($bc);
        $sth->execute($retBc) || return;
        ($retBc) = $sth->fetchrow_array;

    }
    if(!$retBc ||$retBc eq ''){
        $retBc=$bc;
    }
    $sth->finish;
    return $retBc;
=cut    

}

#-----------------------------------------------------
# added on 2007-10-05
sub folletize {
  my ($bc) = @_;

  $bc = deFolletize($bc);
  $bc = 'T' . substr("0000000$bc", -7);

=item
  $bc =~ s/ +//g;
  if($bc !~ m/^T[\d]{7}/){
    if(length($bc)==7){
       $bc ="T" . $bc ;

    }
    elsif(length($bc )<7){
      $bc="0000000$bc";
      $bc  ="T" . substr $bc,-7;
    }
    else{
      $bc  ="T" . substr $bc,0,7;
    }
  }
=cut

  return $bc;
}

#-----------------------------------------------------
# added on 2007-10-05

sub deFolletize {
  my ($bc) = @_;
  if ($bc =~ m/^T/gi) {
    $bc =~ s/^T//gi;
    $bc =~ s/^0+//g;
  }
  elsif ($bc =~ m/^([\d]{7})0\d/g) {
    $bc = $1;
    $bc =~ s/^0+//g;
  }
  return $bc;
}

#-----------------------------------------------------

# Added on Fri, Nov 09, 2007 @ 09:11:17 EST
sub Circ_validateSpectrumBarcode {
  my ($dbh, $bc) = @_;
  $bc =~ s/ +//g;

  my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");

  $sth->execute($bc) || return;
  my ($retBc) = $sth->fetchrow_array;
  if (!$retBc || $retBc eq '') {
    $retBc = deSpectrumize($bc);
    $sth->execute($retBc) || return;
    ($retBc) = $sth->fetchrow_array;

  }
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
  }
  $sth->finish;
  return $retBc;

}

#-----------------------------------------------------

sub deSpectrumize {
  my ($bc) = @_;
  if (length($bc) == 14 || length($bc) == 13) {
    $bc = substr $bc, 5, 8;
    $bc =~ s/^0+//g;
  }
  elsif (length($bc) == 8) {
    $bc = substr $bc, 0, 7;
    $bc =~ s/^0+//g;
  }
  return $bc;
}

#-----------------------------------------------------
# Added on  Tue, Sep 01, 2009 @ 11:10:31 EDT
sub Circ_validateLeadingZeroBc {
  my ($dbh, $bc) = @_;
  $bc =~ s/ +//g;

  my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");

  $sth->execute($bc) || return;
  my ($retBc) = $sth->fetchrow_array;
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
    $retBc =~ s/^0+//g;
    $sth->execute($retBc) || return;
    ($retBc) = $sth->fetchrow_array;

  }
  if (!$retBc || $retBc eq '') {
    $retBc = $bc;
  }
  $sth->finish;
  return $retBc;

}

#-----------------------------------------------------
# Added on Tue, Oct 06, 2009 @ 10:54:59 EDT
#
sub circ_userCircStatByRid {
  my ($dbh, $uid, $rid) = @_;

  my $sth = $dbh->prepare("
select    i.rid, i.barcode, i.callNumber ,m.title, m.author, l.dateLoan, l.dateDue,
          l.dateReturn  
from      opl_marcRecord m     inner join opl_item i on i.rid=m.rid             
          inner  join opl_loan l on i.barcode=l.barcode   
where     uid=?  && i.rid =?                
order by  idloan 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;

}

#------------------------------------------
sub circ_adjustHoldReserve {
  my ($dbh) = @_;
  my $tm = localtime;
  my $todayStr =
    sprintf("%04d-%02d-%02d", $tm->year + 1900, ($tm->mon) + 1, $tm->mday);

  my $sth = $dbh->prepare(
"select rid from opl_reserve where numCopyReserve >0 && dateExpiry >now() && dateCancel is null "
  );

  $sth->execute() || return;
  while (my ($rid) = $sth->fetchrow_array) {
    my $nAvailable = circ_numItemsAvailable($dbh, $rid);
    my $nextReserve = circ_getNextReserveByRid($dbh, $rid);
    while ($nextReserve && $nAvailable > 0) {

      my $idReserve     = $nextReserve->{'idReserve'};
      my $uidReserve    = $nextReserve->{'uid'};
      my $reserveExpiry = $nextReserve->{'dateExpiry'};

      my $recInfo = circ_getRecInfo($dbh, $rid, $uidReserve);
      my $dateExpiry =
        date_getDeadLineDate($recInfo->{'holdPeriod'}, $todayStr);
      if ($dateExpiry > $reserveExpiry) {
        $dateExpiry = $reserveExpiry;
      }

      circ_fillReserve($dbh, $uidReserve, $rid);
      circ_placeHold($dbh, $idReserve, $todayStr, $dateExpiry);
      $nAvailable = circ_numItemsAvailable($dbh, $rid);
      $nextReserve = circ_getNextReserveByRid($dbh, $rid);

    }

    #alignBookingStatusByRid($dbh, $rid);
  }
}

# -------------------------- BOOKING RESERVE SYSTEM -------------------------
sub circ_saveBooking {
  my ($dbh, $bookingInfo) = @_;
  my $old_booking;
  if (defined $bookingInfo->{'bookingId'} && $bookingInfo->{'bookingId'} > 0) {
    $old_booking = bs_getBookingById($dbh, $bookingInfo->{'bookingId'});
  }
  my $bookingId = bs_saveBooking($dbh, $bookingInfo);
  $bookingInfo->{'bookingId'} = $bookingId;
  alignBookingReserve($dbh, $bookingInfo, $old_booking);
  return $bookingId;

}

#===============================================================================
sub circ_cancelBooking {
  my ($dbh, $bookingId) = @_;
  my $oldBooking = bs_getBookingById($dbh, $bookingId);
  alignBookingReserve($dbh, undef, $oldBooking);
  bs_cancelBooking($dbh, $bookingId);

}

#===============================================================================
sub alignBookingReserve {
  my ($dbh, $newBooking, $oldBooking) = @_;
  my $newItemList;
  my $oldItemList;
  my ($newExpiry, $oldExpiry);
  my ($bookingId, $reqUid) = (0, 0);
  @$newItemList = ();
  @$oldItemList = ();
  if (defined $newBooking) {
    $newItemList  = $newBooking->{'itemList'};
    @$newItemList = sort { $a->{'rid'} <=> $b->{'rid'} } @$newItemList;
    $newExpiry    = $newBooking->{'reserveTo'};
    $bookingId    = $newBooking->{'bookingId'};
    $reqUid       = $newBooking->{'reqUid'};
  }
  if (defined $oldBooking) {
    $oldExpiry    = $oldBooking->{'reserveTo'};
    $oldItemList  = $oldBooking->{'itemList'};
    @$oldItemList = sort { $a->{'rid'} <=> $b->{'rid'} } @$oldItemList;
  }

  my $timeNow = date_now();

  my ($i, $j, $n, $m) = (0, 0, scalar(@$newItemList), scalar(@$oldItemList));
  while ($i < $n && $j < $m) {
    if (@$newItemList[$i]->{'rid'} == @$oldItemList[$j]->{'rid'}) {
      my $rid = @$newItemList[$i]->{'rid'};
      my $numCopyDiff =
        @$newItemList[$i]->{'numOfCopy'} - @$oldItemList[$j]->{'numOfCopy'};
      my $idReserve = @$oldItemList[$j]->{'idReserve'};
      updateBookingReserve($dbh, $rid, $idReserve, $numCopyDiff, $newExpiry,
        $oldExpiry);
      $i++;
      $j++;
    }
    elsif (@$newItemList[$i]->{'rid'} < @$oldItemList[$j]->{'rid'}) {
      my $rid       = @$newItemList[$i]->{'rid'};
      my $numOfCopy = @$newItemList[$i]->{'numOfCopy'};
      my $idReserve =
        addBookingReserve($dbh, $bookingId, $reqUid, $rid, $numOfCopy,
        $newBooking->{'reserveFrom'}, $newExpiry);
      bs_setIdReserve2Booking($dbh, $idReserve, $bookingId, $rid, 1);
      alignBookingStatusByIdReserve($dbh, $idReserve);
      $i++;
    }
    else {
      my $idReserve = @$oldItemList[$j]->{'idReserve'};
      circ_cancelHoldReserve($dbh, $idReserve, $timeNow);
      $j++;
    }
  }
  while ($i < $n) {
    my $rid       = @$newItemList[$i]->{'rid'};
    my $numOfCopy = @$newItemList[$i]->{'numOfCopy'};
    my $idReserve =
      addBookingReserve($dbh, $bookingId, $reqUid, $rid, $numOfCopy, $newBooking->{'reserveFrom'},
      $newExpiry);
    bs_setIdReserve2Booking($dbh, $idReserve, $bookingId, $rid, 1);
    alignBookingStatusByIdReserve($dbh, $idReserve);
    $i++;

  }
  while ($j < $m) {
    my $idReserve = @$oldItemList[$j]->{'idReserve'};
    circ_cancelHoldReserve($dbh, $idReserve, $timeNow);
    $j++;

  }

}

#===============================================================================
sub addBookingReserve {
  my ($dbh, $bookingId, $reqUid, $rid, $numOfCopy, $dateReserve, $dateExpiry) =
    @_;
  my $idReserve = 0;
  my $idReserve =
    circ_placeReserve($dbh, $reqUid, $rid, $numOfCopy, $dateReserve,
    $dateExpiry);
  my $numItemAvailable = circ_numItemsAvailable($dbh, $rid);
  my $numHold = 0;
  if ($numItemAvailable) {
    my $loopEnd =
      $numItemAvailable < $numOfCopy ? $numItemAvailable : $numOfCopy;
    $numHold = $loopEnd;
    for (my $i = 0 ; $i < $loopEnd ; $i++) {
      circ_fillReserve($dbh, $reqUid, $rid);
      circ_placeHold($dbh, $idReserve, $dateReserve, $dateExpiry);
    }
  }

  return $idReserve;

}

#===============================================================================
sub updateBookingReserve {
  my ($dbh, $rid, $idReserve, $numCopyDiff, $newExpiry, $oldExpiry) = @_;
  my $timeNow = date_now();
  if ($numCopyDiff == 0) {
    if ($newExpiry ne $oldExpiry) {
      $dbh->do(
        "update opl_reserve set dateExpiry='$newExpiry' where
            idReserve=$idReserve"
      );
      $dbh->do(
        "update opl_hold set dateExpiry='$newExpiry' where
            idReserve=$idReserve"
      );
    }
  }
  elsif ($numCopyDiff > 0) {
    $dbh->do(
      "update opl_reserve set dateExpiry='$newExpiry',
        numCopyReserve= numCopyReserve + $numCopyDiff 
        where idReserve=$idReserve"
    );

    my $numItemAvailable = circ_numItemsAvailable($dbh, $rid);
    my ($numHold) = $dbh->selectrow_array(
      "select count(*) from opl_hold where idReserve=? 
                            && datecancel is null && dateExpiry >now()"
    );

    if ($numItemAvailable) {
      my $loopEnd =
        $numItemAvailable < $numCopyDiff ? $numItemAvailable : $numCopyDiff;
      $numHold = $loopEnd;
      for (my $i = 0 ; $i < $loopEnd ; $i++) {
        circ_fillReserveById($dbh, $idReserve);
        circ_placeHold($dbh, $idReserve, $timeNow, $newExpiry);
      }
    }

  }
  else {
    my ($numHold) = $dbh->selectrow_array(
      "select count(*) from opl_hold where idReserve=? 
                            && datecancel is null && dateExpiry >now()"
    );
    my ($numReserve) = $dbh->selectrow_array(
      "select numCopyReserve from opl_reserve where idReserve=? )");

    my $n = $numReserve + $numCopyDiff;
    if ($n > 0) {
      $dbh->do(
        "update opl_reserve set dateExpiry='$newExpiry' ,numCopyReserve =$n 
                where idReserve=$idReserve"
      );
    }
    else {
      $dbh->do(
        "update opl_reserve set dateExpiry='$newExpiry' ,numCopyReserve =0 
                where idReserve=$idReserve"
      );
      for (my $i = $n ; $i < 0 ; $i++) {
        circ_cancelHold($dbh, $idReserve);
      }
    }

  }
}

#===============================================================================

sub alignBookingStatusByRid {
  my ($dbh, $rid) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select  bookingId from opl_bookingItem where rid= ? && itemCategory=1 
order by bookingId 
_SQL_

  $sth->execute($rid);
  while (my ($bookingId) = $sth->fetchrow_array) {
    if ($bookingId) {
      alignBookingStatus($dbh, $bookingId);
    }
  }
  $sth->finish;
}

#===============================================================================

sub alignBookingStatusByIdReserve {
  my ($dbh, $idReserve) = @_;

  my $sth = $dbh->prepare(<<_SQL_);
select b.bookingId from opl_bookingItem b inner join opl_bookingRequest r using(bookingId)
where  b.idReserve= ? && b.itemCategory=1  && r.reserveTo>now()
order  by b.bookingId 
_SQL_

  $sth->execute($idReserve);
  while (my ($bookingId) = $sth->fetchrow_array) {
    if ($bookingId) {
      alignBookingStatus($dbh, $bookingId);
    }
  }
  $sth->finish;
}

#===============================================================================
sub alignBookingStatus {
  my ($dbh, $bookingId) = @_;

  my $sth = $dbh->prepare(<<_SQL_);
select status,sum(c1) numOfCopyReserved, sum(c2) numOfHold , sum(c3) numOfLoan  
from  (select i.bookingId,i.numOfCopy c1 , sum(if(h.idloan=0 && dateCancel is null,1,0)) c2 , sum(if(h.barcode<>'',1,0)) c3        
       from   opl_bookingItem i left outer join opl_hold h on h.idReserve=i.idReserve        
       where  i.bookingId= ?   group by h.idReserve) t inner join opl_bookingRequest r using(bookingId)
_SQL_

  $sth->execute($bookingId);
  if (my $c = $sth->fetchrow_hashref) {
    my $status        = $c->{'status'};
    my $totalHoldLoan = $c->{'numOfHold'} + $c->{'numOfLoan'};

    if ($c->{'numOfCopyReserved'} == $c->{'numOfLoan'}) {
      $status = 'filled';
    }
    elsif ($status eq 'waiting' && $c->{'numOfCopyReserved'} == $totalHoldLoan)
    {
      $status = 'processing';
    }
    if ($status ne $c->{'status'}) {
      $dbh->do(
        "update opl_bookingRequest set status='$status' 
                where bookingId= $bookingId"
      );
    }

  }

  $sth->finish;

}

#===============================================================================
sub circ_updateReserve {
  my ($dbh, $idReserve, $numOfCopy) = @_;
  my ($copyReserve, $copyHold) = (0, 0);
  $copyReserve = $dbh->selectrow_array(
    "select numCopyReserve from opl_reserve where idReserve = $idReserve ");
  $copyHold = $dbh->selectrow_array(
"select count(*) from opl_hold h where h.idReserve = $idReserve group by h.idReserve"
  );
  my $numOfCopy_prev = $copyReserve + $copyHold;
  my $numOfCopy_diff = $numOfCopy_prev - $numOfCopy;
  if ($numOfCopy_diff > 0) {
    while ($copyReserve > 0 && $numOfCopy_diff > 0) {
      $copyReserve--;
      $numOfCopy_diff--;
    }
    $dbh->do(
"update opl_reserve set numCopyReserve=$copyReserve where idReserve=$idReserve "
    );
    while ($numOfCopy_diff > 0) {
      circ_cancelHold($dbh, $idReserve);
    }
  }
  elsif ($numOfCopy_diff < 0) {
    $dbh->do(
"update opl_reserve set numCopyReserve=numCopyReserve+$numOfCopy_diff where idReserve=$idReserve "
    );
    circ_adjustHoldReserve($dbh);
  }
}

########################## EBOOK ##############################################
sub circ_infoRecord_ebook {
  my ($dbh, $bid, $itemList) = @_;
  my ($numTotal, $numLoan, $numReserve, $numHold, $totalCirc) = (0, 0, 0, 0, 0);
  my $ebLicense = eb_getLicense($dbh, $bid);
  $numTotal   = $ebLicense->{'totalLicense'};
  $numLoan    = eCirc_getTotalOnLoan($dbh, $bid);
  $numReserve = eCirc_getTotalOnReserve($dbh, $bid);
  return ($numTotal, $numLoan, $numReserve, $numHold, $itemList, $totalCirc,
    $bid);
}

#===============================================================================
sub eCirc_getActiveLoanId {
  my ($dbh, $bid, $uid) = @_;
  my $sth = $dbh->prepare(
    "select idloan from opl_ebLoan where bid=? && uid=? && dateReturn >now()");
  $sth->execute($bid, $uid);
  my $rec = $sth->fetchrow_hashref;
  $sth->finish;
  return $rec->{'idloan'};
}

#===============================================================================
sub eCirc_processLoan {
  my ($dbh, $uid, $bid, $dateLoan, $dateDue) = @_;
  my $dayTimeNow = date_now();
  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 opl_ebLoan
set     uid         = ?,
        bid         = ?,
        dateLoan    = now(),
        dateDue     = ?,
        dateReturn  = ?
_SQL_
  my $sth    = $dbh->prepare($sql);
  my $rv     = $sth->execute($uid, $bid, $dateDue, $dateDue);
  my $idloan = $dbh->{'mysql_insertid'};
  $sth->finish;
  return ($rv eq '0E0') ? 0 : $idloan;
}

#===============================================================================
sub eCirc_processReturn {
  my ($dbh, $idloan) = @_;
  my $sql = <<_SQL_;
update  opl_ebLoan
set     dateReturn     = now()
where   idloan= ?
_SQL_
  my $sth = $dbh->prepare($sql);
  my $rv  = $sth->execute($idloan);
  $sth->finish;
  return ($rv eq '0E0') ? 0 : 1;
}

#===============================================================================
sub eCirc_getNextReserveByBid {

  #Mon, Feb 23, 2009 @ 16:45:31 EST
  my ($dbh, $bid) = @_;
  my $sth = $dbh->prepare(<<_SQL_);
select  u.uid, u.firstname, u.lastname,
        r.bid, r.idReserve, r.dateReserve, r.dateExpiry 
from    opl_ebReserve as r inner join opl_user as u on r.uid=u.uid 
where   bid = ? && 
        idloan=0  && 
        dateCancel is null && 
        dateExpiry > now() 
order by idReserve
limit 1
_SQL_
  $sth->execute($bid);
  my $reserve = undef;
  if (my $r = $sth->fetchrow_hashref) {
    $reserve = $r;
  }
  $sth->finish;

  return $reserve;
}

#===============================================================================
sub eCirc_processReserve {
  my ($dbh, $uid, $bid, $dateReserve, $dateExpiry) = @_;
  my $sth = $dbh->prepare(
    "insert into opl_ebReserve(bid, uid,  dateReserve, dateExpiry)
                             values (?,?,?,?)"
  );
  my $result = $sth->execute($bid, $uid, $dateReserve, $dateExpiry);
  my $reserveId = $dbh->{'mysql_insertid'};

  $sth->finish;
  return $result eq '0E0' ? 0 : $reserveId;
}

#===============================================================================
sub eCirc_updateReserve {
  my ($dbh, $uid, $bid, $idloan) = @_;
  my $sql = <<_SQL_;
update  opl_ebReserve
set     idloan  = ?
where   bid = ? &&
        uid = ? &&
        dateCancel is null &&
        to_days(now()) - to_days(dateExpiry) <= 0
_SQL_
  my $sth = $dbh->prepare($sql);
  my $rv = $sth->execute($idloan, $bid, $uid);
  $sth->finish;
  return $rv eq '0E0' ? 0 : 1;
} #===============================================================================

sub eCirc_updateReserveById {
  my ($dbh, $idReserve, $idloan) = @_;
  my $sql = <<_SQL_;
update  opl_ebReserve
set     idloan  = ?
where   idReserve= ?
_SQL_
  my $sth = $dbh->prepare($sql);
  my $rv = $sth->execute($idloan, $idReserve);
  $sth->finish;
  return $rv eq '0E0' ? 0 : 1;
}

#===============================================================================
sub eCirc_getUserReserveList {
  my ($dbh, $uid, $sort) = @_;
  my $sql = "select   e.* ,m.title, m.author, m.pubName, m.pubDate 
             from     opl_ebReserve e inner join  opl_ridBid r using(bid)
                                   inner join opl_marcRecord m using(rid)
             where    e.dateExpiry >now() &&  dateCancel is null && idloan = 0 && uid = ?";
  if (defined $sort && $sort ne '') {
    $sql .= " order by  " . $sort;
  }
  else {
    $sql .= " order by idReserve desc";
  }
  my $sth = $dbh->prepare($sql);
  $sth->execute($uid) || return;
  my @reserveList;
  while (my $r = $sth->fetchrow_hashref) {
    push @reserveList, $r;
  }
  $sth->finish;
  return \@reserveList;

}

#=======================================================
# Tue, Apr 09, 2013 @ 08:54:06 EDT
#
sub eCirc_isOnLoanTo {
  my ($dbh, $uid, $bid) = @_;
  my $ret = 0;
  my $sth = $dbh->prepare(
    "select idloan from opl_ebLoan where uid=? && bid=? && dateReturn >now()");
  $sth->execute($uid, $bid) || return 0;
  if (my $lid = $sth->fetchrow_hashref) {
    $ret = 1;
  }
  $sth->finish;
  return $ret;
}

#===============================================================================
sub eCirc_isReserveTo {
  my ($dbh, $uid, $bid) = @_;
  my $ret = 0;
  my $sth = $dbh->prepare(
"select idReserve from opl_ebReserve where uid=? && bid=? && dateExpiry >now() && dateCancel is null && idloan=0"
  );
  $sth->execute($uid, $bid) || return 0;
  if (my $idReserve = $sth->fetchrow_hashref) {
    $ret = 1;
  }
  $sth->finish;
  return $ret;
}

#===============================================================================

sub eCirc_getUserLoanList {
  my ($dbh, $uid, $sort) = @_;
  my $sql = "select  r.rid,i.price,idloan,i.callNumber,
                      m.title, m.author, m.pubName, m.pubDate ,m.tempIll,
                      l.dateDue,l.dateLoan,i.barcode,0 as renewalCount ,0 as maxRenewal,0 as overdue,
                      to_days(now()) - to_days(dateLoan) as deltaDate

             from     opl_ebLoan l inner join  opl_ridBid r using(bid)
                                   inner join opl_marcRecord m using(rid)
                                   inner join opl_item i using(rid)
             where    dateReturn >now()  && uid = ?";

  if (defined $sort && $sort ne '') {
    $sql .= " order by  " . $sort;
  }
  else {
    $sql .= " order by dateLoan desc";
  }
  my $sth = $dbh->prepare($sql);
  $sth->execute($uid) || return;
  my @loanList;
  while (my $loan = $sth->fetchrow_hashref) {
    push @loanList, $loan;
  }
  $sth->finish;
  return \@loanList;

}

#===============================================================================
sub eCirc_getTotalOnReserve {
  my ($dbh, $bid) = @_;
  my $sth = $dbh->prepare(
    "select count(bid) from  opl_ebReserve 
                           where  dateExpiry >now() &&  dateCancel is null && idloan = 0 && bid = ? "
  );
  $sth->execute($bid);
  my ($numLoan) = $sth->fetchrow_array;
  $sth->finish;
  return $numLoan eq '0E0' ? 0 : $numLoan;

}

#===============================================================================
sub eCirc_getTotalOnLoan {
  my ($dbh, $bid) = @_;
  my $sth = $dbh->prepare(
    "select count(l.bid) from opl_ebLoan l where dateReturn > now() && bid = ?"
  );
  $sth->execute($bid);

  my ($numLoan) = $sth->fetchrow_array;
  $sth->finish;
  return $numLoan eq '0E0' ? 0 : $numLoan;

}

#===============================================================================
sub eCirc_getTotalAvailable {
  my ($dbh, $bid) = @_;
  my $ebLicense = eb_getLicense($dbh, $bid);
  my $numAvail = 0;
  if (defined $ebLicense) {
    $numAvail = $ebLicense->{'totalLicense'} - eCirc_getTotalOnLoan($dbh, $bid);
  }

  return $numAvail;
}

#===============================================================================
#Wed, Dec 04, 2013 @ 15:58:50 EST
sub getRSLoanPeriod {
  my ($dbh, $uid, $rid) = @_;
  my $loanPeriod = -1;
  my $sth        = $dbh->prepare("
    select r.shelfId,if(loanType='default',-1,if( loanType='daily', loanPeriod div 24,loanPeriod)) period ,count(u.uid) 
    from opl_reserveShelf r inner join opl_reserveShelfItems i using(shelfId) 
                            left outer join opl_reserveShelfUsers u using(shelfId) 
    where rid=? && ((r.reserveFrom='0000-00-00' && reserveTo ='0000-00-00') 
                    || now() between reserveFrom and reserveTo) 
    group by shelfId ");

  $sth->execute($rid);

  while (my ($shelfId, $p, $u) = $sth->fetchrow_array) {
    if ($u == 0 || isUserInRsList($dbh, $shelfId, $uid)) {
      if (($loanPeriod == 0 && $p == -1) || $p > $loanPeriod) {
        $loanPeriod = $p;
      }
    }

  }
  $sth->finish;
  return $loanPeriod;
}

#===============================================================================
sub isUserInRsList {
  my ($dbh, $shelfId, $uid) = @_;
  my $sth = $dbh->prepare(
"select count(*) as count from opl_reserveShelfUsers where shelfId=? &&  uid=?"
  );
  $sth->execute($shelfId, $uid);
  my ($ret) = $sth->fetchrow_array;
  $sth->finish;
  return $ret eq '0E0' ? 0 : $ret;
}

#===============================================================================
sub circ_getItemOnLoan {
  my ($dbh, $barcode) = @_;
  my $sql = <<_SQL_;
select  *
from    opl_loan
where   barcode = ?
     && dateReturn is null
_SQL_

  my @val = ($barcode);
  my $loan = $dbh->selectrow_hashref($sql, undef, @val) || return;
  return $loan;

}

#===============================================================================
sub circ_anonymizeLoan{
    my($dbh)=@_;
    my ($ndays)=$dbh->selectrow_array("select val from opl_preference where var='anonymizeLoan'");
    if($ndays && $ndays>0){ 
        $dbh->do("
        update opl_loan l inner join  opl_user u using(uid) 
        set l.uid=0 
        where dateReturn is not null && u.keepLoanHistory=0 && date_add(dateLoan, interval $ndays day) <now()");
    }
    
}
#===============================================================================
sub updateLoanStats{
    my($dbh,$uid,$barcode,$idloan,$dateLoan)=@_;
    #update user loan count stats
    my($csJson)=$dbh->selectrow_array("select circStats from opl_user where uid=?",undef,$uid);
    $csJson="{}" if(!$csJson || $csJson eq "");
    my $circStats=decode_json($csJson);
    my $year=substr($dateLoan,0,4);
    if($circStats->{$year}){
        $circStats->{$year} +=1;
    }
    else{
        $circStats->{$year}=1;
    }
    $circStats->{"L"} =[] if(!defined $circStats->{"L"});
    push @{$circStats->{"L"}},$idloan;
    $dbh->do("update opl_user set circStats=? where uid=?",undef,to_json($circStats),$uid);
    #update item loan count stats
    ($csJson)=$dbh->selectrow_array("select circStats from opl_item where barcode=?",undef,$barcode);
    $csJson="{}" if(!$csJson || $csJson eq "");
    my $circStats=decode_json($csJson);
    if($circStats->{$year}){
        $circStats->{$year} +=1;
    }
    else{
        $circStats->{$year}=1;
    }
    $dbh->do("update opl_item set circStats=? where barcode=?",undef,to_json($circStats),$barcode);
}