package Opals::Eq_Search;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    
    eq_category_getList
    eq_category_getListById

    eq_search_cat
    eq_search_countByCat
    eq_search
);

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

use Text::CSV_XS;

use Digest::SHA qw(
    sha1_base64
    sha1_hex
);


################################################################################
#               SEARCH by Category
################################################################################

sub eq_category_getList {

    my ($dbh) = @_;
    my @catList;

    my $sqlCount = "select count(*) from eq_items where rid in (select rid from eq_records where FIND_IN_SET (?, category))";
    my $sql = "select * from eq_category where parentId=0 order by name";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $subsql = "select * from eq_category where parentId=? order by name";
    my $sth_sub = $dbh->prepare($subsql);
    my $sthCount = $dbh->prepare($sqlCount);
    while ( my $rec = $sth->fetchrow_hashref){
        $sthCount->execute($rec->{'id'});
        $sth_sub->execute($rec->{'id'});
        $rec->{'hasChild'} = 0;
        $rec->{'count'} = $sthCount->fetchrow_array();
        while(my $c = $sth_sub->fetchrow_hashref){
            $rec->{'hasChild'} = 1;
            $sthCount->execute($c->{'id'});
            $c->{'count'} = $sthCount->fetchrow_array();
            push @{$rec->{'children'}}, $c;
        }
        push @catList,$rec;
    }
    $sth->finish;
    $sth_sub->finish;

    return \@catList;
}

sub eq_category_getListById {

    my ($dbh,$id) = @_;
    my @catIdList;
    
    if ($id > 0) {
        my $sql = "select id from eq_category where  id=$id || parentId=$id ";
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        while ( my $rec = $sth->fetchrow_hashref){
            push @catIdList,$rec->{'id'};
        }
    }
    return \@catIdList;
}

sub eq_search_cat{

    my ($dbh,$catId,$field,$kw,$offset,$pSize,$resultOrder,$sortAttr) = @_;
    my ($resultSize, @recordList)= (0, ());
    my $fCount = 1;
    my ($type, $sField, $reqF) = split(/:/, $field);
    my $sql = "select r.rid, r.rname,r.category from eq_records r WHERE_CLAUSE ";
    my $sqlCount = "select count(*) from eq_records r WHERE_CLAUSE ";

    my $sthRec = $dbh->prepare(<<_SQL_);
select  r.*, d.* from    eq_def d left join eq_recordFields r on r.fId = d.id && r.rid = ?
where   d.defType  = 'record' && r.id is not NULL order by d.fOrder 
_SQL_

    my $condCat=="";
    if ($kw eq '*'){
        if (scalar(@$catId)==0){
           $sql =~ s/WHERE_CLAUSE/where r.deleted = '0'/;
           $sqlCount =~ s/WHERE_CLAUSE/where r.deleted = '0'/;
        }
        else{
            foreach my $c(@$catId){
                if ($condCat eq ""){
                    $condCat .= "  find_in_set ($c,r.category) ";
                }
                else{
                    $condCat .= " || find_in_set ($c,r.category) ";
                }
            }
            $sql =~ s/WHERE_CLAUSE/where $condCat/;
            $sqlCount =~ s/WHERE_CLAUSE/where $condCat/;
        }
    }
    else{
        if (scalar(@$catId)==0){
            $sql =~ s/WHERE_CLAUSE/where MATCH(r.rname) AGAINST ('$kw' in boolean mode) /;
            $sqlCount =~ s/WHERE_CLAUSE/where MATCH(r.rname) AGAINST ('$kw' in boolean mode)/;
        }
        else{
            foreach my $c(@$catId){
                if ($condCat eq ""){
                    $condCat .= " find_in_set ($c,r.category) ";
                }
                else{
                    $condCat .= " || find_in_set ($c,r.category) ";
                }
            }
            $condCat =~ s/\|\|$//;
            $sql =~ s/WHERE_CLAUSE/where MATCH(r.rname) AGAINST ('$kw' in boolean mode) && $condCat/;
            $sqlCount =~ s/WHERE_CLAUSE/where MATCH(r.rname) AGAINST ('$kw' in boolean mode) && $condCat/;
        }
    }
    $resultSize = $dbh->selectrow_array($sqlCount, undef);
    my $availRange = ($resultSize>1)?$resultSize - $offset + 1 : 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }

    my $rCount = 0;
    my $rOrder = $offset;
    $sql .= " order by r.rname ";

    if ($resultSize > 0){
        $sql .= " LIMIT $offset , $availRange ";
        my $sth = $dbh->prepare($sql);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        $sth->execute();
        while (my ($rid,$rname,$category) = $sth->fetchrow_array()){
            $fCount = 1;
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sthRec->execute($rid);
            my @fields=(); 
            while (my $f = $sthRec->fetchrow_hashref()) {
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},      
                    fname       => $f->{'name'},
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            push @recordList, {
                rid         =>  $rid,
                name        =>  $rname,
                category    =>  $category,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sthRec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub eq_search_countByCat{

    my ($dbh, $catId, $kw) = @_;
    my $resultSize= 0;
    my $fCount = 1;

    my $sqlCount = "select count(*) from eq_records r ";
    my $cond=="";
    if ($kw eq '*'){
        if (scalar(@$catId)==0){
            
        }
        else{
            foreach my $c(@$catId){
                if ($cond eq ""){
                    $cond .= "  find_in_set ($c,r.category) ";
                }
                else{
                    $cond .= " || find_in_set ($c,r.category) ";
                }
            }
            $cond =~ s/\|\|$//;
        }
    }
    else{
         if (scalar(@$catId)==0){

        }
        else{

        }
    }
    if ( $cond && $cond ne ""){
        $sqlCount .=  " where ( $cond ) "; 
    }
    $resultSize = $dbh->selectrow_array($sqlCount, undef);
    return $resultSize || 0;

}

#******************************************************************
#                   section : SEARCH
#******************************************************************
sub record_maxOrder{
    my ($dbh) = @_;

    my $key = 'defType';
    my $maxOrder = $dbh->selectall_hashref(<<_SQL_, $key);
select  defType, max(fOrder) as maxOrder
from    eq_def
group by defType
_SQL_
    #return ($maxOrder->{'record'}->{'maxOrder'}, $maxOrder->{'item'}->{'maxOrder'});
    return $maxOrder;
}

sub eq_search {

    my ($dbh, $srchCatId, $field, $kw, $offset, $pSize, $sortOrder,$sortAttr,$exactmatch) = @_;
    my ($resultSize, $recordList);
    my ($type, $sField, $reqF) = split(/:/, $field);
    
    if ($type eq '0'){
        if ($kw eq '*'){
            ($resultSize, $recordList) = srch_eq_record_All($dbh,$sField,$kw,$offset,$pSize,$sortOrder,$sortAttr,$exactmatch);
        }
        else{
            ($resultSize, $recordList)  = srch_eq_record_item_All($dbh,$sField,$kw,$offset,$pSize,$sortOrder,$sortAttr,$exactmatch);
        }
    }
    elsif($type eq 'r'){
        if ($reqF eq '1'){
            ($resultSize, $recordList)   = srch_eq_record_byName($dbh,$sField,$kw,$offset,$pSize,$sortOrder,$sortAttr,$exactmatch);
        }
        else{
            ($resultSize, $recordList)   = srch_eq_record_bySubFields($dbh,$sField,$kw,$offset,$pSize,$sortOrder,$sortAttr,$exactmatch);
        }
    }
    elsif($type eq 'i'){
        if ($reqF eq '1'){
            ($resultSize, $recordList)  = srch_eq_item_byBarCode_ItemType($dbh,$sField,$kw,$offset,$pSize,$sortOrder,$sortAttr,$exactmatch);
        }
        else{
            ($resultSize, $recordList)  = srch_eq_item_bySubFields($dbh,$sField,$kw,$offset,$pSize,$sortOrder,$sortAttr,$exactmatch);
        }
    }
    return ($resultSize,$recordList);
}

sub srch_eq_record_byName{

    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr,$exactmatch) = @_;
    my ($sql,$sql1, $sql2, $sqlCount);
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sql1 = "select r.rid, r.rname, r.category from eq_records as r WHERE_CLAUSE ";
    #$sql2 = "select rid  from eq_records WHERE_CLAUSE ";
    $sqlCount = "select COUNT(*) from eq_records r WHERE_CLAUSE ";
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where r.deleted = '0'/;
        $sql1       =~ s/WHERE_CLAUSE /where r.deleted = '0'/;    
        }
    else{
        if ($exactmatch && $exactmatch == 1) {
            $sqlCount   =~ s/WHERE_CLAUSE /where rname = '$kw'  && r.deleted='0'/;
            $sql1       =~ s/WHERE_CLAUSE /where rname = '$kw'  && r.deleted='0'/;
        }else {
            $sqlCount   =~ s/WHERE_CLAUSE /where MATCH (rname) AGAINST ('$kw' in boolean mode)  /;
            $sql1       =~ s/WHERE_CLAUSE /where MATCH (rname) AGAINST ('$kw' in boolean mode)  /;
        }
    }
#cart type
    if ($field == 4) {
        $sqlCount .= " && container=1";
        $sql1 .= " && container=1";
        $sql2 .= " && container=1";
    }
    if ($sortAttr && $sortAttr > 0){
        $sql = "select r.rid, r.rname from eq_records as r inner join eq_recordFields as rf ";
        $sql .= " on r.rid = rf.rid && rf.fId = $sortAttr where r.rid in ( $sql2 ) ";
        if ($sortOrder && $sortOrder == 2){
            $sql .=  "order by rf.fValue desc ";         }
        else{
            $sql .= " order by rf.fValue asc ";        }
    }
    else{
         $sql .= $sql1 ;
         if ( $sortOrder && $sortOrder == 2){
            $sql .=  "order by r.rname desc ";        }
        else{
             $sql .= " order by r.rname asc ";        }
    }
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
    
    if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth     = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        my $sth_rec = $dbh->prepare(<<_SQL_);
select  r.*, d.*
from    eq_def d left join eq_recordFields r on r.fId = d.id && r.rid = ?
where   d.defType  = 'record' order by d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid, $rname,$category) = $sth->fetchrow_array()){
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},      
                    fname       => $f->{'name'},
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            push @recordList, {
                rid         =>  $rid,
                name        =>  $rname,
                category    =>  $category,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_record_bySubFields{

    my ($dbh, $field, $kw,$offset, $pSize , $sortOrder, $sortAttr,$exactmatch) = @_;
    my ($sql,$sql1, $sql2, $sqlCount);
    my @val = ();
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    #$sql = "select r.rid,r.rname,r.category from eq_records as r where r.rid in ";
    $sql = "select distinct (rf.rid) from eq_recordFields rf inner join eq_records r using(rid)  WHERE_CLAUSE ";
    $sqlCount = "select COUNT(distinct r.rid) from eq_recordFields as rf inner join eq_records as r using(rid) WHERE_CLAUSE ";
    
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE / where r.deleted = '0' && fId = $field /;
        $sql       =~ s/WHERE_CLAUSE / where r.deleted = '0' && fId = $field /;  
        }
    else{
        if ($exactmatch && $exactmatch == 1) {
            $sqlCount  =~ s/WHERE_CLAUSE /where rf.fValue = \?  && rf.fId = \? && r.deleted='0'/;
            $sql       =~ s/WHERE_CLAUSE /where rf.fValue = \? && rf.fId = \? && r.deleted='0'/;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE /where MATCH (rf.fValue) AGAINST (\? in boolean mode ) && rf.fId = \? && r.deleted='0'/;
            $sql       =~ s/WHERE_CLAUSE /where MATCH (rf.fValue) AGAINST (\? in boolean mode ) && rf.fId = \? && r.deleted='0'/;
        }
        @val        = ($kw, $field); 
    }
    if (! $sortAttr ){
        $sql .= " order by rf.fValue, r.rname " ;
        #$sql .= "(" . $sql1 . ") group by r.rid " ;
        }
    elsif ($sortAttr && $sortAttr > 0){
        $sql .= " && fId = $sortAttr order by rf.fValue, r.rname" ;
        #$sql .= "(" . $sql1 . ") group by r.rid " ;
        }
    else{
        $sql .= " order by r.rname, rf.fValue " ;
    }
    if ($sortOrder && $sortOrder == 2){
            $sql .=  " desc ";
        }
        else{
             $sql .= " asc ";
        }

    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef,@val);
    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
    if ($resultSize > 0){
        my $query = "$sql LIMIT $offset,$availRange";
        my $sth     = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        my $sth_rec = $dbh->prepare(<<_SQL_);
SELECT  r.rname, rf.*, d.*
FROM    eq_def as d 
    LEFT JOIN eq_recordFields as rf ON rf.fId = d.id && rf.rid = ?
    INNER JOIN eq_records as r USING (rid)
WHERE   d.defType  = 'record'
ORDER   by d.fOrder
_SQL_
        $sth->execute(@val);
        while (my ($rid ) = $sth->fetchrow_array()){
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            my ($rname,$category);
            while (my $f = $sth_rec->fetchrow_hashref()) {
                $rname = $f->{'rname'};
                $category = $f->{'category'};
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},      
                    fname       => $f->{'name'},
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            for (my $i = $fCount; $i <= $recMaxOrder; $i++) {
                push @fields, {
                    fid     => $i,
                    fval    => '', };
            }
            push @recordList, {
                rid         =>  $rid,
                name        =>   $rname,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_record_byAllSubFields{
    my ($dbh, $field, $kw,$offset, $pSize) = @_;
    my ($sql, $sqlCount);
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};

    $sqlCount = "select COUNT(distinct rid) from eq_recordFields WHERE_CLAUSE ";
    $sql = "select distinct (f.rid) from eq_recordFields f inner join eq_records r on r.rid = f.rid WHERE_CLAUSE ORDER BY fId";
    
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE //;
        $sql        =~ s/WHERE_CLAUSE //;    }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE /where MATCH (fValue) AGAINST ('$kw') /;
        $sql        =~ s/WHERE_CLAUSE /where MATCH (fValue) AGAINST ('$kw') /;
    }
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
    if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth     = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        my $sth_rec = $dbh->prepare(<<_SQL_);
SELECT  r.rname, r.category, rf.*, d.*
FROM    eq_def as d 
    LEFT JOIN eq_recordFields as rf ON rf.fId = d.id && rf.rid = ?
    INNER JOIN eq_records as r using(rid)
WHERE   d.defType  = 'record'
ORDER   by d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid ) = $sth->fetchrow_array()){
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            my ($rname,$category);
            while (my $f = $sth_rec->fetchrow_hashref()) {
                $rname = $f->{'rname'};
                $category = $f->{'category'};
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},      
                    fname       => $f->{'name'},
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            push @recordList, {
                rid         =>  $rid,
                name        =>  $rname,
                category    =>  $category,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_item_byBarCode_ItemType{
    
    my ($sql, $sqlIn, $sqlCount);
    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr,$exactmatch) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sql = " select distinct r.rid, r.rname,r.category from eq_records as r left outer join eq_recordFields as rf on r.rid = rf.rid where r.rid IN " ;
    $sqlIn = "select distinct (i.rid) from eq_items as i inner join eq_records as r on r.rid = i.rid WHERE_CLAUSE group by i.rid ";
    $sqlCount = "select COUNT(distinct i.rid) from eq_items i inner join eq_records r using(rid) WHERE_CLAUSE ";
    my $thisField;
    if ($field == 3 ){
        $thisField = 'TypeId';
        }
    else{
        $thisField = 'barcode';
    }

    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where r.deleted = '0'/;
        $sqlIn      =~ s/WHERE_CLAUSE /where r.deleted = '0'/;    }
    else{
        if ($exactmatch && $exactmatch == 1) {
            $sqlCount   =~ s/WHERE_CLAUSE /where $thisField = '$kw' && r.deleted = '0'/;
            $sqlIn      =~ s/WHERE_CLAUSE /where $thisField = '$kw' && r.deleted = '0'/;
        }else{
            $sqlCount   =~ s/WHERE_CLAUSE /where MATCH($thisField) AGAINST ('$kw' in boolean mode) && r.deleted = '0'/;
            $sqlIn      =~ s/WHERE_CLAUSE /where MATCH($thisField) AGAINST ('$kw' in boolean mode) && r.deleted = '0'/;
        }
    }

    if (!$sortAttr ){
        $sql .= "(" . $sqlIn . " ) order by r.rname " ;}
    elsif ($sortAttr > 0){
        $sql .= "( " . $sqlIn . " ) && fid = $sortAttr order by fValue ";
    }
    else{
         $sql .= "( " . $sqlIn . " ) order by r.rname ";
    }

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

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

    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ?");
        my $sth_rec = $dbh->prepare(<<_SQL_);
SELECT  r.rname, r.category, rf.*, d.*
FROM    eq_def as d 
    LEFT JOIN eq_recordFields rf ON rf.fId = d.id && rf.rid = ?
    INNER JOIN eq_records r ON r.rid = rf.rid
WHERE d.defType = 'record'
ORDER BY d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid, $rname, $category) = $sth->fetchrow_array()) {
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid     => $f->{'fId'},
                    fval    => $f->{'fValue'},  
                    fname   => $f->{'name'},
                    display => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                };
                $fCount++;
            }
            push @recordList, {
                rid         => $rid,
                name        => $rname,
                category    =>  $category,
                offset      => $rCount,
                resultOrder => $rOrder,
                iCount      => $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        #$sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList)
}


