package Opals::Eq_Report;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
  eq_rpt_getLoanedItemList
  eq_rpt_fieldMap
  eq_rpt_grpInfoFieldMap
  eq_rpt_getNoticeDataOnLoan
  eq_rpt_dateTypeList
  eq_rpt_grpByFieldList
  eq_rpt_getReturnedItemList
  eq_rpt_getItemAcqst
  eq_rpt_getItemListByStatus
  eq_rpt_getLateReturnOdItemList

);
use Opals::Equipment qw(
    eq_item_findByBarcode
    eq_record_findByRId
);

=item
use Opals::Eq_Record qw(
    eq_item_findByBarcode
    eq_record_findByRId
);
=cut

use Opals::Locale qw(
  loc_getMsgFile
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;
use Text::CSV_XS;
use Opals::Constant;
use JSON;
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);
use Time::localtime;
my $tm = localtime;
my $dateToday = sprintf("%04d-%02d-%02d %02d:%02d:%02d",$tm->year+1900,($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec);

sub eq_rpt_getLoanedItemList{
  my ($dbh, $p) = @_;
  my ($groupBy,$orderBy,$dateFrom,$dateTo,$overdue)=($p->{'groupBy'},$p->{'orderBy'},$p->{'dateFrom'},$p->{'dateTo'},$p->{'overdue'});
  my ($count,@loanList,$totalPrice) =(0,(),0);
  my $recFormat=$p->{'recFormat'} || "full";
  my $grp = "uid";
  my $sql = (<<_SQL_);
      select distinct u.uid,u.firstname,u.lastname,u.grade, u.homeroom,u.teacher,u.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,
          l.id as idloan, l.barcode,l.dateLoan,l.dateDue,r.rname,r.rid, to_days(now())-to_days(l.dateDue) as od, sfValue as buildingCode
      from eq_loan l inner join 
          (eq_items i left join eq_itemFields f on f.rid=i.rid && f.iid=i.iid && f.sfId=18 inner join eq_records r on r.rid=i.rid) using (barcode)
        inner join opl_user as u on l.uid = u.uid 
        left outer join opl_category c on c.catid=u.categorycode
        where dateReturn is null
_SQL_
=item
my $sql = (<<_SQL_);
      select distinct u.uid,u.firstname,u.lastname,u.grade, u.homeroom,u.teacher,u.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,
          l.id as idloan, l.barcode,l.dateLoan,l.dateDue,r.name, r.name as rname,r.rid, to_days(now())-to_days(l.dateDue) as od, i.buildingCode
      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
        where dateReturn is null
_SQL_
=cut
  if ($p->{'rptType'} && $p->{'rptType'} eq 'overdue'){
    $sql .= " && l.dateDue < now()" ;
  }
	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'}' " ;
		 }
	}
  if ($p->{'dateType'} eq 'loanDate'){
       $sql .= " && to_days(l.dateLoan)>=to_days('$dateFrom') && to_days(l.dateLoan)<=to_days('$dateTo')" ;
    }
  else{
      $sql .= " && to_days(l.dateDue)>=to_days('$dateFrom') && to_days(l.dateDue)<=to_days('$dateTo')";
  }

  if ($p->{'userType'} && $p->{'userType'} ne "") {
    $sql .= " && u.categorycode = $p->{'userType'} ";
  }
  if ($p->{'buildingId'} && $p->{'buildingId'} ne ""){
      #$sql .= " && f.sfValue = '$p->{'buildingId'}' ";
      $sql .= " &&  (u.buildingcode= '$p->{'buildingId'}' ||  f.sfValue = '$p->{'buildingId'}' ) ";
  }
  if ($p->{'overdue'}){
    $sql .= " && l.dateDue < now() ";
  }
  if ($groupBy eq 'none'){
    $sql .= " group by l.barcode ";
    $grp = "barcode";
  }
  if ($groupBy eq 'username'){
    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') ";
  my $sth= $dbh->prepare($sql);
    $sth->execute();
    my $list;
    my $count=0;
    my $price=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->{'price'}=0;
      $rec->{'idloan'}      = $rec->{'idloan'} || 0;
      $rec->{'od'} = $rec->{'od'} + 0;
      if (!$recFormat || $recFormat ne "brief"){
        $itemInfo = eq_item_findByBarcode($dbh,$rec->{'barcode'});
        #$itemInfo = eq_item_findByBarcode($dbh,{barcode=>$rec->{'barcode'} });
        #($rid,$recordInfo) = eq_record_findByRId($dbh,{recordId=>$itemInfo->{'rid'}});
        $rec->{'serialNumber'} = $itemInfo->{'fieldById'}->{'5'}->{'value'};
        $rec->{'price'} = $itemInfo->{'fieldById'}->{'6'}->{'value'};
      }
      if (!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'} 
          }
        }
        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'}
          }
        }
        else{
          $list->{$rec->{$grp}}->{'grpInfo'} = {
              uid         => $rec->{'uid'},
              firstname   => $rec->{'firstname'},
              lastname    => $rec->{'lastname'},
              barcode     => $rec->{'barcode'},
              sorted       => $rec->{'title'},
            };
            $list->{$rec->{$grp}}->{'grpInfo'} = $rec;
        }
      }
      $rec->{'username'}  = $rec->{'lastname'} . ', '. $rec->{'firstname'};
      push @{$list->{$rec->{$grp}}->{'data'}},$rec;
      $count++;
      $price = $itemInfo->{'price'};
      $price =~ s/[^0-9.]//g;
      $totalPrice += ($price>0.0)?$price:0;
    }
    @loanList=();
    foreach my $k(sort {$a <=> $b} keys %{$list}){
      push @loanList, $list->{$k};
    }
=item    
    if ($grp = "uid"){
      if ($orderBy eq "homeroom"){
        @loanList = sort { $a->{'grpInfo'}->{'homeroom'} cmp $b->{'grpInfo'}->{'homeroom'}} @loanList;
      }
      elsif ($orderBy eq "grade"){
        @loanList = sort { $a->{'grpInfo'}->{'grade'} cmp $b->{'grpInfo'}->{'grade'}} @loanList;
      }
      else{
        @loanList = sort { $a->{'grpInfo'}->{'lastname'} cmp $b->{'grpInfo'}->{'lastname'}} @loanList;
      }
    }
=cut    
	 if ($groupBy eq 'none' ){
     @loanList = sort { $a->{'grpInfo'}->{'lastname'} cmp $b->{'grpInfo'}->{'lastname'}} @loanList;
   }	
	 elsif ($groupBy eq 'username'){
		 if ($p->{'orderBy'} eq 'grade') {
				@loanList = sort { $a->{'grpInfo'}->{'grade'} <=> $b->{'grpInfo'}->{'grade'}} @loanList;
	   }
		 elsif ($p->{'orderBy'} eq 'homeroom'){
			  @loanList = sort { $a->{'grpInfo'}->{'homeroom'} cmp $b->{'grpInfo'}->{'homeroom'} } @loanList;
		 }
		 else{
			 @loanList = sort { $a->{'grpInfo'}->{'lastname'} cmp $b->{'grpInfo'}->{'lastname'}} @loanList;	 
		 }
	 }
   $sth->finish;
   return ($count,\@loanList,$totalPrice);
}
 
sub eq_rpt_getReturnedItemList1{
  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_item i using(barcode)
  inner join eq_record r using (rid)         
  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,
          l.id as idloan, l.barcode,l.dateLoan,l.dateDue,l.dateReturn,r.name as rname,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.rname ";
  }
  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'} || "";
      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);
}

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 
    left join eq_itemFields f on f.rid=i.rid && f.iid=i.iid && f.sfId=18 
    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,
          l.id as idloan, l.barcode,l.dateLoan,l.dateDue,l.dateReturn,r.rname,r.rid, sfValue as buildingCode,if (s.rid>0, 1,0) as selected 
      from eq_loan l inner join 
          (eq_items i left join eq_itemFields f on f.rid=i.rid && f.iid=i.iid && f.sfId=18 inner join eq_records r on r.rid=i.rid) using (barcode)
        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.rname = '$p->{'eqName'}' " ;
		 }
		 else{
      $sql .= " && r.rname 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.rname ";
  }
  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->{'price'}=0;
      if (!$recFormat || $recFormat ne "brief"){
        $itemInfo = eq_item_findByBarcode($dbh,$rec->{'barcode'});
        ($rid,$recordInfo) = eq_record_findByRId($dbh,{recordId=>$itemInfo->{'rid'}});
        $rec->{'ifields'} = $itemInfo->{'fields'};
        $rec->{'rfields'} = $recordInfo;
        $rec->{'username'}  = $rec->{'lastname'} . ', '. $rec->{'firstname'};
        $nSelected += $rec->{'selected'}?1:0;
        foreach my $i ( @{$itemInfo->{'fields'}}){
            if ($i->{'sfid'} eq "5"){
                $rec->{'serialNumber'} = $i->{'sfval'};
            }
            if ($i->{'sfname'} =~ /Price/i){
                $rec->{'price'} = $i->{'sfval'} ;
            }
            if ($rec->{'serialNumber'} && $rec->{'price'}){
                last;
            }
        }
      }
      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);
}

