#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Digest::SHA qw(
    sha512_hex
);
use POSIX qw(
    ceil
    floor
);

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
    tmpl_preference
    tmpl_getRecTypeList

);
use Opals::User qw(
    user_currentUser
    user_getInformationById
);
use Opals::Constant;
use Opals::MarcXml qw(
    mxml_updateItemStatus
);
use Opals::Transaction qw(
    trans_doReverseLost
);

use Opals::Inventory qw(
    inv_inventory
    inv_closeInventory
    inv_getInventoryInfo
    inv_cleanupInventory
    inv_deleteInventory
    inv_getInventoryByStatus
    inv_getInventoryStatusByBc
    inv_mark_Returned
);
=item
use Opals::Circulation qw(
    Circ_validateFollettBarcode
    Circ_validateSpectrumBarcode
    Circ_validateLeadingZeroBc
    Circ_validateSagebrushBarcode
);
=cut
use Opals::BarcodeMgmt qw(
    bcm_validateBc
);

#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 $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $loginuid = $ENV{'curUserId'};

my $syspref = tmpl_preference($dbh);
    
my $validateBc = $syspref->{'validateBarcode'}; 
my $barcodeType = $syspref->{'barcodeType'}; 
my $isLcc = ($syspref->{'classificationSystem'} eq 'LCC')?1:0; 
my $cgi = CGI->new;
# $cgi->param('aaa') returns an array of aaa
my $input = $cgi->Vars();

my $op =  $input->{'op'};
my $op_1 =  $input->{'op_1'};

  my $ext = ($op =~ m/^(view|mark)$/i) ? '-view' : 
          ($op =~ m/^print$/i) ? '-print' : '';

my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'report/inventory' . $ext . '.tmpl',
        reqPermission   => 'rpt_notice|rpt_catRec',
#        op              => $op,
    }
);
 
my $source   = $input->{'source'};
my $iRange   = $input->{'iRange'};
my $fid      = $input->{'openFid'};
my $list     = $input->{'list'};
my $pageNum  = $input->{'pNum'};
my $pageSize = $input->{'pSize'};
my $invId    = '';
my @recType     = $cgi->param('recType');

$invId    = $input->{'invId'};

my ($callNoB,$callNoE,$invRecType,$invItemType);
    $callNoB  = $input->{'callNoB'};
    $callNoE  = $input->{'callNoE'};

 ($pageNum)  || ($pageNum = 1);
($pageSize) || ($pageSize = 20);
 


# See User.pm for the list of permissions
if ($permission && ($permission->{'rpt_catRec'} || $permission->{'rpt_notice'})) {
     
    my $error;
    $error->{'errorCount'} = 0;
    my ($errCode, $myCookie, $user) = user_currentUser($dbh, $cgi);
    my $uid = $user->{'uid'};

   if($invId && $invId>0 ){
     ($callNoB,$callNoE,$invRecType,$invItemType) = inv_getInventoryInfo($dbh,$invId);
     if($callNoB ne '' &&  $callNoE ne ''){
         $iRange='range';
     }
     else{
         $iRange='whole';
     }

   }
   else{
        $invRecType="book,journal";
   }
   my $recordTypeArr =tmpl_getRecTypeList(3);
   my $itemTypeList  = getItemTypeList($dbh);
      if ($op =~ m/^add$/i) {
          my @recType     = $cgi->param('recType');
          $invRecType  = join(",",@recType);
          if(!defined $invId ){
            my @itemType     = $cgi->param('itemType');
            $invItemType  = join(",",@itemType);
          }


      if (!$invId || $invId eq ''){ 
             addInventoryInfo($dbh,$uid,$iRange);
           
           ($invId) = $dbh->selectrow_array(<<_STH_);
select  invId
from    opl_inventoryInfo
where   uid    = $uid &&
        status = 'scanning'
order by  invId desc  limit 0,1        
_STH_
        }
                   
           ($fid, $error, $pageNum) = add($dbh, $cgi, $input,$invId);
           addCallNumber($dbh,$invId,$isLcc);
         
   }
   elsif ($op =~ m/^remove$/i) {
        my $type = $input->{'type'};
        remove($dbh, $type, $list,$invId);
   }
   elsif ($op =~ m/^inventory$/i) {
        #inventory($dbh,$callNoB,$callNoE,$invId);
  my $sth_update = $dbh->prepare(<<_STH_);
update   opl_inventoryInfo
set      status = ? 
where    invId  = ?  
_STH_
   
         # UPDATE Inventory status is WAITING
        $sth_update->execute('waiting',$invId);
        inv_inventory($dbh);
        #$template->param(
        #    processing         =>1);

   }
   elsif ($op =~ m/^mark$/i) {
        mark($dbh, $input, $cgi,$invId);
        $op = 'view';
   }
   elsif ($op =~ m/^new$/i) {
        inv_cleanupInventory($dbh,$uid);
        $template->param(
            newInv => 1,
        );
   }
   elsif ($op =~ m/^delete$/i) {
       inv_deleteInventory($dbh,$uid,$invId);
       ($invId,$callNoB,$callNoE)=('','','');
   }
   elsif ($op =~ m/^close$/i) {
      inv_closeInventory($dbh,$invId);
   }



        # listing...
        my $status    = $input->{'status'};
 
    
        if ($op =~ m/^view$/i) {
            #my $sortField = ($status =~ m/(invalid|loan)/i) ? 'unsortable' : $input->{'sortField'};
            if($op_1 =~ m/remove/i){
                removeMissing($dbh,$uid,$invId,$input,$cgi) ;
            }
            my $sortField =  $input->{'sortField'};
            my ($holding, $editable, $allEditable) = 
                inv_getInventoryByStatus($dbh, $invId, $status,$sortField,$pageNum, $pageSize);
            my ($pageList, $pageNum) = getPageListByStatus($dbh,$invId, $status, $pageNum, $pageSize);
            my ($isValid) = $dbh->selectrow_array(<<_STH_);
select  varValue
from    opl_inventoryResult
where   varName = 'isValid'
_STH_
            $template->param(
                holding        => $holding,
                rangedPageList => $pageList,
                pageNum        => $pageNum,
                editable       => $editable,
                allEditable    => $allEditable,
                result_isValid => $isValid,
                status         => $status,
                "is_$status"   => 1,
                sortField      => $sortField,
                "sortBy_$sortField"      => $sortField,
            );
        }
        elsif ($op =~ m/^print$/i) {
            my $holding = printHolding($dbh, $input, $cgi);
            $template->param(
                holding         => $holding,
                "is_$status"    => 1,
                status          => $input->{'status'},
            );
        }
        else {
            if ($op =~ m/^report$/i) {
                    $template->param(
                        inventoryReport => 1,
                    );
            }
  ####($callNoB,$callNoE,$fid,$status) = $dbh->selectrow_array(<<_STH_);
           
 ($callNoB,$callNoE,$status) = $dbh->selectrow_array(<<_STH_);
select i.callNoB, i.callNoE ,status
from    opl_inventoryInfo as i left outer join opl_inventoryFile as f on i.invId = f.invId 
where   i.invId = $invId
_STH_
            if($status=~ m/scanning/i){
                $template->param( "is_scanning"  =>1) ;
            }
            listInfo($dbh,$uid,$callNoB,$callNoE,$template,$fid,$pageNum,$pageSize,$invId);
            $template->param(
                scanner => ($source =~ m/^scanner$/i)?1:0,
                whole   => ($iRange =~ m/^whole$/i)?1:0,
                callNoB => $callNoB,
                callNoE => $callNoE,
                openFid => $fid
            );
            $template->param($error);
        }
        
 
     $template->param(
        invId => $invId,
        callNoB => $callNoB,
        callNoE => $callNoE,

    );
    foreach my $r(@{$recordTypeArr}){
       foreach my $item (@{$r->{'item'}}){
           if($invRecType =~ m/$item->{'mType'}/i){
               $item->{'checked'}=1;
           }
       }
   }
   if(defined $invItemType && $invItemType ne ''){
       my $itemTypeTbl={};
       foreach my $t(split (",", $invItemType )){
           $itemTypeTbl->{uc($t)}=1;
       }
       foreach my $t(@$itemTypeList){
           if($itemTypeTbl->{uc($t->{'typeId'})}){
               $t->{'checked'}=1;
           }
           else{
               $t->{'checked'}=0;
           }
       }
   }
   $template->param(itemTypeList =>  $itemTypeList);
   $template->param(invRecTypeList=>$recordTypeArr);

 
      
}
 $template->param(hlpUrl     => Opals::Constant->getHlpUrl('inventoryList') );
 

tmpl_write($dbh, $cgi, $cookieList, $template);
#$dbh->disconnect();

############################################################
sub addCallNumber {
    my ($dbh, $invId,$isLcc) = @_;
    my $callNumSortField=$isLcc?"callNumSort_lcc":"callNumSort_dewey" ;
    my $sth_callNumber = $dbh->prepare(<<_STH_);
update      opl_inventory as a 
inner join  opl_item as i 
            on a.barcode = i.barcode
set         a.callNumber = i.callNumber,
            a.callNumberSort=i.$callNumSortField
where       invId = ?
_STH_

    $sth_callNumber->execute($invId);
    $sth_callNumber->finish;
}

################################################################################
sub addInventoryInfo {
    my ($dbh,$uid, $iRange) = @_;

      my $callNoB = $input->{'callNoB'};
      my $callNoE = $input->{'callNoE'};
      my @recType     = $cgi->param('recType');
      my $invRecType  = join(",",@recType);
      my @itemType     = $cgi->param('itemType');
      my $invItemType  = join(",",@itemType);

      $callNoB =~ s/^ +| +$//i;
      $callNoE =~ s/^ +| +$//i;
=item      
    if ($iRange eq 'whole'){
     $callNoB = $dbh->selectrow_array(<<_STH_);
select  min(callNumber) 
from    opl_item 
where   callNumber <> ""
_STH_
     $callNoE = $dbh->selectrow_array(<<_STH_);
select  max(callNumber) 
from    opl_item 
_STH_
       
   }
   else{
      $callNoB = $input->{'callNoB'};
      $callNoE = $input->{'callNoE'};
   }
=cut   
   my $sth = $dbh->prepare(<<_STH_);
insert into opl_inventoryInfo
set     callNoB     = ?,
        callNoE     = ?,
        invRecType  =?,
        invItemType  =?,
        uid         = ?
_STH_

     $sth->execute($callNoB, $callNoE,$invRecType,$invItemType,$uid);
     $sth->finish;
     
}

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

sub reformatBc2Folet{
  my ($bc)=@_;
  if($bc !=~ m/^T[\d]{7}/){
    if(length($bc)==7){
       $bc ="T" . $bc ;

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

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


sub addBarcode {
    my ($dbh, $uid, $fid, $barcodeList,$invId) = @_;
    
    my $sth = $dbh->prepare(<<_STH_);
delete from  opl_inventory
where     fid     = 0 &&
          invId   = ? &&
          status regExp 'missing|excluded' &&
          updatedStatus not regExp 'missing' 
           
_STH_
    $sth->execute($invId);



my $sth_update = $dbh->prepare(<<_STH_);
update   opl_inventory 
set      status ='active',        
         fid= ? 
where    barcode = ? &&         
         invId   = ?  
_STH_
  
            

my $sth_insert = $dbh->prepare(<<_STH_);
insert into opl_inventory
set     fid     = ?,
        invId   = ?,
        barcode = ?

_STH_


    my ($countUnique, $countDuplicate) = (0, 0);
    my @barcodes = split /,/, $barcodeList;

    if($validateBc eq '1'){
        for(my $i=0; $i < scalar(@barcodes) ;$i++){
            @barcodes[$i]= bcm_validateBc($dbh,@barcodes[$i],$barcodeType);
            #@barcodes[$i] =Circ_validateFollettBarcode($dbh,@barcodes[$i]);
        }
    }
    $countUnique = scalar(@barcodes);
    foreach my $barcode (@barcodes) {
            #Ha 2008-06-27  $sth_insert->execute($fid,$invId,$barcode);
             #IF @@ROWCOUNT = 0 
            if ($sth_update->execute($fid,$barcode,$invId)) {
                $sth_insert->execute($fid,$invId,$barcode);
            }
   }
    $countUnique -= $countDuplicate;
   
    $sth->finish;
    $sth_update->finish;
    $sth_insert->finish;

    
    #update orderidB and orderidE for table opl_inventoryInfo
    updateInventoryInfo_orderID($dbh, $uid,$invId, $fid);
 
    if ($countUnique) {
        $dbh->do(<<_STH_);
update  opl_inventoryResult
set     varValue = 0
where   varName = 'isValid'
_STH_
    }

    return ($countUnique) ? $fid : undef;
}

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

sub validateBc{
    my($dbh,$barcode,$barcodeType) =@_;
   if($barcodeType eq '1'){#follett
        $barcode  = Circ_validateFollettBarcode($dbh,$barcode );
    }
    elsif($barcodeType eq '2'){#spectrum
        $barcode  = Circ_validateSpectrumBarcode($dbh,$barcode );
    }
    elsif($barcodeType eq '3'){#leading zero
        $barcode  = Circ_validateLeadingZeroBc($dbh,$barcode );
    }
    elsif($barcodeType eq '4'){#SagebrushBarcode
        $barcode  = Circ_validateSagebrushBarcode($dbh,$barcode );
    }

    #else{#follett
    #    $barcode  = Circ_validateFollettBarcode($dbh,$barcode );
    #}
    return $barcode;
   
}

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

sub updateInventoryInfo_orderID {
    my ($dbh, $uid,$invId,$fid) = @_;
    my ($orderidB,$orderidE);  
    $orderidB = $dbh->selectrow_array(<<_STH_);
select   scanOrder 
from     opl_inventory 
where    invId   = $invId 
order by scanOrder
limit    0,1
_STH_

    $orderidE = $dbh->selectrow_array(<<_STH_);
select   scanOrder 
from     opl_inventory 
where    invId   = $invId 
order by scanOrder  desc
limit    0,1 
_STH_
   
    my $sth = $dbh->prepare(<<_STH_);
update opl_inventoryInfo 
set     orderidB    = ?,
        orderidE    = ?
where   invId       = ? &&
        uid         = ?

_STH_
     $sth->execute($orderidB,$orderidE,$invId,$uid);
     $sth->finish;
     
}
################################################################################

sub addBarcodeFromScanner {
    my ($dbh, $uid, $barcode,$invId) = @_;
    my $fid=getManualScanFid($dbh, $uid, $invId);

    $barcode= bcm_validateBc($dbh,$barcode,$barcodeType) if($validateBc eq '1');
    #    $barcode =Circ_validateFollettBarcode($dbh,$barcode);

    my $bc = $dbh->selectrow_array(<<_STH_);
select   barcode 
from     opl_inventory 
where    invId   = $invId && 
         barcode = '$barcode' &&
         status regexp 'missing'
_STH_
    if($bc){
        my $sth = $dbh->prepare(<<_STH_);
update opl_inventory
set     status ='active',
        fid= ?
where   barcode = ? &&
        invId= ?
_STH_

    $sth->execute($fid,$barcode, $invId);
        return $fid;
    }
    else{
        return addBarcode($dbh, $uid, $fid, $barcode,$invId);
    }
}
############################################################

sub addBarcodeFromFile {
    my ($dbh, $uid, $fname, $fdata,$invId) = @_;

    my $barcodeList = '';
    my $bc;
    while ($bc = <$fdata>) {
        $bc =~ s/[\x0a\x0d]//g;
        $bc =~ s/^\s+|\s+$//g;

        if ($bc =~ m/^(tmp|dup|__)_/i) {
            next;
        }
        #chomp($bc);
        $barcodeList .= $bc . ',';
    }

#    $barcodeList =~ s/\s+/,/g;
    
    $barcodeList =~ s/,+/,/g;
    $barcodeList =~ s/(^,|,$)//;
    if (!$barcodeList) {
        return;
    }

    my $mesgDigest = sha512_hex($barcodeList . $invId);
    my $sth = $dbh->prepare(<<_STH_);
insert  into opl_inventoryFile
set     fname      = ?,
        uid        = ?,
        invId      = ?,
        mesgDigest = ?
_STH_

    my $rv =$sth->execute($fname, $uid, $invId,$mesgDigest);
    $sth->finish;
    if (!$rv) {
        return;
    }
   
    my ($fid) = $dbh->selectrow_array(<<_STH_);
select  fid
from    opl_inventoryFile
where   mesgDigest = '$mesgDigest'
_STH_
   
      return addBarcode($dbh, $uid, $fid, $barcodeList,$invId);
}
############################################################

sub add {
    my ($dbh, $cgi, $input,$invId) = @_;

    my ($errCode, $myCookie, $user) = user_currentUser($dbh, $cgi);
    my $uid = $user->{'uid'};

    my $source = $input->{'source'};
    my ($fid, $error, $pageNum);
    $pageNum = 1;
    $error->{'errorCount'} = 0;
my $sth_info = $dbh->prepare(<<_STH_);
update opl_inventoryInfo
set     status =CONCAT_WS(',',status,'scanning')
where   invId= ?
_STH_

    if ($source =~ m/^file$/i) {
        my $fname = $input->{'bcFile'};
        my $fdata = $cgi->param('bcFile');
        $fid = addBarcodeFromFile($dbh, $uid, $fname, $fdata,$invId);
        if (!$fid) {
            $error->{'err_add_file'} = $fname;
            $error->{'errorCount'}++;
        }
        else{
            $sth_info->execute($invId);
            $sth_info->finish;
        }
    }
    elsif ($source =~ m/^scanner$/i) {
        my $barcodeList;
        $barcodeList = $input->{'bc'};
        $barcodeList =~ s/\s+/,/g;
        $barcodeList =~ s/,+/,/g;
        $barcodeList =~ s/(^,|,$)//;

        $fid = addBarcodeFromScanner($dbh, $uid, $barcodeList,$invId);
        if (!$fid) {
            $error->{'err_add_scanner'} = $barcodeList;
            $error->{'errorCount'}++;
        }
        else  {
            $pageNum = -1;
            $sth_info->execute($invId);
            $sth_info->finish;
        }
    }
    elsif ($source =~ m/^missing$/i) {
        removeMissing($dbh,$uid,$invId,$input,$cgi) ;
    }

    return ($fid, $error, $pageNum);
}
############################################################
sub getManualScanFid {
    my ($dbh, $uid, $invId) = @_;
  my ($fid) = $dbh->selectrow_array(<<_STH_);
select  fid
from    opl_inventoryFile
where   fname = '' &&
        uid = $uid &&
        invId= $invId
_STH_

    if (!$fid) {
        $dbh->do(<<_STH_);
insert into opl_inventoryFile
set     uid = $uid,
        invId = $invId
_STH_
        ($fid) = $dbh->selectrow_array(<<_STH_);
select  fid
from    opl_inventoryFile
where   fname = '' &&
        uid = $uid &&
        invId= $invId
_STH_
    
    }
return $fid;
    
}
############################################################

sub remove {
    my ($dbh, $type, $list,$invId) = @_;
    
    # Remove everything
    if ($type =~ m/^all$/i) {
=item        
        $dbh->do(<<_STH_);
delete   from opl_inventoryInfo 
where    invId = $invId
_STH_
=cut
         $dbh->do(<<_STH_);
delete   from opl_inventory
where    invId = $invId
_STH_
        $dbh->do(<<_STH_);
delete   from opl_inventoryFile
where    invId = $invId
_STH_
        return;
    }

    if (!$list || !$type || $type !~ m/^(fid|barcode)$/i) {
        return;
    }

    my $sth;
    if ($type =~ m/^fid$/i) {
        $sth = $dbh->prepare(<<_STH_);
delete from opl_inventoryFile where fid = ? && invId= ?
_STH_
    }
    else {
        $sth = $dbh->prepare(<<_STH_);
delete from opl_inventory where barcode = ? && invId= ?
_STH_
    }

    foreach my $item (split(/,/, $list)) {
        $sth->execute($item, $invId);
        if ($type =~ m/^fid$/i) {
            $dbh->do(<<_STH_);
delete from opl_inventory where fid = $item
_STH_
        }
    }
    $sth->finish;

    $dbh->do(<<_STH_);
update  opl_inventoryInfo
set     status = CONCAT_WS(',',status,'scanning')
where   invId = $invId
_STH_

    return;
}

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


sub mark_Missing_Found_Active {
    my ($dbh, $status, $barcodeList,$invId) = @_;
    my $sth;
    
    my @barcode = @{$barcodeList};
    if (scalar @barcode == 1 && $barcode[0] eq '__ALL__') {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_inventory
where   status regexp ? &&
        invId  = ?
_STH_
        $sth->execute($status,$invId);
        my @bc;
        while (my $h = $sth->fetchrow_hashref) {
            push @bc, $h->{'barcode'};
        }
        $sth->finish;
        @barcode = @bc;
    }

      foreach my $bc (@barcode) {
        $bc    =~ s/^\s+|\s+$//g;
        markInvStatus($dbh,$invId,$bc,$status);

    }
    
}
############################################################
sub markInvStatus{
    my ($dbh,$invId,$barcode,$status)=@_;
    my ($newStatus, $available);

    if ($status =~ m/^missing$/i) {
        ($newStatus, $available) = (ITEM_INACTIVE, 0),
    }
    elsif ($status =~ m/^(found|damaged)$/i) {
        ($newStatus, $available) = (ITEM_ACTIVE, 1),
    }
    else {
        return;
    }



    my $sth =$dbh->prepare(<<_STH_);
select barcode 
from   opl_inventory
where invId=? && barcode=? && status regexp ? && updatedStatus not regexp ?
_STH_

    my $sth_status = $dbh->prepare(<<_STH_);
insert into opl_itemstatus
set     barcode = ?,
        ondate  = now(),
        status  = ?
_STH_

     my $sth_found = $dbh->prepare(<<_STH_);
insert into opl_found
set     barcode = ?,
        ondate  = now()
_STH_

    my $sth_item = $dbh->prepare(<<_STH_);
update  opl_item
set     available = ?
where   barcode   = ?
_STH_


    my $sth_update = $dbh->prepare(<<_STH_);
update  opl_inventory
set     updatedStatus = CONCAT_WS(',', updatedStatus, ?)
where   barcode       = ? &&
        status        regexp ?  &&
        invId         = ?
_STH_

        $sth->execute($invId,$barcode,$status,$status);
        if(my $rec=$sth->fetchrow_hashref) {  
            my $curStatus=getItemCurStatus($dbh,$barcode);
            if(defined $curStatus && $curStatus == ITEM_LOST && $newStatus==ITEM_ACTIVE){
                trans_doReverseLost($dbh,$barcode,$loginuid);
            }
            mxml_updateItemStatus($dbh,$barcode,$newStatus);
            #$sth_status->execute($barcode,$newStatus);
            #$sth_found->execute($barcode) if ($status =~ m/^found$/i); # comment-out???
            #$sth_item->execute($available,$barcode);
            $sth_update->execute($status,$barcode,$status,$invId);
        }
    $sth->finish;
    $sth_status->finish;
    $sth_found->finish;
    $sth_item->finish;
    $sth_update->finish;



}
############################################################
sub getItemCurStatus{
    my($dbh,$barcode)=@_;
    my ($status)=$dbh->selectrow_array("select status from opl_itemstatus where barcode='$barcode' order by id desc limit 1");
    return $status;
}

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

sub mark_Missplaced {
    my ($dbh, $status, $barcodeList,$invId) = @_;
    my $sth;

    my @barcode = @{$barcodeList};
    if (scalar @barcode == 1 && $barcode[0] eq '__ALL__') {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_inventory
where   status  regexp ? &&
        invId  = ?
_STH_
        $sth->execute($status,$invId);
        
        my @bc;
        while (my $h = $sth->fetchrow_hashref) {
            push @bc, $h->{'barcode'};
        }
        $sth->finish;
        @barcode = @bc;
    }

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

    $sth_update->finish;
}
############################################################

sub mark_Returned {
    my ($dbh, $status, $barcodeList,$invId) = @_;
    my @barcode = @{$barcodeList};
    my $sth;
    
    if (scalar @barcode == 1 && $barcode[0] eq '__ALL__') {
        $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_inventory
where   status  regexp ? &&
        invId  = ?
_STH_
        $sth->execute($status,$invId);
        
        my @bc;
        while (my $h = $sth->fetchrow_hashref) {
            push @bc, $h->{'barcode'};
        }
        $sth->finish;
        @barcode = @bc;
    }
    inv_mark_Returned(($dbh, \@barcode,$invId));
     




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

sub mark {
    my ($dbh, $input, $cgi,$invId) = @_;

    my $status  = $input->{'status'};
     $invId  = $input->{'invId'};

    if (!$status || $status !~ m/missing|found|loan|damaged|misplace/i) {
        return;
    }

    my @barcode;
    if ($input->{'selection'} =~ m/^all$/i) {
        @barcode = ('__ALL__');
    }
    else {
        foreach my $bc ($cgi->param('barcode')) {
            if ($bc =~ m/^(updated|reference)::/i) {
                next;
            }

            push @barcode, $bc;
        }
    }

    if (scalar @barcode < 0) {
        return;
    }
 
    if ($status =~ m/missing|found|damaged/i) {
        mark_Missing_Found_Active($dbh, $status, \@barcode,$invId);
    }
    elsif ($status =~ m/misplace/i) {
           mark_Missplaced($dbh, $status, \@barcode,$invId);
    }
     else {#if ($status =~ m/^loan$/i) {
        mark_Returned($dbh, $status, \@barcode,$invId);
    }
}
############################################################

sub printHolding {
    my ($dbh, $input, $cgi) = @_;

    my @holding ; 
    my $status  = $input->{'status'};
    my $invId   = $input->{'invId'};
    if (!$status || $status !~ m/missing|found|loan|damaged|invalid|misplace|noCallNumber|excluded/i) {
        return \@holding;
    }
    my $sth;
    my ($fid, $scanOrder) = (-1, -1);
    my $sortField = $input->{'sortField'};
    if ($input->{'selection'} =~ m/^all$/i) { # Print all
         my ($holding, $editable, $allEditable) =
         inv_getInventoryByStatus($dbh, $invId, $status, $sortField);
         return $holding;

    } 
    else {
        foreach my $bc ($cgi->param('barcode')) {
                $bc =~ s/updated:://i;
                $bc =~ s/\s+//i;
                
            my $h =inv_getInventoryStatusByBc($dbh, $invId,$bc,$status);
            
            if($h){
                push @holding , $h;
            }
        }   

        if ($sortField){
            if($sortField =~ m/^callnumber$/i) {
                @holding = sort {
                    $a->{'callNumber'} cmp $b->{'callNumber'} || 
                    $a->{'callNumber'} <=> $b->{'callNumber'}
                }@holding
            }
            elsif($sortField =~ m/^title$/i) {
                @holding = sort {
                    $a->{'title'} cmp $b->{'title'} || 
                    $a->{'title'} <=> $b->{'title'}
                }@holding
            }
            elsif($sortField =~ m/^barcode$/i) {
                @holding = sort {
                    $a->{'barcode'} cmp $b->{'barcode'} || 
                    $a->{'barcode'} <=> $b->{'barcode'}
                    }@holding
            }
            if($sortField =~ m/^refPos$/i) {
                @holding = sort {
                    $a->{'refPos'} cmp $b->{'refPos'} || 
                    $a->{'refPos'} <=> $b->{'refPos'}
                    }@holding
            }   
       }
    }
    
    return \@holding;
}
############################################################

sub getInputFile {
    my ($dbh, $fid,$invId) = @_;
    my ($user, $count);
    my $dummy;
    my @file;
    my @scanner;
    my $total = 0;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_inventoryFile
where   invId = ?
_STH_
    my $sth_count = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_inventory
where   fid    = ? && 
        invId  = ? &&
        status not like '%missing%'  
_STH_

    $sth->execute($invId);
    while (my $f = $sth->fetchrow_hashref) {
        $f->{'isOpened'} = ($f->{'fid'} == $fid);

        $sth_count->execute($f->{'fid'},$invId );
        $f->{'count'} = $sth_count->fetchrow_array;
        $total += $f->{'count'};

        ($user, $dummy) = user_getInformationById($dbh, $f->{'uid'});
        $f->{'firstname'} = $user->{'firstname'};
        $f->{'lastname'} = $user->{'lastname'};

        if ($f->{'fname'}) {
            push @file, $f;
        }
        else {
            push @scanner, $f;
        }
    }
    $sth_count->finish;
    $sth->finish;

    my @inFile = (@scanner, @file);

    return (\@inFile, $total);
}
############################################################

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

    my $pageOffset = $pageSize * ($pageNum - 1);

    my $query = <<_STH_;
select  *
from    opl_inventoryInfo
_STH_

  
    my $sth = $dbh->prepare($query);
    my @holding;
    $sth->execute;
    while (my $h = $sth->fetchrow_hashref) {
        push @holding, $h;
    }
    $sth->finish;

    return @holding;
}
############################################################

sub getInputFileData {
    my ($dbh, $fid, $pageNum, $pageSize,$invId) = @_;
    if($pageNum <1){
        $pageNum=1;
    }
    my $pageOffset = $pageSize * ($pageNum - 1);

    my $query ="select  * from  opl_inventory  where invId = $invId " ;

    if ($fid && $fid>0 ) {
        $query .= " && fid=$fid ";
    }
    else{
        $query .= " && fid>0 ";

    }
    $query .= " order by fid, scanOrder limit $pageOffset, $pageSize ";
    my $sth = $dbh->prepare($query);
    my @holding;
    $sth->execute;
    while (my $h = $sth->fetchrow_hashref) {
        push @holding, $h;
    }
    $sth->finish;

    return @holding;
}
############################################################

sub getPageListByStatus {
    my ($dbh, $invId, $status, $pageNum, $pageSize) = @_;

    my ($count) = $dbh->selectrow_array(<<_STH_);
select count(*) from opl_inventory where invId = $invId &&  status regExp '$status'
_STH_
    my $pageMax = ceil($count / $pageSize);
    if ($pageNum && $pageNum > $pageMax) {
        $pageNum = $pageMax;
    }

    my @pageList = tmpl_rangedPageList($count, $pageNum, $pageSize, 10);

    return (\@pageList, $pageNum);
}
############################################################

sub getPageListByFid {
    my ($dbh, $fid, $pageNum, $pageSize,$invId) = @_;

    my $query = 'select count(*) from opl_inventory';
    $query .= ' where invId = ' . $invId;
    if ($fid && $fid =~ m/^[\d]+$/) {
        $query .= ' && fid = ' . $fid;
    }
    else  {
        $query .= ' && fid >0';
    }
    
    my ($count) = $dbh->selectrow_array($query);
    if ($pageNum && $pageNum == -1) {
        $pageNum = ceil($count / $pageSize);
    }
    my @pageList = tmpl_rangedPageList($count, $pageNum, $pageSize, 5);

    return (\@pageList, $pageNum);
}
############################################################

sub getTotalLoan {
    my ($dbh,$callNoB,$callNoE) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select  *
from    opl_loan
where   dateReturn is null
group by barcode
_STH_

    my $sth_loan = $dbh->prepare(<<_STH_);
select  *
from    opl_loan
where   barcode = ?
order by dateLoan desc
limit 0, 1
_STH_

    my $sth_currentStatus = $dbh->prepare(<<_STH_);
select  *
from    opl_itemstatus
where   barcode = ?
order by ondate desc
limit 0, 1
_STH_

    $sth->execute();
    my ($status, $lastLoan);
    my $loan = 0;
    while (my $l = $sth->fetchrow_hashref) {
        $sth_loan->execute($l->{'barcode'});
        $lastLoan = $sth_loan->fetchrow_hashref;
        if ($lastLoan && $lastLoan->{'dateReturn'}) {
            next;
        }

        $sth_currentStatus->execute($l->{'barcode'});
        ($status) = $sth_currentStatus->fetchrow_array;
        if (!$status || $status == ITEM_ACTIVE) {
            $loan++;
        }
    }
    $sth->finish;
    $sth_currentStatus->finish;

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

sub getInfo {
    my ($dbh,$invId) = @_;
    
    my ($dateInventory,$callNoB,$callNoE,$invRecType,$invItemType) = $dbh->selectrow_array(<<_STH_);
select  invDate,callNoB,callNoE,invRecType,invItemType
from    opl_inventoryInfo
where   invId = $invId
_STH_
 
   

$callNoB = "0" if( $callNoB eq'');

$callNoE .= "zzzzzzzz";

    my $total_qry = "
select  count(*)
from    opl_item i inner join opl_marcRecord r on i.rid=r.rid
where   available = 1 &&
        barcode not regexp '^(tmp|dup|__)_' &&
        callNumber >= '$callNoB' && 
        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 ''){
        $total_qry .= " and ($filterCond)";
    }

    my ($total_active) =$dbh->selectrow_array($total_qry);

   
    my $total_loan_qry ="
select count(*) from opl_loan  as l
inner join  opl_item as i 
            on l.barcode = i.barcode
inner join opl_marcRecord r on i.rid=r.rid             
where  l.dateReturn is null  && 
       i.callNumber >= '$callNoB' && 
       i.callNumber <= '$callNoE'";
if($filterCond  ne ''){
        $total_loan_qry .= " and ($filterCond)";
    }


      
    my ($total_loan) =  $dbh->selectrow_array($total_loan_qry);
  
    my ($total_validated) = $dbh->selectrow_array(<<_STH_);
select  count(*)
from    opl_inventory
where   invId = $invId &&
        status='active'
_STH_



    return ($total_active,$total_validated,$total_loan,$dateInventory);
}



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

sub getResult {
    my ($dbh,$invId,$callNoB,$callNoE) = @_;
    my $result;

    my $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_inventory
where   status regexp ? &&
        invId   = ? 
_STH_

    my $sth_update = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_inventory
where   updatedStatus regexp ? &&
        invId   = ? 
_STH_


     my ($isStatus) = $dbh->selectrow_array(<<_STH_);
select  status
from    opl_inventoryInfo
where   invId   = $invId 
_STH_
   
   if($isStatus && $isStatus =~ m/done/i){
        $template->param(
            result => 1,
        );
    }
   

    my @status = qw(damaged found missing loan invalid noCallNumber misplace excluded);
    foreach my $s (@status) {
        $sth->execute($s,$invId);
        ($result->{$s}) = $sth->fetchrow_array;
 
        $sth_update->execute($s,$invId);
        ($result->{$s.'_updated'}) = $sth_update->fetchrow_array;

        $result->{$s.'_total'} = $result->{$s} + $result->{$s.'_updated'};
    }
   
    my $sth_OK = $dbh->prepare(<<_STH_);
select  count(*)
from    opl_inventory
where   status = ? &&
        invId  = ?

_STH_

    $sth_OK->execute('',$invId);   
    ($result->{'active'}) = $sth_OK->fetchrow_array;
    $result->{'active'.'_total'} = $result->{'active'};
     
    $sth->finish;
    $sth_update->finish;
    $sth_OK->finish;

    return $result;
}
############################################################
sub getItemOnLoanWithoutScanning{
    my ($dbh,$uid,$callNoB,$callNoE,$invId) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select count(distinct i.barcode) 
from opl_item i left outer join opl_inventory v on i.barcode=v.barcode   && v.invId=?
          inner join opl_loan l  on l.barcode =i.barcode  
where  i.barcode not regexp '^___' && v.barcode is null      
       && i.callNumber >= ? &&         i.callNumber <= ?  
       && l.dateReturn is null   order by i.barcode

_STH_
    
    my $numOnloanWithoutScan=0;
    $sth->execute($invId,$callNoB,$callNoE);  
    ($numOnloanWithoutScan) = $sth->fetchrow_array();
    return $numOnloanWithoutScan;
}


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

sub listInfo {
    my ($dbh,$uid,$callNoB,$callNoE, $template, $fid, $pageNum, $pageSize,$invId) = @_;
    my ($inputFile, $total_scanned) = getInputFile($dbh,$fid ,$invId);
    my $pageList;
   # if($callNoB eq $callNoE){
      $callNoE .="zzzzzzz";
   # }
 
    ($pageList, $pageNum)   = getPageListByFid($dbh, $fid, $pageNum, $pageSize,$invId);
    my @inputFileData       = getInputFileData($dbh, $fid, $pageNum, $pageSize,$invId);
    my $numOnloanWithoutScan= getItemOnLoanWithoutScanning($dbh,$uid,$callNoB,$callNoE,$invId);
    $template->param(
        total_scanned   => $total_scanned,
        inputFile       => $inputFile,
        rangedPageList  => $pageList,
        inputFileData   => \@inputFileData,
    );
    
    my ($total_active,$total_validated,$total_loan,$dateInventory) = getInfo($dbh,$invId);
        $template->param(
           total_active    => $total_active,
           total_validated => $total_validated,
           #$total_range     => $total_range,
           total_loan      => $total_loan,
           #numOnloanWithoutScan =>$numOnloanWithoutScan,
           #range_loan      => $range_loan,
           dateInventory   => $dateInventory,
    );

    my $result = getResult($dbh,$invId,$callNoB,$callNoE);
    foreach my $r (keys %{$result}) {
        $template->param(
            "result_$r" => $result->{$r},
        );
    }
  
    if ($total_scanned) {  
        my $unknownLimit = floor($total_active* 20/100);
        my $unknown = $total_active  - $total_scanned;
        $template->param(
            isReady      => ($unknown <= $unknownLimit),
            unknownLimit => $unknownLimit,
        );
    }
    return $template;
}

############################################################
# remove barcodes marked in the view page from missing list
# by changing their status from missing to active
############################################################

#removeMissing($dbh,$uid,$invId,$input,$cgi) ;
sub removeMissing{
    my ($dbh,$uid,$invId,$input,$cgi) =@_;
    
    my $source = $input->{'source'};
    if ($source =~ m/^missing$/i) {
        my $fid=getManualScanFid($dbh, $uid, $invId);
        my $sth = $dbh->prepare(<<_STH_);
update opl_inventory
set     status ='active',
        fid= ?
where   barcode = ? &&
        invId= ?
_STH_


           foreach my $b ($cgi->param('barcode')) {
               $b =~ s/^updated:://g;
               $b =~ s/^\s+|\s+$//i;
               $sth->execute($fid,$b, $invId);
           }
            $sth->finish;
        
    }

}


#-------------------------------------------------------------------------------
### Fri, May 09, 2014 @ 15:06:45 EDT: Item type Authority
sub getItemTypeList{
    my ($dbh)= @_;
    my $sql = <<_SQL_;
SELECT     distinct id as typeId
FROM       opl_itemType
WHERE      id <>"" && itemCategory=1
ORDER BY    id
_SQL_

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my @retVal;
    my $itentypeStr=""; 
    my $i=0;
    my $excl="ebook,e-book";
    while (my $rec = $sth->fetchrow_hashref) { 
        $rec->{'checked'}=($excl =~ m/$rec->{'typeId'}/i)?0:1;   
        $rec->{'firstCell'} = $i%4==0?1:0;  
        $rec->{'lastCell'} = $i%4==4?1:0;  
        $i++;
        $rec->{'id'} =$i;   
        push @retVal, $rec;
    }
    $sth->finish;
           
    return \@retVal;
}