# Name: Verkauf und Gutschriften Export
# Description: Exportiert Verkaufspositionen und Gutschriften im gewaehlten Zeitraum.
# FileName: verkauf_gutschriften_export
# UseDateFilter: true
# DateFilterInfo: a.dErstellt / g.dErstellt

SELECT
    CONVERT(varchar(10), a.dErstellt, 103) AS [Date Start],
    CONVERT(varchar(10), a.dErstellt, 103) AS [Date End],
    ISNULL(ap.cArtNr, '') AS [SKU],
    CAST(ISNULL(ap.fAnzahl, 0) AS decimal(18,2)) AS [Quantity],
    REPLACE(
        CAST(
            CAST(
                ISNULL(ap.fVkNetto, 0)
                * (1 - ISNULL(ap.fRabatt, 0) / 100)
                * (1 + ISNULL(ap.fMwSt, 0) / 100)
            AS decimal(18,2))
        AS varchar(50)),
        '.',
        ','
    ) AS [Gross Price Per Item],
    REPLACE(
        CAST(
            CAST(ISNULL(ap.fMwSt, 0) AS decimal(18,2))
        AS varchar(50)),
        '.',
        ','
    ) AS [Tax Line],
    'DE' AS [Country],
    ISNULL(ap.cName, '') AS [Product Title],
    ISNULL(za.cName, '') AS [Sales Category],
    'VERKAUF' AS [Sales Sub Category],
    '' AS [Sales Channel],
    ISNULL(a.cAuftragsNr, '') AS [Order ID],
    CAST(ISNULL(a.kKunde, 0) AS varchar(50)) AS [Customer ID],
    '' AS [Status]
FROM Verkauf.tAuftrag a
INNER JOIN Verkauf.tAuftragPosition ap
    ON ap.kAuftrag = a.kAuftrag
LEFT JOIN dbo.tZahlungsart za
    ON za.kZahlungsart = a.kZahlungsart
WHERE a.dErstellt >= @DateFrom
  AND a.dErstellt < @DateTo
  AND ISNULL(ap.fAnzahl, 0) <> 0

UNION ALL

SELECT
    CONVERT(varchar(10), g.dErstellt, 103) AS [Date Start],
    CONVERT(varchar(10), g.dErstellt, 103) AS [Date End],
    ISNULL(gp.cArtNr, '') AS [SKU],
    CAST(ISNULL(gp.nAnzahl, 0) AS decimal(18,2)) AS [Quantity],
    REPLACE(
        CAST(
            CAST(
                -1
                * ISNULL(gp.fVKNetto, 0)
                * (1 + ISNULL(gp.fMwSt, 0) / 100)
            AS decimal(18,2))
        AS varchar(50)),
        '.',
        ','
    ) AS [Gross Price Per Item],
    REPLACE(
        CAST(
            CAST(ISNULL(gp.fMwSt, 0) AS decimal(18,2))
        AS varchar(50)),
        '.',
        ','
    ) AS [Tax Line],
    'DE' AS [Country],
    ISNULL(gp.cString, '') AS [Product Title],
    ISNULL(za2.cName, '') AS [Sales Category],
    'GUTSCHRIFT' AS [Sales Sub Category],
    '' AS [Sales Channel],
    ISNULL(a2.cAuftragsNr, '') AS [Order ID],
    CAST(ISNULL(g.kKunde, 0) AS varchar(50)) AS [Customer ID],
    'GUTGESCHRIEBEN' AS [Status]
FROM dbo.tgutschrift g
INNER JOIN dbo.tGutschriftPos gp
    ON gp.tGutschrift_kGutschrift = g.kGutschrift
LEFT JOIN Verkauf.tAuftragPosition ap2
    ON ap2.kAuftragPosition = gp.kBestellPos
LEFT JOIN Verkauf.tAuftrag a2
    ON a2.kAuftrag = ap2.kAuftrag
LEFT JOIN dbo.tZahlungsart za2
    ON za2.kZahlungsart = a2.kZahlungsart
WHERE g.dErstellt >= @DateFrom
  AND g.dErstellt < @DateTo
  AND ISNULL(g.nStorno, 0) = 0
  AND ISNULL(gp.nAnzahl, 0) <> 0
ORDER BY
    [Date Start],
    [Order ID],
    [SKU],
    [Sales Sub Category];