sub srch_eq_item_bySubFields{

    my ($dbh, $field, $kw, $offset, $pSize, $sortOrder, $sortAttr,$exactmatch) =@_;
    my ($sql, $sqlIn, $sqlCount);
    $sql = " select distinct r.rid, r.rname from eq_records as r inner join eq_recordFields as rf using(rid) where r.rid IN " ;

    my @val = ();
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sqlCount = "select COUNT(distinct r.rid) as count from eq_itemFields as i inner join eq_records as r using(rid) WHERE_CLAUSE ";
    $sqlIn = "select distinct (r.rid) from eq_itemFields as i inner join eq_records r using(rid) WHERE_CLAUSE ";
    
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where r.deleted = '0' && sfId = $field /;
        $sqlIn      =~ s/WHERE_CLAUSE /where r.deleted = '0' && sfId = $field /;
    }
    else{
        if ($exactmatch && $exactmatch == 1) {
            $sqlCount   =~ s/WHERE_CLAUSE /where sfValue = \? && sfId = \? && r.deleted='0'/;
            $sqlIn      =~ s/WHERE_CLAUSE /where sfValue = \? && sfId = \? && r.deleted='0'/;
        }
        else{
            $sqlCount   =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST (\? in boolean mode ) && sfId = \? && r.deleted='0'/;
            $sqlIn      =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST (\? in boolean mode ) && sfId = \? && r.deleted='0'/;
        }
        @val = ($kw,$field);
    }

    if (!$sortAttr ){
        $sql .= "(" . $sqlIn . ") order by r.rname " ;}
    elsif ($sortAttr > 0){
        $sql .= "(" . $sqlIn . ") && fid = $sortAttr order by fValue ";
    }
    else{
         $sql .= "(" . $sqlIn . ") order by r.rname ";
    }

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


    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );

    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef,@val);

    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
    
    if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ?");
        my $sth_rec = $dbh->prepare(<<_SQL_);
