package Opals::BookingSystem;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    bs_saveBooking
    bs_addBooking
    bs_updateBooking
    bs_delBooking
    bs_cancelBooking
    bs_getBookingById
    bs_getBookingById_libCat
    bs_getBookingByUid
    bs_getBooikngItemList
    bs_getBooking4UserList
    bs_createBooking
    bs_addItem2Booking
    bs_updateBookingItemList
    bs_delItemFromBooking
    bs_addUser2Booking
    bs_updateBooking4UserList
    bs_delUserfromBooking
    bs_isRecBooked
    bs_numCopyBooked
    bs_numCopyBookedByBid
    bs_numBItemLoan
    bs_numBItemHold_bk
    bs_numBItemPending_bk
    bs_getBooked4UidList
    bs_isItemBooked
    bs_isItemBooked_cat
    bs_getBooking4ByUid
    bs_isBItemLoan2Uid_bk
    bs_isBcHold4Uid_bk
    bs_isItemLoan2Uid_bk
    bs_isBcHold_bk
    bs_isItemBooked4Uid
    bs_getBookingStatus
    bs_fill_bk
    bs_placeHold_bk
    bs_getBookingId4hold_bk
    bs_getBookingInfo

    bs_getBookingList
    bs_getBookingListByGrp
    bs_updateBookingStatus
    bs_getBookingBarcodeList_bk
    bs_getAvailableBarcodeList
    bs_markDelBarcodeHold_bk
    bs_removeDelBarcodeHold_bk
    bs_setIdReserve2Booking
    
);
# Version number
$VERSION   = 0.01;      


use POSIX qw(
    ceil
);
use strict;

use Time::localtime;


use strict;
use Date::Calc::Object qw(
    :all
);
use Opals::Date qw(
    date_now
    date_today
    date_validateWorkday
    date_addDeltaWorkday
    date_text
    date_getDeadLineDate
);
################################################################################
sub bs_saveBooking{
    my ($dbh, $bookingInfo)=@_;
    my $bookingId=0;
    if(defined $bookingInfo->{'bookingId'} && $bookingInfo->{'bookingId'}>0){
        bs_updateBooking($dbh, $bookingInfo->{'bookingId'},$bookingInfo);
        $bookingId=$bookingInfo->{'bookingId'};
    }
    else{
        $bookingId =bs_addBooking($dbh, $bookingInfo);
    }
    return $bookingId;
}

################################################################################
sub bs_addBooking{
    my ($dbh, $bookingInfo)=@_;
=item
   
****** NOTE **********

Structure of bookingInfo:

     {reqUid=>userId,
      reqFirstname=>'',
      reqLastname=>'',
      department=>'',
      section=>'',
      reserveFrom=>'',
      reserveTo=>'',
      itemList=>({rid=>22,itemCategory=>1,numOfCopy=>1},....),
      reserve4UserList=>(uid1,uid2.....)
     }

****** /NOTE **********
     
=cut    
    my $bookingId =bs_createBooking($dbh,
                                     $bookingInfo->{'responsible'},
                                     $bookingInfo->{'reqUid'},
                                     $bookingInfo->{'reqFirstname'},
                                     $bookingInfo->{'reqLastname'},
                                     $bookingInfo->{'department'},
                                     $bookingInfo->{'course'},
                                     $bookingInfo->{'section'},
                                     $bookingInfo->{'reserveFrom'},
                                     $bookingInfo->{'reserveTo'}
                                    );
    foreach my $item(@{$bookingInfo->{'itemList'}}){
        bs_addItem2Booking($dbh,
                           $bookingId,
                           $item->{'rid'},
                           $item->{'itemCategory'},
                           $item->{'numOfCopy'}
                          );
    }
    foreach my $uid(@{$bookingInfo->{'reserve4UserList'}}){
        bs_addUser2Booking($dbh,$bookingId,$uid);
    }
    return $bookingId;

}

