#!/usr/bin/perl

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

use Opals::Context;

use Time::localtime;

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

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;

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $pref = tmpl_preference($dbh);
my $libname = decode('utf8', $pref->{'libname'});
#----------------------------------------------------------------------------------------------------
my $mypdf=`/bin/mktemp`;
    my $pdf = new PDF::Create('filename' => $mypdf,
                              'Version'  => 1.2,
                              'PageMode' => 'UseOutlines',
                              'Title'    => 'My title',
                         );
    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 $title_E="Overdue Item(s) Reminder";
    my $title_S="Alvio de vencimiento";
    my $title_F="Overdue Item(s) Reminder";

    my $msgT_E="The following items that you borrowed from the library/media center are now past due:";
    my $msgT_S="El libro siguiente prestado de la biblioteca es atrasado:";
    my $msgT_F="The following items that you borrowed from the library/media center are now past due:";

    my $msgB_E="Please return or renew these items as soon as possible. Thank you in advance for your cooperation.";
    my $msgB_S="Por favor la vuelta o renueva el libro cuanto antes. Gracias por adelantado por su coooperacion.";
    my $msgB_F="Please return or renew these items as soon as possible. Thank you in advance for your cooperation.";
    my $todayStr ="";                         
#----------------------------------------------------------------------------------------------------

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

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 $msg  = decode('utf8',$input->{'msg'});
#my $msg  = $input->{'msg'};

if ( $sort1 eq '' )
{
    $sort1 = "homeroom";
}
if ( !$sort2 )
{
    if ( $sort1 eq "username" ) { $sort2 = "title"; }
    else { $sort2 = "username"; }
}

    my $dateFrom =  $input->{'dateFrom'};
    my $dateTo =    $input->{'dateTo'};

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

    my $byPass = (!$dateFrom && !$dateTo) ? 1 : 0;

    $dateFrom = $dateToday if ( !$dateFrom );
    $dateTo = $dateToday if ( !$dateTo );
   
    my $pNum = $input->{'pNum'};
   if ( $sort1 eq 'username' )
    {
        if ( $input->{'list'} )
        { 
            GetOverdueUserList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
            $template->param(userlist => 1);
        }
        else  {          
                 GetOverdueUserNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
         }
    }
    elsif ( $sort1 eq 'teacher' )
    {
        if ( $input->{'list'} )
        { 
            GetOverdueTeacherList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(teacherlist => 1);
        }
        else            
        { 
            GetOverdueTeacherNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input); 
        }
    }
    elsif ( $sort1 eq 'homeroom' )
    {
        if ( $input->{'list'} )
        { 
            GetOverdueHomeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input); 
            $template->param(homeroomlist => 1);
        }
        else            
        { 
            GetOverdueHomeroomNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input); 
        }
    }
    elsif ( $sort1 eq 'grade' )
    {
        #GetOverdueGrade($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo);
        if ( $input->{'list'} )
        { 
            GetOverdueGradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
            $template->param(gradelist => 1);
        }
        else            
        { 
            GetOverdueGradeNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input); 
        }
    }

#    my $optNotice = Opals::Context->preference('reportGroup');
#    if ( $optNotice == 1 )
#        { $template->param(reportHomeroom => 1); }
#    elsif ( $optNotice == 2 )
#        { $template->param(reportTeacher => 2); }
#    elsif ( $optNotice == 3 )
#        { $template->param(reportGrade => 3); }

    OptionPrintNotice($dbh, $template);
if ( $input->{'list'} ) {
    tmpl_write($dbh, $cgi, $cookie, $template);
}
else {
    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 GetOverdueTeacherList
{    

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

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

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

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

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

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

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

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

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

    $template->param(ItemsInLoan => \@DataRecs);
    $template->param(test=> $input->{'roomlist'});
}
#--------------------------------------------------------
sub GetOverdueTeacherNotice
{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input) = @_;

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

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

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


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

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);
    $szSQL .= "order by teacher, buildingcode, homeroom, lastname, firstname, uid";

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

    for (my $i=0; $i<$nList; $i++)
    {
        if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
        if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
        if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }

        my $bResult = $query->execute($roomlist[$i], $bdinglist[$i], $teacherlist[$i]);
        MakeNotice($query, \@DataRecs);        
    }

    printOd_pdf(\@DataRecs);
    #$template->param(odusers => \@DataRecs);
}


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

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

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

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

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

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

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

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

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            
            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                phone           => $loan->{'phone'},
                deltaDueDate    => $loan->{'deltaDueDate'},
                uid        => $loan->{'uid'},
            };

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

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

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


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

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

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

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

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

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);
    $szSQL .= "order by lastname, firstname, uid";

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

    for (my $i=0; $i<$nList; $i++)
    {
        if ( !$roomlist[$i] ) { $roomlist[$i] = ''; }
        if ( !$bdinglist[$i] ) { $bdinglist[$i] = ''; }
        if ( !$teacherlist[$i] ) { $teacherlist[$i] = ''; }

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

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

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

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

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

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

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


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

    my @gradelist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct grade from opl_user order by grade";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @gradelist, $rec->{'grade'};
        }
        $query->finish;
    }
    else
    {
        @gradelist = split /\$/, $input->{'gradelist'};
        if ( scalar(@gradelist) == 0 ) { push @gradelist, ''; }
    }

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

    $szSQL = AddDateConstraint($szSQL, $dateFrom, $dateTo);
    $szSQL .= "order by lastname, firstname, uid";

    my $query = $dbh->prepare($szSQL);


    my @DataRecs = ();
    my $nList = scalar(@gradelist);
    for (my $i=0; $i<$nList; $i++)
    {
        #if ( !$gradelist[$i] ) { $gradelist[$i] = ''; }
        if($gradelist[$i] eq 'none'){
            $gradelist[$i] ='';
        }
      
              my $bResult = $query->execute($gradelist[$i]);
   
        MakeNotice($query, \@DataRecs);
    }

    printOd_pdf(\@DataRecs);
    #$template->param(odusers => \@DataRecs);
}


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

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

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


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

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

            $loan->{'title'} =~ s/\\/\\\\/g;
            $loan->{'title'} =~ s/\"/\\\"/g;
            if($loan->{'grade'} eq ''){
                $loan->{'grade'}='none';
            }
            ($lastname,$firstname,$username,$grade,$homeroom,$building,$teacher)=($loan->{'lastname'},
                                                                           $loan->{'firstname'},
                                                                           $loan->{'username'},
                                                                           $loan->{'grade'},
                                                                           $loan->{'homeroom'},
                                                                           $loan->{'buildingcode'},
                                                                           $loan->{'teacher'});
            push @loanRecs, {
                firstname       => $loan->{'firstname'},
                lastname        => $loan->{'lastname'},
                username        => $loan->{'username'},
                userbarcode     => $loan->{'userbarcode'}, 
                grade           => $loan->{'grade'},   
                title           => $loan->{'title'},
                dewey           => $loan->{'dewey'}, 
                barcode         => $loan->{'barcode'},
                price           => $loan->{'price'},
                dateDue         => $loan->{'dateDue'},
                dateLoan        => $loan->{'dateLoan'},
                homeroom        => $loan->{'homeroom'},
                buildingcode    => $loan->{'buildingcode'}, 
                teacher         => $loan->{'teacher'},
                deltaDueDate    => $loan->{'deltaDueDate'}, 
                uid             => $loan->{'uid'},
                phone           => $loan->{'phone'},
            };

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

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

}

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

    my @idlist = ();
    
    if ( $input->{'whole'} )
    {
        my $sql = "select distinct uid from opl_user order by lastname, firstname, uid";
        my $query = $dbh->prepare($sql);
        $query->execute();
        while ( my $rec = $query->fetchrow_hashref )
        {
            push @idlist, $rec->{'uid'};
        }
        $query->finish;
    }
    else
    {
        @idlist = split /\$/, $input->{'idlist'};
    }

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

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

    if ( $orderby eq 'name' ) { $szSQL .= " order by lastname, firstname, username"; }
    else { $szSQL .= " order by homeroom, lastname, firstname, username"; }
   
    my $query = $dbh->prepare($szSQL);
    my @DataRecs = ();
    my $nList = scalar(@idlist);

    for (my $i=0; $i<$nList; $i++)
    {
        my $bResult = $query->execute($idlist[$i]);
        
        MakeNotice($query, \@DataRecs);
    }

    printOd_pdf(\@DataRecs);
    #$template->param(odusers => \@DataRecs);
}



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

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

}

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

#----------------------------------------------------------
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 trim{
    my $str = shift;
    $str =~ s/^\s+//;
    $str =~ s/\s+$//;
    return $str;

}

#--------------------------------------------------------
sub MakeNotice
{
    my ($query, $DataRecs) = @_;
    my %iduser = {};
    $iduser{'uid'} = -1;
    my @loanRecs = ();
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if ( $iduser{'uid'} == -1 ) { %iduser = %$loan; }

        if ( $iduser{'uid'} != $loan->{'uid'} )
        {
            # Prepare the group title
            my @data = @loanRecs;
            if ( scalar(@data) != 0 )
            {
                push @$DataRecs, { oditems => \@data, firstname => $iduser{'firstname'}, 
                            lastname => $iduser{'lastname'}, userbarcode => $iduser{'userbarcode'},
                            username => $iduser{'username'}, uid => $iduser{'uid'},
                            homeroom => $iduser{'homeroom'}, teacher => $iduser{'teacher'},
                            grade => $iduser{'grade'}, phone => $iduser{'phone'}, 
                            addrLine1=> $iduser{'addrLine1'},addrLine2   => $iduser{'addrLine2'},
                            city  => $iduser{'city'}, zip => $iduser{'zip'},state => $iduser{'state'}
};
            }
            @loanRecs = ();
            %iduser = %$loan;
        }
        
        # Format each row of data
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

        $loan->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
        push @loanRecs, {
            grade           => $loan->{'grade'},
            title           => decode('utf8',$loan->{'title'}),
            dewey           => $loan->{'dewey'}, 
            barcode         => $loan->{'barcode'},
            price           => $loan->{'price'},
            dateDue         => $loan->{'dateDue'},
            deltaDueDate    => $loan->{'deltaDueDate'},
        };
    }                    
    $query->finish;
    if ( scalar(@loanRecs) != 0 )
    { 
        @loanRecs = sort { lc(trimArticle($a->{"title"})) cmp lc(trimArticle($b->{"title"})) } @loanRecs; 
        push @$DataRecs, {
            oditems     => \@loanRecs,
            firstname   => $iduser{'firstname'}, 
            lastname    => $iduser{'lastname'},
            userbarcode => $iduser{'userbarcode'},
            username    => $iduser{'username'},
            uid         => $iduser{'uid'},
            homeroom    => $iduser{'homeroom'},
            teacher     => $iduser{'teacher'},
            grade       => $iduser{'grade'},
            phone       => $iduser{'phone'},
            addrLine1   => $iduser{'addrLine1'},
            addrLine2   => $iduser{'addrLine2'},
            city        => $iduser{'city'},
            zip         => $iduser{'zip'},
            state       => $iduser{'state'},
        }; 
    }
}
#----------------------------------------------------------------------------------------------------
 sub printOd_pdf{
    my($odData)=@_; 
    my $pageN  ;
    my $subPageCount = 1;
    my $xRow=6;
    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;
    }
    for(my $i=0; $i<scalar(@$odData); $i++){
       my $pNo=1;
       $xRow=6;
       if(($subPageCount % $optPage)==1 || ($optPage==1)){
          $pageN = $root->new_page;
       }
      $x = X_LEFT_MARGIN;
      $y = (Y_PAGE - $subPageCount*(Y_PAGE/$optPage)) + 40  ;
      my $aaa=@$odData[$i]->{'oditems'};

       $totalPage = floor((scalar (@$aaa)-$rowPerPage+$xRow)/($rowPerPage)) + 1;
       if ((scalar (@$aaa)-$rowPerPage+$xRow)%($rowPerPage) > 0){
           $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
                );
    
       my($x1,$y1,$x2,$y2);
       my $heightTbl;
       $heightTbl= ($rowPerPage -$xRow)* ROW_HEIGHT;
  
       ($x1,$y1,$x2,$y2)=($x,$y+$heightTbl-10,$x+X_TABLE,$y+ $heightTbl+ ROW_HEIGHT -10);
       if($printAdd){
           $y1 = $y1 - Y_ADDRESS;
           $y2 = $y2 - Y_ADDRESS  ;
       }
       writeHeaderRow($pageN,$x1,$y1,$x2,$y2+2);
       $y2 =$y1;
       $y1 -= ROW_HEIGHT;
       
       my $j=0;       
       for($j=0; $j<$rowPerPage-$xRow && $j < scalar(@$aaa); $j++){
       #for($j=0; $j<10 && $j < 2; $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++)

       $pageN->stringl($f1, 10,$x, $y-20, $msgB);
             
     }       
               
   
    $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);
    $pageN->stringc($f1, 14,306, $y1 - 37, $libname);
    $pageN->stringc($f1, 14,306, $y1 - 55 , $title);
    my ($add_x,$add_y);
    my ($info_x,$info_y);
    my ($msg_x,$msg_y);
    my @address=();
    my @userInfo=();
    my $lastname  = decode('utf8',$odData->{'lastname'});
    my $firstname  = decode('utf8', $odData->{'firstname'});
    if ($printAdd){
        ($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 - 70);
        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,"$lastname, $firstname";
        push @address,"$addrLine1";
        if($addrLine2 ne ""){
            push @address,$addrLine2;
        }
        my $addrLine3  ;
        $addrLine3 = $city;
        $addrLine3 .= ", $state " if($state ne "");
        $addrLine3 .= " $zip " if($zip ne "");
        push @address,$addrLine3;
        printInfo($pageN,$f1,\@address,$add_x,$add_y);
        push @userInfo, "User Barcode: " . $odData->{'userbarcode'} if($odData->{'userbarcode'} ne "");

        
    }
    else{
        ($info_x,$info_y)=($x ,$y1-70);
        ($msg_x,$msg_y)=($x,$info_y - ROW_HEIGHT_10*3);
        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);
    
       $msg =~ s/_EOL_/\n/;
       $msg =~ s/\n+/\n/;
       my @msgArr  = split /\n/, $msg;
       my ($ii,$nLine)=(0,1);
      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 - 15*($nLine),$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), $tmpMsg); 
               $nLine++;          
           }
          $ii++;
       }
       $pageN->stringl($f1, 10,$msg_x, $msg_y - 40, $msgT);
   
}
   
#----------------------------------------------------------------------------------------------------
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,$x1,$y1,$x2,$y2)=@_;
 
    $pageN->line($x1, $y1, $x2, $y1);
    $pageN->line($x1, $y2, $x2, $y2);
    
    my $x=$x1;
    my $xWidth=45;
    if ($optTitle==1){
        $pageN->line($x, $y1, $x,$y2);   
            writeCell('left',$pageN,$f2,10,$x+5, $y1 +5,  'Title') ;  
        $x += 165;
        $xWidth=0;
     } 
    $pageN->line($x, $y1, $x,$y2);     
        writeCell('left',$pageN,$f2,10, $x+5, $y1 +5,  'Barcode') ;  
    $x += 100 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);    
    if ($optPrice==1){
        writeCell('left',$pageN,$f2,10,$x+5, $y1 +5,  'Price') ;  
        $x +=60 + $xWidth;
    }
    $pageN->line($x, $y1, $x,$y2);    
        writeCell('left',$pageN,$f2,10, $x+5, $y1 +5,  'Dewey #') ;  
    $x +=85 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$f2,10, $x+5, $y1 +5, 'Due Date') ;  
    $x +=85 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$f2,10, $x+5, $y1 +5, 'Overdue') ;  
    $pageN->line($x2, $y1, $x2,$y2);
    
   
}
#----------------------------------------------------------------------------------------------------
# 2007-10-18
# There is a bug in pdf writer which will not write properly if there are unclose 
# open round brakets "(".
# example : string "click here :(to find" will not be write to the pdf file.
# Solution: add ")" if it's missing.
#
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 );
    }
     $outStr =$outStr . '...'; 
    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=45;
    if( $optTitle==1){ 
        $pageN->line($x1, $y1, $x1,$y2);   
            writeCell('left',$pageN,$f1,10,$x1+5, $y1 +5, formatStr($pageN,$f1,10,$text->{'title'},155-$dddLen) ) ; 
            $x+=165;
            $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+=100 + $xWidth;
    if( $optPrice==1){ 
        $pageN->line($x, $y1, $x,$y2);    
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5,  $text->{'price'}) ;  
        $x+=60 + $xWidth;
    }
    $pageN->line($x, $y1, $x,$y2);    
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5,  formatStr_1($pageN,$f1,10,$text->{'dewey'},75-$dddLen + $xWidth)) ;  
        $x+=85 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$f1,10, $x+5, $y1 +5,  $text->{'dateDue'}) ;  
        $x+=85 + $xWidth;
    $pageN->line($x, $y1, $x,$y2);
        writeCell('left',$pageN,$f1,10,$x+5, $y1 +5,  $text->{'deltaDueDate'}) ;  
    $pageN->line($x2, $y1, $x2,$y2);
    
}

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