package Opals::BarcodeMgmt;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    bcm_cleanSysBarcodeTbl
    bcm_updateStatusBcTbl
    bcm_createNextBiggestBc
    bcm_getBiggestBcFromDB
    bcm_getVendorList
    bcm_getSetPending
    bcm_alignTakenBc
    bcm_getNextPendingBc_import
    bcm_getBcRangeList


    bcm_validateBc
    bcm_validateFollettBarcode
    bcm_validateSpectrumBarcode
    bcm_validateLeadingZeroBc
    bcm_validateSagebrushBarcode
);

# Version number
$VERSION   = 0.01;      

use strict;
use Encode;
#-------------------------------------------------------------------------------
sub bcm_cleanSysBarcodeTbl{
    my ($dbh) = @_;
    my $timeout =getTimeOut($dbh);
    $dbh->do(<<_STH_);
update opl_bcmBc b inner join opl_session s using(sessionid) 
set b.pending=-1,b.pendingFor='', b.sessionId='' 
where pendingFor <> 'import' && b.pending>-1 && s.lasttime + interval $timeout  < now();  
_STH_


   #CASE: import

    $dbh->do(<<_STH_);
    update opl_bcmBc b left outer join opl_marcImport i on i.iid = b.pending 
           && b.status = 'available' && pending >-1 
    set b.pending=-1, pendingFor =''    
    where    pendingFor ='import'
             && (i.status ='done' or i.iid is null)
_STH_

}
#-------------------------------------------------------------------------------
sub bcm_updateStatusBcTbl{
    my ($dbh,$barcodes,$status,$rid,$pending,$pendingFor,$sessionid) = @_;
    
    my $sql = <<_STH_;
update  opl_bcmBc 
set     status = ? , sessionid = ?,
        pending= ?, pendingFor = ? 
where   barcode = ? && rid= ?
_STH_
    my $sth = $dbh->prepare($sql);
    my @arrBarcodes = split /,/,$barcodes;
    for(my $i=0; $i <scalar(@arrBarcodes); $i++){
        $sth->execute($status,$sessionid,$pending,$pendingFor,@arrBarcodes[$i],$rid);
    }
   
    $sth->finish;

}
#-------------------------------------------------------------------------------

sub bcm_getSetPending{
    my ($dbh,$startBc,$rid,$n,$pending,$pendingFor,$sessionid) = @_;
    if($rid>0){
        getSetPending_vendor($dbh,$startBc,$rid,$n,$pending,$pendingFor,$sessionid);
    }
    else{
        getSetPending_manual($dbh,$startBc,$rid,$n,$pending,$pendingFor,$sessionid);
    }
}
#-------------------------------------------------------------------------------

sub getSetPending_vendor{    
    my ($dbh,$startBc,$rid,$n,$pending,$pendingFor,$sessionid) = @_;
    my  @bcList =();
    return \@bcList if(!defined $n || $n<=0);

    my $sql = <<_STH_;
select      barcode  
from        opl_bcmBc s left outer join opl_item i using(barcode)
where       s.rid = $rid && 
            s.status = 'available' && 
            s.pending = -1 && 
            s.barcode >='$startBc' &&
            i.barcode is null
order by    s.barcode 
limit      $n 
_STH_
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $barcode = $sth->fetchrow_array) {
        push @bcList , {barcode=>$barcode};
    }
    $sth->finish;
