#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;

use Time::localtime;

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 ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemonloan_prt.tmpl',
            reqPermission   => 'notice',
        }
    );
   my $incExcOpt = $input->{'incExcOpt'};
    if(!$incExcOpt || $incExcOpt ne 'inclusion'){
        $incExcOpt='exclusion';
    }

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 $dateFrom = $input->{'dateFrom'};
    my $dateTo = $input->{'dateTo'};
    
    my $dateToday = date_f005();
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;

    my $byPass = (!$dateFrom && !$dateTo) ? 1 : 0;

    $dateFrom = $dateToday if ( !$dateFrom );
    $dateTo = $dateToday if ( !$dateTo );
   
    my $pNum = $input->{'pNum'};

      
    if(defined $input->{'incUserInfo'} && $input->{'incUserInfo'} eq '1'){
        if ( $sort1 eq 'username' )
        {
            if ( $input->{'list'} )
            { 
              GetOnLoan_UserList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo,$input, $input->{'orderby'},$incExcOpt);
                $template->param(idlist => 1);
            }
            else            
            { 
               GetOnLoan_UserNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'},$incExcOpt);
               $template->param(odnotice => 1);
            }
        }
        elsif ( $sort1 eq 'teacher' )
        {
            if ( $input->{'list'} )
            { 
                GetOnLoan_TeacherList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt);
                $template->param(teacherlist => 1);
            }
            else            
            { 
                GetOnLoan_TeacherNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt); 
                $template->param(odnotice => 1);
            }
        }
        elsif ( $sort1 eq 'homeroom' )
        {
            if ( $input->{'list'} )
            { 
                GetOnLoan_HomeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt); 
                $template->param(homeroomlist => 1);
            }
            else            
            { 
                GetOnLoan_HomeroomNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt); 
                $template->param(odnotice => 1);
            }
        }
        elsif ( $sort1 eq 'grade' )
        {
            #GetOnLoan_Grade($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo);
            if ( $input->{'list'} )
            { 
                GetOnLoan_GradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt);
                $template->param(gradelist => 1);
            }
            else            
            { 
                GetOnLoan_GradeNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt); 
                $template->param(odnotice => 1);
            }
        }
    }
    else
    {
        #GetOnLoan_Grade($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo);
        if ( $input->{'list'} && $input->{'idloanList'} ne '' )
        { 
            GetOnLoan_noneGrpList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt);
            $template->param(idloanlist => 1);
        }
    }

#    my $optNotice = Opals::Context->preference('reportGroup');
#    if ( $optNotice == 1 )
#        { $template->param(reportHomeroom => 1); }
#    elsif ( $optNotice == 2 )
#        { $template->param(reportTeacher => 2); }
#    elsif ( $optNotice == 3 )
#        { $template->param(reportGrade => 3); }
    if($input->{'headers'} eq 'true'){
        $template->param(includeHeaders => 1);
    }
    OptionPrintNotice($dbh, $template);
    my $itemOnLoanMsgMap =loc_getMsgFile('circ/itemOnLoan.msg');
    loc_write($template,$itemOnLoanMsgMap);

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


