zabbixSql/reglament.sql

-- =============================================================================
-- Отчет по регламентным работам для периферийного оборудования (на месяц)
-- =============================================================================

WITH 
-- 1. Получаем уникальные устройства из Zabbix и размножаем их
devices_raw AS (
    SELECT DISTINCT
        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,
        CAST(nullif(regexp_replace(hinv.notes, 'ОБЪЕКТ №\s*', '', 'g'),'') AS INTEGER) AS object_num
    FROM hosts h
    LEFT JOIN host_inventory hinv 
        ON hinv.hostid = h.hostid
    WHERE 
        h.status = 0
        AND h.host NOT LIKE '%WiMAX'
        AND hinv."name" NOT LIKE '%Датчик дорожного полотна%'
        AND hinv."name" != 'Дорожный контроллер Синтез-Д'
        AND (CAST(nullif(regexp_replace(hinv.notes, 'ОБЪЕКТ №\s*', '', 'g'),'') AS INTEGER)  
            IN (1,2,4,5,8,9,11,12,14,24,25,27,28,29,30,31,32,34,35,36,38,40,41,42,43,44,45,46,48,49,50,51,53,54,55,57,58,60,62,63,64,65,66,67,68,69,70,76,77,81,82,84,85)
            OR CAST(nullif(regexp_replace(hinv.notes, 'ОБЪЕКТ №\s*', '', 'g'),'') AS INTEGER)  
            IN (16,39,75,23))
        AND h.hostid != 10725
        AND (
            hinv."name" LIKE '%Видеокамера%' 
            OR hinv."name" LIKE '%Маршрутизатор%'
            OR hinv."name" LIKE '%Детектор%'
            OR hinv."name" LIKE '%Метеостанция%'
            OR hinv."name" LIKE '%Коммутационный шкаф%'
        )
),

-- Размножаем устройства: если есть Маршрутизатор и Коммутационный шкаф, создаем две записи
devices AS (
    -- Видеокамеры
    SELECT 
        host_name,
        visible_name,
        equipment_type,
        'Видеокамера' AS report_type,
        equipment_type AS full_equipment_name,
        object_number,
        coordinates,
        object_num
    FROM devices_raw
    WHERE equipment_type LIKE '%Видеокамера%'
    
    UNION ALL
    
    -- Детекторы
    SELECT 
        host_name,
        visible_name,
        equipment_type,
        'Детектор' AS report_type,
        equipment_type AS full_equipment_name,
        object_number,
        coordinates,
        object_num
    FROM devices_raw
    WHERE equipment_type LIKE '%Детектор%'
    
    UNION ALL
    
    -- Маршрутизаторы (если есть слово Маршрутизатор)
    SELECT 
        host_name,
        visible_name,
        equipment_type,
        'Маршрутизатор' AS report_type,
        REGEXP_REPLACE(equipment_type, '/ Коммутационный шкаф.*$', '') AS full_equipment_name,
        object_number,
        coordinates,
        object_num
    FROM devices_raw
    WHERE equipment_type LIKE '%Маршрутизатор%'
    
    UNION ALL
    
    -- Коммутационные шкафы (если есть слово Коммутационный шкаф)
    SELECT 
        host_name,
        visible_name,
        equipment_type,
        'Коммутационный шкаф' AS report_type,
        'Коммутационный шкаф' AS full_equipment_name,
        object_number,
        coordinates,
        object_num
    FROM devices_raw
    WHERE equipment_type LIKE '%Коммутационный шкаф%'
    
    UNION ALL
    
    -- Метеостанции
    SELECT 
        host_name,
        visible_name,
        equipment_type,
        'Метеостанция' AS report_type,
        equipment_type AS full_equipment_name,
        object_number,
        coordinates,
        object_num
    FROM devices_raw
    WHERE equipment_type LIKE '%Метеостанция%'
),

-- 2. Соединяем устройства с регламентными работами
report_base AS (
    SELECT 
        d.host_name,
        d.visible_name,
        d.equipment_type,
        d.report_type,
        d.full_equipment_name,
        d.object_number,
        d.coordinates,
        d.object_num,
        TO_CHAR(
            CASE 
                WHEN ${end_dt} IS NOT NULL THEN to_date(${end_dt}, 'yyyymmdd')
                ELSE NOW()::date
            END, 
            'DD.MM.YYYY'
        ) AS report_date,
        r.briefdescription AS work_description,
        r."Result" AS work_result,
        ROW_NUMBER() OVER (
            PARTITION BY d.host_name, d.report_type
            ORDER BY r.briefdescription
        ) AS work_num
    FROM devices d
    CROSS JOIN report.reglamentsuka r
    WHERE 
        d.report_type = r."type"
)

-- 3. Финальный вывод
SELECT 
    ROW_NUMBER() OVER (
        ORDER BY 
            object_num NULLS LAST,
            CASE report_type
                WHEN 'Видеокамера' THEN 1
                WHEN 'Детектор' THEN 2
                WHEN 'Маршрутизатор' THEN 3
                WHEN 'Коммутационный шкаф' THEN 4
                WHEN 'Метеостанция' THEN 5
                ELSE 6
            END,
            host_name,
            work_num
    ) AS "№ п/п",
    -- Формируем название оборудования
    CASE 
        WHEN report_type = 'Коммутационный шкаф' THEN 'Коммутационный шкаф'
        WHEN report_type = 'Маршрутизатор' THEN 'Маршрутизатор'
        WHEN report_type IN ('Видеокамера', 'Детектор', 'Метеостанция') 
            THEN full_equipment_name
        ELSE full_equipment_name
    END AS "Оборудование",
    object_num AS "Объект",
    coordinates AS "Координаты",
    work_description AS "Описание_работ",
    report_date AS "Дата",
    work_result AS "Результат"
FROM report_base
ORDER BY "№ п/п";