SHARE
TWEET

Untitled

a guest Dec 5th, 2019 108 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
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
 
Top