################################################################################
sub bs_updateBooking{
    my ($dbh,$bookingId,$bookingInfo)=@_;
# Structure of bookingInfo as in bs_addReserve:
#
    updateBookingRequest($dbh,
                     $bookingId,
                     $bookingInfo->{'reqUid'},
                     $bookingInfo->{'reqFirstname'},
                     $bookingInfo->{'reqLastname'},
                     $bookingInfo->{'department'},
                     $bookingInfo->{'section'},
                     $bookingInfo->{'reserveFrom'},
                     $bookingInfo->{'reserveTo'}
                     );
    bs_updateBookingItemList($dbh,$bookingId,$bookingInfo->{'itemList'});  
    bs_updateBooking4UserList($dbh,$bookingId,$bookingInfo->{'reserve4UserList'});              

}
#===============================================================================
sub bs_delBooking{

    my ($dbh,$bookingId)=@_;
    delBookingRequest($dbh,$bookingId);
    bs_delItemFromBooking_all($dbh,$bookingId);
    bs_delUserfromBooking_all($dbh,$bookingId);

}
#===============================================================================
sub bs_cancelBooking{

    my ($dbh,$bookingId)=@_;
    if(defined $bookingId && $bookingId>0){
        $dbh->do("update opl_bookingRequest set status='cancel' where bookingId=$bookingId");
    }
}
#===============================================================================
sub bs_updateBookingStatus{

    my ($dbh,$bookingId,$status)=@_;
    if(defined $bookingId && $bookingId>0){
        $dbh->do("update opl_bookingRequest set status='$status' where bookingId=$bookingId");
    }
}
#===============================================================================
sub bs_getBookingById{
    my ($dbh,$bookingId)=@_;
    my $bookingInfo={};
    my $reserveInfo = bs_getBookingInfo($dbh,$bookingId);
    if($reserveInfo->{'bookingId'}>0){
        $bookingInfo = $reserveInfo;    
        $bookingInfo->{'itemList'}=bs_getBooikngItemList($dbh,$bookingId);        
        $bookingInfo->{'reserve4UserList'}=bs_getBooking4UserList($dbh,$bookingId);
    }
    return $bookingInfo;
}
#===============================================================================
sub bs_getBookingById_libCat{
    my ($dbh,$bookingId)=@_;
    my $bookingInfo=bs_getBookingById($dbh,$bookingId);
    my $sth =$dbh->prepare(<<_SQL_);
    select r.title ,r.author,r.pubPlace,r.pubName,r.pubDate,i.callNumber 
    from opl_marcRecord r left outer join opl_item i using(rid)
    where r.rid=? 
_SQL_
    my $numOnHold  =getbookingNumOnHold($dbh,$bookingId);
    my $loanList =getbookingOnLoanList($dbh,$bookingId);
    my $itemList = $bookingInfo->{'itemList'};
    foreach my $item(@$itemList){
        $sth->execute($item->{'rid'});
        while(my $rec =$sth->fetchrow_hashref){
             $item->{'title'}       =$rec->{'title'};
             $item->{'author'}      =$rec->{'author'};
             $item->{'pubPlace'}    =$rec->{'pubPlace'};
             $item->{'pubName'}     =$rec->{'pubName'};
             $item->{'pubDate'}     =$rec->{'pubDate'};
             $item->{'callNumber'}  =$rec->{'callNumber'};
             $item->{'isbn'}        =getItemISBN_libCat($item->{'rid'});
             $item->{'isbnList'}    =join(",",@{$item->{'isbn'}});
             $item->{'numOnHold'}   =defined $numOnHold->{$item->{'rid'}}?$numOnHold->{$item->{'rid'}}:0;
             if(defined $loanList->{$item->{'rid'}}){
                 $item->{'loanList'}    =$loanList->{$item->{'rid'}};
             }
        }
        $sth->finish;
    }

     $bookingInfo->{'startDate'} = substr $bookingInfo->{'reserveFrom'} ,0,10;
     $bookingInfo->{'startTime'} = substr $bookingInfo->{'reserveFrom'},11,5;
     $bookingInfo->{'endDate'}   = substr $bookingInfo->{'reserveTo'} ,0,10;
     $bookingInfo->{'endTime'}   = substr $bookingInfo->{'reserveTo'},11,5;

     my @tmp=  sort bibSortFunc @$itemList;
     $bookingInfo->{'itemList'}=\@tmp;
    return $bookingInfo;
}
#--------------------------------------------
sub getbookingNumOnHold{
    my($dbh,$bookingId)=@_;
    my $retval={};
    my $sth=$dbh->prepare(<<_SQL_);
    select b.rid,count(h.idReserve) nHold
    from   opl_bookingItem b inner join opl_hold h using(idReserve) 
    where  bookingId=? && idloan=0 && dateCancel is null && dateExpiry >now()
    group  by b.rid 
_SQL_

    $sth->execute($bookingId);
    while(my ($rid,$nHold)=$sth->fetchrow_array){
           $retval->{$rid} = $nHold;
    }
    $sth->finish;
    return $retval;
}
#--------------------------------------------
sub getbookingOnLoanList{
    my($dbh,$bookingId)=@_;
    my $retval={};
    my $sth=$dbh->prepare(<<_SQL_);
select u.uid,b.rid,l.barcode,l.dateLoan,l.dateDue ,l.dateReturn, 
            if(lastname<>'' && lastname is not null && firstname <>'' && firstname is not null,concat(lastname,', ',firstname),concat(if(lastname is null,'',lastname),firstname)) as name 
     from   opl_bookingItem b inner join opl_hold h using(idReserve) 
            inner join opl_loan l using(idloan) 
            inner join opl_user u on u.uid=l.uid 
     where  bookingId=?
_SQL_
    
    $sth->execute($bookingId);
    while(my $rec=  $sth->fetchrow_hashref){
           push @{$retval->{$rec->{'rid'}}}, $rec;
    }
    $sth->finish;
    return $retval;
}

#===============================================================================
sub bibSortFunc{
    if(lc($a->{'callNumber'}) gt lc($b->{'callNumber'})){
        return 1;
    }
    elsif(lc($a->{'callNumber'}) lt lc($b->{'callNumber'})){
        return -1;
    }
    else{
       if(lc($a->{'author'}) gt lc($b->{'author'})){
            return 1;
        }
        elsif(lc($a->{'author'}) lt lc($b->{'author'})){
            return -1;
        }
        else{
           if(lc($a->{'title'}) gt lc($b->{'title'})){
                return 1;
            }
            elsif(lc($a->{'title'}) lt lc($b->{'title'})){
                return -1;
            }
            else{
                return 0;
            }

       }

    }
}

sub getItemISBN_libCat{
    my ($rid)=@_;
    my @isbnList=();
    my $zdbDir = 
          Opals::Context->config('zRoot') . '/'
        . Opals::Context->config('zPort') . '/'
        . 'record' . '/'
        . Opals::Context->config('zDatabase');
    my $dir = ceil($rid/1000);
    if ( -f "$zdbDir/$dir/$rid.xml") {
        my $xml;
        open MARCXML, "<$zdbDir/$dir/$rid.xml";
        while (<MARCXML>) {
            $xml .= $_;
        }
        close MARCXML;
        my $marc = Opals::Marc::Record::newFromXml($xml);
        foreach my $f020 ($marc->field('020')){
            my $tmp ="";
            my $isbn=$f020->subfield('a');

            if( $isbn ne ''){
                 $isbn =~ m/([\D^x^X]*)([\dxX][\dxX -]{8,}[\dxX])([\D^x^X]*)/;
                 $tmp = $2;
                 $tmp =~ s/[- ]//g;
                push @isbnList, {item=>$tmp};
            }
        }
        
    }
    return \@isbnList;
    
}
#===============================================================================
sub bs_getBookingByUid{
    my ($dbh,$uid)=@_;
    my @bookingInfoList=();
    my $bidList = getBidListByUid($dbh,$uid);
    foreach my $bookingId(@$bidList){
        push @bookingInfoList, bs_getBookingById($dbh,$bookingId);
    }
    return \@bookingInfoList;
}
#===============================================================================
sub getBidListByUid{
    my ($dbh,$uid)=@_;
    my @bidList=();
    my $sth =$dbh->prepare(<<_SQL_);
select  bookingId 
from opl_bookingRequest
where reqUid=? && deleted=0 && achv_id=0
_SQL_

    $sth->execute($uid);
    while(my ($bid) =$sth->fetchrow_array){
        push @bidList,$bid;
    }
    $sth->finish;
    return \@bidList;
}
#===============================================================================
sub getBidByListUserName{
    my ($dbh,$name)=@_;
    my @bookingInfoList=();
    return \@bookingInfoList;
}

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

   my $sth =$dbh->prepare(<<_SQL_);
