Я работаю над корпоративной системой, перестраиваю отчет. Система написана на php с использованием smarty, zend fw, pear компоненты: pear::db, html_Quickform.
Вопрос возник по запросу (ниже оригинальный),
Цитата:
я его перестраиваю для получения более развернутой формы отчета и я не понимаю где поискать и не пойму что делают некоторые вещи, например:$query = "SELECT DISTINCT sp_tp.id, sp_tp.name, sp_marsh.number,
(SELECT COUNT(1) FROM sp_ts WHERE marsh_id = sp_marsh.id AND sp_ts.tp = sp_tp.id AND sp_ts.date_mont <= ?::date) AS ts_conn_count,
(SELECT COUNT(1) FROM sp_ts WHERE sp_ts.marsh_id = sp_marsh.id AND lastregistration IS NOT NULL
AND sp_ts.tp = sp_tp.id ) AS ts_once_regs,
get_marsh_reis_zayav_count_period(sp_marsh.id, ?::date, ?::date) as reis_plan_count,
(SELECT COUNT(1) FROM ts_direction_changes tdc WHERE tdc.marsh_id = sp_marsh.id AND time >= ?::date AND time <= ?::date+'24:00:00'::interval AND tdc.direction <> 0) as reis_fakt_count,
get_marsh_ts_zayav_count_period(sp_marsh.id, ?::date, ?::date) as ts_plan_count,
vypusk.sum as ts_fakt_count,
COALESCE(ts_regs, 0) AS ts_regs";
$query.=" FROM sp_ts
FULL JOIN (SELECT marsh_id, COUNT(DISTINCT ts) AS ts_regs FROM kt_ts_registrations
WHERE ltime >= (?::timestamp) AND ltime <= (?::timestamp+'24:00'::interval) GROUP BY marsh_id) AS ts_regs_table
ON sp_ts.marsh_id = ts_regs_table.marsh_id
JOIN sp_tp ON sp_tp.id = sp_ts.tp
JOIN sp_marsh ON sp_marsh.id = sp_ts.marsh_id
JOIN (SELECT tmp1.marsh_id,SUM(tmp1.count) FROM
(SELECT ktr.ltime::date, ktr.marsh_id, COUNT(DISTINCT(ktr.ts)) FROM kt_ts_registrations ktr
WHERE ktr.ltime >= ktr.ltime::date+'5:00:00'::interval AND
ktr.ltime <= ktr.ltime::date+'24:00:00'::interval AND
ktr.ltime >= ?::timestamp AND
ktr.ltime <= ?::timestamp+'24:00:00'::interval
GROUP BY ktr.ltime::date, ktr.marsh_id) AS tmp1 GROUP BY tmp1.marsh_id) vypusk ON (sp_ts.marsh_id = vypusk.marsh_id)
";
$query.=" WHERE sp_marsh.number <> '666' AND sp_tp.id <> 16 ORDER BY sp_tp.name";
$res = $db->getAll($query,
array($_GET['end_date'],$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'])
,DB_FETCHMODE_ASSOC);
(SELECT COUNT(1) FROM sp_ts WHERE marsh_id = sp_marsh.id AND sp_ts.tp = sp_tp.id AND sp_ts.date_mont <= ?::date) AS ts_conn_count,
(SELECT COUNT(1) FROM sp_ts WHERE sp_ts.marsh_id = sp_marsh.id AND lastregistration IS NOT NULL
AND sp_ts.tp = sp_tp.id ) AS ts_once_regs,
get_marsh_reis_zayav_count_period(sp_marsh.id, ?::date, ?::date) as reis_plan_count,
(SELECT COUNT(1) FROM ts_direction_changes tdc WHERE tdc.marsh_id = sp_marsh.id AND time >= ?::date AND time <= ?::date+'24:00:00'::interval AND tdc.direction <> 0) as reis_fakt_count,
get_marsh_ts_zayav_count_period(sp_marsh.id, ?::date, ?::date) as ts_plan_count,
vypusk.sum as ts_fakt_count,
COALESCE(ts_regs, 0) AS ts_regs";
$query.=" FROM sp_ts
FULL JOIN (SELECT marsh_id, COUNT(DISTINCT ts) AS ts_regs FROM kt_ts_registrations
WHERE ltime >= (?::timestamp) AND ltime <= (?::timestamp+'24:00'::interval) GROUP BY marsh_id) AS ts_regs_table
ON sp_ts.marsh_id = ts_regs_table.marsh_id
JOIN sp_tp ON sp_tp.id = sp_ts.tp
JOIN sp_marsh ON sp_marsh.id = sp_ts.marsh_id
JOIN (SELECT tmp1.marsh_id,SUM(tmp1.count) FROM
(SELECT ktr.ltime::date, ktr.marsh_id, COUNT(DISTINCT(ktr.ts)) FROM kt_ts_registrations ktr
WHERE ktr.ltime >= ktr.ltime::date+'5:00:00'::interval AND
ktr.ltime <= ktr.ltime::date+'24:00:00'::interval AND
ktr.ltime >= ?::timestamp AND
ktr.ltime <= ?::timestamp+'24:00:00'::interval
GROUP BY ktr.ltime::date, ktr.marsh_id) AS tmp1 GROUP BY tmp1.marsh_id) vypusk ON (sp_ts.marsh_id = vypusk.marsh_id)
";
$query.=" WHERE sp_marsh.number <> '666' AND sp_tp.id <> 16 ORDER BY sp_tp.name";
$res = $db->getAll($query,
array($_GET['end_date'],$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'],
$_GET['start_date'],$_GET['end_date'])
,DB_FETCHMODE_ASSOC);
Цитата:
"::interval", или "tmp1" - это в SUM(tmp1.count), или к откуда "ktr" берется
Помогите пожалуйста ссылкой, или указанием к чему это относится.