#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

use Opals::Constant;

use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
    tmpl_rangedPageList

);

use Opals::Date qw(
    date_text
);

use Opals::Equipment qw(
    
    eq_item_findByBarcode 

);
my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();


my ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
            {
                dbh             => $dbh,
                cgi             => $cgi,
                tmplFile        => 'eqmnt/report/itemStats.tmpl',
                reqPermission   => 'eq_report',
            }
    );

my $pref = tmpl_preference($dbh);
my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
my $sort1       = $input->{'sort1'};
my $sortOrder   = $input->{'sortOrder'};
my $pRange = 5;
my $pSize = 10;
my $curPage = $input->{'pNum'};
my $offset;
    ($curPage && $curPage >= 1) ||($curPage = 1 );
    $offset = ($curPage - 1 ) * $pSize;


my $status = $input->{'type'};
my $type ;

    if ($status eq '3'){
        $status= ITEM_LOST;
        $type = 'Lost';
    }
    elsif ($status eq '2'){
        $status= ITEM_DAMAGED ;
        $type = 'Damaged';
    }
    elsif ($status eq '0'){
        $status= ITEM_INACTIVE ;
        $type = 'Missing';
    }
    else {
        $status= ITEM_LOST;
        $type = 'Lost';

    }

    if (!$sort1){
        $sort1 = "title";
    }
    if (!$sortOrder){
        $sortOrder    = '2';
    }
    if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $todayStr;
    }
    if (! $dateFrom && ! $dateTo){
        $dateFrom   = $todayStr;
        $dateTo     = $todayStr;
    }
    else{
        $dateFrom .= " 00:00:00";
        $dateTo   .= " 23:59:59";
    }
=item    
    $template->param(
        dateFrom    => substr($dateFrom,0,10),
        dateTo      => substr($dateTo,0,10),
    );
=cut    
    if ($dateRange eq 'fYear'){
        $template->param(
            rangeFYear      => 1,
            dateRangeOpt    => "fYear",
            dateFrom    => substr($dateFrom,0,10),
            dateTo      => substr($dateTo,0,10),

        );
    }
    elsif ($dateRange eq 'all'){
        $template->param(
            rangeAll      => 1,
            dateRangeOpt    => "all",
        );
    }
    else{
        $template->param (
            rangeSel        => 1,
            dateRangeOpt    => "rangeSel",
            dateFrom    => substr($dateFrom,0,10),
            dateTo      => substr($dateTo,0,10),
        );
    }


    my $sortAttr = 'name';
    if ($sort1 eq 'recordId'){
        $sortAttr = 'recordId';
    }
    elsif ($sort1 eq 'barcode'){
        $sortAttr = 'barcode';    
    }
    elsif ($sort1 eq 'ondate'){
        $sortAttr = 'ondate';    
    }
    elsif ($sort1 eq 'username'){
        $sortAttr = 'username';    
    }

    my ($resultSize, $resultList) = getList($dbh, $offset, $pSize,  $dateFrom,  $dateTo, $sortAttr ,$sortOrder);
    my @rangedPageList = tmpl_rangedPageList($resultSize, $curPage, $pSize, $pRange);
    $template->param(
        eq_itemLost     => ($status == 3)? 1 : 0,
        eq_itemDamaged  => ($status == 2)? 1 : 0,
        type            => $type,
        status          => $status,
        resultList      => $resultList,
        resultSize      => $resultSize,
        pNum            => $curPage,
        sort1           => $sort1,
        sortOrder       => $sortOrder,
        sortOrderReverse => ($sortOrder && $sortOrder == 1)  ? 2 : 1,
        sortRecordId    => ($sortAttr eq 'recordId') ? 1 : 0,
        sortName       => ($sortAttr eq 'name')    ? 1 : 0,
        sortBarcode     => ($sortAttr eq 'barcode')  ? 1 : 0,
        sortPrice       => ($sortAttr eq 'price')     ? 1 : 0,
        sortOndate      => ($sortAttr eq 'ondate')     ? 1 : 0,
        sortUsername    => ($sortAttr eq 'username')     ? 1 : 0,
        sortDown        => ($sortOrder && $sortOrder == 1)  ? 1 : 0,
        rangedPageList  =>  \@rangedPageList,
   );

tmpl_write($dbh, $cgi, $cookie, $template);

#########################################################################

sub trimArticle{
    my ($str)=@_;
    $str =~ s/^a |^an |^the //i;
    return $str;
}

sub getList{

    my ($dbh,  $offset, $pSize, $dateFrom,$dateTo,$sortAttr,$sortOrder) = @_;
    my @itemLostList = ();
    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, 
        concat_ws(' ', u.firstname, u.lastname) as uname , u.userbarcode from (
        select i1.rid ,i1.barcode,ist.ondate
            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";
       # where l.id = (select max(id) from eq_loan l2 where l.barcode = l2.barcode) || l.id is null";
    if ($sortAttr =~ m/^name/i){
        $sql .= " order by r.rname";
    } 
    elsif ($sortAttr =~ m/recordId/i){
        $sql .= " order by r.rid";
    }
    elsif ($sortAttr =~ m/ondate/i){
        $sql .= " order by i.ondate";
    }
    elsif ($sortAttr =~ m/username/i){
        $sql .= " order by uname, userbarcode";
    }
    else{
        $sql .= " order by i.barcode";
    }

    if ($sortOrder && $sortOrder ==2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }

    my @param = $status;
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @param);
        
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );

    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }
    $sql .= " LIMIT $offset , $availRange ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($status);
    while(my ($rid, $rname, $barcode,$ondate,$idloan, $uname, $ubarcode) = $sth->fetchrow_array()){
        push @itemLostList, {
             rid         => $rid,
             rname       => $rname,
             barcode     => $barcode,
             ondate      => $ondate,
             uname       => $uname,
             ubarcode    => $ubarcode,
        };
    }         
    $sth->finish;
    #$sth_user->finish;
    return ($resultSize, \@itemLostList);
}