#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Encode;
use PDF::Create;
use POSIX qw(
    floor
    ceil
);

use Opals::Context;

use Time::localtime;

use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Report qw(
    rpt_onloanList
    
);


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

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
    
);

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

my $cgi    = CGI->new;
my $input  = $cgi->Vars();
my $pref   = tmpl_preference($dbh);
my $libname    = decode('utf8', $pref->{'libname'});
my $libAddress = decode('utf8', $pref->{'libAddress'});
my $libCity    = decode('utf8', $pref->{'libCity'});
my $libState   = decode('utf8', $pref->{'libState'});
my $libZip     = decode('utf8', $pref->{'libZip'});
my $libPhone   = decode('utf8', $pref->{'libPhone'});
my $libFax     = decode('utf8', $pref->{'libFax'});
my $libEmail   = decode('utf8', $pref->{'libEmail'});

#-------------------------------------------------------------------------------------
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'circ/odprint.tmpl',
            reqPermission   => 'notice',
        }
    );

my $sort1    = $input->{'sort1'};
my $sort2    = $input->{'sort2'};
   $sort2    ='dewey' if($sort2 eq 'callNumber');
my $optTitle = $input->{'optTitle'};
my $optBilg  = $input->{'optBilg'};
my $prtType  = $input->{'prtType'};
my $optPrice = $input->{'optPrice'};
my $optPage  = $input->{'optPage'};
my $printGreeting =$input->{'optGreeting'};

my $msgTopCbox = $input->{'msgTopCbox'};
my $msgBotCbox = $input->{'msgBotCbox'};
my $srcCall    = $input->{'srcCall'};
my $incOpt     = $input->{'incOpt'};

my @msgArr=();
my @lang;
my $l;
if($optBilg eq 'enSp'){
   @lang=('en','sp');
   $l ='en';
}
elsif($optBilg eq 'frOnly'){
   @lang=('fr');
   $l='fr';
}
else{
   @lang=('en');
   $l='en';
}

my $todayStr ="";                         

#Mon, Apr 16, 2012 @ 10:26:26 EDT
my @csvHeader=qw(
    TEACHER
    GRADE
    HOMEROOM
    BUILDINGCODE
    LASTNAME
    FIRSTNAME
    USERBARCODE
    ADDRLINE1      
    ADDRLINE2      
    CITY           
    ZIP            
    STATE          
    PHONE          
    CELLPHONE      
    FAX            
    EMAIL          
    GENDER        
    YEARGRADUATION
    TITLE
    BARCODE
    PRICE
    CALLNUMBER
    DATELOAN
    DATEDUE
    OD_DAYS
);
my @fields=qw(
    teacher
    grade
    homeroom
    buildingcode
    lastname
    firstname
    userbarcode
    addrLine1      
    addrLine2      
    city           
    zip            
    state          
    phone          
    cellphone      
    fax            
    email          
    gender        
    yeargraduation
    title
    barcode
    price
    callNumber
    dateLoan
    dateDue
    deltaDueDate
);

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

my $DataRecs;
if ( $sort1 eq '' ){
    $sort1 = "homeroom";
}
if ( !$sort2 ){
    if ( $sort1 eq "username" ) { $sort2 = "title"; }
    else { $sort2 = "username"; }
}
   my $pref = tmpl_preference($dbh);
    my $reportGroup;
    if($pref->{'reportGroup'} == 1 ){
        $reportGroup = "homeroom";
    }
    elsif($pref->{'reportGroup'} == 2 ){
        $reportGroup = "teacher";
    }
    elsif($pref->{'reportGroup'} ==3 ){
        $reportGroup = "grade";
    }
    else{
        $reportGroup = "username";
    }
    
    my $sort1 = $input->{'sort1'};
    my $sort2 = $input->{'sort2'};
    
    if ( $sort1 eq '' ){
      $sort1 = $reportGroup ;
    }
    
    if ( !defined $sort2 ){
        if ( $sort1 eq "username" ) { $sort2 = "title"; }
        else { $sort2 = "username"; }
    }


my $userSortBy=''; 
if($sort1 eq 'username' && $input->{'userSortBy'} ne ''){
    $userSortBy =  $input->{'userSortBy'};
}

my $dateFrom =  $input->{'dateFrom'};
my $dateTo =    $input->{'dateTo'};
my $dateToday = date_f005();
$dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
$todayStr = date_text($dateToday,1);
$dateFrom = $dateToday if ( !$dateFrom );
$dateTo = $dateToday if ( !$dateTo );

my $dateField = $input->{'dateField'};
if(!$dateField || $dateField eq ''){
    $dateField = 'dateDue';
}

my $pNum = $input->{'pNum'};
my $rpFilter={};
   $rpFilter->{'dateField'}     = $dateField;
   $rpFilter->{'dateFrom'}      = $dateFrom;
   $rpFilter->{'dateTo'}        = $dateTo;
   $rpFilter->{'groupBy'}       = $sort1;
   $rpFilter->{'sortWithinGroup'} = $sort2;
   $rpFilter->{'overdue'}       = 1;
   $rpFilter->{'userSortBy'}    = $userSortBy;

if($input->{'op'} eq 'exportCsv' ){
    #Tue, Apr 17, 2012 @ 08:36:08 EDT
    my $odList;
    $odList = getODList2Export($dbh, $rpFilter,getGrpList($dbh,$sort1,$input));
    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=odList.csv\n\n"; 
    print "\"",  join("\",\"",@csvHeader), "\"\n";
    foreach my $row(@$odList){
        print "\"",  join("\",\"",@$row), "\"\n";
    }
}
else{
    if ( $input->{'list'} ){
        #my $DataRecs = getOverDueList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        my ($odTotal,$DataRecs) =  rpt_onloanList($dbh,$rpFilter,getGrpList($dbh,$sort1,$input));
        setOverDueListByGrp($sort1);
        $template->param(odList => $DataRecs);

    }

    if($input->{'headers'} eq 'true'){
        $template->param(includeHeaders => 1);
    }

    my $noticeMsgMap =loc_getMsgFile('circ/notice.msg');
    loc_write($template,$noticeMsgMap);
    tmpl_write($dbh, $cgi, $cookie, $template); 
}

#----------------------------------------------------------------------------------------------------
sub getGrpList{
    my($dbh,$grp,$input)=@_;
    my $grpList=[];
    my $sth = $dbh->prepare("select * from opl_user where uid =?") ;


    if ($grp  eq 'username' ){
        my @uidList =  split /\$/, $input->{'idlist'};
        foreach my $uid( @uidList){
            $sth->execute($uid);
            if(my $rec= $sth->fetchrow_hashref){
                push @$grpList,$rec;
            }
        }
    }
    elsif ($grp eq 'teacher' || $grp eq 'homeroom' ){
        my @roomlist   = split /\$/, $input->{'roomlist'};
        my @bdinglist  = split /\$/, $input->{'bdinglist'};
        my @teacherlist= split /\$/, $input->{'teacherlist'};
        for (my $i=0; $i<scalar(@roomlist) ; $i++ ){
            push @$grpList,{homeroom     => @roomlist[$i],
                            teacher      => @teacherlist[$i],
                            buildingcode => @bdinglist[$i]
                            };
        }

    }
    elsif ($grp eq 'grade'){
        my @gradelist =  split /\$/, $input->{'gradelist'};
        foreach my $g(@gradelist ){
            push @$grpList,{grade => $g};
        }

    }
    return $grpList;
}

#----------------------------------------------------------------------------------------------------
sub getODList2Export{
    my ($dbh, $rpFilter,$grpList)=@_;
    my $retList=[];

    my ($odTotal,$odList) =  rpt_onloanList($dbh,$rpFilter,$grpList);

    foreach my $grp(@$odList){
        foreach my $el(@{$grp->{'loanList'}}){
            my $item=[];
            foreach my $field(@fields){
                if($field eq 'gender'){
                    $el->{$field} =($el->{$field}==0)?"F":"M";
                }
                push @$item,$el->{$field};
            }
            push @$retList,$item;
        }
    }
    return $retList;
}


#----------------------------------------------------------------------------------------------------
sub setOverDueListByGrp{
    my ($sort1)=@_;
    if ( $sort1 eq 'username' ){
        $template->param(userlist => 1);
    }
    elsif ( $sort1 eq 'teacher' ){
        $template->param(teacherlist => 1);
    }
    elsif ( $sort1 eq 'homeroom' ){
        $template->param(homeroomlist => 1);
    }
    elsif ( $sort1 eq 'grade' ){
        $template->param(gradelist => 1);
    }

}  
   
#----------------------------------------------------------------------------------------------------
sub GetOverdueTeacherList
{    

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

    my @roomlist = ();
    my @teacherlist = ();
    my @bdinglist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct trim(homeroom) as  homeroom, trim(buildingcode) as buildingcode, trim(teacher) as teacher from opl_user";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            if ( !$rec->{'homeroom'} ) { $rec->{'homeroom'} = ''; }
            if ( !$rec->{'buildingcode'} ) { $rec->{'buildingcode'} = ''; }
            if ( !$rec->{'teacher'} ) { $rec->{'teacher'} = ''; }

            push @roomlist, $rec->{'homeroom'};
            push @bdinglist, $rec->{'buildingcode'};
            push @teacherlist, $rec->{'teacher'};
        }
        $query->finish;
    }
    else
    {
        @roomlist = split /\$/, $input->{'roomlist'};
        @bdinglist = split /\$/, $input->{'bdinglist'};
        @teacherlist = split /\$/, $input->{'teacherlist'};

        if ( scalar(@roomlist) == 0 ) { push @roomlist, ''; }
        if ( scalar(@bdinglist) == 0 ) { push @bdinglist, ''; }
        if ( scalar(@teacherlist) == 0 ) { push @teacherlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom,trim(buildingcode) as  buildingcode, username, userbarcode,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey,
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && trim(homeroom)=? 
        && trim(buildingcode)=? && trim(teacher)=?";

    
    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@roomlist);
    
    for (my $i=0; $i<$nList; $i++)
    {
        if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
        if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
        if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }
        
        my $bResult = $query->execute(trim($roomlist[$i]), trim($bdinglist[$i]), trim($teacherlist[$i]));
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data
            $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
            $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            $loan->{'price'}  =~ s/[a-zA-Z]//g ;

            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                phone           => $loan->{'phone'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                uid             => $loan->{'uid'},
            };
        }
        $query->finish;
                
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();
            SecondSort(\@data, \@loanRecs, $sort2);

            push @DataRecs, { group => \@data, teacher => trim($teacherlist[$i]), hmroom => trim($roomlist[$i]), 
                        building => trim($bdinglist[$i]) };
        }
        @loanRecs = ();
    }

    return \@DataRecs;
    #$template->param(ItemsInLoan => \@DataRecs);
    $template->param(test=> $input->{'roomlist'});
}

#----------------------------------------------------------------------------------------------------
sub GetOverdueHomeroomList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;

    my @roomlist = ();
    my @teacherlist = ();
    my @bdinglist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, trim(teacher) as teacher from opl_user order by buildingcode, homeroom, teacher";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            if ( !$rec->{'homeroom'} ) { $rec->{'homeroom'} = ''; }
            if ( !$rec->{'buildingcode'} ) { $rec->{'buildingcode'} = ''; }
            if ( !$rec->{'teacher'} ) { $rec->{'teacher'} = ''; }

            push @roomlist, $rec->{'homeroom'};
            push @bdinglist, $rec->{'buildingcode'};
            push @teacherlist, $rec->{'teacher'};
        }
        $query->finish;
    }
    else
    {
        @roomlist = split /\$/, $input->{'roomlist'};
        @bdinglist = split /\$/, $input->{'bdinglist'};
        @teacherlist = split /\$/, $input->{'teacherlist'};

        if ( scalar(@roomlist) == 0 ) { push @roomlist, ''; }
        if ( scalar(@bdinglist) == 0 ) { push @bdinglist, ''; }
        if ( scalar(@teacherlist) == 0 ) { push @teacherlist, ''; }
    }

    my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        l.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i 
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && trim(homeroom)=?
        && trim(buildingcode)=? && trim(teacher)=?";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);

    my $query = $dbh->prepare($szSQL);
    my @DataRecs;# = ();
    my $nList = scalar(@roomlist);
    
    for (my $i=0; $i<$nList; $i++)
    {
        if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
        if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
        if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }

        my $bResult = $query->execute(trim($roomlist[$i]),trim($bdinglist[$i]), trim($teacherlist[$i]));
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data
            $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
            $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            $loan->{'price'}  =~ s/[a-zA-Z]//g ;

            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                phone           => $loan->{'phone'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                uid             => $loan->{'uid'},
            };

        }                    
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();

            SecondSort(\@data, \@loanRecs, $sort2);
            push @DataRecs, { group => \@data, teacher => trim($teacherlist[$i]), hmroom => trim($roomlist[$i]), 
                            building => trim($bdinglist[$i]) };
        }
        @loanRecs = ();
    }
    return \@DataRecs;
    #$template->param(ItemsInLoan => \@DataRecs);
}


            

#--------------------------------------------------------
sub GetOverdueGradeList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    
    my @gradelist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct grade from opl_user order by grade";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @gradelist, $rec->{'grade'};
        }
        $query->finish;
    }
    else
    {
        @gradelist = split /\$/, $input->{'gradelist'};
        if ( scalar(@gradelist) == 0 ) { push @gradelist, ''; }
    }
   my $szSQL = "
select  trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && grade=?";

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@gradelist);
    
    for (my $i=0; $i<$nList; $i++)
    {
        my $gradeLevel=$gradelist[$i];
        if ( $gradelist[$i] eq 'none' ) { $gradeLevel = ''; }

        my $bResult = $query->execute($gradeLevel);
 
        my @loanRecs = ();
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data
            $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
            $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            $loan->{'price'}  =~ s/[a-zA-Z]//g ;

             push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                homeroom        => $loan->{'homeroom'},
                buildingcode    => $loan->{'buildingcode'}, 
                teacher         => $loan->{'teacher'},
                deltaDueDate    => $loan->{'deltaDueDate'}, 
                uid        => $loan->{'uid'},
                phone           => $loan->{'phone'},
            };
        }
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();

            SecondSort(\@data, \@loanRecs, $sort2);
            push @DataRecs, { group => \@data, grade => $gradelist[$i] };
        }
        @loanRecs = ();
    }

    return \@DataRecs;
  #  $template->param(ItemsInLoan => \@DataRecs);
}



#-------------------------------------------------------------------------
sub GetOverdueUserList
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    
    my @idlist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct uid from opl_user order by lastname, firstname, uid";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @idlist, $rec->{'uid'};
        }
        $query->finish;
    } 
    else
    {
        @idlist = split /\$/, $input->{'idlist'};
        if ( scalar(@idlist) == 0 ) { push @idlist, ''; }
    }

    my $szSQL = "
select  u.sid,trim(teacher) as teacher, grade, trim(homeroom) as homeroom, trim(buildingcode) as buildingcode, username, userbarcode,yeargraduation,
        u.uid, firstname, lastname, phone, i.barcode, i.price, dateLoan, dateDue,
        m.title, m.author, m.pubDate, m.pubName, i.callNumber as dewey, 
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user as u, opl_loan as l, opl_marcRecord as m, opl_item as i
where   u.uid = l.uid && dateReturn is null 
        && l.barcode=i.barcode && i.rid=m.rid && l.uid=?  
        && to_days(dateDue) >= to_days('$dateFrom')   
        && to_days(dateDue) <= to_days('$dateTo')
        && to_days(dateDue) < to_days(now())";

    if ( $orderby eq 'name' ) { $szSQL .= " order by lastname, firstname, username"; }
    elsif(!$orderby || $orderby eq '') { $szSQL .= " order by homeroom, lastname, firstname, username"; }
    else{$szSQL .= " order by $orderby"; }
 

    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@idlist);

    for (my $i=0; $i<$nList; $i++)
    {
       # if ( $idlist[$i]==0 ) { $idlist[$i] = ''; }
        my $bResult = $query->execute($idlist[$i]);
        my @loanRecs = ();
        my ($sid,$lastname,$firstname,$username,$grade,$homeroom,$building,$teacher,$yeargraduation);
        while ( my $loan = $query->fetchrow_hashref() )
        {
# Format each row of data

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            $loan->{'price'}  =~ s/[a-zA-Z]//g ;

            ($sid,$lastname,$firstname,$username,$grade,$homeroom,$building,$teacher,$yeargraduation)=(
                                                                           $loan->{'sid'},
                                                                           $loan->{'lastname'},
                                                                           $loan->{'firstname'},
                                                                           $loan->{'username'},
                                                                           $loan->{'grade'},
                                                                           $loan->{'homeroom'},
                                                                           $loan->{'buildingcode'},
                                                                           $loan->{'teacher'},
                                                                           $loan->{'yeargraduation'}
                                                                           );
            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},   
                yeargraduation  => $loan->{'yeargraduation'},   
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                homeroom        => $loan->{'homeroom'},
                buildingcode    => $loan->{'buildingcode'}, 
                teacher         => $loan->{'teacher'},
                deltaDueDate    => $loan->{'deltaDueDate'}, 
                uid             => $loan->{'uid'},
                phone           => $loan->{'phone'},
            };

        }                    
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();
            SecondSort(\@data, \@loanRecs, $sort2);
            foreach my $rec (@data){
                $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
                $rec->{'dateDue'}  = date_text($rec->{'dateDue'}, 0);
            }
            push @DataRecs, { group => \@data,sid=>$sid,homeroom=>$homeroom, building=>$building, teacher=>$teacher, grade=>$grade,  
                              yeargraduation=>$yeargraduation,lastname=>$lastname, firstname =>$firstname,username=>$username };
        }
        @loanRecs = ();
    }

    return \@DataRecs;
   # $template->param(ItemsInLoan => \@DataRecs);

}

#----------------------------------------------------------
sub trimArticle{
    my ($str)=@_;
    $str =~ s/^a |^an |^the //i;
    return $str;
}

#----------------------------------------------------------
sub SecondSort
{
    my ($data, $loanRecs, $sort2) = @_;
    
    if ( $sort2 eq "username" )
    { 
        @$data = sort { $a->{"lastname"} cmp $b->{"lastname"} 
                        || $a->{"firstname"} cmp $b->{"firstname"} 
                        || $a->{"uid"} <=> $b->{'uid'} } @$loanRecs; 
    }
    else { @$data = sort { $a->{$sort2} cmp $b->{$sort2} } @$loanRecs; }

}

#--------------------------------------------------------
sub AddDateConstraint
{
    my ($szSQL, $dateFrom, $dateTo) = @_;
    $szSQL = $szSQL . " && to_days(dateDue) >= to_days('$dateFrom')";
    $szSQL = $szSQL . " && to_days(dateDue) <= to_days('$dateTo')";
    $szSQL = $szSQL . " && dateDue < now()";
    return $szSQL;
}


####################################################
sub trim{
    my $str = shift;
    $str =~ s/^\s+//;
    $str =~ s/\s+$//;
    return $str;

}