12667

gistfile1.txt

WITH cte_reporting_period AS (
    SELECT id
    FROM public.reporting_periods rp
    WHERE rp."year" :: INT = :reportingPeriodYear AND rp."type" = :typeAssessmentStateSwss
)
SELECT
    coalesce(to_char(pfm.updated_at, 'DD-MM-YYYY'), 'Не заполнено')                         AS "updatedTime",
    pfm.plumbing_facilities_id                                                              AS "plumbingFacilities",
    cr.inn                                                                                  AS "requisite",
    coalesce(legal_form.name, 'Не заполнено')                                               AS "legalForm",
    coalesce(cr.name_short, 'Не заполнено')                                                 AS "nameVkh",
    coalesce(gt.name, 'Не заполнено')                                                       AS "moAffiliation",
    coalesce(pfm.subscribers_count, 0)                                                      AS "subscribersCount",
    CASE WHEN pfm.guaranteeing_organization 
        THEN 'Да'
        ELSE 'Нет'
    END                                                                                     AS "guaranteeingOrganization",
    coalesce(pfm.without_sewerage_population_total, 0)                                      AS "withoutSeweragePopulationTotal",
    coalesce(pfm.without_sewerage_population_wastes_export, 0)                              AS "withoutSeweragePopulationWastesExport",
    coalesce(pfm.networks_length_gravity_total, 0.0)                                        AS "networksLengthGravityTotal",
    coalesce(pfm.networks_length_pressure_total, 0.0)                                       AS "networksLengthPressureTotal",
    coalesce(pfm.pumping_station_total_count, 0)                                            AS "pumpingStationTotalCount",
    coalesce(pfm.networks_length_total, 0.0)                                                AS "networksLengthTotal",
    coalesce(pfm.rain_sewer_discharge_count_total, 0)                                       AS "rainSewerDischargeCountTotal",
    coalesce(pfm.rain_sewer_in_operation_count, 0)                                          AS "rainSewerInOperationCount",
    coalesce(pfm.implemented_total, 0.0)                                                    AS "implementedTotal",
    coalesce(pfm.implemented_first_half, 0.0)                                               AS "implementedFirstHalf",
    coalesce(pfm.implemented_second_half, 0.0)                                              AS "implementedSecondHalf",
    coalesce(pfm.implemented_other_consumers, 0.0)                                          AS "implementedOtherConsumers",
    coalesce(pfm.first_half_tariff, 0.0)                                                    AS "firstHalfTariff",
    coalesce(pfm.second_half_tariff, 0.0)                                                   AS "secondHalfTariff",
    coalesce(pfm.other_customers_tariff, 0.0)                                               AS "otherCustomersTariff",
    coalesce(pfm.amount_of_receivables, 0.0)                                                AS "amountOfReceivables",
    coalesce(pfm.amount_payable, 0.0)                                                       AS "amountPayable",
    coalesce(to_char(pfm.investment_program_approval_date, 'DD-MM-YYYY'), 'Не заполнено')   AS "investmentProgramApprovalDate",
    coalesce(pfm.financial_assets_total, 0.0)                                               AS "financialAssetsTotal",
    coalesce(pfm.financial_assets_depreciation_sum, 0.0)                                    AS "financialAssetsDepreciationSum",
    coalesce(pfm.financial_assets_capital_expenditure_sum, 0.0)                             AS "financialAssetsCapitalExpenditureSum",
    coalesce(pfm.financial_assets_budget, 0.0)                                              AS "financialAssetsBudget",
    coalesce(pfm.financial_assets_other_sources_sum, 0.0)                                   AS "financialAssetsOtherSourcesSum",
    coalesce(pfm.financial_assets_profit, 0.0)                                              AS "financialAssetsProfit",
    coalesce(pfm.financial_assets_lesion, 0.0)                                              AS "financialAssetsLesion",
    coalesce(pfm.financial_assets_depreciation_deductions_sum, 0.0)                         AS "financialAssetsDepreciationDeductionsSum",
    sos.geo_tag_id AS sos_geo_tag_id,
    pfm.geo_tag_id AS pfm_geo_tag_id
FROM volga_river_recovery.plumbing_facilities_mo pfm
    JOIN volga_river_recovery.plumbing_facilities pf ON pfm.plumbing_facilities_id = pf.id AND pf.deleted_at IS NULL
    JOIN volga_river_recovery.sewerage_object_state sos ON pf.sewerage_object_state_id = sos.id AND sos.reporting_period_id = (SELECT id FROM cte_reporting_period)
    JOIN public.company_requisites cr ON cr.id = pf.requisite_id
    JOIN refs legal_form ON legal_form.id = pf.legal_form_id 
    JOIN geo_tags gt on pfm.geo_tag_id = gt.id
WHERE pfm.deleted_at IS NULL
LIMIT :pageSize
OFFSET :pageNumber * :pageSize



