Guest User

Untitled

a guest
Dec 9th, 2019
116
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category, mapping_table_name, mapping_upsert_function_name)
  2. VALUES ('UMRAH',
  3. 'experience_extranet_ticket.ticket_umrah',
  4. 'experience_extranet_ticket_v2_helper.upsert_ticket_umrah_additional_data($1, $2)')
  5. ON CONFLICT (experience_category) DO UPDATE
  6. SET
  7. mapping_table_name = EXCLUDED.mapping_table_name,
  8. mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
  9.  
  10. INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category, mapping_table_name, mapping_upsert_function_name)
  11. VALUES ('LEISURE',
  12. 'experience_extranet_ticket.ticket_leisure',
  13. 'experience_extranet_ticket_v2_helper.upsert_ticket_leisure_additional_data($1, $2)')
  14. ON CONFLICT (experience_category) DO UPDATE
  15. SET
  16. mapping_table_name = EXCLUDED.mapping_table_name,
  17. mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
  18.  
  19. CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_umrah
  20. (
  21. ticket_id TEXT NOT NULL PRIMARY KEY
  22. CONSTRAINT ticket_umrah_ticket_id_fkey
  23. REFERENCES experience_extranet_ticket.ticket
  24. ON UPDATE CASCADE ON DELETE RESTRICT,
  25. "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
  26. );
  27.  
  28. CREATE INDEX IF NOT EXISTS experience_extranet_ticket_umrah__lut__idx
  29. ON experience_extranet_ticket.ticket_umrah ("_lut");
  30.  
  31. DO $$
  32. BEGIN
  33. CREATE TRIGGER populate_lut_ticket_umrah
  34. BEFORE INSERT OR UPDATE
  35. ON experience_extranet_ticket.ticket_umrah
  36. FOR EACH ROW
  37. EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
  38. EXCEPTION
  39. WHEN duplicate_object
  40. THEN RAISE NOTICE 'the trigger already exists';
  41. END;
  42. $$;
  43.  
  44. CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_leisure
  45. (
  46. ticket_id TEXT NOT NULL PRIMARY KEY
  47. CONSTRAINT ticket_leisure_ticket_id_fkey
  48. REFERENCES experience_extranet_ticket.ticket
  49. ON UPDATE CASCADE ON DELETE RESTRICT,
  50. "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
  51. );
  52.  
  53. CREATE INDEX IF NOT EXISTS experience_extranet_ticket_leisure__lut__idx
  54. ON experience_extranet_ticket.ticket_leisure ("_lut");
  55.  
  56. DO $$
  57. BEGIN
  58. CREATE TRIGGER populate_lut_ticket_leisure
  59. BEFORE INSERT OR UPDATE
  60. ON experience_extranet_ticket.ticket_leisure
  61. FOR EACH ROW
  62. EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
  63. EXCEPTION
  64. WHEN duplicate_object
  65. THEN RAISE NOTICE 'the trigger already exists';
  66. END;
  67. $$;
  68.  
  69.  
  70. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_hotels_by_experience_id(experience_id_spec text) returns jsonb
  71. security definer
  72. language sql
  73. as $$
  74. SELECT jsonb_agg(to_jsonb(result))
  75. FROM (
  76. SELECT *
  77. FROM experience_extranet_experience.experience_umrah_hotel
  78. WHERE experience_id = experience_id_spec
  79. ) AS result
  80. $$;
  81.  
  82. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_flights_by_experience_id(experience_id_spec text) returns jsonb
  83. security definer
  84. language sql
  85. as $$
  86. SELECT jsonb_agg(to_jsonb(result))
  87. FROM (
  88. SELECT *
  89. FROM experience_extranet_experience.experience_umrah_flight
  90. WHERE experience_id = experience_id_spec
  91. ) AS result
  92. $$;
  93.  
  94. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_travel_agent_by_experience_id(experience_id_spec text) returns jsonb
  95. security definer
  96. language sql
  97. as $$
  98. SELECT jsonb_agg(to_jsonb(result))
  99. FROM (
  100. SELECT *
  101. FROM experience_extranet_experience.experience_umrah_travel_agent
  102. WHERE experience_id = experience_id_spec
  103. ) AS result
  104. $$;
  105.  
  106. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_additional_data(
  107. filter_experience_id TEXT)
  108. RETURNS JSONB
  109. SECURITY DEFINER
  110. LANGUAGE SQL
  111. AS $$
  112. WITH get_umrah_result AS (SELECT *
  113. FROM experience_extranet_experience.experience_umrah
  114. WHERE experience_id = filter_experience_id),
  115. get_travel_agent_result AS (SELECT to_jsonb(result)
  116. FROM (SELECT *
  117. FROM experience_extranet_experience.experience_umrah_travel_agent
  118. WHERE experience_id = filter_experience_id) result),
  119. get_hotels_result AS (SELECT jsonb_agg(to_jsonb(result))
  120. FROM (SELECT *
  121. FROM experience_extranet_experience.experience_umrah_hotel
  122. WHERE experience_id = filter_experience_id) result),
  123. get_flights_result AS (SELECT jsonb_agg(to_jsonb(result))
  124. FROM (SELECT *
  125. FROM experience_extranet_experience.experience_umrah_flight
  126. WHERE experience_id = filter_experience_id) result),
  127. get_itinerary_description_result AS (SELECT *
  128. FROM experience_extranet_experience.experience_itinerary_description eid
  129. WHERE EXISTS(
  130. SELECT ei.experience_itinerary_id
  131. FROM experience_extranet_experience.experience_itinerary ei
  132. WHERE ei.experience_id = filter_experience_id
  133. AND ei.experience_itinerary_id = eid.experience_itinerary_id
  134. )),
  135. get_itinerary_result AS (SELECT array_agg(itinerary_result) AS result
  136. FROM (SELECT *, (SELECT array_agg(res) AS descriptions
  137. FROM (SELECT *, (SELECT array_agg(img) AS images
  138. FROM (SELECT *
  139. FROM experience_extranet_experience.experience_itinerary_image iti
  140. WHERE iti.itinerary_description_id = des.itinerary_description_id) img)
  141. FROM get_itinerary_description_result des
  142. WHERE ei.experience_itinerary_id = des.experience_itinerary_id
  143. ORDER BY des.time) res)
  144. FROM experience_extranet_experience.experience_itinerary ei
  145. WHERE experience_id = filter_experience_id
  146. ORDER BY ei.day) itinerary_result),
  147. get_tour_meeting_point_result AS (SELECT to_jsonb(result)
  148. FROM (SELECT *
  149. FROM experience_extranet_experience.experience_tour_meeting_point mp
  150. WHERE mp.experience_id = filter_experience_id) AS result),
  151. get_tour_content_result AS (SELECT *
  152. FROM experience_extranet_content.experience_tour_content
  153. WHERE experience_id = filter_experience_id)
  154. SELECT jsonb_build_object(
  155. 'duration_day', (SELECT duration_day FROM get_umrah_result),
  156. 'all_day_long', (SELECT all_day_long FROM get_umrah_result),
  157. 'travel_agent', (SELECT * FROM get_travel_agent_result),
  158. 'flights', (SELECT * FROM get_flights_result),
  159. 'hotels', (SELECT * FROM get_hotels_result),
  160. 'itineraries', (SELECT * FROM get_itinerary_result),
  161. 'pickup_price', (SELECT pickup_price FROM get_tour_content_result),
  162. 'pickup_address', (SELECT pickup_address FROM get_tour_content_result),
  163. 'drop_off_point', (SELECT drop_off_point FROM get_tour_content_result),
  164. 'meeting_point_available', (SELECT CASE WHEN get_tour_meeting_point_result IS NULL THEN FALSE ELSE TRUE END
  165. FROM get_tour_meeting_point_result),
  166. 'meeting_point', (SELECT * FROM get_tour_meeting_point_result)
  167. );
  168. $$;
  169.  
  170.  
  171. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_umrah_additional_data(filter_experience_id text, additional_data_spec jsonb) returns boolean
  172. security definer
  173. language plpgsql
  174. as $$
  175. DECLARE
  176. travel_agent_id_spec BIGINT;
  177. itinerary_id BIGINT;
  178. itinerary JSONB;
  179. description JSONB;
  180. BEGIN
  181. INSERT INTO experience_extranet_experience.experience_umrah (experience_id, duration_day, all_day_long)
  182. VALUES (
  183. filter_experience_id,
  184. (COALESCE(NULLIF(additional_data_spec->>'duration_day', ''), '0'))::INTEGER,
  185. (COALESCE(NULLIF(additional_data_spec->>'all_day_long', ''), 'FALSE')) :: BOOLEAN
  186. )
  187. ON CONFLICT (experience_id) DO UPDATE
  188. SET
  189. duration_day = EXCLUDED.duration_day,
  190. all_day_long = EXCLUDED.all_day_long;
  191.  
  192. travel_agent_id_spec := additional_data_spec -> 'travel_agent' ->> 'travel_agent_id';
  193. IF (travel_agent_id_spec = 0)
  194. THEN
  195. travel_agent_id_spec := nextval('experience_extranet_experience.experience_umrah_travel_agent_travel_agent_id_seq');
  196. END IF;
  197.  
  198. INSERT INTO experience_extranet_experience.experience_umrah_travel_agent (travel_agent_id, experience_id, travel_agent_name, license_number)
  199. VALUES (
  200. travel_agent_id_spec,
  201. filter_experience_id,
  202. additional_data_spec->'travel_agent'->>'travel_agent_name',
  203. additional_data_spec->'travel_agent'->>'license_number'
  204. )
  205. ON CONFLICT (travel_agent_id) DO UPDATE
  206. SET
  207. experience_id = EXCLUDED.experience_id,
  208. travel_agent_name = EXCLUDED.travel_agent_name,
  209. license_number = EXCLUDED.license_number;
  210.  
  211. DELETE
  212. FROM experience_extranet_experience.experience_umrah_hotel
  213. WHERE hotel_id NOT IN
  214. (SELECT hotel_id FROM jsonb_array_elements(additional_data_spec->'hotels') AS elem
  215. WHERE hotel_id = (elem->>'hotel_id') :: BIGINT) AND experience_id = filter_experience_id;
  216.  
  217. INSERT INTO experience_extranet_experience.experience_umrah_hotel (hotel_id, experience_id, country, city, rating, name, photos, distance_from_main_mosque)
  218. SELECT
  219. (hotel ->> 'hotel_id') :: BIGINT,
  220. filter_experience_id,
  221. hotel ->> 'country',
  222. hotel ->> 'city',
  223. (hotel ->> 'rating') :: INTEGER,
  224. hotel ->> 'name',
  225. hotel -> 'photos',
  226. hotel ->> 'distance_from_main_mosque'
  227. FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
  228. WHERE (hotel ->> 'hotel_id') :: BIGINT <> 0
  229. ON CONFLICT (hotel_id) DO UPDATE
  230. SET country = EXCLUDED.country,
  231. city = EXCLUDED.city,
  232. name = EXCLUDED.name,
  233. photos = EXCLUDED.photos,
  234. distance_from_main_mosque = EXCLUDED.distance_from_main_mosque;
  235.  
  236. INSERT INTO experience_extranet_experience.experience_umrah_hotel (experience_id, country, city, rating, name, photos, distance_from_main_mosque)
  237. SELECT
  238. filter_experience_id,
  239. hotel ->> 'country',
  240. hotel ->> 'city',
  241. (hotel ->> 'rating') :: INTEGER,
  242. hotel ->> 'name',
  243. hotel -> 'photos',
  244. hotel ->> 'distance_from_main_mosque'
  245. FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
  246. WHERE (hotel ->> 'hotel_id') :: BIGINT = 0;
  247.  
  248.  
  249. DELETE
  250. FROM experience_extranet_experience.experience_umrah_flight
  251. WHERE flight_id NOT IN
  252. (SELECT flight_id
  253. FROM jsonb_array_elements(additional_data_spec->'flights') AS elem
  254. WHERE flight_id = (elem->>'flight_id') :: BIGINT) AND experience_id = filter_experience_id;
  255.  
  256. INSERT INTO experience_extranet_experience.experience_umrah_flight (experience_id, flight_order, airline_brand, port)
  257. SELECT filter_experience_id,
  258. (flight ->> 'flight_order') :: INTEGER,
  259. flight ->> 'airline_brand',
  260. flight ->> 'port'
  261. FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
  262. WHERE (flight ->> 'flight_id') :: BIGINT = 0;
  263.  
  264. INSERT INTO experience_extranet_experience.experience_umrah_flight (flight_id, experience_id, flight_order, airline_brand, port)
  265. SELECT (flight ->> 'flight_id') :: BIGINT,
  266. filter_experience_id,
  267. (flight ->> 'flight_order') :: INTEGER,
  268. flight ->> 'airline_brand',
  269. flight ->> 'port'
  270. FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
  271. WHERE (flight ->> 'flight_id') :: BIGINT <> 0
  272. ON CONFLICT (flight_id) DO UPDATE
  273. SET flight_order = EXCLUDED.flight_order, airline_brand = EXCLUDED.airline_brand, port = EXCLUDED.port;
  274.  
  275. DELETE FROM experience_extranet_experience.experience_itinerary
  276. WHERE NOT EXISTS (
  277. SELECT experience_itinerary_id
  278. FROM jsonb_array_elements(additional_data_spec->'itineraries') AS elem
  279. WHERE experience_itinerary_id = (elem->>'experience_itinerary_id')::BIGINT
  280. ) AND experience_id = filter_experience_id;
  281.  
  282. FOR itinerary IN SELECT jsonb_array_elements(additional_data_spec->'itineraries') LOOP
  283. IF ((SELECT COUNT(*) FROM experience_extranet_experience.experience_itinerary WHERE experience_itinerary_id = (itinerary->>'experience_itinerary_id')::BIGINT) > 0)THEN
  284. itinerary_id := (itinerary->>'experience_itinerary_id')::BIGINT;
  285. ELSE
  286. itinerary_id := nextval('experience_extranet_experience.experience_itinerary_seq');
  287. END IF;
  288.  
  289. PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary(itinerary_id, filter_experience_id, itinerary);
  290.  
  291. DELETE FROM experience_extranet_experience.experience_itinerary_description eid
  292. WHERE NOT EXISTS(
  293. SELECT *
  294. FROM jsonb_array_elements(itinerary->'descriptions') AS elem
  295. WHERE eid.itinerary_description_id = (elem->>'itinerary_description_id')::BIGINT
  296. ) AND eid.experience_itinerary_id = itinerary_id;
  297.  
  298. FOR description IN SELECT jsonb_array_elements(itinerary->'descriptions') LOOP
  299. PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_description(itinerary_id, description);
  300. END LOOP;
  301.  
  302. END LOOP;
  303.  
  304. IF (additional_data_spec ->> 'meeting_point' IS NULL OR (additional_data_spec ->> 'meeting_point_available') :: BOOLEAN IS FALSE) THEN
  305. DELETE FROM experience_extranet_experience.experience_tour_meeting_point
  306. WHERE experience_id = filter_experience_id;
  307. ELSE
  308. PERFORM experience_extranet_management_v2_helper.upsert_tour_meeting_point(filter_experience_id, additional_data_spec -> 'meeting_point');
  309. END IF;
  310.  
  311. PERFORM experience_extranet_content_v2_helper.upsert_experience_tour_content(filter_experience_id, additional_data_spec);
  312.  
  313. RETURN FOUND;
  314. END;
  315. $$;
  316.  
  317. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_leisure_additional_data(filter_experience_id TEXT)
  318. RETURNS JSONB
  319. SECURITY DEFINER
  320. LANGUAGE SQL
  321. AS $$
  322. WITH get_leisure_result AS (
  323. SELECT * FROM experience_extranet_experience.experience_leisure WHERE experience_id = filter_experience_id
  324. )
  325. SELECT jsonb_build_object('experience_id',
  326. (SELECT experience_id FROM get_leisure_result)
  327. );
  328. $$;
  329.  
  330.  
  331. CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_leisure_additional_data(
  332. filter_experience_id TEXT, additional_data_spec JSONB)
  333. RETURNS BOOLEAN
  334. SECURITY DEFINER
  335. LANGUAGE plpgsql
  336. AS $$
  337. BEGIN
  338. INSERT INTO experience_extranet_experience.experience_leisure (experience_id) VALUES (filter_experience_id)
  339. ON CONFLICT (experience_id) DO NOTHING;
  340.  
  341. RETURN FOUND;
  342. END;
  343. $$;
  344.  
  345.  
  346.  
  347. DO $$
  348. BEGIN
  349. ALTER TABLE IF EXISTS experience_extranet_ticket.ticket_refund_policy
  350. ADD COLUMN num_of_days_prior_to INTEGER;
  351. EXCEPTION
  352. WHEN duplicate_column THEN RAISE NOTICE 'column num_of_days_prior_to already exists';
  353. END;
  354. $$;
  355.  
  356. CREATE OR REPLACE FUNCTION experience_extranet_ticket_v1_helper.get_ticket_refund_policy(filter_ticket_id TEXT)
  357. RETURNS JSONB
  358. SECURITY DEFINER
  359. LANGUAGE SQL
  360. AS $$
  361. SELECT to_jsonb(array_agg(res))
  362. FROM (SELECT *, jsonb_build_object('days_from', trp.num_of_days_prior,
  363. 'days_to', COALESCE(trp.num_of_days_prior_to, trp.num_of_days_prior)) AS num_of_days_range_prior
  364. FROM experience_extranet_ticket.ticket_refund_policy trp
  365. WHERE trp.ticket_id = filter_ticket_id
  366. AND trp.active = TRUE
  367. ORDER BY trp.num_of_days_prior) AS res;
  368. $$;
  369.  
  370.  
  371. CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec JSONB)
  372. RETURNS TEXT
  373. SECURITY DEFINER
  374. LANGUAGE plpgsql
  375. AS $$
  376. DECLARE
  377. policy jsonb;
  378. time_slot jsonb;
  379. exp_ticket_id TEXT;
  380. group_list jsonb;
  381. group_detail_list jsonb;
  382. redeemable jsonb;
  383. BEGIN
  384. exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
  385. nextval('experience_extranet_ticket.experience_ticket_seq'));
  386.  
  387. INSERT INTO experience_extranet_ticket.ticket (
  388. ticket_id,
  389. experience_id,
  390. ticket_type,
  391. ticket_barcode_type,
  392. ticket_name,
  393. ticket_description,
  394. maximum_adult_size,
  395. minimum_adult_size,
  396. maximum_child_size,
  397. minimum_child_size,
  398. group_size,
  399. refundable,
  400. active,
  401. total_inventory_qty,
  402. voucher_format,
  403. voucher_fulfillment,
  404. issuance_approval,
  405. ticket_guest_type,
  406. agent_url,
  407. hold_booking_time,
  408. deleted,
  409. voucher_usage_validity,
  410. voucher_sales_validity,
  411. date_of_birth,
  412. reservation,
  413. inventory_type,
  414. voucher_type,
  415. term_condition,
  416. voucher_code_format,
  417. reservation_day,
  418. best_deal,
  419. subtitle,
  420. direct_entrance,
  421. ticket_has_commission,
  422. commission_rate,
  423. multi_supplier,
  424. how_to_redeem_description,
  425. need_self_identifier,
  426. self_identifier_required_type,
  427. self_identifier_type,
  428. price_include,
  429. price_exclude,
  430. reservation_time,
  431. min_reservation_day,
  432. need_booking_options,
  433. pushed_to_aggregator
  434. )
  435. VALUES (
  436. exp_ticket_id,
  437. ticket_spec ->> 'experience_id',
  438. ticket_spec ->> 'ticket_type',
  439. ticket_spec ->> 'ticket_barcode_type',
  440. ticket_spec ->> 'ticket_name',
  441. ticket_spec ->> 'ticket_description',
  442. (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
  443. (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
  444. (ticket_spec ->> 'maximum_child_size') :: INTEGER,
  445. (ticket_spec ->> 'minimum_child_size') :: INTEGER,
  446. case (ticket_spec ->> 'group_size') :: INTEGER
  447. when 0
  448. then 1
  449. else (ticket_spec ->> 'group_size') :: INTEGER end,
  450. (ticket_spec ->> 'refundable') :: BOOLEAN,
  451. (ticket_spec ->> 'active') :: BOOLEAN,
  452. (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
  453. COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
  454. COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
  455. COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
  456. 'SPECIFIC',
  457. ticket_spec ->> 'agent_url',
  458. (ticket_spec ->> 'hold_booking_time') :: BIGINT,
  459. FALSE,
  460. CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
  461. THEN NULL
  462. ELSE
  463. row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
  464. 'validity_type',
  465. ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
  466. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  467. END,
  468. CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
  469. THEN NULL
  470. ELSE
  471. row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
  472. 'validity_type',
  473. ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
  474. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  475. END,
  476. ticket_spec ->> 'date_of_birth',
  477. ticket_spec ->> 'reservation',
  478. ticket_spec ->> 'inventory_type',
  479. ticket_spec ->> 'voucher_type',
  480. ticket_spec ->> 'term_condition',
  481. ticket_spec ->> 'voucher_code_format',
  482. (ticket_spec ->> 'reservation_day') :: INTEGER,
  483. (ticket_spec ->> 'best_deal') :: BOOLEAN,
  484. ticket_spec ->> 'subtitle',
  485. (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
  486. COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
  487. (ticket_spec ->> 'commission_rate') :: REAL,
  488. (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
  489. ticket_spec ->> 'how_to_redeem_description',
  490. COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
  491. ticket_spec ->> 'self_identifier_required_type',
  492. ticket_spec ->> 'self_identifier_type',
  493. ticket_spec ->> 'price_include',
  494. ticket_spec ->> 'price_exclude',
  495. ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
  496. :: experience_extranet_experience.hour_minute,
  497. COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
  498. COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
  499. COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  500. );
  501.  
  502. FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
  503. INSERT INTO experience_extranet_ticket.ticket_refund_policy (
  504. ticket_id,
  505. num_of_days_prior,
  506. num_of_days_prior_to,
  507. percentage,
  508. active
  509. )
  510. VALUES (
  511. exp_ticket_id,
  512. (COALESCE(policy->'num_of_days_range_prior'->>'days_from', policy->>'num_of_days_prior'))::INTEGER,
  513. (COALESCE(policy->'num_of_days_range_prior'->>'days_to', policy->>'num_of_days_prior'))::INTEGER,
  514. (policy ->> 'percentage') :: DOUBLE PRECISION,
  515. (policy ->> 'active') :: BOOLEAN
  516. );
  517. END LOOP;
  518.  
  519. FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
  520. INSERT INTO experience_extranet_ticket.ticket_time_slot (
  521. ticket_id,
  522. open_time,
  523. close_time
  524. )
  525. VALUES (
  526. exp_ticket_id,
  527. row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
  528. 'minute') :: experience_extranet_experience.hour_minute,
  529. row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
  530. 'minute') :: experience_extranet_experience.hour_minute
  531. );
  532. END LOOP;
  533.  
  534. FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
  535. FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
  536. INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
  537. ticket_id,
  538. group_unit_id,
  539. group_unit,
  540. group_id,
  541. group_name,
  542. group_description,
  543. maximum_size,
  544. minimum_size,
  545. quantifier
  546. )
  547. VALUES (
  548. exp_ticket_id,
  549. COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
  550. group_list ->> 'group_unit',
  551. COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
  552. group_detail_list ->> 'group_name',
  553. group_detail_list ->> 'group_description',
  554. (group_detail_list ->> 'maximum_size') :: INTEGER,
  555. (group_detail_list ->> 'minimum_size') :: INTEGER,
  556. (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
  557. );
  558. END LOOP;
  559. END LOOP;
  560.  
  561. FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
  562. INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
  563. redeemable_mapping_id,
  564. ticket_id,
  565. experience_id
  566. )
  567. VALUES (
  568. nextval('experience_extranet_ticket.ticket_redeemable_seq'),
  569. exp_ticket_id,
  570. redeemable->>'experience_id'
  571. );
  572. END LOOP;
  573.  
  574. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
  575.  
  576. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
  577.  
  578. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
  579.  
  580. IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  581. THEN
  582. PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
  583. ELSE END IF;
  584.  
  585. -- TODO: delete this when proper feature to map SF config is made
  586. INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
  587. SELECT ticket_id, 1, now()
  588. FROM experience_extranet_experience.experience e
  589. JOIN experience_extranet_ticket.ticket t
  590. ON e.experience_id = t.experience_id
  591. WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
  592. AND t.ticket_id = exp_ticket_id
  593. ON CONFLICT DO NOTHING;
  594.  
  595. RETURN exp_ticket_id;
  596. END;
  597. $$;
  598.  
  599.  
  600. CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.update_ticket(ticket_spec JSONB)
  601. RETURNS BOOLEAN
  602. SECURITY DEFINER
  603. LANGUAGE plpgsql
  604. AS $$
  605. DECLARE
  606. refund_policies jsonb;
  607. BEGIN
  608.  
  609. UPDATE experience_extranet_ticket.ticket
  610. SET
  611. --experience_id = ticket_spec->>'experience_id', --must not be updated
  612. --ticket_type = ticket_spec->>'ticket_type', --must not be updated
  613. --ticket_barcode_type = ticket_spec->>'ticket_barcode_type', --must not be updated
  614. ticket_name = ticket_spec ->> 'ticket_name',
  615. ticket_description = ticket_spec ->> 'ticket_description',
  616. maximum_adult_size = (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
  617. minimum_adult_size = (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
  618. maximum_child_size = (ticket_spec ->> 'maximum_child_size') :: INTEGER,
  619. minimum_child_size = (ticket_spec ->> 'minimum_child_size') :: INTEGER,
  620. group_size = case (ticket_spec ->> 'group_size') :: INTEGER
  621. when 0
  622. then 1
  623. else (ticket_spec ->> 'group_size') :: INTEGER end,
  624. refundable = (ticket_spec ->> 'refundable') :: BOOLEAN,
  625. active = (ticket_spec ->> 'active') :: BOOLEAN,
  626. total_inventory_qty = (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
  627. --voucher_format = COALESCE(ticket_spec->>'voucher_format', 'BARCODE'), --must not be updated
  628. --voucher_fulfillment = COALESCE(ticket_spec->>'voucher_fulfillment', 'AUTOMATIC'), --must not be updated
  629. issuance_approval = COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
  630. --ticket_guest_type = COALESCE(ticket_spec->>'ticket_guest_type', 'GENERIC'), --must not be updated
  631. agent_url = ticket_spec ->> 'agent_url',
  632. hold_booking_time = (ticket_spec ->> 'hold_booking_time') :: BIGINT,
  633. voucher_usage_validity = CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR
  634. ticket_spec -> 'voucher_usage_validity' = '{}')
  635. THEN NULL
  636. ELSE
  637. row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days',
  638. ticket_spec -> 'voucher_usage_validity' ->> 'validity_type',
  639. ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from',
  640. ticket_spec -> 'voucher_usage_validity' ->>
  641. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  642. END,
  643. voucher_sales_validity = CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR
  644. ticket_spec -> 'voucher_sales_validity' = '{}')
  645. THEN NULL
  646. ELSE
  647. row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days',
  648. ticket_spec -> 'voucher_sales_validity' ->> 'validity_type',
  649. ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from',
  650. ticket_spec -> 'voucher_sales_validity' ->>
  651. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  652. END,
  653. date_of_birth = ticket_spec ->> 'date_of_birth',
  654. reservation = ticket_spec ->> 'reservation',
  655. inventory_type = ticket_spec ->> 'inventory_type',
  656. voucher_type = ticket_spec ->> 'voucher_type',
  657. term_condition = ticket_spec ->> 'term_condition',
  658. voucher_code_format = ticket_spec ->> 'voucher_code_format',
  659. reservation_day = (ticket_spec ->> 'reservation_day') :: INTEGER,
  660. best_deal = (ticket_spec ->> 'best_deal') :: BOOLEAN,
  661. subtitle = ticket_spec ->> 'subtitle',
  662. direct_entrance = (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
  663. -- ticket_has_commission = COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
  664. commission_rate = (ticket_spec ->> 'commission_rate') :: DOUBLE PRECISION,
  665. multi_supplier = (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
  666. how_to_redeem_description = ticket_spec ->> 'how_to_redeem_description',
  667. need_self_identifier = COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
  668. self_identifier_required_type = ticket_spec ->> 'self_identifier_required_type',
  669. self_identifier_type = ticket_spec ->> 'self_identifier_type',
  670. price_include = ticket_spec ->> 'price_include',
  671. price_exclude = ticket_spec ->> 'price_exclude',
  672. reservation_time = ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
  673. :: experience_extranet_experience.hour_minute,
  674. min_reservation_day = COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
  675. need_booking_options = COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
  676. pushed_to_aggregator = COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  677. WHERE experience_extranet_ticket.ticket.ticket_id = (ticket_spec ->> 'ticket_id');
  678.  
  679. DELETE FROM experience_extranet_ticket.ticket_refund_policy
  680. WHERE NOT EXISTS(
  681. SELECT num_of_days_prior
  682. FROM jsonb_array_elements(ticket_spec -> 'refund_policies') AS elem
  683. WHERE num_of_days_prior = (elem ->> 'num_of_days_prior') :: INTEGER
  684. ) AND ticket_id = ticket_spec ->> 'ticket_id';
  685.  
  686. FOR refund_policies IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
  687. INSERT INTO experience_extranet_ticket.ticket_refund_policy (
  688. ticket_id,
  689. num_of_days_prior,
  690. num_of_days_prior_to,
  691. percentage,
  692. active
  693. )
  694. SELECT
  695. ticket_spec ->> 'ticket_id',
  696. COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_from', refund_policies ->> 'num_of_days_prior')::INTEGER,
  697. COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_to', refund_policies ->> 'num_of_days_prior')::INTEGER,
  698. (refund_policies ->> 'percentage') :: DOUBLE PRECISION,
  699. (refund_policies ->> 'active') :: BOOLEAN
  700.  
  701. ON CONFLICT (ticket_id, num_of_days_prior)
  702. DO UPDATE
  703. SET
  704. percentage = EXCLUDED.percentage,
  705. num_of_days_prior_to = EXCLUDED.num_of_days_prior_to,
  706. active = EXCLUDED.active;
  707. END LOOP;
  708.  
  709. DELETE FROM experience_extranet_ticket.ticket_time_slot
  710. WHERE ticket_id = ticket_spec ->> 'ticket_id';
  711.  
  712.  
  713. INSERT INTO experience_extranet_ticket.ticket_time_slot (
  714. ticket_id,
  715. open_time,
  716. close_time
  717. )
  718. SELECT
  719. ticket_id,
  720. row (open_time_hour, open_time_minute) :: experience_extranet_experience.hour_minute,
  721. row (close_time_hour, close_time_minute) :: experience_extranet_experience.hour_minute
  722. FROM (
  723. SELECT
  724. ticket_spec ->> 'ticket_id' AS "ticket_id",
  725. (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
  726. 'hour') :: INTEGER AS "open_time_hour",
  727. (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
  728. 'minute') :: INTEGER AS "open_time_minute",
  729. (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
  730. 'hour') :: INTEGER AS "close_time_hour",
  731. (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
  732. 'minute') :: INTEGER AS "close_time_minute"
  733. ) ticket_time_slot
  734.  
  735. ON CONFLICT DO NOTHING;
  736.  
  737. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(ticket_spec ->> 'ticket_id', ticket_spec);
  738.  
  739. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_customer_group(ticket_spec);
  740.  
  741. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redeemable_mapping(ticket_spec);
  742.  
  743. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_term_condition');
  744.  
  745. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_redemption');
  746.  
  747. IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  748. THEN
  749. PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(ticket_spec ->> 'ticket_id', ticket_spec);
  750. ELSE END IF;
  751.  
  752. RETURN FOUND;
  753.  
  754. END;
  755. $$;
  756.  
  757.  
  758. CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
  759. security definer
  760. language plpgsql
  761. as $$
  762. DECLARE
  763. policy jsonb;
  764. time_slot jsonb;
  765. exp_ticket_id TEXT;
  766. group_list jsonb;
  767. group_detail_list jsonb;
  768. redeemable jsonb;
  769. BEGIN
  770. exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
  771. nextval('experience_extranet_ticket.experience_ticket_seq'));
  772.  
  773. INSERT INTO experience_extranet_ticket.ticket (
  774. ticket_id,
  775. experience_id,
  776. ticket_type,
  777. ticket_barcode_type,
  778. ticket_name,
  779. ticket_description,
  780. maximum_adult_size,
  781. minimum_adult_size,
  782. maximum_child_size,
  783. minimum_child_size,
  784. group_size,
  785. refundable,
  786. active,
  787. total_inventory_qty,
  788. voucher_format,
  789. voucher_fulfillment,
  790. issuance_approval,
  791. ticket_guest_type,
  792. agent_url,
  793. hold_booking_time,
  794. deleted,
  795. voucher_usage_validity,
  796. voucher_sales_validity,
  797. date_of_birth,
  798. reservation,
  799. inventory_type,
  800. voucher_type,
  801. term_condition,
  802. voucher_code_format,
  803. reservation_day,
  804. best_deal,
  805. subtitle,
  806. direct_entrance,
  807. ticket_has_commission,
  808. commission_rate,
  809. multi_supplier,
  810. how_to_redeem_description,
  811. need_self_identifier,
  812. self_identifier_required_type,
  813. self_identifier_type,
  814. price_include,
  815. price_exclude,
  816. reservation_time,
  817. min_reservation_day,
  818. need_booking_options,
  819. pushed_to_aggregator
  820. )
  821. VALUES (
  822. exp_ticket_id,
  823. ticket_spec ->> 'experience_id',
  824. ticket_spec ->> 'ticket_type',
  825. ticket_spec ->> 'ticket_barcode_type',
  826. ticket_spec ->> 'ticket_name',
  827. ticket_spec ->> 'ticket_description',
  828. (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
  829. (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
  830. (ticket_spec ->> 'maximum_child_size') :: INTEGER,
  831. (ticket_spec ->> 'minimum_child_size') :: INTEGER,
  832. case (ticket_spec ->> 'group_size') :: INTEGER
  833. when 0
  834. then 1
  835. else (ticket_spec ->> 'group_size') :: INTEGER end,
  836. (ticket_spec ->> 'refundable') :: BOOLEAN,
  837. (ticket_spec ->> 'active') :: BOOLEAN,
  838. (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
  839. COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
  840. COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
  841. COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
  842. 'SPECIFIC',
  843. ticket_spec ->> 'agent_url',
  844. (ticket_spec ->> 'hold_booking_time') :: BIGINT,
  845. FALSE,
  846. CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
  847. THEN NULL
  848. ELSE
  849. row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
  850. 'validity_type',
  851. ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
  852. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  853. END,
  854. CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
  855. THEN NULL
  856. ELSE
  857. row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
  858. 'validity_type',
  859. ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
  860. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  861. END,
  862. ticket_spec ->> 'date_of_birth',
  863. ticket_spec ->> 'reservation',
  864. ticket_spec ->> 'inventory_type',
  865. ticket_spec ->> 'voucher_type',
  866. ticket_spec ->> 'term_condition',
  867. ticket_spec ->> 'voucher_code_format',
  868. (ticket_spec ->> 'reservation_day') :: INTEGER,
  869. (ticket_spec ->> 'best_deal') :: BOOLEAN,
  870. ticket_spec ->> 'subtitle',
  871. (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
  872. COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
  873. (ticket_spec ->> 'commission_rate') :: REAL,
  874. (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
  875. ticket_spec ->> 'how_to_redeem_description',
  876. COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
  877. ticket_spec ->> 'self_identifier_required_type',
  878. ticket_spec ->> 'self_identifier_type',
  879. ticket_spec ->> 'price_include',
  880. ticket_spec ->> 'price_exclude',
  881. ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
  882. :: experience_extranet_experience.hour_minute,
  883. COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
  884. COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
  885. COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  886. );
  887.  
  888. FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
  889. INSERT INTO experience_extranet_ticket.ticket_refund_policy (
  890. ticket_id,
  891. num_of_days_prior,
  892. percentage,
  893. active
  894. )
  895. VALUES (
  896. exp_ticket_id,
  897. (policy ->> 'num_of_days_prior') :: INTEGER,
  898. (policy ->> 'percentage') :: DOUBLE PRECISION,
  899. (policy ->> 'active') :: BOOLEAN
  900. );
  901. END LOOP;
  902.  
  903. FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
  904. INSERT INTO experience_extranet_ticket.ticket_time_slot (
  905. ticket_id,
  906. open_time,
  907. close_time
  908. )
  909. VALUES (
  910. exp_ticket_id,
  911. row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
  912. 'minute') :: experience_extranet_experience.hour_minute,
  913. row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
  914. 'minute') :: experience_extranet_experience.hour_minute
  915. );
  916. END LOOP;
  917.  
  918. FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
  919. FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
  920. INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
  921. ticket_id,
  922. group_unit_id,
  923. group_unit,
  924. group_id,
  925. group_name,
  926. group_description,
  927. maximum_size,
  928. minimum_size,
  929. quantifier
  930. )
  931. VALUES (
  932. exp_ticket_id,
  933. COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
  934. group_list ->> 'group_unit',
  935. COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
  936. group_detail_list ->> 'group_name',
  937. group_detail_list ->> 'group_description',
  938. (group_detail_list ->> 'maximum_size') :: INTEGER,
  939. (group_detail_list ->> 'minimum_size') :: INTEGER,
  940. (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
  941. );
  942. END LOOP;
  943. END LOOP;
  944.  
  945. FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
  946. INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
  947. redeemable_mapping_id,
  948. ticket_id,
  949. experience_id
  950. )
  951. VALUES (
  952. nextval('experience_extranet_ticket.ticket_redeemable_seq'),
  953. exp_ticket_id,
  954. redeemable->>'experience_id'
  955. );
  956. END LOOP;
  957.  
  958. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
  959.  
  960. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
  961.  
  962. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
  963.  
  964. IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  965. THEN
  966. PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
  967. ELSE END IF;
  968.  
  969. -- TODO: delete this when proper feature to map SF config is made
  970. INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
  971. SELECT ticket_id, 1, now()
  972. FROM experience_extranet_experience.experience e
  973. JOIN experience_extranet_ticket.ticket t
  974. ON e.experience_id = t.experience_id
  975. WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
  976. AND t.ticket_id = exp_ticket_id
  977. ON CONFLICT DO NOTHING;
  978.  
  979. RETURN exp_ticket_id;
  980. END;
  981. $$;
  982.  
  983. CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
  984. security definer
  985. language plpgsql
  986. as $$
  987. DECLARE
  988. policy jsonb;
  989. time_slot jsonb;
  990. exp_ticket_id TEXT;
  991. group_list jsonb;
  992. group_detail_list jsonb;
  993. redeemable jsonb;
  994. BEGIN
  995. exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
  996. nextval('experience_extranet_ticket.experience_ticket_seq'));
  997.  
  998. INSERT INTO experience_extranet_ticket.ticket (
  999. ticket_id,
  1000. experience_id,
  1001. ticket_type,
  1002. ticket_barcode_type,
  1003. ticket_name,
  1004. ticket_description,
  1005. maximum_adult_size,
  1006. minimum_adult_size,
  1007. maximum_child_size,
  1008. minimum_child_size,
  1009. group_size,
  1010. refundable,
  1011. active,
  1012. total_inventory_qty,
  1013. voucher_format,
  1014. voucher_fulfillment,
  1015. issuance_approval,
  1016. ticket_guest_type,
  1017. agent_url,
  1018. hold_booking_time,
  1019. deleted,
  1020. voucher_usage_validity,
  1021. voucher_sales_validity,
  1022. date_of_birth,
  1023. reservation,
  1024. inventory_type,
  1025. voucher_type,
  1026. term_condition,
  1027. voucher_code_format,
  1028. reservation_day,
  1029. best_deal,
  1030. subtitle,
  1031. direct_entrance,
  1032. ticket_has_commission,
  1033. commission_rate,
  1034. multi_supplier,
  1035. how_to_redeem_description,
  1036. need_self_identifier,
  1037. self_identifier_required_type,
  1038. self_identifier_type,
  1039. price_include,
  1040. price_exclude,
  1041. reservation_time,
  1042. min_reservation_day,
  1043. need_booking_options,
  1044. pushed_to_aggregator
  1045. )
  1046. VALUES (
  1047. exp_ticket_id,
  1048. ticket_spec ->> 'experience_id',
  1049. ticket_spec ->> 'ticket_type',
  1050. ticket_spec ->> 'ticket_barcode_type',
  1051. ticket_spec ->> 'ticket_name',
  1052. ticket_spec ->> 'ticket_description',
  1053. (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
  1054. (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
  1055. (ticket_spec ->> 'maximum_child_size') :: INTEGER,
  1056. (ticket_spec ->> 'minimum_child_size') :: INTEGER,
  1057. case (ticket_spec ->> 'group_size') :: INTEGER
  1058. when 0
  1059. then 1
  1060. else (ticket_spec ->> 'group_size') :: INTEGER end,
  1061. (ticket_spec ->> 'refundable') :: BOOLEAN,
  1062. (ticket_spec ->> 'active') :: BOOLEAN,
  1063. (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
  1064. COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
  1065. COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
  1066. COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
  1067. 'SPECIFIC',
  1068. ticket_spec ->> 'agent_url',
  1069. (ticket_spec ->> 'hold_booking_time') :: BIGINT,
  1070. FALSE,
  1071. CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
  1072. THEN NULL
  1073. ELSE
  1074. row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
  1075. 'validity_type',
  1076. ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
  1077. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  1078. END,
  1079. CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
  1080. THEN NULL
  1081. ELSE
  1082. row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
  1083. 'validity_type',
  1084. ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
  1085. 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
  1086. END,
  1087. ticket_spec ->> 'date_of_birth',
  1088. ticket_spec ->> 'reservation',
  1089. ticket_spec ->> 'inventory_type',
  1090. ticket_spec ->> 'voucher_type',
  1091. ticket_spec ->> 'term_condition',
  1092. ticket_spec ->> 'voucher_code_format',
  1093. (ticket_spec ->> 'reservation_day') :: INTEGER,
  1094. (ticket_spec ->> 'best_deal') :: BOOLEAN,
  1095. ticket_spec ->> 'subtitle',
  1096. (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
  1097. COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
  1098. (ticket_spec ->> 'commission_rate') :: REAL,
  1099. (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
  1100. ticket_spec ->> 'how_to_redeem_description',
  1101. COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
  1102. ticket_spec ->> 'self_identifier_required_type',
  1103. ticket_spec ->> 'self_identifier_type',
  1104. ticket_spec ->> 'price_include',
  1105. ticket_spec ->> 'price_exclude',
  1106. ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
  1107. :: experience_extranet_experience.hour_minute,
  1108. COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
  1109. COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
  1110. COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  1111. );
  1112.  
  1113. FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
  1114. INSERT INTO experience_extranet_ticket.ticket_refund_policy (
  1115. ticket_id,
  1116. num_of_days_prior,
  1117. percentage,
  1118. active
  1119. )
  1120. VALUES (
  1121. exp_ticket_id,
  1122. (policy ->> 'num_of_days_prior') :: INTEGER,
  1123. (policy ->> 'percentage') :: DOUBLE PRECISION,
  1124. (policy ->> 'active') :: BOOLEAN
  1125. );
  1126. END LOOP;
  1127.  
  1128. FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
  1129. INSERT INTO experience_extranet_ticket.ticket_time_slot (
  1130. ticket_id,
  1131. open_time,
  1132. close_time
  1133. )
  1134. VALUES (
  1135. exp_ticket_id,
  1136. row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
  1137. 'minute') :: experience_extranet_experience.hour_minute,
  1138. row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
  1139. 'minute') :: experience_extranet_experience.hour_minute
  1140. );
  1141. END LOOP;
  1142.  
  1143. FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
  1144. FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
  1145. INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
  1146. ticket_id,
  1147. group_unit_id,
  1148. group_unit,
  1149. group_id,
  1150. group_name,
  1151. group_description,
  1152. maximum_size,
  1153. minimum_size,
  1154. quantifier
  1155. )
  1156. VALUES (
  1157. exp_ticket_id,
  1158. COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
  1159. group_list ->> 'group_unit',
  1160. COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
  1161. group_detail_list ->> 'group_name',
  1162. group_detail_list ->> 'group_description',
  1163. (group_detail_list ->> 'maximum_size') :: INTEGER,
  1164. (group_detail_list ->> 'minimum_size') :: INTEGER,
  1165. (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
  1166. );
  1167. END LOOP;
  1168. END LOOP;
  1169.  
  1170. FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
  1171. INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
  1172. redeemable_mapping_id,
  1173. ticket_id,
  1174. experience_id
  1175. )
  1176. VALUES (
  1177. nextval('experience_extranet_ticket.ticket_redeemable_seq'),
  1178. exp_ticket_id,
  1179. redeemable->>'experience_id'
  1180. );
  1181. END LOOP;
  1182.  
  1183. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
  1184.  
  1185. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
  1186.  
  1187. PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
  1188.  
  1189. IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  1190. THEN
  1191. PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
  1192. ELSE END IF;
  1193.  
  1194. -- TODO: delete this when proper feature to map SF config is made
  1195. INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
  1196. SELECT ticket_id, 1, now()
  1197. FROM experience_extranet_experience.experience e
  1198. JOIN experience_extranet_ticket.ticket t
  1199. ON e.experience_id = t.experience_id
  1200. WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
  1201. AND t.ticket_id = exp_ticket_id
  1202. ON CONFLICT DO NOTHING;
  1203.  
  1204. RETURN exp_ticket_id;
  1205. END;
  1206. $$;
RAW Paste Data