#!/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
    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 $sort1 = $input->{'sort1'};
my $sort2 = $input->{'sort2'};
if ( $sort1 eq '' )
{
       $sort1 = "homeroom";
}
if ( !$sort2 )
{
    if ( $sort1 eq "username" ) { $sort2 = "title"; }
    else { $sort2 = "username"; }
}
     my $incExcOpt = $input->{'incExcOpt'};
 if(!$incExcOpt || $incExcOpt ne 'inclusion'){
     $incExcOpt='exclusion';
 }
 
my $pagesize = 1000000;
if ( $sort1 eq 'username' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemonloan_user.tmpl',
            reqPermission   => 'notice',
        }
    );
     $template->param(username => 1);
    #$pagesize = 15;
}
elsif ( $sort1 eq 'teacher' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemonloan_teacher.tmpl',
            reqPermission   => 'notice',
        }
    ); 
     $template->param(teacher => 1);
    #$pagesize = 2;
}
elsif ( $sort1 eq 'homeroom' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemonloan_homeroom.tmpl',
            reqPermission   => 'notice',
        }
    );
     $template->param(homeroom => 1);
    #$pagesize = 2;
}
elsif ( $sort1 eq 'grade' )
{
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/itemonloan_grade.tmpl',
            reqPermission   => 'notice',
        }
    );
     $template->param(grade => 1);
    #$pagesize = 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 $dateRange= $input->{'dateRangeOpt'};
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $dateToday;
    }
 
    my $byPass = (!$dateFrom && !$dateTo) ? 1 : 0;

    $dateFrom = $dateToday 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        => 'report/itemonloan_user.tmpl',
                reqPermission   => 'notice',
            }
        );
        $template->param(username => 1);
        GetOnLoan_User($dbh, $template, $sort1, $sort2, $input->{'idlist'}, $dateFrom, $dateTo, 
                            $input->{'orderby'}, $pNum, $pagesize,$incExcOpt);
    }
    elsif ( $sort1 eq 'teacher' ){
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'report/itemonloan_teacher.tmpl',
                reqPermission   => 'notice',
            }
        ); 
        $template->param(teacher => 1);
        GetOnLoan_Teacher($dbh, $template, $sort1, $sort2, $input->{'idlist'}, $dateFrom, $dateTo, 
                           $pNum, $pagesize,$incExcOpt);
    }
    elsif ( $sort1 eq 'homeroom' )    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'report/itemonloan_homeroom.tmpl',
                reqPermission   => 'notice',
            }
        );
        $template->param(homeroom => 1);
        GetOnLoan_Homeroom($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize,$incExcOpt);
    }
    elsif ( $sort1 eq 'grade' )    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'report/itemonloan_grade.tmpl',
                reqPermission   => 'notice',
            }
        );
        $template->param(grade => 1);
        GetOnLoan_Grade($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize,$incExcOpt);
    }
    elsif ( $sort1 eq 'none' )    {
        ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'report/itemonloan_none.tmpl',
                reqPermission   => 'notice',
            }
        );
        $template->param(noneGrp => 1);
        GetOnLoan_noneGrp($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize,$incExcOpt);
    }


########################################
# Get the total 
    my $szSql = "select count(*) from opl_loan l inner join opl_item i on l.barcode =i.barcode 
                        inner join opl_marcRecord m on m.rid =i.rid                        
                        where dateReturn is null  && 
                              to_days(dateLoan) >= to_days('$dateFrom') && 
                              to_days(dateLoan) <= to_days('$dateTo') ";
    if($incExcOpt eq 'exclusion'){
       $szSql .= " && (m.tempIll is NULL ||  m.tempIll = '') ";
    }                                                     
    my $queryTotal = $dbh->prepare($szSql);
    $queryTotal->execute();
    my ($totalOdHoldings) = $queryTotal->fetchrow_array;
    $queryTotal->finish();
    $totalOdHoldings = 0 if ( !$totalOdHoldings );
    $template->param(totalOdHoldings => $totalOdHoldings);

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(idloanList  => $input->{'idloanList'});
    $template->param(debugStr   =>$sort1,
                     incExcOpt  => $incExcOpt);

    
if($dateRange eq 'all'){
    $template->param(
        rangeAll   => 1,
        dateRangeOpt=>"all"
   );
}
elsif($dateRange eq 'fYear'){
    $template->param(
        from    => substr($dateFrom, 0, 10),
        to      => substr($dateTo, 0, 10),
        rangeFYear   => 1,
        dateRangeOpt=>"fYear"
   );
}
else{
    $template->param(
        from    => substr($dateFrom, 0, 10),
        to      => substr($dateTo, 0, 10),
        rangeSel   => 1,
        dateRangeOpt=>"rangeSel"
   );

}

$template->param( incExcOpt=> $incExcOpt);

