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_fieldCodes
    mapData2FieldCode
		
		eq_maxBarcodeDup
    
	  eq_record_getInfo
    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_record_getItemList
    
    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_findItemByBarcode
    eq_item_getBarcodeList
    eq_item_getMaxIddByRId
   
    eq_item_getAccessoryList
    
		eq_record_items_deleteAll

    eq_authCtrlFields_getList
		eq_authCtrlFields_getListByFCode

    eq_containerFields_getList
    eq_containerItems_getList

    eq_isBarcodeExist 
    eq_itemType_getList
    eq_getItemTypeList
    eq_dataType_getList
    
    eq_search_byNoCopy

    eq_definition_getList

    eq_category_getList
    eq_getCategoryName
    eq_getCategoryNameById
    eq_categoryMapList
    eq_getCategoryMapList
    eq_categoryNameById

    eq_getSchoolList
    eq_building_getList
    eq_getItemBuildingList


    eq_report_getOverList
    eq_getAccessoryListByTypeId
    eq_getAuthCtrlFieldsByName
    eq_getAccessoryList
    eq_getUserDeclinedItems

    eq_dbInfo
    eq_itemFields
    eq_recordFields
);

# Version number
$VERSION   = 0.01;      

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

use Opals::Eq_SolrIndex;

#******************************************************************
#               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 hidden=0 && 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.hidden=0 && 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.hidden=0 && 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 hidden=0 && 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'};
}
#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 where hidden=0 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 where hidden=0 order by id");
    $sth->execute();

    while (my $rec = $sth->fetchrow_hashref){
        push @eqDefList, $rec;
    }
    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;
}

