#!/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        => 'eqmnt/report/circStats_prt.tmpl',
            reqPermission   => 'eq_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 ($itemField2Display , $itemNoCircList );
    if ( $prtOpt eq 'all'){
        ($itemField2Display , $itemNoCircList ) = getItemNoCircList($dbh,$dateFrom,$dateTo,$sort,$sDirection);

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

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

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


sub getItemNoCircList {

    my ($dbh, $dateFrom, $dateTo, $sort, $sDir) = @_;
   
    my @Items = ();
    my $itemFields2Display = getItemField2Display($dbh);
    my $itemFieldSelect = "";
    my $itemFieldTable = "";
    foreach my $c (@{$itemFields2Display}){
            $itemFieldSelect .= "f".$c->{'id'} . ".sfValue as f". $c->{'id'} ." ,";
            $itemFieldTable .=  " left join eq_itemFields "."f".$c->{'id'} . " on (" . "f".$c->{'id'}.".rid = i.rid && i.iid = "."f".$c->{'id'}.".iid && f". $c->{'id'} . ".sfId = " .  $c->{'id'} . ") ";
        }
    $itemFieldSelect =~ s/,$//;  
    my $sql = " select r.rname as rname, i.rid as rid, i.barcode as barcode, $itemFieldSelect , count(l.barcode)
                from eq_records r inner join eq_items i using(rid)";
        $sql .=  $itemFieldTable;
        $sql .= " left outer join eq_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();              
    my $curRId = 0;
    my  $rid;
    my $recordInfo ;
    while( my $rec = $query->fetchrow_hashref){
        foreach my $c (@{$itemFields2Display}) {
            push @{$rec->{'cols'}}, { value => $rec->{'f'.$c->{'id'}}};
        }
        push @Items, $rec;
    }
    return ($itemFields2Display, \@Items);
}

sub getSelectedItemNoCircList {
    
    my $itemFields2Display = getItemField2Display($dbh);
    my @Items = ();
    my ($dbh,$sessionID,$ssVar) = @_;
    my $sql_bc = "select barcode from opl_sessionVar where var = '$ssVar' order by sOrder ";
    my $sth_bc = $dbh->prepare($sql_bc);
    
    my $itemFieldSelect = "";
    my $itemFieldTable = "";
    foreach my $c (@{$itemFields2Display}){
            $itemFieldSelect .= "f".$c->{'id'} . ".sfValue as f". $c->{'id'} ." ,";
            $itemFieldTable .=  " left join eq_itemFields "."f".$c->{'id'} . " on (" . "f".$c->{'id'}.".rid = i.rid && i.iid = "."f".$c->{'id'}.".iid && f". $c->{'id'} . ".sfId = " .  $c->{'id'} . ") ";
        }
    $itemFieldSelect =~ s/,$//; 
    my $sql =   my $sql = " select r.rname as rname, i.rid as rid, i.barcode as barcode, $itemFieldSelect 
                from eq_records r inner join eq_items i using(rid)";
    $sql .=  $itemFieldTable;
    $sql .= " where i.barcode = ? ";
    
    my $sth_bc = $dbh->prepare($sql_bc);
    my $sth = $dbh->prepare($sql);
    $sth_bc->execute();


    while (my $bc = $sth_bc->fetchrow_array() ){
        $sth->execute($bc);
        while( my $rec = $sth->fetchrow_hashref){
            foreach my $c (@{$itemFields2Display}) {
                push @{$rec->{'cols'}}, { value => $rec->{'f'.$c->{'id'}}};
            }
            push @Items, $rec;
        }
    }


    $sth_bc->finish;
    $sth->finish;
    return ($itemFields2Display, \@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: