#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use POSIX qw(
    floor
);
use Opals::Context;
use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::User qw(
    user_getInformation
);
use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
     date_now

);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
    tmpl_preference
);

use Opals::CollectionStats qw(
    colStats_Fill
    colStats_getLastStatsDate
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();

my @typeTbl_total=(
             'Total',
             'Print',
             'Non Print');

my @typeTbl_print=(
             'Fiction',
             'Non Fiction',
             'Pro',
             'Ref',
             'Serial',
             'Short Stories'
             );

my @typeTbl_nonPrint=(
             'Video',
             'Audio',
             'Multi Media',
             'CD / Cassette',
             'Kit',
             'Mixed Material',
             'Notated Music',
             'Map'
             );




my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/beds_rpTable.tmpl',
            reqPermission   => 'report',
        }
);


    
    my $pref = tmpl_preference($dbh);

    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    my $dateFrom= $input->{'dateFrom'};
    my $dateTo  = $input->{'dateTo'};
    my $dateRange= $input->{'dateRangeOpt'};
    
    if ($dateRange eq "all"){
        #$dateFrom= "1970-01-01";
        $dateFrom=getTheFirstDateImport($dbh);
        $dateTo  = $dateToday;
    }
   
    my $dateFirst = $pref->{'dateFirst'};
    my $dateLast  = $pref->{'dateLast'};
    
    $dateFrom = $dateFirst if ( !$dateFrom || $dateFrom eq '');
    $dateTo = $dateLast if ( !$dateTo || $dateTo eq '' );
    my $lastStatsDate=colStats_getLastStatsDate($dbh);
    if($lastStatsDate eq "" || $lastStatsDate <= $dateTo ){
        colStats_Fill($dbh);
    }
    my (@reportTotal,@reportPrint,@reportNonPrint);



    genReport($dbh,$dateFrom, $dateTo);
    
    $template->param( reports_total    => \@reportTotal,
                      reports_print    => \@reportPrint,
                      reports_nonPrint => \@reportNonPrint);
    

    $template->param(
        BEDSreport     => 1,
        dateToday      => $dateToday
    );
    
    if($dateRange eq 'all'){
        $template->param(
            from         => substr($dateFrom, 0, 10),
            to           => substr($dateTo, 0, 10),
            rangeAll     => 1,
            dateRangeOpt =>"all"
       );
    }
    elsif($dateRange eq 'rangeSel'){
        $template->param(
            from         => substr($dateFrom, 0, 10),
            to           => substr($dateTo, 0, 10),
            rangeSel     => 1,
            dateRangeOpt =>"rangeSel"
       );
    }
    else{
        $template->param(
            from         => substr($dateFrom, 0, 10),
            to           => substr($dateTo, 0, 10),
            rangeFYear   => 1,
            dateRangeOpt =>"fYear"
       );
    }
  
tmpl_write($dbh, $cgi, $cookie, $template);
#$dbh->disconnect();

#####################################################################
sub getTheFirstDateImport{
    my($dbh)=@_;
    my $retVal;
    my $sth = $dbh->prepare(<<_STH_);
select dateImport from opl_item where dateImport is not null OR dateImport != '' order by dateImport limit 1 
_STH_
    $sth->execute();
    ($retVal) = $sth->fetchrow_array;
    $sth->finish;
    
    return $retVal;
}

#####################################################################

