#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

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

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 ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'txtbk/odnotice.tmpl',
            }
        );

my $pref = tmpl_preference($dbh);
my $libname    = decode('utf8', $pref->{'libname'});

my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
my $sort1       = $input->{'sort1'};
my $sort2       = $input->{'sort2'};
my $categorycode= $input->{'categorycode'} || 0;

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 $firstLoad = (!$dateFrom || !$dateTo)? 1:0;
    if (! $dateFrom && ! $dateTo){
        $dateFrom   = $todayStr;
        $dateTo     = $todayStr;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo   .= " 23:59:59";
    }

    if ($firstLoad){
        $template->param(username => 1);
        $template->param(ReportMsg => 1);
        goto NoProcess;
    }
    if ( $sort1 eq 'username'){
        my $orderBy = $input->{'orderBy'};
        getOverdue_user($dbh, $template, $dateFrom, $dateTo, $orderBy, $sort2,$categorycode);
    }
    elsif ($sort1 eq 'teacher'){
        getOverdue_teacher($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode);
    }
    elsif ($sort1 eq 'homeroom'){
        getOverdue_homeroom($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode);
    }
    elsif ($sort1 eq 'grade'){
        getOverdue_grade($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode);
    }
    elsif ($sort1 eq 'course'){
        getOverdue_course($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode);
    }
    elsif ($sort1 eq 'courseteacher'){
        getOnLoan_courseteacher($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode);
    }
    elsif ($sort1 eq 'title'){
        getOverdue_title($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode);
    }
  
NoProcess:

    $template->param(dateFrom   => substr($dateFrom,0,10));
    $template->param(dateTo     => substr($dateTo,0,10));
    $template->param(sort1      => $sort1);    
    $template->param(sort2      => $sort2);    
    $template->param("sort2" . $sort2   => 1);
    $template->param("libname"  => $libname );
    $template->param("categorycode"  => $categorycode);

    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
        );
    }
    $template->param(catList=> getUserCatList($dbh, $categorycode));
    my $itemOnLoanMsgMap =loc_getMsgFile('circ/notice.msg');
    loc_write($template,$itemOnLoanMsgMap);
    tmpl_write($dbh, $cgi, $cookie, $template);

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

sub getOverdue_user{

    my ($dbh, $template, $dateFrom, $dateTo, $orderBy, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue,to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername, cl.coursename as 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') ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode ";
    my $sth = $dbh->prepare($sql);
    $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->{'barcode'}  = $itemInfo->{'barcode'};
        $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'}}->{'teacher'}   = $loan->{'teacher'};
             $tbOnLoan->{$loan->{'uid'}}->{'buildingcode'}= $loan->{'buildingcode'};
             $tbOnLoan->{$loan->{'uid'}}->{'count'} = $row;
             $row++;
        }
        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(tbOnLoan => \@tbOnLoanList);
    $template->param(
        username    => 1,
        sort1       => "username",
        "sort" . $orderBy => 1,
        totalOnLoan => $totalOnLoan,
    );
}

sub getOverdue_teacher{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue, 
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename
        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')";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by barcode";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'title'} = $itemInfo->{'title'};
        $loan->{'price'} = $itemInfo->{'price'};
        $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
        $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
        $totalOnLoan += 1;
        $loan->{'teacher'} =~ s/(^\s+|\s+$)//g;
        $loan->{'buildingcode'} =~ s/(^\s+|\s+$)//g;
        $loan->{'homeroom'} =~  s/(^\s+|\s+$)//g;
        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 $teacherBuildingHomeroom = $loan->{'teacher'} . $loan->{'buildingcode'} . $loan->{'homeroom'};
        if (!$tbOnLoan->{$teacherBuildingHomeroom}){
            $tbOnLoan->{$teacherBuildingHomeroom}->{'teacher'}    = $loan->{'teacher'};
            $tbOnLoan->{$teacherBuildingHomeroom}->{'buildingcode'}    = $loan->{'buildingcode'};
            $tbOnLoan->{$teacherBuildingHomeroom}->{'homeroom'}    = $loan->{'homeroom'};
            $tbOnLoan->{$teacherBuildingHomeroom}->{'count'} = $row;
            $row++;
        }
        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);
    $template->param(
        teacher => 1,
        sort1   => "teacher",
        totalOnLoan => $totalOnLoan,
    );
}

