WITH requests AS (
    SELECT rf.id,
           fin_requests_fd.get_first_request_id(rf.id) as parent_id
    FROM fin_requests_fd.requests_fd rf
    WHERE  rf.geo_tag_id = :subjectId
      AND rf.deleted_at IS NULL
      AND rf.decision_date BETWEEN coalesce(:dateFrom, '01.01.2014'):: DATE AND coalesce(:dateTo::DATE, CURRENT_DATE)
      AND rf.decision_date IS NOT NULL
), unique_requests AS (
    SELECT DISTINCT ON (parent_id) * FROM requests ORDER BY parent_id, id DESC
),requests_with_parent AS (
    SELECT id, parent_id FROM unique_requests
    UNION
    SELECT parent_id AS id, null AS parent_id FROM unique_requests
    WHERE id <> parent_id
),requests_current AS (
    SELECT rf.limit_year_id AS limit_year_id,
           rf.id AS Id,
           rwp.parent_id AS parent_id,
           rf.geo_tag_id AS geo_tag_id,
           rf.registration_date AS registration_date,
           rf.decision_date AS decision_date,
           rf.decision_number AS decision_number,
           rf.mski_decision_of_fin_date AS mski_decision_of_fin_date,
           rf.mski_decision_of_fin_number AS mski_decision_of_fin_number,
           rf.sphere_of_mski_id AS sphere_of_mski_id,
           rf.type_of_mski_id AS type_of_mski_id,
           sum(coalesce(rfm.amount_of_funds,0)) AS amount_of_funds,
           sum(coalesce(rfm.amount_of_subject,0)) AS amount_of_subject,
           sum(coalesce(rfm.amount_of_mo,0)) AS amount_of_mo
    FROM fin_requests_fd.requests_fd rf
             JOIN  requests_with_parent rwp ON rwp.ID = rf.ID
             LEFT JOIN public.refs r_ly ON r_ly.ID = rf.limit_year_id
             LEFT JOIN fin_requests_fd.requests_fd_mo rfm ON rf.id = rfm.request_fd_id AND rfm.deleted_at IS NULL
    GROUP BY rf.id, rf.limit_year_id, rwp.parent_id, rf.geo_tag_id
),requests_mo_schedule AS (
    SELECT rwp.id AS id,
           coalesce(rfms.program_stage_id,0) AS program_stage_id,
           rfms.request_fd_mo_id AS request_fd_mo_id,
           sum(coalesce(rfms.amount_of_subject_year,0)) AS amount_of_subject_year,
           sum(coalesce(rfms.amount_of_mo_year,0)) AS amount_of_mo_year,
           sum(coalesce(rfms.borrowed_money_year,0)) AS borrowed_money_year,
           sum(coalesce(rfms.participant_money_year,0)) AS participant_money_year,
           sum(coalesce(rfms.other_money_year,0)) AS other_money_year,
           sum(coalesce(rfms.amount_of_funds_year,0)) AS amount_of_funds_year,
           sum(coalesce(rfms.borrowed_money_year,0) + coalesce(rfms.participant_money_year,0)
               + coalesce(rfms.other_money_year,0)) AS borrowed_participant_other,
           sum(coalesce(rfms.borrowed_money_year,0) + coalesce(rfms.amount_of_funds_year,0)
               + coalesce(rfms.amount_of_subject_year,0) + coalesce(rfms.amount_of_mo_year,0)
               + coalesce(rfms.other_money_year,0) +coalesce(rfms.participant_money_year,0)) AS expenditure_schedule
    FROM requests_with_parent rwp
             LEFT JOIN fin_requests_fd.requests_fd_mo rfm ON rfm.request_fd_id = rwp.id AND rfm.deleted_at IS NULL
             LEFT JOIN fin_requests_fd.requests_fd_mo_schedule rfms ON rfms.request_fd_mo_id = rfm.id
    GROUP BY rwp.id, rfms.program_stage_id,rfms.request_fd_mo_id
), transfer_decisions AS (
    SELECT td.request_fd_id AS id,
           sum(coalesce(td.sum_of_work,0)) + sum(coalesce(td.sum_on_percent,0)) FILTER ( WHERE r_ps.code = :typeTransfer) AS amount_transfer,
           sum(coalesce(td.sum_of_work,0)) + sum(coalesce(td.sum_on_percent,0)) FILTER ( WHERE td.payment_type_id = :typeRefund) AS amount_return
    FROM requests_with_parent rwp
             LEFT JOIN fin_requests_fd.transfer_decisions td ON rwp.id = td.request_fd_id AND td.deleted_at IS NULL
             LEFT JOIN refs r_ps ON r_ps.ID  = td.payment_type_id
    GROUP BY td.request_fd_id
),requests_sum_limit AS (
    SELECT rwp.id AS  Id, rwp.parent_id AS parent_id,
           rc.limit_year_id AS limit_year_id,
           sum(rcn.amount_of_funds) Column13,
           sum(rms.amount_of_subject_year) AS Column14,
           sum(rms.amount_of_mo_year) AS Column15,
           sum(rms.borrowed_participant_other) AS Column16,
           sum(rms.borrowed_money_year) AS Column17,
           sum(rms.participant_money_year) AS Column18,
           sum(rms.other_money_year) AS Column19,
           sum(rms.expenditure_schedule) FILTER ( WHERE r_ps.code = 'stage2013') AS Column20,
           sum(rms.expenditure_schedule) FILTER ( WHERE r_ps.code = 'stage2014') AS Column21,
           sum(rms.expenditure_schedule) FILTER ( WHERE r_ps.code = 'stage2015') AS Column22,
           sum(rms.expenditure_schedule) FILTER ( WHERE r_ps.code = 'stage2016') AS Column23,
           sum(rms.expenditure_schedule) FILTER ( WHERE r_ps.code = 'stage2017') AS Column24,
           sum(tr.amount_transfer) AS Column25,
           sum(tr.amount_return) AS Column26,
           sum(rcn.amount_of_funds) + sum(tr.amount_transfer) + sum(tr.amount_return) AS Column27,
           sum(coalesce(rof.sum,0)) AS Column28,
           sum(rc.amount_of_funds) - sum(rcn.amount_of_funds) AS Column29,
           sum(rc.amount_of_subject) - sum(rcn.amount_of_subject) AS Column30,
           sum(rc.amount_of_mo) - sum(rcn.amount_of_mo) AS Column31,
           rms.request_fd_mo_id AS Column32
    FROM requests_with_parent rwp
             LEFT JOIN requests_with_parent rwpn ON rwpn.id =  rwp.parent_id and rwpn.parent_id IS NULL
             LEFT JOIN requests_current rc ON rc.ID = rwp.id
             LEFT JOIN requests_current rcn ON rcn.ID = rwpn.id
             LEFT JOIN requests_mo_schedule rms ON rms.id = rwp.Id
             LEFT JOIN transfer_decisions tr ON tr.id = rwp.id
             LEFT JOIN fin_requests_fd.reduction_of_financing rof ON rof.request_fd_id = rwp.id AND rof.deleted_at IS NULL
             LEFT JOIN refs r_ps ON r_ps.ID = rms.program_stage_id
    WHERE rwp.parent_id IS NOT NULL
    GROUP BY rwp.id, rwp.parent_id, rms.request_fd_mo_id, rc.limit_year_id
)
SELECT
    1 AS total_group_field,
    case when lead(rc.geo_tag_id) over (partition by rc.geo_tag_id order by rc.geo_tag_id, rsl.Id) IS NULL  then TRUE else FALSE end FlagLimit,
    r_ly.name AS Column1,
    fa.regioncode AS Column2,
    rsl.id AS Column3,
    gt.name AS Column4,
    r_ly.name AS Column5,
    rc.registration_date AS Column6,
    rc.decision_date AS Column7,
    rc.decision_number AS Column8,
    rc.mski_decision_of_fin_date AS Column9,
    rc.mski_decision_of_fin_number AS Column10,
    rc.sphere_of_mski_id AS Column11,
    rc.type_of_mski_id AS Column12,
    rsl.Column13 AS Column13,
    rsl.Column14 AS Column14,
    rsl.Column15 AS Column15,
    rsl.Column16 AS Column16,
    rsl.Column13 AS Column17,
    rsl.Column14 AS Column18,
    rsl.Column19 AS Column19,
    rsl.Column20 AS Column20,
    rsl.Column21 AS Column21,
    rsl.Column22 AS Column22,
    rsl.Column23 AS Column23,
    rsl.Column24 AS Column24,
    rsl.Column25 AS Column25,
    rsl.Column26 AS Column26,
    rsl.Column27 AS Column27,
    rsl.Column28 AS Column28,
    rsl.Column29 AS Column29,
    rsl.Column30 AS Column30,
    rsl.Column31 AS Column31,
    r_rsl.name AS Column32
FROM requests_sum_limit rsl
         LEFT JOIN public.refs r_ly ON r_ly.ID = rsl.limit_year_id
         LEFT JOIN requests_current rc ON rc.Id = rsl.Id
         LEFT JOIN public.geo_tags gt ON gt.id = rc.geo_tag_id
         LEFT JOIN public.refs r_rsl ON r_rsl.ID = rsl.Column32
         LEFT JOIN public.fias_addrobj fa on fa.okato =  gt.okato and fa.livestatus = '1' and fa.actstatus = '1'
ORDER BY r_ly.name, fa.regioncode, rsl.id;