CREATE OR ALTER PROCEDURE [FK].[GetOSSDetailedData]
@zrodlo smallint, @okres_od [fk].[tData], @okres_do [fk].[tData], @kursy AS VARCHAR(max)

AS

CREATE TABLE #stawki (kraj VARCHAR(3) COLLATE database_default, stawka FLOAT, opis VARCHAR(10) COLLATE database_default)

INSERT INTO #stawki (kraj, stawka, opis)
VALUES ('AT', 10.00, 'obniżona'), ('AT', 13.00, 'obniżona'), ('AT', 20.00, 'podstawowa'),
       ('BE', 6.00, 'obniżona'), ('BE', 12.00, 'obniżona'), ('BE', 21.00, 'podstawowa'),
       ('BG', 9.00, 'obniżona'), ('BG', 20.00, 'podstawowa'),
       ('HR', 5.00, 'obniżona'), ('HR', 13.00, 'obniżona'), ('HR', 25.00, 'podstawowa'),
       ('CY', 5.00, 'obniżona'), ('CY', 9.00, 'obniżona'), ('CY', 19.00, 'podstawowa'),
       ('DK', 25.00, 'podstawowa'),
       ('FR', 2.10, 'obniżona'), ('FR', 5.50, 'obniżona'), ('FR', 10.00, 'obniżona'), ('FR', 20.00, 'podstawowa'),
       ('EL', 6.00, 'obniżona'), ('EL', 13.00, 'obniżona'), ('EL', 24.00, 'podstawowa'),
       ('ES', 4.00, 'obniżona'), ('ES', 10.00, 'obniżona'), ('ES', 21.00, 'podstawowa'),
       ('NL', 9.00, 'obniżona'), ('NL', 21.00, 'podstawowa'),
       ('IE', 4.80, 'obniżona'), ('IE', 9.00, 'obniżona'), ('IE', 13.50, 'obniżona'), ('IE', 21.00, 'podstawowa'), ('IE', 23.00, 'podstawowa'),
       ('LT', 5.00, 'obniżona'), ('LT', 9.00, 'obniżona'), ('LT', 21.00, 'podstawowa'),
       ('LV', 5.00, 'obniżona'), ('LV', 12.00, 'obniżona'), ('LV', 21.00, 'podstawowa'),
       ('MT', 5.00, 'obniżona'), ('MT', 7.00, 'obniżona'),('MT', 12.00, 'obniżona'), ('MT', 18.00, 'podstawowa'),
       ('DE', 7.00, 'obniżona'), ('DE', 19.00, 'podstawowa'),
       ('PL', 5.00, 'obniżona'), ('PL', 8.00, 'obniżona'), ('PL', 23.00, 'podstawowa'),
       ('PT', 6.00, 'obniżona'), ('PT', 13.00, 'obniżona'), ('PT', 23.00, 'podstawowa'),   
       ('SI', 5.00, 'obniżona'), ('SI', 9.50, 'obniżona'), ('SI', 22.00, 'podstawowa'),
       ('SE', 6.00, 'obniżona'), ('SE', 12.00, 'obniżona'), ('SE', 25.00, 'podstawowa'),
       ('HU', 5.00, 'obniżona'), ('HU', 18.00, 'obniżona'), ('HU', 27.00, 'podstawowa'),
       ('UK', 5.00, 'obniżona'), ('UK', 20.00, 'podstawowa'),
       ('IT', 4.00, 'obniżona'), ('IT', 5.00, 'obniżona'), ('IT', 10.00, 'obniżona'), ('IT', 22.00, 'podstawowa'),
       ('XI', 20.00, 'podstawowa'), ('XI', 5.00, 'obniżona')


IF @okres_od >= '2024-09-01'
	INSERT INTO #stawki (kraj, stawka, opis)  VALUES ('FI', 10.00, 'obniżona'), ('FI', 14.00, 'obniżona'), ('FI', 24.00, 'podstawowa'), ('FI', 25.50, 'podstawowa')
ELSE
	INSERT INTO #stawki (kraj, stawka, opis)  VALUES ('FI', 10.00, 'obniżona'), ('FI', 14.00, 'obniżona'), ('FI', 24.00, 'podstawowa')


