#!/usr/bin/perl

use strict;
use CGI;

use Opals::Context;


use POSIX qw(
    ceil
);


use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Tb_Record qw(

    tb_record_findByRId

);

use Opals::Utility qw(
    util_escapeXml
);

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

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

my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'eqmnt/report/getRecs.tmpl',
    }
);

my $sortOrder = $input->{'sortOrder'};
    $sortOrder = " asc " if (! $sortOrder);
my $sortAttr = $input->{'sortBy'};
    if (!$sortAttr){
        $sortAttr = " i.barcode ";
    }
    elsif ($sortAttr =~ m/name/){
        $sortAttr = " r.rname ";
    }
    else{
        $sortAttr = " i.barcode ";
    }


my $pSize = 10;
my $curPage = $input->{'pNum'};
($curPage && $curPage >= 1) || ($curPage = 1);
my $offset = ($curPage - 1) * $pSize + $ENV{'Z_INDEX_BASE'};


my ($resultSize, $result,$bcMatchedList) ;
my $sf=$input->{'sf0'};
my $kw =$input->{'kw0'};

    my ($resultSize , $itemList) = _getRecs($dbh,$sf, $kw, $sortAttr, $sortOrder,$offset,$pSize  );
    my $recReturned = scalar(@$itemList);
        

    $template->param(
        resultSize  => $resultSize,
        recReturned => $recReturned,
        itemList    => $itemList,
    );


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


sub _getRecs {
   
    my ($dbh, $sfCode, $kw, $sortAttr, $sortOrder,  $pOffset, $pSize) = @_;
    my $from = "";
    my $where = "";
    
    if ($sfCode eq "eqName"){
        #$sortAttr = ' r.rname ';
        $from = " from eq_records r inner join eq_items i using(rid) ";
        $where = " where r.rname regexp '$kw'  
            && i.barcode not regexp '^\_\_\_' " ;
    }
    elsif ($sfCode eq "barcode"){
        #$sortAttr = ' i.barcode ';
        $from = " from eq_records r inner join eq_items i using(rid) ";
        $where = " where i.barcode regexp '$kw'  
            && i.barcode not regexp '^\_\_\_' ";
    }
    else {
        $from = " from eq_records r inner join eq_items i on r.rid = i.rid 
        left outer join eq_recordFields r_f on r.rid = r_f.rid && r_f.fValue !='' 
        left outer join eq_itemFields i_f on r.rid = i_f.rid && i_f.sfValue != '' ";
        $where = " where (r.rname like '\%$kw\%' || i.barcode like '\%$kw\%' ||
        r_f.fValue like '\%$kw\%' || i_f.sfValue like '\%$kw\%' )
        && i.barcode not regexp '^\_\_\_' ";
    }
    #$where .= " order by r.rid, i.iid ";
    my $sqlCount = "select count(distinct i.barcode) $from $where ";
    my $sql = "select r.rid,r.rname, i.barcode as barcode $from $where " ;
    $sql .= " group by i.barcode order by $sortAttr $sortOrder limit $pOffset, $pSize ";
    my $resultSize = $dbh->selectrow_array($sqlCount);
    my $sth;
    my @itemList;
    if ($resultSize && $resultSize > 0){
       $sth = $dbh->prepare($sql);
       $sth->execute();
       while (my $rec = $sth->fetchrow_hashref){
            push @itemList, $rec;
       }
    }
    $sth->finish if $sth;
    return ($resultSize, \@itemList);
}