history_scope.sql 1.3 KB

12345678910111213141516171819202122232425262728293031
  1. WITH current_invoice AS (
  2. SELECT Client_DB, Beleg_Nr, Kunde_Nr, Fahrzeug_Nr
  3. FROM dbo.Forderungen_Kopf
  4. WHERE Client_DB = :client_db AND Beleg_Nr = :beleg_nr
  5. ), scoped_invoices AS (
  6. {% if scope == 'invoice' %}
  7. SELECT Client_DB, Beleg_Nr FROM current_invoice
  8. {% elif scope == 'open' %}
  9. SELECT F.Client_DB, F.Beleg_Nr FROM dbo.Forderungen_Kopf AS F
  10. WHERE F.Client_DB = :client_db AND F.Beleg_Offen <> 0
  11. AND EXISTS (SELECT 1 FROM current_invoice AS C WHERE C.Kunde_Nr = F.Kunde_Nr)
  12. {% else %}
  13. SELECT F.Client_DB, F.Beleg_Nr FROM dbo.Forderungen_Kopf AS F
  14. WHERE F.Client_DB = :client_db
  15. AND EXISTS (
  16. SELECT 1 FROM current_invoice AS C WHERE C.Kunde_Nr = F.Kunde_Nr
  17. {% if scope == 'vehicle' %}
  18. AND C.Fahrzeug_Nr IS NOT NULL AND C.Fahrzeug_Nr <> '' AND C.Fahrzeug_Nr = F.Fahrzeug_Nr
  19. {% endif %}
  20. )
  21. UNION
  22. SELECT H.Client_DB, H.Beleg_Nr FROM dbo.Forderungen_Historie AS H
  23. WHERE H.Client_DB = :client_db
  24. AND EXISTS (
  25. SELECT 1 FROM current_invoice AS C WHERE C.Kunde_Nr = H.Kunde_Nr
  26. {% if scope == 'vehicle' %}
  27. AND C.Fahrzeug_Nr IS NOT NULL AND C.Fahrzeug_Nr <> '' AND C.Fahrzeug_Nr = H.Fahrzeug_Nr
  28. {% endif %}
  29. )
  30. {% endif %}
  31. )