CREATE FUNCTION [dbo].[CCAK_VYSV_PredmetyStudenta] (
    @PAR_ORGANIZACE_ID ID,
    @PAR_OBDOBI_ID_R ID,
    @PAR_OBDOBI_ID_P ID,
    @PAR_SKUPINA_ID ID,
    @PAR_OSOBA_ID ID,
    @PAR_VYSVEDCENI_ID ID
)
RETURNS @PREDMETY_TABLE TABLE (
    ORGANIZACE_ID ID,
    VYSVEDCENI_ID ID,
    OBDOBI_ID_R ID,
    OBDOBI_ID_P ID,
    OSOBA_ID ID,
    REALIZACE_ID ID,
    NAZEV VARCHAR(255),
    PORADI_NA_VYSVEDCENI INT,
    PRIZNAK_ZAMEK BIT,
    PRIZNAK_DRUH_PREDMETU VARCHAR(10)
)
AS
BEGIN
    DECLARE
        @POUZIT_STUDIJNI_PLAN BIT,
        @MA_STUDENT_ULOZENE_VYSVEDCENI BIT

    -- zamezeni parameter sniffingu (prekopirovani parametru do promennych)
    DECLARE
        @ORGANIZACE_ID ID,
        @OBDOBI_ID_R ID,
        @OBDOBI_ID_P ID,
        @SKUPINA_ID ID,
        @OSOBA_ID ID,
        @VYSVEDCENI_ID ID

    SET @ORGANIZACE_ID = @PAR_ORGANIZACE_ID
    SET @OBDOBI_ID_R = @PAR_OBDOBI_ID_R
    SET @OBDOBI_ID_P = @PAR_OBDOBI_ID_P
    SET @SKUPINA_ID = @PAR_SKUPINA_ID
    SET @OSOBA_ID = @PAR_OSOBA_ID
    SET @VYSVEDCENI_ID = @PAR_VYSVEDCENI_ID


    SET @POUZIT_STUDIJNI_PLAN = CASE WHEN (dbo.CCAK_CtiParametrChar(@ORGANIZACE_ID, NULL, NULL, 'POUZIVAT_UCEBNI_PLAN') = 'ANO') THEN 1 ELSE 0 END;

    SET @MA_STUDENT_ULOZENE_VYSVEDCENI = CASE WHEN EXISTS (
            SELECT NULL
            FROM CCAK_VYSVEDCENI
            WHERE ORGANIZACE_ID = @ORGANIZACE_ID
            AND OBDOBI_ID_R = @OBDOBI_ID_R
            AND OBDOBI_ID_P = @OBDOBI_ID_P
            AND SKUPINA_ID = @SKUPINA_ID
            AND OSOBA_ID = @OSOBA_ID
    ) THEN 1 ELSE 0 END;


    -- zkusim najit ulozenou definici vysvedceni studenta
    IF (@VYSVEDCENI_ID IS NULL)
    BEGIN
        SET @VYSVEDCENI_ID = (
            SELECT VYSVEDCENI_ID
            FROM CCAK_VYSVEDCENI
            WHERE ORGANIZACE_ID = @ORGANIZACE_ID
            AND OBDOBI_ID_R = @OBDOBI_ID_R
            AND OBDOBI_ID_P = @OBDOBI_ID_P
            AND SKUPINA_ID = @SKUPINA_ID
            AND OSOBA_ID = @OSOBA_ID
            );
    END

    -- zkusim najit ulozenou definici vysvedceni tridy
    IF (@VYSVEDCENI_ID IS NULL)
    BEGIN
        SET @VYSVEDCENI_ID = (
            SELECT VYSVEDCENI_ID
            FROM CCAK_VYSVEDCENI
            WHERE ORGANIZACE_ID = @ORGANIZACE_ID
            AND OBDOBI_ID_R = @OBDOBI_ID_R
            AND OBDOBI_ID_P = @OBDOBI_ID_P
            AND SKUPINA_ID = @SKUPINA_ID
            AND OSOBA_ID IS NULL
            );
    END

    IF (@VYSVEDCENI_ID IS NULL)
        RETURN


    -- nacteni ulozenych predmetu osoby
    INSERT INTO @PREDMETY_TABLE (
        ORGANIZACE_ID,
        VYSVEDCENI_ID,
        OBDOBI_ID_R,
        OBDOBI_ID_P,
        OSOBA_ID,
        REALIZACE_ID,
        NAZEV,
        PORADI_NA_VYSVEDCENI,
        PRIZNAK_ZAMEK,
        PRIZNAK_DRUH_PREDMETU
    )
    SELECT
    vys.ORGANIZACE_ID, vys.VYSVEDCENI_ID, vys.OBDOBI_ID_R, vys.OBDOBI_ID_P, @OSOBA_ID, vys_real.REALIZACE_ID, REAL.NAZEV, vys_real.PORADI, vys_real.PRIZNAK_ZAMEK
    , REAL.PRIZNAK_DRUH_PREDMETU
    FROM CCAK_VYSVEDCENI vys
    INNER JOIN CCAK_VYSVEDCENI_REALIZACE vys_real ON vys_real.VYSVEDCENI_ID = vys.VYSVEDCENI_ID
    INNER JOIN CCAK_REALIZACE_VZD_JEDNOTEK REAL ON REAL.ORGANIZACE_ID = vys.ORGANIZACE_ID AND REAL.OBDOBI_ID = vys.OBDOBI_ID_R AND REAL.REALIZACE_ID = vys_real.REALIZACE_ID
    WHERE vys.VYSVEDCENI_ID = @VYSVEDCENI_ID


    IF (@POUZIT_STUDIJNI_PLAN = 1) BEGIN
        UPDATE PREDMET
        SET PREDMET.PRIZNAK_DRUH_PREDMETU = (
            SELECT TOP 1 spo.DRUH_PREDMETU
            FROM CCAK_STUDIJNI_PLAN_OSOBY AS spo
            INNER JOIN CCAK_REALIZACE_VZD_JEDNOTEK AS rvj ON spo.ORGANIZACE_ID = rvj.ORGANIZACE_ID AND spo.OBDOBI_ID = rvj.OBDOBI_ID AND spo.REALIZACE_ID = rvj.REALIZACE_ID
            WHERE (spo.ORGANIZACE_ID = @ORGANIZACE_ID)
            AND (spo.OSOBA_ID = @OSOBA_ID)
            AND isnull(rvj.NAZEV, '') = isnull(PREDMET.NAZEV, '')
        )
        FROM @PREDMETY_TABLE PREDMET
    END


    -- pomocna tabulka pro rozvrhovane/uzavrene predmety
    DECLARE @PREDMETY_ROZVRHU_TABLE TABLE (
        REALIZACE_ID ID,
        ZKRATKA VARCHAR(20),
        PORADI_ZOBRAZENI NUMERIC(5,0),
        NAZEV VARCHAR(255),
        PRIZNAK_DRUH_PREDMETU VARCHAR(1),
        DRUH_VZD_JEDNOTKY VARCHAR(1)
    )

    -- povinne rozvrhovane/uzavrene predmety studenta
    INSERT INTO @PREDMETY_ROZVRHU_TABLE (REALIZACE_ID, ZKRATKA, PORADI_ZOBRAZENI, NAZEV, PRIZNAK_DRUH_PREDMETU, DRUH_VZD_JEDNOTKY)
    SELECT
    ud.REALIZACE_ID, pr.ZKRATKA, pr.PORADI_ZOBRAZENI, pr.NAZEV, pr.PRIZNAK_DRUH_PREDMETU, pr.DRUH_VZD_JEDNOTKY
    FROM CCAK_REALIZACE_VZD_JEDNOTEK pr
    INNER JOIN CCAK_UDALOST ud ON pr.ORGANIZACE_ID = ud.ORGANIZACE_ID AND pr.OBDOBI_ID = ud.OBDOBI_ID_R AND pr.REALIZACE_ID = ud.REALIZACE_ID
    INNER JOIN CCAK_UDALOST_STUDIJNI_SKUPINY uss ON ud.ORGANIZACE_ID = uss.ORGANIZACE_ID AND ud.UDALOST_ID = uss.UDALOST_ID
    INNER JOIN CCAK_OSOBY_VE_SKUPINE ovs ON uss.ORGANIZACE_ID = ovs.ORGANIZACE_ID AND uss.OBDOBI_ID = ovs.OBDOBI_ID AND uss.SKUPINA_ID = ovs.SKUPINA_ID
    INNER JOIN CCAK_CIS_VERZI_ROZVRHU cvr ON ud.ORGANIZACE_ID = cvr.ORGANIZACE_ID AND ud.OBDOBI_ID_P = cvr.OBDOBI_ID AND ud.VERZE_ID = cvr.VERZE_ID
    WHERE (pr.ORGANIZACE_ID = @ORGANIZACE_ID)
    AND (ovs.OSOBA_ID = @OSOBA_ID)
    AND (ud.OBDOBI_ID_R = @OBDOBI_ID_R)
    AND (ud.OBDOBI_ID_P = @OBDOBI_ID_P OR dbo.CCAK_JeDrivejsiObdobiRoku(ud.ORGANIZACE_ID, ud.OBDOBI_ID_P, @OBDOBI_ID_P) = 1)
    AND (ud.TYP_UDALOSTI_ID = 'ROZVRH')
    AND (cvr.PRIZNAK_PLATNA = 1)

    UNION

    SELECT re.REALIZACE_ID, re.ZKRATKA, re.PORADI_ZOBRAZENI, re.NAZEV, re.PRIZNAK_DRUH_PREDMETU, re.DRUH_VZD_JEDNOTKY
    FROM CCAK_UZAVERKA_HODN AS uz
    INNER JOIN CCAK_REALIZACE_VZD_JEDNOTEK re ON re.ORGANIZACE_ID = uz.ORGANIZACE_ID AND re.OBDOBI_ID = uz.OBDOBI_ID_PREDMET AND re.REALIZACE_ID = uz.REALIZACE_ID
    WHERE (uz.ORGANIZACE_ID = @ORGANIZACE_ID)
    AND (uz.OBDOBI_ID_PREDMET = @OBDOBI_ID_R)
    AND (uz.OBDOBI_ID = @OBDOBI_ID_P OR dbo.CCAK_JeDrivejsiObdobiRoku(uz.ORGANIZACE_ID, uz.OBDOBI_ID, @OBDOBI_ID_P) = 1)
    AND (uz.OSOBA_ID = @OSOBA_ID)

    IF (@POUZIT_STUDIJNI_PLAN = 1) BEGIN
        UPDATE PREDMET
        SET PREDMET.PRIZNAK_DRUH_PREDMETU = (
            SELECT TOP 1 spo.DRUH_PREDMETU
            FROM CCAK_STUDIJNI_PLAN_OSOBY AS spo
            INNER JOIN CCAK_REALIZACE_VZD_JEDNOTEK AS rvj ON spo.ORGANIZACE_ID = rvj.ORGANIZACE_ID AND spo.OBDOBI_ID = rvj.OBDOBI_ID AND spo.REALIZACE_ID = rvj.REALIZACE_ID
            WHERE (spo.ORGANIZACE_ID = @ORGANIZACE_ID)
            AND (spo.OSOBA_ID = @OSOBA_ID)
            AND isnull(rvj.NAZEV, '') = isnull(PREDMET.NAZEV, '')
        )
        FROM @PREDMETY_ROZVRHU_TABLE PREDMET
    END

    DELETE FROM @PREDMETY_ROZVRHU_TABLE
    WHERE (PRIZNAK_DRUH_PREDMETU NOT IN ('P', 'V'))

    DELETE ULOZENY
    FROM @PREDMETY_TABLE ULOZENY
    WHERE ULOZENY.PRIZNAK_ZAMEK = 0
    AND PRIZNAK_DRUH_PREDMETU IN ('P', 'V')
    AND NOT EXISTS (
        SELECT NULL
        FROM @PREDMETY_ROZVRHU_TABLE POVINNY
        WHERE ULOZENY.NAZEV = POVINNY.NAZEV
    )

    -- cistim pomocnou tabulku
    DELETE FROM @PREDMETY_ROZVRHU_TABLE

    -- nepovinne rozvrhovane/uzavrene predmety studenta
    INSERT INTO @PREDMETY_ROZVRHU_TABLE (
        REALIZACE_ID, ZKRATKA, PORADI_ZOBRAZENI, NAZEV, PRIZNAK_DRUH_PREDMETU, DRUH_VZD_JEDNOTKY)
    SELECT
    ud.REALIZACE_ID, pr.ZKRATKA, pr.PORADI_ZOBRAZENI, pr.NAZEV, pr.PRIZNAK_DRUH_PREDMETU, pr.DRUH_VZD_JEDNOTKY
    FROM CCAK_REALIZACE_VZD_JEDNOTEK pr
    INNER JOIN CCAK_CIS_OBDOBI obd ON pr.ORGANIZACE_ID = obd.ORGANIZACE_ID AND pr.OBDOBI_ID = obd.OBDOBI_ID
    INNER JOIN CCAK_UDALOST ud ON pr.ORGANIZACE_ID = ud.ORGANIZACE_ID AND pr.OBDOBI_ID = ud.OBDOBI_ID_R AND pr.REALIZACE_ID = ud.REALIZACE_ID
    INNER JOIN CCAK_UDALOST_STUDIJNI_SKUPINY uss ON ud.ORGANIZACE_ID = uss.ORGANIZACE_ID AND ud.UDALOST_ID = uss.UDALOST_ID
    INNER JOIN CCAK_OSOBY_VE_SKUPINE ovs ON uss.ORGANIZACE_ID = ovs.ORGANIZACE_ID AND uss.OBDOBI_ID = ovs.OBDOBI_ID AND uss.SKUPINA_ID = ovs.SKUPINA_ID
    INNER JOIN CCAK_CIS_VERZI_ROZVRHU cvr ON ud.ORGANIZACE_ID = cvr.ORGANIZACE_ID AND ud.OBDOBI_ID_P = cvr.OBDOBI_ID AND ud.VERZE_ID = cvr.VERZE_ID
    WHERE (pr.ORGANIZACE_ID = @ORGANIZACE_ID)
    AND (ovs.OSOBA_ID = @OSOBA_ID)
    AND (ud.OBDOBI_ID_R = @OBDOBI_ID_R)
    AND (ud.OBDOBI_ID_P = @OBDOBI_ID_P OR dbo.CCAK_JeDrivejsiObdobiRoku(ud.ORGANIZACE_ID, ud.OBDOBI_ID_P, @OBDOBI_ID_P) = 1)
    AND ((ud.TYP_UDALOSTI_ID = 'ROZVRH') OR (ud.TYP_UDALOSTI_ID = 'ZAJMOVY_UTVAR' AND obd.DATUM_OD < '2012-09-01T00:00:00'))
    AND (cvr.PRIZNAK_PLATNA = 1)

    UNION

    SELECT re.REALIZACE_ID, re.ZKRATKA, re.PORADI_ZOBRAZENI, re.NAZEV, re.PRIZNAK_DRUH_PREDMETU, re.DRUH_VZD_JEDNOTKY
    FROM CCAK_UZAVERKA_HODN AS uz
    INNER JOIN CCAK_REALIZACE_VZD_JEDNOTEK re ON re.ORGANIZACE_ID=uz.ORGANIZACE_ID AND re.OBDOBI_ID=uz.OBDOBI_ID_PREDMET AND re.REALIZACE_ID=uz.REALIZACE_ID
    INNER JOIN CCAK_CIS_OBDOBI obd ON re.ORGANIZACE_ID = obd.ORGANIZACE_ID AND re.OBDOBI_ID = obd.OBDOBI_ID
    WHERE (uz.ORGANIZACE_ID = @ORGANIZACE_ID)
    AND (uz.OBDOBI_ID_PREDMET = @OBDOBI_ID_R)
    AND (uz.OBDOBI_ID = @OBDOBI_ID_P OR dbo.CCAK_JeDrivejsiObdobiRoku(uz.ORGANIZACE_ID, uz.OBDOBI_ID, @OBDOBI_ID_P) = 1)
    AND ((obd.DATUM_OD >= '2012-09-01T00:00:00' AND NOT re.DRUH_VZD_JEDNOTKY = 'Z') OR (obd.DATUM_OD < '2012-09-01T00:00:00')) -- zájmové předněty se po 1.9.2012 neuvádějí
    AND (uz.OSOBA_ID = @OSOBA_ID)

    IF (@POUZIT_STUDIJNI_PLAN = 1) BEGIN
        UPDATE PREDMET
        SET PREDMET.PRIZNAK_DRUH_PREDMETU = (
            SELECT TOP 1 spo.DRUH_PREDMETU
            FROM CCAK_STUDIJNI_PLAN_OSOBY AS spo
            INNER JOIN CCAK_REALIZACE_VZD_JEDNOTEK AS rvj ON spo.ORGANIZACE_ID = rvj.ORGANIZACE_ID AND spo.OBDOBI_ID = rvj.OBDOBI_ID AND spo.REALIZACE_ID = rvj.REALIZACE_ID
            WHERE (spo.ORGANIZACE_ID = @ORGANIZACE_ID)
            AND (spo.OSOBA_ID = @OSOBA_ID)
            AND isnull(rvj.NAZEV, '') = isnull(PREDMET.NAZEV, '')
        )
        FROM @PREDMETY_ROZVRHU_TABLE PREDMET
    END

    DELETE FROM @PREDMETY_ROZVRHU_TABLE
    WHERE (PRIZNAK_DRUH_PREDMETU IN ('P', 'V'))

    DELETE ULOZENY
    FROM @PREDMETY_TABLE ULOZENY
    WHERE ULOZENY.PRIZNAK_ZAMEK = 0
    AND (PRIZNAK_DRUH_PREDMETU NOT IN ('P', 'V') OR PRIZNAK_DRUH_PREDMETU IS NULL)
    AND NOT EXISTS (
        SELECT NULL
        FROM @PREDMETY_ROZVRHU_TABLE NEPOVINNY
        WHERE ULOZENY.NAZEV = NEPOVINNY.NAZEV
    )

    RETURN
END

GO