package Opals::Eq_Inventory;

require Exporter;
@ISA       = qw(Exporter);
@EXPORT_OK = qw(
    
    inv_inventory
    inv_closeInventory
    inv_getInventory 
    inv_deleteInventory
    inv_getInventoryByStatus
    inv_getInventoryStatusByBc
    inv_getInventoryStatusByBcList
    inv_mark_Returned
    inv_cleanupInventory
);

use Opals::Constant;

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

sub inv_inventory {

    my ($dbh) = @_;
    
    my ($uid, $invId) = $dbh->selectrow_array(<<_STH_);
select  uid, invId
from    eq_inventoryInfo
where   status = 'waiting'
order by invId desc limit 0,1
_STH_

    my $sth_update = $dbh->prepare(<<_STH_);
update  eq_inventoryInfo
set     status = ?
where   invId   = ?
_STH_
    
    $sth_update->execute('processing', $invId);
    preInventory($dbh,$uid,$invId);
    inventory($dbh,$invId);
    $sth_update->execute('done', $invId);
}

sub preInventory {

    my ($dbh, $uid, $invId) = @_;
    my $sth_del = $dbh->prepare(<<_STH_);
delete  from eq_inventory
where   fid = 0 &&
        status = 'missing' && invId = ?
_STH_
    $sth_del->execute($invId);

    my $sth = $dbh->prepare(<<_STH_);
update  eq_inventory
set     status = 'active',
        noteDetail  = null
where   invId = ?        
_STH_

    $sth->execute($invId);
    $sth->finish;
    $sth_del->finish;
    
}

sub inventory {

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

#case invalid barcode
    my $sth_invalid = $dbh->prepare(<<_STH_);
update  eq_inventory as a left outer join eq_items as b using(barcode)
        set a.status = 'invalid'
where   b.barcode is null && a.invId = ?
_STH_
    $sth_invalid->execute($invId);

#case missing
    inventory_missing($dbh,$invId );

#case loan but in equipment room  
    my $sth_loan = $dbh->prepare(<<_STH_);
update   eq_inventory as a inner join eq_loan as l on a.barcode= l.barcode
set      a.status    = CONCAT_WS(',', a.status, 'loan'),
         noteDetail  = CONCAT_WS(',',noteDetail,CONCAT('idloan:',l.id) )
where    l.dateReturn  is null   &&
         a.status not regExp 'missing'&&
         a.invId     = ?
_STH_
   $sth_loan->execute($invId);

 # CASE Found #####invId ???
  my $sth_found = $dbh->prepare(<<_STH_);
update  eq_inventory as a inner join eq_itemStatus as b on a.barcode= b.barcode
        left outer join eq_itemStatus c on b.barcode=c.barcode && b.id  < c.id 
set     a.status = CONCAT_WS(',' , a.status, 'found')	
where 	a.status not regExp 'missing'&&
        c.id  is null &&
        (b.status     =  3 || b.status     = 0 ) &&
        invId        = ?
_STH_
   $sth_found->execute($invId);
# CASE  Damage 
  my $sth_damage = $dbh->prepare(<<_STH_);
update  eq_inventory as a inner join eq_itemStatus as b on a.barcode= b.barcode
        left outer join eq_itemStatus c on b.barcode=c.barcode && b.id  < c.id 
set     a.status = CONCAT_WS(',' , a.status, 'damaged')	
where 	a.status not regExp 'missing'&&
        c.id  is null &&
        b.status     = 2  &&
        invId        = ?
_STH_
    $sth_damage->execute($invId);

    $sth_invalid->finish;
    $sth_loan->finish;
    $sth_found->finish;
    $sth_damage->finish;
}

sub inventory_missing {

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

    my @loanMissing = ();
    my @missing = ();
    my $sth_onloan_missing = $dbh->prepare(<<_STH_);
select ucase(barcode) as barcode from eq_loan where dateReturn is null
union 
select distinct ucase(barcode) as barcode from eq_items where available = 0 && barcode not regexp '^\_\_\_'
order by barcode
_STH_
#get items on loan or items status is missing ,lost or damaged
   $sth_onloan_missing->execute;
   while (my ($bc) = $sth_onloan_missing->fetchrow_array()){
         push @loanMissing,$bc;
   }
   $sth_onloan_missing->finish;
   @loanMissing=sort(@loanMissing);
#get possible missing barcodes
   my $sth = $dbh->prepare(<<_STH_);
select  distinct ucase(i.barcode) as barcode
from    eq_items i left outer join eq_inventory v on i.barcode = v.barcode && v.invId = ?
where   i.barcode not regexp '^\_\_\_' && v.barcode is null
        order by i.barcode
_STH_

    $sth->execute($invId);
    my $n=scalar(@loanMissing);
    my $i = 0;
    while ( my $h = $sth->fetchrow_hashref) {
        while( $i < $n && $h->{'barcode'} gt @loanMissing[$i] ) {
            $i++;
        }
        if($i >= $n || $h->{'barcode'} lt @loanMissing[$i] ){
            push @missing,$h;
        }

    }
    my $sth_missing = $dbh->prepare(<<_STH_);
insert into eq_inventory
    set invId =?,
    barcode =? ,
    status ='missing'
_STH_
    foreach my $h (@missing){
        $sth_missing->execute($invId,$h->{'barcode'});
    }
    $sth->finish;
    $sth_missing->finish;
}

sub inv_getInventory {

    my ($dbh) = @_;

    my $sth = $dbh->prepare(<<_STH_);
select *
from eq_inventoryInfo
order by invId desc
_STH_
    my @invList;
    $sth->execute;
    while ( my $inv = $sth->fetchrow_hashref) {
        if ($inv->{'status'} eq 'closed'){
            $inv->{'closed'} = 1;
        }
        elsif ($inv->{'status'} eq 'scanning'){
            $inv->{'scanning'} = 1;
        }
        elsif ($inv->{'status'} eq 'waiting'){
            $inv->{'waiting'} = 1;
        }
        elsif ($inv->{'status'} eq 'done'){
            $inv->{'done'} = 1;
        }
        push @invList, $inv;
    }
    $sth->finish;
    return @invList; 
}


############################################################
sub inv_closeInventory{
    my ($dbh, $invId) = @_;

    my $sth_update = $dbh->prepare(<<_STH_);
update   eq_inventoryInfo
set      status = 'closed'
where    invId  = ? 
_STH_
       
    $sth_update->execute($invId);

    $sth_update->finish;

    return; 
}

sub inv_cleanupInventory{
    my ($dbh,$uid) = @_;
    my $sth = $dbh->prepare(<<_STH_);
delete   
from    eq_inventory as a inner join eq_inventoryInfo as b
        on a.invId = b,invId
where   a.status   = 'active' &&
        b.uid      = ?
_STH_
   
    my $sth_update = $dbh->prepare(<<_STH_);
update   eq_inventoryInfo
set      status = 'closed'
where    uid    = ? 
_STH_
       
    $sth->execute($uid);
    $sth_update->execute($uid);

    $sth->finish;
    $sth_update->finish;

    return; 
}


sub inv_deleteInventory {

    my ($dbh,$invId) = @_;
    my $sth_info = $dbh->prepare(<<_STH_);
delete   
from    eq_inventoryInfo
where   invId    = ?
_STH_

    my $sth_file = $dbh->prepare(<<_STH_);
delete   
from    eq_inventoryFile
where   invId    = ?
_STH_
    
    my $sth = $dbh->prepare(<<_STH_);
delete   
from    eq_inventory
where   invId    = ?
_STH_
    
    $sth_info->execute($invId);
    $sth_file->execute($invId);
    $sth->execute($invId);
 
    $sth_info->finish;
    $sth_file->finish;
    $sth->finish;
    
    return; 
}

sub inv_getInventoryByStatus {

    my ($dbh,$invId,$status,$sortField, $pageNo,$pageSize) = @_;
    my @holdingList ;
    my ($query,$editable, $allEditable, $sf);
    if ($sortField !~ m/barcode|title/i){
        $sf = "scanOrder";
    }
    else {
        $sf = $sortField;
    }


    if ($status eq 'invalid') {
        $query = <<_STH_;
select * from eq_inventory where invId = ? && status regexp ?        
_STH_
    }
    else {
        $query = <<_STH_;
select  v.*, r.rname as title, v.barcode as barcode
from    eq_inventory v 
        inner join eq_items i using(barcode)
        inner join eq_records r using(rid)
where   invId = ? &&  status regexp ?        
_STH_
    }
    if ($sf ne ''){
        $query .= " order by $sf";
    }

    if ($pageSize && $pageNo ) {
        my $pageOffset = $pageSize * ($pageNo - 1);
        $query .= " limit $pageOffset , $pageSize " ;
    }
    my $sth = $dbh->prepare($query);
    $sth->execute($invId, $status);
    while( my $h = $sth->fetchrow_hashref) {
        if ($h->{'updatedStatus'} =~ m/$status/i){
            $h->{'updated'} = 1;
            $editable = 1 if (!$h->{'updated'});
        }
        push @holdingList, $h;
    }
    
    my ($allEditable) = $dbh->selectrow_array(<<_STH_);
select  count(*)
from    eq_inventory
where   status regExp  '$status' &&
        updated = 0
_STH_

    return (\@holdingList,$editable, $allEditable );
  
}

sub inv_getInventoryStatusByBc {
    
    my($dbh,$invId,$bc,$status) = @_;
    my @holdingList;
    my ($query );

    if ($status eq 'invalid') {
        $query = <<_STH_;
select * from eq_inventory where invId = ? && barcode = ?  && status regexp ?        
_STH_
    }
    else {
        $query = <<_STH_;
select  v.*, r.rname as title, v.barcode as barcode
from    eq_inventory v 
        inner join eq_items i using(barcode)
        inner join eq_records r using(rid)
where   invId = ? && v.barcode = ? && status regexp ?        
_STH_
    }

    my $sth = $dbh->prepare($query);
    $sth->execute($invId,$bc,$status);
    my $h;
    if ($h = $sth->fetchrow_hashref){
    
    }
    return $h;
}
sub inv_getInventoryStatusByBcList {
    
    my($dbh,$invId,$bcList,$status, $type, $sortField) = @_;
    my @holdingList;
    my ($query );
    my $sf = 'barcode';
    if ($status eq 'invalid') {
        $query = <<_STH_;
select * from eq_inventory where invId = $invId  && status regexp ?        
_STH_
    }
    else {
        $query = <<_STH_;
select  v.*, r.rname as title, v.barcode as barcode
from    eq_inventory v 
        inner join eq_items i using(barcode)
        inner join eq_records r using(rid)
where   invId = $invId &&  status regexp '$status'        
_STH_
    }

    if ($type eq 'selected'){
        $query .= " && v.barcode in ($bcList) order by field(v.barcode, $bcList) ";
    }
    else {
        if ($sortField ne "" && $sortField =~ m/barcode|title/i ){
            $sf = $sortField;
        }
        $query .= " order by $sf " ;
    }


    my $sth = $dbh->prepare($query);
    
    #$sth->execute($invId,$bcList,$status);
    $sth->execute();
    my $h;
    while ($h = $sth->fetchrow_hashref){
        if ($h->{'updatedStatus'} =~ m/$status/i){
            $h->{'updated'} = 1;
        }
        push @holdingList, $h;
    }
    return \@holdingList;
}

sub inv_mark_Returned {
    my ($dbh, $barcodeList,$invId) = @_;
    my @barcode = @{$barcodeList};
     
    my $sth_noteDetail = $dbh->prepare(<<_STH_);
select  noteDetail
from    eq_inventory
where   status  regexp 'loan' &&
        invId   = ? &&
        barcode = ?
_STH_

    my $sth_update_loan = $dbh->prepare(<<_STH_);
update  eq_loan  
set     dateReturn    = now()
where   barcode       = ? &&
        id        = ? &&
        dateReturn is null
_STH_

    my $sth_update_inv = $dbh->prepare(<<_STH_);
update  eq_inventory 
set     updatedStatus = CONCAT(updatedStatus, ',loan')
where   barcode     = ? &&
        invId       = ? &&
        status      regexp 'loan' 
_STH_
   
   my @val; 
   my ($noteDetail,$idloan);
   foreach my $bc (@barcode) {
        $bc    =~ s/^\s+|\s+$//g;

        @val = ($invId,$bc); 
        ($noteDetail) = $dbh->selectrow_array($sth_noteDetail, undef, @val);
        my $statusDetail =getStatusDetail($noteDetail);
        $idloan = $statusDetail->{'idloan'};
        if($idloan ne ''){
            $sth_update_inv->execute($bc,$invId);
            $sth_update_loan->execute($bc,$idloan);
        }
    }
    $sth_update_loan->finish;
    $sth_update_inv->finish;
}

sub getStatusDetail{
    my($noteDetail)=@_;
    my $retval;
    my @note = split(/,/,$noteDetail);
    for(my $i; $i < scalar(@note); $i++){
           my @tmp=  split(/:/,@note[$i]);
           $retval->{@tmp[0]}=@tmp[1];
    }
    return $retval;
}