WITH cte_reporting_period AS (
    SELECT id
    FROM public.reporting_periods rp
    WHERE rp.year :: INT = :reportingPeriodYear AND rp.type = :typeAssessmentStateSwss
), cte_geo_tags AS (
    SELECT gt.id AS geo_tag_id, *
    FROM fias_addrobj fa
        JOIN geo_tags gt on gt.oktmo = fa.oktmo AND gt.level = 4
    WHERE aoguid = :aoGuid
)
--SELECT * FROM cte_geo_tags

, cte_data AS (
    SELECT
        coalesce(to_char(pfm.updated_at, 'DD-MM-YYYY'), 'Не заполнено')                         AS "updatedTime",
        pfm.plumbing_facilities_id                                                              AS "plumbingFacilities",
        cr.inn                                                                                  AS "requisite",
        coalesce(legal_form.name, 'Не заполнено')                                               AS "legalForm",
        coalesce(cr.name_short, 'Не заполнено')                                                 AS "nameVkh",
        coalesce(gt.name, 'Не заполнено')                                                       AS "moAffiliation",
        coalesce(pfm.subscribers_count, 0)                                                      AS "subscribersCount",
        CASE WHEN pfm.guaranteeing_organization 
            THEN 'Да'
            ELSE 'Нет'
        END                                                                                     AS "guaranteeingOrganization",
        coalesce(pfm.without_sewerage_population_total, 0)                                      AS "withoutSeweragePopulationTotal",
        coalesce(pfm.without_sewerage_population_wastes_export, 0)                              AS "withoutSeweragePopulationWastesExport",
        coalesce(pfm.networks_length_gravity_total, 0.0)                                        AS "networksLengthGravityTotal",
        coalesce(pfm.networks_length_pressure_total, 0.0)                                       AS "networksLengthPressureTotal",
        coalesce(pfm.pumping_station_total_count, 0)                                            AS "pumpingStationTotalCount",
        coalesce(pfm.networks_length_total, 0.0)                                                AS "networksLengthTotal",
        coalesce(pfm.rain_sewer_discharge_count_total, 0)                                       AS "rainSewerDischargeCountTotal",
        coalesce(pfm.rain_sewer_in_operation_count, 0)                                          AS "rainSewerInOperationCount",
        coalesce(pfm.implemented_total, 0.0)                                                    AS "implementedTotal",
        coalesce(pfm.implemented_first_half, 0.0)                                               AS "implementedFirstHalf",
        coalesce(pfm.implemented_second_half, 0.0)                                              AS "implementedSecondHalf",
        coalesce(pfm.implemented_other_consumers, 0.0)                                          AS "implementedOtherConsumers",
        coalesce(pfm.first_half_tariff, 0.0)                                                    AS "firstHalfTariff",
        coalesce(pfm.second_half_tariff, 0.0)                                                   AS "secondHalfTariff",
        coalesce(pfm.other_customers_tariff, 0.0)                                               AS "otherCustomersTariff",
        coalesce(pfm.amount_of_receivables, 0.0)                                                AS "amountOfReceivables",
        coalesce(pfm.amount_payable, 0.0)                                                       AS "amountPayable",
        coalesce(to_char(pfm.investment_program_approval_date, 'DD-MM-YYYY'), 'Не заполнено')   AS "investmentProgramApprovalDate",
        coalesce(pfm.financial_assets_total, 0.0)                                               AS "financialAssetsTotal",
        coalesce(pfm.financial_assets_depreciation_sum, 0.0)                                    AS "financialAssetsDepreciationSum",
        coalesce(pfm.financial_assets_capital_expenditure_sum, 0.0)                             AS "financialAssetsCapitalExpenditureSum",
        coalesce(pfm.financial_assets_budget, 0.0)                                              AS "financialAssetsBudget",
        coalesce(pfm.financial_assets_other_sources_sum, 0.0)                                   AS "financialAssetsOtherSourcesSum",
        coalesce(pfm.financial_assets_profit, 0.0)                                              AS "financialAssetsProfit",
        coalesce(pfm.financial_assets_lesion, 0.0)                                              AS "financialAssetsLesion",
        coalesce(pfm.financial_assets_depreciation_deductions_sum, 0.0)                         AS "financialAssetsDepreciationDeductionsSum"
    FROM volga_river_recovery.plumbing_facilities_mo pfm
        JOIN volga_river_recovery.plumbing_facilities pf ON pfm.plumbing_facilities_id = pf.id AND pf.deleted_at IS NULL
        JOIN volga_river_recovery.sewerage_object_state sos ON pf.sewerage_object_state_id = sos.id AND sos.reporting_period_id = (SELECT id FROM cte_reporting_period)
        JOIN public.company_requisites cr ON cr.id = pf.requisite_id
        JOIN refs legal_form ON legal_form.id = pf.legal_form_id 
        JOIN geo_tags gt on pfm.geo_tag_id = gt.id AND gt.id IN (SELECT geo_tag_id FROM cte_geo_tags)
    WHERE pfm.deleted_at IS NULL
)
SELECT * FROM cte_data

--2019
--21
--'83b6a0ad-9d9f-466b-a423-bb7379b97718'
--e3a88932-c76e-4158-842c-d710a9856df1
--50
--0