#!/usr/bin/perl
##### Tue, Mar 22, 2011 @ 15:41:24 EDT
# scp /www/odev/bin/report/itemNoCirc  mimas:/www/opals/bin/report/; scp /www/odev/bin/report/itemNoCirc_prt mimas:/www/opals/bin/report/; scp /www/odev/htdocs/theme/opals/report/itemNoCirc.tmpl mimas:/www/opals/htdocs/theme/opals/en/report/
# scp /www/odev/htdocs/theme/opals/locale/en/report/reports.msg mimas:/www/opals/htdocs/theme/opals/en/locale/en/report/reports.msg
# scp /www/odev/htdocs/theme/opals/locale/fr/report/reports.msg mimas:/www/opals/htdocs/theme/opals/en/locale/fr/report/reports.msg
#

#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::Locale qw(
    loc_getMsgFile
    loc_write
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $incExcOpt = $input->{'incExcOpt'};
if($incExcOpt ne 'inclusion'){
    $incExcOpt='exclusion';
}
my @inEx_itemType =  ($cgi->param('inEx_itemType'));
my $inEx_itemTypeStr = join(',',@inEx_itemType);
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemNoCirc.tmpl',
            reqPermission   => 'rpt_circ',
        }
);
 # GET LIST OF ITEM TYPE FROM opl_itemType TABLE --- quick item entry
     # Mon, Nov 17, 2008 @ 15:43:05 EST
     my $itemTypeList     = getItemTypeList($dbh,\@inEx_itemType);
     $template->param(
            itemTypeList =>  $itemTypeList,
        );

$template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemNoCirc') );
my $mSort      = $input->{'sort'};
my $sDirection = $input->{'sDirection'};

$sDirection='asc' if(!$sDirection || $sDirection eq '');
if ( !$mSort ){ 
    $mSort = 'barcode'; 
}
  
    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->{'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";
    if( $input->{'reportFrom'} ne '' ||  $input->{'dateRangeOpt'} eq 'all'){
        my $recordList = GetItemNoCircList($dbh, $dateFrom, $dateTo,$incExcOpt, \@inEx_itemType, $mSort, $sDirection, $pNum, $pagesize);
                                 MakePages($dbh, $input, $template, $pagesize, $dateFrom, $dateTo, $incExcOpt, \@inEx_itemType);
        $template->param(itemNoCirc       =>1, resultList       => $recordList);

    }

     #$sDirection=($sDirection eq 'asc' ?'desc':'asc');

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(
                 pNum             => $pNum, 
                 sort             => $mSort,
                 sDirection       => $sDirection,
                 exclusion        => ($incExcOpt eq 'exclusion')?1:0,
                 inclusion        => ($incExcOpt eq 'inclusion')?1:0,
                 inEx_itemTypeStr => $inEx_itemTypeStr,
                 ); 

#Tue, Jan 12, 2010 @ 10:31:41 EST
my $msgValMap ={};
my $msgMap            =loc_getMsgFile('report/reports.msg',$msgValMap);
loc_write($template,$msgMap);

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

######################################################################
sub GetItemNoCircList{
    my ($dbh, $dateFrom, $dateTo, $inExOpt, $inEx_itemType, $mSort, $sDirection, $pNum, $pagesize ) =@_;
    my $pageoffset = $pNum;
    if ( !$pageoffset ) {
        $pageoffset = 0;
    }
    else {
        $pageoffset = ($pNum - 1) * $pagesize;
    }
    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' ";
        }
    }
    my @Items = ();
    my $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)";  
   }
 $sql .=<<_STH_;                 
               order by $mSort $sDirection 
               limit  $pageoffset, $pagesize
_STH_
     my $query = $dbh->prepare($sql);

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

}

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

sub MakePages
{
     my ($dbh, $input, $template, $pagesize, $dateFrom, $dateTo,$inExOpt, $inEx_itemType) = @_;  
     my $countNoLoan = 0;
=item
     my $sqlCond ='';

    my ($op,$cmp) = ($inExOpt eq 'exclusion')?('AND','<>'):('OR','=');
    foreach my $exIType (@$inEx_itemType){
        $exIType =~ s/\'/\\\'/gi;
        if($sqlCond ne ''){
            $sqlCond .=" $op typeId $cmp '$exIType' ";
        }
        else{
            $sqlCond =" typeId $cmp '$exIType' ";
        }
    }
           my $sql_count_loan = <<_STH_;
     select  count(distinct i.rid)
               from opl_marcRecord m inner join opl_item i on i.rid=m.rid 
                    inner 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'
_STH_
   
   if($sqlCond ne ''){
     $sql_count_loan .= " AND ($sqlCond)";  
   }
  
    my $sql_count_total = <<_STH_;
     select  count(distinct i.rid)
               from opl_marcRecord m inner join opl_item i on i.rid=m.rid 
               where i.barcode not regexp '^\_\_\_'   
                    && i.barcode not regexp '^TMP\_' 
                    && i.dateImport <= '$dateTo' 
_STH_
    if($sqlCond ne ''){
       $sql_count_total .= " AND ($sqlCond)";  
    }

    my $sql = "select (" .  $sql_count_total . ") - (" . $sql_count_loan .")" ;   
=cut
    my $sqlCond ='';
    my ($op,$cmp) = ($inExOpt eq 'exclusion')?('AND','<>'):('OR','=');
    foreach my $exIType (@$inEx_itemType){
        $exIType =~ s/\'/\\\'/gi;
        if($sqlCond ne ''){
            $sqlCond .=" $op typeId $cmp '$exIType' ";
        }
        else{
            $sqlCond =" typeId $cmp '$exIType' ";
        }
    }
   my $sql =<<_STH_;
    select  count(distinct  m.rid,i.barcode)
               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)";  
   }


      my $sth = $dbh->prepare($sql);
    $sth->execute();
    $countNoLoan = $sth->fetchrow_array;
    
   ($input->{'pNum'} && $input->{'pNum'} > 0) || ($input->{'pNum'} = 1);

    my @rangedPageList = tmpl_rangedPageList($countNoLoan, $input->{'pNum'}, $pagesize, 10);
    $template->param(
        countNoLoan     => $countNoLoan,
        rangedPageList  => \@rangedPageList,
    );


}


#-------------------------------------------------------------------------------
### Wed, Jan 21, 2009 @ 13:49:00 EST : Item type Authority
sub getItemTypeList{
    my ($dbh,$excludeList)= @_;
    my $sql = <<_SQL_;
SELECT     distinct id as typeId
FROM       opl_itemType
WHERE      id <>""
ORDER BY    id
_SQL_

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my @retVal;
    my $itentypeStr=""; 
    my $i=0;
    while (my $rec = $sth->fetchrow_hashref) { 
           $rec->{'excluded'}=isInExcludeTypeList($rec->{'typeId'},$excludeList);
           $rec->{'firstCell'} = $i%5==0?1:0;  
           $rec->{'lastCell'} = $i%5==4?1:0;  
           $i++;
           $rec->{'id'} =$i;   
           push @retVal, $rec;
    }
    $sth->finish;
           
    return \@retVal;
}
#-------------------------------------------------------------------------------
sub isInExcludeTypeList{
    my ($itemType,$excludeList) =@_;
    foreach my $exType (@$excludeList){
        if($exType eq $itemType){
            return 1;
        }
    }
    return 0;
}
__END_OF_FILE: