#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

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

use Opals::Date qw(
    date_time_text
);


use Opals::Equipment qw(
    
    eq_item_findByBarcode
    eq_record_findByRId
    eq_def_getFieldId

);
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();


my ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => '/eqmnt/eqOnReserve.tmpl',
                reqPermission   => 'eq_circ_loan',
            }
        );

my $pref = tmpl_preference($dbh);

my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
my $sort1       = $input->{'sort1'};
my $sort2       = $input->{'sort2'};

if ($sort1 eq '') {
    $sort1 = "homeroom";
}
if (!$sort2){
    if ($sort1 eq "username"){
        $sort2 = "title";    }
    else{
        $sort2 = "username";
    }
 }   

my $firstLoad = (!$dateFrom || !$dateTo)? 1:0;

    if (! $dateFrom && ! $dateTo){
        $dateFrom   = $todayStr;
        $dateTo     = $todayStr;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo   .= " 23:59:59";
    }

    if ($firstLoad){
        $template->param(username => 1);
        $template->param(ReportMsg => 1);
        goto NoProcess;
    }
    
    if ( $sort1 eq 'username'){
        my $orderby = $input->{'orderby'};
        getOnReserve_user($dbh, $template, $dateFrom, $dateTo, $orderby);
    }
    elsif ($sort1 eq 'teacher'){
        getOnReserve_teacher($dbh, $template, $dateFrom, $dateTo);
    }
    elsif ($sort1 eq 'homeroom'){
        getOnReserve_homeroom($dbh, $template, $dateFrom, $dateTo);
    }
    elsif ($sort1 eq 'grade'){
        getOnReserve_grade($dbh, $template, $dateFrom, $dateTo);
    }

NoProcess:

    $template->param(dateFrom   => substr($dateFrom,0,10));
    $template->param(dateTo     => substr($dateTo,0,10));
    $template->param(sort1      => $sort1);    
    $template->param(sort2      => $sort2);    

    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
        );
    }


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

#----------------------------------------------

sub getOnReserve_user{
    my ($dbh, $template, $dateFrom, $dateTo, $orderby) = @_;
    #my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my $rname;
    my $eqOnReserve;
    my $row = 0;
    my $totalOnReserveHold = 0;

    my $sql = "select distinct r.* , u.*
                from    (eq_reserve as r left outer join eq_hold as h on r.id = h.idReserve)
                        inner join opl_user as u on r.uid = u.uid
                where   r.dateCancel is null && r.dateExpiry > now() && r.dateReserve >= ? && r.dateReserve <= ? &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))";

    if ($orderby eq 'name'){
        $sql .= " order by concat(lastname, firstname, 'zzzzzz'), username";    }
    else{
        $sql .= " order by concat(lastname, firstname, 'zzzzzz'), concat('homeroom','zzzzzzz'), username"; }
    my $sth_onHold = $dbh->prepare("select count(*) as nHold
                from    eq_hold
                where   idReserve = ? && dateExpiry >= now() && dateCancel is null && dateLoan is null"
                );
    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $reserve = $sth->fetchrow_hashref){
        my ($rid_1, $recordInfo) = eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
        $rname = $recordInfo->[0]{'rname'};
=item        
        foreach my $f( @$recordInfo){
            if ($f->{'fId'} == $eqNameId){
                $rname = $f->{'fValue'};
                last;
            }
        }
=cut
        $sth_onHold->execute($reserve->{'id'});
        my ($hRec) = $sth_onHold->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'} > 0){
            $reserve->{'onHold'} = 1;
            $reserve->{'numHold'}= $hRec->{'nHold'};
        }
        else{
            $reserve->{'onHold'} = 0;
            $reserve->{'numHold'}= 0;
        }
        $reserve->{'numReserve'} = $reserve->{'numCopyReserve'};
        $reserve->{'rname'} =  $rname;
        $reserve->{'dateReserveFormat'} = date_time_text ($reserve->{'dateReserve'}, 0, 'en');
        $reserve->{'dateExpiryFormat'} =  date_time_text ($reserve->{'dateExpiry'}, 0 ,'en');
        $reserve->{'qty'} = $reserve->{'numReserve'}  + $reserve->{'numHold'};
        $totalOnReserveHold +=  $reserve->{'qty'} ;
        if (!$eqOnReserve->{$reserve->{'uid'}}){
             $eqOnReserve->{$reserve->{'uid'}}->{'uid'} = $reserve->{'uid'};
             $eqOnReserve->{$reserve->{'uid'}}->{'firstname'} = $reserve->{'firstname'};
             $eqOnReserve->{$reserve->{'uid'}}->{'lastname'} = $reserve->{'lastname'};
             $eqOnReserve->{$reserve->{'uid'}}->{'grade'} = $reserve->{'grade'};
             $eqOnReserve->{$reserve->{'uid'}}->{'homeroom'} = $reserve->{'homeroom'};
             $eqOnReserve->{$reserve->{'uid'}}->{'building'} = $reserve->{'building'};
             $eqOnReserve->{$reserve->{'uid'}}->{'count'} = $row;
             $row++;
        }
        push @{$eqOnReserve->{$reserve->{'uid'}}->{'group'}}, $reserve;
    }
    $sth->finish;
    $sth_onHold->finish;
    my @eqOnReserveList = ();
    foreach my $k( sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserve => \@eqOnReserveList);
    $template->param(
        username    => 1,
        sort1       => "username",
        totalOnReserveHold => $totalOnReserveHold, 
    );
}

