#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

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

use Opals::Date qw(
    date_time_text
);

use Opals::Equipment qw(
    
    eq_item_findByBarcode

);
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

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

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


my ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'eqmnt/report/itemOnLoan.tmpl',
                reqPermission   => 'eq_report',
            }
        );

my $pref = tmpl_preference($dbh);

my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
my $sort1       = $input->{'sort1'};
my $sort2       = $input->{'sort2'};
my $overdueOnly = $input->{'overdue'};
    if (!$overdueOnly){
        $overdueOnly = 0;
    }

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

my $firstLoad = (!$dateFrom || !$dateTo)? 1:0;

    if (! $dateFrom && ! $dateTo){
        $dateFrom   = $todayStr;
        $dateTo     = $todayStr;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo   .= " 23:59:59";
    }

    if ($firstLoad){
        $template->param(username => 1);
        $template->param(ReportMsg => 1);
        goto NoProcess;
    }
if ($permission && $permission->{'eq_report'}) {
   
    if ( $sort1 eq 'username'){
        my $orderby = $input->{'orderby'};
        getOnLoan_user($dbh, $template, $dateFrom, $dateTo, $orderby,$overdueOnly);
    }
    elsif ($sort1 eq 'teacher'){
        getOnLoan_teacher($dbh, $template, $dateFrom, $dateTo ,$overdueOnly);
    }
    elsif ($sort1 eq 'homeroom'){
        getOnLoan_homeroom($dbh, $template, $dateFrom, $dateTo ,$overdueOnly);
    }
    elsif ($sort1 eq 'grade'){
        getOnLoan_grade($dbh, $template, $dateFrom, $dateTo ,$overdueOnly);
    }
}  

NoProcess:

    $template->param(dateFrom   => substr($dateFrom,0,10));
    $template->param(dateTo     => substr($dateTo,0,10));
    $template->param(sort1      => $sort1);    
    $template->param(sort2      => $sort2);    

    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
        );
    }

tmpl_write($dbh, $cgi, $cookie, $template);

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

sub getOnLoan_user{
    my ($dbh, $template, $dateFrom, $dateTo, $orderby,$overdueOnly) = @_;
    my $eqOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
    my $sql = "select l.*, u.* , l.dateDue < now() as overdue
        from eq_loan as l inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && dateLoan >= ? && dateLoan <= ? ";
    if ($overdueOnly){
        $sql .= " && l.dateDue < now()";
    }

    if ($orderby eq 'name'){
        $sql .= " order by concat(lastname, firstname, 'zzzzzz'), username";
    }
    else{
        $sql .= " order by concat(lastname, firstname, 'zzzzzz'), concat('homeroom','zzzzzzz'), username";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = eq_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'rname'} = $itemInfo->{'rname'};
        $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
        $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
        $totalOnLoan += 1;
        if (!$eqOnLoan->{$loan->{'uid'}}){
             $eqOnLoan->{$loan->{'uid'}}->{'uid'} = $loan->{'uid'};
             $eqOnLoan->{$loan->{'uid'}}->{'firstname'} = $loan->{'firstname'};
             $eqOnLoan->{$loan->{'uid'}}->{'lastname'} = $loan->{'lastname'};
             $eqOnLoan->{$loan->{'uid'}}->{'grade'} = $loan->{'grade'};
             $eqOnLoan->{$loan->{'uid'}}->{'homeroom'} = $loan->{'homeroom'};
             $eqOnLoan->{$loan->{'uid'}}->{'building'} = $loan->{'building'};
             $eqOnLoan->{$loan->{'uid'}}->{'teacher'} = $loan->{'teacher'};
             $eqOnLoan->{$loan->{'uid'}}->{'count'} = $row;
             $row++;
        }
        push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
=item  
    if ($orderby =~ m/home/i) {
        @eqOnLoanList = sort {
                $a->{'lastname'} cmp $b->{'lastname'}
                    ||
                $a->{'lastname'} <=> $b->{'lastname'}
                    ||
                $a->{'firstname'} cmp $b->{'firstname'}
                    ||
                $a->{'firstname'} <=> $b->{'firstname'}
            } @eqOnLoanList;
    }
    else{
         @eqOnLoanList = sort {
                $a->{'homeroom'} cmp $b->{'homeroom'}
                    ||
                $a->{'homeroom'} <=> $b->{'homeroom'}
         } @eqOnLoanList;
    }
=cut

    my @sorted = sort { $a->{'lastname'} cmp $b->{'lastname'}} @eqOnLoanList;

    $template->param(eqOnLoan => \@sorted);
    $template->param(
        username => 1,
        sort1   => "username",
        orderbyHRoom => ($orderby eq 'room')? 1:0,
        orderbyName => ($orderby eq 'name')? 1:0,
        overdueOnly=> $overdueOnly,
        totalOnLoan => $totalOnLoan,
    );
}


sub getOnLoan_teacher{
    my ($dbh, $template, $dateFrom, $dateTo,$overdueOnly) = @_;
    my $eqOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
    my $sql = "select l.*, u.* , l.dateDue < now() as overdue 
        from    eq_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&    dateLoan >= ? && dateLoan <= ? ";


    if ($overdueOnly){
        $sql .= " && l.dateDue < now()";
    }

    $sql .= " order by concat(teacher, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(homeroom,'zzzzzz') ";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = eq_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'rname'} = $itemInfo->{'rname'};
        $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
        $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
        $totalOnLoan += 1;
        if (!$loan->{'teacher'} || $loan->{'teacher'} eq ""){
            $loan->{'teacher'} = " N/A";
        }
        if (!$eqOnLoan->{$loan->{'teacher'}}){
            $eqOnLoan->{$loan->{'teacher'}}->{'teachername'} = $loan->{'teacher'};
            $eqOnLoan->{$loan->{'teacher'}}->{'homeroom'} = $loan->{'homeroom'};
            $eqOnLoan->{$loan->{'teacher'}}->{'building'} = $loan->{'building'};
            $eqOnLoan->{$loan->{'teacher'}}->{'count'} = $row;
            $row++;
        }
        push @{$eqOnLoan->{$loan->{'teacher'}}->{'group'}}, $loan;
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(sort keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanTeacher => \@eqOnLoanList);
    $template->param(
        teacher => 1,
        sort1   => "teacher",
        overdueOnly=> $overdueOnly,
        totalOnLoan => $totalOnLoan,
    );

}

sub getOnLoan_homeroom{
    my ($dbh, $template, $dateFrom, $dateTo,$overdueOnly) = @_;
    my $eqOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    eq_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&   dateLoan >= ? && dateLoan <= ? ";

    if ($overdueOnly){
        $sql .= " && l.dateDue < now()";
    }
    $sql .= " order by concat(homeroom, 'zzzzzz' ), concat(buildingcode, 'zzzzzz'), concat(teacher,'zzzzzz') ";

    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = eq_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'rname'} = $itemInfo->{'rname'};
        $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
        $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
        $totalOnLoan += 1;
     
        if (!$loan->{'homeroom'} || $loan->{'homeroom'} eq ""){
            $loan->{'homeroom'} = " N/A";
        }
        if (!$eqOnLoan->{$loan->{'homeroom'}}){
             $eqOnLoan->{$loan->{'homeroom'}}->{'homeroom'} = $loan->{'homeroom'};
             $eqOnLoan->{$loan->{'homeroom'}}->{'building'} = $loan->{'building'};
             $eqOnLoan->{$loan->{'homeroom'}}->{'teacher'} = $loan->{'teacher'};
             $eqOnLoan->{$loan->{'homeroom'}}->{'count'} = $row;
             $row++;
        }
        push @{$eqOnLoan->{$loan->{'homeroom'}}->{'group'}}, $loan;
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k( sort keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanHomeroom => \@eqOnLoanList);
    $template->param(
        homeroom => 1,
        sort1   => "homeroom",
        overdueOnly=> $overdueOnly,
        totalOnLoan => $totalOnLoan,
    );
}

sub getOnLoan_grade{
    my ($dbh, $template, $dateFrom, $dateTo,$overdueOnly) = @_;
    my $eqOnLoan;
    my $row = 0;
    my $totalOnLoan = 0;
    my $sql = "select l.*, u.*, l.dateDue < now() as overdue
        from    eq_loan as l inner join opl_user as u on l.uid = u.uid
        where   dateReturn is null &&   dateLoan >= ? && dateLoan <= ? ";
    if ($overdueOnly){
        $sql .= " && l.dateDue < now()";
    }
    
    $sql .= " order by concat(grade, 'zzzzzz' ), concat(lastname,firstname, 'zzzzzz')";
    my $sth = $dbh->prepare($sql);
    $sth->execute($dateFrom, $dateTo);
    while (my $loan = $sth->fetchrow_hashref){
        my $itemInfo = eq_item_findByBarcode($dbh,$loan->{'barcode'});
        $loan->{'rname'} = $itemInfo->{'rname'};
        $loan->{'dateLoanFormat'} = date_time_text ($loan->{'dateLoan'}, 0, 'en');
        $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 0 ,'en');
        $totalOnLoan += 1;

        if (!$loan->{'grade'} || $loan->{'grade'} eq ""){
            $loan->{'grade'} = " N/A";
        }
        if (!$eqOnLoan->{$loan->{'grade'}}){
             $eqOnLoan->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
             $eqOnLoan->{$loan->{'grade'}}->{'count'} = $row;
             $row++;
        }
        push @{$eqOnLoan->{$loan->{'grade'}}->{'group'}}, $loan;
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(sort keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanGrade => \@eqOnLoanList);
    $template->param(
        grade   => 1,
        sort1   => "grade",
        overdueOnly=> $overdueOnly,
        totalOnLoan => $totalOnLoan,
    );
}