#set pending
    if($#bcList >=0 ){
        my $firstBc=@bcList[0]->{'barcode'};
        my $lastBc=@bcList[$#bcList]->{'barcode'};
        $sth =$dbh->prepare("update opl_bcmBc set pending=?,pendingFor=?,sessionid =? where rid=? && barcode >=? && barcode <=?");
        $sth->execute($pending,$pendingFor,$sessionid,$rid,$firstBc,$lastBc);
    }
    return \@bcList;
}
#-------------------------------------------------------------------------------
sub getSetPending_manual{
    my ($dbh,$startBc,$rid,$n,$pending,$pendingFor,$sessionid) = @_;
    my  @bcList =();
    return \@bcList if(!defined $n || $n<=0);
    my $prefix="";
    my $bc=0;
    my $bcLen=0;
    my $sth = $dbh->prepare("replace into  opl_bcmBc set  rid  =0,status='available', pending=? , pendingFor=? , sessionid=? ,barcode = ?");

    if($startBc =~ m/([\D]*)([\d]*$)/){
        $prefix=$1;
        $bc=int($2);
        $bcLen=length($2);
    }
    my $strFmt="%s%0$bcLen" ."d";
    my $count=0;
    while($count <$n){
        my $t=$n - $count;
        my $bcFirst=sprintf($strFmt,$prefix,$bc);
        my $nextBc=$bcFirst;
        my $bcLast=sprintf($strFmt,$prefix,($bc+$t));
        my $bcUsed=_getBcUsedInRange($dbh,$bcFirst,$bcLast);
        for(my $i=0; $i<$t;$i++){
            $nextBc=sprintf($strFmt,$prefix,$bc);
            if(!defined $bcUsed->{$nextBc}){
                push @bcList, {barcode=>$nextBc};
                $sth->execute($pending,$pendingFor,$sessionid,$nextBc);
                $count++;
            }
            $bc++;
        }
    }
   
    return \@bcList;

}
#-------------------------------------------------------------------------------
sub _getBcUsedInRange{
    my($dbh,$bc_first,$bc_last)=@_;
    my $bcList={};
    my $sth=$dbh->prepare("select barcode from opl_item where barcode between ? AND ?");
    $sth->execute($bc_first,$bc_last);
    while(my ($bc)=$sth->fetchrow_array){
        $bcList->{$bc}=1;
    }
    return $bcList;
       
}

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

sub bcm_getVendorList{
    my ($dbh) = @_;
    
    my $sql = <<_STH_;
select      v.vid vid,vendor, r.rid as rid,bcLen,preFix,rangeFr,rangeTo , 
            min(s.barcode) as firstAvail, count(barcode) availTotal,
            s.pending,s.pendingFor  
from        opl_bcmVendor v inner join opl_bcmRange r on v.vid=r.vid 
            inner join opl_bcmBc s on s.rid = r.rid  
where       s.status = 'available' && 
            (pending is NULL || pending = '' || pending = -1) && 
            (pendingFor is NULL || pendingFor ='') 
group by vid,r.rid  having availTotal>0 
order by    vid,rid
_STH_
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my  @recordList =();
    my $preVid=-1;
    my $i=-1;
    while (my $rec = $sth->fetchrow_hashref) {
        my ($rangeFr,$rangeTo)=('','');
        my $preFix= $rec->{'preFix'};
        my $padLen=$rec->{'bcLen'}- length($preFix);
        $rangeFr =sprintf("%s%0*d",$preFix,$padLen,$rec->{'rangeFr'});
        $rangeTo =sprintf("%s%0*d",$preFix,$padLen,$rec->{'rangeTo'});

        my $vGrp={  rid       =>$rec->{'rid'},
                    rangeFr   =>$rangeFr,
                    rangeTo   =>$rangeTo,
                    availTotal=>$rec->{'availTotal'},
                    firstAvail=>$rec->{'firstAvail'}
                   };
        if($preVid != $rec->{'vid'}){
            push @recordList,{ vid=> $rec->{'vid'}, vendor  => $rec->{'vendor'}};
            $i++;
            $preVid = $rec->{'vid'};
        }
        push @{@recordList[$i]->{'vendorGrp'}},$vGrp;
    }
    $sth->finish;
    return \@recordList;
}
#-------------------------------------------------------------------------------
sub bcm_getBcRangeList{
 my ($dbh) = @_;
    
    my $sql = <<_STH_;
select v.vendor,r.rid,bcLen,r.preFix,r.rangeFr,r.rangeTo 
from opl_bcmVendor v inner join opl_bcmRange r using(vid)
_STH_
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my  @rangeList =();
    while (my $rec = $sth->fetchrow_hashref) {
        my ($rangeFr,$rangeTo)=('','');
        my $preFix= $rec->{'preFix'};
        my $padLen=$rec->{'bcLen'}- length($preFix);
        $rangeFr =sprintf("%s%0*d",$preFix,$padLen,$rec->{'rangeFr'});
        $rangeTo =sprintf("%s%0*d",$preFix,$padLen,$rec->{'rangeTo'});

        push @rangeList,{rid       =>$rec->{'rid'},
                        vendor    =>$rec->{'vendor'},
                        rangeFr   =>$rangeFr,
                        rangeTo   =>$rangeTo,
                        availTotal=>$rec->{'availTotal'},
                        firstAvail=>$rec->{'firstAvail'}
                       };
    }
    $sth->finish;
    return \@rangeList;
}
#-------------------------------------------------------------------------------

