/lib/FORM_DynTable.inc

<?php

require_once('LANG_Label.inc');
require_once('FORM_Object.inc');
require_once('AV_ModObject.inc');
require_once('LIB_Document.inc');
require_once('AV_Exception.inc');
require_once('FORM_LexAnalyzer.inc');
require_once('AV_ArrayObject.inc');
require_once('AV_FaultTolerantFS.inc');

//--Class: FORM_DynTable
//--Desc: Manages dynamic tables. Dynamic table have property tables
//--Desc: associated.
class FORM_DynTable
{
  protected $table;                    //--Desc: Table's name
  protected $dbDriver;                 //--Desc: Database driver handler
  protected $module;                   //--Desc: Module's handler
  protected $pageBuilder;
  protected $fieldsDef;                //--Desc: Keeps dynamic table fields
                                       //--Desc: definition
  protected $keyFields;
  protected $oldValues;                //--Desc: Array of old values kept
                                       //--Desc: after an update
  protected $formulaFieldsDef;
  protected $formulas;
  protected $formulaType;
  protected $lang;
  private $lockedField;
                                  
 
  //
  //--Public
  //
  function __construct($table, &$module)
  {
    $this->table = $table;
    $this->module = & $module;
    $this->lang = $module->pageBuilder->lang;
    $this->pageBuilder = & $this->module->pageBuilder;
    $this->dbDriver = & $module->dbDriver;
    $this->lockedField = NULL;
    $this->fieldsDef = NULL;
    $this->formulaFieldsDef = NULL;
    $this->keyFields = array();
  }

  function GetReadableFields()
  {
    // Find readble fields
    $SQL = 'SELECT fieldName FROM FORM_Field ';
    $SQL .= "WHERE (accessMode = 'r' OR accessMode = 'rw') ";
    $SQL .= "AND tableName ='". $this->table ."' AND ";
    $SQL .= "type <> 'selectMult' ";
  
    return $this->dbDriver->GetCol($SQL);
  }

  function LoadFieldsDef($disableFileRequired = false,$disableRequired = false)
  {
    // prevents fieldsdef reload
    if($this->fieldsDef != NULL)
      return $this->fieldsDef;

    // load fieldsdef from database
    $fieldsDef = $this->GetFieldsDef();

    // prepare fieldsDef
    foreach($fieldsDef as & $fieldDef) 
      $this->ParseFieldDef($fieldDef,$disableFileRequired,$disableRequired);

    return $this->fieldsDef;
  }

  function Duplicate($sourceKey, $fields, $extraFields = array())
  {
    // do insertion
    $SQL = "INSERT INTO " . $this->table . " (";
    $first = true;

    foreach($this->fieldsDef as $field)
    {
      if ($field['keyField'] != 1)
      {
	if (strpos($field['accessMode'],'w')!==false &&
	    ((isset($field['list']) && !$field['list']) || 
	     !isset($field['list'])) &&
	    $field['type'] != 'selectMult' &&
	    $field['type'] != 'descr')
	{
	  if(!$first)
	  {
	    $SQL .= ', ';
	    $VALS .= ', ';
	  }
	  $first = false;

	  if($field['type'] == 'file')
	  {
	    $SQL .= $field['fieldName'].'FileName, ';
	    $SQL .= $field['fieldName'].'Type, ';
	    $VALS .= $field['fieldName'].'FileName, ';
	    $VALS .= $field['fieldName'].'Type, ';
	  }
	  $SQL .= $field['fieldName'];

	  if(isset($fields[$field['fieldName']]))
	    $VALS .= "'".$fields[$field['fieldName']]."'";
	  else
	    $VALS .= $field['fieldName'];
	}
      }
      else
      {
	if(isset($fields[$field['fieldName']]))
	{
	  if(!$first)
	  {
	    $SQL .= ', ';
	    $VALS .= ', ';
	  }
	  $first = false;
	  $SQL .= $field['fieldName'];
	  $VALS .= "'".$fields[$field['fieldName']]."'";
	}
      }
    }

    foreach($extraFields as $key => $field)
    {
      if(!$first)
      {
	$SQL .= ', ';
	$VALS .= ', ';
      }
      $first = false;
      $SQL .= $key;
      $VALS .= "$field";
    }

    

    $SQL .= ") SELECT " . $VALS;
    $SQL .= " FROM " . $this->table;
    $SQL .= " WHERE ".$sourceKey['fieldName']."='".$sourceKey['fieldValue']."'";


    $this->dbDriver->ExecSQL($SQL);
  
    foreach($this->fieldsDef as $field)
    {
      switch($field['type'])
      {
      case "selectMult":
      case "selectMultiple":
	$this->DuplicateOptions($sourceKey,$field);
	break;

      case "fileList":
	$this->DuplicateFileList($sourceKey,$field);
	break;

      case "emailList":
	$this->DuplicateEmailList($sourceKey,$field);
	break;

      case "langLabel":
      case "langTextArea":
      case "langHtmlArea":
	$this->DuplicateLabel($sourceKey,$field);
	break;
      }

    }
  }

  function Insert(& $fields, $extraFields = array())
  {
    // checks for violated fields
    $this->CheckIndexes($fields);

    // prevents locale problems inserting float fields
    setlocale(LC_ALL, 'en_US');

    // do insertion
    $SQL = "INSERT INTO " . $this->table . " (";
    $first = true;
    $fileFields = array();

    foreach($this->fieldsDef as $field)
    {
      if ($field['keyField'] != 1)
      {
	if($field['type'] == 'file' &&
	   $fields[$field['fieldName']]['error']==UPLOAD_ERR_OK &&
	   $fields[$field['fieldName']]['tmp_name'] != NULL)
	{
	  $this->SetFile($field,$fields[$field['fieldName']],$extraFields);

	  if($this->dbDriver->type=='oracle')
	    $fileFields[] = $field['fieldName'];
	}

	if (strpos($field['accessMode'],'w')!==false &&
	    ((isset($field['list']) && !$field['list']) || 
	     !isset($field['list'])) &&
	    $field['type'] != 'selectMult' &&
	    $field['type'] != 'descr' &&
	    $fields[$field['fieldName']] !== NULL &&
	    $fields[$field['fieldName']] !== '')
	{
	  if(!$first)
	  {
	    $SQL .= ', ';
	    $VALS .= ', ';
	  }
	  $first = false;
	  $SQL .= $field['fieldName'];
	  if(($field['type'] == 'select' ||
	      $field['type'] == 'readOnlySelect')
	     && (! isset($fields[$field['fieldName']]) ||
		 $fields[$field['fieldName']] === 0 ||
		 $fields[$field['fieldName']] === NULL))
	    $VALS .= 'NULL';
	  else
	  {
	    if($field['maxValue']!=NULL && $fields[$field['fieldName']] > $field['maxValue'])
	      $VALS .= "'" .$field['maxValue']. "'";
	    elseif($field['minValue']!=NULL && $fields[$field['fieldName']] < $field['minValue'])
	      $VALS .= "'" .$field['minValue']. "'";
	    else
	      $VALS .= "'" . $fields[$field['fieldName']]. "'";
	  }
	}
      }
      else
      {
	$fieldKey = $field['fieldName']; 
      }
    }
    foreach($extraFields as $key => $field)
    {
      if(!$first)
      {
	$SQL .= ', ';
	$VALS .= ', ';
      }
      $first = false;
      $SQL .= $key;
      $VALS .= "$field";
    }
    
    // reverts default locale
    setlocale(LC_ALL, $this->lang.'.utf8');
 

    $SQL .= ") VALUES (" . $VALS . ")";

    if($this->dbDriver->type=='oracle' && count($fileFields)>0)
    {
      foreach($fileFields as $key=>$file)
      {
	if($key>0)
	{
	  $FIELDS .= ',';
	  $VARS .= ',';
	}
	$FIELDS .= $file;
	$VARS .= ':'.$file;
	$this->dbDriver->BindBlob($file,$fields[$file]['content']);
      }
      $SQL .= 'RETURNING '.$FIELDS;
      $SQL .= ' INTO '.$VARS;

    }
    
    $this->dbDriver->ExecSQL($SQL);
  
    // determine key value
    if($fields[$fieldKey]!=NULL)
      $keyValue = $fields[$fieldKey];
    elseif($extraFields[$fieldKey]!=NULL)
      $keyValue = $extraFields[$fieldKey];
    else
      $keyValue = 'LAST_INSERT_ID()';

    foreach($this->fieldsDef as $field)
    {
      switch($field['type'])
      {
      case "selectMult":
	$this->SetOptions($fields[$field['fieldName']], $field, 
			  $keyValue,$fieldKey);
	break;
      case "selectMultiple":
	$this->SetSelectMultiple($fields[$field['fieldName']], $field, 
				 $keyValue,$fieldKey);
	break;
      case "fileList":
	$this->SetFileList($fields[$field['fieldName']],$field,
			    $keyValue);
	break;
      case "objectList":
	$this->SetObjectList($fields[$field['fieldName']],$field,
			     $keyValue,$fieldKey,true);
	break;
      case "emailList":
	$this->SetEmailList($fields[$field['fieldName']],$field,
			    $keyValue);
	break;
      case 'schedule':
	$this->SetSchedule($fields[$field['fieldName']], $field, 
			   $keyValue,$fieldKey);
	break;
      case "langLabel":
      case "langTextArea":
      case "langHtmlArea":
	$this->SetLabel($field, $fields[$field['fieldName']], $keyValue, 
			$fieldKey);
	break;
      }

    }
  }

  
  function Set($fields, $extraFields = array())
  {
    // checks for violated fields
    $this->CheckIndexes($fields);

    // prevents locale problems inserting float fields
    setlocale(LC_ALL, 'en_US');
    $blobToFS = $this->pageBuilder->siteConfig->getVar('admin','blobToFS');
    // do update operation
    $SQL = "UPDATE " . $this->table . " SET ";
    $WHERE .= ' WHERE ';
    $first = true;
    $firstWhere = true;
    $fileFields = array();
    foreach($this->fieldsDef as $field)
    {
      if($field['type'] == 'file' &&
	 $fields[$field['fieldName']]['error']==UPLOAD_ERR_OK &&
	 $fields[$field['fieldName']]['tmp_name'] != NULL)
      {
	$this->SetFile($field,$fields[$field['fieldName']],$extraFields);
	if($this->dbDriver->type=='oracle' || $blobToFS)
	  $fileFields[] = $field['fieldName'];
      }


      if($field['keyField'] == '0')
      {
	if (strpos($field['accessMode'],'w')!==false &&
	    ((isset($field['list']) && !$field['list']) || 
	     !isset($field['list'])) &&
	    $field['type'] != 'selectMult' &&
	    $field['type'] != 'descr')
	{
	  if($fields[$field['fieldName']] !== NULL)
	  {
	    if(!$first)
	      $SQL .= ', ';
	    else 
	      $first = false;
	    $SQL .= $field['fieldName'] . " = ";
	    if($fields[$field['fieldName']] === '')
	      $SQL .= 'NULL';
	    else
	    {
	      if($field['maxValue']!=NULL && $fields[$field['fieldName']] > $field['maxValue'])
		$SQL .= "'" .$field['maxValue']. "'";
	      elseif($field['minValue']!=NULL && $fields[$field['fieldName']] < $field['minValue'])
		$SQL .= "'" .$field['minValue']. "'";
	      else
		$SQL .= "'" . $fields[$field['fieldName']] . "'";
	    }
	  }
	} 
      }
      else
      {
	$fieldKey = $field['fieldName'];
	$fieldName = $field['fieldName'];
	if(!$firstWhere)
	  $WHERE .= ' AND ';
	else
	  $firstWhere = false;
	$WHERE .= $fieldName . " = '" . $fields[$fieldName] . "'";
	$fieldKeyValue = $fields[$fieldName];
      } // if($field['keyField'] == 0)
    } // foreach($this->fieldsDef as $field)
    
    foreach($extraFields as $field => $value)
    {
      if(!$first)
	$SQL .= ', ';
      else
	$first = false;
      $SQL .= "$field = $value";
    }

    // reverts default locale
    setlocale(LC_ALL, $this->lang.'.utf8');

    // lock rows
    $LOCK = 'SELECT * FROM ' . $this->table . $WHERE . ' FOR UPDATE';
    $this->oldValues = $this->dbDriver->GetRow($LOCK);

    if(count($this->oldValues) == 0)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    // do update
    if(!$first)
    {
      $SQL .= $WHERE;
      if($this->dbDriver->type=='oracle' && count($fileFields)>0 && !$blobToFS)
      {
	foreach($fileFields as $key=>$file)
	{
	  if($key>0)
	  {
	    $FIELDS .= ',';
	    $VARS .= ',';
	  }
	  $FIELDS .= $file;
	  $VARS .= ':'.$file;
	  $this->dbDriver->BindBlob($file,$fields[$file]['content']);
	}
	$SQL .= 'RETURNING '.$FIELDS;
	$SQL .= ' INTO '.$VARS;
      }
      
      $this->dbDriver->ExecSQL($SQL);
    }

    // mark old blob filesystem files to be deleted if they were updated
    if($blobToFS && count($fileFields)>0)
    {
      $SQL = "UPDATE LIB_BlobFS SET deleted='1' ";
      $SQL .= 'WHERE id IN (';
      $firstBlob = true;
      foreach($fileFields as $key=>$file)
      {
	if($firstBlob)
	  $firstBlob = false;
	else
	  $SQL .= ',';
	$SQL .= "'".$this->oldValues[$file]."'";
      }
      $SQL .= ')';
      $this->dbDriver->ExecSQL($SQL);
    }

    foreach($this->fieldsDef as $field)
    {
      switch($field['type']){ 
      case "selectMult":
	$this->SetOptions($fields[$field['fieldName']], $field, 
			  $fieldKeyValue, $fieldKey);
	break;

      case "selectMultiple":
	$this->SetSelectMultiple($fields[$field['fieldName']], $field, 
				 $fieldKeyValue, $fieldKey);
	break;

      case "fileList":
	$this->oldValues[$field['fieldName']] = 
	  $this->SetFileList($fields[$field['fieldName']],$field,
			     $fieldKeyValue);
	break;
      case "objectList":
	$this->oldValues[$field['fieldName']] = 
	  $this->SetObjectList($fields[$field['fieldName']],$field,
			       $fieldKeyValue,$fieldKey,false);
	break;
      case "emailList":
	$this->oldValues[$field['fieldName']] = 
	  $this->SetEmailList($fields[$field['fieldName']],$field,
			      $fieldKeyValue);
	break;
      case 'schedule':
	$this->SetSchedule($fields[$field['fieldName']], $field, 
			   $fieldKeyValue,$fieldKey);
	break;
      case "langLabel":
      case "langTextArea":
      case "langHtmlArea":
	$this->SetLabel($field, $fields[$field['fieldName']], 
			$fieldKeyValue, $fieldKey);
	break;
      }
    }
    
    return $this->oldValues;
  }


  function Del($fields)
  {
    // do update operation
    $SQL = "DELETE FROM " . $this->table . " WHERE ";
    $first = true;
    foreach($this->fieldsDef as $field)
    {
      if($field['keyField'] != '0')
      {
	$fieldName = $field['fieldName'];
	if(!$first)
	  $SQL .= 'AND ';
	else
	  $first = false;
	$SQL .= $fieldName . " = '" . $fields[$fieldName] . "' ";
      } 
    }
    
    if($first)
      throw new AV_Exception(FAILED, $this->lang);

    // does delete operation
    $this->dbDriver->ExecSQL($SQL);
  }

  function GetForm($keyFields, $fieldName, $sourceId, & $templates, 
		   & $formObject, $defValues = array(),$dateFormat = NULL,
		   $numberFormat = true, $short = false)
  {
    // load formula defs
    $this->LoadFormulaDefs();

    foreach($this->formulaFieldsDef as $value)
    {
      $fields[$value['fieldName']] = $this->ConvertIf($value['expression']);
      $this->formulaType[$value['fieldName']] = $value['formulaType'];
    }

    $parms['locale'] = localeconv();

    foreach($fields as $field => $expression)
      $this->formulas[$field] = $this->Decompose($this->formulas, $expression,
						 $fields,$parms);

    $this->LoadFieldsDef();

    if($keyFields != NULL)
      $values = $this->Get($keyFields);
    else
      $values = $this->GetDefValues();

    $values = ArrayObjectMerge($defValues, $values);

    $langLabel = new LANG_Label($this->module);
    foreach ($this->fieldsDef as $key=>$value)
    {
      if($fieldName == '' || $fieldName == $value['fieldName'] || strstr($fieldName,'.',true)==$value['fieldName'])
      {
	// desabilita template da base em caso de carregamento de campo único
	if($fieldName == $value['fieldName'])
	  unset($this->fieldsDef[$key]['template']);
	// ajusta label para todos os campos
	if($value['labelId']!=NULL)
	  $this->fieldsDef[$key]['label'] = 
	    $langLabel->GetLabel($value['labelId'], 
				 $this->lang);

	// ajusta outros labels do campo
	$this->SetFieldLabels($this->fieldsDef[$key]);

	// ajusta xhtmlRequired para todos os campos
	if($value['required'])
	{
	  $this->fieldsDef[$key]['xhtmlRequired'] = "class='required'";
	  $this->fieldsDef[$key]['required'] = "required";
	}
	else
	  unset($this->fieldsDef[$key]['required']);

	// desabilita campo caso não seja para escrita
	if(strpos($value['accessMode'],'w')==false)
	  $this->fieldsDef[$key]['xhtmlDisabled'] = "disabled='disabled'";

	// ajusta versão do avalanche para uso nos campos
	$this->fieldsDef[$key]['avVersion'] = $this->pageBuilder->avVersion;

 	$child = & $this->GetField($this->fieldsDef[$key],$values,$keyFields,
				   $templates,$sourceId,$dateFormat,
				   $numberFormat,$fieldName,$short);

	if($child != NULL)
	{
	  $formObject->children[$value['orderby']] = & $child;
	  $formObject->attrs[$value['fieldName']] = $child->attrs['value'];
	  if($short)
	    unset($child->attrs['value']);
	}
	else if(isset($values[$value['fieldName']]))
	{
	  $formObject->attrs[$value['fieldName']] = $values[$value['fieldName']];
	}
	  
      }
    }
    return $formObject;
  }

  function Get($keyFields)
  {
    $where = NULL;
    $select = NULL;
    foreach($this->fieldsDef as $key => $value)
    {
      if($value['type'] != 'selectMult' &&
	 $value['type'] != 'descr' && 
	 strpos($value['accessMode'],'r')!==false)
      {
	if($select != NULL)
	  $select .= ',';

	$select .= $value['fieldName'];
      }
      if($value['keyField'] == 1)  
      {  
	if (!isset($keyFields[$value['fieldName']]))
	  throw new AV_Exception(INVALID_FIELD, $this->lang, 
			 Array('fieldName'=>$value['fieldName'],
			       'table'=>$this->table,
			       'addMsg'=>' ('.$value['fieldName']. ')'));

	if($where != NULL)
	  $where .= ' AND ';
	$where .= $value['fieldName']. " = '";
	$where .= $keyFields[$value['fieldName']]. "'";
      }
    }
    $from = $this->table;

    // no fields to be returned
    if($select == NULL)
      return NULL;

    if($where == NULL)
    {
      throw new AV_Exception(INVALID_FIELD, $this->lang, 
			     Array('fieldName'=>'keyFields',
				   'table'=>$this->table,
				   'addMsg'=>' (keyFields)'));
    }

    $SQL = ' SELECT ' .$select. ' FROM ' .$from. ' WHERE ' ;
    $SQL .= $where. ' LOCK IN SHARE MODE';
    
    // Executes SQL statement
    $values = $this->dbDriver->GetRow($SQL);
    if($values==NULL)
    {
      throw new AV_Exception(NOT_FOUND, $this->lang, 
			     Array('table'=>$this->table));
    }
    return $values;
  }

  function GetDefValues()
  {
    try
    {
      $desc = $this->dbDriver->GetAll('DESC '.$this->table);
      $values = array();
      foreach($desc as $field)
      {
	if($field['Default']!==NULL && $field['Default']!=='' &&
	   !in_array($field['Field'],$this->keyFields))
	  $values[$field['Field']] = $field['Default'];
      }
      return $values;
    } 
    catch(Exception $e)
    {
      // table may not exist (search fields form)
      return array();
    }
  }
         

  function GetAll($orderBy = NULL)
  {
    $returnFields = $this->GetReadableFields();

    if(count($returnFields)==0)
      throw new AV_Exception(FAILED, $this->lang);

    $returnFields = implode(',', $returnFields);

    $SQL = "SELECT $returnFields FROM ".$this->table.' ';
    if($orderBy != NULL)
      $SQL .= "ORDER BY $orderBy ";
    $SQL .= 'LOCK IN SHARE MODE';
   
    return $this->dbDriver->GetAll($SQL);
  }  

  function AddField($fieldName, $dbType, $labels, $type, $size, $prevField,
		    $consistType, $required, $allowNull,$accessMode)
  {
    if($prevField != '')
    {
      $SQL = 'SELECT orderby FROM FORM_Field WHERE ';
      $SQL .= "tableName='".$this->table."' AND fieldName = '$prevField' ";
      $SQL .= 'LOCK IN SHARE MODE ';
      $order = $this->dbDriver->GetOne($SQL);
      if($order == NULL)
	throw new AV_Exception(INVALID_FIELD, $this->lang);


      $SQL = 'SELECT orderby from FORM_Field WHERE ';
      $SQL .= "tableName='".$this->table."' AND orderby > $order ";
      $SQL .= 'FOR UPDATE ';
      $this->dbDriver->GetCol($SQL);
 
      $SQL = 'UPDATE FORM_Field SET orderby=orderby+1 WHERE ';
      $SQL .= "tableName='".$this->table."' AND orderby > $order";
      $this->dbDriver->ExecSQL($SQL);

      $order++;
    }
    else
      $order = 1;

    $labelMan = new LANG_Label($this->module);
    $labelId = $this->table . '_' . $fieldName;
    $labelMan->SetLabel($labelId, $labels);
    
    $SQL = 'INSERT INTO FORM_Field ';
    $SQL .= '(tableName,fieldName,labelId,type,size,orderby,';
    $SQL .= 'consistType,required,allowNull,trimField,';
    $SQL .= 'uniq,keyField,accessMode,staticProp) VALUES ';
    $SQL .= "('".$this->table."','$fieldName','$labelId','$type',$size,";
    $SQL .= "$order,'$consistType','$required','$allowNull','1',";
    $SQL .= "'0','0','$accessMode','0')";
    $this->dbDriver->ExecSQL($SQL);

    // clears fieldsDef to force new load
    $this->fieldsDef = NULL;

    $SQL = 'ALTER TABLE ' . $this->table;
    $SQL .= " ADD $fieldName $dbType;";

    return $SQL;
  }


  function ChangeField($newName, $dbType, $labels, $type, $size, 
		       $consistType, $required, $allowNull,$accessMode)
  {
    if($this->lockedField==NULL)
      throw new AV_Exception(FAILED, $this->lang);

    if($this->lockedField['staticProp']=='1')
      throw new AV_Exception(PERMISSION_DENIED, $this->lang);

    $SQL = NULL;

    if($labels != NULL)
    {
      $labelMan = new LANG_Label($this->module);
      $labelId = $this->table . '_' . $this->lockedField['fieldName'];
      $labelMan->SetLabel($labelId, $labels);
      $SQL = "labelId='$labelId'";
    }

    if($type != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "type='$type'";
    }

    if($size != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "size=$size";
    }

    if($consistType != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "consistType='$consistType'";
    }

    if($required != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "required='$required'";
    }

    if($allowNull != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "allowNull='$allowNull'";
    }

    if($accessMode != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "accessMode='$accessMode'";
    }

    if($SQL != NULL)
    {
      $SQL = "UPDATE FORM_Field SET $SQL WHERE ";
      $SQL .= "tableName='".$this->table."' AND ";
      $SQL .= "fieldName='".$this->lockedField['fieldName']."' ";
      $this->dbDriver->ExecSQL($SQL);

      // clears fieldsDef to force new load
      $this->fieldsDef = NULL;
    }

    if($newName != NULL && $dbType != NULL)
    {
      $SQL = 'ALTER TABLE ' . $this->table;
      $SQL .= " CHANGE ".$this->lockedField['fieldName']." $newName $dbType;";
      $this->lockedField = NULL;
      return $SQL;
    }
    else if($newName != NULL || $dbType != NULL)
     throw new AV_Exception(INVALID_FIELD, $this->lang);

    $this->lockedField = NULL;
    return NULL;
  }

  function DropField($fieldName)
  {
    $SQL = 'DELETE FROM FORM_Field WHERE ';
    $SQL .= "tableName='".$this->table."' AND fieldName='$fieldName' ";
    $this->dbDriver->ExecSQL($SQL);
    
    // clears fieldsDef to force new load
    $this->fieldsDef = NULL;

    $SQL = 'ALTER TABLE ' . $this->table;
    $SQL .= " DROP $fieldName";
    return $SQL;
  }


  function SetOrder($fieldName, $up)
  {
    
    $SQL = 'SELECT MAX(orderby) FROM FORM_Field WHERE ';
    $SQL .= "tableName = '" . $this->table . "' ";
    $SQL .= "LOCK IN SHARE MODE";
    $last = $this->dbDriver->GetOne($SQL);
    if($last == NULL)
      throw new AV_Exception(FAILED, $this->lang);

    $SQL = 'SELECT orderby FROM FORM_Field WHERE ';
    $SQL .= "tableName = '" . $this->table . "' AND fieldName = '$fieldName' ";
    $SQL .= "FOR UPDATE";
    $order = $this->dbDriver->GetOne($SQL);
    if($order == NULL)
      return;

    //up ou down
    if($up)
    {
      if($order == 1)
	return;
      $nextOrder = $order-1;
    }
    else
    {
      if($last == $order)
	return;
      $nextOrder = $order+1;
    }
    
    $SQL = 'SELECT orderby FROM FORM_Field WHERE ';
    $SQL .= "tableName = '" . $this->table . "' AND orderby = $nextOrder ";
    $SQL .= "FOR UPDATE";
    $nextOrder = $this->dbDriver->GetOne($SQL);
    if($nextOrder == NULL)
      throw new AV_Exception(FAILED, $this->lang);

    $SQL = "UPDATE FORM_Field SET orderby=$order WHERE ";
    $SQL .= "tableName = '" . $this->table . "' AND orderby = $nextOrder ";
    $this->dbDriver->ExecSQL($SQL);
     
    $SQL = "UPDATE FORM_Field SET orderby=$nextOrder WHERE ";
    $SQL .= "tableName = '" . $this->table . "' AND fieldName = '$fieldName' ";
    $this->dbDriver->ExecSQL($SQL);
  
    // clears fieldsDef to force new load
    $this->fieldsDef = NULL;
  }


  function GetFieldInfo($fieldName)
  {
    $SQL = 'SELECT labelId,type,template,langTemplate,size,orderby,';
    $SQL .= 'consistType,required,allowNull ';
    $SQL .= 'FROM FORM_Field WHERE ';
    $SQL .= "tableName = '" . $this->table . "' AND fieldName = '$fieldName' ";
    $SQL .= 'LOCK IN SHARE MODE ';

    $field = $this->dbDriver->GetRow($SQL);
    if(count($field) == 0)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    $labelMan = new LANG_Label($this->module);
    $labelList = $labelMan->GetLabelList($field['labelId']);

    foreach($labelList as $label)
      $field['descr_'.$label['lang']] = $label['value'];
    return $field;
  }


  function GetNextFieldValue($fieldName, $prefix)
  {
    $offset = mb_strlen($prefix) + 1;
    $SQL = "SELECT MAX(SUBSTR($fieldName,$offset)+0) ";
    $SQL .= 'FROM '. $this->table .' WHERE ';
    $SQL .= "$fieldName like '$prefix%' ";
    $SQL .= 'LOCK IN SHARE MODE';

    $num = $this->dbDriver->GetOne($SQL);
    if($num == NULL)
      return $prefix.'1';
    return $prefix . ++$num;
  }

  function GetSelectFieldInfo($fieldName)
  {
    $SQL = 'SELECT * FROM FORM_SelectField WHERE ';
    $SQL .= "tableName = '".$this->table."' AND fieldName = '$fieldName' ";
    $SQL .= 'LOCK IN SHARE MODE ';
    return $this->dbDriver->GetRow($SQL);
  }

  //
  //--Protected
  //
  protected function ParseFieldDef(& $fieldDef,$disableFileRequired,$disableRequired)
  {
    $key = $fieldDef['orderby'];
    $this->fieldsDef[$key] = & $fieldDef;

    // sets default stripSlashes to false
    $this->fieldsDef[$key]['stripSlashes'] = false;      

    if($disableRequired)
      $fieldDef['required'] = '0';

    if($fieldDef['keyField']=='1')
      $this->keyFields[] = $fieldDef['fieldName'];

    if($fieldDef['attributes'] != NULL)
      $fieldDef['attributes'] = json_decode($fieldDef['attributes'],true);

    switch($fieldDef['type'])
    {
      // sets langLabel field as list
    case 'langLabel': 
    case "langTextArea": 
    case "langHtmlArea":
      $this->fieldsDef[$key]['list'] = true;
      $this->fieldsDef[$key]['sizeVarName'] = $fieldDef['fieldName'].
	'_numOfOptions';
      $subfieldDef = array();
      $subfieldDef[0]['fieldName'] = 'lang';
      $subfieldDef[0]['required'] = $fieldDef['required'];
      $subfieldDef[0]['allowNull'] = $fieldDef['allowNull'];
      $subfieldDef[0]['stripSlashes'] = false;
      $subfieldDef[1]['fieldName'] = 'value';
      $subfieldDef[1]['required'] = $fieldDef['required'];
      $subfieldDef[1]['allowNull'] = $fieldDef['allowNull'];
      $subfieldDef[1]['stripSlashes'] = false;
      $subfieldDef[1]['consistType'] = $fieldDef['consistType'];
      $this->fieldsDef[$key]['fieldsDef'] = $subfieldDef;
      break;
    case 'schedule':
      $this->fieldsDef[$key]['list'] = true;
      $this->fieldsDef[$key]['required'] = $fieldDef['required'];
      $this->fieldsDef[$key]['sizeVarName'] = $fieldDef['fieldName'].
	'_numOfOptions';
      $subfieldDef = array();
      $subfieldDef[0]['fieldName'] = 'start';
      $subfieldDef[0]['required'] = false;
      $subfieldDef[0]['allowNull'] = true;
      $subfieldDef[0]['consistType'] = 'datetime';
      $subfieldDef[1]['fieldName'] = 'numOfHours';
      $subfieldDef[1]['required'] = false;
      $subfieldDef[1]['allowNull'] = true;
      $subfieldDef[1]['consistType'] = 'integer';
      $this->fieldsDef[$key]['fieldsDef'] = $subfieldDef;
      break;

    case 'selectMult':
      $this->fieldsDef[$key]['list'] = true;
      $this->fieldsDef[$key]['sizeVarName'] = $fieldDef['fieldName'].
	'_numOfOptions';
      $subfieldDef = array();
      $subfieldDef[0]['fieldName'] = 'value';
      $subfieldDef[0]['required'] = false;
      $subfieldDef[0]['allowNull'] = true;
      $subfieldDef[0]['consistType'] = 'integer';
      $this->fieldsDef[$key]['fieldsDef'] = $subfieldDef;
      break;

    case 'selectMultiple':
      // gambiarra pra evitar addslashes      
      $this->fieldsDef[$key]['stripSlashes'] = true; 
      break;

    case 'fileList':
      $subfieldDef = array();
      $subfieldDef[0]['fieldName'] = 'docId';
      $subfieldDef[0]['required'] = true;
      $subfieldDef[0]['allowNull'] = false;
      $subfieldDef[0]['consistType'] = 'integer';
      $this->fieldsDef[$key]['fieldsDef'] = $subfieldDef;
      // gambiarra pra evitar addslashes      
      $this->fieldsDef[$key]['stripSlashes'] = true; 
      break;

    case 'objectList':
      $subfields = $this->LoadObjectListDefs($fieldDef);
      $this->fieldsDef[$key]['fieldsDef'] = $subfields;
      // gambiarra pra evitar addslashes      
      $this->fieldsDef[$key]['stripSlashes'] = true; 
      
      break;
      
    case 'file':
      // gambiarra pra evitar addslashes      
      $this->fieldsDef[$key]['stripSlashes'] = true; 
      
      if($disableFileRequired)
	$this->fieldsDef[$key]['required'] = '0';
      $this->fieldsDef[$key]['allowNull'] = '1';
      break;
      
    case 'emailList':
      // gambiarra pra evitar addslashes      
      $this->fieldsDef[$key]['stripSlashes'] = true; 
    }
  }
  
  protected function LoadObjectListDefs($fieldDef)
  {
    $data = $this->GetObjectListInfo($fieldDef);
    $libDoc = new LIB_Document($this->module);
    $fieldsDef = $libDoc->LoadFieldsDef(NULL, $data['path']);
    $excludeFields = array('userId','groupId','userRight','groupRight',
			   'otherRight','creationDate','lastChanged',
			   'lastChangedUserId','keywords');
    foreach($fieldsDef as $key => &$field)
      if(in_array($field['fieldName'],$excludeFields))
	unset($fieldsDef[$key]);

    return $fieldsDef;
  }