sub eq_rpt_fieldMap {
    my ($grp,$type) = @_;
    $type = (defined $type)?$type:"loan";
    my $msgFile = loc_getMsgFile('report/reports.msg');
    my $msgFile_user = loc_getMsgFile('user/userInfo.msg');
    my $rptFieldMap = {
      "loan" => {
         'grade'=> [
                    {'header'=>$msgFile->{'userTxt'},     'field'=>'username','wd'=>'10%'},
                    {'header'=>$msgFile->{'homeRoomTxt'} || "Homeroom",'field'=>'homeroom','wd'=>'6%'},
                    {'header'=>$msgFile_user->{'buildTxt'} ||"Building Code",'field'=>'buildingcode','wd'=>'6%'},
                    {'header'=>$msgFile->{'teacherTxt'} || "Teacher",'field'=>'teacher','wd'=>'10%'},
                    {'header'=>$msgFile->{'eqmntTxt'}||'Equipment', 'field'=>'rname','wd'=>'20%'},
                    {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'8%'},
                    {'header'=>$msgFile->{'priceTxt'},'field'=>'price','wd'=>'5%'},
                    {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'odLoanTxt'}, 'field'=>'od','wd'=>'5%'}
                 ],
        'homeroom'=> [
                    {'header'=>$msgFile->{'userTxt'},   'field'=>'username','wd'=>'10%'},
                    {'header'=>$msgFile->{'gradeTxt'},  'field'=>'grade','wd'=>'5%'},
                    {'header'=>$msgFile->{'eqmntTxt'}||'Equipment',   'field'=>'rname', 'wd'=>'15%'},
                    {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                    {'header'=>$msgFile->{'priceTxt'},  'field'=>'price','wd'=>'5%'},
                    {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'odLoanTxt'}, 'field'=>'od','wd'=>'5%'}
                 ],
        'teacher'=> [ 
                    {'header'=>$msgFile->{'userTxt'},   'field'=>'username','wd'=>'15%' },
                    {'header'=>$msgFile->{'gradeTxt'},  'field'=>'grade','wd'=>'5%'},
                    {'header'=>$msgFile->{'eqmntTxt'}||'Equipment',   'field'=>'rname','wd'=>'30%'},
                    {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                    {'header'=>$msgFile->{'priceTxt'},  'field'=>'price','wd'=>'5%'},
                    {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'odLoanTxt'}, 'field'=>'od','wd'=>'5%'}
                 ],
        'username'=>[
                   {'header'=>$msgFile->{'eqmntTxt'}||'Equipment','field'=>'rname', 'wd'=>'35%'},
                   {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                   {'header'=>$msgFile->{'priceTxt'},'field'=>'price','wd'=>'10%'},
                   {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'odLoanTxt'}, 'field'=>'od','wd'=>'5%'}
                 ],
        'none'=>[
                 {'header'=>$msgFile->{'userTxt'},'field'=>'username','wd'=>'15%'},
                 {'header'=>$msgFile->{'userBcTxt'}||'User barcode','field'=>'userbarcode','wd'=>'5%'},
                 {'header'=>$msgFile->{'buildCodeTxt'}||'Building','field'=>'buildingcode','wd'=>'5%'},
                 {'header'=>$msgFile->{'teacherTxt'}||'Teacher','field'=>'teacher','wd'=>'10%'},
                 {'header'=>$msgFile->{'eqmntTxt'}||'Equipment','field'=>'rname', 'wd'=>'20%'},
                 {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'5%'},
                 {'header'=>$msgFile->{'priceTxt'},'field'=>'price','wd'=>'5%'},
                 {'header'=>$msgFile->{'serialNumTxt'}||"Serial Number",'field'=>'serialNumber','wd'=>'10%'},
                 {'header'=>$msgFile_user->{'loanDateTxt'} ,'field'=>'dateLoan','wd'=>'10%'},
                 {'header'=>$msgFile_user->{'dueDateTxt'} ,'field'=>'dateDue','wd'=>'10%'},
                 {'header'=>$msgFile_user->{'odLoanTxt'}, 'field'=>'od','wd'=>'5%'}
              ],
      },
      "returned"=> {
         'grade'=> [
                    {'header'=>$msgFile->{'userTxt'},     'field'=>'username','wd'=>'10%'},
                    {'header'=>$msgFile->{'homeRoomTxt'} || "Homeroom",'field'=>'homeroom','wd'=>'7%'},
                    {'header'=>$msgFile_user->{'buildTxt'} ||"Building Code",'field'=>'buildingcode','wd'=>'8%'},
                    {'header'=>$msgFile->{'teacherTxt'} || "Teacher",'field'=>'teacher','wd'=>'10%'},
                    {'header'=>$msgFile->{'eqmntTxt'}||'Equipment', 'field'=>'rname','wd'=>'20%'},
                    {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                    {'header'=>$msgFile->{'priceTxt'},'field'=>'price','wd'=>'5%'},
                    {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                 ],
        'homeroom'=> [
                    {'header'=>$msgFile->{'userTxt'},   'field'=>'username','wd'=>'10%'},
                    {'header'=>$msgFile->{'gradeTxt'},  'field'=>'grade','wd'=>'10%'},
                    {'header'=>$msgFile->{'eqmntTxt'}||'Equipment',   'field'=>'rname', 'wd'=>'25%'},
                    {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                    {'header'=>$msgFile->{'priceTxt'},  'field'=>'price','wd'=>'5%'},
                    {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'returnDateTxt'}, 'field'=>'dateReturn','wd'=>'10%'}
                 ],
        'teacher'=> [ 
                    {'header'=>$msgFile->{'userTxt'},   'field'=>'username','wd'=>'15%' },
                    {'header'=>$msgFile->{'gradeTxt'},  'field'=>'grade','wd'=>'5%'},
                    {'header'=>$msgFile->{'eqmntTxt'}||'Equipment',   'field'=>'rname','wd'=>'25%'},
                    {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                    {'header'=>$msgFile->{'priceTxt'},  'field'=>'price','wd'=>'5%'},
                    {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                    {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'returnDateTxt'}, 'field'=>'dateReturn','wd'=>'10%'}
                 ],
        'username'=>[
                   {'header'=>$msgFile->{'eqmntTxt'}||'Equipment','field'=>'rname', 'wd'=>'35%'},
                   {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'10%'},
                   {'header'=>$msgFile->{'priceTxt'},'field'=>'price','wd'=>'10%'},
                   {'header'=>$msgFile->{'serialNumTxt'} || 'Serial Number','field'=>'serialNumber','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'loanDateTxt'} ||"Loan Date",'field'=>'dateLoan','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'dueDateTxt'} || 'Due Date','field'=>'dateDue','wd'=>'10%'},
                   {'header'=>$msgFile_user->{'returnDateTxt'}, 'field'=>'dateReturn','wd'=>'10%'}
                 ],
        'none'=>[
                 {'header'=>$msgFile->{'userTxt'},'field'=>'username','wd'=>'15%'},
                 {'header'=>$msgFile->{'userBcTxt'}||'User barcode','field'=>'userbarcode','wd'=>'5%'},
                 {'header'=>$msgFile->{'buildCodeTxt'}||'Building','field'=>'buildingcode','wd'=>'5%'},
                 {'header'=>$msgFile->{'teacherTxt'}||'Teacher','field'=>'teacher','wd'=>'10%'},
                 {'header'=>$msgFile->{'eqmntTxt'}||'Equipment','field'=>'rname', 'wd'=>'15%'},
                 {'header'=>$msgFile->{'barcodeTxt'},'field'=>'barcode','wd'=>'5%'},
                 {'header'=>$msgFile->{'priceTxt'},'field'=>'price','wd'=>'5%'},
                 {'header'=>$msgFile->{'serialNumTxt'}||"Serial Number",'field'=>'serialNumber','wd'=>'10%'},
                 {'header'=>$msgFile_user->{'loanDateTxt'} ,'field'=>'dateLoan','wd'=>'10%'},
                 {'header'=>$msgFile_user->{'dueDateTxt'} ,'field'=>'dateDue','wd'=>'10%'},
                 {'header'=>$msgFile_user->{'returnDateTxt'}, 'field'=>'dateReturn','wd'=>'10%'}
              ],
        },
    };
    
    if (defined $grp && $grp ne ""){
      return $rptFieldMap->{$type}->{$grp};
    }
    return $rptFieldMap->{$type};
}
 
sub eq_rpt_grpInfoFieldMap{
    my ($grp) = @_;
    my $msgFile = loc_getMsgFile('report/reports.msg');
    my $rptGrpInfoFieldMap={
        'grade'   =>[{'header'=>$msgFile->{'gradeTxt'} || "Grade",            'field'=>'grade'}],
        'homeroom'=>[{'header'=>$msgFile->{'homeRoomTxt'} || "Homeroom",      'field'=>'homeroom'},
                    {'header'=>$msgFile->{'buildCodeTxt'} || "Building Code", 'field'=>'buildingcode'},
                    {'header'=>$msgFile->{'teacherTxt'} || "Teacher",         'field'=>'teacher'}],
        'teacher' =>[{'header'=>$msgFile->{'teacherTxt'} || "Teacher",        'field'=>'teacher'},
                   {'header'=>$msgFile->{'homeRoomTxt'} || "Homeroom",        'field'=>'homeroom'},
                   {'header'=>$msgFile->{'buildCodeTxt'} || "Building Code",  'field'=>'buildingcode'}],
        'Equipment'=>[{'header'=>$msgFile->{'eqmntTxt'} || "Equipment",            'field'=>'rname'}],
        'username'=>[{'header'=>$msgFile->{'userTxt'},                        'field'=>'username'},
                   {'header'=>$msgFile->{'homeRoomTxt'}|| "Homeroom",         'field'=>'homeroom'},
                   {'header'=>$msgFile->{'teacherText'} || "Teacher",                      'field'=>'teacher'},
                   {'header'=>$msgFile->{'userBarcodeTxt'},                   'field'=>'userbarcode'},
                   {'header'=>$msgFile->{'userIdTxt'},                        'field'=>'sid'},
                   {'header'=>$msgFile->{'gradeTxt'},                         'field'=>'grade'},
                   {'header'=>$msgFile->{'emailTxt'} || "Email",              'field'=>'email'} ],
        'none'    => [{'header'=>$msgFile->{'userTxt'},                        'field'=>'username'},
                   {'header'=>$msgFile->{'homeRoomTxt'}|| "Homeroom",         'field'=>'homeroom'},
                   {'header'=>$msgFile->{'teacherText'} || "Teacher",                      'field'=>'teacher'},
                   {'header'=>$msgFile->{'userBarcodeTxt'},                   'field'=>'userbarcode'},
                   {'header'=>$msgFile->{'userIdTxt'},                        'field'=>'sid'},
                   {'header'=>$msgFile->{'gradeTxt'},                         'field'=>'grade'},
                   {'header'=>$msgFile->{'emailTxt'} || "Email",              'field'=>'email'} ]
    };
    if (defined $grp && $grp ne ""){
      return $rptGrpInfoFieldMap->{$grp};
    }

    return $rptGrpInfoFieldMap;
}

sub eq_rpt_grpByFieldList {
    my $msgFile = loc_getMsgFile('user/userInfo.msg');
    my $rptGrpByFieldList=[
      {'name'=>$msgFile->{'gradeTxt'},  'id'=>'grade','value'=>'grade'},
      {'name'=>$msgFile->{'homeroomTxt'}, 'id'=>'homeroom','value'=>'homeroom'},
      {'name'=>$msgFile->{'homeroomTeacherTxt'}||"Homeroom Teacher",'id'=>'teacher','value'=>'teacher'},
      {'name'=>$msgFile->{'userTxt'},'id'=>'user','value'=>'username'},
      {'name'=>$msgFile->{'noneTxt'} || "None",'id'=>'none','value'=>'none'}
    ];
    return $rptGrpByFieldList;
}


sub eq_rpt_getNoticeDataOnLoan {
  my ($dbh,$trans) = @_;
  my $list = ();
  my $uid = $trans->{'uid'};
  my $loanIdList = ($trans->{'loanIdList'})?$trans->{'loanIdList'}:[];
  my $lIdList= join(',',@{$loanIdList});
  if (!$lIdList || $lIdList eq "" && $uid){
    my $lids = $dbh->selectall_arrayref("select id from eq_loan where dateReturn is null && dateDue<now() && uid=$uid",{Slice=>{}});
    $lids = [map {$_->{'id'}} @$lids] ;
    $lIdList = join (',',@{$lids});
  }
  my $sql = (<<_SQL_);
      select distinct u.uid,u.firstname,u.lastname,u.grade, u.homeroom,u.teacher,u.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,
          l.id as idloan, l.barcode,l.dateLoan,l.dateDue,r.rname,r.rid, to_days(now())-to_days(l.dateDue) as od, sfValue as buildingCode
      from eq_loan l inner join 
          (eq_items i left join eq_itemFields f on f.rid=i.rid && f.iid=i.iid && f.sfId=18 inner join eq_records r on r.rid=i.rid) using (barcode)
        inner join opl_user as u on l.uid = u.uid 
        left outer join opl_category c on c.catid=u.categorycode
        where u.uid=$uid && find_in_set(l.id,'$lIdList')
_SQL_
  my $sth= $dbh->prepare($sql);
  $sth->execute();
  my $count=0;
  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'} || "";
    my $itemInfo = eq_item_findByBarcode($dbh,$rec->{'barcode'});
    my ($rid,$recordInfo) = eq_record_findByRId($dbh,{recordId=>$itemInfo->{'rid'}});
    $rec->{'ifields'} = $itemInfo->{'fields'};
    $rec->{'rfields'} = $recordInfo;
    foreach my $i ( @{$itemInfo->{'fields'}}){
        if ($i->{'sfid'} eq "5"){
            $rec->{'serialNumber'} = $i->{'sfval'};
        }
        if ($i->{'sfname'} =~ /Price/i){
            $rec->{'price'} = $i->{'sfval'} ;
        }
        if ($rec->{'serialNumber'} && $rec->{'price'}){
            last;
        }
    }
    if (!defined $list->{'userInfo'}){
        $list->{'userInfo'} = {
            uid         => $rec->{'uid'},
            userbarcode => $rec->{'userbarcode'},
            sid         => $rec->{'sid'},
            lastname    => $rec->{'lastname'}, 
            firstname   => $rec->{'firstname'},
            fullname    => $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'},
            email       => $rec->{'email'},
            homeroom    => $rec->{'homeroom'},
            teacher     => $rec->{'teacher'},
            grade       => $rec->{'grade'},
            buildingcode=> $rec->{'buildingcode'} 
        }
    }
    $rec->{'fullname'} = $rec->{'fullname'} || $rec->{'lastname'} . ", " . $rec->{'firstname'};
    $rec->{'dateLoan'} = $rec->{'dateLoan_text'} || $rec->{'dateLoan'};
    $rec->{'dateDue'} = $rec->{'dateDue_text'} || $rec->{'dateDue'};
    push @{$list->{'list'}}, $rec ;
  }
  return $list;
}