################################################################################
#Get Record and item or itemList and field map
# If param₋>{'barcode'} : return item Else return list of items
# 1) $itemInfo = eq_get_recordInfo($dbh,{rid=>$rid, barcode=>$barcode});
# 2) $recordInfo = eq_get_recordInfo($dbh,{rid=>$rid});
# $ret = {record=>$record,item=>$item,items=>$items,map=>$map};
################################################################################
sub eq_record_getInfo {
  my ($dbh, $param) = @_;
  my $record;
  return if (!$param->{'rid'} || $param->{'rid'} eq "");
  my $sql = "select * from eq_records inner join eq_recordFields using(rid) where rid=?";
  my $sth = $dbh->prepare($sql);
  $sth->execute($param->{'rid'} );
  while( my $r = $sth->fetchrow_hashref){
	 if (!$record->{'rid'}){
		$record->{'rid'} = $r->{'rid'};
		$record->{'name'} = $r->{'rname'};
		$record->{'title'} = $r->{'rname'};
		$record->{'createdDate'} = $r->{'createdDate'};
		$record->{'modifiedDate'} = $r->{'modfiedDate'};
		$record->{'category'} = $r->{'category'};
		$record->{'categoryStr'} = eq_categoryNameById($dbh,$r->{'category'});
	 }
	 if (!$record->{$r->{'fId'}}){
		$record->{$r->{'fId'}} = $r->{'fValue'};
	 }
  }
  my $item;
  my $items;
  if ($param->{'barcode'}){
	 $sql = "select * from eq_items i left outer join eq_itemFields f using(rid,iid) where i.barcode=?";
	 $sth = $dbh->prepare($sql);
	 $sth->execute($param->{'barcode'});
	 while( my $i = $sth->fetchrow_hashref){
		if (!$item->{'barcode'}){
		  $item->{'rid'} = $i->{'rid'};
		  $item->{'iid'} = $i->{'iid'};
		  $item->{'barcode'} = $i->{'barcode'};
		  $item->{'typeId'} = $i->{'typeId'};
		  $item->{'available'} = $i->{'available'};
		  $item->{'createdDate'} = $i->{'createdDate'};
		  $item->{'modifiedDate'} = $i->{'modfiedDate'};
		  $item->{'copyNo'} = $i->{'copyNo'}||"";
		}
		if (!$item->{$i->{'sfId'}}){
		  $item->{$i->{'sfId'}} = $i->{'sfValue'};
		}
	 }
  }
  else{
	 my $sql_i = "select * from eq_items i where i.rid=? && barcode not regexp '^___' order by i.iid";
	 my $sql_f = "select * from eq_itemFields f where f.rid=? && f.iid=? order by f.sfId";
	 my $sth_i = $dbh->prepare($sql_i);
	 my $sth_f = $dbh->prepare($sql_f);
	 $sth_i->execute($param->{'rid'});
	 while( my $i = $sth_i->fetchrow_hashref){
    $item={};
		if ($i->{'barcode'} ne ''){
		  $item->{'iid'} = $i->{'iid'};
		  $item->{'barcode'} = $i->{'barcode'};
		  $item->{'typeId'} = $i->{'typeId'};
		  $item->{'available'} = $i->{'available'};
		  $item->{'createdDate'} = $i->{'createdDate'};
		  $item->{'modifiedDate'} = $i->{'modfiedDate'};
		  $item->{'copyNo'} = $i->{'copyNo'}||"";
		  $sth_f->execute($i->{'rid'},$i->{'iid'});
		  while( my $f = $sth_f->fetchrow_hashref){
			 $item->{$f->{'sfId'}} = $f->{'sfValue'};
		  }
		  push @$items,$item;
		}
	 }
	 $sth_i->finish;
	 $sth_f->finish;
  }
  my $map=undef;
  if ($param->{'map'}&& $param->{'map'!=0}){
      $sql = "select id,defType,name,idName,description,showOnSearch,showOnRecFull,showOnRecBrief,showOnLoan,showOnLoanRpt from eq_def";
      $sth = $dbh->prepare($sql);
      $sth->execute();
      while( my $r = $sth->fetchrow_hashref){
        if (!$map->{$r->{'defType'}}->{$r->{'id'}}){
          $map->{$r->{'defType'}}->{$r->{'id'}} = {
            'id' 		=> $r->{'id'},
            'name' 	=> $r->{'name'},
            'idName' 	=> $r->{'idName'},
            'description' => $r->{'description'},
            'showOnRecFull' => $r->{'showOnRecFull'},
            'showOnRecBrief'=> $r->{'showOnRecBrief'}
            }
         }
      }
  }
  $sth->finish;
  return {record=>$record,item=>$item,items=>$items,map=>$map};
}
###################################################################
# 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'}){
        my $incomplete = ($params->{'incomplete'} && $params->{'incomplete'}==1)?'true':'false';
         $sql = "insert into eq_records set rid=?,rname=?,category=?,container=?,incomplete=?"; 
         $sth = $dbh->prepare($sql);
         $sth->execute($params->{'rid'},$params->{'fValue'},$params->{'category'},$params->{'container'},$incomplete);
         $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,$note) = @_;
   return -1 if ($rid eq '');
   $dbh->do("update eq_records set deleted='1',indexed='0', modifiedDate=now() where rid=$rid limit 1");
	 $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");
	 my $sql = "select rid,iid,barcode from eq_items where rid=? && barcode not regexp '^___' && deleted='0'; ";
	 my $sth = $dbh->prepare($sql);
   $sth->execute($rid);
	 while ( my $rec = $sth->fetchrow_hashref) {
  	 eq_item_deleteBarcode($dbh,{rid=>$rec->{'rid'},iid=>$rec->{'iid'},note=>$note});
   }
   return 1;
}

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'}: "",
            categoryStr=>$rec->{'category'}?eq_categoryNameById($dbh,$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);
}

