mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
71 lines
2.7 KiB
SQL
71 lines
2.7 KiB
SQL
-- 1) Link budgets to chart of accounts (optional)
|
|
ALTER TABLE public.budgets
|
|
ADD COLUMN IF NOT EXISTS gl_account_id UUID REFERENCES public.chart_of_accounts(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_budgets_gl_account ON public.budgets(gl_account_id);
|
|
|
|
-- 2) Monthly actuals view: union of expense + income transactions
|
|
DROP VIEW IF EXISTS public.budget_actuals_monthly CASCADE;
|
|
|
|
CREATE VIEW public.budget_actuals_monthly
|
|
WITH (security_invoker = true)
|
|
AS
|
|
-- Expenses from bills (use expense_account_id when present)
|
|
SELECT
|
|
b.association_id,
|
|
date_trunc('month', b.bill_date)::date AS period_month,
|
|
'expense'::text AS account_type,
|
|
b.expense_account_id AS gl_account_id,
|
|
coa.account_name AS category_name,
|
|
SUM(COALESCE(b.amount, 0))::numeric AS amount
|
|
FROM public.bills b
|
|
LEFT JOIN public.chart_of_accounts coa ON coa.id = b.expense_account_id
|
|
WHERE b.status IN ('paid', 'approved', 'partially_paid')
|
|
AND b.bill_date IS NOT NULL
|
|
GROUP BY b.association_id, date_trunc('month', b.bill_date), b.expense_account_id, coa.account_name
|
|
|
|
UNION ALL
|
|
|
|
-- Expenses from billable_expenses (categorised line items)
|
|
SELECT
|
|
be.association_id,
|
|
date_trunc('month', be.date)::date AS period_month,
|
|
'expense'::text AS account_type,
|
|
NULL::uuid AS gl_account_id,
|
|
COALESCE(be.category, 'Uncategorized') AS category_name,
|
|
SUM(COALESCE(be.amount, 0))::numeric AS amount
|
|
FROM public.billable_expenses be
|
|
WHERE be.date IS NOT NULL
|
|
AND COALESCE(be.is_credit, false) = false
|
|
GROUP BY be.association_id, date_trunc('month', be.date), be.category
|
|
|
|
UNION ALL
|
|
|
|
-- Income from admin payments (owner payments recorded directly)
|
|
SELECT
|
|
ap.association_id,
|
|
date_trunc('month', COALESCE(ap.payment_date, ap.created_at::date))::date AS period_month,
|
|
'income'::text AS account_type,
|
|
NULL::uuid AS gl_account_id,
|
|
'Owner Payments'::text AS category_name,
|
|
SUM(COALESCE(ap.amount, 0))::numeric AS amount
|
|
FROM public.admin_payments ap
|
|
WHERE ap.status IN ('completed', 'paid', 'success')
|
|
GROUP BY ap.association_id, date_trunc('month', COALESCE(ap.payment_date, ap.created_at::date))
|
|
|
|
UNION ALL
|
|
|
|
-- Income from client invoices (paid)
|
|
SELECT
|
|
ci.association_id,
|
|
date_trunc('month', COALESCE(ci.paid_date, ci.issue_date))::date AS period_month,
|
|
'income'::text AS account_type,
|
|
NULL::uuid AS gl_account_id,
|
|
'Invoiced Income'::text AS category_name,
|
|
SUM(COALESCE(ci.total, ci.amount, 0))::numeric AS amount
|
|
FROM public.client_invoices ci
|
|
WHERE ci.status IN ('paid', 'partially_paid')
|
|
AND COALESCE(ci.paid_date, ci.issue_date) IS NOT NULL
|
|
GROUP BY ci.association_id, date_trunc('month', COALESCE(ci.paid_date, ci.issue_date));
|
|
|
|
GRANT SELECT ON public.budget_actuals_monthly TO authenticated, anon, service_role; |