DROP FUNCTION IF EXISTS FK.GetAccountPlanId
GO

-- Funkcja zwraca jeden rekord z ID planu kont (YearId oraz Id) lub nic, jeśli podany numer nie występuje w planie kont. 
-- Funkcja sprawdza czy kolejne poziomy numeru konta są analityką lub słownikiem i jednocześnie sprawdza czy podana wartości poziom1-5 występuje jako pozycja słownikowa. 
-- Innymi słowy, funkcja zwróci ID, jeśli podany numer jest w 100% poprawny (poziomy słownikowe są sprawdzane czy naprawdę istnieją).
CREATE FUNCTION FK.GetAccountPlanId(@rokId AS SMALLINT, @synt AS INT, @poz1 AS INT, @poz2 AS INT, @poz3 AS INT, @poz4 AS INT, @poz5 AS INT)
RETURNS TABLE
AS 
RETURN (

-- Funkcja zwraca ID planu kont, ale robi to w dość nieoczywisty sposób, 
-- sprawdza osobno każdą pozycje, czy może istnieć w planie kont na konkretnym 
-- poziomie, pod warunkiem, że sprawdzenie z poprzedniego poziomu zwróciło true.
-- Bardzo ciężko opisać ten algorytm, ale spróbujmy...
--
--
-- Założenia do poniższego zapytania:
--
-- p0-p5, to aliasy dla wyników konkretnego poziomu, gdzie p0 to syntetyka, a kolejne p1-p5 to kolejne poziomy analityki.
-- p0 (syntetyka) jest sprawdzany w pierwszym FROM (na dole jest WHERE, który filtruje "0" dla każdego z poziomów typX/wartX). 
--
-- Każdy kolejny APPLY, sprawdza TYLKO jeden poziom analityki, ale sprawdza go przez pryzmat czterech rodzajów:
-- typX = -1, czyli analityka
-- typX = 0, czyli brak poziomu w planie kont
-- typX > 0, czyli słownik (tak, tutaj celowo nie ma sprawdzenia typX <> 1000004)
-- typX = 1000004, kartoteka urzędów
--
-- Każdy APPLY zwraca 0 lub 1 rekord, bo zapytania są tak dobrane, żeby tylko jeden union mógł być spełniony.
-- Jeśli APPLY nic nie zwróci, to koniec sprawdzania (numer konta jest niepoprawny), a jeśli coś się znajdzie, to
-- wynik tego APPLY przekazywany jest do kolejnego APPLY. 
-- Rekord z poprzedniego APPLY staje się parentem dla kolejnych sprawdzeń w tym kolejnym APPLY.
--
-- Algorytm wykorzystuje zasadę, że każdy rekord planu kont ma UNIKALNY zestaw wartości (jest zresztą na to indeks unikalny)
-- rokId, syntet, typ1, wart1, typ2, wart2, typ3, wart3, typ4, wart4, typ5, wart5.
-- ORAZ, że w tabeli plan kont, rekord opisujący np. poziom2
-- ma wartości poprzednich poziomów (czyli konkretne rokId, syntet, typ1, wart1), a kolejne składowe są zawsze "0".
SELECT 
    --
    -- WAŻNE!
    -- Ta funkcja NIE MOŻE zwracać niczego poza kluczem głównym,
    -- ponieważ wpłynie to znacząco na wydajność zapytań, które
    -- jej używają (jak zwykle widoczne przy większych bazach).
    -- Jeśli potrzebne są dodatkowe elementy, to w zapytaniu które
    -- wywołuję tę funkcję, trzeba dodać zwykłego JOIN do plankont 
    -- i będzie działać to w zupełności poprawnie i szybko.
    --
    p5.rokId AS YearId
    , p5.id AS Id
FROM FK.plankont AS p0
CROSS APPLY (

    -- sprawdzamy, czy poziom1 w planie kont jest analityką (typ1 == -1) i jeśli tak, 
    -- to czy jego wartość jest tym co przyszło w parametrach (wart1 == @poz1).
    SELECT p.rokId, p.syntet, p.typ1, p.wart1 FROM FK.plankont AS p
    WHERE p.rokId = p0.rokId AND p.syntet = p0.syntet
    AND p.typ1 = -1 AND p.wart1 = @poz1
    AND p.typ2 = 0 AND p.wart2 = 0 
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL 

    -- sprawdzamy, czy poziom1 w planie kont NIE ISTNIEJE, czyli (w tym przypadku) "syntetyka" jest ostatnim poziomem.
    -- jeśli tak, to @poz1 == 0 i wart1 == 0 i typ1 == 0. 
    -- innymi słowy tutaj sprawdzamy, czy przekazane "0" w argumentach funkcji oznaczają jakby "brak numeru" w planie kont,
    -- czyli konto 201-5, to tak naprawdę zapis 201-5-0-0-0-0 i każde z tych "0" jest spawdzane i musi występować w planie kont.
    SELECT p.rokId, p.syntet, p.typ1, p.wart1 FROM FK.plankont AS p
    WHERE p.rokId = p0.rokId AND p.syntet = p0.syntet
    AND p.typ1 = 0 AND p.wart1 = @poz1 AND @poz1 = 0
    AND p.typ2 = 0 AND p.wart2 = 0 
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL

    -- sprawdzamy, czy poziom1 w planie kont jest słownikiem (typ1 > 0) i jeśli tak, 
    -- to sprawdzamy, czy wartość @poz1 podana w funkcji, występuje na liście pozycji tego słownika
    SELECT p.rokId, p.syntet, p.typ1, p.wart1 FROM FK.plankont AS p
    JOIN SSCommon.STElements AS se ON se.ElementKindId = p.typ1 AND se.Position > 0
    WHERE p.rokId = p0.rokId AND p.syntet = p0.syntet
    AND p.typ1 > 0 AND p.wart1 = 0
    AND p.typ2 = 0 AND p.wart2 = 0 
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND se.Position = @poz1

    UNION ALL

    -- spradzamy, czy poziom1 w planie kont jest urzędem (typ1 = 1000004) i jeśli tak,
    -- to sprawdzamy, czy wartość @poz1 podana w funkcji, występuje na liście pozycji tego słownika
    SELECT p.rokId, p.syntet, p.typ1, p.wart1 FROM FK.plankont AS p
    CROSS JOIN fk.urzad_sk AS us
    WHERE p.rokId = p0.rokId AND p.syntet = p0.syntet
    AND p.typ1 = 1000004 AND p.wart1 = 0
    AND p.typ2 = 0 AND p.wart2 = 0 
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND us.pozycja = @poz1

    -- wynik z p1, jest przekazywany do kolejne APPLY
    -- MAKSYMALNIE jeden UNION zwróci dane, tzn. że albo 
    -- znajdzie konkrenty jeden rekord i sprawdzenie pójdzie dalej
    -- ale na poziomie p1 nie będzie dopasowania i koniec sprawdzania.
) AS p1
CROSS APPLY (
    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2 FROM FK.plankont AS p
    -- w tym (i kolejnych) WHERE następuje powiązanie nowo sprawdzanego rekordu z poziomu2
    -- z poprzednio znalezionym rekordem z poziomu1, tzn. rekord poziomu1 jest parentem dla poziom2. 
    -- czyli tutaj łączymy:
    -- rok, synetykę, typ1, wart1 (bo one MUSZĄ być te same, zgodnie z tym jak działa tabela plankont)
    -- i szukamy tego co w poziom2 (w tym UNION typ2 == -1, czyli normalna analityka),
    -- a dodatkowo typ3, wart3, typ4, wart4, typ5, wart5 są 0, bo sprawdzamy TYLKO poziom2 i on
    -- w tych konkretnych kolumnach NA PEWNO musi meć "0".
    WHERE p.rokId = p1.rokId AND p.syntet = p1.syntet
    AND p.typ1 = p1.typ1 AND p.wart1 = p1.wart1
    AND p.typ2 = -1 AND p.wart2 = @poz2
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL 

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2 FROM FK.plankont AS p
    WHERE p.rokId = p1.rokId AND p.syntet = p1.syntet
    AND p.typ1 = p1.typ1 AND p.wart1 = p1.wart1
    AND p.typ2 = 0 AND p.wart2 = @poz2 AND @poz2 = 0
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2 FROM FK.plankont AS p
    JOIN SSCommon.STElements AS se ON se.ElementKindId = p.typ2 AND se.Position > 0
    WHERE p.rokId = p1.rokId AND p.syntet = p1.syntet
    AND p.typ1 = p1.typ1 AND p.wart1 = p1.wart1
    AND p.typ2 > 0 AND p.wart2 = 0 
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND se.Position = @poz2

    UNION ALL

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2 FROM FK.plankont AS p
    CROSS JOIN FK.urzad_sk AS us
    WHERE p.rokId = p1.rokId AND p.syntet = p1.syntet
    AND p.typ1 = p1.typ1 AND p.wart1 = p1.wart1
    AND p.typ2 = 1000004 AND p.wart2 = 0 
    AND p.typ3 = 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND us.pozycja = @poz2
    
    -- i znowu, efekty tego zapytania przekazujemy do kolejnego APPLY
    -- i zabawa zaczyna się od nowa, aż do OSTATNIEGO poziomu, który wtedy zwraca nam klucz główny rekordu.
    -- WAŻNE! Jeśli numer konta jest poprawny, ZAWSZE przejdzie przez wszystkie APPLY i ostatni z nich
    -- dopiero będzie wskazywał na ID, ponieważ dla konta 201-4-8-0-0-0, te ostatnie "0" też muszą być sprawdzony,
    -- czy w planie kont na poziomach 3-5 też są oczekiwane "0".
) AS p2
CROSS APPLY (
    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3 FROM FK.plankont AS p
    WHERE p.rokId = p2.rokId AND p.syntet = p2.syntet
    AND p.typ1 = p2.typ1 AND p.wart1 = p2.wart1
    AND p.typ2 = p2.typ2 AND p.wart2 = p2.wart2
    AND p.typ3 = -1 AND p.wart3 = @poz3
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL 

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3 FROM FK.plankont AS p
    WHERE p.rokId = p2.rokId AND p.syntet = p2.syntet
    AND p.typ1 = p2.typ1 AND p.wart1 = p2.wart1
    AND p.typ2 = p2.typ2 AND p.wart2 = p2.wart2
    AND p.typ3 = 0 AND p.wart3 = @poz3 AND @poz3 = 0
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3 FROM FK.plankont AS p
    JOIN SSCommon.STElements AS se ON se.ElementKindId = p.typ3 AND se.Position > 0
    WHERE p.rokId = p2.rokId AND p.syntet = p2.syntet
    AND p.typ1 = p2.typ1 AND p.wart1 = p2.wart1
    AND p.typ2 = p2.typ2 AND p.wart2 = p2.wart2
    AND p.typ3 > 0 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND se.Position = @poz3

    UNION ALL

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3 FROM FK.plankont AS p
    CROSS JOIN fk.urzad_sk AS us
    WHERE p.rokId = p2.rokId AND p.syntet = p2.syntet
    AND p.typ1 = p2.typ1 AND p.wart1 = p2.wart1
    AND p.typ2 = p2.typ2 AND p.wart2 = p2.wart2
    AND p.typ3 = 1000004 AND p.wart3 = 0 
    AND p.typ4 = 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND us.pozycja = @poz3

) AS p3
CROSS APPLY (
    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3, p.typ4, p.wart4 FROM FK.plankont AS p
    WHERE p.rokId = p3.rokId AND p.syntet = p3.syntet
    AND p.typ1 = p3.typ1 AND p.wart1 = p3.wart1
    AND p.typ2 = p3.typ2 AND p.wart2 = p3.wart2
    AND p.typ3 = p3.typ3 AND p.wart3 = p3.wart3
    AND p.typ4 = -1 AND p.wart4 = @poz4
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL 

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3, p.typ4, p.wart4 FROM FK.plankont AS p
    WHERE p.rokId = p3.rokId AND p.syntet = p3.syntet
    AND p.typ1 = p3.typ1 AND p.wart1 = p3.wart1
    AND p.typ2 = p3.typ2 AND p.wart2 = p3.wart2
    AND p.typ3 = p3.typ3 AND p.wart3 = p3.wart3
    AND p.typ4 = 0 AND p.wart4 = @poz4 AND @poz4 = 0
    AND p.typ5 = 0 AND p.wart5 = 0

    UNION ALL

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3, p.typ4, p.wart4 FROM FK.plankont AS p
    JOIN SSCommon.STElements AS se ON se.ElementKindId = p.typ4 AND se.Position > 0
    WHERE p.rokId = p3.rokId AND p.syntet = p3.syntet
    AND p.typ1 = p3.typ1 AND p.wart1 = p3.wart1
    AND p.typ2 = p3.typ2 AND p.wart2 = p3.wart2
    AND p.typ3 = p3.typ3 AND p.wart3 = p3.wart3
    AND p.typ4 > 0 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND se.Position = @poz4

    UNION ALL

    SELECT p.rokId, p.syntet, p.typ1, p.wart1, p.typ2, p.wart2, p.typ3, p.wart3, p.typ4, p.wart4 FROM FK.plankont AS p
    CROSS JOIN FK.urzad_sk AS us
    WHERE p.rokId = p3.rokId AND p.syntet = p3.syntet
    AND p.typ1 = p3.typ1 AND p.wart1 = p3.wart1
    AND p.typ2 = p3.typ2 AND p.wart2 = p3.wart2
    AND p.typ3 = p3.typ3 AND p.wart3 = p3.wart3
    AND p.typ4 = 1000004 AND p.wart4 = 0 
    AND p.typ5 = 0 AND p.wart5 = 0
    AND us.pozycja = @poz4

) AS p4
CROSS APPLY (
    SELECT p.rokId, p.id FROM FK.plankont AS p
    WHERE p.rokId = p4.rokId AND p.syntet = p4.syntet
    AND p.typ1 = p4.typ1 AND p.wart1 = p4.wart1
    AND p.typ2 = p4.typ2 AND p.wart2 = p4.wart2
    AND p.typ3 = p4.typ3 AND p.wart3 = p4.wart3
    AND p.typ4 = p4.typ4 AND p.wart4 = p4.wart4
    AND p.typ5 = -1 AND p.wart5 = @poz5 

    UNION ALL 

    SELECT p.rokId, p.id FROM FK.plankont AS p
    WHERE p.rokId = p4.rokId AND p.syntet = p4.syntet
    AND p.typ1 = p4.typ1 AND p.wart1 = p4.wart1
    AND p.typ2 = p4.typ2 AND p.wart2 = p4.wart2
    AND p.typ3 = p4.typ3 AND p.wart3 = p4.wart3
    AND p.typ4 = p4.typ4 AND p.wart4 = p4.wart4
    AND p.typ5 = 0 AND p.wart5 = @poz5 AND @poz5 = 0

    UNION ALL

    SELECT p.rokId, p.id FROM FK.plankont AS p
    JOIN SSCommon.STElements AS se ON se.ElementKindId = p.typ5 AND se.Position > 0
    WHERE p.rokId = p4.rokId AND p.syntet = p4.syntet
    AND p.typ1 = p4.typ1 AND p.wart1 = p4.wart1
    AND p.typ2 = p4.typ2 AND p.wart2 = p4.wart2
    AND p.typ3 = p4.typ3 AND p.wart3 = p4.wart3
    AND p.typ4 = p4.typ4 AND p.wart4 = p4.wart4
    AND p.typ5 > 0 AND p.wart5 = 0
    AND se.Position = @poz5

    UNION ALL

    SELECT p.rokId, p.id FROM FK.plankont AS p
    CROSS JOIN FK.urzad_sk AS us
    WHERE p.rokId = p4.rokId AND p.syntet = p4.syntet
    AND p.typ1 = p4.typ1 AND p.wart1 = p4.wart1
    AND p.typ2 = p4.typ2 AND p.wart2 = p4.wart2
    AND p.typ3 = p4.typ3 AND p.wart3 = p4.wart3
    AND p.typ4 = p4.typ4 AND p.wart4 = p4.wart4
    AND p.typ5 = 1000004 AND p.wart5 = 0
    AND us.pozycja = @poz5

) AS p5

WHERE p0.rokId = @rokId AND p0.syntet = @synt
AND p0.typ1 = 0 AND p0.wart1 = 0 
AND p0.typ2 = 0 AND p0.wart2 = 0 
AND p0.typ3 = 0 AND p0.wart3 = 0 
AND p0.typ4 = 0 AND p0.wart4 = 0 
AND p0.typ5 = 0 AND p0.wart5 = 0
)