sub eq_record_getItemList{
    my ($dbh, $recordId)=@_;
    return undef if ($recordId eq '');
    my $sth = $dbh->prepare("select i.* from eq_items i inner join eq_records r using(rid) 
      where rid=? && i.barcode not regexp '^\_\_\_' && i.deleted='0'");
    $sth->execute($recordId);
    my @itemList;
    while (my $item = $sth->fetchrow_hashref()){
      push @itemList, {
            barcode => $item->{'barcode'},
            typeId  => $item->{'typeId'},
            itemType=> $item->{'typeId'},
            copyNo  => $item->{'copyNo'},
            cDate   => $item->{'createdDate'},
            container=>$item->{'container'}, 
						note		=> $item->{'note'},	
            serialNumber=>$item->{'serialNumber'},
       };
    }
    $sth->finish;
    return \@itemList;
}
###################################################################
# function eq_record_findByRId
#   params:
#           - record id
#   return : record
###################################################################
sub eq_record_findByRId{
    my ($dbh,$params) = @_;
    return if ($params->{'recordId'} eq '' && $params->{'rid'} 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'  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'}? eq_categoryNameById($dbh,$rec->{'category'}): "",
            container   => $rec->{'container'} || "",
            rid         => $rec->{'rid'},
            rname       => $rec->{'rname'},
            fValue      => $rec->{'fValue'} || "",
            fId         => ($rec->{'fId'})?$rec->{'fId'}:$rec->{'id'},
            showOnRecBrief => $rec->{'showOnRecBrief'},
            showOnLoan  => $rec->{'showOnLoan'}
            };
    }
    $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,$p) = @_;
    return -1 if ($p->{'iid'} eq '');
    my ($sql, $sth, $id);
    #my $consumable = $p->{'recType'} eq 'consumable'?'1':'0';
    my $consumable = $p->{'consumable'};
    $p->{'qty'} = $p->{'qty'} || 1;
    $p->{'unitDescription'} = $p->{'unitDescription'} || "";
    $p->{'unitPerItem'} = $p->{'unitPerItem'} || 1;
    if ($p->{'reqField'}){
        if (eq_isBarcodeExist($dbh,$p->{'barcode'})){
            $p->{'barcode'} = eq_maxBarcodeDup($dbh,$p->{'barcode'});
        }
        #$sql = "insert into eq_items set iid=?,rid=?,barcode=?,typeId=?,available=1,consumable=?,createdDate=now()";
        $sql = "insert into eq_items set iid=?,rid=?,barcode=?,copyNo=?,typeId=?,available=1,consumable=?,qty=?,unitDescription=?,unitPerItem=?,createdDate=now()";
        $sth = $dbh->prepare($sql);
        #$sth->execute($p->{'iid'}, $p->{'rid'},$p->{'barcode'},$p->{'typeId'},$consumable);
        $sth->execute($p->{'iid'},$p->{'rid'},$p->{'barcode'},$p->{'copyNo'},$p->{'typeId'},$consumable,$p->{'qty'},$p->{'unitDescription'},$p->{'unitPerItem'});
        $id = $dbh->{'mysql_insertid'};
        if ($consumable){
          $p->{'type'}='received';
          eq_circ_consumableMgmt($dbh,$p);
        }
    }
    else{
        $sql = "insert into eq_itemFields set iid=?, rid=?, sfId=?, sfValue=?";
        $sth = $dbh->prepare($sql);
        if (!$p->{'sfValue'}){
            $p->{'sfValue'} = "";
        }
        $sth->execute($p->{'iid'},$p->{'rid'},$p->{'sfId'},$p->{'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 $sql_update = "update eq_items set barcode=?,typeId=?,copyNo=?,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'});
        _updateBarcodeToOtherTables($dbh,$params);
        $sth_update->finish;
    }
    else{
        $params->{'reqField'} = 1;
        my $ret = eq_item_add($dbh,$params);
    }
    consumableItemUpdate($dbh,$params);
}
sub consumableItemUpdate {
  my ($dbh,$p)=@_;
  open debug, ">/tmp/U";
  print debug "consumableItemUpdate", to_json($p),"\n";

  #if ($consumable){}
  $dbh->do("update eq_items set qty=qty+? where barcode=? && consumable='1'",undef,$p->{'qtyAdd'},$p->{'barcode'});
  $p->{'type'}='received';
  $p->{'qty'} = $p->{'qtyAdd'};
  print debug "consumableItemUpdate 2", to_json($p),"\n";
  eq_circ_consumableMgmt($dbh,$p);
        


  close debug;
}

