mirror of
https://origin.cursor.com/mrdevmx/panels.git
synced 2026-10-09 12:03:17 +00:00
e89b51bf43
La vigencia de 3 meses para alta o reingreso cuenta desde la fecha de carga. Quien recibe el expediente revisa si el documento es válido. Co-authored-by: alberto.martinez <alberto.martinez@mrdev.mx>
98 lines
2.6 KiB
PL/PgSQL
98 lines
2.6 KiB
PL/PgSQL
--liquibase formatted sql
|
|
-- PANELS · core · vigencia documental desde la fecha de carga
|
|
|
|
--changeset panel:core-032a-doc-types-no-required-dates endDelimiter:; splitStatements:true
|
|
UPDATE core.document_types
|
|
SET requires_issued_at = false,
|
|
requires_expires_at = false;
|
|
|
|
--changeset panel:core-032b-fn-worker-eval-doc-validity splitStatements:false
|
|
CREATE OR REPLACE FUNCTION core._worker_eval_doc_validity(
|
|
p_policy jsonb,
|
|
p_doc jsonb,
|
|
p_ctx jsonb,
|
|
p_today date
|
|
)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
IMMUTABLE
|
|
AS $$
|
|
DECLARE
|
|
v_mode text := COALESCE(p_policy->>'validity_mode', 'none');
|
|
v_issued date;
|
|
v_expires date;
|
|
v_uploaded date;
|
|
v_need_fresh boolean;
|
|
v_days integer;
|
|
BEGIN
|
|
IF p_doc IS NULL THEN
|
|
RETURN jsonb_build_object(
|
|
'present', false,
|
|
'valid', false,
|
|
'validity_status', 'missing',
|
|
'issued_at', NULL,
|
|
'expires_at', NULL
|
|
);
|
|
END IF;
|
|
v_issued := NULLIF(p_doc->>'issued_at', '')::date;
|
|
v_expires := NULLIF(p_doc->>'expires_at', '')::date;
|
|
BEGIN
|
|
v_uploaded := COALESCE(
|
|
NULLIF(left(p_doc->>'uploaded_at', 10), '')::date,
|
|
NULL
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
v_uploaded := NULL;
|
|
END;
|
|
v_need_fresh := core._worker_freshness_required(p_ctx);
|
|
IF v_mode = 'expiry' THEN
|
|
IF v_expires IS NOT NULL AND v_expires < p_today THEN
|
|
RETURN jsonb_build_object(
|
|
'present', true,
|
|
'valid', false,
|
|
'validity_status', 'expired',
|
|
'issued_at', v_issued,
|
|
'expires_at', v_expires
|
|
);
|
|
END IF;
|
|
RETURN jsonb_build_object(
|
|
'present', true,
|
|
'valid', true,
|
|
'validity_status', 'ok',
|
|
'issued_at', v_issued,
|
|
'expires_at', v_expires
|
|
);
|
|
END IF;
|
|
IF v_mode = 'freshness' THEN
|
|
IF v_need_fresh AND v_uploaded IS NOT NULL THEN
|
|
v_days := COALESCE((p_policy->>'freshness_days')::integer, 90);
|
|
IF (p_today - v_uploaded) > v_days THEN
|
|
RETURN jsonb_build_object(
|
|
'present', true,
|
|
'valid', false,
|
|
'validity_status', 'stale_for_alta',
|
|
'issued_at', v_issued,
|
|
'expires_at', v_expires
|
|
);
|
|
END IF;
|
|
END IF;
|
|
RETURN jsonb_build_object(
|
|
'present', true,
|
|
'valid', true,
|
|
'validity_status', 'ok',
|
|
'issued_at', v_issued,
|
|
'expires_at', v_expires
|
|
);
|
|
END IF;
|
|
RETURN jsonb_build_object(
|
|
'present', true,
|
|
'valid', true,
|
|
'validity_status', 'ok',
|
|
'issued_at', v_issued,
|
|
'expires_at', v_expires
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:core-032c-grants endDelimiter:; splitStatements:true
|
|
GRANT EXECUTE ON FUNCTION core._worker_eval_doc_validity(jsonb, jsonb, jsonb, date) TO panels_core_app;
|