#!/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::Equipment qw(
    
    eq_item_findByBarcode

);

use Opals::Date qw(
    date_time_text
);

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, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => '/eqmnt/eqOnLoan_prt.tmpl',
#        reqPermission   => 'reqPermission1|reqPermission2|etc.',
    }
);

my $pref = tmpl_preference($dbh);

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

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

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

if ( $sort1 eq 'username'){
    if ($input->{'list'}){
        getOnLoan_userList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
        $template->param(idList => 1);
    }
    else{
        getOnLoan_userNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $input->{'orderby'});
        $template->param(odnotice => 1);
    }
}
elsif($sort1 eq 'teacher' ){
    if ($input->{'list'}){
        getOnLoan_teacherList($dbh, $template, $sort1,$sort2,$dateFrom, $dateTo, $input);
        $template->param(teacherList => 1);
    }
    else{
        getOnLoan_teacherNotice($dbh, $template, $sort1,$sort2,$dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
 
    }

}
elsif ($sort1 eq 'homeroom'){
    if ($input->{'list'}){
        getOnLoan_homeroomList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(homeroomList => 1);
    }
    else{
        getOnLoan_homeroomNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }

}
elsif ($sort1 eq 'grade'){
    if ($input->{'list'}){
        getOnLoan_gradeList($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(gradeList => 1);
    }
    else{
        getOnLoan_gradeNotice($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input);
        $template->param(odnotice => 1);
    }
}


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


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

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

sub getOnLoan_userList{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @idList = ();
    my $eqOnLoan;
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }
    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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            l.uid = ?    ";

    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);
    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');
            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'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $p(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$p};
    }
    $template->param(eqOnLoan => \@eqOnLoanList);
    $template->param(
        username    => 1,
        sort1       => "username",
    );
}

sub getOnLoan_teacherList{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input)= @_;
    my @teacherList = ();
    my $eqOnLoan;
    @teacherList = split /\$/, $input->{'teacherList'};    
    
    if (scalar(@teacherList) == 0){
        push @teacherList, '';}
    else{
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }

    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 >= '$dateFrom'  && dateLoan <= '$dateTo' &&
            u.teacher = ? ";
            
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for ( my $i = 0; $i < $nList; $i++){
        $sth->execute($teacherList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            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'};
            }
            push @{$eqOnLoan->{$loan->{'teacher'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $t( sort keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$t};
    }
    $template->param(eqOnLoan => \@eqOnLoanList);
    $template->param(
        teacher => 1,
        sort1   => "teacher",
    );
}

sub getOnLoan_gradeList {

    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @gradeList = ();
    my $eqOnLoan;
    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){
        push @gradeList, '';}
    else{
        foreach my $grade(@gradeList){
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }
    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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.grade = ?    ";
    my $sth     = $dbh->prepare($sql);
    my $nList   = scalar(@gradeList);
    for (my $i = 0; $i < $nList; $i++){
        $sth->execute($gradeList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            if ($loan->{'grade'} eq "") {
                $loan->{'grade'} = " N/A";
            }
            if (!$eqOnLoan->{$loan->{'grade'}}){
                $eqOnLoan->{$loan->{'grade'}}->{'grade'} = $loan->{'grade'};
            }
            push @{$eqOnLoan->{$loan->{'grade'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $g (sort keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$g};
    }
    $template->param(eqOnLoan => \@eqOnLoanList);
    $template->param(
        grade   => 1,
    );
}

sub getOnLoan_homeroomList {

    my ($dbh, $template, $sort1, $sort2,$dateFrom, $dateTo, $input ) = @_;
    my @homeroomList = ();
    my $eqOnLoan;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    if (scalar(@homeroomList) == 0){
        push @homeroomList, '';}
    else{
        foreach my $hr(@homeroomList){
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
    }
    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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.homeroom = ?    ";
    my $sth     = $dbh->prepare($sql);
    my $nList   = scalar(@homeroomList);
    for (my $i = 0; $i < $nList; $i++){
        $sth->execute($homeroomList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            if ($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'};
            }
            push @{$eqOnLoan->{$loan->{'homeroom'}}->{'group'}}, $loan;
        }
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $g (sort keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$g};
    }
    $template->param(eqOnLoan => \@eqOnLoanList);
    $template->param(
        homeroom   => 1,
    );

}

sub getOnLoan_userNotice{

    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @idList = ();
    my $eqOnLoan;
    @idList = split /\$/, $input->{'idList'};
    if (scalar(@idList) == 0){ 
        push @idList, ''; }


        
     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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            l.uid = ?    ";

    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);
    my $nList = scalar(@idList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($idList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            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'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanNotice => \@eqOnLoanList);
}

sub getOnLoan_teacherNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @teacherList = ();
    my $eqOnLoan;
    @teacherList = split /\$/, $input->{'teacherList'};
    if (scalar(@teacherList) == 0){ 
        push @teacherList, ''; }
    else{
        foreach my $teacher(@teacherList){
            if ($teacher eq " " || $teacher eq "None" || $teacher eq "N/A"){
                $teacher = "";
            }
        }
    }
    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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.teacher = ?    ";
   
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@teacherList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($teacherList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            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'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanNotice => \@eqOnLoanList);
}

sub getOnLoan_homeroomNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @homeroomList = ();
    my $eqOnLoan;
    @homeroomList = split /\$/, $input->{'homeroomList'};
    if (scalar(@homeroomList) == 0){ 
        push @homeroomList, ''; }
    else{
        foreach my $hr(@homeroomList){
            if ($hr eq " " || $hr eq "None" || $hr eq "N/A"){
                $hr = "";
            }
        }
    }
    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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.homeroom = ?    ";
   
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@homeroomList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($homeroomList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            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'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanNotice => \@eqOnLoanList);
}

sub getOnLoan_gradeNotice{
    my ($dbh, $template, $sort1, $sort2, $dateFrom, $dateTo, $input, $orderby) = @_;
    my @gradeList = ();
    my $eqOnLoan;
    @gradeList = split /\$/, $input->{'gradeList'};
    if (scalar(@gradeList) == 0){ 
        push @gradeList, ''; }
    else{
        foreach my $grade(@gradeList){
            if ($grade eq " " || $grade eq "None" || $grade eq "N/A"){
                $grade = "";
            }
        }
    }
    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 >= '$dateFrom' && dateLoan <= '$dateTo' &&
            u.grade = ?    ";
   
    my $sth = $dbh->prepare($sql);
    my $nList = scalar(@gradeList);
    for (my $i = 0;  $i < $nList ; $i++){
        $sth->execute($gradeList[$i]);
        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'}, 1, 'en');
            $loan->{'dateDueFormat'} =  date_time_text ($loan->{'dateDue'}, 1 ,'en');

            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'}}->{'userbarcode'} = $loan->{'userbarcode'};
                $eqOnLoan->{$loan->{'uid'}}->{'phone'} = $loan->{'phone'};
            }
            push @{$eqOnLoan->{$loan->{'uid'}}->{'group'}}, $loan;
        }    
    }
    $sth->finish;
    my @eqOnLoanList = ();
    foreach my $k(keys %{$eqOnLoan}){
        push @eqOnLoanList, $eqOnLoan->{$k};
    }
    $template->param(eqOnLoanNotice => \@eqOnLoanList);
}