#!/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
);


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,());
if ($ENV{'REQUEST_METHOD'} eq "POST") {
    $input = decode_json($cgi->param('POSTDATA'));
    ($totalFound,$retList) = getList($dbh,$input);
}

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

});

#--------------------------------------------------------------------------------
#
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 @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, 
        u.firstname as firstname, u.lastname as lastname , u.userbarcode,i.note from (
        select i1.rid ,i1.barcode,ist.ondate,ist.note
            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 
        inner join eq_records r on r.rid = i.rid 
        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 && l.dateReturn = i.ondate";

    my @param = $statusCode;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @param);
    my $sth = $dbh->prepare($sql);
    $sth->execute($statusCode);
    while(my ($rid,$name,$barcode,$ondate,$idloan,$firstname,$lastname,$ubarcode,$note)=$sth->fetchrow_array()){
        push @itemList, {
             rid         => $rid,
             name       => $name,
             barcode     => $barcode,
             ondate      => date_text($ondate,1,"en"),
             firstname   => $firstname || "",
             lastname    => $lastname || "",
             ubarcode    => $ubarcode || "",
             note        => $note || "",
             selected    => "false"
        };
    }     
    $sth->finish;
    return ($resultSize, \@itemList);
}