4itj97rc

1DO $migrate_house_doubles$
2
3DECLARE 
4	r record;
5
6	houses text[];
7
8	v_fias_code varchar(36);
9	url_decision_guid varchar(36);
10	
11	table_timestamp text;
12
13	i integer := 0;
14
15BEGIN 
16	
17	SELECT translate(current_date::text, '-', '') || '_' || translate(split_part(current_time::text, '.', 1), ':', '') INTO table_timestamp;
18	RAISE NOTICE 'Savepoints tables marker = %', table_timestamp;
19	EXECUTE format('CREATE TABLE IF NOT EXISTS savepoints.hcsint_40716_crp_decision_header' || '_' || table_timestamp || 
20					' AS SELECT * FROM crpsm.crp_decision_header WHERE 1=2');
21	
22	FOR r IN(
23		WITH decisions AS(
24			SELECT
25				dch.*,
26				dch.house_guid AS fias_code,
27				-- выбор СФФКР и замена владельца специального счета трактуются как один вид
28				CASE
29					WHEN dch.decision_type_code = '7' THEN '1'
30					ELSE dch.decision_type_code
31				END AS decision_type
32			FROM
33				crpsm.crp_decision_header dch
34			WHERE
35				dch.status = 'PUBLISHED'
36				AND dch.om_protocol_guid IS NULL
37		),
38		mapping AS(
39			SELECT
40				*
41			FROM
42				dblink('dbname=hcsnsidb host=pgf-nsi user=hcs_nsi_rw password=hcs_nsi_rw',
43					'SELECT hm.houseguid_double, hm.houseguid_actual FROM nsism.nsi_house_mapping hm WHERE hm.houseguid_double IN(SELECT unnest(string_to_array('''
44					|| (SELECT string_agg(fias_code, ' ') FROM decisions)::TEXT || ''', '' '')))'
45				) AS m(
46					not_actual_guid VARCHAR(36),
47					actual_guid VARCHAR(36)
48				)
49		), grouped AS (SELECT
50			COUNT( decisions.fias_code ) AS doubles,
51			string_agg(decisions.fias_code || ':'|| decisions.guid, ', ' ORDER BY decisions.fias_code) AS fias_to_url_guid_map,
52			mapping.actual_guid AS actual_fias_code
53		FROM
54			decisions
55		INNER JOIN mapping ON
56			decisions.fias_code = mapping.not_actual_guid OR decisions.fias_code = mapping.actual_guid
57		GROUP BY
58			mapping.actual_guid,
59			decisions.decision_type,
60			decisions.effective_date
61		), grouped_filtered AS (SELECT
62			*
63		FROM
64			grouped
65		WHERE
66			doubles = 1 AND POSITION(actual_fias_code IN fias_to_url_guid_map) = 0
67		)
68		SELECT * FROM grouped_filtered
69		INNER JOIN
70			-- получаем идентификаторы регионов одним запросом через dblink
71			(	SELECT
72					*
73				FROM
74					dblink('dbname=hcsnsidb host=pgf-nsi user=hcs_nsi_rw password=hcs_nsi_rw',
75						'DROP TABLE IF EXISTS hcsint_40716_tmp_address_store;
76						CREATE TEMPORARY TABLE hcsint_40716_tmp_address_store (
77							house_guid CHARACTER VARYING,
78							region_guid CHARACTER VARYING
79						);
80						DO $$
81						DECLARE
82							house_guids_list CHARACTER VARYING[] := ' || ( SELECT quote_literal(array_agg(DISTINCT actual_fias_code)) FROM grouped_filtered ) || ';
83							rh CHARACTER VARYING;
84						BEGIN
85							FOREACH rh IN ARRAY house_guids_list
86							LOOP
87								INSERT INTO hcsint_40716_tmp_address_store SELECT rh, fah.* FROM nsism.func_find_region_aoguid_by_houseguid(rh) as fah;
88							END LOOP;
89						END$$;
90						SELECT * FROM hcsint_40716_tmp_address_store;'
91					) AS frg (
92						nsi_fias_code VARCHAR(36),
93						nsi_region_guid VARCHAR(36)
94					)
95 			) fias_region_mapping
96 		ON grouped_filtered.actual_fias_code = fias_region_mapping.nsi_fias_code
97	) LOOP
98		houses = string_to_array(r.fias_to_url_guid_map, ', ');
99		
100		RAISE NOTICE '===================================================================================================';
101		
102		url_decision_guid = (string_to_array(houses[1], ':'))[2];
103		v_fias_code = (string_to_array(houses[1], ':'))[1];
104		i := i + 1;
105		
106		IF v_fias_code = r.actual_fias_code THEN
107			RAISE NOTICE 'Skip decision_guid=%, fias_code=%, actual=%', url_decision_guid, v_fias_code, r.actual_fias_code;
108			CONTINUE;
109		END IF;
110		
111		-- вставка нового дома в таблицу адресов, если его там еще нет
112		IF NOT EXISTS (SELECT 1 FROM crpsm.crp_fias_address WHERE house_guid = r.actual_fias_code) THEN
113			RAISE NOTICE 'Add fias_house with code=%, region_guid=%', r.actual_fias_code, r.nsi_region_guid;
114			INSERT INTO crpsm.crp_fias_address (house_guid, region_guid) VALUES (r.actual_fias_code, r.nsi_region_guid);
115		END IF;
116		
117		RAISE NOTICE 'Update Decision decision_guid=% set fias_code=%', url_decision_guid, r.actual_fias_code;
118		EXECUTE ('INSERT INTO savepoints.hcsint_40716_crp_decision_header_' || table_timestamp
119				|| ' SELECT * FROM crpsm.crp_decision_header WHERE guid = $1') USING url_decision_guid;
120		UPDATE crpsm.crp_decision_header SET house_guid = r.actual_fias_code WHERE guid = url_decision_guid;
121	END LOOP;
122
123	RAISE NOTICE 'Count of updated decisions = %', i;
124
125END;
126$migrate_house_doubles$ LANGUAGE plpgsql;