#!/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
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
    tmpl_preference
);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);


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/BEDSreport.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 $dateFirst = $pref->{'dateFirst'};
    my $dateLast  = $pref->{'dateLast'};
    
    my $dateFrom= $input->{'dateFrom'};
    my $dateTo  = $input->{'dateTo'};
    my $dateRange= $input->{'dateRangeOpt'};
    
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
    
    $dateFrom = $dateFirst if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateLast if ( !$dateTo );
    $dateTo .= " 23:59:59";
    my $literaryFormMap_display={
        'nonFiction'     	=>'Non Fiction ' ,
        'fiction'       	=>'Fiction ' ,
        'comicStrips'     	=>'Comic strips ' ,
        'dramas'        	=>'Dramas ' ,
        'essays'        	=>'Essays ' ,
        'novels'        	=>'Novels ' ,
        'humor'         	=>'Humor ' ,
        'letters'       	=>'Letters ' ,
        'shortStories'  	=>'Short stories ' ,
        'mixedDorms'    	=>'Mixed Dorms ' ,
        'poetry'        	=>'Poetry ' ,
        'speeches'      	=>'Speeches ' ,
        'unknown'       	=>'Unknown '  
    };
    my $recordFormatMap_display={
        'book'     	        =>'Book ' ,
        'journal'     	    =>'Journal ' ,
        'sheetMusic'        =>'Sheet music ' ,
        'artifact'     	    =>'Artifact ' ,
        'photoPoster'       =>'Photo poster ' ,
        'musicCassette'     =>'Music cassette ' ,
        'bookOnTape'        =>'Book on Tape ' ,
        'movie'     	    =>'movie ' ,
        'libraryKit'        =>'Library Kit ' ,
        'mixedMaterial'     =>'Mixed material ' ,
        'map'     	        =>'Map ' ,
        'internet'     	    =>'Internet ' ,
        'electronicMedia'	=>'Electronic Media ' ,
        'CDMusic'     	    =>'CD Music ' ,
        'compactDisc'       =>'Compact disc '
        };
    my $recordFormatMap_icon={
        'book'     	        =>'book.png' ,
        'journal'     	    =>'journal.png' ,
        'sheetMusic'        =>'sheetMusic.png' ,
        'artifact'     	    =>'artifact.png' ,
        'photoPoster'       =>'photoPoster.png' ,
        'musicCassette'     =>'musicCassette.png' ,
        'bookOnTape'        =>'bookOnTape.png' ,
        'movie'     	    =>'movie.png' ,
        'libraryKit'        =>'libraryKit.png' ,
        'mixedMaterial'     =>'shim.png' ,
        'map'     	        =>'map.png' ,
        'internet'     	    =>'internet.png' ,
        'electronicMedia'	=>'electronicMedia.png ' ,
        'CDMusic'     	    =>'CDMusic.png' ,
        'compactDisc'       =>'compactDisc.png '
       };    
    my $literaryFormList = getLiteraryFormList($dbh);
    my ($recFormatList_prt,$recFormatList_nonPrt)    = getRecFormatList($dbh);
    my $prefixList       = getPrefixList($dbh);
    my @bStatusList       = qw(new deleted );
    my @hStatusList       = qw(import deleted damaged lost missing found);
    my @reportTotal;
    my  ($rptTotal,$rptPrint,$rptNonPrint,,$rptPrefix); 
    my $bedsRpt = genBedsRpt($dbh, { dateFrom       => $dateFrom,
                                     dateTo         => $dateTo,
                                    });
    
    $template->param(
        BEDSreport      => 1,
        rpt_print       => $rptPrint,
        rpt_nonPrint    => $rptNonPrint,
        rpt_total       => $rptTotal,
        rpt_Prefix      => $rptPrefix,
        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"
       );
    }


    my $msgMap            =loc_getMsgFile('report/reports.msg');
    loc_write($template,$msgMap);

tmpl_write($dbh, $cgi, $cookie, $template);

################################################################################
sub genBedsRpt(){
    my($dbh, $params) =@_;
#   $params = {
#       dateFrom     => '',
#       dateTo       => '',
#     }
    
    my @incRecFormat  =();                             
    my @exclRecFormat =();  
    
    # Print Items
    @exclRecFormat =();                             
    $params->{'incRecFormat'}  = $recFormatList_prt;
    $params->{'exclRecFormat'} = \@exclRecFormat;
    $rptPrint            =  genBibRecordRpt($dbh,$params);
    my $rptPrint_holding =  genHoldingRpt($dbh,$params);
    $rptPrint = mergeReport($rptPrint,$rptPrint_holding);  

    # Non_Print Item
    @exclRecFormat = ();#qw(book journal sheetMusic);  
    $params->{'incRecFormat'}  = $recFormatList_nonPrt;
    $params->{'exclRecFormat'} = \@exclRecFormat;
    $rptNonPrint            = genBibRecordRpt($dbh,$params);
    my $rptNonPrint_holding = genHoldingRpt ($dbh,$params);
    $rptNonPrint = mergeReport($rptNonPrint,$rptNonPrint_holding);  
    
    # Total Item  
    $rptTotal = genTotalReport($rptPrint,$rptNonPrint );
    #Prefix Report
    $rptPrefix = genHoldingRptByPrefix($dbh,$params);
}
#####################################################################
sub genTotalReport{
    my($r1,$r2)=@_;
    my $ret; my $print;
    foreach my $rRow1(@$r1){
        my $lf1 =$rRow1->{'litFrmList'};
        foreach my $lfRow1(@$lf1){
            foreach my $sType(keys %{$lfRow1}){
                $print->{$sType} += $lfRow1->{$sType};
            }
        }
    
    }
    $print->{'type'} ='Print';
    push @$ret,$print;
    my $nonPrt;
    foreach my $rRow2(@$r2){
        my $lf2 =$rRow2->{'litFrmList'};
        foreach my $lfRow2(@$lf2){
            foreach my $sType(keys %{$lfRow2}){
                $nonPrt->{$sType} += $lfRow2->{$sType};
            }
        }
    
    }
    $nonPrt->{'type'} ='NonPrint';
    push @$ret,$nonPrt;

    return $ret;
}

#####################################################################
sub mergeReport_bk{
    my($r1,$r2)=@_;
    foreach my $tRow(@$r1){
        foreach my $hRow(@$r2){
            if($hRow->{'type'} eq $tRow->{'type'}){
                foreach my $sType(keys %{$hRow}){
                     next if($sType eq 'type');
                    $tRow->{$sType} =$hRow->{$sType};
                }
                last;
            }
        }
    }
    return $r1;
}

sub mergeReport_bb{
    my($r1,$r2)=@_; my $rowSpan =0;
    open debug,">>/tmp/dd";
    print debug ref($r1), "\n";
    print debug ref($r2), "\n";
    close debug;
    foreach my $rRow1(@$r1){  
        $rowSpan =0;      
        foreach my $rRow2(@$r2){
            #$rRow1 = $rRow2 if(!defined $rRow1->{'recFmt'});
            if($rRow1->{'recFmt'} eq $rRow2->{'recFmt'}){
                 my ($lf1,$lf2) =($rRow1->{'litFrmList'}, $rRow2->{'litFrmList'}); 
                 foreach my $lfRow2(@$lf2){
                    my $tNew =1; 
                    foreach my $lfRow1(@$lf1){
                        if($lfRow1->{'type'} eq $lfRow2->{'type'}){
                            $tNew =0;   last;
                        }
                    } 
                    if($tNew >0){
                         push @{$rRow1->{'litFrmList'}}, $lfRow2;
                         #$rowSpan++;
                    }
                 }
                 foreach my $lfRow1(@$lf1){
                    foreach my $lfRow2(@$lf2){
                        if($lfRow1->{'type'} eq $lfRow2->{'type'}){  
                            $rowSpan ++;                          
                            foreach my $sType(keys %{$lfRow2}){
                                next if($sType eq 'type');
                                $lfRow1->{$sType} = $lfRow2->{$sType} if (! defined $lfRow1->{$sType}); 
                            }
                            last;
                        }#End IF
                    }
                 }

            }

        }
        $rRow1->{'rowSpan'} = $rowSpan;
    }   

    return $r1;
}

