#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use POSIX;

use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

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

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);
use Opals::Session qw(
    SessionHdl_get
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $incExcOpt     = $input->{'incExcOpt'};
my $inEx_itemTypeStr =  $input->{'inEx_itemType'};
my @inEx_itemType =  split(",",$inEx_itemTypeStr);

my $sessionID = $cgi->cookie('globalSessionID');
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemNoCirc_prt.tmpl',
            reqPermission   => 'rpt_circ',
        }
);
 
my $prtType    = $input->{'prtType'};
my $mSort      = $input->{'sort'};
my $sDirection = $input->{'sDirection'};

if ( !$mSort ){ 
    $mSort = 'barcode'; 
    $sDirection='asc';
}

    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;

    my $dateFrom= $input->{'reportFrom'};
    my $dateTo  = $input->{'reportTo'};
    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }

    $dateFrom = $dateToday if ( !$dateFrom );
    $dateFrom .= " 00:00:00";
    $dateTo = $dateToday if ( !$dateTo );
    $dateTo .= " 23:59:59";

    my $recordList = GetItemNoCircList($dbh, $dateFrom, $dateTo,$incExcOpt, \@inEx_itemType, $mSort, $sDirection);
    if($prtType && $prtType eq 'csv'){
        saveItemNoCircListAsCsv($recordList);
    }
    else{
        if($dateRange eq 'all'){
            $template->param(
                 rangeAll   => 1,
                dateRangeOpt=>"all"
           );
        }
        elsif($dateRange eq 'fYear'){
            $template->param(
                from    => substr($dateFrom, 0, 10),
                to      => substr($dateTo, 0, 10),
                rangeFYear   => 1,
                dateRangeOpt=>"fYear"
           );
        }
        else{
            $template->param(
                from    => substr($dateFrom, 0, 10),
                to      => substr($dateTo, 0, 10),
                rangeSel   => 1,
                dateRangeOpt=>"rangeSel"
           );

        }

        $template->param(resultList => $recordList,
                         sort       => $mSort,
                         sDirection => $sDirection,
                         exclusion => ($incExcOpt eq 'exclusion')?1:0,
                         inclusion => ($incExcOpt eq 'inclusion')?1:0,
                         ); 

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

######################################################################
sub GetItemNoCircList{
    my ($dbh, $dateFrom, $dateTo, $inExOpt, $inEx_itemType, $mSort, $sDirection ) =@_;
    my $sql ='';
 
    if($inExOpt ne '' ){
        my $sqlCond ='';
        my ($op,$cmp) = ($inExOpt eq 'exclusion')?('AND','<>'):('OR','=');

        foreach my $exIType (@$inEx_itemType){
            $exIType =~ s/\'/\\\'/gi;
            next if ($exIType eq '');
            if($sqlCond ne ''){
                $sqlCond .=" $op typeId $cmp '$exIType' ";
            }
            else{
                $sqlCond =" typeId $cmp '$exIType' ";
            }
        }
         $sql  =<<_STH_;
    select  distinct m.title,m.author,m.pubDate,i.barcode,i.callNumber,i.dateImport,i.rid
               from opl_marcRecord m inner join opl_item i on i.rid=m.rid 
                    left outer join opl_loan l on i.barcode=l.barcode  
                    && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
               where i.barcode not regexp '^\_\_\_' 
                     && i.barcode not regexp '^TMP\_' 
                     && i.dateImport <= '$dateTo'
                     && l.barcode is null
_STH_


       if($sqlCond ne ''){
         $sql .= " AND ($sqlCond)";  
       }
      }
    else{
        $sql=" select distinct m.rid, m.title,m.author,m.pubDate,i.barcode,i.callNumber
               from opl_marcRecord m inner join opl_sessionVar s on m.rid=s.rid  
               inner join opl_item i on i.rid=s.rid 
               where  s.var='itemNoCirc' && s.ssid ='$sessionID' && i.barcode not regexp '^\_\_\_' 
               group by m.rid ";


    }
   $sql .=" order by $mSort $sDirection ";

        my $query = $dbh->prepare($sql);
        $query->execute();
        my $pNum = -1;
        my @Items = ();

        while (my $rec = $query->fetchrow_hashref)
        {   
            $pNum++;
            $rec->{'odd'} = $pNum%2;     
            $rec->{'order'} = $pNum;
            push @Items, $rec; 
        }
        $query->finish;

    return \@Items;

}

######################################################################
sub saveItemNoCircListAsCsv{
   my ($recordList)=@_;
   print "Content-Type:application/x-download\n";
   print "Content-Disposition:attachment;filename=itemNoCircList.csv\n\n";    
   print "\"Title\",\"Author\",\"Pub.Date\",\"Call Number\",\"Barcode\"\n" ;
   my @field =qw(title author pubDate callNumber barcode);
   my $rowStr="";
   foreach my $rec(@$recordList){
       $rowStr="";
        foreach my $f(@field){
            my $str =$rec->{$f};
            $str =~ s/"/""/gi;
            $rowStr .= "\"$str\",";
         }
         $rowStr =~ s/,$//g;
        print "$rowStr\n";
   }
   
}


__END_OF_FILE: