56 lines
1.6 KiB
MySQL
56 lines
1.6 KiB
MySQL
|
-- Deploy numerus:add_expense to pg
|
||
|
|
-- requires: schema_numerus
|
||
|
|
-- requires: expense
|
||
|
|
-- requires: expense_tax
|
||
|
|
-- requires: tax
|
||
|
|
-- requires: company
|
||
|
|
-- requires: currency
|
||
|
|
-- requires: parse_price
|
||
|
|
-- requires: tax
|
||
|
|
-- requires: tag_name
|
||
|
|
-- requires: expense_status
|
||
|
|
-- requires: expense_expense_status
|
||
|
|
|
||
|
|
begin;
|
||
|
|
|
||
|
|
set search_path to numerus, public;
|
||
|
|
|
||
|
|
create or replace function add_expense(company integer, status text, invoice_date date, contact_id integer, invoice_number text, amount text, taxes integer[], tags tag_name[]) returns uuid as
|
||
|
|
$$
|
||
|
|
declare
|
||
|
|
eid integer;
|
||
|
|
eslug uuid;
|
||
|
|
begin
|
||
|
|
insert into expense (company_id, contact_id, invoice_number, invoice_date, amount, currency_code, expense_status, tags)
|
||
|
|
select company_id
|
||
|
|
, contact_id
|
||
|
|
, invoice_number
|
||
|
|
, invoice_date
|
||
|
|
, parse_price(amount, currency.decimal_digits)
|
||
|
|
, currency_code
|
||
|
|
, status
|
||
|
|
, tags
|
||
|
|
from company
|
||
|
|
join currency using (currency_code)
|
||
|
|
where company.company_id = add_expense.company
|
||
|
|
returning expense_id, slug
|
||
|
|
into eid, eslug;
|
||
|
|
|
||
|
|
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 eslug;
|
||
|
|
end;
|
||
|
|
$$
|
||
|
|
language plpgsql;
|
||
|
|
|
||
|
|
revoke execute on function add_expense(integer, text, date, integer, text, text, integer[], tag_name[]) from public;
|
||
|
|
grant execute on function add_expense(integer, text, date, integer, text, text, integer[], tag_name[]) to invoicer;
|
||
|
|
grant execute on function add_expense(integer, text, date, integer, text, text, integer[], tag_name[]) to admin;
|
||
|
|
|
||
|
|
drop function if exists add_expense(integer, date, integer, text, text, integer[], tag_name[]);
|
||
|
|
|
||
|
|
commit;
|