Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category, mapping_table_name, mapping_upsert_function_name)
- VALUES ('UMRAH',
- 'experience_extranet_ticket.ticket_umrah',
- 'experience_extranet_ticket_v2_helper.upsert_ticket_umrah_additional_data($1, $2)')
- ON CONFLICT (experience_category) DO UPDATE
- SET
- mapping_table_name = EXCLUDED.mapping_table_name,
- mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
- INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category, mapping_table_name, mapping_upsert_function_name)
- VALUES ('LEISURE',
- 'experience_extranet_ticket.ticket_leisure',
- 'experience_extranet_ticket_v2_helper.upsert_ticket_leisure_additional_data($1, $2)')
- ON CONFLICT (experience_category) DO UPDATE
- SET
- mapping_table_name = EXCLUDED.mapping_table_name,
- mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
- CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_umrah
- (
- ticket_id TEXT NOT NULL PRIMARY KEY
- CONSTRAINT ticket_umrah_ticket_id_fkey
- REFERENCES experience_extranet_ticket.ticket
- ON UPDATE CASCADE ON DELETE RESTRICT,
- "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
- );
- CREATE INDEX IF NOT EXISTS experience_extranet_ticket_umrah__lut__idx
- ON experience_extranet_ticket.ticket_umrah ("_lut");
- DO $$
- BEGIN
- CREATE TRIGGER populate_lut_ticket_umrah
- BEFORE INSERT OR UPDATE
- ON experience_extranet_ticket.ticket_umrah
- FOR EACH ROW
- EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
- EXCEPTION
- WHEN duplicate_object
- THEN RAISE NOTICE 'the trigger already exists';
- END;
- $$;
- CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_leisure
- (
- ticket_id TEXT NOT NULL PRIMARY KEY
- CONSTRAINT ticket_leisure_ticket_id_fkey
- REFERENCES experience_extranet_ticket.ticket
- ON UPDATE CASCADE ON DELETE RESTRICT,
- "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
- );
- CREATE INDEX IF NOT EXISTS experience_extranet_ticket_leisure__lut__idx
- ON experience_extranet_ticket.ticket_leisure ("_lut");
- DO $$
- BEGIN
- CREATE TRIGGER populate_lut_ticket_leisure
- BEFORE INSERT OR UPDATE
- ON experience_extranet_ticket.ticket_leisure
- FOR EACH ROW
- EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
- EXCEPTION
- WHEN duplicate_object
- THEN RAISE NOTICE 'the trigger already exists';
- END;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_hotels_by_experience_id(experience_id_spec text) returns jsonb
- security definer
- language sql
- as $$
- SELECT jsonb_agg(to_jsonb(result))
- FROM (
- SELECT *
- FROM experience_extranet_experience.experience_umrah_hotel
- WHERE experience_id = experience_id_spec
- ) AS result
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_flights_by_experience_id(experience_id_spec text) returns jsonb
- security definer
- language sql
- as $$
- SELECT jsonb_agg(to_jsonb(result))
- FROM (
- SELECT *
- FROM experience_extranet_experience.experience_umrah_flight
- WHERE experience_id = experience_id_spec
- ) AS result
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_travel_agent_by_experience_id(experience_id_spec text) returns jsonb
- security definer
- language sql
- as $$
- SELECT jsonb_agg(to_jsonb(result))
- FROM (
- SELECT *
- FROM experience_extranet_experience.experience_umrah_travel_agent
- WHERE experience_id = experience_id_spec
- ) AS result
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_additional_data(
- filter_experience_id TEXT)
- RETURNS JSONB
- SECURITY DEFINER
- LANGUAGE SQL
- AS $$
- WITH get_umrah_result AS (SELECT *
- FROM experience_extranet_experience.experience_umrah
- WHERE experience_id = filter_experience_id),
- get_travel_agent_result AS (SELECT to_jsonb(result)
- FROM (SELECT *
- FROM experience_extranet_experience.experience_umrah_travel_agent
- WHERE experience_id = filter_experience_id) result),
- get_hotels_result AS (SELECT jsonb_agg(to_jsonb(result))
- FROM (SELECT *
- FROM experience_extranet_experience.experience_umrah_hotel
- WHERE experience_id = filter_experience_id) result),
- get_flights_result AS (SELECT jsonb_agg(to_jsonb(result))
- FROM (SELECT *
- FROM experience_extranet_experience.experience_umrah_flight
- WHERE experience_id = filter_experience_id) result),
- get_itinerary_description_result AS (SELECT *
- FROM experience_extranet_experience.experience_itinerary_description eid
- WHERE EXISTS(
- SELECT ei.experience_itinerary_id
- FROM experience_extranet_experience.experience_itinerary ei
- WHERE ei.experience_id = filter_experience_id
- AND ei.experience_itinerary_id = eid.experience_itinerary_id
- )),
- get_itinerary_result AS (SELECT array_agg(itinerary_result) AS result
- FROM (SELECT *, (SELECT array_agg(res) AS descriptions
- FROM (SELECT *, (SELECT array_agg(img) AS images
- FROM (SELECT *
- FROM experience_extranet_experience.experience_itinerary_image iti
- WHERE iti.itinerary_description_id = des.itinerary_description_id) img)
- FROM get_itinerary_description_result des
- WHERE ei.experience_itinerary_id = des.experience_itinerary_id
- ORDER BY des.time) res)
- FROM experience_extranet_experience.experience_itinerary ei
- WHERE experience_id = filter_experience_id
- ORDER BY ei.day) itinerary_result),
- get_tour_meeting_point_result AS (SELECT to_jsonb(result)
- FROM (SELECT *
- FROM experience_extranet_experience.experience_tour_meeting_point mp
- WHERE mp.experience_id = filter_experience_id) AS result),
- get_tour_content_result AS (SELECT *
- FROM experience_extranet_content.experience_tour_content
- WHERE experience_id = filter_experience_id)
- SELECT jsonb_build_object(
- 'duration_day', (SELECT duration_day FROM get_umrah_result),
- 'all_day_long', (SELECT all_day_long FROM get_umrah_result),
- 'travel_agent', (SELECT * FROM get_travel_agent_result),
- 'flights', (SELECT * FROM get_flights_result),
- 'hotels', (SELECT * FROM get_hotels_result),
- 'itineraries', (SELECT * FROM get_itinerary_result),
- 'pickup_price', (SELECT pickup_price FROM get_tour_content_result),
- 'pickup_address', (SELECT pickup_address FROM get_tour_content_result),
- 'drop_off_point', (SELECT drop_off_point FROM get_tour_content_result),
- 'meeting_point_available', (SELECT CASE WHEN get_tour_meeting_point_result IS NULL THEN FALSE ELSE TRUE END
- FROM get_tour_meeting_point_result),
- 'meeting_point', (SELECT * FROM get_tour_meeting_point_result)
- );
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_umrah_additional_data(filter_experience_id text, additional_data_spec jsonb) returns boolean
- security definer
- language plpgsql
- as $$
- DECLARE
- travel_agent_id_spec BIGINT;
- itinerary_id BIGINT;
- itinerary JSONB;
- description JSONB;
- BEGIN
- INSERT INTO experience_extranet_experience.experience_umrah (experience_id, duration_day, all_day_long)
- VALUES (
- filter_experience_id,
- (COALESCE(NULLIF(additional_data_spec->>'duration_day', ''), '0'))::INTEGER,
- (COALESCE(NULLIF(additional_data_spec->>'all_day_long', ''), 'FALSE')) :: BOOLEAN
- )
- ON CONFLICT (experience_id) DO UPDATE
- SET
- duration_day = EXCLUDED.duration_day,
- all_day_long = EXCLUDED.all_day_long;
- travel_agent_id_spec := additional_data_spec -> 'travel_agent' ->> 'travel_agent_id';
- IF (travel_agent_id_spec = 0)
- THEN
- travel_agent_id_spec := nextval('experience_extranet_experience.experience_umrah_travel_agent_travel_agent_id_seq');
- END IF;
- INSERT INTO experience_extranet_experience.experience_umrah_travel_agent (travel_agent_id, experience_id, travel_agent_name, license_number)
- VALUES (
- travel_agent_id_spec,
- filter_experience_id,
- additional_data_spec->'travel_agent'->>'travel_agent_name',
- additional_data_spec->'travel_agent'->>'license_number'
- )
- ON CONFLICT (travel_agent_id) DO UPDATE
- SET
- experience_id = EXCLUDED.experience_id,
- travel_agent_name = EXCLUDED.travel_agent_name,
- license_number = EXCLUDED.license_number;
- DELETE
- FROM experience_extranet_experience.experience_umrah_hotel
- WHERE hotel_id NOT IN
- (SELECT hotel_id FROM jsonb_array_elements(additional_data_spec->'hotels') AS elem
- WHERE hotel_id = (elem->>'hotel_id') :: BIGINT) AND experience_id = filter_experience_id;
- INSERT INTO experience_extranet_experience.experience_umrah_hotel (hotel_id, experience_id, country, city, rating, name, photos, distance_from_main_mosque)
- SELECT
- (hotel ->> 'hotel_id') :: BIGINT,
- filter_experience_id,
- hotel ->> 'country',
- hotel ->> 'city',
- (hotel ->> 'rating') :: INTEGER,
- hotel ->> 'name',
- hotel -> 'photos',
- hotel ->> 'distance_from_main_mosque'
- FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
- WHERE (hotel ->> 'hotel_id') :: BIGINT <> 0
- ON CONFLICT (hotel_id) DO UPDATE
- SET country = EXCLUDED.country,
- city = EXCLUDED.city,
- name = EXCLUDED.name,
- photos = EXCLUDED.photos,
- distance_from_main_mosque = EXCLUDED.distance_from_main_mosque;
- INSERT INTO experience_extranet_experience.experience_umrah_hotel (experience_id, country, city, rating, name, photos, distance_from_main_mosque)
- SELECT
- filter_experience_id,
- hotel ->> 'country',
- hotel ->> 'city',
- (hotel ->> 'rating') :: INTEGER,
- hotel ->> 'name',
- hotel -> 'photos',
- hotel ->> 'distance_from_main_mosque'
- FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
- WHERE (hotel ->> 'hotel_id') :: BIGINT = 0;
- DELETE
- FROM experience_extranet_experience.experience_umrah_flight
- WHERE flight_id NOT IN
- (SELECT flight_id
- FROM jsonb_array_elements(additional_data_spec->'flights') AS elem
- WHERE flight_id = (elem->>'flight_id') :: BIGINT) AND experience_id = filter_experience_id;
- INSERT INTO experience_extranet_experience.experience_umrah_flight (experience_id, flight_order, airline_brand, port)
- SELECT filter_experience_id,
- (flight ->> 'flight_order') :: INTEGER,
- flight ->> 'airline_brand',
- flight ->> 'port'
- FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
- WHERE (flight ->> 'flight_id') :: BIGINT = 0;
- INSERT INTO experience_extranet_experience.experience_umrah_flight (flight_id, experience_id, flight_order, airline_brand, port)
- SELECT (flight ->> 'flight_id') :: BIGINT,
- filter_experience_id,
- (flight ->> 'flight_order') :: INTEGER,
- flight ->> 'airline_brand',
- flight ->> 'port'
- FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
- WHERE (flight ->> 'flight_id') :: BIGINT <> 0
- ON CONFLICT (flight_id) DO UPDATE
- SET flight_order = EXCLUDED.flight_order, airline_brand = EXCLUDED.airline_brand, port = EXCLUDED.port;
- DELETE FROM experience_extranet_experience.experience_itinerary
- WHERE NOT EXISTS (
- SELECT experience_itinerary_id
- FROM jsonb_array_elements(additional_data_spec->'itineraries') AS elem
- WHERE experience_itinerary_id = (elem->>'experience_itinerary_id')::BIGINT
- ) AND experience_id = filter_experience_id;
- FOR itinerary IN SELECT jsonb_array_elements(additional_data_spec->'itineraries') LOOP
- IF ((SELECT COUNT(*) FROM experience_extranet_experience.experience_itinerary WHERE experience_itinerary_id = (itinerary->>'experience_itinerary_id')::BIGINT) > 0)THEN
- itinerary_id := (itinerary->>'experience_itinerary_id')::BIGINT;
- ELSE
- itinerary_id := nextval('experience_extranet_experience.experience_itinerary_seq');
- END IF;
- PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary(itinerary_id, filter_experience_id, itinerary);
- DELETE FROM experience_extranet_experience.experience_itinerary_description eid
- WHERE NOT EXISTS(
- SELECT *
- FROM jsonb_array_elements(itinerary->'descriptions') AS elem
- WHERE eid.itinerary_description_id = (elem->>'itinerary_description_id')::BIGINT
- ) AND eid.experience_itinerary_id = itinerary_id;
- FOR description IN SELECT jsonb_array_elements(itinerary->'descriptions') LOOP
- PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_description(itinerary_id, description);
- END LOOP;
- END LOOP;
- IF (additional_data_spec ->> 'meeting_point' IS NULL OR (additional_data_spec ->> 'meeting_point_available') :: BOOLEAN IS FALSE) THEN
- DELETE FROM experience_extranet_experience.experience_tour_meeting_point
- WHERE experience_id = filter_experience_id;
- ELSE
- PERFORM experience_extranet_management_v2_helper.upsert_tour_meeting_point(filter_experience_id, additional_data_spec -> 'meeting_point');
- END IF;
- PERFORM experience_extranet_content_v2_helper.upsert_experience_tour_content(filter_experience_id, additional_data_spec);
- RETURN FOUND;
- END;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_leisure_additional_data(filter_experience_id TEXT)
- RETURNS JSONB
- SECURITY DEFINER
- LANGUAGE SQL
- AS $$
- WITH get_leisure_result AS (
- SELECT * FROM experience_extranet_experience.experience_leisure WHERE experience_id = filter_experience_id
- )
- SELECT jsonb_build_object('experience_id',
- (SELECT experience_id FROM get_leisure_result)
- );
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_leisure_additional_data(
- filter_experience_id TEXT, additional_data_spec JSONB)
- RETURNS BOOLEAN
- SECURITY DEFINER
- LANGUAGE plpgsql
- AS $$
- BEGIN
- INSERT INTO experience_extranet_experience.experience_leisure (experience_id) VALUES (filter_experience_id)
- ON CONFLICT (experience_id) DO NOTHING;
- RETURN FOUND;
- END;
- $$;
- DO $$
- BEGIN
- ALTER TABLE IF EXISTS experience_extranet_ticket.ticket_refund_policy
- ADD COLUMN num_of_days_prior_to INTEGER;
- EXCEPTION
- WHEN duplicate_column THEN RAISE NOTICE 'column num_of_days_prior_to already exists';
- END;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_ticket_v1_helper.get_ticket_refund_policy(filter_ticket_id TEXT)
- RETURNS JSONB
- SECURITY DEFINER
- LANGUAGE SQL
- AS $$
- SELECT to_jsonb(array_agg(res))
- FROM (SELECT *, jsonb_build_object('days_from', trp.num_of_days_prior,
- 'days_to', COALESCE(trp.num_of_days_prior_to, trp.num_of_days_prior)) AS num_of_days_range_prior
- FROM experience_extranet_ticket.ticket_refund_policy trp
- WHERE trp.ticket_id = filter_ticket_id
- AND trp.active = TRUE
- ORDER BY trp.num_of_days_prior) AS res;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec JSONB)
- RETURNS TEXT
- SECURITY DEFINER
- LANGUAGE plpgsql
- AS $$
- DECLARE
- policy jsonb;
- time_slot jsonb;
- exp_ticket_id TEXT;
- group_list jsonb;
- group_detail_list jsonb;
- redeemable jsonb;
- BEGIN
- exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
- nextval('experience_extranet_ticket.experience_ticket_seq'));
- INSERT INTO experience_extranet_ticket.ticket (
- ticket_id,
- experience_id,
- ticket_type,
- ticket_barcode_type,
- ticket_name,
- ticket_description,
- maximum_adult_size,
- minimum_adult_size,
- maximum_child_size,
- minimum_child_size,
- group_size,
- refundable,
- active,
- total_inventory_qty,
- voucher_format,
- voucher_fulfillment,
- issuance_approval,
- ticket_guest_type,
- agent_url,
- hold_booking_time,
- deleted,
- voucher_usage_validity,
- voucher_sales_validity,
- date_of_birth,
- reservation,
- inventory_type,
- voucher_type,
- term_condition,
- voucher_code_format,
- reservation_day,
- best_deal,
- subtitle,
- direct_entrance,
- ticket_has_commission,
- commission_rate,
- multi_supplier,
- how_to_redeem_description,
- need_self_identifier,
- self_identifier_required_type,
- self_identifier_type,
- price_include,
- price_exclude,
- reservation_time,
- min_reservation_day,
- need_booking_options,
- pushed_to_aggregator
- )
- VALUES (
- exp_ticket_id,
- ticket_spec ->> 'experience_id',
- ticket_spec ->> 'ticket_type',
- ticket_spec ->> 'ticket_barcode_type',
- ticket_spec ->> 'ticket_name',
- ticket_spec ->> 'ticket_description',
- (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
- (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
- (ticket_spec ->> 'maximum_child_size') :: INTEGER,
- (ticket_spec ->> 'minimum_child_size') :: INTEGER,
- case (ticket_spec ->> 'group_size') :: INTEGER
- when 0
- then 1
- else (ticket_spec ->> 'group_size') :: INTEGER end,
- (ticket_spec ->> 'refundable') :: BOOLEAN,
- (ticket_spec ->> 'active') :: BOOLEAN,
- (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
- COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
- COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
- COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
- 'SPECIFIC',
- ticket_spec ->> 'agent_url',
- (ticket_spec ->> 'hold_booking_time') :: BIGINT,
- FALSE,
- CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_type',
- ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_type',
- ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- ticket_spec ->> 'date_of_birth',
- ticket_spec ->> 'reservation',
- ticket_spec ->> 'inventory_type',
- ticket_spec ->> 'voucher_type',
- ticket_spec ->> 'term_condition',
- ticket_spec ->> 'voucher_code_format',
- (ticket_spec ->> 'reservation_day') :: INTEGER,
- (ticket_spec ->> 'best_deal') :: BOOLEAN,
- ticket_spec ->> 'subtitle',
- (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
- COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
- (ticket_spec ->> 'commission_rate') :: REAL,
- (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
- ticket_spec ->> 'how_to_redeem_description',
- COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
- ticket_spec ->> 'self_identifier_required_type',
- ticket_spec ->> 'self_identifier_type',
- ticket_spec ->> 'price_include',
- ticket_spec ->> 'price_exclude',
- ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
- :: experience_extranet_experience.hour_minute,
- COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
- COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
- COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
- );
- FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
- INSERT INTO experience_extranet_ticket.ticket_refund_policy (
- ticket_id,
- num_of_days_prior,
- num_of_days_prior_to,
- percentage,
- active
- )
- VALUES (
- exp_ticket_id,
- (COALESCE(policy->'num_of_days_range_prior'->>'days_from', policy->>'num_of_days_prior'))::INTEGER,
- (COALESCE(policy->'num_of_days_range_prior'->>'days_to', policy->>'num_of_days_prior'))::INTEGER,
- (policy ->> 'percentage') :: DOUBLE PRECISION,
- (policy ->> 'active') :: BOOLEAN
- );
- END LOOP;
- FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
- INSERT INTO experience_extranet_ticket.ticket_time_slot (
- ticket_id,
- open_time,
- close_time
- )
- VALUES (
- exp_ticket_id,
- row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
- 'minute') :: experience_extranet_experience.hour_minute,
- row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
- 'minute') :: experience_extranet_experience.hour_minute
- );
- END LOOP;
- FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
- FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
- INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
- ticket_id,
- group_unit_id,
- group_unit,
- group_id,
- group_name,
- group_description,
- maximum_size,
- minimum_size,
- quantifier
- )
- VALUES (
- exp_ticket_id,
- COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
- group_list ->> 'group_unit',
- COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
- group_detail_list ->> 'group_name',
- group_detail_list ->> 'group_description',
- (group_detail_list ->> 'maximum_size') :: INTEGER,
- (group_detail_list ->> 'minimum_size') :: INTEGER,
- (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
- );
- END LOOP;
- END LOOP;
- FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
- INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
- redeemable_mapping_id,
- ticket_id,
- experience_id
- )
- VALUES (
- nextval('experience_extranet_ticket.ticket_redeemable_seq'),
- exp_ticket_id,
- redeemable->>'experience_id'
- );
- END LOOP;
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
- IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
- THEN
- PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
- ELSE END IF;
- -- TODO: delete this when proper feature to map SF config is made
- INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
- SELECT ticket_id, 1, now()
- FROM experience_extranet_experience.experience e
- JOIN experience_extranet_ticket.ticket t
- ON e.experience_id = t.experience_id
- WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
- AND t.ticket_id = exp_ticket_id
- ON CONFLICT DO NOTHING;
- RETURN exp_ticket_id;
- END;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.update_ticket(ticket_spec JSONB)
- RETURNS BOOLEAN
- SECURITY DEFINER
- LANGUAGE plpgsql
- AS $$
- DECLARE
- refund_policies jsonb;
- BEGIN
- UPDATE experience_extranet_ticket.ticket
- SET
- --experience_id = ticket_spec->>'experience_id', --must not be updated
- --ticket_type = ticket_spec->>'ticket_type', --must not be updated
- --ticket_barcode_type = ticket_spec->>'ticket_barcode_type', --must not be updated
- ticket_name = ticket_spec ->> 'ticket_name',
- ticket_description = ticket_spec ->> 'ticket_description',
- maximum_adult_size = (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
- minimum_adult_size = (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
- maximum_child_size = (ticket_spec ->> 'maximum_child_size') :: INTEGER,
- minimum_child_size = (ticket_spec ->> 'minimum_child_size') :: INTEGER,
- group_size = case (ticket_spec ->> 'group_size') :: INTEGER
- when 0
- then 1
- else (ticket_spec ->> 'group_size') :: INTEGER end,
- refundable = (ticket_spec ->> 'refundable') :: BOOLEAN,
- active = (ticket_spec ->> 'active') :: BOOLEAN,
- total_inventory_qty = (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
- --voucher_format = COALESCE(ticket_spec->>'voucher_format', 'BARCODE'), --must not be updated
- --voucher_fulfillment = COALESCE(ticket_spec->>'voucher_fulfillment', 'AUTOMATIC'), --must not be updated
- issuance_approval = COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
- --ticket_guest_type = COALESCE(ticket_spec->>'ticket_guest_type', 'GENERIC'), --must not be updated
- agent_url = ticket_spec ->> 'agent_url',
- hold_booking_time = (ticket_spec ->> 'hold_booking_time') :: BIGINT,
- voucher_usage_validity = CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR
- ticket_spec -> 'voucher_usage_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days',
- ticket_spec -> 'voucher_usage_validity' ->> 'validity_type',
- ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from',
- ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- voucher_sales_validity = CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR
- ticket_spec -> 'voucher_sales_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days',
- ticket_spec -> 'voucher_sales_validity' ->> 'validity_type',
- ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from',
- ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- date_of_birth = ticket_spec ->> 'date_of_birth',
- reservation = ticket_spec ->> 'reservation',
- inventory_type = ticket_spec ->> 'inventory_type',
- voucher_type = ticket_spec ->> 'voucher_type',
- term_condition = ticket_spec ->> 'term_condition',
- voucher_code_format = ticket_spec ->> 'voucher_code_format',
- reservation_day = (ticket_spec ->> 'reservation_day') :: INTEGER,
- best_deal = (ticket_spec ->> 'best_deal') :: BOOLEAN,
- subtitle = ticket_spec ->> 'subtitle',
- direct_entrance = (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
- -- ticket_has_commission = COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
- commission_rate = (ticket_spec ->> 'commission_rate') :: DOUBLE PRECISION,
- multi_supplier = (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
- how_to_redeem_description = ticket_spec ->> 'how_to_redeem_description',
- need_self_identifier = COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
- self_identifier_required_type = ticket_spec ->> 'self_identifier_required_type',
- self_identifier_type = ticket_spec ->> 'self_identifier_type',
- price_include = ticket_spec ->> 'price_include',
- price_exclude = ticket_spec ->> 'price_exclude',
- reservation_time = ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
- :: experience_extranet_experience.hour_minute,
- min_reservation_day = COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
- need_booking_options = COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
- pushed_to_aggregator = COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
- WHERE experience_extranet_ticket.ticket.ticket_id = (ticket_spec ->> 'ticket_id');
- DELETE FROM experience_extranet_ticket.ticket_refund_policy
- WHERE NOT EXISTS(
- SELECT num_of_days_prior
- FROM jsonb_array_elements(ticket_spec -> 'refund_policies') AS elem
- WHERE num_of_days_prior = (elem ->> 'num_of_days_prior') :: INTEGER
- ) AND ticket_id = ticket_spec ->> 'ticket_id';
- FOR refund_policies IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
- INSERT INTO experience_extranet_ticket.ticket_refund_policy (
- ticket_id,
- num_of_days_prior,
- num_of_days_prior_to,
- percentage,
- active
- )
- SELECT
- ticket_spec ->> 'ticket_id',
- COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_from', refund_policies ->> 'num_of_days_prior')::INTEGER,
- COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_to', refund_policies ->> 'num_of_days_prior')::INTEGER,
- (refund_policies ->> 'percentage') :: DOUBLE PRECISION,
- (refund_policies ->> 'active') :: BOOLEAN
- ON CONFLICT (ticket_id, num_of_days_prior)
- DO UPDATE
- SET
- percentage = EXCLUDED.percentage,
- num_of_days_prior_to = EXCLUDED.num_of_days_prior_to,
- active = EXCLUDED.active;
- END LOOP;
- DELETE FROM experience_extranet_ticket.ticket_time_slot
- WHERE ticket_id = ticket_spec ->> 'ticket_id';
- INSERT INTO experience_extranet_ticket.ticket_time_slot (
- ticket_id,
- open_time,
- close_time
- )
- SELECT
- ticket_id,
- row (open_time_hour, open_time_minute) :: experience_extranet_experience.hour_minute,
- row (close_time_hour, close_time_minute) :: experience_extranet_experience.hour_minute
- FROM (
- SELECT
- ticket_spec ->> 'ticket_id' AS "ticket_id",
- (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
- 'hour') :: INTEGER AS "open_time_hour",
- (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
- 'minute') :: INTEGER AS "open_time_minute",
- (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
- 'hour') :: INTEGER AS "close_time_hour",
- (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
- 'minute') :: INTEGER AS "close_time_minute"
- ) ticket_time_slot
- ON CONFLICT DO NOTHING;
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(ticket_spec ->> 'ticket_id', ticket_spec);
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_customer_group(ticket_spec);
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redeemable_mapping(ticket_spec);
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_term_condition');
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_redemption');
- IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
- THEN
- PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(ticket_spec ->> 'ticket_id', ticket_spec);
- ELSE END IF;
- RETURN FOUND;
- END;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
- security definer
- language plpgsql
- as $$
- DECLARE
- policy jsonb;
- time_slot jsonb;
- exp_ticket_id TEXT;
- group_list jsonb;
- group_detail_list jsonb;
- redeemable jsonb;
- BEGIN
- exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
- nextval('experience_extranet_ticket.experience_ticket_seq'));
- INSERT INTO experience_extranet_ticket.ticket (
- ticket_id,
- experience_id,
- ticket_type,
- ticket_barcode_type,
- ticket_name,
- ticket_description,
- maximum_adult_size,
- minimum_adult_size,
- maximum_child_size,
- minimum_child_size,
- group_size,
- refundable,
- active,
- total_inventory_qty,
- voucher_format,
- voucher_fulfillment,
- issuance_approval,
- ticket_guest_type,
- agent_url,
- hold_booking_time,
- deleted,
- voucher_usage_validity,
- voucher_sales_validity,
- date_of_birth,
- reservation,
- inventory_type,
- voucher_type,
- term_condition,
- voucher_code_format,
- reservation_day,
- best_deal,
- subtitle,
- direct_entrance,
- ticket_has_commission,
- commission_rate,
- multi_supplier,
- how_to_redeem_description,
- need_self_identifier,
- self_identifier_required_type,
- self_identifier_type,
- price_include,
- price_exclude,
- reservation_time,
- min_reservation_day,
- need_booking_options,
- pushed_to_aggregator
- )
- VALUES (
- exp_ticket_id,
- ticket_spec ->> 'experience_id',
- ticket_spec ->> 'ticket_type',
- ticket_spec ->> 'ticket_barcode_type',
- ticket_spec ->> 'ticket_name',
- ticket_spec ->> 'ticket_description',
- (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
- (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
- (ticket_spec ->> 'maximum_child_size') :: INTEGER,
- (ticket_spec ->> 'minimum_child_size') :: INTEGER,
- case (ticket_spec ->> 'group_size') :: INTEGER
- when 0
- then 1
- else (ticket_spec ->> 'group_size') :: INTEGER end,
- (ticket_spec ->> 'refundable') :: BOOLEAN,
- (ticket_spec ->> 'active') :: BOOLEAN,
- (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
- COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
- COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
- COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
- 'SPECIFIC',
- ticket_spec ->> 'agent_url',
- (ticket_spec ->> 'hold_booking_time') :: BIGINT,
- FALSE,
- CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_type',
- ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_type',
- ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- ticket_spec ->> 'date_of_birth',
- ticket_spec ->> 'reservation',
- ticket_spec ->> 'inventory_type',
- ticket_spec ->> 'voucher_type',
- ticket_spec ->> 'term_condition',
- ticket_spec ->> 'voucher_code_format',
- (ticket_spec ->> 'reservation_day') :: INTEGER,
- (ticket_spec ->> 'best_deal') :: BOOLEAN,
- ticket_spec ->> 'subtitle',
- (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
- COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
- (ticket_spec ->> 'commission_rate') :: REAL,
- (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
- ticket_spec ->> 'how_to_redeem_description',
- COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
- ticket_spec ->> 'self_identifier_required_type',
- ticket_spec ->> 'self_identifier_type',
- ticket_spec ->> 'price_include',
- ticket_spec ->> 'price_exclude',
- ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
- :: experience_extranet_experience.hour_minute,
- COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
- COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
- COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
- );
- FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
- INSERT INTO experience_extranet_ticket.ticket_refund_policy (
- ticket_id,
- num_of_days_prior,
- percentage,
- active
- )
- VALUES (
- exp_ticket_id,
- (policy ->> 'num_of_days_prior') :: INTEGER,
- (policy ->> 'percentage') :: DOUBLE PRECISION,
- (policy ->> 'active') :: BOOLEAN
- );
- END LOOP;
- FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
- INSERT INTO experience_extranet_ticket.ticket_time_slot (
- ticket_id,
- open_time,
- close_time
- )
- VALUES (
- exp_ticket_id,
- row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
- 'minute') :: experience_extranet_experience.hour_minute,
- row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
- 'minute') :: experience_extranet_experience.hour_minute
- );
- END LOOP;
- FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
- FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
- INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
- ticket_id,
- group_unit_id,
- group_unit,
- group_id,
- group_name,
- group_description,
- maximum_size,
- minimum_size,
- quantifier
- )
- VALUES (
- exp_ticket_id,
- COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
- group_list ->> 'group_unit',
- COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
- group_detail_list ->> 'group_name',
- group_detail_list ->> 'group_description',
- (group_detail_list ->> 'maximum_size') :: INTEGER,
- (group_detail_list ->> 'minimum_size') :: INTEGER,
- (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
- );
- END LOOP;
- END LOOP;
- FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
- INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
- redeemable_mapping_id,
- ticket_id,
- experience_id
- )
- VALUES (
- nextval('experience_extranet_ticket.ticket_redeemable_seq'),
- exp_ticket_id,
- redeemable->>'experience_id'
- );
- END LOOP;
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
- IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
- THEN
- PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
- ELSE END IF;
- -- TODO: delete this when proper feature to map SF config is made
- INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
- SELECT ticket_id, 1, now()
- FROM experience_extranet_experience.experience e
- JOIN experience_extranet_ticket.ticket t
- ON e.experience_id = t.experience_id
- WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
- AND t.ticket_id = exp_ticket_id
- ON CONFLICT DO NOTHING;
- RETURN exp_ticket_id;
- END;
- $$;
- CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
- security definer
- language plpgsql
- as $$
- DECLARE
- policy jsonb;
- time_slot jsonb;
- exp_ticket_id TEXT;
- group_list jsonb;
- group_detail_list jsonb;
- redeemable jsonb;
- BEGIN
- exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
- nextval('experience_extranet_ticket.experience_ticket_seq'));
- INSERT INTO experience_extranet_ticket.ticket (
- ticket_id,
- experience_id,
- ticket_type,
- ticket_barcode_type,
- ticket_name,
- ticket_description,
- maximum_adult_size,
- minimum_adult_size,
- maximum_child_size,
- minimum_child_size,
- group_size,
- refundable,
- active,
- total_inventory_qty,
- voucher_format,
- voucher_fulfillment,
- issuance_approval,
- ticket_guest_type,
- agent_url,
- hold_booking_time,
- deleted,
- voucher_usage_validity,
- voucher_sales_validity,
- date_of_birth,
- reservation,
- inventory_type,
- voucher_type,
- term_condition,
- voucher_code_format,
- reservation_day,
- best_deal,
- subtitle,
- direct_entrance,
- ticket_has_commission,
- commission_rate,
- multi_supplier,
- how_to_redeem_description,
- need_self_identifier,
- self_identifier_required_type,
- self_identifier_type,
- price_include,
- price_exclude,
- reservation_time,
- min_reservation_day,
- need_booking_options,
- pushed_to_aggregator
- )
- VALUES (
- exp_ticket_id,
- ticket_spec ->> 'experience_id',
- ticket_spec ->> 'ticket_type',
- ticket_spec ->> 'ticket_barcode_type',
- ticket_spec ->> 'ticket_name',
- ticket_spec ->> 'ticket_description',
- (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
- (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
- (ticket_spec ->> 'maximum_child_size') :: INTEGER,
- (ticket_spec ->> 'minimum_child_size') :: INTEGER,
- case (ticket_spec ->> 'group_size') :: INTEGER
- when 0
- then 1
- else (ticket_spec ->> 'group_size') :: INTEGER end,
- (ticket_spec ->> 'refundable') :: BOOLEAN,
- (ticket_spec ->> 'active') :: BOOLEAN,
- (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
- COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
- COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
- COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
- 'SPECIFIC',
- ticket_spec ->> 'agent_url',
- (ticket_spec ->> 'hold_booking_time') :: BIGINT,
- FALSE,
- CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_type',
- ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
- THEN NULL
- ELSE
- row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_type',
- ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
- 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
- END,
- ticket_spec ->> 'date_of_birth',
- ticket_spec ->> 'reservation',
- ticket_spec ->> 'inventory_type',
- ticket_spec ->> 'voucher_type',
- ticket_spec ->> 'term_condition',
- ticket_spec ->> 'voucher_code_format',
- (ticket_spec ->> 'reservation_day') :: INTEGER,
- (ticket_spec ->> 'best_deal') :: BOOLEAN,
- ticket_spec ->> 'subtitle',
- (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
- COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
- (ticket_spec ->> 'commission_rate') :: REAL,
- (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
- ticket_spec ->> 'how_to_redeem_description',
- COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
- ticket_spec ->> 'self_identifier_required_type',
- ticket_spec ->> 'self_identifier_type',
- ticket_spec ->> 'price_include',
- ticket_spec ->> 'price_exclude',
- ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
- :: experience_extranet_experience.hour_minute,
- COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
- COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
- COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
- );
- FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
- INSERT INTO experience_extranet_ticket.ticket_refund_policy (
- ticket_id,
- num_of_days_prior,
- percentage,
- active
- )
- VALUES (
- exp_ticket_id,
- (policy ->> 'num_of_days_prior') :: INTEGER,
- (policy ->> 'percentage') :: DOUBLE PRECISION,
- (policy ->> 'active') :: BOOLEAN
- );
- END LOOP;
- FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
- INSERT INTO experience_extranet_ticket.ticket_time_slot (
- ticket_id,
- open_time,
- close_time
- )
- VALUES (
- exp_ticket_id,
- row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
- 'minute') :: experience_extranet_experience.hour_minute,
- row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
- 'minute') :: experience_extranet_experience.hour_minute
- );
- END LOOP;
- FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
- FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
- INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
- ticket_id,
- group_unit_id,
- group_unit,
- group_id,
- group_name,
- group_description,
- maximum_size,
- minimum_size,
- quantifier
- )
- VALUES (
- exp_ticket_id,
- COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
- group_list ->> 'group_unit',
- COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
- group_detail_list ->> 'group_name',
- group_detail_list ->> 'group_description',
- (group_detail_list ->> 'maximum_size') :: INTEGER,
- (group_detail_list ->> 'minimum_size') :: INTEGER,
- (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
- );
- END LOOP;
- END LOOP;
- FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
- INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
- redeemable_mapping_id,
- ticket_id,
- experience_id
- )
- VALUES (
- nextval('experience_extranet_ticket.ticket_redeemable_seq'),
- exp_ticket_id,
- redeemable->>'experience_id'
- );
- END LOOP;
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
- PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
- IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
- THEN
- PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
- ELSE END IF;
- -- TODO: delete this when proper feature to map SF config is made
- INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
- SELECT ticket_id, 1, now()
- FROM experience_extranet_experience.experience e
- JOIN experience_extranet_ticket.ticket t
- ON e.experience_id = t.experience_id
- WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
- AND t.ticket_id = exp_ticket_id
- ON CONFLICT DO NOTHING;
- RETURN exp_ticket_id;
- END;
- $$;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement