#!/usr/bin/perl 
#
# This script is used to remove all duplicate barcodes.
#
=item
command to update all the sites:

for i in `ls /etc/opals/conf/`; do cp -p /www/opals/script/update/update20110704/genStatsTbl /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo /tmp/urt; done

=cut


use lib '/www/opals/module';
use Opals::Context("/etc/opals/conf/_MY_SITE_");
#use Opals::Context("/etc/opals/conf/ztest");
#use Opals::Context("/etc/opals/conf/odev");
#use Opals::Context("/etc/opals/conf/npcsd_nph");

use strict;
use DBI;
use Getopt::Std;
use POSIX qw(
    ceil
    floor
);
use Opals::Utility qw(
  util_getXmlRecord
);

#my %options = ();
#getopts("c:",\%options);
#my $configFile = $options{c};
#if (!$configFile || ! -f $configFile) {
#    print "Usage: $0 -c CONFIG_FILE\n";
#    exit 1;
#}
#
my $dbh = Opals::Context->dbh();
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;

my $dbPrefixList=undef;


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_



#8816  813

my $statId=0;
my ($import,$deleted,$damaged,$lost,$missing,$found)=(0,0,0,0,0,0);
$dbh->do("truncate table opl_recordStats");
$dbh->do("truncate table opl_itemStats");
my $sth = $dbh->prepare(<<_SQL_);
select      distinct rid
from        opl_item
order by    rid asc 
_SQL_
    $sth->execute;

     while (my ($rid) = $sth->fetchrow_array) {
        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; 
    $sth->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 ) prefix
    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;
 
 
}





#------------------------------------------------------------------------------  
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;
 
 
}
#------------------------------------------------------------------------------
sub getPrefixFromMarcDelTbl{
    my($dbh,$rid) =@_;
    my $sth = $dbh->prepare(<<_SQL_);
    select  content 
    from    opl_marcDelete 
    where   content regexp '<controlfield tag="001">$rid<'
_SQL_
    $sth->execute(); 
    my $ret=undef;
    
    if(my ($content) =$sth->fetchrow_array){
        while ($content =~ m/<datafield tag="852" ind1="[\d ]" ind2="[\d ]">(.*?)<\/datafield>(.*)/s) {
                $content =$2;
                my $tmp852=$1;
                if ($tmp852 =~ m/<subfield code="p">(.*)<\/subfield>/ ) {
                    my $tmpBc=$1;
                    if($tmp852 =~ m/<subfield code="k">(.*)<\/subfield>/){
                        $ret->{$tmpBc}=$1;
                    }
                }
       }
   }
   $sth->finish;
   return $ret; 
}

#------------------------------------------------------------------------------
sub getDbPrefixList{
    my($dbh) =@_;
    my $prefixList=[];
    my $sth_prefixList = $dbh->prepare(<<_SQL_);
    select distinct(sf852Data) from opl_itemInfo where sf852Code='k' order by sf852Data desc
_SQL_
    $sth_prefixList->execute();
    while( my($prefix) =$sth_prefixList->fetchrow_array){
        push @$prefixList,$prefix;
    }
    $sth_prefixList->finish;
    return $prefixList;
}
#------------------------------------------------------------------------------
sub getPrefix{
    my($dbh,$barcode) =@_;
    $dbPrefixList =getDbPrefixList($dbh) if(!defined $dbPrefixList);
      
    my $prefix = "";
    my $sth = $dbh->prepare(<<_SQL_);
    select  callnumber 
    from    opl_item 
    where   barcode=?
_SQL_
    $sth->execute($barcode); 
    if(my ($callnumber) =$sth->fetchrow_array){
        foreach my $p(@$dbPrefixList){
            if($callnumber =~ m/^$p/i){
                $prefix=$p;
                last;
            }
        }
    }
    $sth->finish;
    return $prefix; 
}

#------------------------------------------------------------------------------
sub retrievePrefix{
    my ($dbh,$rid,$bc)=@_;
    my $prefixhash =getPrefixFromMarcDelTbl($dbh,$rid);
    if(defined $prefixhash && $prefixhash->{$bc}){
        return $prefixhash->{$bc};
    }
    else{
        return getPrefix($dbh,$bc);
    }
}



exit 0;