################################################################################
# On change barcode, update new barcode to eq_loan,eq_itemStatus,...
sub _updateBarcodeToOtherTables {
  my ($dbh,$params) = @_;
  return -1 if (!$params->{'barcode'} || !$params->{'curBarcode'});
  $dbh->do("update eq_loan set barcode=? where barcode=?",undef,$params->{'barcode'},$params->{'curBarcode'});
  $dbh->do("update eq_itemStatus set barcode=? where barcode=?",undef,$params->{'barcode'},$params->{'curBarcode'});
  $dbh->do("update eq_hold set barcode=? where barcode=?",undef,$params->{'barcode'},$params->{'curBarcode'});
  $dbh->do("update eq_cart set barcode=? where barcode=?",undef,$params->{'barcode'},$params->{'curBarcode'});
  $dbh->do("update eq_cartItem set barcode=? where barcode=?",undef,$params->{'barcode'},$params->{'curBarcode'});
}

################################################################################
# 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,modifiedDate=now(),deleted='1',note=? where rid=? && iid=? && barcode=?";
        my $sth_update = $dbh->prepare($sql_update);
        $sth_update->execute($newBarcode, $params->{'note'},$params->{'rid'}, $params->{'iid'}, $barcode);
        #Eq_Circulation::circ_updateItemStatus($dbh,$barcode,ITEM_DELETED);
        #eq_circ_updateItemStatus($dbh,$barcode,ITEM_DELETED);
        my $sth = $dbh->prepare("insert into eq_itemStatus set barcode=?,ondate=now(),status=?,note=?");
        $sth->execute($barcode,ITEM_DELETED ,$params->{'note'});
        $sth->finish;
        $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,i.note,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;
    my $serialNumber = "";
    while (my ($iid,$barcode,$typeId,$copyNo,$createdDate,$note,$container) = $sth->fetchrow_array()){
       $sth_item->execute($recordId, $iid);
       my @fields;
       my ($fCount,$fCountBrief) = (0,0);
       while(my $f = $sth_item->fetchrow_hashref()){
            if ($f->{'sfId'} == 5){
              $serialNumber = $f->{'sfValue'};
             } ;
            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++;
            }
            if ($f->{'showOnRecBrief'}){
                $fCountBrief++;
            }
       }
       push @itemList, {
            iid     => $iid,
            barcode => $barcode,
            typeId  => $typeId,
            itemType=> $typeId,
            copyNo  => $copyNo,
            cDate   => $createdDate,
            container=>$container, 
						note		=>$note,	
            serialNumber=>$serialNumber,
            even    => $iid%2?'even':'odd',
            fields  => \@fields,
				fCountBrief=>$fCountBrief
       };
       $i++;
    }
    $sth->finish;
    $sth_item->finish;
    return \@itemList;
}

sub eq_findItemByBarcode {
  my ($dbh, $p) = @_;
  return eq_item_findByBarcode($dbh,$p->{'barcode'});
}

sub eq_item_findByBarcode{
    my ($dbh, $barcode) = @_;
    return undef if ($barcode eq '');
    my @itemInfo = ();
    my $rname;
    #my $sql = "SELECT rid, iid, typeId, available,consumable FROM eq_items WHERE barcode = ? ";
    my $sql = "SELECT rid, iid, typeId, available,consumable,qty,unitPerItem,unitDescription FROM eq_items WHERE barcode = ? ";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute($barcode);
    my ($rid, $iid,$typeId,$available,$consumable,$qty,$unitPerItem,$unitDescription) = $sth->fetchrow_array();
    my @fields;
    if ($rid && $iid){
        $sql = "SELECT r.rname as rname, 
					 i.*, d.name, d.display,d.showOnRecBrief,d.showOnLoan 
                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'},
                sfShowOnLoan  => $f->{'showOnLoan'}
            };
        }
        push @itemInfo, {
            rid         =>  $rid,
            rname       =>  $rname,
            name        =>  $rname,
            iid         =>  $iid,
            typeId      =>  $typeId,
            available   =>  $available,
            consumable  =>  $consumable || 0,
            qty         =>  $qty,
            unitPerItem =>  $unitPerItem,
            unitDescription=>$unitDescription,
            fields      =>  \@fields,
            fieldById   =>  _formatFields(\@fields)
        };
    }
    else{
        return undef;
    }
    return $itemInfo[0];
}

