· 5 years ago · Nov 05, 2020, 03:36 PM
1-- Организации УО, которые учитываются на карте внедрения
2DROP TABLE IF EXISTS uo_orgs;
3CREATE TEMP TABLE uo_orgs AS
4SELECT DISTINCT
5 org.ppa_organization_guid
6FROM
7 corg.org_implementation_map_org org
8WHERE
9 nsi_20_code='1';
10
11-- Дома МКД с указанием УО и способа управления, которые учитываются на карте внедрения
12DROP TABLE IF EXISTS mp_orgs;
13CREATE TEMP TABLE mp_orgs AS
14SELECT
15 house_management_periods.ppa_organization_guid,
16 CASE
17 WHEN house_management_periods.nsi_25_code = '1' THEN 'OWNERS'
18 WHEN house_management_periods.nsi_25_code IN ('2', '3', '4', '7') THEN 'COMMUNION'
19 WHEN house_management_periods.nsi_25_code = '5' THEN 'MANAGEMENT'
20 WHEN house_management_periods.nsi_25_code = '6' THEN 'NOT_SELECT'
21 ELSE 'UNKNOWN'
22 END parameter_code,
23 house_management_periods.fias_house_code
24FROM
25 (
26 SELECT
27 ah.guid,
28 mp.ppa_organization_guid,
29 ah.fias_region_code,
30 ah.fias_house_code,
31 COALESCE(mp.nsi_25_code, ah.nsi_25_code, '0'::character varying) AS nsi_25_code,
32 row_number() OVER (PARTITION BY ah.guid ORDER BY mp.nsi_25_code DESC, mp.last_editing_date DESC, mp.guid) AS row_num
33 FROM shm.hm_houses_apartment ah
34 LEFT JOIN shm.hm_house_management_periods mp ON
35 (
36 mp.fias_house_code = ah.fias_house_code
37 AND mp.status IN ('INCLUDED', 'EXCLUDED')
38 AND (mp.management_period_from IS NULL OR mp.management_period_from <= now())
39 AND (mp.management_period_to IS NULL OR mp.management_period_to > now())
40 AND mp.entity_is_actual
41 AND mp.document_guid IS NOT NULL
42 )
43 WHERE
44 ah.entity_is_actual
45 ) house_management_periods
46WHERE
47 house_management_periods.row_num = 1;
48
49-- Организаций у которых есть хотя бы один дом
50SELECT
51 COUNT(*)
52FROM
53 uo_orgs
54WHERE
55 EXISTS (SELECT * FROM mp_orgs WHERE
56 parameter_code = 'MANAGEMENT'
57 AND mp_orgs.ppa_organization_guid = uo_orgs.ppa_organization_guid
58 );