sub initReportTbl{

     
    push @reportTotal,{type=>"Total"};
    push @reportTotal,{type=>"Print"};
    push @reportTotal,{type=>"Non Print"};

    push @reportPrint,{type=>"Fiction"};
    push @reportPrint,{type=>"Non Fiction"};
    push @reportPrint,{type=>"Pro"};
    push @reportPrint,{type=>"Ref"};
    push @reportPrint,{type=>"Serial"};
    push @reportPrint,{type=>"Short stories"};
    
    push @reportNonPrint,{type=>"Video"};
    push @reportNonPrint,{type=>"Audio"};
    push @reportNonPrint,{type=>"Multi Media"};
    push @reportNonPrint,{type=>"CD / Cassette"};
    push @reportNonPrint,{type=>"Kit"};
    push @reportNonPrint,{type=>"Mixed Material"};
    push @reportNonPrint,{type=>"Notated Music"};
    push @reportNonPrint,{type=>"Map"};
   
   for (my $i=0;$i<scalar @reportTotal;$i++)
    {           
          @reportTotal[$i]->{'title_asOfDateB'}=0;
          @reportTotal[$i]->{'holding_asOfDateB'}=0; 
          @reportTotal[$i]->{'title_asOfDateE'}=0;   
          @reportTotal[$i]->{'holding_asOfDateE'}=0; 
          @reportTotal[$i]->{'holdingAdded'}=0;      
          @reportTotal[$i]->{'titleAdded'}=0;        
          @reportTotal[$i]->{'titleDelLost'}=0;      
          @reportTotal[$i]->{'holdingDelLost'}=0;    
          @reportTotal[$i]->{'titleFound'}=0;       
          @reportTotal[$i]->{'holdingFound'}=0;      
    }

   for (my $i=0;$i<scalar @reportPrint;$i++)
   {
          @reportPrint[$i]->{'title_asOfDateB'}=0;
          @reportPrint[$i]->{'holding_asOfDateB'}=0; 
          @reportPrint[$i]->{'title_asOfDateE'}=0;   
          @reportPrint[$i]->{'holding_asOfDateE'}=0; 
          @reportPrint[$i]->{'holdingAdded'}=0;      
          @reportPrint[$i]->{'titleAdded'}=0;        
          @reportPrint[$i]->{'titleDelLost'}=0;      
          @reportPrint[$i]->{'holdingDelLost'}=0;    
          @reportPrint[$i]->{'titleFound'}=0;       
          @reportPrint[$i]->{'holdingFound'}=0;        
   }

   for (my $i=0;$i<scalar @reportNonPrint;$i++)
    {      
          @reportNonPrint[$i]->{'title_asOfDateB'}=0;
          @reportNonPrint[$i]->{'holding_asOfDateB'}=0; 
          @reportNonPrint[$i]->{'title_asOfDateE'}=0;   
          @reportNonPrint[$i]->{'holding_asOfDateE'}=0; 
          @reportNonPrint[$i]->{'holdingAdded'}=0;      
          @reportNonPrint[$i]->{'titleAdded'}=0;        
          @reportNonPrint[$i]->{'titleDelLost'}=0;      
          @reportNonPrint[$i]->{'holdingDelLost'}=0;    
          @reportNonPrint[$i]->{'titleFound'}=0;       
          @reportNonPrint[$i]->{'holdingFound'}=0;     
    }
    
}
#####################################################################
#
#                  General Report
#
#####################################################################
sub genReport{
    my ($dbh,$dateFrom,$dateTo)=@_;
    getStatsReport($dbh,$dateFrom,$dateTo);

 }


