#!/usr/bin/perl

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

use Opals::Context;

use Time::localtime;

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

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

use Opals::Locale qw(
    loc_getMsgFile
    loc_write
    
);
use Opals::Transactions qw(
    trans_getUnpaidFineList
    );

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 $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'    => '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 $messages= {
        title=>{en=>'Overdue Item(s) 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 items that you borrowed from the library/media center are now past due:',
                fr=>'Les ouvrages ou documents suivants sont en retard.' ,
                sp=>'El libro siguiente prestado de la biblioteca:'
                },
        
       message2=>{
                en=>'Please return  these items 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 devuelva el libro lo mas pronto posible.  Gracias por su cooperación.'
                }
        };

    my $itemTblHeader= {
        title=>{
            en=>'Title',
            fr=>'Titre',
            sp=>'Título'
            },
        barcode=>{
            en=>'Barcode',
            fr=>'#Code',
            sp=>'Código'
            },
        price=>{
            en=>'Price',
            fr=>'Prix',
            sp=>'Precio'
            },
        fineType=>{
            en=>'Type',
            fr=>'Type',
            sp=>'Type'
            },

        dewey=>{
            en=>'Dewey',
            fr=>'# Classification',
            sp=>'Clasificación'
            },
        dateLoan=>{
            en=>'Loan Date',
            fr=>'Date ',
            sp=>'Feche'
            },
        dateDue=>{
            en=>'Due Date',
            fr=>'Date d\'échéance',
            sp=>'Feche de vencimiento'
            },
        date=>{
            en=>'Date',
            fr=>'Date',
            sp=>'Feche'
            },
        amount=>{
            en=>'Amount',
            fr=>'Montant',
            sp=>'Cantidad'
            },
        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 $headers={
        odNotice        => ['title', 'barcode', 'price', 'dewey', 'dateLoan', 'dateDue'],
        unpaidItem      => ['title', 'barcode', 'fineType', 'date', 'amount']
    };
   my $tblDef={
       odNoticeData=>{
            title   =>{width=>150  ,height=>10, padding=>5 ,font=>$f1,fontSize=>10,align=>'left'  },
            barcode =>{width=>100  ,height=>10, padding=>5 ,font=>$f1,fontSize=>10,align=>'left'  },
            price   =>{width=>45   ,height=>10, padding=>5 ,font=>$f1,fontSize=>10,align=>'right' },
            dewey   =>{width=>88   ,height=>10, padding=>5 ,font=>$f1,fontSize=>10,align=>'left'  },
            dateLoan=>{width=>88   ,height=>10, padding=>5 ,font=>$f1,fontSize=>10,align=>'left'  },
            dateDue =>{width=>87   ,height=>10, padding=>5 ,font=>$f1,fontSize=>10,align=>'left'  }
           },
       odNoticeHeaderMain=>{
            title   =>{width=>150  ,height=>10, padding=>5 ,font=>$f2,fontSize=>10,align=>'center'},
            barcode =>{width=>100  ,height=>10, padding=>5 ,font=>$f2,fontSize=>10,align=>'center'},
            price   =>{width=>45   ,height=>10, padding=>5 ,font=>$f2,fontSize=>10,align=>'center'},
            dewey   =>{width=>88   ,height=>10, padding=>5 ,font=>$f2,fontSize=>10,align=>'center'},
            dateLoan=>{width=>88   ,height=>10, padding=>5 ,font=>$f2,fontSize=>10,align=>'center'},
            dateDue =>{width=>87   ,height=>10, padding=>5 ,font=>$f2,fontSize=>10,align=>'center'}
           },
       odNoticeHeaderSub=>{
            title   =>{width=>150  ,height=>10, padding=>5 ,font=>$f3,fontSize=>10,align=>'center' },
            barcode =>{width=>100  ,height=>10, padding=>5 ,font=>$f3,fontSize=>10,align=>'center' },
            price   =>{width=>45   ,height=>10, padding=>5 ,font=>$f3,fontSize=>10,align=>'center' },
            dewey   =>{width=>88   ,height=>10, padding=>5 ,font=>$f3,fontSize=>10,align=>'center' },
            dateLoan=>{width=>88   ,height=>10, padding=>5 ,font=>$f3,fontSize=>10,align=>'center' },
            dateDue =>{width=>87   ,height=>10, padding=>5 ,font=>$f3,fontSize=>10,align=>'center' }
           },
      unpaidItemData=>{
            title   =>{width=>200   ,height=>10, padding=>5,font=>$f1,fontSize=>10,align=>'left'  },
            barcode =>{width=>120   ,height=>10, padding=>5,font=>$f1,fontSize=>10,align=>'left'  },
            fineType=>{width=>55    ,height=>10, padding=>5,font=>$f1,fontSize=>10,align=>'left'  },
            date    =>{width=>95    ,height=>10, padding=>5,font=>$f1,fontSize=>10,align=>'left'  },
            amount  =>{width=>88    ,height=>10, padding=>5,font=>$f1,fontSize=>10,align=>'right' }
           },
     unpaidItemHeaderMain=>{
            title   =>{width=>200   ,height=>10, padding=>5,font=>$f2,fontSize=>10,align=>'center'},
            barcode =>{width=>120   ,height=>10, padding=>5,font=>$f2,fontSize=>10,align=>'center'},
            fineType=>{width=>55    ,height=>10, padding=>5,font=>$f2,fontSize=>10,align=>'center'},
            date    =>{width=>95    ,height=>10, padding=>5,font=>$f2,fontSize=>10,align=>'center'},
            amount  =>{width=>88    ,height=>10, padding=>5,font=>$f2,fontSize=>10,align=>'center'}
           },
     unpaidItemHeaderSub=>{
            title   =>{width=>200   ,height=>10, padding=>5,font=>$f3,fontSize=>10,align=>'center'},
            barcode =>{width=>120   ,height=>10, padding=>5,font=>$f3,fontSize=>10,align=>'center'},
            fineType=>{width=>55    ,height=>10, padding=>5,font=>$f3,fontSize=>10,align=>'center'},
            date    =>{width=>95    ,height=>10, padding=>5,font=>$f3,fontSize=>10,align=>'center'},
            amount  =>{width=>88    ,height=>10, padding=>5,font=>$f3,fontSize=>10,align=>'center'}
           }
           
   };
   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 $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};
   }   
#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/;
    $todayStr = date_text($dateToday,1);
    
    my $byPass = (!$dateFrom && !$dateTo) ? 1 : 0;

    $dateFrom = $dateToday if ( !$dateFrom );
    $dateTo = $dateToday if ( !$dateTo );
   
    my $pNum = $input->{'pNum'};
    my $DataRecs;
   if ( $sort1 eq 'username' )
    {
        if ( $input->{'list'} )
        { 
            GetOverdueUserList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
            $template->param(userlist => 1);
        }
        else  {          
                 $DataRecs = 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            
        { 
            $DataRecs = 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            
        { 
            $DataRecs = 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            
        { 
            $DataRecs = 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); }

#Thu, Jan 29, 2009 @ 10:43:06 EST


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

if ( $input->{'list'} ) { 
    my $noticeMsgMap =loc_getMsgFile('circ/notice.msg');
    loc_write($template,$noticeMsgMap);
    tmpl_write($dbh, $cgi, $cookie, $template); 
    }
else {
    my $headerRow_od=getHeaderRow('odNotice');
    foreach my $rec(@$DataRecs){
        foreach my $od(@{$rec->{'oditems'}}){
            $od->{'type'}='odNoticeData';
        }
        my @aaa=(
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'123',dateLoan=>'2010-06-01'},
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'1234',dateLoan=>'2010-06-01'},
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'1235',dateLoan=>'2010-06-01'},
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'12335',dateLoan=>'2010-06-01'},
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'122335',dateLoan=>'2010-06-01'},
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'172335',dateLoan=>'2010-06-01'},
                {type=>'odNoticeData' ,height=>10, title=>'aaa',barcode=>'1236',dateLoan=>'2010-06-01'}
                );
        @{$rec->{'oditems'}}=({type=>'text',text=>'Overdue Item List'}, @$headerRow_od, @{$rec->{'oditems'}},@aaa);
    
        if(defined $input->{'incUnpaidNotice'} &&  $input->{'incUnpaidNotice'}  eq '1'){
            my $headerRow_unpaid=getHeaderRow('unpaidItem');
            my @upItemList=trans_getUnpaidFineList($dbh,$rec->{'uid'});
            if(scalar(@upItemList)>0){
                foreach my $f(@upItemList){
                    $f->{'type'}='unpaidItemData';
                    $f->{'amount'}=$f->{'damount'};
                    $f->{'fineType'}=$f->{'description'};
                    $f->{'date'}=date_text($f->{'date'}, 0,$l);

                }
                @{$rec->{'oditems'}}=(@{$rec->{'oditems'}},
                                      {type=>'text',text=>''},
                                      {type=>'text',text=>'Unpaid Item List',font=>$f2},
                                      @$headerRow_unpaid,
                                      @upItemList

                                      );    
            }
        }
 
    }
    printOd_pdf($DataRecs);
    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 && trim(homeroom)=? 
        && trim(buildingcode)=? && trim(teacher)=?";

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

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

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

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

    $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 && trim(homeroom)=?
        && trim(buildingcode)=? && trim(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);        
    }

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

#----------------------------------------------------------------------------------------------------
sub getHeaderRow{
    my ($headerType)=@_;
    my @hRow=();
    my $headerName=$headerType ."HeaderMain";
    foreach my $l(@lang){
        my $hr={type    =>$headerName};
        foreach my $h(@{$headers->{$headerType}}){
             $hr->{$h} =$itemTblHeader->{$h}->{$l};
        }
        push @hRow,$hr;
        $headerName=$headerType ."HeaderSub";

   
    }
    return \@hRow;
}

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

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

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

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

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

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

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

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

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

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

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

    $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 && trim(homeroom)=?
        && trim(buildingcode)=? && trim(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);
    }
    return \@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'},
                dateLoan        => $loan->{'dateLoan'},
                homeroom        => $loan->{'homeroom'},
                buildingcode    => $loan->{'buildingcode'}, 
                teacher         => $loan->{'teacher'},
                deltaDueDate    => $loan->{'deltaDueDate'}, 
                uid        => $loan->{'uid'},
                phone           => $loan->{'phone'},
            };
        }
        $query->finish;
            
# Prepare the group title
        if ( scalar(@loanRecs) != 0 )
        {
            my @data = ();

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

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

    return \@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);
    }
    return \@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 getGuardianAddress{
    my ($dbh,$uid)=@_;
    my $address={};
    my $sth=$dbh->prepare("select gaddrLine1 addrLine1,gaddrLine2 addrLine2,gcity city,gzip zip,gstate state from opl_guardian where uid=$uid limit 1");
    $sth->execute();
    $address =$sth->fetchrow_hashref;
    $sth->finish;
    return $address;	
    
}

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

        if ( $preUid == -1 ) { $iduser = $loan; }

        if ( $preUid ne $loan->{'uid'} )
        {
		$preUid = $loan->{'uid'};
		if($iduser->{'addrLine1'} eq ''){
		    my $address=getGuardianAddress($dbh,$iduser->{'uid'});
		    $iduser->{'addrLine1'}=$address->{'addrLine1'};
		    $iduser->{'addrLine2'}=$address->{'addrLine2'};
		    $iduser->{'city'}=$address->{'city'};
		    $iduser->{'zip'}=$address->{'zip'};
		    $iduser->{'state'}=$address->{'state'};
		}
            # 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,$l);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0,$l);

        $loan->{'title'} =~ s/\\/\\\\/g;
        $loan->{'title'} =~ s/\"/\\\"/g;
        push @loanRecs, {
            grade           => $loan->{'grade'},
            title           => decode('utf8',$loan->{'title'}),
            dewey           => decode('utf8',$loan->{'dewey'}), 
            barcode         => $loan->{'barcode'},
            price           => decode('utf8',$loan->{'price'}),
            dateDue         => decode('utf8',$loan->{'dateDue'}),
            dateLoan        => decode('utf8',$loan->{'dateLoan'}),
            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 formatOdDataRow{
    my ($odArr)=@_;
    my @retArr=();
    
    foreach my $r(@$odArr){
        my$type=$r->{'type'};
        if($type =~ m/odNotice|unpaidItem/g){
            my $tDef    = $tblDef->{$type}->{'title'};
            my $f       =$tDef->{'font'};
            my $fs      =$tDef->{'fontSize'};
            my $dddLen = $fs*$pageN->string_width($f,'...') ;
            my $width   =$tDef->{'width'} - $tDef->{'padding'} - $dddLen;

            my $titleArr=formatStr_nRow($pageN,$f,$fs,$r->{'title'},$width,2);
            $r->{'title'}=@$titleArr[0];
            push @retArr,$r;
            if(scalar(@$titleArr)>1){
               my($title,$barcode,$type)=(@$titleArr[1],$r->{'barcode'},$r->{'type'});
               push @retArr,{title=>$title,barcode=>$barcode,type=>$type};
            }
        }
        else{
            push @retArr,$r;
        }
    }
    return \@retArr;
}
#----------------------------------------------------------------------------------------------------
 sub printOd_pdf{
    my($odData)=@_; 
    #my $pageN  ;
    my $subPageCount = 1;
    my $xRow=4;
    my ($x,$y);
    my $totalPage=0;
    my $rowPerPage;
    my $printAdd=0;
    if($prtType eq 'address'){
        $printAdd=1;
    }
    
    
    if($optPage ==1){
       $rowPerPage=38; 
       #$printAdd=1;
    }
    elsif($optPage ==2){
        $rowPerPage=17;
    }
    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)) + 50  ;
      #my $aaa=@$odData[$i]->{'oditems'};
      my $aaa=formatOdDataRow(@$odData[$i]->{'oditems'});

       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-35 , '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 + 38 - (scalar(@lang))*ROW_HEIGHT_10;
           $y2 = $y2 - Y_ADDRESS + 38 - (scalar(@lang))*ROW_HEIGHT_10 ;
           $xRow  += ceil((ROW_HEIGHT_10/ROW_HEIGHT)*4) +  scalar(@lang);

       }
       
       my $j=0;  
       $xRow  +=1; 
       my ($curBc,$preBc,$nextBc,$curType,$nextType ,$preType)=('','','','','','');
       my $curPg=$pNo; 
       for($j=0; $j<$rowPerPage - $xRow && $j < scalar(@$aaa); $j++){
           $curBc = @$aaa[$j]->{'barcode'};
           $curType = @$aaa[$j]->{'type'};
           if(($curType eq $preType && $curBc eq $preBc) || ($curType =~m/HeaderSub/g  )){
           #if(( $curBc eq $preBc) || ($curType =~m/headerRow/g && $preType =~m/headerRow/g )){
                @$aaa[$j]->{'barcode'} ='' if($curType =~ m/odNotice|unpaidItem/g );
                $y1 +=6; $y2 +=6;
           }
           elsif($curType =~ m/odNotice|unpaidItem/g || ($j==0 && $curType !~ m/text/g) ){ 
               $pageN->line($x1, $y2, $x2, $y2);
               
           }
           if($j+1 < scalar(@$aaa)){
                $nextBc=@$aaa[$j+1]->{'barcode'};
                $nextType = @$aaa[$j+1]->{'type'};
           }
           else{
               $pageN->line($x1, $y1, $x2, $y1);
           }
           if(($curType =~ m/odNoticeData|unpaidItemData|text/ && $curBc ne $nextBc) || ($curType =~m/HeaderMain/g && $nextType !~ m/HeaderSub/g) ){
                $pageN->line($x1, $y1, $x2, $y1);
           }
           writeRow($pageN,$x1,$y1,$x2,$y2,@$aaa[$j]);

  
           $y1 -= ROW_HEIGHT;
           $y2 -= ROW_HEIGHT;
           $preBc=@$aaa[$j]->{'barcode'};
           $preType = $curType;

        }


       $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)) + 30;
                         
               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-35 , 'Page ' . $pNo .'/' . $totalPage);
               ##writeHeaderRow($pageN,$x1,$y1,$x2,$y2+2);
               $y2 =$y1;
               $y1 -= ROW_HEIGHT;
               
           }# if(($j % 10)==0)
          
          
           $curBc = @$aaa[$j]->{'barcode'};
           $curType = @$aaa[$j]->{'type'};
           if($curBc eq $preBc ){
                @$aaa[$j]->{'barcode'} ='' if($curType =~ m/odNotice|unpaidItem/g );
                $y1 +=6; $y2 +=6;
           }
           elsif(($curType =~ m/odNoticeData|unpaidItemData/g ) ||$curType =~ m/HeaderMain/g  &&  $preType =~ m/text/g ){ 
                $pageN->line($x1, $y2, $x2, $y2);
           }
           if($j+1 < scalar(@$aaa)){
               $nextBc=@$aaa[$j+1]->{'barcode'};
                $nextType = @$aaa[$j+1]->{'type'};
           }
           else{
               $pageN->line($x1, $y1, $x2, $y1);
           }
           if(($curType =~ m/odNoticeData|unpaidItemData|text/ && $curBc ne $nextBc )  || ($curType =~m/HeaderMain/g && ($nextType !~ m/HeaderSub/g)) ){
                $pageN->line($x1, $y1, $x2, $y1);
           }
           writeRow($pageN,$x1,$y1,$x2,$y2,@$aaa[$j]);
           $y1 -= ROW_HEIGHT;
           $y2 -= ROW_HEIGHT;
           $preBc=@$aaa[$j]->{'barcode'};
           $preType = $curType;
       }#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-35 , '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 - ROW_HEIGHT);
        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,303, $msg_y +10  , $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+1)*($ii)- 5 ,@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 eq "left"){   
        $pageN->stringl($f,$fs,$x,$y,$txtStr);
    }
    elsif ($pos eq "right"){   
        $pageN->stringr($f,$fs,$x,$y,$txtStr);
    }
    else{   
        $pageN->stringc($f,$fs,$x,$y,$txtStr);
    }
   # print $pageN->string_width($f,$txtStr) ." -- $txtStr\n";
}
#----------------------------------------------------------------------------------------------------
# 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_nRow{
    my($pageN,$f,$fs,$inStr,$len,$nRow)=@_;
    my $outStr=$inStr;
    my @outStrArr=();
    $outStr =~ s/\s+$//g;
    my $n=1;
    my $sWidth= $fs * $pageN->string_width($f,$outStr);
    if($sWidth<=$len){
        push @outStrArr,$outStr;
        return \@outStrArr;
    }
    while($n<$nRow && $outStr ne ''){
        while($sWidth > $len){
           $outStr =~ s/\S+$//g;
           $outStr =~ s/\s+$//g;
           $sWidth= $fs * $pageN->string_width($f,$outStr );
        }
        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;
            }
        }
        while ($numUnclose>0){
            $outStr =$outStr . ')'; 
            $numUnclose -=1;
        }
        push @outStrArr,$outStr;
        $inStr =substr($inStr,length($outStr)); 
        $inStr =~ s/^\s+//g;
        $outStr=$inStr;
        $sWidth= $fs * $pageN->string_width($f,$outStr);
        $n++;
    }
    if($n >=$nRow && $inStr ne ''){
        push @outStrArr,formatStr($pageN,$f,$fs,$inStr,$len);
    }
    return \@outStrArr  ;
}

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

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

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

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

#----------------------------------------------------------------------------------------------------
sub writeRow{
    my($pageN,$x1,$y1,$x2,$y2,$text)=@_;
      my ($font,$fontSize) = ($f1,10);  
      $font =$text->{'font'} if(defined $text->{'font'} );
      if($text->{'type'} =~ m/odNotice|unpaidItem/g){
          writeRow_headerData($pageN,$font,$fontSize,$x1,$y1,$x2,$y2,$text);
      }
      elsif($text->{'type'} eq 'text'){
          writeRowText($pageN,$font,$fontSize,$x1,$y1,$x2,$y2,$text);
      }

}
#----------------------------------------------------------------------------------------------------
sub writeRowText{
    my($pageN,$font,$fontSize,$x1,$y1,$x2,$y2,$text)=@_;
    writeCell('left',$pageN,$font,$fontSize, $x1, $y1 +5,   $text->{'text'}) ;
}
#----------------------------------------------------------------------------------------------------
sub writeRow_headerData{
    my($pageN,$font,$fontSize,$x1,$y1,$x2,$y2,$text)=@_;
 
    my $x=$x1;
    my $type=$text->{'type'};
    my $tDef    = $tblDef->{$type};
    my ($align,$width,$font,$fontSize,$padding);
    $pageN->line($x, $y1, $x,$y2);   
    my $xPos;
    my $columns;
    if($type =~ m/odNotice/g) {
        $columns=$headers->{'odNotice'};
    }
    else{
        $columns=$headers->{'unpaidItem'};
    }
    foreach my $field(@$columns){
        if($field eq 'title' && $optTitle !=1){
            next;
        }
        else{
            $align  =$tDef->{$field}->{'align'};
            $width  =$tDef->{$field}->{'width'};
            $font   =$tDef->{$field}->{'font'};
            $padding   =$tDef->{$field}->{'padding'};
            $fontSize=$tDef->{$field}->{'fontSize'};
            $xPos =$x;
            if($align eq 'center'){
                $xPos +=ceil($width/2);
            }
            elsif($align eq 'right'){
                $xPos +=$width -$padding;
            }
            else{
                $xPos +=$padding;
            }
            writeCell($align,$pageN,$font,$fontSize,$xPos, $y1 +5, $text->{$field}) ; 
            $x +=$width;
        }
        $pageN->line($x, $y1, $x,$y2);   

    }

    $pageN->line($x2, $y1, $x2,$y2);
} 
#----------------------------------------------------------------------------------------------------