#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;use POSIX qw(
    floor
    ceil
);

use Date::Calc qw(
    Day_of_Week 
    Week_Number 
    Day_of_Year
    Monday_of_Week 
    Month_to_Text
    English_Ordinal
    );

use Time::localtime;

use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_deltaWorkDay
    date_deltaMonth
);

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

use Opals::Equipment qw(
    eq_record_findByRId
);
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        => 'txtbk/report/circStats_prt.tmpl',
            reqPermission   => 'tb_report',
        }
);
my $sessionID = $cgi->cookie('globalSessionID');
my $dateFrom = $input->{'dateFrom'};
my $dateTo   = $input->{'dateTo'}; 
my ($numOfWorkDay,$numOfMonth);
my $sort  = $input->{'sort'};
    $sort = ' rname' if (!$sort || $sort eq "");

my $sDirection = $input->{'sDirection'};
    $sDirection='asc' if(!$sDirection || $sDirection eq '');

my $prtOpt = $input->{'prtOpt'}; 

    my $tm = localtime;
    my $dateToday = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);
    my $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
    if(!$dateFrom && !$dateTo){
        $dateFrom = $dateToday ;
        $dateTo = $dateToday ;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo  =($dateTo gt $dateToday)?$dateToday:$dateTo;
        $dateTo .= " 23:59:59";
    }
    $numOfWorkDay = date_deltaWorkDay(date_parse($dateFrom),date_parse($dateTo) );
    $numOfWorkDay =1 if ($numOfWorkDay==0);
    $numOfMonth   = date_deltaMonth(date_parse($dateFrom),date_parse($dateTo));
    $numOfMonth   = 1 if ($numOfMonth==0);

    my $itemNoCircList ;
    if ( $prtOpt eq 'all'){
        $itemNoCircList = getItemNoCircList($dbh,$dateFrom,$dateTo,$sort,$sDirection);

    }
    else{
        $itemNoCircList  = getSelectedItemNoCircList($dbh,$sessionID,"tbItemNoCirc");
    }

    $template->param(
        itemNoCircList      => $itemNoCircList,
        countItemNoCirc     => scalar(@{$itemNoCircList}),
    );

tmpl_write($dbh, $cgi, $cookie, $template);
$dbh->disconnect();
    
#########################################################################################


sub getItemNoCircList {

    my ($dbh, $dateFrom, $dateTo, $sort, $sDir) = @_;
   
    my @Items = ();
     my $sql = " select i.rid as rid, 
                r1.fVal as title,
                group_concat(r2.fVal order by r2.fVal SEPARATOR '; ') as author,
                group_concat(r3.fVal order by r3.fVal SEPARATOR '; ') as ISBN,
                i.barcode as barcode,  
                i.classNumber as callNumber,
                count(l.barcode)
                from tb_items i
                inner join tb_records r1 on r1.rid = i.rid && r1.fId = '245_a' 
                inner join tb_records r2 on r2.rid = i.rid && r2.fId = '100_a' 
                inner join tb_records r3 on r3.rid = i.rid && r3.fId = '020_a' 
                left outer join tb_loan l on l.barcode = i.barcode
                && l.dateLoan <= '$dateTo' && l.dateLoan >= '$dateFrom'
                where i.barcode not regexp '^\_\_\_'
                group by i.barcode
                having count(l.barcode) = 0
                order by $sort $sDir ";

    my $query = $dbh->prepare($sql);
    $query->execute();              
    while( my $rec = $query->fetchrow_hashref){
           push @Items, $rec;
    }
    return \@Items;
}

sub getSelectedItemNoCircList {
    
    my @Items = ();
    my ($dbh,$sessionID,$ssVar) = @_;
    my $sql = " select v.rid, v.barcode, 
                r1.fVal as title,
                group_concat(r2.fVal order by r2.fVal SEPARATOR '; ') as author,
                group_concat(r3.fVal order by r3.fVal SEPARATOR '; ') as ISBN,
                i.barcode as barcode,  
                i.classNumber as callNumber
                from opl_sessionVar v
                inner join tb_items i on i.barcode = v.barcode
                inner join tb_records r1 on r1.rid = i.rid && r1.fId = '245_a' 
                inner join tb_records r2 on r2.rid = i.rid && r2.fId = '100_a' 
                inner join tb_records r3 on r3.rid = i.rid && r3.fId = '020_a' 
                where var = '$ssVar' && ssid = '$sessionID'
                group by v.barcode
                order by sOrder ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(); 
    while (my $rec = $sth->fetchrow_hashref ){
            push @Items, $rec;
    }

    $sth->finish;
    return \@Items;

}

sub getItemField2Display {
    my ($dbh) = @_;
    my @itemField2Display = ();
    my $sql = " select id, name from eq_def where defType = 'item' && display = 1" ; 
    my $query = $dbh->prepare($sql);
    $query->execute();
    while (my $rec = $query->fetchrow_hashref){
        push @itemField2Display, $rec;
    }
    return \@itemField2Display;
}

__END_OF_FILE: