tandem
/
numerus
2
0
Fork 0
Code Issues 39 Pull Requests Packages Projects 1 Releases 1 Wiki Activity
numerus/revert/edit_expense.sql

56 lines
1.6 KiB
MySQL
Raw Permalink Normal View History

Add expenses statuses We only want two statuses for expense: not yet paid (pending), and paid. Thus, it is a bit different from quotes and invoices, because expenses do not pass throw the “workflow” of created→sent→{pending,paid}. That’s way in this case the status field is already in the new expense form, instead of hidden, and by pending is not equivalent to created but unpaid (i.e., the same status color). With the new select field in the form, the file field no longer can span two columns or it would be alone on the next row. Closes #67.
2023-07-11 13:33:26 +00:00
-- Deploy numerus:edit_expense to pg
-- requires: schema_numerus
-- requires: expense
-- requires: currency
-- requires: parse_price
-- requires: tax
-- requires: tag_name
Remove the status parameter from add_expense and edit_expense, and forms Users are no longer expected to manually set the status of an expense and, instead, have to add payments to such expense to mark it as partial or paid. That means that the PL/pgSQL functions must not accept a status parameter, the edit and new forms should no longer have a field for the status, and that the expense list should no longer have the “quick edit” for their status. That’s why it no longer should have a pointer cursor, unlike invoice or quote status.
2024-08-13 00:34:21 +00:00
-- requires: expense_status
-- requires: expense_expense_status
Add SQL function to edit expenses
2023-05-04 10:34:47 +00:00
begin;
Add expenses statuses We only want two statuses for expense: not yet paid (pending), and paid. Thus, it is a bit different from quotes and invoices, because expenses do not pass throw the “workflow” of created→sent→{pending,paid}. That’s way in this case the status field is already in the new expense form, instead of hidden, and by pending is not equivalent to created but unpaid (i.e., the same status color). With the new select field in the form, the file field no longer can span two columns or it would be alone on the next row. Closes #67.
2023-07-11 13:33:26 +00:00
set search_path to numerus, public;
Remove the status parameter from add_expense and edit_expense, and forms Users are no longer expected to manually set the status of an expense and, instead, have to add payments to such expense to mark it as partial or paid. That means that the PL/pgSQL functions must not accept a status parameter, the edit and new forms should no longer have a field for the status, and that the expense list should no longer have the “quick edit” for their status. That’s why it no longer should have a pointer cursor, unlike invoice or quote status.
2024-08-13 00:34:21 +00:00
drop function if exists edit_expense(uuid, date, integer, text, text, integer[], tag_name[]);
Add expenses statuses We only want two statuses for expense: not yet paid (pending), and paid. Thus, it is a bit different from quotes and invoices, because expenses do not pass throw the “workflow” of created→sent→{pending,paid}. That’s way in this case the status field is already in the new expense form, instead of hidden, and by pending is not equivalent to created but unpaid (i.e., the same status color). With the new select field in the form, the file field no longer can span two columns or it would be alone on the next row. Closes #67.
2023-07-11 13:33:26 +00:00
Remove the status parameter from add_expense and edit_expense, and forms Users are no longer expected to manually set the status of an expense and, instead, have to add payments to such expense to mark it as partial or paid. That means that the PL/pgSQL functions must not accept a status parameter, the edit and new forms should no longer have a field for the status, and that the expense list should no longer have the “quick edit” for their status. That’s why it no longer should have a pointer cursor, unlike invoice or quote status.
2024-08-13 00:34:21 +00:00
create or replace function edit_expense(expense_slug uuid, status text, invoice_date date, contact_id integer, invoice_number text, amount text, taxes integer[], tags tag_name[]) returns uuid as
Add expenses statuses We only want two statuses for expense: not yet paid (pending), and paid. Thus, it is a bit different from quotes and invoices, because expenses do not pass throw the “workflow” of created→sent→{pending,paid}. That’s way in this case the status field is already in the new expense form, instead of hidden, and by pending is not equivalent to created but unpaid (i.e., the same status color). With the new select field in the form, the file field no longer can span two columns or it would be alone on the next row. Closes #67.
2023-07-11 13:33:26 +00:00
$$
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)
Remove the status parameter from add_expense and edit_expense, and forms Users are no longer expected to manually set the status of an expense and, instead, have to add payments to such expense to mark it as partial or paid. That means that the PL/pgSQL functions must not accept a status parameter, the edit and new forms should no longer have a field for the status, and that the expense list should no longer have the “quick edit” for their status. That’s why it no longer should have a pointer cursor, unlike invoice or quote status.
2024-08-13 00:34:21 +00:00
, expense_status = status
Add expenses statuses We only want two statuses for expense: not yet paid (pending), and paid. Thus, it is a bit different from quotes and invoices, because expenses do not pass throw the “workflow” of created→sent→{pending,paid}. That’s way in this case the status field is already in the new expense form, instead of hidden, and by pending is not equivalent to created but unpaid (i.e., the same status color). With the new select field in the form, the file field no longer can span two columns or it would be alone on the next row. Closes #67.
2023-07-11 13:33:26 +00:00
, 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;
Remove the status parameter from add_expense and edit_expense, and forms Users are no longer expected to manually set the status of an expense and, instead, have to add payments to such expense to mark it as partial or paid. That means that the PL/pgSQL functions must not accept a status parameter, the edit and new forms should no longer have a field for the status, and that the expense list should no longer have the “quick edit” for their status. That’s why it no longer should have a pointer cursor, unlike invoice or quote status.
2024-08-13 00:34:21 +00:00
revoke execute on function edit_expense(uuid, text, date, integer, text, text, integer[], tag_name[]) from public;
grant execute on function edit_expense(uuid, text, date, integer, text, text, integer[], tag_name[]) to invoicer;
grant execute on function edit_expense(uuid, text, date, integer, text, text, integer[], tag_name[]) to admin;
Add SQL function to edit expenses
2023-05-04 10:34:47 +00:00
commit;