#####################################################################
sub getStats{
   my ($dbh,$dateFrom,$dateTo) = @_; 
   my $sth;
   my $totalTitle=0;
   my @val=();
   $dateFrom='1970-01-01' if(!$dateFrom || $dateFrom eq '');
   $dateTo  =`date '+%Y-%m-%d'` if(!$dateTo || $dateTo eq '');

   my $sql="SELECT    status, record_Holding ,
                         sum(print) as Print, 
                         sum(nonPrint) as 'Non Print',
                         sum(fiction) as Fiction ,
                         sum(nonFiction) as 'Non Fiction',
                         sum(pro) as Pro,
                         sum(ref) as Ref,
                         sum(serial) as Serial,
                         sum(shortStories) as 'Short Stories',
                         sum(video) as Video,
                         sum(audio)as Audio,
                         sum(multiMedia) as 'Multi Media',
                         sum(cassette) as 'CD / Cassette',
                         sum(kit) as Kit,
                         sum(mixedMaterial) as 'Mixed Material',
                         sum(notatedMusic) as 'Notated Music',
                         sum(map) as Map
             FROM     opl_collectionStats 
             Where onDate >= ? && onDate <= ? 
             GROUP BY status,record_Holding"; 
     
 
    $sth= $dbh->prepare($sql);
    $sth->execute($dateFrom,$dateTo);
    while (my $rec=$sth->fetchrow_hashref){
        push @val,$rec;
    }
       
  $sth->finish;
  return @val;

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

sub getStatsReport{
    my ($dbh,$dateFrom,$dateTo)=@_;
    
    my $availStatsB = getAvailableAsDateOf($dbh,$dateFrom);
    my $adlfStats = getAddDelLostFound($dbh,$dateFrom,$dateTo);
    my $availStatsE = getAvailableAsDateOf($dbh,$dateTo);
    foreach my $t (@typeTbl_total){       
         push @reportTotal,{type=>$t,
                            title_asOfDateB=>$availStatsB->{$t}->{'record'},
                            holding_asOfDateB=>$availStatsB->{$t}->{'holding'},
                            holdingAdded=>$adlfStats->{'add'}->{$t}->{'holding'},
                            titleAdded=>$adlfStats->{'add'}->{$t}->{'record'},
                            titleDel=>$adlfStats->{'delete'}->{$t}->{'record'},
                            holdingLost=>$adlfStats->{'lost'}->{$t}->{'holding'},
                            holdingDel=>$adlfStats->{'delete'}->{$t}->{'holding'},
                            titleFound=>0,
                            holdingFound=>$adlfStats->{'found'}->{$t}->{'holding'},
                            title_asOfDateE=>$availStatsE->{$t}->{'record'},
                            holding_asOfDateE=>$availStatsE->{$t}->{'holding'}
         };                
    }
    foreach my $t (@typeTbl_print){       
         push @reportPrint,{type=>$t,
                            title_asOfDateB=>$availStatsB->{$t}->{'record'},
                            holding_asOfDateB=>$availStatsB->{$t}->{'holding'},
                            holdingAdded=>$adlfStats->{'add'}->{$t}->{'holding'},
                            titleAdded=>$adlfStats->{'add'}->{$t}->{'record'},
                            titleDel=>$adlfStats->{'delete'}->{$t}->{'record'},
                            holdingLost=>$adlfStats->{'lost'}->{$t}->{'holding'},
                            holdingDel=>$adlfStats->{'delete'}->{$t}->{'holding'},
                            titleFound=>0,
                            holdingFound=>$adlfStats->{'found'}->{$t}->{'holding'},
                            title_asOfDateE=>$availStatsE->{$t}->{'record'},
                            holding_asOfDateE=>$availStatsE->{$t}->{'holding'}
          };                
    }
    foreach my $t (@typeTbl_nonPrint){       
         push @reportNonPrint,{type=>$t,
                            title_asOfDateB=>$availStatsB->{$t}->{'record'},
                            holding_asOfDateB=>$availStatsB->{$t}->{'holding'},
                            holdingAdded=>$adlfStats->{'add'}->{$t}->{'holding'},
                            titleAdded=>$adlfStats->{'add'}->{$t}->{'record'},
                            titleDel=>$adlfStats->{'delete'}->{$t}->{'record'},
                            holdingLost=>$adlfStats->{'lost'}->{$t}->{'holding'},
                            holdingDel=>$adlfStats->{'delete'}->{$t}->{'holding'},
                            titleFound=>0,
                            holdingFound=>$adlfStats->{'found'}->{$t}->{'holding'},
                            title_asOfDateE=>$availStatsE->{$t}->{'record'},
                            holding_asOfDateE=>$availStatsE->{$t}->{'holding'}
          };     
    }

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

sub  getAvailableAsDateOf{
    my ($dbh,$date)=@_;
    my @stats =getStats($dbh,"",$date);
    my $ret;
    foreach my $rec(@stats){
        foreach my $type(keys %{$rec}){
            if($rec->{'status'} eq 'add' || $rec->{'status'} eq 'found'){
                if(!$ret->{$type}->{$rec->{'record_Holding'}} ){
                    $ret->{$type}->{$rec->{'record_Holding'}}   = $rec->{$type};
                }
                else{
                    $ret->{$type}->{$rec->{'record_Holding'}}   += $rec->{$type};
                }
            }
            else{
                if(!$ret->{$type}->{$rec->{'record_Holding'}}){
                    $ret->{$type}->{$rec->{'record_Holding'}}   = $rec->{$type};
                }
                else{
                    $ret->{$type}->{$rec->{'record_Holding'}}   -= $rec->{$type};
                }
            }

        }

    }
    $ret->{'Total'}->{'record'}  = $ret->{'Print'}->{'record'}
                                         + $ret->{'Non Print'}->{'record'};
    $ret->{'Total'}->{'holding'} = $ret->{'Print'}->{'holding'}
                                         + $ret->{'Non Print'}->{'holding'};
    
    return $ret;

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

sub  getAddDelLostFound_bk{
    my ($dbh,$dateFrom,$dateTo)=@_;
    my @stats =getStats($dbh,$dateFrom,$dateTo);
    my $ret;
    foreach my $rec(@stats){
        foreach my $type(keys %{$rec}){
            if($rec->{'status'} eq 'lost' || $rec->{'status'} eq 'delete'){
                if (!$ret->{'lostDeleted'}->{$type}->{$rec->{'record_Holding'}}){
                    $ret->{'lostDeleted'}->{$type}->{$rec->{'record_Holding'}} = $rec->{$type};
                }
                else{
                    $ret->{'lostDeleted'}->{$type}->{$rec->{'record_Holding'}} += $rec->{$type};
                }
            }
            else{
                $ret->{$rec->{'status'}}->{$type}->{$rec->{'record_Holding'}} =  $rec->{$type};
            }
         }
         $ret->{$rec->{'status'}}->{'Total'}->{'record'} =  $ret->{$rec->{'status'}}->{'Print'}->{'record'}
                                                         + $ret->{$rec->{'status'}}->{'Non Print'}->{'record'} ;


         $ret->{$rec->{'status'}}->{'Total'}->{'holding'} = $ret->{$rec->{'status'}}->{'Print'}->{'holding'}
                                                          + $ret->{$rec->{'status'}}->{'Non Print'}->{'holding'} ;

         $ret->{'lostDeleted'}->{'Total'}->{'record'} = $ret->{'lostDeleted'}->{'Print'}->{'record'}
                                                      + $ret->{'lostDeleted'}->{'Non Print'}->{'record'}; 
                                                      

         $ret->{'lostDeleted'}->{'Total'}->{'holding'} = $ret->{'lostDeleted'}->{'Print'}->{'holding'}
                                                       + $ret->{'lostDeleted'}->{'Non Print'}->{'holding'} ;


    
    }
    return $ret;
}
#####################################################################

sub  getAddDelLostFound{
    my ($dbh,$dateFrom,$dateTo)=@_;
    my @stats =getStats($dbh,$dateFrom,$dateTo);
    my $ret;
    foreach my $rec(@stats){
        foreach my $type(keys %{$rec}){
            $ret->{$rec->{'status'}}->{$type}->{$rec->{'record_Holding'}} =  $rec->{$type};
         
         }
         $ret->{$rec->{'status'}}->{'Total'}->{'record'} =  $ret->{$rec->{'status'}}->{'Print'}->{'record'}
                                                         + $ret->{$rec->{'status'}}->{'Non Print'}->{'record'} ;


         $ret->{$rec->{'status'}}->{'Total'}->{'holding'} = $ret->{$rec->{'status'}}->{'Print'}->{'holding'}
                                                          + $ret->{$rec->{'status'}}->{'Non Print'}->{'holding'} ;

         $ret->{'deleted'}->{'Total'}->{'record'} = $ret->{'deleted'}->{'Print'}->{'record'}
                                                      + $ret->{'deleted'}->{'Non Print'}->{'record'}; 
                                                      
         $ret->{'lost'}->{'Total'}->{'holding'} = $ret->{'lost'}->{'Print'}->{'holding'}
                                                       + $ret->{'lost'}->{'Non Print'}->{'holding'} ;
         $ret->{'deleted'}->{'Total'}->{'holding'} = $ret->{'deleted'}->{'Print'}->{'holding'}
                                                       + $ret->{'deleted'}->{'Non Print'}->{'holding'} ;



    
    }
    return $ret;
}

#####################################################################