#!/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::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 ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'txtbk/ajax/util/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 $to      = $cgi->param('eAddress');
my $subject = $cgi->param('subject');
my $pref    = tmpl_preference($dbh);
my $from    = $pref->{'emailAddress'};
my $smtp    = $pref->{'emailSmtp'};
my $pqf;
my $contStr = "";

if ( $sort1 eq '' )
    {
        $sort1 = "homeroom";
    }
if (!$sort2){
    if ($sort1 eq "username"){
        $sort2 = "title";    }
    else{
        $sort2 = "username";
    }
}   
elsif($sort1 eq $sort2){
    if($sort1 eq "title"){
        $sort2 = "username";
    }
    else{
        $sort2 = "title";
    }
}

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 );

    $to =~ s/ +/,/g;
    $to =~ s/;+/,/g;
    $to =~ s/,+/,/g;
    $to =~ s/(^,|,$)//g;
    $subject='Overdue Items List' if(!$subject || $subject eq '');
    $contStr = getContEmail($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
    if (mail_send($pref, $to,$subject , $contStr)){
       $template->param(done=>1);
       $template->param(error=>0);
    }
    else{
       $template->param(error=>1);
        $template->param(done=>0);
    }

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

sub getContEmail{
   my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
   my $retStr = "";
    if ( $sort1 eq 'username'){
        my $orderBy = $input->{'orderBy'};
        $retStr = getOverdue_user($dbh, $dateFrom, $dateTo, $orderBy, $sort2, $input);
    }
    elsif ($sort1 eq 'teacher'){
        $retStr = getOverdue_teacher($dbh, $dateFrom, $dateTo, $sort2 , $input);
    }
    elsif ($sort1 eq 'homeroom'){
        $retStr = getOverdue_homeroom($dbh, $dateFrom, $dateTo, $sort2, $input );
    }
    elsif ($sort1 eq 'grade'){
        $retStr = getOverdue_grade($dbh, $dateFrom, $dateTo, $sort2 , $input);
    }
    elsif ($sort1 eq 'course'){
        $retStr = getOverdue_course($dbh, $dateFrom, $dateTo, $sort2 , $input);
    }
    elsif ($sort1 eq 'courseteacher'){
        $retStr = getOverdue_courseteacher($dbh, $dateFrom, $dateTo, $sort2 , $input);
    }
    elsif ($sort1 eq 'title'){
        $retStr = getOverdue_title($dbh, $dateFrom, $dateTo, $sort2 , $input);
    }

   return $retStr;    
}

sub createContentOD{
    my ($sort1,$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 ( $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'} : "N/A";
            $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 ( $sort1 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 ( $sort1 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 ( $sort1 eq 'grade' ){
            $tmpStr = $rec->{'grade'}? $rec->{'grade'} : "N/A";
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Grade:</b> " . $tmpStr . "</td>" ;            
        }
        elsif ($sort1 eq 'title'){
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Title:</b> " . $rec->{'title'} . "</td>" ;  
        }
        elsif ($sort1 eq 'course'){
            $tmpStr = $rec->{'coursename'}? $rec->{'coursename'} : "N/A";
            $retval .= "<td colspan='12' style='padding:0px'>";
            $retval .= "<b>Course:</b> " . $tmpStr . "</td>" ;  
        }
        elsif ($sort1 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($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>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 (($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>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 ( $sort1 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 ( $sort1 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 ( $sort1 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 ( $sort1 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 getOverdue_user{
    my ($dbh, $dateFrom, $dateTo, $orderBy, $sort2, $input) = @_;
    my $tbOverdue;
    my $row = 0;
    my $totalOverdue = 0;
    my $odStr = "";
    my @idList = ();
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; 
    }
    my $sql = "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
        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 = ?  ";
    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);
            $loan->{'dateDue_text'}     = date_text ($loan->{'dateDue'}, 0 );
            $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'}}->{'count'} = $row;
                 $row++;
            }
            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 'room' ){
        @tbOverdueList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @tbOverdueList;
    }
    else{
        @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    }

    $odStr = createContentOD($sort1,\@tbOverdueList);
    return $odStr;
}

sub getOverdue_teacher{
    my ($dbh, $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){
            $teacher =~ s/^\s+|\s+$//g;
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
        foreach my $hr(@homeroomList){
            $hr =~ s/^\s+|\s+$//g;
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            $building =~ s/^\s+|\s+$//g;
            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 ($dbh, $dateFrom, $dateTo, $sort2, $input) = @_;
    my $tbOverdue;
    my $row = 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 = "";
            }
        }
        foreach my $building(@buildingList){
            if ($building eq " " || $building eq "None" || $building eq "N/A"){
                $building = "";
            }
        }
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }
    my $sql = "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
        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 = ? && u.teacher = ?
        order by concat(homeroom, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(teacher,'zzzzzz') ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $nList   = scalar(@homeroomList);
    for (my $i = 0; $i < $nList; $i++){
        $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);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            $totalOverdue += 1;
            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($sort1,\@tbOverdueList);
    return $odStr;
}
sub getOverdue_course{
    my ($dbh, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOverdue;
    my $row = 0;
    my $totalOverdue = 0;
    my @courseCodeList=();
    my $odStr = "";
    @courseCodeList = split /\$/,  $input->{'courseCodeList'};
    if (scalar(@courseCodeList) == 0){
        push @courseCodeList, '';}

    my $sql = "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, 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 = ?
        order by (coursename)";
        
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseCodeList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $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($sort1,\@tbOverdueList);
    return $odStr;

}
sub getOverdue_courseteacher {
    my ($dbh, $dateFrom, $dateTo,$sort2,$input) = @_;
    my @courseTeacherIdList=();
    my $tbOverdue;
    my $odStr = "";
    @courseTeacherIdList = split /\$/,  $input->{'courseTeacherIdList'};
    if (scalar(@courseTeacherIdList) == 0){
        push @courseTeacherIdList, '';}
    my $sql = "select l.*, u.*, 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
        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') &&
                t.teacherId = ?" ;
    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);
                $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
                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{
            $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 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
            left join
           (tb_teacherSchedule as ts inner join opl_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 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);
            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($sort1,\@tbOverdueList);
    return $odStr;
}

 sub getOverdue_grade{
    my ($dbh, $dateFrom, $dateTo, $sort2, $input) = @_;
    my @gradeList = ();
    my $tbOverdue;
    my $row = 0;
    my $totalOverdue = 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.*, 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
        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 = ?
        order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, 'zzzzzz')";
    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);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            $totalOverdue += 1;
            if (!$loan->{'grade'} || $loan->{'grade'} eq ""){
                $loan->{'grade'} = " N/A";
            }
            if (!$tbOverdue->{$loan->{'grade'}}){
                 $tbOverdue->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
                 $tbOverdue->{$loan->{'grade'}}->{'count'} = $row;
                 $row++;
            }
            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($sort1,\@tbOverdueList);
    return $odStr;
}
sub getOverdue_title{
    my ($dbh, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOverdue;
    my @recIdList=();
    my $row = 0;
    my $totalOverdue = 0;
    my $odStr = "";
    @recIdList = split /\$/,  $input->{'recIdList'};
    if (scalar(@recIdList) == 0){
        push @recIdList, '';}
    my $sql = "select i.rid, 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.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);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            $loan->{'t_teachername'}    = $loan->{'t_teachername'};
            $totalOverdue += 1;
            if (!$tbOverdue->{$loan->{'title'}}){
                $tbOverdue->{$loan->{'title'}}->{'title'} = $loan->{'title'};
                $tbOverdue->{$loan->{'title'}}->{'rid'} = $loan->{'rid'};
                $tbOverdue->{$loan->{'title'}}->{'price'} = $loan->{'price'};
                $tbOverdue->{$loan->{'title'}}->{'count'} = $row;
                $row++;
            }
            push @{$tbOverdue->{$loan->{'title'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    $odStr = createContentOD($sort1,\@tbOverdueList);
    return $odStr;
}
#----------------------------------------------------------
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;
}