#!/usr/bin/perl

use strict;

use CGI;

use Time::localtime;
use JSON;
use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
);


use Opals::Tb_Record qw(
    
    tb_item_findByBarcode

);

use Opals::Date qw(
    date_text
);

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 ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => '/txtbk/report/tbOnLoan_prt.tmpl',
#        reqPermission   => 'reqPermission1|reqPermission2|etc.',
    }
);
my $pref = tmpl_preference($dbh);

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

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";
    }
}

if (! $dateFrom && ! $dateTo){
    $dateFrom   = $todayStr;
    $dateTo     = $todayStr;
    }
else{
    $dateFrom .= " 00:00:00";
    $dateTo   .= " 23:59:59";
    }

if ( $sort1 eq 'username'){
    my $orderBy = $input->{'orderBy'};
    if ($input->{'list'}){
        getOnLoan_userList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderBy);
        $template->param(idList => 1);
    }
    else{
        getOnLoan_userNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderBy);
        $template->param(odnotice => 1);
    }
}
elsif($sort1 eq 'teacher' ){
    if ($input->{'list'}){
        getOnLoan_teacherList($dbh, $template, $sort1,$sort2,$dateFrom, $dateTo, $input);
        $template->param(teacherList => 1);
    }
    else{
        getOnLoan_teacherNotice($dbh, $template, $sort1,$sort2,$dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'homeroom'){
    if ($input->{'list'}){
        getOnLoan_homeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(homeroomList => 1);
    }
    else{
        getOnLoan_homeroomNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'grade'){
    if ($input->{'list'}){
        getOnLoan_gradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(gradeList => 1);
    }
    else{
        getOnLoan_gradeNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'title'){
    if ($input->{'list'}){
        getOnLoan_titleList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(recIdList => 1);
    }
    else{
        getOnLoan_titleNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'course'){
    if ($input->{'list'}){
        getOnLoan_courseList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(courseList => 1);
    }
    else{
        getOnLoan_courseNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'courseteacher'){
    if ($input->{'list'}){
        getOnLoan_courseTeacherList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(courseTeacherList => 1);
    }
    else{
        getOnLoan_courseTeacherNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
if($input->{'headers'} eq 'true'){
        $template->param(includeHeaders => 1);
    }

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

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

sub getOnLoan_userList{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderBy) = @_;
    my @idList = ();
    my $tbOnLoan;
    my $totalOnLoan = 0;

    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
=item
     my $sql = "select l.*, u.* , l.dateDue < now() as overdue,
        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 && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.uid = ?  ";
=cut
     my $sql = "select l.*, u.* , l.dateDue < now() as overdue
        from tb_loan as l inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && dateLoan >= '$dateFrom' && dateLoan <= '$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->{'dateLoan_text'}    = date_text ($loan->{'dateLoan'}, 0);
            $loan->{'dateDue_text'}     = date_text ($loan->{'dateDue'}, 0 );
            #$totalOnLoan += 1;
            if (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;

    my @tbOnLoanList = ();

    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    
    if ( $orderBy eq 'room' ){
        @tbOnLoanList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @tbOnLoanList;
    }
    else{
        @tbOnLoanList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOnLoanList;
    }
    $template->param(tbOnLoanUser => \@tbOnLoanList);
}

sub getOnLoan_teacherList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input)= @_;
    my @teacherList = ();
    my @homeroomList = ();
    my @buildingList = ();

    my $tbOnLoan;
    @teacherList = split /\$/, $input->{'teacherList'};    
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'};

    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 = "";
            }
        }
    }
=item
    my $sql = "select u.*, l.*, 
        l.dateDue < now() as overdue, 
        t.teacherId as c_teacherId , 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 &&  dateLoan >= '$dateFrom'  && dateLoan <= '$dateTo' && 
                u.teacher = ? && u.homeroom = ? && u.buildingcode=? ";
#order by concat(t.lastname, t.firstname, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(homeroom,'zzzzzz') ";
=cut
    my $sql = "select u.*, l.*, l.dateDue < now() as overdue
        from  tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom'  && dateLoan <= '$dateTo' && 
                u.teacher = ? && u.homeroom = ? && u.buildingcode=? ";

    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);
            if (!$loan->{'teacher'}) {
                $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 $teacherBuildingHomeroom = $loan->{'teacher'} . $loan->{'buildingcode'} . $loan->{'homeroom'};
            if (!$tbOnLoan->{$teacherBuildingHomeroom}){
                $tbOnLoan->{$teacherBuildingHomeroom}->{'teacher'}    = $loan->{'teacher'};
                $tbOnLoan->{$teacherBuildingHomeroom}->{'building'}    = $loan->{'buildingcode'};
                $tbOnLoan->{$teacherBuildingHomeroom}->{'homeroom'}    = $loan->{'homeroom'};
            }
            push @{$tbOnLoan->{$teacherBuildingHomeroom}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    $template->param(tbOnLoanTeacher => \@tbOnLoanList);
}

sub getOnLoan_gradeList {

    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @gradeList = ();
    my $tbOnLoan;
    @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 = "";
            }
        }
    }
=item    
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 &&  dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && u.grade = ?";   
=cut        
#order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, 'zzzzzz')";
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where dateReturn is null &&  dateLoan >= '$dateFrom' && dateLoan <= '$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);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            if (!$loan->{'grade'} || $loan->{'grade'} eq ""){
                $loan->{'grade'} = " N/A";
            }
            if (!$tbOnLoan->{$loan->{'grade'}}){
                 $tbOnLoan->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
            }
            push @{$tbOnLoan->{$loan->{'grade'}}->{'group'}}, $loan;
        }
    }
    
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    $template->param(tbOnLoanGrade => \@tbOnLoanList);
}

sub getOnLoan_homeroomList {

    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @homeroomList = ();
    my @buildingList = ();
    my @teacherList = ();
    my $tbOnLoan;
    @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 "" || $hr eq "None" || $hr eq "N/A" ){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            if ($building eq " " || $building eq "" || $building eq "None" || $building eq "N/A"){
                $building = "";
            }
        }
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "" || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }
=item    
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' 
        && u.homeroom = ? && u.buildingcode=? && u.teacher=?  ";
#order by concat(homeroom, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(teacher,'zzzzzz') ";
=cut
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'
                && u.homeroom = ? && u.buildingcode = ? && u.teacher = ?";
        
    my $sth     = $dbh->prepare($sql);
    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);
            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 (!$tbOnLoan->{$hrBldTeacher}){
                $tbOnLoan->{$hrBldTeacher}->{'homeroom'} = $loan->{'homeroom'};
                $tbOnLoan->{$hrBldTeacher}->{'building'} = $loan->{'buildingcode'};
                $tbOnLoan->{$hrBldTeacher}->{'teacher'} = $loan->{'teacher'};
            }
            push @{$tbOnLoan->{$hrBldTeacher}->{'group'}}, $loan;
         }
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    $template->param(tbOnLoanHomeroom => \@tbOnLoanList);
}

sub getOnLoan_titleList {

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @recIdList=();
    my $tbOnLoan;
    my $totalLoan = 0;
    @recIdList = split /\$/,  $input->{'recIdList'};
    if (scalar(@recIdList) == 0){
        push @recIdList, '';}
=item        
    my $sql = "select i.rid ,l.*, u.*, l.dateDue < now() as overdue,
        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 && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && i.rid = ?" ;
=cut
    my $sql = "select i.rid ,l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        inner join tb_items as i using (barcode)
        where   dateReturn is null && dateLoan >= '$dateFrom' && dateLoan <= '$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);
            
            if (!$tbOnLoan->{$loan->{'title'}}){
                $tbOnLoan->{$loan->{'title'}}->{'title'} = $loan->{'title'};
                $tbOnLoan->{$loan->{'title'}}->{'rid'} = $loan->{'rid'};
                $tbOnLoan->{$loan->{'title'}}->{'price'} = $loan->{'price'};
            }
            push @{$tbOnLoan->{$loan->{'title'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    
    my @tbOnLoanList = ();

    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    $template->param(tbOnLoanTitle => \@tbOnLoanList);
}

sub getOnLoan_courseList {
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseNameList=();
    my $tbOnLoan;
    my $totalLoan = 0;
    @courseNameList = split /\$/,  $input->{'courseNameList'};
    if (scalar(@courseNameList) == 0){
        push @courseNameList, '';
    }
=item
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && cl.courseCode = ?" ;
=cut    
    
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.courseName = ?";

    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseNameList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        if ($courseNameList[$i]){
            $sth->execute($courseNameList[$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 (!$tbOnLoan->{$loan->{'courseName'}}){
                    $tbOnLoan->{$loan->{'courseName'}}->{'courseName'} = $loan->{'courseName'};
                }
                push @{$tbOnLoan->{$loan->{'courseName'}}->{'group'}}, $loan;
            }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
=item        
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue,
            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 && 
                dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && cl.courseCode is null" ;
=cut
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'  && (l.courseName is null || l.courseName = '') ";

        $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 (!$tbOnLoan->{$loan->{'couresName'}}){
                $tbOnLoan->{$loan->{'courseName'}}->{'courseName'} = $loan->{'coursename'};
            }
            push @{$tbOnLoan->{$loan->{'courseCode'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k( keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort{$a->{'coursename'} cmp $b->{'coursename'}}  @tbOnLoanList;
    $template->param(tbOnLoanCourse => \@tbOnLoanList);
}

sub getOnLoan_courseTeacherList {
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseTeacherList=();
    my $tbOnLoan;
    my $totalLoan = 0;
    @courseTeacherList = split /\$/,  $input->{'courseTeacherList'};
    if (scalar(@courseTeacherList) == 0){
        push @courseTeacherList, '';}
=item
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && t.teacherId = ?" ;
=cut
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.teacherName = ?" ;
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseTeacherList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        $courseTeacherList[$i] = lc($courseTeacherList[$i]) eq 'n/a'?'': $courseTeacherList[$i]; 
        print debug "ct[1]: $courseTeacherList[$i] \n";
        if ( $courseTeacherList[$i] || lc($courseTeacherList[$i]) ne "n/a"){
            $sth->execute($courseTeacherList[$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 (!$tbOnLoan->{$loan->{'teacherName'}}){
                     $tbOnLoan->{$loan->{'teacherName'}}->{'teacherName'} = $loan->{'teacherName'};
                }
                push @{$tbOnLoan->{$loan->{'teacherName'}}->{'group'}}, $loan;
            }
        }
        else{
            $flagNull = 1;
        }
    }
  
    if ($flagNull ){
       my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue
            from    tb_loan as l inner join opl_user as u on l.uid = u.uid
            where dateReturn is null && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && (l.teacherName is null || l.teacherName='')";
            $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 (!$tbOnLoan->{$loan->{'teacherName'}}){
                    $tbOnLoan->{$loan->{'teacherName'}}->{'teacherName'} = $loan->{'teacherName'};
            }
            push @{$tbOnLoan->{$loan->{'teacherName'}}->{'group'}}, $loan;
        }
    }
    
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k( keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort{$a->{'teacherName'} cmp $b->{'teacherName'}}  @tbOnLoanList;
    if ($sort2 && $sort2 eq 'username'){
      @tbOnLoanList = sort{$a->{'group'}->{'lastname'} cmp $b->{'group'}->{'lastname'}}  @tbOnLoanList;
    }
    $template->param(tbOnLoanCourseTeacher => \@tbOnLoanList);
}

sub getOnLoan_userNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderBy) = @_;
    my @idList = ();
    my $tbOnLoan;
    my $totalOnLoan = 0;

    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
=item
     my $sql = "select l.*, u.* , l.dateDue < now() as overdue,
        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 &&    dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'  &&  l.uid = ?  ";
=cut
    my $sql = "select l.*, u.* , l.dateDue < now() as overdue
        from tb_loan as l inner join opl_user as u on l.uid = u.uid
        where dateReturn is null &&    dateLoan >= '$dateFrom' && dateLoan <= '$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->{'dateLoan_text'}    = date_text ($loan->{'dateLoan'}, 0);
            $loan->{'dateDue_text'}     = date_text ($loan->{'dateDue'}, 0 );
            if (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;

    my @tbOnLoanList = ();

    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOnLoanList;
    $template->param(tbOnLoanNotice => \@tbOnLoanList);
}

sub getOnLoan_teacherNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input)= @_;
    my @teacherList = ();
    my @homeroomList = ();
    my @buildingList = ();

    my $tbOnLoan;
    @teacherList = split /\$/, $input->{'teacherList'};    
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'};

    
    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 = "";
            }
        }
    }
=item    
    my $sql = "select u.*, l.*, 
        l.dateDue < now() as overdue, 
        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 &&  dateLoan >= '$dateFrom'  && dateLoan <= '$dateTo' && 
                u.teacher = ?  && u.homeroom = ? && u.buildingcode=? ";
#order by concat(t.lastname, t.firstname, 'zzzzzz' ),  concat(buildingcode, 'zzzzzz'), concat(homeroom,'zzzzzz') ";
=cut
     my $sql = "select u.*, l.*, l.dateDue < now() as overdue
        from  tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom'  && dateLoan <= '$dateTo' && 
                u.teacher = ?  && u.homeroom = ? && u.buildingcode=? ";
   
    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);
            if (!$loan->{'teacher'}) {
                $loan->{'teacher'} = " N/A";;
            }
            if (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'}; 
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
   
   @tbOnLoanList = sort { $a->{'teacher'} cmp $b->{'teacher'} } @tbOnLoanList;
   $template->param(tbOnLoanNotice => \@tbOnLoanList);
   
}

sub getOnLoan_homeroomNotice{
    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @homeroomList = ();
    my @buildingList = ();
    my @teacherList = ();

    my $tbOnLoan;
    @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,
        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 && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'
             && u.homeroom = ?   && u.buildingcode=? && u.teacher=?  ";
            
    my $sth     = $dbh->prepare($sql);
    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);
            if (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
         }
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @tbOnLoanList;
    $template->param(tbOnLoanNotice => \@tbOnLoanList);
}

sub getOnLoan_gradeNotice{
    
    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @gradeList = ();
    my $tbOnLoan;
    @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 = "";
            }
        }
    }
=item
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 &&  dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && u.grade = ? ";
=cut
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom' && dateLoan <= '$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);
            $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
            if (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOnLoanList = ();
    foreach my $k(sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'grade'} cmp $b->{'grade'} } @tbOnLoanList;
    $template->param(tbOnLoanNotice => \@tbOnLoanList);
}

sub getOnLoan_titleNotice{
    
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @recIdList=();
    my $tbOnLoan;
    my $totalLoan = 0;
    @recIdList = split /\$/,  $input->{'recIdList'};
    if (scalar(@recIdList) == 0){  push @recIdList, '';}
=item    
    my $sql = "select i.rid ,l.*, u.*, l.dateDue < now() as overdue,
        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 &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && i.rid = ?" ;
=cut
    my $sql = "select i.rid ,l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        inner join tb_items as i using (barcode)
        where   dateReturn is null &&   dateLoan >= '$dateFrom' && dateLoan <= '$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);
            if (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    
    my @tbOnLoanList = ();

    foreach my $k( sort keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'firstname'} cmp $b->{'lastname'} } @tbOnLoanList;
    $template->param(tbOnLoanNotice => \@tbOnLoanList);
}

sub getOnLoan_courseNotice{
my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseNameList=();
    my $tbOnLoan;
    my $totalLoan = 0;
    @courseNameList = split /\$/,  $input->{'courseNameList'};
    if (scalar(@courseNameList) == 0){
        push @courseNameList, '';}
=item
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && cl.courseCode = ?" ;
=cut
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.courseName = ?";

    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseNameList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        if ($courseNameList[$i]){
            $sth->execute($courseNameList[$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 (!$tbOnLoan->{$loan->{'uid'}}){
                     $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                     $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                     $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                     $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                     $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                     $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                     $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                     $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                     $tbOnLoan->{$loan->{'uid'}}->{'teacherName'}  = $loan->{'teacherName'};
                }
                push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
           }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
=item        
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue,
            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 &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && cl.courseCode is null" ;
=cut
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&  dateLoan >= '$dateFrom' && dateLoan <= '$dateTo'  && (l.courseName is null || l.courseName = '') ";

        $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 (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    
    $sth->finish;
    
    my @tbOnLoanList = ();

    foreach my $k( keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOnLoanList;
    $template->param(tbOnLoanNotice => \@tbOnLoanList);


}
sub getOnLoan_courseTeacherNotice{
my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseTeacherList=();
    my $tbOnLoan;
    my $totalLoan = 0;
    @courseTeacherList = split /\$/,  $input->{'courseTeacherList'};
    if (scalar(@courseTeacherList) == 0){
        push @courseTeacherList, '';}
=item        
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && t.teacherId = ?";
=cut
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.teacherName = ?" ;

    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseTeacherList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
         if ($courseTeacherList[$i]){
            $sth->execute($courseTeacherList[$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 (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                }
                push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
           }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
=item        
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue,
            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 &&   dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && t.teacherId is null" ;
=cut
        my $sql_null = "select l.*, u.*, l.dateDue < now() as overdue
            from    tb_loan as l inner join opl_user as u on l.uid = u.uid
            where dateReturn is null && dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' && l.teacherName 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 (!$tbOnLoan->{$loan->{'uid'}}){
                 $tbOnLoan->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOnLoan->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOnLoan->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOnLoan->{$loan->{'uid'}}->{'building'}  = $loan->{'building'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};   
                 $tbOnLoan->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOnLoan->{$loan->{'uid'}}->{'teacherName'}  = $loan->{'teacherName'};
            }
            push @{$tbOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    
    my @tbOnLoanList = ();

    foreach my $k( keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOnLoanList;
    $template->param(tbOnLoanNotice => \@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"} } @$data; 
    }
    elsif($sort2 eq "title"){
        @$data = sort { lc(trimArticle($a->{"title"})) cmp lc(trimArticle($b->{"title"})) } @$data; 
    }
    elsif($sort2 eq "t_teacher"){
        @$data = sort {($a->{"t_teachername"}) cmp ($b->{"t_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;
    }
    else 
    { 
        @$data = sort { $a->{$sort2} cmp $b->{$sort2} || $a->{"lastname"} cmp $b->{"lastname"} 
                        || $a->{"firstname"} cmp $b->{"firstname"} } @$data; 
    }
}