478 lines
18 KiB
SQL
478 lines
18 KiB
SQL
WITH
|
|
-- 1. Получаем все проверки ICMP за период
|
|
checks AS (
|
|
SELECT
|
|
h.host AS host_name,
|
|
COALESCE(h.name, h.host) AS visible_name,
|
|
COALESCE(hinv."name", 'не указан') AS equipment_type,
|
|
COALESCE(hinv.notes, '') AS object_number,
|
|
CASE
|
|
WHEN hinv.location_lat IS NOT NULL AND hinv.location_lon IS NOT NULL
|
|
AND hinv.location_lat != '' AND hinv.location_lon != ''
|
|
THEN hinv.location_lat || ' ' || hinv.location_lon
|
|
WHEN hinv.location_lat IS NOT NULL AND hinv.location_lat != ''
|
|
THEN hinv.location_lat
|
|
WHEN hinv.location_lon IS NOT NULL AND hinv.location_lon != ''
|
|
THEN hinv.location_lon
|
|
ELSE ''
|
|
END AS coordinates,
|
|
TO_TIMESTAMP(hx.clock) AT TIME ZONE 'Europe/Moscow' AS check_time,
|
|
hx.value AS status
|
|
FROM hosts h
|
|
INNER JOIN items i
|
|
ON i.hostid = h.hostid
|
|
AND i.key_ = 'icmpping'
|
|
AND i.status = 0
|
|
AND i.value_type = 3
|
|
INNER JOIN history_uint hx
|
|
ON hx.itemid = i.itemid
|
|
LEFT JOIN host_inventory hinv
|
|
ON hinv.hostid = h.hostid
|
|
WHERE
|
|
h.status = 0
|
|
AND h.host NOT LIKE '%WiMAX'
|
|
AND CAST(NULLIF(REGEXP_REPLACE(hinv.notes, 'ОБЪЕКТ №\s*', '', 'g'), '') AS INTEGER)
|
|
BETWEEN 1 AND 85
|
|
AND TO_TIMESTAMP(hx.clock) >= TO_DATE(${start_dt}, 'yyyymmdd') - INTERVAL '1 day'
|
|
AND TO_TIMESTAMP(hx.clock) <= TO_DATE(${end_dt}, 'yyyymmdd') + INTERVAL '1 day'
|
|
),
|
|
|
|
-- 2. Добавляем предыдущий статус
|
|
checks_with_prev AS (
|
|
SELECT
|
|
*,
|
|
LAG(status) OVER (PARTITION BY host_name ORDER BY check_time) AS prev_status,
|
|
COALESCE(LEAD(check_time) OVER (PARTITION BY host_name ORDER BY check_time), NOW()) AS next_check_time
|
|
FROM checks
|
|
),
|
|
|
|
-- 3. Определяем моменты смены статуса
|
|
status_changes AS (
|
|
SELECT
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
check_time,
|
|
status,
|
|
prev_status,
|
|
CASE
|
|
WHEN DATE(next_check_time) > DATE(check_time)
|
|
THEN DATE_TRUNC('day', check_time) + INTERVAL '1 day'
|
|
ELSE next_check_time
|
|
END AS next_check_time,
|
|
CASE WHEN status != prev_status THEN 1 ELSE 0 END AS is_start
|
|
FROM checks_with_prev
|
|
UNION ALL
|
|
SELECT
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
DATE_TRUNC('day', next_check_time) + INTERVAL '00:00:00' AS check_time,
|
|
status,
|
|
prev_status,
|
|
next_check_time,
|
|
1 AS is_start
|
|
FROM checks_with_prev
|
|
WHERE DATE(next_check_time) > DATE(check_time)
|
|
),
|
|
|
|
-- 4. Нумеруем интервалы
|
|
intervals AS (
|
|
SELECT
|
|
*,
|
|
SUM(is_start) OVER (PARTITION BY host_name ORDER BY check_time) AS interval_id
|
|
FROM status_changes
|
|
WHERE status IS NOT NULL
|
|
AND DATE_TRUNC('day', check_time) >= TO_DATE(${start_dt}, 'yyyymmdd')
|
|
AND DATE_TRUNC('day', check_time) <= TO_DATE(${end_dt}, 'yyyymmdd')
|
|
),
|
|
|
|
-- 5. Группируем интервалы
|
|
grouped_intervals AS (
|
|
SELECT
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
MIN(check_time) AS check_time,
|
|
MAX(next_check_time) AS next_check_time,
|
|
MAX(status) AS status
|
|
FROM intervals
|
|
GROUP BY
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
interval_id
|
|
),
|
|
|
|
-- 6. Посуточная агрегация (часы доступности)
|
|
daily_stats_raw AS (
|
|
SELECT
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
DATE(check_time) AS check_date,
|
|
ROUND(
|
|
SUM(CASE WHEN status = 1 THEN EXTRACT(EPOCH FROM (next_check_time - check_time)) / 3600 ELSE 0 END) * 2
|
|
) / 2 AS available_hours
|
|
FROM grouped_intervals
|
|
GROUP BY
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
DATE(check_time)
|
|
),
|
|
|
|
-- 7. Статистика по периферии (для маршрутизаторов)
|
|
peripheral_stats AS (
|
|
SELECT
|
|
object_number,
|
|
check_date,
|
|
MAX(CASE WHEN available_hours > 12 THEN 1 ELSE 0 END) AS has_peripheral_work
|
|
FROM daily_stats_raw
|
|
WHERE equipment_type NOT LIKE 'Маршрутизатор%'
|
|
GROUP BY object_number, check_date
|
|
),
|
|
|
|
-- 8. Собираем все дни месяца
|
|
all_dates AS (
|
|
SELECT generate_series(
|
|
TO_DATE(${start_dt}, 'yyyymmdd'),
|
|
TO_DATE(${end_dt}, 'yyyymmdd'),
|
|
'1 day'::interval
|
|
)::date AS check_date
|
|
),
|
|
|
|
-- 9. Кросс-джойн для каждого оборудования
|
|
equipment_dates AS (
|
|
SELECT DISTINCT
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
ad.check_date
|
|
FROM daily_stats_raw d
|
|
CROSS JOIN all_dates ad
|
|
),
|
|
|
|
-- 10. Присоединяем доступность ко всем дням
|
|
daily_stats_full AS (
|
|
SELECT
|
|
ed.visible_name,
|
|
ed.equipment_type,
|
|
ed.object_number,
|
|
ed.coordinates,
|
|
ed.check_date,
|
|
COALESCE(dsr.available_hours, 0) AS available_hours
|
|
FROM equipment_dates ed
|
|
LEFT JOIN daily_stats_raw dsr
|
|
ON dsr.visible_name = ed.visible_name
|
|
AND dsr.check_date = ed.check_date
|
|
),
|
|
|
|
-- 11. Финальная агрегация по оборудованию
|
|
equipment_summary AS (
|
|
SELECT
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
STRING_AGG(
|
|
CASE
|
|
WHEN available_hours > 0 THEN TO_CHAR(check_date, 'DD.MM') || ':' || available_hours::TEXT
|
|
ELSE NULL
|
|
END,
|
|
'; ' ORDER BY check_date
|
|
) AS daily_schedule,
|
|
COUNT(CASE WHEN available_hours > 12 THEN 1 END) AS days_gt_12h,
|
|
COUNT(CASE WHEN available_hours <= 12 AND available_hours > 0 THEN 1 END) AS days_le_12h,
|
|
ROUND(AVG(available_hours), 3) AS avg_hours,
|
|
-- Суммарные сутки доступности (факт) - по каждому устройству
|
|
ROUND(SUM(available_hours) / 24, 0) AS total_days_per_device
|
|
FROM daily_stats_full
|
|
GROUP BY
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates
|
|
),
|
|
|
|
-- 12. Отдельно считаем работу маршрутизаторов
|
|
router_work AS (
|
|
SELECT
|
|
d.object_number,
|
|
COUNT(DISTINCT d.check_date) AS router_work_days
|
|
FROM daily_stats_raw d
|
|
INNER JOIN peripheral_stats p
|
|
ON p.object_number = d.object_number
|
|
AND p.check_date = d.check_date
|
|
WHERE d.equipment_type LIKE 'Маршрутизатор%'
|
|
AND p.has_peripheral_work = 1
|
|
GROUP BY d.object_number
|
|
),
|
|
|
|
-- 13. Определяем этап
|
|
stages AS (
|
|
SELECT DISTINCT
|
|
object_number,
|
|
CASE
|
|
WHEN object_number IN ('4','28','30','31','34','36','40','42','55','57','58','62','63','64','67','68','69','76','81','82','85') THEN '1 этап'
|
|
WHEN object_number IN ('1','9','12','27','35','38','41','43','44','48','49','51','53','54','60','65','77','84') THEN '2 этап'
|
|
ELSE '3 этап'
|
|
END AS stage
|
|
FROM equipment_summary
|
|
),
|
|
|
|
-- 14. Подготавливаем данные для PIVOT-агрегации
|
|
daily_pivot AS (
|
|
SELECT
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
check_date,
|
|
available_hours
|
|
FROM daily_stats_full
|
|
),
|
|
|
|
-- 15. СВОДНАЯ ТАБЛИЦА по типам оборудования
|
|
summary_by_type AS (
|
|
SELECT
|
|
equipment_type,
|
|
-- оборудование-сутки факт (сумма по всем устройствам типа)
|
|
ROUND(SUM(total_days_per_device), 0) AS fact_days,
|
|
-- Кол-во оборудования (статичное из Excel)
|
|
CASE
|
|
WHEN equipment_type LIKE 'Видеокамера%' THEN 85
|
|
WHEN equipment_type LIKE 'Детектор%' THEN 79
|
|
WHEN equipment_type LIKE 'Метеостанция%' THEN 30
|
|
WHEN equipment_type LIKE 'Маршрутизатор%' THEN 88
|
|
WHEN equipment_type LIKE 'Коммутационный шкаф%' THEN 88
|
|
WHEN equipment_type LIKE 'Криптошлюз%' THEN 88
|
|
ELSE 0
|
|
END AS equipment_count,
|
|
-- оборудование-сутки план = 30 дней × количество оборудования
|
|
CASE
|
|
WHEN equipment_type LIKE 'Видеокамера%' THEN 30 * 85
|
|
WHEN equipment_type LIKE 'Детектор%' THEN 30 * 79
|
|
WHEN equipment_type LIKE 'Метеостанция%' THEN 30 * 30
|
|
WHEN equipment_type LIKE 'Маршрутизатор%' THEN 30 * 88
|
|
WHEN equipment_type LIKE 'Коммутационный шкаф%' THEN 30 * 88
|
|
WHEN equipment_type LIKE 'Криптошлюз%' THEN 30 * 88
|
|
ELSE 0
|
|
END AS plan_days,
|
|
-- оборудование-сутки процент = факт / план
|
|
ROUND(SUM(total_days_per_device) /
|
|
CASE
|
|
WHEN equipment_type LIKE 'Видеокамера%' THEN 30 * 85
|
|
WHEN equipment_type LIKE 'Детектор%' THEN 30 * 79
|
|
WHEN equipment_type LIKE 'Метеостанция%' THEN 30 * 30
|
|
WHEN equipment_type LIKE 'Маршрутизатор%' THEN 30 * 88
|
|
WHEN equipment_type LIKE 'Коммутационный шкаф%' THEN 30 * 88
|
|
WHEN equipment_type LIKE 'Криптошлюз%' THEN 30 * 88
|
|
ELSE 1
|
|
END, 9) AS percent,
|
|
-- Тариф в сутки (статичный из Excel)
|
|
CASE
|
|
WHEN equipment_type LIKE 'Видеокамера%' THEN 43038.15
|
|
WHEN equipment_type LIKE 'Детектор%' THEN 91293.59
|
|
WHEN equipment_type LIKE 'Метеостанция%' THEN 53323.20
|
|
WHEN equipment_type LIKE 'Маршрутизатор%' THEN 8868.36
|
|
WHEN equipment_type LIKE 'Коммутационный шкаф%' THEN 4514.60
|
|
WHEN equipment_type LIKE 'Криптошлюз%' THEN 10883.72
|
|
ELSE 0
|
|
END AS daily_rate,
|
|
-- Итоговая сумма = Тариф × Кол-во дней в месяце × процент
|
|
ROUND(
|
|
CASE
|
|
WHEN equipment_type LIKE 'Видеокамера%' THEN 43038.15
|
|
WHEN equipment_type LIKE 'Детектор%' THEN 91293.59
|
|
WHEN equipment_type LIKE 'Метеостанция%' THEN 53323.20
|
|
WHEN equipment_type LIKE 'Маршрутизатор%' THEN 8868.36
|
|
WHEN equipment_type LIKE 'Коммутационный шкаф%' THEN 4514.60
|
|
WHEN equipment_type LIKE 'Криптошлюз%' THEN 10883.72
|
|
ELSE 0
|
|
END * 30 *
|
|
(SUM(total_days_per_device) /
|
|
CASE
|
|
WHEN equipment_type LIKE 'Видеокамера%' THEN 30 * 85
|
|
WHEN equipment_type LIKE 'Детектор%' THEN 30 * 79
|
|
WHEN equipment_type LIKE 'Метеостанция%' THEN 30 * 30
|
|
WHEN equipment_type LIKE 'Маршрутизатор%' THEN 30 * 88
|
|
WHEN equipment_type LIKE 'Коммутационный шкаф%' THEN 30 * 88
|
|
WHEN equipment_type LIKE 'Криптошлюз%' THEN 30 * 88
|
|
ELSE 1
|
|
END),
|
|
2
|
|
) AS total_amount
|
|
FROM equipment_summary
|
|
GROUP BY equipment_type
|
|
)
|
|
|
|
-- 16. Итоговый SELECT (детальный отчёт)
|
|
SELECT
|
|
'ДЕТАЛЬНЫЙ ОТЧЁТ' AS section,
|
|
s.stage AS "Этап",
|
|
s.object_number AS "№ объекта",
|
|
e.equipment_type AS "Вид оборудования",
|
|
e.coordinates AS "Координаты",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-01' THEN d.available_hours END) AS "01.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-02' THEN d.available_hours END) AS "02.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-03' THEN d.available_hours END) AS "03.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-04' THEN d.available_hours END) AS "04.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-05' THEN d.available_hours END) AS "05.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-06' THEN d.available_hours END) AS "06.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-07' THEN d.available_hours END) AS "07.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-08' THEN d.available_hours END) AS "08.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-09' THEN d.available_hours END) AS "09.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-10' THEN d.available_hours END) AS "10.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-11' THEN d.available_hours END) AS "11.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-12' THEN d.available_hours END) AS "12.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-13' THEN d.available_hours END) AS "13.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-14' THEN d.available_hours END) AS "14.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-15' THEN d.available_hours END) AS "15.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-16' THEN d.available_hours END) AS "16.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-17' THEN d.available_hours END) AS "17.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-18' THEN d.available_hours END) AS "18.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-19' THEN d.available_hours END) AS "19.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-20' THEN d.available_hours END) AS "20.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-21' THEN d.available_hours END) AS "21.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-22' THEN d.available_hours END) AS "22.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-23' THEN d.available_hours END) AS "23.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-24' THEN d.available_hours END) AS "24.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-25' THEN d.available_hours END) AS "25.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-26' THEN d.available_hours END) AS "26.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-27' THEN d.available_hours END) AS "27.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-28' THEN d.available_hours END) AS "28.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-29' THEN d.available_hours END) AS "29.06",
|
|
MAX(CASE WHEN d.check_date = DATE '2026-06-30' THEN d.available_hours END) AS "30.06",
|
|
e.days_gt_12h AS "Дней >12 ч",
|
|
e.days_le_12h AS "Дней ≤12 ч",
|
|
e.avg_hours AS "Среднее часов",
|
|
COALESCE(rw.router_work_days, 0) AS "Маршрутизатор работа в днях"
|
|
FROM equipment_summary e
|
|
LEFT JOIN stages s ON s.object_number = e.object_number
|
|
LEFT JOIN daily_pivot d
|
|
ON d.visible_name = e.visible_name
|
|
AND d.equipment_type = e.equipment_type
|
|
AND d.object_number = e.object_number
|
|
AND d.coordinates = e.coordinates
|
|
LEFT JOIN router_work rw ON rw.object_number = e.object_number
|
|
WHERE e.daily_schedule IS NOT NULL
|
|
GROUP BY
|
|
s.stage,
|
|
s.object_number,
|
|
e.equipment_type,
|
|
e.coordinates,
|
|
e.days_gt_12h,
|
|
e.days_le_12h,
|
|
e.avg_hours,
|
|
rw.router_work_days
|
|
|
|
UNION ALL
|
|
|
|
-- 17. СВОДНАЯ ТАБЛИЦА (как в Excel)
|
|
SELECT
|
|
'СВОДНАЯ' AS section,
|
|
'' AS "Этап",
|
|
'' AS "№ объекта",
|
|
s.equipment_type AS "Вид оборудования",
|
|
'' AS "Координаты",
|
|
NULL AS "01.06",
|
|
NULL AS "02.06",
|
|
NULL AS "03.06",
|
|
NULL AS "04.06",
|
|
NULL AS "05.06",
|
|
NULL AS "06.06",
|
|
NULL AS "07.06",
|
|
NULL AS "08.06",
|
|
NULL AS "09.06",
|
|
NULL AS "10.06",
|
|
NULL AS "11.06",
|
|
NULL AS "12.06",
|
|
NULL AS "13.06",
|
|
NULL AS "14.06",
|
|
NULL AS "15.06",
|
|
NULL AS "16.06",
|
|
NULL AS "17.06",
|
|
NULL AS "18.06",
|
|
NULL AS "19.06",
|
|
NULL AS "20.06",
|
|
NULL AS "21.06",
|
|
NULL AS "22.06",
|
|
NULL AS "23.06",
|
|
NULL AS "24.06",
|
|
NULL AS "25.06",
|
|
NULL AS "26.06",
|
|
NULL AS "27.06",
|
|
NULL AS "28.06",
|
|
NULL AS "29.06",
|
|
NULL AS "30.06",
|
|
-- Колонки сводной таблицы в правильном порядке
|
|
s.fact_days AS "оборудование-сутки факт",
|
|
s.plan_days AS "оборудование-сутки план",
|
|
s.percent AS "оборудование-сутки процент",
|
|
s.equipment_count AS "Кол-во оборудования",
|
|
s.daily_rate AS "Тариф в сутки по договору ИТСР",
|
|
s.total_amount AS "Итоговая сумма к начислению"
|
|
FROM summary_by_type s
|
|
|
|
UNION ALL
|
|
|
|
-- 18. ИТОГОВАЯ СТРОКА
|
|
SELECT
|
|
'ИТОГО' AS section,
|
|
'' AS "Этап",
|
|
'' AS "№ объекта",
|
|
'Итоговая Сумма' AS "Вид оборудования",
|
|
'' AS "Координаты",
|
|
NULL AS "01.06",
|
|
NULL AS "02.06",
|
|
NULL AS "03.06",
|
|
NULL AS "04.06",
|
|
NULL AS "05.06",
|
|
NULL AS "06.06",
|
|
NULL AS "07.06",
|
|
NULL AS "08.06",
|
|
NULL AS "09.06",
|
|
NULL AS "10.06",
|
|
NULL AS "11.06",
|
|
NULL AS "12.06",
|
|
NULL AS "13.06",
|
|
NULL AS "14.06",
|
|
NULL AS "15.06",
|
|
NULL AS "16.06",
|
|
NULL AS "17.06",
|
|
NULL AS "18.06",
|
|
NULL AS "19.06",
|
|
NULL AS "20.06",
|
|
NULL AS "21.06",
|
|
NULL AS "22.06",
|
|
NULL AS "23.06",
|
|
NULL AS "24.06",
|
|
NULL AS "25.06",
|
|
NULL AS "26.06",
|
|
NULL AS "27.06",
|
|
NULL AS "28.06",
|
|
NULL AS "29.06",
|
|
NULL AS "30.06",
|
|
SUM(s.fact_days) AS "оборудование-сутки факт",
|
|
SUM(s.plan_days) AS "оборудование-сутки план",
|
|
ROUND(SUM(s.fact_days) / SUM(s.plan_days), 9) AS "оборудование-сутки процент",
|
|
SUM(s.equipment_count) AS "Кол-во оборудования",
|
|
NULL AS "Тариф в сутки по договору ИТСР",
|
|
SUM(s.total_amount) AS "Итоговая сумма к начислению"
|
|
FROM summary_by_type s
|
|
|
|
ORDER BY section DESC, "Этап", "№ объекта", "Вид оборудования"; |