#!/usr/bin/perl

use strict;
use CGI;
use JSON;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
);
use Opals::Date qw(
    date_text
    date_time_text
    date_now
);

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

my $cgi = CGI->new;
my $input = {};
my $rs;
my $sortBy;
my $groupBy;
my $defLang= Opals::Context->preference('lang') || 'en';
my ($total,$count,$list,$totalPrice,$nSelected);
my $sort2;
if ($ENV{'REQUEST_METHOD'} eq "POST") {
  $input = decode_json($cgi->param('POSTDATA'));
  ($total,$count,$nSelected,$list,$totalPrice) = eq_rpt_getReturnedItemList($dbh,$input);
  #($total,$count,$nSelected,$list,$totalPrice) = _eq_rpt_getReturnedItemList($dbh,$input);
  $rs = {total=>$total,count=>($count && $count)>0?$count:-1,groupBy=>$input->{'groupBy'},report=>$list,totalPrice=>$totalPrice,nSelected=>$nSelected};
} 
print "Content-type: text/plain\n\n";
print to_json($rs);
#----------------------------------------------

sub _eq_rpt_getReturnedItemList{
  my ($dbh, $p) = @_;
  my ($groupBy,$orderBy,$dateFrom,$dateTo,$overdue)=($p->{'groupBy'},$p->{'orderBy'},$p->{'dateFrom'},$p->{'dateTo'},$p->{'overdue'});
  my ($count,@returnList,$totalPrice) =(0,(),0);
  my $recFormat=$p->{'recFormat'} || "full";
  my $grp = "uid";
  my $sql_count="
  select count(*)    
  from eq_loan l inner join (eq_items i 
    inner join eq_records r on r.rid=i.rid) using (barcode)         
    inner join opl_user as u on l.uid = u.uid";
  if ($p->{'buildingId'} && $p->{'buildingId'} ne ""){
      $sql_count .= " &&  u.buildingcode= '$p->{'buildingId'}' ";
  }
  $sql_count .= "   
    left outer join opl_category c on c.catid=u.categorycode         
  where dateReturn is not null  && to_days(l.dateReturn)>=to_days('$dateFrom') && to_days(l.dateReturn)<=to_days('$dateTo') ";
  my $sth = $dbh->prepare($sql_count);
  $sth->execute();
  my ($total) = $sth->fetchrow_array;
  $total = $total>0?$total:0;
  my $sql = (<<_SQL_);
      select distinct u.uid,u.firstname,u.lastname,u.grade, u.homeroom,u.teacher,u.buildingcode as buildingcode,email,yeargraduation,
          u.userbarcode,u.addrLine1,u.addrLine2,u.city,u.state,u.phone,u.cellphone,u.fax,u.zip,u.email,u.categorycode,c.catname ,
          i.typeId,i.price,i.serialNumber,
          l.id as idloan, l.barcode,l.dateLoan,l.dateDue,l.dateReturn,r.name,r.rid, i.buildingCode,if (s.rid>0, 1,0) as selected 
      from eq_loan l inner join 
          eq_item i using (barcode) inner join eq_record r using(rid)
        inner join opl_user as u on l.uid = u.uid 
        left outer join opl_category c on c.catid=u.categorycode
        left outer join opl_sessionVar s on l.id=s.rid
        where dateReturn is not null
_SQL_
	if ($p->{'eqName'} &&  $p->{'eqName'} ne ""){
		 $p->{'eqName'} =~/^\s+|\s+$/;
		 if ($p->{'eqNameExactMatch'}){
      $sql .= " && r.name = '$p->{'eqName'}' " ;
		 }
		 else{
      $sql .= " && r.name regexp '$p->{'eqName'}' " ;
		 }
	}
  $sql .= " && to_days(l.dateReturn)>=to_days('$dateFrom') && to_days(l.dateReturn)<=to_days('$dateTo')";

  if ($p->{'userType'} && $p->{'userType'} ne "") {
    $sql .= " && u.categorycode = $p->{'userType'} ";
  }
  if ($p->{'buildingId'} && $p->{'buildingId'} ne ""){
      $sql .= " &&  u.buildingcode= '$p->{'buildingId'}' ";
  }
  if ($groupBy eq 'username' || $groupBy eq 'none'){
    if (defined $p->{'grpList'} && @{$p->{'grpList'}}>0){
      my $uidStr = "";
      foreach my $grp (@{$p->{'grpList'}}){
        $uidStr .= ($grp->{'uid'}>0)? $grp->{'uid'}.",":'';
      }
      $uidStr =~ s/,$//; 
      $sql .= ($uidStr ne "")? " && l.uid in($uidStr) " :"";
    }
  }
  elsif ($groupBy eq 'teacher' || $groupBy eq 'grade' || $groupBy eq 'homeroom') {
    if (defined $p->{'grpList'} && @{$p->{'grpList'}}>0){
      my $uidStr = "";
      foreach my $grp (@{$p->{'grpList'}}){
        #$uidStr .= ($grp->{'grpId'}>0)? $grp->{'grpId'}.",":'';
        $uidStr = ($uidStr eq "")? $uidStr : $uidStr . "," ;
        if ($grp->{'grpId'} && scalar(@{$grp->{'grpId'}}>0)){
          $uidStr .= join ",", @{$grp->{'grpId'}};
        }
      }
      $uidStr =~ s/,$//; 
      $sql .= ($uidStr ne "")? " && l.uid in($uidStr) " :"";
    }
    $grp = $groupBy;
  }
  #$sql .= " order by concat(lastname,firstname,'zzzzzz'), concat(homeroom,'zzzzzz') ";
  if ($p->{'orderBy'} && $p->{'orderBy'} eq "username"){
    $sql .= " order by concat(lastname,firstname,'zzzzzz') ";
  }
  elsif($p->{'orderBy'} && $p->{'orderBy'} eq "rname" ){
    $sql .= " order by r.name ";
  }
  elsif($p->{'orderBy'} && ($p->{'orderBy'} eq "userbarcode"||$p->{'orderBy'} eq "buildingcode"||$p->{'orderBy'} eq "teacher")){
    $sql .= " order by  " . "u." . $p->{'orderBy'};
  }
  elsif($p->{'orderBy'} && ($p->{'orderBy'} eq "barcode" || $p->{'orderBy'} eq "dateLoan"||$p->{'orderBy'} eq "dateDue"||$p->{'orderBy'} eq "dateReturn")){
    $sql .= " order by  " . "l." . $p->{'orderBy'};
  }
  else{
    $sql .= " order by l.dateReturn ";
  }
  if ($p->{'reverse'} && $p->{'reverse'} eq "1"){
    $sql .= "  desc";
  }

  if ($p->{'pNum'} && $p->{'pSize'} && $p->{'pSize'}>0){
    my $offset = 0;
    if ($p->{'pNum'} > 1) {
      $offset =  ($p->{'pNum'}-1)  * $p->{'pSize'};
    }
    #my $limit  = $p->{'curPage'} * $p->{'pSize'};
    my $limit  = $p->{'pSize'};
    $limit = ($limit > $total)? $total : $limit;
    $sql .= " limit $offset, $limit ";
  }
    $sth= $dbh->prepare($sql);
    $sth->execute();
    my $list;
    my $count=0;
    my $price=0;
    my $nSelected=0;
    my ($rid,$recordInfo,$itemInfo);
    while(my $rec=$sth->fetchrow_hashref) {
      $rec->{'userbarcode'} = $rec->{'userbarcode'} || "";
      $rec->{'grade'}   =  $rec->{'grade'} || "";
      $rec->{'teacher'} = $rec->{'teacher'} || "";
      $rec->{'buildingcode'} = $rec->{'buildingcode'} || "";
      $rec->{'homeroom'} = $rec->{'homeroom'} || "";
      $rec->{'email'}     = $rec->{'email'} || "";
      $rec->{'addrLine1'} = $rec->{'addrLine1'} || "";
      $rec->{'addrLine2'} = $rec->{'addrLine2'} || "";
      $rec->{'city'}  = $rec->{'city'} || "";
      $rec->{'state'} = $rec->{'state'} || "";
      $rec->{'zip'}   = $rec->{'zip'} || "";
      $rec->{'phone'} = $rec->{'phone'} || "";
      $rec->{'cellphone'} = $rec->{'cellphone'} || "";
      $rec->{'rname'} = $rec->{'name'};
      $rec->{'username'} = $rec->{'lastname'} . ", " . $rec->{'firstname'};
      if ($groupBy eq "none"){
        push @{$list->{$rec->{$groupBy}}->{'data'}},$rec;
      }
      elsif (!defined $list->{$rec->{$grp}}){
        if  ($grp eq "uid") {
          $list->{$rec->{$grp}}->{'grpInfo'} = {
            uid         => $rec->{'uid'},
            firstname   => $rec->{'firstname'},
            lastname    => $rec->{'lastname'},
            userbarcode => $rec->{'userbarcode'},
            grade       => $rec->{'grade'},
            teacher     => $rec->{'teacher'},
            buildingcode=> $rec->{'buildingcode'},
            homeroom    => $rec->{'homeroom'},
            email       => $rec->{'email'} ,
            username    => $rec->{'lastname'} . ', '. $rec->{'firstname'},
            addrLine1   => $rec->{'addrLine1'} ,
            addrLine2   => $rec->{'addrLine2'} ,
            city        => $rec->{'city'} ,
            state       => $rec->{'state'} ,
            zip         => $rec->{'zip'} ,
            phone       => $rec->{'phone'} ,
            cellphone   => $rec->{'cellphone'} ,
            username    => $rec->{'username'}
          };
          if ($groupBy eq "none"){
            $list->{$rec->{$grp}}->{'grpInfo'}->{'dateLoan'} = $rec->{'dateLoan'};
            $list->{$rec->{$grp}}->{'grpInfo'}->{'dateDue'} = $rec->{'dateDue'};
            $list->{$rec->{$grp}}->{'grpInfo'}->{'dateReturn'} = $rec->{'dateReturn'};
            $list->{$rec->{$grp}}->{'grpInfo'}->{'rname'} = $rec->{'rname'};
            $list->{$rec->{$grp}}->{'grpInfo'}->{'price'} = $rec->{'price'};
          }
        }
        elsif ($grp eq "homeroom") {
          $list->{$rec->{$grp}}->{'grpInfo'} = {
            homeroom    => $rec->{'homeroom'},
            teacher     => $rec->{'teacher'},
            buildingcode=> $rec->{'buildingcode'} || ""
          }
        }
        elsif ($grp eq "grade") {
          $list->{$rec->{$grp}}->{'grpInfo'} = {
            grade   =>  $rec->{'grade'}
          }
        }
        elsif ($grp eq "teacher") {
          $list->{$rec->{$grp}}->{'grpInfo'} = {
            teacher =>  $rec->{'teacher'}
          }
        }
        elsif ($grp eq "rname" || $grp eq "eqName") {
          $list->{$rec->{$grp}}->{'grpInfo'} = {
            eqName  =>  $rec->{'rname'},
            rname   =>  $rec->{'rname'},
            rid     => $rec->{'rid'}
          }
        }
        push @{$list->{$rec->{$grp}}->{'data'}},$rec;
      }
      $count++;
      $price = $rec->{'price'};
      $price =~ s/[^0-9.]//g;
      $totalPrice += ($price>0.0)?$price:0;
    }
    @returnList=();
    foreach my $k(sort keys %{$list}){
      push @returnList, $list->{$k};
    }
	 if ($groupBy eq 'none' ){
     if ($orderBy){
      @returnList = sort { $a->{'grpInfo'}->{$orderBy} cmp $b->{'grpInfo'}->{$orderBy} } @returnList;
     }
     else{
      @returnList = sort { $a->{'grpInfo'}->{'lastname'} cmp $b->{'grpInfo'}->{'lastname'} } @returnList;
     }
   }	
	 elsif ($groupBy eq 'username'){
		 if ($p->{'orderBy'} eq 'grade') {
				@returnList = sort { $a->{'grpInfo'}->{'grade'} <=> $b->{'grpInfo'}->{'grade'}} @returnList;
	   }
		 elsif ($p->{'orderBy'} eq 'homeroom'){
			  @returnList = sort { $a->{'grpInfo'}->{'homeroom'} cmp $b->{'grpInfo'}->{'homeroom'} } @returnList;
		 }
		 else{
			 @returnList = sort { $a->{'grpInfo'}->{'lastname'} cmp $b->{'grpInfo'}->{'lastname'}} @returnList;	 
		 }
	 }
   $sth->finish;
   return ($total,$count,$nSelected,\@returnList,$totalPrice);
}