function createJoinsSelectsForDDLS($fields, $bAsLeftJoin, &$lNumDDLs, &$strJoins, &$strDDLFields, &$strDDLRecSetFNs)
{
//---------------------------------------------------------------------
// This routine creates the Joins and Selects to load the DDL values
// from a personalized table.
//
// $fields is a pointer to the fields table of a schema:
// $this->saiSchema = new muser_schema;
// $this->saiSchema->loadUFSchemaViaAttachTypeUserTabName(CENUM_CONTEXT_CLIENT, 'Shift Ascension Intake', $this->lSAITableID);
// $this->saiTableSchema = &$this->saiSchema->schema[$this->lSAITableID];
// $saiFields = $this->saiTableSchema->fields;
//
// createJoinsSelectsForDDLS($saiFields, true, $lNumDDLs, $strJoins, $strDDLFields, $strDDLRecSetFNs);
//---------------------------------------------------------------------
$lNumDDLs = 0;
$strJoins = '';
$strDDLFields = '';
$strDDLRecSetFNs = array();
foreach ($fields as $field) {
if ($field->enumFieldType == CS_FT_DDL) {
$strFNInternal = $field->strFieldNameInternal;
$strJoinTableAs = 'ddl_' . $strFNInternal;
$strJoins .= ($bAsLeftJoin ? 'LEFT ' : 'INNER') . " JOIN uf_ddl AS {$strJoinTableAs} ON {$strJoinTableAs}.ufddl_lKeyID = {$strFNInternal} \n";
$strDDLFields .= ",\n {$strJoinTableAs}.ufddl_strDDLEntry AS `" . strEscMysqlQuote($field->strFieldNameUser) . "` ";
$strDDLRecSetFNs[$lNumDDLs] = $field->strFieldNameUser;
++$lNumDDLs;
}
}
}
function strSelectTermViaFieldInfo($field)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$strField = '';
if ($field->lTableID > 0) {
$strField .= strtoupper(substr($field->enumParentTable, 0, 1)) . substr($field->enumParentTable, 1) . ':';
}
$strField .= $field->strUserTableName . ':' . $field->strUserFN;
return strEscMysqlQuote($strField);
}
public function strCreateTempTableUFExport(&$fields, $strTableName, &$sqlDelete, &$strFieldNamesSel)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$sqlDelete = "DROP TABLE IF EXISTS {$strTableName} ;";
$sqlStr = "\n CREATE TEMPORARY TABLE {$strTableName}(\n tmp_lKeyID int(11) NOT NULL AUTO_INCREMENT,\n tmp_lForeignID int(11) NOT NULL DEFAULT '0' ";
$idx = 0;
$strFieldNamesSel = '';
foreach ($fields as $field) {
$enumFT = $field->enumFieldType;
if ($enumFT != CS_FT_HEADING) {
$strFN = 'field_' . str_pad($idx, 4, '0', STR_PAD_LEFT);
$strFieldNamesSel .= ', ' . $strFN . ' AS `' . strEscMysqlQuote($field->pff_strFieldNameUser) . '` ';
}
switch ($enumFT) {
case CS_FT_CHECKBOX:
$sqlStr .= ",\n {$strFN} varchar(3) DEFAULT '' ";
break;
case CS_FT_DATE:
$sqlStr .= ",\n {$strFN} date NOT NULL DEFAULT '0000-00-00' ";
break;
case CS_FT_TEXT255:
case CS_FT_DDL:
$sqlStr .= ",\n {$strFN} varchar(255) DEFAULT '' ";
break;
case CS_FT_TEXT80:
$sqlStr .= ",\n {$strFN} varchar(80) DEFAULT '' ";
break;
case CS_FT_TEXT20:
$sqlStr .= ",\n {$strFN} varchar(20) DEFAULT '' ";
break;
case CS_FT_INTEGER:
$sqlStr .= ",\n {$strFN} int(11) DEFAULT '0' ";
break;
case CS_FT_CURRENCY:
$sqlStr .= ",\n {$strFN} decimal(10,2) DEFAULT '0.00' " . ",\n {$strFN}" . "ACO varchar(80) DEFAULT '' ";
break;
case CS_FT_LOG:
case CS_FT_TEXTLONG:
$sqlStr .= ",\n {$strFN} text NOT NULL ";
break;
case CS_FT_HEADING:
break;
default:
screamForHelp($enumFT . ': invalid field type<br>error on line ' . __LINE__ . ',<br>file ' . __FILE__ . ',<br>function ' . __FUNCTION__);
break;
}
if ($enumFT != CS_FT_HEADING) {
++$idx;
}
}
$sqlStr .= " ,\n PRIMARY KEY (tmp_lKeyID),\n KEY tmp_lForeignID (tmp_lForeignID)\n ) ENGINE=MyISAM;";
return $sqlStr;
}
function sqlViaTableFields($lTableID, $parentTable, &$sqlParts, $strFNPrefix = '', $bExcludeDDL = false, $bExcludeMultiDDL = false, $bIncludeHidden = false)
{
//---------------------------------------------------------------------
//
//---------------------------------------------------------------------
$sqlParts = new stdClass();
$strFieldPrefix = $this->schema[$lTableID]->strFieldPrefix;
$strFNForeignID = $this->schema[$lTableID]->strDataTableFID;
$sqlParts->strSELECT = $this->schema[$lTableID]->strDataTableKeyID . ', ' . $strFNForeignID . ' ';
$sqlParts->lNumRSFields = 0;
$sqlParts->rsFields = array();
if ($this->schema[$lTableID]->lNumFields > 0) {
$fields =& $this->schema[$lTableID]->fields;
foreach ($fields as $field) {
$enumFT = $field->enumFieldType;
$bAddField = true;
if ($field->bHidden && !$bIncludeHidden) {
$bAddField = false;
} elseif ($enumFT == CS_FT_HEADING) {
$bAddField = false;
} elseif ($enumFT == CS_FT_DDL) {
if ($bExcludeDDL) {
$bAddField = false;
}
} elseif ($enumFT == CS_FT_DDLMULTI) {
if ($bExcludeMultiDDL) {
$bAddField = false;
}
}
if ($bAddField) {
$sqlParts->strSELECT .= ", \n" . $field->strFieldNameInternal . ' AS `' . $strFNPrefix . strEscMysqlQuote($field->strFieldNameUser) . '`';
$sqlParts->rsFields[$sqlParts->lNumRSFields] = new stdClass();
$spfield =& $sqlParts->rsFields[$sqlParts->lNumRSFields];
$spfield->strFieldNameInternal = $field->strFieldNameInternal;
$spfield->lFieldID = $field->lFieldID;
$spfield->strFieldNameUser = $field->strFieldNameUser;
++$sqlParts->lNumRSFields;
}
}
}
$sqlParts->strINNER = ' INNER JOIN ' . $this->schema[$lTableID]->strDataTableName . " ON {$parentTable->strFNKeyID} = {$strFNForeignID} ";
}