IF @okres_od >= '2024-01-01'
    INSERT INTO #stawki (kraj, stawka, opis)  VALUES ('LU', 3.00, 'obniżona'), ('LU', 7.00, 'obniżona'),('LU', 8.00, 'obniżona'),  ('LU', 13.00, 'obniżona'), ('LU', 14.00, 'obniżona'), ('LU', 16.00, 'podstawowa'), ('LU', 17.00, 'podstawowa')
ELSE IF @okres_od >= '2023-01-01' AND  @okres_od < '2024-01-01'
	INSERT INTO #stawki (kraj, stawka, opis) VALUES ('LU', 3.00, 'obniżona'), ('LU', 7.00, 'obniżona'), ('LU', 13.00, 'obniżona'), ('LU', 16.00, 'podstawowa')
ELSE
	INSERT INTO #stawki (kraj, stawka, opis) VALUES ('LU', 3.00, 'obniżona'), ('LU', 8.00, 'obniżona'), ('LU', 14.00, 'obniżona'), ('LU', 17.00, 'podstawowa')

IF @okres_od >= '2024-01-01'
	INSERT INTO #stawki (kraj, stawka, opis) VALUES  ('CZ', 10.00, 'obniżona'),('CZ', 12.00, 'obniżona'),('CZ', 15.00, 'obniżona'),('CZ', 21.00, 'podstawowa')
 ELSE
	INSERT INTO #stawki (kraj, stawka, opis) VALUES  ('CZ', 10.00, 'obniżona'),('CZ', 15.00, 'obniżona'), ('CZ', 21.00, 'podstawowa')
                                              
IF @okres_od >= '2025-01-01'
	INSERT INTO #stawki (kraj, stawka, opis) VALUES  ('SK', 5.00, 'obniżona'), ('SK', 10.00, 'obniżona'), ('SK', 19.00, 'obniżona'),('SK', 20.00, 'podstawowa'),('SK', 23.00, 'podstawowa')
ELSE
    INSERT INTO #stawki (kraj, stawka, opis) VALUES  ('SK', 10.00, 'obniżona'), ('SK', 20.00, 'podstawowa')

IF @okres_od >= '2025-07-01'
    INSERT INTO #stawki (kraj, stawka, opis) VALUES  ('EE', 5.00, 'obniżona'),('EE', 9.00, 'obniżona'), ('EE', 13.00, 'obniżona'),('EE', 20.00, 'podstawowa'),('EE', 22.00, 'podstawowa'),('EE', 24.00, 'podstawowa')
ELSE IF @okres_od >= '2024-01-01' AND @okres_do < '2025-07-01'
    INSERT INTO #stawki (kraj, stawka, opis) VALUES  ('EE', 5.00, 'obniżona'),('EE', 9.00, 'obniżona'), ('EE', 13.00, 'obniżona'),('EE', 20.00, 'podstawowa'),('EE', 22.00, 'podstawowa')
ELSE
	INSERT INTO #stawki (kraj, stawka, opis) VALUES ('EE', 5.00, 'obniżona'), ('EE', 9.00, 'obniżona'), ('EE', 20.00, 'podstawowa')

 IF @okres_od >= '2025-08-01'
    INSERT INTO #stawki (kraj, stawka, opis) VALUES ('RO', 5.00, 'obniżona'), ('RO', 9.00, 'obniżona'), ('RO', 19.00, 'podstawowa'), ('RO', 11.00, 'obniżona'), ('RO', 21.00, 'podstawowa')
ELSE
    INSERT INTO #stawki (kraj, stawka, opis) VALUES ('RO', 5.00, 'obniżona'), ('RO', 9.00, 'obniżona'), ('RO', 19.00, 'podstawowa')