sub bcm_getBiggestBcFromDB{
    my ($dbh) = @_;
    
#    my $sql = <<_STH_;
#select  max(barcode) as max
#from    opl_item  
#where barcode not regexp '^\_\_\_|^tmp_'
#_STH_
    my $sql = <<_STH_;
select   max(cast(barcode as UNSIGNED)) as max
from    opl_item  
where barcode not regexp '^\_\_\_|^tmp|temp' && cast(barcode as signed) >0
_STH_
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref) {
        return $rec->{'max'}
    }
    
    return ;
}
#-------------------------------------------------------------------------------

sub bcm_createNextBiggestBc{
     my ($dbh,$bc) = @_;
     my $prefix = "";
     my $bigBc = $bc;
        $bigBc =~ m/(^.*[\D]|^)([\d]+)$/;
        $prefix= $1;           
        
     my $tmp = $2;

     if($tmp=~ m/(^0+)(\d+)/){
        $tmp = $2;
        $prefix .=$1;
     }
     $bc = $prefix . ($tmp +1);

     return $bc;
}
#-------------------------------------------------------------------------------
sub bcm_alignTakenBc{
    my ($dbh) = @_;
    $dbh->do(<<_STH_);
update opl_bcmBc b inner join opl_item i using(barcode) 
set b.status='taken' , pending=-1 ,pendingFor=''
_STH_
 
}
#-------------------------------------------------------------------------------
sub bcm_getNextPendingBc_import{
    my ($dbh,$importId) = @_;
    my ($barcode) = $dbh->selectrow_array(<<_SQL_);
select      barcode  
from        opl_bcmBc   
where       status = 'available' &&  
            pending  = $importId  && pendingFor = 'import' 
order by    barcode limit 1 
_SQL_

   if(!$barcode){
    $barcode ='';
   }
   else{
       $dbh->do("update opl_bcmBc set status='taken' ,pending=-1 ,pendingFor='' where barcode='$barcode'");
   }
   return $barcode;
}
#-------------------------------------------------------------------------------
sub getTimeOut{
    my ($dbh) = @_;
    my $timeout = Opals::Context->preference('timeout');
    my $expr = {
            s => 'second',
            m => 'minute',
            h => 'hour',
            d => 'day',
            M => 'month',
            y => 'year',
     };
    if ($timeout =~ m/(\d+)([smhdMY])/) {
        if ($2) {
            $timeout = "$1 " . $expr->{$2};
        }
        else {
            $timeout = "5 minute";
        }
    }
    else {
        $timeout = "5 minute";
    }
   return $timeout;
}

