· 7 years ago · Feb 21, 2019, 08:54 PM
1
2-- доп параметры дома (в Ñамом доме колонки нет)
3DROP TABLE IF EXISTS tmp_house_params;
4CREATE TEMPORARY TABLE tmp_house_params AS
5SELECT gin.house_guid, MAX(val.value_integer) AS liv_numbr
6FROM hmsm.hm_house_general_infos gin
7JOIN 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
8JOIN hmsm.hm_house_estate_objects obj ON obj.guid = par.house_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND obj.param_code = '20140' -- 20140 КоличеÑтво проживающих select * from nsism.nsi_housing_fund_attr_description_forms where name like '%прожив%'
9JOIN hmsm.hm_house_estate_object_values val ON val.estate_object_guid = obj.guid AND val.system_date_from <= now() AND val.system_date_to >= now()
10GROUP BY gin.house_guid;
11ANALYZE tmp_house_params;
12
13-- доп параметры квартиры (в Ñамой квартире колонки нет)
14DROP TABLE IF EXISTS tmp_apartment_params ;
15CREATE TEMPORARY TABLE tmp_apartment_params AS
16SELECT par.apartment_guid, MAX(val.value_integer) AS liv_numbr
17FROM hmsm.hm_apartment_params par
18JOIN 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 --20125 КоличеÑтво лиц, проживающих в квартире. 20130 КоличеÑтво граждан, проживающих в комнате в коммунальной квартире select * from nsism.nsi_housing_fund_attr_description_forms where name like '%прожив%'
19JOIN hmsm.hm_apartment_estate_object_values val ON val.estate_object_guid = obj.guid AND now() BETWEEN val.system_date_from AND val.system_date_to
20GROUP BY par.apartment_guid;
21ANALYZE tmp_apartment_params;
22
23-- доп параметры комнаты (в Ñамой комнате колонка то же еÑÑ‚ÑŒ), UI вроде берет из Ñамой комнаты
24DROP TABLE IF EXISTS tmp_room_params;
25CREATE TEMPORARY TABLE tmp_room_params AS
26SELECT house_guid, apartment_guid, SUM(value_integer) AS liv_numbr
27FROM (
28SELECT r.house_guid, r.guid, r.apartment_guid, MAX(val.value_integer) AS value_integer
29FROM hmsm.hm_rooms r
30JOIN hmsm.hm_room_params par ON par.room_guid = r.guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
31JOIN hmsm.hm_room_estate_objects obj ON obj.guid = par.room_estate_object_guid AND obj.status IN ('CREATED', 'MODIFIED') AND par.actual
32JOIN hmsm.hm_room_estate_object_values val ON val.estate_object_guid = obj.guid AND val.system_date_from <= now() AND val.system_date_to >= now()
33GROUP BY r.house_guid, r.guid, r.apartment_guid )t
34GROUP BY house_guid, apartment_guid;
35ANALYZE tmp_room_params;
36
37-- комнаты (в доп параметрах комнаты то же еÑÑ‚ÑŒ параметр), UI вроде берет из Ñамой комнаты
38DROP TABLE IF EXISTS tmp_rooms;
39CREATE TEMPORARY TABLE tmp_rooms AS
40SELECT r.house_guid, r.apartment_guid, SUM(r.number_of_living::integer) AS liv_numbr
41FROM hmsm.hm_rooms r
42LEFT JOIN hmsm.hm_apartments ap ON ap.guid = r.apartment_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier --комната может быть без квартиры, например Ñразу в жилом доме
43WHERE r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
44AND regexp_replace(r.number_of_living, '[^0-9]', '99999', 'g')::integer < 1000 --колво прож текÑтовое, поÑтому фильтруем нечиÑловые Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ñ Ð·Ð°Ð¼ÐµÐ½ÑÑ Ð¸Ñ… на 99999
45GROUP BY r.house_guid, r.apartment_guid;
46ANALYZE tmp_rooms;
47
48
49
50-- ЛС
51--колво проживающих из ЛС: по каждой комнате или квартире взÑÑ‚ поÑледний по дате Ð¸Ð·Ð¼ÐµÐ½ÐµÐ½Ð¸Ñ Ð›Ð¡. Комнаты Ñуммированы по квартире
52DROP TABLE IF EXISTS tmp_living_acc;
53CREATE TEMPORARY TABLE tmp_living_acc AS
54SELECT
55 house_guid,
56 apartment_guid,
57 MAX(living_cnt_hd_acc) AS living_cnt_hd_acc, --прож в ЖД
58 MAX(living_cnt_ap_acc) AS living_cnt_ap_acc, --прож в квартире
59 SUM(living_cnt_r_acc) AS living_cnt_r_acc --прож в комнате
60FROM
61 (
62 SELECT DISTINCT
63 acc.house_guid, COALESCE(ap.guid, ap_r.guid) AS apartment_guid, r.guid,
64 -- ЛС ЖД
65 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,
66 -- ЛС квартиры
67 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,
68 --ЛС комнаты
69 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
70 FROM
71 hmsm.hm_calc_shares sh
72 JOIN hmsm.hm_accounts acc ON acc.guid = sh.account_guid AND acc.account_status = 'ACTUAL' AND acc.is_actual AND sh.annulled = false
73 -- квартиры без комнат
74 LEFT JOIN hmsm.hm_apartments ap ON ap.guid = sh.apartments_guid AND ap.actual AND ap.status = 'APPROVED' AND ap.confirmed_by_supplier
75 -- комнаты (и квартиры - еÑли еÑÑ‚ÑŒ)
76 LEFT JOIN hmsm.hm_rooms r ON r.guid = sh.room_guid AND r.actual AND r.status = 'APPROVED' AND r.confirmed_by_supplier
77 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
78 ) t
79GROUP BY house_guid, apartment_guid;
80CREATE INDEX idx_tmp_living_acc ON tmp_living_acc (house_guid) WHERE apartment_guid IS NULL;
81ANALYZE tmp_living_acc;
82
83--select * from tmp_living_acc where living_cnt_hd_acc > 0
84
85--витрина по домам и помещениÑм (порÑдок полей как в шаблоне xls)
86DROP TABLE IF EXISTS tmp_house_apart_liv_numbr ;
87CREATE TEMPORARY TABLE tmp_house_apart_liv_numbr AS
88SELECT
89 adr.regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
90 h.guid, --№ п/п
91 h.house_guid,
92 h.house_code,
93 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,
94 h.blocked_house,--жд блок заÑтройки
95 adr.postalcode,-- Ð˜Ð½Ð´ÐµÐºÑ Ð´Ð¾Ð¼Ð°
96 adr.adr,--ÐÐ´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
97 h.oktmo_code, --ОКТМО
98 h.total_square AS house_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ зданиÑ
99 h.wall_materials, -- Тип внутренних Ñтен wall_materials Материалы Ñтен - ÑпиÑок Ñтрокой
100 h.building_year, --Год поÑтройки
101 h.operation_year, --Год ввода в ÑкÑплуатацию
102 h.max_floor_count, --Кол-во Ñтажей
103 h.underground_floor_count, -- КоличеÑтво подземных Ñтажей
104 COALESCE(h.plan_series,'') ||'-'|| COALESCE(h.plan_type_code,'') AS house_plan_ser_type, --СериÑ, тип проекта зданиÑ
105
106 hpar.liv_numbr AS hm_houses_liv_numbr, -- Кол-во проживающих в доме из доп.параметров дома
107 rh.liv_numbr AS room_h_liv_numbr, --Кол-во проживающих из комнат (в доме без квартир)
108 rhpar.liv_numbr AS room_h_par_liv_numbr, --Кол-во проживающих из доп парам комнат (в доме без квартир)
109 acc_r.living_cnt_hd_acc AS acc_h_rooms_liv_numbr, -- Кол-во проживающих из ЛС комнат (в доме без квартир)
110
111 ap.guid AS apartment_guid, --квартиры (блоки в ЖД лежат в квартирах, в блоке могут быть комнаты)
112 ap.flat_number, --номер квартиры
113 ap.total_square AS apart_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ жилого помещениÑ/блока
114 ap.residential_premise_type_code,--"ХарактериÑтика помещениÑ" = квартира коммунальго заÑÐµÐ»ÐµÐ½Ð¸Ñ 1ÐžÑ‚Ð´ÐµÐ»ÑŒÐ½Ð°Ñ ÐºÐ²Ð°Ñ€Ñ‚Ð¸Ñ€Ð° 2Квартира коммунального заÑÐµÐ»ÐµÐ½Ð¸Ñ 3Общежитие nsism.nsi_resident_premise_types
115
116 appar.liv_numbr AS apart_liv_numbr, --прож в квартире (взÑто из доп парам квартиры)
117 rpar.liv_numbr AS room_par_liv_numbr, --прож в квартире (взÑто из доп парам комнаты)
118 r.liv_numbr AS room_liv_numbr, --прож в квартире (взÑто из комнат)
119 acc_app.living_cnt_ap_acc AS acc_ap_liv_numbr, --прож в квартире (взÑто из ЛС квартиры)
120 acc_app.living_cnt_r_acc AS acc_r_liv_numbr --прож в квартире (взÑто из ЛС комнат квартиры)
121FROM
122 --дома
123 hmsm.hm_houses h --полный ÑпиÑок домов из Ð ÐО (колонки Ñ Ð¿Ñ€Ð¾Ð¶ нет)
124 LEFT JOIN hmsm.hm_async_processes async ON async.house_guid = h.guid
125 --nsi может в tmp?
126 LEFT JOIN (
127 SELECT house_guid, postalcode, regioncode, adr
128 FROM DBLINK('hcsnsidb_d', '
129 SELECT house_guid, a.postalcode, ad.regioncode, nsism.func_get_address_by_attr(house_abbr,house_fullname,null,
130 region_fullname,area_abbr,area_fullname,city_abbr,city_fullname,settlement_abbr,settlement_fullname,street_abbr,
131 street_fullname,additional_territory_abbr,additional_territory_fullname,
132 additional_ter_street_abbr,additional_ter_street_fullname,ctar_abbr,ctar_fullname,planstruct_abbr,planstruct_fullname) adr
133 FROM nsism.nsi_addresses a
134 JOIN nsism.nsi_addrobj ad ON ad.aouid = a.region_guid AND ad.aolevel = 1 AND a.is_actual AND ad.is_actual
135 ') AS (house_guid varchar(36), postalcode varchar(6), regioncode varchar(2), adr text)
136 ) adr ON adr.house_guid = h.house_guid
137
138 LEFT JOIN tmp_house_params hpar ON hpar.house_guid = h.guid --доп параметры дома
139 LEFT JOIN tmp_rooms rh ON rh.house_guid = h.guid AND rh.apartment_guid IS NULL -- комнаты в домах (не имеющие квартир)
140 LEFT JOIN tmp_room_params rhpar ON rhpar.house_guid = h.guid AND rhpar.apartment_guid IS NULL -- доп параметры комнаты
141 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 )
142 --помещениÑ
143 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'-- квартиры (блоки) (колонки Ñ Ð¿Ñ€Ð¾Ð¶ нет) 1 Жилое 2 Ðежилое nsism.nsi_apartment_categories
144 LEFT JOIN tmp_apartment_params appar ON appar.apartment_guid = ap.guid -- доп параметры квартир
145 LEFT JOIN tmp_room_params rpar ON rpar.apartment_guid = ap.guid -- доп параметры комнаты
146 LEFT JOIN tmp_rooms r ON r.apartment_guid = ap.guid -- комнаты в помещениÑÑ…
147 LEFT JOIN tmp_living_acc acc_app ON acc_app.house_guid = h.guid AND acc_app.apartment_guid = ap.guid -- ЛС помещений
148WHERE h.status = 'APPROVED' AND async.guid IS NULL; --дом не в аÑинх обраб
149
150
151
152--выгрузка в ÑкÑель данных по домам
153SELECT
154MAX(regioncode) regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
155guid, --№ п/п
156--MAX(house_guid) house_guid,
157--MAX(house_code) house_code,
158MAX(house_type_code) house_type_code, --Тип дома
159--BOOL_OR(blocked_house) blocked_house,--жд блок заÑтройки
160MAX(postalcode) postalcode,-- Ð˜Ð½Ð´ÐµÐºÑ Ð´Ð¾Ð¼Ð°
161MAX(adr) adr,--ÐÐ´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
162MAX(oktmo_code) oktmo_code, --ОКТМО
163MAX(house_total_square) house_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ зданиÑ
164MAX(wall_materials) wall_materials, -- Тип внутренних Ñтен wall_materials Материалы Ñтен - ÑпиÑок Ñтрокой
165MAX(building_year) building_year, --Год поÑтройки
166MAX(operation_year) operation_year, --Год ввода в ÑкÑплуатацию
167MAX(max_floor_count) max_floor_count, --Кол-во Ñтажей
168MAX(underground_floor_count) underground_floor_count, -- КоличеÑтво подземных Ñтажей
169MAX(house_plan_ser_type) house_plan_ser_type, --СериÑ, тип проекта зданиÑ
170--МКД
171COUNT(CASE WHEN house_type_code = 'МКД' THEN apartment_guid END) AS cnt_app_mkd,-- МКД Общее кол-во квартир (в Ñ‚.ч.квартир ком.заÑелениÑ)
172COUNT(CASE WHEN house_type_code = 'МКД' AND residential_premise_type_code = '2' THEN apartment_guid END) AS cnt_app_cmnl_mkd,-- МКД Кол-во квартир коммунального заÑелениÑ
173COUNT(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, --МКД Кол-во квартир Ñ ÑƒÐºÐ°Ð·Ð°Ð½Ð½Ñ‹Ð¼ кол-вом проживающих
174SUM(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, --МКД Кол-во проживающих
175--ЖД
176COUNT(CASE WHEN house_type_code = 'ЖД блок' THEN apartment_guid END) AS cnt_blk_in_gd,-- ЖД блок Кол-во блоков
177COUNT(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, --ЖД блок Кол-во блоков Ñ ÐºÐ¾Ð¼Ð½Ð°Ñ‚Ð°Ð¼Ð¸ (наличие комнаты определÑем по наличию параметров по комнате)
178COUNT(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 не Ñчитаем указанным)
179SUM(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, --ЖД блок Кол-во проживающих
180SUM(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 --ЖД Кол-во проживающих ÐЕ Ð±Ð»Ð¾ÐºÐ¸Ñ€Ð¾Ð²Ð°Ð½Ð½Ð°Ñ Ð·Ð°Ñтройка
181FROM tmp_house_apart_liv_numbr
182GROUP BY guid;
183
184
185--выгрузка в ÑкÑель данных по квартирам/блокам
186SELECT
187MAX(regioncode) regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
188MAX(guid) guid, --Ðомер Ñтроки дома из ЛиÑта 1
189MAX(GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr)) AS liv_numbr, --КоличеÑтво проживающих в квартире/блоке
190MAX(apart_total_square) --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ жилого помещениÑ
191FROM tmp_house_apart_liv_numbr
192WHERE 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
193GROUP BY apartment_guid