Guest User

Untitled

a guest
Oct 23rd, 2019
136
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