function RepStandardQuery() { $sreport = SQLFC($this->report_id); // Now the tables $rows_tab = SQL_AllRows("SELECT * From reporttables WHERE report={$sreport}"); $rows_tab = KeyRowsFromRows($rows_tab, 'table_id'); // Now all columns $rows_col = SQL_AllRows("SELECT * From reportcolumns WHERE report={$sreport}\n ORDER BY uicolseq "); $this->rows_col = $rows_col; foreach ($this->rows_col as $key => $colinfo) { $table_dd = dd_Tableref($colinfo['table_id']); $column_id = $colinfo['column_id']; if (intval($colinfo['dispsize']) == 0) { $this->rows_col[$key]['dispsize'] = $table_dd['flat'][$column_id]['dispsize']; } } // Make two header lines out of column information $aTemp = array(); foreach ($this->rows_col as $colinfo) { $x = substr($colinfo['description'], 0, $colinfo['dispsize']); $aTemp[] = str_pad($x, $colinfo['dispsize'], ' ', STR_PAD_RIGHT); } $this->hTitles = implode(' ', $aTemp); $aTemp = array(); foreach ($this->rows_col as $colinfo) { $aTemp[] = str_repeat('=', $colinfo['dispsize']); } $this->hDashes = implode(' ', $aTemp); $this->hWidth = strlen($this->hDashes); $this->hTitle = str_repeat(' ', intval(($this->hWidth - strlen($this->hTitle)) / 2)) . $this->hTitle; // Go get the joins $SQL_FROMJOINS = $this->ehProcessFromJoins(array_keys($rows_tab)); // Build a list of columns, and order-by columns, and filters' $this->Cols = array(); $SQL_COLSA = array(); $SQL_COLSOBA = array(); $SQL_COLSWHA = array(); foreach ($rows_col as $row_col) { $this->Cols[] = $row_col['column_id']; $SQL_COLSA[] = $row_col['table_id'] . '.' . $row_col['column_id']; if ($row_col['flag_sort'] == 'Y') { //$SQL_COLSOBA[$row_col['uisort']] // =$row_col['table_id'].'.'.$row_col['column_id']; $SQL_COLSOBA[] = $row_col['table_id'] . '.' . $row_col['column_id']; } //if($row_col['compoper']<>'' && $row_col['compval']<>'') { // $table_dd=DD_TableRef($row_col['table_id']); // $ddcol=&$table_dd['flat'][$row_col['column_id']]; // $colval=SQL_Format($ddcol['type_id'],$row_col['compval']); // $SQL_COLSWHA[] // =$row_col['table_id'].'.'.$row_col['column_id'] // .$row_col['compoper'] // .$colval; //} } // Collapse the lists into strings $SQL_COLS = implode("\n ,", $SQL_COLSA); $SQL_COLSOB = ''; if (count($SQL_COLSOBA) > 0) { ksort($SQL_COLSOBA); $SQL_COLSOB = "\n ORDER BY " . implode(',', $SQL_COLSOBA); } $SQL_WHERE = ''; if ($this->row_rep['repfilters'] != '') { $SQL_WHERE = "\n WHERE " . $this->row_rep['repfilters']; } //if(count($SQL_COLSWHA)>0) { // $SQL_WHERE="\n WHERE ".implode("\n ",$SQL_COLSWHA); //} // Now build the final SQL $SQ = " SELECT " . $SQL_COLS . $SQL_FROMJOINS . $SQL_WHERE . $SQL_COLSOB; //echo $SQ; return $SQ; }
function index_hidden_ajxFETCH() { $returns = array(); // First fetch the values $table_id = gp('ajxtable'); $table_dd = dd_Tableref($table_id); $colname = gp('ajxcol'); $colvalue = gp('ajxval'); $lcontrols = gp('ajxcontrols'); $lcolumns = gp('ajxcolumns'); $acontrols = explode(',', $lcontrols); $acolumns = explode(',', $lcolumns); // Since this is an ajax call, malformed requests exit with // no error or explanation if (count($acontrols) != count($acolumns)) { exit; } if (count($acontrols) == '') { exit; } if ($colvalue == '') { exit; } // Split column names and values and build a where clause // If any funny business, exit with no explanation $acols = explode(',', $colname); $avals = explode(',', $colvalue); $awhere = array(); if (count($acols) != count($avals)) { exit; } foreach ($acols as $x => $acol) { $awhere[] = str_replace("'", "''", $acol) . ' = ' . SQLFC($avals[$x]); } // Build and execute some SQL $sq = "SELECT " . str_replace("'", "''", $lcolumns) . " FROM " . str_replace("'", "''", $table_id) . " WHERE " . implode(' AND ', $awhere); $row = SQL_OneRow($sq); // Any unusable results return with no error or complaint if (!is_array($row)) { return false; } if (count($row) == 0) { return false; } //ob_start(); //hprint_r($row); //$returns[]="echo|".ob_get_clean(); //$returns[]='echo|'.$sq; // Build and return the controls foreach ($acontrols as $x => $acontrol) { //$returns[]='echo|'.$acontrol; //$returns[]='echo|'.$acolumns[$x]; $cn = $acolumns[$x]; if ($table_dd['flat'][$cn]['type_id'] == 'date') { $row[$cn] = hFormat('date', $row[$cn]); } if (is_null($row[$cn])) { $row[$cn] = ''; } else { $row[$cn] = trim($row[$cn]); } $returns[] = '_value|' . $acontrol . '|' . trim($row[$acolumns[$x]]); } echo implode('|-|', $returns); }