SHARE
TWEET

Untitled

a guest Dec 9th, 2019 89 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top