#!/usr/bin/perl

#use utf8;
use strict;


use CGI;
use JSON;

use Opals::Context;
use Opals::Constant;
use Opals::Date qw(
    date_text
    date_now
);

use Opals::Eq_Report qw(
  eq_rpt_getItemListByStatus
);

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

my $cgi = CGI->new;
my $input;
my $todayStr   = date_now();
$todayStr =~ s/([\d]{4}-[\d]{2}-[\d]{2}) ([\d:])+/$1/;

my ($totalFound,$retList) = (0,());
my ($found,$result) = (0,());
if ($ENV{'REQUEST_METHOD'} eq "POST") {
    $input = decode_json($cgi->param('POSTDATA'));
    $input->{'todayStr'} = $todayStr;
    ($found,$result) = getList($dbh,$input);
    #($found,$result)=getListByStatus($dbh,$input);
    #($found,$result)=eq_rpt_getItemListByStatus($dbh,$input);
}

print "Content-type: text/plain\n\n";
print  to_json({
    totalFound=> $found, #$totalFound,
    itemList  => $result #$retList,
});

#--------------------------------------------------------------------------------
#
#
sub getListByStatus {
  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) || "";
  if ($p->{'date'} eq 'all'){
    $dateFrom = "2000-01-01";
    $dateTo = $todayStr ;
  }
  $dateFrom .= " 00:00:00";
  $dateTo .= " 23:59:00";

  open debug,">/tmp/R"; 
  print debug "input::",to_json($input),"\n"; 
  print debug "\nbcList : $bcList\t", $bcListStr , "\n";
  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;
    #$limit = ($limit > $offset) 
  if (!$bcListStr || $bcListStr eq "" ){
    $sql .= " limit $offset,$limit";
  }
  print debug "SQLCount: $sqlCount\n\n";
  print debug "\nSQL: $sql \n";
  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;
  print debug "resultSize:$resultSize\n";
  close debug; 
  return ($resultSize, \@rs);

}
################################################################################

sub getList{
    my ($dbh,  $params ) = @_;
    my $statusCode = $params->{'status'};
    my $dateFrom= $params->{'dateFrom'};
    my $dateTo  = $params->{'dateTo'};
    if ($params->{'date'} eq 'all'){
        $dateFrom = "2000-01-01";
        $dateTo = $todayStr ;
    }
    $dateFrom .= " 00:00:00";
    $dateTo .= " 23:59:00";
    my $pageNum = $params->{'pageNum'};
    my $pageSize = $params->{'pageSize'};
    my $bcList = $params->{'bcList'};
    my $bcListStr = join (",", map "'$_'",  @$bcList) || "";

    my @itemList = ();
    my $sqlCount = " select count(*)
            from eq_items i1 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 i1.barcode=ist.barcode ";
    my $sql = "
    select i.rid,r.rname,i.barcode,i.ondate,l.id, itf.sfValue as statusNote,u.firstname as firstname,u.lastname as lastname,u.userbarcode,u.buildingcode as ubuildingcode, 
        replace(replace(i.note,'\n',' '), '\r',' ') as note,ii.sfValue as ibuildingCode,man.fValue as manufacturer,model.fValue as model from (
        select i1.rid ,i1.barcode,ist.ondate,ist.note,i1.iid
            from eq_items i1 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 i1.barcode=ist.barcode) as i 
            left outer join eq_itemFields ii on ii.rid=i.rid && ii.iid=i.iid && ii.sfId=18 ";
     if ($params->{'buildingId'} && $params->{'buildingId'} ne ""){
      $sql .= " && ii.sfValue='$params->{'buildingId'}'";
      $sqlCount .= "inner join eq_itemFields ii on ii.rid=i1.rid && ii.iid=i1.iid && ii.sfId=18 && ii.sfValue='$params->{'buildingId'}'";
     }
     $sql .= " inner join eq_records r on r.rid = i.rid 
          left outer join eq_recordFields man on man.rid=r.rid && man.fId=1 
          left outer join eq_recordFields model on model.rid=r.rid && model.fId=3
          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 ";
        if ($statusCode==14){
          $sql .= " && l.dateReturn <= i.ondate";
			    $sql .= " left outer join eq_itemFields itf on itf.rid=i.rid and itf.iid=i.iid and itf.sfId=38 ";
        }
        else{
          $sql .= " && l.dateReturn = i.ondate ";
			    $sql .= " left outer join eq_itemFields itf on itf.rid=i.rid and itf.iid=i.iid and itf.sfId=38 ";
        }
    my @param = $statusCode;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @param);
    my $offset = $pageNum>1? ($pageNum-1)*$pageSize:0;
    my $limit = $pageSize;
    if ($bcListStr && $bcListStr ne ""){
      $sqlCount .= " && i.barcode in ($bcListStr)";
      $sql .=  " where i.barcode in ($bcListStr)";
    }
    else{
      $sql .= " limit $offset,$limit";
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute($statusCode);
    my $user;
    my ($last_loan_firstname,$last_loan_lastname,$last_loan_ubarcode,$last_loan_ubuildingcode) = ("","","");
    my $last_loan;

    open debug, ">/tmp/debugK";
    print debug "***SQL ***: $sql \n\n"; 
    #print debug "\nbcList : $bcList\t", $bcListStr , "\n";
    #print debug "***SQLCount:\t$sqlCount\n"; 
    close debug; 
    while(my ($rid,$name,$barcode,$ondate,$idloan,$statusNote,$firstname,$lastname,$ubarcode,$ubuildingcode,$note,$ibuildingCode,$manufacturer,$model)=$sth->fetchrow_array()){
      ($last_loan_firstname,$last_loan_lastname,$last_loan_ubarcode,$last_loan_ubuildingcode) = ("","","","");
      $last_loan = {};
      if ($firstname eq "" && $lastname eq "" && $ubarcode eq ""){
        $last_loan = getLastCheckoutInfo($dbh,$barcode);
      }
      $note =~ s/"/\"/g;
      $note =~ s/\//\//g;
      $note =~ s/\/\/\///g;
      $note =~ s/\#//g;
      $note =~ s/,/\,/g;
      push @itemList, {
           rid            => $rid,
           name           => $name,
           equipment      => $name,
           model          => $model,
           manufacturer   => $manufacturer, 
           barcode        => $barcode,
           ondate         => $ondate,
           ondate_text    => date_text($ondate,1,"en"),
           firstname      => $firstname || "",
           lastname       => $lastname || "",
           ubarcode       => $ubarcode || "",
           ubuildingcode  => $ubuildingcode || "",
           last_loan      => $last_loan || {},
           note        		=> $note || "",
           selected    		=> "false",
					 statusNote		  => $statusNote || "",
           ibuildingCode  => $ibuildingCode?$ibuildingCode:"",
      };
    }     
    $sth->finish;
    return ($resultSize, \@itemList);
}

sub getUserLostItem {
  my ($dbh,$lid,$barcode) = @_;
  my $sql = (<<_STH_);
  select u.firstname,u.lastname,u.userbarcode 
  from eq_loan l inner join eq_odl d on l.id=d.idloan 
  inner join opl_user u on l.uid=u.uid where l.id=$lid && d.type='lost' && l.barcode='$barcode'
_STH_
  my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $user = $sth->fetchrow_hashref;
    $sth->finish;
    return $user;
}

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;
}