/**
* Generate a list of FROM and JOIN commands out of the
* processed YAML page description. Assume the first entry
* is the FROM table and that all entries will join to
* something above them.
*
* @param array $yamlP2 The processed page description
* @access private
*/
function genSQLFromJoins($yamlP2)
{
// Get the list of tables and pop off the first one
$tables = array_keys($yamlP2['table']);
$SQL_from = array_shift($tables);
$SQL_Joins = array();
$tables_done = array($SQL_from);
// Loop through children looking for which of the
// parents they can join to
foreach ($tables as $table) {
$dd1 = ddTable($table);
$table_par = ArraySafe($yamlP2['table'][$table], 'table_par', '');
$table_chd = $table;
$table_join = $table_par;
if ($table_par == '') {
foreach ($tables_done as $table_done) {
if (isset($dd1['fk_parents'][$table_done])) {
$table_par = $table_done;
$table_chd = $table;
break;
} elseif (isset($dd1['fk_children'][$table_done])) {
// Cause the loop to tstop
$table_par = $table;
$table_chd = $table_done;
break;
}
}
}
if ($table_par == '') {
$this->errorAdd("Table {$table} does not join to any " . "previously listed table.");
} else {
$tables_done[] = $table;
$dd = ddTable($table_par);
$apks = explode(',', $dd['pks']);
$apks2 = array();
foreach ($apks as $apk) {
$apks2[] = "{$table_chd}.{$apk} = {$table_par}.{$apk}";
}
$SQL_Joins[$table] = array("expression" => implode(' AND ', $apks2), 'view' => $dd['viewname'], 'left_join' => ArraySafe($yamlP2['table'][$table]['left_join'], 'N'));
}
}
// Now join them all up and return
$view_id = ddView($SQL_from);
$retval = "\n FROM {$view_id} {$SQL_from} ";
foreach ($SQL_Joins as $table_id => $SQL_Join) {
$view_id = ddView($table_id);
#$view_id = $SQL_Join['view'];
$expr = $SQL_Join['expression'];
$left = $SQL_Join['left_join'] == 'Y' ? 'LEFT ' : '';
$retval .= "\n {$left}JOIN {$view_id} {$table_id} ON {$expr}";
}
return $retval;
}
function mainLayout($container)
{
# Erase default help message
vgfSet('htmlHelp', '');
$top = $container;
# Pull the values
# For a user, if there is no row, enter one and try again
$dd = ddTable("configuser");
$view = ddView('configuser');
$row = SQL_AllRows("Select * from {$view}");
if (count($row) == 1) {
$row = $row[0];
} else {
SQL("Insert into {$view} (skey_quiet) values ('N')");
$row = SQL_OneRow("Select * from {$view}");
}
# Basic information at top
html('h1', $top, 'User Configuration');
html('p', $top, 'Any changes made here will take immediate
effect.');
# Set up titles
$table = html('table', $top);
$thead = html('thead', $table);
$tr = html('tr', $thead);
$tr->h('th', 'Setting', 'dark');
$tr->h('th', 'Your Value', 'dark');
$tr->h('th', 'Default Value', 'dark');
$tr->h('th', ' ', 'dark');
# Now put out inputs for each one
$tbody = html('tbody', $table);
$askip = array('recnum', '_agg', 'skey_quiet', 'skey', 'uid_ins');
foreach ($this->flat as $column_id => $colinfo) {
if (in_array($column_id, $askip)) {
continue;
}
$tr = html('tr', $tbody);
$tr->hp['id'] = 'tr_' . $column_id;
$tr->SetAsParent();
$td = html('td', $tr, $colinfo['description']);
# the input
$input = input($colinfo);
$input->hp['id'] = 'inp_' . $column_id;
if ($colinfo['type_id'] == 'text') {
$input->setHTML($row[$column_id]);
} else {
$input->hp['value'] = $row[$column_id];
x4Script('$a.byId("' . $input->hp['id'] . '").value="' . $row[$column_id] . '"');
}
$input->hp['onchange'] = 'instaSave(this)';
$input->ap['skey'] = $row['skey'];
$td = html('td', $tr);
$td->addChild($input);
# The default value
$td = html('td', $tr, ConfigGet($column_id, '*null*', array('user')));
$td->hp['id'] = 'def_' . $column_id;
# The reset
$td = html('td', $tr);
$button = html('a-void', $td, 'Use Default');
$button->hp['onclick'] = "makeDefault('{$column_id}')";
}
}
/**
* Generate search results for an x4browse/search
*
* @author: Kenneth Downs
*/
function browseFetch()
{
# This is the list of columns to return
$acols = explode(',', $this->dd['projections']['_uisearch']);
# By default the search criteria come from the
# variables, unless it is a child table search
$vals = aFromGP('x4w_');
$awhere = array();
$tabPar = gp('tableIdPar');
if ($tabPar != '') {
$ddpar = ddTable(gp('tableIdPar'));
$pks = $ddpar['pks'];
$stab = ddView(gp('tableIdPar'));
$skey = SQLFN(gp('skeyPar'));
$vals2 = SQL_OneRow("SELECT {$pks} FROM {$stab} WHERE skey = {$skey}");
if (!$vals2) {
$vals2 = array();
}
$vals = array_merge($vals, $vals2);
}
# Build the where clause
#
foreach ($vals as $column_id => $colvalue) {
if (!isset($this->flat[$column_id])) {
continue;
}
$colinfo = $this->flat[$column_id];
$exact = isset($vals2[$column_id]);
//$tcv = trim($colvalue);
$tcv = $colvalue;
$type = $colinfo['type_id'];
if ($tcv != "") {
// trap for a % sign in non-string
$xwhere = sqlFilter($this->flat[$column_id], $tcv);
if ($xwhere != '') {
$awhere[] = "({$xwhere})";
}
}
}
# <----- RETURN
if (count($awhere) == 0) {
x4Debug("returning");
return;
}
# Generate the limit
# KFD 11/12/08, modified to respect sql_limit, with default of 100
$SLimit = ' LIMIT ' . configGet('sql_limit', 100);
if ($tabPar != '') {
if (a($this->dd['fk_parents'][$tabPar], 'uiallrows', 'N') == 'Y') {
$SLimit = '';
}
}
# Build the Order by
#
$ascDesc = gp('sortAD') == 'ASC' ? ' ASC' : ' DESC';
$aorder = array();
$searchsort = trim(a($this->dd, 'uisearchsort', ''));
if (gpExists('sortAD')) {
$aorder[] = gp('sortCol') . ' ' . gp('sortAD');
}
if ($searchsort != '') {
$aocols = explode(",", $searchsort);
foreach ($aocols as $pmcol) {
$char1 = substr($pmcol, 0, 1);
$column_id = substr($pmcol, 1);
if ($char1 == '+') {
$aorder[] = $column_id . ' ASC';
} else {
$aorder[] = $column_id . ' DESC';
}
}
$SQLOrder = " ORDER BY " . implode(',', $aorder);
} else {
# KFD 6/18/08, new routine that works out sort
$aorder = sqlOrderBy($vals);
if (count($aorder) == 0) {
$SQLOrder = '';
} else {
$SQLOrder = " ORDER BY " . implode(',', $aorder);
}
}
# just before building the query, drop out
# any columns that have a table_id_fko to the parent
foreach ($acols as $idx => $column_id) {
if ($this->flat[$column_id]['table_id_fko'] == $tabPar && $tabPar != '') {
unset($acols[$idx]);
}
}
// Build the where and limit
$SWhere = ' WHERE ' . implode(' AND ', $awhere);
// Retrieve data
$SQL = "SELECT skey," . implode(',', $acols) . " FROM " . $this->view_id . $SWhere . $SQLOrder . $SLimit;
$answer = SQL_AllRows($SQL);
$this->browseFetchModify($answer);
x4Data('browseFetch', $answer);
return;
}
function DDTable_IDResolve($table_id)
{
return ddView($table_id);
// Both super user and nobody get original table
if (SessionGet("ROOT")) {
return $table_id;
}
// KFD 1/23/08. This probably should never have been here,
// since it would always return an unusable answer.
//
//if(!LoggedIn()) {
// return $table_id;
//}
$ddTable = dd_TableRef($table_id);
// This is case of nonsense table, give them back original table
if (count($ddTable) == 0) {
return $table_id;
}
//echo "permspec is: ".$ddTable['permspec'];
$views = ArraySafe($ddTable, 'tableresolve', array());
if (count($views) == 0) {
return $table_id;
} else {
// KFD 1/23/08. This code takes advantage of the fact that
// the public user by itself is always the very last
// effective group. Therefore, if a user is not logged
// in, we will take the very last entry, assuming that it
// gives the answer for somebody who is only in one group.
//
if (LoggedIn()) {
return $views[SessionGet('GROUP_ID_EFF')];
} else {
return array_pop($views);
}
}
}
function fetchParent()
{
$ddpar = ddTable(gp('tableIdPar'));
$pks = $ddpar['pks'];
$stab = ddView(gp('tableIdPar'));
$skey = SQLFN(gp('skeyPar'));
$vals2 = SQL_OneRow("SELECT {$pks} FROM {$stab} WHERE skey = {$skey}");
if (!$vals2) {
$vals2 = array();
}
return $vals2;
}
function x4sqlUpd($table, $row, $whr)
{
if (!isset($whr['skey'])) {
x4Error("The database update cannot be performed because the" . " 'skey' column is not present. This is very likely a" . " programming error, please contact your programmer.");
} else {
$view = ddView($table);
$row = array_merge($row, $whr);
SQLX_Update($table, $row);
}
}