1149 lines
44 KiB
PHP
1149 lines
44 KiB
PHP
<?php
|
||
|
||
# Простая OLAPообразная статистика
|
||
# См. http://yourcmc.ru/wiki/Общий_вид_статистики
|
||
# admin_olap.tpl, admin_olap_csv.tpl
|
||
# (c) Vitaliy Filippov 2010+
|
||
|
||
$is_adm = true;
|
||
require_once 'lib/templating.php';
|
||
|
||
require_once 'olap_config.php';
|
||
|
||
$modified = false;
|
||
foreach ($_REQUEST as $k => $v)
|
||
{
|
||
if ($v === "")
|
||
{
|
||
unset($_GET[$k]);
|
||
unset($_POST[$k]);
|
||
$modified = true;
|
||
}
|
||
}
|
||
if ($modified)
|
||
{
|
||
header("Location: ?".http_build_query($_GET+$_POST));
|
||
exit;
|
||
}
|
||
|
||
$olap = new OLAP($_GET+$_POST);
|
||
$olap->execute();
|
||
exit;
|
||
|
||
# TODO уметь горизонтальные графики, уметь комбинировать графики с другими группировками (возможно, кроме горизонтали)
|
||
|
||
class OLAP
|
||
{
|
||
static $initialised;
|
||
static $sources;
|
||
static $mwTimestampSql = array(
|
||
'year' => 'SUBSTR($,1,4)',
|
||
'ym' => 'CONCAT(SUBSTR($,1,4),"-",SUBSTR($,5,2))',
|
||
'month' => 'SUBSTR($,5,2)',
|
||
'ymd' => 'CONCAT(SUBSTR($,1,4),"-",SUBSTR($,5,2),"-",SUBSTR($,7,2))',
|
||
'day' => 'SUBSTR($,7,2)',
|
||
'weekday' => 'WEEKDAY(CONCAT(SUBSTR($,1,4),"-",SUBSTR($,5,2),"-",SUBSTR($,7,2)))',
|
||
'week' => 'WEEK(CONCAT(SUBSTR($,1,4),"-",SUBSTR($,5,2),"-",SUBSTR($,7,2)))',
|
||
'hour' => 'SUBSTR($,9,2)',
|
||
'minute' => 'SUBSTR($,11,2)',
|
||
'second' => 'SUBSTR($,13,2)',
|
||
);
|
||
static $functions = array(
|
||
'year' => array('name' => 'Год', 'time_format' => 'Y', 'sql' => 'SUBSTR($,1,4)'),
|
||
'ym' => array('name' => 'Год-месяц', 'time_format' => 'Y-m', 'sql' => 'SUBSTR($,1,7)'),
|
||
'month' => array('name' => 'Месяц', 'time_format' => 'm', 'sql' => 'MONTH($)'),
|
||
'ymd' => array('name' => 'Дата', 'time_format' => 'Y-m-d', 'sql' => 'SUBSTR($,1,10)'),
|
||
'day' => array('name' => 'День месяца', 'time_format' => 'd', 'sql' => 'DAY($)'),
|
||
'weekday' => array('name' => 'День недели', 'time_format' => 'N', 'sql' => 'WEEKDAY($)'),
|
||
'week' => array('name' => '№ недели', 'time_format' => 'W', 'sql' => 'WEEK($)'),
|
||
'hour' => array('name' => 'Час', 'time_format' => 'H', 'sql' => 'HOUR($)'),
|
||
'minute' => array('name' => 'Минута', 'time_format' => 'H:i', 'sql' => 'DATE_FORMAT($, "%k:%i")'),
|
||
'second' => array('name' => 'Секунда', 'time_format' => 'H:i:s', 'sql' => 'DATE_FORMAT($, "%k:%i:%s")'),
|
||
);
|
||
static $aggregates = array(
|
||
'count' => array('name' => 'Количество', 'sql' => 'COUNT($)'),
|
||
'sum' => array('name' => 'Сумма', 'sql' => 'SUM($)'),
|
||
'avg' => array('name' => 'Среднее', 'sql' => 'AVG($)'),
|
||
'min' => array('name' => 'Минимум', 'sql' => 'MIN($)'),
|
||
'max' => array('name' => 'Максимум', 'sql' => 'MAX($)'),
|
||
'stddev' => array('name' => 'Дисперсия', 'sql' => 'STDDEV($)'),
|
||
'n_uniq' => array('name' => 'Количество уникальных', 'sql' => 'COUNT(DISTINCT $)'),
|
||
'sum_rnd' => array('name' => 'Сумма округлённая', 'sql' => 'ROUND(SUM($))'),
|
||
);
|
||
static $group_types = array(
|
||
'tables' => 'Несколько таблиц',
|
||
'tr' => 'По вертикали',
|
||
'td' => 'По горизонтали',
|
||
'cell' => 'В ячейке',
|
||
'graph' => 'График',
|
||
);
|
||
|
||
var $request;
|
||
var $current_srcid, $current_src;
|
||
var $time_start, $nfields, $build, $csv;
|
||
var $tables, $where, $where_nosql, $group_fields, $cell;
|
||
var $is_html_format = true;
|
||
var $add_spanned;
|
||
var $graph_scale = 600; /* пикселей */
|
||
var $graph_logarithmic = false;
|
||
static $decimal = array(3, '.', '');
|
||
|
||
function __construct($request)
|
||
{
|
||
$this->time_start = microtime(true);
|
||
$this->request = $request;
|
||
if (!self::$initialised)
|
||
self::initialise();
|
||
$this->parse_request();
|
||
}
|
||
|
||
static function initialise()
|
||
{
|
||
global $template;
|
||
set_time_limit(300);
|
||
$template->compiletime_functions['fformat'] = 'OLAP::tpl_field_format';
|
||
foreach (self::$sources as $k => &$s)
|
||
{
|
||
$s['id'] = $k;
|
||
if (empty($s['where']))
|
||
$s['where'] = array();
|
||
}
|
||
unset($s);
|
||
$i = 0;
|
||
foreach (self::$group_types as $k => $v)
|
||
self::$group_types[$k] = array('id' => $k, 'num' => $i++, 'name' => $v);
|
||
foreach (self::$functions as $k => &$v)
|
||
$v['id'] = $k;
|
||
unset($v);
|
||
foreach (self::$aggregates as $k => &$v)
|
||
$v['id'] = $k;
|
||
unset($v);
|
||
self::$initialised = true;
|
||
}
|
||
|
||
function mysql()
|
||
{
|
||
static $mysql;
|
||
if (!$mysql)
|
||
{
|
||
global $db;
|
||
$mysql = new DatabaseMysql(!empty($this->current_src['db']) ? $this->current_src['db'] : $db);
|
||
}
|
||
return $mysql;
|
||
}
|
||
|
||
function parse_request()
|
||
{
|
||
$request = &$this->request;
|
||
|
||
// выбираем источник данных
|
||
$this->current_srcid = @$request['datasource'];
|
||
$this->current_src = @self::$sources[$this->current_srcid];
|
||
if (!$this->current_src)
|
||
return;
|
||
|
||
// дополняем всяким бредом всякий бред
|
||
foreach ($this->current_src['fielddescs'] as $k => &$v)
|
||
{
|
||
$v['id'] = $k;
|
||
if (!empty($v['autofilter']))
|
||
{
|
||
$f = $v['sql'];
|
||
if (!$f)
|
||
$f = $k;
|
||
$v['options'] = $this->mysql()->select(
|
||
$this->current_src['tables'],
|
||
"$f id, $f name",
|
||
$this->current_src['where'],
|
||
array('GROUP BY' => $f)
|
||
);
|
||
}
|
||
if (!empty($v['options']))
|
||
{
|
||
if (is_callable($v['options']))
|
||
$v['options'] = call_user_func($v['options'], $this);
|
||
foreach ($v['options'] as &$o)
|
||
$v['options_hash'][$o['id']] = $o['name'];
|
||
}
|
||
if (is_object($v['sql']) && get_class($v['sql']) === 'Closure')
|
||
{
|
||
$v['sql'] = $v['sql']($this);
|
||
}
|
||
}
|
||
unset($v);
|
||
|
||
// читаем параметры запроса
|
||
$this->tables = array();
|
||
$this->where = array();
|
||
$this->where_nosql = array();
|
||
$this->group_fields = array();
|
||
if (!empty($request['graph_log']))
|
||
{
|
||
$this->graph_logarithmic = true;
|
||
$this->request['graph_log'] = 1;
|
||
}
|
||
foreach ($request as $k => $v)
|
||
{
|
||
// условия выборки: where-ОПЕРАЦИЯ-ПОЛЕ = ЗНАЧЕНИЕ
|
||
if (substr($k, 0, 6) == 'where-')
|
||
{
|
||
list($t, $f) = explode('-', substr($k, 6), 2);
|
||
$fd = &$this->current_src['fielddescs'][$f];
|
||
if (!$fd)
|
||
{
|
||
$f = str_replace('_', ' ', $f);
|
||
$fd = &$this->current_src['fielddescs'][$f];
|
||
}
|
||
if (!$fd || !strlen($v))
|
||
continue;
|
||
if (!empty($fd['join']))
|
||
$this->tables += $fd['join'];
|
||
$this->request[$k] = $v;
|
||
if (!empty($fd['is_time']))
|
||
$v = VMXTemplate::timestamp($v, $this->current_src['fielddescs'][$f]['format']);
|
||
$this->where_nosql[] = array($f, $t, $v);
|
||
if ($fd['options'])
|
||
{
|
||
foreach ($fd['options'] as &$o)
|
||
if ("$o[id]" === $v)
|
||
$o['selected'] = true;
|
||
unset($o);
|
||
}
|
||
// NoSQL-поля будет фильтровать sql_filter-функция
|
||
if (empty($fd['nosql']))
|
||
{
|
||
$dn = !empty($fd['sql']) ? $fd['sql'] : $f;
|
||
$ve = $this->mysql()->quote($v);
|
||
if ($t == 'eq')
|
||
{
|
||
if ($fd['sql_eq'])
|
||
$this->where[] = str_replace('$', $ve, $fd['sql_eq']);
|
||
else
|
||
$this->where[] = "$dn = $ve";
|
||
}
|
||
elseif ($t == 'ge' && $fd['le_ge'])
|
||
$this->where[] = "$dn >= $ve";
|
||
elseif ($t == 'le' && $fd['le_ge'])
|
||
$this->where[] = "$dn <= $ve";
|
||
}
|
||
}
|
||
// описание группировки
|
||
elseif (substr($k, 0, 11) == 'group-type-')
|
||
{
|
||
$i = substr($k, 11);
|
||
$gf = array();
|
||
foreach(array('type', 'field', 'func', 'sort_dir', 'sort_field', 'sort_aggr', 'sort_func') as $k)
|
||
$gf[$k] = @$request["group-$k-$i"];
|
||
if (!empty($request['csv']) && $gf['type'] == 'graph')
|
||
$gf['type'] = 'tr';
|
||
if (self::$group_types[$gf['type']] && $gf['field'] !== '')
|
||
{
|
||
$gf['num'] = count($this->group_fields);
|
||
$this->group_fields[] = $gf;
|
||
$this->request[$k] = $v;
|
||
}
|
||
}
|
||
}
|
||
usort($this->group_fields, 'OLAP::group_type_cmp');
|
||
|
||
// Число мест под поля группировки
|
||
$this->nfields = intval(@$request['field_count']);
|
||
if ($this->nfields <= 0)
|
||
$this->nfields = 5;
|
||
if (count($this->group_fields) > $this->nfields)
|
||
$this->nfields = count($this->group_fields);
|
||
|
||
// Описание показателя
|
||
foreach(array('field', 'func', 'aggr') as $x)
|
||
$this->cell[$x] = @$request["cell_$x"];
|
||
$this->cell['field_name'] = isset($this->cell['field']) ? $this->current_src['fielddescs'][$this->cell['field']]['name'] : NULL;
|
||
$this->cell['func_name'] = isset($this->cell['func']) ? self::$functions[$this->cell['func']]['name'] : NULL;
|
||
$this->cell['aggr_name'] = isset($this->cell['aggr']) ? self::$aggregates[$this->cell['aggr']]['name'] : NULL;
|
||
$this->cell['value_desc'] = $this->cell['field_name'] ?: '*';
|
||
if ($this->cell['func_name'])
|
||
$this->cell['value_desc'] = $this->cell['func_name'].'('.$this->cell['value_desc'].')';
|
||
$this->cell['value_desc_brace'] = $this->cell['value_desc'];
|
||
if ($this->cell['aggr_name'])
|
||
{
|
||
$this->cell['value_desc_brace'] = $this->cell['aggr_name'].' ('.$this->cell['value_desc'].')';
|
||
$this->cell['value_desc'] = $this->cell['aggr_name'].': '.$this->cell['value_desc'];
|
||
}
|
||
|
||
// Запрос на построение?
|
||
$this->build = !empty($request['build']);
|
||
$this->csv = !empty($request['csv']);
|
||
if ($this->csv)
|
||
{
|
||
$this->add_spanned = true;
|
||
$this->is_html_format = false;
|
||
}
|
||
}
|
||
|
||
function execute()
|
||
{
|
||
global $template;
|
||
|
||
// Сельф
|
||
$template->vars('olap', $this);
|
||
|
||
// Если источник ещё не выбран, выдаём форму выбора
|
||
if (!$this->current_src)
|
||
{
|
||
$template->vars('sources', array_values(self::$sources));
|
||
$template->vars('select', true);
|
||
print $template->parse('admin_olap.tpl');
|
||
return;
|
||
}
|
||
|
||
$template->vars(array(
|
||
'rpt_link' => "?".http_build_query($this->request),
|
||
'request' => $this->request,
|
||
'group_fields' => $this->group_fields,
|
||
'cell' => &$this->cell,
|
||
'field_count' => $this->nfields,
|
||
'srcid' => $this->current_srcid,
|
||
'src' => $this->current_src,
|
||
'fielddescs' => array_values($this->current_src['fielddescs']),
|
||
'group_types' => array_values(self::$group_types),
|
||
'functions' => array_values(self::$functions),
|
||
'aggregates' => array_values(self::$aggregates),
|
||
));
|
||
|
||
if (!$this->build)
|
||
{
|
||
print $template->parse('admin_olap.tpl');
|
||
exit;
|
||
}
|
||
|
||
// читаем сами данные, заодно наборы значений для заглавных строк таблицы,
|
||
// заодно полные наборы значений для каждого поля
|
||
// т.к. наборы значений для заглавных столбцов таблицы берутся из самих данных
|
||
$data = $tdkeys = NULL;
|
||
$tdk_group = array();
|
||
$output = array(
|
||
array('output' => &$data, 'group_by' => $this->group_fields),
|
||
array('output' => &$tdkeys, 'group_by' => &$tdk_group),
|
||
);
|
||
foreach ($this->group_fields as $i => &$v)
|
||
{
|
||
$v['keys'] = array();
|
||
$output[] = array('output' => &$v['keys'], 'group_by' => array($v));
|
||
if ($v['type'] == 'tables' || $v['type'] == 'td')
|
||
$tdk_group[] = $v;
|
||
}
|
||
unset($v);
|
||
$this->load($this->cell['field'], $this->cell['func'], $this->cell['aggr'], $output);
|
||
|
||
// читаем значения, по которым следует сортировать значения полей группировки
|
||
// т.е. значения поля можно сортировать по агрегату другого поля
|
||
foreach ($this->group_fields as $i => &$v)
|
||
{
|
||
$fields = array();
|
||
$v_group = array();
|
||
if (strlen($v['sort_aggr']) && strlen($v['sort_field']))
|
||
{
|
||
// группировка по всем полям tables + того же типа,
|
||
// предшествующим это, включая само поле
|
||
for ($j = 0; $j <= $i; $j++)
|
||
if ($this->group_fields[$j]['type'] == 'tables' ||
|
||
$this->group_fields[$j]['type'] == $v['type'])
|
||
$v_group[] = $this->group_fields[$j];
|
||
$this->load($v['sort_field'], $v['sort_func'], $v['sort_aggr'],
|
||
array(array('output' => &$v['sort'], 'group_by' => $v_group))
|
||
);
|
||
}
|
||
$v['keys'] = array_keys($v['keys']);
|
||
if (count($v_group) <= 1)
|
||
self::do_sort($v['keys'], $v['sort'], $v['sort_dir']);
|
||
}
|
||
unset($v);
|
||
|
||
$vars = array('build' => 1);
|
||
if ($this->group_fields && $this->group_fields[0]['type'] == 'graph')
|
||
{
|
||
$vars['graphs'] = $this->build_graphs($this->group_fields, $data, $tdkeys);
|
||
$vars['groups'] = $this->tpl_jsgraphs($vars['graphs']);
|
||
}
|
||
else
|
||
$vars['tables'] = $this->build_tables($this->group_fields, $data, $tdkeys);
|
||
if (!$this->csv)
|
||
$vars['csv_link'] = "?csv=1&".http_build_query($this->request);
|
||
$vars['memory'] = file_size_string(memory_get_usage());
|
||
$vars['time_elapsed'] = sprintf("%.3f", microtime(true) - $this->time_start);
|
||
|
||
$template->vars($vars);
|
||
if ($this->csv)
|
||
{
|
||
header("Content-Type: text/csv; charset=windows-1251");
|
||
header("Content-Disposition: inline; filename=OLAP-".date('YmdHis').".csv");
|
||
$out = $template->parse('admin_olap_csv.tpl');
|
||
print iconv("utf-8", "windows-1251", $out);
|
||
}
|
||
else
|
||
print $template->parse('admin_olap.tpl');
|
||
}
|
||
|
||
// Загрузка данных из текущего источника, сама выбирает способ - SQL или программный
|
||
// $field, $func, $aggr - поле, преобразование, агрегатная функция
|
||
// $output - спецификация того, куда записать результат - массив массивов вида:
|
||
// array('output' => &$array, 'group_by' => <часть_полей_группировки>)
|
||
function load($field, $func, $aggr, $output)
|
||
{
|
||
if (!empty($this->current_src['use_pgm']) ||
|
||
!empty($this->current_src['sql_filter']))
|
||
return $this->load_pgm($field, $func, $aggr, $output);
|
||
else
|
||
return $this->load_sql($field, $func, $aggr, $output);
|
||
}
|
||
|
||
// Загрузка данных с группировкой и подсчётом показателей через SQL
|
||
// FIXME корректно вычисляется только первый показатель, остальные по сути просто заполняются
|
||
// наборами ключей с некоторыми значениями, не равными правильным агрегатам.
|
||
function load_sql($field, $func, $aggr, $output)
|
||
{
|
||
$group_fields = $output[0]['group_by'];
|
||
$fields = array();
|
||
$group_by = array();
|
||
$tables = array();
|
||
foreach ($group_fields as $i => &$v)
|
||
{
|
||
$v['sql'] = $this->sql_trans_field($v['field'], $v['func'], $tables);
|
||
$v['keys'] = array();
|
||
$fields[] = $v['sql'].' f'.$i;
|
||
$group_by[] = $v['sql'];
|
||
}
|
||
unset($v);
|
||
|
||
$sql = $this->sql_trans_field($field, $func, $tables);
|
||
$fields[] = str_replace('$', $sql, self::$aggregates[$aggr]['sql']).' c';
|
||
|
||
$code = 'while($r = $result->fetch_assoc()) { ';
|
||
foreach ($output as $k => $o)
|
||
{
|
||
$asgn = '$output['.intval($k).'][\'output\']';
|
||
foreach ($o['group_by'] as $f)
|
||
$asgn .= '[$r[\'f'.$f['num'].'\']]';
|
||
$code .= $asgn . ' = $r[\'c\']; ';
|
||
}
|
||
$code .= '}';
|
||
|
||
$result = $this->mysql()->select(
|
||
$this->current_src['tables'] + $this->tables + $tables, $fields,
|
||
array_merge($this->current_src['where'], $this->where),
|
||
array('GROUP BY' => $group_by),
|
||
MS_RESULT
|
||
);
|
||
eval($code);
|
||
}
|
||
|
||
// Загрузка данных с группировкой и подсчётом показателей в PHP
|
||
function load_pgm($field, $func, $aggr, $output)
|
||
{
|
||
$code = '';
|
||
foreach ($output as $k => $o)
|
||
{
|
||
$asgn = '$output['.intval($k).'][\'output\']';
|
||
foreach ($o['group_by'] as $f)
|
||
$asgn .= '['.$this->func_trans_field($f['field'], $f['func']).']';
|
||
$code .= "self::aggr_update_$aggr($asgn, ".$this->func_trans_field($field, $func).");\n";
|
||
}
|
||
|
||
$gen = $this->current_src['generator'];
|
||
$pipe = $this->current_src['sql_filter'];
|
||
if ($gen)
|
||
{
|
||
// Загрузка данных не из базы, а из произвольной функции
|
||
$code = 'while($r = $result->fetch()) { ' . $code . '}';
|
||
}
|
||
elseif ($pipe)
|
||
{
|
||
$call = $pipe;
|
||
if (is_array($pipe))
|
||
{
|
||
if (is_string($pipe[0]) && class_exists($pipe[0]))
|
||
$call = $pipe[0].'::'.$pipe[1];
|
||
elseif (is_object($pipe[0]))
|
||
$call = '$pipe->'.$pipe[1];
|
||
else
|
||
die("Invalid sql_filter callback in source '".$this->current_src['in']."'");
|
||
}
|
||
// Поддержка фильтрации данных через callback
|
||
// Важно, что функция потоковая, чтобы память не хавала
|
||
// В начале и конце дёргается с NULL'ом вместо строки:
|
||
// В начале типа "конструктора", в конце типа "выплюнуть запомненное"
|
||
$code = $call.'(NULL, $this); while($row = $result->fetch_assoc()) { foreach('.
|
||
$call.'($row, $this) as $r) { '.$code.'} } '.
|
||
'foreach('.$call.'(NULL, $this) as $r) { '.$code.'}';
|
||
}
|
||
else
|
||
$code = 'while($r = $result->fetch_assoc()) { ' . $code . '}';
|
||
|
||
// "Завершение" вычисления агрегата
|
||
if (is_callable('self::aggr_finish_'.$aggr))
|
||
{
|
||
foreach ($output as $k => $o)
|
||
{
|
||
$var = '$output['.intval($k).'][\'output\']';
|
||
foreach ($o['group_by'] as $i => $f)
|
||
{
|
||
$code .= "foreach($var as &\$a$i) ";
|
||
$var = '$a'.$i;
|
||
}
|
||
$code .= "self::aggr_finish_$aggr($var);";
|
||
}
|
||
}
|
||
|
||
$fields = array();
|
||
foreach ($output as $k => $o)
|
||
foreach ($o['group_by'] as $f)
|
||
$fields[$f['field']] = true;
|
||
if ($field)
|
||
$fields[$field] = true;
|
||
foreach ($this->current_src['fielddescs'] as $f => $a)
|
||
if (!empty($a['mandatory']))
|
||
$fields[$f] = true;
|
||
foreach ($fields as $f => &$v)
|
||
{
|
||
$a = $this->current_src['fielddescs'][$f];
|
||
$v = $a['sql'] ? $a['sql'] . ' ' . $f : $f;
|
||
}
|
||
unset($v);
|
||
$fields = array_values($fields);
|
||
|
||
if ($gen = $this->current_src['generator'])
|
||
{
|
||
// Функция, принимает массив имён нужных полей и ограничения в формате
|
||
// [ поле, оператор, значение ] и должна возвращать объект, умеющий fetch()
|
||
$result = call_user_func($gen, array(
|
||
'fields' => array_keys($fields),
|
||
'fields_sql' => $fields,
|
||
'where' => $this->where_nosql,
|
||
'where_sql' => $this->where,
|
||
));
|
||
}
|
||
else
|
||
{
|
||
// SQL
|
||
$result = $this->mysql()->select(
|
||
$this->current_src['tables'], $fields,
|
||
array_merge($this->current_src['where'], $this->where),
|
||
$this->current_src['sql_options'],
|
||
MS_RESULT
|
||
);
|
||
}
|
||
eval($code);
|
||
}
|
||
|
||
// Построение "графиков" - рядом с цифрой рисуется горизонтальная полоса пропорциональной длины
|
||
function build_graphs($fields, &$data, &$tdkeys)
|
||
{
|
||
$my = array_shift($fields);
|
||
$keys = $my['sort'] ? array_keys($my['sort']) : $my['keys'];
|
||
self::do_sort($keys, $my['sort'], $my['sort_dir']);
|
||
$graphs = array();
|
||
if (count($fields))
|
||
{
|
||
// Если кроме $my остались ещё поля - уйдём в рекурсию
|
||
foreach ($fields as &$v)
|
||
if ($v['sort'])
|
||
$v['orig_sort'] = &$v['sort'];
|
||
unset($v);
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $data))
|
||
{
|
||
foreach ($fields as &$v)
|
||
if (!empty($v['orig_sort']))
|
||
$v['sort'] = &$v['orig_sort'][$k];
|
||
unset($v);
|
||
$gr = $this->build_graphs($fields, $data[$k], $tdkeys);
|
||
// Допишем своё значение поля в начало описаний "дочерних" графиков
|
||
$my_desc = array(
|
||
'field' => $this->current_src['fielddescs'][$my['field']]['name'],
|
||
'func' => self::$functions[$my['func']]['name'],
|
||
'value' => $this->field_format($my['field'], '', '', $k)
|
||
);
|
||
foreach ($gr as &$g)
|
||
{
|
||
array_unshift($g['desc'], $my_desc);
|
||
$graphs[] = $g;
|
||
}
|
||
unset($g);
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
// А вот здесь реально строим данные для графика
|
||
$gr = array(
|
||
'desc' => array(),
|
||
'fieldid' => $my['field'],
|
||
'funcid' => $my['func'],
|
||
'field' => $this->current_src['fielddescs'][$my['field']]['name'],
|
||
'func' => self::$functions[$my['func']]['name'],
|
||
'data' => array(),
|
||
'max' => NULL,
|
||
'min' => NULL,
|
||
'numeric' => true,
|
||
);
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $data))
|
||
{
|
||
$v = $ov = floatval($data[$k]);
|
||
if ($this->graph_logarithmic)
|
||
{
|
||
if ($v >= 1)
|
||
$v = 1+log($v);
|
||
elseif ($v <= -1)
|
||
$v = -1-log(-$v);
|
||
}
|
||
$ov = str_replace('.00', '', sprintf("%.2f", $ov));
|
||
$fv = $this->field_format($my['field'], '', '', $k);
|
||
if (!is_numeric($fv))
|
||
$gr['numeric'] = false;
|
||
$gr['data'][] = array($fv, $ov, $v, 0);
|
||
if ($gr['max'] === NULL || $gr['max'] < $v)
|
||
$gr['max'] = $v;
|
||
if ($gr['min'] === NULL || $gr['min'] > $v)
|
||
$gr['min'] = $v;
|
||
}
|
||
}
|
||
if ($gr['min'] >= 0)
|
||
$gr['min'] = 0;
|
||
else
|
||
$gr['bi_sign'] = true;
|
||
// Масштабируем график до целых чисел с точностью $this->graph_scale
|
||
if ($gr['max'] > $gr['min'])
|
||
{
|
||
if (!empty($gr['bi_sign']))
|
||
{
|
||
foreach ($gr['data'] as &$d)
|
||
{
|
||
// $d[2] - положительные значения, $d[3] - отрицательные
|
||
if ($d[2] >= 0)
|
||
{
|
||
$d[2] = intval($d[2]/$gr['max']*$this->graph_scale/2);
|
||
$d[3] = 0;
|
||
}
|
||
else
|
||
{
|
||
$d[3] = intval($d[2]/$gr['min']*$this->graph_scale/2);
|
||
$d[2] = 0;
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
foreach ($gr['data'] as &$d)
|
||
$d[2] = intval($d[2]/$gr['max']*$this->graph_scale);
|
||
}
|
||
unset($d);
|
||
}
|
||
$graphs[] = $gr;
|
||
}
|
||
return $graphs;
|
||
}
|
||
|
||
// Рекурсивное построение набора HTML-таблиц
|
||
function recurse_tables($fields, &$bytype, &$data, &$tdkeys)
|
||
{
|
||
if ($fields && $fields[0]['type'] == 'tables')
|
||
{
|
||
$tables = array();
|
||
$my = array_shift($fields);
|
||
foreach ($fields as &$v)
|
||
if ($v['sort'])
|
||
$v['orig_sort'] = &$v['sort'];
|
||
$keys = $my['sort'] ? array_keys($my['sort']) : $my['keys'];
|
||
self::do_sort($keys, $my['sort'], $my['sort_dir']);
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $data))
|
||
{
|
||
foreach ($fields as &$v)
|
||
if ($v['orig_sort'])
|
||
$v['sort'] = &$v['orig_sort'][$k];
|
||
$sub = $this->recurse_tables($fields, $bytype, $data[$k], $tdkeys[$k]);
|
||
foreach ($sub as &$t)
|
||
{
|
||
array_unshift($t['desc'], array(
|
||
'field' => $this->current_src['fielddescs'][$my['field']]['name'],
|
||
'func' => self::$functions[$my['func']]['name'],
|
||
'value' => $this->field_format($my['field'], '', '', $k)
|
||
));
|
||
$tables[] = $t;
|
||
}
|
||
}
|
||
}
|
||
return $tables;
|
||
}
|
||
$tdhead = $this->recurse_head($bytype['td'], $tdkeys);
|
||
$trhead = $this->recurse_head($bytype['tr'], $data);
|
||
$cells = $this->recurse_cells($fields, $data, $tdkeys);
|
||
if (!is_array($cells))
|
||
$cells = array(array($cells));
|
||
$rows = array();
|
||
foreach ($tdhead as $i => &$v)
|
||
{
|
||
foreach ($v as &$h)
|
||
$h['class'] = "h$i";
|
||
$vcopy = $v;
|
||
if ($trhead)
|
||
array_unshift($vcopy, array('class' => 'empty', 'colspan' => count($trhead)));
|
||
$rows[] = $vcopy;
|
||
}
|
||
foreach ($trhead as $i => &$v)
|
||
{
|
||
$rownum = count($tdhead);
|
||
foreach ($v as &$h)
|
||
{
|
||
$rows[$rownum][] = array('text' => $h['text'], 'rowspan' => $h['colspan'], 'class' => "v$i");
|
||
$rownum += $h['colspan'];
|
||
}
|
||
}
|
||
$i = count($tdhead);
|
||
foreach ($cells as &$v)
|
||
{
|
||
if (empty($rows[$i]))
|
||
$rows[$i] = array();
|
||
$rows[$i] = array_merge($rows[$i], $v);
|
||
$i++;
|
||
}
|
||
foreach ($tdhead as &$v)
|
||
{
|
||
if (!empty($v[0]['sort']))
|
||
{
|
||
$r = array();
|
||
if ($trhead)
|
||
$r[] = array('class' => 'empty', 'colspan' => count($trhead));
|
||
foreach ($v as &$h)
|
||
$r[] = array('text' => $h['sort'], 'colspan' => $h['colspan'], 'class' => 'hsort');
|
||
$rows[] = $r;
|
||
}
|
||
}
|
||
foreach (array_reverse($trhead) as $v)
|
||
{
|
||
if (!empty($v[0]['sort']))
|
||
{
|
||
for ($rownum = 0; $rownum < count($tdhead); $rownum++)
|
||
$rows[$rownum][] = array('class' => 'empty');
|
||
foreach ($v as &$h)
|
||
{
|
||
$rows[$rownum][] = array('text' => $h['sort'], 'rowspan' => $h['colspan'], 'class' => 'vsort');
|
||
$rownum += $h['colspan'];
|
||
}
|
||
for (; $rownum < count($rows); $rownum++)
|
||
$rows[$rownum][] = array('class' => 'empty');
|
||
}
|
||
}
|
||
ksort($rows);
|
||
$table = array(
|
||
'desc' => array(),
|
||
'rows' => $rows,
|
||
);
|
||
return array($table);
|
||
}
|
||
|
||
// Рекурсивное построение тела таблицы
|
||
function recurse_cells($fields, &$data, &$tdkeys)
|
||
{
|
||
if (!$fields)
|
||
return $this->field_format($this->cell['field'], $this->cell['func'], $this->cell['aggr'], $data);
|
||
$my = array_shift($fields);
|
||
foreach ($fields as &$v)
|
||
if ($v['sort'])
|
||
$v['orig_sort'] = &$v['sort'];
|
||
$keys = $my['sort'] ? array_keys($my['sort']) : $my['keys'];
|
||
self::do_sort($keys, $my['sort'], $my['sort_dir']);
|
||
if ($my['type'] == 'tr')
|
||
{
|
||
$rows = array();
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $data))
|
||
{
|
||
foreach ($fields as &$v)
|
||
if ($v['type'] == $my['type'] && !empty($v['orig_sort']))
|
||
$v['sort'] = &$v['orig_sort'][$k];
|
||
$r = $this->recurse_cells($fields, $data[$k], $tdkeys);
|
||
if (is_array($r))
|
||
$rows = array_merge($rows, $r);
|
||
else
|
||
$rows[] = array($r);
|
||
}
|
||
}
|
||
return $rows;
|
||
}
|
||
elseif ($my['type'] == 'td')
|
||
{
|
||
$cols = array();
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $tdkeys))
|
||
{
|
||
foreach ($fields as &$v)
|
||
if ($v['type'] == $my['type'] && !empty($v['orig_sort']))
|
||
$v['sort'] = &$v['orig_sort'][$k];
|
||
$r = $this->recurse_cells($fields, $data[$k], $tdkeys[$k]);
|
||
if (is_array($r))
|
||
$cols = array_merge($cols, $r[0]);
|
||
else
|
||
$cols[] = $r;
|
||
}
|
||
}
|
||
return array($cols);
|
||
}
|
||
else // if ($my['type'] == 'cell')
|
||
{
|
||
$list = '<ul>';
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $data))
|
||
{
|
||
foreach ($fields as &$v)
|
||
if ($v['type'] == $my['type'] && $v['orig_sort'])
|
||
$v['sort'] = &$v['orig_sort'][$k];
|
||
$list .= '<li>'.$this->field_format($my['field'], $my['func'], '', $k).': ';
|
||
if ($fields)
|
||
$list .= "\n";
|
||
$sub = $this->recurse_cells($fields, $data[$k], $tdkeys);
|
||
$sub = str_replace('<li>', ' <li>', $sub);
|
||
$list .= $sub;
|
||
$list .= "</li>\n";
|
||
}
|
||
}
|
||
$list .= '</ul>';
|
||
return $list;
|
||
}
|
||
}
|
||
|
||
// Рекурсивное построение заглавной строки/колонки
|
||
function recurse_head($fields, &$data)
|
||
{
|
||
$rows = array();
|
||
if (!$fields)
|
||
return $rows;
|
||
$rows[] = array();
|
||
$my = array_shift($fields);
|
||
foreach ($fields as &$v)
|
||
if ($v['sort'])
|
||
$v['orig_sort'] = &$v['sort'];
|
||
$keys = $my['sort'] ? array_keys($my['sort']) : $my['keys'];
|
||
self::do_sort($keys, $my['sort'], $my['sort_dir']);
|
||
foreach ($keys as $k)
|
||
{
|
||
if (array_key_exists($k, $data))
|
||
{
|
||
foreach ($fields as &$v)
|
||
if ($v['type'] == $my['type'] && !empty($v['orig_sort']))
|
||
$v['sort'] = &$v['orig_sort'][$k];
|
||
$span = 0;
|
||
if ($fields)
|
||
{
|
||
$subrows = $this->recurse_head($fields, $data[$k]);
|
||
foreach ($subrows[0] as $td)
|
||
$span += $td['colspan'];
|
||
foreach ($subrows as $i => &$row)
|
||
{
|
||
if (empty($rows[$i+1]))
|
||
$rows[$i+1] = array();
|
||
$rows[$i+1] = array_merge($rows[$i+1], $row);
|
||
}
|
||
}
|
||
else
|
||
$span = 1;
|
||
$c = array('text' => $this->field_format($my['field'], $my['func'], '', $k), 'colspan' => $span);
|
||
if ($my['sort_field'])
|
||
$c['sort'] = $this->field_format($my['sort_field'], $my['sort_func'], $my['sort_aggr'], $my['sort'][$k]);
|
||
if ($this->add_spanned)
|
||
{
|
||
$c['colspan'] = 1;
|
||
$rows[0][] = $c;
|
||
$c['spanned'] = 1;
|
||
for ($i = 1; $i < $span; $i++)
|
||
$rows[0][] = $c;
|
||
}
|
||
else
|
||
$rows[0][] = $c;
|
||
}
|
||
}
|
||
return $rows;
|
||
}
|
||
|
||
function build_tables($group_fields, &$data, &$tdkeys)
|
||
{
|
||
$bytype = array('td' => array(), 'tr' => array());
|
||
foreach ($group_fields as &$gf)
|
||
$bytype[$gf['type']][] = &$gf;
|
||
$tables = $this->recurse_tables($group_fields, $bytype, $data, $tdkeys);
|
||
return $tables;
|
||
}
|
||
|
||
function func_trans_field($field, $func)
|
||
{
|
||
if ($field === '')
|
||
$field = '1';
|
||
$fd = &$this->current_src['fielddescs'][$field];
|
||
$c = "\$r['$field']";
|
||
if (!empty($fd['is_time']) &&
|
||
(empty(self::$functions[$func]) || !empty(self::$functions[$func]['time_format'])))
|
||
{
|
||
if ($fd['format'] == VMXTemplate::TS_UNIX)
|
||
$c = "date('Y-m-d', $c)";
|
||
elseif ($fd['format'] == VMXTemplate::TS_MW)
|
||
$c = "VMXTemplate::timestamp($c, VMXTemplate::TS_DB)";
|
||
}
|
||
if ($func)
|
||
$c = "self::fn_$func($c)";
|
||
return $c;
|
||
}
|
||
|
||
function sql_trans_field($field, $func, &$tables)
|
||
{
|
||
$fd = &$this->current_src['fielddescs'][$field];
|
||
if (!empty($fd['join']))
|
||
$tables += $fd['join'];
|
||
if (empty($field) && $field !== '0')
|
||
$sql = '1';
|
||
else
|
||
{
|
||
$sql = $field;
|
||
if (isset($fd['sql']))
|
||
$sql = $fd['sql'];
|
||
}
|
||
if (!empty($fd['is_time']) &&
|
||
(empty(self::$functions[$func]) || !empty(self::$functions[$func]['time_format'])))
|
||
{
|
||
if ($fd['format'] == VMXTemplate::TS_UNIX)
|
||
$sql = "FROM_UNIXTIME($sql)";
|
||
elseif ($fd['format'] == VMXTemplate::TS_MW)
|
||
{
|
||
if (isset(self::$mwTimestampSql[$func]))
|
||
$sql = str_replace('$', $sql, self::$mwTimestampSql[$func]);
|
||
$func = NULL;
|
||
}
|
||
}
|
||
if (isset(self::$functions[$func]))
|
||
$sql = str_replace('$', $sql, self::$functions[$func]['sql']);
|
||
return $sql;
|
||
}
|
||
|
||
function field_format($field, $func, $aggr, $value)
|
||
{
|
||
$o = &$this->current_src['fielddescs'][$field]['options_hash'];
|
||
if ($o && $o[$value] && !$aggr)
|
||
return $o[$value];
|
||
elseif (preg_match('/^-?\d+\.\d+$/s', $value))
|
||
{
|
||
$value = number_format($value, self::$decimal[0], self::$decimal[1], self::$decimal[2]);
|
||
return $value;
|
||
}
|
||
elseif (($proc = @$this->current_src['fielddescs'][$field]['format_func']) &&
|
||
is_callable($proc))
|
||
{
|
||
return $proc($value, $this->is_html_format);
|
||
}
|
||
$fn = $this->is_html_format ? 'htmlspecialchars' : 'addslashes';
|
||
return $fn($value);
|
||
}
|
||
|
||
// Сортировать значения $values по $sortvalues =
|
||
// array('значение' => 'ключ сортировки') или по самим себе
|
||
static function do_sort(&$values, &$sortvalues, &$sort_dir)
|
||
{
|
||
$fn = 'sort';
|
||
$sort_dir = strtolower($sort_dir);
|
||
if ($sort_dir == 'desc')
|
||
$fn = 'rsort';
|
||
if ($sortvalues)
|
||
{
|
||
$ufn = $sort_dir == 'desc' ? "OLAP::custom_reverse_cmp" : "OLAP::custom_sort_cmp";
|
||
self::$sort = &$sortvalues;
|
||
usort($values, $ufn);
|
||
}
|
||
else
|
||
$fn($values);
|
||
}
|
||
|
||
static $sort;
|
||
static function custom_sort_cmp($a, $b)
|
||
{
|
||
return self::$sort[$a] < self::$sort[$b] ? -1 : (self::$sort[$a] > self::$sort[$b] ? 1 : 0);
|
||
}
|
||
static function custom_reverse_cmp($a, $b)
|
||
{
|
||
return self::$sort[$a] > self::$sort[$b] ? -1 : (self::$sort[$a] < self::$sort[$b] ? 1 : 0);
|
||
}
|
||
static function group_type_cmp($a, $b)
|
||
{
|
||
$an = $a['type'] == 'graph' ? -1 : self::$group_types[$a['type']]['num'];
|
||
$bn = $b['type'] == 'graph' ? -1 : self::$group_types[$b['type']]['num'];
|
||
$r = $an-$bn;
|
||
if ($r == 0)
|
||
$r = $a['num']-$b['num'];
|
||
return $r;
|
||
}
|
||
|
||
static function tpl_field_format($field, $func, $aggr, $value)
|
||
{
|
||
return "\$tpldata['olap']->field_format($field, $func, $aggr, $value)";
|
||
}
|
||
|
||
static function o_tsformat($f, &$row, $field)
|
||
{
|
||
$tf = "_t$field";
|
||
if (!$row[$tf])
|
||
$row[$tf] = Template::timestamp($row[$field]);
|
||
return date($f, $row[$tf]);
|
||
}
|
||
|
||
//// АГРЕГАТНЫЕ ФУНКЦИИ
|
||
|
||
// Количество - обновление
|
||
static function aggr_update_count(&$aggr, &$value)
|
||
{
|
||
$aggr++;
|
||
}
|
||
|
||
// Сумма - обновление
|
||
static function aggr_update_sum(&$aggr, &$value)
|
||
{
|
||
$aggr += $value;
|
||
}
|
||
|
||
// Сумма округлённая - обновление
|
||
static function aggr_update_sum_rnd(&$aggr, &$value)
|
||
{
|
||
$aggr += $value;
|
||
}
|
||
|
||
// Сумма округлённая - обновление
|
||
static function aggr_finish_sum_rnd(&$aggr)
|
||
{
|
||
$aggr = round($aggr);
|
||
}
|
||
|
||
// Среднее - обновление
|
||
static function aggr_update_avg(&$aggr, &$value)
|
||
{
|
||
$aggr['count']++;
|
||
$aggr['sum'] += $value;
|
||
}
|
||
|
||
// Среднее - финализация
|
||
static function aggr_finish_avg(&$aggr)
|
||
{
|
||
if ($aggr['count'])
|
||
$aggr = $aggr['sum'] / $aggr['count'];
|
||
else
|
||
$aggr = 0;
|
||
}
|
||
|
||
// Минимум - обновление
|
||
static function aggr_update_min(&$aggr, &$value)
|
||
{
|
||
if ($aggr > $value)
|
||
$aggr = $value;
|
||
}
|
||
|
||
// Максимум - обновление
|
||
static function aggr_update_max(&$aggr, &$value)
|
||
{
|
||
if ($aggr < $value)
|
||
$aggr = $value;
|
||
}
|
||
|
||
// Дисперсия - обновление
|
||
static function aggr_update_stddev(&$aggr, &$value)
|
||
{
|
||
$aggr['count']++;
|
||
$aggr['sum'] += $value;
|
||
$aggr['sum2'] += $value*$value;
|
||
}
|
||
|
||
// Дисперсия - финализация
|
||
static function aggr_finish_stddev(&$aggr)
|
||
{
|
||
if ($aggr['count'])
|
||
$aggr = sqrt(($aggr['sum2'] - $aggr['sum']*$aggr['sum']/$aggr['count']) / $aggr['count']);
|
||
else
|
||
$aggr = 0;
|
||
}
|
||
|
||
// Число уникальных - обновление
|
||
static function aggr_update_n_uniq(&$aggr, &$value)
|
||
{
|
||
$aggr[$value] = true;
|
||
}
|
||
|
||
// Число уникальных - финализация
|
||
static function aggr_finish_n_uniq(&$aggr)
|
||
{
|
||
$aggr = count($aggr);
|
||
}
|
||
|
||
// Идея: Оставить последнюю группировку несколькими линиями на одном графике
|
||
// А остальные вытащить в список "групп"
|
||
function tpl_jsgraphs($graphs)
|
||
{
|
||
$groups = array();
|
||
$group = false;
|
||
foreach ($graphs as $i => $graph)
|
||
{
|
||
$min_ne = count($graph['desc'])-1;
|
||
if ($i > 0)
|
||
{
|
||
foreach ($graph['desc'] as $j => $d)
|
||
{
|
||
if ($d['value'] != $graphs[$i-1]['desc'][$j]['value'])
|
||
{
|
||
$min_ne = $j;
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
$a = $b = array();
|
||
for ($j = 0; $j < count($graph['desc']); $j++)
|
||
{
|
||
$d = $graph['desc'][$j];
|
||
$d = $d['field'] . ($d['func'] ? ' ('.strtolower($d['func']).')' : '') .
|
||
': ' . $d['value'];
|
||
$a[] = $d;
|
||
if ($j >= $min_ne)
|
||
$b[] = array('level' => $j, 'text' => $d);
|
||
}
|
||
$graph['last_desc'] = array_pop($a);
|
||
array_pop($b);
|
||
if ($min_ne < count($graph['desc'])-1)
|
||
{
|
||
if ($group)
|
||
$groups[] = $group;
|
||
$group = array(
|
||
'desc' => array_slice($graph['desc'], 0, -1),
|
||
'full_desc' => $a,
|
||
'changed_desc' => $b,
|
||
'graphs' => array(),
|
||
'numeric' => true,
|
||
);
|
||
}
|
||
$group['graphs'][] = $graph;
|
||
$group['numeric'] = $group['numeric'] && $graph['numeric'];
|
||
}
|
||
if ($group)
|
||
$groups[] = $group;
|
||
if ($groups)
|
||
{
|
||
$g = $groups[0]['graphs'][0];
|
||
if ($this->current_src['fielddescs'][$g['fieldid']]['is_time'] &&
|
||
(!$g['funcid'] || $g['funcid'] == 'ym' || $g['funcid'] == 'ymd'))
|
||
{
|
||
$fmt = $g['funcid'] == 'ym' ? '%Y-%m' : '%Y-%m-%d';
|
||
foreach ($groups as &$g)
|
||
$g['date_axis'] = $fmt;
|
||
}
|
||
}
|
||
return $groups;
|
||
}
|
||
}
|