inline

CREATE FUNCTION KwotaWplaconaPrzedTerminem (@id_naleznosci int)
RETURNS TABLE AS RETURN(
    SELECT(
    CAST(
        ISNULL(
            (
                select sum(w.kwota) from naleznosci as n
                inner join wplaty as w
                on n.id = w.naleznosci_id
                where n.termin >= w.data_wplaty AND n.id = @id_naleznosci
                group by n.id
            ),
            0.00
        )
        AS DECIMAL(15,2)
    )
    ) AS KwotaWplaconaPrzedTerminem
)

CREATE FUNCTION PoprzedniaWplata (@id_naleznosci int, @id_wplaty int)
RETURNS TABLE AS RETURN
(
    select top 1 * from
    (
        select termin from naleznosci as nn
        where nn.id = @id_naleznosci
            AND termin < ( select www.data_wplaty from wplaty as www where www.id = @id_wplaty )

        union

        select ww.data_wplaty from wplaty as ww
            where ww.data_wplaty < ( select www.data_wplaty from wplaty as www where www.id = @id_wplaty )
            AND ww.naleznosci_id = @id_naleznosci

    ) as squery

    order by case when termin is null then 1 else 0 end, termin desc
)

CREATE FUNCTION PoczatekZakresu (@id_naleznosci int, @id_wplaty int, @s_dzien date)
RETURNS TABLE AS RETURN
(
    SELECT CASE WHEN
        (SELECT * FROM PoprzedniaWplata(@id_naleznosci, @id_wplaty)) IS NULL
        THEN (SELECT dateadd(day, -1, (select ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty)))
        ELSE
        (
            SELECT
                IIF(
                    (select nn.termin from naleznosci as nn where nn.id = @id_naleznosci)
                        >= (select ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty),
                    (select ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty) ,

                    (
                        SELECT
                            IIF
                            (
                                (SELECT * FROM PoprzedniaWplata(@id_naleznosci, @id_wplaty) as pw1) > @s_dzien ,
                                (SELECT * FROM PoprzedniaWplata(@id_naleznosci, @id_wplaty) as pw2) , @s_dzien
                            )
                    )
                )
        )
        END
    AS data_od
)

CREATE FUNCTION KoniecZakresu (@id_naleznosci int, @id_wplaty int, @s_dzien date)
RETURNS TABLE AS RETURN
(
    SELECT CASE WHEN
        (SELECT ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty) <=
            (SELECT nn.termin from naleznosci as nn where nn.id = @id_naleznosci)
        THEN
            (SELECT ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty)
        ELSE (
            select top 1 * from
            (
                SELECT GETDATE() as termin

                union

                select sss.dzien from stawki as sss
                where sss.dzien > ( SELECT * FROM PoczatekZakresu (@id_naleznosci, @id_wplaty, @s_dzien) )

                union

                select wx.data_wplaty from wplaty as wx
                where wx.data_wplaty > ( SELECT * FROM PoczatekZakresu (@id_naleznosci, @id_wplaty, @s_dzien) )
                    AND wx.naleznosci_id = @id_naleznosci

            ) as data_do
            order by termin asc
        )
    END
    as data_do
)

CREATE FUNCTION RoznicaDniZakresu (@id_naleznosci int, @id_wplaty int, @s_dzien date)
RETURNS TABLE AS RETURN
(
    SELECT
    (
        datediff(
            day,
            (SELECT * FROM PoczatekZakresu(@id_naleznosci, @id_wplaty, @s_dzien)),
            (SELECT * FROM KoniecZakresu(@id_naleznosci, @id_wplaty, @s_dzien))

        )
    ) as RoznicaDniZakresu
)

CREATE FUNCTION OdsetkiPoWplacie(@id_naleznosci int, @id_wplaty int, @s_dzien date)
RETURNS TABLE AS RETURN
(
    SELECT
        IIF
        (
            ((SELECT * FROM KoniecZakresu(@id_naleznosci, @id_wplaty, @s_dzien)) -- data_do
                = (select ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty)),
            ( SUM(
                (SELECT * FROM RoznicaDniZakresu(@id_naleznosci, @id_wplaty, @s_dzien)) * IIF((select ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty) <= (select nn.termin from naleznosci as nn where nn.id = @id_naleznosci), 0, (select ss.stawka from stawki as ss where ss.dzien = @s_dzien) * 1)/365/100
            ) OVER (partition by @id_wplaty)),
        0)
    AS OdsetkiPoWplacie
)

CREATE FUNCTION CzyKoniecWplaty(@id_naleznosci int, @id_wplaty int, @s_dzien date)
RETURNS TABLE AS RETURN
(
    SELECT
        IIF(
            ((SELECT * FROM KoniecZakresu(@id_naleznosci, @id_wplaty, @s_dzien)) -- data_do
            = (select ww.data_wplaty from wplaty as ww where ww.id = @id_wplaty)), 1 , 0)
    AS CzyKoniecWplaty
)

CREATE FUNCTION CzyPierwszaWplata (@id_naleznosci int, @id_wplaty int)
RETURNS TABLE AS RETURN
(
    SELECT CASE WHEN (
        select top 1 ww.data_wplaty from wplaty as ww
        where ww.data_wplaty < ( select www.data_wplaty from wplaty as www where www.id = @id_wplaty )
            AND ww.naleznosci_id = @id_naleznosci
        order by ww.data_wplaty desc
    ) IS NULL
    THEN 1
    ELSE 0
    END
    AS CzyPierwszaWplata
)

CREATE FUNCTION Obciazenia (@id_naleznosci int, @id_wplaty int, @s_dzien date)
RETURNS TABLE AS RETURN
(
    SELECT ( IIF((SELECT * FROM CzyPierwszaWplata(@id_naleznosci, @id_wplaty)) = 1
        AND (SELECT * FROM CzyKoniecWplaty(@id_naleznosci, @id_wplaty, @s_dzien)) = 1, (select nn.kwota from naleznosci as nn where nn.id = @id_naleznosci), 0) )
    AS Obciazenia
)