Example #1
0
 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);
 }
Example #2
0
 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;
 }