#########################################################################
# Tue, Aug 14, 2012 @ 08:55:03 EDT
#
# return barcode depends on system preference setting  'barcodeType'
#  1: follett
#  2: spectrum
#  3: leading zero
#  4: Sagebrush
sub bcm_validateBc{
   my($dbh,$barcode,$barcodeType) =@_;
   if($barcodeType eq '1'){#follett
        $barcode  = bcm_validateFollettBarcode($dbh,$barcode );
    }
    elsif($barcodeType eq '2'){#spectrum
        $barcode  = bcm_validateSpectrumBarcode($dbh,$barcode );
    }
    elsif($barcodeType eq '3'){#leading zero
        $barcode  = bcm_validateLeadingZeroBc($dbh,$barcode );
    }
    elsif($barcodeType eq '4'){#Sagebrush
        $barcode  = bcm_validateSagebrushBarcode($dbh,$barcode );
    }

    return $barcode;
   
}
#########################################################################
# Tue, Aug 14, 2012 @ 08:55:03 EDT
#  FORMAT: (0nn)nnnn(0nn)
sub bcm_validateSagebrushBarcode{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
    my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");
    
    $sth->execute($bc) || return;
    my ($retBc) = $sth->fetchrow_array;
    if(!$retBc ||$retBc eq ''){
	$retBc =$bc;
        if($retBc =~ m/^0/gi){
            $retBc =~ s/^0+//g;
            $retBc =~ s/0\d\d$//g;
            $sth->execute($retBc) || return;
            ($retBc) = $sth->fetchrow_array;
        }
    }
    if(!$retBc ||$retBc eq ''){
        $retBc=$bc;
    }
    $sth->finish;
    return $retBc;
 
}
#########################################################################
# Wed, Sep 12, 2012 @ 08:36:19 EDT
# FORMAT: length:14
#         1st char:  TYPE 
#           (For Code 39 Mod 10 : 3 = library books, 4 = textbooks, and 2 = patrons OR
#            For Follett Classic: T = library books, X = textbooks, and P = patrons)
#         4   chars: INDICATOR LOCATION 
#         8   chars: CODE ITEM OR PATRON (these are numeric) 
#         1   char:  NUMBER CHECK DIGIT
#            (For Mod 10 this character is numeric (digits 0-9).
#             For Mod 43 this character is alphanumeric  (characters A-Z 
#                  plus the symbols “-“, “.”, blank space,“$”, ”/”, “+”, “%”)
#
sub bcm_validateFollettBarcode{
     my ($dbh,$bc) =@_;
     $bc =~ s/ +//g;

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

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


#########################################################################
# Added on 2007-09-13
#
# Move from Circulation module to here on
#           Tue, Aug 14, 2012 @ 08:55:03 EDT
sub bcm_validateFollettBarcode_classic{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
   my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");
    
    $sth->execute($bc) || return;
    my ($retBc) = $sth->fetchrow_array;
    if(!$retBc ||$retBc eq ''){
        my $fBc =folletize($bc);
        $sth->execute($fBc) || return;
        ($retBc) = $sth->fetchrow_array;

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

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

#-----------------------------------------------------
sub folletize{
    my ($bc)=@_;

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

return $bc;
}
#-----------------------------------------------------
# added on 2007-10-05

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


#########################################################################
# Added on Fri, Nov 09, 2007 @ 09:11:17 EST
# Move from Circulation module to here on
#           Tue, Aug 14, 2012 @ 08:55:03 EDT
#Tue, Aug 14, 2012 @ 08:55:03 EDT
#
sub bcm_validateSpectrumBarcode{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
   
    my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");
    
    $sth->execute($bc) || return;
    my ($retBc) = $sth->fetchrow_array;
    if(!$retBc ||$retBc eq ''){
        $retBc =deSpectrumize($bc);
        $sth->execute($retBc) || return;
        ($retBc) = $sth->fetchrow_array;

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

sub deSpectrumize{
    my ($bc)=@_;
    my $bcLen=length($bc);
    if($bcLen ==14 || $bcLen ==13){
        $bc = substr $bc,5,8;
        $bc =~ s/^0+//g;
    }
    elsif($bcLen ==8 || $bcLen ==7 ){
        $bc = substr $bc,0,$bcLen -1;
        $bc =~ s/^0+//g;
    }   
    elsif($bcLen <7 ){
        $bc = sprintf("%08d", $bc);
    }

    return $bc;
}




#########################################################################
# Added on  Tue, Sep 01, 2009 @ 11:10:31 EDT
#
# Move from Circulation module to here on
#           Tue, Aug 14, 2012 @ 08:55:03 EDT
sub bcm_validateLeadingZeroBc{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
   
    my $sth = $dbh->prepare("select barcode from opl_item where barcode=?");
    
    $sth->execute($bc) || return;
    my ($retBc) = $sth->fetchrow_array;
    if(!$retBc ||$retBc eq ''){
        $retBc =$bc;
        $retBc =~ s/^0+//g;
        $sth->execute($retBc) || return;
        ($retBc) = $sth->fetchrow_array;

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



1;