#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Encode;
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
    tmpl_preference
);
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);

my $pref = tmpl_preference($dbh);
my $reportGroup;
if($pref->{'reportGroup'} == 1 ){
    $reportGroup = "homeroom";
}
elsif($pref->{'reportGroup'} == 2 ){
    $reportGroup = "teacher";
}
elsif($pref->{'reportGroup'} ==3 ){
    $reportGroup = "grade";
}
else{
    $reportGroup = "username";
}
my $sort1 = $input->{'sort1'};
my $sort2 = $input->{'sort2'};
if ( $sort1 eq '' )
{
       $sort1 = $reportGroup ;
}
if ( !$sort2 )
{
    if ( $sort1 eq "username" ) { $sort2 = "title"; }
    else { $sort2 = "username"; }
}

my $pagesize = 1000000;
if ( $sort1 eq 'username' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/odnt_user.tmpl',
            reqPermission   => 'notice',
        }
    );
     $template->param(username => 1);
    #$pagesize = 15;
}
elsif ( $sort1 eq 'teacher' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/odnt_teacher.tmpl',
            reqPermission   => 'notice',
        }
    ); 
     $template->param(teacher => 1);
    #$pagesize = 2;
}
elsif ( $sort1 eq 'homeroom' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/odnt_homeroom.tmpl',
            reqPermission   => 'notice',
        }
    );
     $template->param(homeroom => 1);
    #$pagesize = 2;
}
elsif ( $sort1 eq 'grade' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/odnt_grade.tmpl',
            reqPermission   => 'notice',
        }
    );
     $template->param(grade => 1);
    #$pagesize = 1;
}
#$template->param(xxx => $sort2);

#if ($permission && $permission->{'user_delete'}) 
#{
#    my $input = $cgi->Vars();
#    if ($input->{'op'} eq 'delete') 
#    {
#        if (user_delete($dbh, $input)) 
#        {
#            $template->param(success => 1);
#        }
#        else 
#        {
#            $template->param(error => 1);
#        }
#    }
#    $template->param(userDel => 1);
#}
    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 );
    $dateFrom = $pref->{'dateFirst'} if ( !$dateFrom );
    $dateTo = $dateToday if ( !$dateTo );
   
    my $pNum = $input->{'pNum'};
    if ( $byPass ) 
    { 
        $pNum = 1; 
        $template->param(ReportMsg => 1);
        goto NoProcess; 
    }

    if ( $sort1 eq 'username' )
    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'circ/odnt_user.tmpl',
                reqPermission   => 'notice',
            }
        );
         $template->param(username => 1);
         GetOverdueUser($dbh, $template, $sort1, $sort2, $input->{'idlist'}, $dateFrom, $dateTo, $input->{'orderby'}, $pNum, $pagesize);
    }
    elsif ( $sort1 eq 'teacher' )
    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'circ/odnt_teacher.tmpl',
                reqPermission   => 'notice',
            }
        ); 
         $template->param(teacher => 1);
         GetOverdueTeacher($dbh, $template, $sort1, $sort2, $input->{'idlist'}, $dateFrom, $dateTo, $pNum, $pagesize);
    }
    elsif ( $sort1 eq 'homeroom' )
    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'circ/odnt_homeroom.tmpl',
                reqPermission   => 'notice',
            }
        );
         $template->param(homeroom => 1);
         GetOverdueHomeroom($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize);
    }
    elsif ( $sort1 eq 'grade' )
    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'circ/odnt_grade.tmpl',
                reqPermission   => 'notice',
            }
    );
     $template->param(grade => 1);
     GetOverdueGrade($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize);



    #$pagesize = 1;
}
########################################
# Get the total 
    my $szSql = "select count(*) from opl_loan where dateReturn is null";
    $szSql = AddDateConstraint($szSql, $dateFrom, $dateTo);
    my $queryTotal = $dbh->prepare($szSql);
    $queryTotal->execute();
    my ($totalOdHoldings) = $queryTotal->fetchrow_array;
    $queryTotal->finish();
    $totalOdHoldings = 0 if ( !$totalOdHoldings );
    $template->param(totalOdHoldings => $totalOdHoldings);

#    $szSql = "select count(distinct rid) from opl_loan as l, opl_item as i where dateReturn is null && l.barcode=i.barcode";
#    $szSql = AddDateConstraint($szSql, $dateFrom, $dateTo);
#    $queryTotal = $dbh->prepare($szSql);
#    $queryTotal->execute();
#    my ($totalOdTitles) = $queryTotal->fetchrow_array;
#    $queryTotal->finish();
#    $totalOdTitles = 0 if ( !$totalOdTitles );
#    $template->param(totalOdTitles => $totalOdTitles);

NoProcess:
    $template->param(dateFrom => substr($dateFrom, 0, 10));
    $template->param(dateTo => substr($dateTo, 0, 10));
    $template->param(sort1 => $sort1);
    $template->param(sort2 => $sort2);
    $template->param(pNum => $pNum);
    $template->param(idlist => $input->{'idlist'});
    $template->param(gradelist => $input->{'gradelist'});
    $template->param(roomlist => $input->{'roomlist'});
    $template->param(teacherlist => $input->{'teacherlist'});
    $template->param(bdinglist => $input->{'bdinglist'});

    $template->param(circulation =>1);
    my $noticeMsgMap =loc_getMsgFile('circ/notice.msg');
    loc_write($template,$noticeMsgMap);

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


#----------------------------------------------------------
sub MakePages
{
    my ($template, $pagetotal, $pNum, $pagesize) = @_;

    $template->param(total => $pagetotal);

    my @arrayref = ();
    if ( !$pNum )
        { @arrayref = tmpl_rangedPageList($pagetotal, 1, $pagesize, 10); }
    else
        { @arrayref = tmpl_rangedPageList($pagetotal, $pNum, $pagesize, 10); }

    $template->param(rangedPageList => \@arrayref);
}
    
#--------------------------------------------------------
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 GetOverdueUser
{
    my ($dbh, $template, $sort1, $sort2, $idlist, $dateFrom, $dateTo, $orderby, $pNum, $pagesize) = @_;
    
    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
        
#select  teacher, grade, homeroom, buildingcode, username, userbarcode,
#        opl_user.uid, firstname, lastname, barcode, dateLoan, dateDue,
#        to_days(now()) - to_days(dateDue) as deltaDueDate 
#from    opl_user, opl_loan 
#where   opl_user.uid = opl_loan.uid && dateReturn is null";
    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, 
        userbarcode, opl_user.email, g.gemail,
        opl_user.uid, firstname, lastname, 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 left outer join opl_guardian as g on opl_user.uid = g.uid, opl_loan, opl_marcRecord as m, opl_item as i 
where   opl_user.uid = opl_loan.uid && dateReturn is null 
        && opl_loan.barcode=i.barcode && i.rid=m.rid";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);
    
    if ( $orderby eq 'name' ) { $szSQL .= " order by concat(lastname, firstname,'zzzzzz'), username"; }
    else { $szSQL .= " order by concat(lastname, firstname,'zzzzzz'), concat(homeroom,'zzzzzz'), username"; $orderby = 'room'; }

    my @group = ($sort1, $sort2);
    my %grpval = {};

    #$template->param(xxx => $szSQL);

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my $bZebraServerDown = 0;
    
    my $count = -1;
    my @loanRecs = ();
    my @DataRecs = ();
   
    my $row = 0; 
    my $inPage = 0;
    my $initialized = 0;
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if($loan->{'grade'} eq ''){
            $loan->{'grade'}='none';
        }
        if ( !$initialized )
        {
            $initialized = 1;
            $grpval{'uid'} = $loan->{'uid'} . "diff";
        }
       
# Prepare the group title
        if ( $loan->{'uid'} ne $grpval{'uid'} ) 
        {
            if ( $inPage == 1 )
            {
                my @data = sort { $a->{$sort2} cmp $b->{$sort2} } @loanRecs;
                my $send2parent=0;
                if($grpval{'email'} eq '' && $grpval{'gemail'} ne ''){
                    $send2parent =1;
                }
                push @DataRecs, { group => \@data, uid => $grpval{'uid'}, username => $grpval{'username'},
                                 lastname => $grpval{'lastname'}, firstname => $grpval{'firstname'},
                                 useremail => $grpval{'email'}, 
                                 gemail => $grpval{'gemail'}, 
                                 userbarcode => $grpval{'userbarcode'},
                                 homeroom => $grpval{'homeroom'}, building => $grpval{'buildingcode'},
                                 teacher => $grpval{'teacher'}, grade => $grpval{'grade'}, count => $row ,
                                 email=>$grpval{'email'},send2parent=>$send2parent };
                @loanRecs = ();
                $row++;
            }    

            $count++;
            %grpval = %$loan;
            if ( $count >= $pageoffset ) { $inPage = 1; }
            if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; };
        }

        next if ( $inPage != 1 );
        
# Format each row of data
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);
        
        if (!$bZebraServerDown) {
            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            push @loanRecs, {
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'},
                barcode         => $loan->{'barcode'},
                dateLoan        => $loan->{'dateLoan'},
                dateDue         => $loan->{'dateDue'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                price           => $loan->{'price'},
            };
        }
        else {               
            push @loanRecs, {
                title           => '',
                dewey           => '',
                barcode         => $loan->{'barcode'},
                dateLoan        => $loan->{'dateLoan'},
                dateDue         => $loan->{'dateDue'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                price           => $loan->{'price'},
            };
        }
        if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; @loanRecs = (); };
    }

# Store the last group    
    $count++; $row++;
    if ( @loanRecs > 0  && $inPage == 1 )
    {
        my @data = sort { $a->{$sort2} cmp $b->{$sort2} } @loanRecs;

        my $send2parent=0;
        if($grpval{'email'} eq '' && $grpval{'gemail'} ne ''){
            $send2parent =1;
        }
        push @DataRecs, { group => \@data, uid => $grpval{'uid'}, username => $grpval{'username'},
                        lastname => $grpval{'lastname'}, firstname => $grpval{'firstname'},
                        userbarcode => $grpval{'userbarcode'},useremail => $grpval{'email'}, 
                        homeroom => $grpval{'homeroom'}, building => $grpval{'buildingcode'},
                        teacher => $grpval{'teacher'}, grade => $grpval{'grade'}, count => $row ,
                        email=>$grpval{'email'}, gemail=>$grpval{'gemail'},send2parent=>$send2parent};
    }

    $query->finish;

    my $optNotice = Opals::Context->preference('reportGroup');
    if ( $idlist )
    {
        my @idArray = split /\t/, $input->{'idlist'};

        foreach my $rec (@DataRecs)
        {
            foreach my $id (@idArray)
            {
                if ( $rec->{'uid'} == $id )
                { 
                    SendOverdueBorrower($dbh, $template, $rec, $optNotice); 
                    last;
                }
            }
        }
    }


    $template->param(ItemsInLoan => \@DataRecs);
    $template->param("sort" . $orderby => 1);
    $template->param(orderby => $orderby);

    MakePages($template, $count, $pNum, $pagesize);
}

#--------------------------------------------------------
sub GetOverdueTeacher
{
    my ($dbh, $template, $sort1, $sort2, $idlist, $dateFrom, $dateTo, $pNum, $pagesize) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
        
    my $szSQL = "
select  trim(teacher) as teacher, grade,trim(homeroom) as homeroom,trim(buildingcode) as buildingcode, username, userbarcode,
        u.uid, firstname, lastname, 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";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo) . " order by concat(teacher,'zzzzzz'), concat(buildingcode,'zzzzzz'), concat(homeroom,'zzzzzz')";

    my @group = ($sort1, $sort2);
    my %grpval = {};

      # $template->param(xxx => $szSQL);
    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    
    my $count = -1;
    my @loanRecs = ();
    my @DataRecs = ();
   
    my $row = 0; 
    my $inPage = 0;
    my $initialized = 0;
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if($loan->{'grade'} eq ''){
            $loan->{'grade'}='none';
        }
        if ( !$initialized )
        {
            $initialized = 1;
            $grpval{'teacher'} = $loan->{'teacher'} . "diff";
            $grpval{'homeroom'} = $loan->{'homeroom'} . "diff";
            $grpval{'buildingcode'} = $loan->{'buildingcode'} . "diff";
        }
        
# Prepare the group title
        if ( uc($loan->{'teacher'}) ne uc($grpval{'teacher'}) || uc($loan->{'homeroom'}) ne uc($grpval{'homeroom'}) 
            || uc($loan->{'buildingcode'}) ne uc($grpval{'buildingcode'}) )
        {
            if ( $inPage == 1 )
            {
                my @data = ();
                SecondSort(\@data, \@loanRecs, $sort2);
        
                push @DataRecs, { group => \@data, teachername => $grpval{'teacher'}, hmroom => $grpval{'homeroom'}, 
                                building => $grpval{'buildingcode'}, count => $row };
                @loanRecs = ();
                $row++;
            }    
            
            $count++;                
            %grpval = %$loan;
            if ( $count >= $pageoffset ) { $inPage = 1; }
            if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; };
        }

        next if ( $inPage != 1 );

