#!/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, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => '/eqmnt/eqOnReserve_prt.tmpl',
#        reqPermission   => 'reqPermission1|reqPermission2|etc.',
    }
);

my $pref = tmpl_preference($dbh);

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

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

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

if ( $sort1 eq 'username'){
    if ($input->{'list'}){
        getOnReserve_userList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
        $template->param(idList => 1);
    }
    #else{
    #    getOnReserve_userNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
    #    $template->param(odnotice => 1);}
}
elsif($sort1 eq 'teacher' ){
    if ($input->{'list'}){
        getOnReserve_teacherList($dbh, $template, $sort1,$sort2,$dateFrom, $dateTo, $input);
        $template->param(teacherList => 1);
    }
    #else{
    #    getOnReserve_teacherNotice($dbh, $template, $sort1,$sort2,$dateFrom, $dateTo, $input);
    #    $template->param(odnotice => 1);    }
}
elsif ($sort1 eq 'homeroom'){
    if ($input->{'list'}){
        getOnReserve_homeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(teacherList => 1);
    }
    #else{
    #    getOnReserve_homeroomNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
    #    $template->param(odnotice => 1);    }

}
elsif ($sort1 eq 'grade'){
    if ($input->{'list'}){
        getOnReserve_gradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(gradeList => 1);
    }
    #else{
    #   getOnReserve_gradeNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
    #    $template->param(odnotice => 1);    }
}


if($input->{'headers'} eq 'true'){
        $template->param(includeHeaders => 1);
    }

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

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

sub getOnReserve_userList{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my @idList = ();
    my $eqOnReserve;
    my $rname;

    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
    
    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 >= '$dateFrom' && r.dateReserve <= '$dateTo' &&  u.uid = ? &&
                        ((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 = $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"
                );

    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        while (my $reserve =$sth->fetchrow_hashref ){
            my ($rid_1, $recordInfo) =
            eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
            foreach my $f( @$recordInfo){
                if ($f->{'fId'} == $eqNameId){
                    $rname = $f->{'fValue'};
                    last;
                }
            }
            $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'};

            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'};
            }
            push @{$eqOnReserve->{$reserve->{'uid'}}->{'group'}}, $reserve;
        }    
    }
    $sth->finish;
    $sth_onHold->finish;

    my @eqOnReserveList = ();
    foreach my $p(keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$p};
    }
    $template->param(eqOnReserve => \@eqOnReserveList);
    $template->param(
        username    => 1,
        sort1       => "username",
    );
}

sub getOnReserve_teacherList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input)= @_;
    my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my @teacherList = ();
    my $eqOnReserve;
    my $rname;
    @teacherList = split /\$/, $input->{'teacherList'};    
    
    if (scalar(@teacherList) == 0){
        push @teacherList, '';}
    else{
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }
    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 >= '$dateFrom' && r.dateReserve <= '$dateTo' &&  u.teacher = ? &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))";

    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"
                );

    my $nList = scalar(@teacherList);
    for ( my $i = 0; $i < $nList; $i++){
        $sth->execute($teacherList[$i]);
        while (my $reserve = $sth->fetchrow_hashref){
            my ($rid_1, $recordInfo) =
            eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
            foreach my $f( @$recordInfo){
                if ($f->{'fId'} == $eqNameId){
                    $rname = $f->{'fValue'};
                    last;
                }
            }
            $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'};
            if (!$reserve->{'teacher'} || $reserve->{'teacher'} eq ""){
                $reserve->{'teacher'} = "N/A";
            }
            if (!$eqOnReserve->{$reserve->{'teacher'}}){
                $eqOnReserve->{$reserve->{'teacher'}}->{'teachername'} = $reserve->{'teacher'};
                $eqOnReserve->{$reserve->{'teacher'}}->{'homeroom'} = $reserve->{'homeroom'};
                $eqOnReserve->{$reserve->{'teacher'}}->{'building'} = $reserve->{'building'};
            }
            push @{$eqOnReserve->{$reserve->{'teacher'}}->{'group'}}, $reserve;
        }
    }
    $sth->finish;
    my @eqOnReserveList = ();
    foreach my $t( sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$t};
    }
    $template->param(eqOnReserve => \@eqOnReserveList);
    $template->param(
        teacher => 1,
        sort1   => "teacher",
    );
}