#--------------------------------------------------------
#Thu, Jun 03, 2010 @ 12:09:22 EDT
sub GetOnLoan_noneGrpList{
    my($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;
    my $idloanlist = '';
    if(!$sort2 || $sort2 eq''){ $sort2='dewey';}
    my $szSQL = "
select  i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        now()>dateDue as overDue , m.tempIll 
from    opl_loan  l inner join opl_user u using(uid)
        inner join  opl_item i using(barcode) 
        inner join opl_marcRecord  m using(rid) 
where   dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid
        && to_days(dateLoan) >= to_days('$dateFrom') 
        && to_days(dateLoan) <= to_days('$dateTo') ";

    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    if($input->{'idloanList'} && $input->{'idloanList'}  ne ''){
        $idloanlist =  $input->{'idloanList'};
        $idloanlist =~ s/\$+/\$/g;
        $idloanlist =~ s/^\$|\$$//g;
        $idloanlist =~ s/\$/,/g;
       $szSQL .=  " && idloan in ($idloanlist) ";
    }
    if($sort2 ne ""){
        if($sort2 eq 'username'){
            $szSQL .=  " order by u.lastname,u.firstname " 
        }
        else{
            $szSQL .=  " order by $sort2  "
        }
         
    }

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my @DataRecs=();
    while ( my $loan = $query->fetchrow_hashref() ){
        $loan->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
            if( $incExcOpt eq 'inclusion' && 
                ($loan->{'tempIll'} eq 'temporary' || $loan->{'tempIll'} eq 'ILL')){
                  $loan->{'tempIll'} = 1;
            }
            push  @DataRecs, $loan;  
    }
    
    if($input->{'headers'} eq 'true'){
        $template->param(includeUserInfo => 1);
    }

    $template->param(ItemsInLoan => \@DataRecs);
    $query->finish;

}

#--------------------------------------------------------
sub GetOnLoan_TeacherList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;

    my @roomlist = ();
    my @teacherlist = ();
    my @bdinglist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct trim(homeroom) as homeroom,  trim(buildingcode) as buildingcode,  trim(teacher) as teacher from opl_user";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            if ( !$rec->{'homeroom'} ) { $rec->{'homeroom'} = ''; }
            if ( !$rec->{'buildingcode'} ) { $rec->{'buildingcode'} = ''; }
            if ( !$rec->{'teacher'} ) { $rec->{'teacher'} = ''; }  
            push @roomlist, $rec->{'homeroom'};
            push @bdinglist, $rec->{'buildingcode'};
            push @teacherlist, $rec->{'teacher'};
        }
        $query->finish;
    }
    else
    {
        @roomlist = split /\$/, $input->{'roomlist'};
        @bdinglist = split /\$/, $input->{'bdinglist'};
        @teacherlist = split /\$/, $input->{'teacherlist'};

        if ( scalar(@roomlist) == 0 ) { push @roomlist, ''; }
        if ( scalar(@bdinglist) == 0 ) { push @bdinglist, ''; }
        if ( scalar(@teacherlist) == 0 ) { push @teacherlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey,
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && homeroom=? 
        && buildingcode=? && teacher=?  
        && to_days(dateLoan) >= to_days('$dateFrom')  
        && to_days(dateLoan) <= to_days('$dateTo') ";
    
    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@roomlist);
    
    for (my $i=0; $i<$nList; $i++)
    {
              
        my $bResult = $query->execute($roomlist[$i], $bdinglist[$i], $teacherlist[$i]);
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
           push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                phone           => $loan->{'phone'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                uid             => $loan->{'uid'},
            };
        }
        $query->finish;
                
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();
            SecondSort(\@data, \@loanRecs, $sort2);
            foreach my $rec (@data){
                $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
            }

            push @DataRecs, { group => \@data, teacher => trim($teacherlist[$i]), hmroom => trim($roomlist[$i]), 
                        building => trim($bdinglist[$i]) };
        }
        @loanRecs = ();
    }

    $template->param(ItemsInLoan => \@DataRecs);
    $template->param(test=> $input->{'roomlist'});
}
#--------------------------------------------------------
sub GetOnLoan_TeacherNotice
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;

    my @roomlist = ();
    my @teacherlist = ();
    my @bdinglist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct trim(homeroom) as homeroom,trim(buildingcode) as  buildingcode, trim(teacher) as teacher from opl_user order by teacher, buildingcode, homeroom";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            if ( !$rec->{'homeroom'} ) { $rec->{'homeroom'} = ''; }
            if ( !$rec->{'buildingcode'} ) { $rec->{'buildingcode'} = ''; }
            if ( !$rec->{'teacher'} ) { $rec->{'teacher'} = ''; }

            push @roomlist, $rec->{'homeroom'};
            push @bdinglist, $rec->{'buildingcode'};
            push @teacherlist, $rec->{'teacher'};
        }
        $query->finish;
    }
    else
    {
        @roomlist = split /\$/, $input->{'roomlist'};
        @bdinglist = split /\$/, $input->{'bdinglist'};
        @teacherlist = split /\$/, $input->{'teacherlist'};

        if ( scalar(@roomlist) == 0 ) { push @roomlist, ''; }
        if ( scalar(@bdinglist) == 0 ) { push @bdinglist, ''; }
        if ( scalar(@teacherlist) == 0 ) { push @teacherlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom,trim(buildingcode) as buildingcode, username, userbarcode,
        l.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i 
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && homeroom=?
        && buildingcode=? && teacher=? 
        && to_days(dateLoan) >= to_days('$dateFrom')  
        && to_days(dateLoan) <= to_days('$dateTo') ";
        
    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
         
    $szSQL .= "  order by teacher, buildingcode, homeroom, lastname, firstname, uid";

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@roomlist);

    for (my $i=0; $i<$nList; $i++)
    {
        if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
        if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
        if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }

        my $bResult = $query->execute($roomlist[$i], $bdinglist[$i], $teacherlist[$i]);
        MakeNotice($query, \@DataRecs);        
    }

    $template->param(odusers => \@DataRecs);
}