# Format each row of data
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

        $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'}, 
            barcode         => $loan->{'barcode'},
            price           => $loan->{'price'},
            dateDue         => $loan->{'dateDue'},
            dateLoan        => $loan->{'dateLoan'},
            deltaDueDate    => $loan->{'deltaDueDate'},
            uid        => $loan->{'uid'},
        };
        if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; @loanRecs = (); };
    }

    $query->finish;

# Store the last group    
    $count++; $row++;
    if ( @loanRecs > 0 && $inPage == 1 )
    {
        my @data = ();
        SecondSort(\@data, \@loanRecs, $sort2);

        push @DataRecs, { group => \@data, teachername => $grpval{'teacher'}, hmroom => $grpval{'homeroom'}, 
                        building => $grpval{'buildingcode'}, count => $row };
    }

    if ( $idlist )
    {
        my @idArray = split /\t/, $input->{'idlist'};
        push @idArray , "";

        foreach my $rec (@DataRecs)
        {
            foreach my $id (@idArray)
            {
                if ( $rec->{'teacher'} eq $id )
                { 
                    SendOverdueTeacher($dbh, $template, $id, \@DataRecs); 
                    last;
                }
            }
        }
    }

    $template->param(ItemsInLoan => \@DataRecs);
    
    MakePages($template, $count, $pNum, $pagesize);
}

#--------------------------------------------------------
sub GetOverdueHomeroom
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
        
    my $szSQL = "
select  trim(teacher) as teacher, grade,trim(homeroom) as  homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        l.uid, firstname, lastname, 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";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo) . " order by concat(homeroom,'zzzzzz'), concat(buildingcode,'zzzzzz'), concat(teacher,'zzzzzz')";
        #$template->param(xxx => $szSQL);

    my @group = ($sort1, $sort2);
    my %grpval = {};

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my $bZebraServerDown = 0;
    
    my $count = -1;
    my @loanRecs = ();
    my @DataRecs = ();
    
    my $row = 0;
    my $inPage = 0;
    my $initialized = 0;
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if($loan->{'grade'} eq ''){
            $loan->{'grade'}='none';
        }
       # $loan->{'teacher'}=~ s/^ +| +$//g;
        if ( !$initialized )
        {
            $initialized = 1;
            $grpval{'homeroom'}     = $loan->{'homeroom'} . "diff";
            $grpval{'buildingcode'} = $loan->{'buildingcode'} . "diff";
            $grpval{'teacher'}      = $loan->{'teacher'} . "diff";
        }
                 
# Prepare the group title
        if ( uc($loan->{'homeroom'}) ne uc($grpval{'homeroom'}) || uc($loan->{'buildingcode'}) ne uc($grpval{'buildingcode'}) 
                || uc($loan->{'teacher'}) ne uc($grpval{'teacher'}) )
        {
            if ( $inPage == 1 )
            {
                my @data = ();
        
                SecondSort(\@data, \@loanRecs, $sort2);
                push @DataRecs, { group => \@data, teacher => $grpval{'teacher'}, hmroom => $grpval{'homeroom'}, 
                                building => $grpval{'buildingcode'}, count => $row };
                @loanRecs = ();
                $row++;
            }
            $count++;
            %grpval = %$loan;
            if ( $count >= $pageoffset ) { $inPage = 1; }
            if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; };
        }

        next if ( $inPage != 1 );

# Format each row of data
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

#        if ( !$bZebraServerDown ) {
            $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'}, 
                barcode => $loan->{'barcode'},
                price => $loan->{'price'},
                dateDue => $loan->{'dateDue'},
                dateLoan => $loan->{'dateLoan'},
                deltaDueDate => $loan->{'deltaDueDate'},
                uid => $loan->{'uid'},
            };
#        }
#        else {               
#            push @loanRecs, {firstname => $loan->{'firstname'}, lastname => $loan->{'lastname'},
#                    username => $loan->{'username'}, userbarcode => '',
#                    grade => $loan->{'grade'}, title => '', dewey => '', 
#                    barcode => $loan->{'barcode'}, dateDue => $loan->{'dateDue'},
#                    deltaDueDate => $loan->{'deltaDueDate'}, uid => $loan->{'uid'} }
#        }
    }

    $query->finish;

# Store the last group
    $count++; $row++;
    if ( @loanRecs > 0 && $inPage == 1 )
    {
        my @data = ();
        SecondSort(\@data, \@loanRecs, $sort2);

        #$grpval{'homeroom'} = 'aklsdf';
        push @DataRecs, {
            group => \@data,
            hmroom => $grpval{'homeroom'} , 
            teacher => $grpval{'teacher'},
            building => $grpval{'buildingcode'},
            count => $row };
        #$template->param(xxx => $DataRecs[$#DataRecs]->{'homeroom'});
    }

    $template->param(ItemsInLoan => \@DataRecs);
    
    MakePages($template, $count, $pNum, $pagesize);
}

#--------------------------------------------------------
sub GetOverdueGrade
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize) = @_;

    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
     
    my $szSQL = "
select  trim(teacher) as teacher, grade,  trim(homeroom) homeroom,  trim(buildingcode) buildingcode, username, userbarcode,
        u.uid, firstname, lastname, 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";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo) . " order by concat(grade,'zzzzzz'), concat(lastname, firstname,'zzzzzz')";

    my @group = ($sort1, $sort2);
    my %grpval = {};

    #$template->param(xxx => $szSQL);
    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my $bZebraServerDown = 0;
    
    my $count = -1;
    my @loanRecs = ();
    my @DataRecs = ();
   
    my $row = 0; 
    my $inPage = 0;
    my $initialized = 0;
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if($loan->{'grade'} eq ''){
            $loan->{'grade'}='none';
        }
        if ( !$initialized )
        {
            $initialized = 1;
            $grpval{'grade'} = $loan->{'grade'} . "diff";
        }
        
# Prepare the group title
        if ( $loan->{'grade'} ne $grpval{'grade'} )
        {
            if ( $inPage == 1 )
            {
                my @data = ();
                SecondSort(\@data, \@loanRecs, $sort2);
                push @DataRecs, { group => \@data, grade => $grpval{'grade'}, count => $row };
                @loanRecs = ();
                $row++;
            }    
            
            $count++;                 
            %grpval = %$loan;
            if ( $count >= $pageoffset ) { $inPage = 1; }
            if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; };
        }

        next if ( $inPage != 1 );
        
# Format each row of data
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

        $loan->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
        push @loanRecs, {
            firstname       => $loan->{'firstname'},
            lastname        => $loan->{'lastname'},
            username        => $loan->{'username'},
            teacher         => $loan->{'teacher'}, 
            homeroom        => $loan->{'homeroom'},
            buildingcode    => $loan->{'buildingcode'}, 
            userbarcode     => $loan->{'userbarcode'},
            title           => $loan->{'title'},
            dewey           => $loan->{'dewey'}, 
            barcode         => $loan->{'barcode'},
            price           => $loan->{'price'},
            dateDue         => $loan->{'dateDue'},
            dateLoan        => $loan->{'dateLoan'},
            deltaDueDate    => $loan->{'deltaDueDate'},
            uid        => $loan->{'uid'},
        };
        if ( $count >= $pageoffset + $pagesize ) { $inPage = 2; @loanRecs = (); };
    }

    $query->finish;

# Store the last group    
    $count++; $row++;
    if ( @loanRecs > 0 && $inPage == 1 )
    {
        my @data = ();
        SecondSort(\@data, \@loanRecs, $sort2);

        push @DataRecs, { group => \@data, grade => $grpval{'grade'}, count => $row };
    }

    $template->param(ItemsInLoan => \@DataRecs);
    MakePages($template, $count, $pNum, $pagesize);


}

