Jakob Wennberg
91e2c1705a
Per-line VAT rates: - Add generatePerRateLines() to group invoice items by vat_rate with separate revenue + VAT lines per rate group (invoice-entries.ts) - Add getAvailableVatRates() and getVatTreatmentForRate() (vat-rules.ts) - PDF template shows per-line VAT column and per-rate totals for mixed-rate invoices - Invoice create/review UI supports per-line rate selection - Types: add vat_rate/vat_amount to InvoiceItem, vat_rate to CreateInvoiceItemInput Invoice document types (proforma, delivery note): - Add InvoiceDocumentType, document_type and converted_from_id to Invoice type - PDF hides prices for delivery notes, adds proforma notice - Email templates support all document types - mark-paid skips journal entries for non-invoice document types - Migration 031: invoice_document_type Accounting method support: - Add AccountingMethod type (accrual/cash) - Migration 032: add_accounting_method column to company_settings VAT declaration rewrite: - Rewrite to read directly from general ledger (26xx/3xxx account lines) instead of aggregating invoices/transactions/receipts - ACCOUNT_RUTA mapping drives momsdeklaration boxes from GL balances Bank reconciliation: - Transaction ingest now pre-fetches unlinked GL lines and attempts auto-reconciliation during import - Add transaction.reconciled event type - Add ReconciliationMethod type and reconciliation_method on Transaction - Migration 030: bank_reconciliation - New reconciliation engine, API routes, and BankReconciliationView component Pagination (fetchAllRows): - New lib/supabase/fetch-all.ts overcomes PostgREST 1000-row limit - Adopted in all report generators, SIE/SRU export, account list APIs Fiscal period validation: - New validate-period-duration.ts enforces max 18 months per BFL 3 kap. - Applied in period-service.ts and fiscal-periods API Account mapper simplification: - Remove Levenshtein/fuzzy matching, use exact account number match only Swedbank parser improvements: - Support abbreviated headers (Clnr, Bokfdag, Radnr) - Use Referens column as counterparty Chart of accounts management: - Add DELETE endpoint with system account and usage protection - PUT uses partial updates - New AccountCombobox, AddAccountDialog, EditAccountDialog, ChartOfAccountsManager Tax deadline corrections: - Rewrite inkomstdeklaration_ab using Skatteverket lookup table - Rewrite arsredovisning deadline to 7 months after FY end per ÅRL 8:3 Onboarding first fiscal year: - Add first fiscal year toggle with date pickers and 18-month validation UI terminology: - Change "okategoriserad/kategorisera" to "obokförd/bokföra" throughout Report column fix: - Fix start_date/end_date to period_start/period_end in report queries Supplier invoice input: - CreateSupplierInvoiceItemInput uses amount field (legacy quantity/unit_price kept) Misc: - SIE import uses upsert for idempotent account creation - account-descriptions.ts falls back to BAS reference data - Add invoice_default_notes to CompanySettings - Update CLAUDE.md to reflect current project state Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
71 lines
2.1 KiB
PL/PgSQL
71 lines
2.1 KiB
PL/PgSQL
-- Bank reconciliation support
|
|
-- Adds reconciliation_method to transactions, indexes for fast lookups,
|
|
-- and an RPC function to find unlinked 1930 journal entry lines.
|
|
|
|
-- Add reconciliation_method column to transactions
|
|
ALTER TABLE public.transactions
|
|
ADD COLUMN IF NOT EXISTS reconciliation_method TEXT
|
|
CHECK (reconciliation_method IN (
|
|
'auto_exact', 'auto_date_range', 'auto_reference', 'auto_fuzzy', 'manual'
|
|
));
|
|
|
|
-- Index for fast lookup of unreconciled transactions (no journal entry linked)
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_unmatched
|
|
ON public.transactions (user_id, date)
|
|
WHERE journal_entry_id IS NULL;
|
|
|
|
-- Index for fast lookup of bank account GL lines
|
|
CREATE INDEX IF NOT EXISTS idx_journal_entry_lines_1930
|
|
ON public.journal_entry_lines (account_number, journal_entry_id)
|
|
WHERE account_number = '1930';
|
|
|
|
-- RPC function: returns posted journal entry lines on account 1930
|
|
-- that have no linked transaction (i.e., unreconciled GL lines).
|
|
CREATE OR REPLACE FUNCTION public.get_unlinked_1930_lines(
|
|
p_user_id UUID,
|
|
p_date_from DATE DEFAULT NULL,
|
|
p_date_to DATE DEFAULT NULL
|
|
)
|
|
RETURNS TABLE (
|
|
line_id UUID,
|
|
journal_entry_id UUID,
|
|
debit_amount NUMERIC,
|
|
credit_amount NUMERIC,
|
|
line_description TEXT,
|
|
entry_date DATE,
|
|
voucher_number INT,
|
|
voucher_series TEXT,
|
|
entry_description TEXT,
|
|
source_type TEXT
|
|
)
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT
|
|
jel.id AS line_id,
|
|
je.id AS journal_entry_id,
|
|
jel.debit_amount,
|
|
jel.credit_amount,
|
|
jel.line_description,
|
|
je.entry_date,
|
|
je.voucher_number,
|
|
je.voucher_series,
|
|
je.description AS entry_description,
|
|
je.source_type
|
|
FROM public.journal_entry_lines jel
|
|
JOIN public.journal_entries je ON je.id = jel.journal_entry_id
|
|
WHERE jel.account_number = '1930'
|
|
AND je.user_id = p_user_id
|
|
AND je.status = 'posted'
|
|
AND (p_date_from IS NULL OR je.entry_date >= p_date_from)
|
|
AND (p_date_to IS NULL OR je.entry_date <= p_date_to)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM public.transactions t
|
|
WHERE t.journal_entry_id = je.id
|
|
AND t.user_id = p_user_id
|
|
)
|
|
ORDER BY je.entry_date, je.voucher_number;
|
|
$$;
|