#--------------------------------------------------------
sub GetOnLoan_HomeroomList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;

    my @roomlist = ();
    my @teacherlist = ();
    my @bdinglist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, trim(teacher) as teacher from opl_user order by buildingcode, homeroom, teacher";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            if ( !$rec->{'homeroom'} ) { $rec->{'homeroom'} = ''; }
            if ( !$rec->{'buildingcode'} ) { $rec->{'buildingcode'} = ''; }
            if ( !$rec->{'teacher'} ) { $rec->{'teacher'} = ''; }

            push @roomlist, $rec->{'homeroom'};
            push @bdinglist, $rec->{'buildingcode'};
            push @teacherlist, $rec->{'teacher'};
        }
        $query->finish;
    }
    else
    {
        @roomlist = split /\$/, $input->{'roomlist'};
        @bdinglist = split /\$/, $input->{'bdinglist'};
        @teacherlist = split /\$/, $input->{'teacherlist'};

        if ( scalar(@roomlist) == 0 ) { push @roomlist, ''; }
        if ( scalar(@bdinglist) == 0 ) { push @bdinglist, ''; }
        if ( scalar(@teacherlist) == 0 ) { push @teacherlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        l.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i 
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && homeroom=?
        && buildingcode=? && teacher=? 
        && to_days(dateLoan) >= to_days('$dateFrom')  
        && to_days(dateLoan) <= to_days('$dateTo') ";

    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    my $query = $dbh->prepare($szSQL);
    my @DataRecs;# = ();
    my $nList = scalar(@roomlist);
    
    for (my $i=0; $i<$nList; $i++)
    {
       # if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
       # if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
       # if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }
       
        my $bResult = $query->execute($roomlist[$i], $bdinglist[$i], $teacherlist[$i]);
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                phone           => $loan->{'phone'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                uid             => $loan->{'uid'},
            };
        }                    
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();

            SecondSort(\@data, \@loanRecs, $sort2);
            foreach my $rec (@data){
                $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
            }
            push @DataRecs, { group => \@data, teacher => trim($teacherlist[$i]), hmroom => trim($roomlist[$i]), 
                            building => trim($bdinglist[$i]) };
        }
        @loanRecs = ();
    }

    $template->param(ItemsInLoan => \@DataRecs);
}

#--------------------------------------------------------
sub GetOnLoan_HomeroomNotice
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;

    my @roomlist = ();
    my @teacherlist = ();
    my @bdinglist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct trim(homeroom) as homeroom,  trim(buildingcode) as buildingcode,  trim(teacher) as teacher from opl_user order by buildingcode, homeroom, teacher";
        my $query = $dbh->prepare($sql);
        $query->execute();
        
        while ( my $rec = $query->fetchrow_hashref )
        {
            if ( !$rec->{'homeroom'} ) { $rec->{'homeroom'} = ''; }
            if ( !$rec->{'buildingcode'} ) { $rec->{'buildingcode'} = ''; }
            if ( !$rec->{'teacher'} ) { $rec->{'teacher'} = ''; }

            push @roomlist, $rec->{'homeroom'};
            push @bdinglist, $rec->{'buildingcode'};
            push @teacherlist, $rec->{'teacher'};
        }
        $query->finish;
    }
    else
    {
        @roomlist = split /\$/, $input->{'roomlist'};
        @bdinglist = split /\$/, $input->{'bdinglist'};
        @teacherlist = split /\$/, $input->{'teacherlist'};

        if ( scalar(@roomlist) == 0 ) { push @roomlist, ''; }
        if ( scalar(@bdinglist) == 0 ) { push @bdinglist, ''; }
        if ( scalar(@teacherlist) == 0 ) { push @teacherlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade,trim(homeroom) as  homeroom,trim(buildingcode) as  buildingcode, username, userbarcode,
        l.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i 
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && homeroom=?
        && buildingcode=? && teacher=?  
        && to_days(dateLoan) >= to_days('$dateFrom')   
        && to_days(dateLoan) <= to_days('$dateTo') ";
         
   
    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }
    $szSQL .= " order by lastname, firstname, uid";
    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@roomlist);

    for (my $i=0; $i<$nList; $i++)
    {
        if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
        if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
        if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }

        my $bResult = $query->execute($roomlist[$i], $bdinglist[$i], $teacherlist[$i]);
        MakeNotice($query, \@DataRecs);
    }

    $template->param(odusers => \@DataRecs);
}

#--------------------------------------------------------
sub GetOnLoan_GradeList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;
    
    my @gradelist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct grade from opl_user order by grade";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @gradelist, $rec->{'grade'};
        }
        $query->finish;
    }
    else
    {
        @gradelist = split /\$/, $input->{'gradelist'};
        if ( scalar(@gradelist) == 0 ) { push @gradelist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade,  trim(homeroom) as homeroom,  trim(buildingcode) as buildingcode, username, userbarcode,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && grade=?  
        && to_days(dateLoan) >= to_days('$dateFrom')   
        && to_days(dateLoan) <= to_days('$dateTo') "; 
   
    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@gradelist);
    my $debugStr=""; 
    for (my $i=0; $i<$nList; $i++)
    {   
        my $gradeLevel=$gradelist[$i];
        if ( $gradelist[$i] eq 'none' ) { $gradeLevel = ''; }
        my $bResult = $query->execute($gradeLevel);
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                homeroom        => $loan->{'homeroom'},
                buildingcode    => $loan->{'buildingcode'}, 
                teacher         => $loan->{'teacher'},
                deltaDueDate    => $loan->{'deltaDueDate'}, 
                uid             => $loan->{'uid'},
                phone           => $loan->{'phone'},
            };
        }                    
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();
            SecondSort(\@data, \@loanRecs, $sort2);
            foreach my $rec (@data){
                $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
            }
            push @DataRecs, { group => \@data, grade => $gradelist[$i] };
        }
        @loanRecs = ();
    }

    $template->param(ItemsInLoan => \@DataRecs);
}

#--------------------------------------------------------
sub GetOnLoan_GradeNotice
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input,$incExcOpt) = @_;

    my @gradelist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct grade from opl_user order by grade";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @gradelist, $rec->{'grade'};
        }
        $query->finish;
    }
    else
    {
        @gradelist = split /\$/, $input->{'gradelist'};
        if ( scalar(@gradelist) == 0 ) { push @gradelist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && grade=?
        && to_days(dateLoan) >= to_days('$dateFrom')   
        && to_days(dateLoan) <= to_days('$dateTo') ";
    
    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }        
    $szSQL .= " order by lastname, firstname, uid";

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@gradelist);
    for (my $i=0; $i<$nList; $i++)
    {
        #if ( !$gradelist[$i] ) { $gradelist[$i] = ''; }
        my $gradeLevel=$gradelist[$i];
        if ( $gradelist[$i] eq 'none' ) { $gradeLevel = ''; }
        my $bResult = $query->execute($gradeLevel);

        MakeNotice($query, \@DataRecs);
    }

    $template->param(odusers => \@DataRecs);
}

