package Opals::CollectionStats;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    colStats_Fill
    colStats_getLastStatsDate
);
use Opals::Date qw(
     date_f005
);

# Version number
$VERSION   = 0.01;      

use strict;
use Encode;

############################################################
sub colStats_Fill{
    my($dbh)=@_;
    my ($sql,$sth);
    my $lastDate =colStats_getLastStatsDate($dbh);

    $lastDate='1970-01-01' if($lastDate eq "");
    
    $sth=$dbh->prepare("delete from opl_collectionStats where onDate =? ");
    $sth->execute($lastDate);
    $sql = "select   distinct m.rid,m.leader,m.cf_008 
            from     opl_marcRecord m inner join opl_item i on m.rid=i.rid
            where    cast(i.dateImport as DATE ) >= ? OR  cast(i.modDate as DATE ) >= ? 
                     &&  (m.tempIll is NULL ||  m.tempIll = '') 
            order by rid asc";
    $sth=$dbh->prepare($sql);
    $sth->execute($lastDate,$lastDate);
    
   while (my ($rid,$leader,$cf_008) = $sth->fetchrow_array) {
       updateRecordType($dbh, $rid,$leader,$cf_008,$lastDate);
   }
   my $tmpDate =colStats_getLastStatsDate($dbh);
   my $dateToday = date_f005();

   if($tmpDate ne $dateToday){
        $dbh->do("insert into opl_collectionStats set onDate='$dateToday' ,record_Holding='holding'");
	    $dbh->do("insert into opl_collectionStats set onDate='$dateToday' ,record_Holding='record'");
   }

   $sth->finish;
}

############################################################
sub colStats_getLastStatsDate{
    my($dbh)=@_;
    my $retDate;
    my $sql_lastInCollTbl ="select max(onDate) from opl_collectionStats";
    my $sth_lastDate = $dbh->prepare($sql_lastInCollTbl);
       $sth_lastDate->execute;
    ($retDate) = $sth_lastDate->fetchrow_array;
    $sth_lastDate->finish;
    $retDate="" if(!$retDate);
    return $retDate;

}
############################################################

sub updateRecordType {
   my ($dbh, $rid, $leader, $cf_008,$lastDate) = @_;
 
   my $sth = $dbh->prepare(<<_SQL_);
update opl_marcRecord
set print=?, nonPrint=?
where rid =?
_SQL_

    my @typePrint=('print','shortStories','serial','fiction','nonFiction','pro','ref');
    my @typeNonPrint=('nonPrint','video','audio','multiMedia','cassette','kit','mixedMaterial','notatedMusic','map');
    
    my $recType =getRecordType($dbh, $rid, $leader, $cf_008);
    my($printStr,$nonPrintStr)=("","");

    foreach my $type (@typePrint){
        if($recType->{$type} ==1){
            $printStr .= "$type,";
        }
    }
    foreach my $type (@typeNonPrint){
       if($recType->{$type} ==1){
            $nonPrintStr .= "$type,";
        }        
    }

    $sth->execute($printStr,$nonPrintStr,$rid);
    $sth->finish;
    update_recStats($dbh,$rid,$recType,$lastDate); 
}
############################################################
sub getRecordType {
   my ($dbh, $rid, $leader, $cf_008) = @_;

   my $charAtPos6Leader;
   my $charAtPos34Cf008;
   my $recType;

   my $sth_proRef = $dbh->prepare(<<_SQL_);
select count(distinct barcode) as count from opl_item
where callNumber like ? && rid=? 
_SQL_

 
    #default material type -- in case leader is null
    $recType->{'nonPrint'}=0;
    $recType->{'print'}=1;
    
    # Extract leader
    if ($leader && $leader ne '') {
        $charAtPos6Leader =  substr $leader, 6, 1;
         
        $recType->{'nonPrint'}=1;
        $recType->{'print'}=0;

        if($charAtPos6Leader eq 'a') {
            $recType->{'print'}=1;
            $recType->{'nonPrint'}=0;
        }
        elsif($charAtPos6Leader eq 'g') {
            $recType->{'video'}=1;
        }
        elsif($charAtPos6Leader eq 'i') {
            $recType->{'audio'}=1;
        }
        elsif($charAtPos6Leader eq 'm') {
            $recType->{'multiMedia'}=1;
        }
        elsif($charAtPos6Leader eq 'j') {
            $recType->{'cassette'}=1;
        }
        elsif($charAtPos6Leader eq 'o') {
            $recType->{'kit'}=1;
        }
        elsif($charAtPos6Leader eq 'p') {
            $recType->{'mixedMaterial'}=1;
        }
        elsif($charAtPos6Leader eq 'c') {
            $recType->{'notatedMusic'}=1;
        }
        elsif($charAtPos6Leader eq 'e') {
            $recType->{'map'}=1;
        }

        $charAtPos6Leader =  substr $leader, 6, 2;
        if($charAtPos6Leader eq 'as') {
            $recType->{'serial'}=1;
        }
    }
    
    # Extract control field -- 008
    if($recType->{'print'}==1 && $cf_008 && length($cf_008)>=34){
        $charAtPos34Cf008 =  substr $cf_008, 33, 1;
        #print "[$charAtPos34Cf008]";
        if($charAtPos34Cf008 eq 'j') {
            $recType->{'shortStories'}=1;
        }
        elsif($charAtPos34Cf008 eq '1') {
            $recType->{'fiction'}=1;
        }
        elsif($charAtPos34Cf008 eq '0') {
            $recType->{'nonFiction'}=1;
        }
    }
    
   # Pro or Ref :check fron callNumber field 

    my @val=("pro%",$rid);
    my $num = $dbh->selectrow_array($sth_proRef, undef,@val);
    if($num>0){
        $recType->{'pro'}=1;
    }
    @val=("ref%",$rid);
    $num = $dbh->selectrow_array($sth_proRef, undef,@val);
    if($num>0){
        $recType->{'ref'}=1;
    }
    $sth_proRef->finish;

    return $recType;
    
}

