#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;
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::Tb_Record qw(
    
    tb_item_findByBarcode

);

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 $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $sort1       = $input->{'sort1'} || 'username';
my $sort2       = $input->{'sort2'} || 'title';
my $groupBy     = $input->{'groupBy'} || 'username';
my $orderBy     = $input->{'orderBy'};
my $to          = $input->{'email'};
my $subject     = $input->{'subject'};
my @idList = split /\$/, $input->{'idList'};
#----------------------------------------------------------------------------------------------------
my $defLang= Opals::Context->preference('lang');

my $pref    = tmpl_preference($dbh);
my $from    = $pref->{'emailAddress'};
my $smtp    = $pref->{'emailSmtp'};
my $pqf;
my $contStr = "";
        
    $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
    $todayStr = date_text($dateToday,1 );
    my $pNum = $input->{'pNum'};
    my $contStr = getContEmail();
    $to =~ s/ +/,/g;
    $to =~ s/;+/,/g;
    $to =~ s/,+/,/g;
    $to =~ s/(^,|,$)//g;
    $subject='Overdue Items List' if(!$subject || $subject eq '');
    my $ret = {} ;
    if (mail_send($pref, $to,$subject , $contStr)){
       $ret = { send => 1};
    }
    else{
       $ret = { send =>  0};
    }
    print "Content-type: text/plain\n\n";
    my $retJSON = to_json($ret,{pretty=>1});

    print  $retJSON; ;

#--------------------------------------------------------
sub getContEmail{
   
    my $retStr ="";
    if ($groupBy eq "username"){
        $retStr = getOverdue_user();
    }
    elsif ($groupBy eq "course"){
        $retStr = getOverdue_course();
    }
    elsif ($groupBy eq "grade"){
        $retStr = getOverdue_grade();
    }
    elsif ($groupBy eq "courseteacher"){
        $retStr = getOverdue_courseteacher();
    }
    elsif ($groupBy eq "homeroom"){
        $retStr = getOverdue_homeroom();
    }
    elsif ($groupBy eq "teacher"){
        $retStr = getOverdue_homeroom();
    }
    elsif ($groupBy eq "title"){
        $retStr = getOverdue_title();
    }
    return $retStr; 
}
#--------------------------------------------------------
sub createContentOD{
    my ($grpBy,$items)=@_;
    my $retval="";
    my $tmpStr="";
    
    $retval .= "<p align='right'>$todayStr</p>";
    $retval .= "<center><h2>$libname<br />Overdue textbook Reminder</h2></center><br />";

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

        if ( $grpBy 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;";
            $tmpStr = $rec->{'homeroom'}? $rec->{'homeroom'} : "N/A";
            $retval .= "<b>Grade:</b>$rec->{'grade'} ";
            $retval .= "<b>Homeroom:</b>  $tmpStr &nbsp;&nbsp;";
            $tmpStr = $rec->{'building'}? $rec->{'building'} : "N/A";
            $retval .= "<b>Building:</b>  $tmpStr  &nbsp;&nbsp;";
            $tmpStr = $rec->{'teacher'}? $rec->{'teacher'} : "N/A";
            $retval .= "<b>Teacher:</b>   $tmpStr </td>";
        }
        elsif ( $grpBy eq 'teacher' ){
            $retval .= "<td colspan='12' style='padding:0px'>";
            $tmpStr = $rec->{'teacher'}? $rec->{'teacher'} : "N/A";
            $retval .= "<b>Teacher:</b>  $tmpStr " ;
            $tmpStr = $rec->{'hmroom'}? $rec->{'homeroom'} : "N/A";
            $retval .= "<b>Homeroom:</b> $tmpStr ";
            $tmpStr = $rec->{'building'}? $rec->{'building'} : "N/A";
            $retval .= "<b>Building:</b> $tmpStr </td>";                      
        }
        elsif ( $grpBy eq 'homeroom' ){   
            $retval .= "<td colspan='12' style='padding:0px'>";
            $tmpStr = $rec->{'homeroom'}? $rec->{'homeroom'} : "N/A";
            $retval .= "<b>Homeroom:</b> $tmpStr ";
            $tmpStr = $rec->{'building'}? $rec->{'building'} : "N/A";
            $retval .= "<b>Building:</b> $tmpStr " ;
            $tmpStr = $rec->{'teacher'}? $rec->{'teacher'} : "N/A";
            $retval .= "<b>Teacher:</b>  $tmpStr </td>" ;
            
        }
        elsif ( $grpBy eq 'grade' ){
            $tmpStr = $rec->{'grade'}? $rec->{'grade'} : "N/A";
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Grade:</b> " . $tmpStr . "</td>" ;            
        }
        elsif ( $grpBy eq 'title'){
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Title:</b> " . $rec->{'title'} . "</td>" ;  
        }
        elsif ( $grpBy eq 'course'){
            $tmpStr = $rec->{'coursename'}? $rec->{'coursename'} : "N/A";
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Course:</b> " . $tmpStr . "</td>" ;  
        }
        elsif ( $grpBy eq 'courseteacher'){
            $tmpStr = $rec->{'c_teachername'}? $rec->{'c_teachername'} : "N/A";
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Course Teacher:</b> " . $tmpStr . "</td>" ;  
        }
        $retval .= "</tr>\n";
        $retval .= formatDisplay($grpBy,$rec);
        $retval .= "</table><br>";
     }#end For Loop Item
    return $retval; 
}
#--------------------------------------------------------
sub formatDisplay{
   my ($grpBy,$rec)=@_;
   my $tmpStr="";
   my $retval="";
   if ( $grpBy 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>Course</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Course Teacher</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 colspan='2'>";
            $retval .= $group->{'title'} . " </td>";
            $retval .= "<td colspan='2'> $group->{'barcode'} </td>";
            $tmpStr = $group->{'price'}? $group->{'price'} : "";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'coursename'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'c_teachername'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dateDue_text'} </td>";
            $retval .= "<td> $group->{'deltaDateDue'} </td>";
            $retval .= "</tr>\n";
        } #end For  Loop Group 
   }
   elsif (($grpBy eq 'homeroom') || ($grpBy 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>Course</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Course Teacher</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'} : "n/a";
            $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->{'coursename'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'c_teachername'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'dateDue_text'} </td>";
            $retval .= "<td> $group->{'deltaDateDue'} </td>";
            $retval .= "</tr>\n";
        } #end For  Loop Group 
   }
   elsif ( $grpBy eq 'grade' ){
        $retval .= "<tr style='background:#CCC4CB;'>";
        $retval .= "<th><b>User</b></th>";
        $retval .= "<th><b>Hroom</b></th>";
        $retval .= "<th><b>Building</b></th>";
        $retval .= "<th><b>Teacher</b></th>";
        $retval .= "<th colspan='2'><b>Title</b></th>";
        $retval .= "<th><b>BarCode</b></th>";
        $retval .= "<th><b>Price</b></th>";
        $retval .= "<th nowrap><b>Course</b></th>";
        $retval .= "<th nowrap ><b>Course Teacher</b></th>";
        $retval .= "<th nowrap><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>";
            $retval .= "<td> $group->{'homeroom'} </td>";
            $retval .= "<td> $group->{'buildingcode'} </td>";
            $retval .= "<td> $group->{'teacher'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'title'} </td>";
            $retval .= "<td> $group->{'barcode'} </td>";
            $tmpStr = $group->{'price'}? $group->{'price'} : "n/a";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap> $group->{'coursename'} </td>";
            $retval .= "<td nowrap> $group->{'c_teachername'} </td>";
            $retval .= "<td nowrap> $group->{'dateDue_text'} </td>";
            $retval .= "<td> $group->{'deltaDateDue'} </td>";
            $retval .= "</tr>\n";       
        }#end For  Loop Group
   }
   elsif ( $grpBy eq 'title') {
        $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>BarCode</b></th>";
        $retval .= "<th><b>Price</b></th>";
        $retval .= "<th nowrap><b>Course</b></th>";
        $retval .= "<th nowrap colspan='2'><b>Course Teacher</b></th>";
        $retval .= "<th nowrap><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>";
            $retval .= "<td> $group->{'homeroom'} </td>";
            $retval .= "<td> $group->{'buildingcode'} </td>";
            $retval .= "<td> $group->{'teacher'} </td>";
            $retval .= "<td> $group->{'barcode'} </td>";
            $tmpStr = $group->{'price'}? $group->{'price'} : "n/a";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap> $group->{'coursename'} </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'c_teachername'} </td>";
            $retval .= "<td nowrap> $group->{'dateDue_text'} </td>";
            $retval .= "<td> $group->{'deltaDateDue'} </td>";
            $retval .= "</tr>\n";       
        }#end For loop 
   }
   elsif ( $grpBy eq 'course') {
        $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 colspan='2'><b>Course Teacher</b></th>";
        $retval .= "<th nowrap><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>";
            $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'} : "";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'c_teachername'} </td>";
            $retval .= "<td nowrap> $group->{'dateDue_text'} </td>";
            $retval .= "<td> $group->{'deltaDateDue'} </td>";
            $retval .= "</tr>\n";       
        }#end for
   }
   elsif ( $grpBy eq 'courseteacher') {
        $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 colspan='2'><b>Course</b></th>";
        $retval .= "<th nowrap><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>";
            $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'} : "";
            $retval .= "<td>$tmpStr </td>";
            $retval .= "<td nowrap colspan='2'> $group->{'coursename'} </td>";
            $retval .= "<td nowrap> $group->{'dateDue_text'} </td>";
            $retval .= "<td> $group->{'deltaDateDue'} </td>";
            $retval .= "</tr>\n";       
        }#end for
   }


   return $retval; 
}

