· 5 years ago · Feb 06, 2020, 01:54 PM
1DO
2$$
3 BEGIN
4 BEGIN
5 ALTER TABLE experience_extranet_ticket.ticket ALTER COLUMN ticket_description SET DEFAULT NULL;
6 ALTER TABLE experience_extranet_ticket.ticket ALTER COLUMN ticket_description DROP NOT NULL;
7 END;
8 END;
9$$;
10
11CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_itinerary_geo_location (
12 itinerary_geo_location_id BIGINT NOT NULL,
13 experience_itinerary_id BIGINT NOT NULL,
14 geo_location experience_extranet_experience.geo_location NOT NULL,
15
16 _lut TIMESTAMPTZ NOT NULL,
17
18 PRIMARY KEY (itinerary_geo_location_id),
19 FOREIGN KEY (experience_itinerary_id)
20 REFERENCES experience_extranet_experience.experience_itinerary ON UPDATE CASCADE ON DELETE CASCADE
21);
22
23CREATE INDEX IF NOT EXISTS experience_extranet_experience_itinerary_geo_location_lut__idx
24 ON experience_extranet_experience.experience_itinerary_geo_location (_lut);
25
26CREATE INDEX IF NOT EXISTS experience_extranet_experience_itinerary_geo_location__experience_itinerary_id
27 ON experience_extranet_experience.experience_itinerary_geo_location (experience_itinerary_id);
28
29CREATE SEQUENCE IF NOT EXISTS experience_extranet_experience.experience_itinerary_geo_loc_seq START 1000;
30
31DO $$
32BEGIN
33 CREATE TRIGGER populate_lut_experience_itinerary_geo_loc
34 BEFORE INSERT OR UPDATE ON experience_extranet_experience.experience_itinerary_geo_location
35 FOR EACH ROW
36 EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
37EXCEPTION
38 WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
39END;
40$$;
41
42DO $$
43BEGIN
44 BEGIN
45 ALTER TABLE experience_extranet_experience.experience_itinerary
46 ADD COLUMN flight TEXT DEFAULT NULL,
47 ADD COLUMN accommodation TEXT DEFAULT NULL,
48 ADD COLUMN ground_transport TEXT DEFAULT NULL,
49 ADD COLUMN meal TEXT DEFAULT NULL,
50 ADD COLUMN additional_detail TEXT DEFAULT NULL;
51 EXCEPTION
52 WHEN duplicate_column
53 THEN RAISE NOTICE 'amenities columns already exists';
54 END;
55END;
56$$;
57
58CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_tour_additional_data(filter_experience_id text) returns jsonb
59 security definer
60 language sql
61as $$
62 WITH
63 get_tour_result AS (
64 SELECT *
65 FROM experience_extranet_experience.experience_tour
66 WHERE experience_id = filter_experience_id
67 ),
68 get_tour_meeting_point_result AS (
69 SELECT to_jsonb(result)
70 FROM (
71 SELECT *
72 FROM experience_extranet_experience.experience_tour_meeting_point mp
73 WHERE mp.experience_id = filter_experience_id
74 ) AS result
75 ),
76 get_tour_content_result AS (
77 SELECT *
78 FROM experience_extranet_content.experience_tour_content
79 WHERE experience_id = filter_experience_id
80 )
81 SELECT jsonb_build_object(
82 'pickup_available', (SELECT pickup_available FROM get_tour_result),
83 'meeting_point_geo_location', (SELECT meeting_point_geo_location FROM get_tour_result),
84 'meeting_point_address', (SELECT meeting_point_address FROM get_tour_result),
85 'duration_minute', (SELECT duration_minute FROM get_tour_result),
86 'duration_hour', (SELECT duration_hour FROM get_tour_result),
87 'duration_day', (SELECT duration_day FROM get_tour_result),
88 'all_day_long_experience', (SELECT all_day_long_experience FROM get_tour_result),
89 'additional_warning', (SELECT additional_warning FROM get_tour_result),
90 'min_age', (SELECT min_age FROM get_tour_result),
91 'max_age', (SELECT max_age FROM get_tour_result),
92 'min_height', (SELECT min_height FROM get_tour_result),
93 'max_height', (SELECT max_height FROM get_tour_result),
94 'min_weight', (SELECT min_weight FROM get_tour_result),
95 'max_weight', (SELECT max_weight FROM get_tour_result),
96 'itineraries', (SELECT array_agg(get_experience_itineraries::JSONB) FROM experience_extranet_management_v2_helper.get_experience_itineraries(filter_experience_id)),
97 'pickup_price', (SELECT pickup_price FROM get_tour_content_result),
98 'pickup_address', (SELECT pickup_address FROM get_tour_content_result),
99 'drop_off_point', (SELECT drop_off_point FROM get_tour_content_result),
100 'meeting_point_available', (SELECT CASE WHEN get_tour_meeting_point_result IS NULL THEN FALSE ELSE TRUE END
101 FROM get_tour_meeting_point_result),
102 'meeting_point', (SELECT * FROM get_tour_meeting_point_result)
103 );
104$$;
105
106CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_itinerary_geo_location(itinerary_id bigint, geo_location_spec jsonb) returns boolean
107 security definer
108 language sql
109as
110$$
111 WITH res AS (
112 INSERT INTO experience_extranet_experience.experience_itinerary_geo_location (
113 itinerary_geo_location_id,
114 experience_itinerary_id,
115 geo_location
116 )
117 VALUES (
118 CASE WHEN (
119 (SELECT COUNT(*) FROM experience_extranet_experience.experience_itinerary_geo_location WHERE itinerary_geo_location_id = (geo_location_spec->>'itinerary_geo_location_id')::BIGINT) > 0)
120 THEN (geo_location_spec->>'itinerary_geo_location_id')::BIGINT
121 ELSE nextval('experience_extranet_experience.experience_itinerary_geo_loc_seq')
122 END,
123 itinerary_id,
124 row(geo_location_spec->'geo_location'->>'lat', geo_location_spec->'geo_location'->>'lon')::experience_extranet_experience.geo_location
125 )
126 ON CONFLICT (itinerary_geo_location_id) DO UPDATE
127 SET
128 geo_location = EXCLUDED.geo_location
129 RETURNING 1
130 )
131 SELECT COUNT(*) > 0 FROM res;
132$$;
133
134CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_itinerary(
135 itinerary_id_spec BIGINT, filter_experience_id TEXT, itinerary JSONB)
136 RETURNS BOOLEAN
137SECURITY DEFINER
138LANGUAGE SQL
139AS $$
140 WITH res AS (INSERT INTO experience_extranet_experience.experience_itinerary (
141 experience_itinerary_id,
142 experience_id,
143 day,
144 all_day_long,
145 flight,
146 accommodation,
147 ground_transport,
148 meal,
149 additional_detail
150 )
151 VALUES (
152 itinerary_id_spec,
153 filter_experience_id,
154 (itinerary ->> 'day') :: INTEGER,
155 (COALESCE (itinerary->>'all_day_long', 'FALSE')):: BOOLEAN,
156 (itinerary ->> 'flight'),
157 (itinerary ->> 'accommodation'),
158 (itinerary ->> 'ground_transport'),
159 (itinerary ->> 'meal'),
160 (itinerary ->> 'additional_detail')
161 )
162 ON CONFLICT (experience_itinerary_id)
163 DO UPDATE
164 SET
165 day = EXCLUDED.day,
166 all_day_long = EXCLUDED.all_day_long,
167 flight = EXCLUDED.flight,
168 accommodation = EXCLUDED.accommodation,
169 ground_transport = EXCLUDED.ground_transport,
170 meal = EXCLUDED.meal,
171 additional_detail = EXCLUDED.additional_detail
172 RETURNING 1)
173 SELECT COUNT(*) > 0
174 FROM res;
175$$;
176
177CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_itinerary(filter_experience_id text, itinerary jsonb) returns boolean
178 security definer
179 language plpgsql
180as $$
181DECLARE
182 itinerary_id BIGINT;
183 description JSONB;
184 geo_location JSONB;
185BEGIN
186 IF ((SELECT COUNT(*) FROM experience_extranet_experience.experience_itinerary WHERE experience_itinerary_id = (itinerary->>'experience_itinerary_id')::BIGINT) > 0) THEN
187 itinerary_id := (itinerary->>'experience_itinerary_id')::BIGINT;
188 ELSE
189 itinerary_id := nextval('experience_extranet_experience.experience_itinerary_seq');
190 END IF;
191
192 PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary(itinerary_id, filter_experience_id, itinerary);
193
194 DELETE FROM experience_extranet_experience.experience_itinerary_description eid
195 WHERE NOT EXISTS(
196 SELECT *
197 FROM jsonb_array_elements(itinerary->'descriptions') AS elem
198 WHERE eid.itinerary_description_id = (elem->>'itinerary_description_id')::BIGINT
199 ) AND eid.experience_itinerary_id = itinerary_id;
200
201 FOR description IN SELECT jsonb_array_elements(itinerary->'descriptions') LOOP
202 PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_description(itinerary_id, description);
203 END LOOP;
204
205 DELETE FROM experience_extranet_experience.experience_itinerary_description eid
206 WHERE NOT EXISTS(
207 SELECT *
208 FROM jsonb_array_elements(itinerary->'descriptions') AS elem
209 WHERE eid.itinerary_description_id = (elem->>'itinerary_description_id')::BIGINT
210 ) AND eid.experience_itinerary_id = itinerary_id;
211
212 FOR description IN SELECT jsonb_array_elements(itinerary->'descriptions') LOOP
213 PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_description(itinerary_id, description);
214 END LOOP;
215
216 DELETE FROM experience_extranet_experience.experience_itinerary_geo_location eigl
217 WHERE NOT EXISTS(
218 SELECT *
219 FROM jsonb_array_elements(itinerary->'descriptions') AS elem
220 WHERE eigl.itinerary_geo_location_id = (elem->>'itinerary_geo_location_id')::BIGINT
221 ) AND eigl.experience_itinerary_id = itinerary_id;
222
223 FOR geo_location IN SELECT jsonb_array_elements(itinerary->'geo_locations') LOOP
224 PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_geo_location(itinerary_id, geo_location);
225 END LOOP;
226
227 RETURN FOUND;
228END;
229$$;
230
231
232CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_tour_additional_data(filter_experience_id text, additional_data_spec jsonb) returns boolean
233 security definer
234 language plpgsql
235as $$
236DECLARE
237 itinerary JSONB;
238BEGIN
239 INSERT INTO experience_extranet_experience.experience_tour (
240 experience_id,
241 pickup_available,
242 meeting_point_address,
243 meeting_point_geo_location,
244 duration_minute,
245 duration_hour,
246 duration_day,
247 all_day_long_experience,
248 additional_warning,
249 min_age,
250 max_age,
251 min_height,
252 max_height,
253 min_weight,
254 max_weight,
255 customer_risk_aggreement_needed
256 )
257 VALUES (
258 filter_experience_id,
259 (additional_data_spec->>'pickup_available')::BOOLEAN,
260 additional_data_spec->>'meeting_point_address',
261 (CASE WHEN additional_data_spec->'meeting_point_geo_location' IS NOT NULL
262 THEN row(additional_data_spec->'meeting_point_geo_location'->>'lat', additional_data_spec->'meeting_point_geo_location'->>'lon')::experience_extranet_experience.geo_location
263 ELSE NULL END),
264 (COALESCE(NULLIF(additional_data_spec->>'duration_minute', ''), '0'))::INTEGER,
265 (COALESCE(NULLIF(additional_data_spec->>'duration_hour', ''), '0'))::INTEGER,
266 (COALESCE(NULLIF(additional_data_spec->>'duration_day', ''), '0'))::INTEGER,
267 (additional_data_spec->>'all_day_long_experience')::BOOLEAN,
268 additional_data_spec->>'additional_warning',
269 (additional_data_spec->>'min_age')::INTEGER,
270 (additional_data_spec->>'max_age')::INTEGER,
271 (COALESCE(NULLIF(additional_data_spec->>'min_height', ''), '0'))::INTEGER,
272 (COALESCE(NULLIF(additional_data_spec->>'max_height', ''), '0'))::INTEGER,
273 (COALESCE(NULLIF(additional_data_spec->>'min_weight', ''), '0'))::INTEGER,
274 (COALESCE(NULLIF(additional_data_spec->>'max_weight', ''), '0'))::INTEGER,
275 (additional_data_spec->>'customer_risk_aggreement_needed')::BOOLEAN
276 )
277 ON CONFLICT (experience_id) DO UPDATE
278 SET pickup_available = EXCLUDED.pickup_available,
279 meeting_point_geo_location = (CASE WHEN additional_data_spec->'meeting_point_geo_location' IS NOT NULL
280 THEN row(additional_data_spec->'meeting_point_geo_location'->>'lat', additional_data_spec->'meeting_point_geo_location'->>'lon')::experience_extranet_experience.geo_location
281 ELSE NULL END),
282 duration_minute = EXCLUDED.duration_minute,
283 duration_hour = EXCLUDED.duration_hour,
284 duration_day = EXCLUDED.duration_day,
285 all_day_long_experience = EXCLUDED.all_day_long_experience,
286 additional_warning = EXCLUDED.additional_warning,
287 min_age = EXCLUDED.min_age,
288 max_age = EXCLUDED.max_age,
289 min_height = EXCLUDED.min_height,
290 max_height = EXCLUDED.max_height,
291 min_weight = EXCLUDED.min_weight,
292 max_weight = EXCLUDED.max_weight,
293 customer_risk_aggreement_needed = EXCLUDED.customer_risk_aggreement_needed,
294 meeting_point_address = EXCLUDED.meeting_point_address;
295
296 DELETE FROM experience_extranet_experience.experience_itinerary
297 WHERE NOT EXISTS (
298 SELECT experience_itinerary_id
299 FROM jsonb_array_elements(additional_data_spec->'itineraries') AS elem
300 WHERE experience_itinerary_id = (elem->>'experience_itinerary_id')::BIGINT
301 ) AND experience_id = filter_experience_id;
302
303 FOR itinerary IN SELECT jsonb_array_elements(additional_data_spec->'itineraries') LOOP
304 PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary(filter_experience_id, itinerary);
305 END LOOP;
306
307 IF (additional_data_spec ->> 'meeting_point' IS NULL OR (additional_data_spec ->> 'meeting_point_available') :: BOOLEAN IS FALSE) THEN
308 DELETE FROM experience_extranet_experience.experience_tour_meeting_point
309 WHERE experience_id = filter_experience_id;
310 ELSE
311 PERFORM experience_extranet_management_v2_helper.upsert_tour_meeting_point(filter_experience_id, additional_data_spec -> 'meeting_point');
312 END IF;
313
314 PERFORM experience_extranet_content_v2_helper.upsert_experience_tour_content(filter_experience_id, additional_data_spec);
315
316 RETURN FOUND;
317END;
318$$;
319
320
321CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_itineraries(filter_experience_id TEXT)
322 RETURNS SETOF JSONB
323LANGUAGE SQL
324AS $$
325 WITH
326 get_itinerary_description_result AS (
327 SELECT *
328 FROM experience_extranet_experience.experience_itinerary_description eid
329 WHERE EXISTS(
330 SELECT ei.experience_itinerary_id
331 FROM experience_extranet_experience.experience_itinerary ei
332 WHERE ei.experience_id = filter_experience_id
333 AND ei.experience_itinerary_id = eid.experience_itinerary_id
334 )
335 ),
336 get_itinerary_geo_location_result AS (
337 SELECT *
338 FROM experience_extranet_experience.experience_itinerary_geo_location eigl
339 WHERE EXISTS(
340 SELECT ei.experience_itinerary_id
341 FROM experience_extranet_experience.experience_itinerary ei
342 WHERE ei.experience_id = filter_experience_id
343 AND ei.experience_itinerary_id = eigl.experience_itinerary_id
344 )
345 )
346 SELECT to_jsonb(itinerary_result) AS result FROM (
347 SELECT
348 *,
349 (SELECT array_agg(res) AS descriptions
350 FROM
351 (SELECT
352 *,
353 (SELECT array_agg(img) as images
354 FROM
355 (SELECT *
356 FROM experience_extranet_experience.experience_itinerary_image iti
357 WHERE iti.itinerary_description_id = des.itinerary_description_id) img
358 )
359 FROM get_itinerary_description_result des
360 WHERE ei.experience_itinerary_id = des.experience_itinerary_id
361 ORDER BY des.time
362 ) res
363 ),
364 (SELECT array_agg(res) AS geo_locations
365 FROM
366 (SELECT *
367 FROM get_itinerary_geo_location_result geo
368 WHERE ei.experience_itinerary_id = geo.experience_itinerary_id
369 ORDER BY geo.itinerary_geo_location_id
370 ) res
371 )
372 FROM experience_extranet_experience.experience_itinerary ei
373 WHERE experience_id = filter_experience_id
374 ORDER BY ei.day
375 ) itinerary_result;
376$$;
377
378DROP FUNCTION IF EXISTS experience_extranet_management_v2_helper.get_experience_itinerary(TEXT);
379
380CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_itineraries_contents(filter_experience_id TEXT)
381 RETURNS JSONB
382LANGUAGE SQL
383AS $$
384SELECT jsonb_agg(res)
385FROM (SELECT ei.*
386 FROM experience_extranet_experience.experience_itinerary ei
387 WHERE experience_id = filter_experience_id
388 ORDER BY ei.day) res
389$$;
390
391CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_itineraries_descriptions_contents(filter_experience_id TEXT)
392 RETURNS JSONB
393LANGUAGE SQL
394AS $$
395SELECT jsonb_agg(res)
396FROM (SELECT ei.day, eid.time, eid.description
397 FROM experience_extranet_experience.experience_itinerary ei
398 JOIN experience_extranet_experience.experience_itinerary_description eid
399 ON ei.experience_itinerary_id = eid.experience_itinerary_id
400 WHERE experience_id = filter_experience_id
401 ORDER BY ei.day, eid.time) res
402$$;
403
404CREATE OR REPLACE FUNCTION experience_extranet_content_v2_api.get_contents_by_experience_id(experience_id text) returns jsonb
405 SECURITY DEFINER
406 LANGUAGE SQL
407as $$
408SELECT to_jsonb(result)
409 FROM (
410 SELECT
411 experience_extranet_content_v1_helper.get_themes_by_experience_id(experience_id) AS "themes",
412 experience_extranet_content_v1_helper.get_price_exclusion_by_experience_id(experience_id) AS "exclusion_prices",
413 experience_extranet_content_v1_helper.get_price_inclusion_by_experience_id(experience_id) AS "inclusion_prices",
414 experience_extranet_content_v1_helper.get_nearby_facilities_by_experience_id(experience_id) AS "nearby_facilities",
415 experience_extranet_content_v1_helper.get_facilities_by_experience_id(experience_id) AS "onsite_facilities",
416 experience_extranet_content_v1_helper.get_clothing_policies_by_experience_id(experience_id) AS "clothing_policies",
417 experience_extranet_content_v1_helper.get_additional_necessities_by_experience_id(experience_id) AS "additional_necessities",
418 experience_extranet_content_v1_helper.get_term_conditions_by_experience_id(experience_id) AS "term_conditions",
419 translate(
420 experience_extranet_content_v1_helper.get_service_languages_by_experience_id(experience_id) :: TEXT,
421 '\"',
422 ''
423 ) :: TEXT [] AS "service_language",
424 experience_extranet_content_v1_helper.get_attributes_by_experience_id(experience_id) AS "extranet_experience_attributes",
425 experience_extranet_management_v2_helper.get_experience_itineraries_contents(experience_id) AS "itineraries_base",
426 experience_extranet_management_v2_helper.get_experience_itineraries_descriptions_contents(experience_id) AS "itineraries",
427 experience_extranet_content_v2_helper.get_operational_day_content_by_experience_id(
428 experience_id) AS "operational_days",
429 experience_extranet_content_v2_helper.get_experience_tags_by_experience_id(experience_id) AS "tags",
430 experience_extranet_management_v2_helper.get_experience_sections_by_experience_id(experience_id) AS "sections",
431 experience_extranet_content_v2_helper.get_operational_period_content_by_experience_id(
432 experience_id) AS "operational_periods",
433 experience_extranet_content_v2_helper.get_public_transport_by_experience_id(experience_id) AS "public_transports",
434 experience_extranet_content_v2_helper.get_shuttle_transport_by_experience_id(experience_id) AS "shuttle_transport",
435 experience_extranet_content_v2_helper.get_event_schedule_by_experience_id(experience_id) AS "event_schedules",
436 experience_extranet_content_v2_helper.get_audio_guide_by_experience_id(experience_id) AS "audio_guide",
437 experience_extranet_content_v2_helper.get_guided_tour_by_experience_id(experience_id) AS "guided_tour",
438 experience_extranet_content_v2_helper.get_additional_content_data_by_experience_id(experience_id) AS "additional_data_content"
439 ) AS result
440$$;