sub getOnReserve_teacher{
    my ($dbh, $template, $dateFrom, $dateTo) = @_;
    #my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my $rname;
    my $eqOnReserve;
    my $row = 0;
    my $totalOnReserveHold = 0;
    
    my $sql = "select distinct r.* , u.*
                from    (eq_reserve as r left outer join eq_hold as h on r.id = h.idReserve)
                        inner join opl_user as u on r.uid = u.uid
                where   r.dateCancel is null && r.dateExpiry > now() && r.dateReserve >= ? && r.dateReserve <= ? &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null))) 
                order by concat(teacher, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(homeroom,'zzzzzz') ";
    
    my $sth = $dbh->prepare($sql);
    my $sth_onHold = $dbh->prepare("select count(*) as nHold
                from    eq_hold
                where   idReserve = ? && dateExpiry >= now() && dateCancel is null && dateLoan is null"
                );
    $sth->execute($dateFrom, $dateTo);
    while (my $reserve = $sth->fetchrow_hashref){
        my ($rid_1, $recordInfo) = eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
        $rname = $recordInfo->[0]{'rname'};
=item
        foreach my $f( @$recordInfo){
            if ($f->{'fId'} == $eqNameId){
                $rname = $f->{'fValue'};
                last;
            }
        }
=cut
        if (!$reserve->{'teacher'} || $reserve->{'teacher'} eq ""){
            $reserve->{'teacher'} = " N/A";
        }
        $sth_onHold->execute($reserve->{'id'});
        my ($hRec) = $sth_onHold->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'} > 0){
            $reserve->{'onHold'} = 1;
            $reserve->{'numHold'}= $hRec->{'nHold'};
        }
        else{
            $reserve->{'onHold'} = 0;
            $reserve->{'numHold'}= 0;
        }
        $reserve->{'numReserve'} = $reserve->{'numCopyReserve'};
        $reserve->{'rname'} =  $rname;
        $reserve->{'qty'} = $reserve->{'numReserve'}  + $reserve->{'numHold'};
        $reserve->{'dateReserveFormat'} = date_time_text ($reserve->{'dateReserve'}, 0, 'en');
        $reserve->{'dateExpiryFormat'} =  date_time_text ($reserve->{'dateExpiry'}, 0 ,'en');
        $totalOnReserveHold +=  $reserve->{'qty'} ;

        if (!$eqOnReserve->{$reserve->{'teacher'}}){
            $eqOnReserve->{$reserve->{'teacher'}}->{'teachername'} = $reserve->{'teacher'};
            $eqOnReserve->{$reserve->{'teacher'}}->{'homeroom'} = $reserve->{'homeroom'};
            $eqOnReserve->{$reserve->{'teacher'}}->{'building'} = $reserve->{'building'};
            $eqOnReserve->{$reserve->{'teacher'}}->{'count'} = $row;
            $row++;
        }
        push @{$eqOnReserve->{$reserve->{'teacher'}}->{'group'}}, $reserve;
    }
    $sth->finish;
    $sth_onHold->finish;
    my @eqOnReserveList = ();
    foreach my $k(sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveTeacher => \@eqOnReserveList);
    $template->param(
        teacher => 1,
        sort1   => "teacher",
        totalOnReserveHold => $totalOnReserveHold, 
    );
}

sub getOnReserve_homeroom{
    my ($dbh, $template, $dateFrom, $dateTo) = @_;
    #my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my $eqOnReserve;
    my $row = 0;
    my $totalOnReserveHold = 0;
    my $rname;

    my $sql = "select distinct r.* , u.*
                from    (eq_reserve as r left outer join eq_hold as h on r.id = h.idReserve)
                        inner join opl_user as u on r.uid = u.uid
                where   r.dateCancel is null && r.dateExpiry > now() && r.dateReserve >= ? && r.dateReserve <= ? &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))
                order by concat(homeroom, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(teacher,'zzzzzz') ";
    
    my $sth = $dbh->prepare($sql);
    my $sth_onHold = $dbh->prepare("select count(*) as nHold
                from    eq_hold
                where   idReserve = ? && dateExpiry >= now() && dateCancel is null && dateLoan is null"
    );
    $sth->execute($dateFrom, $dateTo);
    while (my $reserve = $sth->fetchrow_hashref){
        my ($rid_1, $recordInfo) = eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
        $rname = $recordInfo->[0]{'rname'};
=item
        foreach my $f( @$recordInfo){
            if ($f->{'fId'} == $eqNameId){
                $rname = $f->{'fValue'};
                last;
            }
        }
=cut
        $reserve->{'rname'} = $rname;
        if (!$reserve->{'homeroom'} || $reserve->{'homeroom'} eq ""){
            $reserve->{'homeroom'} = " N/A";
        }

        $sth_onHold->execute($reserve->{'id'});
        my ($hRec) = $sth_onHold->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'} > 0){
            $reserve->{'onHold'} = 1;
            $reserve->{'numHold'}= $hRec->{'nHold'};
        }
        else{
            $reserve->{'onHold'} = 0;
            $reserve->{'numHold'}= 0;
        }
        $reserve->{'numReserve'} = $reserve->{'numCopyReserve'};
        $reserve->{'rname'} = $rname;
        $reserve->{'qty'} = $reserve->{'numReserve'}  + $reserve->{'numHold'};
        $reserve->{'dateReserveFormat'} = date_time_text ($reserve->{'dateReserve'}, 0, 'en');
        $reserve->{'dateExpiryFormat'} =  date_time_text ($reserve->{'dateExpiry'}, 0 ,'en');
        $totalOnReserveHold +=  $reserve->{'qty'} ;

        if (!$eqOnReserve->{$reserve->{'homeroom'}}){
             $eqOnReserve->{$reserve->{'homeroom'}}->{'homeroom'} = $reserve->{'homeroom'};
             $eqOnReserve->{$reserve->{'homeroom'}}->{'building'} = $reserve->{'building'};
             $eqOnReserve->{$reserve->{'homeroom'}}->{'teacher'} = $reserve->{'teacher'};
             $eqOnReserve->{$reserve->{'homeroom'}}->{'count'} = $row;
             $row++;

        }
        push @{$eqOnReserve->{$reserve->{'homeroom'}}->{'group'}}, $reserve;
    }
    $sth->finish;
    $sth_onHold->finish;
    my @eqOnReserveList = ();
    foreach my $k( sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveHomeroom => \@eqOnReserveList);
    $template->param(
        homeroom => 1,
        sort1   => "homeroom",
        totalOnReserveHold => $totalOnReserveHold, 
    );
}

sub getOnReserve_grade{
    my ($dbh, $template, $dateFrom, $dateTo) = @_;
    my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my $eqOnReserve;
    my $row = 0;
    my $totalOnReserveHold = 0;
    my $rname;
    my $sql = "select distinct r.* , u.*
        from    (eq_reserve as r left outer join eq_hold as h on r.id = h.idReserve)
                inner join opl_user as u on r.uid = u.uid
        where   r.dateCancel is null && r.dateExpiry > now() && r.dateReserve >= ? && r.dateReserve <= ? &&
                ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))
        order by concat(grade, 'zzzzzz' ), concat(lastname,firstname, 'zzzzzz')";

    my $sth = $dbh->prepare($sql);
    my $sth_onHold = $dbh->prepare("select count(*) as nHold
        from    eq_hold
        where   idReserve = ? && dateExpiry >= now() && dateCancel is null && dateLoan is null"
    );
    $sth->execute($dateFrom, $dateTo);
    while (my $reserve = $sth->fetchrow_hashref){
        my ($rid_1, $recordInfo) = eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
        $rname = $recordInfo->[0]{'rname'};
=item
        foreach my $f( @$recordInfo){
            if ($f->{'fId'} == $eqNameId){
                $rname = $f->{'fValue'};
                last;
            }
        }
=cut
        if (!$reserve->{'grade'} || $reserve->{'grade'} eq ""){
            $reserve->{'grade'} = " N/A";
        }

        $sth_onHold->execute($reserve->{'id'});
        my ($hRec) = $sth_onHold->fetchrow_hashref;
        if ($hRec && $hRec->{'nHold'} > 0){
            $reserve->{'onHold'} = 1;
            $reserve->{'numHold'}= $hRec->{'nHold'};
        }
        else{
            $reserve->{'onHold'} = 0;
            $reserve->{'numHold'}= 0;
        }
        $reserve->{'numReserve'} = $reserve->{'numCopyReserve'};
        $reserve->{'rname'} = $rname;
        $reserve->{'qty'} = $reserve->{'numReserve'}  + $reserve->{'numHold'};
        $reserve->{'dateReserveFormat'} = date_time_text ($reserve->{'dateReserve'}, 0, 'en');
        $reserve->{'dateExpiryFormat'} =  date_time_text ($reserve->{'dateExpiry'}, 0 ,'en');
        $totalOnReserveHold +=  $reserve->{'qty'} ;

        if (!$eqOnReserve->{$reserve->{'grade'}}){
             $eqOnReserve->{$reserve->{'grade'}}->{'grade'} = $reserve->{'grade'};
             $eqOnReserve->{$reserve->{'grade'}}->{'count'} = $row;
             $row++;
        }
        push @{$eqOnReserve->{$reserve->{'grade'}}->{'group'}}, $reserve;
    }
    $sth->finish;
    my @eqOnReserveList = ();
    foreach my $k(sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveGrade => \@eqOnReserveList);
    $template->param(
        grade   => 1,
        sort1   => "grade",
        totalOnReserveHold => $totalOnReserveHold, 

    );
}