#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Circulation qw(
    circ_infoRecord
);

use Opals::Search qw(
    srch_searchRecord
);

use Time::localtime;

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

use Opals::Template qw(
    tmpl_read
    tmpl_write
);


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

my $cgi = CGI->new;
my $input = $cgi->Vars();

my $srchField = $cgi->param('sf');
($srchField) || ($srchField = 0);
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/odlist.tmpl',
            srchField       => $srchField,
#            reqPermission   => 'user_delete',
        }
);

#if ($permission && $permission->{'user_delete'}) 
#{
#    my $input = $cgi->Vars();
#    if ($input->{'op'} eq 'delete') 
#    {
#        if (user_delete($dbh, $input)) 
#        {
#            $template->param(success => 1);
#        }
#        else 
#        {
#            $template->param(error => 1);
#        }
#    }
#    $template->param(userDel => 1);
#}

    my $sort1 = $input->{'sort1'};
    my $sort2 = $input->{'sort2'};
    if ( $sort1 eq '' )
    {
        $sort1 = "grade";
    }
    if ( $sort2 eq '' )
    {
        $sort2 = "firstname";
    }
    GetLoan($dbh, $template, $sort1, $sort2);

    $template->param(havegrade => 1);
    $template->param(haveteacher => 1);
    $template->param(havehomeroom => 1);
    $template->param(havebuildingcode => 1);    
    $template->param(haveusername => 1);    
    $template->param("have" . $sort1 => 0);

    $template->param(sort1 => $sort1);
    $template->param(sort2 => $sort2);

    $template->param(active_report => 1);
    tmpl_write($dbh, $cgi, $cookie, $template);

#--------------------------------------------------------
sub GetLoan
{
    my ($dbh, $template, $sort1, $sort2) = @_;

    my $szSQL = "
select  teacher, grade, homeroom, buildingcode, username,
        firstname, lastname, barcode, dateLoan, dateDue,
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user, opl_loan 
where   opl_user.uid = opl_loan.uid && dateReturn is null
order by ";

    my @group = ($sort1, $sort2);
    my $grpval = {teacher => '', grade => '', homeroom => '', buildingcode => '', username => ''};

    if ( ($sort2 eq 'dewey') || ($sort2 eq 'title') )
    {
        GetLoanExt($dbh, $template, $sort1, $sort2);
        return;
    }

    $szSQL .= $sort1 . ", " . $sort2;

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my $bZebraServerDown = 0;
    
    my $teacher = '';
    my $grade = 0;
    my $homeroom = '';
    my $buildingcode = '';
    my $username = '';
    
    my @loanRecs = ();
    my @DataRecs = ();
    my $GroupTitle = '';
    
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if ( $loan->{'deltaDueDate'} < 0 )
        {
            next;
        }
        
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);
        
        my $pqf = "\@attr 1=5000 $loan->{'barcode'}";
        my ($resultSize, $result) = srch_searchRecord($dbh, 0, 'b', $pqf, $ENV{'Z_INDEX_BASE'}, 1);
        if ($result) 
        {
            my ($numTotal, $numLoan, $numReserve, $numHold, $items)
                = circ_infoRecord($dbh, $result->[0]->{'rid'}, $result->[0]->{'itemList'});
            $loan->{'rid'}     = $result->[0]->{'rid'};
            $loan->{'title'}   = $result->[0]->{'title'};
            $loan->{'author'}  = $result->[0]->{'author'};
            $loan->{'pubName'} = $result->[0]->{'pubName'};
            $loan->{'pubDate'} = $result->[0]->{'pubDate'};
            $loan->{'dewey'}   = $items->[0]->{'callnumber'}
        }
        else 
        {
            $bZebraServerDown = 1;            
            next;
        }
        
# Prepare the group title
        if ( $loan->{$group[0]} ne $grpval->{$group[0]} )
        {
            if ( @loanRecs > 0 )
            {
                my @data = @loanRecs;
                push @DataRecs, { group => \@data, $group[0] => $GroupTitle }; #$grpval->{$group[0]} };
            }    
            
            if ( $group[0] eq 'username' )
            {
                $GroupTitle = $loan->{'lastname'} . ", " . $loan->{'firstname'} . " (" . $loan->{'username'} . ")";
            }
            else
            {
                $GroupTitle = $loan->{$group[0]};
            }
            $grpval->{$group[0]} = $loan->{$group[0]};
            @loanRecs = ();
        }

# Format each row of data
        if ( !$bZebraServerDown )
        {
            push @loanRecs, {title => $loan->{'title'}, dewey => $loan->{'dewey'}, 
                    barcode => $loan->{'barcode'}, dateDue => $loan->{'dateDue'},
                    teacher => $loan->{'teacher'}, grade => $loan->{'grade'}, 
                    firstname => $loan->{'firstname'}, lastname => $loan->{'lastname'},
                    homeroom => $loan->{'homeroom'}, buildingcode => $loan->{'buildingcode'},
                    username => $loan->{'username'}}
        }
        else
        {               
            push @loanRecs, {title => '', dewey => '', 
                    barcode => $loan->{'barcode'}, dateDue => $loan->{'dateDue'},
                    teacher => $loan->{'teacher'}, grade => $loan->{'grade'}, 
                    firstname => $loan->{'firstname'}, lastname => $loan->{'lastname'},
                    homeroom => $loan->{'homeroom'}, buildingcode => $loan->{'buildingcode'},
                    username => $loan->{'username'}}
        }
    }

# Store the last group    
    if ( @loanRecs > 0 )
    {
        my @data = @loanRecs;
        push @DataRecs, { group => \@data, $group[0] => $GroupTitle }; #$grpval->{$group[0]} };
    }

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

#--------------------------------------------------------
sub GetLoanExt
{
    my ($dbh, $template, $sort1, $sort2) = @_;

    my $szSQL = "
select  teacher, grade, homeroom, buildingcode, username, 
        firstname, lastname, barcode, dateLoan, dateDue,
        to_days(now()) - to_days(dateDue) as deltaDueDate 
from    opl_user, opl_loan 
where   opl_user.uid = opl_loan.uid && dateReturn is null";

    my $grpval = {teacher => '', grade => '', homeroom => '', buildingcode => '', username => ''};

    my $query = $dbh->prepare($szSQL);
    my $bResult = $query->execute();
    my $bZebraServerDown = 0;
    
    my $teacher = '';
    my $grade = 0;
    my $homeroom = '';
    my $buildingcode = '';
    
    my @loanRecs = ();
    my @DataRecs = ();
    
    while ( my $loan = $query->fetchrow_hashref() )
    {
        if ( $loan->{'deltaDueDate'} < 0 )
        {
            next;
        }
        
        $loan->{'dateLoan'} = date_text($loan->{'dateLoan'}, 0);
        $loan->{'dateDue'}  = date_text($loan->{'dateDue'}, 0);

        my $pqf = "\@attr 1=5000 $loan->{'barcode'}";
        my ($resultSize, $result) = srch_searchRecord($dbh, 0, 'b', $pqf, $ENV{'Z_INDEX_BASE'}, 1);
        if ($result) 
        {
            my ($numTotal, $numLoan, $numReserve, $numHold, $items)
                = circ_infoRecord($dbh, $result->[0]->{'rid'}, $result->[0]->{'itemList'});
            $loan->{'rid'}     = $result->[0]->{'rid'};
            $loan->{'title'}   = $result->[0]->{'title'};
            $loan->{'author'}  = $result->[0]->{'author'};
            $loan->{'pubName'} = $result->[0]->{'pubName'};
            $loan->{'pubDate'} = $result->[0]->{'pubDate'};
            $loan->{'dewey'}   = $items->[0]->{'callnumber'}
        }
        else 
        {
            $bZebraServerDown = 1;            
            next;
        }

# Format each row of data
        if ( !$bZebraServerDown )
        {
            push @loanRecs, {title => $loan->{'title'}, dewey => $loan->{'dewey'}, 
                    barcode => $loan->{'barcode'}, dateDue => $loan->{'dateDue'},
                    teacher => $loan->{'teacher'}, grade => $loan->{'grade'}, 
                    firstname => $loan->{'firstname'}, lastname => $loan->{'lastname'},
                    homeroom => $loan->{'homeroom'}, buildingcode => $loan->{'buildingcode'},
                    username => $loan->{'username'}}
        }
        else
        {               
            push @loanRecs, {title => '', dewey => '', 
                    barcode => $loan->{'barcode'}, dateDue => $loan->{'dateDue'},
                    teacher => $loan->{'teacher'}, grade => $loan->{'grade'}, 
                    firstname => $loan->{'firstname'}, lastname => $loan->{'lastname'},
                    homeroom => $loan->{'homeroom'}, buildingcode => $loan->{'buildingcode'},
                    username => $loan->{'username'}}
        }         
    }
        
    @loanRecs = sort { ($a->{$sort1} cmp $b->{$sort1}) || ($a->{$sort2} cmp $b->{$sort2}) } @loanRecs;
    
# Prepare the group title
    my @dataGrp = ();
    my $GroupTitle = '';    
    foreach my $loan (@loanRecs)
    {
        if ( $loan->{$sort1} ne $grpval->{$sort1} )
        {
            if ( @dataGrp > 0 )
            {
                my @data = @dataGrp;
                push @DataRecs, { group => \@data, $sort1 => $GroupTitle }; #$grpval->{$sort1} };
            }
            if ( $sort1 eq 'username' )
            {
                $GroupTitle = $loan->{'lastname'} . ", " . $loan->{'firstname'} . " (" . $loan->{'username'} . ")";
            }
            else
            {
                $GroupTitle = $loan->{$sort1};
            }

            $grpval->{$sort1} = $loan->{$sort1};
            @dataGrp = ();
        }
        push @dataGrp, $loan;
    }

# Store the last group    
    if ( @dataGrp > 0 )
    {
        my @data = @dataGrp;
        push @DataRecs, { group => \@data, $sort1 => $GroupTitle }; #$grpval->{$sort1} };
    }

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