· 6 years ago · Jun 20, 2019, 08:00 AM
1DROP TABLE IF EXISTS tmp_conflict_data;
2CREATE TEMPORARY TABLE tmp_conflict_data AS
3SELECT
4 ARRAY_AGG(DISTINCT org_house_agr.or_organization_root_guid)::uuid[] AS or_organization_root_guid_arr,
5 ARRAY_AGG(COALESCE(ppa.entity_name, ppa.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa.ogrn,'-') || ', КПП: ' || COALESCE(ppa.kpp,'-'))::varchar[] AS organization_name_arr,
6 org_house_agr.fias_house_code::uuid,
7 1::integer AS confl_type,
8 NULL::varchar(50) AS information_type_code
9FROM
10 corg.org_pf_336_offence_pd_tech_org_house_agr org_house_agr
11 JOIN sppa.ppa_organizations ppa ON ppa.organization_root_guid = org_house_agr.or_organization_root_guid::varchar AND ppa.entity_is_actual
12WHERE
13 org_house_agr.with_agreement = FALSE
14 AND ARRAY['8'::varchar] && org_house_agr.org_role_code_arr
15 AND org_house_agr.report_month = '2019-05-01'::date
16 AND COALESCE(org_house_agr.percent_fill,100) < 100 -- Фиксируем конфликт только для домов у которых есть нарушение по ТХ (временно, до уточнения в Ланите)
17GROUP BY
18 org_house_agr.fias_house_code
19HAVING
20 COUNT(DISTINCT org_house_agr.or_organization_root_guid) > 1;
21
22INSERT INTO tmp_conflict_data
23SELECT
24 null::uuid[] AS or_organization_root_guid_arr,
25 null::varchar[] AS organization_name_arr,
26 h.fias_house_code::uuid,
27 2::integer AS confl_type,
28 NULL::varchar(50) AS information_type_code
29FROM
30 corg.org_pf_336_offence_pd_tech_house_adr h
31 LEFT JOIN shm.hm_house_management_periods mng_per ON mng_per.fias_house_code = h.fias_house_code::varchar
32 AND mng_per.status IN ('INCLUDED','EXCLUDED')
33 AND '2019-05-31'::date BETWEEN COALESCE(mng_per.management_period_from, '1900-01-01')::date AND COALESCE(mng_per.management_period_to, '2900-01-01')::date
34 AND mng_per.is_management_agreement_exists = FALSE -- -- Признак наличия ДУ (для ТСЖ) (для ТСЖ/ЖСК/ЖК/Иных кооперативов: должна отсутствовать информация по дому, что «Управление многоквартирным домом осуществляется управляющей организацией по договору управления»
35WHERE
36 h.report_month = '2019-05-01'::date
37 AND h.hm_house_guid IS NOT NULL
38 AND h.house_type = '1'
39 AND h.house_management_type_code IS NULL -- в РАО отсутствует в доме значение поля «Способ управления»
40 AND mng_per.guid IS NULL;
41
42DROP TABLE IF EXISTS tmp_ppa_deleg_acc_org;
43CREATE TEMPORARY TABLE tmp_ppa_deleg_acc_org AS
44SELECT
45 acc.or_organization_root_guid_base,
46 adr.fias_house_code
47FROM
48 corg.org_pf_336_offence_pd_tech_acc acc
49 JOIN corg.org_pf_336_offence_pd_tech_house_adr adr ON adr.hm_house_guid = acc.house_guid
50WHERE
51 acc.report_month = '2019-05-01'::date
52 AND NOT ARRAY['8'::varchar] && acc.org_role_code_arr
53GROUP BY
54 acc.or_organization_root_guid_base,
55 adr.fias_house_code;
56ANALYZE tmp_ppa_deleg_acc_org;
57
58DROP TABLE IF EXISTS tmp_ppa_deleg;
59CREATE TEMPORARY TABLE tmp_ppa_deleg AS
60SELECT
61 ppa_from.organization_root_guid::uuid AS or_organization_root_guid_from,
62 ppa_to.organization_root_guid::uuid AS or_organization_root_guid_to,
63 it.information_type_code AS information_type_code,
64 MAX(COALESCE(ppa_from.entity_name, ppa_from.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa_from.ogrn,'-') || ', КПП: ' || COALESCE(ppa_from.kpp,'-')) AS ppa_from_info,
65 MAX(COALESCE(ppa_to.entity_name, ppa_to.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa_to.ogrn,'-') || ', КПП: ' || COALESCE(ppa_to.kpp,'-')) AS ppa_to_info,
66
67 BOOL_OR(ppa_to.entity_is_actual) AS org_to_actual
68FROM
69 sppa.ppa_access_right_requests req
70 JOIN sppa.ppa_delegated_access_rights r ON r.request_guid = req.guid AND r.entity_is_actual AND req.status = 'ACCEPTED' AND req.revoke_reason IS NULL
71 JOIN sppa.ppa_delegated_information_types it on it.access_right_guid = r.guid AND it.information_type_code IN ('35','3') -- Информация о состоянии расчетов потребителей за жилое помещение и коммунальные услуги nsism.nsi_information_types
72 JOIN sppa.ppa_organizations ppa_from ON ppa_from.guid = req.organization_from AND ppa_from.entity_is_actual
73 LEFT JOIN sppa.ppa_information_systems ppa_is ON ppa_is.guid = req.information_system_guid
74 JOIN sppa.ppa_organizations ppa_to ON ppa_to.guid = COALESCE(ppa_is.organization_guid, req.organization_to)
75WHERE
76 '2019-05-31'::date BETWEEN COALESCE(req.start, '1900-01-01')::date AND COALESCE(req.finish, '5000-01-01')::date -- Дата начала / окончания делегирования
77GROUP BY
78 ppa_from.organization_root_guid,
79 ppa_to.organization_root_guid,
80 it.information_type_code;
81
82DROP TABLE IF EXISTS tmp_ppa_deleg_conflict;
83CREATE TEMPORARY TABLE tmp_ppa_deleg_conflict AS
84SELECT
85 d.or_organization_root_guid_from AS or_organization_root_guid_from,
86 d.information_type_code AS information_type_code
87FROM tmp_ppa_deleg d
88GROUP BY d.or_organization_root_guid_from, d.information_type_code
89HAVING COUNT(DISTINCT d.or_organization_root_guid_to) > 1;
90
91INSERT INTO tmp_conflict_data
92SELECT
93 ARRAY_AGG(DISTINCT d.or_organization_root_guid_to) AS or_organization_root_guid_arr,
94 ARRAY_AGG(DISTINCT d.ppa_to_info) AS organization_name_arr,
95 acc.fias_house_code AS fias_house_code,
96 3 AS confl_type,
97 d.information_type_code AS information_type_code
98FROM
99 tmp_ppa_deleg d
100 JOIN tmp_ppa_deleg_conflict conf ON conf.or_organization_root_guid_from = d.or_organization_root_guid_from AND d.information_type_code = conf.information_type_code
101 JOIN tmp_ppa_deleg_acc_org acc ON d.or_organization_root_guid_from = acc.or_organization_root_guid_base
102GROUP BY d.or_organization_root_guid_from, d.information_type_code, acc.fias_house_code
103HAVING COUNT(DISTINCT d.or_organization_root_guid_to) > 1;
104
105INSERT INTO tmp_conflict_data
106SELECT DISTINCT
107 d.or_organization_root_guid_from || ARRAY_AGG(DISTINCT d.or_organization_root_guid_to) AS or_organization_root_guid_arr,
108 ARRAY_AGG(DISTINCT d.ppa_to_info) AS organization_name_arr,
109 acc.fias_house_code AS fias_house_code,
110 4 AS confl_type,
111 d.information_type_code AS information_type_code
112FROM
113 tmp_ppa_deleg d
114 JOIN tmp_ppa_deleg_acc_org acc ON d.or_organization_root_guid_from = acc.or_organization_root_guid_base
115WHERE d.org_to_actual = FALSE
116GROUP BY d.or_organization_root_guid_from, d.information_type_code, d.or_organization_root_guid_to, acc.fias_house_code;
117
118INSERT INTO tmp_conflict_data
119SELECT
120 ARRAY_AGG(DISTINCT d.or_organization_root_guid_to) AS or_organization_root_guid_arr,
121 ARRAY_AGG(DISTINCT d.ppa_to_info) AS organization_name_arr,
122 acc.fias_house_code AS fias_house_code,
123 5 AS confl_type,
124 d.information_type_code AS information_type_code
125FROM
126 tmp_ppa_deleg d
127 JOIN tmp_ppa_deleg_acc_org acc ON d.or_organization_root_guid_from = acc.or_organization_root_guid_base
128WHERE EXISTS (SELECT 1
129 FROM tmp_ppa_deleg d1
130 WHERE d1.or_organization_root_guid_from = d.or_organization_root_guid_to
131 AND d1.information_type_code = d.information_type_code
132 AND d1.or_organization_root_guid_from <> d1.or_organization_root_guid_to -- Не учитываем делегирование самому себе
133 LIMIT 1)
134GROUP BY d.information_type_code, d.or_organization_root_guid_to, acc.fias_house_code;
135
136INSERT INTO tmp_conflict_data
137SELECT
138ARRAY_AGG(organization_root_guid)::uuid[] AS or_organization_root_guid_arr,
139ARRAY_AGG(COALESCE(ppa.entity_name, ppa.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa.ogrn,'-') || ', КПП: ' || COALESCE(ppa.kpp,'-'))::varchar[] AS organization_name_arr,
140tmp_risu.fias_house_code::uuid,
1416::integer AS confl_type,
142 NULL::varchar(50) AS information_type_code
143FROM sppa.ppa_organizations ppa
144JOIN
145 (
146 SELECT
147 fias_house_code::uuid,
148 management_period_from,
149 management_period_to,
150 ppa_organization_guid AS ppa_organization_guid -- Если по дому и договору в РИСУ есть 2 организации - возьмем сперва ту, которая имеет призанк обособленного подразделения
151 FROM
152 shm.hm_house_management_periods
153 WHERE
154 entity_is_actual = TRUE
155 AND status in ('INCLUDED','EXCLUDED')
156 AND '2019-05-01'::date BETWEEN COALESCE(date_trunc('month', management_period_from), '1900-01-01')::date AND COALESCE(management_period_to, '2900-01-01')::date
157 AND is_management_agreement_exists = FALSE -- -- Признак наличия ДУ (для ТСЖ) (для ТСЖ/ЖСК/ЖК/Иных кооперативов: должна отсутствовать информация по дому, что «Управление многоквартирным домом осуществляется управляющей организацией по договору управления»
158 ) tmp_risu ON tmp_risu.ppa_organization_guid = ppa.guid AND ppa.entity_is_actual
159GROUP BY tmp_risu.fias_house_code
160HAVING
161 COUNT(DISTINCT ppa.organization_root_guid) > 1 -- Несколько РАЗНЫХ организаций управляли домом в одном периоде
162 AND MAX(tmp_risu.management_period_from) <= MIN(tmp_risu.management_period_to); --периоды пересекаются
163
164
165SELECT
166 h.region_code,
167 confl.or_organization_root_guid_arr,
168 confl.fias_house_code,
169 confl.confl_type,
170 confl.information_type_code
171FROM
172 tmp_conflict_data confl
173 JOIN corg.org_pf_336_offence_pd_tech_house_adr h ON h.fias_house_code = confl.fias_house_code
174 JOIN cdim.dim_admterritory adm_r ON adm_r.root_guid = h.dim_admterritory_root_guid::character varying
175WHERE
176 h.hm_house_guid IS NOT NULL
177 AND h.report_month = '2019-05-01'::date
178 AND h.region_code IS NOT NULL
179 AND CASE WHEN (h.region_code IN ('77','78','92') AND '2019-05-01'::date >= '2019-07-01'::date) OR (h.region_code NOT IN ('77','78','92')) THEN TRUE ELSE FALSE END
180GROUP BY
181 h.region_code,
182 confl.or_organization_root_guid_arr,
183 confl.fias_house_code,
184 confl.confl_type,
185 confl.information_type_code
186HAVING COUNT(*) > 1