12024

WITH cte_table1_1 AS (
    SELECT
        mo.name                                                     AS mo_name,
        sf.name                                                     AS t1_1,
        mo.name                                                     AS t1_2,
        vpd.documents_name                                          AS t1_3,
        vpd.efficiency_indicator                                    AS t1_4,
        vpd.rating                                                  AS t1_5,
        string_agg(DISTINCT vpdt.tech_name, ', ')                   AS t1_6,
        opf.name                                                    AS t1_7,
        vpd.operating_organization_name                             AS t1_8,
        m.geo_tag_id                                                AS sf_id,
        rpo.geo_tag_id                                              AS mo_id,
        rpov.sewerage_report_mo_id                                  AS sewerage_report_mo_id
    FROM mski_monitoring.monitoring m
        JOIN reporting_periods rp ON rp.id = m.reporting_period_id
        LEFT JOIN mski_monitoring.regional_program_objects rpo ON rpo.monitoring_id = m.id AND rpo.deleted_at IS NULL
        LEFT JOIN mski_monitoring.regional_program_objects_volga rpov ON rpov.id = rpo.id
        LEFT JOIN volga_river_recovery.volga_program_documents vpd ON vpd.id = rpov.program_document_id AND vpd.deleted_at IS NULL
        LEFT JOIN refs opf ON opf.id = vpd.ownership_id
        LEFT JOIN volga_river_recovery.volga_program_document_tech_rel vpdtr ON vpdtr.volga_program_document_id = vpd.id
        LEFT JOIN volga_river_recovery.volga_program_documents_tech vpdt ON vpdt.id = vpdtr.volga_program_document_tech_id
        JOIN geo_tags sf ON sf.id = m.geo_tag_id
        LEFT JOIN geo_tags mo ON mo.id = rpo.geo_tag_id
    WHERE m.type = 'vrr' AND m.id = :monitoringId
    GROUP BY m.id, rpo.id, sf_id, mo_id, mo_name, rpov.sewerage_report_mo_id, t1_1, t1_2, t1_3, t1_4, t1_5, t1_7, t1_8
), cte_table1_2 AS (
    SELECT
        srmsi.population                                            AS t1_9,
        (
            coalesce(srmc.discharged_surface_wastewater, 0) +
            coalesce(srmcs.water_pollution, 0) +
            coalesce(srmcs.water_pollution_excess, 0) +
            coalesce(srmcs.water_clear_standart, 0)
        )                                                           AS t1_10,
        (
            coalesce(srmc.discharged_surface_wastewater, 0) -
            coalesce(srmc.surface_wastewater_flowing_rain_sewer, 0) +
            coalesce(srmcs.water_pollution, 0) +
            coalesce(srmcs.water_pollution_excess, 0)
        )                                                           AS t1_11,
        coalesce(srmc.discharged_surface_wastewater, 0)             AS t1_12,
        (
            coalesce(srmc.discharged_surface_wastewater, 0) -
            coalesce(srmc.surface_wastewater_flowing_rain_sewer, 0)
        )                                                           AS t1_13,
        0                                                           AS t1_14,
        coalesce(srmcs.water_pollution, 0)                          AS t1_15,
        coalesce(srmcs.water_pollution_excess, 0)                   AS t1_16,
        coalesce(srmcs.water_clear_standart, 0)                     AS t1_17,
        sr.geo_tag_id                                               AS sf_id,
        srm.geo_tag_id                                              AS mo_id,
        srm.id                                                      AS sewerage_report_mo_id
    FROM volga_river_recovery.sewerages_reports sr
        LEFT JOIN volga_river_recovery.sewerages_reports_mo srm ON srm.sewerage_report_id = sr.id
        LEFT JOIN volga_river_recovery.sewerages_reports_mo_service_implementation srmsi ON srmsi.municipal_area_id = srm.id
        LEFT JOIN volga_river_recovery.sewerages_reports_mo_clean_sewage srmcs ON srmcs.municipal_area_id = srm.id
        LEFT JOIN volga_river_recovery.sewerages_reports_mo_css srmc ON srmc.municipal_area_id = srm.id
    WHERE sr.reporting_period_id = (SELECT m.reporting_period_id FROM mski_monitoring.monitoring m WHERE m.type = 'vrr' AND m.id = :monitoringId)
), cte_data_table1 AS (
    SELECT * FROM cte_table1_1 t1
        JOIN cte_table1_2 t2 USING(sf_id, mo_id, sewerage_report_mo_id)
), cte_data_table2 AS (
    SELECT
        row_number() OVER (
            PARTITION BY m.geo_tag_id, mo.name
            ORDER BY m.geo_tag_id, mo.name
        )                                                           AS t2_1,
        so.sewerage_object_name                                     AS t2_2,
        sss.name                                                    AS t2_3,
        so_type.name                                                AS t2_4,
        status.name                                                 AS t2_5,
        work_type.name                                              AS t2_6,
        owner.name                                                  AS t2_7,
        so_base_operation.name                                      AS t2_8,
        get_address_short(a.full_address)                           AS t2_9,
        soq.commissioning_year                                      AS t2_10,
        coalesce(soq.amortization_wear, 0)                          AS t2_11,
        coalesce(soq.physical_wear, 0)                              AS t2_12,
        so.design_parameters                                        AS t2_13,
        coalesce(rpol.before_specific_operating_costs, 0)           AS t2_14,
        coalesce(rpol.after_specific_operating_costs, 0)            AS t2_15,
        coalesce(rpol.before_commissioning_power_consumption, 0)    AS t2_16,
        coalesce(rpol.after_commissioning_power_consumption, 0)     AS t2_17,
        m.geo_tag_id                                                AS sf_id,
        rpo.geo_tag_id                                              AS mo_id,
        mo.name                                                     AS mo_name,
        rpov.sewerage_report_mo_id                                  AS sewerage_report_mo_id
    FROM mski_monitoring.monitoring m
        JOIN reporting_periods rp ON rp.id = m.reporting_period_id
        LEFT JOIN mski_monitoring.regional_program_objects rpo ON rpo.monitoring_id = m.id AND rpo.deleted_at IS NULL
        LEFT JOIN mski_monitoring.regional_program_objects_volga rpov ON rpov.id = rpo.id
        LEFT JOIN volga_river_recovery.volga_program_documents vpd ON vpd.id = rpov.program_document_id AND vpd.deleted_at IS NULL
        LEFT JOIN mski_monitoring.regional_program_objects_layout rpol ON rpol.object_id = rpo.id AND rpol.type = 'vrr'
        LEFT JOIN volga_river_recovery.volga_program_documents_sewerage_objects_rel vpdsor ON vpdsor.volga_program_document_id = vpd.id
        LEFT JOIN volga_river_recovery.sewerage_objects so ON so.id = vpdsor.sewerage_object_id
        LEFT JOIN volga_river_recovery.sewerage_systems_settlements sss ON sss.id = so.sewerage_systems_settlements_id AND sss.deleted_at IS NULL
        LEFT JOIN refs so_type ON so.type_id = so_type.id
        JOIN refs status ON status.id = so.status_id
        LEFT JOIN refs so_base_operation ON so_base_operation.id = so.base_operation_id
        LEFT JOIN addresses a ON a.id = so.address
        LEFT JOIN volga_river_recovery.sewerage_objects_queues soq ON so.id = soq.sewerage_objects_id AND soq.deleted_at IS NULL
        LEFT JOIN refs work_type ON work_type.id = vpd.work_type_id
        LEFT JOIN refs owner ON owner.id = vpd.type_of_ownership_id
        LEFT JOIN geo_tags mo ON mo.id = rpo.geo_tag_id
    WHERE m.type = 'vrr' AND m.id = :monitoringId
    GROUP BY m.geo_tag_id, rpo.geo_tag_id, mo.name, rpov.sewerage_report_mo_id, t2_2, t2_3, t2_4, t2_5, t2_6, t2_7, t2_8, t2_9, t2_10, t2_11, t2_12, t2_13, t2_14, t2_15, t2_16, t2_17
)
SELECT * FROM cte_data_table1 t1
    JOIN cte_data_table2 t2 USING(sf_id, mo_id, mo_name, sewerage_report_mo_id)
ORDER BY sf_id, mo_name, sewerage_report_mo_id