#!/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::Report qw(
    getAverageCopyrightDate 
     _splitDeweyNumber
     _first_digit_matched
     _first_two_digits_matched    
    _first_digit_unmatched
     createRegExp

);


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

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




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


    
    my $pref = tmpl_preference($dbh);
    my $pagesize = 20;
    my $pNum = $input->{'pNum'};
    $pNum = 1 if ( !$pNum );

    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";
        $dateTo  = $dateToday;
    }
    my $sort    = $input->{'sort'};
    $sort = 'ondate' if ( !$sort );

   
    my $dateFirst = $pref->{'dateFirst'};
    my $dateLast  = $pref->{'dateLast'};
    
    $dateFrom = $dateFirst if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateLast if ( !$dateTo );
    $dateTo .= " 23:59:59";

    my (@reportTotal,@reportPrint,@reportNonPrint);

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

    $template->param(
        sort           => $sort,
        pNum           => $pNum,
        BEDSreport     => 1,
        dateToday      => $dateToday
    );
    
    if($dateRange eq 'all'){
        $template->param(
            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 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)=@_;
    getTotalTitleInLibrary($dbh,$dateFrom);
    getTotalTitleInLibrary($dbh,$dateTo);
    getTotalHoldingInLibrary($dbh,$dateFrom);
    getTotalHoldingInLibrary($dbh,$dateTo);
    getTotalTitleDeleted($dbh,$dateFrom,$dateTo);
    getTotalHoldingLostDel($dbh,$dateFrom,$dateTo);
    getTotalHoldingFound($dbh,$dateFrom, $dateTo);
    getTotalTitleAdded($dbh,$dateFrom,$dateTo);
    getTotalHoldingAdded($dbh,$dateFrom, $dateTo);
    for(my $i=2;$i<4; $i++){
          my $prefix = @reportPrint[$i]->{'type'};
          @reportPrint[$i]->{'title_asOfDateB'}  =getTotalTitleByCategory($dbh,$dateFrom,$prefix );
          @reportPrint[$i]->{'holding_asOfDateB'}=getTotalHoldingByCategory($dbh,$dateFrom,$prefix); 
          @reportPrint[$i]->{'title_asOfDateE'}  =getTotalTitleByCategory($dbh,$dateTo,$prefix);
          @reportPrint[$i]->{'holding_asOfDateE'}=getTotalHoldingByCategory($dbh,$dateTo,$prefix); 
          @reportPrint[$i]->{'holdingAdded'}     =getTotalHoldingAddedbyPrefix($dbh,$dateFrom, $dateTo,$prefix);      
          @reportPrint[$i]->{'titleAdded'}       =getTotalTitleAddedByPrefix($dbh,$dateFrom, $dateTo,$prefix);        
          @reportPrint[$i]->{'titleDelLost'}     =getTotalTitleDeletedByPrefix($dbh,$dateFrom, $dateTo,$prefix);      
          @reportPrint[$i]->{'holdingDelLost'}   =getTotalHoldingDeletedByPrefix($dbh,$dateFrom, $dateTo,$prefix);    
          @reportPrint[$i]->{'holdingFound'}     =getTotalHoldingFoundByPrefix($dbh,$dateFrom, $dateTo,$prefix);
    }

    for (my $i=0;$i<scalar @reportTotal;$i++){
        @reportTotal[$i]->{'titleFound'}= @reportTotal[$i]->{'title_asOfDateE'} 
                                        - @reportTotal[$i]->{'title_asOfDateB'}   
                                        - @reportTotal[$i]->{'titleAdded'}
                                        + @reportTotal[$i]->{'titleDelLost'};
    }
    for (my $i=0;$i<scalar @reportPrint ;$i++){
        @reportPrint[$i]->{'titleFound'}= @reportPrint[$i]->{'title_asOfDateE'} 
                                        - @reportPrint[$i]->{'title_asOfDateB'}   
                                        - @reportPrint[$i]->{'titleAdded'}
                                        + @reportPrint[$i]->{'titleDelLost'};
    }
    for (my $i=0;$i<scalar @reportNonPrint;$i++){
        @reportNonPrint[$i]->{'titleFound'}= @reportNonPrint[$i]->{'title_asOfDateE'} 
                                           - @reportNonPrint[$i]->{'title_asOfDateB'}   
                                           - @reportNonPrint[$i]->{'titleAdded'}
                                           + @reportNonPrint[$i]->{'titleDelLost'};
    }

}


#####################################################################
sub getTotalTitleInLibrary{
   my ($dbh,$date) = @_; 
   my $sth;
   my $totalTitle=0;
   
   my $sql="select  m.rid,mid(m1.leader,7,2) as mType,mid(m1.cf_008,34,1) as lType
    from
    (select  t2.rid,t2.count
    from 
        (select rid,count(barcode) as count from (select rid,barcode from opl_item where dateImport <?) as t1         
        group by rid) as t2
        left outer join 
        (select i.rid ,count(i.barcode) as count
        from opl_item i inner  join 
            (select t3.* from
                (select * from opl_itemstatus where ondate < ? && status<>6) as t3
                left outer join 
                (select * from opl_itemstatus where ondate <? && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode group by rid 
        ) as t6 on t6.rid=t2.rid
    where t2.count >t6.count || t6.count is null
    )as m  
    inner  join opl_marcRecord m1 on m.rid=m1.rid "; 
     
 
    $sth= $dbh->prepare($sql);
    $sth->execute($date,$date,$date);
    while (my $rec=$sth->fetchrow_hashref){
       if($date eq $dateFrom){ 
            filterResult($rec,'title_asOfDateB');
       }
       else{
            filterResult($rec,'title_asOfDateE');
       }
    }
       
  $sth->finish;
   
}

#####################################################################
sub filterResult{
    # Print Items:       base on Leader  field  -- character positions  06  --    “a”    
    # Fiction Items      base on control field 008   -- character positions  34  --    “1”
    # NonFiction Items:  base on control field 008   -- character positions  34  --   “0”
    # Pro                base on Callnumber    --  contain string         “PRO”
    # Ref                base on Callnumber    --  contain string         “REF”
    # Serial:            base on Leader  field  -- character positions  06  --   “as”
    # Short stories      base on control field 008   -- character positions  34  --  “ j”
    # NonPrint Items:
    # Video:             base on Leader  field  -- character positions  06  --   “g”
    # Audio              base on Leader  field  -- character positions  06  --   “i”
    # Multi Media        base on Leader  field  -- character positions  06  --   “m”
    # CD / Cassette      base on Leader  field  -- character positions  06  --   “j”
    # Kit  	             base on Leader  field  -- character positions  06  --   “o”
    # Mixed Material     base on Leader  field  -- character positions  06  --   “p”
    # Notated Music      base on Leader  field  -- character positions  06  --   “c”
    # Map                base on Leader  field  -- character positions  06  --   “e”
    
    my($rec,$colName)=@_;
    @reportTotal[0]->{$colName} +=1;
    if($rec->{'mType'}=~ m/^a/ ){
        @reportTotal[1]->{$colName} +=1;
        if($rec->{'lType'} eq '1'){
            @reportPrint[0]->{$colName} +=1;
        }
        else{
            @reportPrint[1]->{$colName} +=1;
        
            if($rec->{'lType'} eq 'j'){
                @reportPrint[5]->{$colName} +=1;
            }
            elsif($rec->{'mType'} eq 'as'){
                @reportPrint[4]->{$colName} +=1;
            }  
        }
    }
    else{
        @reportTotal[2]->{$colName} +=1;

        if($rec->{'mType'} =~ m/^g/){
            @reportNonPrint[0]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^i/ ){
            @reportNonPrint[1]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^m/){
            @reportNonPrint[2]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^j/){
            @reportNonPrint[3]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^o/){
            @reportNonPrint[4]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^p/){
            @reportNonPrint[5]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^c/){
            @reportNonPrint[6]->{$colName} +=1;
        }
        elsif($rec->{'mType'}  =~ m/^e/){
            @reportNonPrint[7]->{$colName} +=1;
        }
      
    }
}

#####################################################################
#
#  function getTotalHoldingInLibrary
#  
#  used to get total  holding in database (excluding deleted items)
#
#  $type: is the 7th character in the leader field where:
#
#        a: print Item
#        g: video  
#        i: audio
#        m: multimedia
#        j: cassette or cd rom
#        o: kit
#        c:
#        k:
#    where ondate <? && status<>6 ??????
#####################################################################
sub getTotalHoldingInLibrary{
   my ($dbh,$date) = @_; 
   my $sth;
   my $totalHolding=0;

   my $sql ="
    select m.rid,m.barcode,mid(m1.leader,7,2) as mType,mid(m1.cf_008,34,1) as lType
    from
    (select  t2.rid,t2.barcode
    from 
        (select rid,barcode from opl_item where dateImport <?) as t2
        left outer join 
        (select i.barcode
        from opl_item i inner  join 
            (select t3.* from
                (select * from opl_itemstatus where ondate <? && status<>6) as t3
                left outer join 
                (select * from opl_itemstatus where ondate <? && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode
        ) as t6 on t6.barcode =t2.barcode
    where  t6.barcode is null
    )as m 
    inner  join opl_marcRecord m1 on m.rid=m1.rid ";

     
    $sth= $dbh->prepare($sql);
    $sth->execute($date,$date,$date);
    while(my $rec =$sth->fetchrow_hashref){
       if($date eq $dateFrom){ 
        filterResult($rec,'holding_asOfDateB');
       }
       else{
        filterResult($rec,'holding_asOfDateE');
       }
    }
   
  $sth->finish;
   
}


#####################################################################
#
#   function getTotalHoldingLostDel
#   
#   (for print title)    
#
#####################################################################

sub getTotalHoldingLostDel{
   my ($dbh,$dateFrom, $dateTo) = @_; 
   my $totalHolding; 
   my $sth ;

   my $sql ="select m.rid,m.barcode,mid(m1.leader,7,2) as mType,mid(m1.cf_008,34,1) as lType
    from
    (select  t2.rid,t6.barcode
    from
        (select rid, barcode from (select rid,barcode from opl_item) as t1 group by rid) as t2
        inner  join
        (select i.rid ,i.barcode 
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate between ? and ? && status<>6) as t3
                left outer join
                (select * from opl_itemstatus where ondate between ? and ? && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode 
        ) as t6 on t6.rid=t2.rid    
    )as m
    inner join opl_marcRecord m1 on m.rid=m1.rid ";
 
  
   $sth= $dbh->prepare($sql);

   $sth->execute($dateFrom, $dateTo,$dateFrom, $dateTo);
     while(my $rec =$sth->fetchrow_hashref){
       filterResult($rec,'holdingDelLost');
    }
    
   $sth->finish;
}
#####################################################################
sub getTotalHoldingFound{
   my ($dbh,$dateFrom, $dateTo) = @_; 
   my $totalHolding; 
   my $sth ;

   my $sql ="select m.rid,m.barcode,mid(m1.leader,7,2) as mType,mid(m1.cf_008,34,1) as lType
    from
   (select i.rid,i.barcode from opl_item i inner join
(select s2.barcode
from
(
select t3.* from
                (select * from opl_itemstatus where   ondate <  ?  && status <> 6 ) as t3
                left outer join
                (select * from opl_itemstatus where  ondate <? && status <> 6 ) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
) as s1 
left outer join 
(select * from opl_itemstatus where ondate between ? and ?) as s2 
on s1.barcode=s2.barcode && s1.id <s2.id && s2.status not in(6,3,5) where s2.barcode is not null)
as t on t.barcode=i.barcode
   )as m
    inner join opl_marcRecord m1  on m.rid=m1.rid ";
    
    
   $sth= $dbh->prepare($sql);

   $sth->execute($dateFrom,$dateFrom, $dateFrom, $dateTo);
   while(my $rec =$sth->fetchrow_hashref){
       filterResult($rec,'holdingFound');
    }
   
   $sth->finish;
}
#####################################################################

sub getTotalTitleFound{
    my ($dbh,$dateFrom, $dateTo,$mtype,$ltype) = @_;
   my $totalTitle=0; 
   my $sth ;

   my $sql ="select m.rid,mid(m1.leader,7,2) as mType,mid(m1.cf_008,34,1) as lType
    from
   (select i.rid,i.barcode from opl_item i inner join
(select s2.barcode
from
(
select t3.* from
                (select * from opl_itemstatus where   ondate <  ?  && status <> 6 ) as t3
                left outer join
                (select * from opl_itemstatus where  ondate <? && status <> 6 ) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
) as s1 
left outer join 
(select * from opl_itemstatus where ondate between ? and ?) as s2 
on s1.barcode=s2.barcode && s1.id <s2.id && s2.status not in(6,3,5) where s2.barcode is not null)
as t on t.barcode=i.barcode
   )as m
    inner join opl_marcRecord m1 on m.rid=m1.rid  ";
    
   $sth= $dbh->prepare($sql);
   $sth->execute($dateFrom,$dateFrom, $dateFrom, $dateTo);
   while(my ($t) =$sth->fetchrow_array){
      $totalTitle +=1  if(isTitleFound($dbh,$dateFrom,$t) ==1 );
   }
    
   $sth->finish;
   return $totalTitle;
}
#####################################################################

sub getTotalTitleFoundByPrefix{
    my ($dbh,$dateFrom, $dateTo,$prefix) = @_;
   my $totalTitle=0; 
   my $sth ;

   my $sql ="select distinct m.rid
    from
   (select i.rid,i.barcode from opl_item i inner join
(select s2.barcode
from
(
select t3.* from
                (select * from opl_itemstatus where   ondate <  ?  && status <> 6 ) as t3
                left outer join
                (select * from opl_itemstatus where  ondate <? && status <> 6 ) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
) as s1 
left outer join 
(select * from opl_itemstatus where ondate between ? and ?) as s2 
on s1.barcode=s2.barcode && s1.id <s2.id && s2.status not in(6,3,5) where s2.barcode is not null)
as t on t.barcode=i.barcode &&  callNumber regExp ?
   )as m";
    
  
   $sth= $dbh->prepare($sql);
   $sth->execute($dateFrom,$dateFrom, $dateFrom, $dateTo,$prefix);
   while(my ($t) =$sth->fetchrow_array){
      $totalTitle +=1  if(isTitleFound($dbh,$dateFrom,$t) ==1 );
   }
    
   $sth->finish;
   return $totalTitle;
}

#####################################################################
sub isTitleFound{
   my ($dbh,$dateFrom,$rid) = @_;
   my $sth ;

   my $sql ="select  count(t2.rid)
    from
        (select rid,count(barcode) as count from (select rid,barcode from opl_item where rid=? && dateImport < ? ) 
        as t1 group by rid) as t2
        inner  join
        (select i.rid ,count(i.barcode) as count
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate< ?  ) as t3
                left outer join
                (select * from opl_itemstatus where ondate < ? ) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode group by rid
        ) as t6 on t6.rid=t2.rid
    where t2.count =t6.count ";
   $sth= $dbh->prepare($sql);

   $sth->execute($rid,$dateFrom,$dateFrom, $dateFrom);
   my ($count)=$sth->fetchrow_array;
   $sth->finish;
   return $count;

 
}
#####################################################################
#
#   function getTotalHoldingAdded
#      $mType: (material type)
#        a: print Item
#        g: video  
#        i: audio
#        m: multimedia
#        j: cassette or cd rom
#        o: kit
#        c:
#        k:

#####################################################################
sub getTotalHoldingAdded{
   my ($dbh,$dateFrom, $dateTo) = @_;  
   my $sth;
   my $sql = "select   m.rid,i.barcode,mid(m.leader,7,2) as mType,mid(m.cf_008,34,1) as lType
              from     opl_item i inner join opl_marcRecord m on i.rid=m.rid
              where    i.dateImport between ? and ? ";
     
    $sth= $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
   while(my $rec =$sth->fetchrow_hashref){
       filterResult($rec,'holdingAdded');
    }

}


#####################################################################
#
#   function getTotalTitleAdded
#      $mtype: (material type)
#        a: print Item
#        g: video  
#        i: audio
#        m: multimedia
#        j: cassette or cd rom
#        o: kit
#        c:
#        k:
#
#####################################################################
sub getTotalTitleAdded{
   my ($dbh,$dateFrom,$dateTo) = @_;  
   my $sth;
   my $sql="select m.rid,mid(m.leader,7,2) as mType,mid(m.cf_008,34,1) as lType
            from     opl_item i inner join opl_marcRecord m on i.rid=m.rid 
            group by rid having min(dateImport) between ? and ? ";

   $sth= $dbh->prepare($sql);
   $sth->execute($dateFrom, $dateTo);
   while(my $rec =$sth->fetchrow_hashref){
       filterResult($rec,'titleAdded');
   }
    
}
#####################################################################
#
#   function getTotalTitleDeleted
#
#
#####################################################################
sub getTotalTitleDeleted{
   my ($dbh,$dateFrom,$dateTo) = @_; 
   my $sth;
   my $sql="
    select m.rid,mid(m1.leader,7,2) as mType,mid(m1.cf_008,34,1) as lType
    from
    (    
  
 select distinct t0.rid from

(select rid,count(barcode) as count from opl_item where dateImport <? group by rid  )as t0
inner join 
(
select t4.rid,t4.count
from 
(
 select i.rid, count(i.barcode) as count from opl_item i inner join 
 (
  select t1.barcode from
 
   (select * from opl_itemstatus where ondate <? && status<>6) as t1
   left outer join 
   (select * from opl_itemstatus where ondate <? && status<>6) as t2
   on  t1.barcode =t2.barcode &&  t1.id <t2.id
   where t2.id is null && t1.status in(3,5)
 )
as t3
on t3.barcode=i.barcode 
group by i.rid ) as t4
inner join

(select i.rid from opl_item i inner join
 (
  select t1.barcode from
 
   (select * from opl_itemstatus where ondate between  ? and ? && status<>6) as t1
   left outer join 
   (select * from opl_itemstatus where ondate between  ? and ? && status<>6) as t2
   on  t1.barcode =t2.barcode &&  t1.id <t2.id
   where t2.id is null && t1.status in(3,5)
 ) as t5
on i.barcode=t5.barcode) as t6 
on t4.rid=t6.rid)
as t7 on t0.rid=t7.rid
where t0.count =t7.count   
  )as m
    
    inner join opl_marcRecord m1 on m.rid=m1.rid ";
    
    
    

    $sth= $dbh->prepare($sql);
    $sth->execute($dateTo,$dateTo,$dateTo,$dateFrom,$dateTo,$dateFrom,$dateTo);
    while(my $rec =$sth->fetchrow_hashref){
       filterResult($rec,'titleDelLost');
    }
    
    $sth->finish;

}



#####################################################################
sub getTotalTitleByCategory{
   my ($dbh,$date,$prefix) = @_;  
   my $sth;
   my $totalTitle = 0;
   my $sql="select  count(t2.rid)
    from 
        (select rid,count(barcode) as count from (
           select rid,barcode from opl_item where dateImport <? && callNumber regExp ?) as t1 
           group by rid) as t2
        left outer join 
        (select i.rid ,count(i.barcode) as count
        from opl_item i inner  join 
            (select t3.* from
                (select * from opl_itemstatus where ondate < ? && status<>6) as t3
                left outer join 
                (select * from opl_itemstatus where ondate <? && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode && i.callNumber regExp ? group by rid 
        ) as t6 on t6.rid=t2.rid
    where t2.count >t6.count || t6.count is null  ";
   
   $sth= $dbh->prepare($sql);
   $sth->execute($date,$prefix,$date,$date,$prefix);
   ($totalTitle)=$sth->fetchrow_array;
   $sth->finish;
  return $totalTitle;   
}
#####################################################################
sub getTotalHoldingByCategory{
   my ($dbh,$date,$prefix) = @_;  
   my $sth;
   my $totalHolding = 0;
   my $sql="select  count(t2.barcode)
    from 
        (select rid,barcode from opl_item where dateImport < ? && callNumber regExp ?) as t2
        left outer join 
        (select i.barcode
        from opl_item i inner  join 
            (select t3.* from
                (select * from opl_itemstatus where ondate < ? && status<>6) as t3
                left outer join 
                (select * from opl_itemstatus where ondate < ? && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode && i.callNumber regExp ? 
        ) as t6 on t6.barcode =t2.barcode
    where  t6.barcode is null ";
   
   $sth= $dbh->prepare($sql);
   $sth->execute($date,$prefix,$date,$date,$prefix);
   ($totalHolding)=$sth->fetchrow_array;
   $sth->finish;
  return $totalHolding;   
}

#####################################################################
#
#   function getTotalTitleAddedByPrefix
#      $prefix: pro|ref
#      $mType: (material type)
#        a: print Item
#        g: video  
#        i: audio
#        m: multimedia
#        j: cassette or cd rom
#        o: kit
#        c:
#        k:
#
#####################################################################

sub getTotalTitleAddedByPrefix{
  my ($dbh,$dateFrom,$dateTo,$prefix) = @_;  
   my $sth;
   my $totalTitle=0;
   my $sql="select count(t.rid) 
    from ( select   i.rid 
             from     opl_item i inner join opl_marcRecord m1 on i.rid=m1.rid 
             where  callNumber regExp ?
             group by rid having min(dateImport) between ? and ?) as t";

   $sth= $dbh->prepare($sql);
   $sth->execute($prefix,$dateFrom, $dateTo);
   ($totalTitle)=$sth->fetchrow_array;
    $sth->finish;
   return $totalTitle;
}

#####################################################################
#
#   function getTotalHoldingAddedbyPrefix
#      $prefix: pro|ref
#      $mType: (material type)
#        a: print Item
#        g: video  
#        i: audio
#        m: multimedia
#        j: cassette or cd rom
#        o: kit
#        c:
#        k:

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

sub getTotalHoldingAddedbyPrefix{
   my ($dbh,$dateFrom, $dateTo,$prefix) = @_;  
   my $sth;
   my $totalHolding =0;
   my $sql = "select     count(i.barcode) 
    from        opl_item i 
    where      callNumber regExp ? && dateImport between ? and ?";
    
    $sth= $dbh->prepare($sql);
    $sth->execute($prefix,$dateFrom, $dateTo);
    ($totalHolding)=$sth->fetchrow_array;

   return $totalHolding;
 

}

#####################################################################
#
#   function getTotalHoldingDeletedByPrefix
#

#
#####################################################################
sub getTotalHoldingDeletedByPrefix{
   my ($dbh,$dateFrom, $dateTo,$prefix) = @_; 
   my $totalHolding; 
   my $sth ;

   my $sql ="select  count(t2.barcode)
    from
        (select rid, barcode from (select rid,barcode from opl_item where callNumber regExp ?) as t1 group by rid) as t2
        inner  join
        (select i.rid ,i.barcode 
        from opl_item i inner  join
            (select t3.* from
                (select * from opl_itemstatus where ondate between ? and ? && status<>6) as t3
                left outer join
                (select * from opl_itemstatus where ondate between ? and ? && status<>6) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
            )as t5 on i.barcode=t5.barcode && callNumber regExp ?
        ) as t6 on t6.rid=t2.rid " ;

     $sth= $dbh->prepare($sql);

   $sth->execute($prefix,$dateFrom, $dateTo,$dateFrom, $dateTo,$prefix);
   ($totalHolding)=$sth->fetchrow_array;
    
   $sth->finish;
   return $totalHolding;
}

#####################################################################
#
#   function getTotalTitleDeletedByPrefix
#
#
#####################################################################

sub getTotalTitleDeletedByPrefix{
    my ($dbh,$dateFrom,$dateTo,$prefix) = @_; 
   my $sth;
   my $totalTitle=0;
   my $sql="
select count(distinct t0.rid )from
(select rid,count(barcode) as count from opl_item where dateImport <? && callNumber regExp ?  group by rid  )as t0
inner join 
(
select t4.rid,t4.count
from 
(
 select i.rid, count(i.barcode) as count from opl_item i inner join 
 (
  select t1.barcode from
 
   (select * from opl_itemstatus where ondate <? && status<>6) as t1
   left outer join 
   (select * from opl_itemstatus where ondate <? && status<>6) as t2
   on  t1.barcode =t2.barcode &&  t1.id <t2.id
   where t2.id is null && t1.status in(3,5)
 )
as t3
on t3.barcode=i.barcode && callNumber regExp ?
group by i.rid ) as t4
inner join

(select i.rid from opl_item i inner join
 (
  select t1.barcode from
 
   (select * from opl_itemstatus where ondate between  ? and  ? && status<>6) as t1
   left outer join 
   (select * from opl_itemstatus where ondate between  ? and ? && status<>6) as t2
   on  t1.barcode =t2.barcode &&  t1.id <t2.id
   where t2.id is null && t1.status in(3,5)
 ) as t5
on i.barcode=t5.barcode && callNumber regExp ? )as t6 
on t4.rid=t6.rid)
as t7 on t0.rid=t7.rid
where t0.count =t7.count   
    
   ";

    $sth= $dbh->prepare($sql);
    $sth->execute($dateTo,$prefix,$dateTo,$dateTo,$prefix,$dateFrom,$dateTo,$dateFrom,$dateTo,$prefix);
   ($totalTitle)=$sth->fetchrow_array;
   
  $sth->finish;
  return $totalTitle;
}



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

sub getTotalHoldingFoundByPrefix{
   my ($dbh,$dateFrom, $dateTo,$prefix) = @_; 
   my $totalHolding; 
   my $sth ;

   my $sql ="select count(distinct i.barcode) from opl_item i inner join
(select s2.barcode
from
(
select t3.* from
                (select * from opl_itemstatus where   ondate <  ?  && status <> 6 ) as t3
                left outer join
                (select * from opl_itemstatus where  ondate <? && status <> 6 ) as t4
            on t3.barcode=t4.barcode && t3.id <t4.id where t4.id is null && t3.status in (3,5)
) as s1 
left outer join 
(select * from opl_itemstatus where ondate between ? and ?) as s2 
on s1.barcode=s2.barcode && s1.id <s2.id && s2.status not in(6,3,5) where s2.barcode is not null)
as t on t.barcode=i.barcode && i.callNumber regExp ?
   ";
   $sth= $dbh->prepare($sql);

   $sth->execute($dateFrom,$dateFrom, $dateFrom, $dateTo,$prefix);
   ($totalHolding)=$sth->fetchrow_array;
    
   $sth->finish;
   return $totalHolding;
}