sub _formatFields {
  my ($fields) = @_;
  my $ret = {};
  foreach my $f (@$fields) {
  $ret->{$f->{'sfid'}} = {
      name  => $f->{'sfname'},
      value => $f->{'sfval'},
      id    => $f->{'sfid'}
    }
  }
  return $ret;
}

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_getAccessoryList {
    my ($dbh,$p)=@_;
    return eq_item_getAccessoryList($dbh,$p);
}


sub eq_item_getAccessoryList {
    my ($dbh,$p)=@_;
    my $rid = $p->{'rid'} || undef;
    my $barcode = $p->{'barcode'} || undef;

    my $sql = "select r.rname as name, i.* from eq_items i inner join eq_records r using(rid) where r.deleted='0' && i.deleted='0' && i.barcode not regexp '^\_\_\_'";
    if (defined $rid && $rid>0) {
        $sql .= '&& rid = ' . $rid ;
    }
    if (defined $barcode && $barcode ne "") {
        $sql .= "&& barcode='$barcode'" ;
    }
    else{
      return;
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $acs=[];
    my $item=undef;
    my $ret = $sth->fetchrow_hashref;
    $acs = _getAccessoryList($dbh,$ret->{'barcode'});
    if ($ret){
      $item = { 
        name    => $ret->{'name'},
        id      => $ret->{'id'},
        rid     => $ret->{'rid'},
        iid     => $ret->{'iid'},
        barcode => $ret->{'barcode'},
        copyNo  => $ret->{'copyNo'},
        typeId  => $ret->{'typeId'},
        consumable=>$ret->{'consumable'},
        accessoryList=>$acs,
        curAccessoryList=>$acs,
        selected => 1,
        changed  => 0
      };
    }
    $sth->finish;
    return $item;
}

sub _getAccessoryList {
  my ($dbh,$barcode) = @_;
  my $sql= "select accessory from eq_itemAccessory where barcode=? && status=1";
  my @values = ();
  push @values,$barcode;
  my $ret = $dbh->selectcol_arrayref($sql,{},@values );
  return $ret;
}

sub eq_record_items_deleteAll {
	 my ($dbh,$param)=@_;
	 return -1 if (!$param->{'rid'} || $param->{'rid'}<0);
	 my $rid= $param->{'rid'};
	 my $recInfo= eq_record_getInfo($dbh,{rid=>$rid});
	 my $recJson = to_json({
			record=>$recInfo->{'record'},
			items=>$recInfo->{'items'}
	 });
   $dbh->do("update eq_records set deleted='1',indexed='0', modifiedDate=now() where rid=$rid limit 1");
	 #$dbh->do("insert into eq_recordArchive(rid,ondate,data) values (?,now(),?)",undef,$rid,$recJson);
   $dbh->do("delete from eq_recordFields where rid=$rid");
   $dbh->do("delete from eq_itemFields where rid=$rid");
	 my $sql = "select rid,iid,barcode from eq_items where rid=? && barcode not regexp '^___' && deleted='0'; ";
	 my $sth = $dbh->prepare($sql);
   $sth->execute($rid);
	 while ( my $rec = $sth->fetchrow_hashref) {
  	 eq_item_deleteBarcode($dbh,{rid=>$rid,iid=>$rec->{'iid'},note=>$param->{note}});
   }
	 my $eq_solr = Opals::Eq_SolrIndex->new(dbh=>$dbh);
	 $eq_solr->eq_solrIndex_cleanByRId($rid);
   $eq_solr->eq_slr_updateIndex();
}

#--------------------------------------------------------------------------------
sub eq_authCtrlFields_getList{
    my ($dbh,$fCode) = @_;
		my $sql = "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 ";
		if (defined $fCode && $fCode > 0){
			 $sql .= " where ac.fCode = $fCode ";
		}
		$sql .= "order by defType,fCode,fVal";
		my $sth = $dbh->prepare($sql);
    $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;
}

sub eq_authCtrlFields_getListByFCode{
    my ($dbh,$fCode) = @_;
		my $ret = [];
		return if (!$fCode);
		my $sql = "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
			 where ac.fCode = $fCode order by defType,fCode,fVal";
		my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref()){
        push @{$ret}, $rec->{'fVal'};
    }
    return $ret;
}