sub getOnReserve_gradeList {
    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my @gradeList = ();
    my $eqOnReserve;
    my $rname;

    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){
        push @gradeList, '';}
    else{
        foreach my $grade(@gradeList){
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }

    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 >= '$dateFrom' && r.dateReserve <= '$dateTo' &&  u.grade = ? &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))";

    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"
       );
    my $nList   = scalar(@gradeList);
    for (my $i = 0; $i < $nList; $i++){
        $sth->execute($gradeList[$i]);
        while (my $reserve = $sth->fetchrow_hashref){
            my ($rid_1, $recordInfo) = eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
            foreach my $f( @$recordInfo){
                if ($f->{'fId'} == $eqNameId){
                    $rname = $f->{'fValue'};
                    last;
                }
            }
            if ($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->{'dateReserveFormat'} = date_time_text ($reserve->{'dateReserve'}, 0, 'en');
            $reserve->{'dateExpiryFormat'} =  date_time_text ($reserve->{'dateExpiry'}, 0 ,'en');
            $reserve->{'qty'} = $reserve->{'numReserve'}  + $reserve->{'numHold'};
            if (!$eqOnReserve->{$reserve->{'grade'}}){
                $eqOnReserve->{$reserve->{'grade'}}->{'grade'} = $reserve->{'grade'};
            }
            push @{$eqOnReserve->{$reserve->{'grade'}}->{'group'}}, $reserve;
        }
    }
    $sth->finish;
    $sth_onHold->finish;

    my @eqOnReserveList = ();
    foreach my $g (sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$g};
    }
    $template->param(eqOnReserve => \@eqOnReserveList);
    $template->param(
        grade   => 1,
    );
}

sub getOnReserve_homeroomList {

    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my @homeroomList = ();
    my $eqOnReserve;
    my $rname;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    if (scalar(@homeroomList) == 0){
        push @homeroomList, '';}
    else{
        foreach my $hr(@homeroomList){
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
    }
 
    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 >= '$dateFrom' && r.dateReserve <= '$dateTo' &&  u.homeroom = ? &&
                        ((h.dateLoan is null && h.dateCancel is null && h.dateExpiry > now())
                        || (r.numCopyReserve > 0 && (h.dateCancel is not null || h.idReserve is null)))";

    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"
    );

    my $nList   = scalar(@homeroomList);
    for (my $i = 0; $i < $nList; $i++){
        $sth->execute($homeroomList[$i]);
        while (my $reserve = $sth->fetchrow_hashref){
            my ($rid_1, $recordInfo) =
            eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
            foreach my $f( @$recordInfo){
                if ($f->{'fId'} == $eqNameId){
                    $rname = $f->{'fValue'};
                    last;
                }
            }
            if ($reserve->{'homeroom'} eq "") {
                $reserve->{'homeroom'} = " N/A";
            }
            $reserve->{'rname'} = $reserve->{'rid'};
            $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'};

            if (!$eqOnReserve->{$reserve->{'homeroom'}}){
                $eqOnReserve->{$reserve->{'homeroom'}}->{'homeroom'} = $reserve->{'homeroom'};
                $eqOnReserve->{$reserve->{'homeroom'}}->{'building'} = $reserve->{'building'};
                $eqOnReserve->{$reserve->{'homeroom'}}->{'teacher'} = $reserve->{'teacher'};
            }
            push @{$eqOnReserve->{$reserve->{'homeroom'}}->{'group'}}, $reserve;
        }
    }
    $sth->finish;
    $sth_onHold->finish;

    my @eqOnReserveList = ();
    foreach my $g (sort keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$g};
    }
    $template->param(eqOnReserve => \@eqOnReserveList);
    $template->param(
        homeroom   => 1,
    );
}
=item
sub getOnReserve_userNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my $eqNameId = eq_def_getFieldId($dbh, {defType =>'record', fname =>'Equipment Name'});
    my @idList = ();
    my $eqOnReserve;
    my $rname;
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
        
   
     my $sql = "
        select  r.*, u.* 
        from    eq_reserve as r inner join opl_user as u on r.uid = u.uid
        where   r.dateCancel is null && r.dateExpiry > now()  && 
            r.dateReserve >= '$dateFrom' && r.dateReserve <= '$dateTo' && r.uid = ?";
    
    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 = $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"
                );
    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        while (my $reserve =$sth->fetchrow_hashref ){
            my ($rid_1, $recordInfo) =
            eq_record_findByRId($dbh,{recordId=>$reserve->{'rid'}});
            foreach my $f( @$recordInfo){
                if ($f->{'fId'} == $eqNameId){
                    $rname = $f->{'fValue'};
                    last;
                }
            }
            $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'}, 1, 'en');
            $reserve->{'dateExpiryFormat'} =  date_time_text ($reserve->{'dateExpiry'}, 1 ,'en');
            $reserve->{'qty'} = $reserve->{'numReserve'}  + $reserve->{'numHold'};

            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'}}->{'teacher'} = $reserve->{'teacher'};
                $eqOnReserve->{$reserve->{'uid'}}->{'userbarcode'} = $reserve->{'userbarcode'};
                $eqOnReserve->{$reserve->{'uid'}}->{'phone'} = $reserve->{'phone'};
            }
            push @{$eqOnReserve->{$reserve->{'uid'}}->{'group'}}, $reserve;
        }    
    }
    $sth->finish;
    my @eqOnReserveList = ();
    foreach my $k(keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveNotice => \@eqOnReserveList);
}

sub getOnReserve_teacherNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @teacherList = ();
    my $eqOnReserve;
    my $rname;
    @teacherList = split /\$/, $input->{'teacherList'};
    if (scalar(@teacherList) == 0){ 
        push @teacherList, ''; }
    else{
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.teacher = ?    ";
   
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($teacherList[$i]);
        while (my $reserve =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $reserve->{'barcode'});
            $reserve->{'rname'} = $itemInfo->{'rname'};
            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'}}->{'teacher'} = $reserve->{'teacher'};
                $eqOnReserve->{$reserve->{'uid'}}->{'userbarcode'} = $reserve->{'userbarcode'};
                $eqOnReserve->{$reserve->{'uid'}}->{'phone'} = $reserve->{'phone'};
            }
            push @{$eqOnReserve->{$reserve->{'uid'}}->{'group'}}, $reserve;
        }    
    }
    $sth->finish;
    my @eqOnReserveList = ();
    foreach my $k(keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveNotice => \@eqOnReserveList);
}

sub getOnLoan_homeroomNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @homeroomList = ();
    my $eqOnReserve;
    my $rname;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    if (scalar(@homeroomList) == 0){ 
        push @homeroomList, ''; }
    else{
        foreach my $hr(@homeroomList){
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
    }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.homeroom = ?    ";
   
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@homeroomList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($homeroomList[$i]);
        while (my $reserve =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $reserve->{'barcode'});
            $reserve->{'rname'} = $itemInfo->{'rname'};
            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'}}->{'teacher'} = $reserve->{'teacher'};
                $eqOnReserve->{$reserve->{'uid'}}->{'userbarcode'} = $reserve->{'userbarcode'};
                $eqOnReserve->{$reserve->{'uid'}}->{'phone'} = $reserve->{'phone'};
            }
            push @{$eqOnReserve->{$reserve->{'uid'}}->{'group'}}, $reserve;
        }    
    }
    $sth->finish;
    my @eqOnReserveList = ();
    foreach my $k(keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveNotice => \@eqOnReserveList);
}

sub getOnReserve_gradeNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @gradeList = ();
    my $eqOnReserve;
    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){ 
        push @gradeList, ''; }
    else{
        foreach my $grade(@gradeList){
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.grade = ?    ";
   
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@gradeList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($gradeList[$i]);
        while (my $reserve =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $reserve->{'barcode'});
            $reserve->{'rname'} = $itemInfo->{'rname'};
            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'}}->{'teacher'} = $reserve->{'teacher'};
                $eqOnReserve->{$reserve->{'uid'}}->{'userbarcode'} = $reserve->{'userbarcode'};
                $eqOnReserve->{$reserve->{'uid'}}->{'phone'} = $reserve->{'phone'};
            }
            push @{$eqOnReserve->{$reserve->{'uid'}}->{'group'}}, $reserve;
        }    
    }
    $sth->finish;
    my @eqOnReserveList = ();
    foreach my $k(keys %{$eqOnReserve}){
        push @eqOnReserveList, $eqOnReserve->{$k};
    }
    $template->param(eqOnReserveNotice => \@eqOnReserveList);
}
=cut