CREATE TABLE #oss_data (section TINYINT, tier TINYINT, krajDostawy VARCHAR(200), krajWysylki VARCHAR(200), rodzajDostawy VARCHAR(6), stawka MONEY, rodzajStawki VARCHAR(10),
						nettoWAL MONEY, vatWAL MONEY, bruttoWAL MONEY, kodwal VARCHAR(3) COLLATE database_default, kursEUR MONEY, nettoEUR MONEY, vatEUR MONEY, bruttoEUR MONEY, skrotDostawy VARCHAR(3) COLLATE database_default,
						skrotWysylki VARCHAR(3) COLLATE database_default, zrodlo TINYINT, znacznik VARCHAR(1) COLLATE database_default, dataDokumentu DATETIME, dataOperacji DATETIME, dokNumer INT, numerEwidencyjny VARCHAR(20) COLLATE database_default,
						numerDokumentu VARCHAR(256) COLLATE database_default, trescDokumentu VARCHAR(256) COLLATE database_default, okresRejestru DATETIME, kontrahentNazwa VARCHAR(150) COLLATE database_default, kontrahentNIP VARCHAR(20) COLLATE database_default, kontrahentPozycja INT,
						kontrahentKraj VARCHAR(2) COLLATE database_default, JPK_V7 VARCHAR(256) COLLATE database_default, korektaRok INT, korektaKwartal INT, identyfikatorVAT VARCHAR(20) COLLATE database_default, identyfikatorNIP VARCHAR(20) COLLATE database_default, vatMethodDifferences TINYINT)

SET @kursy = REPLACE(@kursy, ',', '.')

CREATE TABLE #kursy (waluta VARCHAR(3) COLLATE database_default, kurs FLOAT)
if LEN(@kursy) > 4
    INSERT INTO #kursy
    SELECT LEFT(value, 3), TRY_CONVERT(float, SUBSTRING(value, 5, LEN(value)-4)) FROM [Common].[SplitString](@kursy, ';')
INSERT INTO #kursy values('EUR', 1.00)

DECLARE @vatMethod AS INT = (SELECT ISNULL((SELECT napis FROM FK.INIFILE WHERE plik = 'FIRMA.FK' AND sekcja = 'Deklaracje' AND klucz = 'VIUDOMethod'), 0))
declare @countryElementKindId int = (select [ElementKindId] from [SSCommon].[STElementKinds] where [Guid] like 'B4B8932D-E1DC-4150-9604-6DA76F492970')

-- dane szczegółowe
INSERT INTO #oss_data
SELECT	IIF(rv.korektaOSS IS NULL, IIF(ISNULL(rv.krajWysylki, 'PL') = 'PL', 20, 30), 50), 2, 
        krd.Title, krw.Title, 
        IIF(rv.rodzajDostawy = 1, 'Towar', 'Usługa'), rv.stawka, '',
        IIF(SIGN(rv.nettoWaluta) = 0, rv.netto, rv.nettoWaluta), 
        IIF(SIGN(rv.vatWaluta) = 0, rv.vat, rv.vatWaluta),
        IIF(SIGN(rv.bruttoWaluta) = 0, rv.brutto, rv.bruttoWaluta),
        IIF(ud.Nazwa IS NULL, 'PLN', ud.Nazwa),
        0.0, 0.0, 0.0, 0.0,
        rv.krajDostawy,
        krajWysylki =       IIF(rv.korektaOSS IS NULL, rv.krajWysylki, NULL),
        zrodlo =            dk.zrodlo,
        znacznik =          CHAR(ISNULL(rv.znacznik, '')),
        dataDokumentu =	    dk.datadok,
        dataOperacji =      dk.dataOper,
        dokNumer =			dk.numer,
        numerEwidencyjny =  CONCAT(dk.skrot, ' ', dk.numer),
        numerDokumentu =    dk.nazwa,
        trescDokumentu =    dk.tresc,
        okresRejestru =	    rv.okres,
        kontrahentNazwa =   kh.nazwa,
        kontrahentNip =	    kh.nip,
        kontrahentPozycja = kh.kontrahent,
        kontrahentKraj =    kh.kraj,
        JPK_V7 =            dk.[atrJpkV7],
        korektaRok	=       IIF(rv.korektaOSS IS NULL, 0, DATEPART(YEAR, rv.okres)),
        korektaKwartal =    IIF(rv.korektaOSS IS NULL, 0, DATEPART(QUARTER, rv.okres)),
        NULL, NULL, NULL
FROM FK.rejVat rv
JOIN FK.defRej dr ON dr.id = rv.def AND (dr.flagi & 0x02) = 0x02
JOIN FK.dokumenty dk ON dk.id = rv.dId AND dk.rokId = rv.rokDok AND dk.zrodlo <> 2
LEFT JOIN FK.Udok ud ON ud.RokId = dk.rokId AND ud.DokId = dk.id AND ud.TypExt = 1
LEFT JOIN fk.DaneKh kh ON kh.id = rv.danekh
LEFT JOIN SSCommon.STElements AS krw ON krw.ElementKindId = @countryElementKindId AND krw.Shortcut = rv.krajWysylki
LEFT JOIN SSCommon.STElements AS krd ON krd.ElementKindId = @countryElementKindId AND krd.Shortcut = rv.krajDostawy
WHERE ((rv.okres BETWEEN @okres_od AND @okres_do AND rv.korektaOSS IS NULL)  OR rv.korektaOSS BETWEEN @okres_od AND @okres_do) 
      AND (dk.zrodlo = @zrodlo OR @zrodlo = 1) AND rv.krajDostawy IS NOT NULL AND rv.krajWysylki IS NOT NULL AND rv.rodzajDostawy > 0
ORDER BY dk.datadok DESC, dk.skrot DESC, dk.numer DESC

-- dodajemy informacje o stawce
UPDATE oss
SET oss.rodzajStawki = ISNULL(stw.opis, '???')
FROM #oss_data oss
LEFT JOIN #stawki stw ON oss.skrotDostawy = stw.kraj AND ROUND(oss.stawka, 2) = ROUND(stw.stawka, 2)
WHERE oss.tier = 2

-- dodajemy informacje o kusie , przeliczamy wszystko na EUR
UPDATE oss
SET oss.nettoEUR = ROUND(oss.nettoWAL / krs.kurs, 2), oss.vatEUR = ROUND(oss.vatWAL / krs.kurs, 2), 
    oss.bruttoEUR = ROUND(oss.bruttoWAL / krs.kurs, 2), oss.kursEUR = krs.kurs
FROM #oss_data oss
JOIN #kursy krs ON oss.kodwal = krs.waluta
WHERE oss.tier = 2

-- pierwszy poziom grupowania
INSERT INTO #oss_data (section, tier, krajDostawy, krajWysylki, rodzajDostawy, stawka, rodzajStawki, nettoWAL, vatWAL, bruttoWAL, kodwal, skrotDostawy, skrotWysylki,
                       nettoEUR, vatEUR, bruttoEUR, kursEUR, korektaRok, korektaKwartal)
SELECT oss.section, 1, oss.krajDostawy, oss.krajWysylki, oss.rodzajDostawy, oss.stawka, oss.rodzajStawki, SUM(oss.nettoWAL), SUM(oss.vatWAL), SUM(oss.bruttoWAL),
       oss.kodwal, oss.skrotDostawy, oss.skrotWysylki, SUM(oss.nettoEUR), SUM(oss.vatEUR), SUM(oss.bruttoEUR), oss.kursEUR, oss.korektaRok, oss.korektaKwartal
FROM #oss_data oss 
WHERE tier = 2
GROUP BY oss.section, oss.krajDostawy, oss.krajWysylki, oss.rodzajDostawy, oss.stawka, oss.rodzajStawki, oss.kodwal, oss.skrotDostawy, oss.skrotWysylki, oss.kursEUR, korektaRok, korektaKwartal

-- wynikowy poziom grupowania C.2, C.3 
INSERT INTO #oss_data (section, tier, krajDostawy, krajWysylki, rodzajDostawy, stawka, rodzajStawki, nettoEUR, vatEUR, bruttoEUR, kodwal, skrotDostawy, skrotWysylki, korektaRok, korektaKwartal, vatMethodDifferences)
SELECT oss.section, 0, oss.krajDostawy, oss.krajWysylki, oss.rodzajDostawy, oss.stawka, oss.rodzajStawki, SUM(oss.nettoEUR), IIF(@vatMethod = 0, SUM(oss.vatEUR), ROUND(SUM(oss.nettoEUR) * (oss.stawka / 100), 2)),
       SUM(oss.bruttoEUR), NULL, oss.skrotDostawy, oss.skrotWysylki, oss.korektaRok, oss.korektaKwartal, IIF(@vatMethod = 1 AND ABS(SUM(oss.vatEUR) - ROUND(SUM(oss.nettoEUR) * (oss.stawka / 100), 2)) > 0.0, 1, 0)
FROM #oss_data oss 
WHERE tier = 1 AND section < 50
GROUP BY oss.section, oss.krajDostawy, oss.krajWysylki, oss.rodzajDostawy, oss.stawka, oss.rodzajStawki, oss.skrotDostawy, oss.skrotWysylki, oss.korektaRok, oss.korektaKwartal  

