camper/deploy/add_booking_from_payment.sql

109 lines
2.2 KiB
PL/PgSQL

-- Deploy camper:add_booking_from_payment to pg
-- requires: roles
-- requires: schema_camper
-- requires: booking
-- requires: booking__payment_fields
-- requires: booking__stay
-- requires: booking_option
-- requires: payment
-- requires: payment__acsi_card
-- requires: payment_customer
-- requires: payment_option
begin;
set search_path to camper, public;
create or replace function add_booking_from_payment(payment_slug uuid) returns integer as
$$
declare
bid integer;
begin
insert into booking
( company_id
, campsite_type_id
, stay
, subtotal_nights
, number_adults
, subtotal_adults
, number_teenagers
, subtotal_teenagers
, number_children
, subtotal_children
, number_dogs
, subtotal_dogs
, subtotal_tourist_tax
, total
, currency_code
, zone_preferences
, acsi_card
, holder_name
, address
, postal_code
, city
, country_code
, email
, phone
, lang_tag
)
select company_id
, campsite_type_id
, daterange(arrival_date, departure_date)
, subtotal_nights
, number_adults
, subtotal_adults
, number_teenagers
, subtotal_teenagers
, number_children
, subtotal_children
, number_dogs
, subtotal_dogs
, subtotal_tourist_tax
, total
, currency_code
, zone_preferences
, acsi_card
, coalesce(full_name, 'Unknown')
, address
, postal_code
, city
, country_code
, email
, phone
, coalesce(lang_tag, 'und')
from payment
left join payment_customer using (payment_id)
where payment.slug = payment_slug
returning booking_id into bid;
if bid is null then
raise invalid_parameter_value using message = payment_slug || ' is not a valid payment.';
end if;
insert into booking_option
( booking_id
, campsite_type_option_id
, units
, subtotal
)
select bid
, campsite_type_option_id
, units
, subtotal
from payment_option
join payment using (payment_id)
where payment.slug = payment_slug
;
return bid;
end
$$
language plpgsql
;
revoke execute on function add_booking_from_payment(uuid) from public;
grant execute on function add_booking_from_payment(uuid) to employee;
grant execute on function add_booking_from_payment(uuid) to admin;
commit;