#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

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

use Time::localtime;

use Digest::SHA qw(
    sha512_hex
);


use Opals::Constant;

use Opals::User qw(
    user_currentUser
    user_getInformationById
);

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Eq_Inventory qw(

    inv_inventory
    inv_closeInventory
    inv_cleanupInventory
    inv_getInventoryByStatus
    inv_getInventoryStatusByBc
    inv_mark_Returned 
    
);

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

my $cgi = CGI->new;
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, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'eqmnt/report/inventory' . $ext . '.tmpl',
            reqPermission   => 'eq_report',
        }
);

    my $source  = $input->{'source'};
    my $invId   = $input->{'invId'};
    my $fname   = $input->{'bcFile'};
    my $fdata   = $cgi->param('bcFile');
    my $bc      = $input->{'bc'};
    my $fid     = $input->{'openFid'};
    my $pNum    = $input->{'pNum'} || 1;
    my $pSize   = $input->{'pSize'} || 10;
    my $list    = $input->{'list'};
    my $error;

    my ($errCode, $myCookie, $user) = user_currentUser($dbh, $cgi);
    my $uid = $user->{'uid'};
    if ($op =~ m/^add$/i) {
        if (!$invId || $invId eq ''){
            addInventoryInfo($dbh, $uid);
            #get last scannning invId
            if (!$invId) {
                ($invId) = $dbh->selectrow_array(<<_STH_);
select invId from eq_inventoryInfo 
where uid = $uid && status = 'scanning' order by invId desc limit 0,1
_STH_
            }
        }
        ($fid, $error, $pNum) = add($dbh,$cgi,$input,$invId, $uid);
    }
    elsif( $op =~ m/^remove$/i) {
        remove($dbh,{type=>$input->{'type'},list=>$list,invId=>$invId});
    }
    elsif ($op =~ m/^inventory$/i) {
        my $sth_update = $dbh->prepare(<<_STH_);
update eq_inventoryInfo set status = ? where invId = ?
_STH_
    $sth_update->execute('waiting', $invId);
    inv_inventory($dbh);
    }
    elsif ( $op =~ m/^mark$/i) {
        mark($dbh, $input, $cgi);
        $op = 'view';
    }
    elsif ($op =~ m/^new$/) {
        inv_cleanupInventory($dbh,$uid);
        $template->param(
            newInv => 1,
        );
    }
    elsif ( $op =~ m/^close$/i) {
        inv_closeInventory($dbh,$invId);
    }
    
        
    my $status = $input->{'status'};
    if ($op =~ m/^view$/i) {
        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, $pNum,$pSize);
        my ($pageList, $pageNum) = getPageListByStatus($dbh,$invId, $status, $pNum, $pSize);
        $template->param(
            holding         => $holding,  
            rangedPageList  => $pageList,
            pageNum         => $pageNum,
            #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
            );
        }
        
        $template->param (
            scanner         => ($source =~ m/^scanner/i) ? 1:0,
            invId           => $invId,
            err_count       => $error->{'err_count'},
            err_add_file    => $error->{'err_add_file'},
            err_add_scanner => $error->{'err_add_scanner'},
            openFid         => $fid,
            url_eq_inventoryList=>'/bin/eqmnt/report/inventoryList'
        );
  
        listInfo($dbh, $uid, $invId, $fid, $pNum, $pSize, $template);
    }
    
    $template->param (
        invId => $invId
    ); 

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

sub  addInventoryInfo {
    my ($dbh, $uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
insert into eq_inventoryInfo set uid = ?
_STH_
     $sth->execute($uid);
     $sth->finish;
}

sub add {

    my ($dbh, $cgi, $input, $invId, $uid) = @_;
    my ($fid, $error, $pageNum);
    my $source = $input->{'source'};
    my $fdata   = $cgi->param('bcFile');
    my $fname = $input->{'bcFile'};

    if ($source =~ /^file$/i) {
        $fid = addBCFromFile($dbh,{uid=>$uid,fname=>$fname, fdata=>$fdata,invId=>$invId});
        if (!$fid) {
            $error->{'err_add_file'} = $fname;
            $error->{'err_count'} = 1;
        }
    }
    elsif ($source =~ m/^scanner$/i) {
        my $barcodeList = $input->{'bc'};
        $barcodeList =~ s/\s+/,/g;
        $barcodeList =~ s/,+/,/g;
        $barcodeList =~ s/(^,|,$)//;
        $fid = addBCFromScanner($dbh,{uid=>$uid,barcodeList=>$barcodeList,invId=>$invId});    
        if (!$fid){
            $error->{'err_add_scanner'} = $barcodeList;
            $error->{'err_count'} = 1;
        }
    }
    return ($fid, $error, $pageNum);
}

sub addBCFromScanner {
    
     my ($dbh, $params ) = @_;
     my $fid=getManualScanFid($dbh, $params->{'uid'}, $params->{'invId'});
     $params->{'fid'} =  $fid;
     return addBarcode($dbh,$params);   
}

sub addBCFromFile {
    my ($dbh, $params) = @_;
    my ($barcodeList, $bc) = ('','');
    my $fdata = $params->{'fdata'};
    while ($bc = <$fdata>) {
        $bc =~ s/[\x0a\x0d]//g;
        $bc =~ s/^\s+|\s+$//g;
        if ($bc =~ m/^(tmp|dup|__)_/i) {
            next;
        }
        $barcodeList .= $bc . ',';
    }
    $barcodeList =~ s/,+/,/g;
    $barcodeList =~ s/(^,|,$)//;
    if (!$barcodeList) {
        return;
    }
    my $mesgDigest = sha512_hex($barcodeList . $invId);
    my $sth = $dbh->prepare(<<_STH_);
insert  into eq_inventoryFile
set     fname      = ?,
        uid        = ?,
        invId      = ?,
        mesgDigest = ?
_STH_

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

    return addBarcode($dbh,{fid=>$fid,uid=>$uid,barcodeList=>$barcodeList,invId=>$invId});
}

################################################################################
# addBarcode
# params : # fid, uid, barcodeList, invId
################################################################################

sub addBarcode {
    my ($dbh, $params ) = @_;
    return undef  if (!$params->{'barcodeList'});

    my $sth = $dbh->prepare(<<_STH_);
delete from eq_inventory
where  fid     = 0 &&
          invId   = ? &&
          status regExp 'missing' &&
          updatedStatus not regExp 'missing'
           
_STH_

    $sth->execute($params->{'invId'});

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

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

    my @barcodes = split /,/, $params->{'barcodeList'};
    foreach my $bc (@barcodes) {
        if ($sth_update->execute($params->{'fid'},$params->{'invId'},$bc)) {
            $sth_insert->execute($params->{'fid'},$params->{'invId'},$bc);
        }
    }
    $sth->finish;
    $sth_update->finish;
    $sth_insert->finish;

    return $params->{'fid'};
}


sub getManualScanFid {
    my ($dbh, $uid, $invId) = @_;
    my $fid = $dbh->selectrow_array(<<_STH_);
select  fid
from    eq_inventoryFile
where   fname = '' &&
        uid = $uid &&
        invId= $invId
_STH_

    if (!$fid) {
        $dbh->do(<<_STH_);
insert into eq_inventoryFile
set     uid = $uid,
        invId = $invId
_STH_
        $fid = $dbh->{'mysql_insertid'};
    }
    return $fid;
}

sub getInputByInvId {

    my ($dbh, $fid, $invId) = @_;
    my $total = 0;
    my ($user, $dummy);
    my (@file, @scanner) = ((),());
    my $sth = $dbh->prepare(<<_STH_);
select * 
from    eq_inventoryFile
where   invId = ?
_STH_

    my $sth_count = $dbh->prepare(<<_STH_);
select count(*)
from    eq_inventory
where   fid = ? &&
        invId   = ?
_STH_
    $sth->execute($invId);
    
    while(my $f = $sth->fetchrow_hashref) {
        $f->{'isOpen'} = ($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;
        }
    }
    my @inFile = (@scanner, @file);
    return (\@inFile, $total);

}

sub listInfo {
    my ($dbh, $uid, $invId, $fid, $pNum, $pSize, $template) = @_;
    
    my ($inputFile, $total_scanned)  = getInputByInvId($dbh, $fid, $invId);
    my $pageList;
    ($pageList, $pNum)  = getPageListByFid($dbh, $fid, $pNum, $pSize,$invId);
    my @inputFileData   = getInputFileData($dbh, $fid, $pNum, $pSize,$invId);

    $template->param(
        total_scanned   => $total_scanned,
        inputFile       => $inputFile,
        rangedPageList  => $pageList,
        inputFileData   => \@inputFileData,
    );

    my ($total_active, $total_loan,$total_validated, $dateInventory) = getInfo($dbh,$invId);
    $template->param(
        total_active    => $total_active,
        total_loan      => $total_loan,
        total_validated => $total_validated,
        dateInventory   => $dateInventory
    );

    my $result = getResult($dbh, $invId );
    foreach my $r (keys %{$result}) {
        $template->param(
            "result_$r" => $result->{$r},
        );
    }
    if ( $total_scanned){
        my $unknownLimit = $total_active - $total_scanned;
        $template->param(
            isReady      => ($unknownLimit <= 0) ?1:0,
            unknownLimit => $unknownLimit,
        );
    }
}

sub getPageListByFid {
    
    my ($dbh, $fid, $pageNum, $pageSize,$invId) = @_;
    my $query = 'select count(*) from eq_inventory';
    $query .= ' where invId = ' . $invId;
    if ($fid && $fid =~ m/^[\d]+$/) {
        $query .= ' && fid = ' . $fid;
    }
    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 getInputFileData {
    
    my ($dbh, $fid, $pageNum, $pageSize,$invId) = @_;
    if($pageNum <1){
        $pageNum=1;
    }
    my $pageOffset = $pageSize * ($pageNum - 1);

    my $query ="select  * from  eq_inventory ";

    if (!$fid || $fid =~ m/[\D]/) {
        $query .= " where fid > 0";
    }
    else {
        $query .= " where fid = $fid ";
    }
    $query .= " && invId = $invId" ;
    $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 getInfo {

    my ($dbh, $invId) = @_;

    my ($total_active) = $dbh->selectrow_array(<<_STH_);
select  count(*) 
from    eq_items
where   available = 1 &&
        barcode not regexp '^(___)_' 
_STH_
# barcode not regexp '^(tmp|dup|___)_'
    my ($total_loan) = $dbh->selectrow_array(<<_STH_);
select  count(*) 
from    eq_loan as l
inner join eq_items as i using(barcode)
where   l.dateReturn is null
_STH_
 
    my ($dateInventory) = $dbh->selectrow_array(<<_STH_); 
select invDate
from eq_inventoryInfo
where invId = $invId
_STH_

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

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

}

sub getResult {
    my ($dbh,$invId) = @_;
    my $result;
    my $sth = $dbh->prepare(<<_STH_);
select  count(*)
from    eq_inventory
where   status regexp ? &&
        invId   = ? 
_STH_
    my $sth_update = $dbh->prepare(<<_STH_);
select  count(*)
from    eq_inventory
where   updatedStatus regexp ? &&
        invId   = ? 
_STH_
     my ($isStatus) = $dbh->selectrow_array(<<_STH_);
select  status
from    eq_inventoryInfo
where   invId   = $invId 
_STH_
   if($isStatus && $isStatus =~ m/done/i){
        $template->param(
            result => 1,
        );
    }
    my @status = qw(damaged found missing loan invalid );
    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    eq_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 remove {

    my ($dbh, $params) = @_;
    
    if (!$params->{'type'}){
        return;
    }
    my $type = $params->{'type'};
    my $sth;    

    if ($type =~ m/^all$/i){
        $dbh->do(<<_STH_);
delete from eq_inventory where invId = $params->{'invId'}
_STH_
        $dbh->do(<<_STH_);
delete from eq_inventoryFile where invId = $params->{'invId'}
_STH_
    }
    if (!$params->{'list'} || $params->{'type'} !~ m/fid|barcode/i){
        return;
    }
    if ($type =~ m/^fid$/i){
         $sth= $dbh->prepare(<<_STH_);
delete from eq_inventoryFile where fid = ? && invId = ?    
_STH_
    }
    else {
        $sth= $dbh->prepare(<<_STH_);
delete from eq_inventory where barcode = ? && invId = ?    
_STH_
    }

    foreach my $item (split/,/, $params->{'list'}){
        $sth->execute( $item, $params->{'invId'});
        if ($type =~ m/^fid$/i){
            $dbh->do(<<_STH_);
delete from eq_inventory where fid = $item
_STH_
        }
    }
    $sth->finish;
    $dbh->do(<<_STH_);
update  eq_inventoryInfo
set     status = CONCAT_WS(',',status,'scanning')
where   invId = $invId
_STH_

}

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

    my ($count) = $dbh->selectrow_array(<<_STH_);
select count(*) from eq_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, 5);

    return (\@pageList, $pageNum);
}


sub mark {

    my ($dbh, $input, $cgi) = @_;
    my $status = $input->{'status'};
    my $invId = $input->{'invId'};

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

    my @barcode;
    if ($input->{'selection'} =~ m/^all$/i) {
        @barcode = ('__ALL__')
    }
    else {
        foreach my $bc ($cgi->param ('barcode')) {
            if ($bc =~ m/^(update|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);
    }
    else{
        mark_Returned($dbh,$status,\@barcode,$invId);
    }
}

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    eq_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   eq_inventory
where invId=? && barcode=? && status regexp ? && updatedStatus not regexp ?
_STH_
    my $sth_status = $dbh->prepare(<<_STH_);
insert into eq_itemStatus
set     barcode = ?,
        ondate  = now(),
        status  = ?
_STH_
     my $sth_found = $dbh->prepare(<<_STH_);
insert into eq_found
set     barcode = ?,
        ondate  = now()
_STH_
    my $sth_item = $dbh->prepare(<<_STH_);
update  eq_items
set     available = ?
where   barcode   = ?
_STH_
    my $sth_update = $dbh->prepare(<<_STH_);
update  eq_inventory
set     updatedStatus = CONCAT_WS(',', updatedStatus, ?)
where   barcode       = ? &&
        status        regexp ?  &&
        invId         = ?
_STH_
    $sth->execute($invId,$barcode,$status,$status);
    if(my $rec=$sth->fetchrow_hashref) {  
        $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 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    eq_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));
}


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

sub removeMissing {

    my ($dbh, $uid, $invId, $input, $cgi) = @_;
    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 eq_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;
    }
}


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/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;
            }
        }   
     }
   
    return \@holding;

}