sub eq_rpt_dateTypeList {
    my $msgFile = loc_getMsgFile('circ/itemOnLoan.msg');
    my $rptDateTypeList=[
      {'value'=>'dueDate'   , 'id'=>'dueDate',    'name'=> $msgFile->{'dueDateTxt'}},
      {'value'=> 'loanDate' , 'id'=>'loanDate',   'name'=>  $msgFile->{'loanDateTxt'} }
    ];
    return $rptDateTypeList;
}

sub eq_rpt_getItemAcqst {
    my ($dbh,  $params) = @_;
    my $dateFrom= $params->{'dateFrom'};
    $dateFrom .= " 00:00:00";
    my $dateTo= $params->{'dateTo'};
    $dateTo .= " 23:59:59";
    my @itemList = ();
    my ($itemCount,$recordCount)= (0,0);
    my $buildingId = $params->{'buildingId'} || "";
    my $sql = <<_SQL_;
select r.rname as name ,i.rid,i.barcode,i.createdDate,
MAX(case when f.fId=1 then f.fValue end) as manufacturer,
MAX(case when f.fId=3 then f.fValue end) as model
from eq_items i inner join eq_records r using(rid) 
    left outer join eq_recordFields f on r.rid=f.rid 
where i.barcode not regexp '^___' && i.deleted<>0  and i.createdDate between '$dateFrom' and '$dateTo'
group by i.barcode
order by r.rname , r.rid
_SQL_

    my $sql_itemCount="select count(*) from eq_items i inner join eq_records r using(rid) where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo'";
    my $sql_recordCount="select count(distinct i.rid) from eq_items i inner join eq_records r using(rid) where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo'";


   if ($params->{'buildingId'} && $params->{'buildingId'} ne "") {
   $sql = "select r.rname as name ,i.rid,i.barcode,i.createdDate,
MAX(case when f.fId=1 then f.fValue end) as manufacturer,
MAX(case when f.fId=3 then f.fValue end) as model
from eq_items i inner join eq_itemFields itf on (i.iid=itf.iid and i.rid=itf.rid and itf.sfId=18)
inner join eq_records r on r.rid=i.rid
    left outer join eq_recordFields f on r.rid=f.rid 
where i.barcode not regexp '^___' && i.deleted<>0  and i.createdDate between '$dateFrom' and '$dateTo' && itf.sfValue='$buildingId'
group by i.barcode order by r.rname , r.rid " ; 

  $sql_itemCount="select count(*) from eq_items i 
    inner join eq_itemFields itf on (i.iid=itf.iid and i.rid=itf.rid and itf.sfId=18)
    inner join eq_records r on r.rid = i.rid
    where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo'  && itf.sfValue='$buildingId'";
  $sql_recordCount="select count(distinct i.rid) from eq_items i 
    inner join eq_itemFields itf on (i.iid=itf.iid and i.rid=itf.rid && itf.sfId=18)
    inner join eq_records r on r.rid=i.rid
    where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo' && itf.sfValue='$buildingId'";
   }

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $recordInfo;
    while (my $rec = $sth->fetchrow_hashref) {
      push @itemList ,$rec;
    }
    $sth = $dbh->prepare($sql_itemCount);
    $sth->execute();
    $itemCount = $sth->fetchrow_array;
    $sth = $dbh->prepare($sql_recordCount);
    $sth->execute();
    $recordCount = $sth->fetchrow_array;
    $sth->finish;
    return ($recordCount,$itemCount,\@itemList);
}

sub eq_rpt_getItemAcqst_1 {
  my ($dbh,$p) = @_;
  my $dateFrom= $p->{'dateFrom'};
    $dateFrom .= " 00:00:00";
  my $dateTo= $p->{'dateTo'};
    $dateTo .= " 23:59:59";

  my ($itemCount,$recordCount,@itemList)= (0,0,());
  my $sql = <<_SQL_;
select r.name ,i.rid,i.barcode,i.createdDate,r.manufacturer,r.model
from eq_item i inner join eq_record r using(rid) 
where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo'
group by i.barcode
order by r.name,r.rid
_SQL_
  my $sql_itemCount="select count(*) from eq_item i inner join eq_record r using(rid) 
    where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo'";
  my $sql_recordCount="select count(distinct i.rid) from eq_item i inner join eq_record r using(rid) 
    where i.barcode not regexp '^___' && i.deleted<>0 and i.createdDate between '$dateFrom' and '$dateTo'";
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  my $recordInfo;
  while (my $rec = $sth->fetchrow_hashref) {
    push @itemList ,$rec;
  }
  $sth = $dbh->prepare($sql_itemCount);
  $sth->execute();
  $itemCount = $sth->fetchrow_array;
  $sth = $dbh->prepare($sql_recordCount);
  $sth->execute();
  $recordCount = $sth->fetchrow_array;
  $sth->finish;
  return ($recordCount,$itemCount,\@itemList);
}