-- dodajemy identyfikator VAT/NIP dla do sekcji C.3
UPDATE oss SET oss.identyfikatorVAT = atrVAT.StringValue, oss.identyfikatorNIP = atrNIP.StringValue 
FROM #oss_data oss
JOIN SSCommon.STElements elem ON elem.ElementKindId = @countryElementKindId AND elem.Shortcut = oss.skrotWysylki
JOIN SSCommon.STElementExAttributes atrVAT ON atrVAT.ElementId = elem.ElementId AND atrVAT.Title = 'kraj.identyfikatorVAT'
JOIN SSCommon.STElementExAttributes atrNIP ON atrNIP.ElementId = elem.ElementId AND atrNIP.Title = 'kraj.identyfikatorNIP'
WHERE oss.section = 30 AND oss.tier = 0

-- wynikowy poziom grupowania C.5 
INSERT INTO #oss_data (section, tier, krajDostawy, vatEUR, skrotDostawy, korektaRok, korektaKwartal)
SELECT oss.section, 0, oss.krajDostawy, SUM(oss.vatEUR), oss.skrotDostawy, oss.korektaRok, oss.korektaKwartal
FROM #oss_data oss 
WHERE tier = 1 AND section = 50
GROUP BY oss.section, oss.krajDostawy, oss.skrotDostawy, oss.korektaRok, oss.korektaKwartal  
HAVING SUM(oss.vatEUR) <> 0

-- podsumowanie, pole 13, 14
INSERT INTO #oss_data (section, vatEUR)
SELECT IIF(oss.rodzajDostawy = 'Usługa', 21, 22), SUM(oss.vatEUR) FROM #oss_data oss 
WHERE section = 20 AND tier = 0 GROUP BY oss.section, oss.rodzajDostawy  

-- podsumowanie, pole 15, 16
INSERT INTO #oss_data (section, vatEUR)
SELECT IIF(oss.rodzajDostawy = 'Usługa', 31, 32), SUM(oss.vatEUR) FROM #oss_data oss 
WHERE section = 30 AND tier = 0 GROUP BY oss.section, oss.rodzajDostawy  

-- podsumowanie, pole 17
if (SELECT count(*) FROM #oss_data oss WHERE section IN(21, 22, 31, 32)) > 0
    INSERT INTO #oss_data (section, vatEUR)
    SELECT 40, SUM(oss.vatEUR) FROM #oss_data oss 
    WHERE section IN(21, 22, 31, 32)

-- C.6
if (SELECT count(*) FROM #oss_data oss WHERE tier = 0) > 0
    INSERT INTO #oss_data (section, krajDostawy, vatEUR, skrotDostawy)
    SELECT 60, oss.krajDostawy, SUM(oss.vatEUR), oss.skrotDostawy FROM #oss_data oss 
    WHERE oss.tier = 0 GROUP BY oss.krajDostawy, oss.skrotDostawy
    HAVING SUM(oss.vatEUR) <> 0

-- C.7
if (SELECT count(*) FROM #oss_data oss WHERE section = 60) > 0
    INSERT INTO #oss_data (section, vatEUR)
    SELECT 70, iif(SUM(oss.vatEUR) > 0, SUM(oss.vatEUR), 0.0) FROM #oss_data oss 
    WHERE oss.section = 60 and oss.vatEUR > 0

SELECT section, tier, krajDostawy, krajWysylki, rodzajDostawy, stawka, rodzajStawki, nettoWAL, vatWAL, bruttoWAL, IIF(kodwal IS NULL AND tier = 0, 'EUR', kodwal) waluta, 
       kursEUR, nettoEUR, vatEUR, bruttoEUR, skrotDostawy, skrotWysylki, zrodlo, znacznik, dataDokumentu, dataOperacji, dokNumer, numerEwidencyjny, numerDokumentu,
       trescDokumentu, okresRejestru, kontrahentNazwa, kontrahentNIP, kontrahentPozycja, kontrahentKraj, JPK_V7, korektaRok, korektaKwartal, identyfikatorVAT, identyfikatorNIP, vatMethodDifferences
FROM #oss_data ORDER BY section, krajDostawy, krajWysylki, rodzajDostawy, stawka DESC, korektaRok, korektaKwartal, kodwal, tier
for json auto

DROP TABLE #kursy
DROP TABLE #stawki
DROP TABLE #oss_data