#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Encode;
use PDF::Create;
use POSIX qw(
    floor
);

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_preference
);
use constant    X_PAGE        =>  612;
use constant    X_LEFT_MARGIN =>  25;
use constant    Y_PAGE        =>  792;
use constant    X_TABLE       =>  558;
use constant    ROW_HEIGHT    =>   18;

use Opals::Mail qw(
    mail_send
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $pref = tmpl_preference($dbh);
my $libname = decode('utf8', $pref->{'libname'});
my $dateToday = date_f005();
my $todayStr;

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

my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/odemail.tmpl',
            reqPermission   => 'notice',
        }
    );

my $sort1    = $input->{'sort1'};
my $sort2    = $input->{'sort2'};
my $optTitle = $input->{'optTitle'};
my $optPage  = $input->{'optPage'};
my $msg  = decode('utf8',$input->{'msg'});
#my $msg  = $input->{'msg'};


my $to      = $cgi->param('email');
my $subject = $cgi->param('subject');
my $pref    = tmpl_preference($dbh);
my $from    = $pref->{'emailAddress'};
my $smtp    = $pref->{'emailSmtp'};
my $pqf;
my $contStr = "";

if(!$to){
   $template->param(
        openForm =>1,
        email     =>$to
   );
}
else{
    if ( $sort1 eq '' )
    {
        $sort1 = "homeroom";
    }
    if ( !$sort2 )
    {
        if ( $sort1 eq "username" ) { $sort2 = "title"; }
        else { $sort2 = "username"; }
    }

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

        
        $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
        $todayStr = date_text($dateToday,1 );

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

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

       if ( $input->{'list'} ){
           $contStr = getContEmail($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
       }
         


        $to =~ s/ +/,/g;
        $to =~ s/;+/,/g;
        $to =~ s/,+/,/g;
        $to =~ s/(^,|,$)//g;
        $subject='Library Overdue Notice' if(!$subject || $subject eq '');
        if (mail_send($pref, $to,$subject , $contStr)){
           $template->param(done=>1);
        }
        else{
           $template->param(error=>1);
        }
        $template->param(email=>$to );

}



if ( $input->{'list'} ) {
    tmpl_write($dbh, $cgi, $cookie, $template);
}

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

   if ( $sort1 eq 'username' ){
       $retStr = GetOverdueUserList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
   }
   elsif ( $sort1 eq 'teacher' ){
         $retStr = GetOverdueTeacherList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
   }
   elsif ( $sort1 eq 'homeroom' ){
       $retStr = GetOverdueHomeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
   }
   elsif ( $sort1 eq 'grade' ){
       $retStr = GetOverdueGradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
   }           
   
   return $retStr;    
}
#--------------------------------------------------------
sub createContentOD{
    my ($sort1,$items)=@_;
    my $retval="";
    my $tmpStr="";
    
    $retval .= "<p align='right'>$todayStr</p>";
    $retval .= "<center><h2>$libname<br />Overdue Items Reminder</h2></center><br />";

    foreach my $rec (@$items){           
        $retval .= "<table  style='width:890px; background-color: #b0b0b0; cellspacing:1; cellpadding:1' cols=10 >";
        $retval .= "<tr style='background:#E4C4E3; border:1px'>";

        if ( $sort1 eq 'username' ){
            if ($rec->{'lastname'}){
                if ($rec->{'firstname'}){
                    $tmpStr = $rec->{'lastname'} . "," . $rec->{'firstname'} ;
                }
                else{
                    $tmpStr = $rec->{'lastname'};
                }
            }
            else{
                if ($rec->{'firstname'}){
                    $tmpStr = $rec->{'firstname'};
                }
                else{
                    $tmpStr = "none";
                }                    
            }
            if ($rec->{'username'}){
               $tmpStr .= "($rec->{'username'})";
            }
            
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>User:</b>$tmpStr" . "&nbsp;&nbsp;&nbsp;&nbsp;";
            $retval .= "<b>Grade:</b>$rec->{'grade'} <br> ";
            
            $tmpStr = $rec->{'homeroom'}? $rec->{'homeroom'} : "none";
            $retval .= "<b>Homeroom:</b>  $tmpStr";
            $tmpStr = $rec->{'building'}? $rec->{'building'} : "none";
            $retval .= "<b>Building:</b>  $tmpStr";
            $tmpStr = $rec->{'teacher'}? $rec->{'teacher'} : "none";
            $retval .= "<b>Teacher:</b>   $tmpStr </td>";
        }
        elsif ( $sort1 eq 'teacher' ){
            $retval .= "<td colspan='12' style='padding:0px'>";
            $tmpStr = $rec->{'teacher'}? $rec->{'teacher'} : "none";
            $retval .= "<b>Teacher:</b>  $tmpStr " ;
            $tmpStr = $rec->{'hmroom'}? $rec->{'hmroom'} : "none";
            $retval .= "<b>Homeroom:</b> $tmpStr ";
            $tmpStr = $rec->{'building'}? $rec->{'building'} : "none";
            $retval .= "<b>Building:</b> $tmpStr </td>";                      
        }
        elsif ( $sort1 eq 'homeroom' ){   
            $retval .= "<td colspan='12' style='padding:0px'>";
            $tmpStr = $rec->{'hmroom'}? $rec->{'hmroom'} : "none";
            $retval .= "<b>Homeroom:</b> $tmpStr ";
            $tmpStr = $rec->{'building'}? $rec->{'building'} : "none";
            $retval .= "<b>Building:</b> $tmpStr " ;
            $tmpStr = $rec->{'teacher'}? $rec->{'teacher'} : "none";
            $retval .= "<b>Teacher:</b>  $tmpStr </td>" ;
            
        }
        elsif ( $sort1 eq 'grade' ){
            $tmpStr = $rec->{'grade'}? $rec->{'grade'} : "none";
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Grade:</b> " . $tmpStr . "</td>" ;            
        }
        
        $retval .= "</tr>\n";
        $retval .= formatDisplay($sort1,$rec);
                     
        
        $retval .= "</table><br>";
     }#end For Loop Item

    #$retval .= $tmpStr;

    
    return $retval; 
}
#--------------------------------------------------------
sub formatDisplay{
   my ($sort1,$rec)=@_;
   my $tmpStr="";
   my $retval="";
   
   if ( $sort1 eq 'username' ){
        $retval .= "<tr style='background:#CCC4CB; '>";       
        $retval .= "<th colspan='2'><b>Title</b></th>";
        $retval .= "<th colspan='2'><b>BarCode</b></th>";
        $retval .= "<th><b>Price</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Dewey #</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Loan Date</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Due Date</b></th>";
        $retval .= "<th width=6%><b>Overdue</b></th>";
        $retval .= "</tr>\n";       
        
        foreach my $group (@{$rec->{'group'}}){
            $retval .= "<tr style='background:#FFFFFF'><td colspan='2'>";
            $retval .= $group->{'title'} . " </td>";
            $retval .= "<td colspan='2'> $group->{'barcode'} </td>";
            $tmpStr = $group->{'price'}? $group->{'price'} : "n/a";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dewey'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dateLoan'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dateDue'} </td>";
            $retval .= "<td> $group->{'deltaDueDate'} </td>";
            $retval .= "</tr>\n";
        } #end For  Loop Group 
   }
   elsif (($sort1 eq 'homeroom') || ($sort1 eq 'teacher')){
        $retval .= "<tr background='#CCC4CB'>";
        $retval .= "<th><b>User</b></th>";
        $retval .= "<th><b>Grade</b></th>";
        $retval .= "<th><b>Title</b></th>";
        $retval .= "<th><b>BarCode </b></th>";
        $retval .= "<th><b>Price</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Dewey #</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Loan Date</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Due Date</b></th>";
        $retval .= "<th><b>Overdue</b></th>";
        $retval .= "</tr>\n";       

        foreach my $group (@{$rec->{'group'}}){
            $retval .= "<tr style='background:#FFFFFF'><td>";
            if ($group->{'lastname'}){
                if ($group->{'firstname'}){
                    $tmpStr = $group->{'lastname'} . "," . $group->{'firstname'} ;
                }
                else{
                    $tmpStr = $group->{'lastname'};
                }
            }
            else{
                if ($group->{'firstname'}){
                    $tmpStr = $group->{'firstname'};
                }
                else{
                    $tmpStr = "none";
                }                    
            }
            if ($group->{'username'}){
               $tmpStr .= "($group->{'username'})";
            }

            $retval .= $tmpStr . "</td>";
            $tmpStr  = $group->{'grade'}? $group->{'grade'} : "none";
            $retval .= "<td> $tmpStr </td>";
            $retval .= "<td> $group->{'title'} </td>";
            $retval .= "<td> $group->{'barcode'} </td>";
            $tmpStr = $group->{'price'}? $group->{'price'} : "n/a";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dewey'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dateLoan'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dateDue'} </td>";
            $retval .= "<td> $group->{'deltaDueDate'} </td>";
            $retval .= "</tr>\n";
        } #end For  Loop Group 
   }
   elsif ( $sort1 eq 'grade' ){
        $retval .= "<tr style='background:#CCC4CB;'>";
        $retval .= "<th><b>User</b></th>";
        $retval .= "<th><b>Homeroom</b></th>";
        $retval .= "<th><b>Building</b></th>";
        $retval .= "<th><b>Teacher</b></th>";
        $retval .= "<th><b>Title</b></th>";
        $retval .= "<th><b>BarCode</b></th>";
        $retval .= "<th><b>Price</b></th>";
        $retval .= "<th nowrap><b>Dewey #</b></th>";
        $retval .= "<th nowrap><b>Loan Date</b></th>";
        $retval .= "<th nowrap><b>Due Date</b></th>";
        $retval .= "<th><b>OD</b></th>";
        $retval .= "</tr>\n";       

        foreach my $group (@{$rec->{'group'}}){
            $retval .= "<tr style='background:#FFFFFF'><td>";
            if ($group->{'lastname'}){
                if ($group->{'firstname'}){
                    $tmpStr = $group->{'lastname'} . "," . $group->{'firstname'} ;
                }
                else{
                    $tmpStr = $group->{'lastname'};
                }
            }
            else{
                if ($group->{'firstname'}){
                    $tmpStr = $group->{'firstname'};
                }
                else{
                    $tmpStr = "none";
                }                    
            }
            if ($group->{'username'}){
               $tmpStr .= "($group->{'username'})";
            }
            $retval .= $tmpStr . "</td>";
            $retval .= "<td> $group->{'homeroom'} </td>";
            $retval .= "<td> $group->{'buildingcode'} </td>";
            $retval .= "<td> $group->{'teacher'} </td>";
            $retval .= "<td> $group->{'title'} </td>";
            $retval .= "<td> $group->{'barcode'} </td>";
            $tmpStr = $group->{'price'}? $group->{'price'} : "n/a";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap> $group->{'dewey'} </td>";
            $retval .= "<td nowrap> $group->{'dateLoan'} </td>";
            $retval .= "<td nowrap> $group->{'dateDue'} </td>";
            $retval .= "<td> $group->{'deltaDueDate'} </td>";
            $retval .= "</tr>\n";       
        }#end For  Loop Group
   }


   return $retval; 
}
#--------------------------------------------------------
sub GetOverdueTeacherList
{    
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my $odStr = "";
    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=?";

    
    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);

    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(trim($roomlist[$i]), trim($bdinglist[$i]), trim($teacherlist[$i]));
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# 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;
            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'},
                dateLoan        => $loan->{'dateLoan'},
                dateDue         => $loan->{'dateDue'},
                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);

            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'});
    $odStr = createContentOD($sort1,\@DataRecs);
    
    return $odStr;
}
#--------------------------------------------------------
sub GetOverdueHomeroomList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my $odStr = "";
    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=?";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);

    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(trim($roomlist[$i]),trim($bdinglist[$i]), trim($teacherlist[$i]));
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# 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;
            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);
            push @DataRecs, { group => \@data, teacher => trim($teacherlist[$i]), hmroom => trim($roomlist[$i]), 
                            building => trim($bdinglist[$i]) };
        }
        @loanRecs = ();
    }
    
    $odStr = createContentOD($sort1,\@DataRecs);
    return $odStr;
    #$template->param(ItemsInLoan => \@DataRecs);
}
            
#--------------------------------------------------------
sub GetOverdueGradeList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my $odStr = ""; 
    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=?";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@gradelist);
    
    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->{'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'},
                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);
            push @DataRecs, { group => \@data, grade => $gradelist[$i] };
        }
        @loanRecs = ();
    }

    
    $template->param(ItemsInLoan => \@DataRecs);
    $odStr = createContentOD($sort1,\@DataRecs);
    return $odStr;
}


#-------------------------------------------------------------------------
sub GetOverdueUserList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my $odStr = "";
    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(dateDue) >= to_days('$dateFrom')   
        && to_days(dateDue) <= to_days('$dateTo')
        && to_days(dateDue) < to_days(now())";

    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 = ();
    }

    $odStr = createContentOD($sort1,\@DataRecs);
    $template->param(ItemsInLoan => \@DataRecs);
    
    return $odStr;
}


#----------------------------------------------------------
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; 
    }
    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 trim{
    my $str = shift;
    $str =~ s/^\s+//;
    $str =~ s/\s+$//;
    return $str;

}