package Opals::CollectionStats;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    colStat_genRecordStats
    colStat_updateItemStats
);
use Opals::Date qw(
     date_f005
);

use Opals::Constant;
# Version number
$VERSION   = 0.01;      
# Tue, Sep 25, 2012 @ 10:18:16 EDT
# Create record stat
#
use strict;
use Encode;

############################################################
sub colStat_genRecordStats{
    my($dbh,$rid)=@_;
    my $sth_insert_recStat=$dbh->prepare(<<_SQL_);
        insert into  opl_recordStats
        set          rid   = ?, 
                     onDate = ?, status= ?
_SQL_

    my $sth_insert_itemStat=$dbh->prepare(<<_SQL_);
        insert into opl_itemStats
        set         statId  = ?,      rid      = ?, 
                    barcode = ?,      prefix   = ?,
                    import  = ?,      deleted  = ?,
                    damaged = ?,      lost     = ?,
                    missing = ?,      found    = ?
            
_SQL_

    my $statId=0;
    my ($import,$deleted,$damaged,$lost,$missing,$found)=(0,0,0,0,0,0);
    $dbh->do("delete from opl_recordStats where rid= $rid");
    $dbh->do("delete from opl_itemStats   where rid= $rid");

    my $stats =_getStatusImport($dbh,$rid);

    foreach my $date(sort keys %$stats){
        foreach my $recStats (qw(new changed deleted)){
            next if(!$stats->{$date}->{$recStats});

            $sth_insert_recStat->execute($rid,$date,$recStats);
            $statId = $dbh->{'mysql_insertid'};
            foreach my $iStats(@{$stats->{$date}->{$recStats}}){
                ($import,$deleted,$damaged,$lost,$missing,$found)=(0,0,0,0,0,0);
                if($iStats->{'stats'} eq 'import'){
                    $import =1;
                }
                elsif($iStats->{'stats'} eq 'deleted'){
                    $deleted =1;
                }
                elsif($iStats->{'stats'} eq 'damaged'){
                    $damaged =1;
                }
                elsif($iStats->{'stats'} eq 'lost'){
                    $lost =1;
                }
                elsif($iStats->{'stats'} eq 'missing'){
                    $missing =1;
                }
                elsif($iStats->{'stats'} eq 'found'){
                    $found =1;
                }
                $sth_insert_itemStat->execute($statId,$rid,$iStats->{'bc'},$iStats->{'prefix'},
                                             $import,$deleted,$damaged,$lost,$missing,$found);
            }
        }
    }
    $sth_insert_recStat->finish;
    $sth_insert_itemStat->finish; 


}
############################################################
sub _getStatusImport{
    my ($dbh,$rid)=@_;
    my $ret={};
    my $sth=$dbh->prepare(<<_SQL_);
    select      i.barcode,left(dateImport,10) ,if(f.sf852Data is null,'',  f.sf852Data )
    from        opl_item i left outer join opl_itemInfo f on i.barcode=f.barcode && f.sf852Code='k' 
    where       rid=? 
    order by    dateImport                
_SQL_
    $sth->execute($rid);
    my $preDate ='';
    my $statsType='new';
    my $bcPrefix={};
    while(my ($bc,$dateImport,$prefix) =$sth->fetchrow_array){
        $preDate = $dateImport if($preDate eq '');
        $statsType='changed'   if($dateImport ne $preDate );
        $bcPrefix->{$bc}=$prefix;
        push @{$ret->{$dateImport}->{$statsType}}, {bc=>$bc,stats=>'import',prefix=>$bcPrefix->{$bc}};
    }
   $sth->finish;
 
   my $sth=$dbh->prepare(<<_SQL_);
    select      s.barcode,left(s.onDate,10),s.status   
    from        opl_itemstatus s inner join opl_item i using(barcode)
    where       i.rid=? && s.status<6
    order by    s.barcode,s.id                
_SQL_


    my $preBc='';
    my $preStatus=-1;
    my $itemStats=undef;
    #my $statsMap={2=>'damaged',3=>'lost',5=>'deleted'};
    my $statsMap={0=>'missing',2=>'damaged',3=>'lost',5=>'deleted'};
    $sth->execute($rid);
    
    while(my($bc,$ondate,$status)=$sth->fetchrow_array){
        $statsType ='changed';
        $itemStats=undef;
        if($bc eq $preBc && ($preStatus==3 || $preStatus==0) && $status==1){
        #if($bc eq $preBc && $preStatus==3  && $status==1){
            $itemStats ='found';
        }
        elsif($status==5){
            $statsType = 'deleted' if(_getNumOfRecHolding($dbh,$rid,$ondate)==0);
            $itemStats ='deleted';
        }
        else{
            $itemStats =$statsMap->{$status};
        }
        push @{$ret->{$ondate}->{$statsType}},{bc=>$bc,stats=>$itemStats,prefix=>$bcPrefix->{$bc}} ;
        $preStatus=$status;
        $preBc=$bc;
    }
    return $ret;


}

##############################################################################################
sub _getNumOfRecHolding{
   my ($dbh,$rid,$delDate) = @_;
    $delDate = $delDate . " 23:59:59";
    #number of items import at that time
    my $sth_imp = $dbh->prepare("select count(*) from opl_item where rid=? && dateImport <= ?");
       
    #number of items deleted at that time
    my $sth_del = $dbh->prepare("select     count(s.barcode) 
                                 from       opl_item i inner join opl_itemstatus s on 
                                            s.barcode = i.barcode  
                                 where      rid= ? &&  s.onDate <= ? && status=5 && 
                                            substring(i.barcode, 1, 3) regexp '___'") ;
    $sth_imp->execute($rid,$delDate);
    my ($numImp) = $sth_imp->fetchrow_array;
    $sth_del->execute($rid,$delDate);
    my ($numDel) = $sth_del->fetchrow_array;
    my $count = -1;
       $count = $numImp - $numDel; #number of items remain at that time
    $sth_imp->finish;
    $sth_del->finish;
 
    return $count;
 
 
}
##############################################################################################
# Move fron module MarcXml.pn to here on Tue, Sep 25, 2012 @ 14:39:37 EDT
#
# Thu, Mar 10, 2011 @ 11:08:13 EST
# Upddate record statistics
sub colStat_updateItemStats{
    my ($dbh,$bc,$status,$onDate)=@_;
    my $sth_insert_itemStat=$dbh->prepare(<<_SQL_);
insert into opl_itemStats
set         statId  = ?,      rid      = ?, 
            barcode = ?,      
            import  = ?,      deleted  = ?,
            damaged = ?,      lost     = ?,
            missing = ?,      found    = ?
            
_SQL_
  
    my ($import,$deleted,$damaged,$lost,$missing,$found) =(0,0,0,0,0,0);
    my $rid         = getRid($dbh,$bc);
    my $itemStats   = getItemStats($dbh,$bc, $status);
   
    if($itemStats eq 'import'){
        $import=1;
    }
    elsif($itemStats eq 'deleted'){
        $deleted=1;
    }
    elsif($itemStats eq 'damaged'){
        $damaged=1;
    }
    elsif($itemStats eq 'lost'){
        $lost=1;
    }
    elsif($itemStats eq 'missing'){
        $missing=1;
    }
    elsif($itemStats eq 'found'){
        $found=1;
    }
    else{
        return;
    }
    
    my $statsId =updateRecStats($dbh,$rid, $itemStats,$onDate);
    if($statsId){
        $sth_insert_itemStat->execute($statsId,$rid,$bc,$import,$deleted,
                                      $damaged,$lost,$missing,$found);
    }
    $sth_insert_itemStat->finish;

}

##############################################################################################
sub updateRecStats{
    my ($dbh,$rid, $itemStats,$onDate)=@_;
    my $sth_insert_recStat=$dbh->prepare(<<_SQL_);
insert into opl_recordStats
set         rid   = ?, 
            onDate = ?, status= ?
_SQL_
    
    my $statId_ret = 0;
    my ($statId,$statStatus,$statDate) = getLastStatRec($dbh,$rid);
    $statDate = substr($statDate, 0,10);
    $onDate   = substr($onDate, 0,10);
    if($itemStats eq 'import'){
        if($statId){
            if(($statStatus eq 'new' || $statStatus eq 'changed') && $onDate eq $statDate ){
                $statId_ret=$statId;
            }
            else{
                $sth_insert_recStat->execute($rid,$onDate,'changed');
                $statId_ret = $dbh->{'mysql_insertid'};
            }
        }
        else{
            $sth_insert_recStat->execute($rid,$onDate,'new');
            $statId_ret = $dbh->{'mysql_insertid'};
        }
    }
    elsif($itemStats eq 'deleted' && getNumOfRecHolding($dbh,$rid) == 0){
        $sth_insert_recStat->execute($rid,$onDate,'deleted');
        $statId_ret = $dbh->{'mysql_insertid'};
    }
    else{
        if($statDate eq $onDate && $statStatus eq 'changed'){
            $statId_ret=$statId;
        }
        else{
            $sth_insert_recStat->execute($rid,$onDate,'changed');
            $statId_ret = $dbh->{'mysql_insertid'};
        }
    }
    $sth_insert_recStat->finish;
    return $statId_ret;

}

##############################################################################################
sub getLastStatRec{
    my ($dbh,$rid )=@_;
    my $sth = $dbh->prepare(<<_SQL_);
    select statId,status,onDate from opl_recordStats where   rid = ? order by statId desc limit 1
_SQL_
    $sth->execute($rid);
    my ($statId,$status,$onDate) =(0,'','');
       ($statId,$status,$onDate)= $sth->fetchrow_array;
    $sth->finish;
    return ($statId,$status,$onDate);
}

##############################################################################################
sub getNumOfRecHolding{
    my ($dbh,$rid) = @_;
    my $sth = $dbh->prepare("select count(*) from opl_item where rid=? && substring(barcode, 1, 3) <> '___'");
    $sth->execute($rid);
    my ($count) = $sth->fetchrow_array;
    $sth->finish;
    return $count;
 
}

##############################################################################################
sub getItemStats{
    my ($dbh,$bc, $status)=@_;
    my $stats="";
    my $secondLastStatus=getSecondLastStatus($dbh,$bc);
    if(defined $secondLastStatus && $secondLastStatus ==$status){
         $stats="unchanged";
    }
    elsif($status == ITEM_ACTIVE ){
        if(defined $secondLastStatus && $secondLastStatus == ITEM_LOST){
                $stats = 'found';
        }
        else{
            my($c)=$dbh->selectrow_array("select count(*) from opl_itemStats where barcode=?",undef,$bc);
            $stats = 'import' if($c==0);
            #$stats = 'import' if(isNewItem($dbh,$bc));
        }
    }
    elsif($status == ITEM_DAMAGED){
        $stats = 'damaged';
    }
    elsif($status == ITEM_LOST){
        $stats = 'lost';
    }
    elsif($status == ITEM_DELETED){
        $stats = 'deleted';
    }
    elsif($status == ITEM_INACTIVE){
        $stats = 'missing';
    }
    return $stats;
}


##############################################################################################
sub getSecondLastStatus{
    my ($dbh,$bc)=@_;
    my $status=undef;
    my $sth = $dbh->prepare(<<_SQL_);
    select status  from opl_itemstatus where  barcode = ?
    order by id desc limit 1,1
_SQL_
    $sth->execute($bc);
    if(my ($s)= $sth->fetchrow_array){
        $status=$s;
    }
    $sth->finish;
    return $status;
}

##############################################################################################
sub isNewItem{
    my ($dbh,$bc)=@_;
    my $sth = $dbh->prepare(<<_SQL_);
    select if(dateImport =modDate,1,0) from opl_item where barcode = ?
_SQL_
    $sth->execute($bc);
    my ($isNew)= $sth->fetchrow_array;
    $sth->finish;
    return $isNew;
}

##############################################################################################
sub getRid{
    my ($dbh,$bc)=@_;
    my $sth = $dbh->prepare(<<_SQL_);
    select rid from opl_item where barcode = ?
_SQL_
    $sth->execute($bc)|| return ;
    my ($rid)= $sth->fetchrow_array;
    $sth->finish;
    return $rid;
}

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

1;