package Opals::Eq_BarcodeMgmt;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(

    eq_bcm_getVendorList

    eq_bcm_createNextBiggestBc
    eq_bcm_getBiggestBcFromDB
    eq_bcm_alignTakenBc

    eq_bcm_validateBc
    eq_bcm_validateFollettBarcode
    eq_bcm_validateSpectrumBarcode
    eq_bcm_validateLeadingZeroBc
    eq_bcm_validateSagebrushBarcode

);

# Version number
$VERSION   = 0.01;      

use strict;
use Encode;

sub eq_bcm_getVendorList {
    my ($dbh,$vid,$rid,$filter) = @_;
    
    my $sql = <<_STH_;
select    v.vid vid,vendor, r.rid as rid,bcLen,preFix,rangeFr,rangeTo,
          count(barcode) as total, min(s.barcode) as firstAvail,
          sum(IF(s.status = 'available',1,0))  availTotal,
          sum(IF(s.status = 'taken',1,0))  takenTotal,
          sum(IF(s.status regexp 'pending',1,0))  pendingTotal  
from      eq_bcmVendor v left outer join eq_bcmRange r on v.vid=r.vid 
          inner join eq_bcmBc s on s.rid = r.rid 
_STH_
    
    my $sql_firstAvail = <<_SQL_;
select  min(s.barcode) 
from    eq_bcmBc s inner join eq_bcmRange r using(rid) inner join eq_bcmVendor v using(vid) 
where s.status='available' && s.rid=? 
_SQL_


my $cond ='';
if(defined $rid && !($rid eq '' ||  $rid < 0 ||  $rid eq '0')){
    $cond .=' r.rid='. $rid  ;
}
if(defined $cond && $cond ne''){
    $sql .= " where " . $cond ;
}
    $sql .=' group by r.rid  order by  vid,rid';
  
    my $sth = $dbh->prepare($sql);
    my $sth_firstAvail = $dbh->prepare($sql_firstAvail);
    $sth->execute();
    my  @recordList =();
    while (my $rec = $sth->fetchrow_hashref) {
        $sth_firstAvail->execute($rec->{'rid'});
        $rec->{'firstAvail'} = $sth_firstAvail->fetchrow_array;
        push @recordList,$rec;
    }
    $sth->finish;
    $sth_firstAvail->finish;
    return \@recordList;
          
}

# return barcode depends on system preference setting  'barcodeType'
#  1: follett
#  2: spectrum
#  3: leading zero
#  4: Sagebrush
#
sub eq_bcm_validateBc{
   my($dbh,$barcode,$barcodeType) =@_;
   if($barcodeType eq '1'){#follett
        $barcode  = eq_bcm_validateFollettBarcode($dbh,$barcode );
    }
    elsif($barcodeType eq '2'){#spectrum
        $barcode  = eq_bcm_validateSpectrumBarcode($dbh,$barcode );
    }
    elsif($barcodeType eq '3'){#leading zero
        $barcode  = eq_bcm_validateLeadingZeroBc($dbh,$barcode );
    }
    elsif($barcodeType eq '4'){#Sagebrush
        $barcode  = eq_bcm_validateSagebrushBarcode($dbh,$barcode );
    }
    return $barcode;
   
}

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

    my $sql = <<_STH_;
select   max(cast(barcode as UNSIGNED)) as max
from    eq_items
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 eq_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 eq_bcm_alignTakenBc {
    my ($dbh) = @_;
    $dbh->do(<<_STH_);
update eq_bcmBc b inner join eq_items i using(barcode) 
set b.status='taken' , pending=-1 ,pendingFor=''
_STH_
 
}

#########################################################################
# Tue, Aug 14, 2012 @ 08:55:03 EDT
#  FORMAT: (0nn)nnnn(0nn)
sub eq_bcm_validateSagebrushBarcode{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
    my $sth = $dbh->prepare("select barcode from eq_items 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 eq_bcm_validateFollettBarcode{
     my ($dbh,$bc) =@_;
     $bc =~ s/ +//g;

    my $sth = $dbh->prepare("select barcode from eq_items 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 eq_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 eq_bcm_validateFollettBarcode_classic{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
   my $sth = $dbh->prepare("select barcode from eq_items 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 eq_bcm_validateSpectrumBarcode{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
   
    my $sth = $dbh->prepare("select barcode from eq_items 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 eq_bcm_validateLeadingZeroBc{
    my ($dbh,$bc) =@_;
    $bc =~ s/ +//g;
   
    my $sth = $dbh->prepare("select barcode from eq_items 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;