select  * 
from opl_bookingHold
where bookingId=?  && rid= ? && deleted =0
order by bookingId, rid,barcode
_SQL_

    $sth->execute($bookingId,$rid);
    my @bcList=();
    while(my $bc =$sth->fetchrow_hashref){
        push @bcList,$bc;
    }
    $sth->finish;
    return \@bcList;


}
################################################################################
sub bs_getAvailableBarcodeList{
    my ($dbh,$rid,$itemCategory)=@_;

    my $itemTbl={1=>'opl_item',
                2=>'tbk_items',
                3=>'eq_items'
                };
   if(!defined $itemCategory || $itemCategory>3 || $itemCategory<1){
        $itemCategory=1;
   }
   my $sth =$dbh->prepare(<<_SQL_);
select  barcode 
from $itemTbl->{$itemCategory}
where rid= ? && available=1 && barcode not regexp '^\_\_\_'
_SQL_

    $sth->execute($rid);
    my @bcList=();
    while(my $bc =$sth->fetchrow_hashref){
        push @bcList,$bc;
    }
    $sth->finish;
    return \@bcList;


}


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

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

   my $sth =$dbh->prepare(<<_SQL_);
select  rid,itemCategory ,numOfCopy ,idReserve
from opl_bookingItem
where bookingId=? && deleted=0 
order by  rid
_SQL_

    $sth->execute($bookingId);
    my @itemList=();
    while(my $item =$sth->fetchrow_hashref){
        push @itemList,$item;
    }
    $sth->finish;
    return \@itemList;

}

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

