69 lines
1.8 KiB
PL/PgSQL
69 lines
1.8 KiB
PL/PgSQL
-- Deploy numerus:add_collection to pg
|
||
-- requires: roles
|
||
-- requires: schema_numerus
|
||
-- requires: collection
|
||
-- requires: invoice_collection
|
||
-- requires: company
|
||
-- requires: currency
|
||
-- requires: parse_price
|
||
-- requires: tag_name
|
||
-- requires: update_invoice_collection_status
|
||
|
||
begin;
|
||
|
||
set search_path to numerus, public;
|
||
|
||
create or replace function add_collection(company integer, invoice_id integer, collection_date date, payment_account_id integer, description text, amount text, tags tag_name[]) returns uuid as
|
||
$$
|
||
declare
|
||
cid integer;
|
||
cslug uuid;
|
||
amount_cents integer;
|
||
begin
|
||
insert into collection
|
||
( company_id
|
||
, payment_account_id
|
||
, description
|
||
, collection_date
|
||
, amount
|
||
, currency_code
|
||
, payment_status
|
||
, tags
|
||
)
|
||
select company_id
|
||
, payment_account_id
|
||
, description
|
||
, collection_date
|
||
, parse_price(amount, currency.decimal_digits)
|
||
, currency_code
|
||
, 'complete'
|
||
, tags
|
||
from company
|
||
join currency using (currency_code)
|
||
where company.company_id = add_collection.company
|
||
returning collection_id, slug, collection.amount
|
||
into cid, cslug, amount_cents
|
||
;
|
||
|
||
if invoice_id is not null then
|
||
-- must be inserted before updating statuses, so that it can see this
|
||
-- collection’s amount too.
|
||
insert into invoice_collection (invoice_id, collection_id)
|
||
values (invoice_id, cid)
|
||
;
|
||
|
||
perform update_invoice_collection_status(cid, invoice_id, amount_cents);
|
||
end if;
|
||
|
||
return cslug;
|
||
end
|
||
$$
|
||
language plpgsql
|
||
;
|
||
|
||
revoke execute on function add_collection(integer, integer, date, integer, text, text, tag_name[]) from public;
|
||
grant execute on function add_collection(integer, integer, date, integer, text, text, tag_name[]) to invoicer;
|
||
grant execute on function add_collection(integer, integer, date, integer, text, text, tag_name[]) to admin;
|
||
|
||
commit;
|