#----------------------------------------------------------
sub SendOverdueBorrower
{
    my ($dbh, $template, $rec, $optNotice) = @_;
 
# Make title and date
    my $msg  = "<html>\n<head>\n<title>" . $template->param('libname') . "</title>\n</head>\n";
    $msg .= "<center><b>" . $template->param('libname') . "<br />\n";
    $msg .= "Overdue Items Reminder</b></center><br /><br />\n";

    $msg .= "Date printed: " . $template->param('today') . "<br>";
    $msg .= "<b>" . $rec->{'lastname'} . ", " . $rec->{'firstname'} . "</b>&nbsp;&nbsp;&nbsp;&nbsp;";

    if ( $optNotice == 1 )
        { $msg .= "Homeroom: " . $rec->{'homeroom'} . "&nbsp;&nbsp;&nbsp;&nbsp;"; }
    elsif ( $optNotice == 2 )
        { $msg .= "Teacher: " . $rec->{'teacher'} . "&nbsp;&nbsp;&nbsp;&nbsp;"; }
    elsif ( $optNotice == 3 )
        { $msg .= "Grade: " . $rec->{'grade'} . "&nbsp;&nbsp;&nbsp;&nbsp;"; }
    else
        { $msg .= "&nbsp;&nbsp;&nbsp;&nbsp;"; }

    if ( $rec->{'email'} )
        { $msg .= "Email: " . $rec->{'email'} . "<br /><br />\n"; }
    else
        { $msg .= "<br /><br />\n"; }

    $msg .= "The following items that you borrowed from the library / media center are now past due:<br /><br />\n";

    $msg .= "<table border='0' cellpadding='0' cellspacing='0' style='width:680' align='center'>";
    my $loanRecs = $rec->{'group'};
    foreach my $rec (@$loanRecs)
    {
        $msg .= "<tr>\n";
        $msg .= "<td>" . $rec->{'title'} . "</td>\n";
       # $msg .= "<td>" . $rec->{'dewey'} . "</td>\n";
        $msg .= "<td>" . $rec->{'dateLoan'} . "</td>\n";
        $msg .= "<td>" . $rec->{'barcode'} . "</td>\n";
        $msg .= "<td>" . $rec->{'price'} . "</td>\n";
        $msg .= "<td>" . $rec->{'dateDue'} . "</td>\n";
        $msg .= "</tr>\n";
    }

    $msg .= "</table>\n";
    $msg .= "<br /><br />\n";

    $msg .= "Please return these items as soon as possible. Thank you in advance for your cooperation\n";
    $msg .= "</body></html>";

#    open ttt, ">/tmp/odmail.htm";
#    print ttt $msg;
#    close ttt;
}

#----------------------------------------------------------
sub SendOverdueTeacher
{
    my ($dbh, $template, $teachername, $dataRecs) = @_;

# Make title and date
    my $msg  = "<html>\n<head>\n<title>" . $template->param('libname') . "</title>\n</head>\n";
    $msg .= "<center><b>" . $template->param('libname') . "<br />\n";
    $msg .= "Overdue Items Reminder</b></center><br /><br />\n";

    $msg .= "Date printed: " . $template->param('today') . "<br>";
    $msg .= "<b>" . $teachername . "</b>\n";

    $msg .= "The following items that your students borrowed from the library / media center are now past due:<br /><br />\n";

    foreach my $data (@$dataRecs)
    {
        if ( $data->{'teacher'} ne $teachername )
        {
            next;
        }
        
        $msg .= "<table border='0' cellpadding='0' cellspacing='0' style='width:680' align='center'>";
        my $loanRecs = $data->{'group'};
        $msg .= "<tr><th colspan='4'>Homeroom: " . $data->{'homeroom'} . "&nbsp;&nbsp;&nbsp;&nbsp\n";
        $msg .= "Building :" . $data->{'building'} . "</td></tr>\n";
        foreach my $rec (@$loanRecs)
        {
            $msg .= "<tr>\n";
            $msg .= "<td>" . $rec->{'title'} . "</td>\n";
            #$msg .= "<td>" . $rec->{'dewey'} . "</td>\n";
            $msg .= "<td>" . $rec->{'dateLoan'} . "</td>\n";
            $msg .= "<td>" . $rec->{'barcode'} . "</td>\n";
            $msg .= "<td>" . $rec->{'price'} . "</td>\n";
            $msg .= "<td>" . $rec->{'dateDue'} . "</td>\n";
            $msg .= "</tr>\n";
        }
        $msg .= "</table><br /><br />\n";
    }

    $msg .= "<br /><br />\n";

    $msg .= "Please recommend students about these items. Thank you in advance for your cooperation\n";
    $msg .= "</body></html>";

#    open ttt, ">/tmp/odmail.htm";
#    print ttt $msg;
#    close ttt;

}

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