#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;

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

use Opals::User qw(
    user_getUserList
    user_getUserCircCount
    user_getUserCircCount_textbook
);
use Opals::Date qw(
    date_text
);
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

    my $cgi     = CGI->new;
    my $input   = $cgi->Vars();
    my $dateFrom=$input->{'dateFrom'};
    my $dateTo  =$input->{'dateTo'} ;
    my $userType=$input->{'userType'};
    my $incUserInfo=$input->{'incUserInfo'};
    my $sort2   =$input->{'sort2'}|| " u.lastname,u.firstname";
    $sort2 ="u.lastname,u.firstname" if($sort2 eq 'fullname');

    $sort2 ="titleSort" if($sort2 eq 'title');
    my $orderBy =undef;
    my $incExcl   =$input->{'incExc'}|| "inclusion";
        
    my $rptType =$input->{'rptType'};
    $incUserInfo="true" if($rptType eq 'od');
    my $rptOn   =$input->{'rptOn'};
    my $grpBy   =$input->{'groupBy'};
       $grpBy   ='none' if($grpBy !~ m/yeargraduation|teacher|grade|homeroom|username/);
    $orderBy =$input->{'orderBy'};
    if(!$incUserInfo || $incUserInfo eq 'false') {
        $grpBy='none';
    }
    elsif($grpBy eq 'username') {
       $orderBy ='lastname' if($orderBy !~ m/yeargraduation|homeroom|grade|teacher|callNumber/);
    }
    elsif($grpBy eq 'homeroom') {
        $orderBy ='homeroom,buildingcode' if($orderBy !~ m/yeargraduation|homeroom|grade|teacher|callNumber/);
    }
    elsif($grpBy eq 'teacher') {
        $orderBy ='teacher' if($orderBy !~ m/yeargraduation|homeroom|grade|teacher|callNumber/);
    }
    elsif($grpBy eq 'grade') {
        $orderBy ='grade' if($orderBy !~ m/yeargraduation|homeroom|grade|teacher|callNumber/);
    }
    elsif($grpBy eq 'yeargraduation') {
        $orderBy ='yeargraduation' if($orderBy !~ m/yeargraduation|homeroom|grade|teacher|callNumber/);
    }  
    
    my $rs     = getLoanList($dbh);
    my $rsJSON = to_json($rs,{pretty=>1});


    print "Content-type: text/plain\n\n";
    print  $rsJSON;

 
#------------------------------------------------------------------------------
sub getLoanList{
    my ($dbh)=@_;
    my @qryArg=();
    my $rs={};
    my @ret=();
    my $sql=<<_SQL_;

    select l.idloan,l.dateLoan,l.dateDue,DATEDIFF(now(),l.dateDue) od ,u.uid,u.lastname,u.firstname,u.email email,phone,cellphone,workPhone,
           u.teacher,u.homeroom,u.buildingcode,u.grade,u.sid,u.userbarcode,u.categorycode,if(u.yeargraduation>0,yeargraduation,"none") yeargraduation,
           i.*, m.title,m.titleSort,m.author,m.pubDate,m.pubName,m.pubPlace,m.isbn from opl_loan l inner join opl_item i using(barcode) 
    inner join opl_marcRecord m using(rid) inner join opl_user u using(uid)
    where l.dateReturn is null
_SQL_

    if(defined $dateFrom &&   $dateFrom =~ m/\d\d\d\d-\d\d-\d\d/ && 
       defined $dateTo   &&  $dateTo    =~ m/\d\d\d\d-\d\d-\d\d/ ){
        $dateTo =~ s/(\d\d\d\d-\d\d-\d\d).*/$1 23:59:59/g;
        if($rptOn eq 'dateLoan'){
            $sql .= " && (dateLoan between ? and  ? )";
        }
        else{
            $sql .= " && (dateDue between ? and  ?)";
        }
        push @qryArg,$dateFrom;
        push @qryArg,$dateTo;

    }
    if($incExcl eq 'exclusion'){
        $sql .= " && (m.tempIll is null)";

    }
    if($rptType eq 'od'){
        $sql .= " && dateDue <now() ";
    }

    if(defined $userType && $userType>0){
         $sql .= " && u.categorycode=? ";
        push @qryArg,$userType;
    }

    if(defined $orderBy && $grpBy ne"none"){
        $sql .= " order by u.$orderBy ,$sort2";
    }
    else{
        $sql .= " order by $sort2";
    }

    
    #open debug,">/tmp/ss";print debug "[$orderBy][$grpBy ][$incUserInfo] .$sql \n";close debug;
    my $sth=$dbh->prepare($sql);
    $sth->execute(@qryArg);
    my  $grpByField=$grpBy;
    $grpByField ='uid' if ($grpBy eq 'username');
    my $count=0;
    my $kf="";
    while(my $rec =$sth->fetchrow_hashref){
        my $defLang= Opals::Context->preference('lang');
        $rec->{'dateDueTxt'}=date_text($rec->{'dateDue'}, 0,$defLang);
        $rec->{'dateLoanTxt'}=date_text($rec->{'dateLoan'}, 0,$defLang);
        $count++;
        $rec->{'fullname'}= sprintf "%s, %s ", $rec->{'lastname'} , $rec->{'firstname'};
        $rec->{'fullname'} =~ s/^, |, $//g; 
        if(!$incUserInfo ){
            $kf="none";
        }
        elsif($grpByField eq 'homeroom'){
            $kf=sprintf "[%40s][%40s][%40s]", $rec->{'buildingcode'}, $rec->{'homeroom'},$rec->{'teacher'};
        }
        elsif($grpByField eq 'teacher'){
            $kf=sprintf "[%40s][%40s][%40s]", $rec->{'teacher'},$rec->{'buildingcode'}, $rec->{'homeroom'};
        }
        else{
            $kf= $rec->{$grpByField};
        }
        $rec->{'price'} =~ s/^\D+|\D+$//gi;
         if((!defined $rec->{'email'} || $rec->{'email'} eq '') && (defined $rec->{'uid'} && $rec->{'uid'}>0)){
            my ($gemail)=$dbh->selectrow_array("select gemail  from opl_guardian where uid=$rec->{'uid'}");
            $rec->{'email'} = $gemail;
         }

        if(!defined $rs->{$kf}){
           $rs->{$kf}->{'data'} =[];
           $rs->{$kf}->{'grpInfo'} ={ 
                                      uid           =>$rec->{'uid'},
                                      userbarcode   =>$rec->{'userbarcode'},
                                      sid           =>$rec->{'sid'},
                                      fullname      =>$rec->{'fullname'},
                                      lastname      =>$rec->{'lastname'},
                                      firstname     =>$rec->{'firstname'},
                                      email         =>$rec->{'email'},
                                      phone         =>$rec->{'phone'},
                                      homeroom      =>$rec->{'homeroom'},
                                      teacher       =>$rec->{'teacher'},
                                      grade         =>$rec->{'grade'},
                                      yeargraduation=>$rec->{'yeargraduation'},
                                      buildingcode  =>$rec->{'buildingcode'}
           }

       }
       push @{$rs->{$kf}->{'data'}},$rec;
    }
    foreach my $f(sort keys %$rs){
        push @ret,$rs->{$f};
    }
    if ($grpBy eq 'username'  ){
        if($orderBy eq 'teacher'){
            @ret= sort {lc($a->{'grpInfo'}->{'teacher'} . ' ' . $a->{'grpInfo'}->{'lastname'} .  ' ' . $a->{'grpInfo'}->{'firstname'}) 
                    cmp lc($b->{'grpInfo'}->{'teacher'} . ' ' . $b->{'grpInfo'}->{'lastname'} .  ' ' . $b->{'grpInfo'}->{'firstname'})} @ret;
        }
        elsif($orderBy eq 'grade'){
            @ret= sort {lc($a->{'grpInfo'}->{'grade'} . ' ' . $a->{'grpInfo'}->{'lastname'} . ' ' . $a->{'grpInfo'}->{'firstname'}) 
                    cmp lc($b->{'grpInfo'}->{'grade'} . ' ' . $b->{'grpInfo'}->{'lastname'} . ' ' . $b->{'grpInfo'}->{'firstname'})} @ret;
        }
        elsif($orderBy eq 'yeargraduation'){
            @ret= sort {lc($a->{'grpInfo'}->{'yeargraduation'} . ' ' . $a->{'grpInfo'}->{'lastname'} . ' ' . $a->{'grpInfo'}->{'firstname'}) 
                    cmp lc($b->{'grpInfo'}->{'yeargraduation'} . ' ' . $b->{'grpInfo'}->{'lastname'} . ' ' . $b->{'grpInfo'}->{'firstname'})} @ret;
        }
         elsif($orderBy eq 'homeroom'){
            @ret= sort {lc($a->{'grpInfo'}->{'homeroom'} . ' ' . $a->{'grpInfo'}->{'lastname'} . ' ' . $a->{'grpInfo'}->{'firstname'}) 
                    cmp lc($b->{'grpInfo'}->{'homeroom'} . ' ' . $b->{'grpInfo'}->{'lastname'} . ' ' . $b->{'grpInfo'}->{'firstname'})} @ret;
        }
        else{
            @ret= sort {lc($a->{'grpInfo'}->{'lastname'} . ' ' . $a->{'grpInfo'}->{'firstname'}) 
                    cmp lc($b->{'grpInfo'}->{'lastname'} . ' ' . $b->{'grpInfo'}->{'firstname'})} @ret;
        }
    }
    elsif($grpBy eq 'teacher'){
        @ret= sort {lc($a->{'grpInfo'}->{'teacher'} .  " " . $a->{'grpInfo'}->{'homeroom'} ) 
                   cmp lc($b->{'grpInfo'}->{'teacher'} ." " . $b->{'grpInfo'}->{'homeroom'})} @ret;

    }
    elsif( $grpBy eq 'homeroom'){
        @ret= sort {lc($a->{'grpInfo'}->{'homeroom'} . " " . $a->{'grpInfo'}->{'teacher'}  ) 
                   cmp lc($b->{'grpInfo'}->{'homeroom'} . " " . $b->{'grpInfo'}->{'teacher'} )} @ret;

    }
    return {count=>$count,groupBy=>$grpBy,report=>\@ret};
    
}
#------------------------------------------------------------------------------








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