sub mergeReport{
    my($r1,$r2)=@_; 
    my $rowSpan =0;
    foreach my $recFrm(sort keys (%$r1)){ 
        if(){
        }
    }
    foreach my $recFrm(sort keys (%$r2)){
    }
        $rowSpan =0;      
        foreach my $rRow2(@$r2){
            if($rRow1->{'recFmt'} eq $rRow2->{'recFmt'}){
                 my ($lf1,$lf2) =($rRow1->{'litFrmList'}, $rRow2->{'litFrmList'}); 
                 foreach my $lfRow2(@$lf2){
                    my $tNew =1; 
                    foreach my $lfRow1(@$lf1){
                        if($lfRow1->{'type'} eq $lfRow2->{'type'}){
                            $tNew =0;   last;
                        }
                    } 
                    if($tNew >0){
                         push @{$rRow1->{'litFrmList'}}, $lfRow2;
                         #$rowSpan++;
                    }
                 }
                 foreach my $lfRow1(@$lf1){
                    foreach my $lfRow2(@$lf2){
                        if($lfRow1->{'type'} eq $lfRow2->{'type'}){  
                            $rowSpan ++;                          
                            foreach my $sType(keys %{$lfRow2}){
                                next if($sType eq 'type');
                                $lfRow1->{$sType} = $lfRow2->{$sType} if (! defined $lfRow1->{$sType}); 
                            }
                            last;
                        }#End IF
                    }
                 }
            }
        }
        $rRow1->{'rowSpan'} = $rowSpan;
    }   

    return $r1;
}


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

sub  genBibRecordRpt{
    my ($dbh,$params)=@_;
    my $ret;
    my $rsB = countBibRecord($dbh,{ incRecFormat    =>$params->{'incRecFormat'},
                                    exclRecFormat   =>$params->{'exclRecFormat'},
                                    dateFrom        =>$params->{'dateFrom'}
                                 });
    
    my $rs    = countBibRecord($dbh,{ incRecFormat  =>$params->{'incRecFormat'},
                                      exclRecFormat =>$params->{'exclRecFormat'},
                                      dateFrom      =>$params->{'dateFrom'},
                                      dateTo        =>$params->{'dateTo'}
                                    });

    my $rsE  = countBibRecord($dbh,{ incRecFormat   =>$params->{'incRecFormat'},
                                    exclRecFormat   =>$params->{'exclRecFormat'},
                                    dateTo          =>$params->{'dateTo'}
                                    });
      
    #$ret =  mergeReport($rsB,$rs);    
    #$ret =  mergeReport($ret,$rsE);    

    return $ret;
}
#####################################################################
sub  countBibRecord{
    my ($dbh,$params)=@_;
#
#   return hash ref structure
#   ret->recFormat->literaryForm->status->{new/delete}
#   example
#   ret={book=> {essay        =>{new=>20,delete=>0},
#                shortStories=>{new=>0,delete=>3}
#               },
#        journal=>{
#               .
#               }
#        .
#        .
#        .
#
#        }
                
                 
    my $ret;
    my $dateFrom        = $params->{'dateFrom'};
    my $dateTo          = $params->{'dateTo'};     
    my $incRecFormat    = $params->{'incRecFormat'};
    my $exclRecFormat   = $params->{'exclRecFormat'};
    my ($sql,$sql_sel, $sql_where) = ("","","");
    $sql_sel = "select     recFormat,literaryForm, count(r.rid) as count 
                 from       opl_recordStats r inner join opl_marcRecord m using(rid) ";

    if($dateFrom && $dateTo){
        $sql_where= "  where  onDate > '$dateFrom'  &&  onDate < '$dateTo' ";
    }
    else{      
        my $onDate = ($dateFrom)? $dateFrom : $dateTo; 
        $sql_where= " where  onDate <= '$onDate' ";
    }

    $sql_where .= " && r.status =?  ";

    if(defined $incRecFormat && ref($incRecFormat) eq "ARRAY" && scalar(@{$incRecFormat})>0){
        $sql_where .= " AND (";
        for(my $i=0; $i< scalar(@$incRecFormat); $i++){
            $sql_where .= "  OR " if($i>0);           
            $sql_where .=  " recFormat = '". @$incRecFormat[$i] ."'";   
        }
        $sql_where .= ")";
    }
    if(defined $exclRecFormat && ref($exclRecFormat) eq "ARRAY"){
        foreach my $rFormat (@$exclRecFormat){
            $sql_where .= " AND NOT  recFormat= '$rFormat' ";   
        }
    }
   
    $sql =  $sql_sel  . $sql_where . " GROUP BY  recFormat,literaryForm " ;
    my $sth= $dbh->prepare($sql);
    my ($recFormat,$literary,$count) =('','',0);
    foreach my $s qw(new deleted){
        $sth->execute($s);
        while (my ($recFormat,$literary,$count) = $sth->fetchrow_array) {
            $ret->{$recFormat}->{$literary}->{$s}  = $count;
        }
   }
   if(!$dateFrom || !$dateTo){
       foreach my $recfmt(keys %$ret){
           foreach my $litForm(keys %{$ret->{$recfmt}}){
                $ret->{$recfmt}->{$litForm} ={'available' =>( $ret->{$recfmt}->{$litForm}->{'new'}
                                                             - $ret->{$recfmt}->{$litForm}->{'deleted'})};
           }
       }
   }
   $sth->finish;         
   return $ret;
}

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

sub  genHoldingRpt{
    my ($dbh,$params)=@_;
    my $ret=[];
    my ($cB,$cAdded,$cFound,$cDel,$cLost,$cE) = (0,0,0,0,0,0);
    my ($tB,$tAdded,$tFound,$tDel,$tLost,$tE) = (0,0,0,0,0,0);
     
      #  my $lf_uc = uc(substr( $lf, 0, 1)) . substr( $lf, 1) ;
        my $rsB = countHolding($dbh,{incRecFormat   =>$params->{'incRecFormat'},
                                    exclRecFormat   =>$params->{'exclRecFormat'},
                                    dateFrom        =>$params->{'dateFrom'}
                                    });
        my $rs  = countHolding($dbh,{ incRecFormat  =>$params->{'incRecFormat'},
                                          exclRecFormat =>$params->{'exclRecFormat'},
                                          dateFrom      =>$params->{'dateFrom'},
                                          dateTo        =>$params->{'dateTo'}
                                        });

       my $rsE  = countHolding($dbh,{ incRecFormat    =>$params->{'incRecFormat'},
                                        exclRecFormat   =>$params->{'exclRecFormat'},
                                        dateTo          =>$params->{'dateTo'}
                                        });

        

    $ret =  mergeReport($rsB,$rs);    
    $ret =  mergeReport($ret,$rsE);    
    return $ret;
}
#####################################################################
sub  countHolding{
    my ($dbh,$params)=@_;
    my $ret;
    my $dateFrom        = $params->{'dateFrom'} ; 
    my $dateTo          = $params->{'dateTo'}   ;     
    my $incRecFormat    = $params->{'incRecFormat'};
    my $exclRecFormat   = $params->{'exclRecFormat'};
    my ($sql,$sql_base, $sql_where) = ("","","");
    if($dateFrom && $dateTo){
        $sql_where =  "  where r.onDate > '$dateFrom'  &&  r.onDate < '$dateTo' ";
    }
    else{      
        my $onDate = ($dateFrom)? $dateFrom : $dateTo; 
        $sql_where = "  where r.onDate <= '$onDate' ";
    }
  
    if(defined $incRecFormat && ref($incRecFormat) eq "ARRAY" && scalar(@{$incRecFormat})>0){
        $sql_where .= " AND (";
        for(my $i=0; $i< scalar(@$incRecFormat); $i++){
            $sql_where .= "  OR " if($i>0);           
            $sql_where .=  " recFormat = '". @$incRecFormat[$i] ."'";   
        }
         $sql_where .= ")";
    }
    if(defined $exclRecFormat && ref($exclRecFormat) eq "ARRAY"){
        foreach my $rFormat (@$exclRecFormat){
            $sql_where .= " AND NOT recFormat ='$rFormat' ";   
        }
    }
    $sql_base = " from opl_itemStats i inner join opl_recordStats r using(rid) inner  join opl_marcRecord m using(rid) ";
    my $sth;
    foreach my $s qw(import deleted damaged lost missing found){
        $sql ='';
        if ($s eq 'import'){
            $sql = "select recFormat,literaryForm, sum(i.import)"  . $sql_base . $sql_where . " GROUP BY recFormat,literaryForm";
        }
        elsif ($s eq 'deleted'){
            $sql = "select recFormat,literaryForm, sum(i.deleted)" . $sql_base . $sql_where . " GROUP BY recFormat,literaryForm";
        }
        elsif ($s eq 'damaged'){
            $sql = "select recFormat,literaryForm, sum(i.damaged)" . $sql_base . $sql_where . " GROUP BY recFormat,literaryForm";
        }
        elsif ($s eq 'lost'){
            $sql = "select recFormat,literaryForm, sum(i.lost)"    . $sql_base . $sql_where . " GROUP BY recFormat,literaryForm";
        }                
        elsif ($s eq 'missing'){
            $sql = "select recFormat,literaryForm, sum(i.missing)" . $sql_base . $sql_where . " GROUP BY recFormat,literaryForm";
        }
        elsif ($s eq 'found'){
            $sql = "select recFormat,literaryForm, sum(i.found)"   . $sql_base . $sql_where . " GROUP BY recFormat,literaryForm";
        }    
        $sth= $dbh->prepare($sql);
        $sth->execute();
        while (my ($recFormat,$literary,$count) = $sth->fetchrow_array) {
            $ret->{$recFormat}->{$literary}->{$s}  = $count;

        }
    }
      
   if(!$dateFrom || !$dateTo){
      foreach my $recfmt(keys %$ret){
           foreach my $litForm(keys %{$ret->{$recfmt}}){
                $ret->{$recfmt}->{$litForm}->{'available'} =  $ret->{$recfmt}->{$litForm}->{'import'}
                                                            + $ret->{$recfmt}->{$litForm}->{'found'}
                                                            - $ret->{$recfmt}->{$litForm}->{'deleted'}
                                                            - $ret->{$recfmt}->{$litForm}->{'lost'}
                                                            - $ret->{$recfmt}->{$litForm}->{'missing'};
           }
       }

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

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

sub  genHoldingRptByPrefix{
    my ($dbh,$params)=@_;
    my $ret;
    my ($cB,$cAdded,$cFound,$cDel,$cLost,$cE) = (0,0,0,0,0,0);
    my ($tB,$tAdded,$tFound,$tDel,$tLost,$tE) = (0,0,0,0,0,0);
    my $rsB = countHoldingByPrefix($dbh,{dateFrom        =>$params->{'dateFrom'}});
    my $rs    = countHoldingByPrefix($dbh,{ dateFrom      =>$params->{'dateFrom'},
                                            dateTo        =>$params->{'dateTo'}
                                          });

    my $rsE  = countHoldingByPrefix($dbh,{ dateTo          =>$params->{'dateTo'}});
     
    foreach my $p (@$prefixList){
        $cB =$rsB->{$p}->{'import'} + $rsB->{$p}->{'found'} 
                         - $rsB->{$p}->{'lost'}   - $rsB->{$p}->{'missing'} 
                         - $rsB->{$p}->{'deleted'};
        $cAdded =$rs->{$p}->{'import'};
        $cFound =$rs->{$p}->{'found'};
        $cDel   =$rs->{$p}->{'deleted'};
        $cLost  =$rs->{$p}->{'lost'} + $rs->{$p}->{'missing'};
        $cE     =$rsE->{$p}->{'import'} + $rsE->{$p}->{'found'} 
                             - $rsE->{$p}->{'lost'}   - $rsE->{$p}->{'missing'} 
                             - $rsE->{$p}->{'deleted'};


        push @$ret     ,   { type             => ($p ne '')? $p : '',
                             holding_asOfDateB => $cB,
                             holdingAdded      => $cAdded,
                             holdingFound      => $cFound,
                             holdingDel        => $cDel,
                             holdingLost       => $cLost,
                             holding_asOfDateE => $cE,
                             title_asOfDateB   => 'na',
                             titleAdded        => 'na', 
                             titleDel          => 'na', 
                             title_asOfDateE   => 'na' 
                           };
        $tB      += $cB;
        $tAdded  += $cAdded;
        $tFound  += $cFound; 
        $tDel    += $cDel;
        $tLost   += $cLost;
        $tE      += $cE;
     }
    return $ret;


}
#####################################################################
sub  countHoldingByPrefix{
    my ($dbh,$params)=@_;
    my $ret;
    my $dateFrom        = $params->{'dateFrom'} ; 
    my $dateTo          = $params->{'dateTo'}   ;     
    my ($sql,$sql_base, $sql_where) = ("","","");
    
    if($dateFrom && $dateTo){
        $sql_where =  "  where r.onDate > '$dateFrom'  &&  r.onDate < '$dateTo' ";
    }
    else{      
        my $onDate = ($dateFrom)? $dateFrom : $dateTo; 
        $sql_where = "  where r.onDate <= '$onDate' ";
    }

    $sql_base = " from opl_itemStats i inner join opl_recordStats r using(rid) ";
    my $sth;
    foreach my $s qw(import deleted damaged lost missing found){
        $sql ='';
        if ($s eq 'import'){
            $sql = "select prefix, sum(i.import)"  . $sql_base . $sql_where . " GROUP BY prefix";
        }
        elsif ($s eq 'deleted'){
            $sql = "select prefix, sum(i.deleted)" . $sql_base . $sql_where . " GROUP BY prefix";
        }
        elsif ($s eq 'damaged'){
            $sql = "select prefix, sum(i.damaged)" . $sql_base . $sql_where . " GROUP BY prefix";
        }
        elsif ($s eq 'lost'){
            $sql = "select prefix, sum(i.lost)"    . $sql_base . $sql_where . " GROUP BY prefix";
        }                
        elsif ($s eq 'missing'){
            $sql = "select prefix, sum(i.missing)" . $sql_base . $sql_where . " GROUP BY prefix";
        }
        elsif ($s eq 'found'){
            $sql = "select prefix, sum(i.found)"   . $sql_base . $sql_where . " GROUP BY prefix";
        }    
        $sth= $dbh->prepare($sql);
        $sth->execute();
        while (my ($prefix,$count) = $sth->fetchrow_array) {
            $ret->{$prefix}->{$s}  = $count;

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

sub  getPrefixList{
   my ($dbh)=@_;
    my $ret=[];
    my $sth=$dbh->prepare(<<_SQL_);
    select distinct prefix  from opl_itemStats order by  prefix
_SQL_
    $sth->execute();
    while( my($prefix) =$sth->fetchrow_array){
        push @$ret,$prefix;
    }
    $sth->finish;
    return $ret;
}


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

sub  getRecFormatList{
    my ($dbh)=@_;
    my ($retPrt,$retNonPrt)=([],[]);
    my $sth=$dbh->prepare(<<_SQL_);
    select distinct recFormat  from opl_marcRecord order by recFormat 
_SQL_
    $sth->execute();
    while( my($recFormat) =$sth->fetchrow_array){
        if($recFormat eq 'book' || $recFormat eq 'journal' || $recFormat eq 'sheetMusic'){
                push @$retPrt,$recFormat;
        }
        else{
            push @$retNonPrt,$recFormat;
        }
        
    }
    $sth->finish;
    
    return ($retPrt,$retNonPrt);
}

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

sub  getLiteraryFormList{
    my ($dbh)=@_;
    my $ret;
    my $sth=$dbh->prepare(<<_SQL_);
    select distinct literaryForm  from opl_marcRecord order by  literaryForm
_SQL_
    $sth->execute();
    while( my($literaryForm) =$sth->fetchrow_array){
        push @$ret,$literaryForm;
    }
    $sth->finish;
    return $ret;
}

sub genReportTbl{
    my ($dbh)=@_;
    my $ret;
    my $sth=$dbh->prepare(<<_SQL_);
    select distinct m.recFormat,m.literaryform 
    from opl_recordStats r inner join opl_marcRecord m using(rid)  
    group by recFormat,literaryForm 
    order by recFormat,literaryForm
_SQL_

    my @colList= qw(titleAsOfDateB holdingAsOfdateB titleAdded holdingAdded
    holdingLost holdingFound titleDeleted holdingDeleted titleAsOfDateE holdingAsOfdateE);
    my $preRecFmt="";
    $sth->execute();
    while(my($recFmt,$litform)=$sth->fetchrow_array){
        if($preRecFmt ne $recFmt){
            $ret->{$recFmt}->{'litFormCount'}=0;
            $preRecFmt =$recFmt;
        }
        $ret->{$recFmt}->{'litFormCount'} +=1;

        foreach my $col(@colList){
            $ret->{$recFmt}->{$litform}->{$col} =0;
        }
    }
    return $ret;
}