· 7 years ago · Feb 26, 2019, 10:40 AM
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
86--SELECT * FROM nsism.nsi_addrobj ad where ad.aolevel = 1 and ad.is_actual and offname like '%Камч%' --41
87--Ð°Ð´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
88DROP TABLE IF EXISTS tmp_nsi_addr;
89CREATE TEMPORARY TABLE tmp_nsi_addr AS
90SELECT house_guid, postalcode, regioncode, adr
91FROM DBLINK('hcsnsidb_d', '
92 SELECT house_guid, a.postalcode, ad.regioncode, nsism.func_get_address_by_attr(house_abbr,house_fullname,null,
93 region_fullname,area_abbr,area_fullname,city_abbr,city_fullname,settlement_abbr,settlement_fullname,street_abbr,
94 street_fullname,additional_territory_abbr,additional_territory_fullname,
95 additional_ter_street_abbr,additional_ter_street_fullname,ctar_abbr,ctar_fullname,planstruct_abbr,planstruct_fullname) adr
96 FROM nsism.nsi_addresses a
97 JOIN nsism.nsi_addrobj ad ON ad.aouid = a.region_guid AND ad.aolevel = 1 AND a.is_actual AND ad.is_actual
98 ') AS (house_guid varchar(36), postalcode varchar(6), regioncode varchar(2), adr text);
99ANALYZE tmp_nsi_addr;
100
101--витрина по домам и помещениÑм (порÑдок полей как в шаблоне xls)
102DROP TABLE IF EXISTS tmp_house_apart_liv_numbr ;
103CREATE TEMPORARY TABLE tmp_house_apart_liv_numbr AS
104SELECT
105 adr.regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
106 h.guid, --№ п/п
107 h.house_guid,
108 h.house_code,
109 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,
110 h.blocked_house,--жд блок заÑтройки
111 adr.postalcode,-- Ð˜Ð½Ð´ÐµÐºÑ Ð´Ð¾Ð¼Ð°
112 adr.adr,--ÐÐ´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
113 h.oktmo_code, --ОКТМО
114 h.total_square AS house_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ зданиÑ
115 h.wall_materials, -- Тип внутренних Ñтен wall_materials Материалы Ñтен - ÑпиÑок Ñтрокой
116 h.building_year, --Год поÑтройки
117 h.operation_year, --Год ввода в ÑкÑплуатацию
118 h.max_floor_count, --Кол-во Ñтажей
119 h.underground_floor_count, -- КоличеÑтво подземных Ñтажей
120 COALESCE(h.plan_series,'') ||'-'|| COALESCE(h.plan_type_code,'') AS house_plan_ser_type, --СериÑ, тип проекта зданиÑ
121
122 hpar.liv_numbr AS hm_houses_liv_numbr, -- Кол-во проживающих в доме из доп.параметров дома
123 rh.liv_numbr AS room_h_liv_numbr, --Кол-во проживающих из комнат (в доме без квартир)
124 rhpar.liv_numbr AS room_h_par_liv_numbr, --Кол-во проживающих из доп парам комнат (в доме без квартир)
125 acc_r.living_cnt_hd_acc AS acc_h_rooms_liv_numbr, -- Кол-во проживающих из ЛС комнат (в доме без квартир)
126
127 ap.guid AS apartment_guid, --квартиры (блоки в ЖД лежат в квартирах, в блоке могут быть комнаты)
128 ap.flat_number, --номер квартиры
129 ap.total_square AS apart_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ жилого помещениÑ/блока
130 ap.residential_premise_type_code,--"ХарактериÑтика помещениÑ" = квартира коммунальго заÑÐµÐ»ÐµÐ½Ð¸Ñ 1ÐžÑ‚Ð´ÐµÐ»ÑŒÐ½Ð°Ñ ÐºÐ²Ð°Ñ€Ñ‚Ð¸Ñ€Ð° 2Квартира коммунального заÑÐµÐ»ÐµÐ½Ð¸Ñ 3Общежитие nsism.nsi_resident_premise_types
131
132 appar.liv_numbr AS apart_liv_numbr, --прож в квартире (взÑто из доп парам квартиры)
133 rpar.liv_numbr AS room_par_liv_numbr, --прож в квартире (взÑто из доп парам комнаты)
134 r.liv_numbr AS room_liv_numbr, --прож в квартире (взÑто из комнат)
135 acc_app.living_cnt_ap_acc AS acc_ap_liv_numbr, --прож в квартире (взÑто из ЛС квартиры)
136 acc_app.living_cnt_r_acc AS acc_r_liv_numbr --прож в квартире (взÑто из ЛС комнат квартиры)
137FROM
138 --дома
139 hmsm.hm_houses h --полный ÑпиÑок домов из Ð ÐО (колонки Ñ Ð¿Ñ€Ð¾Ð¶ нет)
140 LEFT JOIN hmsm.hm_async_processes async ON async.house_guid = h.guid
141 --nsi может в tmp?
142 LEFT JOIN tmp_nsi_addr adr ON adr.house_guid = h.house_guid
143 LEFT JOIN tmp_house_params hpar ON hpar.house_guid = h.guid --доп параметры дома
144 LEFT JOIN tmp_rooms rh ON rh.house_guid = h.guid AND rh.apartment_guid IS NULL -- комнаты в домах (не имеющие квартир)
145 LEFT JOIN tmp_room_params rhpar ON rhpar.house_guid = h.guid AND rhpar.apartment_guid IS NULL -- доп параметры комнаты
146 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 )
147 --помещениÑ
148 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
149 LEFT JOIN tmp_apartment_params appar ON appar.apartment_guid = ap.guid -- доп параметры квартир
150 LEFT JOIN tmp_room_params rpar ON rpar.apartment_guid = ap.guid -- доп параметры комнаты
151 LEFT JOIN tmp_rooms r ON r.apartment_guid = ap.guid -- комнаты в помещениÑÑ…
152 LEFT JOIN tmp_living_acc acc_app ON acc_app.house_guid = h.guid AND acc_app.apartment_guid = ap.guid -- ЛС помещений
153WHERE h.status = 'APPROVED' AND async.guid IS NULL; --дом не в аÑинх обраб
154
155
156
157--выгрузка в ÑкÑель данных по домам
158SELECT
159MAX(regioncode) regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
160guid, --№ п/п
161--MAX(house_guid) house_guid,
162--MAX(house_code) house_code,
163MAX(house_type_code) house_type_code, --Тип дома
164--BOOL_OR(blocked_house) blocked_house,--жд блок заÑтройки
165MAX(postalcode) postalcode,-- Ð˜Ð½Ð´ÐµÐºÑ Ð´Ð¾Ð¼Ð°
166MAX(adr) adr,--ÐÐ´Ñ€ÐµÑ Ð´Ð¾Ð¼Ð°
167MAX(oktmo_code) oktmo_code, --ОКТМО
168MAX(house_total_square) house_total_square, --ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ зданиÑ
169MAX(wall_materials) wall_materials, -- Тип внутренних Ñтен wall_materials Материалы Ñтен - ÑпиÑок Ñтрокой
170MAX(building_year) building_year, --Год поÑтройки
171MAX(operation_year) operation_year, --Год ввода в ÑкÑплуатацию
172MAX(max_floor_count) max_floor_count, --Кол-во Ñтажей
173MAX(underground_floor_count) underground_floor_count, -- КоличеÑтво подземных Ñтажей
174MAX(house_plan_ser_type) house_plan_ser_type, --СериÑ, тип проекта зданиÑ
175--МКД
176COUNT(CASE WHEN house_type_code = 'МКД' THEN apartment_guid END) AS cnt_app_mkd,-- МКД Общее кол-во квартир (в Ñ‚.ч.квартир ком.заÑелениÑ)
177COUNT(CASE WHEN house_type_code = 'МКД' AND residential_premise_type_code = '2' THEN apartment_guid END) AS cnt_app_cmnl_mkd,-- МКД Кол-во квартир коммунального заÑелениÑ
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)>=0 THEN apartment_guid END) AS cnt_app_liv_mkd, --МКД Кол-во квартир Ñ ÑƒÐºÐ°Ð·Ð°Ð½Ð½Ñ‹Ð¼ кол-вом проживающих
179SUM(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, --МКД Кол-во проживающих
180--ЖД
181COUNT(CASE WHEN house_type_code = 'ЖД блок' THEN apartment_guid END) AS cnt_blk_in_gd,-- ЖД блок Кол-во блоков
182COUNT(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, --ЖД блок Кол-во блоков Ñ ÐºÐ¾Ð¼Ð½Ð°Ñ‚Ð°Ð¼Ð¸ (наличие комнаты определÑем по наличию параметров по комнате)
183COUNT(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 не Ñчитаем указанным)
184SUM(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, --ЖД блок Кол-во проживающих
185SUM(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 --ЖД Кол-во проживающих ÐЕ Ð±Ð»Ð¾ÐºÐ¸Ñ€Ð¾Ð²Ð°Ð½Ð½Ð°Ñ Ð·Ð°Ñтройка
186FROM tmp_house_apart_liv_numbr
187GROUP BY guid;
188
189
190--выгрузка в ÑкÑель данных по квартирам/блокам
191SELECT
192MAX(regioncode) regioncode, --Ð´Ð»Ñ Ñ€Ð°Ð·Ð±Ð¸Ð²ÐºÐ¸ нет в ÑкÑеле
193MAX(guid) guid, --Ðомер Ñтроки дома из ЛиÑта 1
194MAX(GREATEST(apart_liv_numbr, room_par_liv_numbr, room_liv_numbr, acc_ap_liv_numbr, acc_r_liv_numbr)) AS liv_numbr, --КоличеÑтво проживающих в квартире/блоке
195MAX(apart_total_square) apart_total_square--ÐžÐ±Ñ‰Ð°Ñ Ð¿Ð»Ð¾Ñ‰Ð°Ð´ÑŒ жилого помещениÑ
196FROM tmp_house_apart_liv_numbr
197WHERE 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
198GROUP BY apartment_guid