if($incExcOpt eq 'exclusion'){
    $template->param(exclusion =>1);
}
elsif($incExcOpt eq 'inclusion'){
    $template->param(inclusion =>1);
}

    my $itemOnLoanMsgMap =loc_getMsgFile('circ/itemOnLoan.msg');
    loc_write($template,$itemOnLoanMsgMap);


    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 GetOnLoan_User
{
    my ($dbh, $template, $sort1, $sort2, $idlist, $dateFrom, $dateTo, $orderby, $pNum, $pagesize,$incExcOpt) = @_;
    
    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,
        opl_user.uid, firstname, lastname, 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_user, 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
        && 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 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;
                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, uid => $grpval{'uid'}, username => $grpval{'username'},
                                 lastname => $grpval{'lastname'}, firstname => $grpval{'firstname'}, 
                                 userbarcode => $grpval{'userbarcode'},
                                 homeroom => $grpval{'homeroom'}, building => $grpval{'buildingcode'},
                                 teacher => $grpval{'teacher'}, 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
        
        if (!$bZebraServerDown) {
            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if( $incExcOpt eq 'inclusion' && 
                ($loan->{'tempIll'} eq 'temporary' || $loan->{'tempIll'} eq 'ILL')){
                  $loan->{'tempIll'} = 1;
            }

            push @loanRecs, {
                title      => $loan->{'title'},
                dewey      => $loan->{'dewey'},
                barcode    => $loan->{'barcode'},
                dateDue    => $loan->{'dateDue'},
                dateLoan   => $loan->{'dateLoan'},
                overDue    => $loan->{'overDue'},
                price      => $loan->{'price'},
                tempIll    => $loan->{'tempIll'},            
            };
        }
        else {               
            push @loanRecs, {
                title      => '',
                dewey      => '',
                barcode    => $loan->{'barcode'},
                dateDue    => $loan->{'dateDue'},
                dateLoan   => $loan->{'dateLoan'},
                overDue    => $loan->{'overDue'},
                price      => $loan->{'price'},
                tempIll    => $loan->{'tempIll'},            
            };
        }
        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;
                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, uid => $grpval{'uid'}, username => $grpval{'username'},
                        lastname => $grpval{'lastname'}, firstname => $grpval{'firstname'},
                        userbarcode => $grpval{'userbarcode'},
                        homeroom => $grpval{'homeroom'}, building => $grpval{'buildingcode'},
                        teacher => $grpval{'teacher'}, grade => $grpval{'grade'}, count => $row };
    }

    $query->finish;
    if ( $orderby eq 'room' ){
        @DataRecs = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @DataRecs;
    }
  
    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 GetOnLoan_Teacher
{
    my ($dbh, $template, $sort1, $sort2, $idlist, $dateFrom, $dateTo, $pNum, $pagesize,$incExcOpt) = @_;

    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,
        now()>dateDue as overDue , m.tempIll 
        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
        && 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 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);
        
                foreach my $rec(@data){
                    $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                    $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
                }
                        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->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
        if( $incExcOpt eq 'inclusion' && 
            ($loan->{'tempIll'} eq 'temporary' || $loan->{'tempIll'} eq 'ILL')){
                 $loan->{'tempIll'} = 1;
        }

        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'},
            overDue      => $loan->{'overDue'},
            uid          => $loan->{'uid'},
            tempIll      => $loan->{'tempIll'},            
        };
        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);

                foreach my $rec(@data){
                    $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                    $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
                }
        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 GetOnLoan_Homeroom
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize,$incExcOpt) = @_;

    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, 
        now()>dateDue as overDue , m.tempIll 
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
        && 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 concat(homeroom,'zzzzzz'), concat(buildingcode,'zzzzzz'), concat(teacher,'zzzzzz')" ;
 #  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';
        }
        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);
                foreach my $rec(@data){
                    $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                    $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
                }
                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

#        if ( !$bZebraServerDown ) {
            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if( $incExcOpt eq 'inclusion' && 
                ($loan->{'tempIll'} eq 'temporary' || $loan->{'tempIll'} eq 'ILL')){
                  $loan->{'tempIll'} = 1;
            }

            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'},
                overDue      => $loan->{'overDue'},
                uid          => $loan->{'uid'},     
                tempIll      => $loan->{'tempIll'},            
            };
              }

    $query->finish;

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

                foreach my $rec(@data){
                    $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                    $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
                }
        #$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 GetOnLoan_Grade
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $pNum, $pagesize,$incExcOpt) = @_;

    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, 
        now()>dateDue as overDue , m.tempIll 
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
        && 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 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);
                foreach my $rec(@data){
                    $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                    $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
                }
                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->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
            if( $incExcOpt eq 'inclusion' && 
                ($loan->{'tempIll'} eq 'temporary' || $loan->{'tempIll'} eq 'ILL')){
                  $loan->{'tempIll'} = 1;
            }
        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'},
            overDue         => $loan->{'overDue'},
            uid             => $loan->{'uid'},
            tempIll         => $loan->{'tempIll'},            
        };
        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);
        foreach my $rec(@data){
            $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
            $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
        }
        push @DataRecs, { group => \@data, grade => $grpval{'grade'}, count => $row };
    }

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

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

    my $pageoffset = $pNum;
    if ( !$pageoffset )
        { $pageoffset = 0; }
    else
        { $pageoffset = ($pNum - 1)*$pagesize; }
    if(!$sort2 || $sort2 eq ''){
        $sort2 = 'dewey';
    }
    my $szSQL = "
select  idloan,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, 
        now()>dateDue as overDue , m.tempIll 
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
        && 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 $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() )
    {
        $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;  
    }

    $query->finish;
        
    $count = defined @DataRecs? scalar(@DataRecs):-1;
    $template->param(count => $count);
    $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->{'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->{'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 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"} } @$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} || $a->{"lastname"} cmp $b->{"lastname"} 
                        || $a->{"firstname"} cmp $b->{"firstname"} } @$loanRecs; 
    }
}

__END_OF_FILE: