Guest User

Untitled

a guest
Dec 5th, 2019
123
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH cte_subject_geo_tag AS (
  2.     SELECT
  3.         gp.id
  4.     FROM new_reloc_contracts c
  5.         JOIN geo_tags gc ON c.mo_geo_tag_id = gc.id
  6.         JOIN geo_tags gp ON gc.lft BETWEEN gp.lft AND gp.rgt AND gp.level = 2
  7.     WHERE c.id = :contractId AND c.deleted_at IS NULL
  8. ),
  9. cte_emrg_housing_doc_new AS (
  10.     SELECT
  11.         DISTINCT ON (d.geo_tag_id)
  12.         d.id
  13.     FROM emrg_housing_docs_new d
  14.         JOIN statuses s ON d.status_id = s.id
  15.         JOIN cte_subject_geo_tag g ON g.id = d.geo_tag_id
  16.     WHERE
  17.         d.deleted_at IS NULL        
  18.         AND s.code = 'new_emrg_doc.verified_by_fund'
  19.     ORDER BY d.geo_tag_id, d.created_at DESC
  20. ),
  21. cte_requests_mo AS (
  22.     SELECT
  23.         DISTINCT (frfmo.geo_tag_id) AS id
  24.     FROM fin_requests_for_municipal_organizations frfmo
  25.         JOIN fin_requests fr on frfmo.financing_request_id = fr.id
  26.         JOIN statuses s ON s.id = fr.status_id
  27.     WHERE fr.deleted_at IS NULL
  28.         AND frfmo.deleted_at IS NULL
  29.         AND fr.version = 'v9'
  30.         AND fr.id = 0
  31.         AND ((
  32.             fin_request_is_change(fr.id) AND s.code IN (
  33.                 'approved_sf',
  34.                 'received_to_fund',
  35.                 'financing_request_verified_by_fund',
  36.                 'financing_request_fund_approved'
  37.             ))
  38.             OR s.code = 'financing_request_on_control'
  39.         )
  40. ),
  41. cte_houses AS (
  42.     SELECT
  43.         ehdnh.id,
  44.         bool_or(hr.resettlement_program_id IS NULL) OR bool_or(r.code = '81005') is_relocation_with_fund,
  45.         ehdnma.geo_tag_id,
  46.         revision.address_id
  47.     FROM cte_emrg_housing_doc_new d
  48.         JOIN emrg_housing_docs_new_mun_areas ehdnma ON d.id = ehdnma.document_id
  49.         JOIN geo_tags g ON ehdnma.geo_tag_id = g.id
  50.         JOIN emrg_housing_docs_new_houses ehdnh ON ehdnma.id = ehdnh.municipal_area_id
  51.         JOIN house_quarters_residents_snapshots hqrs ON ehdnh.snapshot_id = hqrs.id
  52.         JOIN house_revision revision ON hqrs.house_revision_id = revision.id
  53.         JOIN house_quarters hq ON hqrs.id = hq.snapshot_id
  54.         LEFT JOIN house_resettlements hr ON hq.id = hr.quarters_id AND hr.deleted_at IS NULL
  55.         LEFT JOIN reloc_programs_name_and_requisites rpnar ON hr.resettlement_program_id = rpnar.id
  56.         LEFT JOIN refs r ON rpnar.program_type_id = r.id
  57.     WHERE hq.deleted_at IS NULL AND (ehdnma.geo_tag_id IN (SELECT id FROM cte_requests_mo) OR :requestId = 0)
  58.     GROUP BY ehdnh.id, ehdnma.geo_tag_id, revision.address_id
  59. ),
  60. cte_data AS (
  61.     SELECT
  62.         cte_houses.id,
  63.         g.name                            AS municipality,
  64.         get_address_short(a.full_address) AS address
  65.     FROM cte_houses
  66.         JOIN geo_tags g ON g.id = cte_houses.geo_tag_id
  67.         JOIN addresses a ON cte_houses.address_id = a.id
  68.     WHERE CASE WHEN :isContractRelocationWithFund THEN cte_houses.is_relocation_with_fund ELSE TRUE END    
  69. )
  70. SELECT *
  71. FROM cte_data
RAW Paste Data