ceVuSsGz

1 /*
2select f.code, f.name, ARRAY_AGG(i.code||'-'||i.name)
3from nsism.nsi_housing_fund_attr_descriptions d
4join nsism.nsi_housing_fund_attr_description_forms f ON f.housing_fund_attr_description_guid = d.guid
5join nsism.nsi_housing_fund_attr_description_items i ON i.housing_fund_attr_description_guid = d.guid
6where f.code IN ('20140', '17023', '20147', '13207', '13301', '13267', '13289', '14745')
7group by f.code, f.name  
8
9'13207';'Тип внутридомовой системы отопления';'{5511-Нет,"3719-Квартирное отопление (котел)","1047-Домовая котельная",1046-Электрическая,1045-Печная,1044-Центральная}'
10'13267';'Тип внутридомовой инженерной системы холодного водоснабжения';'{5517-Нет,"2217-Кольцевая или с закольцованными вводами",2216-Тупиковая}'
11'13289';'Тип внутридомовой инженерной системы водоотведения';'{5522-Нет,"3343-Локальная канализация (септик)","3342-Выгребная яма","3341-Централизованная канализация"}'
12'13301';'Тип внутридомовой инженерной системы газоснабжения';'{"3722-от накопителей (дворовые)",1088-нет,"1087-баллонный газ",1086-центральное}'
13'20147';'Тип общежития';'{"4301-Общежитие секционного типа","4300-Общежитие гостиничного типа","4299-Общежитие коридорного типа","4298-Общежитие квартирного типа"}'
14'14745';'Тип внутридомовой инженерной системы горячего водоснабжения ';'{5518-Нет,"3721-Индивидуальный котел","3720-Дровяная колонка",2243-Электроводонагреватели,"2242-Газовые колонки (ВДГО)","2241-Кольцевая или с закольцованными вводами",2240-Тупиковая}'
15'11745' 'Наличие внутридомовой системы горячего водоснабжения'
16
17*/
18
19
20--USER hcs_analytic_rw
21
22
23-- доп параметры дома  (в самом доме колонки c колвом проживающих нет)
24DROP TABLE IF EXISTS tmp_house_params;
25CREATE TEMPORARY TABLE tmp_house_params AS
26SELECT 
27	gin.house_guid, 
28	MAX(CASE WHEN obj.param_code = '20140' THEN val.value_integer END) AS liv_numbr, --20140-Количество проживающих
29	BOOL_OR(CASE WHEN obj.param_code = '17023' THEN val.value_boolean END) AS is_common_house, --17023-Общежитие
30	MAX(CASE WHEN obj.param_code = '20147' THEN nsi.code_name END) AS type_common_house, --20147-Тип общежития (Общежитие гостиничного типа Общежитие квартирного типа Общежитие коридорного типа Общежитие секционного типа)
31
32	MAX(CASE WHEN obj.param_code = '13207' THEN val.value_nsiref_code END) AS type_otopl, --13267-Тип внутридомовой инженерной системы отопления (5511-Нет,"3719-Квартирное отопление (котел)","1047-Домовая котельная",1046-Электрическая,1045-Печная,1044-Центральная)
33	MAX(CASE WHEN obj.param_code = '13301' THEN val.value_nsiref_code END) AS type_gas, --13267-Тип внутридомовой инженерной системы газоснабжения (3722-от накопителей (дворовые)",1088-нет,"1087-баллонный газ",1086-центральное)
34	MAX(CASE WHEN obj.param_code = '13267' THEN val.value_nsiref_code END) AS type_hvs, --13267-Тип внутридомовой инженерной системы холодного водоснабжения (5517-Нет,"2217-Кольцевая или с закольцованными вводами",2216-Тупиковая)
35	MAX(CASE WHEN obj.param_code = '13267' THEN nsi.code_name END) AS type_hvs_name, --13267-Тип внутридомовой инженерной системы холодного водоснабжения (5517-Нет,"2217-Кольцевая или с закольцованными вводами",2216-Тупиковая)
36	MAX(CASE WHEN obj.param_code = '13289' THEN val.value_nsiref_code END) AS type_canal, --13267-Тип внутридомовой инженерной системы водоотведения (5522-Нет,"3343-Локальная канализация (септик)","3342-Выгребная яма","3341-Централизованная канализация)
37	BOOL_OR(CASE WHEN obj.param_code = '11789' THEN val.value_boolean END) AS is_type_canal, --'11789' 'Наличие системы водоотведения'
38	MAX(CASE WHEN obj.param_code = '14745' THEN val.value_nsiref_code END) AS type_gvs, --14745-Тип внутридомовой инженерной системы горячего водоснабжения (5518-Нет,"3721-Индивидуальный котел","3720-Дровяная колонка",2243-Электроводонагреватели,"2242-Газовые колонки (ВДГО)","2241-Кольцевая или с закольцованными вводами",2240-Тупиковая)
39	MAX(CASE WHEN obj.param_code = '14745' THEN nsi.code_name END) AS type_gvs_name,
40	BOOL_OR(CASE WHEN obj.param_code = '11745' THEN val.value_boolean END) AS is_exists_gvs --17023-Общежитие
41	--select * from nsism.nsi_housing_fund_attr_description_forms where name like '%прожив%'
42FROM hmsm.hm_house_general_infos gin
43JOIN hmsm.hm_house_general_info_params par ON par.house_general_info_guid = gin.guid AND par.actual AND last_version AND gin.status = 'APPROVED' AND gin.last_approved_version
44JOIN hmsm.hm_house_estate_objects obj ON obj.guid = par.house_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND obj.param_code IN ('20140', '17023', '20147', '13207', '13301', '13267', '13289', '14745', '11745', '11789')
45JOIN hmsm.hm_house_estate_object_values val ON val.estate_object_guid = obj.guid AND now() between val.system_date_from AND COALESCE(val.system_date_to, '2999-01-01')
46LEFT JOIN (SELECT code, code_name FROM DBLINK('hcsnsidb_d', 'SELECT code, name FROM nsism.nsi_housing_fund_attr_description_items') AS (code varchar(4), code_name varchar(2500))) nsi ON nsi.code = val.value_nsiref_code
47GROUP BY gin.house_guid;
48ANALYZE tmp_house_params;
49
50
51
52
53-- доп параметры квартиры (в самой квартире колонки нет)
54DROP TABLE IF EXISTS tmp_apartment_params ;
55CREATE TEMPORARY TABLE tmp_apartment_params  AS
56SELECT par.apartment_guid, MAX(val.value_integer) AS liv_numbr
57FROM hmsm.hm_apartment_params par
58JOIN hmsm.hm_apartment_estate_objects obj ON par.apartment_estate_object_guid = obj.guid AND obj.status IN ('CREATED', 'MODIFIED') AND obj.param_code IN ( '20130', '20125') AND par.actual 
59JOIN hmsm.hm_apartment_estate_object_values val ON val.estate_object_guid = obj.guid AND now() between val.system_date_from AND COALESCE(val.system_date_to, '2999-01-01')
60GROUP BY par.apartment_guid;
61CREATE INDEX idx_tmp_apartment_params ON tmp_apartment_params (apartment_guid);
62ANALYZE tmp_apartment_params;
63
64-- доп параметры комнаты (в самой комнате колонка то же есть), UI вроде берет из самой комнаты
65DROP TABLE IF EXISTS tmp_room_params;
66CREATE TEMPORARY TABLE tmp_room_params AS
67SELECT house_guid, apartment_guid, SUM(value_integer) AS liv_numbr
68FROM (
69	SELECT r.house_guid, r.guid, r.apartment_guid, MAX(val.value_integer) AS value_integer
70	FROM hmsm.hm_rooms r
71	JOIN hmsm.hm_room_params par ON par.room_guid = r.guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
72	JOIN hmsm.hm_room_estate_objects obj ON obj.guid = par.room_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND par.actual
73	JOIN hmsm.hm_room_estate_object_values val ON val.estate_object_guid = obj.guid AND now() between val.system_date_from AND COALESCE(val.system_date_to, '2999-01-01')
74	GROUP BY r.house_guid, r.guid, r.apartment_guid )t
75GROUP BY house_guid, apartment_guid;
76ANALYZE tmp_room_params;
77
78-- комнаты (в доп параметрах комнаты то же есть параметр), UI вроде берет из самой комнаты
79DROP TABLE IF EXISTS tmp_rooms;
80CREATE TEMPORARY TABLE tmp_rooms AS
81SELECT r.house_guid, r.apartment_guid, SUM(r.number_of_living::integer) AS liv_numbr, count(DISTINCT CASE WHEN ap.guid IS NOT NULL THEN r.guid END) rooms_count
82FROM hmsm.hm_rooms r
83LEFT JOIN hmsm.hm_apartments ap ON ap.guid = r.apartment_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier --комната может быть без квартиры, например сразу в жилом доме
84WHERE r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
85AND regexp_replace(r.number_of_living, '[^0-9]', '99999', 'g')::integer < 1000 --колво прож текстовое, поэтому фильтруем нечисловые значения заменяя их на 99999
86GROUP BY r.house_guid, r.apartment_guid;
87ANALYZE tmp_rooms;
88
89
90
91-- ЛС
92--колво проживающих из ЛС: по каждой комнате или квартире взят последний по дате изменения ЛС. Комнаты суммированы по квартире
93DROP TABLE IF EXISTS tmp_living_acc;
94CREATE TEMPORARY TABLE tmp_living_acc AS
95SELECT
96	house_guid,
97	apartment_guid,
98	MAX(living_cnt_hd_acc) AS living_cnt_hd_acc, --прож в ЖД
99	MAX(living_cnt_ap_acc) AS living_cnt_ap_acc, --прож в квартире
100	SUM(living_cnt_r_acc) AS living_cnt_r_acc --прож в комнате
101FROM
102	(
103	SELECT DISTINCT 
104		acc.house_guid, COALESCE(ap.guid, ap_r.guid) AS apartment_guid, r.guid,
105		-- ЛС ЖД
106		CASE WHEN ap.guid IS NULL AND r.guid IS NULL THEN FIRST_VALUE(acc.living_count) OVER(PARTITION BY ap_r.guid ORDER BY acc.last_editing_date DESC, acc.living_count DESC) ELSE 0 END AS living_cnt_hd_acc,
107		-- ЛС квартиры
108		CASE WHEN ap.guid IS NOT NULL THEN FIRST_VALUE(acc.living_count) OVER(PARTITION BY ap.guid ORDER BY acc.last_editing_date DESC, acc.living_count DESC) ELSE 0 END AS living_cnt_ap_acc,
109		--ЛС комнаты
110		CASE WHEN r.guid IS NOT NULL THEN FIRST_VALUE(acc.living_count) OVER(PARTITION BY r.guid ORDER BY acc.last_editing_date DESC, acc.living_count DESC) ELSE 0 END AS living_cnt_r_acc
111	FROM
112		hmsm.hm_calc_shares sh
113		JOIN hmsm.hm_accounts acc ON acc.guid = sh.account_guid AND acc.account_status = 'ACTUAL' AND acc.is_actual AND sh.annulled = false
114		-- квартиры без комнат
115		LEFT JOIN hmsm.hm_apartments ap ON ap.guid = sh.apartments_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier
116		-- комнаты (и квартиры - если есть)
117		LEFT JOIN hmsm.hm_rooms r ON r.guid = sh.room_guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
118		LEFT JOIN hmsm.hm_apartments ap_r ON r.apartment_guid = ap_r.guid AND ap_r.actual AND ap_r.status = 'APPROVED' AND ap_r.confirmed_by_supplier
119	) t
120GROUP BY house_guid, apartment_guid;
121CREATE INDEX idx_tmp_living_acc ON tmp_living_acc (house_guid) WHERE apartment_guid IS NULL;
122CREATE INDEX idx_tmp_living_acc_h_ap ON tmp_living_acc (house_guid, apartment_guid) WHERE apartment_guid IS NOT NULL;
123ANALYZE tmp_living_acc;
124
125--select * from tmp_living_acc where living_cnt_hd_acc > 0
126
127 
128--SELECT   * FROM  nsism.nsi_addrobj ad where ad.aolevel = 1  and ad.is_actual and offname like '%Камч%' --41
129--адрес дома	
130DROP TABLE IF EXISTS tmp_nsi_addr;
131CREATE TEMPORARY TABLE tmp_nsi_addr AS 
132SELECT house_guid, MAX(postalcode) postalcode, MAX(regioncode) regioncode, MAX(adr) adr
133FROM DBLINK('hcsnsidb_d', '
134	SELECT house_guid, a.postalcode, ad.regioncode, nsism.func_get_address_by_attr(house_abbr,house_fullname,null, 		
135	region_fullname,area_abbr,area_fullname,city_abbr,city_fullname,settlement_abbr,settlement_fullname,street_abbr,
136	street_fullname,additional_territory_abbr,additional_territory_fullname,
137	additional_ter_street_abbr,additional_ter_street_fullname,ctar_abbr,ctar_fullname,planstruct_abbr,planstruct_fullname) adr
138	FROM nsism.nsi_addresses a
139	JOIN nsism.nsi_addrobj ad ON ad.aouid = a.region_guid AND ad.aolevel = 1 AND a.is_actual AND ad.is_actual 
140  --WHERE ad.regioncode = ''41''
141	') AS (house_guid varchar(36), postalcode varchar(6), regioncode varchar(2), adr text)
142GROUP BY house_guid;  
143ANALYZE tmp_nsi_addr;
144
145
146--витрина по домам и квартирам (порядок полей как в шаблоне xls)
147DROP TABLE IF EXISTS tmp_house_apart_liv_numbr ;
148CREATE TEMPORARY TABLE tmp_house_apart_liv_numbr AS
149
150SELECT
151	reg.regioncode, --для разбивки нет в экселе
152	h.guid AS hm_guid, --№ п/п
153	h.house_guid,
154	h.house_code,
155	CASE WHEN h.house_type_code = '1' THEN 'МКД' WHEN h.house_type_code = '2' AND h.blocked_house = false THEN 'ЖД' WHEN h.house_type_code = '2' AND h.blocked_house THEN 'ЖД блок' END AS house_type_code,
156	h.blocked_house,--жд блок застройки	
157	adr.postalcode,-- Индекс дома
158	adr.adr,--Адрес дома
159	h.oktmo_code, --ОКТМО
160
161	--new
162	CASE 
163		WHEN hpar.is_common_house = true THEN 'ДА'
164		WHEN hpar.is_common_house = false THEN 'НЕТ'
165		WHEN hpar.is_common_house is null AND hpar.type_common_house IS NOT NULL THEN 'ДА'
166	END AS common_house, --Общежитие
167
168	CASE WHEN h.building_year::integer < 1957 THEN 'ДА' END AS building_year_1957,  --Год постройки ранее 1957
169	CASE WHEN h.building_year::integer between 1957 AND 1970 THEN 'ДА' END AS building_year_1957_1970,  --Год постройки 1957-1970
170	CASE WHEN h.building_year::integer between 1971 AND 1995 THEN 'ДА' END AS building_year_1971_1995,  --Год постройки 1971-1995
171	CASE WHEN h.building_year::integer between 1996 AND 2002 THEN 'ДА' END AS building_year_1996_2002,  --Год постройки 1996-2002
172	CASE WHEN h.building_year::integer between 2003 AND 2010 THEN 'ДА' END AS building_year_2003_2010,  --Год постройки 2003-2010
173	CASE WHEN h.building_year::integer > 2010 THEN 'ДА' END AS building_year_2010,  --Год постройки после 2010
174
175	CASE --13267-Тип внутридомовой инженерной системы газоснабжения (3722-от накопителей (дворовые)",1088-нет,"1087-баллонный газ",1086-центральное)
176		WHEN hpar.type_gas = '1087' THEN 'баллонный газ' --1087-баллонный газ
177		WHEN hpar.type_gas = '1086' THEN 'сетевой' --1086-центральное
178	END AS type_gas,  --Газ (сетевой/сжижженый(баллоны))
179
180	hpar.type_hvs_name, --Холодное водоснабжение (5517-Нет,"2217-Кольцевая или с закольцованными вводами",2216-Тупиковая)
181	
182	COALESCE(hpar.type_gvs_name, CASE WHEN is_exists_gvs = false THEN 'Нет' END) AS type_gvs_name, --14745-Тип внутридомовой инженерной системы горячего водоснабжения (5518-Нет,"3721-Индивидуальный котел","3720-Дровяная колонка",2243-Электроводонагреватели,"2242-Газовые колонки (ВДГО)","2241-Кольцевая или с закольцованными вводами",2240-Тупиковая)
183
184	CASE --13267-Тип внутридомовой инженерной системы отопления (5511-Нет,"3719-Квартирное отопление (котел)","1047-Домовая котельная",1046-Электрическая,1045-Печная,1044-Центральная)
185		WHEN hpar.type_otopl = '1044' THEN 'Центральная' --1044-Центральная
186		WHEN hpar.type_otopl IN ('3719', '1047') THEN 'от индивидуальных установок, котлов' 
187		WHEN hpar.type_otopl = '1045' THEN 'печное' 
188	END AS type_otopl,  --Газ (сетевой/сжижженый(баллоны))
189
190
191	CASE --13267-Тип внутридомовой инженерной системы водоотведения (5522-Нет,"3343-Локальная канализация (септик)","3342-Выгребная яма","3341-Централизованная канализация)
192		WHEN hpar.type_canal = '3341' THEN 'через коммунальную канализационную систему' --3341-Централизованная канализация
193		WHEN hpar.type_canal = '3343' THEN 'через индивидуальную канализационну систему(включая септик)' --3343-Локальная канализация (септик)
194		WHEN hpar.type_canal = '3342' THEN 'через систему труб в выгребные ямы' --3342-Выгребная яма
195		WHEN hpar.type_canal = '5522' THEN 'система канализации отсутствует' --3342-Выгребная яма
196		WHEN hpar.is_type_canal = false THEN 'система канализации отсутствует' 
197	END AS type_canal,  --Газ (сетевой/сжижженый(баллоны))
198
199	h.total_square AS house_total_square, --Общая площадь здания
200	h.wall_materials, -- Тип внутренних стен   wall_materials Материалы стен - список строкой 	
201	h.building_year, --Год постройки
202	h.operation_year, --Год ввода в эксплуатацию
203	h.max_floor_count, --Кол-во этажей
204	h.underground_floor_count, -- Количество подземных этажей
205	COALESCE(h.plan_series,'') ||'-'|| COALESCE(h.plan_type_code,'') AS house_plan_ser_type, --Серия, тип проекта здания
206
207	hpar.liv_numbr AS hm_houses_liv_numbr, -- Кол-во проживающих в доме из доп.параметров дома
208	rh.liv_numbr AS room_h_liv_numbr, --Кол-во проживающих из комнат (в доме без квартир)
209	rhpar.liv_numbr AS room_h_par_liv_numbr, --Кол-во проживающих из доп парам комнат (в доме без квартир)
210	acc_r.living_cnt_hd_acc AS acc_h_rooms_liv_numbr, -- Кол-во проживающих из ЛС комнат  (в доме без квартир)
211	
212	ap.guid AS apartment_guid, --квартиры (блоки в ЖД лежат в квартирах, в блоке могут быть комнаты)
213	ap.flat_number, --номер квартиры
214	ap.total_square AS apart_total_square, --Общая площадь жилого помещения/блока
215	ap.residential_square, -- Жилая площадь
216	r.rooms_count, --Число жилых комнат квартиры коммунального заселения
217	ap.residential_premise_type_code,--"Характеристика помещения" = квартира коммунальго заселения 1Отдельная квартира 2Квартира коммунального заселения 3Общежитие nsism.nsi_resident_premise_types 
218	
219	appar.liv_numbr AS apart_liv_numbr, --прож  в квартире (взято из доп парам квартиры)
220	rpar.liv_numbr AS room_par_liv_numbr, --прож  в квартире (взято  из доп парам комнаты)
221	r.liv_numbr AS room_liv_numbr, --прож в квартире (взято из комнат)
222	acc_app.living_cnt_ap_acc AS acc_ap_liv_numbr, --прож в квартире (взято из ЛС квартиры)
223	acc_app.living_cnt_r_acc AS acc_r_liv_numbr --прож в квартире (взято из ЛС комнат квартиры)
224FROM 
225	--дома
226	hmsm.hm_houses h  --полный список домов из РАО (колонки с прож нет)   idx_hm_houses_house_guid  house_guid
227	LEFT JOIN hmsm.hm_async_processes async ON async.house_guid = h.guid
228	LEFT JOIN tmp_nsi_addr adr ON adr.house_guid = h.house_guid
229	LEFT JOIN (SELECT * FROM DBLINK('hcsnsidb_d', 'SELECT ad.aoguid, ad.regioncode, ad.offname FROM nsism.nsi_addrobj ad where ad.aolevel = 1 AND  ad.is_actual') AS (region_code varchar(36), regioncode varchar(36), name varchar(64))) reg ON reg.region_code = h.region_code
230	LEFT JOIN tmp_house_params hpar ON hpar.house_guid = h.guid --доп параметры дома
231	LEFT JOIN tmp_rooms rh ON rh.house_guid = h.guid AND rh.apartment_guid IS NULL --  комнаты в домах (не имеющие квартир)
232	LEFT JOIN tmp_room_params rhpar ON rhpar.house_guid = h.guid AND rhpar.apartment_guid IS NULL -- доп параметры комнаты
233	LEFT JOIN tmp_living_acc acc_r ON acc_r.house_guid = h.guid AND acc_r.apartment_guid IS NULL --  ЛС комнат  (их немного поэтому INDEX idx_tmp_living_acc )
234	--квартиры 
235	LEFT JOIN hmsm.hm_apartments ap ON ap.house_guid = h.guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier AND apartment_category_code = '1'--FK на hm_houses, квартиры (блоки) (колонки с прож нет) 1 Жилое 2 Нежилое nsism.nsi_apartment_categories
236	LEFT JOIN tmp_apartment_params appar ON appar.apartment_guid = ap.guid -- доп параметры квартир idx_tmp_apartment_params apartment_guid
237	LEFT JOIN tmp_room_params rpar ON rpar.apartment_guid = ap.guid -- доп параметры комнаты
238	LEFT JOIN tmp_rooms r ON r.apartment_guid = ap.guid --  комнаты в помещениях
239	LEFT JOIN tmp_living_acc acc_app ON acc_app.house_guid = h.guid AND acc_app.apartment_guid = ap.guid --  ЛС помещений  idx_tmp_living_acc_h_ap по house_guid и  apartment_guid
240WHERE h.status = 'APPROVED' AND async.guid IS NULL; --дом не в асинх обраб
241--AND h.region_code = 'd02f30fc-83bf-4c0f-ac2b-5729a866a207' --камчатка
242
243
244 
245--выгрузка в эксель данных по домам
246SELECT
247MAX(regioncode) regioncode, --для разбивки нет в экселе
248hm_guid, --№ п/п
249--MAX(house_guid) house_guid,
250--MAX(house_code) house_code,
251MAX(house_type_code) house_type_code, --Тип дома
252--BOOL_OR(blocked_house) blocked_house,--жд блок застройки	
253MAX(postalcode) postalcode,-- Индекс дома
254MAX(adr) adr,--Адрес дома
255MAX(oktmo_code) oktmo_code, --ОКТМО
256
257MAX(common_house) common_house, --Общежитие
258
259MAX(building_year_1957) building_year_1957,  --Год постройки ранее 1957
260MAX(building_year_1957_1970) building_year_1957_1970,  --Год постройки 1957-1970
261MAX(building_year_1971_1995) building_year_1971_1995,  --Год постройки 1971-1995
262MAX(building_year_1996_2002) building_year_1996_2002,  --Год постройки 1996-2002
263MAX(building_year_2003_2010) building_year_2003_2010,  --Год постройки 2003-2010
264MAX(building_year_2010) building_year_2010,  --Год постройки после 2010
265MAX(type_gas) type_gas,  --Газ (сетевой/сжижженый(баллоны))
266MAX(type_hvs_name) type_hvs_name, --хвс
267MAX(type_gvs_name) type_gvs_name, --гвс 
268MAX(type_otopl) type_otopl,  --отопление
269MAX(type_canal) type_canal,  --канализация
270
271MAX(house_total_square) house_total_square, --Общая площадь здания
272MAX(wall_materials) wall_materials, -- Тип внутренних стен   wall_materials Материалы стен - список строкой 	
273MAX(building_year) building_year, --Год постройки
274MAX(operation_year) operation_year, --Год ввода в эксплуатацию
275MAX(max_floor_count) max_floor_count, --Кол-во этажей
276MAX(underground_floor_count) underground_floor_count, -- Количество подземных этажей
277MAX(house_plan_ser_type) house_plan_ser_type, --Серия, тип проекта здания
278--МКД
279COUNT(CASE WHEN house_type_code = 'МКД' THEN apartment_guid END) AS cnt_app_mkd,--  МКД Общее кол-во квартир (в т.ч.квартир ком.заселения)	
280COUNT(CASE WHEN house_type_code = 'МКД' AND residential_premise_type_code = '2' THEN apartment_guid END) AS cnt_app_cmnl_mkd,-- МКД Кол-во квартир коммунального заселения	
281COUNT(CASE WHEN house_type_code = 'МКД' AND GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr)>=0 THEN apartment_guid END) AS cnt_app_liv_mkd, --МКД Кол-во квартир с указанным кол-вом проживающих	
282SUM(COALESCE( CASE WHEN house_type_code = 'МКД' THEN GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr) END,0)) AS cnt_mkd_liv_numbr, --МКД Кол-во проживающих
283--ЖД
284COUNT(CASE WHEN house_type_code = 'ЖД блок' THEN apartment_guid END) AS cnt_blk_in_gd,-- ЖД блок  Кол-во блоков	
285COUNT(CASE WHEN house_type_code = 'ЖД блок' AND GREATEST(room_par_liv_numbr, room_liv_numbr, acc_r_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr)>=0 THEN apartment_guid END) AS cnt_blk_rms_in_gd, --ЖД блок  Кол-во блоков с комнатами (наличие комнаты определяем по наличию параметров по комнате)	
286COUNT(CASE WHEN house_type_code = 'ЖД блок' AND GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr, hm_houses_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr)>=0 THEN apartment_guid END) AS cnt_blk_hv_liv_gd, --ЖД блок  Кол-во блоков с указанным кол-вом проживающих	(??0 не считаем указанным)
287SUM(COALESCE(CASE WHEN house_type_code = 'ЖД блок' THEN GREATEST(room_par_liv_numbr, room_liv_numbr, acc_r_liv_numbr, hm_houses_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr) END,0)) AS cnt_blk_liv_gd, --ЖД блок  Кол-во проживающих
288SUM(COALESCE(CASE WHEN house_type_code = 'ЖД' THEN GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr, hm_houses_liv_numbr, room_h_liv_numbr, room_h_par_liv_numbr, acc_h_rooms_liv_numbr) END, 0)) AS cnt_liv_gd --ЖД Кол-во проживающих НЕ блокированная застройка
289FROM tmp_house_apart_liv_numbr  
290GROUP BY hm_guid;
291 
292 
293--выгрузка в эксель данных по квартирам/блокам
294SELECT
295	MAX(regioncode) AS regioncode, --для разбивки нет в экселе
296	MAX(hm_guid) AS hm_guid, --Номер строки дома из Листа 1
297	MAX(flat_number) AS flat_number, --номер квартиры
298	MAX(CASE
299		 WHEN residential_premise_type_code = '1' THEN 'Отдельная квартир'
300		 WHEN residential_premise_type_code = '2' THEN 'Квартира коммунального заселения'
301		 WHEN residential_premise_type_code = '3' THEN 'Общежитие'
302	END) AS apart_type,--Тип жилого помещения  
303	MAX(apart_total_square) AS apart_total_square,--Общая площадь жилого помещения
304	MAX(residential_square) AS residential_square, -- Жилая площадь
305	MAX(rooms_count) AS rooms_count, --Число жилых комнат квартиры коммунального заселения
306	MAX(GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr)) AS liv_numbr --Количество проживающих в квартире/блоке	
307FROM tmp_house_apart_liv_numbr  
308WHERE apartment_guid IS not null AND GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr) >=0
309GROUP BY apartment_guid;
310 
311
312
313--выгрузка в эксель данных по комнатам
314--(prv в отличии от основного запроса тут нет поиска колва проживающих по ЛС, в тз вроде не требуется)
315SELECT 
316	-- regioncode, --для разбивки нет в экселе prv вроде не нужна разбивка по регионам для комнат, их не много?
317	r.guid, --№ п/п
318	MAX(r.house_guid) AS hm_guid, --Номер строки дома из Листа 1
319	MAX(ap.guid) AS apartment_guid,--Номер строки квартиры из Листа 2
320	MAX(COALESCE(r.house_guid, ap.house_guid)) AS house_guid, --Номер строки дома (комната может ссылаться на дом)
321	MAX(r.room_number) room_number, -- Номер комнаты  
322	MAX(r.total_square) total_square,   --Площадь комнаты	
323	MAX(COALESCE(CASE WHEN regexp_replace(r.number_of_living, '[^0-9]', '99999', 'g')::integer < 1000 THEN r.number_of_living::integer END, value_integer)) AS liv_numbr -- Количество проживающих в комнате < 1000 --колво прож текстовое, поэтому фильтруем нечисловые значения заменяя их на 99999
324FROM hmsm.hm_rooms r
325LEFT JOIN hmsm.hm_apartments ap ON ap.guid = r.apartment_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier --комната может быть без квартиры, например сразу в жилом доме
326LEFT JOIN 
327	(SELECT r.guid, MAX(val.value_integer) AS value_integer
328	FROM hmsm.hm_rooms r
329	JOIN hmsm.hm_room_params par ON par.room_guid = r.guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
330	JOIN hmsm.hm_room_estate_objects obj ON obj.guid = par.room_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND par.actual
331	JOIN hmsm.hm_room_estate_object_values val ON val.estate_object_guid = obj.guid AND now() between val.system_date_from AND COALESCE(val.system_date_to, '2999-01-01')
332	GROUP BY r.guid) param ON param.guid = r.guid
333WHERE r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
334GROUP BY r.guid;