############################################################
sub update_recStats{
     my($dbh,$rid,$recType,$lastDate)=@_;
     update_recStats_holding($dbh,$rid,$recType,$lastDate);   
     update_recStats_title($dbh,$rid,$recType,$lastDate);
 }
############################################################
sub update_recStats_holding{
     my($dbh,$rid,$recType,$lastDate)=@_;
     $lastDate='1970-01-01' if(!$lastDate || $lastDate eq '');
     
     my $holdingType;
     foreach my $type(keys %{$recType}){
        $holdingType->{$type} =$recType->{$type};
     }
     $holdingType->{'pro'}=0;
     $holdingType->{'ref'}=0;
     my $hStats = getStatsHoldingByRid($dbh,$rid,$holdingType,$lastDate);
     foreach my $d( keys %{$hStats}){
         my $tmp=$hStats->{$d};
         foreach my $status (keys %{$tmp}){
             if(isExist($dbh,'holding',$d,$status) ne 'true'){
                 insertIntoTbl($dbh,'holding',$d,$status,$tmp->{$status});
             }
             else{
                 updateTbl($dbh,'holding',$d, $status,$tmp->{$status});
             }
         }
     } 
}

############################################################
sub update_recStats_title{
    my($dbh,$rid,$recType,$lastDate)=@_;

     $lastDate='1970-01-01' if(!$lastDate || $lastDate eq '');
    
     #========= Case ADD : ================================
     my $selectSql_addRec = "select min(cast(dateImport as date)) date
                          from opl_item 
                          where rid=?";
     my $sth= $dbh->prepare($selectSql_addRec);
     $sth->execute($rid);
     if(my ($dateImport) = $sth->fetchrow_array){
         if($dateImport ge $lastDate) {
             if(isExist($dbh,'record',$dateImport,'add') ne 'true'){
                insertIntoTbl($dbh,'record',$dateImport, 'add',$recType);
             }
             else{
                 updateTbl($dbh,'record',$dateImport, 'add',$recType);
             }
         }

    }
    $sth->finish;
    #======= Case DELETED RECORD ========================
            
     my $sql_count = "select count(distinct barcode) from opl_item where  rid=? && cast(dateImport as date) < ?";
     my $sth_count= $dbh->prepare($sql_count);
           
     my $selectSql = "select   s.id,s.status,s.barcode,i.callNumber, cast(ondate as date) date
                      from     opl_itemstatus s inner join opl_item i 
                      on       s.barcode=i.barcode 
                      where    rid=? && ondate > ? && s.status <>6 
                      order by id";
     $sth= $dbh->prepare($selectSql);
     $sth->execute($rid,$lastDate);
     my $status;
     my @val =();
     my ($numHolding,$numDelHolding)=(0,0);
     while (my $rec = $sth->fetchrow_hashref) {
         @val=($rid,$rec->{'date'});
         $numHolding = $dbh->selectrow_array($sth_count, undef, @val);
         
         if($rec->{'status'} ==5){
             $numDelHolding = $numDelHolding +1;            
         
             if($numHolding == $numDelHolding){
                 if(isExist($dbh,'record',$rec->{'date'},'delete') ne 'true'){
                    insertIntoTbl($dbh,'record',$rec->{'date'},'delete',$recType);
                 }
                 else{
                     updateTbl($dbh,'record',$rec->{'date'}, 'delete',$recType);
                 }
             }
         }
     }
     $sth->finish;

}
############################################################
sub getStatsHoldingByRid{
     my($dbh,$rid,$holdingType,$lastDate)=@_;
     my $hStats;
     $lastDate='1970-01-01' if(!$lastDate || $lastDate eq '');
     #========= Case ADD : ================================     
     my $selectSql_add = "select cast(dateImport as date) date,callNumber
                          from opl_item 
                          where rid=?  && dateImport >= ?";
                         
     my $sth= $dbh->prepare($selectSql_add);
     $sth->execute($rid,$lastDate);
     while (my ($date,$callNumber) = $sth->fetchrow_array) {
         foreach my $t(keys %$holdingType){
             $hStats->{$date}->{'add'}->{$t}+=1 if($holdingType->{$t} ==1);
         }     
         if($callNumber){   
            $hStats->{$date}->{'add'}->{'pro'}+=1 if($callNumber =~ m/^pro/gi);
            $hStats->{$date}->{'add'}->{'ref'}+=1 if($callNumber =~ m/^ref/gi);
         }
    }
   
     #======= Case LOST,DELETE,FOUND ========================
        # status 1: Active
        # status 3: Lost
        # status 5: Delete
        
     my $selectSql = "select   distinct s.status,s.barcode,i.callNumber as callNumber, cast(ondate as date) date
                      from     opl_itemstatus s inner join opl_item i 
                      on       (s.barcode=i.barcode) or  i.barcode like  concat('___',s.barcode,'_0%' ) 
                      where    rid=? && ondate > ? && s.status <>6 
                      order by id";
     $sth= $dbh->prepare($selectSql);
     $sth->execute($rid,$lastDate);
     my ($status,$date,$callNumber)=('','','');
     my $preStatus =1;
     while (my $rec = $sth->fetchrow_hashref) {
         $date       = $rec->{'date'};
         $callNumber = $rec->{'callNumber'};
         if($rec->{'status'} ==3){
            $status = 'lost';
         }
         elsif($rec->{'status'} ==5){
             $status = 'delete';
         }
         elsif($preStatus==3 &&  ($rec->{'status'} ==1 || $rec->{'status'} ==2)){
             $status = 'found';
         } 
         $preStatus=$rec->{'status'} ;
         foreach my $t(keys %$holdingType){
            if($status eq 'lost' || $status eq 'delete' ||$status eq 'found' ){
                $hStats->{$date}->{$status}->{$t}+=1 if($holdingType->{$t} ==1);
            }
               
         }              
         if($callNumber){   
             $hStats->{$date}->{$status}->{'pro'}+=1 if($callNumber =~ m/^pro/gi);
             $hStats->{$date}->{$status}->{'ref'}+=1 if($callNumber =~ m/^ref/gi); 
         }

    }


    $sth->finish;
    return $hStats;
}