sub eq_getAuthCtrlFieldsByName{
    my ($dbh,$name) = @_;
		my $ret = [];
		return if (!$name);
		my $sql = "select * from eq_authCtrl where name=? order by fData";
		my $sth = $dbh->prepare($sql);
    $sth->execute($name);
    while (my $rec = $sth->fetchrow_hashref()){
        push @{$ret}, $rec;
    }
    return $ret;
}
#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_getItemTypeList {
    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 order by id" );
    $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_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 order by id" );
    $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_getCategoryName {
  my ($dbh) = @_;
  return eq_category_getList($dbh);
}
sub eq_getCategoryNameById {
    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_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_getCategoryMapList {
    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) = @_;
    return undef if (!$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_getItemBuildingList {
    my ($dbh) = @_;
    return eq_building_getList($dbh);
}

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;
}

sub eq_search_byNoCopy {
  my ($dbh,$p) = @_;
  my $sqlCount = "select count(*) from (select count(i.barcode) as count from eq_records r inner join eq_items i using(rid)  where r.deleted<>'1' && i.deleted<> '1'  group by r.rid having count> 10) as c";
  my $sql = "
  select r.rid,r.rname,model.fValue as model, manu.fValue as manufacturer,count(i.barcode) as count 
  from  eq_records r inner join eq_items i using(rid) 
	left outer join eq_recordFields model on model.rid=r.rid and model.fId=1
	left outer join eq_recordFields manu on manu.rid=r.rid and manu.fId=3
  where r.deleted <> '1'  && i.deleted<>'1'  group by i.rid having count>$p->{'noCopy'}";

  if (defined $p->{'orderBy'} && $p->{'orderBy'} =~ m/rid|author|count/ ) {
    $sql .= " order by $p->{'orderBy'}";
  }
  else{
    $sql .= " order by titleSort";
  }
  $sql .= ($p->{'reverse'} eq 'false')?" asc" : " desc";
  #open debug,">/tmp/debugK";print debug "p",to_json($p),"\n";print debug "reverse $p->{'reverse'}\n";print debug "sql:$sql\n";close debug;
  my @recordList = ();
  my @bind_values=();
  my ($resultSize) = $dbh->selectrow_array($sqlCount, undef, @bind_values);
  if ($resultSize>0){
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
         push @recordList,$rec;
      };
  }
  else{
    $resultSize=0;
  }
  return ($resultSize, \@recordList);
}

