· 6 years ago · Aug 14, 2019, 08:28 AM
1DROP TABLE IF EXISTS tmp_gkn_apartments;
2CREATE TEMPORARY TABLE tmp_gkn_apartments AS
3 SELECT DISTINCT
4 hp.guid AS apartment_guid,
5 hp.guid,
6 hp.flat_number,
7 hp.nsi_11_code,
8 NULL::VARCHAR(255) AS room_number,
9 hp.entity_is_actual,
10 hp.last_editing_date,
11 hp.apartment_uid,
12 hp.premise_purpose_code,
13 hp.rosreestr_root_guid AS rosreestr_root_guid,
14 NULL::VARCHAR(36) AS room_guid,
15 (
16 SELECT
17 es.egrp_root_guid
18 FROM
19 shm.hm_egrp_shares es
20 WHERE
21 (es.apartment_guid = hp.guid OR es.apartment_guid IS NULL)
22 AND es.house_guid = '39192e90-82f6-45eb-9930-0e5877b70419'
23 AND hp.rosreestr_root_guid IS NULL
24 LIMIT 1
25 ) AS egrp_root_guid,
26 (
27 SELECT
28 COUNT(DISTINCT org_person_guid)
29 FROM
30 shm.hm_account_org_person aop
31 INNER JOIN shm.hm_accounts ha ON aop.account_guid = ha.guid
32 INNER JOIN shm.hm_calc_shares hs ON hs.account_guid = ha.guid
33 WHERE
34 ha.account_status <> 'CLOSED'
35 AND hs.apartments_guid = hp.guid
36 AND aop.link_as_owner
37 AND aop.added_manually IS NOT TRUE
38 AND ha.house_guid = '39192e90-82f6-45eb-9930-0e5877b70419'
39 ) AS egrp_link,
40 (
41 SELECT
42 creator_org_guid
43 FROM
44 shm.hm_entity_editors ee
45 WHERE
46 ee.entity_guid = hp.guid
47 LIMIT 1
48 ) AS creator_org_guid
49 FROM
50 shm.hm_apartments hp
51 INNER JOIN shm.hm_houses hm ON hp.hm_house_guid = hm.guid
52 WHERE
53 hm.guid = '39192e90-82f6-45eb-9930-0e5877b70419'
54 AND hp.hm_house_guid = '39192e90-82f6-45eb-9930-0e5877b70419'
55 AND hp.flat_number = '124'
56 UNION ALL
57 SELECT DISTINCT
58 hp.guid AS apartment_guid,
59 hr.guid,
60 hp.flat_number,
61 hp.nsi_11_code,
62 hr.room_number,
63 hr.entity_is_actual,
64 hp.last_editing_date,
65 hp.apartment_uid,
66 hp.premise_purpose_code,
67 COALESCE(hr.rosreestr_root_guid, hp.rosreestr_root_guid) AS rosreestr_root_guid,
68 hr.guid AS room_guid,
69 (
70 SELECT
71 es.egrp_root_guid
72 FROM
73 shm.hm_egrp_shares es
74 WHERE
75 (es.apartment_guid = hp.guid OR es.apartment_guid IS NULL)
76 AND(es.room_guid = hr.guid OR es.room_guid IS NULL)
77 AND es.house_guid = '39192e90-82f6-45eb-9930-0e5877b70419'
78 AND hp.rosreestr_root_guid IS NULL
79 LIMIT 1
80 ) AS egrp_root_guid,
81 (
82 SELECT
83 COUNT(DISTINCT org_person_guid)
84 FROM
85 shm.hm_account_org_person aop
86 INNER JOIN shm.hm_accounts ha ON aop.account_guid = ha.guid
87 INNER JOIN shm.hm_calc_shares hs ON hs.account_guid = ha.guid
88 WHERE
89 ha.account_status <> 'CLOSED'
90 AND (hr.guid = hs.room_guid OR hs.apartments_guid = hp.guid)
91 AND aop.link_as_owner
92 AND aop.added_manually IS NOT TRUE
93 ) AS egrp_link,
94 (
95 SELECT
96 creator_org_guid
97 FROM
98 shm.hm_entity_editors ee
99 WHERE
100 ee.entity_guid = hp.guid
101 LIMIT 1
102 ) AS creator_org_guid
103 FROM
104 shm.hm_apartments hp
105 INNER JOIN shm.hm_houses hm ON hp.hm_house_guid = hm.guid
106 INNER JOIN shm.hm_rooms hr ON hr.apartment_guid = hp.guid
107 WHERE
108 hm.guid = '39192e90-82f6-45eb-9930-0e5877b70419'
109 AND hp.hm_house_guid = '39192e90-82f6-45eb-9930-0e5877b70419'
110 AND hp.flat_number = '124';
111
112 ANALYZE tmp_gkn_apartments;
113
114DROP TABLE IF EXISTS tmp_gkn_org_p;
115CREATE TEMPORARY TABLE tmp_gkn_org_p ON COMMIT DROP AS
116 SELECT
117 org.guid AS org_guid,
118 COALESCE(CASE WHEN BTRIM(org.organization_short_name) = '' THEN NULL ELSE org.organization_short_name END, org.entity_name) AS organization_name,
119 org.ogrn,
120 org.inn,
121 org.kpp,
122 ha.apartments_guid,
123 ha.room_guid,
124 ROUND(ha.square_ap, 2) AS square_ap,
125 ha.cnt_ac,
126 ha.account_numbers
127 FROM
128 (
129 SELECT
130 ha.organization_guid,
131 COALESCE(hs.apartments_guid, hr.apartment_guid) AS apartments_guid,
132 hs.room_guid,
133 /*SUM((CASE
134 WHEN hs.room_guid IS NULL THEN hp.total_square
135 ELSE ha.total_square
136 END))*/
137 SUM(ha.total_square) AS square_ap,
138 COUNT(DISTINCT ha.guid) AS cnt_ac,
139 STRING_AGG(CONCAT_WS('/', ha.account_number, ha.account_number_gis_hcs), ',') AS account_numbers
140 FROM
141 shm.hm_accounts ha
142 INNER JOIN shm.hm_houses hm ON ha.house_guid = hm.guid
143 INNER JOIN shm.hm_calc_shares hs ON hs.account_guid = ha.guid
144 LEFT JOIN shm.hm_rooms hr ON hr.guid = hs.room_guid
145 LEFT JOIN shm.hm_apartments hp ON
146 (hs.apartments_guid = hp.guid OR hr.apartment_guid = hp.guid)
147 AND hp.hm_house_guid = '39192e90-82f6-45eb-9930-0e5877b70419'
148 WHERE
149 /*hp.entity_is_actual
150 AND*/ ha.account_status <> 'CLOSED'
151 AND hm.guid = '39192e90-82f6-45eb-9930-0e5877b70419'
152
153 GROUP BY
154 ha.organization_guid,
155 COALESCE(hs.apartments_guid, hr.apartment_guid),
156 hs.room_guid
157 ) ha
158 INNER JOIN sppa.ppa_organizations org ON org.guid = ha.organization_guid
159 ORDER BY
160 organization_name;
161
162ANALYZE tmp_gkn_org_p;
163
164
165DROP TABLE IF EXISTS tmp_gkn_hp;
166CREATE TEMPORARY TABLE tmp_gkn_hp AS
167 SELECT
168 ap.apartment_guid,
169 ap.guid,
170 ap.flat_number,
171 (CASE WHEN ap.nsi_11_code = '1' THEN 'жилое' ELSE 'нежилое' END)::VARCHAR(7) AS apartments_type, --character varying(7)
172 ap.room_number,
173 (CASE WHEN ap.entity_is_actual THEN 'актуальное' ELSE 'неактуальное' END)::VARCHAR(12) AS apartments_status, --character varying(12)
174 ap.last_editing_date,
175 ap.apartment_uid,
176 (CASE WHEN ap.rosreestr_root_guid IS NOT NULL THEN 'Да' ELSE 'Нет' END)::VARCHAR(3) AS link_to_gkn,
177 ap.egrp_link,
178 COALESCE(
179 (
180 SELECT
181 json_agg(
182 JSON_BUILD_OBJECT(
183 'org_guid', COALESCE(org_p1.org_guid, ''),
184 'organization_name', COALESCE(org_p1.organization_name, ''),
185 'ogrn', COALESCE(org_p1.ogrn, ''),
186 'inn', COALESCE(org_p1.inn, ''),
187 'kpp', COALESCE(org_p1.kpp, ''),
188 'square_ap', COALESCE(tmp_gkn_org_p.square_ap::TEXT, ''),
189 'cnt_ac', COALESCE(tmp_gkn_org_p.cnt_ac::TEXT, ''),
190 'account_numbers', COALESCE(tmp_gkn_org_p.account_numbers::TEXT, '')
191 )
192 )
193 FROM
194 (
195 SELECT DISTINCT
196 tmp_gkn_org_p.org_guid,
197 tmp_gkn_org_p.organization_name,
198 tmp_gkn_org_p.ogrn,
199 tmp_gkn_org_p.inn,
200 tmp_gkn_org_p.kpp
201 FROM
202 tmp_gkn_org_p
203 UNION ALL
204 SELECT
205 org.guid AS org_guid,
206 COALESCE(CASE WHEN BTRIM(org.organization_short_name) = '' THEN NULL ELSE org.organization_short_name END, org.entity_name) AS organization_name,
207 org.ogrn,
208 org.inn,
209 org.kpp
210 FROM sppa.ppa_organizations org
211 WHERE null = org.guid
212 AND (SELECT COUNT(1) FROM tmp_gkn_org_p) = 0
213 ORDER BY organization_name
214 ) org_p1
215 LEFT JOIN tmp_gkn_org_p ON
216 org_p1.org_guid = tmp_gkn_org_p.org_guid
217 AND tmp_gkn_org_p.apartments_guid = ap.apartment_guid
218 AND COALESCE(tmp_gkn_org_p.room_guid, '-1') = COALESCE(ap.room_guid, '-1')
219 ),
220 '[{"org_guid" : "", "organization_name" : "", "ogrn" : "", "inn" : "", "kpp" : "", "square_ap" : "", "cnt_ac" : "", "account_numbers" : ""}]'::JSON
221 ) AS org_details,
222 ap.creator_org_guid,
223 ap.premise_purpose_code,
224 ap.rosreestr_root_guid,
225 ap.egrp_root_guid
226 FROM
227 tmp_gkn_apartments ap;
228ANALYZE tmp_gkn_hp;
229
230select * from tmp_gkn_hp