#!/usr/bin/perl

use strict;
use CGI;
use JSON;
use Time::localtime;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
);

use Opals::Date qw(
    date_text
);

use Opals::Tb_Record qw(
  
    tb_item_findByBarcode

);

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();

my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $sort1       = $input->{'sort1'};
my $sort2       = $input->{'sort2'} ;
my $groupBy     = $input->{'groupBy'} || 'username';
my $orderBy     = $input->{'orderBy'};

    $dateFrom .= " 00:00:00";
    $dateTo   .= " 23:59:59";
if (!$sort2 || $sort2 eq ""){
    $sort2 = ($groupBy eq "username" ) ? "title":"username";
}
my $defLang= Opals::Context->preference('lang');

    my $rs;
    if ( $groupBy eq 'username'){
        my $orderBy = $input->{'orderBy'};
        $rs = getOverdue_user($dbh, $dateFrom, $dateTo, $orderBy, $sort2);
    }
    elsif ($groupBy eq 'teacher'){
        $rs = getOverdue_teacher($dbh, $dateFrom, $dateTo, $sort2);
    }
    elsif ($groupBy eq 'homeroom'){
        $rs = getOverdue_homeroom($dbh,$dateFrom, $dateTo, $sort2);
    }
    elsif ($groupBy eq 'grade'){
        $rs = getOverdue_grade($dbh, $dateFrom, $dateTo, $sort2);
    }
    elsif ($groupBy eq 'course'){
        $rs = getOverdue_course($dbh, $dateFrom, $dateTo, $sort2);
    }
    elsif ($groupBy eq 'courseteacher'){
        $rs = getOnLoan_courseteacher($dbh, $dateFrom, $dateTo, $sort2);
    }
    elsif ($groupBy eq 'title'){
        $rs = getOverdue_title($dbh,$dateFrom, $dateTo, $sort2);
    }
  
    my $rsJSON = to_json($rs,{pretty=>1});

    print "Content-type: text/plain\n\n";
    print  $rsJSON;

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

sub getOverdue_user{

    my ($dbh, $dateFrom, $dateTo, $orderBy, $sort2) = @_;
    my $tbOnLoan;
    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 od, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername, cl.coursename as c_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 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')
        group by l.barcode ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $count=0;
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'}    = $itemInfo->{'title'};
        $rec->{'price'}    = $itemInfo->{'price'};
        $rec->{'barcode'}  = $itemInfo->{'barcode'};
        $rec->{'dateLoan_text'}    = date_text ($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'}     = date_text ($rec->{'dateDue'},0,$defLang);
        $count++;
        if (!defined $tbOnLoan->{$rec->{'uid'}}){
            $tbOnLoan->{$rec->{'uid'}}->{'grpInfo'} = {
                uid         => $rec->{'uid'},
                userbarcode => $rec->{'userbarcode'},
                sid         => $rec->{'sid'},
                lastname    => $rec->{'lastname'}, 
                firstname   => $rec->{'firstname'},
                fullname    => $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'},
                email       => $rec->{'email'},
                homeroom    => $rec->{'homeroom'},
                teacher     => $rec->{'teacher'},
                grade       => $rec->{'grade'},
                buildingcode=> $rec->{'buildingcode'}                      
            }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$rec->{'uid'}}->{'data'}}, $rec ;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    if ( $orderBy eq 'homeroom' ){
        @tbOnLoanList = sort { $a->{'grpInfo'}->{'homeroom'} cmp $b->{'grpInfo'}->{'homeroom'} } @tbOnLoanList;
    }
    else{
        @tbOnLoanList = sort { $a->{'grpInfo'}->{'lastname'} cmp $b->{'grpInfo'}->{'lastname'} } @tbOnLoanList;
    }
    return {count=>$count,groupBy=>$groupBy,report=>\@tbOnLoanList};
}

sub getOverdue_teacher{
    my ($dbh, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    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 od, 
        t.uid 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 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') group by barcode ";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $count=0;
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'} = $itemInfo->{'title'};
        $rec->{'price'} = $itemInfo->{'price'};
        $rec->{'dateLoan_text'} = date_text ($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'} =  date_text ($rec->{'dateDue'},0,$defLang);
        $count++;
        $rec->{'teacher'} =~ s/(^\s+|\s+$)//g;
        $rec->{'buildingcode'} =~ s/(^\s+|\s+$)//g;
        $rec->{'homeroom'} =~  s/(^\s+|\s+$)//g;
        if (!$rec->{'teacher'} || $rec>{'teacher'} eq "") {
            $rec->{'teacher'} = " N/A";
        }
        if (!$rec->{'homeroom'} || $rec->{'homeroom'} eq ""){
            $rec->{'homeroom'} = " N/A";
        }
        if (!$rec->{'buildingcode'} || $rec->{'buildingcode'} eq ""){
            $rec->{'buildingcode'} = " N/A";
        }
        my $teacherBuildingHomeroom = $rec->{'teacher'} . $rec->{'buildingcode'} . $rec->{'homeroom'};
        if (!defined $tbOnLoan->{$teacherBuildingHomeroom}){
            $tbOnLoan->{$teacherBuildingHomeroom}->{'grpInfo'} = {
                teacher     => $rec->{'teacher'},
                homeroom    => $rec->{'homeroom'},
                buildingcode=> $rec->{'buildingcode'}
            }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$teacherBuildingHomeroom}->{'data'}}, $rec;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    return {count=>$count,groupBy=>$groupBy, report=>\@tbOnLoanList};
}

sub getOverdue_homeroom{
    my ($dbh, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $count = 0;
    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 od, 
        t.uid 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 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') 
        group by barcode                
        order by concat(u.homeroom, 'zzzzzz' ), concat(u.buildingcode, 'zzzzzz'), concat(u.teacher,'zzzzzz') ";
           
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'} = $itemInfo->{'title'};
        $rec->{'price'} = $itemInfo->{'price'};
        $rec->{'dateLoan_text'} = date_text ($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'} =  date_text ($rec->{'dateDue'},0,$defLang);
        $count++;
        $rec->{'teacher'} =~ s/(^\s+|\s+$)//g;
        $rec->{'buildingcode'} =~ s/(^\s+|\s+$)//g;
        $rec->{'homeroom'} =~  s/(^\s+|\s+$)//g;
 
        if (!$rec->{'homeroom'} || $rec->{'homeroom'} eq ""){
            $rec->{'homeroom'} = " N/A";
        }
        if (!$rec->{'buildingcode'} || $rec->{'buildingcode'} eq ""){
            $rec->{'buildingcode'} = " N/A";
        }
        if (!$rec->{'teacher'} || $rec->{'teacher'} eq ""){
            $rec->{'teacher'} = " N/A";
        }
        my $homeroomBuildingTeacher = lc($rec->{'homeroom'} . $rec->{'buildingcode'} . $rec->{'teacher'});
        if (!$tbOnLoan->{$homeroomBuildingTeacher}){
             $tbOnLoan->{$homeroomBuildingTeacher}->{'grpInfo'} = {
                'homeroom'      => $rec->{'homeroom'},
                'buildingcode'  => $rec->{'buildingcode'},
                'teacher'       => $rec->{'teacher'},
                'grade'         => $rec->{'grade'},
             }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$homeroomBuildingTeacher}->{'data'}}, $rec;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    return {count=>$count,groupBy=>$groupBy,report=>\@tbOnLoanList};
}

sub getOverdue_grade{
    my ($dbh, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $count=0;
    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 od, 
        t.uid 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 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')
        group by barcode                
        order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, 'zzzzzz')";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'} = $itemInfo->{'title'};
        $rec->{'price'} = $itemInfo->{'price'};
        $rec->{'dateLoan_text'} = date_text ($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'} =  date_text ($rec->{'dateDue'},0,$defLang);
        $count++;
        $rec->{'grade'} =~ s/(^\s+|\s+$)//g;
        if (!defined $rec->{'grade'} || $rec->{'grade'} eq ""){
            $rec->{'grade'} = " N/A";
        }
        if (!defined $tbOnLoan->{$rec->{'grade'}} ){
             $tbOnLoan->{$rec->{'grade'}}->{'grpInfo'} = {
                'grade' => $rec->{'grade'}
             }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$rec->{'grade'}}->{'data'}}, $rec;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort{$a->{'grpInfo'}->{"grade"}<=>$b->{'grpInfo'}->{"grade"}||$a->{'grpInfo'}->{"grade"}cmp$b->{'grpInfo'}->{"grade"}}@tbOnLoanList;
    return {count=>$count,groupBy=>$groupBy,report=>\@tbOnLoanList};

}

sub getOverdue_course{
    my ($dbh,$dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $count = 0;
    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 od, 
        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')
        group by barcode
        order by (coursename)";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'} = $itemInfo->{'title'};
        $rec->{'price'} = $itemInfo->{'price'};
        $rec->{'dateLoan_text'} = date_text ($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'} =  date_text ($rec->{'dateDue'},0,$defLang);
        $count++;
        if (!defined $rec->{'courseCode'} || $rec->{'courseCode'} eq "" || $rec->{'courseCode'} eq "0"){
            $rec->{'courseCode'} = "";
            $rec->{'coursename'} = " N/A";    
        }
        if (!defined $tbOnLoan->{$rec->{'courseCode'}}){
             $tbOnLoan->{$rec->{'courseCode'}}->{'grpInfo'} = {
                 'courseCode'   => $rec->{'courseCode'},
                 'coursename'   => $rec->{'coursename'},
             }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$rec->{'courseCode'}}->{'data'}}, $rec;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'grpInfo'}->{"coursename"} cmp $b->{'grpInfo'}->{"coursename"} } @tbOnLoanList; 
    return {count=>$count,groupBy=>$groupBy, report=>\@tbOnLoanList};
}


sub getOnLoan_courseteacher{
    my ($dbh,$dateFrom,$dateTo,$sort2) = @_;
    my $tbOnLoan;
    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 od, 
        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')
        group by l.barcode
        order by (c_teachername)";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $count=0;
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'} = $itemInfo->{'title'};
        $rec->{'price'} = $itemInfo->{'price'};
        $rec->{'dateLoan_text'} = date_text($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'} =  date_text($rec->{'dateDue'},0,$defLang);
        $count++;
        if (!$rec->{'c_teacherId'} || $rec->{'c_teacherId'} eq "" || $rec->{'c_teacherId'} eq "0"){
            $rec->{'c_teacherId'} = "0";
            $rec->{'c_teachername'} = " N/A";    
        }
        if (!defined $tbOnLoan->{$rec->{'c_teacherId'}}){
            $tbOnLoan->{$rec->{'c_teacherId'}}->{'grpInfo'} = {
                'c_teacherId'   => $rec->{'c_teacherId'},
                'c_teachername' => $rec->{'c_teachername'},
            }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$rec->{'c_teacherId'}}->{'data'}}, $rec;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(keys %{$tbOnLoan}){
        secondSort($tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'grpInfo'}->{"c_teachername"} cmp $b->{'grpInfo'}->{"c_teachername"} } @tbOnLoanList ; 
    return {count=>$count,groupBy=>$groupBy, report=>\@tbOnLoanList};
}

sub getOverdue_title{
    my ($dbh, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $count = 0;
    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 od, 
        t.uid 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
        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')
        group by l.barcode                
        order by (cl.courseName)";

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$rec->{'barcode'});
        $rec->{'title'} = $itemInfo->{'title'};
        $rec->{'price'} = $itemInfo->{'price'};
        $rec->{'rid'} = $itemInfo->{'rid'};
        $rec->{'dateLoan_text'} = date_text($rec->{'dateLoan'},0,$defLang);
        $rec->{'dateDue_text'} =  date_text($rec->{'dateDue'},0,$defLang);
        $count++;;
        if (!$tbOnLoan->{$rec->{'title'}}){
            $tbOnLoan->{$rec->{'title'}}->{'grpInfo'} =  {
                'title' => $rec->{'title'} ,
                'rid'   => $rec->{'rid'}
            }
        }
        $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
        $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
        $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
        push @{$tbOnLoan->{$rec->{'title'}}->{'data'}}, $rec;
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'data'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { lc(trimArticle($a->{'grpInfo'}->{'title'})) cmp lc(trimArticle($b->{'grpInfo'}->{'title'})) } @tbOnLoanList;

    return {count=>$count,groupBy=>$groupBy, report=>\@tbOnLoanList};
}
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 secondSort
{
    my ($data, $sort2) = @_;
    
    if ( $sort2 eq "username" )
    { 
        @$data = sort { $a->{"lastname"} cmp $b->{"lastname"} || $a->{"firstname"} cmp $b->{"firstname"} || 
            lc(trimArticle($a->{"title"})) cmp lc(trimArticle($b->{"title"})) } @$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; 
    }
}