############################################################
sub isExist{
    my ($dbh,$rec_Holding,$onDate,$status)=@_;
    my $sql = " select id from opl_collectionStats  where record_Holding =? && onDate =? && status=?";
    my $chckSth= $dbh->prepare($sql);
    my @val=($rec_Holding,$onDate,$status);
    (my $id) = $dbh->selectrow_array($chckSth, undef, @val);
    $chckSth->finish;
    return ($id && $id>0)? 'true':'false';
    
}

############################################################
sub insertIntoTbl{
    my($dbh,$rec_Holding,$date, $status,$recType)=@_;
        my $insertSql = "insert into opl_collectionStats 
                         set  record_Holding = ? ,
                              onDate      = ?, ";
           $insertSql .= "    status      = ?, " ;
        my @val = ($rec_Holding,$date,$status);
        foreach my $t (sort keys %{$recType}){
            if($recType->{$t} >0 ){
                $insertSql .= " $t = ?, ";
                push @val, $recType->{$t};
            }
        }
        $insertSql =~ s/, $/ /;
        
        my $insertSth= $dbh->prepare($insertSql);       
        $insertSth->execute(@val);
        $insertSth->finish;              
}

############################################################
sub updateTbl{
    my ($dbh,$rec_Holding,$date, $status,$recType)=@_;
    my $updateSql = "update opl_collectionStats set "; 
    my @val = ();
    my $cVal;                          
    foreach my $t (sort keys %{$recType}){
        if($recType->{$t} >0 ){
            $updateSql .= "$t =  $t + $recType->{$t}, ";
        }
    }
    
    $updateSql =~ s/, $/ /;
    
    $updateSql .= ' where record_Holding =? && onDate =? && status=?';
    @val = ($rec_Holding,$date,$status);
                     
    my $updateSth= $dbh->prepare($updateSql);       
    $updateSth->execute(@val);  
    $updateSth->finish;            
}
#////////////////////////////////////////////////////////////////////////////


1;