65 lines
2.1 KiB
PL/PgSQL
65 lines
2.1 KiB
PL/PgSQL
-- Deploy numerus:compute_new_quote_amount to pg
|
|
-- requires: roles
|
|
-- requires: schema_numerus
|
|
-- requires: company
|
|
-- requires: tax
|
|
-- requires: new_quote_product
|
|
-- requires: new_quote_amount
|
|
|
|
begin;
|
|
|
|
set search_path to numerus, public;
|
|
|
|
create or replace function compute_new_quote_amount(company_id integer, products new_quote_product[]) returns new_quote_amount as
|
|
$$
|
|
declare
|
|
result new_quote_amount;
|
|
begin
|
|
if array_length(products, 1) is null then
|
|
select to_price(0, decimal_digits), array[]::text[][], to_price(0, decimal_digits)
|
|
from company
|
|
join currency using (currency_code)
|
|
where company.company_id = compute_new_quote_amount.company_id
|
|
into result.subtotal, result.taxes, result.total;
|
|
else
|
|
with product as (
|
|
select round(parse_price(price, currency.decimal_digits) * quantity * (1 - discount_rate))::integer as subtotal
|
|
, tax
|
|
, decimal_digits
|
|
from unnest(products)
|
|
join company on company.company_id = compute_new_quote_amount.company_id
|
|
join currency using (currency_code)
|
|
)
|
|
, tax_amount as (
|
|
select tax_id
|
|
, sum(round(subtotal * tax.rate)::integer)::integer as amount
|
|
, decimal_digits
|
|
from product, unnest(product.tax) as product_tax(tax_id)
|
|
join tax using (tax_id)
|
|
group by tax_id, decimal_digits
|
|
)
|
|
, tax_total as (
|
|
select sum(amount)::integer as amount, array_agg(array[name, to_price(amount, decimal_digits)]) as taxes
|
|
from tax_amount
|
|
join tax using (tax_id)
|
|
)
|
|
select to_price(sum(subtotal)::integer, decimal_digits)
|
|
, coalesce(taxes, array[]::text[][])
|
|
, to_price(sum(subtotal)::integer + coalesce(tax_total.amount, 0), decimal_digits) as total
|
|
from product, tax_total
|
|
group by tax_total.amount, taxes, decimal_digits
|
|
into result.subtotal, result.taxes, result.total;
|
|
end if;
|
|
|
|
return result;
|
|
end
|
|
$$
|
|
language plpgsql
|
|
stable;
|
|
|
|
revoke execute on function compute_new_quote_amount(integer, new_quote_product[]) from public;
|
|
grant execute on function compute_new_quote_amount(integer, new_quote_product[]) to invoicer;
|
|
grant execute on function compute_new_quote_amount(integer, new_quote_product[]) to admin;
|
|
|
|
commit;
|