74 lines
2.2 KiB
PL/PgSQL
74 lines
2.2 KiB
PL/PgSQL
-- Seguimiento de Impuestos GLM
|
|
-- Actualización para consultar, editar o eliminar eventos desde la app.
|
|
-- Los eventos quedan bloqueados automáticamente después del primer aviso enviado.
|
|
-- Ejecutar una sola vez si ya se ejecutó el script completo anteriormente.
|
|
|
|
begin;
|
|
|
|
create or replace function public.tax_obligation_delivery_status()
|
|
returns table (
|
|
obligation_id uuid,
|
|
has_sent_notification boolean,
|
|
first_sent_at timestamptz,
|
|
sent_notification_count bigint
|
|
)
|
|
language sql
|
|
stable
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
select
|
|
obligation.id as obligation_id,
|
|
count(log.id) filter (where log.status = 'sent') > 0 as has_sent_notification,
|
|
min(log.sent_at) filter (where log.status = 'sent') as first_sent_at,
|
|
count(log.id) filter (where log.status = 'sent') as sent_notification_count
|
|
from public.tax_obligations obligation
|
|
left join public.tax_notification_log log
|
|
on log.obligation_id = obligation.id
|
|
where obligation.active = true
|
|
and public.has_tax_calendar_access()
|
|
group by obligation.id;
|
|
$$;
|
|
|
|
revoke all on function public.tax_obligation_delivery_status()
|
|
from public, anon;
|
|
grant execute on function public.tax_obligation_delivery_status()
|
|
to authenticated;
|
|
|
|
create or replace function public.tax_prevent_sent_obligation_changes()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
if exists (
|
|
select 1
|
|
from public.tax_notification_log log
|
|
where log.obligation_id = old.id
|
|
and log.status = 'sent'
|
|
) and (
|
|
new.due_date is distinct from old.due_date
|
|
or new.description is distinct from old.description
|
|
or new.category is distinct from old.category
|
|
or new.country_id is distinct from old.country_id
|
|
or new.active is distinct from old.active
|
|
) then
|
|
raise exception using
|
|
errcode = 'P0001',
|
|
message = 'Esta obligación ya generó avisos y no puede modificarse ni eliminarse.';
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists tax_obligations_lock_after_sent_trigger
|
|
on public.tax_obligations;
|
|
|
|
create trigger tax_obligations_lock_after_sent_trigger
|
|
before update on public.tax_obligations
|
|
for each row execute function public.tax_prevent_sent_obligation_changes();
|
|
|
|
commit;
|