sub eq_rpt_getItemListByStatus {
  my ($dbh,$p) = @_;
  my $statusCode = $p->{'status'};
  my $dateFrom= $p->{'dateFrom'};
  my $dateTo  = $p->{'dateTo'};
  my $pageNum = $p->{'pageNum'};
  my $pageSize = $p->{'pageSize'};
  my $sortBy = $p->{'sortBy'} || 'name';
  my $reverse = $p->{'reverse'};
  my $bcList = $p->{'bcList'};
  my $bcListStr = join (",", map "'$_'",  @$bcList) || "";
  my $buildingId = $p->{'buildingId'} || "";
  if ($p->{'date'} eq 'all'){
    $dateFrom = "2000-01-01";
    $dateTo = $p->{'todayStr'} ;
  }
  $dateFrom .= " 00:00:00";
  $dateTo .= " 23:59:00";
  my $sqlCount = (<<_SQLCount_);
select count(*)             
from eq_item i inner join             
  (select ist1.* from (select * from eq_itemStatus where ondate between '$dateFrom' and '$dateTo') as ist1                 
    left outer join 
      (select * from eq_itemStatus where ondate between '$dateFrom' and '$dateTo') as ist2             
      on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status=?)             
  as ist on i.barcode=ist.barcode 
_SQLCount_

  my $sql = (<<_SQL_);
select r.rid,r.name,r.manufacturer,r.model,r.category,r.warrantyNote,r.manufacturerUrl,r.manualUrl,r.supplierUrl,r.generalNote,r.generalAccessoriesNote,
i.barcode,i.price,i.serialNumber,i.location,i.buildingCode,i.copyNumber,i.typeId,i.districtInventoryNumber,i.fundingSource,i.districtCode,i.machineName,
i.statusNote,i.vendorName,i.assignedPersonFirstName,i.assignedPersonLastName,i.purchaseOrder,i.ipAddress,i.macAddress,i.softwareKey,i.cartNumber,i.slotNumber,i.accessories,
ist.ondate,ist.note as statusNote,
u.lastname,u.firstname,u.userbarcode,u.homeroom,u.buildingcode as ubuildingcode,u.teacher,u.grade,u.yeargraduation
from eq_item i 
  inner join eq_record r using(rid)
	inner join 
    (select ist1.* from
      (select * from eq_itemStatus where ondate between '$dateFrom' and '$dateTo') as ist1
        left outer join (select * from eq_itemStatus where ondate between '$dateFrom' and '$dateTo') as ist2
  on ist1.barcode=ist2.barcode && ist1.id <ist2.id where ist2.id is null && ist1.status='$statusCode') as ist on i.barcode=ist.barcode
  left outer join (eq_loan l inner join eq_odl odl on l.id = odl.idloan inner join opl_user u on l.uid = u.uid) on l.barcode = i.barcode
_SQL_
  
  my $condStr=" where ";
  if ($p->{'buildingId'} && $p->{'buildingId'} ne ""){
    $condStr .= " i.buildingcode ='$p->{'buildingId'}'";
    $sqlCount .= " where i.buildingcode ='$p->{'buildingId'}'";
  }
  $condStr = $condStr eq " where "? $condStr: $condStr . " &&";
=item
  if ($statusCode==14){
    $condStr .= " l.dateReturn<=ist.ondate";
    $condStr .= "group by l.id";
  }
  else{
    $condStr .= " l.dateReturn=ist.ondate ";
    $condStr .= "group by i.barcode"
  }
=cut  

  $sql .= $condStr eq " where "?"": $condStr ;
  if ($bcListStr && $bcListStr ne ""){
     $sqlCount .= " && i.barcode in ($bcListStr)";
     $sql .=  " where i.barcode in ($bcListStr)";
  }
  $sql .= " group by barcode";
  $sql .= " order by $sortBy";
  if ($reverse == 1){
    $sql .= " desc ";
  }
  my $offset = $pageNum>1? ($pageNum-1)*$pageSize:0;
  my $limit = $pageSize;
  if (!$bcListStr || $bcListStr eq "" ){
    $sql .= " limit $offset,$limit";
  }
  my $user;
  my $last_loan;
  my @rs=(); 
  my @params=$statusCode;
  my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @params);
  my $sth = $dbh->prepare($sql);
  #$sth->execute($statusCode);
  $sth->execute();
  while(my $rec=$sth->fetchrow_hashref() ){
      $last_loan = {};
      if ($rec->{'firstname'} eq "" && $rec->{'lastname'} eq "" && $rec->{'userbarcode'} eq ""){
        $last_loan = _getLastCheckoutInfo($dbh,$rec->{'barcode'});
      }
      $rec->{'last_loan'} = $last_loan || {};
      $rec->{'note'} =~ s/"/\"/g;
      $rec->{'note'} =~ s/\//\//g;
      $rec->{'note'} =~ s/\/\/\///g;
      $rec->{'note'} =~ s/\#//g;
      $rec->{'note'} =~ s/,/\,/g;
      push @rs, $rec;
  } 
  $sth->finish;
  return ($resultSize, \@rs);
}