#----------------------------------------------------------
sub secondSort
{
    my ($data, $sort2) = @_;
    
    if ( $sort2 eq "username" )
    { 
        @$data = sort { $a->{"lastname"} cmp $b->{"lastname"} || $a->{"firstname"} cmp $b->{"firstname"} } @$data; 
    }
    elsif($sort2 eq "title"){
        @$data = sort { lc(trimArticle($a->{"title"})) cmp lc(trimArticle($b->{"title"})) } @$data; 
    }
    elsif($sort2 eq "courseteacher"){
        @$data = sort {($a->{"c_teachername"}) cmp ($b->{"c_teachername"}) } @$data; 
    }
    elsif($sort2 eq "course"){
        @$data = sort {($a->{"coursename"}) cmp ($b->{"coursename"}) } @$data; 
    }
    elsif($sort2 eq "dateLoan") {
        @$data = sort { (convertDatetoNumber($a->{"dateLoan"})  <=>convertDatetoNumber($b->{"dateLoan"}) ) } @$data;
    }
    elsif($sort2 eq "dueDate") {
        @$data = sort { (convertDatetoNumber($a->{"dateDue"})  <=>convertDatetoNumber($b->{"dateDue"}) ) } @$data;
    }
    elsif($sort2 eq "barcode") {
        @$data = sort { ($a->{"barcode"})  cmp ($b->{"barcode"}) } @$data;
    }
    elsif($sort2 eq "deltaDateDue") {
        @$data = sort { ($a->{"deltaDateDue"})  <=> ($b->{"deltaDateDue"}) } @$data;
    }
    else 
    { 
        @$data = sort { $a->{$sort2} cmp $b->{$sort2} || $a->{"lastname"} cmp $b->{"lastname"} 
                        || $a->{"firstname"} cmp $b->{"firstname"} } @$data; 
    }
}

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