sub eq_getUserDeclinedItems{
  my($dbh,$uid)=@_;
  my @declinedItems=();
  my $sth = $dbh->prepare("select it.id,it.description,d.typeId as itemDeclined,d.uid 
    from opl_itemType as it inner join opl_itemCategory as ic on it.itemCategory=ic.id 
    left outer join eq_itemDeclined d on d.typeId=it.id and d.uid=? && d.status=1
    where it.itemCategory=3 order by it.id");
    $sth->execute($uid);
    my $order = 0;
    while(my $f = $sth->fetchrow_hashref()){
      $f->{'checked'}=$f->{'uid'} && $f->{'uid'}>0?1:0;
        push @declinedItems,$f;
    }
    $sth->finish;
    return \@declinedItems;
}

sub eq_dbInfo {
  my ($dbh,$p) = @_;
  my $sql=<<_SQL_;
    select count(distinct rid) as totRecs,count(i.barcode) as totItems from eq_records r inner join eq_items i using(rid) where r.deleted='0' && i.deleted='0' && i.barcode not regexp '^___';
_SQL_
  
  my ($numRecs,$numItems) = $dbh->selectrow_array($sql, undef);
  $sql = "select count(*) from eq_loan l inner join eq_items i using(barcode) where l.dateReturn is null";
  my ($numOnLoan) = $dbh->selectrow_array($sql, undef);
  $sql = "select count(*) from eq_category";
  my ($numCategories) = $dbh->selectrow_array($sql, undef) || 0;
  my $numCSVdata = 0;
  my ($numCSVData) =$dbh->selectrow_array("select count(*) from eq_csvDuplicate", undef) || 0;
  ($numCSVData) += $dbh->selectrow_array("select count(*) from eq_csvImport", undef) || 0;
  return {
      numRecs=>$numRecs,
      numItems=>$numItems,
      numOnLoan=>$numOnLoan,
      numCategories=>$numCategories,
      numCSVData=> $numCSVData
  };
}

sub eq_itemFields {
  my $fields = [
    {"id"=>"eq_barcode","idName"=>"eq_barcode","name"=>"Barcode","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"copyNumber","idName"=>"copyNumber","name"=>"Copy Number","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"typeId","idName"=>"typeId","name"=>"Item Type","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"5","idName"=>"serialNumber","name"=>"Serial Number","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"42","idName"=>"purchaseOrder","name"=>"Purchase Order","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"6","idName"=>"price","name"=>"Price","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"43","idName"=>"ipAddress","name"=>"IP Address","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"44","idName"=>"macAddress","name"=>"MAC Address","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"45","idName"=>"softwareKey","name"=>"Software Key","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"46","idName"=>"cartNumber","name"=>"Cart Number","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"47","idName"=>"slotNumber","name"=>"Slot Number","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"48","idName"=>"accessories","name"=>"Accessories","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"9","idName"=>"fundingSource","name"=>"Funding Source","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"26","idName"=>"districtCode","name"=>"District Code","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"18","idName"=>"buildingCode","name"=>"Building Code","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"11","idName"=>"location","name"=>"Location","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"31","idName"=>"dateAcquired","name"=>"Date Acquired/Assigned/Install","description"=>"","dataType"=>"date","authCtrl"=>"0","size"=>50 },
    {"id"=>"7","idName"=>"districtInventoryNumber","name"=>"District Inventory Number","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50},
    {"id"=>"38","idName"=>"statusNote","name"=>"Item Status Note","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"37","idName"=>"machineName","name"=>"Machine Name","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"39","idName"=>"vendorName","name"=>"Vendor Name","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"40","idName"=>"assignedPersonFirstName","name"=>"Assigned Person First Name","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"41","idName"=>"assignedPersonLastName","name"=>"Assigned Person Last Name","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"8","idName"=>"itemDesignation","name"=>"Item Designation","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
  ];
}
sub eq_recordFields {
  my $fields = [
    {"id"=>"eq_name","idName"=>"eq_name","name"=>"Equipment","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"category","idName"=>"category","name"=>"Category","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"1","idName"=>"manufacturer","name"=>"Manufacturer","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"3","idName"=>"model","name"=>"Model","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"14","idName"=>"warrantyNote","name"=>"Warranty Note","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"16","idName"=>"manufacturerUrl","name"=>"Manufacturer Url","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"28","idName"=>"manualUrl","name"=>"Manual Url","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"27","idName"=>"supplierUrl","name"=>"Supplier Url","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"33","idName"=>"generalNote","name"=>"General Note","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
    {"id"=>"36","idName"=>"generalAccessoriesNote","name"=>"General Accessories Note","description"=>"","dataType"=>"text","authCtrl"=>"0","size"=>50 },
  ];
}

sub eq_getAccessoryListByTypeId {
  my ($dbh ) = @_;
  my $sql="select typeId as type,group_concat(accessory) as list from eq_accsMgmt group by typeId";
	my $sth = $dbh->prepare($sql);
	$sth->execute();
	my $ret ={};
    while (my $rec = $sth->fetchrow_hashref()){
        $ret->{$rec->{'type'}} = $rec->{'list'};
    }
    return $ret;
}

1;