public static function display(&$report, &$request)
{
//determine if this is an asyncronous report or not
$report->async = !isset($request->query['content_only']);
if (isset($request->query['no_async'])) {
$report->async = false;
}
//if we're only getting the report content
if (isset($request->query['content_only'])) {
$template = 'html/content_only';
} else {
$template = 'html/report';
}
try {
$additional_vars = array();
if (isset($request->query['no_charts'])) {
$additional_vars['no_charts'] = true;
}
$html = $report->renderReportPage($template, $additional_vars);
echo $html;
} catch (Exception $e) {
if (isset($request->query['content_only'])) {
$template = 'html/blank_page';
}
$vars = array('title' => $report->report, 'header' => '<h2>There was an error running your report</h2>', 'error' => $e->getMessage(), 'content' => "<h2>Report Query</h2>" . $report->options['Query_Formatted']);
echo PhpReports::render($template, $vars);
}
}
public static function setVar($key, $value)
{
if (!self::$vars) {
self::$vars = array();
}
self::$vars[$key] = $value;
}
public static function filter($value, $options = array(), &$report, &$row)
{
// If this is html
$html = isset($options['html']) ? $options['html'] : false;
$template = isset($options['template']) ? $options['template'] : $value->getValue();
$result = PhpReports::renderString($template, array("value" => $value->getValue(), "row" => $row));
$value->setValue($result, $html);
return $value;
}
public static function filter($value, $options = array(), &$report, &$row)
{
$handle = fopen($value->getValue(), 'rb');
$img = new Imagick();
$img->readImageFile($handle);
$data = $img->identifyImage();
if (!isset($options['format'])) {
$options['format'] = self::$default_format;
}
$value->setValue(PhpReports::renderString($options['format'], $data));
return $value;
}
public static function parse($key, $value, &$report)
{
$params = null;
if (is_array($value)) {
$params = $value;
} elseif ($value[0] === '{') {
$params = PhpReports::json_decode($value, true);
}
//if it couldn't be parsed as json, try parsing it as a shortcut form
if (!$params) {
$params = static::parseShortcut($value);
}
if (!$params) {
throw new Exception("Could not parse header '{$key}'");
}
//run defined validation rules and fill in default params
try {
$params = static::validate($params);
} catch (Exception $e) {
throw new Exception($key . " Header: " . $e->getMessage());
}
static::init($params, $report);
}
PhpReports::displayDashboard($name);
});
//JSON list of reports (used for typeahead search)
Flight::route('/report-list-json', function () {
header("Content-Type: application/json");
header("Cache-Control: max-age=3600");
echo PhpReports::getReportListJSON();
});
//if no report format is specified, default to html
Flight::route('/report', function () {
PhpReports::displayReport($_REQUEST['report'], 'html');
});
//reports in a specific format (e.g. 'html','csv','json','xml', etc.)
Flight::route('/report/@format', function ($format) {
PhpReports::displayReport($_REQUEST['report'], $format);
});
Flight::route('/edit', function () {
PhpReports::editReport($_REQUEST['report']);
});
Flight::route('/set-environment', function () {
header("Content-Type: application/json");
$_SESSION['environment'] = $_REQUEST['environment'];
echo '{ "status": "OK" }';
});
//email report
Flight::route('/email', function () {
PhpReports::emailReport();
});
Flight::set('flight.handle_errors', false);
Flight::set('flight.log_errors', true);
Flight::start();
public function renderReportPage($template = 'html/report', $additional_vars = array())
{
$this->run();
$template_vars = array('is_ready' => $this->is_ready, 'async' => $this->async, 'report_url' => PhpReports::$request->base . '/report/?' . $_SERVER['QUERY_STRING'], 'report_querystring' => $_SERVER['QUERY_STRING'], 'base' => PhpReports::$request->base, 'report' => $this->report, 'vars' => $this->prepareVariableForm(), 'macros' => $this->macros);
$template_vars = array_merge($template_vars, $additional_vars);
$template_vars = array_merge($template_vars, $this->options);
return PhpReports::render($template, $template_vars);
}
public static function run(&$report)
{
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
//expand macros in query
$sql = PhpReports::render($report->raw_query, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
$datasets = array();
$explicit_datasets = preg_match('/--\\s+@dataset(\\s*=\\s*|\\s+)true/', $sql);
foreach ($queries as $i => $query) {
$is_last = $i === count($queries) - 1;
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = mysql_query($query, $report->conn);
if (!$result) {
throw new Exception("Query failed: " . mysql_error($report->conn));
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if (mysql_fetch_assoc($result)) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
// If this query should be included as a dataset
if (!$explicit_datasets && $is_last || preg_match('/--\\s+@dataset(\\s*=\\s*|\\s+)true/', $query)) {
$dataset = array('rows' => array());
while ($row = mysql_fetch_assoc($result)) {
$dataset['rows'][] = $row;
}
// Get dataset title if it has one
if (preg_match('/--\\s+@title(\\s*=\\s*|\\s+)(.*)/', $query, $matches)) {
$dataset['title'] = $matches[2];
}
$datasets[] = $dataset;
}
}
return $datasets;
}
Flight::route('/report', function () {
PhpReports::displayReport($_REQUEST['report'], 'html');
});
//reports in a specific format (e.g. 'html','csv','json','xml', etc.)
Flight::route('/report/@format', function ($format) {
PhpReports::displayReport($_REQUEST['report'], $format);
});
Flight::route('/edit', function () {
PhpReports::editReport($_REQUEST['report']);
});
Flight::route('/add', function () {
PhpReports::addReport($_REQUEST['report']);
});
Flight::route('/set-environment', function () {
header("Content-Type: application/json");
$_SESSION['environment'] = $_REQUEST['environment'];
echo '{ "status": "OK" }';
});
//email report
Flight::route('/email', function () {
PhpReports::emailReport();
});
//email report
Flight::route('/email_scheduler', function () {
PhpReports::scheduleEmail();
});
Flight::start();
} else {
header("Location: login");
exit;
}
public static function beforeRender(&$report)
{
//cache for Twig parameters for each dataset/column
$twig_params = array();
// Now that we know how many datasets we have, expand out Rollup headers with dataset->true
$new_rollups = array();
foreach ($report->options['Rollup'] as $i => $rollup) {
if ($rollup['dataset'] === true && isset($report->options['DataSets'])) {
$copy = $rollup;
foreach ($report->options['DataSets'] as $i => $dataset) {
$copy['dataset'] = $i;
$new_rollups[] = $copy;
}
} else {
$new_rollups[] = $rollup;
}
}
$report->options['Rollup'] = $new_rollups;
// First get all the values
foreach ($report->options['Rollup'] as $rollup) {
// If we already got twig parameters for this dataset, skip it
if (isset($twig_params[$rollup['dataset']])) {
continue;
}
$twig_params[$rollup['dataset']] = array();
if (isset($report->options['DataSets'])) {
if (isset($report->options['DataSets'][$rollup['dataset']])) {
foreach ($report->options['DataSets'][$rollup['dataset']]['rows'] as $row) {
foreach ($row['values'] as $value) {
if (!isset($twig_params[$rollup['dataset']][$value->key])) {
$twig_params[$rollup['dataset']][$value->key] = array('values' => array());
}
$twig_params[$rollup['dataset']][$value->key]['values'][] = $value->getValue();
}
}
}
}
}
// Then, calculate other statistical properties
foreach ($twig_params as $dataset => &$tp) {
foreach ($tp as $column => &$params) {
//get non-null values and sort them
$real_values = array_filter($params['values'], function ($a) {
if ($a === null || $a === '') {
return false;
}
return true;
});
sort($real_values);
$params['sum'] = array_sum($real_values);
$params['count'] = count($real_values);
if ($params['count']) {
$params['mean'] = $params['average'] = $params['sum'] / $params['count'];
$params['median'] = $params['count'] % 2 ? ($real_values[$params['count'] / 2 - 1] + $real_values[$params['count'] / 2]) / 2 : $real_values[floor($params['count'] / 2)];
$params['min'] = $real_values[0];
$params['max'] = $real_values[$params['count'] - 1];
} else {
$params['mean'] = $params['average'] = $params['median'] = $params['min'] = $params['max'] = 0;
}
$devs = array();
foreach ($real_values as $v) {
$devs[] = pow($v - $params['mean'], 2);
}
$params['stdev'] = sqrt(array_sum($devs) / (count($devs) - 1));
}
}
//render each rollup row
foreach ($report->options['Rollup'] as $rollup) {
if (!isset($report->options['DataSets'][$rollup['dataset']]['footer'])) {
$report->options['DataSets'][$rollup['dataset']]['footer'] = array();
}
$columns = $rollup['columns'];
$row = array('values' => array(), 'rollup' => true);
foreach ($twig_params[$rollup['dataset']] as $column => $p) {
if (isset($columns[$column])) {
$p = array_merge($p, array('row' => $twig_params[$rollup['dataset']]));
$row['values'][] = new ReportValue(-1, $column, PhpReports::renderString($columns[$column], $p));
} else {
$row['values'][] = new ReportValue(-1, $column, null);
}
}
$report->options['DataSets'][$rollup['dataset']]['footer'][] = $row;
}
}
public static function run(&$report)
{
$report->conn->SetFetchMode(ADODB_FETCH_ASSOC);
$rows = array();
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
//expand macros in query
$sql = PhpReports::render($report->raw_query, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
foreach ($queries as $query) {
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = $report->conn->Execute($query);
if (!$result) {
throw new Exception("Query failed: " . $report->conn->ErrorMsg());
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if ($result->GetAssoc()) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
}
return $result->GetArray();
}
public static function run(&$report)
{
$report->conn->SetFetchMode(ADODB_FETCH_ASSOC);
$rows = array();
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
$raw_sql = "";
foreach ($report->raw_query as $qry) {
if (is_array($qry)) {
foreach ($qry as $key => $value) {
// TODO handle arrays better
if (!is_bool($value) && !is_array($value)) {
$qry[$key] = PhpReports::renderString($value, $macros);
}
}
//TODO This sux - need a class or something :-)
$raw_sql .= PivotTableSQL($report->conn, $qry['tables'], $qry['rows'], $qry['columns'], $qry['where'], $qry['orderBy'], $qry['limit'], $qry['agg_field'], $qry['agg_label'], $qry['agg_fun'], $qry['include_agg_field'], $qry['show_count']);
} else {
$raw_sql .= $qry;
}
}
//expand macros in query
$sql = PhpReports::render($raw_sql, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
foreach ($queries as $query) {
if (!is_array($query)) {
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = $report->conn->Execute($query);
if (!$result) {
throw new Exception("Query failed: " . $report->conn->ErrorMsg());
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if ($result->GetAssoc()) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
}
}
return $result->GetArray();
}
