-- Grupa 1

-- Pojebane zadanie 1
CREATE OR REPLACE
PROCEDURE zad1(
    im IN UCZNIOWIE.IMIE%TYPE, nazw IN UCZNIOWIE.NAZWISKO%TYPE,
    dataur IN UCZNIOWIE.DATA_UR%TYPE
) IS
    iducz UCZNIOWIE.ID_UCZNIA%TYPE;

    CURSOR cur IS
        SELECT ID_UCZNIA
        FROM UCZNIOWIE
        WHERE IMIE = im AND
            NAZWISKO = nazw;
BEGIN
    INSERT INTO KLASY
    VALUES (10, 'Vk', 10);

    OPEN cur;

    FETCH cur INTO iducz;

    IF cur%found THEN
        DBMS_OUTPUT.PUT_LINE('Uczen znajduje sie w bazie');
    ELSE
        INSERT INTO UCZNIOWIE
        VALUES (20, nazw, im, dataur, 10);
    END IF;

    CLOSE cur;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Blad: ' || SQLERRM)
END;

-- Zadanie 2

CREATE OR REPLACE
FUNCTION zad2(
    przed_nazw OUT PRZEDMIOTY.NAZWA%TYPE, im OUT NAUCZYCIELE.IMIE%TYPE,
    nazw OUT NAUCZYCIELE.NAZWISKO%TYPE
) RETURN NUMBER IS
    srednia NUMBER;
BEGIN
    SELECT P.NAZWA, N.IMIE, N.NAZWISKO, AVG(O.OCENA)
    INTO przed_nazw, im, nazw, srednia
    FROM PRZEDMIOTY P, NAUCZYCIELE N, OCENY O
    WHERE P.ID_PRZEDMIOTU = N.ID_PRZEDMIOTU AND
        O.ID_PRZEDMIOTU = P.ID_PRZEDMIOTU AND
        O.ID_NAUCZYCIELA = N.ID_NAUCZYCIELA
    GROUP BY P.NAZWA, N.IMIE, N.NAZWISKO
    ORDER BY AVG(O.OCENA) DESC
    FETCH FIRST ROW ONLY;

    RETURN srednia;
END;


-- Zadanie 3 też pojebane

CREATE OR REPLACE
PROCEDURE zad3(
    im IN OUT NAUCZYCIELE.IMIE%TYPE, nazw IN OUT NAUCZYCIELE.NAZWISKO%TYPE,
    srednia OUT NUMBER
) IS
BEGIN
    SELECT N.IMIE, N.NAZWISKO, AVG(O.OCENA)
    INTO im, nazw, srednia
    FROM NAUCZYCIELE N, OCENA O, KLASY K
    WHERE K.ID_NAUCZYCIELA = N.ID_NAUCZYCIELA AND
        O.ID_NAUCZYCIELA = N.ID_NAUCZYCIELA
    GROUP BY N.IMIE, N.NAZWISKO;
END;

-- Zadanie 4

CREATE OR REPLACE
TRIGGER zad4
BEFORE UPDATE OR INSERT ON UCZNIOWIE
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        INSERT INTO TEST
        VALUES ('UPDATE ' || :OLD.IMIE || ' ' :NEW.IMIE);
    ELSE IF INSERTING THEN
        INSERT INTO TEST
        VALUES ('INSERT ' || :NEW.IMIE);
    END IF;
END;

-- Zadanie 5

