SHARE
TWEET

Untitled

a guest Oct 23rd, 2019 105 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH cte_under_construction_quarters_data AS (
  2.     SELECT
  3.         DISTINCT ON (hq_to.id)
  4.         nrcq.id                                 AS id,
  5.         hq_to.id                                AS under_construction_quarter_id,
  6.         hq_to.house_quarters_id                 AS emergency_quarter_id,
  7.         hq_to.name                              AS under_construction_quarter_number,
  8.         coalesce(
  9.             hq_to.square,
  10.             hrt_to.planned_providing_area_square
  11.         )                                       AS under_construction_quarter_square,                                
  12.         coalesce(
  13.             get_address_full(a.full_address),
  14.             ehp.building_address
  15.         )                                       AS under_construction_quarter_address,
  16.         hrt_to.amount_of_funds                  AS amount_of_funds,
  17.         hrt_to.amount_of_funds_subject          AS amount_of_funds_subject,
  18.         hrt_to.amount_of_funds_mo               AS amount_of_funds_mo,
  19.         hrt_to.amount_of_extrabudgetary_funds   AS amount_of_extrabudgetary_funds,
  20.         rgkh_summ_nullable(
  21.             +hrt_to.amount_of_funds,
  22.             +hrt_to.amount_of_funds_subject,
  23.             +hrt_to.amount_of_funds_mo,
  24.             +hrt_to.amount_of_extrabudgetary_funds
  25.         )                                       AS sum_of_funds,
  26.         gt.id                                   AS geo_tag_id,
  27.         gt.name                                 AS geo_tag_name
  28.     FROM new_reloc_contracts nrc
  29.         JOIN new_reloc_contracts_quarters nrcq ON nrcq.contract_id = nrc.id
  30.         JOIN house_quarters hq_to ON hq_to.id = nrcq.house_quarter_id
  31.         JOIN house_quarters_residents_snapshots hqrs ON hq_to.snapshot_id = hqrs.id
  32.         JOIN house_revision hr on hqrs.house_revision_id = hr.id
  33.         LEFT JOIN addresses a ON hr.address_id = a.id
  34.         LEFT JOIN epd_house_passports ehp ON hr.id = ehp.house_revision_id
  35.         LEFT JOIN house_resettlements hrt_to ON hq_to.id = hrt_to.quarters_id AND hrt_to.deleted_at IS NULL
  36.         LEFT JOIN geo_tags gt on hr.geo_tag_id = gt.id
  37.     WHERE nrc.id = :contractId
  38.         AND hq_to.deleted_at IS NULL
  39.     ORDER BY hq_to.id, hrt_to.id
  40. ), cte_emergency_quarters_data AS (
  41.     SELECT
  42.         DISTINCT ON (hq_from.id)
  43.         cucqd.under_construction_quarter_id     AS under_construction_quarter_id,
  44.         hq_from.id                              AS emergency_quarter_id,
  45.         hq_from.square                          AS emergency_quarter_square,
  46.         concat_ws(
  47.             ', ',
  48.             coalesce(
  49.                 get_address_full(a.full_address),
  50.                 ehp.building_address
  51.             ),
  52.             hq_from.name
  53.         )                                       AS emergency_quarter_address_with_number,
  54.         hrt_stage.id                            AS resettlement_stage_id,
  55.         hrt_stage.name                          AS resettlement_stage_name,
  56.         hrt_from.residents_count                AS resettlement_residents,
  57.         hrt_from.is_resident_consent            AS is_resettlement_resident_consent
  58.     FROM house_quarters hq_from
  59.         JOIN cte_under_construction_quarters_data cucqd ON cucqd.emergency_quarter_id = hq_from.id
  60.         JOIN house_quarters_residents_snapshots hqrs ON hq_from.snapshot_id = hqrs.id
  61.         JOIN house_revision hr on hqrs.house_revision_id = hr.id
  62.         LEFT JOIN addresses a ON hr.address_id = a.id
  63.         LEFT JOIN epd_house_passports ehp ON hr.id = ehp.house_revision_id
  64.         LEFT JOIN house_resettlements hrt_from ON hq_from.id = hrt_from.quarters_id AND hrt_from.deleted_at IS NULL
  65.         LEFT JOIN refs hrt_stage ON hrt_from.stage_id = hrt_stage.id
  66.     WHERE hq_from.deleted_at IS NULL
  67.     ORDER BY hq_from.id, hrt_from.id
  68. ), cte_data AS (
  69.     SELECT
  70.         qd_to.id,
  71.         qd_to.under_construction_quarter_id,
  72.         qd_from.emergency_quarter_id,
  73.         qd_to.under_construction_quarter_number,
  74.         qd_to.under_construction_quarter_address,
  75.         qd_from.emergency_quarter_address_with_number,
  76.         qd_from.resettlement_stage_id AS resettlement_stage,
  77.         qd_from.resettlement_stage_name,
  78.         qd_to.under_construction_quarter_square,
  79.         qd_from.emergency_quarter_square,
  80.         qd_from.resettlement_residents,
  81.         qd_from.is_resettlement_resident_consent,
  82.         qd_to.amount_of_funds,
  83.         qd_to.amount_of_funds_subject,
  84.         qd_to.amount_of_funds_mo,
  85.         qd_to.amount_of_extrabudgetary_funds,
  86.         qd_to.sum_of_funds,
  87.         qd_to.geo_tag_id,
  88.         qd_to.geo_tag_name
  89.     FROM cte_under_construction_quarters_data qd_to
  90.         LEFT JOIN cte_emergency_quarters_data qd_from USING(under_construction_quarter_id)
  91. )
  92. SELECT *
  93. FROM cte_data
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top