#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use Time::localtime;
use Opals::Context;
use Opals::Constant;

use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::Date qw(
    date_parse
    date_today
    date_text
);

use Opals::Equipment qw(

    eq_defRecord_getList
    eq_defRecord_getLists
    eq_defItem_getList
    eq_defItem_getLists
    eq_record_findByRId
    eq_item_findByBarcode
    
);


use Opals::Template qw(
    tmpl_read
    tmpl_write
);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $sessionID = $cgi->cookie('globalSessionID');

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

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



my $dateFrom    = $input->{'dateFrom'};
my $dateTo      = $input->{'dateTo'};
my $dateRange   = $input->{'dateRangeOpt'};
if ($dateRange eq "all"){
        $dateFrom= "1970-01-01";
        $dateTo  = $todayStr;
    }
my $sort1       = $input->{'sort1'};
my $sortOrder   = $input->{'sortOrder'};
    ($sortOrder && $sortOrder >=1 ) || ($sortOrder = 1);
my $prtOpt = $input->{'prtOpt'};   
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';
    }

my $sortAttr = 'name';

if ($sort1 eq 'recordId'){
    $sortAttr = 'recordId';
}
elsif ($sort1 eq 'barcode'){
    $sortAttr = 'barcode';    
}
elsif ($sort1 eq 'ondate'){
    $sortAttr = 'ondate';    
}
my @csvHeader=qw(
    NAME
    MANUFACTURE
    MODEL
    SERIAL#
    BARCODE
    STATUS
);
my @fields=(
    "uname",
    "rname",
    "barcode",
    "Manufacturer",
    "Model",
    "General notes",
    "Serial#",
    "Price",
    "Items Status notes"
);

my @field1=qw(
    uname
    rname
    barcode
);

my $itemList;
my $defRecList      =  eq_defRecord_getList($dbh);
my $defItemList      = eq_defItem_getList($dbh);

if ($input->{'op'} eq "exportCSV"){
    if ($prtOpt eq 'all') {
        $itemList = getAllByStatus($dbh,$dateFrom, $dateTo, $sortAttr, $sortOrder,$status); 
    }
    else{
        $itemList = getSelectedStatus($dbh,$sessionID, "eq_item" . $type, $status);
    }
    my @list;
    my @header = ("Name","Equipment","Barcode");
    foreach my $rd (@$defRecList){
        if ($rd->{'display'}){
            push @header, $rd->{'name'};
        }
    }
    foreach my $id(@$defItemList){
        if ($id->{'display'}){
            push @header, $id->{'name'};
        }
    }
    foreach my $r(@$itemList){
        my $item=[];
        push @$item,($r->{'uname'},$r->{'rname'},$r->{'barcode'});

        foreach my $rf(@{$r->{'rFields'}}){
            if ($rf->{'display'}){
                push @$item, $rf->{'fValue'};
            }
        }
        foreach my $if(@{$r->{'iFields'}}){
            if ($if->{'sfDisplay'}){
                push @$item, $if->{'sfval'};
            }
        }
        push @list,$item;
    }
    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=equipment.csv\n\n"; 
    print "\"",  join("\",\"",@header), "\"\n";
    foreach my $row(@list){
        print "\"",  join("\",\"",@$row), "\"\n";
    }
}
else{
    if ( $prtOpt eq 'all'){
        $itemList = getAllByStatus($dbh,$dateFrom, $dateTo, $sortAttr, $sortOrder,$status);
    }
    else{
        $itemList = getSelectedStatus($dbh,$sessionID, "eq_item" . $type, $status);
    }
    $template->param(
        itemList    => $itemList,
        type            => $type,
    );
    tmpl_write($dbh, $cgi, $cookie, $template);
}

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

sub getAllByStatus{
    my ($dbh, $dateFrom,$dateTo,$sortAttr,$sortOrder,$status) = @_;
    my @itemList = ();
    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 opl_user u on l.uid = u.uid 
        on l.barcode = i.barcode
        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";
    }
    else{
        $sql .= " order by i.barcode";
    }

    if ($sortOrder && $sortOrder ==2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    my @param = $status;
    my $sth = $dbh->prepare($sql);
    $sth->execute($status);
    while(my ($rid,$rname,$barcode,$ondate,$idloan,$uname,$ubarcode) = $sth->fetchrow_array()){
        my $recordInfo = eq_record_findByRId($dbh,{recordId=>$rid});
        my $itemInfo    = eq_item_findByBarcode($dbh,$barcode);
        my $fields = $itemInfo->{'fields'};

        push @itemList, {
             rid        => $rid,
             rname      => $rname,
             barcode    => $barcode,
             ondate     => $ondate,
             uname      => $uname,
             ubarcode   => $ubarcode,
             rFields    => $recordInfo,
             iFields    => $itemInfo->{'fields'}
        };
    }         
    $sth->finish;
    return (\@itemList);
}

sub getSelectedStatus {
    
    my ($dbh,$sessionID, $ssVar , $status) = @_;
    my @itemList = ();

    my $sql = " select v.rid , r.rname, v.barcode, s.ondate,l.id, concat_ws(' ', u.firstname, u.lastname) as uname , u.userbarcode 
        from eq_itemStatus s 
        inner join opl_sessionVar v on s.barcode = v.barcode && s.status = ?
        inner join eq_records r on r.rid = v.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 = v.barcode
        where v.ssid = '$sessionID' && v.var = '$ssVar' order by v.sOrder ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($status);
    while(my ($rid , $rname,$barcode, $ondate, $idloan,$uname,$userbarcode) = $sth->fetchrow_array()){
        my $recordInfo = eq_record_findByRId($dbh,{recordId=>$rid});
        my $itemInfo    = eq_item_findByBarcode($dbh,$barcode);
        push @itemList, {
             rid         => $rid,
             rname       => $rname,
             barcode     => $barcode,
             ondate      => $ondate,
             uname       => $uname ,
             ubarcode    => $userbarcode,
             rFields    => $recordInfo,
             iFields    => $itemInfo->{'fields'}
        };
    }
    $sth->finish;
    return  \@itemList;
}

__END_OF_FILE: