numerus/deploy/edit_quote.sql

-- Deploy numerus:edit_quote to pg
-- requires: roles
-- requires: schema_numerus
-- requires: quote
-- requires: currency
-- requires: parse_price
-- requires: edited_quote_product
-- requires: tax
-- requires: quote_contact
-- requires: quote_payment_method
-- requires: quote_product
-- requires: quote_product_tax
-- requires: quote_product_product
-- requires: tag_name

begin;

set search_path to numerus, public;

create or replace function edit_quote(quote_slug uuid, quote_status text, contact_id integer, terms_and_conditions text, notes text, payment_method_id integer, tags tag_name[], products edited_quote_product[]) returns uuid as
$$
declare
	qid integer;
	products_to_keep integer[];
	products_to_delete integer[];
	company integer;
	ccode text;
	product edited_quote_product;
	qpid integer;
begin
	update quote
	set quote_status = edit_quote.quote_status
	  , terms_and_conditions = edit_quote.terms_and_conditions
	  , notes = edit_quote.notes
	-- contact_id = edit_quote.contact_id
	  --, payment_method_id = edit_quote.payment_method_id
	  , tags = edit_quote.tags
	where slug = quote_slug
	returning quote_id, company_id, currency_code
	into qid, company, ccode
	;

	if qid is null then
		return null;
	end if;

	if payment_method_id is null then
		delete from quote_payment_method where quote_id = qid;
	else
		insert into quote_payment_method (quote_id, payment_method_id)
		values (qid, payment_method_id)
		on conflict (quote_id) do update
		set payment_method_id = edit_quote.payment_method_id;
	end if;

	if contact_id is null then
		delete from quote_contact where quote_id = qid;
	else
		insert into quote_contact (quote_id, contact_id)
		values (qid, contact_id)
		on conflict (quote_id) do update
		set contact_id = edit_quote.contact_id;
	end if;

	foreach product in array products
	loop
		if product.quote_product_id is null then
			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;
		else
			qpid := product.quote_product_id;

			update quote_product
			set name = product.name
			  , description = coalesce(product.description, '')
			  , price = parse_price(product.price, currency.decimal_digits)
			  , quantity = product.quantity
			  , discount_rate = product.discount_rate
			from currency
			where quote_product_id = qpid
			  and currency_code = ccode;
		end if;
		products_to_keep := array_append(products_to_keep, qpid);

		if product.product_id is null then
			delete from quote_product_product where quote_product_id = qpid;
		else
			insert into quote_product_product (quote_product_id, product_id)
			values (qpid, product.product_id)
			on conflict (quote_product_id) do update
			set product_id = product.product_id;
		end if;

		delete from quote_product_tax where quote_product_id = qpid;

		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;

	select array_agg(quote_product_id)
	into products_to_delete
	from quote_product
	where quote_id = qid
	  and not (quote_product_id = any(products_to_keep));

	if array_length(products_to_delete, 1) > 0 then
		delete from quote_product_tax where quote_product_id = any(products_to_delete);
		delete from quote_product_product where quote_product_id = any(products_to_delete);
		delete from quote_product where quote_product_id = any(products_to_delete);
	end if;

	return quote_slug;
end;
$$
language plpgsql;

revoke execute on function edit_quote(uuid, text, integer, text, text, integer, tag_name[], edited_quote_product[]) from public;
grant execute on function edit_quote(uuid, text, integer, text, text, integer, tag_name[], edited_quote_product[]) to invoicer;
grant execute on function edit_quote(uuid, text, integer, text, text, integer, tag_name[], edited_quote_product[]) to admin;

commit;