#!/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         archId = ?, rid   = ?, 
            onDate = ?, status= ?
_SQL_

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


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

    my $marcXmlParser=undef;
    my $dir;
    my $rStat;
    while (my ($rid) = $sth->fetchrow_array) {
               
        $rStat = getStatusImport($dbh,$rid);
        ##$rStat = getStatusChange($dbh,$rid);
        
        foreach my $d(keys %{$rStat}){
            print $d."\n";
            my $prefix = $rStat->{$d};
            foreach my $p(keys %{$prefix}){
                print $p ."\n";
                my $stat = $prefix->{$p};
                foreach my $s(keys %{$stat}){
                    print "  $s  = $stat->{$s}\n";

                }

            }
        }
    }
    $sth_updateRecForm->finish;
    $sth->finish;
#------------------------------------------------------------------------------  
sub getStatusImport{
    my ($dbh,$rid)=@_;
    my $ret={};
    my $sth=$dbh->prepare(<<_SQL_);
    select  f.sf852Data prefix, left(dateImport,10) dateImport
    from    opl_item i  
            left outer join opl_itemInfo f on f.barcode=i.barcode &&
            f.sf852Code='k' 
    where   rid=?
    
_SQL_
   
   $sth->execute($rid); 
   while(my ($prefix,$dateImport) =$sth->fetchrow_array){
        $prefix="" if(!$prefix);
        $ret->{$dateImport}->{$prefix}->{"import"}=0 if($ret->{$dateImport}->{"prefix"});
        $ret->{$dateImport}->{$prefix}->{"import"} += 1;

   }
   $sth->finish;
   return $ret;
}

#------------------------------------------------------------------------------  
sub getStatusDeleted{
    my ($dbh,$rid)=@_;
    my $ret={};
   
    my $sth=$dbh->prepare(<<_SQL_);
    select  concat(left(s.ondate,10),"23:59:59")  as delDate     
    from    opl_item i inner join opl_itemstatus s on s.barcode=i.barcode  
    where   rid=?
    group by left(s.ondate,10)
    order by delDate
    
_SQL_
   
   $sth->execute($rid); 
   while(my ($delDate) =$sth->fetchrow_array){
        if(getNumOfRecHolding($dbh,$rid,$delDate)==0){
            $ret->{$delDate}= {deleted=>1} if($ret->{$delDate});
            $ret->{$delDate}->{"deleted"} += 1;

        }

   }  
   $sth->finish;
   return $ret;
}


#------------------------------------------------------------------------------  
sub getNumOfRecHolding{
    my ($dbh,$rid,$delDate) = @_;
    #number of items import at that time
    my $sth_imp = $dbh->prepare("select count(*) from opl_item where rid=? && importDate    <= ? ");
       
    #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);
    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);
    }
}

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

    my $pStat={};
    my $ondate='';
    my $prefix ='';
    my ($import,$deleted,$damaged,$lost,$missing,$found) =(0,0,0,0,0,0);
    while (my ($prefix,$bc,$ondate,$status) = $sth->fetchrow_array()) {
        $prefix ='' if (!$prefix );
        if(!$rStat->{$ondate}->{$prefix}){
            $rStat->{$ondate}->{$prefix}={found=>0,lost=>0,damaged=>0,deleted=>0};
        }
        if($status==1 && $preStat==3){
            $rStat->{$ondate}->{$prefix}->{'found'} +=1;
        }
        elsif($status==2){
            $rStat->{$ondate}->{$prefix}->{'damaged'} +=1;
        }
        elsif($status==3){
            $rStat->{$ondate}->{$prefix}->{'lost'} +=1;
        }
        elsif($status==5){
            if (!$prefix){
                $prefix = retrievePrefix($dbh,$rid,$bc);
            }
            $rStat->{$ondate}->{$prefix}->{'deleted'} +=1;
        }
        $preStat = $status;
    
    }
    
    $sth->finish;
    return $rStat;
    
}


exit 0;