  protected function GetObjectListInfo($fieldDef)
  {
    $SQL = 'SELECT path, ol.folderId, parentIdField ';
    $SQL .= 'FROM FORM_ObjectListField ol, LIB_Folder f ';
    $SQL .= "WHERE tableName='".$this->table."' ";
    $SQL .= "AND fieldName='".$fieldDef['fieldName']."' ";
    $SQL .= 'AND ol.folderId=f.folderId ';
    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);
    return $data;
  }

  protected function GetFieldsDef()
  {
    $SQL = 'SELECT labelId,fieldName,type,template,langTemplate,outputFuncRef,';
    $SQL .= 'size,attributes,consistType,`maxValue`,minValue,allowNull,trimField,required,uniq,';
    $SQL .= 'staticProp,accessMode, keyField, orderby ';
    $SQL .= 'FROM FORM_Field ';
    $SQL .= "WHERE tableName ='". $this->table;
    $SQL .= "' ORDER BY orderby";
    $SQL .= ' LOCK IN SHARE MODE ';
    // Executes SQL statement
    return $this->dbDriver->GetAll($SQL);
  } 

  protected function GetTemplate($templates, $fieldName)
  {
    if(is_array($templates[$fieldName]))
      return $templates[$fieldName]['fileName'];
    else
      return $templates[$fieldName];
  }

  protected function &GetField(& $fieldDef, & $values, $keyFields,  
			       & $templates, $sourceId, $dateFormat = NULL,
			       $numberFormat = true, $fieldName = NULL, 
			       $short = false)
  {
    switch ($fieldDef['type'])
    {
    case "langLabel":
    case "langTextArea":
    case "langHtmlArea":
      return $this->GetLangField($fieldDef, $values, $templates);

    case 'descr':
    case "password":
    case 'textArea':
    case "text":
    case 'hidden':
    case 'integer':
    case 'dateFilter':
    case 'currencyFilter':
    case 'integerFilter':
      return $this->GetTextField($fieldDef, $values);

    case 'authSecret':
      return $this->GetAuthSecretField($fieldDef, $values);

    case 'htmlArea':
      if($short)
	return $this->GetTextField($fieldDef, $values);
      return $this->GetHtmlField($fieldDef, $values);

    case 'boolean':
      return $this->GetBooleanField($fieldDef, $values);

    case "accessRight":
      return $this->GetAccessRightField($fieldDef, $values);

    case "date":
    case "datetime":
      return $this->GetDateField($fieldDef, $values, $dateFormat);

    case "cpf":
      return $this->GetCpfField($fieldDef, $values);
    
    case "cnpj":
      return $this->GetCnpjField($fieldDef, $values);
    
    case "cep":
      return $this->GetCepField($fieldDef, $values);

    case 'schedule':
      return $this->GetScheduleField($fieldDef, $values, $keyFields);

    case "select":
    case "radio":
      return $this->GetSelectField($fieldDef, $values, $keyFields,  
				   $templates, $sourceId);
    case 'readOnlySelect':
      return $this->GetReadOnlySelectField($fieldDef, $values);

    case "selectMult":
    case "selectMultiple":
      return $this->GetSelectMultField($fieldDef, $values, $keyFields, 
				       $templates, $sourceId);
    case 'float':
    case 'currency':
      return $this->GetFloatField($fieldDef, $values, $numberFormat);

    case 'percent':
      return $this->GetPercentField($fieldDef, $values, $numberFormat);

    case 'file':
      return $this->GetFileField($fieldDef, $values);

    case 'usageTerms':
      return $this->GetUsageTermsField($fieldDef, $values);

    case 'legalEntityRef':
     return $this->GetLEOrNPEntityRef($fieldDef, $values, 
				      'legalEntity');

    case 'naturalPersonRef':
      return $this->GetLEOrNPEntityRef($fieldDef, $values, 
				       'naturalPerson');

    case 'leOrNpEntityRef':
      return $this->GetLEOrNPEntityRef($fieldDef, $values, NULL);

    case 'fileList':
      return $this->GetFileListField($fieldDef,$keyFields,$templates);

    case 'objectList':
      return $this->GetObjectListField($fieldDef,$keyFields,$dateFormat,
				       $numberFormat,$fieldName,$short);

    case 'emailList':
      return $this->GetEmailListField($fieldDef,$keyFields,$templates);

    case 'folder':
      return $this->GetFolderField($fieldDef, $values);

    case 'formula':
      return $this->GetFormulaField($fieldDef, $values);

    default: 
      return NULL;
    }
  }

  //--Method: GetLangLabel
  protected function GetLangField(& $fieldDef, & $values, & $templates)
  {
    // retrieves all registered languages
    $langLabel = new LANG_Label($this->module);
    $langList = $langLabel->GetLangList();

    // creates lang field
    $langField = new FORM_Object('field', $this->pageBuilder, 
				   $templates, 'nextLangField');
    $langField->attrs['fieldName'] = $fieldDef['fieldName'];
    $langField->attrs['numOfOptions'] = count($langList);
    $langField->attrs['type'] = 'langField';

    // creates lang subfields
    foreach($langList as $key => $lang)
    {
      $field = new AV_ModObject('field', $this->pageBuilder);

      // fieldDef pode ser AV_OCIArray no caso de conexão oracle
      if(gettype($fieldDef)=='object')
	$field->attrs = clone $fieldDef;
      else
	$field->attrs = $fieldDef;

      $field->attrs['lang'] = $lang['lang'];
      $field->attrs['langLabel'] = mb_substr($lang['lang'],0,2);
      $field->attrs['fieldIndex'] = $key+1;
      $keys[$lang['lang']] = $key;
      $langField->children[$key] = $field;

     // initiate new html to have at least a <p>&nbsp;</p>
     if($fieldDef['type'] == 'langHtmlArea')
	$field->attrs['value'] = "<p>&nbsp;</p>";
    }

    // set's lang subfields values
    $list = $langLabel->GetLabelList($values[$fieldDef['fieldName']],
				     $fieldDef['type']!='langLabel');

    foreach($list as $key=>$value)
    {
      $langField->children[$keys[$value['lang']]]->attrs['value'] = 
	$value['value'];
      if($value['lang'] == $this->lang)
	$langField->attrs['value'] = $value['value'];
    }
    return $langField;
  }
  
  //--Method: GetTextField
  protected function &GetTextField(& $fieldDef, $values)
  {
    $fieldDef['value'] = $values[$fieldDef['fieldName']];
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }

  protected function &GetAuthSecretField(& $fieldDef, $values)
  {
    global $SM_siteID;

    $ga = new PHPGangsta_GoogleAuthenticator();
    if($values[$fieldDef['fieldName']]==NULL)
      $fieldDef['value'] = $ga->createSecret();
    else
      $fieldDef['value'] = $values[$fieldDef['fieldName']];

    $name = $SM_siteID;
    $fieldDef['qrCodeUrl'] = $ga->getQRCodeGoogleUrl($SM_siteID.'-'.$values['login'],
						     $fieldDef['value']);
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }
  
  //--Method: GetTextField
  protected function GetHtmlField(& $fieldDef, $values)
  {
    // initiate new html to have at least a <p>&nbsp;</p>
    if($values[$fieldDef['fieldName']] == NULL)
      $values[$fieldDef['fieldName']] = "<p>&nbsp;</p>";

    return $this->GetTextField($fieldDef,$values);
  }

  //--Method: GetTextField
  protected function &GetBooleanField(& $fieldDef, $values)
  {
    if($values[$fieldDef['fieldName']])
      $fieldDef['xhtmlCheckedAttr'] = "checked='checked'";
    return $this->GetTextField($fieldDef, $values);
  }

  //--Method: GetTextField
  protected function &GetPercentField(& $fieldDef, $values, $numberFormat)
  {
    if($numberFormat)
      $values[$fieldDef['fieldName']] *= 100;
    return $this->GetFloatField($fieldDef, $values,$numberFormat);
  }

  //--Method: GetTextField
  protected function &GetFloatField(& $fieldDef, $values, $numberFormat)
  {
    if($numberFormat)
    {
      $locale = localeconv();
    
      if(isset($values[$fieldDef['fieldName']]))
      {
	$fieldDef['value'] = number_format($values[$fieldDef['fieldName']],
					   $locale['frac_digits'],
					   $locale['mon_decimal_point'],
					   $locale['mon_thousands_sep']);
      }
    }
    else
      $fieldDef['value'] = $values[$fieldDef['fieldName']];
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }

  
  protected function GetAccessRightField(& $fieldDef, $values)
  {
    $fieldDef['value'] = $values[$fieldDef['fieldName']];
    $fieldDef['read'] = (strpos($fieldDef['value'],'r')===false)?'0':'1';
    $fieldDef['write'] = (strpos($fieldDef['value'],'w')===false)?'0':'1';
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }
  
  protected function &GetDateField(& $fieldDef, $values, $dateFormat)
  {
    if($values[$fieldDef['fieldName']] != 0)
    {
      $timestamp = strtotime($values[$fieldDef['fieldName']]);
      if($dateFormat != NULL)
      {
	$fieldDef['value'] = strftime($dateFormat,$timestamp);
	$fieldDef['valueUTS'] = $values[$fieldDef['fieldName']];
      }
      else
	$fieldDef['value'] = $values[$fieldDef['fieldName']];

      $fieldDef['year'] = date('Y',$timestamp);
      $fieldDef['month'] = date('m',$timestamp);
      $fieldDef['day'] = date('d',$timestamp);
	
      $fieldDef['hour'] = date('H',$timestamp);
      $fieldDef['minute'] = date('i',$timestamp);
      $fieldDef['second'] = date('s',$timestamp);

    }
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }
 
  //--Method: GetCpfField
  protected function &GetCpfField(& $fieldDef, $values)
  {
    $fieldDef['value'] = $values[$fieldDef['fieldName']];
    $fieldDef['cpf_1'] = mb_substr($values[$fieldDef['fieldName']],0,-2); 
    $fieldDef['cpf_2'] = mb_substr($values[$fieldDef['fieldName']],-2); 
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }
  
  //--Method: GetCnpjField
  protected function &GetCnpjField(& $fieldDef, $values)
  {
    $fieldDef['value'] = $values[$fieldDef['fieldName']];
    $fieldDef['cnpj_1'] = mb_substr($values[$fieldDef['fieldName']],0,-6); 
    $fieldDef['cnpj_2'] = mb_substr($values[$fieldDef['fieldName']],-6, 4);
    $fieldDef['cnpj_3'] = mb_substr($values[$fieldDef['fieldName']],-2); 
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }

  //--Method: GetCepField
  protected function &GetCepField(& $fieldDef, $values)
  {
    $fieldDef['value'] = $values[$fieldDef['fieldName']];
    $fieldDef['cep_1'] = mb_substr($values[$fieldDef['fieldName']],0,-3);
    $fieldDef['cep_2'] = mb_substr($values[$fieldDef['fieldName']],-3); 
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = $fieldDef;
    return $modobj;
  }

  //--Method: GetSchedule
  protected function &GetScheduleField(& $fieldDef, $values, $keyFields)
  {
    $SQL = "SELECT LPAD(DAY(start),2,'0') as startDay, ";
    $SQL .= "LPAD(MONTH(start),2,'0') as startMonth, ";
    $SQL .= "LPAD(YEAR(start),4,'0') as startYear, ";
    $SQL .= "LPAD(HOUR(start),2,'0') as startHour, ";
    $SQL .= "LPAD(MINUTE(start),2,'0') as startMinute, numOfHours ";
    $SQL .= 'FROM AV_Schedule ';
    $SQL .= "WHERE docId='".$keyFields['docId']."' ";
    $SQL .= 'ORDER BY start ';
    $scheduleList = $this->dbDriver->GetAll($SQL);

    $values[$fieldDef['fieldName']] = json_encode($scheduleList);

    return $this->GetTextField($fieldDef,$values);
    
  }

  //--Method: GetSelectField
  function &GetSelectField(& $fieldDef,$values,$keyFields,$templates,
			   $sourceId)
  {  
    // retrieves information from FORM_SelectField
    $SQL = ' SELECT * FROM FORM_SelectField WHERE ';
    $SQL .= "tableName='" .$this->table. "' AND ";
    $SQL .= "(fieldName='" . $fieldDef['fieldName']. "' OR ";
    $SQL .= "FIND_IN_SET('".$fieldDef['fieldName']."',targetField)<>0) ";
    $selectFields = $this->dbDriver->GetAll($SQL);

    foreach($selectFields as $key=>$sField)
    {
      if($selectFields[$key]['fieldName']==$fieldDef['fieldName'])
	$field = $selectFields[$key];
      else 
      {
	$targetFields = explode(',',$selectFields[$key]['targetField']);
	if(in_array($fieldDef['fieldName'],$targetFields))
	  $sourceField=$selectFields[$key]['fieldName'];
      }
    }

    $fieldDef['targetField'] = $field['targetField'];
    $fieldDef['sourceField'] = $field['sourceField'];
    $modobj = new FORM_Object('field', $this->pageBuilder, 
			      $templates, $fieldDef['type'].'Option');
    $modobj->attrs = & $fieldDef;

    if($field['sourceField'] != NULL)
    {
      if($sourceId != '')
	$sourceValue = $sourceId;
      else
	$sourceValue = $values[$sourceField];
      //$fieldDef['value'] = $sourceValue;
    }
    // else

    $fieldDef['value'] = $values[$fieldDef['fieldName']];

    // retrieves select options if sourceField is NULL (no dependence)
    // or if sourceValue is set (dependence solved)
    if($field['sourceField'] == NULL || $sourceValue != '')
    {
      $currValue = $values[$fieldDef['fieldName']];
      $SQL = "SELECT ";
      if($field['optionTableValue'] == NULL)
	$SQL .= 'value, ';
      else
	$SQL .= $field['optionTableValue'] . ' as value, ';
      $SQL .=  $field['optionTableKey'] . " as id,";
      $SQL .= "IF(" . $field['optionTableKey'] . " ='".$currValue;
      $SQL .= "',1,0) as selected, '";
      $SQL .= $fieldDef['type']."Option' as type FROM ";
      $SQL .= $field['optionTable'];
      if($field['optionTableValue'] == NULL)
	$SQL .= " ,LANG_Label";

      $WHERE = '';
      if($sourceValue != '')
	$WHERE = $field['sourceField'] . " = '$sourceValue' ";
      if($field['optionTableValue'] == NULL)
      {
	if($WHERE != '')
	  $WHERE .= ' AND ';
	$WHERE .= "labelId = name AND lang = '";
	$WHERE .= $this->lang."'"; 
      }
      if($WHERE != '')
	$SQL .= ' WHERE ' . $WHERE;

      if($field['orderField'] != NULL)
      {
	$SQL .= ' ORDER BY ' . $field['orderField'];
	$SQL .= ' '.($field['ascOrder']=='1'?'ASC':'DESC');
      }

      $rows = $this->dbDriver->GetAll($SQL);

      $i = 0;
      foreach($rows as & $row)
      {
	$modobj->children[$i] = new AV_ModObject('option', 
						 $this->pageBuilder);
	if($row['selected'] == 1)
	{
	  $row['xhtmlSelectedAttr'] = "selected='selected'";
	  $row['xhtmlCheckedAttr'] = "checked='checked'";
	  $modobj->attrs['text'] = $row['value'];
	  $fieldDef['text'] = $row['value'];
	}
	$modobj->children[$i]->attrs = & $row;
	$modobj->children[$i]->attrs['fieldIndex'] = $i+1;
	$modobj->children[$i]->attrs['fieldName'] = $fieldDef['fieldName'];
	$modobj->children[$i]->attrs['lang'] = $this->lang;
	$i++;
      }
      $modobj->attrs['numOfOptions'] = $i;
    }
    return $modobj;
  }


  protected function GetReadOnlySelectField(& $fieldDef, $values)
  {
    // retrieves information from FORM_SelectField
    $SQL = ' SELECT * FROM FORM_SelectField WHERE ';
    $SQL .= "tableName='" .$this->table. "' AND ";
    $SQL .= "fieldName='" . $fieldDef['fieldName']. "' ";
    $SQL .= 'LOCK IN SHARE MODE';
    $selectField = $this->dbDriver->GetRow($SQL);

    $SQL = "SELECT ";
    if($selectField['optionTableValue'] == NULL)
      $SQL .= 'value ';
    else
      $SQL .= $selectField['optionTableValue'] . ' as value ';
    $SQL .= 'FROM ' . $selectField['optionTable'];
    if($field['optionTableValue'] == NULL)
      $SQL .= " ,LANG_Label";
    $SQL .= ' WHERE ';
    $SQL .= $selectField['optionTableKey'];
    $SQL .= "='".$values[$fieldDef['fieldName']]."' ";
    if($selectField['optionTableValue'] == NULL)
    {
      $SQL .= "AND labelId = name AND lang = '";
      $SQL .= $this->pageBuilder->lang . "'"; 
    }

    $fieldDef['text'] = $this->dbDriver->GetOne($SQL);

    return $this->GetTextField($fieldDef, $values);
  }

  //--Method: GetSelectMultField
  protected function &GetSelectMultField(&$fieldDef,$values,$keyFields,
					$templates,$sourceId)
  {
    // retrieves information from FORM_SelectField
    $SQL = ' SELECT * FROM FORM_SelectField WHERE ';
    $SQL .= "tableName='" .$this->table. "' AND ";
    $SQL .= "(fieldName='" . $fieldDef['fieldName']. "' OR ";
    $SQL .= "FIND_IN_SET('".$fieldDef['fieldName']."',targetField)<>0) ";
    $selectFields = $this->dbDriver->GetAll($SQL);

    foreach($selectFields as $key=>$sField)
    {
      if($selectFields[$key]['fieldName']==$fieldDef['fieldName'])
	$field = $selectFields[$key];
      else 
      {
	$targetFields = explode(',',$selectFields[$key]['targetField']);
	if(in_array($fieldDef['fieldName'],$targetFields))
	  $sourceField=$selectFields[$key]['fieldName'];
      }
    }
     
    $fieldDef['targetField'] = $field['targetField'];
    $fieldDef['sourceField'] = $field['sourceField'];
    $modobj = new FORM_Object('field', $this->pageBuilder, 
			      $templates, 'selectMultOption');

    if($field['sourceField'] != NULL)
    {
      if($sourceId != '')
	$sourceValue = $sourceId;
      else
	$sourceValue = $values[$sourceField];
    }

    // retrieves select options if sourceField is NULL (no dependence)
    // or if sourceValue is set (dependence solved)
    if($field['sourceField'] == NULL || $sourceValue != '')
    {
      $SQL = 'SELECT ';
      if($field['optionTableValue'] == NULL)
	$SQL .= 'value, ';
      else
	$SQL .= $field['optionTableValue'] . ' as value, ';
      $SQL .= $field['optionTableKey']." as id, ";
      $SQL .= "'selectMultOption' as type ";
      $SQL .= "FROM ".$field['optionTable'];
      if($field['optionTableValue'] == NULL)
	$SQL .= " ,LANG_Label ";
      $WHERE = '';
      if($sourceValue != '')
	$WHERE = $field['sourceField'] . " = '$sourceValue' ";
      if($field['optionTableValue'] == NULL)
      {
	if($WHERE != '')
	  $WHERE .= ' AND ';
	$WHERE .= "labelId = name AND lang = '";
	$WHERE .= $this->lang."'"; 
      }
      if($WHERE != '')
	$SQL .= ' WHERE ' . $WHERE;

      if($field['orderField'] != NULL)
      {
	$SQL .= ' ORDER BY ' . $field['orderField'];
	$SQL .= ' '.($field['ascOrder']=='1'?'ASC':'DESC');
      }

      $rows = $this->dbDriver->GetAll($SQL);
      
      if($keyFields != NULL)
      {
	// retrieves selected options
	$control = TRUE;
	foreach($keyFields as $key => $value)
	{
	  if($control = TRUE)
	  {
	    $WHERE = $key. " = '" .$value. "'"; 
	    $control = FALSE;
	  }
	  else
	    $WHERE .=" AND " .$key. " = '" .$value. "'"; 
	}
	$SQL = ' SELECT '.$field['optionTableKey'].' FROM ';
	$SQL .= $field['selectedTable'].' WHERE '.$WHERE; 
	$temp1 = $this->dbDriver->GetCol($SQL);
      
      }
      foreach ($rows as $key => $value)
      {
	if (in_array($value['id'],$temp1))
	{
	  $rows[$key]['selected'] = 1;
	  $rows[$key]['xhtmlCheckedAttr'] = "checked='checked'";
	  $selected[] = $value['id'];
	  $selectedText[] = $value['value'];
	}
	else
	  $rows[$key]['selected'] = 0;
	$rows[$key]['fieldName'] = $fieldDef['fieldName'];
      }
      $fieldDef['value'] = $selected;
      $fieldDef['text'] = implode(',', $selectedText);
      $modobj->LoadChildrenFromRows($rows, "option", "numOfOptions");
    }
    else
      $fieldDef['numOfOptions'] = '0';

    $modobj->attrs = ArrayObjectMerge($modobj->attrs,$fieldDef);
    return $modobj;
  }

  protected function &GetFileListField(&$fieldDef,&$keyFields,&$templates)
  {
    $fileListField = new AV_ModObject('file', $this->pageBuilder,
				      $this->GetTemplate($templates,
							 'fileListItem'),
				      'imageListItem');
    $fileListField->attrs = & $fieldDef;
    $fileListField->isArray = true;

    $SQL = 'SELECT path,contentFileTable,contentFileFK,maxFiles ';
    $SQL .= 'FROM LIB_Folder f, LIB_FileListField cif ';
    $SQL .= "WHERE tableName='".$this->table."' ";
    $SQL .= "AND fieldName='".$fieldDef['fieldName']."' ";
    $SQL .= 'AND f.folderId = cif.folderId ';
    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);
    $fileListField->attrs['path'] = $data['path'];
    $fileListField->attrs['maxFiles'] = $data['maxFiles'];

    // retrives file list
    if($keyFields != NULL)
    {
      $SQL = "SELECT fieldName,fileId as docId,value as title,contentType,";
      $SQL .= 'creationDate, lastChanged ';
      $SQL .= 'FROM '.$data['contentFileTable'].' ci, ';
      $SQL .= 'LIB_FileDocument d, LIB_Node n, LANG_Label l ';
      $SQL .= "WHERE tableName='".$this->table."' ";
      $SQL .= "AND fieldName='".$fieldDef['fieldName']."'";
      reset($keyFields);
      $SQL .= "AND ci.".$data['contentFileFK']."='".current($keyFields)."' ";
      $SQL .= 'AND fileId=d.docId AND d.docId=n.nodeId AND d.title=l.labelId ';
      $SQL .= "AND lang='".$this->lang."' ";
      $SQL .= 'ORDER BY idx';

      $fileListField->attrs['value'] = $this->dbDriver->GetAll($SQL);
    }    

    return $fileListField;
  }


  protected function &GetObjectListField(&$fieldDef,&$keyFields,$dateFormat,
					 $numberFormat,$fieldName,$short)
  {
    $objectListField = new AV_ModObject('object', $this->pageBuilder);
    $objectListField->attrs = & $fieldDef;
    $objectListField->isArray = true;

    $data = $this->GetObjectListInfo($fieldDef);
    $objectListField->attrs['path'] = $data['path'];

    $libDoc = new LIB_Document($this->module);
    $formObject = new FORM_Object('form', $this->pageBuilder); 

    $keyFieldsAux = NULL;
    $libDoc->GetForm($keyFieldsAux,$data['path'],NULL,NULL,$templates, 
		     $formObject,NULL,$dateFormat,$numberFormat,$short);

    // remove LIB_Node fields
    $objectListField->children = $formObject->children;
    $excludeFields = array('userId','groupId','userRight','groupRight',
			   'otherRight','creationDate','lastChanged',
			   'lastChangedUserId','keywords');
    foreach($objectListField->children as $key=>& $child)
      if(in_array($child->attrs['fieldName'],$excludeFields))
	unset($objectListField->children[$key]);

    // retrives object list data
    if($keyFields != NULL)
    {
      $search['path'] = $data['path'];
      $exlusionFilter['fieldName'] = $data['parentIdField'];
      $exlusionFilter['value'] = current($keyFields);
      $exlusionFilter['operator'] = '=';
      $search['exclusionFilters'][$data['parentIdField']][] = $exlusionFilter;
      $search['orderBy'] = 'itemOrder';
      $search['order'] = '1';
      $search['dateFormat'] = $dateFormat;
      $search['numberFormat'] = $numberFormat;
      $search['tempTable'] = '0';
      $objectListField->attrs['value'] = $libDoc->Search($search);
    }    
    else
      $objectListField->attrs['value'] = array();

    return $objectListField;
  }

  protected function &GetEmailListField(&$fieldDef,&$keyFields,&$templates)
  {
    $field = new AV_ModObject('file', $this->pageBuilder);
    $field->attrs = & $fieldDef;
    $field->isArray = true;

    $SQL = 'SELECT listTable, f.fieldName as keyField ';
    $SQL .= 'FROM FORM_EmailListField el, FORM_Field f ';
    $SQL .= "WHERE el.tableName='".$this->table."' ";
    $SQL .= "AND el.fieldName='".$fieldDef['fieldName']."' ";
    $SQL .= 'AND el.tableName = f.tableName ';
    $SQL .= "AND keyField = '1' ";

    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    // retrives email list
    if($keyFields != NULL)
    {
      reset($keyFields);
      $SQL = 'SELECT email FROM '.$data['listTable'].' WHERE ';
      $SQL .= $data['keyField']."='".current($keyFields)."' ";
      $SQL .= 'ORDER BY idx';

      $field->attrs['value'] = $this->dbDriver->GetCol($SQL);
    }    

    return $field;
  }

  protected function &GetFolderField(&$fieldDef, $values)
  {
    $modobj = new AV_ModObject('field', $this->pageBuilder);
    $modobj->attrs = & $fieldDef;

    if($values[$fieldDef['fieldName']]!=NULL)
    {
      $folders = explode('/',$values[$fieldDef['fieldName']]);
      if(count($folders)<2)
	throw new AV_Exception(INVALID_FIELD, $this->lang, 
			       Array('fieldName'=>$fieldDef['fieldName'],
				     'table'=>$this->table));
      // eats empty item
      array_pop($folders);

      $fieldDef['value'] = array_pop($folders);
      $fieldDef['folderPath'] = implode('/',$folders).'/';
    }
    return $modobj;
  }

  //--Method: GetFileField
  protected function GetFileField(& $fieldDef, $values)
  {
    $labelMan = new LANG_Label($this->module);
    $fieldDef['adminMaxFileSizeTag']=$labelMan->GetLabel('adminMaxFileSizeTag',
							 $this->lang);
    $fieldDef['maxFileSize'] = ini_get('upload_max_filesize');
    return $this->GetTextField($fieldDef, $values);
  }
  
  //--Method: GetUsageTermsField
  protected function GetUsageTermsField(& $fieldDef, $values)
  {
    if($values[$fieldDef['fieldName']]==NULL)
    {
      $libDoc = new LIB_Document($this->module);
      $params['path'] = '/content/'.$this->pageBuilder->siteConfig->getVar('cms','usageTermsPath');
      $params['orderBy'] = 'fixedOrder';
      $params['order'] = '1';
      $params['limit'] = 1;
      $rows = $libDoc->Search($params);

      if(count($rows)==0)
	throw new AV_Exception(NOT_FOUND, $this->lang, 
			       Array('fieldName'=>$fieldDef['fieldName']));
      
      $docId = $rows[0]['docId'];
    }
    else
      $docId = $values[$fieldDef['fieldName']];

    $label = $fieldDef['acceptUseTermsTag1'];
    $label .= " <a href='content/$docId' target='_blank'>";
    $label .= $fieldDef['acceptUseTermsTag2']."</a> ";
    $label .= $fieldDef['acceptUseTermsTag3'];
    $fieldDef['label'] = $label;
    $fieldDef['docId'] = $docId;
    return $this->GetTextField($fieldDef, $values);
  }

  protected function GetLEOrNPEntityRef(& $fieldDef, $values, $histEntityType)
  {
    $fieldDef['lang'] = $this->lang;

    $labelMan = new LANG_Label($this->module);
    $fieldDef['searchCriteriaTag'] = $labelMan->GetLabel('searchCriteriaTag',
							 $this->lang);

    $fieldDef['searchTag'] = $labelMan->GetLabel('searchTag',$this->lang);

    $fieldDef['newTag'] = $labelMan->GetLabel('newTag',$this->lang);

    $fieldDef['registerTag'] = $labelMan->GetLabel('registerTag',$this->lang);

    $fieldDef['changeSelectionTag'] = $labelMan->GetLabel('changeSelectionTag',
							  $this->lang);

    $fieldDef['legalEntityTag'] = $labelMan->GetLabel('legalEntityTag',
						      $this->lang);

    $fieldDef['naturalPersonTag'] = $labelMan->GetLabel('naturalPersonTag',
							$this->lang);

    if($values[$fieldDef['fieldName']] != NULL)
    {
      if($histEntityType == NULL)
      {
	$libDoc = new LIB_Document($this->module);
	$docData = $libDoc->GetDocData($values[$fieldDef['fieldName']],
				       'share');

	$histEntityType = $docData['type'];
      }
      switch($histEntityType)
      {
      case 'legalEntity':
	$table = new FORM_DynTable('EM_LegalEntity', $this->module);
	break;
      case 'naturalPerson':
	$table = new FORM_DynTable('EM_NaturalPerson', $this->module);
	break;
      }

      $table->LoadFieldsDef();

      $keyFields = Array('docId'=>$values[$fieldDef['fieldName']]);
      $legalEntityData = $table->Get($keyFields);

      switch($histEntityType)
      {
      case 'legalEntity':
	$cnpj_1 = mb_substr($legalEntityData['cnpj'],0,-6); 
	$cnpj_2 = mb_substr($legalEntityData['cnpj'],-6, 4);
	$cnpj_3 = mb_substr($legalEntityData['cnpj'],-2); 
	$fieldDef['id'] = $cnpj_1 . '/' . $cnpj_2 . '-' . $cnpj_3;
	$fieldDef['title'] = $legalEntityData['fantasyName'];
	break;
      case 'naturalPerson':
	$cpf_1 = mb_substr($legalEntityData['cpf'],0,-2); 
	$cpf_2 = mb_substr($legalEntityData['cpf'],-2); 
	$fieldDef['id'] = $cpf_1 . '-' . $cpf_2;
	$fieldDef['title'] = $legalEntityData['name'];
	break;
      }	
      $fieldDef['entityRefType'] = $histEntityType;
    }
    return $this->GetTextField($fieldDef, $values);
  }

  protected function GetNextFieldName($prefix)
  {
    $offset = mb_strlen($prefix) + 1;
    $SQL = "SELECT MAX(SUBSTR(fieldName,$offset)+0) ";
    $SQL .= 'FROM FORM_Field WHERE ';
    $SQL .= "tableName='" . $this->table . "' AND fieldName like '$prefix%' ";
    $SQL .= 'LOCK IN SHARE MODE';

    $num = $this->dbDriver->GetOne($SQL);
    if($num == NULL)
      return $prefix.'1';
    return $prefix . ++$num;
  }

  protected function LockFieldForUpdate($fieldName)
  {
    $SQL = 'SELECT * FROM  FORM_Field WHERE ';
    $SQL .= "tableName='".$this->table."' AND fieldName='$fieldName' ";
    $SQL .= 'FOR UPDATE ';
    $fieldDef = $this->dbDriver->GetRow($SQL);
    if(count($fieldDef) == 0)
      throw new AV_Exception(NOT_FOUND, $this->lang);
    $this->lockedField = $fieldDef;
    return $fieldDef;
  }

  protected function InsSelectField($fieldName, $optionTable, $optionTableKey,
				    $selectedTable, $sourceField, $targetField)
  {
    $SQL = 'INSERT INTO FORM_SelectField (tableName,fieldName,optionTable,';
    $SQL .= 'optionTableKey,selectedTable,sourceField,targetField) VALUES ';
    $SQL .= "('".$this->table."','$fieldName','$optionTable',";
    $SQL .= "'$optionTableKey','$selectedTable','$sourceField',";
    $SQL .= "'$targetField')";
    $this->dbDriver->ExecSQL($SQL);
  }

  protected function SetSelectField($fieldName, $optionTable, $optionTableKey,
				    $selectedTable, $sourceField, $targetField)
  {
    $SQL = 'SELECT * FROM FORM_SelectField WHERE ';
    $SQL .= "tableName='".$this->table."' AND fieldName='$fieldName' ";
    $SQL .= 'FOR UPDATE';
    $field = $this->dbDriver->GetRow($SQL);
    if(cont($field)==0)
       throw new AV_Exception(NOT_FOUND, $this->lang);

    $SQL = NULL;
    if($optionTable != NULL)
      $SQL .= "optionTable = '$optionTable'";

    if($optionTableKey != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "optionTableKey='$optionTableKey'";
    }

    if($selectedTable != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "selectedTable='$selectedTable'";
    }

    if($sourceField != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "sourceField='$sourceField'";
    }

    if($targetField != NULL)
    {
      if($SQL != NULL)
	$SQL .= ',';
      $SQL .= "targetField='$targetField'";
    }

    if($SQL == NULL)
      return;

    $SQL = 'UPDATE FORM_SelectField SET ' . $SQL;
    $SQL .= "tableName='".$this->table."' AND fieldName='$fieldName' ";
    $this->dbDriver->ExecSQL($SQL);
  }

  private function ConvertIf($expression)
  {
    $ctrl = array();
    $pos = 0;
    while($pos<strlen($expression))
    {      
      if(strtolower(substr($expression,$pos,2))=='if')
      {
	// remove if    
	$expression = substr_replace($expression,'',$pos,2);
	$ctrl[] = 'if';
	$pos++;
      }
      else if($expression[$pos]==',')
      {
	if(end($ctrl)=='if')
	{
	  $expression = substr_replace($expression,')?',$pos,1);
	  $ctrl[count($ctrl)-1] = 'then';
	  $pos+=2;
	}
	else
	{
	  $expression = substr_replace($expression,':',$pos,1);
	  $pos++;
	}
      }
      else if($expression[$pos]==')')
      {
	if(end($ctrl)=='then')
	  $expression = substr_replace($expression,'',$pos,1);
	else
	  $pos++; // )
	array_pop($ctrl);	  
      }
      else if($expression[$pos]=='(')
      {
	$ctrl[] = '(';
	$pos++;	
      }
      else if($expression[$pos]=='=')
      {
	$expression = substr_replace($expression,'==',$pos,1);
	$pos+=2;
      }
      else
	$pos++;
    }
    return $expression;
  }

  //--Method: GetFormulaField
  protected function &GetFormulaField(& $fieldDef, $values)
  {
    // campos com pow não são calculados na interface
    $found = (stristr($this->formulas[$fieldDef['fieldName']], 'pow') ||
	      stristr($this->formulas[$fieldDef['fieldName']], 'log'));
    if($found===false)
    {
      $fieldDef['expression']="SetNodeValue('".$fieldDef['fieldName']."',\\\"";
      $fieldDef['expression'].=$this->formulas[$fieldDef['fieldName']];
      $fieldDef['expression'].="\\\");";
    }
    $fieldDef['type'] = $this->formulaType[$fieldDef['fieldName']];
    $field = & $this->GetField($fieldDef, $values, NULL, $templates, NULL);
    $fieldDef['type'] = 'formula';
    $fieldDef['formulaType'] = $this->formulaType[$fieldDef['fieldName']];
    return $field;
  }


  //--Method: Decompose
  protected function Decompose(&$formulas, $formula, &$formulaFields, &$params)
  {
    $lex = new FORM_LexAnalyzer($formula);
    while($lex->NextToken())
    {
      if($lex->token == varTk)
      {
	// check if variable is a formula
	if(isset($formulaFields[$lex->value]))
	{
	  // check if the formula variable has already been decomposed
	  if(isset($formulas[$lex->value]))
	    $out .= '(' . $formulas[$lex->value] . ')';
	  else
	  {
	    // decompose formula variable
	    $out .= '(' . $this->Decompose($formulas, 
					   $formulaFields[$lex->value], 
					   $formulaFields, $params);
	    $out .= ')';
	  }
	}
	else
	  // prints normal variable
	  $out .= $this->PrintVar($lex->value, $params);
      }
      else
	$out .= $lex->value;
    }
    return $out;
  }

  protected function PrintVar($fieldName, & $params)
  {
    $out = "eval(StrToFloat(GetNodeValue('$fieldName'), '";
    $out .= $params['locale']['mon_decimal_point'] . "','";
    $out .= $params['locale']['mon_thousands_sep'] . "'))";
    return $out;
  }

  //--Method: LoadFormulaDefs
  protected function LoadFormulaDefs()
  {
    if($this->formulaFieldsDef != NULL)
      return $this->formulaFieldsDef;

    $SQL = 'SELECT * FROM FORM_Formula formula, FORM_Field field ';
    $SQL .= "WHERE formula.tableName = '" .$this->table. "' ";
    $SQL .= 'AND formula.tableName=field.tableName ';
    $SQL .= 'AND formula.fieldName=field.fieldName ';
    $SQL .= 'LOCK IN SHARE MODE ' ; 
    $this->formulaFieldsDef = $this->dbDriver->GetAll($SQL);     
    return $this->formulaFieldsDef;
  }

  protected function GetFieldLabel($fieldDef)
  {
    if($fieldDef['labelId'] != NULL)
    {
      $langLabel = new LANG_Label($this->module);
      return ' ('.$langLabel->GetLabel($fieldDef['labelId'],$this->lang).')';
    }
    else
      return ' ('.$fieldDef['fieldName'].')';
  }


  //
  //--Private
  //
  private function CheckIndexes($fields)
  {
    $rows = $this->GetViolatedIndexes($fields);

    if(count($rows) != 0)
    {
      $fieldDef = $this->GetViolatedIndexField($fields, $rows[0]);
      throw new AV_Exception(INDEX_VIOLATED, $this->lang,
			     array('fieldName'=>$fieldDef['fieldName'],
				   'addMsg'=>$this->GetFieldLabel($fieldDef)));
    }
  }
  


  private function GetViolatedIndexes($fields)
  {
    // compose index search SQL from dynamic table unique fields
    $firstField = true;
    $firstWhere = true;
    $firstKey = true;

    foreach($this->fieldsDef as $key => $field)
    {
      $fieldName = $field['fieldName'];
      if($field['keyField'] != '1')
      {
	if($field['uniq'] == '1' && 
	   $fields[$fieldName] !== NULL &&
	   $fields[$fieldName] !== '')
	{
	  if(!$firstField)
	    $FIELDS .= ', ';
	  else $firstField = false;

	  if(!$firstWhere)
	    $WHERE .= ' OR ';
	  else $firstWhere = false;
	
	  $FIELDS .= $fieldName;
	  $WHERE .= $fieldName . " = '";
	  $WHERE .= $fields[$fieldName] . "'";
	} // if($field['uniq'] == '1')
      } 
      else // $field['keyField'] != '0'
      {
	if(!$firstKey)
	  $WHERE2 .= ' OR ';
	else
	  $firstKey = false;
	$WHERE2 .= $fieldName . " <> '" . $fields[$fieldName] . "'";
      } // if($field['keyField'] == '0')
    } // foreach($this->fieldsDef as $field)


    if(isset($WHERE2))
      $WHERE = '(' . $WHERE . ') AND (' . $WHERE2 . ')';

    // searches for the index
    if($FIELDS !== NULL)
    {
      $SQL = 'SELECT ' . $FIELDS . ' FROM ' . $this->table;
      $SQL .= ' WHERE ' . $WHERE . ' LOCK IN SHARE MODE ';
      $rows = $this->dbDriver->GetAll($SQL);
    }

    return $rows;
  }


  private function GetViolatedIndexField($fields, & $row)
  {
    // searches violated index from dynamic table unique fields
    foreach($this->fieldsDef as $field)
    {
      if($field['uniq'] == '1')
      {
	$fieldName = $field['fieldName'];
	if(isset($row[$fieldName]) && $row[$fieldName] === $fields[$fieldName])
	  return $field;
      }
    }

    throw new AV_Exception(FATAL_ERROR, $this->lang);
  }

  private function SetOptions(& $options, $fieldDef, $fieldValue, $fieldKey)
  {
    if($options == '')
      return;

    $field = $this->GetSelectFieldInfo($fieldDef['fieldName']);
      
    if($fieldValue != 'LAST_INSERT_ID()')
    {
      $SQL = 'DELETE FROM '.$field['selectedTable']. ' WHERE '.$fieldKey.' = ';
      $SQL .= $fieldValue;
      $this->dbDriver->ExecSQL($SQL);
    }
    $SQL = 'INSERT INTO '.$field['selectedTable'].' ('.$fieldKey;
    $SQL .= ', '.$field['optionTableKey'].') VALUES ';
    $first = true;
    foreach ($options as $opt)
    {
      if($opt['value'] != NULL)
      {
	if($first) 
	  $first = false;
	else 
	  $SQL .= ', ';
	$SQL .="(".$fieldValue.",".$opt['value'].")";
      }
    }
    if($first)
    {
      if($fieldDef['required'])
      {
	throw new AV_Exception(FIELD_REQUIRED,$this->lang,
			       array('fieldName'=>$fieldDef['fieldName'],
				     'addMsg'=>$this->GetFieldLabel($fieldDef)));
      }
      return;
    }
    $this->dbDriver->ExecSQL($SQL);
  }

  private function SetSelectMultiple(& $options, $fieldDef, $keyValue, $fieldKey)
  {
    if($options == NULL)
      return;

    $field = $this->GetSelectFieldInfo($fieldDef['fieldName']);
      
    if($keyValue != 'LAST_INSERT_ID()')
    {
      $SQL = 'DELETE FROM '.$field['selectedTable']. ' WHERE '.$fieldKey.' = ';
      $SQL .= $keyValue;
      $this->dbDriver->ExecSQL($SQL);
    }
    $SQL = 'INSERT INTO '.$field['selectedTable'].' ('.$fieldKey;
    $SQL .= ', '.$field['optionTableKey'].') VALUES ';
    $first = true;
    foreach ($options as $opt)
    {
      if($opt != NULL)
      {
	if($first) 
	  $first = false;
	else 
	  $SQL .= ', ';
	$SQL .="(".$keyValue.",".$opt.")";
      }
    }
    if($first)
    {
      if($fieldDef['required'])
      {
	throw new AV_Exception(FIELD_REQUIRED,$this->lang,
			       array('fieldName'=>$fieldDef['fieldName'],
				     'addMsg'=>$this->GetFieldLabel($fieldDef)));
      }
      return;
    }
    $this->dbDriver->ExecSQL($SQL);
  }

  private function DuplicateOptions($sourceKey, $fieldDef)
  {
    $field = $this->GetSelectFieldInfo($fieldDef['fieldName']);
      
    $SQL = 'INSERT INTO '.$field['selectedTable'].' ('.$sourceKey['fieldName'];
    $SQL .= ', '.$field['optionTableKey'].') SELECT LAST_INSERT_ID()';
    $SQL .= ", ".$field['optionTableKey']." FROM ".$field['selectedTable'];
    $SQL .= ' WHERE '.$sourceKey['fieldName']."='".$sourceKey['fieldValue']."'";

    $this->dbDriver->ExecSQL($SQL);
  }

  private function SetFileList(& $options, $fieldDef, $fieldValue)
  {
    if($options == '')
      return NULL;

    $SQL = 'SELECT contentFileTable,contentFileFK,maxFiles ';
    $SQL .= 'FROM LIB_FileListField cif ';
    $SQL .= "WHERE tableName='".$this->table."' ";
    $SQL .= "AND fieldName='".$fieldDef['fieldName']."' ";
    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    $table = $data['contentFileTable'];
    $tableField = $data['contentFileFK'];

    // remove old files
    if ($fieldValue != 'LAST_INSERT_ID()')
    {
      $SQL = "SELECT fileId ";
      $SQL .= "FROM $table ci ";
      $SQL .= "WHERE tableName='".$this->table."' ";
      $SQL .= "AND fieldName='".$fieldDef['fieldName']."'";
      $SQL .= "AND $tableField='$fieldValue' ";
      $SQL .= 'ORDER BY idx';
      $oldValues = $this->dbDriver->GetCol($SQL);

      $SQL = "DELETE FROM $table ";
      $SQL .= "WHERE tableName='".$this->table."' ";
      $SQL .= "AND fieldName='".$fieldDef['fieldName']."' ";
      $SQL .= "AND $tableField='$fieldValue'";
      $this->dbDriver->ExecSQL($SQL);
    }

    $SQL = "INSERT INTO $table ";
    $SQL .= "(tableName,fieldName,$tableField,fileId,idx) VALUES ";

    $first = true;
    foreach ($options as $key=>$opt)
    {
      if($opt['docId'] != NULL)
      {
	if($first) 
	  $first = false;
	else 
	  $SQL .= ',';
	$SQL.="('".$this->table."','".$fieldDef['fieldName']."',$fieldValue,";
	$SQL.=$opt['docId'].",$key)";
      }
    }
    if(!$first)
      $this->dbDriver->ExecSQL($SQL);
    return $oldValues;
  }

  private function SetObjectList(& $objects, $fieldDef, $fieldValue, $fieldKey,$new)
  {
    if($objects == '')
      return NULL;

    $data = $this->GetObjectListInfo($fieldDef);
    $libDoc = new LIB_Document($this->module);

    // read current object list
    if(!$new)
    {
      $formObject = new FORM_Object('form',$this->pageBuilder,$templates);
      $keyFields[$fieldKey] = $fieldValue;
      $libDoc->GetForm($keyFields,$data['path'],$fieldDef['fieldName'],NULL,
		       $templates,$formObject);

      if(count($formObject->children) != 1)
	throw new AV_Exception(FAILED, $this->lang);
      $objectList = current($formObject->children);
    }

    foreach($objects as $key=>& $obj)
    {
      $obj['itemOrder'] = $key;
      if($obj['docId']!=NULL)
      {
	// remove docId from current object list, so it won't be removed
	foreach($objectList->attrs['value'] as $key=>$value)
	  if($value['docId']==$obj['docId'])
	    unset($objectList->attrs['value'][$key]);
      }

      // /insert/set object
      $libDoc = new LIB_Document($this->module);
      $libDoc->LoadFieldsDef($obj['docId'],$data['path']);
      $obj[$data['parentIdField']] = $fieldValue;
      if($obj['docId']!=NULL)
	$libDoc->Set($obj);
      else
      {
	$obj['path'] = $data['path'];
	$libDoc->Insert($obj);
      }

    }

    // removes unlisted objects
    if(!$new)
    {
      foreach($objectList->attrs['value'] as $value)
	$libDoc->DelDocLink($data['path'],$value['docId']);
      return array_values($objectList->attrs['value']);
    }
    return NULL;
  }


  private function SetEmailList(& $options, $fieldDef, $fieldValue)
  {
    if($options == '')
      return NULL;

    $SQL = 'SELECT listTable, f.fieldName as keyField ';
    $SQL .= 'FROM FORM_EmailListField el, FORM_Field f ';
    $SQL .= "WHERE el.tableName='".$this->table."' ";
    $SQL .= "AND el.fieldName='".$fieldDef['fieldName']."' ";
     $SQL .= 'AND el.tableName = f.tableName ';
    $SQL .= "AND keyField = '1' ";

    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    // remove old emails
    if ($fieldValue != 'LAST_INSERT_ID()')
    {
      $SQL = 'SELECT email FROM '.$data['listTable'].' ';
      $SQL .= 'WHERE '.$data['keyField']."='".$fieldValue."' ";
      $SQL .= 'ORDER BY idx';
      $oldValues = $this->dbDriver->GetCol($SQL);

      $SQL = 'DELETE FROM '.$data['listTable'].' ';
      $SQL .= 'WHERE '.$data['keyField']."='".$fieldValue."' ";
      $this->dbDriver->ExecSQL($SQL);
    }

    $SQL = 'INSERT INTO '.$data['listTable'];
    $SQL .= '('.$data['keyField'].',email,idx) VALUES ';

    $first = true;
    foreach ($options as $key=>$opt)
    {
      if($first) 
	$first = false;
      else 
	$SQL .= ',';
      $SQL.="('$fieldValue','".$opt."',$key)";
    }
    if(!$first)
      $this->dbDriver->ExecSQL($SQL);
    return $oldValues;
  }

  private function DuplicateFileList($sourceKey, $fieldDef)
  {

    $SQL = 'SELECT contentFileTable,contentFileFK,maxFiles ';
    $SQL .= 'FROM LIB_FileListField cif ';
    $SQL .= "WHERE tableName='".$this->table."' ";
    $SQL .= "AND fieldName='".$fieldDef['fieldName']."' ";
    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    $table = $data['contentFileTable'];
    $tableField = $data['contentFileFK'];

    $SQL = "INSERT INTO $table ";
    $SQL .= "(tableName,fieldName,$tableField,fileId,idx) SELECT ";
    $SQL .= "tableName,fieldName,LAST_INSERT_ID(),fileId,idx ";
    $SQL .= "FROM $table ";
    $SQL .= "WHERE tableName='".$this->table."' ";
    $SQL .= "AND fieldName='".$fieldDef['fieldName']."' ";
    $SQL .= "AND $tableField='".$sourceKey['fieldValue']."'";
    $this->dbDriver->ExecSQL($SQL);   
  }

  private function DuplicateEmailList($sourceKey, $fieldDef)
  {
    $SQL = 'SELECT listTable, f.fieldName as keyField ';
    $SQL .= 'FROM FORM_EmailListField el, FORM_Field f ';
    $SQL .= "WHERE el.tableName='".$this->table."' ";
    $SQL .= "AND el.fieldName='".$fieldDef['fieldName']."' ";
     $SQL .= 'AND el.tableName = f.tableName ';
    $SQL .= "AND keyField = '1' ";

    $data = $this->dbDriver->GetRow($SQL);
    if(count($data) == NULL)
      throw new AV_Exception(NOT_FOUND, $this->lang);

    $SQL = 'INSERT INTO '.$data['listTable'];
    $SQL .= '('.$data['keyField'].',email,idx) SELECT ';
    $SQL .= 'LAST_INSERT_ID(),email,idx ';
    $SQL .= 'FROM '.$data['listTable'].' ';
    $SQL .= 'WHERE '.$data['keyField']."='".$sourceKey['fieldValue']."' ";
    $this->dbDriver->ExecSQL($SQL);   
  }

  private function SetSchedule(& $schedule, $fieldDef, $fieldValue, $fieldKey)
  {
    if($schedule == '')
      return;

    // remove old files
    if ($fieldValue != 'LAST_INSERT_ID()')
    {
      $SQL = "DELETE FROM AV_Schedule ";
      $SQL .= "WHERE docId='$fieldValue' ";
      $this->dbDriver->ExecSQL($SQL);
    }

    $SQL = "INSERT INTO AV_Schedule ";
    $SQL .= "(docId, start, numOfHours) VALUES ";

    $first = true;
    foreach ($schedule as $key=>$item)
    {
      if($item['start'] != NULL && $item['numOfHours'] != NULL)
      {
	if($first) 
	  $first = false;
	else 
	  $SQL .= ',';
	$SQL.="($fieldValue,'".$item['start']."','".$item['numOfHours']."')";
      }
    }
    if($first)
    {
      if($fieldDef['required'])
	throw new AV_Exception(FIELD_REQUIRED,$this->lang,
			       array('fieldName'=>$fieldDef['fieldName'],
				     'addMsg'=>$this->GetFieldLabel($fieldDef)));
    }
    else
      $this->dbDriver->ExecSQL($SQL);
  }

  private function SetLabel($fieldDef, $fields, $labelName, $fieldKey)
  {
    if(count($fields)==0)
      return;

    $labelMan = new LANG_Label($this->module);
    if($labelName == 'LAST_INSERT_ID()')
    {
      $SQL = "SELECT LAST_INSERT_ID() FROM DUAL"; 
      $returnlabelId = $this->dbDriver->GetOne($SQL);
    }
    else
      $returnlabelId = $labelName;

    $labelId = $this->table."_".$fieldDef['fieldName']."_".$returnlabelId;
    
    $labelMan->SetLabel($labelId,$fields,$fieldDef['type']!='langLabel');
    
    $WHERE = " WHERE ".$fieldKey." = '".$returnlabelId."'";
    $SQL = 'SELECT * FROM ' . $this->table . $WHERE . ' FOR UPDATE';
    $this->dbDriver->GetRow($SQL); 
    
    $SQL = "UPDATE " .$this->table. " SET " . $fieldDef['fieldName']." = '";
    $SQL .= $labelId. "' WHERE ".$fieldKey." = '".$returnlabelId."'";
    
    $this->dbDriver->ExecSQL($SQL);
   
  }

  private function DuplicateLabel($sourceKey, $fieldDef)
  {

    $labelMan = new LANG_Label($this->module);
    $SQL = "SELECT LAST_INSERT_ID() FROM DUAL"; 
    $returnlabelId = $this->dbDriver->GetOne($SQL);

    $sourceLabelId = $this->table."_".$fieldDef['fieldName']."_".$sourceKey['fieldValue'];
    $labelId = $this->table."_".$fieldDef['fieldName']."_".$returnlabelId;
    
    $labelMan->DuplicateLabel($sourceLabelId,$labelId,$fieldDef['type']!='langLabel');

    $WHERE = " WHERE ".$sourceKey['fieldName']." = '".$returnlabelId."'";
    $SQL = 'SELECT * FROM ' . $this->table . $WHERE . ' FOR UPDATE';
    $this->dbDriver->GetRow($SQL); 
    
    $SQL = "UPDATE " .$this->table. " SET " . $fieldDef['fieldName']." = '";
    $SQL .= $labelId. "' WHERE ".$sourceKey['fieldName']." = '".$returnlabelId."'";
    
    $this->dbDriver->ExecSQL($SQL);
   
  }

  private function SetFieldLabels(& $fieldDef)
  {
    $SQL = 'SELECT l.labelId, value FROM FORM_FieldLabels fl, LANG_Label l ';
    $SQL .= "WHERE tableName = '" . $this->table . "' AND ";
    $SQL .= "fieldName = '" . $fieldDef['fieldName'] . "' AND ";
    $SQL .= "fl.labelId = l.labelId AND ";
    $SQL .= "lang = '".$this->lang."'";
    $fieldLabels = $this->dbDriver->GetAll($SQL);

    foreach($fieldLabels as $key=>$label)
      $fieldDef[$label['labelId']] = $label['value'];
  }

  private function SetFile(&$fieldDef,&$value,&$extraFields)
  {
    $extraFields[$fieldDef['fieldName'].'FileName'] = "'".$value['name']."'";
    $extraFields[$fieldDef['fieldName'].'Type'] = "'".$value['type']."'";

    if(!$this->pageBuilder->siteConfig->getVar('admin','blobToFS'))
    {
    
      $data = file_get_contents($value['tmp_name']);
      if(!$data)
	throw new AV_Exception(FAILED, $this->lang,
			       array('addMsg'=>' (unable to open file)',
				     'tmp_name'=>$value['tmp_name']));
      $extraFields[$fieldDef['fieldName'].'Size'] = "'".
	$this->PrepareFileSize(strlen($data))."'";

      switch($this->dbDriver->type)
      {
      case 'mysql':
	$data = "'".addslashes($data)."'";
	
	$extraFields[$fieldDef['fieldName']] = & $data;
	break;
	
      case 'oracle':
	$extraFields[$fieldDef['fieldName']] = 'EMPTY_BLOB()';
	$value['content'] = $data;
	break;
	
      }
    }
    else
    {
      $libPath = $this->pageBuilder->siteConfig->getVar("dirs", 
							"library");
      $this->dbDriver->ExecSQL('INSERT INTO LIB_BlobFS VALUES ()');
      $blobId = $this->dbDriver->GetOne('SELECT LAST_INSERT_ID() FROM DUAL');
      if(!ft_rename($value['tmp_name'], $libPath . $blobId))
	throw new AV_Exception(FAILED, $this->lang,
			       array('addMsg'=>' (unable to rename file)',
				     'tmp_name'=>$value['tmp_name']));
      
      $extraFields[$fieldDef['fieldName']] = $blobId;
    }
  }

  private function PrepareFileSize($fileSize)
  {
    if($fileSize >= 1073741824) 
      $ret = number_format(($fileSize / 1073741824),3) . " GB";
    elseif ($fileSize >= 1048576) 
      $ret = number_format(($fileSize / 1048576),2) . " MB";
    elseif ($fileSize >= 1024)
      $ret = number_format(($fileSize / 1024),0) . " KB";
    elseif ($fileSize >= 0)
      $ret = $fileSize . " B";
    else
      $ret = "0 B";
    return $ret;
  }
}

?>