sub bs_getBooking4UserList{
    my ($dbh,$bookingId)=@_;
    my $sth =$dbh->prepare(<<_SQL_);
select  u.*   
from opl_bookingFor b inner join opl_user u on u.uid=b.uid
where bookingId=? && deleted=0
_SQL_

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



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



sub bs_createBooking{
    my ($dbh,$responsible,$uid,$reqFirstname,$reqLastname,$department,$course,$section,$reserveFrom,$reserveTo) = @_;
    my $bookingId;
    my $sth= $dbh->prepare(<<_SQL_);
insert into opl_bookingRequest
    set responsible=?,
    reqUid=?,
    reqFirstname=?,
    reqLastname=?,
    department=?,
    course=?,
    section=?,
    reserveFrom=?,
    reserveTo=?,
    createdDate=now()
_SQL_
    $sth->execute($responsible,$uid,$reqFirstname,$reqLastname,$department,$course,$section,$reserveFrom,$reserveTo);
    $bookingId=$dbh->{'mysql_insertid'};
    $sth->finish;
    return $bookingId;
    
}

################################################################################
sub updateBookingRequest{
    my ($dbh,$bookingId,$uid,$reqFirstname,$reqLastname,$department,$section,$reserveFrom,$reserveTo) = @_;
    my $timeNow = date_now();

    my $sth_check= $dbh->prepare(<<_SQL_);
select count(*) as count
from opl_bookingRequest 
where    bookingId= ?  
      && reqUid   =?  
      && reqFirstname=?
      && reqLastname=?
      && department=?
      && section=?
      && reserveFrom=?
      && reserveTo=?
      && deleted=0
_SQL_

    my $sth_archive= $dbh->prepare(<<_SQL_);
insert into opl_bookingRequest(achv_id,reqUid,reqFirstname,reqLastname,department,section,reserveFrom,reserveTo,createdDate)
        select                bookingId,reqUid,reqFirstname,reqLastname,department,section,reserveFrom,reserveTo,createdDate
from opl_bookingRequest 
where bookingId= ?;
_SQL_

    my $sth_update= $dbh->prepare(<<_SQL_);
update  opl_bookingRequest
        set reqUid=?,
        reqFirstname=?,
        reqLastname=?,
        department=?,
        section=?,
        reserveFrom=?,
        reserveTo=?,
        modDate='$timeNow'
where   bookingId = $bookingId  
_SQL_

    $sth_check->execute($bookingId,$uid,$reqFirstname,$reqLastname,$department,$section,$reserveFrom,$reserveTo);
    my ($c)=$sth_check->fetchrow_array;
    if($c==0){
        #$sth_archive->execute($bookingId);
        $sth_update->execute($uid,$reqFirstname,$reqLastname,$department,$section,$reserveFrom,$reserveTo);
    }

    $sth_update->finish;
    $sth_archive->finish;
    $sth_check->finish;

}
################################################################################
sub delBookingRequest{
    my ($dbh,$bookingId) = @_;
    my $timeNow = date_now();
    my $update_sth= $dbh->prepare(<<_SQL_);
update  opl_bookingRequest 
set     deleted=1, modDate='$timeNow'
where bookingId=?
_SQL_
    $update_sth->execute($bookingId);
    $update_sth->finish;

}
################################################################################
sub bs_getBookingInfo{
    my ($dbh,$bookingId) = @_;
    my $reserveInfo={};
    my $sth= $dbh->prepare(<<_SQL_);
select * 
from  opl_bookingRequest 
where deleted=0 && bookingId=?
_SQL_

    $sth->execute($bookingId);
    if(my $rInfo = $sth->fetchrow_hashref){
        $reserveInfo = $rInfo;
    }
    $sth->finish;
    return $reserveInfo;
}

################################################################################
sub bs_addItem2Booking{
    my ($dbh,$bookingId,$rid,$itemCategory,$numOfCopy) = @_;
    my $timeNow = date_now();
    my $sth= $dbh->prepare(<<_SQL_);
insert into opl_bookingItem
set    bookingId    = $bookingId,
       rid          = $rid,
       itemCategory = $itemCategory,
       numOfCopy    = $numOfCopy,
       createdDate  ='$timeNow',
       modDate      ='$timeNow'
_SQL_
    $sth->execute();
    $sth->finish;

}
################################################################################
sub bs_setIdReserve2Booking{
    my ($dbh,$idReserve,$bookingId,$rid,$itemCategory) = @_;
    my $timeNow = date_now();
    my $sth= $dbh->prepare(<<_SQL_);
update opl_bookingItem
set    idReserve=?
where bookingId=? &&
      rid =? &&
      itemCategory=?
_SQL_
    $sth->execute($idReserve,$bookingId,$rid,$itemCategory);
    $sth->finish;

}

################################################################################
sub bs_updateBookingItemList{
    my ($dbh,$bookingId,$itemList) = @_;
#          $itemList = array of {rid=>?,itemCategory=?,numOfCopy=>?} 
#
    my $timeNow = date_now();
    my $sth=$dbh->prepare(<<_SQL_);
select * from opl_bookingItem
where bookingId =? && rid=? && itemCategory=? 
_SQL_

    my $sth_update=$dbh->prepare(<<_SQL_);
update opl_bookingItem
set bookingId= ?,deleted=? ,modDate=? ,numOfCopy=?
where bookingId =? && rid=? && itemCategory=?
_SQL_

   my $sth_markDel=$dbh->prepare(<<_SQL_);
update opl_bookingItem
set deleted=1 
where bookingId =? 
_SQL_

   my $sth_del=$dbh->prepare(<<_SQL_);
update opl_bookingItem
set   bookingId= -1*abs(bookingId),
      modDate=?
where bookingId =? && deleted=1
_SQL_

    $sth_markDel->execute($bookingId);
    foreach my $item(@$itemList){
        $sth->execute($bookingId,$item->{'rid'},$item->{'itemCategory'});
        if(my $rec=$sth->fetchrow_hashref){
            $sth_update->execute($bookingId,0,$rec->{'modDate'},$item->{'numOfCopy'},$bookingId,$item->{'rid'},$item->{'itemCategory'});
        }
        else{
            $sth_update->execute($bookingId*-1,1,$timeNow,$bookingId,$item->{'rid'},$item->{'itemCategory'});
            bs_addItem2Booking($dbh,$bookingId,$item->{'rid'},$item->{'itemCategory'},$item->{'numOfCopy'});
        }

    }
    $sth_del->execute($timeNow,$bookingId);
    $sth->finish;
    $sth_update->finish;
    $sth_markDel->finish;
    $sth_del->finish;
}

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

sub bs_delItemFromBooking{
    my ($dbh,$bookingId,$rid,$itemCategory) = @_;
    my $timeNow = date_now();
    my $update_sth= $dbh->prepare(<<_SQL_);
update opl_bookingItem
set deleted=1 , bookingId= abs(bookingId) * -1,modDate=?
where bookingId=? && rid=? && itemCategory=?
_SQL_
    $update_sth->execute($timeNow,$bookingId,$rid,$itemCategory);
    $update_sth->finish;
}
################################################################################

sub bs_delItemFromBooking_all{
    my ($dbh,$bookingId) = @_;
    my $timeNow = date_now();
    my $update_sth= $dbh->prepare(<<_SQL_);
update opl_bookingItem
set deleted=1 , bookingId= abs(bookingId) * -1,modDate=?
where bookingId=? 
_SQL_
    $update_sth->execute($timeNow,$bookingId);
    $update_sth->finish;
}

################################################################################
sub bs_addUser2Booking{
    my ($dbh,$bookingId,$uid) = @_;
    my $timeNow = date_now();

    my $sth=$dbh->prepare(<<_SQL_);
insert into opl_bookingFor
set    bookingId=?,uid=?,
       createdDate='$timeNow', modDate='$timeNow'

_SQL_

    $sth->execute($bookingId,$uid);
    $sth->finish;
    
}
################################################################################
sub bs_updateBooking4UserList{
    my ($dbh,$bookingId,$uidList) = @_;
#   uidList+ array of uid;   
#
    my $timeNow = date_now();

    my $sth_markDel=$dbh->prepare(<<_SQL_);
update opl_bookingFor
set deleted= 1
where bookingId =? 
_SQL_

   my $sth_undel=$dbh->prepare(<<_SQL_);
update opl_bookingFor
set  deleted= 0 
where bookingId =? && uid=?
_SQL_

    my $sth=$dbh->prepare(<<_SQL_);
select * from opl_bookingFor
where bookingId =? &&  uid=?
_SQL_
        

    my $sth_del=$dbh->prepare(<<_SQL_);
update opl_bookingFor
set   bookingId= -1*abs(bookingId),
      modDate=?
where bookingId =? && deleted=1
_SQL_

    $sth_markDel->execute($bookingId);
    foreach my $uid(@$uidList){
        $sth->execute($bookingId,$uid);
        if(my $rec=$sth->fetchrow_hashref){
            $sth_undel->execute($bookingId,$uid);
        }
        else{
            bs_addUser2Booking($dbh,$bookingId,$uid);
        }
    }
    $sth_del->execute($timeNow,$bookingId);
    
}

################################################################################
sub bs_delUserfromBooking{
    my ($dbh,$bookingId,$uid) = @_;
    my $timeNow = date_now();

    my $sth=$dbh->prepare(<<_SQL_);
update opl_bookingFor
set   bookingId=-1*abs(bookingId), deleted=1,modDate=?
where bookingId=? && uid=?
_SQL_

    $sth->execute($timeNow,$bookingId,$uid);
    $sth->finish;
    
}
################################################################################
sub bs_delUserfromBooking_all{
    my ($dbh,$bookingId) = @_;
    my $timeNow = date_now();

    my $sth=$dbh->prepare(<<_SQL_);
update opl_bookingFor
set   bookingId=-1*abs(bookingId), deleted=1,modDate=?
where bookingId=?
_SQL_

    $sth->execute($timeNow,$bookingId);
    $sth->finish;
    
}

################################################################################
sub bs_isRecBooked{
    my ($dbh,$rid,$itemCategory,$from,$to)=@_;
    my $numCopyBooked=bs_numCopyBooked($dbh,$rid,$itemCategory,$from,$to);
    my $isBooked= $numCopyBooked>0?1:0;

    return $isBooked;
  
}

################################################################################
sub bs_numCopyBookedByBid{
    my ($dbh,$bookingId)=@_;
    my $totalRequest=0;
    my $sth=$dbh->prepare(<<_SQL_);
     select sum(i.numOfCopy) count from opl_bookingItem i where bookingId=?  group by bookingId
_SQL_
    $sth->execute($bookingId);
    if(my $rec=$sth->fetchrow_hashref){
        $totalRequest=$rec->{'count'} ;
    }
    $sth->finish;

    return $totalRequest;
}

###############################################################################
sub bs_isBcHold_bk{
    my ($dbh,$bc,$itemCategory,$from,$to)=@_;
    my $isHoldFor=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
  
    my $sth=$dbh->prepare(<<_SQL_);
    select  h.bookingId 
    from    opl_bookingRequest b inner join opl_bookingHold h  using(bookingId)
    where   b.deleted=0 && h.barcode=? && h.itemCategory=? 
        && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )

_SQL_
    $sth->execute($bc,$itemCategory);
   
    if(my $rec=$sth->fetchrow_hashref){
        $isHoldFor=$rec->{'bookingId'} ;
    }
    $sth->finish;

    return $isHoldFor;
  
}
###############################################################################
sub bs_isBcHold4Uid_bk{
    my ($dbh,$uid,$bc,$itemCategory,$from,$to)=@_;
    my $isHoldFor=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
  
    my $sth=$dbh->prepare(<<_SQL_);
    select  h.bookingId 
    from    opl_bookingRequest b inner join opl_bookingFor f using(bookingId)
            inner join opl_bookingHold h  using(bookingId)
    where   b.deleted=0 && h.barcode=? && h.itemCategory=? && f.uid =? 
        && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )

_SQL_
    $sth->execute($bc,$itemCategory,$uid);
   
    if(my $rec=$sth->fetchrow_hashref){
        $isHoldFor=$rec->{'bookingId'} ;
    }
    $sth->finish;

    return $isHoldFor;
  
}
###############################################################################
sub bs_isItemLoan2Uid_bk{
    my ($dbh,$uid,$rid,$itemCategory,$from,$to)=@_;
    my $isLoanTo=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
  
    my $sth=$dbh->prepare(<<_SQL_);
    select  h.bookingId 
    from    opl_bookingRequest b inner join opl_bookingFor f using(bookingId)
            inner join opl_bookingHold h  using(bookingId)
    where   b.deleted=0 && h.rid=? && h.itemCategory=? && f.uid =? && idloan>0 
        && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )

_SQL_
    $sth->execute($rid,$itemCategory,$uid);
   
    if(my $rec=$sth->fetchrow_hashref){
        $isLoanTo=$rec->{'bookingId'} ;
    }
    $sth->finish;

    return $isLoanTo;
  
}


###############################################################################
sub bs_isItemBooked4Uid{
    my ($dbh,$uid,$rid,$itemCategory,$from,$to)=@_;
    my $isBook4Uid=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
  
    my $sth=$dbh->prepare(<<_SQL_);
    select  i.bookingId 
    from    opl_bookingRequest b inner join opl_bookingFor f using(bookingId)
            inner join opl_bookingItem i using(bookingId)
    where   b.deleted=0 && i.rid=? && i.itemCategory=? && f.uid =? 
        && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )

_SQL_

    $sth->execute($rid,$itemCategory,$uid);
   
    if(my $rec=$sth->fetchrow_hashref){
        $isBook4Uid=$rec->{'bookingId'} ;
    }
    $sth->finish;

    return $isBook4Uid;
  
}

################################################################################
sub bs_numCopyBooked{
    my ($dbh,$rid,$itemCategory,$from,$to)=@_;
    my $numCopyBooked=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
    my $sth=$dbh->prepare(<<_SQL_);
select sum(numOfCopy)  count from  opl_bookingRequest b inner join opl_bookingItem i on i.bookingId=b.bookingId    
where   b.deleted=0 && i.rid=? && i.itemCategory=? 
        && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )

_SQL_
 
    $sth->execute($rid,$itemCategory);
    if(my $c=$sth->fetchrow_hashref){
        if($c->{'count'}){
            $numCopyBooked= $c->{'count'};
        }
    }
    $sth->finish;
    return $numCopyBooked;

}
################################################################################
sub bs_getBooked4UidList{
    my ($dbh,$rid,$itemCategory,$from,$to)=@_;
    my @uidList=();
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
 my $sth=$dbh->prepare(<<_SQL_);
select  distinct b.reqUid,u.uid
from opl_bookingRequest b inner join opl_bookingItem i using(bookingId) 
    left outer join opl_bookingFor u using(bookingId)
where b.deleted=0 && i.rid=? && i.itemCategory=?  
      && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )
_SQL_

    $sth->execute($rid,$itemCategory);
    my  $tmp={};
    while(my $rec=$sth->fetchrow_hashref){
        if(!$rec->{'uid'}){
            $tmp->{$rec->{'reqUid'}}=1;
        }
        else{
            $tmp->{$rec->{'uid'}}=1;
        }
    }
    foreach my $uid(sort keys(%$tmp)){
        push @uidList,$uid;
    }
    $sth->finish;
    return \@uidList;
}
################################################################################
#
#  This function will check if a given barcode is booked.
#  if the given barcode exits in more than 1 collections, it will return 0; In such
#  case use bs_isItemBooked_cat.
#
sub bs_isItemBooked{
    my ($dbh,$bc,$from,$to)=@_;
    my $isBooked=0;
    my $ridCat =getRidCategory($dbh,$bc);
    if(scalar(@$ridCat)==1){
        $isBooked=bs_isRecBooked($dbh,@$ridCat[0]->{'rid'},@$ridCat[0]->{'itemcategory'},$from,$to);
    }
    return $isBooked;
}
################################################################################
sub bs_isItemBooked_cat{
    my ($dbh,$bc,$itemCategory,$from,$to)=@_;
    my $isBooked=0;
    my $itemTbl ="opl_item";
    if($itemCategory && $itemCategory==2){
        $itemTbl ="tbk_items";
    }
    elsif($itemCategory && $itemCategory==3){
        $itemTbl ="eq_items";
    }
    my ($rid) = $dbh->selectrow_array("select rid from $itemTbl where barcode ='$bc'");
    if($rid){
        $isBooked=bs_isRecBooked($dbh,$rid,$itemCategory,$from,$to);
    }
    return $isBooked;
  
}
################################################################################
sub getRidCategory{
    my ($dbh,$bc)=@_;
    my @catList=();
    my $catTbl={'opl_item'=>1,
                'tbk_items'=>2,
                'eq_items'=>3
                };
 
    foreach my $tbl (keys %$catTbl){
        my ($rid) = $dbh->selectrow_array("select rid from $tbl where barcode ='$bc'");
        if($rid){
            push @catList,{rid=>$rid,itemcategory=>$catTbl->{$tbl}};
        }

    }
    return \@catList;
}

################################################################################
sub bs_getBooking4ByUid{
    my ($dbh,$uid)=@_;
    my @bookingIdList=();
    my $timeNow=date_now();
    my $sth=$dbh->prepare(<<_SQL_);
select  distinct b.bookingId from opl_bookingRequest b inner join opl_bookingFor f on b.bookingId=f.bookingId
where   b.deleted =0 && (b.reserveFrom >? || b.reserveTo >?)
_SQL_
    $sth->execute($timeNow,$timeNow);
    while(my ($bid)=$sth->fetchrow_array){
        push @bookingIdList,$bid;
    }
    $sth->finish;
    return \@bookingIdList;
}

################################################################################
sub bs_getBookingStatus{
    my ($dbh,$bookingId)=@_;
    my ($staus) =$dbh->selectrow_array("select status from opl_bookingRequest where bookingId=$bookingId");
    return $staus;
}

################################################################################
sub alignBookingStatus_bk{
    my ($dbh,$bookingId)=@_;
    my $bItemList =bs_getBooikngItemList($dbh,$bookingId);
    my ($status)=$dbh->selectrow_array("select status from opl_bookingRequest 
                                        where bookingId=$bookingId && deleted=0 && status<>'cancel'");
    my ($coundHold,$countLoan)=(0,0);

    if($status eq 'waiting' || $status eq 'processing' || $status eq 'ready' ){
        foreach my $item(@$bItemList){
            if(isBookingItemReady($dbh,$bookingId,$item->{'rid'},$item->{'itemCategory'})){
                $coundHold++;
            }
            if(isBookingItemLoan_bk($dbh,$bookingId,$item->{'rid'},$item->{'itemCategory'})){
                $countLoan++;
            }
        }
    }

    if($countLoan == scalar @$bItemList){
        $dbh->do("update opl_bookingRequest set status='filled' where bookingId = $bookingId");
    }
    elsif($coundHold == scalar @$bItemList){
        $dbh->do("update opl_bookingRequest set status='ready' where bookingId = $bookingId");

    }
    
}
################################################################################
sub isBookingItemReady_bk{
    my ($dbh,$bookingId,$rid,$itemCategory)=@_;
    my $retVal=0;
    my $sth=$dbh->prepare(<<_SQL_);
select  numOfCopy,count(*)
from    opl_bookingItem i inner join opl_bookingHold h using(bookingId,rid ,itemCategory)
where   i.deleted=0 && 
        h.deleted=0 &&
        bookingId= ? &&
        rid=? &&
        itemCategory=?
_SQL_
    
    $sth->execute($bookingId,$rid,$itemCategory);
    if(my($numResCopy,$numHold)=$sth->fetchrow_array){
        if($numResCopy && $numResCopy == $numHold){
            $retVal=1;
        }
    }
    $sth->finish;
    return $retVal;
    
}
################################################################################
sub isBookingItemLoan_bk{
    my ($dbh,$bookingId,$rid,$itemCategory)=@_;
    my $retVal=0;
    my $sth=$dbh->prepare(<<_SQL_);
select  numOfCopy,count(*)
from    opl_bookingItem i inner join opl_bookingHold h using(bookingId,rid ,itemCategory)
where   i.deleted=0 && 
        h.deleted=0 &&
        bookingId= ? &&
        rid=? &&
        itemCategory=?  &&
        idloan > 0 
_SQL_
    
    $sth->execute($bookingId,$rid,$itemCategory);
    if(my($numResCopy,$numHold)=$sth->fetchrow_array){
        if($numResCopy != 'NULL' && ($numResCopy == $numHold)){
            $retVal=1;
        }
    }
    $sth->finish;
    return $retVal;
    
}
################################################################################
sub bs_isBItemLoan2Uid_bk{
    my ($dbh,$bookingId,$rid,$itemCategory,$uid)=@_;

    my $retVal=0;
    my $sth=$dbh->prepare(<<_SQL_);
    select l.idloan from opl_bookingHold h inner join opl_loan l using(idloan)
    where  h.bookingId= ? && h.rid=? && h.itemCategory=? && l.uid=?
_SQL_
    
    $sth->execute($bookingId,$rid,$itemCategory,$uid);
    if(my ($id)=$sth->fetchrow_array){
        $retVal=1;
    }
    return $retVal; 
}
################################################################################
sub bs_numBItemLoan{
    my ($dbh,$bookingId,$rid,$itemCategory)=@_;
    my $retVal=0;
    my $sth=$dbh->prepare(<<_SQL_);
    select  count(*) 
    from    opl_bookingItem b inner join opl_hold h using(idReserve) 
            inner join opl_loan l using(idloan)
    where   b.bookingId=? && b.rid=? && b.itemCategory=?  && 
            h.idloan <>'' && h.idloan is not null ; 
_SQL_
    
    $sth->execute($bookingId,$rid,$itemCategory);
    if(my($n)=$sth->fetchrow_array){
        $retVal=$n;
    }
    $sth->finish;
    return $retVal;
    
}

################################################################################
sub bs_numBItemLoan_bk{
    my ($dbh,$rid,$itemCategory,$from,$to)=@_;
    my $retVal=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }

    my $sth=$dbh->prepare(<<_SQL_);
    select count(*) 
    from opl_bookingHold h inner join opl_bookingRequest b using(bookingId)
    where  b.deleted=0 && h.rid=? && h.itemCategory=? && h.idloan>0
      && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )
    
_SQL_
    
    $sth->execute($rid,$itemCategory);
    if(my($n)=$sth->fetchrow_array){
        $retVal=$n;
    }
    $sth->finish;
    return $retVal;
    
}
################################################################################
sub bs_numBItemPending_bk{
    my ($dbh,$rid,$itemCategory,$from,$to)=@_;
    my $nPending=bs_numCopyBooked($dbh,$rid,$itemCategory,$from,$to);
    if($nPending>0){
        $nPending -=  bs_numBItemLoan_bk($dbh,$rid,$itemCategory,$from,$to)
                    + bs_numBItemHold_bk($dbh,$rid,$itemCategory,$from,$to);
    }
    return  $nPending;
}

################################################################################
sub bs_numBItemHold_bk{
    my ($dbh,$rid,$itemCategory,$from,$to)=@_;
    my $retVal=0;
    if(!defined $from || $from eq ''){
        $from=date_now();
    }
    if(!defined $to && $to eq ''){
        $to='2200-12-31 00:00:00';
    }
    my $sth=$dbh->prepare(<<_SQL_);
    select count(*) 
    from opl_bookingHold  h inner join opl_bookingRequest b using(bookingId)
    where b.deleted=0 && h.rid=? && h.itemCategory=? && h.idloan=0
        && (  (b.reserveFrom <='$from' && b.reserveTo >='$to')   
            ||(b.reserveFrom >='$from' && b.reserveTo <='$to')
            ||(b.reserveFrom >='$from' && b.reserveFrom <='$to' && b.reserveTo >='$to')
            ||(b.reserveFrom <='$from' && b.reserveTo >='$from' && b.reserveTo <='$to')
           )
_SQL_
    
    $sth->execute($rid,$itemCategory);
    if(my($n)=$sth->fetchrow_array){
        $retVal=$n;
    }
    $sth->finish;
    return $retVal;
    
}

################################################################################
#   sub bs_fill
#        
#       
#
################################################################################
sub bs_fill_bk{
    my ($dbh,$idloan,$bookingId,$bc,$rid,$itemCategory)=@_;
    my $sth=$dbh->prepare(<<_SQL_);
update  opl_bookingHold 
set     idloan      = ?
where   deleted     = 0 && 
        bookingId   = ? &&
        barcode     = ? &&
        idloan      = 0 &&
        rid         = ? &&
        itemCategory =? 
_SQL_

    $sth->execute($idloan,$bookingId,$bc,$rid,$itemCategory);
    $sth->finish;
    alignBookingStatus_bk($dbh,$bookingId);

}
################################################################################
#   sub bs_placeHold
#       Mark item  on hold so it should not be loan to patron that  not in the 
#       booking list
#
################################################################################
sub bs_placeHold_bk{
    my ($dbh,$bookingId,$bc,$rid,$itemCategory)=@_;
    my $timeNow=date_now();

    if(!$bookingId){
        $bookingId= bs_getBookingId4hold_bk($dbh,$rid,$itemCategory);
    }
        return 0 if($bookingId==0);
    
    my $sth=$dbh->prepare(<<_SQL_);
insert into opl_bookingHold
set bookingId   = ?,
    barcode     = ?,
    rid         = ?,
    itemCategory= ?,
    holdDate    = ?
_SQL_
    my $hold = $sth->execute($bookingId,$bc,$rid,$itemCategory,$timeNow);
    $sth->finish;
    alignBookingStatus_bk($dbh,$bookingId);
    return $hold eq '0E0'?0:$bookingId;

}
################################################################################
sub bs_getBookingId4hold_bk{
    my ($dbh,$rid,$itemCategory)=@_;
    my $retVal=0;
    my $sth=$dbh->prepare(<<_SQL_);
    select r.bookingId,numOfCopy 
    from opl_bookingRequest r inner join opl_bookingItem i using(bookingId) 
    where  i.rid=? && i.itemCategory=?  order by reserveFrom,bookingId
_SQL_
    $sth->execute($rid,$itemCategory);
    while(my ($bookingId,$numOfCopy)=$sth->fetchrow_array){
        if(getTotalHoldByBidRid_bk($dbh,$bookingId,$rid,$itemCategory)<$numOfCopy){
            $retVal=$bookingId;
            last;
        }
    }
    return $retVal;
}
################################################################################
sub getTotalHoldByBidRid_bk{
   my ($dbh,$bookingId,$rid,$itemCategory)=@_;
   my $totalHold=0;
   my $sth=$dbh->prepare(<<_SQL_);
    select count(*) 
    from opl_bookingHold 
    where  bookingId=? && rid=? && itemCategory =?
_SQL_

    $sth->execute($bookingId,$rid,$itemCategory);
    if(my ($n)=$sth->fetchrow_array){
        $totalHold=$n;
    }
    return $totalHold;
}
################################################################################
#   sub bs_removeDelBarcodeHold
#       remove barcode from the hold list 
#       
################################################################################
sub bs_removeDelBarcodeHold_bk{ 
    my ($dbh,$bookingId)=@_;
    my $timeNow=date_now();
    my $sth=$dbh->prepare(<<_SQL_);
delete from opl_bookingHold
where   bookingId   = ? &&
        deleted     = 1 
        
_SQL_

    $sth->execute($bookingId);
    $sth->finish;
    alignBookingStatus_bk($dbh,$bookingId);
}


################################################################################
#   sub bs_markDelBarcodeHold
#       mark barcode is deleted  
#       
################################################################################
sub bs_markDelBarcodeHold_bk{ 
    my ($dbh,$bookingId,$status)=@_;
    my $timeNow=date_now();
    my $sth=$dbh->prepare(<<_SQL_);
update  opl_bookingHold
set     deleted     = ?
where   bookingId   = ? 
_SQL_

    $sth->execute($status,$bookingId);
    $sth->finish;
   ##Ha alignBookingStatus_bk($dbh,$bookingId);
}


