mecze_management.ddl

--naglówek mecze_management
CREATE OR REPLACE PACKAGE mecze_management IS

--procedura DODAWANIA MECZU DO TABELI
PROCEDURE dodaj_mecz
(
    dm_id_gospodarza kluby.id_klubu%TYPE,
    dm_bramki_gospodarza NUMBER,
    dm_id_goscia kluby.id_klubu%TYPE,
    dm_bramki_goscia NUMBER,
    dm_nr_licencji_sedziego sędziowie.nr_licencji%TYPE,
    dm_nr_kolejki mecze.kolejka%TYPE
);


-- wyliczenie punktów dla klubów
FUNCTION wylicz_punkty
RETURN kluby.id_klubu%TYPE;


--liczba meczów klubów
FUNCTION ile_meczow
RETURN NUMBER;

END mecze_management;

/

--cialo mecze_management
CREATE OR REPLACE PACKAGE BODY mecze_management IS

--procedura DODAWANIA MECZU DO TABELI
PROCEDURE dodaj_mecz
(
    dm_id_gospodarza kluby.id_klubu%TYPE,
    dm_bramki_gospodarza NUMBER,
    dm_id_goscia kluby.id_klubu%TYPE,
    dm_bramki_goscia NUMBER,
    dm_nr_licencji_sedziego sędziowie.nr_licencji%TYPE,
    dm_nr_kolejki mecze.kolejka%TYPE
)
AS
    v_mecz_istnieje NUMBER := 0;
    v_mecz_nie_istnieje NUMBER := 0;
    v_id_meczu mecze.id_meczu%TYPE := mecze_seq.NEXTVAL;
    v_id_stadionu stadiony.id_stadionu%TYPE := 0;
BEGIN

    --sprawdzenie, na którym stadionie odbyl się mecz na podstawie 
    --podanego id gospodarza (mecz zawsze odbywa się na stadionie
    -- gospodarza)
    SELECT id_stadionu 
    INTO v_id_stadionu
    FROM kluby 
    WHERE id_klubu = dm_id_gospodarza;
    
    --sprawdzenie, czy rekord dotyczacy danego meczu (jego nr kolejki i
    --id stadionu [tym samym id gospodarza] w zupelności wystarczy, ponieważ
    --podczas jednej kolejki klub może być gospodarzem tylko raz)
    SELECT count(*)
    INTO v_mecz_istnieje
    FROM mecze m
    WHERE m.kolejka = dm_nr_kolejki AND m.id_stadionu = v_id_stadionu;
    
    --sprawdzenie, czy podany mecz w ogóle ma prawo bytu, ponieważ każdy klub 
    --w każdej kolejce powinien grać tylko jeden mecz
    SELECT count(*)
    INTO v_mecz_nie_istnieje
    FROM mecze_kluby mk
    JOIN mecze m
    ON mk.id_meczu = m.id_meczu
    WHERE m.kolejka = dm_nr_kolejki AND mk.id_klubu IN (dm_id_gospodarza, dm_id_goscia);
    
    
    IF v_mecz_nie_istnieje = 1
        THEN dbms_output.put_line('Podany mecz nie odbyl się! Któraś z podanych drużyn zagrala już mecz w tej kolejce. ');
        
    ELSIF v_mecz_istnieje = 1
        THEN dbms_output.put_line('Mecz jest już w tabeli.');
        
    ELSE     
        --wstawienie calego rekordu do tabeli mecze, wraz z numerem kolejki, punktami zdobytymi przez gospodarza i gościa, 
        --numerem licencji sędziego oraz stadionem
        IF dm_bramki_gospodarza >= 0 AND dm_bramki_goscia >=0
        THEN
            INSERT INTO mecze VALUES(v_id_meczu, dm_nr_kolejki, dm_bramki_gospodarza, dm_bramki_goscia, dm_nr_licencji_sedziego, v_id_stadionu, 0);
            
            --dodanie meczu do tabeli mecze_klubu
            INSERT INTO mecze_kluby VALUES(dm_id_gospodarza, v_id_meczu);
            INSERT INTO mecze_kluby VALUES(dm_id_goscia, v_id_meczu);
        
        ELSE 
            dbms_output.put_line('Nie można podać ujemnej liczby bramek!');
        END IF;
    END IF;
    
EXCEPTION 
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('Sprawdz ponownie wprowadzone dane.');
END dodaj_mecz;


-- wyliczenie punktów dla klubów

FUNCTION wylicz_punkty
RETURN kluby.id_klubu%TYPE
AS
v_zwyciezca kluby.id_klubu%TYPE;
id_gościa kluby.id_klubu%TYPE;

--Aby użyć operacji UPDATE w funkcji i użyć tej funkcji testujac ja w zapytaniu SELECT
--trzeba użyć PRAGMA AUTONOMOUS_TRANSACTION, a następnie "zcommitować" zmiany
pragma autonomous_transaction;

CURSOR c IS
    SELECT m.id_meczu, m.punkty_gospodarza, m.punkty_gościa, m.rozliczony, k.id_klubu as id_gospodarza
    FROM mecze m, kluby k
    WHERE m.id_stadionu = k.id_stadionu;
BEGIN
    FOR i in c
    LOOP
        --jeśli wartość pola rozliczony jest różna od zera, to znaczy, że drużyny dostaly
        --już punkty za ten mecz
        IF i.rozliczony != 0
        THEN 
            dbms_output.put_line('Mecz o id' || i.id_meczu || 'zostal juz rozliczony');
        
        --w przeciwnym wypadku rozpoczyna się wyliczanie punktów dla danego meczu
        --zwycięska drużyna otrzymuje 3 punkty, przegrana drużyna 0
        --jeśli jest remis - każda z drużyn otrzymuje po 1 punkcie
        ELSE
            --jeżeli wygral gospodarz, to sprawa jest prosta,
            --bo do id_gospodarza jest bezpośredni dostęp z kursora
            IF i.punkty_gospodarza > i.punkty_gościa
            THEN 
                UPDATE kluby
                SET punkty = punkty + 3
                WHERE id_klubu = i.id_gospodarza;
            
            --w przeciwnym wypadku wiadomo, że gościowi też trzeba dopisać
            --punkt lub 3, dlatego teraz zajmę się wyluskaniem z moich danych
            --(a dokladniej z tabeli Mecze_kluby) id_gościa
            ELSE
                SELECT id_klubu
                INTO id_gościa
                FROM mecze_kluby
                WHERE id_meczu = i.id_meczu AND id_klubu != i.id_gospodarza;
                
                --teraz, majac zarówno id_gospodarza, jak i id_gościa, można
                --przejść do sprawdzenia kolejnych opcji wyniku meczu 
                
                --jeśli jest remis
                IF i.punkty_gospodarza = i.punkty_gościa
                THEN 
                    UPDATE kluby
                    SET punkty = punkty + 1
                    WHERE id_klubu IN (i.id_gospodarza, id_gościa);
                
                --zostaje tylko opcja, że wygral gość
                ELSE
                    UPDATE kluby
                    SET punkty = punkty + 3
                    WHERE id_klubu = id_gościa;
                END IF;
                
            END IF;           
        END IF;
    END LOOP;
    
    --niezależnie od wyniku wszystkie mecze zostay on obslużone, mecz można uznać
    --wszystkie za rozliczone
    UPDATE mecze
    SET rozliczony = 1
    WHERE rozliczony = 0;
    commit;
    
    --zwrocenie id_klubu z najwieksza ilościa dotychczas zebranych punktów 
    SELECT id_klubu
    INTO v_zwyciezca
    FROM (SELECT *
          FROM kluby
          ORDER BY punkty DESC, id_klubu)
    WHERE ROWNUM <=1;
    
    return v_zwyciezca;
    
END wylicz_punkty;


--liczba meczów danego klubu

FUNCTION ile_meczow
RETURN NUMBER
AS
v_najwiecej_meczow_klub kluby.id_klubu%TYPE;

CURSOR c IS
    SELECT k.id_klubu, k.nazwa_klubu, count(UNIQUE mk.id_meczu) as liczba_meczow
    FROM mecze_kluby mk
    JOIN kluby k
    ON k.id_klubu = mk.id_klubu
    GROUP BY k.id_klubu, k.nazwa_klubu
    ORDER BY nazwa_klubu;

BEGIN
    FOR i in c
    LOOP
    dbms_output.put_line('No co jest');
        dbms_output.put_line('Klub ' || i.nazwa_klubu || ' zagral do tej pory ' || i.liczba_meczow || ' meczów.');
    END LOOP;
    
    SELECT id_klubu
    INTO v_najwiecej_meczow_klub
    FROM (
            SELECT id_klubu, count(UNIQUE id_meczu) as liczba_meczow
            FROM mecze_kluby 
            GROUP BY id_klubu
            ORDER BY liczba_meczow DESC)
    WHERE ROWNUM <= 1;
    
    return v_najwiecej_meczow_klub;

END ile_meczow;

END mecze_management;