#!/usr/bin/perl

use strict;

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

use Time::localtime;

use Opals::Context;

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

use constant    X_PAGE         =>  612;
use constant    X_LEFT_MARGIN  =>  25;
use constant    Y_PAGE         =>  792;
use constant    X_TABLE        =>  558;
use constant    ROW_HEIGHT     =>  18;
use constant    ROW_HEIGHT_10  =>  12;
use constant    X_ADDRESS      =>  61;
use constant    Y_ADDRESS      =>  144;

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/odnotice_prt.tmpl',
        #reqPermission   => 'tb_notice',
    }
);


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 $libName2print = $input->{'libName'};
$libName2print =~ s/^\s+|\s+$//g;

if ($libName2print ne "" && $libName2print ne $libname){
    $libname = $libName2print;
}

my $mypdf=`/bin/mktemp`;
    my $pdf = new PDF::Create('filename' => $mypdf,
                              'Version'  => 1.2,
                              'PageMode' => 'UseOutlines',
                              'Title'    => 'Loaned textbook notice',
                         );
    my $root = $pdf->new_page('MediaBox' => [ 0, 0, X_PAGE,Y_PAGE ]);
    my $f1 = $pdf->font('Subtype'  => 'Type1',
                        'Encoding' => 'WinAnsiEncoding',
                        'BaseFont' => 'Helvetica');
    my $f2 = $pdf->font('Subtype'  => 'Type1',
                        'Encoding' => 'WinAnsiEncoding',
                        'BaseFont' => 'Helvetica-Bold');
    my $f3 = $pdf->font('Subtype'  => 'Type1',
                        'Encoding' => 'WinAnsiEncoding',
                        'BaseFont' => 'Times-Italic');

    my $messages= {
        title=>{en=>'Textbook On Loan Reminder',
                fr=>'Avis de la bibliothèque',
                sp=>'Carta de notificación de tardía'
                },
        messageTo=>{
                en=>'To the parents or guardians of:',
                fr=>'Aux parents de:',
                sp=>'A los padres o guardián de:'
        },
        message1=>{
                en=>'Textbook items on loan ',
                fr=>'Textbook items on loan ' ,
                sp=>'Textbook items on loan '
                },
        
        };

    my $itemTblHeader= {
        title=>{
            en=>'Title',
            fr=>'Titre',
            sp=>'Título'
            },
        barcode=>{
            en=>'Barcode',
            fr=>'#Code',
            sp=>'Código'
            },
        price=>{
            en=>'Price',
            fr=>'Prix',
            sp=>'Precio'
            },
        course=>{
            en=>'Course',
            fr=>'Cours',
            sp=>'Curso'
        },
        courseteacher=>{
            en=>'Course Teacher',
            fr=>'Profeseur de cours',
            sp=>'Curso profesor'
        },
        dateLoan=>{
            en=>'Loan Date',
            fr=>'Loan Date ',
            sp=>'Feche de vencimiento'
            },

        dateDue=>{
            en=>'Due Date',
            fr=>'Date d\'échéance',
            sp=>'Feche de vencimiento'
            },
        overdue=>{
            en=>'Overdue',
            fr=>'En retard',
            sp=>'Dias'
            }

    };
    foreach my $key(keys %{$messages}){
        foreach my $l('en','fr','sp'){
            $messages->{$key}->{$l}=decode('utf8',$messages->{$key}->{$l} );
        }
    }

    foreach my $key(keys %{$itemTblHeader}){
        foreach my $l('en','fr','sp'){
            $itemTblHeader->{$key}->{$l}=decode('utf8',$itemTblHeader->{$key}->{$l} );
        }
    }

my $dateFrom   = $input->{'dateFrom'};
my $dateTo     = $input->{'dateTo'};
my $sort1      = $input->{'sort1'};
my $sort2      = $input->{'sort2'};
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 $categorycode = $input->{'categorycode'} || 0;

my $msg      = decode('utf8',$input->{'msg'});
$msg =~ s/_EOL_/\n/;
$msg =~ s/\n+/\n/;
my @tmpArr  = split /\n/, $msg;
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 @csvHeader=qw(
    TEACHER
    GRADE
    HOMEROOM
    BUILDINGCODE
    LASTNAME
    FIRSTNAME
    USERBARCODE
    ADDRLINE1      
    ADDRLINE2      
    CITY           
    ZIP            
    STATE          
    EMAIL          
    YEARGRADUATION
    COURSE
    COURSETEACHER
    TITLE
    BARCODE
    PRICE
    CALLNUMBER
    DATELOAN
    DATEDUE
    OD_DAYS
);
my @fields=qw(
    teacher
    grade
    homeroom
    buildingcode
    lastname
    firstname
    userbarcode
    addrLine1      
    addrLine2      
    city           
    zip            
    state          
    email          
    yeargraduation
    c_coursename
    c_teachername
    title
    barcode
    price
    callNumber
    dateLoan
    dateDue
    deltaDateDue
);

my $pageN ;
$pageN = $root->new_page;
my ($ii,$nLine)=(0,1) ;
   while($ii < scalar(@tmpArr) && $nLine<2  ){
      my $tmpMsg=formatStr_3($pageN,$f3,10,@tmpArr[$ii],X_TABLE);
      push @msgArr, $tmpMsg;
      my $remainStr=@tmpArr[$ii];
      if(length($remainStr) > length($tmpMsg) && $nLine++ <2) {
           $remainStr=substr($remainStr,length($tmpMsg),length($remainStr) - length($tmpMsg));
           $remainStr=~ s/^ +//g;
           $remainStr=~ s/^[\s]+//g;
           $tmpMsg=formatStr_3($pageN,$f3,10,$remainStr,X_TABLE);
           push @msgArr,$tmpMsg;
       }
      $ii++;
   }
   foreach my $l(@lang){
       push @msgArr, $messages->{'message1'}->{$l};
   } 
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 ($input->{'op'} eq 'exportCSV'){
    my $odCSV  = getOverdueCSV($dbh,$sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
    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(@$odCSV){
        print "\"",  join("\",\"",@$row), "\"\n";
    }
}
else {
   #doGetOverdueList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
   getLoanList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
   if($input->{'headers'} eq 'true'){
        $template->param(includeHeaders => 1);
   }
   if ( $input->{'list'} ) {
        tmpl_write($dbh, $cgi, $cookieList, $template); 
   }
   else{
        open PDF, "<$mypdf";
        print $cgi->header(
            -type           => 'application/pdf',
            -attachement    => 'OnloanNotice.pdf'
        );
#    print "Content-type: application/pdf\n\n";
        while (<PDF>) {
            print $_;
        }
        close PDF;
   }
}
#------------------------------------------------------------------
sub getLoanList {

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    if ( $sort1 eq 'username'){
        if ($input->{'list'}){
            my $list = getOverdue_userList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
            $template->param(
                idList => 1,
                tbOverdueUser => $list
            );
        }
        else{
            getLoanItems_userNotice($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
            $template->param(odnotice => 1);
        }
    }
    elsif($sort1 eq 'teacher' ){
        if ($input->{'list'}){
            my $list = getOverdue_teacherList($dbh,$sort1,$sort2,$dateFrom, $dateTo, $input);
            $template->param(
                teacherList => 1,
                tbOverdueTeacher => $list
            );
        }
        else{
            getLoanItems_teacherNotice($dbh, $sort1,$sort2,$dateFrom, $dateTo, $input);
            $template->param(odnotice => 1);
        }
    }
    elsif ($sort1 eq 'homeroom'){
        if ($input->{'list'}){
            my $list = getOverdue_homeroomList($dbh,$sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(
                homeroomList => 1,
                tbOverdueHomeroom => $list
            );
        }
        else{
            getLoanItems_homeroomNotice($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(odnotice => 1);
        }
    }
    elsif ($sort1 eq 'grade'){
        if ($input->{'list'}){
            my $list = getOverdue_gradeList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(
                gradeList => 1,
                tbOverdueGrade => $list
            );
        }
        else{
            getLoanItems_gradeNotice($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(odnotice => 1);
        }
    }
    elsif ($sort1 eq 'title'){
        if ($input->{'list'}){
            my $list = getOverdue_titleList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(
                recIdList => 1,
                tbOverdueTitle => $list
            );
        }
        else{
            getLoanItems_titleNotice($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(odnotice => 1);
        }
    }
    elsif ($sort1 eq 'course'){
        if ($input->{'list'}){
            my $list = getLoanItems_courseList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(
                courseList => 1,
                tbOverdueCourse => $list
            );
        }
        else{
            getLoanItems_courseNotice($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(odnotice => 1);
        }
    }
    elsif ($sort1 eq 'courseteacher'){
        if ($input->{'list'}){
            my $list = getLoanItems_courseTeacherList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(
                courseTeacherList => 1,
                tbOverdueCourseTeacher => $list);
        }
        else{
            getLoanItems_courseTeacherNotice($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(odnotice => 1);
        }
    }
}

sub getLoanItems_userList{
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderBy) = @_;
    my @idList = ();
    my $tbOverdue;

    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }

    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
        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') &&
                l.uid = ? ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode";
    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 (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}   = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;

    my @tbOverdueList = ();

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

sub getLoanItems_teacherList{

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

    my $tbOverdue;

    @teacherList = split /\$/, $input->{'teacherList'};    
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'}; 
    
    if (scalar(@teacherList) == 0){
        push @teacherList, '';}
    else{
        foreach my $teacher(@teacherList){
            $teacher =~ s/^\s+|\s+$//g;
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = '';
            }
        }
        foreach my $hr(@homeroomList){
            $hr =~ s/^\s+|\s+$//g;
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = '';
            }
        }
        foreach my $building(@buildingList){
            $building =~ s/^\s+|\s+$//g;
            if ($building eq " " || $building eq "None" || $building eq "N/A" || $building eq "undefined" ){
                $building = '';
            }
        }
    }
    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
        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') &&
                trim(u.teacher) = ? && trim(u.homeroom) = ?  && trim(u.buildingcode) = ?";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode order by u.teacher,u.buildingcode,u.homeroom ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for ( my $i = 0; $i < $nList; $i++){
        my $teacher =   $teacherList[$i]  eq ""? '' : $teacherList[$i] ;
        my $hr =        $homeroomList[$i] eq ""? '' : $homeroomList[$i] ;
        my $building =  $buildingList[$i] eq ""? '' : $buildingList[$i] ;
        $building = (!$building)?'': $building;
        $sth->execute($teacher,$hr,$building);
        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);
            $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 $teacherHrBld = $loan->{'teacher'} . $loan->{'homeroom'} . $loan->{'buildingcode'};
            if (!$tbOverdue->{$teacherHrBld}){
                $tbOverdue->{$teacherHrBld}->{'teachername'}    = $loan->{'teacher'};
                $tbOverdue->{$teacherHrBld}->{'homeroom'} = $loan->{'homeroom'};
                $tbOverdue->{$teacherHrBld}->{'buildingcode'} = $loan->{'buildingcode'};
            }
            push @{$tbOverdue->{$teacherHrBld}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(sort keys %{$tbOverdue}){
        secondSort ($tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    return \@tbOverdueList;
}

sub getLoanItems_gradeList {

    my ($dbh, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @gradeList = ();
    my $tbOverdue;
    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){
        push @gradeList, '';}
    else{
        foreach my $grade(@gradeList){
            $grade =~ s/^\s+|\s+$//g;
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }
    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
        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') &&
                u.grade = ? ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode order by concat(u.grade, 'zzzzzz' ), concat(u.lastname,u.firstname, 'zzzzzz')";
    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 (!$tbOverdue->{$loan->{'grade'}}){
                 $tbOverdue->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
            }
            push @{$tbOverdue->{$loan->{'grade'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    return \@tbOverdueList;
}

sub getLoanItems_homeroomList {

    my ($dbh, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @homeroomList = ();
    my @buildingList = ();
    my @teacherList = ();
    my $tbOverdue;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'}; 
    @teacherList = split /\$/, $input->{'teacherList'};  
    if (scalar(@homeroomList) == 0){
        push @homeroomList, '';}
    else{
        foreach my $teacher(@teacherList){
            $teacher =~ s/^\s+|\s+$//g;
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
        foreach my $hr(@homeroomList){
            $hr =~ s/^\s+|\s+$//g;
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            $building =~ s/^\s+|\s+$//g;
            if ($building eq " " || $building eq "None" || $building eq "N/A" || $building eq "undefined" ){
                $building = "";
            }
        }
    }

  
    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
        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') &&
                trim(u.homeroom) = ?  && trim(u.buildingcode) = ? && trim(u.teacher) = ? ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode order by  u.buildingcode,u.homeroom,u.teacher ";
    my $sth     = $dbh->prepare($sql);
    my $nList   = scalar(@homeroomList);
    for (my $i = 0; $i < $nList; $i++){
        my $hr = $homeroomList[$i] eq ""? '' : $homeroomList[$i] ;
        my $building = $buildingList[$i] eq ""? '' : $buildingList[$i] ;
        my $teacher = $teacherList[$i] eq ""? '' : $teacherList[$i] ;
        $sth->execute($hr,$building, $teacher);
        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);
            $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 $hrBldTeacher = $loan->{'homeroom'} . $loan->{'buildingcode'} . $loan->{'teacher'};
            if (!$tbOverdue->{$hrBldTeacher}){
                 $tbOverdue->{$hrBldTeacher}->{'homeroom'} = $loan->{'homeroom'};
                 $tbOverdue->{$hrBldTeacher}->{'buildingcode'} = $loan->{'buildingcode'};
                 $tbOverdue->{$hrBldTeacher}->{'teacher'} = $loan->{'teacher'};
            }
            push @{$tbOverdue->{$hrBldTeacher}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    return \@tbOverdueList;
}

sub getLoanItems_titleList {

    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @recIdList=();
    my $tbOverdue;
    my $totalLoan = 0;
    @recIdList = split /\$/,  $input->{'recIdList'};
    if (scalar(@recIdList) == 0){
        push @recIdList, '';}
    my $sql = "select i.rid,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
        inner join tb_items as i using (barcode)
        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') &&
                i.rid = ? ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode"; 

    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 (!$tbOverdue->{$loan->{'title'}}){
                $tbOverdue->{$loan->{'title'}}->{'title'} = $loan->{'title'};
                $tbOverdue->{$loan->{'title'}}->{'rid'} = $loan->{'rid'};
                $tbOverdue->{$loan->{'title'}}->{'price'} = $loan->{'price'};
            }
            push @{$tbOverdue->{$loan->{'title'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    
    my @tbOverdueList = ();

    foreach my $k( sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    return \@tbOverdueList;
}

sub getLoanItems_courseList {
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseCodeList=();
    my $tbOverdue;
    my $totalLoan = 0;
    @courseCodeList = split /\$/,  $input->{'courseCodeList'};
    if (scalar(@courseCodeList) == 0){
        push @courseCodeList, '';}
    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.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 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') &&
                cl.courseCode = ? ";
    $sql .= " group by l.barcode";

    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseCodeList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        if ( $courseCodeList[$i] ne "" && $courseCodeList[$i] ne "0"){
            $sth->execute($courseCodeList[$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 (!$tbOverdue->{$loan->{'couresCode'}}){
                    $tbOverdue->{$loan->{'courseCode'}}->{'courseCode'} = $loan->{'courseCode'};
                    $tbOverdue->{$loan->{'courseCode'}}->{'coursename'} = $loan->{'coursename'};
                }
                push @{$tbOverdue->{$loan->{'courseCode'}}->{'group'}}, $loan;
            }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
        my $sql_null = "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.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 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') &&
                    cl.courseCode is null ";
            if ( $categorycode && $categorycode > 0){
                $sql_null .= " && u.categorycode  = $categorycode"
            }
                    
            $sql .= " group by l.barcode ";
            $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 (!$tbOverdue->{$loan->{'couresCode'}}){
                $tbOverdue->{$loan->{'courseCode'}}->{'courseCode'} = $loan->{'courseCode'};
                $tbOverdue->{$loan->{'courseCode'}}->{'coursename'} = $loan->{'coursename'};
            }
            push @{$tbOverdue->{$loan->{'courseCode'}}->{'group'}}, $loan;
        }
    }
    
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort{$a->{'coursename'} cmp $b->{'coursename'}}  @tbOverdueList;
    return \@tbOverdueList;
}

sub getLoanItems_courseTeacherList {
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseTeacherIdList=();
    my $tbOverdue;
    my $totalLoan = 0;
    @courseTeacherIdList = split /\$/,  $input->{'courseTeacherIdList'};
    if (scalar(@courseTeacherIdList) == 0){
        push @courseTeacherIdList, '';}
    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 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 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') &&
                t.uid = ? ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode " ;
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseTeacherIdList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        if ( $courseTeacherIdList[$i] ne "" && $courseTeacherIdList[$i] ne "0"){
            $sth->execute($courseTeacherIdList[$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 (!$tbOverdue->{$loan->{'c_teacherId'}}){
                     $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teacherId'} = $loan->{'c_teacherId'};
                     $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teachername'} = $loan->{'c_teachername'};
                }
                push @{$tbOverdue->{$loan->{'c_teacherId'}}->{'group'}}, $loan;
            }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
        my $sql_null = "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 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 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') &&
                    t.uid is null";
        if ( $categorycode && $categorycode > 0){
            $sql_null .= " && u.categorycode  = $categorycode"
        }
                    
        $sql_null .=  " group by l.barcode " ;
            $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 (!$tbOverdue->{$loan->{'c_teacherId'}}){
                    $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teacherId'} = $loan->{'c_teacherId'};
                    $tbOverdue->{$loan->{'c_teacherId'}}->{'c_teachername'} = $loan->{'c_teachername'};
                }
            push @{$tbOverdue->{$loan->{'c_teacherId'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort{$a->{'c_teachername'} cmp $b->{'c_teachername'}}  @tbOverdueList;
    return \@tbOverdueList;
}

sub getLoanItems_userNotice{
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderBy) = @_;
    my @idList = ();
    my $tbOverdue;

    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }

     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
        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.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                l.uid = ?  ";
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
    $sql .= " group by l.barcode";
    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 (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}= $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    if ( $orderBy eq 'room' ){
        @tbOverdueList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} } @tbOverdueList;
    }
    else{
        @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    }
    printOd_pdf(\@tbOverdueList);
}

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

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

    if (scalar(@teacherList) == 0){
        push @teacherList, '';}
    else{
        foreach my $teacher(@teacherList){
            $teacher =~ s/^\s+|\s+$//g;
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
        foreach my $hr(@homeroomList){
            $hr =~ s/^\s+|\s+$//g;
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            $building =~ s/^\s+|\s+$//g;
            if ($building eq " " || $building eq "None" || $building eq "N/A"){
                $building = "";
            }
        }
    }
    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, 
        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   l.dateReturn is null &&    
                to_days(l.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                u.teacher = ?  && u.homeroom = ?  && u.buildingcode = ?";
        if ( $categorycode && $categorycode > 0){
            $sql .= " && u.categorycode  = $categorycode"
        }
        $sql .= " order by u.teacher,u.buildingcode, u.homeroom";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for ( my $i = 0; $i < $nList; $i++){
        my $teacher =   $teacherList[$i]  eq ""? '' : $teacherList[$i] ;
        my $hr =        $homeroomList[$i] eq ""? '' : $homeroomList[$i] ;
        my $building =  $buildingList[$i] eq ""? '' : $buildingList[$i] ;
        $building = (!$building)?'': $building;
        $sth->execute($teacher,$hr,$building);    
        #$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";
            }
            if (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}      = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'}= $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}    = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}    = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}= $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}= $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}     = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}      = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}    = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
   #@tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
   printOd_pdf(\@tbOverdueList);
}

sub getLoanItems_homeroomNotice {
    my ($dbh, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @homeroomList = ();
    my @buildingList = ();
    my @teacherList = ();
    my $tbOverdue;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    @buildingList = split /\$/, $input->{'buildingList'}; 
    @teacherList = split /\$/, $input->{'teacherList'};  

    if (scalar(@homeroomList) == 0){
        push @homeroomList, '';}
    else{
        foreach my $hr(@homeroomList){
            $hr =~ s/^\s+|\s+$//g;
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
        foreach my $building(@buildingList){
            $building =~ s/^\s+|\s+$//g;
            if ($building eq " " || $building eq "None" || $building eq "N/A"){
                $building = "";
            }
        }
        foreach my $teacher(@teacherList){
            $teacher =~ s/^\s+|\s+$//g;
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }
    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, 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.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                u.homeroom = ?  && u.buildingcode = ? && u.teacher = ? ";
        if ( $categorycode && $categorycode > 0){
            $sql .= " && u.categorycode  = $categorycode"
        }
        $sql .= " order by concat(u.homeroom, 'zzzzzz' ), concat(u.buildingcode, 'zzzzzz'), concat(u.teacher,'zzzzzz') ";
            
    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 (!$tbOverdue->{$loan->{'uid'}}){
                $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
         }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( sort keys %{$tbOverdue}){
        #secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort { $a->{'homeroom'} cmp $b->{'homeroom'} ||  $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    printOd_pdf(\@tbOverdueList);
}

sub getLoanItems_gradeNotice{
    
    my ($dbh, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @gradeList = ();
    my $tbOverdue;
    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){
        push @gradeList, '';}
    else{
        foreach my $grade(@gradeList){
            $grade =~ s/^\s+|\s+$//g;
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }
    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,
        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.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                u.grade = ? ";
        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);
    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 (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k(sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    printOd_pdf(\@tbOverdueList);
}

sub getLoanItems_titleNotice{
    
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @recIdList=();
    my $tbOverdue;
    my $totalLoan = 0;
    @recIdList = split /\$/,  $input->{'recIdList'};
    if (scalar(@recIdList) == 0){
        push @recIdList, '';}
    my $sql = "select i.rid,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,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
        inner join tb_items as i using (barcode)
        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.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                i.rid = ?" ;
        if ( $categorycode && $categorycode > 0){
            $sql .= " && u.categorycode  = $categorycode"
        }

    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 (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( sort keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    printOd_pdf(\@tbOverdueList);
}

sub getLoanItems_courseNotice{
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseCodeList=();
    my $tbOverdue;
    my $totalLoan = 0;
    @courseCodeList = split /\$/,  $input->{'courseCodeList'};
    if (scalar(@courseCodeList) == 0){
        push @courseCodeList, '';}
    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,t.uid 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 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.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                cl.courseCode = ?"              ;
   if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
             
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseCodeList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        if ( $courseCodeList[$i] ne "" && $courseCodeList[$i] ne " " && $courseCodeList[$i] ne "0"){
            $sth->execute($courseCodeList[$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 (!$tbOverdue->{$loan->{'uid'}}){
                     $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                     $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                     $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                     $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                     $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                     $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                     $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                     $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                     $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                     $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                     $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                     $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                     $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                     $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
                }
                push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
           }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
        my $sql_null = "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, t.uid 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 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') &&
                    cl.courseCode is null" ;
            if ( $categorycode && $categorycode > 0){
                $sql_null .= " && u.categorycode  = $categorycode"
            }
        
            $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->{'t_teachername'}    = $loan->{'t_teachername'};
            $loan->{'courseCode'} = " N/A";
            $loan->{'coursename'} = " N/A";
            if (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    printOd_pdf(\@tbOverdueList);
}

sub getLoanItems_courseTeacherNotice{
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;
    my @courseCodeList=();
    my $tbOverdue;
    my $totalLoan = 0;
    @courseCodeList = split /\$/,  $input->{'courseTeacherIdList'};
    if (scalar(@courseCodeList) == 0){
        push @courseCodeList, '';}
    my $sql = "select distinct l.barcode as barcode, l.dateLoan as dateLoan,l.dateDue as dateDue, l.dateReturn as dateReturn,l.teacherName as l_teacherName,
        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,
        t.uid 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 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.dateLoan) >= to_days('$dateFrom') &&
                to_days(l.dateLoan) <= to_days('$dateTo') &&
                l.teacherName =?" ;
#                t.uid = ?" ;
    if ( $categorycode && $categorycode > 0){
        $sql .= " && u.categorycode  = $categorycode"
    }
            
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@courseCodeList);
    my $flagNull = 0;
    for (my $i = 0; $i < $nList ; $i++){
        $courseCodeList[$i] = lc($courseCodeList[$i]) eq 'n/a'?'': $courseCodeList[$i];
        if ( $courseCodeList[$i] ne "" && $courseCodeList[$i] ne " " && $courseCodeList[$i] ne "0"){
            $sth->execute($courseCodeList[$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 (!$tbOverdue->{$loan->{'uid'}}){
                     $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                     $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                     $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                     $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                     $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                     $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                     $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                     $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                     $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                     $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                     $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                     $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                     $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                     $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
                }
                push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
           }
        }
        else{
            $flagNull = 1;
        }
    }
    if ($flagNull ){
=item      
        my $sql_null = "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,t.uid 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 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') &&
                    t.uid is null" ;
        if ( $categorycode && $categorycode > 0){
            $sql_null .= " && u.categorycode  = $categorycode"
        }
=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 || 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->{'t_teachername'}    = $loan->{'t_teachername'};
            $loan->{'courseCode'} = " N/A";
            $loan->{'coursename'} = " N/A";
            if (!$tbOverdue->{$loan->{'uid'}}){
                 $tbOverdue->{$loan->{'uid'}}->{'uid'}       = $loan->{'uid'};
                 $tbOverdue->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                 $tbOverdue->{$loan->{'uid'}}->{'lastname'}  = $loan->{'lastname'};
                 $tbOverdue->{$loan->{'uid'}}->{'userbarcode'}  = $loan->{'userbarcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'grade'}     = $loan->{'grade'};
                 $tbOverdue->{$loan->{'uid'}}->{'homeroom'}  = $loan->{'homeroom'};
                 $tbOverdue->{$loan->{'uid'}}->{'buildingcode'}  = $loan->{'buildingcode'};
                 $tbOverdue->{$loan->{'uid'}}->{'teacher'}  = $loan->{'teacher'};
                 $tbOverdue->{$loan->{'uid'}}->{'phone'}  = $loan->{'phone'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine1'}  = $loan->{'addrLine1'};
                 $tbOverdue->{$loan->{'uid'}}->{'addrLine2'}  = $loan->{'addrLine2'};
                 $tbOverdue->{$loan->{'uid'}}->{'city'}  = $loan->{'city'};
                 $tbOverdue->{$loan->{'uid'}}->{'zip'}  = $loan->{'zip'};
                 $tbOverdue->{$loan->{'uid'}}->{'state'}  = $loan->{'state'};
            }
            push @{$tbOverdue->{$loan->{'uid'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @tbOverdueList = ();
    foreach my $k( keys %{$tbOverdue}){
        secondSort ( $tbOverdue->{$k}->{'group'}, $sort2);
        push @tbOverdueList, $tbOverdue->{$k};
    }
    @tbOverdueList = sort { $a->{'lastname'} cmp $b->{'lastname'} } @tbOverdueList;
    printOd_pdf(\@tbOverdueList);
}

sub OptionPrintNotice
{
    my ($dbh, $template) = @_;
    
    my $optNotice = Opals::Context->preference('reportGroup');

    my @arrOpt = split /,/, $optNotice;
    my @arrPrint = ();

    foreach my $rec (@arrOpt)
    {
        $rec =~ s/^\s+//;
	    $rec =~ s/\s+$//;
        if ( $rec == 1 )
            { push @arrPrint, {reportHomeroom => 1}; }
        elsif ( $rec == 2 )
            { push @arrPrint, {reportTeacher => 2}; }
        elsif ( $rec == 3 )
            { push @arrPrint, {reportGrade => 3}; }
        else
            { push @arrPrint, {reportPhone => 4}; }
    }
    $template->param(optPrn => \@arrPrint);
}


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

sub printOd_pdf{
    my($odData)=@_; 
    #my $pageN  ;
    my $subPageCount = 1;
    my $xRow=4;
    my ($x,$y);
    my $totalPage=0;
    my $rowPerPage;
    my $printAdd=0;
    if($prtType eq 'address'){
        $printAdd=1;
    }
    
    if($optPage ==1){
       $rowPerPage=38; 
       #$printAdd=1;
    }
    elsif($optPage ==2){
        $rowPerPage=16;
    }
    elsif($optPage ==3){
        $rowPerPage=9;
    }
    else{
        $rowPerPage=38;
        $optPage =1;
        #$printAdd=1;
    }
    my $i=0;
    for( $i=0; $i<scalar(@$odData); $i++){
       my $pNo=1;
       $xRow=4;
       if((($subPageCount % $optPage)==1 || ($optPage==1)) && $i>0){
          $pageN = $root->new_page;
       }
      $x = X_LEFT_MARGIN;
      $y = (Y_PAGE - $subPageCount*(Y_PAGE/$optPage)) + 40  ;
      my $aaa=@$odData[$i]->{'group'};

       my $paddingRows=  scalar(@msgArr)  + scalar(@lang) ;
       $paddingRows += 4 if($printAdd);
       $totalPage = floor((scalar (@$aaa)-$rowPerPage+$xRow + $paddingRows )/($rowPerPage)) + 1;
       my $remainingRows=(scalar (@$aaa)-$rowPerPage+$xRow + $paddingRows)%($rowPerPage);
       if ($remainingRows > 0){
           $totalPage += 1; 
       }
       if($remainingRows==0 || $rowPerPage - $remainingRows < scalar(@lang)  + $printAdd*6 ){
           $totalPage +=1 ; 
       }
       $pageN->stringr($f1, 10,580, $y-20 , 'Page ' . $pNo .'/' . $totalPage);
       $pageN->line(0, ($optPage-$subPageCount)*(Y_PAGE/$optPage), X_PAGE, ($optPage-$subPageCount)*(Y_PAGE/$optPage)); 

       printHeader($pageN,$f1,
                   @$odData[$i],
                   $x,$y,
                   $printAdd
                );
       $xRow += ceil((ROW_HEIGHT_10/ROW_HEIGHT) *scalar(@msgArr));
       my($x1,$y1,$x2,$y2);
       my $heightTbl;
       $heightTbl= ($rowPerPage -$xRow)* ROW_HEIGHT;
       ($x1,$y1,$x2,$y2)=($x,$y+$heightTbl - 18  ,$x+X_TABLE,$y+ $heightTbl+ ROW_HEIGHT - 18 );
       if($printAdd){
           $y1 = $y1 - Y_ADDRESS + 50 - (scalar(@lang))*ROW_HEIGHT_10;
           $y2 = $y2 - Y_ADDRESS + 50 - (scalar(@lang))*ROW_HEIGHT_10 ;
           $xRow  += ceil((ROW_HEIGHT_10/ROW_HEIGHT)*4) +  scalar(@lang);
       }
       $pageN->line($x1, $y2+2, $x2, $y2+2);
       $pageN->line($x1, $y1 - (scalar(@lang)-1)*ROW_HEIGHT, $x2, $y1-(scalar(@lang)-1)*ROW_HEIGHT);
       my $font=$f2;
       foreach my $l(@lang){
            writeHeaderRow($pageN,$font,10,$x1,$y1,$x2,$y2+2,$l);
            $y1 -= ROW_HEIGHT;
            $font=$f3;
            #$xRow +=1;
       }
       $y2 =$y1 + ROW_HEIGHT;
       #$y1 -= ROW_HEIGHT;
       my $j=0;     
       $xRow  +=1; 
       for($j=0; $j<$rowPerPage - $xRow && $j < scalar(@$aaa); $j++){
           writeRow($pageN,$x1,$y1,$x2,$y2,@$aaa[$j]);
           $y1 -= ROW_HEIGHT;
           $y2 -= ROW_HEIGHT;
       }
       $subPageCount = ($subPageCount == $optPage)?1:$subPageCount+1;
       for( $j=$rowPerPage-$xRow; $j<scalar(@$aaa); $j++){
            if((($j - $rowPerPage+$xRow) % ($rowPerPage))==0){
               if(($subPageCount % $optPage)==1 || ($optPage==1)){
                  $pageN = $root->new_page;
                  $xRow=0;
               }
               $y = (Y_PAGE - $subPageCount*(Y_PAGE/$optPage)) + 40;
                         
               if($subPageCount<$optPage && $optPage >1 ){
                  $pageN->line(0, ($optPage-$subPageCount)*(Y_PAGE/$optPage), X_PAGE, ($optPage-$subPageCount)*(Y_PAGE/$optPage)); 
               }
              $subPageCount = ($subPageCount == $optPage)?1:$subPageCount+1;
              $pNo +=1 ;
              $heightTbl= ($rowPerPage )* ROW_HEIGHT;
              ($x1,$y1,$x2,$y2)=($x,$y+ $heightTbl,$x+X_TABLE,$y+ $heightTbl+ ROW_HEIGHT);
               $pageN->stringl($f2, 10,$x, $y1 +30, "@$odData[$i]->{'lastname'}, @$odData[$i]->{'firstname'}");
               my $sWidth= 10 * $pageN->string_width($f2,"@$odData[$i]->{'lastname'}, @$odData[$i]->{'firstname'} ");
               $pageN->stringl($f1, 10,$x+$sWidth, $y1 +30, " User Barcode: @$odData[$i]->{'userbarcode'}");
               $pageN->stringr($f1, 10,580, $y-20 , 'Page ' . $pNo .'/' . $totalPage);
               ##writeHeaderRow($pageN,$x1,$y1,$x2,$y2+2);
               $y2 =$y1;
               $y1 -= ROW_HEIGHT;
               
           }# if(($j % 10)==0)
           writeRow($pageN,$x1,$y1,$x2,$y2,@$aaa[$j]);
           $y1 -= ROW_HEIGHT;
           $y2 -= ROW_HEIGHT;
       }#for(my $j=0; $j<scalar(@txt); $j++)
       my $ySign = ($prtType eq 'address')? 75 :0;
       if( $y1< $ySign + 5 + ROW_HEIGHT_10 *  scalar(@lang)){
               if(($subPageCount % $optPage)==1 || ($optPage==1)){
                  $pageN = $root->new_page;
               }
               $y = (Y_PAGE - $subPageCount*(Y_PAGE/$optPage)) + 40;
               if($subPageCount<$optPage && $optPage >1 ){
                  $pageN->line(0, ($optPage-$subPageCount)*(Y_PAGE/$optPage), X_PAGE, ($optPage-$subPageCount)*(Y_PAGE/$optPage)); 
               }
              $subPageCount = ($subPageCount == $optPage)?1:$subPageCount+1;
              $pNo +=1 ;
              $heightTbl= ($rowPerPage )* ROW_HEIGHT;
               ($x1,$y1,$x2,$y2)=($x,$y+ $heightTbl,$x+X_TABLE,$y+ $heightTbl+ ROW_HEIGHT);
               $pageN->stringl($f2, 10,$x, $y1 +30, "@$odData[$i]->{'lastname'}, @$odData[$i]->{'firstname'}");
               my $sWidth= 10 * $pageN->string_width($f2,"@$odData[$i]->{'lastname'}, @$odData[$i]->{'firstname'} ");
               $pageN->stringl($f1, 10,$x+$sWidth, $y1 +30, " User Barcode: @$odData[$i]->{'userbarcode'}");
               $pageN->stringr($f1, 10,580, $y-20 , 'Page ' . $pNo .'/' . $totalPage);
       }
       my @msgBArr;
       foreach my $l(@lang){
           push @msgBArr, $messages->{'message2'}->{$l};
       }   
       foreach my $msgB (@msgBArr){   
            $pageN->stringl($f1, 10,$x, $y1 - 10, $msgB);
             $y1 -=ROW_HEIGHT_10;
       }
        $y1 -= $ySign;
       if($ySign>0){ $pageN->line(300, $y1, 480,$y1);} 
     } #for( $i=0; $i<scalar(@$odData); $i++)
  
    $pdf->close;

}
sub printHeader{
    my($pageN,$f1,$odData,$x,$y,$printAdd)=@_;
    
    my $optNotice = Opals::Context->preference('reportGroup');
    my $x1 =$x;
    my $y1 =$y+Y_PAGE/$optPage -52; # $y+340 @$odData[$i]->{''};
    $pageN->stringr($f1, 10,580, $y1-20, $todayStr);
    #Ha $pageN->stringc($f1, 14,306, $y1 - 37, $libname);
    #ha $pageN->stringc($f1, 14,306, $y1 - 55 , $title);
    my ($add_x,$add_y);
    my ($addFr_x,$addFr_y);
    my ($info_x,$info_y);
    my ($msg_x,$msg_y);
    my @address_fr=();
    my @address_to=();
    my @userInfo=();
    my $lastname  = decode('utf8',$odData->{'lastname'});
    my $firstname  = decode('utf8', $odData->{'firstname'});
    if ($printAdd){
         # Sender address         
         ($addFr_x,$addFr_y)=($x + X_ADDRESS ,$y1 - 17);
         push @address_fr,"$libname ";
         push @address_fr,$libAddress;
         my $tmpAdd = "";
         $tmpAdd .= $libCity  if($libCity ne "");
         $tmpAdd .= ", $libState " if($libState ne "");
         $tmpAdd .= " $libZip " if($libZip ne "");
         push @address_fr,$tmpAdd;
        printInfo($pageN,$f1,\@address_fr,$addFr_x,$addFr_y);

         # Receiver address
        ($add_x,$add_y)=($x + X_ADDRESS,$y1 - Y_ADDRESS);
        ($info_x,$info_y)=($x + 350,$y1 - Y_ADDRESS);
        ($msg_x,$msg_y)=($x,$info_y - 64);
        my $addrLine1  = decode('utf8', $odData->{'addrLine1'});
        my $addrLine2  = decode('utf8', $odData->{'addrLine2'});
        my $city  = decode('utf8', $odData->{'city'});
        my $zip  = decode('utf8', $odData->{'zip'});
        my $state  = decode('utf8', $odData->{'state'});
        push @address_to,"$lastname, $firstname";
        push @address_to,"$addrLine1";
        if($addrLine2 ne ""){
            push @address_to,$addrLine2;
        }
        my $addrLine3  ;
        $addrLine3 = $city;
        $addrLine3 .= ", $state " if($state ne "");
        $addrLine3 .= " $zip " if($zip ne "");
        push @address_to,$addrLine3;
        
        my @msgSender;
        if($printGreeting && $printGreeting eq '1'){
            foreach my $l(@lang){
               push @msgSender, $messages->{'messageTo'}->{$l};
            }   
        }
        my $senderStr = join(" / ",@msgSender); 
        $pageN->stringl($f1, 9,$add_x, $add_y , $senderStr);
        printInfo($pageN,$f1,\@address_to,$add_x,$add_y);
        push @userInfo, "User Barcode: " . $odData->{'userbarcode'} if($odData->{'userbarcode'} ne "");
        
    }
    else{
        $pageN->stringc($f1, 14,306, $y1 - 37, $libname);
        ($info_x,$info_y)=($x ,$y1-70);
        ($msg_x,$msg_y)=($x,$info_y - ROW_HEIGHT_10*3);
        #($msg_x,$msg_y)=($x,$info_y - ROW_HEIGHT_10*(scalar(@msgArr)));
        my $classInfo ="";
        $classInfo .= 'Homeroom: ' . $odData->{'homeroom'} if(index($optNotice,'1') >=0);
        $classInfo .= ' Teacher: ' . $odData->{'teacher'}  if(index($optNotice,"2") >=0);
        $classInfo .= ' Grade: ' . $odData->{'grade'}       if(index($optNotice,"3") >=0);
        push @userInfo,$classInfo;
        my $ubc ="$lastname, $firstname";
           $ubc .= "  " . $odData->{'userbarcode'} if(index($optNotice,"5") >=0 && $odData->{'userbarcode'} ne "");
        push @userInfo,$ubc ;
    }
    push @userInfo,'Phone: ' . $odData->{'phone'} if(index($optNotice,"4") >=0 && $odData->{'phone'} ne"");
    printInfo($pageN,$f1,\@userInfo,$info_x,$info_y);
    
       my @tArr;
       foreach my $l(@lang){
           push @tArr, $messages->{'title'}->{$l};
       }
       my $title = join("-",@tArr);
       $pageN->stringc($f1, 14,306, $msg_y  , $title);
  
       my ($ii,$nLine)=(0,1);
=item       
       while($ii < scalar(@msgArr) && $nLine<2){
          my $tmpMsg=formatStr_3($pageN,$f3,10,@msgArr[$ii],X_TABLE);
          $pageN->stringl($f3, 10,$msg_x, $msg_y  -  ROW_HEIGHT_10*($nLine) -5,$tmpMsg);
          $nLine++;
          if(length(@msgArr[$ii]) > length($tmpMsg) && $nLine<3) {
               $tmpMsg=substr(@msgArr[$ii],length($tmpMsg),length(@msgArr[$ii]) - length($tmpMsg));
               $tmpMsg=~ s/^ +//g;
               $tmpMsg=~ s/^[\s]+//g;
               $tmpMsg=formatStr_3($pageN,$f3,10,$tmpMsg,X_TABLE);

               $pageN->stringl($f3, 10,$msg_x, $msg_y - ROW_HEIGHT_10*($nLine) -5, $tmpMsg); 
               $nLine++;          
           }
          $ii++;
       }
=cut  
    $msg_y -=7;    
    while($ii < scalar(@msgArr)){
          $pageN->stringl($f3, 10,$msg_x, $msg_y  -  ROW_HEIGHT_10*($ii) -12,@msgArr[$ii]);
          $ii++;
    }
   
}
sub printInfo{
    my($pageN,$f1,$info,$x,$y)=@_;

    for(my $i=0;$i<scalar(@$info); $i++){
       $pageN->stringl($f1, 10,$x, $y -($i+1)*ROW_HEIGHT_10  , @$info[$i] );        
    }
    
}
sub writeCell{
    my($pos,$pageN,$f,$fs,$x,$y,$txtStr)=@_; 
    if ($pos =="left"){   
        $pageN->stringl($f,$fs,$x,$y,$txtStr);
    }
    elsif ($pos =="right"){   
        $pageN->stringr($f,$fs,$x,$y,$txtStr);
    }
    else{   
        $pageN->stringc($f,$fs,$x,$y,$txtStr);
    }
   # print $pageN->string_width($f,$txtStr) ." -- $txtStr\n";
}
    
#----------------------------------------------------------------------------------------------------
    
sub writeHeaderRow{
    my($pageN,$font,$fontSize,$x1,$y1,$x2,$y2,$language)=@_;
 
    #$pageN->line($x1, $y1, $x2, $y1);
    #$pageN->line($x1, $y2, $x2, $y2);
    
    my $x=$x1;
    my $xWidth=40;

    if ($optTitle==1){
        $pageN->line($x, $y1, $x,$y2);  
         writeCell('left',$pageN,$font,$fontSize,$x+5, $y1 +5, $itemTblHeader->{'title'}->{$language}) ;  
        $x += 158;
        $xWidth=0;
     } 
    $pageN->line($x, $y1, $x,$y2);     
        writeCell('left',$pageN,$font,$fontSize, $x+5, $y1 +5,  $itemTblHeader->{'barcode'}->{$language}) ;  
    $x += 80 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);    
    if ($optPrice==1){
        writeCell('left',$pageN,$font,$fontSize,$x+5, $y1 +5,  $itemTblHeader->{'price'}->{$language}) ;  
        $x +=36 + $xWidth;
    }
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$font,$fontSize, $x+5, $y1 +5, $itemTblHeader->{'courseteacher'}->{$language}) ;  
    $x +=100 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);    
        writeCell('left',$pageN,$font,$fontSize, $x+5, $y1 +5,  $itemTblHeader->{'dateLoan'}->{$language}) ;  
    $x +=92 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$font,$fontSize ,$x+5, $y1 +5, $itemTblHeader->{'dateDue'}->{$language}) ;  
    $x +=92 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);

   
}
sub formatStr{
    my($pageN,$f,$fs,$inStr,$len)=@_;
    my $outStr=$inStr;
    $outStr =~ s/\s+$//g;
    my $sWidth= $fs * $pageN->string_width($f,$outStr);
    if($sWidth<=$len){
        return $outStr;
    }
    while($sWidth > $len){
       $outStr =~ s/\S+$//g;
       $outStr =~ s/\s+$//g;
       $sWidth= $fs * $pageN->string_width($f,$outStr );
    }

# fix 2007-10-18
    my $numUnclose=0;
    for(my $i=0; $i < length($outStr);$i++){
        my $c=  substr($outStr,$i,1);
        if($c eq '('){
            $numUnclose +=1;
            
        }
        elsif($c eq ')'){
            $numUnclose -=1;
        }
    }
    $outStr =$outStr . '...'; 
    while ($numUnclose>0){
        $outStr =$outStr . ')'; 
        $numUnclose -=1;
    }
# end fix 2007-10-18    
    return $outStr  ;
}
sub formatStr_1{
    my($pageN,$f,$fs,$inStr,$len)=@_;
    my $outStr=$inStr;
    $outStr =~ s/\s+$//g;
    my $sWidth= $fs* $pageN->string_width($f,$outStr);
    if($sWidth<=$len){
        return $outStr;
    }

    
    while($sWidth > $len){
       $outStr=~ s/.$//g;
       $sWidth= $fs * $pageN->string_width($f,$outStr );
    }
# fix 2007-10-18
    my $numUnclose=0;
    for(my $i=0; $i < length($outStr);$i++){
        my $c=  substr($outStr,$i,1);
        if($c eq '('){
            $numUnclose +=1;
            
        }
        elsif($c eq ')'){
            $numUnclose -=1;
        }
    }
    $outStr =$outStr . '...'; 
    while ($numUnclose>0){
        $outStr =$outStr . ')'; 
        $numUnclose -=1;
    }
# end fix 2007-10-18    
    return $outStr  ;
}
sub formatStr_3{
    my($pageN,$f,$fs,$inStr,$len)=@_;
    my $outStr=$inStr;
    $outStr =~ s/\s+$//g;
    my $sWidth= $fs * $pageN->string_width($f,$outStr);
    if($sWidth<=$len){
        return $outStr;
    }
    while($sWidth > $len){
       $outStr =~ s/\S+$//g;
       $outStr =~ s/\s+$//g;
       $sWidth= $fs * $pageN->string_width($f,$outStr );
    }
    return $outStr  ;
}
sub writeRow{
    my($pageN,$x1,$y1,$x2,$y2,$text)=@_;
 
    $pageN->line($x1, $y1, $x2, $y1);
    $pageN->line($x1, $y2, $x2, $y2);

    my $dddLen = 10*$pageN->string_width($f1,'...') ;
    my $x=$x1;
    my $xWidth=40;
    if( $optTitle==1){ 
        $pageN->line($x1, $y1, $x1,$y2);   
            writeCell('left',$pageN,$f1,10,$x1+5, $y1 +5, formatStr($pageN,$f1,10,$text->{'title'},150-$dddLen) ) ; 
            $x+=158;
            $xWidth=0;
    } 
         #$pageN->stringl($f1, 10, $x1+5, $y1 +5,  $text->{'title'});
    $pageN->line($x, $y1, $x,$y2);     
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5,  $text->{'barcode'}) ;  
        $x+=80 + $xWidth;
    if( $optPrice==1){ 
        $pageN->line($x, $y1, $x,$y2);    
        $text->{'price'} =~ s/p//gi;
        $text->{'price'} =~ s/usd//gi;
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5,  $text->{'price'}) ;  
        $x+=36 + $xWidth;
    }
    #$pageN->line($x, $y1, $x,$y2);    
    #    writeCell('left',$pageN,$f1,10, $x+5, $y1 +5, formatStr_1($pageN,$f1,10,$text->{'coursename'},88-$dddLen + $xWidth));  
    #    $x+=98 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);
        my $teacherName = $text->{'c_teachername'} || $text->{'l_teacherName'};
         writeCell('left',$pageN,$f1,10, $x+5, $y1 +5, formatStr_1($pageN,$f1,10,$teacherName,100-$dddLen + $xWidth));  
         #writeCell('left',$pageN,$f1,10, $x+5, $y1 +5, formatStr_1($pageN,$f1,10,$text->{'c_teachername'},100-$dddLen + $xWidth));  
        $x+=100 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);    
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5, $text->{'dateLoan_text'} );  
        $x+=92 + $xWidth;    
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$f1,10, $x + 5, $y1 +5,  $text->{'dateDue_text'}) ;    
        $x +=92 + $xWidth;
    $pageN->line($x2, $y1, $x2,$y2);
   
}


#Thu, Jun 06, 2013 @ 11:14:46 EDT
#
sub getOverdueCSV {
    my ($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my $retList = [];
   
    my $list;
    if ( $sort1 eq 'username'){
        $list = getLoanItems_userList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    elsif($sort1 eq 'teacher'){
        $list = getLoanItems_teacherList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    elsif($sort1 eq 'homeroom'){
        $list = getLoanItems_homeroomList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    elsif($sort1 eq 'grade'){
        $list = getLoanItems_gradeList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    elsif($sort1 eq 'title'){
        $list = getLoanItems_titleList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    elsif($sort1 eq 'course'){
        $list = getLoanItems_courseList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    elsif($sort1 eq 'courseteacher'){
        $list = getLoanItems_courseTeacherList($dbh, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby);
    }
    foreach my $i (@{$list}) {
        foreach my $el( @{$i->{'group'}}){
            my $item=[];
            foreach my $field(@fields){
                push @$item, $el->{$field};
            }
            push @$retList,$item;
        }
    }
    return $retList;
}