sub getOverdue_homeroom{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename
        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') ";
    if ($categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " 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 $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;
        $loan->{'teacher'} =~ s/(^\s+|\s+$)//g;
        $loan->{'buildingcode'} =~ s/(^\s+|\s+$)//g;
        $loan->{'homeroom'} =~  s/(^\s+|\s+$)//g;
 
        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 $homeroomBuildingTeacher = lc($loan->{'homeroom'} . $loan->{'buildingcode'} . $loan->{'teacher'});
        if (!$tbOnLoan->{$homeroomBuildingTeacher}){
             $tbOnLoan->{$homeroomBuildingTeacher}->{'homeroom'} = $loan->{'homeroom'};
             $tbOnLoan->{$homeroomBuildingTeacher}->{'buildingcode'} = $loan->{'buildingcode'};
             $tbOnLoan->{$homeroomBuildingTeacher}->{'teacher'} = $loan->{'teacher'};
             $tbOnLoan->{$homeroomBuildingTeacher}->{'count'} = $row;
             $row++;
        }
        push @{$tbOnLoan->{$homeroomBuildingTeacher}->{'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);
    $template->param(
        homeroom => 1,
        sort1   => "homeroom",
        totalOnLoan => $totalOnLoan,
    );
}

sub getOverdue_grade{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename
        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') " ;
    if ($categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, 'zzzzzz')";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'title'} = $itemInfo->{'title'};
        $loan->{'price'} = $itemInfo->{'price'};
        $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
        $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
        $totalOnLoan += 1;
        $loan->{'grade'} =~ s/(^\s+|\s+$)//g;
        if (!$loan->{'grade'} || $loan->{'grade'} eq ""){
            $loan->{'grade'} = " N/A";
        }
        if (!$tbOnLoan->{$loan->{'grade'}}){
             $tbOnLoan->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
             $tbOnLoan->{$loan->{'grade'}}->{'count'} = $row;
             $row++;
        }
        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);
    $template->param(
        grade   => 1,
        sort1   => "grade",
        totalOnLoan => $totalOnLoan,
    );
}

sub getOverdue_course{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, coalesce(cl.courseCode,'0') as courseCode
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join opl_user as t on ts.teacherId=t.uid
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&  
                to_days(l.dateDue) < to_days(now()) &&
                to_days(l.dateDue) >= to_days('$dateFrom') &&
                to_days(l.dateDue) <= to_days('$dateTo') ";
    if ($categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " order by (coursename)";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'title'} = $itemInfo->{'title'};
        $loan->{'price'} = $itemInfo->{'price'};
        $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
        $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
        $totalOnLoan += 1;
        if (!$loan->{'courseCode'} || $loan->{'courseCode'} eq ""){
            $loan->{'courseCode'} = "";
            $loan->{'coursename'} = " N/A";    
        }
        if (!$tbOnLoan->{$loan->{'courseCode'}}){
             $tbOnLoan->{$loan->{'courseCode'}}->{'courseCode'} = $loan->{'courseCode'};
             $tbOnLoan->{$loan->{'courseCode'}}->{'coursename'} = $loan->{'coursename'};
             $tbOnLoan->{$loan->{'courseCode'}}->{'courseCode'} = $loan->{'courseCode'};
             $tbOnLoan->{$loan->{'courseCode'}}->{'count'} = $row;
             $row++;
        }
        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);
    $template->param(
        course   => 1,
        sort1   => "course",
        totalOnLoan => $totalOnLoan,
    );
}
sub getOnLoan_courseteacher{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        coalesce(t.uid, '0') as c_teacherId , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, coalesce(cl.courseCode,'0') as courseCode
        from    tb_loan as l inner join opl_user as u on l.uid = u.uid
        left join
           (tb_teacherSchedule as ts inner join opl_user as t on ts.teacherId=t.uid
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   
             to_days(l.dateDue) < to_days(now()) &&
             to_days(l.dateDue) >= to_days('$dateFrom') &&
             to_days(l.dateDue) <= to_days('$dateTo') ";
    if ($categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode order by (c_teachername)";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'title'} = $itemInfo->{'title'};
        $loan->{'price'} = $itemInfo->{'price'};
        $loan->{'dateLoan_text'} = date_text ($loan->{'dateLoan'}, 0);
        $loan->{'dateDue_text'} =  date_text ($loan->{'dateDue'}, 0);
        $totalOnLoan += 1;
        if (!$loan->{'c_teacherId'} || $loan->{'c_teacherId'} eq "0"){
            $loan->{'c_teacherId'} = "0";
            $loan->{'c_teachername'} = " N/A";    
        }
        if (!$tbOnLoan->{$loan->{'c_teacherId'}}){
             $tbOnLoan->{$loan->{'c_teacherId'}}->{'c_teacherId'} = $loan->{'c_teacherId'};
             $tbOnLoan->{$loan->{'c_teacherId'}}->{'c_teachername'} = $loan->{'c_teachername'};
             $tbOnLoan->{$loan->{'c_teacherId'}}->{'count'} = $row;
             $row++;
        }
        push @{$tbOnLoan->{$loan->{'c_teacherId'}}->{'group'}}, $loan;
    }
    $sth->finish;
    my @tbOnLoanList = ();

    foreach my $k(keys %{$tbOnLoan}){
        secondSort ( $tbOnLoan->{$k}->{'group'}, $sort2);
        push @tbOnLoanList, $tbOnLoan->{$k};
    }
    @tbOnLoanList = sort { $a->{"c_teachername"} cmp $b->{"c_teachername"} } @tbOnLoanList; 
    $template->param(tbOnLoanCourseTeacher => \@tbOnLoanList);
    $template->param(
        courseteacher   => 1,
        sort1   => "courseteacher",
        totalOnLoan => $totalOnLoan,
    );
}

sub getOverdue_title{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2,$categorycode) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 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.addrLine1,u.addrLine2,u.city,u.zip,u.state,u.email,u.yeargraduation,
        l.dateDue < now() as overdue,
        to_days(now()) - to_days(l.dateDue) as deltaDateDue, 
        t.uid as t_id , concat_ws(',',t.lastname,t.firstname) as c_teachername,
        cl.coursename as coursename, 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') ";
    if ($categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode" ;
    }
    $sql .= " group by l.barcode order by (cl.courseName)";

    my $sth = $dbh->prepare($sql);
    $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);
        $totalOnLoan += 1;
        if (!$tbOnLoan->{$loan->{'title'}}){
            $tbOnLoan->{$loan->{'title'}}->{'title'} = $loan->{'title'};
            $tbOnLoan->{$loan->{'title'}}->{'rid'} = $loan->{'rid'};
            $tbOnLoan->{$loan->{'title'}}->{'count'} = $row;
            $row++;
        }
        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);
    $template->param(
        title   => 1,
        sort1   => "title",
        totalOnLoan => $totalOnLoan,
    );
}

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 "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 getUserCatList{
    my($dbh,$selected)=@_;
    my $sth=$dbh->prepare("select catid val ,catname name,defaultPerm from opl_category order by catname");
    $sth->execute();
    my @catList=();
    while(my $rec=$sth->fetchrow_hashref){
        if ($selected && $rec->{'val'} == $selected){
            $rec->{'selected'} = 1;
        }
        push @catList,$rec
     }
     return \@catList;

}