sub convertDatetoNumber {
#date format 2009-05-04 18:02:15
    my ($d) =@_;
    $d =~/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/;
    my $dd = $1 . $2 . $3 . $4 . $5 . $6;
    return $dd;
}

#--------------------------------------------------------
sub getOverdue_user{
    my $tbOverdue;
    my $count = 0;
    my $totalOverdue = 0;
    my $odStr = "";
    my @idList = ();
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }

    my $sql = "select l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
        l.dateDue < now() as overdue, 
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername, 
        cl.coursename as coursename
        from tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join  
            (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
                inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null && 
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                l.uid = ? group by l.barcode";

    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        while (my $loan = $sth->fetchrow_hashref){
            my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
            $loan->{'title'}    = $itemInfo->{'title'};
            $loan->{'price'}    = $itemInfo->{'price'};
            $loan->{'barcode'}  = $itemInfo->{'barcode'};
            $loan->{'dateLoan_text'}    = date_text ($loan->{'dateLoan'},0,$defLang);
            $loan->{'dateDue_text'}     = date_text ($loan->{'dateDue'},0,$defLang );
            $totalOverdue += 1;
            if (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'building'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}   = $loan->{'teacher'};
                 $count++;
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    
    if ( $orderBy eq 'homeroom' ){
        @tbOverdueList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @tbOverdueList;
    }
    else{
        @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    }
    $odStr = createContentOD($groupBy,\@tbOverdueList);
    return $odStr;
}



sub getOverdue_teacher{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2, $input) = @_;
    my @teacherList = ();
    my @homeroomList = ();
    my @buildingList = ();
    @teacherList = split /\$/, $input->{'teacherList'};    
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'}; 

    my $tbOverdue;
    my $row = 0;
    my $totalOverdue = 0;
    my $odStr = "";
    if (scalar(@teacherList) == 0){
        push @teacherList, '';}
    else{
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
        foreach my $hr(@homeroomList){
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            if ($building eq " " || $building eq "None" || $building eq "N/A"){
                $building = "";
            }
        }
    }
    my $sql = "select u.*, l.*, 
        l.dateDue < now() as overdue, 
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename
        from  tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                u.teacher = ? && u.homeroom = ?  && u.buildingcode = ?
        order by  concat(teacher,'zzzzzz'),  concat(buildingcode, 'zzzzzz'), concat(homeroom,'zzzzzz') ";
    
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for ( my $i = 0; $i < $nList; $i++){
        $sth->execute($teacherList[$i],$homeroomList[$i],$buildingList[$i]);
        while (my $loan = $sth->fetchrow_hashref){
            my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
            $loan->{'title'} = $itemInfo->{'title'};
            $loan->{'price'} = $itemInfo->{'price'};
            $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            $totalOverdue += 1;
            if (!$loan->{'teacher'} || $loan->{'teacher'} eq "") {
                $loan->{'teacher'} = " N/A";;
            }
            if (!$loan->{'homeroom'} || $loan->{'homeroom'} eq ""){
                $loan->{'homeroom'} = " N/A";
            }
            if (!$loan->{'buildingcode'} || $loan->{'buildingcode'} eq ""){
                $loan->{'buildingcode'} = " N/A";
            }
            my $teacherHrBld = $loan->{'teacher'} . $loan->{'homeroom'} . $loan->{'buildingcode'};
            if (!$tbOverdue->{$teacherHrBld}){
                $tbOverdue->{$teacherHrBld}->{'teacher'}    = $loan->{'teacher'};
                $tbOverdue->{$teacherHrBld}->{'homeroom'} = $loan->{'homeroom'};
                $tbOverdue->{$teacherHrBld}->{'building'} = $loan->{'buildingcode'};
                $row++;
            }
            push @{$tbOverdue->{$teacherHrBld}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    $odStr = createContentOD($sort1,\@tbOverdueList);
    return $odStr;
}
#--------------------------------------------------------
sub getOverdue_homeroom{
    my $tbOverdue;
    my $count = 0;
    my $totalOverdue = 0;
    my $odStr = "";
    my @homeroomList = ();
    my @buildingList = ();
    my @teacherList = ();
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'}; 
    @teacherList = split /\$/, $input->{'teacherList'};  

    if (scalar(@homeroomList) == 0){
        push @homeroomList, '';}
    else{
        foreach my $hr(@homeroomList){
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A" || $hr eq " N/A"){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            if ($building eq " " || $building eq "None" || $building eq "N/A" || $building eq " N/A"){
                $building = "";
            }
        }
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A" || $teacher eq " N/A"){
                $teacher = "";
            }
            else{
                $teacher =~ s/\s+//g;
            }
        }
    }
    my $sql = "select l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                u.homeroom = ?  && 
                u.buildingcode = ? && 
                replace(u.teacher,' ','') = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $nList   = scalar(@homeroomList);
    for (my $i = 0; $i < $nList; $i++){
        if ($homeroomList[$i] ne "" && $buildingList[$i] ne "" &&  $teacherList[$i] ne "") {
            $sth->execute($homeroomList[$i],$buildingList[$i], $teacherList[$i]);
            while (my $loan = $sth->fetchrow_hashref){
                my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
                $loan->{'title'} = $itemInfo->{'title'};
                $loan->{'price'} = $itemInfo->{'price'};
                $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'},0,$defLang);
                $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'},0,$defLang);
                $count++;
                if (!$loan->{'homeroom'} || $loan->{'homeroom'} eq ""){
                    $loan->{'homeroom'} = " N/A";
                }
                if (!$loan->{'buildingcode'} || $loan->{'buildingcode'} eq ""){
                    $loan->{'buildingcode'} = " N/A";
                }
                if (!$loan->{'teacher'} || $loan->{'teacher'} eq ""){
                    $loan->{'teacher'} = " N/A";
                }
                my $hrBldTeacher = $loan->{'homeroom'} . $loan->{'buildingcode'} . $loan->{'teacher'};
                if (!$tbOverdue->{$hrBldTeacher}){
                     $tbOverdue->{$hrBldTeacher}->{'homeroom'} = $loan->{'homeroom'};
                     $tbOverdue->{$hrBldTeacher}->{'building'} = $loan->{'buildingcode'};
                     $tbOverdue->{$hrBldTeacher}->{'teacher'} = $loan->{'teacher'};
                }
                push @{$tbOverdue->{$hrBldTeacher}->{'group'}}, $loan;
            }
        }
        else{
            my $sqlNull = "select l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
                u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
                l.dateDue < now() as overdue,
                to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
                t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
                cl.coursename as coursename
                from    tb_loan as l inner join opl_user as u on l.uid = u.uid
                left join
                   (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
                    inner join tb_courseList as cl using(courseCode)) 
                on ts.id = l.teacherScheduleId
                where   dateReturn is null &&   
                        to_days(l.dateDue) < to_days(now()) &&
                        to_days(l.dateDue) >= to_days('$dateFrom') &&
                        to_days(l.dateDue) <= to_days('$dateTo') ";
            if ($homeroomList[$i] eq ""){
                $sqlNull .= " && (u.homeroom is null || u.homeroom = '' )";
            }
            else{
                $sqlNull .= " && u.homeroom = $homeroomList[$i]";
            }
            if ($buildingList[$i] eq ""){
                $sqlNull .= " && (u.buildingcode is null || u.buildingcode = 0)";
            }
            else{
                $sqlNull .= " && u.buildingcode = $buildingList[$i]";
            }
            if ($teacherList[$i] eq ""){
                $sqlNull .= " && (u.teacher is null || u.teacher = '')";
            }
            else{
                $sqlNull .= " && u.teacher = '$teacherList[$i]'";
            }
            $sth = $dbh->prepare($sqlNull);
            $sth->execute();
            while (my $loan = $sth->fetchrow_hashref){
                my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
                $loan->{'title'} = $itemInfo->{'title'};
                $loan->{'price'} = $itemInfo->{'price'};
                $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'},0,$defLang);
                $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'},0,$defLang);
                $count++;
                if (!$loan->{'homeroom'} || $loan->{'homeroom'} eq ""){
                    $loan->{'homeroom'} = " N/A";
                }
                if (!$loan->{'buildingcode'} || $loan->{'buildingcode'} eq ""){
                    $loan->{'buildingcode'} = " N/A";
                }
                if (!$loan->{'teacher'} || $loan->{'teacher'} eq ""){
                    $loan->{'teacher'} = " N/A";
                }
                my $hrBldTeacher = $loan->{'homeroom'} . $loan->{'buildingcode'} . $loan->{'teacher'};
                if (!$tbOverdue->{$hrBldTeacher}){
                     $tbOverdue->{$hrBldTeacher}->{'homeroom'} = $loan->{'homeroom'};
                     $tbOverdue->{$hrBldTeacher}->{'building'} = $loan->{'buildingcode'};
                     $tbOverdue->{$hrBldTeacher}->{'teacher'} = $loan->{'teacher'};
                }
                push @{$tbOverdue->{$hrBldTeacher}->{'group'}}, $loan;
            }
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    $odStr = createContentOD($groupBy,\@tbOverdueList);
    return $odStr;
}

sub getOverdue_course{
    my $tbOverdue;
    my $row = 0;
    my $totalOverdue = 0;
    my @courseCodeList=();
    my $odStr = "";
    @courseCodeList = split /\$/,  $input->{'courseCodeList'};
    
    if (scalar(@courseCodeList) == 0){
        push @courseCodeList, '';}
=item
    my $sql = "select l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, coalesce(cl.courseCode,'0') as courseCode
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&  
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                cl.courseCode = ?
        group by l.barcode " ;
=cut
     my $sql = "select distinct l.barcode as barcode, l.dateLoan as dateLoan,l.dateDue as dateDue, l.dateReturn as dateReturn,
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,u.fullname,u.email,u.yeargraduation,
        l.id as idloan,l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id,concat_ws(',',t.lastname,t.firstname) as c_teachername,cl.coursename as coursename, 
        coalesce(cl.courseCode,'0') as courseCode
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join opl_user as t on ts.teacherId=t.uid
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&  
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') && cl.courseCode = ? group by barcode ";
    
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseCodeList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        $courseCodeList[$i] = $courseCodeList[$i] eq "N/A" ? "":$courseCodeList[$i];
        if ( $courseCodeList[$i] ne "" && $courseCodeList[$i] ne "0"){
            $sth->execute($courseCodeList[$i]);
            while (my $loan = $sth->fetchrow_hashref){
                my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
                $loan->{'title'} = $itemInfo->{'title'};
                $loan->{'price'} = $itemInfo->{'price'};
                $loan->{'rid'} = $itemInfo->{'rid'};
                $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
                $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
                if (!$tbOverdue->{$loan->{'couresCode'}}){
                    $tbOverdue->{$loan->{'courseCode'}}->{'courseCode'} = $loan->{'courseCode'};
                    $tbOverdue->{$loan->{'courseCode'}}->{'coursename'} = $loan->{'coursename'};
                }
                push @{$tbOverdue->{$loan->{'courseCode'}}->{'group'}}, $loan;
            }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue,
            to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
            t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
            cl.coursename as coursename, cl.courseCode as courseCode
            from    tb_loan as l inner join opl_user as u on l.uid = u.uid
            left join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
            on ts.id = l.teacherScheduleId
            where   dateReturn is null &&   
                    to_days(l.dateDue) < to_days(now()) &&
                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                    to_days(l.dateDue) <= to_days('$dateTo') &&
                    cl.courseCode is null" ;
            $sth = $dbh->prepare($sql_null);
            $sth->execute();
        while (my $loan = $sth->fetchrow_hashref){
            my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
            $loan->{'title'} = $itemInfo->{'title'};
            $loan->{'price'} = $itemInfo->{'price'};
            $loan->{'rid'} = $itemInfo->{'rid'};
            $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            $loan->{'courseCode'} = " N/A";
            $loan->{'coursename'} = " N/A";
            if (!$tbOverdue->{$loan->{'couresCode'}}){
                $tbOverdue->{$loan->{'courseCode'}}->{'courseCode'} = $loan->{'courseCode'};
                $tbOverdue->{$loan->{'courseCode'}}->{'coursename'} = $loan->{'coursename'};
            }
            push @{$tbOverdue->{$loan->{'courseCode'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();

    foreach my $k(keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    $odStr = createContentOD($groupBy,\@tbOverdueList);
    return $odStr;

}
sub getOverdue_courseteacher {
    my @courseTeacherIdList=();
    my $tbOverdue;
    my $odStr = "";
    @courseTeacherIdList = split /\$/,  $input->{'courseteacherIdList'};
    if (scalar(@courseTeacherIdList) == 0){
        push @courseTeacherIdList, '';}
=item    
    my $sql = "select l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as c_teacherId , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, cl.courseCode as courseCode
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        inner join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                t.teacherId = ?" ;
=cut
   my $sql = "select l.barcode as barcode, l.dateLoan as dateLoan,l.dateDue as dateDue, l.dateReturn as dateReturn,
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode, u.fullname,u.email,u.yeargraduation,
        l.id as idloan,l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        coalesce(t.uid, '0') as c_teacherId , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, coalesce(cl.courseCode,'0') as courseCode
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join opl_user as t on ts.teacherId=t.uid
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
             to_days(l.dateDue) < to_days(now()) &&
             to_days(l.dateDue) >= to_days('$dateFrom') &&
             to_days(l.dateDue) <= to_days('$dateTo') && 
             t.uid = ? group by l.barcode";
             
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseTeacherIdList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        if ( $courseTeacherIdList[$i] ne "" && $courseTeacherIdList[$i] ne "0"){
            $sth->execute($courseTeacherIdList[$i]);
            while (my $loan = $sth->fetchrow_hashref){
                my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
                $loan->{'title'} = $itemInfo->{'title'};
                $loan->{'price'} = $itemInfo->{'price'};
                $loan->{'rid'} = $itemInfo->{'rid'};
                $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'},0,$defLang);
                $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0,$defLang);
                if (!$tbOverdue->{$loan->{'c_teacherId'}}){
                     $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teacherId'} = $loan->{'c_teacherId'};
                     $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teachername'} = $loan->{'c_teachername'};
                }
                push @{$tbOverdue->{$loan->{'c_teacherId'}}->{'group'}}, $loan;
            }
        }
        else{
            my $sqlNull = "select l.barcode as barcode, l.dateLoan as dateLoan,l.dateDue as dateDue, l.dateReturn as dateReturn,
            u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode, u.fullname,u.email,u.yeargraduation,
            l.id as idloan,l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
            coalesce(t.uid, '0') as c_teacherId , concat_ws(',',t.lastname,t.firstname) as c_teachername,
            cl.coursename as coursename, coalesce(cl.courseCode,'0') as courseCode
            from    tb_loan as l inner join opl_user as u on l.uid = u.uid
            left join
               (tb_teacherSchedule as ts inner join opl_user as t on ts.teacherId=t.uid
                inner join tb_courseList as cl using(courseCode)) 
            on ts.id = l.teacherScheduleId
            where   dateReturn is null &&   
                 to_days(l.dateDue) < to_days(now()) &&
                 to_days(l.dateDue) >= to_days('$dateFrom') &&
                 to_days(l.dateDue) <= to_days('$dateTo') && 
                 t.uid is null group by l.barcode";
            $sth = $dbh->prepare($sqlNull);
            $sth->execute();
            while (my $loan = $sth->fetchrow_hashref){
                my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
                $loan->{'title'} = $itemInfo->{'title'};
                $loan->{'price'} = $itemInfo->{'price'};
                $loan->{'rid'} = $itemInfo->{'rid'};
                $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'},0,$defLang);
                $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0,$defLang);
                $loan->{'c_teacherId'} = 0;
                $loan->{'c_teachername'} = "N/A";
                if (!$tbOverdue->{$loan->{'c_teacherId'}}){
                     $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teacherId'} = $loan->{'c_teacherId'};
                     $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teachername'} = $loan->{'c_teachername'};
                }
                push @{$tbOverdue->{$loan->{'c_teacherId'}}->{'group'}}, $loan;
            }
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    $odStr = createContentOD($groupBy,\@tbOverdueList);
    return $odStr;
}

sub getOverdue_grade{
    my @gradeList = ();
    my $tbOverdue;
    my $count = 0;
    my $odStr = "";
    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){
        push @gradeList, '';}
    else{
        foreach my $grade(@gradeList){
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }
    my $sql = "select l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                 u.grade = ?";
    my $sth = $dbh->prepare($sql);
    my $nList   = scalar(@gradeList);
    for (my $i = 0; $i < $nList; $i++){
        $sth->execute($gradeList[$i]);
        while (my $loan = $sth->fetchrow_hashref){
            my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
            $loan->{'title'} = $itemInfo->{'title'};
            $loan->{'price'} = $itemInfo->{'price'};
            $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0,$defLang);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0,$defLang);
            $count++;
            if (!$loan->{'grade'} || $loan->{'grade'} eq ""){
                $loan->{'grade'} = " N/A";
            }
            if (!$tbOverdue->{$loan->{'grade'}}){
                 $tbOverdue->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
            }
            push @{$tbOverdue->{$loan->{'grade'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    $odStr = createContentOD($groupBy,\@tbOverdueList);
    return $odStr;
}

sub getOverdue_title{
    my $tbOverdue;
    my @recIdList=();
    my $count = 0;
    my $totalOverdue = 0;
    my $odStr = "";
    @recIdList = split /\$/,  $input->{'recIdList'};
    if (scalar(@recIdList) == 0){
        push @recIdList, '';}
    my $sql = "select i.rid, l.barcode as barcode,l.dateLoan as dateLoan,l.dateDue as dateDue,l.dateReturn as dateReturn, 
        u.uid, u.teacher,u.grade,u.homeroom,u.buildingcode,u.lastname,u.firstname,u.userbarcode,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.teacherId as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, cl.courseId as course
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        inner join tb_items as i using (barcode)
        left join
           (tb_teacherSchedule as ts inner join tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') &&
                i.rid = ? " ;

    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@recIdList);
    for (my $i = 0; $i < $nList ; $i++){
        $sth->execute($recIdList[$i]);
        while (my $loan = $sth->fetchrow_hashref){
            my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
            $loan->{'title'} = $itemInfo->{'title'};
            $loan->{'price'} = $itemInfo->{'price'};
            $loan->{'rid'} = $itemInfo->{'rid'};
            $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'},0,$defLang);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'},0,$defLang);
            $loan->{'t_teachername'}    = $loan->{'t_teachername'};
            $count++;
            if (!$tbOverdue->{$loan->{'title'}}){
                $tbOverdue->{$loan->{'title'}}->{'title'} = $loan->{'title'};
                $tbOverdue->{$loan->{'title'}}->{'rid'} = $loan->{'rid'};
                $tbOverdue->{$loan->{'title'}}->{'price'} = $loan->{'price'};
            }
            push @{$tbOverdue->{$loan->{'title'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort { lc(trimArticle($a->{'title'})) cmp lc(trimArticle($b->{'title'})) } @tbOverdueList;
    $odStr = createContentOD($groupBy,\@tbOverdueList);
    return $odStr;
}

sub trimArticle{
    my ($str)=@_;
    $str =~ s/^a |^an |^the //i;
    return $str;
}