CREATE OR REPLACE
PACKAGE zad5
IS
    PROCEDURE zad1(
        im IN UCZNIOWIE.IMIE%TYPE, nazw IN UCZNIOWIE.NAZWISKO%TYPE,
        dataur IN UCZNIOWIE.DATA_UR%TYPE);

    FUNCTION zad2(
        przed_nazw OUT PRZEDMIOTY.NAZWA%TYPE, im OUT NAUCZYCIELE.IMIE%TYPE,
        nazw OUT NAUCZYCIELE.NAZWISKO%TYPE
    ) RETURN NUMBER;

    PROCEDURE zad3(
        im IN OUT NAUCZYCIELE.IMIE%TYPE, nazw IN OUT NAUCZYCIELE.NAZWISKO%TYPE,
        srednia OUT NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY zad5
IS
    -- TO SAMO CO zad1 - zad3 tylko bez
    -- CREATE OR REPLACE
END;

-- Grupa 4

-- Zadanie 1
CREATE OR REPLACE
PROCEDURE zad1(
    im IN CZYTELNICY.IMIE%TYPE, nazw IN CZYTELNICY.NAZWISKO%TYPE,
    idksiazki IN WYPOZYCZENIA.ID_KSIAZKI%TYPE,
    datawyp IN WYPOZYCZENIA.DATA_WYP%TYPE,
    datazwr IN WYPOZYCZENIA.DATA_ZWR%TYPE
) IS
    czyt CZYTELNICY.ID_CZYTELNIKA%TYPE;

    CURSOR cur IS
        SELECT ID_CZYTELNIKA
        FROM CZYTELNICY
        WHERE IMIE = im AND
            NAZWISKO = nazw;
BEGIN
    OPEN cur;

    FETCH cur INTO czyt;

    IF cur%notfound THEN
        INSERT INTO CZYTELNICY
        VALUES (10, nazw, imie, NULL, NULL, NULL, NULL, NULL, 0)
        RETURNING INTO czyt;
    END IF;

    CLOSE cur;

    INSERT INTO WYPOZYCZENIA
    VALUES (10, czyt.id_czytelnika, idksiazki, datawyp, datazwr);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Błąd: ' || SQLERRM);
END;

-- Zadanie 2

CREATE OR REPLACE
FUNCTION zad2(
    od IN WYPOZYCZENIA.DATA_WYP%TYPE,
    DO IN WYPOZYCZENIA.DATA_ZWR%TYPE
) RETURN NUMBER IS
    licznik NUMBER;
    CURSOR cur IS
        SELECT K.TYTUL, K.DATA_WYD
        FROM KSIAZKI K, WYPOZYCZENIA W
        WHERE K.ID_KSIAZKI = W.ID_KSIAZKI
        GROUP BY K.ID_KSIAZKI
        ORDER BY DATA_WYD DESC;
BEGIN
    FOR k IN cur LOOP
        DBMS_OUTPUT.PUT_LINE(k.tytul);
    END LOOP;

    SELECT COUNT(*)
    INTO licznik
    FROM KSIAZKI
    WHERE DATA_WYD >= '2015-01-01';

    RETURN licznik;
END;

-- Zadanie 3

CREATE OR REPLACE
PROCEDURE zad3(
    nazgat IN GATUNEK.NAZWA%TYPE, liczbaksiazek OUT NUMBER
) IS
    CURSOR cur IS
        SELECT A.IMIE, A.NAZWISKO, K.TYTUL, G.NAZWA
        FROM GATUNEK G, KSIAZKI K, AUTORZY A
        WHERE K.ID_GATUNEK = G.ID_GATUNEK AND
            K.ID_AUTORA = A.ID_AUTORA AND
            G.NAZWA = nazgat AND
            K.DATA_WYD > DATEADD(YEAR, -1, SYSDATE)
        ORDER BY A.NAZWISKO, A.IMIE;
BEGIN
    FOR k IN cur LOOP
        DBMS_OUTPUT.PUT_LINE(A.IMIE || ' ' || A.NAZWISKO
            || ' ' || K.TYTUL || ' ' || G.NAZWA);
    END LOOP;

    SELECT COUNT(*)
    INTO liczbaksiazek
    FROM KSIAZKI K, GATUNEK G
    WHERE K.ID_GATUNEK = G.ID_GATUNEK;
END;

-- Zadanie 4

-- Chuj wie

-- Zadanie 5

CREATE OR REPLACE PACKAGE zad5
IS
    PROCEDURE zad1(
        im IN CZYTELNICY.IMIE%TYPE, nazw IN CZYTELNICY.NAZWISKO%TYPE,
        idksiazki IN WYPOZYCZENIA.ID_KSIAZKI%TYPE,
        datawyp IN WYPOZYCZENIA.DATA_WYP%TYPE,
        datazwr IN WYPOZYCZENIA.DATA_ZWR%TYPE
    );

    FUNCTION zad2(
        od IN WYPOZYCZENIA.DATA_WYP%TYPE, DO IN WYPOZYCZENIA.DATA_ZWR%TYPE
    ) RETURN NUMBER;

    PROCEDURE zad3(nazgat IN GATUNEK.NAZWA%TYPE, liczbaksiazek OUT NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY zad5
IS
    -- TO SAMO CO zad1 - zad3 tylko bez
    -- CREATE OR REPLACE
END;

-- Grupa 5

-- Zadanie 1

CREATE OR REPLACE
PROCEDURE zad1(
    im IN PACJENCI.IMIE%TYPE, nazw IN PACJENCI.NAZWISKO%TYPE
) IS
    id_pac PACJENCI.ID_PACJENTA%TYPE;

    CURSOR cur IS
        SELECT ID_PACJENTA
        FROM PACJENCI
        WHERE IMIE = im AND
            NAZWISKO = nazw;
BEGIN
    OPEN cur;
    FETCH cur INTO id_pac;

    IF cur%notfound THEN
        INSERT INTO PACJENCI
        VALUES (seq_pacjenci.NEXTVAL, nazw, im, NULL, NULL, NULL, NULL, NULL)
        RETURING ID_PACJENTA INTO id_pac;
    END IF;

    CLOSE cur;

    INSERT INTO WIZYTY
    VALUES (seq_wizyty.NEXTVAL, id_pac, NULL, NULL, NULL, NULL);
EXCEPTION
    WHEN INVALID_CURSOR THEN
        DBMS_OUTPUT.PUT_LINE('Bledny kursor');
END;

-- Zadanie 2

CREATE OR REPLACE
FUNCTION zad2(
    OD IN WIZYTY.DATA%TYPE, DO IN  WIZYTY.DATA%TYPE
) RETURN NUMBER IS
    CURSOR cur IS
        SELECT P.IMIE, P.NAZWISKO, W.DATA, W.GODZINA, L.IMIE "LIMIE", L.NAZWISKO "LNAZWISKO"
        FROM PACJENCI P, WIZYTY W, LEKARZE L
        WHERE P.ID_PACJENTA = W.ID_PACJENTA AND
            W.ID_LEKARZA = L.ID_LEKARZA AND
            W.DATA >= OD AND
            W.DATA <= DO
BEGIN
    FOR p IN cur LOOP
        DBMS_OUTPUT.PUT_LINE(p.imie || ' ' || p.nazwisko || ' '
            || p.data || ' ' || p.godzina || ' ' || p.limie
            || ' ' || p.lnazwisko);
    END LOOP;

    SELECT COUNT(*)
    INTO liczba_wizyt
    FROM WIZYTY
    WHERE DATA >= OD AND
        DATA <= DO
    GROUP BY ID_PACJENTA
    ORDER BY COUNT(*) DESC;

    RETURN liczba_wizyt;
END;

-- Zadanie 3

CREATE OR REPLACE
PROCEDURE zad3(
    im OUT LEKARZE.IMIE%TYPE, nazw OUT LEKARZE.NAZWISKO%TYPE
) IS
    max_pac NUMBER := 0;
    CURSOR cur IS
        SELECT L.IMIE, L.NAZWISKO, COUNT(DISTINCT(W.ID_PACJENTA)) "ILOSC"
        FROM LEKARZE L, WIZYTY W
        WHERE W.ID_LEKARZA = L.ID_LEKARZA
        ORDER BY L.NAZWISKO, L.IMIE;
BEGIN
    FOR l IN cur LOOP
        IF l.ilosc > max_pac THEN
            im := l.imie;
            nazw := l.nazwisko;
        END;

        DBMS_OUTPUT.PUT_LINE(l.imie || ' ' || l.nazwisko || ' ' || l.ilosc);
    END LOOP;
END;

-- Zadanie 4

CREATE OR REPLACE
TRIGGER zad4
BEFORE DELETE OR INSERT ON WIZYTY
FOR EACH ROW
BEGIN
    IF DELETING THEN
        INSERT INTO TEST
        VALUES (seq_test.NEXTVAL, :OLD.ID_WIZYTY || 'DELETE'); -- :NEW jest NULLem
    ELSIF INSERTING THEN
        INSERT INTO TEST
        VALUES (seq_test.NEXTVAL, :OLD.ID_WIZYTY || 'INSERT' || :NEW.ID_WIZYTY);
    END IF;
END;

-- Zadanie 5

CREATE OR REPLACE PACKAGE zad5
IS
    PROCEDURE zad1(
        im IN PACJENCI.IMIE%TYPE, nazw IN PACJENCI.NAZWISKO%TYPE
    );

    FUNCTION zad2(
        OD IN WIZYTY.DATA%TYPE, DO IN  WIZYTY.DATA%TYPE
    ) RETURN NUMBER;

    PROCEDURE zad3(
        im OUT LEKARZE.IMIE%TYPE, nazw OUT LEKARZE.NAZWISKO%TYPE
    );
END;

CREATE OR REPLACE PACKAGE BODY zad5
IS
    -- TO SAMO CO zad1 - zad3 tylko bez
    -- CREATE OR REPLACE
END;












CREATE OR REPLACE
TRIGGER zad4
BEFORE UPDATE OR INSERT ON ROZPRAWY
FOR EACH ROW
DECLARE
    iloscrozpraw NUMBER;
    za_duzo_rozpraw EXCEPTION;
BEGIN
    SELECT COUNT(*)
    INTO iloscrozpraw
    FROM ROZPRAWY
    WHERE DATA = :NEW.DATA;

    IF iloscrozpraw >= 5 THEN
        RAISE za_duzo_rozpraw
EXCEPTION
    WHEN za_duzo_rozpraw THEN
        DBMS_OUTPUT.PUT_LINE('Za duzo rozpraw');
END;