accounts_payable

SELECT 
    data_project_number,
    data_project_label,
    COUNT(DISTINCT data_invoice_number) AS anzahl_rechnungen,
    COUNT(*) AS anzahl_positionen,
    SUM(data_invoice_position_netto) AS kosten_netto,
    SUM(data_invoice_position_brutto) AS kosten_brutto
FROM accounts_payable
WHERE data_project_number IS NOT NULL
GROUP BY data_project_number, data_project_label
ORDER BY kosten_brutto DESC;

SELECT 
    data_supplier_number,
    data_supplier_label,
    MAX(data_invoice_date) AS letzte_rechnung,
    COUNT(DISTINCT data_invoice_number) AS anzahl_rechnungen,
    SUM(data_invoice_position_netto) AS summe_netto,
    -- Offene Beträge: MAX() verwenden, da pro Rechnung identisch
    SUM(DISTINCT CASE 
        WHEN data_invoice_state = 'open' 
        THEN data_invoice_payed_open 
        ELSE 0 
    END) AS offene_betraege
FROM accounts_payable
GROUP BY data_supplier_number, data_supplier_label
ORDER BY summe_netto DESC;

SELECT 
    data_invoice_position_budget AS budget,
    data_invoice_position_cost_center AS kostenstelle,
    COUNT(*) AS anzahl_positionen,
    SUM(data_invoice_position_netto) AS kosten_gesamt,
    SUM(CASE 
        WHEN data_invoice_position_cleared = true 
        THEN data_invoice_position_netto 
        ELSE 0 
    END) AS kosten_abgerechnet,
    SUM(CASE 
        WHEN data_invoice_position_cleared = false 
        THEN data_invoice_position_netto 
        ELSE 0 
    END) AS kosten_nicht_abgerechnet
FROM accounts_payable
WHERE data_invoice_position_budget IS NOT NULL
GROUP BY data_invoice_position_budget, data_invoice_position_cost_center
ORDER BY kosten_gesamt DESC;

SELECT 
    data_client_number,
    data_client_label,
    DATE_TRUNC('month', data_day_date) AS monat,
    COUNT(*) AS positionen,
    SUM(data_invoice_position_netto) AS kosten_netto
FROM accounts_payable
WHERE data_day_date >= '2024-01-01'
GROUP BY 
    data_client_number, 
    data_client_label,
    DATE_TRUNC('month', data_day_date)
ORDER BY monat, kosten_netto DESC;

SELECT 
    data_project_number,
    data_project_label,
    COUNT(*) AS positionen_gesamt,
    SUM(CASE WHEN data_invoice_position_clearable = true THEN 1 ELSE 0 END) AS positionen_abrechenbar,
    SUM(CASE WHEN data_invoice_position_cleared = true THEN 1 ELSE 0 END) AS positionen_abgerechnet,
    SUM(data_invoice_position_netto) AS kosten_gesamt,
    SUM(CASE 
        WHEN data_invoice_position_clearable = true 
        AND data_invoice_position_cleared = false 
        THEN data_invoice_position_netto 
        ELSE 0 
    END) AS kosten_noch_abzurechnen
FROM accounts_payable
WHERE data_project_number IS NOT NULL
GROUP BY data_project_number, data_project_label
HAVING SUM(CASE 
    WHEN data_invoice_position_clearable = true 
    AND data_invoice_position_cleared = false 
    THEN data_invoice_position_netto 
    ELSE 0 
END) > 0
ORDER BY kosten_noch_abzurechnen DESC;