#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;use POSIX qw(
    floor
);

use Time::localtime;
use Opals::Constant;

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
    tmpl_preference
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);


my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'txtbk/report/barcodeSystemMnt.tmpl',
            reqPermission   => 'tb_report',
        }
);
my $syspref = tmpl_preference($dbh);

my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
  
my $op   = $input->{'op'};
my $vid  = $input->{'vid'};
my $rid  = $input->{'rid'};

if(!defined $input->{'rid'} || $rid eq ''){
    $rid=0;
}

my $filter = $input->{'bcStatusFilter'};
my ($vendor,$bcLen,$preFix,$rangeFr,$rangeTo);
   
    my $pSize = 25;
    my $curPage = $input->{'pNum'};
    ($curPage && $curPage >= 1) || ($curPage = 1);
    my $offset = ($curPage - 1) * $pSize ;

#---------------------- ADD NEW --------------------------------------------
    my $sql_insert_bcmVendor=" insert into     tb_bcmVendor 
set  vendor = ?
";
    
    my $sql_insert_bcmRange= " insert into     tb_bcmRange 
set  vid    = ?,
     bcLen  = ?,
     preFix = ?,
     rangeFr= ?,
     rangeTo= ?  
";
    
    my $sql_insert_bcmBc= " insert into     tb_bcmBc
set  rid    = ?,
     barcode = ?
";
#---------------------- UPDATE --------------------------------------------
    my $sql_update_bcmVendor ="update   tb_bcmVendor 
set      vendor = ?
where    vid    = ?";


    my $sql_update_bcmRange = "update  tb_bcmRange 
set     bcLen  = ?,
        preFix = ?,
        rangeFr= ?,
        rangeTo= ?
where vid =? && rid =?          
";
   
#---------------------- DELETE --------------------------------------------

    my $sql_del_bcmVendor ="delete from tb_bcmVendor 
where    vid    = ?";

    my $sql_del_bcmRange = "delete from tb_bcmRange 
where  rid =?          
";
    my $sql_del_bcmBc = "delete from tb_bcmBc 
where rid =?          
";
if ($permission && $permission->{'tb_report'}) {
    if($op eq 'delete'){
        my $sth = $dbh->prepare($sql_del_bcmVendor);
        $sth->execute($vid);
        $sth->finish;
        
        $sth = $dbh->prepare($sql_del_bcmRange);
        $sth->execute($rid);
        $sth->finish;
    }
    elsif($op eq 'edit'){
        $vendor   = $input->{'ipVendor'}; 
        $bcLen    = $input->{'ipBcLen'}; 
        $preFix   = $input->{'ipPref'}; 
        $rangeFr  = $input->{'ipRangFr'}; 
        $rangeTo  = $input->{'ipRangTo'}; 
        updateBcRange($dbh,$vendor,$bcLen,$preFix,$rangeFr,$rangeTo,$vid,$rid);
        tb_bcm_alignTakenBc($dbh);
    }
    elsif($op eq 'add'){
        $vendor   = $input->{'ipVendor'}; 
        $bcLen    = $input->{'ipBcLen'}; 
        $preFix   = $input->{'ipPref'}; 
        $rangeFr  = $input->{'ipRangFr'}; 
        $rangeTo  = $input->{'ipRangTo'}; 
        addNewBcRange($dbh,$vendor,$bcLen,$preFix,$rangeFr,$rangeTo);
        tb_bcm_alignTakenBc($dbh);
    }
    elsif($filter =~ m/all|taken|available/){
        my $numBc=0; 
        my $vendorList = getVendorList($dbh,$vid,$rid,$filter);
        if(scalar(@$vendorList) ==1){
            my $bcList =getBcListByStatus($dbh,$rid,$filter,$offset,$pSize);
            if($filter eq 'all'){
                $numBc = @$vendorList[0]->{'total'};
            }
            elsif($filter eq 'taken'){
                $numBc = @$vendorList[0]->{'takenTotal'};
            }
            else{
                $numBc = @$vendorList[0]->{'availTotal'};
            }
            if ($numBc - $offset + 1 <= 0) {
                my $availRange = $numBc % $pSize;
                $availRange = $pSize if ($availRange == 0); 
                $offset = $numBc - $availRange + 1;
                $curPage = ($offset - 1) / $pSize + 1;
            }
            my $pRange = 5;
            my @rangedPageList = tmpl_rangedPageList($numBc, $curPage, $pSize, $pRange);
            my $padLen=@$vendorList[0]->{'bcLen'} - length(@$vendorList[0]->{'preFix'});
            $template->param(
                vendor_name     =>@$vendorList[0]->{'vendor'},
                vendor_bcLen    =>@$vendorList[0]->{'bcLen'},
                vendor_prefix   =>@$vendorList[0]->{'preFix'},
                vendor_bcFr     =>sprintf("%s%0*d",@$vendorList[0]->{'preFix'},$padLen,@$vendorList[0]->{'rangeFr'}),
                vendor_bcTo     =>sprintf("%s%0*d",@$vendorList[0]->{'preFix'},$padLen,@$vendorList[0]->{'rangeTo'}),
                vendor_total    =>@$vendorList[0]->{'total'},
                vendor_bcTaken  =>@$vendorList[0]->{'takenTotal'},
                vendor_bcAvail  =>@$vendorList[0]->{'availTotal'},
                rangedPageList  => \@rangedPageList,
                bcList          => $bcList,
                isFilter        =>1 , 
                filter          => ($filter eq 'taken')? 'used': $filter,
                numBc           => $numBc,
                pNum            => $curPage,
                bcListing       =>1,
                rid             =>$rid,
                bcStatusFilter  =>$filter
            );
            if ($filter eq 'taken'){
            $template->param(taken => 1);
            }
        }
    }
    if(!defined $filter || $filter eq ''){
        my $vendorList = getVendorList($dbh,$vid,undef);
        $template->param(vendorList =>$vendorList ,
                         vid        =>$vid); 
    }
    $template->param(op =>$op ); 
}

#Tue, Jan 12, 2010 @ 10:31:41 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
loc_write($template,$msgMap);

tmpl_write($dbh, $cgi, $cookie, $template);

################################################################################
sub addBarcodeListByRange{
    my ($dbh,$rid,$bcLen,$preFix,$rangeFr,$rangeTo) =@_;
    my $bc='';
    my $sth;
    for(my $i=$rangeFr;$i <=$rangeTo;$i++){
        my $padLen=$bcLen - length($preFix);
        $bc = sprintf("%s%0*d",$preFix,$padLen,$i);
        $sth = $dbh->prepare($sql_insert_bcmBc);
        $sth->execute($rid,$bc);
    }
    $sth->finish;
}
################################################################################
sub addNewBcRange{
    my ($dbh,$vendor,$bcLen,$preFix,$rangeFr,$rangeTo) =@_;
    my ($vid) = $dbh->selectrow_array("select vid from tb_bcmVendor where vendor='$vendor'");
    if(!$vid || $vid==0){
        my $sth = $dbh->prepare($sql_insert_bcmVendor);
        my $result = $sth->execute($vendor);
        if($result eq '0E0' ) {
            return;
        }
        $vid = $dbh->{'mysql_insertid'};
    }
         
    my $sth = $dbh->prepare($sql_insert_bcmRange);
    $sth->execute($vid,$bcLen,$preFix,$rangeFr,$rangeTo);
    my $rid = $dbh->{'mysql_insertid'};

    if($rid =='' || !$rid || $rid ==0) {
        return;
    }
    addBarcodeListByRange($dbh,$rid,$bcLen,$preFix,$rangeFr,$rangeTo);
    $sth->finish;
    
}    
################################################################################
sub updateBcRange{
    my ($dbh,$vendor,$bcLen,$preFix,$rangeFr,$rangeTo,$vid,$rid) =@_;
   
    my $sth = $dbh->prepare($sql_update_bcmVendor);
    $sth->execute($vendor,$vid);
    $sth->finish;

    if($rid==0){
        $sth = $dbh->prepare($sql_insert_bcmRange);
        $sth->execute($vid,$bcLen,$preFix,$rangeFr,$rangeTo);
        my $rid = $dbh->{'mysql_insertid'};
        addBarcodeListByRange($dbh,$rid,$bcLen,$preFix,$rangeFr,$rangeTo);
        $sth->finish;
    }
    else{
        $sth = $dbh->prepare($sql_update_bcmRange);
        $sth->execute($bcLen,$preFix,$rangeFr,$rangeTo,$vid,$rid);
      
        $sth = $dbh->prepare($sql_del_bcmBc);
        $sth->execute($rid);
        $sth->finish;
       
        addBarcodeListByRange($dbh,$rid,$bcLen,$preFix,$rangeFr,$rangeTo);
        $sth->finish;
    }
    
}    
################################################################################

sub 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,
          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      tb_bcmVendor v left outer join tb_bcmRange r on v.vid=r.vid 
          inner join tb_bcmBc s on s.rid = r.rid  
_STH_

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);
    $sth->execute();
    my  @recordList =();
    while (my $rec = $sth->fetchrow_hashref) {
        push @recordList,$rec;
    }
    $sth->finish;
    return \@recordList;
}

################################################################################
#Mon, Feb 14, 2011 @ 10:56:06 EST
sub getMaxUsedBarcode{
    my ($dbh,$rid) = @_;
    my ($maxBc) = $dbh->selectrow_array(<<_SQL_);
select max(b.barcode)  from tb_bcmBc b inner join tb_item i using(barcode)   where status='taken' && b.rid= $rid 
_SQL_
 
   if(!$maxBc){
    $maxBc ='';
   }
    return $maxBc;
}

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

sub getBcListByStatus{
    my($dbh,$rid,$status,$offset,$size)=@_;
    
    $offset=0 if(!defined $offset);
    $size=25 if(!defined $size);
    my $bcList=[];
    my $sql="select barcode from tb_bcmBc where rid=$rid ";
    $sql .=" && status='$status' " if(defined $status && $status ne 'all' && $status ne '');
    $sql .=" order by barcode limit $offset,$size";
    if($status eq 'taken'){
        $sql="select b.barcode,r.fVal as title from tb_bcmBc b 
                     inner join tb_items i using(barcode) 
                     inner join tb_records r on r.rid =i.rid  && r.fId='245_a'
                 where b.rid=$rid ";
        $sql .=" && status='$status' ";
        $sql .=" order by b.barcode desc limit $offset,$size";
    
    }
    my $sth=$dbh->prepare($sql);
    $sth->execute();
    my $i=1;

    my $odd_row = 0;
    while(my $bc=$sth->fetchrow_hashref()){
        if($status eq 'taken'){
            $bc->{'odd'} = ($odd_row ^= 1);
        }
        else{
            if($i++%5 == 0){
                $bc->{'newRow'}=1;
                $bc->{'odd'} = ($odd_row ^= 1);
            }
        }
        push @$bcList,$bc;
       
    }
    return $bcList;
}

sub tb_bcm_alignTakenBc{
    my ($dbh) = @_;
    $dbh->do(<<_STH_);
update tb_bcmBc b inner join tb_items i using(barcode) 
set b.status='taken' , pending=-1 ,pendingFor=''
_STH_
 
}