UAI Mantis Reports
EOD; $footer = << UAI Mantis Reports
EOD; if ($_REQUEST['id'] != '') { header("Pragma: no-cache"); header("Expires: 0"); $conn = NewADOConnection('mysql'); $conn->Connect("localhost", "uid", "pwd", "bugtracker"); $rs = $conn->Execute('SELECT id, name, form, `sql` FROM custom_report where id='.$_REQUEST['id']); if (!$rs) print $conn->ErrorMsg(); else while (!$rs->EOF) { if ($rs->fields['form'] == '') { // copy form variables into sql $sql = $rs->fields['sql']; foreach (array_keys($_REQUEST) as $rname) { $sql = str_replace("<%".$rname."%>", $_REQUEST[$rname], $sql); } if (array_key_exists('csv', $_REQUEST)) { header("Content-Type: text/x-csv"); header("Content-Disposition: attachment; filename=\"".$rs->fields['name'].".csv\""); execSql2CSV($conn, $sql); } else { print $header; //print "
".$sql."
\n"; print "
".$rs->fields['name']."

\n"; execSql($conn, $sql); print $footer; } } else { if (array_key_exists('exec', $_REQUEST)) { // copy form variables into sql $sql = $rs->fields['sql']; foreach (array_keys($_REQUEST) as $rname) { $sql = str_replace("<%".$rname."%>", $_REQUEST[$rname], $sql); } //print "
$sql
"; // if we have defined csv then return that if (array_key_exists('csv', $_REQUEST)) { header("Content-Type: text/x-csv"); header("Content-Disposition: attachment; filename=\"".$rs->fields['name'].".csv\""); execSql2CSV($conn, $sql); } else { // otherwise return html print $header; print "
".$rs->fields['name']."

\n"; execSql($conn, $sql); print $footer; } } else { print $header; print "
".$rs->fields['name']."

\n"; // display form displayForm($rs->fields['id'], $rs->fields['form']); print $footer; } } $rs->MoveNext(); } $rs->Close(); # optional $conn->Close(); # optional } else { print 'cannot execute empty id'; } function displayForm($id, $form) { $formheader = << EOD; $formfooter = << Export to Excel

EOD; print $formheader.$form.$formfooter; } function parsFieldPrepend($fields) { $formatting = array(); foreach (array_keys($fields) as $fname) { if (ereg('^([L|R|C|M|D])_.*',$fname, $m)) { $formatting[$fname] = $m[1]; } } return $formatting; } function clearFieldPrepend($field) { if (ereg('^[L|R|C|M|D]_(.*)',$field, $m)) { return $m[1]; } else { return $field; } } function execSql2CSV($conn, $sql) { $conn->SetFetchMode(ADODB_FETCH_ASSOC); $rs = $conn->Execute($sql); if (!$rs) print $conn->ErrorMsg(); else { $s = ""; // print field names foreach (array_keys($rs->fields) as $fname) { if (strlen($s) > 0) $s = "$s,"; // append a comma if this isnt the first column // print the column names without the formatting prepended text $s = "$s\"".clearFieldPrepend($fname)."\""; } print $s."\n"; while (!$rs->EOF) { $s = ""; // print field names foreach (array_keys($rs->fields) as $fname) { if (strlen($s) > 0) $s = "$s,"; // append a comma if this isnt the first column $s = $s.'"'.str_replace('"', '""', $rs->fields[$fname]).'"'; } print "$s\n"; $rs->MoveNext(); } } } function execSql($conn, $sql) { $conn->SetFetchMode(ADODB_FETCH_ASSOC); $rs = $conn->Execute($sql); if (!$rs) print $conn->ErrorMsg(); else { // get the formatting array from the field names // if the field name is prepended with // C_ the column is centered // R_ right aligned // L_ left aligned // M_ aligned with the $ // D_ aligned with the . in a number $formatting = parsFieldPrepend($rs->fields); // print table definition print "\n"; print "\n"; // print field names foreach (array_keys($rs->fields) as $fname) { // print the column names without the formatting prepended text print " \n"; } print "\n"; $i = 0; while (!$rs->EOF) { if ($i % 2 == 0) { print "\n"; } else { print "\n"; } $i++; // print field names foreach (array_keys($rs->fields) as $fname) { if (array_key_exists($fname, $formatting)) { $tdclass = " id={$formatting[$fname]}"; } else { $tdclass = ""; } print " {$rs->fields[$fname]}\n"; } print "\n"; $rs->MoveNext(); } // print table close print "
".clearFieldPrepend($fname)."
\n"; } } ?>