function main() { // First pull the report $row_rep = SQL_OneRow("SELECT * from reports where skey=" . gp('gp_skey')); $sreport = SQL_Format('char', $row_rep['report']); // 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 "); // Go get the joins $SQL_FROMJOINS = $this->ehProcessFromJoins(array_keys($rows_tab)); // Build a list of columns, and order-by columns, and filters $SQL_COLSA = array(); $SQL_COLSOBA = array(); $SQL_COLSWHA = array(); foreach ($rows_col as $row_col) { $SQL_COLSA[] = $row_col['table_id'] . '.' . $row_col['column_id']; if ($row_col['uisort'] != 0) { $SQL_COLSOBA[$row_col['uisort']] = $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 (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; // Display $this->ehProcessDisplay($SQ, $rows_col, $row_rep); }
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; }