#################################################################################
#  sub bs_getGrpBookingList
#
#    filter: a map of following keys
#        
#################################################################################
sub bs_getBookingListByGrp{
    my($dbh,$filter,$pNum,$size,$sort,$sortDir) =@_;
    my @bookingList=();
    my $sqlCond="";
    my $offset = $pNum;
    if ( !$offset ) {
        $offset = 0;
    }
    else {
        $offset = ($pNum - 1) * $size;
    }

    my $from =date_now();
    my $to='2200-12-31 00:00:00';

    if(defined $filter->{'dateFrom'} && isValidDateStr($filter->{'dateFrom'})){
        $from= $filter->{'dateFrom'};
    }
    if(defined $filter->{'dateTo'} && isValidDateStr($filter->{'dateTo'})){
        $to= $filter->{'dateTo'};
    }


    $sqlCond .="( (r.reserveFrom <='$from' && r.reserveTo >='$to')   
                ||(r.reserveFrom >='$from' && r.reserveTo <='$to')
                ||(r.reserveFrom >='$from' && r.reserveFrom <='$to' && r.reserveTo >='$to')
                ||(r.reserveFrom <='$from' && r.reserveTo >='$from' && r.reserveTo <='$to')
               ) ";
     $sqlCond .=" &&  ((f.bookingId is not null ";          
    if(defined $filter->{'bookingForUid'} ){
        $sqlCond .= " && f.uid=" . $filter->{'bookingForUid'};
    } 
    $sqlCond .=" )";      
    if(defined $filter->{'requestUid'}){
        $sqlCond .= " || r.reqUid=" . $filter->{'requestUid'};
    }
    $sqlCond .=" )";   
       
    if(defined $filter->{'status'}){
        $sqlCond .= " &&  status in ('";
        my $str .= join ("','",@{$filter->{'status'}}) ."')";
        #my $str = join(" || status='",@{$filter->{'status'}});
        $sqlCond .= $str;

    }
    
    my $sql ="select distinct r.* from opl_bookingRequest r left outer join opl_bookingFor f using(bookingId)";
       $sql .=" where $sqlCond" if($sqlCond ne "");

    my $sql_count ="select count(distinct r.bookingId) from opl_bookingRequest r left outer join opl_bookingFor f using(bookingId)";
       $sql_count .=" where $sqlCond" if($sqlCond ne "");


    if(defined $sort && defined $sortDir){
        $sql .=" order by $sort $sortDir ";
    }

    if(defined $offset && defined $size){
        $sql .=" limit $offset,$size";
    }
    my $sth=$dbh->prepare($sql_count);
    $sth->execute();
        my $count=0;
    if( my ($c)=$sth->fetchrow_array){
        $count =$c;
    }
    if($count >0){
       $sth=$dbh->prepare($sql);
       $sth->execute();
        while(my $booking=$sth->fetchrow_hashref){
            push @bookingList,$booking;
        }
    }

    return ($count,\@bookingList);

}



#################################################################################
#  sub bs_getBookingList
#
#    filter: a map of following keys
#            dateFrom
#            dateTo
#            teacher
#            course:
#            status : array of status(ready,waiting,processing,cancel); if empty then get all.
#            requestUid
#
#      e.g  {dateForm=>'2010-03-09',dateTo=>'2010-03-15',teacher=>'John William' }
#        
#################################################################################
sub bs_getBookingList{
    my($dbh,$filter,$pNum,$size,$sort,$sortDir) =@_;
    my @bookingList=();
    my $sqlCond="";
    
    my $offset = $pNum;
    if ( !$offset ) {
        $offset = 0;
    }
    else {
        $offset = ($pNum - 1) * $size;
    }

    if(defined $filter->{'dateFrom'} && isValidDateStr($filter->{'dateFrom'})){
        $sqlCond .=" reserveFrom>='" . $filter->{'dateFrom'} ."' ";
        if(defined $filter->{'dateTo'} && isValidDateStr($filter->{'dateTo'})){
            $sqlCond .=" && reserveTo <='" . $filter->{'dateTo'} ."' ";
        }
    }

    if(defined $filter->{'teacher'} || defined $filter->{'name'} ){
        my $name =defined $filter->{'teacher'}? $filter->{'teacher'}:$filter->{'name'};
        $name =~ s/ +/ /g;
        $name =~ s/,/ /g; 
        $name =~ s/'/\\'/g;
        my @data = split(' ', $name);
        foreach my $i (@data) {
            if($i =~ m/\w+/g){
                $sqlCond .=" && " if($sqlCond ne "");
                $sqlCond .= " (reqLastname regexp '^$i' or reqLastname regexp '[^[:alnum:]]+$i' or 
                               reqFirstname regexp '^$i' or reqFirstname regexp '[^[:alnum:]]+$i' ) ";
           }
        }

    }
    if(defined $filter->{'course'}){
        $sqlCond .=" && " if($sqlCond ne "");
        $sqlCond .= " course ='" . $filter->{'course'} ."' ";
    }
    if(defined $filter->{'status'}){
        $sqlCond .=" && " if($sqlCond ne "");
        $sqlCond .= " status in ('";
        my $str .= join ("','",@{$filter->{'status'}}) ."')";
        #my $str = join(" || status='",@{$filter->{'status'}});
        $sqlCond .= $str;

    }
    if(defined $filter->{'requestUid'}){
        $sqlCond .=" && " if($sqlCond ne "");
        $sqlCond .= " reqUid=" . $filter->{'requestUid'};

    }


    my $sql ="select * from opl_bookingRequest ";
    $sql .=" where $sqlCond" if($sqlCond ne "");

    my $sql_count ="select count(*) from opl_bookingRequest ";
       $sql_count .=" where $sqlCond" if($sqlCond ne "");
 
 
    if(defined $sort && defined $sortDir){
        $sql .=" order by $sort $sortDir ";
    }

    if(defined $offset && defined $size){
        $sql .=" limit $offset,$size";
    }
    
    my $sth=$dbh->prepare($sql_count);
    $sth->execute();
    
    my $count=0;
    if( my ($c)=$sth->fetchrow_array){
        $count =$c;
    }
    if($count >0){
        my $sth=$dbh->prepare($sql);
        $sth->execute();

        while(my $booking=$sth->fetchrow_hashref){
            $booking->{'totalRequest'} = bs_numCopyBookedByBid($dbh,$booking->{'bookingId'});
            push @bookingList,$booking;
        }
    }

    return ($count,\@bookingList);

}

################################################################################
sub isValidDateStr{
    my ($dateStr)=@_;
    return ($dateStr =~ m/\d{4}-\d{1,2}-\d{1,2}/g);
}

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

1;