293 lines
10 KiB
SQL
293 lines
10 KiB
SQL
-- =============================================================================
|
||
-- Rechnungsexport – Odoo 17 / PostgreSQL
|
||
-- Rechnungsdatum · Rechnungsnummer · Länder · Zahlart · USt-Nr. · Steuersplit
|
||
--
|
||
-- Modell : account.move (Kundenrechnungen und Gutschriften)
|
||
--
|
||
-- Parameter:
|
||
-- %(month)s Abrechnungszeitraum als Text, z. B. '2026-SEP'
|
||
--
|
||
-- Aufruf (Odoo / psycopg2):
|
||
-- cr.execute(open('Rechnungsexport.sql').read(), {'month': '2026-SEP'})
|
||
--
|
||
-- In psql stattdessen:
|
||
-- \set month '2026-SEP'
|
||
-- ... AND am.invoice_date >= :'month' (siehe params-CTE)
|
||
--
|
||
-- Hinweis zum Monatsfilter:
|
||
-- Das Rechnungsdatum wird auf einen vollen Kalendermonat gefiltert.
|
||
-- Der Monat wird locale-unabhängig in ein Datum umgerechnet; es werden
|
||
-- deutsche (MÄR, MAI, OKT, DEZ) und englische (MAR, MAY, OCT, DEC)
|
||
-- Dreibuchstaben-Kürzel akzeptiert.
|
||
--
|
||
-- Änderungen gegenüber der Vorversion (nur die fehlenden Felder ergänzt):
|
||
-- * Auftragsdatum : aus sale_order.date_order (über invoice_origin)
|
||
-- * Zahldatum : aus der letzten Ausgleichsbuchung der Debitorenzeile
|
||
-- * Lieferadresse Name / Lieferland : aus account_move.partner_shipping_id
|
||
-- (Fallback: Rechnungspartner)
|
||
-- =============================================================================
|
||
|
||
WITH params AS (
|
||
-- Kalendermonat aus %(month)s (z. B. '2026-SEP') ableiten.
|
||
SELECT
|
||
to_date(
|
||
split_part(%(month)s, '-', 1) || '-' ||
|
||
CASE upper(split_part(%(month)s, '-', 2))
|
||
WHEN 'JAN' THEN '01' WHEN 'FEB' THEN '02'
|
||
WHEN 'MÄR' THEN '03' WHEN 'MAR' THEN '03' WHEN 'APR' THEN '04'
|
||
WHEN 'MAI' THEN '05' WHEN 'MAY' THEN '05' WHEN 'JUN' THEN '06'
|
||
WHEN 'JUL' THEN '07' WHEN 'AUG' THEN '08' WHEN 'SEP' THEN '09'
|
||
WHEN 'OKT' THEN '10' WHEN 'OCT' THEN '10' WHEN 'NOV' THEN '11'
|
||
WHEN 'DEZ' THEN '12' WHEN 'DEC' THEN '12'
|
||
END,
|
||
'YYYY-MM'
|
||
) AS first_day
|
||
),
|
||
|
||
invoices AS (
|
||
SELECT
|
||
am.id,
|
||
am.name,
|
||
am.ref,
|
||
am.move_type,
|
||
am.invoice_date,
|
||
am.invoice_origin,
|
||
am.partner_id,
|
||
am.partner_shipping_id,
|
||
am.invoice_payment_term_id,
|
||
am.payment_reference
|
||
FROM account_move am
|
||
CROSS JOIN params p
|
||
WHERE am.invoice_date >= p.first_day
|
||
AND am.invoice_date < p.first_day + INTERVAL '1 month'
|
||
AND am.move_type IN ('out_invoice', 'out_refund')
|
||
),
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- tax summary: invoice line -> tax relation -> account_tax
|
||
-- ---------------------------------------------------------------------------
|
||
line_taxes AS (
|
||
SELECT
|
||
aml.move_id AS invoice_id,
|
||
at.amount AS tax_rate,
|
||
SUM(aml.price_subtotal) AS netto,
|
||
SUM(aml.price_total) AS brutto
|
||
FROM invoices inv
|
||
JOIN account_move_line aml
|
||
ON aml.move_id = inv.id
|
||
JOIN account_move_line_account_tax_rel amlatr
|
||
ON amlatr.account_move_line_id = aml.id
|
||
JOIN account_tax at
|
||
ON at.id = amlatr.account_tax_id
|
||
GROUP BY aml.move_id, at.amount
|
||
),
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- tax split logic from Python:
|
||
-- threshold = 8 for CH, else 16
|
||
-- ---------------------------------------------------------------------------
|
||
tax_split AS (
|
||
SELECT
|
||
lt.invoice_id,
|
||
|
||
MAX(CASE
|
||
WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
THEN ROUND(lt.brutto::numeric, 2)
|
||
END) AS brutto_normal,
|
||
|
||
MAX(CASE
|
||
WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
THEN ROUND(lt.tax_rate::numeric, 2)
|
||
END) AS ust_satz_normal,
|
||
|
||
MAX(CASE
|
||
WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
THEN ROUND(lt.netto::numeric, 2)
|
||
END) AS netto_normal,
|
||
|
||
MAX(CASE
|
||
WHEN NOT (
|
||
(rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
)
|
||
THEN ROUND(lt.brutto::numeric, 2)
|
||
END) AS brutto_erm,
|
||
|
||
MAX(CASE
|
||
WHEN NOT (
|
||
(rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
)
|
||
THEN ROUND(lt.tax_rate::numeric, 2)
|
||
END) AS ust_satz_erm,
|
||
|
||
MAX(CASE
|
||
WHEN NOT (
|
||
(rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
|
||
OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
|
||
)
|
||
THEN ROUND(lt.netto::numeric, 2)
|
||
END) AS netto_erm
|
||
|
||
FROM line_taxes lt
|
||
JOIN invoices inv
|
||
ON inv.id = lt.invoice_id
|
||
JOIN res_partner rp
|
||
ON rp.id = inv.partner_id
|
||
LEFT JOIN res_country rc
|
||
ON rc.id = rp.country_id
|
||
GROUP BY lt.invoice_id
|
||
),
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- shipping costs (product category id = 9, as in Python logic)
|
||
-- ---------------------------------------------------------------------------
|
||
shipping AS (
|
||
SELECT
|
||
aml.move_id AS invoice_id,
|
||
ROUND(SUM(aml.price_total)::numeric, 2) AS shipping_brutto,
|
||
ROUND(SUM(aml.price_subtotal)::numeric, 2) AS shipping_netto
|
||
FROM invoices inv
|
||
JOIN account_move_line aml
|
||
ON aml.move_id = inv.id
|
||
JOIN product_product pp
|
||
ON pp.id = aml.product_id
|
||
JOIN product_template pt
|
||
ON pt.id = pp.product_tmpl_id
|
||
WHERE pt.categ_id = 9
|
||
GROUP BY aml.move_id
|
||
),
|
||
|
||
invoice_tags AS (
|
||
SELECT
|
||
inv.id AS invoice_id,
|
||
STRING_AGG(
|
||
DISTINCT COALESCE(ct.name->>'en_US', ct.name->>'de_DE', ''),
|
||
', ' ORDER BY COALESCE(ct.name->>'en_US', ct.name->>'de_DE', '')
|
||
) AS tags
|
||
FROM invoices inv
|
||
JOIN sale_order so
|
||
ON so.name = inv.invoice_origin
|
||
JOIN sale_order_tag_rel sotr
|
||
ON sotr.order_id = so.id
|
||
JOIN crm_tag ct
|
||
ON ct.id = sotr.tag_id
|
||
GROUP BY inv.id
|
||
),
|
||
|
||
invoice_transaction_reference AS (
|
||
SELECT
|
||
rel.invoice_id,
|
||
pt.provider_reference,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY rel.invoice_id
|
||
ORDER BY pt.id
|
||
) AS rn
|
||
FROM account_invoice_transaction_rel rel
|
||
JOIN payment_transaction pt
|
||
ON pt.id = rel.transaction_id
|
||
),
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Auftragsdatum: Bestellung über invoice_origin (Verkaufsauftragsname) finden
|
||
-- ---------------------------------------------------------------------------
|
||
sale_info AS (
|
||
SELECT
|
||
inv.id AS invoice_id,
|
||
MIN(so.date_order) AS order_date
|
||
FROM invoices inv
|
||
JOIN sale_order so
|
||
ON so.name = inv.invoice_origin
|
||
GROUP BY inv.id
|
||
),
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- Zahldatum: Datum der letzten Ausgleichsbuchung der Debitorenzeile.
|
||
-- account_partial_reconcile.max_date = jüngeres Datum der beiden Buchungen.
|
||
-- ---------------------------------------------------------------------------
|
||
invoice_payments AS (
|
||
SELECT
|
||
aml.move_id AS invoice_id,
|
||
MAX(apr.max_date) AS payment_date
|
||
FROM invoices inv
|
||
JOIN account_move_line aml
|
||
ON aml.move_id = inv.id
|
||
JOIN account_account aa
|
||
ON aa.id = aml.account_id
|
||
AND aa.account_type = 'asset_receivable'
|
||
JOIN account_partial_reconcile apr
|
||
ON apr.debit_move_id = aml.id
|
||
OR apr.credit_move_id = aml.id
|
||
GROUP BY aml.move_id
|
||
)
|
||
|
||
SELECT
|
||
TO_CHAR(inv.invoice_date, 'YYYY-MM-DD') AS "Rechnungsdatum",
|
||
|
||
CASE
|
||
WHEN inv.move_type = 'out_refund'
|
||
THEN COALESCE(inv.name, inv.ref, '') || '-gs'
|
||
ELSE COALESCE(inv.name, inv.ref, '')
|
||
END AS "Rechnungsnummer",
|
||
|
||
COALESCE(rc_inv.name->>'en_US', rc_inv.name->>'de_DE', '') AS "Rechnungsland",
|
||
COALESCE(rc_inv.code, '') AS "Rechnungsland Ländercode",
|
||
COALESCE(apt.name->>'en_US', apt.name->>'de_DE', '') AS "Zahlart",
|
||
|
||
COALESCE(itr.provider_reference, '') AS "Zahlungsreference der Bestellung",
|
||
COALESCE(partner.vat, '') AS "USt. Id / VAT Id des Käufers (in Bestellung)",
|
||
|
||
ROUND(COALESCE(ts.brutto_normal, 0) + COALESCE(ts.brutto_erm, 0), 2) AS "Gesamtpreis brutto",
|
||
ROUND(COALESCE(ts.netto_normal, 0) + COALESCE(ts.netto_erm, 0), 2) AS "Gesamtpreis netto",
|
||
|
||
COALESCE(ts.brutto_normal, 0) AS "Brutto",
|
||
COALESCE(ts.brutto_erm, 0) AS "Brutto erm",
|
||
COALESCE(ts.ust_satz_normal, 0) AS "USt. Satz",
|
||
COALESCE(ts.ust_satz_erm, 0) AS "Ust. Satz erm",
|
||
COALESCE(ts.netto_normal, 0) AS "Netto Betrag",
|
||
COALESCE(ts.netto_erm, 0) AS "Netto Betrag erm",
|
||
|
||
COALESCE(sh.shipping_brutto, 0) AS "Versandkosten brutto",
|
||
COALESCE(sh.shipping_netto, 0) AS "Versandkosten netto",
|
||
|
||
COALESCE(TO_CHAR(si.order_date, 'YYYY-MM-DD'), '') AS "Auftragsdatum",
|
||
COALESCE(inv.invoice_origin, '') AS "Bestellnummer",
|
||
COALESCE(TO_CHAR(ip.payment_date, 'YYYY-MM-DD'), '') AS "Zahldatum",
|
||
|
||
COALESCE(ship.name, partner.name, '') AS "Lieferadresse Name",
|
||
COALESCE(it.tags, '') AS "Tag",
|
||
COALESCE(
|
||
rc_ship.name->>'en_US', rc_ship.name->>'de_DE',
|
||
rc_inv.name->>'en_US', rc_inv.name->>'de_DE',
|
||
''
|
||
) AS "Lieferland"
|
||
|
||
FROM invoices inv
|
||
JOIN res_partner partner
|
||
ON partner.id = inv.partner_id
|
||
LEFT JOIN res_country rc_inv
|
||
ON rc_inv.id = partner.country_id
|
||
LEFT JOIN res_partner ship
|
||
ON ship.id = inv.partner_shipping_id
|
||
LEFT JOIN res_country rc_ship
|
||
ON rc_ship.id = ship.country_id
|
||
LEFT JOIN account_payment_term apt
|
||
ON apt.id = inv.invoice_payment_term_id
|
||
LEFT JOIN tax_split ts
|
||
ON ts.invoice_id = inv.id
|
||
LEFT JOIN shipping sh
|
||
ON sh.invoice_id = inv.id
|
||
LEFT JOIN invoice_tags it
|
||
ON it.invoice_id = inv.id
|
||
LEFT JOIN invoice_transaction_reference itr
|
||
ON itr.invoice_id = inv.id
|
||
AND itr.rn = 1
|
||
LEFT JOIN sale_info si
|
||
ON si.invoice_id = inv.id
|
||
LEFT JOIN invoice_payments ip
|
||
ON ip.invoice_id = inv.id
|
||
ORDER BY inv.invoice_date, inv.name;
|