sub _getLastCheckoutInfo {
  my ($dbh,$barcode) = @_;
  my $sql = (<<_STH_);
  select u.firstname,u.lastname,u.userbarcode,u.buildingcode,l.dateLoan,l.dateReturn from eq_loan l inner join opl_user u on l.uid=u.uid where barcode='$barcode' order by dateLoan desc limit 1
_STH_
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  my $loan = $sth->fetchrow_hashref;
  $sth->finish;
  return $loan;
}

#get items returned late or overdue 
sub eq_rpt_getLateReturnOdItemList {
  my ($dbh,$p)=@_;
  my $dateFrom = $p->{'dateFrom'};
  my $dateTo = $p->{'dateTo'};
  my $equipment = $p->{'eqName'};
  my $itemType = $p->{'itemType'};
  my $sql = (<<_SQL_);
select r.rname as equipment,barcode,dateDue,dateReturn,
  l.uid, u.firstname,u.lastname,u.userbarcode, to_days(l.dateReturn)-to_days(l.dateDue) as daysLOd,
  timediff (l.dateReturn,l.dateDue)
from eq_loan l 
inner join opl_user u using(uid) 
inner join eq_items i using(barcode)
inner join eq_records r using(rid) 
where (dateLoan between '$dateFrom' and '$dateTo') 
and ((dateReturn is not null and dateReturn>dateDue) OR (dateReturn is NULL and dateDue>now()));
_SQL_
  if (defined $equipment && $equipment != "" && $equipment != "*"){
    $sql .= " and r.rname regexp '$equipment' ";
  }
  if (defined $itemType && $itemType != ""){
    $sql .= " and i.typeId = '$itemType'";
  }
  my $sth= $dbh->prepare($sql);
  $sth->execute();
  my @list;
  while(my $rec=$sth->fetchrow_hashref) {
    $rec->{'daysLOd'} = int($rec->{'daysLOd'});
    push @list,$rec;
  }
  return \@list;
}

1;