51 lines
1.4 KiB
MySQL
51 lines
1.4 KiB
MySQL
|
-- Deploy numerus:edit_expense to pg
|
||
|
|
-- requires: schema_numerus
|
||
|
|
-- requires: expense
|
||
|
|
-- requires: currency
|
||
|
|
-- requires: parse_price
|
||
|
|
-- requires: tax
|
||
|
|
-- requires: tag_name
|
||
|
|
|
||
|
|
begin;
|
||
|
|
|
||
|
|
set search_path to numerus, public;
|
||
|
|
|
||
|
|
create or replace function edit_expense(expense_slug uuid, invoice_date date, contact_id integer, invoice_number text, amount text, taxes integer[], tags tag_name[]) returns uuid as
|
||
|
|
$$
|
||
|
|
declare
|
||
|
|
eid integer;
|
||
|
|
begin
|
||
|
|
update expense
|
||
|
|
set invoice_date = edit_expense.invoice_date
|
||
|
|
, contact_id = edit_expense.contact_id
|
||
|
|
, invoice_number = edit_expense.invoice_number
|
||
|
|
, amount = parse_price(edit_expense.amount, decimal_digits)
|
||
|
|
, tags = edit_expense.tags
|
||
|
|
from currency
|
||
|
|
where slug = expense_slug
|
||
|
|
and currency.currency_code = expense.currency_code
|
||
|
|
returning expense_id
|
||
|
|
into eid;
|
||
|
|
|
||
|
|
if eid is null then
|
||
|
|
return null;
|
||
|
|
end if;
|
||
|
|
|
||
|
|
delete from expense_tax where expense_id = eid;
|
||
|
|
|
||
|
|
insert into expense_tax (expense_id, tax_id, tax_rate)
|
||
|
|
select eid, tax_id, tax.rate
|
||
|
|
from tax
|
||
|
|
join unnest(taxes) as etax(tax_id) using (tax_id);
|
||
|
|
|
||
|
|
return expense_slug;
|
||
|
|
end;
|
||
|
|
$$
|
||
|
|
language plpgsql;
|
||
|
|
|
||
|
|
revoke execute on function edit_expense(uuid, date, integer, text, text, integer[], tag_name[]) from public;
|
||
|
|
grant execute on function edit_expense(uuid, date, integer, text, text, integer[], tag_name[]) to invoicer;
|
||
|
|
grant execute on function edit_expense(uuid, date, integer, text, text, integer[], tag_name[]) to admin;
|
||
|
|
|
||
|
|
commit;
|