#!/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_text
    date_time_text
);

use Opals::Tb_Record qw(
    
    tb_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/odnotice.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'};
if ($sort1 eq '') {
    $sort1 = "homeroom";
}

if (!$sort2){
    if ($sort1 eq "username"){
        $sort2 = "title";    }
    else{
        $sort2 = "username";
    }
}   
elsif($sort1 eq $sort2){
    if($sort1 eq "title"){
        $sort2 = "username";
    }
    else{
        $sort2 = "title";
    }
}
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;
    }
   
    my @eqOnLoan = ();
    my $rpFilter = {};
       $rpFilter->{'dateFrom'} = $dateFrom;
       $rpFilter->{'dateTo'} = $dateTo;
    my $loanCount = 0;
    my $filter ;
       $filter->{'dateFrom'} = $dateFrom;
       $filter->{'dateTo'} = $dateTo;

    if ( $sort1 eq 'username'){
        my $grpList = getOverdueList_user($dbh, $rpFilter); 
        $filter->{'user'} = 1;
        my $totalLoan;
        foreach my $rec(@$grpList){
            $filter->{'uid'}    =$rec->{'uid'};
            $rec->{'loanList'}  =getOnloanItemList($dbh,$filter,$sort2);
            $loanCount =scalar(@{$rec->{'loanList'}});
            $totalLoan +=$loanCount;
            push @eqOnLoan,$rec ;
        }
        $template->param (
            username => 1,
            totalOnLoan => $totalLoan
        );
        $template->param(eqOnLoan => \@eqOnLoan);
    }
    elsif ($sort1 eq 'grade'){
        my $grpList = getOverdueList_grade($dbh, $rpFilter);
        $filter->{'grade'} = 1;
        my $totalLoan;
        foreach my $rec(@$grpList){
            $filter->{'grade'}    =$rec->{'grade'} ?$rec->{'grade'} :"N/A";
            $rec->{'loanList'}  =getOnloanItemList($dbh,$filter, $sort2 );
            $loanCount =scalar(@{$rec->{'loanList'}});
            $totalLoan +=$loanCount;
            push @eqOnLoan,$rec ;
        }
        $template->param (
            grade => 1,
            totalOnLoan => $totalLoan
        );
        $template->param(eqOnLoanGrade => \@eqOnLoan);
    }
    elsif ($sort1 eq 'homeroom'){
        my $grpList = getOverdueList_homeroom($dbh, $rpFilter);
        $filter->{'hoomeroom'} = 1;
        my $totalLoan;
        foreach my $rec(@$grpList){
            $filter->{'homeroom'}    =$rec->{'homeroom'} ?$rec->{'homeroom'} :"N/A";
            $rec->{'loanList'}  =getOnloanItemList($dbh,$filter, $sort2 );
            $loanCount =scalar(@{$rec->{'loanList'}});
            $totalLoan +=$loanCount;
            push @eqOnLoan,$rec ;
        }
        $template->param (
            homeroom => 1,
            totalOnLoan => $totalLoan
        );
        $template->param(eqOnLoanHomeRoom => \@eqOnLoan);
    }
    elsif ($sort1 eq 'teacher'){
        my $grpList = getOverdueList_teacher($dbh, $rpFilter);
        $filter->{'teacher'} = 1;
        my $totalLoan;
        foreach my $rec(@$grpList){
            $filter->{'teacher'}    =$rec->{'teacher'} ?$rec->{'teacher'} :"N/A";
            $filter->{'homeroom'}    =$rec->{'homeroom'} ?$rec->{'homeroom'} :"N/A";
            $filter->{'buildingcode'}    =$rec->{'buildingcode'} ?$rec->{'buildingcode'} :"N/A";
            $rec->{'loanList'}  =getOnloanItemList($dbh,$filter, $sort2);
            $loanCount =scalar(@{$rec->{'loanList'}});
            $totalLoan +=$loanCount;
            push @eqOnLoan,$rec ;
        }
        $template->param (
            teacher => 1,
            totalOnLoan => $totalLoan
        );
        $template->param(eqOnLoanTeacher => \@eqOnLoan);
    }
    elsif ($sort1 eq 'rname'){
        my $grpList = getOverdueList_eqName($dbh, $rpFilter);
        $filter->{'rid'} = 1;
        my $totalLoan;
        foreach my $rec(@$grpList){
            $filter->{'rid'}    =$rec->{'rid'};
            $rec->{'loanList'}  =getOnloanItemList($dbh,$filter,$sort2);
            $loanCount =scalar(@{$rec->{'loanList'}});
            $totalLoan +=$loanCount;
            push @eqOnLoan,$rec ;
        }
        $template->param (
            rName => 1,
            totalOnLoan => $totalLoan
        );
        $template->param(eqOnLoanEqName => \@eqOnLoan); 
    }

NoProcess:

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

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

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

#----------------------------------------------
sub getOverdueList_user {
    my ($dbh, $filter) = @_;
    my ($dateFrom, $dateTo) = ($filter->{'dateFrom'}, $filter->{'dateTo'});
    my @retList=();
    my $sql =  (<<_SQL_);
        select distinct u.uid, u.firstname, u.lastname , u.grade, u.homeroom,u.teacher, u.buildingcode, email, yeargraduation
        from eq_loan l inner join
            (eq_items i inner join eq_records r using (rid)) using (barcode)
        inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && to_days(l.dateDue) < to_days(now()) &&
                                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                                    to_days(l.dateDue) <= to_days('$dateTo')
        order by concat(lastname,firstname,'zzzzzz'), concat(homeroom,'zzzzzz')
_SQL_
        
    my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $count=1;
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'count'} = $count++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub getOverdueList_grade {
    my ($dbh, $filter) = @_;
    my ($dateFrom, $dateTo) = ($filter->{'dateFrom'}, $filter->{'dateTo'});
    my @retList=();
    my $sql =  (<<_SQL_);
        select distinct u.grade  from eq_loan l inner join
            (eq_items i inner join eq_records r using (rid)) using (barcode)
        inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && to_days(l.dateDue) < to_days(now()) &&
                                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                                    to_days(l.dateDue) <= to_days('$dateTo')
        order by grade ,concat(buildingcode,'zzzzzz'),concat(homeroom,'zzzzzz')
_SQL_
        
    my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $count=1;
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'count'} = $count++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub getOverdueList_homeroom {
    my ($dbh, $filter) = @_;
    my ($dateFrom, $dateTo) = ($filter->{'dateFrom'}, $filter->{'dateTo'});
    my @retList=();
    my $sql =  (<<_SQL_);
        select distinct u.homeroom, teacher, buildingcode 
        from eq_loan l inner join
            (eq_items i inner join eq_records r using (rid)) using (barcode)
        inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && to_days(l.dateDue) < to_days(now()) &&
                                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                                    to_days(l.dateDue) <= to_days('$dateTo')
        order by concat(homeroom,'zzzzzz'), concat(buildingcode,'zzzzzz'), concat(teacher,'zzzzzz')
_SQL_
        
    my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $count=1;
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'count'} = $count++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub getOverdueList_teacher {
    my ($dbh, $filter) = @_;
    my ($dateFrom, $dateTo) = ($filter->{'dateFrom'}, $filter->{'dateTo'});
    my @retList=();
    my $sql =  (<<_SQL_);
        select distinct u.teacher, homeroom, buildingcode
            from eq_loan l inner join
            (eq_items i inner join eq_records r using (rid)) using (barcode)
        inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && to_days(l.dateDue) < to_days(now()) &&
                                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                                    to_days(l.dateDue) <= to_days('$dateTo')
        order by concat(teacher,'zzzzzz'), concat(homeroom,'zzzzzz'),concat(buildingcode,'zzzzzz')
_SQL_
        
    my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $count=1;
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'count'} = $count++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub getOverdueList_eqName {
    my ($dbh, $filter) = @_;
    my ($dateFrom, $dateTo) = ($filter->{'dateFrom'}, $filter->{'dateTo'});
    my @retList=();
    my $sql =  (<<_SQL_);
        select distinct r.rid, r.rname
            from eq_loan l inner join
            (eq_items i inner join eq_records r using (rid)) using (barcode)
        inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && to_days(l.dateDue) < to_days(now()) &&
                                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                                    to_days(l.dateDue) <= to_days('$dateTo')
        order by  r.rname 
_SQL_
    my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $count=1;
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'count'} = $count++;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub getOnloanItemList{
    my($dbh,$filter,$sortBy)=@_;
    my @loanList=();
    my @sqlCondVals=();
    my ($dateFrom, $dateTo) = ($filter->{'dateFrom'}, $filter->{'dateTo'});
    
    my $sql=(<<_SQL_);
    select u.uid, u.firstname, u.lastname , u.grade, u.homeroom,u.teacher, u.buildingcode, email, yeargraduation,
        l.barcode, l.dateDue, r.rname, to_days(now()) - to_days(l.dateDue) as deltaDateDue
        from eq_loan l inner join
            (eq_items i inner join eq_records r using (rid)) using (barcode)
        inner join opl_user as u on l.uid = u.uid
        where dateReturn is null && to_days(l.dateDue) < to_days(now()) &&
                                    to_days(l.dateDue) >= to_days('$dateFrom') &&
                                    to_days(l.dateDue) <= to_days('$dateTo')
_SQL_

    if(defined $filter->{'uid'} && $filter->{'uid'} ne ''){
        $sql .= " &&  u.uid= ?  " ;
        push  @sqlCondVals,$filter->{'uid'} ;
    }
    if(defined $filter->{'grade'} && $filter->{'grade'} ne ''){
        if ($filter->{'grade'} eq "N/A"){
            $sql .= " &&  u.grade = ' '" ;
        }
        else {
            $sql .= " &&  u.grade = ?  " ;
            push  @sqlCondVals,$filter->{'grade'} ;
        }
    }
    if(defined $filter->{'homeroom'} && $filter->{'homeroom'} ne ''){
        if ($filter->{'homeroom'} eq "N/A"){
            $sql .= " &&  u.homeroom = ' '" ;
        }
        else {
            $sql .= " &&  u.homeroom = ?  " ;
            push  @sqlCondVals,$filter->{'homeroom'} ;
        }
    }
    if(defined $filter->{'teacher'} && $filter->{'teacher'} ne ''){
        if ($filter->{'teacher'} eq "N/A"){
            $sql .= " &&  u.teacher = ' '" ;
        }
        else {
            $sql .= " &&  u.teacher = ?  " ;
            push  @sqlCondVals,$filter->{'teacher'} ;
        }
    }
    if(defined $filter->{'rid'} && $filter->{'rid'} ne ''){
            $sql .= " &&  r.rid = ?  " ;
            push  @sqlCondVals,$filter->{'rid'} ;
    }
    if ($sortBy = m/^barcode|^grade|^homeroom|^teacher/g){
        $sql .= " order by $sortBy ";
    }
    elsif ($sortBy eq "rname"){
        $sql .= " order by r.rname ";
    }
    else {
        $sql .= " order by u.lastname,u.firstname, homeroom";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute(@sqlCondVals);
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
        $rec->{'dateDue'} = date_time_text($rec->{'dateDue'}, 0);
        push @loanList,$rec;
    }
    $sth->finish;
    return \@loanList;
}