#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;
use Opals::Template qw(
    tmpl_preference
);

use Opals::Context;
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

    my $cgi         = CGI->new;
    my $input       = $cgi->Vars();
    my $dateFrom    =$input->{'dateFrom'};
    my $dateTo      =$input->{'dateTo'} ;
    my $pref = tmpl_preference($dbh);
     my $dateFirst = $pref->{'dateFirst'};
    my $dateLast  = $pref->{'dateLast'};

    $dateFrom =  "1970-01-01" if ( !$dateFrom || $dateFrom eq "");
    $dateTo =  $dateLast  if ( !$dateTo || $dateTo eq "");
$dateTo .= " 23:59:59";
    my $colStatRpt=genTblRpt($dbh,$dateFrom,$dateTo);
    my $rsJSON = to_json($colStatRpt,{pretty=>1});

    print "Content-type: text/plain\n\n";
    print  $rsJSON;

#-----------------------------------------------------------------------------------------------
sub  genTblRpt{
    my ($dbh,$dateFrom,$dateTo)=@_;
    my $rpt=getInitStatsTbl($dbh);
    fillHoldingStats($dbh,$rpt,$dateFrom,$dateTo);
    fillTitleStats($dbh,$rpt,$dateFrom,$dateTo);
    #convert report  to array
    my $retTbl={print=>[],nonPrint=>[],total=>{}};
    my @recForm=();
    foreach my $f(sort keys %$rpt){
        next if($f eq 'byPrefix');
        push @recForm,$f;
    }
    
    my $totalStats={print=>{},nonPrint=>{}};
    foreach my $f(@recForm){
        my $litForm=>[];
        my $prtNonprt="nonPrint";
        if($f eq 'book' || $f eq 'sheetMusic' || $f eq 'journal'){
            $prtNonprt="print";
        }
        foreach my $l(sort keys %{$rpt->{$f}}){
            my $stats=$rpt->{$f}->{$l};
            
            $stats->{'asOfDateB'}->{'holding'}->{'inLibrary'} = $stats->{'asOfDateB'}->{'holding'}->{'added'}
                                                              + $stats->{'asOfDateB'}->{'holding'}->{'found'}
                                                              - $stats->{'asOfDateB'}->{'holding'}->{'lost'}
                                                              - $stats->{'asOfDateB'}->{'holding'}->{'missing'}
                                                              - $stats->{'asOfDateB'}->{'holding'}->{'deleted'}
                                                              + $stats->{'asOfDateB'}->{'holding'}->{'missing_del'}
                                                              + $stats->{'asOfDateB'}->{'holding'}->{'lost_del'};
            $stats->{'asOfDateE'}->{'holding'}->{'inLibrary'} = $stats->{'asOfDateB'}->{'holding'}->{'inLibrary'}
                                                              + $stats->{'inRange'}->{'holding'}->{'added'}
                                                              + $stats->{'inRange'}->{'holding'}->{'found'}
                                                              - $stats->{'inRange'}->{'holding'}->{'lost'}
                                                              - $stats->{'inRange'}->{'holding'}->{'missing'}
                                                              - $stats->{'inRange'}->{'holding'}->{'deleted'}
                                                              + $stats->{'inRange'}->{'holding'}->{'missing_del'}
                                                              + $stats->{'inRange'}->{'holding'}->{'lost_del'};
            push @{$litForm},{literary=>$l,stats=>$stats};
            foreach my $s qw(added found lost missing deleted missing_del lost_del){
                $totalStats->{$prtNonprt}->{'inRange'}->{'holding'}->{$s} += $stats->{'inRange'}->{'holding'}->{$s};
                $totalStats->{'total'}->{'inRange'}->{'holding'}->{$s} += $stats->{'inRange'}->{'holding'}->{$s};
            }
            $totalStats->{$prtNonprt}->{'asOfDateB'}->{'holding'}->{'inLibrary'}+= $stats->{'asOfDateB'}->{'holding'}->{'inLibrary'};
            $totalStats->{$prtNonprt}->{'asOfDateE'}->{'holding'}->{'inLibrary'}+= $stats->{'asOfDateE'}->{'holding'}->{'inLibrary'};
            $totalStats->{$prtNonprt}->{'asOfDateB'}->{'title'}->{'inLibrary'}  += $stats->{'asOfDateB'}->{'title'}->{'inLibrary'};
            $totalStats->{$prtNonprt}->{'asOfDateE'}->{'title'}->{'inLibrary'}  += $stats->{'asOfDateE'}->{'title'}->{'inLibrary'};
            $totalStats->{$prtNonprt}->{'inRange'}->{'title'}->{'added'}        += $stats->{'inRange'}->{'title'}->{'added'};
            $totalStats->{$prtNonprt}->{'inRange'}->{'title'}->{'deleted'}      += $stats->{'inRange'}->{'title'}->{'deleted'};
            $totalStats->{'total'}->{'asOfDateB'}->{'holding'}->{'inLibrary'}+= $stats->{'asOfDateB'}->{'holding'}->{'inLibrary'};
            $totalStats->{'total'}->{'asOfDateE'}->{'holding'}->{'inLibrary'}+= $stats->{'asOfDateE'}->{'holding'}->{'inLibrary'};
            $totalStats->{'total'}->{'asOfDateB'}->{'title'}->{'inLibrary'}  += $stats->{'asOfDateB'}->{'title'}->{'inLibrary'};
            $totalStats->{'total'}->{'asOfDateE'}->{'title'}->{'inLibrary'}  += $stats->{'asOfDateE'}->{'title'}->{'inLibrary'};
            $totalStats->{'total'}->{'inRange'}->{'title'}->{'added'}        += $stats->{'inRange'}->{'title'}->{'added'};
            $totalStats->{'total'}->{'inRange'}->{'title'}->{'deleted'}      += $stats->{'inRange'}->{'title'}->{'deleted'};
        }
        my $fRow={form=>$f,litForm=>$litForm};
        push @{$retTbl->{$prtNonprt}},$fRow ;
        
    }
    $retTbl->{'total'}=$totalStats;
#prefix stats
    $retTbl->{'byPrefix'}=[];
    foreach my $prefix(sort keys %{$rpt->{'byPrefix'}}){
        my $stats=$rpt->{'byPrefix'}->{$prefix};
        $stats->{'asOfDateB'}->{'inLibrary'} =  $stats->{'asOfDateB'}->{'added'}
                                              + $stats->{'asOfDateB'}->{'found'}
                                              - $stats->{'asOfDateB'}->{'lost'}
                                              - $stats->{'asOfDateB'}->{'missing'}
                                              - $stats->{'asOfDateB'}->{'deleted'}
                                              + $stats->{'asOfDateB'}->{'missing_del'}
                                              + $stats->{'asOfDateB'}->{'lost_del'};
       $stats->{'asOfDateE'}->{'inLibrary'} =   $stats->{'asOfDateB'}->{'inLibrary'}
                                              + $stats->{'inRange'}->{'added'}
                                              + $stats->{'inRange'}->{'found'}
                                              - $stats->{'inRange'}->{'lost'}
                                              - $stats->{'inRange'}->{'missing'}
                                              - $stats->{'inRange'}->{'deleted'}
                                              + $stats->{'inRange'}->{'missing_del'}
                                              + $stats->{'inRange'}->{'lost_del'};
       push @{$retTbl->{'byPrefix'}},{prefix=>$prefix,stats=>$stats};
    }
    return $retTbl;
}
#-----------------------------------------------------------------------------------------------
sub getInitStatsTbl{
    my($dbh)=@_;
    my $sth=$dbh->prepare(<<_SQL_);
        select distinct m.recFormat,m.literaryForm 
        from opl_marcRecord m inner join opl_recordStats r using(rid)
        where r.onDate<?
_SQL_

    $sth->execute($dateTo);

    my $colStatsTbl={};
    while(my($format,$literary)=$sth->fetchrow_array){
        foreach my $r(qw(asOfDateB inRange  asOfDateE)){
            $colStatsTbl->{$format}->{$literary}->{$r}={
                                              holding=>{added=>0,
                                                        deleted=>0,
                                                        missing=>0,
                                                        lost=>0,
                                                        missing_del=>0,
                                                        lost_del=>0,
                                                        found=>0},
                                              title  =>{
                                                        added=>0,
                                                        deleted=>0}
                                             };
          }
     }
    $sth=$dbh->prepare(<<_SQL_);
        select distinct if(prefix is null or prefix=' ','',prefix) prefix from opl_itemStats i inner join opl_recordStats r using(rid)
        where r.onDate<?
_SQL_

   $sth->execute($dateTo);
    while(my($prefix)=$sth->fetchrow_array){
        foreach my $r(qw(asOfDateB inRange  asOfDateE)){
            $colStatsTbl->{'byPrefix'}->{uc($prefix)}->{$r}={ added=>0,
                                            deleted=>0,
                                            missing=>0,
                                            lost=>0,
                                            missing_del=>0,
                                            lost_del=>0,
                                            found=>0};
        }
    }
    #open debug,">/tmp/ss";print debug to_json($colStatsTbl->{'byPrefix'}, {pretty=>1});close debug;
    return $colStatsTbl;
}
#-----------------------------------------------------------------------------------------------
sub fillTitleStats{
    my($dbh,$colStatsTbl,$dateFrom,$dateTo)=@_;
    my $sth_b=$dbh->prepare("select m.recFormat,m.literaryForm,count(distinct rid)
             from   opl_marcRecord m inner join opl_recordStats r using(rid)  
                    where r.onDate<?
                    && r.status=? group by recFormat,literaryForm");

    my $sth_i=$dbh->prepare("select m.recFormat,m.literaryForm,count(distinct rid)
             from   opl_marcRecord m inner join opl_recordStats r using(rid)  
                    where r.onDate>= ? && r.onDate<= ?
                    && r.status=? group by recFormat,literaryForm");
    $sth_b->execute($dateFrom,'new');
    while(my($recForm,$literary,$count) =$sth_b->fetchrow_array){
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateB'}->{'title'}->{'added'}=$count;
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateB'}->{'title'}->{'inLibrary'}=$count;
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateE'}->{'title'}->{'inLibrary'}=$count;
    }
    $sth_b->execute($dateFrom,'deleted');
    while(my($recForm,$literary,$count) =$sth_b->fetchrow_array){
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateB'}->{'title'}->{'deleted'}=$count;
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateB'}->{'title'}->{'inLibrary'} -=$count;
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateE'}->{'title'}->{'inLibrary'} -=$count;
    }

  
    $sth_i->execute($dateFrom,$dateTo,'new');
    while(my($recForm,$literary,$count) =$sth_i->fetchrow_array){
        $colStatsTbl->{$recForm}->{$literary}->{'inRange'}->{'title'}->{'added'}=$count;
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateE'}->{'title'}->{'inLibrary'} +=$count;
    }
    $sth_i->execute($dateFrom,$dateTo,'deleted');
    while(my($recForm,$literary,$count) =$sth_i->fetchrow_array){
        $colStatsTbl->{$recForm}->{$literary}->{'inRange'}->{'title'}->{'deleted'}=$count;
        $colStatsTbl->{$recForm}->{$literary}->{'asOfDateE'}->{'title'}->{'inLibrary'} -=$count;
    }                    
   
}

#-----------------------------------------------------------------------------------------------
sub fillHoldingStats{
    my($dbh,$colStatsTbl,$dateFrom,$dateTo)=@_;
    my $sql="select r.onDate,m.recFormat,m.literaryForm,i.barcode,i.prefix,i.import,i.deleted,i.lost,i.missing,i.found
             from   opl_marcRecord m inner join opl_recordStats r using(rid)  
                    inner join opl_itemStats i using(statId) 
                    where r.onDate<?
                    order by barcode,i.id";
    my $sth=$dbh->prepare($sql);
    $sth->execute($dateTo);
    my $curBc="";
    my $slot="";
    my $stats={};
    my $curFormat="";
    my $curLiterary="";
    my $curPrefix="";
    while(my($onDate,$format,$literary,$bc,$prefix,$import,$del,$lost,$missing,$found)=$sth->fetchrow_array){
        if($curBc ne $bc){
            if($curBc ne ''){
                foreach my $r qw(asOfDateB inRange){
                    foreach my $s qw(added deleted missing lost found lost_del missing_del){
                        if($stats->{$r}->{$s}){
                             $colStatsTbl->{$curFormat}->{$curLiterary}->{$r}->{'holding'}->{$s} +=1;
                             $colStatsTbl->{'byPrefix'}->{$curPrefix}->{$r}->{$s} +=1;
                        }
                    }
                }
                
            }
            $stats={asOfDateB=>{},inRange=>{}};
            $curBc=$bc;
            $curFormat=$format;
            $curLiterary=$literary;
            $prefix ="" if(!defined $prefix);
            $curPrefix =uc($prefix);
            $curPrefix =~ s/^\s+|\s+$//g;
        }
        $slot=($onDate lt $dateFrom)?"asOfDateB":"inRange";
        if($import){
            $stats->{$slot}->{'added'}=1;
        }
        elsif($del){
            $stats->{$slot}->{'deleted'}=1 ;
            if($stats->{$slot}->{'missing'}){
                $stats->{$slot}->{'missing_del'}=1 ;
            }
            if($stats->{$slot}->{'lost'}){
                $stats->{$slot}->{'lost_del'}=1 ;
            }
         }
        elsif($lost){
            $stats->{$slot}->{'lost'}=1 ;
        }
        elsif($missing){
            $stats->{$slot}->{'missing'}=1 ;
        }
        elsif($found){
            $stats->{$slot}->{'found'}=1 ;
        }
   }
   # stats of the last entry
   foreach my $r qw(asOfDateB inRange){
        foreach my $s qw(added deleted missing lost found lost_del missing_del){
            if($stats->{$r}->{$s}){
                 $colStatsTbl->{$curFormat}->{$curLiterary}->{$r}->{'holding'}->{$s} +=1;
                 $colStatsTbl->{'byPrefix'}->{$curPrefix}->{$r}->{$s} +=1;
            }
        }
    }
}
#------------------------------------------------------------------------------