mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
84 lines
2.4 KiB
PL/PgSQL
84 lines
2.4 KiB
PL/PgSQL
|
|
-- Function: recompute bill status from its approvals
|
|
CREATE OR REPLACE FUNCTION public.recompute_bill_status_from_approvals()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
DECLARE
|
|
v_bill_id uuid;
|
|
v_total int;
|
|
v_approved int;
|
|
v_denied int;
|
|
v_current_status text;
|
|
v_new_status text;
|
|
BEGIN
|
|
v_bill_id := COALESCE(NEW.bill_id, OLD.bill_id);
|
|
IF v_bill_id IS NULL THEN
|
|
RETURN COALESCE(NEW, OLD);
|
|
END IF;
|
|
|
|
SELECT COUNT(*),
|
|
COUNT(*) FILTER (WHERE status = 'approved'),
|
|
COUNT(*) FILTER (WHERE status = 'denied')
|
|
INTO v_total, v_approved, v_denied
|
|
FROM public.bill_approvals
|
|
WHERE bill_id = v_bill_id;
|
|
|
|
IF v_total = 0 THEN
|
|
RETURN COALESCE(NEW, OLD);
|
|
END IF;
|
|
|
|
SELECT status INTO v_current_status FROM public.bills WHERE id = v_bill_id;
|
|
|
|
IF v_denied > 0 THEN
|
|
v_new_status := 'denied';
|
|
ELSIF v_approved = v_total THEN
|
|
v_new_status := 'approved';
|
|
ELSE
|
|
v_new_status := NULL;
|
|
END IF;
|
|
|
|
IF v_new_status IS NOT NULL
|
|
AND v_current_status IS DISTINCT FROM v_new_status
|
|
AND COALESCE(v_current_status, '') NOT IN ('paid', 'cancelled') THEN
|
|
UPDATE public.bills
|
|
SET status = v_new_status,
|
|
updated_at = now(),
|
|
approved_date = CASE WHEN v_new_status = 'approved'
|
|
THEN COALESCE(approved_date, CURRENT_DATE)
|
|
ELSE approved_date END
|
|
WHERE id = v_bill_id;
|
|
END IF;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_bill_approvals_recompute_status ON public.bill_approvals;
|
|
CREATE TRIGGER trg_bill_approvals_recompute_status
|
|
AFTER INSERT OR UPDATE OF status OR DELETE ON public.bill_approvals
|
|
FOR EACH ROW EXECUTE FUNCTION public.recompute_bill_status_from_approvals();
|
|
|
|
-- Backfill existing bills whose approval state is already complete
|
|
UPDATE public.bills b
|
|
SET status = 'approved',
|
|
approved_date = COALESCE(b.approved_date, CURRENT_DATE),
|
|
updated_at = now()
|
|
WHERE b.status NOT IN ('approved', 'denied', 'paid', 'cancelled')
|
|
AND EXISTS (SELECT 1 FROM public.bill_approvals ba WHERE ba.bill_id = b.id)
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM public.bill_approvals ba
|
|
WHERE ba.bill_id = b.id AND ba.status <> 'approved'
|
|
);
|
|
|
|
UPDATE public.bills b
|
|
SET status = 'denied',
|
|
updated_at = now()
|
|
WHERE b.status NOT IN ('denied', 'paid', 'cancelled')
|
|
AND EXISTS (
|
|
SELECT 1 FROM public.bill_approvals ba
|
|
WHERE ba.bill_id = b.id AND ba.status = 'denied'
|
|
);
|