#!/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::Equipment qw(
    
    eq_item_findByBarcode

);

use Opals::Date qw(
    date_text
    date_time_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        => '/eqmnt/report/itemOnLoan_prt.tmpl',
        reqPermission   => 'eq_report',
    }
);

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 $mypdf=`/bin/mktemp`;
    my $pdf = new PDF::Create('filename' => $mypdf,
                              'Version'  => 1.2,
                              'PageMode' => 'UseOutlines',
                              'Title'    => 'Overdue 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=>'Overdue Equipment 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=>'The following equipment that were loaned to you are now past due:',
                fr=>'Les ouvrages ou documents suivants sont en retard.' ,
                sp=>'El libro siguiente prestado de la biblioteca es atrasado:'
                },
        
       message2=>{
                en=>'Please return the equipment as soon as possible. Thank you in advance for your cooperation.',
                fr=>'Veuillez les rapporter au plus tôt. Merci de votre collaboration.',
                sp=>'Por favor la vuelta el libro el mas pronto que posible.Gracias por su cooperacion.'
                }
        };

    my $itemTblHeader= {
        title=>{
            en=>'Equipment',
            fr=>'Equipment',
            sp=>'Equipo'
            },
        barcode=>{
            en=>'Barcode',
            fr=>'#Code',
            sp=>'Código'
            },
        price=>{
            en=>'Price',
            fr=>'Prix',
            sp=>'Precio'
            },
        dateDue=>{
            en=>'Date Due',
            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 $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 $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 ( $sort1 eq 'username'){
    if ($input->{'list'}){
        getOverdue_userList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
        $template->param(idList => 1);
    }
    else{
        getOverdue_userNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'grade'){
    if ($input->{'list'}){
        getOverdue_gradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(gradeList => 1);
    }
    else{
        getOverdue_gradeNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'homeroom'){
    if ($input->{'list'}){
        getOverdue_homeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(homeroomList => 1);
    }
    else{
        getOverdue_homeroomNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}
elsif ($sort1 eq 'teacher'){
    if ($input->{'list'}){
        getOverdue_teacherList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(teacherList => 1);
    }
    else{
        getOverdue_teacherNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}

elsif ($sort1 eq 'rname'){
    if ($input->{'list'}){
        getOverdue_rnameList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(recIdList => 1);
    }
    else{
        getOverdue_rnameNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}


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

if ( $input->{'list'} ) {
    tmpl_write($dbh, $cgi, $cookieList, $template); 
    }
else {
    #tmpl_write($dbh, $cgi, $cookieList, $template); 

    open PDF, "<$mypdf";
    print $cgi->header(
        -type           => 'application/pdf',
        -attachement    => 'OverdueNotice.pdf'
    );
#    print "Content-type: application/pdf\n\n";
    while (<PDF>) {
        print $_;
    }
    close PDF;

}

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


########################################################################################################
# Notice functions
########################################################################################################

sub getOverdue_userNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @idList = ();
    my $eqOnLoan;
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateDue >= '$dateFrom' && dateDue <= '$dateTo' &&
            l.uid = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine1'} = $loan->{'addrLine1'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine2'} = $loan->{'addrLine2'};
                $eqOnLoan->{$loan->{'uid'}}->{'city'} = $loan->{'city'};
                $eqOnLoan->{$loan->{'uid'}}->{'state'} = $loan->{'state'};
                $eqOnLoan->{$loan->{'uid'}}->{'zip'} = $loan->{'zip'};

            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    printOd_pdf(\@eqOnLoanList);
}

sub getOverdue_gradeNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @gradeList = ();
    my $eqOnLoan;
    @gradeList = split /\$/, $input->{'gradeList'};
    
    if (scalar(@gradeList) == 0){ 
        push @gradeList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateDue >= '$dateFrom' && dateDue <= '$dateTo' &&
            u.grade = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@gradeList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($gradeList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine1'} = $loan->{'addrLine1'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine2'} = $loan->{'addrLine2'};
                $eqOnLoan->{$loan->{'uid'}}->{'city'} = $loan->{'city'};
                $eqOnLoan->{$loan->{'uid'}}->{'state'} = $loan->{'state'};
                $eqOnLoan->{$loan->{'uid'}}->{'zip'} = $loan->{'zip'};

            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    printOd_pdf(\@eqOnLoanList);
}

sub getOverdue_homeroomNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @homeroomList = ();
    my $eqOnLoan;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    

    if (scalar(@homeroomList) == 0){ 
        push @homeroomList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateDue >= '$dateFrom' && dateDue <= '$dateTo' &&
            u.homeroom = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@homeroomList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($homeroomList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine1'} = $loan->{'addrLine1'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine2'} = $loan->{'addrLine2'};
                $eqOnLoan->{$loan->{'uid'}}->{'city'} = $loan->{'city'};
                $eqOnLoan->{$loan->{'uid'}}->{'state'} = $loan->{'state'};
                $eqOnLoan->{$loan->{'uid'}}->{'zip'} = $loan->{'zip'};

            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    printOd_pdf(\@eqOnLoanList);
}

sub getOverdue_teacherNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @teacherList = ();
    my $eqOnLoan;
    @teacherList = split /\$/, $input->{'teacherList'};
    

    if (scalar(@teacherList) == 0){ 
        push @teacherList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateDue >= '$dateFrom' && dateDue <= '$dateTo' &&
            u.teacher = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($teacherList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine1'} = $loan->{'addrLine1'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine2'} = $loan->{'addrLine2'};
                $eqOnLoan->{$loan->{'uid'}}->{'city'} = $loan->{'city'};
                $eqOnLoan->{$loan->{'uid'}}->{'state'} = $loan->{'state'};
                $eqOnLoan->{$loan->{'uid'}}->{'zip'} = $loan->{'zip'};

            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    printOd_pdf(\@eqOnLoanList);
}


sub getOverdue_rnameNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @recIdList = ();
    my $eqOnLoan;
    @recIdList = split /\$/, $input->{'recIdList'};
    
    if (scalar(@recIdList) == 0){ 
        push @recIdList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue
    from eq_loan as l inner join eq_items i using (barcode)
    inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateDue >= '$dateFrom' && dateDue <= '$dateTo' &&
            i.rid = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@recIdList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($recIdList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine1'} = $loan->{'addrLine1'};
                $eqOnLoan->{$loan->{'uid'}}->{'addrLine2'} = $loan->{'addrLine2'};
                $eqOnLoan->{$loan->{'uid'}}->{'city'} = $loan->{'city'};
                $eqOnLoan->{$loan->{'uid'}}->{'state'} = $loan->{'state'};
                $eqOnLoan->{$loan->{'uid'}}->{'zip'} = $loan->{'zip'};

            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    printOd_pdf(\@eqOnLoanList);
}

########################################################################################################
# List functions
########################################################################################################


sub getOverdue_userList {

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my $eqOnLoan;
    my @idList = ();
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
    
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            l.uid = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOverdueList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOverdueList, $eqOnLoan->{$k};
    }

    my @sorted = sort { $a->{'lastname'} cmp $b->{'lastname'}} @eqOverdueList;
    $template->param(eqOverdueUser => \@sorted);
}

sub getOverdue_gradeList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @gradeList = ();
    my $eqOnLoan;
    @gradeList = split /\$/, $input->{'gradeList'};
    
    if (scalar(@gradeList) == 0){ 
        push @gradeList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.grade = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@gradeList);
    for (my $i = 0;  $i < $nList ; $i++){
        my $grade = ($gradeList[$i] eq "N/A")?"":$gradeList[$i];
        $sth->execute($grade);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOverdueList;
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOverdueList, $eqOnLoan->{$k};
    }
    my @sorted = sort { $a->{'grade'} cmp $b->{'grade'}} @eqOverdueList;
    $template->param(eqOverdueGrade => \@sorted);

}
sub getOverdue_homeroomList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @homeroomList = ();
    my $eqOnLoan;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    

    if (scalar(@homeroomList) == 0){ 
        push @homeroomList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.homeroom = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@homeroomList);
    for (my $i = 0;  $i < $nList ; $i++){
        my $hroom = ($homeroomList[$i] eq "N/A")?" " : $homeroomList[$i];
        $sth->execute($hroom);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOverdueList;
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOverdueList, $eqOnLoan->{$k};
    }
    my @sorted = sort { $a->{'homeroom'} cmp $b->{'homeroom'}} @eqOverdueList;
    $template->param(eqOverdueHomeroom => \@sorted);

}

sub getOverdue_teacherList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @teacherList = ();
    my $eqOnLoan;
    @teacherList = split /\$/, $input->{'teacherList'};
    
    if (scalar(@teacherList) == 0){ 
        push @teacherList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue
    from eq_loan as l inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.teacher = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for (my $i = 0;  $i < $nList ; $i++){
        my $teacher = ($teacherList[$i] eq "N/A")?" " : $teacherList[$i];
        $sth->execute($teacher);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOverdueList;
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOverdueList, $eqOnLoan->{$k};
    }
    my @sorted = sort { $a->{'teacher'} cmp $b->{'teacher'}} @eqOverdueList;
    $template->param(eqOverdueTeacher => \@sorted);

}


sub getOverdue_rnameList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby, $overdueOnly) = @_;
    my @recIdList = ();
    my $eqOnLoan;
    @recIdList = split /\$/, $input->{'recIdList'};
    
    if (scalar(@recIdList) == 0){ 
        push @recIdList, ''; }
    my $sql = "
    select l.*, u.* , l.dateDue < now() as overdue, to_days(now()) - to_days(l.dateDue) as deltaDateDue
    from eq_loan as l inner join eq_items i using (barcode)
    inner join opl_user as u on l.uid = u.uid
    where   dateReturn is null &&    
            dateLoan >= '$dateFrom' && dateLoan <= '$dateTo' &&
            i.rid = ?    ";
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@recIdList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($recIdList[$i]);
        while (my $loan =$sth->fetchrow_hashref ){
            my $itemInfo = eq_item_findByBarcode($dbh, $loan->{'barcode'});
            $loan->{'rname'} =  $itemInfo->{'rname'};
            my $price = 0;
            foreach my $i ( @{$itemInfo->{'fields'}}){
                if ($i->{'sfname'} =~ /Price/i){
                    $price = $i->{'sfval'};
                    last;
                }
            }
            $loan->{'price'} = $price;
            $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
            if (!$eqOnLoan->{$loan->{'uid'}}){
                $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
                $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
                $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
                $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
                $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
                $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
                $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
                $eqOnLoan->{$loan->{'uid'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOverdueList;
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOverdueList, $eqOnLoan->{$k};
    }
    my @sorted = sort { $a->{'homeroom'} cmp $b->{'homeroom'}} @eqOverdueList;
    $template->param(eqOverdueRname => \@sorted);
}


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; 
    }
    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 += 250;
        $xWidth=0;
     } 

    $pageN->line($x, $y1, $x,$y2);     
        writeCell('left',$pageN,$font,$fontSize, $x+5, $y1 +5,  $itemTblHeader->{'barcode'}->{$language}) ;  
    $x += 70 + $xWidth;

    $pageN->line($x, $y1, $x,$y2);    
    if ($optPrice==1){
        writeCell('left',$pageN,$font,$fontSize,$x+5, $y1 +5,  $itemTblHeader->{'price'}->{$language}) ;  
        $x +=70 + $xWidth;
    }
    else {
        $xWidth = 70;
    }
  
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$font,$fontSize ,$x+5, $y1 +5, $itemTblHeader->{'dateDue'}->{$language}) ;  
        $x +=168 + $xWidth;
    
    $pageN->line($x, $y1, $x,$y2);

   
}
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->{'rname'},250-$dddLen) ) ; 
            $x+=250;
            $xWidth=0;
    } 

    $pageN->line($x, $y1, $x,$y2);     
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5,  $text->{'barcode'}) ;  
        $x+=70 + $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+=70 + $xWidth;
    }

    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$f1,10, $x + 5, $y1 +5,  $text->{'dateDueFormat'}) ;    
        $x +=170 + $xWidth;
   
   $pageN->line($x2, $y1, $x2,$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  ;
}