SELECT  r.rname, rf.*, d.*
FROM    eq_def as d 
    LEFT JOIN eq_recordFields rf ON rf.fId = d.id && rf.rid = ?
    INNER JOIN eq_records r USING (rid)
WHERE d.defType = 'record'
ORDER BY d.fOrder
_SQL_
        $sth->execute(@val);
        while (my ($rid, $rname) = $sth->fetchrow_array()) {
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
            }
                push @fields, {
                    fid     => $f->{'fId'},
                    fval    => $f->{'fValue'},      
                    fname   => $f->{'name'},
                    display => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            for (my $i = $fCount; $i <= $recMaxOrder; $i++) {
                push @fields, {
                    fid     => $i,
                    fval    => '', };
            }
            push @recordList, {
                rid         => $rid,
                name        => $rname,
                offset      => $rCount,
                resultOrder => $rOrder,
                iCount      => $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_item_byAllSubFields{
    my ($dbh, $field, $kw, $offset, $pSize) =@_;
    my ($sql, $sqlCount);
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sqlCount = "select COUNT(distinct rid) from eq_itemFields WHERE_CLAUSE ";
    $sql = "select distinct (sf.rid) from eq_itemFields sf inner join eq_items i on i.rid = sf.rid && i.iid = sf.iid  WHERE_CLAUSE ORDER BY rid";
    
    $sqlCount   =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST ('$kw') /;
    $sql        =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST ('$kw') /;
        
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );

    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
    
    if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ?");
        my $sth_rec = $dbh->prepare(<<_SQL_);
SELECT  r.rname,r.category, rf.*, d.*
FROM    eq_def as d 
    LEFT JOIN eq_recordFields rf ON rf.fId = d.id && rf.rid = ?
    INNER JOIN eq_records r ON r.rid = rf.rid
WHERE d.defType = 'record'
ORDER BY d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid) = $sth->fetchrow_array()) {
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            my ($rname, $category);
            while (my $f = $sth_rec->fetchrow_hashref()) {
                $rname = $f->{'rname'};
                $category = $f->{'category'};
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
            }
                push @fields, {
                    fid     => $f->{'fId'},
                    fval    => $f->{'fValue'},
                    fname       => $f->{'name'},      
                    display => $f->{'display'},
                    };
                $fCount++;
            }
            push @recordList, {
                rid         => $rid,
                name        =>  $rname,
                category    =>  $category,
                offset      => $rCount,
                resultOrder => $rOrder,
                iCount      => $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_record_All {

    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr,$exactmatch ) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    my ($sql, $sqlCount);
    $sql = "select r.rid, r.rname, r.category from eq_records as r";
    if ($sortAttr && $sortAttr > 0){
        $sql .= " inner join eq_recordFields as rf on r.rid = rf.rid where r.deleted='0' && rf.fId = $sortAttr group by r.rid ";
        if ($sortOrder && $sortOrder == 2){
            $sql .= " order by rf.fValue desc ";        }
        else{
             $sql .= " order by rf.fValue asc ";        }
    }
    else{
        if ($sortOrder && $sortOrder == 2){
            $sql .= " order by r.rname desc ";        }
        else{
             $sql .= " order by r.rname asc ";        }
        
    }
    $sqlCount = "select COUNT(distinct rid) from eq_records  where deleted='0'" ;
    ( $offset >= 0 )|| ( $offset = 0 );
    ( $pSize > 0 )  || ( $pSize = 1 );
    
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
    if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth     = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        my $sth_rec = $dbh->prepare(<<_SQL_);
select  r.*, d.*
from    eq_def d left join eq_recordFields r on r.fId = d.id && r.rid = ?
where   d.defType  = 'record'
order by d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid,$rname,$category) = $sth->fetchrow_array()){
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},   
                    fname       => $f->{'name'},   
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            push @recordList, {
                rid         =>  $rid,
                name        =>  $rname,
                category    =>  $category,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_record_item_All {

    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr,$exactmatch) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};

    my $sql = <<_SQL_;
select r.rid, r.rname 
from eq_records r 
   left join eq_recordFields rf using(rid) 
   left join eq_items i using(rid)
   left  join eq_itemFields isf on (i.rid=isf.rid && i.iid = isf.iid)
    WHERE_CLAUSE
_SQL_
=item   
where  r.deleted='0'  &&
      match(rname) against ('$kw' in boolean mode) 
    || ( rf.fValue <> '' && match(rf.fValue) against ('$kw' in boolean mode) )
    || match(i.barcode) against ('$kw' in boolean mode) 
    || ( isf.sfValue <> '' && match (isf.sfValue) against ('$kw' in boolean mode))
group by r.rid order by r.rname
=cut


    my $sqlCount = <<_SQLCount_;
select count(distinct r.rid)  
from eq_records r 
   inner join eq_recordFields rf using(rid) 
   inner join eq_items i using(rid)
   inner  join eq_itemFields isf on (i.rid=isf.rid && i.iid = isf.iid)
   WHERE_CLAUSE
_SQLCount_

=item   
where  r.deleted='0'  &&
      match(rname) against ('$kw' in boolean mode) 
    || ( rf.fValue <> '' && match(rf.fValue) against ('$kw' in boolean mode) )
    || match(i.barcode) against ('$kw' in boolean mode) 
    || ( isf.sfValue <> '' && match (isf.sfValue) against ('$kw' in boolean mode))
=cut

    my $whereMatch = " where r.deleted='0' && 
    match(rname) against ('$kw' in boolean mode) 
    || ( rf.fValue <> '' && match(rf.fValue) against ('$kw' in boolean mode) )
    || match(i.barcode) against ('$kw' in boolean mode) 
    || ( isf.sfValue <> '' && match (isf.sfValue) against ('$kw' in boolean mode) )";

    my $whereExactmatch = " where r.deleted='0' && rname='$kw' || (rf.fValue <>' ' && rf.fValue='$kw') || (i.barcode='$kw') || (isf.sfValue<>'' && isf.sfValue='$kw')";


    if ($exactmatch && $exactmatch == 1) {
        $sql =~ s/WHERE_CLAUSE/ $whereExactmatch/;
        $sqlCount =~ s/WHERE_CLAUSE/ $whereExactmatch/;
    }
    else {
        $sql =~ s/WHERE_CLAUSE/$whereMatch/;
        $sqlCount =~ s/WHERE_CLAUSE/  $whereMatch/;
    }
    $sql .= " group by r.rid order by r.rname";
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    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;    }
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = $offset;
if ($resultSize > 0){
        my $query = "$sql LIMIT $offset , $availRange ";
        my $sth     = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        my $sth_rec = $dbh->prepare(<<_SQL_);
select  r.*, d.*
from    eq_def d left join eq_recordFields r on r.fId = d.id && r.rid = ?
where   d.defType  = 'record'
order by d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid, $rname) = $sth->fetchrow_array()){
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},    
                    fname       => $f->{'name'},
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            for (my $i = $fCount; $i <= $recMaxOrder; $i++) {
                push @fields, {
                    fid     => $i,
                    fval    => '', };
            }
            push @recordList, {
                rid         =>  $rid,
                name       =>  $rname,
                rname       =>  $rname,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}

sub srch_eq_record_byRIdList {

    my ($dbh, $field, $kw, $offset, $pSize, $sortOrder,$sortAttr, $ridStr) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    my ($sql, $sql1, $sql2, $sqlCount);
    $sql = "select r.rid, r.rname from eq_records r where r.deleted='0' && r.rid in ($ridStr) ";
    $sqlCount = "select COUNT(*) from eq_records r where r.deleted='0' && r.rid in ($ridStr) ";
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    my @recordList = ();
    my $rCount = 0;
    my $rOrder = 0;

    if ($resultSize > 0){
        my $query = $sql ;
        my $sth     = $dbh->prepare($query);
        my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_items where rid = ? ");
        my $sth_rec = $dbh->prepare(<<_SQL_);
select  r.*, d.*
from    eq_def d left join eq_recordFields r on r.fId = d.id && r.rid = ?
where   d.defType  = 'record' order by d.fOrder
_SQL_
        $sth->execute();
        while (my ($rid, $rname) = $sth->fetchrow_array()){
            $sth_iCount->execute($rid);
            my ($iCount) = $sth_iCount->fetchrow_array();
            $sth_rec->execute($rid);
            my @fields;
            my $fCount = 1;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                for (my $i = $fCount; $i < $f->{'fOrder'}; $i++, $fCount = $i) {
                    push @fields, {
                        fid     => $i,
                        fval    => '',  };
                }
                push @fields, {
                    fid         => $f->{'fId'},
                    fval        => $f->{'fValue'},      
                    display     => $f->{'display'},
                    url_link    => ($f->{'fieldType'} eq '2')?1:0,
                    };
                $fCount++;
            }
            for (my $i = $fCount; $i <= $recMaxOrder; $i++) {
                push @fields, {
                    fid     => $i,
                    fval    => '', };
            }
            push @recordList, {
                rid         =>  $rid,
                rname       =>  $rname,
                offset      =>  $rCount,
                resultOrder =>  $rOrder,
                iCount      =>  $iCount,
                fields      => \@fields,
            };
            $rCount++;
            $rOrder++;
        }
        $sth_rec->finish;
        $sth->finish;
    }
    return ($resultSize,\@recordList);
}