· 5 years ago · Feb 06, 2020, 02:22 PM
1DO $$
2BEGIN
3 BEGIN
4 ALTER TABLE IF EXISTS experience_extranet_experience.experience
5 ADD COLUMN source TEXT NULL;
6 EXCEPTION
7 WHEN duplicate_column
8 THEN RAISE NOTICE 'column source already exists';
9 END;
10END;
11$$;
12
13DO $$
14BEGIN
15 BEGIN
16 ALTER TABLE IF EXISTS experience_extranet_experience.experience
17 ADD COLUMN default_locale TEXT NULL;
18 EXCEPTION
19 WHEN duplicate_column
20 THEN RAISE NOTICE 'column default_locale already exists';
21 END;
22END;
23$$;
24
25DO $$
26BEGIN
27 BEGIN
28 ALTER TABLE IF EXISTS experience_extranet_ticket.ticket
29 ADD COLUMN source TEXT NULL;
30 EXCEPTION
31 WHEN duplicate_column
32 THEN RAISE NOTICE 'column source already exists';
33 END;
34END;
35$$;
36
37DO $$
38BEGIN
39 BEGIN
40 ALTER TABLE IF EXISTS experience_extranet_ticket.ticket
41 ADD COLUMN default_locale TEXT NULL;
42 EXCEPTION
43 WHEN duplicate_column
44 THEN RAISE NOTICE 'column default_locale already exists';
45 END;
46END;
47$$;
48
49update experience_extranet_experience.experience set source = 'INTERNAL', default_locale = 'en_ID';
50
51UPDATE experience_extranet_ticket.ticket set source = 'INTERNAL', default_locale = 'en_ID';
52
53
54
55DO $$
56BEGIN
57 BEGIN
58 ALTER TABLE IF EXISTS experience_extranet_experience.experience
59 ALTER COLUMN source SET DEFAULT 'INTERNAL';
60 EXCEPTION
61 WHEN duplicate_column
62 THEN RAISE NOTICE 'column source already exists';
63 END;
64END;
65$$;
66
67DO $$
68BEGIN
69 BEGIN
70 ALTER TABLE IF EXISTS experience_extranet_experience.experience
71 ALTER COLUMN default_locale SET DEFAULT 'en_ID';
72 EXCEPTION
73 WHEN duplicate_column
74 THEN RAISE NOTICE 'column default_locale already exists';
75 END;
76END;
77$$;
78
79DO $$
80BEGIN
81 BEGIN
82 ALTER TABLE IF EXISTS experience_extranet_ticket.ticket
83 ALTER COLUMN source SET DEFAULT 'INTERNAL';
84 EXCEPTION
85 WHEN duplicate_column
86 THEN RAISE NOTICE 'column source already exists';
87 END;
88END;
89$$;
90
91DO $$
92BEGIN
93 BEGIN
94 ALTER TABLE IF EXISTS experience_extranet_ticket.ticket
95 ALTER COLUMN default_locale SET DEFAULT 'en_ID';
96 EXCEPTION
97 WHEN duplicate_column
98 THEN RAISE NOTICE 'column default_locale already exists';
99 END;
100END;
101$$;
102
103CREATE OR REPLACE FUNCTION experience_extranet_management_v2_api.get_all_experience_chain(filter_marketing_manager TEXT)
104 RETURNS SETOF JSONB
105SECURITY DEFINER
106LANGUAGE SQL
107AS $$
108SELECT to_jsonb(result)
109FROM (SELECT experience_id,
110 experience_name,
111 experience_category,
112 country,
113 province,
114 city,
115 supplier_id,
116 supplier_name,
117 marketing_manager_user_id,
118 active,
119 product_chain,
120 chain_reference_id,
121 deleted,
122 last_updated,
123 source
124 FROM experience_extranet_experience.experience ee
125 WHERE (filter_marketing_manager = 'ALL' OR marketing_manager_user_id = filter_marketing_manager)
126 AND ee.product_chain = TRUE
127 AND ee.chain_reference_id IS NULL
128 ORDER BY ee.experience_name) result;
129$$;
130
131
132CREATE OR REPLACE FUNCTION experience_extranet_account_v1_api.get_user_by_user_names(filter_user_names TEXT [])
133 RETURNS SETOF JSONB
134SECURITY DEFINER
135LANGUAGE PLPGSQL
136AS $$
137BEGIN
138 RETURN QUERY
139 WITH res AS (
140 SELECT *
141 FROM experience_extranet_account.user
142 WHERE experience_extranet_account.user.user_name = ANY (filter_user_names)
143 AND experience_extranet_account.user.active = TRUE
144 )
145 SELECT to_jsonb(res) FROM res;
146END;
147$$;
148
149CREATE OR REPLACE FUNCTION experience_extranet_management_v2_api.insert_extranet_experience(extranet_experience JSONB)
150 RETURNS TEXT
151SECURITY DEFINER
152LANGUAGE plpgsql
153AS $$
154DECLARE
155 var_experience_id TEXT;
156 contact JSONB;
157 pic JSONB;
158 theme JSONB;
159 asset JSONB;
160 exclusion_price JSONB;
161 inclusion_price JSONB;
162 nearby_facilities JSONB;
163 onsite_facilities JSONB;
164 clothing_policy JSONB;
165 additional_necessity JSONB;
166 term_condition JSONB;
167 service_language TEXT;
168 extranet_attribute JSONB;
169BEGIN
170 var_experience_id := concat(extranet_experience ->> 'supplier_id', '.EXP.',
171 nextval('experience_extranet_experience.experience_seq'));
172
173 INSERT INTO experience_extranet_experience.experience (
174 experience_id,
175 supplier_id,
176 supplier_name,
177 experience_name,
178 extranet_experience_type,
179 active,
180 pickup_available,
181 all_day_long_experience,
182 child_allowed,
183 elderly_allowed,
184 disabled_people_allowed,
185 required_customer_id,
186 customer_risk_aggreement_needed,
187 duration_minute,
188 voucher_type,
189 voucher_code_format,
190 city,
191 postal_code,
192 province,
193 country,
194 min_child_age,
195 max_child_age,
196 min_adult_age,
197 max_adult_age,
198 min_elderly_age,
199 max_elderly_age,
200 min_height,
201 max_height,
202 min_weight,
203 max_weight,
204 reservation,
205 reservation_day,
206 last_updated,
207 operation_days,
208 voucher_validity_policy,
209 geo_location,
210 meeting_point_geo_location,
211 last_redeem_time,
212 weekdays_open_time,
213 weekdays_close_time,
214 weekends_open_time,
215 weekends_close_time,
216 traveloka_recommend,
217 direct_entrance,
218 experience_category,
219 product_chain,
220 chain_reference_id,
221 marketing_manager_user_id,
222 product_owner,
223 tags,
224 pushed_to_aggregator,
225 source,
226 default_locale,
227 available_locales
228 )
229 VALUES (
230 var_experience_id,
231 (extranet_experience ->> 'supplier_id') :: BIGINT,
232 extranet_experience ->> 'supplier_name',
233 extranet_experience ->> 'experience_name',
234 extranet_experience ->> 'extranet_experience_type',
235 (extranet_experience ->> 'active') :: BOOLEAN,
236 (extranet_experience ->> 'pickup_available') :: BOOLEAN,
237 (extranet_experience ->> 'all_day_long_experience') :: BOOLEAN,
238 (extranet_experience ->> 'child_allowed') :: BOOLEAN,
239 (extranet_experience ->> 'elderly_allowed') :: BOOLEAN,
240 (extranet_experience ->> 'disabled_people_allowed') :: BOOLEAN,
241 (extranet_experience -> 'voucher_rule' ->> 'customer_id_required') :: BOOLEAN,
242 (extranet_experience -> 'voucher_rule' ->> 'customer_risk_aggreement_needed') :: BOOLEAN,
243 (extranet_experience ->> 'duration_minute') :: BIGINT,
244 extranet_experience -> 'voucher_rule' ->> 'voucher_type',
245 extranet_experience -> 'voucher_rule' ->> 'voucher_code_format',
246 extranet_experience ->> 'city',
247 extranet_experience ->> 'postal_code',
248 extranet_experience ->> 'province',
249 extranet_experience ->> 'country',
250 (extranet_experience ->> 'min_child_age') :: INTEGER,
251 (extranet_experience ->> 'max_child_age') :: INTEGER,
252 (extranet_experience ->> 'min_adult_age') :: INTEGER,
253 (extranet_experience ->> 'max_adult_age') :: INTEGER,
254 (extranet_experience ->> 'min_elderly_age') :: INTEGER,
255 (extranet_experience ->> 'max_elderly_age') :: INTEGER,
256 (extranet_experience ->> 'min_height') :: INTEGER,
257 (extranet_experience ->> 'max_height') :: INTEGER,
258 (extranet_experience ->> 'min_weight') :: INTEGER,
259 (extranet_experience ->> 'max_weight') :: INTEGER,
260 extranet_experience ->> 'reservation',
261 (extranet_experience ->> 'reservation_day') :: INTEGER,
262 (extranet_experience ->> 'last_updated') :: TIMESTAMPTZ,
263 translate(extranet_experience ->> 'operation_days', '[]', '{}') :: TEXT [],
264 ROW (
265 extranet_experience -> 'voucher_validity_policy' ->> 'validity_days',
266 extranet_experience -> 'voucher_validity_policy' ->> 'validity_type',
267 extranet_experience -> 'voucher_validity_policy' ->> 'validity_period_from',
268 extranet_experience -> 'voucher_validity_policy' ->> 'validity_period_to'
269 ) :: experience_extranet_experience.VOUCHER_VALIDITY_POLICY,
270 ROW (extranet_experience -> 'geo_location' ->> 'lat', extranet_experience -> 'geo_location' ->>
271 'lon') :: experience_extranet_experience.GEO_LOCATION,
272 (CASE WHEN extranet_experience -> 'meeting_point_geo_location' IS NOT NULL
273 THEN ROW (extranet_experience -> 'meeting_point_geo_location' ->> 'lat',
274 extranet_experience -> 'meeting_point_geo_location' ->> 'lon') :: experience_extranet_experience.GEO_LOCATION
275 ELSE NULL END),
276 ROW (extranet_experience -> 'voucher_rule' -> 'last_redeem_time' ->> 'hour',
277 extranet_experience -> 'voucher_rule' -> 'last_redeem_time' ->>
278 'minute') :: experience_extranet_experience.HOUR_MINUTE,
279 ROW (extranet_experience -> 'weekdays_open_time' ->> 'hour', extranet_experience -> 'weekdays_open_time' ->>
280 'minute') :: experience_extranet_experience.HOUR_MINUTE,
281 ROW (extranet_experience -> 'weekdays_close_time' ->> 'hour', extranet_experience -> 'weekdays_close_time' ->>
282 'minute') :: experience_extranet_experience.HOUR_MINUTE,
283 ROW (extranet_experience -> 'weekends_open_time' ->> 'hour', extranet_experience -> 'weekends_open_time' ->>
284 'minute') :: experience_extranet_experience.HOUR_MINUTE,
285 ROW (extranet_experience -> 'weekends_close_time' ->> 'hour', extranet_experience -> 'weekends_close_time' ->>
286 'minute') :: experience_extranet_experience.HOUR_MINUTE,
287 (extranet_experience ->> 'traveloka_recommend') :: BOOLEAN,
288 (extranet_experience -> 'voucher_rule' ->> 'direct_entrance') :: BOOLEAN,
289 extranet_experience ->> 'experience_category',
290 (extranet_experience ->> 'product_chain') :: BOOLEAN,
291 extranet_experience ->> 'chain_reference_id',
292 extranet_experience ->> 'marketing_manager_user_id',
293 extranet_experience ->> 'product_owner',
294 translate(extranet_experience->>'tags', '[]', '{}')::TEXT[],
295 COALESCE((extranet_experience ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE),
296 extranet_experience ->> 'source',
297 extranet_experience ->> 'default_locale',
298 translate(COALESCE(extranet_experience ->> 'available_locales', '[en_ID]'), '[]', '{}') :: TEXT []
299 );
300
301 FOR contact IN SELECT jsonb_array_elements(extranet_experience -> 'contacts') LOOP
302 PERFORM experience_extranet_management_v1_helper.insert_experience_contact(var_experience_id, contact);
303 END LOOP;
304
305 FOR pic IN SELECT jsonb_array_elements(extranet_experience -> 'pics') LOOP
306 PERFORM experience_extranet_management_v1_helper.insert_experience_pic(var_experience_id, pic);
307 END LOOP;
308
309 FOR theme IN SELECT jsonb_array_elements(extranet_experience -> 'themes') LOOP
310 PERFORM experience_extranet_content_v1_api.insert_experience_theme(var_experience_id, theme);
311 END LOOP;
312
313 FOR exclusion_price IN SELECT jsonb_array_elements(extranet_experience -> 'exclusion_prices') LOOP
314 PERFORM experience_extranet_content_v1_api.insert_experience_price_exclusion(var_experience_id, exclusion_price);
315 END LOOP;
316
317 FOR inclusion_price IN SELECT jsonb_array_elements(extranet_experience -> 'inclusion_prices') LOOP
318 PERFORM experience_extranet_content_v1_api.insert_experience_price_inclusion(var_experience_id, inclusion_price);
319 END LOOP;
320
321 FOR nearby_facilities IN SELECT jsonb_array_elements(extranet_experience -> 'nearby_facilities') LOOP
322 PERFORM experience_extranet_content_v1_api.insert_experience_nearby_facility(var_experience_id, nearby_facilities);
323 END LOOP;
324
325 FOR onsite_facilities IN SELECT jsonb_array_elements(extranet_experience -> 'onsite_facilities') LOOP
326 PERFORM experience_extranet_content_v1_api.insert_experience_facility(var_experience_id, onsite_facilities);
327 END LOOP;
328
329 FOR clothing_policy IN SELECT jsonb_array_elements(extranet_experience -> 'clothing_policies') LOOP
330 PERFORM experience_extranet_content_v1_api.insert_experience_clothing_policy(var_experience_id, clothing_policy);
331 END LOOP;
332
333 FOR additional_necessity IN SELECT jsonb_array_elements(extranet_experience -> 'additional_necessities') LOOP
334 PERFORM
335 experience_extranet_content_v1_api.insert_experience_additional_necessity(var_experience_id, additional_necessity);
336 END LOOP;
337
338 FOR service_language IN SELECT jsonb_array_elements(extranet_experience -> 'service_language') LOOP
339 PERFORM experience_extranet_content_v1_api.insert_experience_service_language(var_experience_id, service_language);
340 END LOOP;
341
342 FOR term_condition IN SELECT jsonb_array_elements(extranet_experience -> 'term_conditions') LOOP
343 PERFORM experience_extranet_content_v1_api.insert_experience_term_condition(var_experience_id, term_condition);
344 END LOOP;
345
346 FOR extranet_attribute IN SELECT jsonb_array_elements(extranet_experience -> 'extranet_experience_attributes') LOOP
347 PERFORM experience_extranet_content_v1_api.upsert_experience_attribute(var_experience_id, extranet_attribute);
348 END LOOP;
349
350 PERFORM experience_extranet_content_v2_api.upsert_operational_day(var_experience_id, extranet_experience);
351
352 PERFORM
353 experience_extranet_management_v2_helper.upsert_experience_additional_data(var_experience_id, extranet_experience);
354
355 PERFORM experience_extranet_management_v2_api.upsert_experience_section_list(var_experience_id,
356 COALESCE((extranet_experience ->> 'sections')::JSONB, '[]'::JSONB));
357
358 IF (jsonb_typeof(extranet_experience -> 'operational_periods') = 'array') THEN
359 PERFORM experience_extranet_content_v2_api.upsert_operational_period(var_experience_id, extranet_experience);
360 END IF;
361
362 IF (jsonb_typeof(extranet_experience -> 'public_transports') = 'array') THEN
363 PERFORM experience_extranet_content_v2_api.upsert_public_transport(var_experience_id, extranet_experience);
364 END IF;
365
366 IF (extranet_experience ->> 'shuttle_transport' IS NULL) THEN
367 DELETE FROM experience_extranet_content.experience_shuttle_transport st
368 WHERE st.experience_id = var_experience_id;
369 ELSE
370 PERFORM experience_extranet_content_v2_helper.upsert_shuttle_transport(var_experience_id, extranet_experience -> 'shuttle_transport');
371 END IF;
372
373 RETURN var_experience_id;
374END;
375$$
376;
377
378CREATE INDEX IF NOT EXISTS experience_extranet_account__lower_user_name__idx
379 ON experience_extranet_account.user (lower(user_name));
380
381
382CREATE OR REPLACE FUNCTION experience_extranet_content_v2_api.get_experience_default_locale(experience_id_spec TEXT)
383 RETURNS TEXT
384SECURITY DEFINER
385LANGUAGE plpgsql
386AS $$
387DECLARE
388 result TEXT;
389BEGIN
390 SELECT default_locale INTO result FROM experience_extranet_experience.experience WHERE experience_id = experience_id_spec;
391 RETURN result;
392END;
393$$;
394
395CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_multi_day_tours
396(
397 experience_id TEXT NOT NULL PRIMARY KEY,
398 _lut TIMESTAMPTZ,
399 FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
400);
401
402DO $$
403BEGIN
404 CREATE TRIGGER populate_lut_experience_multi_day_tours
405 BEFORE INSERT OR UPDATE ON experience_extranet_experience.experience_multi_day_tours
406 FOR EACH ROW
407 EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
408EXCEPTION
409 WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
410END;
411$$;
412
413CREATE TABLE IF NOT EXISTS experience_extranet_partner_mapping.ticket_mapping
414(
415 ticket_id TEXT,
416 partner_id TEXT,
417 partner_data JSONB,
418 "_lut" TIMESTAMP WITH TIME ZONE,
419 CONSTRAINT ticket_mapping_pk
420 UNIQUE (ticket_id, partner_id)
421);
422
423CREATE INDEX IF NOT EXISTS ticket_mapping__lut_index
424 ON experience_extranet_partner_mapping.ticket_mapping ("_lut");
425
426DO $$
427BEGIN
428 CREATE TRIGGER populate_lut_partner_ticket_mapping
429 BEFORE INSERT OR UPDATE
430 ON experience_extranet_partner_mapping.ticket_mapping
431 FOR EACH ROW
432 EXECUTE PROCEDURE experience_extranet_partner_mapping.lut_partner_mapping();
433EXCEPTION
434 WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
435END;
436$$;
437
438
439CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_multi_day_tours_travel_agent
440(
441 travel_agent_id BIGSERIAL NOT NULL PRIMARY KEY,
442 experience_id TEXT,
443 travel_agent_name TEXT,
444 license_number TEXT,
445 _lut TIMESTAMPTZ,
446 FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
447);
448
449DO $$
450BEGIN
451 CREATE TRIGGER populate_lut_experience_multi_day_tours_travel_agent
452 BEFORE INSERT OR UPDATE
453 ON experience_extranet_experience.experience_multi_day_tours_travel_agent
454 FOR EACH ROW
455 EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
456 EXCEPTION
457 WHEN duplicate_object
458 THEN RAISE NOTICE 'the trigger already exists';
459END;
460$$;
461
462
463CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_multi_day_tours_travel_agent_by_experience_id(experience_id_spec text) returns jsonb
464 security definer
465 language sql
466as $$
467SELECT jsonb_agg(to_jsonb(result))
468FROM (
469 SELECT *
470 FROM experience_extranet_experience.experience_multi_day_tours_travel_agent
471 WHERE experience_id = experience_id_spec
472 ) AS result
473$$;
474
475CREATE OR REPLACE FUNCTION experience_extranet_partner_mapping_v1_api.get_partner_ticket_mapping(partner_id_spec TEXT,
476 ticket_id_spec TEXT)
477 RETURNS JSONB
478SECURITY DEFINER
479LANGUAGE PLPGSQL
480AS $$
481DECLARE
482 result JSONB;
483BEGIN
484 SELECT to_jsonb(res.*) INTO result
485 FROM (SELECT *
486 FROM experience_extranet_partner_mapping.ticket_mapping tm
487 WHERE tm.ticket_id = ticket_id_spec
488 AND tm.partner_id = partner_id_spec) res;
489
490 RETURN result;
491END;
492$$;
493
494
495CREATE OR REPLACE FUNCTION experience_extranet_partner_mapping_v1_api.upsert_partner_ticket_mapping(ticket_mapping_spec JSONB)
496 RETURNS BOOLEAN
497SECURITY DEFINER
498LANGUAGE PLPGSQL
499AS $$
500DECLARE
501 result BOOLEAN;
502BEGIN
503 WITH upsert_ticket_mapping AS (
504 INSERT INTO experience_extranet_partner_mapping.ticket_mapping (
505 ticket_id,
506 partner_id,
507 partner_data
508 )
509 VALUES
510 (
511 ticket_mapping_spec ->> 'ticket_id',
512 ticket_mapping_spec ->> 'partner_id',
513 ticket_mapping_spec -> 'partner_data'
514 )
515 ON CONFLICT (partner_id, ticket_id)
516 DO UPDATE
517 SET partner_data = EXCLUDED.partner_data
518 RETURNING *
519 ) SELECT count(*) > 0 into result FROM upsert_ticket_mapping;
520
521 RETURN result;
522END;
523$$;
524
525CREATE OR REPLACE FUNCTION experience_extranet_partner_mapping_v1_api.get_product_mapping_by_partner_id(partner_id_spec TEXT,
526 page_spec JSONB,
527 sort_spec JSONB)
528 RETURNS SETOF JSONB
529SECURITY DEFINER
530LANGUAGE plpgsql
531AS $$
532BEGIN
533 RETURN QUERY EXECUTE FORMAT('
534 WITH res AS (
535 SELECT *
536 FROM
537 experience_extranet_partner_mapping.product_mapping
538 WHERE
539 experience_extranet_partner_mapping.product_mapping.partner_id = $1
540 ORDER BY
541 CASE WHEN %L = %L THEN %I END DESC,
542 CASE WHEN %L = %L THEN %I END ASC
543 LIMIT %L
544 OFFSET %L
545 )
546 SELECT to_jsonb(res) from res;',
547 sort_spec ->> 'sort_status', 'DESC', sort_spec ->> 'sort_field',
548 sort_spec ->> 'sort_status', 'ASC', sort_spec ->> 'sort_field',
549 page_spec ->> 'item_per_page', ((page_spec ->> 'current_page') :: INTEGER - 1) *
550 (page_spec ->> 'item_per_page') :: INTEGER
551 )
552 USING partner_id_spec;
553END;
554$$;
555
556CREATE OR REPLACE FUNCTION experience_extranet_partner_mapping_v1_api.delete_partner_ticket_mapping(partner_id_spec TEXT, ticket_id_spec TEXT)
557 RETURNS BOOLEAN
558SECURITY DEFINER
559LANGUAGE PLPGSQL
560AS $$
561DECLARE
562 result BOOLEAN;
563BEGIN
564 WITH delete_ticket_mapping AS (
565 DELETE
566 FROM
567 experience_extranet_partner_mapping.ticket_mapping
568 WHERE
569 partner_id = partner_id_spec AND ticket_id = ticket_id_spec
570 RETURNING *
571 )
572 SELECT count(*) > 0 INTO result
573 FROM delete_ticket_mapping;
574
575 RETURN result;
576END;
577$$;
578
579
580CREATE OR REPLACE FUNCTION experience_extranet_management_v2_api.get_experience_category(filter_spec JSONB)
581 RETURNS SETOF JSONB
582SECURITY DEFINER
583LANGUAGE plpgsql
584AS $$
585BEGIN
586 RETURN QUERY
587 WITH res AS (
588 SELECT experience_id, experience_category FROM experience_extranet_experience.experience
589 OFFSET (((filter_spec ->> 'current_page') :: INTEGER - 1) * (filter_spec ->> 'item_per_page') :: INTEGER) ROWS
590 LIMIT (filter_spec ->> 'item_per_page') :: INTEGER
591 )
592 SELECT to_jsonb(res.*) FROM res;
593END;
594$$;