#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

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

use Opals::Date qw(
    date_text
);

use Opals::Tb_Record qw(
    
    tb_item_findByBarcode

);
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/report/itemOnLoan.tmpl',
                reqPermission   => 'tb_report',
            }
        );

my $pref = tmpl_preference($dbh);

my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
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";
    }
}
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'};
        getOnLoan_user($dbh, $template, $dateFrom, $dateTo,$orderBy ,$sort2);
    }
    elsif ($sort1 eq 'teacher'){
        getOnLoan_teacher($dbh, $template, $dateFrom, $dateTo, $sort2);
    }
    elsif ($sort1 eq 'homeroom'){
        getOnLoan_homeroom($dbh, $template, $dateFrom, $dateTo, $sort2);
    }
    elsif ($sort1 eq 'grade'){
        getOnLoan_grade($dbh, $template, $dateFrom, $dateTo, $sort2);
    }
    elsif ($sort1 eq 'course'){
        getOnLoan_course($dbh, $template, $dateFrom, $dateTo, $sort2);
    }
    elsif ($sort1 eq 'courseteacher'){
        getOnLoan_courseteacher($dbh, $template, $dateFrom, $dateTo, $sort2);
    }
    elsif ($sort1 eq 'title'){
        getOnLoan_title($dbh, $template, $dateFrom, $dateTo, $sort2);
    }

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

    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
        );
    }

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

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

sub getOnLoan_user{
    my ($dbh, $template, $dateFrom, $dateTo, $orderBy, $sort2) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
=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 >= ? && dateLoan <= ? ";
=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 >= ? && dateLoan <= ? ";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($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'};
             $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};
    }
    
=item    
    if ( $orderBy eq 'room' ){
        @tbOnLoanList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @tbOnLoanList;
    }
    else{
        @tbOnLoanList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOnLoanList;
    }
=cut    
    @tbOnLoanList = sort { lc($a->{'lastname'}) cmp lc($b->{'lastname'}) } @tbOnLoanList;

    $template->param(tbOnLoan => \@tbOnLoanList);
    $template->param(
        username    => 1,
        sort1       => "username",
        "sort" . $orderBy => 1,
        totalOnLoan => $totalOnLoan,
    );
}

sub getOnLoan_teacher{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
=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 >= ? && dateLoan <= ? ";
#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 >= ? && dateLoan <= ? ";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($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->{'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}->{'building'}    = $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 getOnLoan_homeroom{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
=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 >= ? && dateLoan <= ? 
        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 >= ? && dateLoan <= ? 
        order by concat(homeroom, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(teacher,'zzzzzz') ";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($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;

        my $sortEmpty = '';
        if ($loan->{'homeroom'} eq "" && $loan->{'buildingcode'} eq "" && $loan->{'teacher'} eq "" ){
            my $sortEmpty = '!';
        }
        my $homeroomBuildingTeacher = lc($loan->{'homeroom'} . $loan->{'buildingcode'} . $loan->{'teacher'}) . $sortEmpty ;
        if (!$tbOnLoan->{$homeroomBuildingTeacher}){
             $tbOnLoan->{$homeroomBuildingTeacher}->{'homeroom'} = $loan->{'homeroom'};
             $tbOnLoan->{$homeroomBuildingTeacher}->{'building'} = $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 getOnLoan_grade{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
=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 >= ? && dateLoan <= ? 
        order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, '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 >= ? && dateLoan <= ? 
        order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, 'zzzzzz')";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($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->{'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 getOnLoan_course{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
=item    
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue,
        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 &&   dateLoan >= ? && dateLoan <= ? 
        order by (coursename)";
=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 >= ? && dateLoan <= ? 
        order by courseName";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($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->{'courseName'} || $loan->{'courseName'} eq ""){
            $loan->{'courseName'} = "";    
        }
        if (!$tbOnLoan->{$loan->{'courseName'}}){
             $tbOnLoan->{$loan->{'courseName'}}->{'courseName'} = $loan->{'courseName'};
             $tbOnLoan->{$loan->{'courseName'}}->{'count'} = $row;
             $row++;
        }
        push @{$tbOnLoan->{$loan->{'courseName'}}->{'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) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
    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 >= ? && dateLoan <= ? 
        order by (l.teacherName)";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($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->{'teacherName'} || $loan->{'teacherName'} eq ""){
            $loan->{'teachername'} = "N/A";    
        }
        if (!$tbOnLoan->{$loan->{'teacherName'}}){
             $tbOnLoan->{$loan->{'teacherName'}}->{'teacherName'} = $loan->{'teacherName'};
             $tbOnLoan->{$loan->{'teacherName'}}->{'count'} = $row;
             $row++;
        }
        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; 
    $template->param(tbOnLoanCourseTeacher => \@tbOnLoanList);
    $template->param(
        courseteacher   => 1,
        sort1   => "courseteacher",
        totalOnLoan => $totalOnLoan,
    );
}
sub getOnLoan_title{
    my ($dbh, $template, $dateFrom, $dateTo, $sort2) = @_;
    my $tbOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
=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.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 tb_teachers as t using(teacherId)
            inner join tb_courseList as cl using(courseCode)) 
        on ts.id = l.teacherScheduleId
        where   dateReturn is null &&   dateLoan >= ? && dateLoan <= ? 
        order by (coursename)";
=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 >= ? && dateLoan <= ? ";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        #my $itemInfo = tb_item_findByBarcode($dbh,$loan->{'barcode'});
        my $itemInfo = getItemBriefInfo($dbh,$loan->{'barcode'});
        $loan->{'title'} = $itemInfo->{'title'} . " (" .$itemInfo->{'rid'} . ")"  ;
        $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'}}->{'price'} = $loan->{'price'};
            $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,
    );
}

############################################
# return only rId, price and title
############################################
sub getItemBriefInfo {
    
    my ($dbh,$bc) =  @_;
    my $sql = (<<_STH_);
select i.rid, i.barcode, i.price,
(select fVal from tb_records as rTitle where rTitle.fId = '245_a' && rTitle.rid = i.rid limit 1) as title
from tb_items i where barcode = ? 
_STH_

    my $itemInfo = $dbh->selectrow_hashref($sql, undef, $bc);
    return $itemInfo;
}

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->{"teacherName"}) cmp ($b->{"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;
    }
    else 
    { 
        @$data = sort { $a->{$sort2} cmp $b->{$sort2} || $a->{"lastname"} cmp $b->{"lastname"} 
                        || $a->{"firstname"} cmp $b->{"firstname"} } @$data; 
    }
}