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) = @_;
   
  # 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);
  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); 
  
  # 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) = @_;
    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;
    }
    
#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, 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.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 .= "   order by i.barcode";
  my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $n=scalar(@loanMissing);
    my $i=0;
    while (my $h = $sth->fetchrow_hashref){
             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 =?
_STH_
    foreach my $h (@missing){
        $sth_missing->execute($invId,$h->{'callNumber'},$h->{'barcode'},$h->{'status'});
    }

    $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) = @_;
    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)
    }
    $sth_fid->finish;
}
######################################################################
sub inventory_misplaceByFid{
    my($dbh,$fid,$callNoB,$callNoE)=@_;

       
    my $sth_pos = $dbh->prepare(<<_STH_);
select  callNumber 
from    opl_inventory 
where   scanOrder     < ? &&
        fid         = ? &&
        callNumber <>''
order by  scanOrder desc limit 1       
_STH_

   my $sth = $dbh->prepare(<<_STH_);
select   i.* 
from    opl_inventory i inner join opl_item t on t.barcode=i.barcode
        inner join opl_marcRecord r on t.rid=r.rid
where   fid =? &&
        status not regExp 'missing|noCallNumber|invalid' 
order by i.callNumber,r.author,r.titleSort,scanOrder
_STH_

   my $sth_firstScanOrder = $dbh->prepare(<<_STH_);
select  min(scanOrder) as scanOderB 
from    opl_inventory
where   fid     = ? && 
        status not regExp 'missing|noCallNumber|invalid' 
_STH_

    my $sth_status = $dbh->prepare(<<_STH_);
update  opl_inventory
set     status        = CONCAT_WS(',', status, 'misplace'),
        noteDetail    = CONCAT_WS(',' ,CONCAT('refPos:',?),noteDetail)       
where   scanOrder     = ? 
_STH_
 
    my $sth_outrange = $dbh->prepare(<<_STH_);
select   * 
from     opl_inventory 
where    fid   = ? && 
         (callNumber <? || callNumber >?) &&
         callNumber <> '' &&
         status not regExp 'missing|noCallNumber|invalid' 
order by callNumber
_STH_



   my $sth_preCallNoOK = $dbh->prepare(<<_STH_);
select  callNumber  
from    opl_inventory
where   scanOrder < ? &&
        fid     = ? &&
        status not regExp 'missing|noCallNumber|invalid|misplace' 
order by  scanOrder desc limit 1       
_STH_

   my ($last, $last1) = (0,0);
   $sth_firstScanOrder->execute($fid);
   if(my $n =  $sth_firstScanOrder->fetchrow_hashref){
        $last  = scalar($n->{'scanOderB'});
        $last1 = $last;
   }
   $sth->execute($fid);
   my @b;
   my $curCallNo    = '';
   my $curCallNoSort = '';
   my $lastCallNo   = '';
   my $lastCallNoSort   = '';
   my $curScanOrder = 1; 
   my @val;
   my ($missplacedPos,$prevOrder);
   my $callNoBSort=uc(getSortCallnumber($callNoB));
   my $callNoESort=uc(getSortCallnumber($callNoE));
   while (my $h = $sth->fetchrow_hashref) {

      $curScanOrder =scalar($h->{'scanOrder'}) ;
      $curCallNo     =uc($h->{'callNumber'}); 
      $curCallNoSort =uc($h->{'callNumberSort'}); 
      $curCallNoSort =~ s/^\s+//g;
          if($curScanOrder  < $last1 && $curScanOrder > $last && $curCallNoSort ne $lastCallNoSort){
              
              $prevOrder =  $last1 > 1 ?  $last1 - 1 : $last1;
              @val = ($prevOrder,$fid); 
              ($missplacedPos) = $dbh->selectrow_array($sth_pos, undef, @val);
              
              # bug found on Wed, Mar 19, 2008 @ 14:00:29 EDT
              # $curScanOrder???
              #$sth_status->execute($curScanOrder,$last1);
             
              $sth_status->execute($missplacedPos,$last1);
              $last1 = $curScanOrder ;

              
          }
          elsif($curScanOrder  < $last1  && $curCallNoSort ne $lastCallNoSort){
              $prevOrder =  $curScanOrder > 1 ?  $curScanOrder - 1 : $curScanOrder;
              @val = ($curScanOrder,$fid); 
              ($missplacedPos) = $dbh->selectrow_array($sth_pos, undef, @val);
              
              $sth_status->execute($missplacedPos,$h->{'scanOrder'});


         }
         elsif($curCallNoSort ne ""){
              $last  = $last1;
              $last1 = $curScanOrder;
              if(uc($curCallNoSort) lt uc($callNoBSort) || uc($curCallNoSort) gt uc($callNoESort)){
                @val = ($curScanOrder,$fid);
                ($missplacedPos) = $dbh->selectrow_array($sth_preCallNoOK, undef, @val);
                $sth_status->execute("$missplacedPos  (out of range)",$h->{'scanOrder'});
              }

          } 
          
            
          $lastCallNoSort= $curCallNoSort;
   
=item       
      $curScanOrder =scalar($h->{'scanOrder'}) ;
      $curCallNo =uc($h->{'callNumber'}); 
          if($curScanOrder  < $last1 && $curScanOrder > $last && $curCallNo ne $lastCallNo){
              
              $prevOrder =  $last1 > 1 ?  $last1 - 1 : $last1;
              @val = ($prevOrder,$fid); 
              ($missplacedPos) = $dbh->selectrow_array($sth_pos, undef, @val);
              
              # bug found on Wed, Mar 19, 2008 @ 14:00:29 EDT
              # $curScanOrder???
              #$sth_status->execute($curScanOrder,$last1);
             
              $sth_status->execute($missplacedPos,$last1);
              $last1 = $curScanOrder ;

              
          }
          elsif($curScanOrder  < $last1  && $curCallNo ne $lastCallNo){
              $prevOrder =  $curScanOrder > 1 ?  $curScanOrder - 1 : $curScanOrder;
              @val = ($curScanOrder,$fid); 
              ($missplacedPos) = $dbh->selectrow_array($sth_pos, undef, @val);
              
              $sth_status->execute($missplacedPos,$h->{'scanOrder'});


         }
         else{
              $last  = $last1;
              $last1 = $curScanOrder;
              if(uc($curCallNo) lt uc($callNoB) || uc($curCallNo) gt uc($callNoE)){
                @val = ($curScanOrder,$fid);
                ($missplacedPos) = $dbh->selectrow_array($sth_preCallNoOK, undef, @val);
                $sth_status->execute("$missplacedPos  (out of range)",$h->{'scanOrder'});
              }

          } 
          
            
          $lastCallNo= uc($curCallNo);
=cut
       
   }#END:while (my $h = $sth->fetchrow_hashref)

   #CASE barcodes are out of range
   $sth_outrange->execute($fid,$callNoB,$callNoE);
  # my $curNote="";
  # while (my $r = $sth_outrange->fetchrow_hashref) {
       # $curNote = $r->{'noteDetail'};
       #$sth_status->execute("$curNote --- (out of range)" ,$r->{'scanOrder'});
  # }
   $sth_firstScanOrder->finish;
   $sth_pos->finish;
   $sth->finish;
   #$sth_outrange->finish;
   $sth_preCallNoOK->finish;
   $sth_status->finish;
}

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

# 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  -- Ha
# 
# inv_inventory
#####################################################

sub inv_inventory {
    my ($dbh) = @_;
    
    
    # Get inventory ID
   my ($uid,$invId,$callNoB,$callNoE,$invRecType,$invItemType) = $dbh->selectrow_array(<<_STH_);
select    uid,invId,callNoB,callNoE, invRecType,invItemType 
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_

   if($callNoB eq ''){
       $callNoB = $dbh->selectrow_array(<<_STH_);
select  min(callNumber) 
from    opl_item 
where   callNumber <> "" && barcode not regexp '^___'
_STH_
   }
 if( $callNoE eq ''){
     $callNoE = $dbh->selectrow_array(<<_STH_);
select  max(callNumber) 
from    opl_item 
where   callNumber <> "" && barcode not regexp '^___'
_STH_
   }
  #if($callNoB eq $callNoE){
      $callNoE .="zzzzzzz";
  #}
  # UPDATE Inventory status is PROCESSING
   $sth_update->execute('processing',$invId);
  
    preInventory($dbh,$uid,$invId);
    inventory($dbh,$invId,$callNoB,$callNoE,$invRecType,$invItemType);
  
  # UPDATE Inventory status is DONE
   $sth_update->execute('done',$invId);
  
}

############################################################
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);
    my $sth = $dbh->prepare(<<_STH_);
select  callNoB,callNoE,invRecType,invItemType
from    opl_inventoryInfo
where   invId    = ?
_STH_
    
    @val = ($invId); 
    ($callNoB,$callNoE,$invRecType,$invItemType) = $dbh->selectrow_array($sth, undef, @val);
    $sth->finish;
    
    return ($callNoB,$callNoE,$invRecType,$invItemType);
}
############################################################

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) = @_;
    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
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_

    }

    # 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'});
        }

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