69 lines
1.8 KiB
MySQL
69 lines
1.8 KiB
MySQL
|
-- 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;
|