90 lines
2.6 KiB
MySQL
90 lines
2.6 KiB
MySQL
|
-- Deploy numerus:add_quote to pg
|
||
|
|
-- requires: roles
|
||
|
|
-- requires: schema_numerus
|
||
|
|
-- requires: quote
|
||
|
|
-- requires: company
|
||
|
|
-- requires: currency
|
||
|
|
-- requires: parse_price
|
||
|
|
-- requires: new_quote_product
|
||
|
|
-- requires: tax
|
||
|
|
-- requires: quote_product
|
||
|
|
-- requires: quote_payment_method
|
||
|
|
-- requires: quote_contact
|
||
|
|
-- requires: quote_product_product
|
||
|
|
-- requires: quote_product_tax
|
||
|
|
-- requires: next_quote_number
|
||
|
|
-- requires: tag_name
|
||
|
|
|
||
|
|
begin;
|
||
|
|
|
||
|
|
set search_path to numerus, public;
|
||
|
|
|
||
|
|
create or replace function add_quote(company integer, quote_date date, contact_id integer, terms_and_conditions text, notes text, payment_method_id integer, tags tag_name[], products new_quote_product[]) returns uuid as
|
||
|
|
$$
|
||
|
|
declare
|
||
|
|
qid integer;
|
||
|
|
qslug uuid;
|
||
|
|
product new_quote_product;
|
||
|
|
ccode text;
|
||
|
|
qpid integer;
|
||
|
|
begin
|
||
|
|
insert into quote (company_id, quote_number, quote_date, terms_and_conditions, notes, tags, currency_code)
|
||
|
|
select company_id
|
||
|
|
, next_quote_number(add_quote.company, quote_date)
|
||
|
|
, quote_date
|
||
|
|
, terms_and_conditions
|
||
|
|
, notes
|
||
|
|
, tags
|
||
|
|
, currency_code
|
||
|
|
from company
|
||
|
|
where company.company_id = add_quote.company
|
||
|
|
returning quote_id, slug, currency_code
|
||
|
|
into qid, qslug, ccode;
|
||
|
|
|
||
|
|
if contact_id is not null then
|
||
|
|
insert into quote_contact (quote_id, contact_id)
|
||
|
|
values (qid, contact_id);
|
||
|
|
end if;
|
||
|
|
|
||
|
|
if payment_method_id is not null then
|
||
|
|
insert into quote_payment_method (quote_id, payment_method_id)
|
||
|
|
values (qid, payment_method_id);
|
||
|
|
end if;
|
||
|
|
|
||
|
|
foreach product in array products
|
||
|
|
loop
|
||
|
|
insert into quote_product (quote_id, name, description, price, quantity, discount_rate)
|
||
|
|
select qid
|
||
|
|
, product.name
|
||
|
|
, coalesce(product.description, '')
|
||
|
|
, parse_price(product.price, currency.decimal_digits)
|
||
|
|
, product.quantity
|
||
|
|
, product.discount_rate
|
||
|
|
from currency
|
||
|
|
where currency_code = ccode
|
||
|
|
returning quote_product_id
|
||
|
|
into qpid;
|
||
|
|
|
||
|
|
if product.product_id is not null then
|
||
|
|
insert into quote_product_product (quote_product_id, product_id)
|
||
|
|
values (qpid, product.product_id);
|
||
|
|
end if;
|
||
|
|
|
||
|
|
insert into quote_product_tax (quote_product_id, tax_id, tax_rate)
|
||
|
|
select qpid, tax_id, tax.rate
|
||
|
|
from tax
|
||
|
|
join unnest(product.tax) as ptax(tax_id) using (tax_id);
|
||
|
|
end loop;
|
||
|
|
|
||
|
|
return qslug;
|
||
|
|
end;
|
||
|
|
$$
|
||
|
|
language plpgsql
|
||
|
|
;
|
||
|
|
|
||
|
|
revoke execute on function add_quote(integer, date, integer, text, text, integer, tag_name[], new_quote_product[]) from public;
|
||
|
|
grant execute on function add_quote(integer, date, integer, text, text, integer, tag_name[], new_quote_product[]) to invoicer;
|
||
|
|
grant execute on function add_quote(integer, date, integer, text, text, integer, tag_name[], new_quote_product[]) to admin;
|
||
|
|
|
||
|
|
commit;
|