package Opals::Inventory;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    inv_inventory
    inv_closeInventory
    inv_getInventoryInfo
    inv_cleanupInventory
    inv_getInventory
    inv_deleteInventory
    inv_getInventoryByStatus
    inv_getInventoryStatusByBc
    inv_mark_Returned
);
#    mxml_validateRid
# Version number
$VERSION   = 0.01;


#use utf8;
use strict;
use POSIX qw(
    ceil
);
use Opals::Context;
use Opals::Constant;
use Opals::Date qw(
    date_f005
);
use Opals::CallNumberUtil qw(
    cn_getCallNumSortByLcc
    cn_getCallNumSortByDewey
);
use Opals::User qw(
    user_currentUser
    user_getInformationById
);
#Tue, May 13, 2014 @ 09:48:36 EDT
# NOTE inventory status "excluded" means items that are not in the selected
#      record format/itemType and should not listed as missing
#
my $classificationSystem=Opals::Context->preference('classificationSystem');
############################################################
sub getSortCallnumber{
    my ($callNum)=@_;
    my $retval=$callNum;
    if($classificationSystem eq 'LCC'){
        $retval= cn_getCallNumSortByLcc($callNum);
    }
    else{
        $retval= cn_getCallNumSortByDewey($callNum);
    }
    $retval =~ s/^\s+//g;
    return $retval;
}
############################################################
sub preInventory{
    my($dbh,$uid,$invId) = @_;
   my $sth_del = $dbh->prepare(<<_STH_);
delete  from  opl_inventory 
where   fid      = 0 &&
        (status   ='missing' || status   ='excluded' )&&
        invId    = ?
            
_STH_
       $sth_del->execute($invId);

    my $sth = $dbh->prepare(<<_STH_);
update  opl_inventory 
set     status     = 'active', 
        noteDetail = null 
where   invId      = ?
            
_STH_

       $sth->execute($invId);
      
       $sth->finish;
       $sth_del->finish;
}


#####################################################
sub inventory {
    my ($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType,$location) = @_;
   
  # CASE INVALID 
    my $sth_invalid = $dbh->prepare(<<_STH_);
update  opl_inventory as a left outer join opl_item as b on a.barcode= b.barcode 
        set     a.status   =  'invalid'	
where   b.barcode    is null && a.invId = ?
_STH_
   $sth_invalid->execute($invId);
    
  # CASE No call Number:  
    my $sth_noCallNo = $dbh->prepare(<<_STH_);
update opl_inventory 
set status =  CONCAT_WS(',', status, 'noCallNumber') 
where callNumber = '' &&
      status not regExp 'invalid' &&
      invId      = ?
_STH_
   $sth_noCallNo->execute($invId);
  
  # CASE Missing  
  
  inventory_missing($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType,$location);
  inventory_excluded($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType);
  # CASE loan but shelf
  my $sth_loan = $dbh->prepare(<<_STH_);
update   opl_inventory as a inner join opl_loan as l on a.barcode= l.barcode
set      a.status    = CONCAT_WS(',', a.status, 'loan'),
         noteDetail  = CONCAT_WS(',',noteDetail,CONCAT('idloan:',l.idloan) )
where    l.dateReturn  is null   &&
         a.status not regExp 'missing|excluded'&&
         a.invId     = ?
_STH_
   $sth_loan->execute($invId);


  # CASE Found #####invId ??? missing/lost/claimed return/claimed never borrow but shelved
  my $sth_found = $dbh->prepare(<<_STH_);
update  opl_inventory as a inner join opl_itemstatus as b on a.barcode= b.barcode
        left outer join opl_itemstatus c on b.barcode=c.barcode && b.id  < c.id 
set     a.status = CONCAT_WS(',' , a.status, 'found')	
where 	a.callNumber >= ? && 
        a.callNumber <= ? && 
        a.status not regExp 'missing'&&
        c.id  is null &&
        (b.status     =  3 || b.status  = 0 ||  b.status  = 11 || b.status  = 12) &&
        invId        = ?
_STH_
   $sth_found->execute($callNoB,$callNoE,$invId);


  # CASE  Damage 
  my $sth_damage = $dbh->prepare(<<_STH_);
update  opl_inventory as a inner join opl_itemstatus as b on a.barcode= b.barcode
        left outer join opl_itemstatus c on b.barcode=c.barcode && b.id  < c.id 
set     a.status = CONCAT_WS(',' , a.status, 'damaged')	
where 	a.callNumber >= ? && 
        a.callNumber <= ? && 
        a.status not regExp 'missing' &&
        c.id  is null &&
        b.status     = 2  &&
        invId        = ?
_STH_
   $sth_damage->execute($callNoB,$callNoE,$invId);


  # CASE MisShelves 
    inventory_misplace($dbh,$invId,$callNoB,$callNoE ,$location); 
    #updateMisplaceBy($dbh,$invId); 
    if(defined $location && $location ne ''){
        invMisLocation($dbh,$invId,$location);
    }
  # remove active status if item is loan or misplaced...
  my $sth_update = $dbh->prepare(<<_STH_);
update   opl_inventory
set      status = replace(status,'active,','')
where    invId  = ?  
_STH_
   $sth_update->execute($invId);

  # find and update reference position if status is loan/damaged/found/invalid
   updateRefPos($dbh,$invId);


    $sth_noCallNo->finish;
    $sth_loan->finish;
    $sth_found->finish;
    $sth_damage->finish;
    
    $sth_update->finish;

    
}
############################################################
sub inventory_missing{
    my ($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType,$location) = @_;
    my @loanMissing=();
    my @missing=();
    my $invRecTypeTbl=undef;
    my $invItemTypeTbl=undef;

    foreach my $rt(split(",",$invRecType)){
        $invRecTypeTbl->{$rt}=1;
    }
   foreach my $it(split(",",$invItemType)){
        $invItemTypeTbl->{$it}=1;
    }

    my($callNumSortB,$callNumSortE);
    if(!defined $location || $location eq ""){
        $callNumSortB=$dbh->selectrow_array("select callNumSort from opl_item 
                                             where callNumber >= '$callNoB' && 
                                                   barcode not regexp '^___' order by callNumSort  limit 1");
        $callNumSortE=$dbh->selectrow_array("select callNumSort from opl_item 
                                             where callNumber <= '$callNoE'  && 
                                                   barcode not regexp '^___' order by callNumSort desc limit 1");
    }
    else{
        $callNumSortB=$dbh->selectrow_array("select callNumSort from opl_item 
                                             where callNumber >= '$callNoB' && location ='$location' && 
                                                   barcode not regexp '^___' order by callNumSort  limit 1");
        $callNumSortE=$dbh->selectrow_array("select callNumSort from opl_item 
                                             where callNumber <= '$callNoE' && location ='$location' && 
                                                   barcode not regexp '^___' order by callNumSort desc limit 1");

    }

  
#get items on loan or items status= missing or lost/damaged
    my $sth_onloan_missing= $dbh->prepare(<<_STH_);
select  ucase(barcode) as barcode from opl_loan where dateReturn is null 
union 
select distinct ucase(barcode) as barcode from opl_item where available=0 && barcode not regexp '^\_\_\_'
order by barcode     
_STH_

    $sth_onloan_missing->execute;
   while (my ($bc) = $sth_onloan_missing->fetchrow_array()){
         push @loanMissing,$bc;
   }
   $sth_onloan_missing->finish;
   @loanMissing=sort(@loanMissing);
 
# get possible missing barcodes
my $sql="select distinct ucase(i.barcode) as barcode ,i.typeId,i.callNumber,i.callNumSort,if(i.location is not null,i.location,'') location, r.recFormat 
from opl_item i inner join opl_marcRecord r using(rid)
     left outer join opl_inventory v on i.barcode=v.barcode && v.invId=$invId 
where i.barcode not regexp '^___' && v.barcode is null 
      && i.callNumSort >= ? &&         i.callNumSort <= ?
";
    if(defined $location){
        if($location ne ''){
            $sql .=" && i.location = '$location' ";
        }
        else{
            $sql .=" && (i.location = '' || i.location is null)";
        }
    }   

my $invRecTypeCond="";
    if($invRecType ne ''){
        $invRecTypeCond=$invRecType;
        $invRecTypeCond =~ s/,/','/g;
        $invRecTypeCond = " r.recFormat in ('$invRecTypeCond')";
    }

    my $invItemTypeCond="";
    if($invItemType ne ''){
        $invItemTypeCond=$invItemType;
        $invItemTypeCond =~ s/,/','/g;
        $invItemTypeCond = " i.typeId in ('$invItemTypeCond')";
    }
    my $filterCond ="";
    if($invRecTypeCond ne ''){
        $filterCond=$invRecTypeCond;
    }
    if( $invItemTypeCond ne ''){
        $filterCond .= " and " if($filterCond ne '');
        $filterCond .= $invItemTypeCond
    }
    if($filterCond  ne ''){
        $sql .= " and ($filterCond)";
    }

    $sql .= "   order by i.barcode";
  my $sth= $dbh->prepare($sql);
    $sth->execute($callNumSortB,$callNumSortE);
    my $n=scalar(@loanMissing);
    my $i=0;
    while (my $h = $sth->fetchrow_hashref){
        #Fri, May 18, 2018 @ 14:40:17 EDT
        #skip in processing items
        my($status)=$dbh->selectrow_array("select status from opl_itemstatus where barcode=? order by id desc limit 1",undef , $h->{'barcode'});
        next if($status==9); 

         while($i < $n && $h->{'barcode'} gt @loanMissing[$i]  ){
            $i++
        }
     
        if($i >= $n || $h->{'barcode'} lt @loanMissing[$i] ){
            $h->{'status'} ='missing';
            push @missing,$h ;

        }
                 
    }

    my $sth_missing = $dbh->prepare(<<_STH_);
insert into opl_inventory
 set invId =?,
 callNumber= ? ,
 barcode =? ,
 status =?,
 callNumberSort=?,
 location=?
_STH_
    foreach my $h (@missing){
        $sth_missing->execute($invId,$h->{'callNumber'},$h->{'barcode'},$h->{'status'},$h->{'callNumSort'},$h->{'location'});
    }

    $sth_missing->finish;
    
}
######################################################################
sub inventory_excluded_bk {
    my ($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType) = @_;
    my $sql="update opl_inventory v left outer join 
            (select barcode from opl_item i inner  join opl_marcRecord r using(rid) 
            where  i.barcode not regexp '^___'  && i.callNumber >= '$callNoB' &&  i.callNumber <= '$callNoE' ";

    my $invRecTypeCond="";
    if($invRecType ne ''){
        $invRecTypeCond=$invRecType;
        $invRecTypeCond =~ s/,/','/g;
        $invRecTypeCond = " r.recFormat in ('$invRecTypeCond')";
    }

    my $invItemTypeCond="";
    if($invItemType ne ''){
        $invItemTypeCond=$invItemType;
        $invItemTypeCond =~ s/,/','/g;
        $invItemTypeCond = " i.typeId in ('$invItemTypeCond')";
    }
    my $filterCond ="";
    if($invRecTypeCond ne ''){
        $filterCond=$invRecTypeCond;
    }
    if( $invItemTypeCond ne ''){
        $filterCond .= " and " if($filterCond ne '');
        $filterCond .= $invItemTypeCond
    }
    if($filterCond  ne ''){
        $sql .= " and ($filterCond)";
    }
     $sql .=")  t on t.barcode=v.barcode  set status='excluded'  where t.barcode is null && v.status <>'invalid' ";

    my $sth= $dbh->prepare($sql);
    $sth->execute();

}
######################################################################
sub inventory_excluded {
    my ($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType) = @_;
    my $sql="update opl_inventory v
             inner join  opl_item i using(barcode)
             inner  join opl_marcRecord r using(rid) 
             set v.status='excluded'
             where  i.barcode not regexp '^___'  && invId=$invId  && v.status <>'invalid' ";
    
    my $invRecTypeCond="";
    if($invRecType ne ''){
        $invRecTypeCond=$invRecType;
        $invRecTypeCond =~ s/,/','/g;
        $invRecTypeCond = " r.recFormat not in ('$invRecTypeCond')";
    }

    my $invItemTypeCond="";
    if($invItemType ne ''){
        $invItemTypeCond=$invItemType;
        $invItemTypeCond =~ s/,/','/g;
        $invItemTypeCond = " i.typeId not in ('$invItemTypeCond')";
    }
    my $filterCond ="";
    if($invRecTypeCond ne ''){
        $filterCond=$invRecTypeCond;
    }
    if( $invItemTypeCond ne ''){
        $filterCond .= " or " if($filterCond ne '');
        $filterCond .= $invItemTypeCond
    }
    if($filterCond  ne ''){
        $sql .= " and ($filterCond)";
    }

    my $sth= $dbh->prepare($sql);
    $sth->execute();

}

######################################################################
sub inventory_misplace {
    my ($dbh,$invId,$callNoB,$callNoE,$location) = @_;
    my @fidArray=();
 
 
    my $sth_fid = $dbh->prepare(<<_STH_);
select   fid  
from     opl_inventoryFile
where    invId   = ? && 
         fid>0 &&
         fname <>''
order by fid
_STH_
    $sth_fid->execute($invId);
    while (my ($fid) = $sth_fid->fetchrow_array()){
        inventory_misplaceByFid($dbh,$fid,$callNoB,$callNoE,$location)
    }
    $sth_fid->finish;
}
######################################################################
sub _getBcScanOrderTbl{
    my($dbh,$fid)=@_;
    my $sth = $dbh->prepare("select barcode,status from opl_inventory 
                            where fid=? && status not regExp 'missing|noCallNumber|invalid'
                            order by scanOrder ");
    $sth->execute($fid);
    my $i=0;
    my $bcScanOrder={};
    while( my($bc,$status)=$sth->fetchrow_array){
        $i++;
        if($status =~ m/misplace/){
            $bcScanOrder->{$bc}=$i;
        }
    }
    return $bcScanOrder;

}
#######################################################################
# sub inventory_misplaceByFid
#
# 1 loop in scanned order
#    i : index of current element
#    p1: index of 1st element of the good sequence prior to p1
#    p2: index of last element of the good sequence start from i && callNumber < callnumber of element at p1
#    N1 number of elements start from p1 to (i-1)
#    N2 number of elements start from i to p2
#
# 2 if callnumber the current element < the prior's one then
#   2.1  find p1,N1;
#
#   2.2  find p2,N2  
#        
#   2.3 if (N1 > N2) then move up bloc  p - p2 to before p1  
#
#   2.4 else move down bloc p1-(p-1) to after p
#
# 3 loop the list again and update it misplace status if if misplace <>0
#######################################################################
sub inventory_misplaceByFid{
    my($dbh,$fid,$callNoB,$callNoE,$location)=@_;
    my $sth = $dbh->prepare("select barcode,callNumber,callNumberSort,scanOrder from opl_inventory  
                            where fid=? && status not regExp 'missing|noCallNumber|invalid'
                            order by scanOrder ");

    my $sth_status = $dbh->prepare(<<_STH_);
update  opl_inventory 
set     status        =  'misplace',
        noteDetail    = CONCAT_WS(',' ,CONCAT('refPos:',?),noteDetail)   ,
        misplacedBy   = ?    
where   scanOrder     = ? 
_STH_
    $sth->execute($fid);
    my $i=0;
    my @itemList=();
    while( my $item=$sth->fetchrow_hashref){
            push @itemList,$item;
    }
    my $numItems=scalar(@itemList);
    for(my $i=1;$i<$numItems;$i++){
        if(callNumCmpFn($itemList[$i-1],$itemList[$i])){
            my $p1 = $i;
            my ($N1,$N2)=(0,0);
            my $tmp=$itemList[$i];
            while($p1>=1 ){
               if(callNumCmpFn($tmp,$itemList[$p1-1]) ){
                   last;
               }
               $N1++; $p1--;
            }
            # check if next correct sequence > current one
            my $p2 = $i;
            while($p2<$numItems-1 &&  callNumCmpFn($itemList[$p2+1],$itemList[$p2]) 
                    && callNumCmpFn($itemList[$p1],$itemList[$p2])){
                $p2++;$N2++;
                if($N1==$N2 || $p2==$numItems-1){
                    last;
                }

            }
            if($N1>$N2){#move up
                moveBloc(\@itemList,$i,$p2,'up');
            }
            else{#move down
                moveBloc(\@itemList,$p1,$i-1 ,'down');
            }

        }
    }

    my($callNoBSort,$callNoESort)=getCallNumSortBounds($dbh,$location,$callNoB,$callNoE);
   
    foreach my $item(@itemList){
        my $misplacedBy     =$item->{'misplaceBy'};
        my $misplacedNote   =$item->{'misplaceRefPos'};

        if(defined $location && $location ne '' && uc($location) ne uc($item->{'location'})){
            $misplacedNote .=" (incorrect location)";
            $misplacedBy=0;
        }
        elsif((defined $callNoBSort && $item->{'callNumberSort'} lt $callNoBSort )||
              (defined $callNoESort && $item->{'callNumberSort'} gt $callNoESort )){
            $misplacedNote .=" (out of range)";
            $misplacedBy=0;
        }
        if(defined $item->{'misplaceBy'} && $item->{'misplaceBy'} !=0){
            $sth_status->execute($misplacedNote ,$misplacedBy,$item->{'scanOrder'});
        }
    }

    

}
######################################################################
sub moveBloc{
    my ($list,$idxB,$idxE,$dir)=@_;
    my $preCallNum="";
    if($idxB>0){
       $preCallNum= @$list[$idxB-1]->{'callNumber'};
    }
    elsif(defined @$list[$idxE+1]){
        $preCallNum=@$list[$idxE+1]->{'callNumber'};
    }
    if($dir eq 'up'){
        my $i =$idxB;
        for($i =$idxB; $i<=$idxE; $i++ ){
            my $tmp=@$list[$i];
            my $j=$i;
            $tmp->{'misplaceBy'}=0 if(!$tmp->{'misplaceBy'});
            while($j>0 && callNumCmpFn(@$list[$j-1],$tmp)){
                    @$list[$j]=@$list[$j-1];
                    $j--;
                    $tmp->{'misplaceBy'}--;
                    $tmp->{'misplaceRefPos'}= $preCallNum;
            }
            
            @$list[$j]=$tmp;

        }
    }
    else{
        my $i =$idxE;
        for($i =$idxE; $i>=$idxB; $i-- ){
            my $tmp=@$list[$i];
            $tmp->{'misplaceBy'}=0 if(!$tmp->{'misplaceBy'});
            my $j=$i;
            while($j<scalar(@$list) -1 && callNumCmpFn($tmp,@$list[$j+1])){
                    @$list[$j]=@$list[$j+1];
                    $j++;
                    $tmp->{'misplaceBy'}++;
                    $tmp->{'misplaceRefPos'}= $preCallNum;
            }

            @$list[$j]=$tmp;

        }
    }

}
######################################################################
sub callNumCmpFn{
    my ($a,$b)=@_;
   return ($a->{'callNumberSort'} ge $b->{'callNumberSort'});

}

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

# find locarion misplaced  
 
#####################################################

sub invMisLocation{
    my($dbh,$invId,$location)=@_;
    my $sth =$dbh->prepare("select barcode,scanOrder from opl_inventory where invId=? && location<>?");
    my $sql_pos ="select callNumber from opl_inventory 
                  where   invId=? && scanOrder<? && fid     = ? && order by scanOrder  desc limit 1";
    my $sql_update ="update opl_inventory 
                    set status=CONCAT_WS(',', status, 'misplace'), noteDetail=CONCAT_WS(',' ,CONCAT('refPos:',?),noteDetail) 
                    where invId=? && barcode=?";
    $sth->execute($invId,$location);
    while( my ($bc,$scanOrder)=$sth->fetchrow_array){
        my($cn)=$dbh->selectrow_array($sql_pos,undef,$invId,$scanOrder);
        $dbh->do($sql_update,undef,"$cn (out of location)",$invId,$bc);
        
    }
}

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

# find and update reference position if status is loan/damaged/found/invalid
 
#####################################################

sub updateRefPos{
    my($dbh,$invId)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select   i.scanOrder
from     opl_inventory as i 
where    i.invId   = ? && 
         (status regExp 'invalid|damaged|found|loan|noCallNumber' && status not regExp 'misplace|active')
         order by scanOrder
_STH_

    my $sth_refPos = $dbh->prepare(<<_STH_);
select   i.callNumber
from     opl_inventory i
where    i.invId   = ? && 
         scanOrder < ?
         order by scanOrder desc
limit 1
_STH_
    my $sth_setRefPos = $dbh->prepare(<<_STH_);
update   opl_inventory 
set      noteDetail = CONCAT_WS(',',CONCAT('refPos:',?),noteDetail)
where    scanOrder = ?
_STH_
   my $refPos;
   $sth->execute($invId);
    while (my $h = $sth->fetchrow_hashref) {
        my $scanOrder=$h->{'scanOrder'};
        ($refPos) = $dbh->selectrow_array($sth_refPos, undef,($invId, $scanOrder));
        $sth_setRefPos->execute($refPos,$scanOrder);

    }              
    $sth->finish;
    $sth_refPos->finish;
    $sth_setRefPos->finish;
}




#####################################################
# added 2007-05-04  -- 
# 
# inv_inventory
#####################################################

sub inv_inventory {
    my ($dbh) = @_;
    
    
    # Get inventory ID
   my ($uid,$invId,$callNoB,$callNoE,$invRecType,$invItemType,$location) = $dbh->selectrow_array(<<_STH_);
select    uid,invId,callNoB,callNoE,invRecType,invItemType,location 
from      opl_inventoryInfo 
where     status ='waiting' 
order by  invId desc  limit 0,1        
_STH_

  my $sth_update = $dbh->prepare(<<_STH_);
update   opl_inventoryInfo
set      status = ? 
where    invId  = ?  
_STH_

   my ($minCallNum,$maxCallNum) =getInvMinMaxCallnum($dbh,$location);
   

   if($callNoB eq ''){
       $callNoB= $minCallNum;
   }
   if( $callNoE eq ''){
      $callNoE =$maxCallNum;
    }
  
  # UPDATE Inventory status is PROCESSING
   $sth_update->execute('processing',$invId);
  
    preInventory($dbh,$uid,$invId);
    inventory($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType,$location);
  
  # UPDATE Inventory status is DONE
   $sth_update->execute('done',$invId);
  
}
############################################################
sub getInvMinMaxCallnum{
    my($dbh,$location)=@_;
    my($minCnSort,$maxCnSort);
    if(defined $location && $location ne ''){
        ($minCnSort,$maxCnSort)=$dbh->selectrow_array("select min(callNumSort),max(callNumSort) 
                                                       from opl_item where barcode not regexp  '^___' && location=?",undef,$location);
    }
    else{
        ($minCnSort,$maxCnSort)=$dbh->selectrow_array("select min(callNumSort),max(callNumSort) 
                                                      from opl_item where barcode not regexp  '^___'");
    }

    my ($minCallNum)=$dbh->selectrow_array("select callNumber from opl_item where callNumSort=? && callNumber is not null limit 1",undef,$minCnSort);
    my ($maxCallNum)=$dbh->selectrow_array("select callNumber from opl_item where callNumSort=? && callNumber is not null limit 1",undef,$maxCnSort);
    return ($minCallNum,$maxCallNum);
   
}
############################################################
sub getCallNumSortBounds{
    my($dbh,$location,$callNoB,$callNoE)=@_;
    my ($callNoBSort,$callNoESort)=(undef,undef);
    my($sql_b,$sql_e)=('','');
    if(defined $callNoB && defined $callNoE){
        $sql_b ="select  callNumSort from opl_item where barcode not regexp '^___' && callNumber >= '$callNoB'";
        $sql_e ="select  callNumSort from opl_item where barcode not regexp '^___' && callNumber <= '$callNoE'";
        if(defined $location && $location ne "" ){
            $sql_b .= " && location ='$location'";
            $sql_e .= " && location ='$location'";
        }

    }
    elsif(defined $location && $location ne ""){
        $sql_b ="select  callNumSort from opl_item where barcode not regexp '^___' && location ='$location'";
        $sql_e ="select  callNumSort from opl_item where barcode not regexp '^___' && location ='$location'";

    }
    if($sql_b ne ''){
        $sql_b .= "order by callNumSort  limit 1 ";
        $sql_e .= "order by callNumSort desc limit 1";
        ($callNoBSort)=$dbh->selectrow_array($sql_b);
        ($callNoESort)=$dbh->selectrow_array($sql_e);
    }
    return ($callNoBSort,$callNoESort);
}

############################################################
sub inv_cleanupInventory{
    my ($dbh,$uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
delete   
from    opl_inventory as a inner join opl_inventoryInfo as b
        on a.invId = b.invId
where   a.status   = 'active' &&
        b.uid      = ?
_STH_
    my $sth_update = $dbh->prepare(<<_STH_);
update   opl_inventoryInfo
set      status = 'closed'
where    uid    = ? 
_STH_
       
    $sth->execute($uid);
    $sth_update->execute($uid);

    $sth->finish;
    $sth_update->finish;

    return; 
}

############################################################
sub inv_closeInventory{
    my ($dbh, $invId) = @_;

=title    
    my $sth = $dbh->prepare(<<_STH_);
delete   
from    opl_inventory
where   invId    = ? &&
        status   = 'active'
_STH_

=cut   
    my $sth_update = $dbh->prepare(<<_STH_);
update   opl_inventoryInfo
set      status = 'closed'
where    invId  = ? 
_STH_
       
#    $sth->execute($invId);
    $sth_update->execute($invId);

#    $sth->finish;
    $sth_update->finish;

    return; 
}

############################################################
sub inv_getInventoryInfo{
    my ($dbh, $invId) = @_;
    my @val;
    my ($callNoB,$callNoE,,$invRecType,$invItemType,$location);
    my $sth = $dbh->prepare(<<_STH_);
select  callNoB,callNoE,invRecType,invItemType,location
from    opl_inventoryInfo
where   invId    = ?
_STH_
    
    @val = ($invId); 
    ($callNoB,$callNoE,$invRecType,$invItemType,$location) = $dbh->selectrow_array($sth, undef, @val);
    $sth->finish;
    
    return ($callNoB,$callNoE,$invRecType,$invItemType,$location);
}
############################################################

sub inv_getInventory {
    my ($dbh) = @_;


    my $query = <<_STH_;
select  *
from    opl_inventoryInfo
order by invId desc
_STH_

  
    my $sth = $dbh->prepare($query);
    my @invList;
    $sth->execute;
    while (my $h = $sth->fetchrow_hashref) {
        if($h->{'status'} eq 'closed'){
            $h->{'closed'} = 1;
        }
        elsif($h->{'status'} eq 'scanning'){
            $h->{'scanning'} = 1;
        }
        elsif($h->{'status'} eq 'waiting'){
            $h->{'waiting'} = 1;
        }
        elsif($h->{'status'} eq 'done'){
            $h->{'done'} = 1;
        }
         
        push @invList, $h;
    }
    $sth->finish;
    return @invList;
}

############################################################
sub inv_deleteInventory{
    my ($dbh,$invId) = @_;
    my $sth_info = $dbh->prepare(<<_STH_);
delete   
from    opl_inventoryInfo
where   invId    = ?
_STH_

    my $sth_file = $dbh->prepare(<<_STH_);
delete   
from    opl_inventoryFile
where   invId    = ?
_STH_
    

    my $sth = $dbh->prepare(<<_STH_);
delete   
from    opl_inventory
where   invId    = ?
_STH_
    
    $sth_info->execute($invId);
    $sth_file->execute($invId);
    $sth->execute($invId);
 
    $sth_info->finish;
    $sth_file->finish;
    $sth->finish;
    
    return; 
}

############################################################
sub inv_getInventoryByStatus{
    my ($dbh,$invId,$status,$sortField,$pageNo,$pageSize,$offsetSel) = @_;
    my @holdingList;
    my ($fid, $scanOrder) = (-1, -1);
    my ($query,$editable, $allEditable, $sf);
 
    if($sortField =~ m/^refPos$/i){
        $sf='noteDetail';
    }
    elsif($sortField && $sortField !~ m/callnumber|barcode|title/i){
        $sf="scanOrder";
    }
    else{
        $sf=$sortField;
    }
    if($status eq 'invalid'){
    $query = <<_STH_;
select i.* 
from    opl_inventory as i 
where   invId= ? &&
        status regexp ?
         
_STH_
    }
    else{
    $query = <<_STH_;
select i.* ,m.title, m.author, m.pubPlace, m.pubName, m.pubDate,m.recFormat,a.typeId,a.location itemLoc 
from    opl_inventory i left outer join opl_item a on a.barcode = i.barcode 
        left outer join opl_marcRecord m on a.rid=m.rid 
where   invId= ? &&
        status regexp ? 
        
_STH_

    }
    if ($status =~m/misplace/i && defined $offsetSel && $offsetSel>0){
        $query .= " && abs(misplacedBy) > $offsetSel ";
    }
    # Ha :2008-10-01 
    if($sf ne ''){
        $query .= "  order by  $sf";
    }
   
    if($pageSize && $pageNo){
        my $pageOffset = $pageSize * ($pageNo - 1);
        $query .= " limit $pageOffset , $pageSize";
    }

    my $sth = $dbh->prepare($query);
    $sth->execute($invId,$status);
    while (my $h = $sth->fetchrow_hashref) {
        my $statusDetail =getStatusDetail($h->{'noteDetail'});
        #get the reference call# 
        if ($h->{'status'} =~ m/misplace|loan|invalid|damaged|found|exluded/i){
            $h->{'refPos'} = $statusDetail->{'refPos'};
        }
        if($h->{'status'}=~ m/invalid/i){
            my $titleRef=''; my $bcRef='';
            if($statusDetail->{'refPos'} ne ''){
            ($titleRef,$bcRef) = $dbh->selectrow_array(<<_STH_);
select title ,v.barcode from opl_marcRecord m 
       inner join opl_item i on i.rid=m.rid 
       inner join opl_inventory v on v.barcode= i.barcode && invId=$invId  && scanOrder<$h->{'scanOrder'} && status<>'invalid' 
       order by scanOrder desc 
       limit 1

_STH_
            }
        $h->{'titleRef'} = $titleRef;
        $h->{'bcRef'}    = $bcRef;
    
                      
        }
        if ($h->{'status'} =~ m/loan$/i){
            $h->{'idloan'}=$statusDetail->{'idloan'};                
        }
        if($h->{'status'}=~ m/misplace/i){
            $h->{'isMisplaced'}=1;
        }
        if ($h->{'updatedStatus'} =~ m/$status/i){
            $h->{'updated'} = 1;
            $editable = 1 if (!$h->{'updated'});
        }
        $h->{'itemLoc'}    = '_blank_' if(!defined $h->{'itemLoc'}  || $h->{'itemLoc'}  eq '');
        push @holdingList,$h;
    }
=item    
    # case sort by ref-position
    if($sortField =~ m/^refPos$/i) {
        @holdingList = sort {
            $a->{'refPos'} <=> $b->{'refPos'}||
            $a->{'refPos'} cmp $b->{'refPos'} 
            }@holdingList;
    }
=cut       
    my ($allEditable) = $dbh->selectrow_array(<<_STH_);
select  count(*)
from    opl_inventory
where   status regExp  '$status' &&
        updated = 0
_STH_
    return (\@holdingList, $editable, $allEditable);  
 
}

############################################################
sub inv_getInventoryStatusByBc{
    my($dbh,$invId,$bc,$status) = @_;
    my @holdingList;
    my ($fid, $scanOrder) = (-1, -1);
    my ($query,$editable, $allEditable);
  
    my $sth;
    if($status eq 'invalid'){
        $sth = $dbh->prepare(<<_STH_);
select  i.* 
from    opl_inventory i  
where   invId= ? &&
        i.barcode =? &&
        status= ? 
_STH_
    }
    else{
        $sth = $dbh->prepare(<<_STH_);
select  i.* ,m.title, m.author, m.pubPlace, m.pubName, m.pubDate
from    opl_inventory i inner join opl_item a on a.barcode = i.barcode 
        inner join opl_marcRecord m on a.rid=m.rid 
where   invId= ? &&
        i.barcode =? &&
        status regExp  ? 
_STH_
    }
 
    $sth->execute($invId,$bc,$status);
    my $h;
    if($h = $sth->fetchrow_hashref)
    {
        my $statusDetail =getStatusDetail($h->{'noteDetail'});
        if ($h->{'status'} =~ m/misplace|loan|invalid|damaged|found|noCallNumber/i){
            $h->{'refPos'} = $statusDetail->{'refPos'};
        }
        if ($h->{'status'} =~ m/loan$/i){
            $h->{'idloan'}=$statusDetail->{'idloan'};                
        }
        if($h->{'status'}=~ m/misplace/i){
            $h->{'isMisplaced'}=1;
        }   
         if($h->{'status'}=~ m/invalid/i){
            my $titleRef='';my $bcRef='';
            if($statusDetail->{'refPos'} ne ''){
            ($titleRef,$bcRef) = $dbh->selectrow_array(<<_STH_);
select title,barcode from opl_marcRecord m 
       inner join opl_item i on i.rid=m.rid 
where i.callNumber='$statusDetail->{'refPos'}'
limit 1
_STH_
    }
        $h->{'titleRef'}=$titleRef;
        $h->{'bcRef'}    = $bcRef;
        
                      
        }
 
   }
   return $h;
}

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

sub getStatusDetail{
    my($noteDetail)=@_;
    my $retval;
    my @note = split(/,/,$noteDetail);
    for(my $i; $i < scalar(@note); $i++){
           my @tmp=  split(/:/,@note[$i]);
           $retval->{@tmp[0]}=@tmp[1];
    }
    return $retval;
}


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

sub inv_mark_Returned {
    my ($dbh, $barcodeList,$invId) = @_;
    my @barcode = @{$barcodeList};
     
    my $sth_noteDetail = $dbh->prepare(<<_STH_);
select  noteDetail
from    opl_inventory
where   status  regexp 'loan' &&
        invId   = ? &&
        barcode = ?
_STH_

    my $sth_update_loan = $dbh->prepare(<<_STH_);
update  opl_loan  
set     dateReturn    = now()
where   barcode       = ? &&
        idloan        = ? &&
        dateReturn is null
_STH_

    my $sth_update_inv = $dbh->prepare(<<_STH_);
update  opl_inventory 
set     updatedStatus = CONCAT(updatedStatus, ',loan')
where   barcode     = ? &&
        invId       = ? &&
        status      regexp 'loan' 
_STH_
   
   my @val; 
   my ($noteDetail,$idloan);
   foreach my $bc (@barcode) {
        $bc    =~ s/^\s+|\s+$//g;

        @val = ($invId,$bc); 
        ($noteDetail) = $dbh->selectrow_array($sth_noteDetail, undef, @val);
        my $statusDetail =getStatusDetail($noteDetail);
        $idloan = $statusDetail->{'idloan'};
        if($idloan ne ''){
            $sth_update_inv->execute($bc,$invId);
            $sth_update_loan->execute($bc,$idloan);
        }
    }
    $sth_update_loan->finish;
    $sth_update_inv->finish;
}