#!/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/storeXmlRecord /tmp/urt; perl -pi -e "s/_MY_SITE_/$i/" /tmp/urt; sudo /tmp/urt; done

=cut


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

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      = ?, 
            prefix  = ?,      
            import  = ?,      deleted  = ?,
            damaged = ?,      lost     = ?,
            missing = ?,      found    = ?
            
_SQL_



#8816  813


my $sth = $dbh->prepare(<<_SQL_);
select      distinct rid
from        opl_item
order by    rid asc 
_SQL_
    $sth->execute;

    my $marcXmlParser=undef;
    my $dir;
    my $hStat;
    my $chgStat;
    my $delStat;
    my $newRec=1;
    my $recChange=0;
    my $countHolding=0;
    while (my ($rid) = $sth->fetchrow_array) {
        $hStat   = getStatusImport($dbh,$rid); 
        $chgStat = getStatusChange($dbh,$rid);
        
        
        foreach my $d(sort keys %{$chgStat}){
            my $prefix = $chgStat->{$d};
            foreach my $p(keys %{$prefix}){
                my $stat = $prefix->{$p};
                foreach my $s(keys %{$stat}){
                    if(!$hStat->{$d}->{$p}->{$s}){
                        $hStat->{$d}->{$p}->{$s}= $chgStat->{$d}->{$p}->{$s} ;
                    }
                    else{
                        $hStat->{$d}->{$p}->{$s} +=$chgStat->{$d}->{$p}->{$s};
                    }
                }
               
            }
        }
       $newRec=1; 
       foreach my $d(sort keys %{$hStat}){
            $recChange=0; 
            $countHolding=getNumOfRecHolding($dbh,$rid,$d);
            my $prefix = $hStat->{$d};
            my ($statId_imp,$statId_del,$statId_chg) =(0,0,0);
            my ($import,$deleted,$damaged,$lost,$missing,$found) =(0,0,0,0,0,0);
         
            if($newRec){
                $newRec=0;
                $sth_insert_recStat->execute($rid,$d,'new');
                $statId_imp = $dbh->{'mysql_insertid'};
            }

            if($countHolding==0){
                $sth_insert_recStat->execute($rid,$d,'deleted');
                $statId_del = $dbh->{'mysql_insertid'};

            }
            #detect change
            foreach my $p(keys %{$prefix}){
                if($hStat->{$d}->{$p}->{'deleted'}){
                    if($countHolding !=0){
                        $recChange=1;
                        last;
                    }
                }
                elsif(($hStat->{$d}->{$p}->{'found'} ||
                      $hStat->{$d}->{$p}->{'missing'} ||
                      $hStat->{$d}->{$p}->{'damaged'} ||
                      $hStat->{$d}->{$p}->{'lost'}) ||
                      ($hStat->{$d}->{$p}->{'import'} && !$statId_imp)
                     ){
                    $recChange=1;
                    last;
                }
            }
            if($recChange){
                $sth_insert_recStat->execute($rid,$d,'changed');
                $statId_chg = $dbh->{'mysql_insertid'};

            }
            foreach my $p(sort keys %{$prefix}){
                my $stat = $prefix->{$p};
   
                    $import     = $stat->{'import'} if($stat->{'import'}); 
                    $deleted    = $stat->{'deleted'} if($stat->{'deleted'});
                    $damaged    = $stat->{'damaged'} if($stat->{'damaged'});
                    $lost       = $stat->{'lost'} if($stat->{'lost'});
                    $missing    = $stat->{'missing'} if($stat->{'missing'});
                    $found      = $stat->{'found'} if($stat->{'found'});
                    
                    if($statId_imp && $import>0 ){
                        $sth_insert_itemStat->execute($statId_imp,$rid,$p,$import,0,0,0,0,0);
                        $import=0;
                    }
                    if($statId_chg && $statId_del){
                        $sth_insert_itemStat->execute($statId_del,$rid,$p,0,$deleted,0,0,0,0);
                        $sth_insert_itemStat->execute($statId_chg,$rid,$p,$import,0,$damaged,$lost,$missing,$found);
                    }
                    elsif($statId_del){
                        $sth_insert_itemStat->execute($statId_del,$rid,$p,$import,$deleted,$damaged,$lost,$missing,$found);
                    }
                    elsif($statId_chg){
                        $sth_insert_itemStat->execute($statId_chg,$rid,$p,$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      s.status,i.barcode,  left(dateImport,10) dateImport,f.sf852Data prefix,s.ondate     
    from        opl_item i left outer join opl_itemInfo f on f.barcode=i.barcode && f.sf852Code='k' 
                left outer join opl_itemstatus s on s.barcode= i.barcode && s.status<6 
    where       rid=? 
    order by    dateImport                
_SQL_
    $sth->execute($rid);
    my $recImport =0;
    while(my ($status,$bc,$dateImport,$prefix,$ondate) =$sth->fetchrow_array){
#        if($recImport == 0){
#            $ret->{$dateImport}->{' _recImport_' .$rid}->{"import"} =1 ;
#            $recImport++;
#        }
        if(!$prefix){
            $prefix = retrievePrefix($dbh,$rid,$bc) if($status == 5);
            #$prefix="" if(!$prefix || $prefix eq '');
        }
        $ret->{$dateImport}->{$prefix}->{"import"}=0 if(!$ret->{$dateImport}->{$prefix}->{"import"});
        $ret->{$dateImport}->{$prefix}->{"import"} += 1;
    }
   $sth->finish;
   return $ret;
}

#------------------------------------------------------------------------------  
 
sub getStatusChange{
    my ($dbh,$rid)=@_;
    my $sth_bk=$dbh->prepare(<<_SQL_);
    select  f.sf852Data prefix, i.barcode,left(s.ondate,10) ondate ,s.status    
    from    opl_item i left outer join opl_itemstatus s on s.barcode=i.barcode  
            inner join opl_itemInfo f on f.barcode=i.barcode &&
            f.sf852Code='k' && s.status <6 
    where   rid=?
    order by s.barcode,s.id
_SQL_

my $sth=$dbh->prepare(<<_SQL_);
    select      f.sf852Data prefix, i.barcode,left(s.ondate,10) ondate ,s.status         
    from        opl_item i left outer join opl_itemInfo f on f.barcode=i.barcode &&  f.sf852Code='k'  
                inner  join opl_itemstatus s on s.barcode=i.barcode   && s.status <6      
    where       rid=?     
    order by    s.barcode,s.id
_SQL_
    $sth->execute($rid);
    my $ret; 
    my $preStat =1;
    my $preBc="";

    my $pStat={};
    my $ondate='';
    #my $prefix ='emptyPrefix';
    my $prefix ='';
    my ($import,$deleted,$damaged,$lost,$missing,$found) =(0,0,0,0,0,0);
    

    while (my ($prefix,$bc,$ondate,$status) = $sth->fetchrow_array()) {
        if(!$prefix){
            $prefix = retrievePrefix($dbh,$rid,$bc) if($status == 5);
            #$prefix="emptyPrefix" if(!$prefix || $prefix eq '');
        }
        if(!$ret->{$ondate}->{$prefix}){
            $ret->{$ondate}->{$prefix}={found=>0,lost=>0,damaged=>0,deleted=>0};
        }
        if($status==1 && $preStat==3){
            $ret->{$ondate}->{$prefix}->{'found'} +=1;
        }
        elsif($status==2){
            $ret->{$ondate}->{$prefix}->{'damaged'} +=1;
        }
        elsif($status==3){
            $ret->{$ondate}->{$prefix}->{'lost'} +=1;
        }
        elsif($status==5){
            $ret->{$ondate}->{$prefix}->{'deleted'} +=1;
#            if(getNumOfRecHolding($dbh,$rid,$ondate)==0){
#                $ret->{$ondate}->{'recDelete_'.$rid}= {deleted=>1};
#            }
#
        }
        $preStat = $status;

    }

    $sth->finish;
    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 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;