94 lines
3.3 KiB
PL/PgSQL
94 lines
3.3 KiB
PL/PgSQL
-- RPC paginada para Reportes Históricos del Cruce de Cuentas GLM.
|
|
-- Ejecutar una sola vez en el SQL Editor del Supabase empresarial.
|
|
-- La función filtra el país en la base de datos antes de paginar.
|
|
|
|
create index if not exists idx_cruces_cuentas_reportes_country_created
|
|
on public.cruces_cuentas_gt_reportes (country, created_at desc);
|
|
|
|
create index if not exists idx_cruces_cuentas_reportes_country_status_created
|
|
on public.cruces_cuentas_gt_reportes (country, estado, created_at desc);
|
|
|
|
create or replace function public.cruce_cuentas_get_historicos(
|
|
p_country text,
|
|
p_page integer default 1,
|
|
p_page_size integer default 10,
|
|
p_search text default null,
|
|
p_status text default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = public
|
|
as $$
|
|
declare
|
|
v_country text := upper(trim(coalesce(p_country, '')));
|
|
v_page integer := greatest(coalesce(p_page, 1), 1);
|
|
v_page_size integer := least(greatest(coalesce(p_page_size, 10), 1), 100);
|
|
v_offset integer;
|
|
v_search text := nullif(trim(coalesce(p_search, '')), '');
|
|
v_status text := nullif(trim(coalesce(p_status, '')), '');
|
|
v_total bigint;
|
|
v_filtered_total bigint;
|
|
v_reports jsonb;
|
|
begin
|
|
if v_country not in ('GT', 'TT') then
|
|
raise exception 'País no permitido: %', v_country;
|
|
end if;
|
|
|
|
v_offset := (v_page - 1) * v_page_size;
|
|
|
|
select count(*)
|
|
into v_total
|
|
from public.cruces_cuentas_gt_reportes r
|
|
where upper(coalesce(r.country, 'GT')) = v_country;
|
|
|
|
select count(*)
|
|
into v_filtered_total
|
|
from public.cruces_cuentas_gt_reportes r
|
|
where upper(coalesce(r.country, 'GT')) = v_country
|
|
and (v_status is null or r.estado = v_status)
|
|
and (
|
|
v_search is null
|
|
or coalesce(r.period_label, '') ilike '%' || v_search || '%'
|
|
or coalesce(r.payroll_file_name, '') ilike '%' || v_search || '%'
|
|
or coalesce(r.ejecutado_por_nombre, '') ilike '%' || v_search || '%'
|
|
or coalesce(r.resuelto_por_nombre, '') ilike '%' || v_search || '%'
|
|
or r.id::text ilike '%' || v_search || '%'
|
|
);
|
|
|
|
select coalesce(jsonb_agg(to_jsonb(q) order by q.created_at desc), '[]'::jsonb)
|
|
into v_reports
|
|
from (
|
|
select r.*
|
|
from public.cruces_cuentas_gt_reportes r
|
|
where upper(coalesce(r.country, 'GT')) = v_country
|
|
and (v_status is null or r.estado = v_status)
|
|
and (
|
|
v_search is null
|
|
or coalesce(r.period_label, '') ilike '%' || v_search || '%'
|
|
or coalesce(r.payroll_file_name, '') ilike '%' || v_search || '%'
|
|
or coalesce(r.ejecutado_por_nombre, '') ilike '%' || v_search || '%'
|
|
or coalesce(r.resuelto_por_nombre, '') ilike '%' || v_search || '%'
|
|
or r.id::text ilike '%' || v_search || '%'
|
|
)
|
|
order by r.created_at desc
|
|
limit v_page_size
|
|
offset v_offset
|
|
) q;
|
|
|
|
return jsonb_build_object(
|
|
'ok', true,
|
|
'country', v_country,
|
|
'page', v_page,
|
|
'page_size', v_page_size,
|
|
'total', v_total,
|
|
'filtered_total', v_filtered_total,
|
|
'total_pages', greatest(ceil(v_filtered_total::numeric / v_page_size)::integer, 1),
|
|
'reports', v_reports
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.cruce_cuentas_get_historicos(text, integer, integer, text, text) from public;
|
|
grant execute on function public.cruce_cuentas_get_historicos(text, integer, integer, text, text) to authenticated;
|