BqKh6nH2

1-- Создаем временную таблицу - все решения, подлежащие анализу
2DROP TABLE IF EXISTS tmp_decisions;
3CREATE TEMPORARY TABLE tmp_decisions AS
4SELECT
5	dch.guid AS guid,
6	dch.house_guid AS fias_code,
7	-- выбор СФФКР и замена владельца специального счета трактуются как один вид
8	CASE
9		WHEN dch.decision_type_code = '7' THEN '1'
10		ELSE dch.decision_type_code
11	END AS decision_type,
12	CASE
13		WHEN dch.om_protocol_guid IS NULL THEN FALSE
14		ELSE TRUE
15	END AS protocol
16FROM
17	crpsm.crp_decision_header dch
18WHERE
19	dch.status = 'PUBLISHED';
20
21ANALYZE tmp_decisions;
22
23-- Создаём временную таблицу - таблица маппинга
24DROP TABLE IF EXISTS tmp_mapping;
25CREATE TEMPORARY TABLE tmp_mapping AS
26SELECT
27	actual_guid,
28	not_actual_guid
29FROM
30	dblink('dbname=hcsnsidb host=pgf-nsi user=hcs_nsi_rw password=hcs_nsi_rw',
31		'SELECT hm.houseguid_double, hm.houseguid_actual FROM nsism.nsi_house_mapping hm'
32	) AS m(
33		not_actual_guid VARCHAR(36),
34		actual_guid VARCHAR(36)
35	);
36
37ANALYZE tmp_mapping;
38
39-- Создаём временную таблицу - таблица решений с дубликатами домов
40DROP TABLE IF EXISTS tmp_grouped;
41CREATE TEMPORARY TABLE tmp_grouped AS
42SELECT
43	MAX(decisions.guid) AS decision_guid,
44	mapping.actual_guid AS actual_fias_code,
45	MAX(mapping.not_actual_guid) AS not_actual_fias_code
46FROM
47	tmp_decisions decisions
48	INNER JOIN tmp_mapping mapping ON decisions.fias_code = mapping.not_actual_guid
49GROUP BY
50	mapping.actual_guid,
51	decisions.decision_type
52HAVING
53	COUNT(decisions.fias_code) = 1
54	AND mapping.actual_guid != MAX(mapping.not_actual_guid)
55	AND COUNT(CASE WHEN protocol='true' THEN 1 END) = 0;
56
57ANALYZE tmp_grouped;
58
59-- Вставка новых домов в таблицу адресов, если их там еще нет
60INSERT INTO crpsm.crp_fias_address (house_guid, region_guid)
61SELECT
62	DISTINCT
63	tmp_grouped.actual_fias_code,
64	fha2.region_guid
65FROM
66	tmp_grouped
67	LEFT JOIN crpsm.crp_fias_address fha ON fha.house_guid = tmp_grouped.actual_fias_code
68	LEFT JOIN crpsm.crp_fias_address fha2 ON fha2.house_guid = tmp_grouped.not_actual_fias_code
69WHERE 
70	fha.house_guid IS NULL;
71
72-- Создание и заполнение таблицы в savepoints
73CREATE TABLE IF NOT EXISTS savepoints.hcsint_40716_crp_decision_header AS
74	SELECT
75		*
76	FROM
77		crpsm.crp_decision_header
78	WHERE
79		guid IN (	SELECT
80						tmp_grouped.decision_guid
81					FROM
82						tmp_grouped
83				);
84
85-- Замена дублей гуидов домов на актуальные для найденных решений				
86UPDATE	
87	crpsm.crp_decision_header
88SET house_guid = gr.actual_fias_code
89FROM
90	tmp_grouped gr
91WHERE
92	guid = gr.decision_guid;