· 4 years ago · Jan 25, 2021, 02:42 PM
1CREATE PROCEDURE gvms_data_import(p_host character varying, p_authorization character varying)
2 LANGUAGE plpgsql
3AS
4$$
5DECLARE
6 l_program TEXT;
7 l_row zz_gvms_import%ROWTYPE;
8 l_json_data json;
9 l_json_object json;
10 l_gmr_count INT;
11 l_state_id INT;
12 l_idx INT;
13BEGIN
14 RAISE NOTICE 'Starting GMR import from HMRC GVMS';
15 -- we need a temporary table to import JSON from CURL
16 DROP TABLE IF EXISTS gvms_gmrs;
17 CREATE TEMP TABLE gvms_gmrs
18 (
19 data VARCHAR
20 );
21
22 l_program := '/usr/bin/curl ' ||
23 '-XGET http://' || p_host || ':8080/api/goods-movement-system/movements ' ||
24 '-H "Authorization: Bearer ' || p_authorization || '"';
25 l_program := 'COPY gvms_gmrs
26 FROM PROGRAM ' || E'\'' || l_program || E'\'';
27 EXECUTE l_program;
28
29 -- parse list of GMRs, and save them inf not existing
30 l_json_data := (SELECT data FROM gvms_gmrs LIMIT 1);
31 FOR l_json_object IN
32 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'data')
33 LOOP
34 RAISE NOTICE ' Importing GMR with id %', l_json_object -> 'gmrId';
35 IF l_json_object -> 'gmrId' IS NULL THEN
36 -- no gmrId in json
37 RAISE WARNING ' Skipping GMR without id';
38 CONTINUE;
39 END IF;
40
41 l_gmr_count := (SELECT COUNT(*) FROM zz_gvms_import WHERE gmr_id = (l_json_object ->> 'gmrId'));
42
43 IF l_gmr_count > 0 THEN
44 -- we have imported that one already
45 RAISE INFO ' Skipping imported GMR with id %', l_json_object -> 'gmrId';
46 CONTINUE;
47 END IF;
48
49 INSERT INTO zz_gvms_import (gmr_id, import_date, data, imported)
50 VALUES (l_json_object ->> 'gmrId', NOW()::DATE, NULL, 'N');
51 END LOOP;
52
53 RAISE NOTICE 'Reading full data for GMRs from HMRC GVMS';
54
55 FOR l_row IN
56 SELECT *
57 FROM zz_gvms_import
58 WHERE data IS NULL
59 AND imported = 'N'
60 LOOP
61 RAISE NOTICE ' Reading data for GMR with id %', l_row.gmr_id;
62 TRUNCATE TABLE gvms_gmrs;
63
64 l_program := '/usr/bin/curl ' ||
65 ' -XGET http://' || p_host || ':8080/api/goods-movement-system/movements/' || l_row.gmr_id ||
66 ' -H "Authorization: Bearer ' || p_authorization || '"';
67 l_program := ' COPY gvms_gmrs
68 FROM PROGRAM ' || E'\'' || l_program || E'\'';
69 EXECUTE l_program;
70 PERFORM PG_SLEEP(1);
71
72 UPDATE zz_gvms_import SET data = (SELECT data FROM gvms_gmrs LIMIT 1) WHERE gmr_id = l_row.gmr_id;
73 END LOOP;
74
75 -- we can commit data, now we got data from web
76 COMMIT;
77
78 RAISE NOTICE 'Inserting data into local tables';
79
80 FOR l_row IN
81 SELECT gmr_id, import_date, data, imported
82 FROM zz_gvms_import
83 WHERE imported <> 'Y'
84 LOOP
85 RAISE NOTICE ' Importing % for date %', l_row.gmr_id, l_row.import_date;
86
87 l_json_data := l_row.data::json -> 'data';
88
89 l_gmr_count := (SELECT COUNT(*) FROM gmr_record WHERE gmr_id = l_json_data ->> 'gmrId');
90 IF l_gmr_count > 0 THEN
91 RAISE INFO ' GMR with GMR_ID % exists', l_json_data ->> 'gmrId';
92 CONTINUE;
93 END IF;
94
95 BEGIN
96
97 l_state_id :=
98 (SELECT id FROM cbk_record_state WHERE value = l_json_data -> 'metadata' ->> 'state' LIMIT 1);
99 IF l_state_id IS NULL THEN
100 l_state_id := 9999;
101 END IF;
102
103 INSERT INTO gmr_record ( id, gmr_id, direction
104 , unaccompanied
105 , vehicle_registration_number, s_and_s_master_ref_num
106 , route_id, departure_date, empty_vehicle_flag
107 , own_vehicle, s_and_s_master_ref_num_vehicle, latest_record_status
108 , last_publish_timestamp, notification_box_id, notification_message_id
109 , business_unit_id, state_id
110 , created_by, created_on)
111 VALUES ( NEXTVAL('gmr_record_seq'), l_json_data ->> 'gmrId', l_json_data ->> 'direction'
112 , CASE WHEN (l_json_data ->> 'isUnaccompanied')::BOOLEAN THEN 'Y' ELSE 'N' END
113 , l_json_data ->> 'vehicleRegNum', l_json_data ->> 'sAndSMasterRefNum'
114 , l_json_data -> 'plannedCrossing' ->> 'routeId', NULL, 'N'
115 , 'N', NULL, l_json_data -> 'metadata' ->> 'state'
116 , NULL, NULL, NULL, 2, l_state_id
117 , 'HMRC DATA IMPORT', NOW());
118
119
120 -- trailer registrations
121 l_idx := 0;
122 FOR l_json_object IN
123 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'trailerRegistrationNums')
124 LOOP
125 INSERT INTO gmr_trailer_registration (record_id, index, registration_number)
126 VALUES (CURRVAL('gmr_record_seq'), l_idx, l_json_object::VARCHAR);
127 l_idx := l_idx + 1;
128 END LOOP;
129
130 -- container reference nums
131 l_idx := 0;
132 FOR l_json_object IN
133 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'containerReferenceNums')
134 LOOP
135 INSERT INTO gmr_container_reference (record_id, index, reference_number)
136 VALUES (CURRVAL('gmr_record_seq'), l_idx, l_json_object::VARCHAR);
137 l_idx := l_idx + 1;
138 END LOOP;
139
140 l_idx := 0;
141 -- customs declarations
142 FOR l_json_object IN
143 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'customsDeclarations')
144 LOOP
145 INSERT INTO gmr_declaration ( id, type, index, record_id
146 , customs_declaration_id, s_and_s_master_ref_num
147 , created_by, created_on)
148 VALUES ( NEXTVAL('gmr_declaration_seq'), 'CUSTOMS', l_idx, CURRVAL('gmr_record_seq')
149 , l_json_object ->> 'customsDeclarationId', l_json_object ->> 'sAndSMasterRefNum'
150 , 'HMRC DATA IMPORT', NOW());
151 l_idx := l_idx + 1;
152 END LOOP;
153
154 -- transit declarations
155 FOR l_json_object IN
156 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'transitDeclarations')
157 LOOP
158 INSERT INTO gmr_declaration ( id, type, index, record_id
159 , transit_declaration_id, s_and_s_master_ref_num
160 , tsad
161 , created_by, created_on)
162 VALUES ( NEXTVAL('gmr_declaration_seq'), 'TRANSIT', l_idx, CURRVAL('gmr_record_seq')
163 , l_json_object ->> 'transitDeclarationId', l_json_object ->> 'sAndSMasterRefNum'
164 , CASE WHEN (l_json_object ->> 'isTSAD')::BOOLEAN THEN 'Y' ELSE 'N' END
165 , 'HMRC DATA IMPORT', NOW());
166 l_idx := l_idx + 1;
167 END LOOP;
168
169 -- tir declarations
170 FOR l_json_object IN
171 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'tirDeclarations')
172 LOOP
173 INSERT INTO gmr_declaration ( id, type, index, record_id
174 , tir_carnet_id, s_and_s_master_ref_num
175 , created_by, created_on)
176 VALUES ( NEXTVAL('gmr_declaration_seq'), 'TIR', l_idx, CURRVAL('gmr_record_seq')
177 , l_json_object ->> 'tirCarnetId', l_json_object ->> 'sAndSMasterRefNum'
178 , 'HMRC DATA IMPORT', NOW());
179 l_idx := l_idx + 1;
180 END LOOP;
181
182 -- ata declarations
183 FOR l_json_object IN
184 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'ataDeclarations')
185 LOOP
186 INSERT INTO gmr_declaration ( id, type, index, record_id
187 , ata_carnet_id
188 , created_by, created_on)
189 VALUES ( NEXTVAL('gmr_declaration_seq'), 'ATA', l_idx, CURRVAL('gmr_record_seq')
190 , l_json_object ->> 'ataCarnetId'
191 , 'HMRC DATA IMPORT', NOW());
192 l_idx := l_idx + 1;
193 END LOOP;
194
195 -- eidr declarations
196 FOR l_json_object IN
197 SELECT JSON_ARRAY_ELEMENTS(l_json_data -> 'eidrDeclarations')
198 LOOP
199 INSERT INTO gmr_declaration ( id, type, index, record_id
200 , trader_eori, s_and_s_master_ref_num
201 , created_by, created_on)
202 VALUES ( NEXTVAL('gmr_declaration_seq'), 'EIDR', l_idx, CURRVAL('gmr_record_seq')
203 , l_json_object ->> 'traderEORI', l_json_object ->> 'sAndSMasterRefNum'
204 , 'HMRC DATA IMPORT', NOW());
205 l_idx := l_idx + 1;
206 END LOOP;
207 COMMIT;
208 END;
209 END LOOP;
210
211END ;
212$$;
213
214ALTER PROCEDURE gvms_data_import(VARCHAR, VARCHAR) OWNER TO gvms;
215
216