#-------------------------------------------------------------------------
sub GetOnLoan_UserList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby,$incExcOpt) = @_;
    
    my @idlist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct uid from opl_user order by lastname, firstname, uid";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @idlist, $rec->{'uid'};
        }
        $query->finish;
    } 
    else
    {
        @idlist = split /\$/, $input->{'idlist'};
        if ( scalar(@idlist) == 0 ) { push @idlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && l.uid=?  
        && to_days(dateLoan) >= to_days('$dateFrom')   
        && to_days(dateLoan) <= to_days('$dateTo') ";
         
    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    if ( $orderby eq 'name' ) { $szSQL .= " order by lastname, firstname, username"; }
    else { $szSQL .= " order by homeroom, lastname, firstname, username"; }



    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@idlist);

    for (my $i=0; $i<$nList; $i++)
    {
       # if ( $idlist[$i]==0 ) { $idlist[$i] = ''; }
        my $bResult = $query->execute($idlist[$i]);
        my @loanRecs = ();
        my ($lastname,$firstname,$username,$grade,$homeroom,$building,$teacher);
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            ($lastname,$firstname,$username,$grade,$homeroom,$building,$teacher)=($loan->{'lastname'},
                                                                           $loan->{'firstname'},
                                                                           $loan->{'username'},
                                                                           $loan->{'grade'},
                                                                           $loan->{'homeroom'},
                                                                           $loan->{'buildingcode'},
                                                                           $loan->{'teacher'});
            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},   
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                homeroom        => $loan->{'homeroom'},
                buildingcode    => $loan->{'buildingcode'}, 
                teacher         => $loan->{'teacher'},
                deltaDueDate    => $loan->{'deltaDueDate'}, 
                uid             => $loan->{'uid'},
                phone           => $loan->{'phone'},
            };
        }                    
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();
            SecondSort(\@data, \@loanRecs, $sort2);
            foreach my $rec (@data){
                $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
            }
            push @DataRecs, { group => \@data,homeroom=>$homeroom, building=>$building, teacher=>$teacher, grade=>$grade,  lastname=>$lastname, firstname =>$firstname,username=>$username };
        }
        @loanRecs = ();
    }

    $template->param(ItemsInLoan => \@DataRecs);

}
#--------------------------------------------------------
sub GetOnLoan_UserNotice
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby,$incExcOpt) = @_;

    my @idlist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct uid from opl_user order by lastname, firstname, uid";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @idlist, $rec->{'uid'};
        }
        $query->finish;
    }
    else
    {
        @idlist = split /\$/, $input->{'idlist'};
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade,trim(homeroom) as  homeroom,trim(buildingcode) as  buildingcode, username, userbarcode,
        l.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue, 
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i 
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && l.uid=?  
        && to_days(dateLoan) >= to_days('$dateFrom')   
        && to_days(dateLoan) <= to_days('$dateTo') "; 

    if($incExcOpt eq 'exclusion'){
       $szSQL .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }

    if ( $orderby eq 'name' ) { $szSQL .= " order by lastname, firstname, username"; }
    else { $szSQL .= " order by homeroom, lastname, firstname, username"; }
   
    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@idlist);

    for (my $i=0; $i<$nList; $i++)
    {
        my $bResult = $query->execute($idlist[$i]);
        
        MakeNotice($query, \@DataRecs);
    }

    $template->param(odusers => \@DataRecs);
}
#----------------------------------------------------------
sub trimArticle{
    my ($str)=@_;
    $str =~ s/^a |^an |^the //i;
    return $str;
}


#----------------------------------------------------------
sub SecondSort
{
    my ($data, $loanRecs, $sort2) = @_;
    
    if ( $sort2 eq "username" )
    { 
        @$data = sort { $a->{"lastname"} cmp $b->{"lastname"} 
                        || $a->{"firstname"} cmp $b->{"firstname"} 
                        || $a->{"uid"} <=> $b->{'uid'} } @$loanRecs; 
    }
    elsif($sort2 eq "title"){
        @$data = sort { lc(trimArticle($a->{"title"})) cmp lc(trimArticle($b->{"title"})) } @$loanRecs; 
    }
    else { @$data = sort { $a->{$sort2} cmp $b->{$sort2} } @$loanRecs; }

}

#--------------------------------------------------------
sub AddDateConstraint
{
    my ($szSQL, $dateFrom, $dateTo) = @_;
    $szSQL = $szSQL . " && to_days(dateDue) >= to_days('$dateFrom')";
    $szSQL = $szSQL . " && to_days(dateDue) <= to_days('$dateTo')";
    $szSQL = $szSQL . " && to_days(dateDue) < to_days(now())";
    return $szSQL;
}

#--------------------------------------------------------
sub MakeNotice
{
    my ($query, $DataRecs) = @_;
    
    my %iduser = {};
    $iduser{'uid'} = -1;

    my @loanRecs = ();
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if ( $iduser{'uid'} == -1 ) { %iduser = %$loan; }

        if ( $iduser{'uid'} != $loan->{'uid'} )
        {
            # Prepare the group title
            my @data = @loanRecs;
   
            SecondSort(\@data, \@data, "title");
            if ( scalar(@data) != 0 )
            {
                push @$DataRecs, { oditems => \@data, firstname => $iduser{'firstname'}, 
                            lastname => $iduser{'lastname'}, userbarcode => $iduser{'userbarcode'},
                            username => $iduser{'username'}, uid => $iduser{'uid'},
                            homeroom => $iduser{'homeroom'}, teacher => $iduser{'teacher'},
                            grade => $iduser{'grade'}, phone => $iduser{'phone'} };
            }
            @loanRecs = ();
            %iduser = %$loan;
        }
        
        # Format each row of data

        $loan->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);
    push @loanRecs, {
            grade           => $loan->{'grade'},
            title           => $loan->{'title'},
            dewey           => $loan->{'dewey'}, 
            barcode         => $loan->{'barcode'},
            price           => $loan->{'price'},
            dateDue         => $loan->{'dateDue'},
            dateLoan        => $loan->{'dateLoan'},
            deltaDueDate    => $loan->{'deltaDueDate'},
        };
    }                    
    $query->finish;
    if ( scalar(@loanRecs) != 0 )
    { 
        SecondSort(\@loanRecs, \@loanRecs, "title");
        #@loanRecs = sort { lc(trimArticle($a->{"title"})) cmp lc(trimArticle($b->{"title"})) } @loanRecs; 
        push @$DataRecs, {
            oditems     => \@loanRecs,
            firstname   => $iduser{'firstname'}, 
            lastname    => $iduser{'lastname'},
            userbarcode => $iduser{'userbarcode'},
            username    => $iduser{'username'},
            uid    => $iduser{'uid'},
            homeroom    => $iduser{'homeroom'},
            teacher     => $iduser{'teacher'},
            grade       => $iduser{'grade'},
            phone       => $iduser{'phone'},
        }; 
    }
}

#----------------------------------------------------------
sub OptionPrintNotice
{
    my ($dbh, $template) = @_;
    
    my $optNotice = Opals::Context->preference('reportGroup');

    my @arrOpt = split /,/, $optNotice;
    my @arrPrint = ();

    foreach my $rec (@arrOpt)
    {
        $rec =~ s/^\s+//;
	    $rec =~ s/\s+$//;
        if ( $rec == 1 )
            { push @arrPrint, {reportHomeroom => 1}; }
        elsif ( $rec == 2 )
            { push @arrPrint, {reportTeacher => 2}; }
        elsif ( $rec == 3 )
            { push @arrPrint, {reportGrade => 3}; }
        else
            { push @arrPrint, {reportPhone => 4}; }
    }
    $template->param(optPrn => \@arrPrint);
}
####################################################
sub trim{
    my $str = shift;
    $str =~ s/^\s+//;
    $str =~ s/\s+$//;
    return $str;

}