package Opals::Equipment;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    
    eq_defRecord_getList
    eq_defRecord_getLists 
    eq_defItem_getList
    eq_defItem_getLists
    eq_def_getList_brief
    
    eq_defRequired_getList
    eq_def_getFieldId
    eq_def_addUpdate
    eq_def_delete
    eq_def_find
    eq_def_colDisplayCount

    eq_fieldCodes
    mapData2FieldCode
    
    eq_record_add
    eq_record_update
    eq_record_update_rname
    eq_record_addUpdate
    eq_record_delete
    eq_record_doDelete
    eq_record_deleteByFid
    eq_record_find
    eq_record_findByRId
    eq_record_findByOffset
    eq_record_idGen
    eq_record_updateFieldName
    eq_record_findByRId4Merge

    eq_item_add
    eq_item_addUpdateBarCodeItemType
    eq_item_addUpdate
    
    eq_item_delete
    eq_item_deleteBarcode

    eq_item_deleteSubFields
    eq_item_deleteByFid
    eq_item_find    
    eq_item_findByRId
    eq_item_findByBarcode
    eq_item_getBarcodeList
    eq_item_getMaxIddByRId
    eq_authCtrlFields_getList
    
    eq_containerFields_getList
    eq_containerItems_getList

    eq_isBarcodeExist 
    eq_itemType_getList
    eq_dataType_getList

    srch_equipment

    srch_eq_record
    srch_eq_item
    srch_eq_record_item
    srch_eq_record_byName
    srch_eq_record_byRIdList

    eq_definition_getList

    eq_category_getList
    eq_categoryMapList

    eq_getSchoolList
    eq_building_getList
);

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;
use JSON;
use Opals::Constant;
use Text::CSV_XS;
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);
use Opals::Eq_Circulation qw(
    circ_updateItemStatus
);


#******************************************************************
#               section : DEFINITION 
#******************************************************************

sub eq_defItem_getList{
    my ($dbh) = @_;
    my $itemList = eq_def_getList($dbh, 'item');
    return $itemList;
}


sub eq_defRecord_getList{
    my ($dbh) = @_;
    my $recordList = eq_def_getList($dbh, 'record');
    return $recordList;
}

sub eq_defRecord_getLists {
    my ($dbh,$orderBy) = @_;
    my $defRecReqList = eq_defRequired_getList($dbh,'record');
    my $defRecList = eq_def_getList($dbh,'record',$orderBy);
    my @defRecordLists = (@$defRecReqList, @$defRecList);
    my $i = 0;
    foreach my $d (@defRecordLists){
        $d->{'displayOrder'} = $i++;
    }
    return \@defRecordLists;
}

sub eq_defItem_getLists {
    my ($dbh,$orderBy) = @_;
    my $defItemReqList = eq_defRequired_getList($dbh,'item');
    my $defItemList = eq_def_getList($dbh,'item', $orderBy);
    my @defItemLists = (@$defItemReqList, @$defItemList);
    my $i = 0;
    foreach my $d (@defItemLists){
        $d->{'displayOrder'} = $i++;
    }
    return \@defItemLists;
}

sub eq_def_getList_brief{

    my ($dbh, $defType) = @_;
    my $ret;
    my $sql = "select id, name from eq_def ";
    if (defined $defType && $defType =~ m/record|item/){
        $sql .= " where defType = '$defType' ";
    }
    $sql .= " order by id";

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my ($id,$name) = $sth->fetchrow_array()){
        $ret->{$id} = $name;
    }
    $sth->finish;
    return $ret;

}

###################################################################
#
# function eq_def_getList
#
#   $params: defType ie: record or item
#   return record or item definition fields
#
###################################################################
sub eq_defRequired_getList{
    
    my ($dbh,$defType) = @_;
    return if ($defType eq '');
    my @retList;
    my $sql = "select * from eq_defRequired where defType = ? order by fOrder";
    my $sth = $dbh->prepare($sql);
    $sth->execute($defType);
    while ( my $rec = $sth->fetchrow_hashref) {
        $rec->{'count'} = 1;
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub eq_def_getList{
    
    my ($dbh,$defType,$orderBy) = @_;
    return if ($defType eq '');
    my $sql;
    if ($defType eq 'record'){    
    $sql = <<_SQL_;
        select  rd.id, count(r.fId) as count
        from    eq_def rd 
        left join eq_recordFields r on rd.id = r.fId
        where rd.defType = 'record'
        group by rd.id
_SQL_
    }
    else{
        $sql = <<_SQL_;
        select  rd.id, count(i.sfId) as count
        from    eq_def rd 
        left join eq_itemFields i on rd.id = i.sfId
        where rd.defType = 'item'
        group by rd.id
_SQL_
    }
    my $idCount = $dbh->selectall_hashref($sql, 'id');
    $sql = "select d.*, t.dataType,t.maxVal  from eq_def d left outer join eq_fieldDataType t on d.fieldType=t.id where defType = ? ";
    if ($orderBy && $orderBy ne ""){
        $sql .= " order by $orderBy ";
    }
    else{
        $sql .= " order by fOrder";
    }
    my @retList;
    my $sth = $dbh->prepare($sql);
    $sth->execute($defType) ;
    while ( my $rec = $sth->fetchrow_hashref) {
        $rec->{'count'} = $idCount->{$rec->{'id'}}->{'count'};
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

###################################################################
# function eq_def_getMaxId
#   return the max. id of table of eq_def
###################################################################
sub eq_def_getMaxId{

    my ($dbh) = @_;
    my $sql = "select max(id) from eq_def";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my ($maxId) = $sth->fetchrow_array;
    $sth->finish;
    return $maxId;
}

###################################################################
# function eq_def_addUpdate
#   if (fId = 0 => newly added field )
#   $params: a hash table contains (f=>field)
#           - fId
#           - defType
#           - fOrder
#           - name
#           - description
#           - reqF (required Field)
#           - reqD (required Data)
#   
#   return id 
###################################################################
sub eq_def_addUpdate {
    
    my ($dbh,$params) = @_;
    my $id = 0;
    return -1 if ($params->{'fId'} eq '');
    if ($params->{'fId'} eq '0' ){
        $id = eq_def_add($dbh, $params);
    }
    else {
        $id = eq_def_update($dbh, $params);
    }
    return $id;
}

###################################################################
# function eq_def_update
#   
#   $params: a hash table contains (f=>field)
#           - fId
#           - defType
#           - fOrder
#           - name
#           - description
#           - reqF (required Field)
#           - reqD (required Data)
#   
#   return id 
###################################################################
sub eq_def_update{
    
    my ($dbh, $params) = @_;
    return if($params->{'fId'} eq '');
    my @fields = ('defType','fOrder','name','description','reqF','reqD','display', 'fieldType');
    my $sql = "update eq_def ";
    my @fVals;
    my $i;
    my $sql_setVal="";
    for ( $i = 0; $i < scalar( @fields); $i++){
        if ($params->{$fields[$i]} ne ''){
            $sql_setVal .= ", " if($sql_setVal ne "");
            $sql_setVal .= " $fields[$i] =? ";
            push @fVals, $params->{$fields[$i]};
        }
    }
    $sql .= "set $sql_setVal where id =?" ;
    push @fVals,$params->{'fId'};
    my $sth = $dbh->prepare($sql);
    $sth->execute(@fVals);
    my $id = $params->{'fId'};
    $sth->finish;
    return $id;
}

###################################################################
# function eq_def_add
#   
#   $params: a hash table contains (f=>field)
#           - fId
#           - defType
#           - fOrder
#           - name
#           - description
#           - reqF (required Field)
#           - reqD (required Data)
#   
#   return id 
###################################################################
sub eq_def_add {
    
    my ($dbh,$params) = @_;
    return -1 if ($params->{'name'} eq '');
    my @fields = ('defType','fOrder','name','description','reqF','reqD','display','fieldType');
    my $sql = "insert into eq_def set ";
    my @fVals;
    my $i;
    my $sql_setVal="";
    for ( $i = 0; $i < scalar( @fields); $i++){
        if ($params->{$fields[$i]} ne ''){
            $sql_setVal .= ", " if($sql_setVal ne "");
            $sql_setVal .= " $fields[$i] =? ";
            push @fVals, $params->{$fields[$i]};
        }
    }
    $sql .= $sql_setVal;
    my $sth = $dbh->prepare($sql);
    $sth->execute(@fVals);
    my $id = $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id;
}

###################################################################
# function eq_def_delete
#   $params : 
#           - id
#           - defType  
#   
###################################################################
sub eq_def_delete {
    my ($dbh, $id, $defType) = @_;
    return if ($id eq '');
    my $sql = "delete from eq_def where id = ? and defType = ? and reqF != 1 limit 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute($id, $defType);
    $sth->finish;
}

###################################################################
# function eq_def_findById
#   $params : 
#           - id
#   return  the definition fields of this 'id'       
#   
###################################################################
sub eq_def_findById{

    my($dbh, $id) = @_;
    return undef if ($id eq '');
    my $recDefList = eq_def_find($dbh,{id=>$id});
    if (scalar(@$recDefList) == 1){
        return @$recDefList[0];
    }
    return undef;
}

###################################################################
# function eq_def_find
#   $params : 
#           - id
#           - ....
#   return  List of definition fields based on params
#   
###################################################################
sub eq_def_find{
    
    my($dbh,$params)= @_;
    my @recDefList;
    my $sql = "select * from eq_def ";    
    my $sql_cond="";
    my @condVals=();
    if ($params->{'id'} ne ''){
        $sql_cond .= " or " if ($sql_cond ne '');
        $sql_cond .= " id=?";
        push @condVals, $params->{'id'};
    }
    return @recDefList if ($sql_cond eq '');
    $sql .= " where $sql_cond";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@condVals);
    while ( my $rec = $sth->fetchrow_hashref){
        push @recDefList, $rec;
    }
    $sth->finish;
    return \@recDefList;
}

###################################################################
# function eq_def_getFieldId
#   $params : a hash table contains
#               - defType (item, record)
#               - fname 
#   return  id of of definition fields based on field name
#   
###################################################################

sub eq_def_getFieldId{
    
    my($dbh, $params) = @_;
    return if ($params->{'defType'} eq '' || $params->{'fname'} eq '');
    my $sth = $dbh->prepare("select id from eq_def where defType = ? and name like ?");
    $sth->execute($params->{'defType'}, $params->{'fname'});
    my ($rec) = $sth->fetchrow_hashref;
    $sth->finish;
    return $rec->{'id'};
}

###################################################################
# function eq_def_colDisplayCount
#   $params : a hash table contains
#           - defType (item,record)
#           - display (1=only set display fields , otherwise all fields)
#   return  number cols to be displayed
#   
###################################################################

sub eq_def_colDisplayCount{
    
    my($dbh,$params) = @_;
    return if ($params->{'defType'} eq '') ;
    my $sql = 'select count(*) from eq_def where defType = ? ';
    if ($params->{'display'} == 1){
        $sql .= ' and display = 1';
    }
    if ($params->{'showOnBrief'} == 1){
        $sql .= ' and showOnRecBrief=1';
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute($params->{'defType'});
    my ($count) = $sth->fetchrow_array;
    $sth->finish;
    return $count;

}

#Mon, Feb 09, 2015 @ 14:24:02 EST
#
#Equipment Definition getList

sub eq_definition_getList {

    my ($dbh,$sf) = @_;
    my $sql = "select id,defType,name from eq_def order by id";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my @retList = ();
    @retList = (
        {id=>"eq_name",defType=>"record",name=>"Equipment Name",selected=>0 },
        {id=>"eq_barcode",defType=>"record",name=>"Barcode",selected=>0},
        {id=>"itemType",defType=>"item",name=>"Item Type",selected=>0},
        {id=>"eq_cart",defType=>"record",name=>"Cart",selected=>0},
    );
    while ( my $rec = $sth->fetchrow_hashref) {
        $rec->{'select'} = 0;
        push @retList, $rec;
    }
    $sth->finish;
    if (defined $sf && $sf ne "") {
        foreach my $r(@retList) {
            if ($r->{'id'} eq $sf){
                $r->{'selected'} = 1;
            }
        }
    }

    return \@retList;
}


#******************************************************************
#                   section : RECORD 
#******************************************************************

##################################################################
# function record_maxOrder
#   
#   return: 
#           - maxOrder of both record and item
#   
###################################################################
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_fieldCodes {
    
    my ($dbh) = @_;
    my @eqDefList = (
        {id => 'eqName', name=>'Equipment Name'},
        {id => 'category', name=>'Catogory'},
        {id => 'bc', name=>'Barcode'},
        {id => 'itemType', name=>'ItemType'},
        
        {id => 'bldCode',     name=>'Building Code'},
        {id => 'bldName',     name=>'Building Name'},
        {id => 'bldAddress',  name=>'Address'},
        {id => 'bldCity',     name=>'City'},
        {id => 'bldState',    name=>'State'},
        {id => 'bldCountry',  name=>'Country'}
        
        );
        

=item
#Fri, Dec 12, 2014 @ 10:44:28 EST
#For category    
    my $sth = $dbh->prepare("select concat('cat',id) as id , concat('Category',' ',name) as name from eq_category order by id");
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        push @eqDefList, $rec;
    }
=cut
    my $sth = $dbh->prepare("select id,name from eq_def order by id");
    $sth->execute();

    while (my $rec = $sth->fetchrow_hashref){
        push @eqDefList, $rec;
    }

    $sth->finish;

    return \@eqDefList;
}
#new mapData...
sub mapData2FieldCode{

    my ($dbh,$rec, $hMap) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1 });
=item    
    my @tmp = split (/,/, $rec) ;
    my $data = "";
    foreach my $t (@tmp){
        $t =~ s/^["\s]*|["\s]*$//g;
        $data .= $t . ",";
    }
    #$csv->parse($data);
=cut    
    $csv->parse($rec);
    my @field = $csv->fields();
    
    foreach my $f(@field) {
        $f =~ s/^\s+//;
        $f =~ s/\s+$//;
        $f =~ s/[^[:print:]]//g;
    }
    my $record;
    my $map = from_json($hMap);
    
    foreach my $k (sort keys %$map){
        if (defined $map->{$k}->{'0'} && $map->{$k}->{'0'}>=0){
            if ($map->{$k}->{'1'} && $map->{$k}->{'1'} > 0 ){
                $record->{$k} = $field[$map->{$k}->{'0'}] . "-" . $field[$map->{$k}->{'1'}]; 
            }
            else{
                $record->{$k} = $field[$map->{$k}->{'0'}];
            }
        }
        else{
            $record->{$k} ="";
        }
    }
    return $record;
}

=item
sub mapData2FieldCode{

    my ($dbh,$rec, $hMap) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1 });
    $csv->parse($rec);
    my @field = $csv->fields();
    foreach my $f(@field) {
        $f =~ s/^\s+//;
        $f =~ s/\s+$//;
        $f =~ s/[^[:print:]]//g;
    }
    my $record;
    my $fCode =  eq_fieldCodes($dbh);
    for ( my $i =0; $i < scalar(@{$hMap}); $i++){
        if ($hMap->[$i] >= 0 ){
            $record->{$fCode->[$i]->{'id'}} = $field[$hMap->[$i]];
        }
        else{
            $record->{$fCode->[$i]->{'id'}} = "";
        }
    }

    
#    while ( my ($k,$v) = each %{$record} ) {
#        if ($k =~ m/^(cat)(\d+)/){
#            if ($v && $v ne ""){
#                $record->{'category'} .= $2 . ",";
#            }
#        }
#    }
    
    return $record;
}
=cut

###################################################################
# function eq_record_addUpdate
#   $params: a hash table contains (f=>field)
#
###################################################################
sub eq_record_addUpdate{
    
    my ($dbh, $params) = @_;
    return -1 if ($params->{'rid'} eq '');
    #my $sql = "select id from eq_record where rid=? and fId=?";
    my $sql = "select id from eq_recordFields where rid=? and fId=?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($params->{'rid'}, $params->{'fId'});
    my ($count) = $sth->fetchrow_array();
    if ($count>0){
        eq_record_update($dbh,$params);
    }
    else{
        eq_record_add($dbh,$params);
    }
}

###################################################################
# function eq_record_add
#   $params: a hash table contains (f=>field)
#
###################################################################
sub eq_record_add {

    my ($dbh, $params) = @_;
    return -1 if ($params->{'rid'} eq '');
    my ($sql, $sth, $id);
    if ($params->{'reqField'}){
         $sql = "insert into eq_records set rid=?, rname=?, category=?,container=?"; 
         $sth = $dbh->prepare($sql);
         $sth->execute($params->{'rid'}, $params->{'fValue'}, $params->{'category'}, $params->{'container'});
         $id = $dbh->{'mysql_insertid'};
    }
    else{
        $sql = "insert into eq_recordFields set rid=?, fId=?, fValue=?";    
        $sth = $dbh->prepare($sql);
        $sth->execute($params->{'rid'}, $params->{'fId'}, $params->{'fValue'});
        $id = $dbh->{'mysql_insertid'};
    }
    $sth->finish;
    return $id;
}

###################################################################
# function eq_record_update
#   $params: a hash table contains (f=>field)
#
###################################################################
sub eq_record_update_rname {
    my ($dbh, $rid, $rname, $cat, $container) = @_;
    return -1 if ($rid eq '' && $rname eq '');
    my $sql = "update eq_records set rname=?, indexed='0' ";
    if ($cat && $cat ne "") {
        $sql .= ", category = '$cat' " ;
    }
    if (($container eq "0") || $container && $container ne "" ){
        $sql .= ", container = '$container'";
    }
    $sql .= " where rid = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rname,$rid);
    $sth->finish;
   }

sub eq_record_update {
    my ($dbh, $params) = @_;
    return -1 if ($params->{'rid'} eq '' && $params->{'fId'} eq '');
    my $sql = "update eq_recordFields set fValue=? where rid=? and fId=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($params->{'fValue'},$params->{'rid'},$params->{'fId'});
    $sth->finish;
   }

###################################################################
# function eq_record_delete
#   
#   Delete record and all of its items.
#
#   $params: record id 
#
###################################################################
#Fri, Jan 30, 2015 @ 15:47:57 EST 
#update deleted field to 1


sub eq_record_doDelete {

    my ($dbh, $rid) = @_;

    return - 1 if ($rid eq '');
=item    
    my $sth = $dbh->prepare("update eq_records set deleted =\"1\",modifiedDate=now() where rid = ? ");
    $sth->execute($rid);
    $sth = $dbh->prepare("update eq_items set deleted =\"1\",modifiedDate=now() where rid =?");
    $sth->execute($rid);
    $sth = $dbh->prepare("insert into eq_recordFieldsArchive select * from eq_recordFields where rid =?");
    $sth->execute($rid);
    $sth = $dbh->prepare("insert into eq_itemFieldsArchive select * from eq_itemFields where rid =?");
    $sth->execute($rid);
    $sth = $dbh->prepare("delete from eq_recordFields where rid =?");
    $sth->execute($rid);
    $sth = $dbh->prepare("delete from eq_itemFields where rid =?");
    $sth->execute($rid);
    $sth->finish;
=cut
    $dbh->do("update eq_records set deleted='1',indexed='0', modifiedDate=now() where rid=$rid limit 1");
#set all barcode or just one ???
    $dbh->do("update eq_items set deleted='1',barcode = concat('___', barcode,'_',$rid),modifiedDate=now() where rid=$rid");
    $dbh->do("insert into eq_recordFieldsArchive (rid,fId, fValue) select rid,fId,fValue from eq_recordFields where rid=$rid");
    $dbh->do("insert into eq_itemFieldsArchive (rid,iid,sfId, sfValue) select rid,iid,sfId,sfValue from eq_itemFields where rid=$rid");
    $dbh->do("delete from eq_recordFields where rid=$rid");
    $dbh->do("delete from eq_itemFields where rid=$rid");
}



sub eq_record_delete {
    
    my ($dbh, $rid) = @_;
    return - 1 if ($rid eq '');
    my $sth = $dbh->prepare("delete from eq_records where rid = ? limit 1");
    $sth->execute($rid);
    my $params= {rid=>$rid, iid=>0};
    eq_item_delete($dbh,$params);
    $sth->finish;
}

###################################################################
# function eq_record_delete
#   $params:
#           - field id
#
###################################################################
sub eq_record_deleteByFid {
    
    my ($dbh, $fId) = @_;
    return -1 if ($fId eq '');
    my $sql = "delete from eq_recordFields where fId = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($fId);
    $sth->finish;
}

###################################################################
# function eq_record_find
# return : list of record
###################################################################
sub eq_record_find {
    my ($dbh, $params) = @_;
    my $offset = $params->{'offset'};
    my $pSize = $params->{'pSize'}; 
    my $kw = $params->{'kw'};
    my $field = $params->{'field'};

    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    my @recordList;
    my $rCount = 0;
    my $sql;  
    my @val = ();
    $sql = ("select distinct (rid) from eq_record WHERE_CLAUSE limit $offset, $pSize");
    if ($kw eq '*'){
        $sql =~ s/WHERE_CLAUSE //;
    }
    elsif ($field == 0) {
        $sql =~ s/WHERE_CLAUSE /where MATCH (fValue) AGAINST (\?) /;
        @val = ($kw);
    }
    else {
        $sql =~ s/WHERE_CLAUSE /where MATCH (fValue) AGAINST (\?) and fId = \? /;
        @val = ($kw, $field);
    }
    my $sth =  $dbh->prepare($sql);
    my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_item where rid = ? ");
    my $sth_rec = $dbh->prepare(<<_SQL_);
select  r.*, d.*
from    eq_def d left join eq_record r on r.fId = d.id && r.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;
        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'},
                };
            $fCount++;
        }
        for (my $i = $fCount; $i <= $recMaxOrder; $i++) {
            push @fields, {
                fid     => $i,
                fval    => '', };
        }
        push @recordList, {
            rid => $rid,
            offset=>$rCount,
            iCount=>$iCount,
            fields => \@fields,
        };
        $rCount++;
    }
    $sth_rec->finish;
    $sth->finish;
    my ($resultSize) = $rCount;
    return ($resultSize, \@recordList); 
}
#------
sub eq_item_find{
    my ($dbh, $params) =@_;
    my $offset = $params->{'offset'};
    my $pSize = $params->{'pSize'}; 
    my $kw = $params->{'kw'};
    my $field = $params->{'field'};

    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    my @recordList;
    my $rCount = 0;
    my $sql;
    my @val = ();

    $sql = ("select distinct(rid) from eq_item WHERE_CLAUSE limit $offset, $pSize");

    if ($field == 0) {
        $sql =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST (\?) /;
        @val = ($kw);
    }
    else{
        $sql =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST (\?) and sfId = \? /;
        @val = ($kw, $field);
    }
    my $sth = $dbh->prepare($sql);
    my $sth_iCount = $dbh->prepare("select count(distinct (iid)) as iCount from eq_item where rid = ?");
    my $sth_rec = $dbh->prepare(<<_SQL_);
select  r.*, d.*
from    eq_def d left join eq_record r on r.fId = d.id && r.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;
        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'},
                };
            $fCount++;
        }
        for (my $i = $fCount; $i <= $recMaxOrder; $i++) {
            push @fields, {
                fid     => $i,
                fval    => '', };
        }
        push @recordList, {
            rid => $rid,
            offset=>$rCount,
            iCount=>$iCount,
            fields => \@fields,
        };
        $rCount++;
    }
    $sth_rec->finish;
    $sth->finish;
    my ($resultSize) = $rCount;
    return ($resultSize, \@recordList); 
    
}

sub eq_record_findByRId4Merge {

    my ($dbh,$rId)  = @_;
    my $sql = "SELECT r.rname as rname, r.category, r.rid, rf.fId,d.name ,d.description,rf.fValue as fValue 
        FROM    eq_records as r left JOIN eq_recordFields as rf ON r.rid = rf.rid
                right JOIN eq_def as d ON rf.fId = d.id && r.rid = ?  
        WHERE d.defType = 'record'  && rf.fValue is not null order by d.fOrder";
    my $sth = $dbh->prepare($sql);
    $sth->execute($rId);

    my @recordInfo = ();
    while( my $rec = $sth->fetchrow_hashref){
        push @recordInfo, {
            rname   => $rec->{'rname'},
            category=> $rec->{'category'}? $rec->{'category'}: "",
            fName    => $rec->{'name'},
            rid     => $rec->{'rid'},
            fValue  => $rec->{'fValue'},
            fId     => ($rec->{'fId'})?$rec->{'fId'}:$rec->{'id'},
        };
    }
    
    $sth = $dbh->prepare("select iid, barcode,typeId, createdDate from eq_items where rid = ? && barcode not regexp '^\_\_\_'");
    $sql = " SELECT  d.*, i.id as i_iid, i.rid, i.sfId, i.sfValue 
                FROM    eq_def as d LEFT JOIN eq_itemFields as i 
                        ON i.sfId = d.id && i.rid = ? && i.iid = ? 
                WHERE   d.defType = 'item'  
                ORDER   by d.fOrder";

    my $sth_item = $dbh->prepare($sql);
    $sth->execute($rId);
    my @itemList;
    while (my ($iid, $barcode, $typeId,$copyNo) = $sth->fetchrow_array()){
       $sth_item->execute($rId, $iid);
       my @fields;
       my $fCount = 0;
       while(my $f = $sth_item->fetchrow_hashref()){
            push @fields, {
                sfId        => ($f->{'sfId'})? $f->{sfId}:$f->{'id'},
                sfVal       => $f->{'sfValue'}, 
                sfName      => $f->{'name'},
                sfReqD      => $f->{'reqD'},
                fieldType   => $f->{'fieldType'},
            };
       }
       @fields = sort{$$a{'sfId'} <=> $$b{'sfId'}} @fields ;
       push @itemList, {
            iid     => $iid,
            bc      => $barcode,
            typeId  => $typeId,
            fields  => \@fields,
       };
    }
    $sth->finish;
    $sth_item->finish;

    my $categories = eq_category_getList($dbh);
    my $category = {};
    foreach my $c (@{$categories}){
        $category->{$c->{'id'}} = $c->{'name'};
    }

    foreach my $rec (@recordInfo) {
        my @cat = split(/,/, $rec->{'category'});
        $rec->{'category'} = "";
        if (!@cat) {
            $rec->{'category'} = '';
        }
        else{
            foreach my $c (@cat){
                $rec->{'category'} .= $category->{$c} . ",";
            }
            $rec->{'category'} =~ s/,$//;
        }
    }
    return (\@recordInfo,\@itemList);

}

#------------------------
###################################################################
# function eq_record_findByRId
#   params:
#           - record id
#   return : record
###################################################################
sub eq_record_findByRId{

    my ($dbh,$params) = @_;
    return if ($params->{'recordId'} eq '');
    #my $categoryMapList = eq_categoryMapList($dbh);
    my @recordInfo=();
    my $sql = "SELECT r.*, r.rid, d.*, rf.id as rf_rid, rf.fId, rf.fValue as fValue
                FROM eq_records as r 
                    left JOIN eq_recordFields as rf ON r.rid = rf.rid 
                    right JOIN eq_def as d ON rf.fId = d.id && r.rid = ?
                WHERE d.defType = 'record'  && rf.fValue is not null order by d.fOrder";
    my $sth = $dbh->prepare($sql);
    $sth->execute($params->{'recordId'});
    my $rid=0;
    while( my $rec = $sth->fetchrow_hashref){
        $rid= $rec->{'rid'};
        push @recordInfo, {
            name        => $rec->{'name'},
            category    => $rec->{'category'}? $rec->{'category'}: "",
            #categoryStr => $rec->{'category'}? $categoryMapList->{$rec->{'category'}}: "",
            categoryStr => $rec->{'category'}? eq_categoryNameById($dbh,$rec->{'category'}): "",
            container   => $rec->{'container'},
            rid         => $rec->{'rid'},
            rname       => $rec->{'rname'},
            fValue      => $rec->{'fValue'},
            fId         => ($rec->{'fId'})?$rec->{'fId'}:$rec->{'id'},
            display     => $rec->{'display'},
            showOnRecBrief => $rec->{'showOnRecBrief'},
            reqD        => $rec->{'reqD'},
            url_link    => ($rec->{'fieldType'} eq '2')?1:0,
            };
    }
    $sth->finish;
    return ($rid,\@recordInfo);
}

###################################################################
# function eq_record_findByOffset
#   params:
#           - record id
#   return : record
###################################################################
sub eq_record_findByOffset{
    my ($dbh,$params) = @_;
    my $offset = $params->{'offset'};

    if (!defined $offset || $offset eq '' || $offset < 0) {
        return;
    }
    my $sql = "select r.rid ";
    $sql .= " from eq_record r inner join eq_def d on r.fId = d.id group by r.rid limit $offset, 1";
    my ($rid) = $dbh->selectrow_array($sql);
    return eq_record_findByRId($dbh, {recordId => $rid});
}


###################################################################
# function eq_record_idGen
# return : return record id based on checksum (sha1_base64)
###################################################################
sub eq_record_idGen {
    
    my ($dbh) = @_;
    #my ($sec,$min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time);
    #my $checkSum = sha1_base64($sec,$min, $hour, $mday, $mon, $year, $wday, $yday, $isdst);
    my $checkSum = `date +\%Y\%m\%d\%H\%M\%S\%N`;
    chomp $checkSum;

    my $sql = "insert into opl_idGen set type = 'equipment', checkSum=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($checkSum);
    $sql = "select id from opl_idGen where type = ? and checkSum=? ";
    $sth = $dbh->prepare($sql);
    $sth->execute('equipment',$checkSum);
    my ($id) = $sth->fetchrow_array();
    $sth->finish;
    return $id;
}

#*****************************************************************#
#                      section : ITEM 
#*****************************************************************#

##################################################################
# function eq_item_add
#   
#   $params: a hash table contains (sf=>subfield)
#           - iid (item id)
#           - rid
#           - sfId
#           - sfValue
#   
#   return id 
###################################################################
sub eq_item_add{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'iid'} eq '');
    my ($sql, $sth, $id);
    if ($params->{'reqField'}){
        if (eq_isBarcodeExist($dbh,$params->{'barcode'})){
            $params->{'barcode'} = eq_maxBarcodeDup($dbh,$params->{'barcode'});
        }
        $sql = "insert into eq_items set iid=?, rid=?, barcode=?, typeId=?, available=1, createdDate=now()";
        $sth = $dbh->prepare($sql);
        $sth->execute($params->{'iid'}, $params->{'rid'}, $params->{'barcode'}, $params->{'typeId'});
        $id = $dbh->{'mysql_insertid'};
    }
    else{
        $sql = "insert into eq_itemFields set iid=?, rid=?, sfId=?, sfValue=?";
        $sth = $dbh->prepare($sql);
        if (!$params->{'sfValue'}){
            $params->{'sfValue'} = "";
        }
        $sth->execute($params->{'iid'}, $params->{'rid'}, $params->{'sfId'}, $params->{'sfValue'});
        $id = $dbh->{'mysql_insertid'};
    }
    $sth->finish;
    return $id;
}

sub eq_item_addUpdateBarCodeItemType{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'iid'} eq '');
    my $sth = $dbh->prepare("select count(*) from eq_items where rid = ? && iid = ? ");
    $sth->execute($params->{'rid'},$params->{'iid'});
    my ($iExist) = $sth->fetchrow_array;

    $sth = $dbh->prepare("select barcode from eq_items where barcode=? && rid <> ? ");
    $sth->execute($params->{'barcode'},$params->{'rid'});
    my ($bcExist) = $sth->fetchrow_array;
    $sth->finish;
    my $maxBarcodeDup = 0;
    if ($bcExist) {
         $maxBarcodeDup = eq_maxBarcodeDup($dbh,$params->{'barcode'});
    }
    if ($iExist){
        if ($bcExist){
            $params->{'barcode'} = $maxBarcodeDup;
        }
        my $sql_update = "update eq_items set barcode=?,typeId=?,copyNo=?,available=1,modifiedDate=now() where rid= ? && iid = ? ";
        my $sth_update = $dbh->prepare($sql_update);
        $sth_update->execute($params->{'barcode'},$params->{'typeId'},$params->{'copyNo'},$params->{'rid'},$params->{'iid'});
        $sth_update->finish;
    }
    else{
        $params->{'reqField'} = 1;
        my $ret = eq_item_add($dbh,$params);
    }
}

#################################################################
# function eq_item_addUpdate
#   -add new item to database or update item if rid, iid and sfId exist
#
#   $params: a hash table contains (sf=>subfield)
#           - iid (item id)
#           - rid
#           - sfId
#           - sfValue
#   
#   return id 
###################################################################
sub eq_item_addUpdate{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'iid'} eq '');
    #my $sql = "insert into eq_item set iid=?, rid=?, sfId=?, sfValue=? on duplicate key update sfValue =?"  ;
    my $sql = "insert into eq_itemFields set iid=?, rid=?, sfId=?, sfValue=? on duplicate key update sfValue =?" ;
    my $sth = $dbh->prepare($sql);
    if (!$params->{'sfValue'}) {
        $params->{'sfValue'} = "";
    }
    $sth->execute($params->{'iid'}, $params->{'rid'}, $params->{'sfId'}, $params->{'sfValue'},  $params->{'sfValue'}|| "" );
    $sth->finish;
}

sub eq_item_deleteBarcode {

    my ($dbh,$params) = @_;
    return -1 if ($params->{'iid'} eq '' || $params->{'rid'} eq '' );
    my $sql = "select barcode from eq_items where rid = ? && iid=? && barcode not regexp '^_' ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($params->{'rid'}, $params->{'iid'} );
    my ($barcode) = $sth->fetchrow_array();
    my $newBarcode = 0;
    if ($barcode){
        $newBarcode = eq_maxBarcodeDeleted($dbh,$barcode);
        #my $sql_update = "update eq_items set barcode = ?, available = 0, modDate = now() where rid = ? && iid = ? && barcode = ?";
        my $sql_update = "update eq_items set barcode=?,available=0,modifiedDate=now(),deleted='1' where rid=? && iid=? && barcode=?";
        my $sth_update = $dbh->prepare($sql_update);
        $sth_update->execute($newBarcode, $params->{'rid'}, $params->{'iid'}, $barcode);
        circ_updateItemStatus($dbh,$barcode,ITEM_DELETED);
        $dbh->do("update eq_itemStatus set barcode='$newBarcode' where barcode='$barcode'");
        $dbh->do("update eq_loan set barcode='$newBarcode' where barcode='$barcode'");
        $sth_update->finish;
    }
    $sth->finish;
}

sub eq_maxBarcodeDeleted {
    my ($dbh,$barcode) = @_;
    my $sql_bc = $barcode;
    $sql_bc =~ s/([*+?])/\\\\$1/g;
    $sql_bc .= '_';
    my $maxBarcode_sql = <<_STH_;
select  max(barcode)
from    eq_items
where   barcode regexp '^(___)?$sql_bc'
_STH_
    my ($maxBarcode) = $dbh->selectrow_array($maxBarcode_sql);
    if ($maxBarcode && $maxBarcode =~ s/([\d]{3})$//) {
        my $dupCount = $1;
        $dupCount =~ s/^0{1,2}//;
        $dupCount++;
        $dupCount = sprintf("%0.3d", $dupCount);
        $maxBarcode .= $dupCount;
    }
    else {
        $maxBarcode = '___'.$barcode.'_000';
    }
    return $maxBarcode;
}

sub eq_maxBarcodeDup {

    my ($dbh,$barcode) = @_;
    my $sql_bc = $barcode;
    $sql_bc =~ s/([*+?])/\\\\$1/g;
    $sql_bc .= '_';

    my $maxBarcode_sql = <<_STH_;
select  max(barcode)
from    eq_items
where   barcode regexp '^DUP_$sql_bc'
_STH_
    my ($maxBarcodeDup) = $dbh->selectrow_array($maxBarcode_sql);

    if ($maxBarcodeDup && $maxBarcodeDup =~ s/([\d]{3})$//) {
        my $dupCount = $1;
        $dupCount =~ s/^0{1,2}//;
        $dupCount++;
        $dupCount = sprintf("%0.3d", $dupCount);
        $maxBarcodeDup .= $dupCount;
    }
    else {
        $maxBarcodeDup = 'DUP_'.$barcode.'_000';
    }
    return $maxBarcodeDup;
}

sub eq_item_delete{

    my ($dbh,$params) = @_;
    return -1 if ($params->{'rid'} eq '' );
    my $sql = "delete from eq_items where rid=? ";
    #my $sql = 'update eq_items set barcode = CONCAT('___' , barcode ) where rid = ? '
    my $sth = $dbh->prepare($sql);
    $sth->execute($params->{'rid'});
    $sth->finish;
}

sub eq_item_deleteSubFields{
    my ($dbh,$params) = @_;
    return -1 if ($params->{'rid'} eq '');
    my $sql = "delete from eq_itemFields where rid=? ";
    if ($params->{'iid'} && $params->{'iid'} > 0 ) {
        $sql .= " && iid = ?" ;
    }

    my $sth = $dbh->prepare($sql);

    if ($params->{'iid'} && $params->{'iid'} > 0 ) {
        $sth->execute($params->{'rid'}, $params->{'iid'});
    }
    else {
        $sth->execute($params->{'rid'});
    }
    $sth->finish;
}


##################################################################
# function eq_item_deleteByFId
#   
#   $params: 
#           - sfId
#   
###################################################################
sub eq_item_deleteByFid {
    
    my ($dbh, $sfId) = @_;
    return -1 if ($sfId eq '');
    my $sql = "delete from eq_itemFields where sfId = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($sfId);
    $sth->finish;
}

##################################################################
# function eq_item_findByRId
#   
#   $params: 
#           - rid
#   return: a list of items
#   
###################################################################
#
#
sub eq_item_findByRId{
    
    my ($dbh, $recordId)=@_;
    #order = 0|1 0:asc, 1:desc

    return undef if ($recordId eq '');
    my $sth = $dbh->prepare("select iid, barcode,typeId,copyNo,i.createdDate,r.container from eq_items i inner join eq_records r using(rid) 
        where rid=? && i.barcode not regexp '^\_\_\_'");
    my $sql = " SELECT  d.*, fdt.dataType, fdt.maxVal, i.id as i_iid, i.rid, i.sfId, i.sfValue, l.name as bldName
                FROM    eq_def as d 
                LEFT OUTER JOIN eq_fieldDataType fdt on d.fieldType=fdt.id
                LEFT OUTER JOIN eq_itemFields as i on i.sfId = d.id && i.rid = ? && i.iid = ? 
                LEFT OUTER join eq_locationDirectory l on (i.sfId = 18 && i.sfValue = l.code)
                WHERE   d.defType = 'item'  
                ORDER   by d.fOrder ";

    my $sth_item = $dbh->prepare($sql);
    $sth->execute($recordId);
    my @itemList;
    my $i=0;
    while (my ($iid,$barcode,$typeId,$copyNo,$createdDate,$container) = $sth->fetchrow_array()){
       $sth_item->execute($recordId, $iid);
       my @fields;
       my $fCount = 0;
       while(my $f = $sth_item->fetchrow_hashref()){
            push @fields, {
                sfid            => ($f->{'sfId'})? $f->{sfId}:$f->{'id'},
                sfval           => ($f->{'sfValue'})? $f->{'sfValue'}:"", 
                sfname          => $f->{'name'},
                sfReqD          => $f->{'reqD'},
                sfDisplay       => $f->{'display'},
                showOnRecBrief  => $f->{'showOnRecBrief'},
                showOnRecFull   => $f->{'showOnRecFull'},
                url_link        => ($f->{'fieldType'} eq '2')?1:0,
                fieldType       => $f->{'fieldType'},
                fieldTypeName   => $f->{'dataType'},
                fieldMaxVal     => $f->{'maxVal'},
                bldName         => $f->{'bldName'}
            };
            if ($f->{'display'}){
                $fCount++;
            }
       }
       push @itemList, {
            index   => $i,
            order   => $i+1,
            iid     => $iid,
            barcode => $barcode,
            typeId  => $typeId,
            copyNo  => $copyNo,
            cDate   => $createdDate,
            container=>$container, 
            even    => $iid%2?'even':'odd',
            fCount  => $fCount+2,
            fields  => \@fields,
       };
       $i++;
    }
    $sth->finish;
    $sth_item->finish;
    return \@itemList;
}

sub eq_item_findByBarcode{
    
    my ($dbh, $barcode) = @_;
    return undef if ($barcode eq '');
    my @itemInfo = ();
    my $rname;
    my $sql = "SELECT rid, iid, typeId, available FROM eq_items WHERE barcode = ? ";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute($barcode);
    my ($rid, $iid,$typeId,$available) = $sth->fetchrow_array();
    my @fields;
    if ($rid && $iid){
        $sql = "SELECT r.rname as rname, i.*, d.name, d.display,d.showOnRecBrief 
                FROM eq_itemFields as i 
                    LEFT JOIN eq_def as d on i.sfId = d.id 
                    INNER JOIN eq_records as r on i.rid = r.rid
                WHERE i.rid = ? && i.iid = ? 
                ORDER by sfId";

        $sth = $dbh->prepare($sql);
        $sth->execute($rid, $iid); 
        my $sfList = eq_defItem_getList($dbh);
        my $sfname;
        while(my $f = $sth->fetchrow_hashref()){
            $rname = $f->{'rname'};
            foreach my $sf (@$sfList){
                if ($sf->{'id'} == $f->{'sfId'}){
                    $sfname = $sf->{'name'};
                }
            }
            push @fields, {
                sfid          => $f->{'sfId'},
                sfval         => $f->{'sfValue'},
                sfname        => $sfname,
                sfDisplay     => $f->{'display'},
                sfShowOnBrief => $f->{'showOnRecBrief'}
            };
        }
        push @itemInfo, {
            rid         =>  $rid,
            rname       =>  $rname,
            iid         =>  $iid,
            typeId      =>  $typeId,
            available   =>  $available,
            fields      =>  \@fields,
        };
    }
    else{
        return undef;
    }
    return $itemInfo[0];
}

sub eq_item_getBarcodeList{
    my ($dbh,$rid) = @_;

    my @barcodeList = ();
    my $sql = "select * from eq_items where barcode not regexp '^\_\_\_'";
    if (! $rid eq '') {
        $sql .= '&& rid <> ' . $rid ;
    }
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $i=0;
    while (my $f = $sth->fetchrow_hashref()){
        push @barcodeList, { 
            i       => $i++,
            id      => $f->{'id'},
            rid     => $f->{'rid'},
            iid     => $f->{'iid'},
            barcode => $f->{'barcode'},
            copyNo  => $f->{'copyNo'}
        };
    }
    $sth->finish;

    return \@barcodeList;
}
sub eq_item_getMaxIddByRId {

    my ($dbh,$rid) = @_;
    return undef if ($rid eq '');

    my $ret = 0;
    my $sql = "select max(iid) from eq_items where rid=$rid";
    $ret = $dbh->selectrow_array($sql);
    return $ret;
}

#--------------------------------------------------------------------------------

sub eq_authCtrlFields_getList{

    my ($dbh) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select ac.name as name,ac.fCode as fId,ac.fData as fVal,ed.defType,ed.fieldType,fdt.dataType from eq_authCtrl ac inner join eq_def ed on ac.fCode=ed.id left outer join eq_fieldDataType fdt on ed.fieldType=fdt.id  order by defType,fCode,fVal
_STH_
    $sth->execute();
    my $tmp;
    my $retVal = [];
    while (my $rec = $sth->fetchrow_hashref()){
        if (!$tmp->{$rec->{'fId'}}){
            $tmp->{$rec->{'fId'}}->{'fId'} = $rec->{'fId'};
            $tmp->{$rec->{'fId'}}->{'name'} = $rec->{'name'};
            $tmp->{$rec->{'fId'}}->{'type'} = $rec->{'defType'};
            $tmp->{$rec->{'fId'}}->{'fieldType'} = $rec->{'dataType'};
            $tmp->{$rec->{'fId'}}->{'checkboxList'} = ($rec->{'dataType'} eq 'CheckboxList')?1:0;
        }
        push @{$tmp->{$rec->{'fId'}}->{'list'}}, $rec;
    }

    foreach my $fId (sort keys %{$tmp}) {
        push @$retVal, $tmp->{$fId};
    }
    return $retVal;
}

#Container : this item can contain other item(s).
sub eq_containerFields_getList {
    my ($dbh) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select r.rid, r.rname, i.barcode,i.typeId,itf.*,ed.name
from eq_records r 
inner join eq_items i using(rid) 
inner join eq_itemFields itf using(rid, iid) 
inner join eq_def ed on ed.id=itf.sfId  
where r.container = "1" && ed.fieldType= 6
_STH_
    $sth->execute();
    my $tmp;
    my $retVal = [];
    while (my $rec = $sth->fetchrow_hashref()){
        if (!$tmp->{$rec->{'sfId'}}){
            $tmp->{$rec->{'sfId'}}->{'sfId'} = $rec->{'sfId'};
            $tmp->{$rec->{'sfId'}}->{'name'} = $rec->{'name'};
        }
        push @{$tmp->{$rec->{'sfId'}}->{'items'}}, {
                rname   => $rec->{'rname'},   
                barcode => $rec->{'barcode'}
                };
    }

    foreach my $fId (sort keys %{$tmp}) {
        push @$retVal, $tmp->{$fId};
    }
    return $retVal;


}
sub eq_containerItems_getList {
    my ($dbh,$containerId) = @_;
    my $sth = $dbh->prepare(<<_STH_);
select r.*, i.* 
from eq_records r 
inner join eq_items i using(rid) 
inner join eq_itemFields itf using(rid,iid) 
inner join eq_def d on itf.sfId=d.id 
where d.name='Cart Number' && itf.sfValue ='$containerId';
_STH_

    $sth->execute();
    my @list = ();
    while (my $rec = $sth->fetchrow_hashref()){
        push @list, $rec;
    }
    $sth->finish;

    return \@list;
}

sub eq_isBarcodeExist{

    my($dbh,$bc)=@_;
    my $sth = $dbh->prepare(<<_STH_);
select barcode from eq_items where barcode=? && deleted='0'
_STH_
    $sth->execute($bc);
    my ($ret) = $sth->fetchrow_array;
    $sth->finish;
    if(!$ret){
        return 0;
    }
    return $ret; 
}

sub eq_itemType_getList{

    my ($dbh) = @_;
    my @itemTypeList = ();
    my $sth = $dbh->prepare(
        "select it.* from opl_itemType as it inner join opl_itemCategory as ic on it.itemCategory = ic.id where it.itemCategory  = 3");
    $sth->execute();
    my $order = 0;
    while(my $f = $sth->fetchrow_hashref()){
        push @itemTypeList, {
            order   => $order++,
            id      => $f->{'id'},
            desc    => $f->{'description'},
        }   
    }
    $sth->finish;
    return \@itemTypeList;
}

sub eq_dataType_getList{

    my ($dbh) = @_;
    my @dataTypeList = ();
    my $sth = $dbh->prepare("select * from eq_fieldDataType; ");
    $sth->execute();
    while(my $f = $sth->fetchrow_hashref()){
        push @dataTypeList, {
            id          => $f->{'id'},
            dataType    => $f->{'dataType'},
        }   
    }
    $sth->finish;
    return \@dataTypeList;

}

sub eq_category_getList {
     my ($dbh) = @_;
    my @catList;

    my $sql = "select id, name, parentId from eq_category where parentId=0 order by name ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $subsql = "select id, name, parentId from eq_category where parentId=? order by name";
    my $sth_sub = $dbh->prepare($subsql);
    while ( my $rec = $sth->fetchrow_hashref){
        $sth_sub->execute($rec->{'id'});
        while(my $c = $sth_sub->fetchrow_hashref){
            push @{$rec->{'children'}}, $c;
        }
        push @catList,$rec;
    }
    $sth->finish;
    $sth_sub->finish;

    return \@catList;
}

sub eq_categoryMapList {

    my ($dbh) = @_;
    my $sth = $dbh->prepare("select id,name from eq_category");
    my $list;
    $sth->execute();
    while (my $b = $sth->fetchrow_hashref()) {
        $list->{$b->{'id'}} = $b->{'name'};
    }
    return $list;
}

sub eq_categoryNameById {

    my ($dbh,$catIds) = @_;
    my $sth = $dbh->prepare("select name from eq_category where id in ( $catIds )");
    my $catNameStr = "";
    $sth->execute();
    while (my $name = $sth->fetchrow_array()) {
        $catNameStr .= ($catNameStr eq "")? $name : ", " . $name ;
    }
    return $catNameStr;
}

sub eq_building_getList {
    
    my ($dbh) = @_;
    my @list =();
    my $sql = 
    my $sql = (<<_SQL_) ;
select count(*) as count, sfValue as id, l.name as name 
from eq_itemFields f inner join eq_locationDirectory l on (BINARY l.code= BINARY f.sfValue) 
where f.sfId=18 group by f.sfValue 
    having count>0 
    order by l.name
_SQL_
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while(my $rec = $sth->fetchrow_hashref) {
        push @list, $rec;
    }
    $sth->finish;
    return \@list;
}

sub eq_getSchoolList {
    
    my($dbh)=@_;
    my $sql = "select id,code,name,address,city,state,country,
        if (phone is NULL,'',phone) as phone,
        if (fax is NULL,'',fax) as fax
        from eq_locationDirectory order by name";
    my $schoolList = $dbh->selectall_arrayref($sql,{Slice=>{}} ,());
    return $schoolList;
}
#TO BE DELETED WHEN SOLR in use..


#******************************************************************
#                   section : SEARCH
#******************************************************************

sub srch_equipment{

    my ($dbh, $field, $kw, $offset, $pSize, $sortOrder,$sortAttr) = @_;
    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);
        }
        else{

            ($resultSize, $recordList)  = srch_eq_record_item_All($dbh, $sField, $kw, $offset, $pSize , $sortOrder , $sortAttr);
        }
    }
    elsif($type eq 'r'){
        if ($reqF eq '1'){
            ($resultSize, $recordList)   = srch_eq_record_byName($dbh, $sField, $kw, $offset, $pSize , $sortOrder , $sortAttr);
        }
        else{
            ($resultSize, $recordList)   = srch_eq_record_bySubFields($dbh, $sField, $kw, $offset, $pSize , $sortOrder, $sortAttr);
        }
    }
    elsif($type eq 'i'){
        if ($reqF eq '1'){
            ($resultSize, $recordList)  = srch_eq_item_byBarCode_ItemType($dbh, $sField, $kw, $offset, $pSize , $sortOrder , $sortAttr);
        }
        else{
            ($resultSize, $recordList)  = srch_eq_item_bySubFields($dbh, $sField, $kw, $offset, $pSize , $sortOrder , $sortAttr);
        }
    }
    return ($resultSize,$recordList);
}

sub srch_eq_record_byName{
    my ($sql, $sql1, $sql2, $sqlCount);
    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sql1 = "select r.rid, r.rname from eq_records as r WHERE_CLAUSE ";
    $sql2 = "select rid  from eq_records WHERE_CLAUSE ";
    $sqlCount = "select COUNT(*) from eq_records WHERE_CLAUSE ";
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE //;
        $sql1       =~ s/WHERE_CLAUSE //;    
        $sql2       =~ s/WHERE_CLAUSE //;    
        }
    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)  /;
        $sql2       =~ s/WHERE_CLAUSE /where MATCH (rname) AGAINST ('$kw' in boolean mode)  /;
    }
    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) = $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);
}

sub srch_eq_record_bySubFields{
    
    my ($dbh, $field, $kw,$offset, $pSize , $sortOrder, $sortAttr) = @_;
    my ($sql,$sql1, $sql2, $sqlCount);
    my @val = ();
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sql = "select r.rid, r.rname from eq_recordFields as rf inner join eq_records as r on r.rid = rf.rid where r.rid in ";
    $sql1 = "select distinct (rid) from eq_recordFields  WHERE_CLAUSE ";
    $sqlCount = "select COUNT(distinct r.rid) from eq_recordFields as rf inner join eq_records as r on r.rid = rf.rid  WHERE_CLAUSE ";
    
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE / where fId = $field /;
        $sql1       =~ s/WHERE_CLAUSE / where fId = $field /;  
        $sql2       =~ s/WHERE_CLAUSE / where fId = $field /;  
        }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE /where MATCH (fValue) AGAINST (\? in boolean mode ) && fId = \? /;
        $sql1       =~ s/WHERE_CLAUSE /where MATCH (fValue) AGAINST (\? in boolean mode ) && fId = \? /;
        @val        = ($kw, $field); 
    }
    if (! $sortAttr ){
        $sql .= "(" . $sql1 . ") && fId = $field group by r.rid order by rf.fValue " ;
        }
    elsif ($sortAttr && $sortAttr > 0){
        $sql .= "(" . $sql1 . ") && fId = $sortAttr group by r.rid order by rf.fValue" ;
        }
    else{
        $sql .= "(" .  $sql1 . ") && fId = $field group by r.rid 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 as rf ON rf.fId = d.id && rf.rid = ?
    INNER JOIN eq_records as r ON r.rid = rf.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;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                $rname = $f->{'rname'};
                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);
}

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, 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 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;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                $rname = $f->{'rname'};
                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);
}

sub srch_eq_item_byBarCode_ItemType{
    
    my ($sql, $sqlIn, $sqlCount);
    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    $sql = " select distinct r.rid, r.rname 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 rid) from eq_items WHERE_CLAUSE ";
    my $thisField;
    if ($field == 3 ){
        $thisField = 'TypeId';
        }
    else{
        $thisField = 'barcode';
    }

    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE //;
        $sqlIn      =~ s/WHERE_CLAUSE //;    }
    else{
        #$kw =~ s/\*/\%/g;
        #$sqlCount   =~ s/WHERE_CLAUSE /where MATCH(barcode) AGAINST ('$kw' in boolean mode) /;
        #$sqlIn      =~ s/WHERE_CLAUSE /where MATCH(barcode) AGAINST ('$kw' in boolean mode) /;
        $sqlCount   =~ s/WHERE_CLAUSE /where MATCH($thisField) AGAINST ('$kw' in boolean mode) /;
        $sqlIn      =~ s/WHERE_CLAUSE /where MATCH($thisField) AGAINST ('$kw' in boolean mode) /;

    }

    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, 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) = $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'},
                    };
                $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)
}


sub srch_eq_item_bySubFields{

    my ($dbh, $field, $kw, $offset, $pSize, $sortOrder, $sortAttr) =@_;
    my ($sql, $sqlIn, $sqlCount);
    $sql = " select distinct r.rid, r.rname from eq_records as r inner join eq_recordFields as rf on r.rid = rf.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 on r.rid = i.rid WHERE_CLAUSE ";
    $sqlIn = "select distinct (r.rid) from eq_itemFields as i inner join eq_records r on r.rid = i.rid WHERE_CLAUSE ";
    
    if ($kw eq '*'){
        $sqlCount   =~ s/WHERE_CLAUSE /where sfId = $field /;
        $sqlIn      =~ s/WHERE_CLAUSE /where sfId = $field /;
    }
    else{
        $sqlCount   =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST (\? in boolean mode ) && sfId = \? /;
        $sqlIn      =~ s/WHERE_CLAUSE /where MATCH(sfValue) AGAINST (\? in boolean mode ) && sfId = \? /;
        @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 ON r.rid = rf.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'},      
                    display => $f->{'display'},
                    };
                $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);
}

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, 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;
            while (my $f = $sth_rec->fetchrow_hashref()) {
                $rname = $f->{'rname'};
                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'},
                    };
                $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);
}
sub srch_eq_record_item{

}
sub srch_eq_record_All{
    my ($dbh, $field, $kw,$offset, $pSize, $sortOrder, $sortAttr ) = @_;
    my $recMaxOrder = record_maxOrder($dbh)->{'record'}->{'maxOrder'};
    my ($sql, $sqlCount);
    $sql = "select r.rid, r.rname from eq_records as r";
    if ($sortAttr && $sortAttr ne ""){
        $sql .= " inner join eq_recordFields as rf on r.rid = rf.rid where 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{
        $sql .= " order by rname ";    }
    $sqlCount = "select COUNT(distinct rid) from eq_records" ;
    ( $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) = $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);
}
sub srch_eq_record_item_All{
    my ($dbh, $field, $kw,$offset, $pSize) = @_;
    my ($resultSize1, $recordList1)  = srch_eq_record_byName($dbh, $field, $kw, $offset, $pSize);
    my ($resultSize2, $recordList2)  = srch_eq_record_byAllSubFields($dbh, $field, $kw, $offset, $pSize);
    my ($resultSize3, $recordList3)  = srch_eq_item_byBarCode_ItemType($dbh, $field, $kw, $offset, $pSize);
    my ($resultSize4, $recordList4)  = srch_eq_item_byAllSubFields($dbh, $field, $kw, $offset, $pSize);
    my ($resultSize,@recordList, @tmp_recordList);
    $resultSize =  $resultSize1 + $resultSize2 +$resultSize3 +$resultSize4;
    @tmp_recordList = (@$recordList1,@$recordList2,@$recordList3,@$recordList4);

    my %Seen = (); 
    foreach my $r(@tmp_recordList){ 
        next if $Seen{$r->{'rid'}}++; push @recordList, $r; 
    }
    $resultSize = scalar(@recordList);
    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 as r where rid in ($ridStr) ";
    $sqlCount = "select COUNT(*) from eq_records r where 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);
}

1;