Funckje

/* #########################    KLIENCI    ######################### */

CREATE OR REPLACE PACKAGE PKLIENT IS
  FUNCTION SELECTKLIENCI RETURN SYS_REFCURSOR;
  FUNCTION SELECTKLIENCIKONTOID (IDKONTO INT) RETURN SYS_REFCURSOR;
  PROCEDURE DELETEKONTO (KON_ID IN INT);
END;
/
CREATE OR REPLACE PACKAGE BODY PKLIENT IS
  -- POKAZ WSYZSTKICH KLIENTOW
    FUNCTION SELECTKLIENCI
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT DISTINCT K.KONTO_ID ,K.IMIE, K. NAZWISKO, K.UPRAWNIENIA, A.MIEJSCOWOSC, A.WOJEWODZTWO ,A.KOD_POCZTOWY, A.ULICA, A.NR_DOMU, A.NR_LOKALU, KT.EMAIL, KT.NR_TEL FROM KONTO K, PRACOWNIK P, ADRES A, KONTAKT KT WHERE A.ADRES_ID = K.ADRES_ID AND K.UPRAWNIENIA = 'klient' AND KT.KONTAKT_ID = K.KONTAKT_ID]';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTKLIENCI;

  -- POKAZ DANE KLIENTA PO ID
  FUNCTION SELECTKLIENCIKONTOID (IDKONTO INT)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT DISTINCT K.KONTO_ID ,K.IMIE, K. NAZWISKO, K.UPRAWNIENIA, A.MIEJSCOWOSC, A.WOJEWODZTWO ,A.KOD_POCZTOWY, A.ULICA, A.NR_DOMU, A.NR_LOKALU, KT.EMAIL, KT.NR_TEL FROM KONTO K, PRACOWNIK P, ADRES A, KONTAKT KT WHERE A.ADRES_ID = K.ADRES_ID AND K.UPRAWNIENIA = 'klient' AND KT.KONTAKT_ID = K.KONTAKT_ID AND K.KONTO_ID=]' || IDKONTO;

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTKLIENCIKONTOID;

  -- USUN KONTO O ZADANYM ID
  PROCEDURE DELETEKONTO
  (KON_ID IN INT)
  AS
  BEGIN
    DELETE FROM KONTO
    WHERE KONTO_ID = KON_ID;
  END DELETEKONTO;
END;
/



/* #########################    PRACOWNICY    ######################### */

CREATE OR REPLACE PACKAGE PPRACOWNIK IS
  FUNCTION SELECTPRACOWNICY RETURN SYS_REFCURSOR;
  FUNCTION SELECTPRACOWNIKKONTOID (IDKONTO INT) RETURN SYS_REFCURSOR;
  PROCEDURE INSERTPRACOWNIK (KON_ID IN INT, PEN IN FLOAT, PREM IN FLOAT);
END;
/
CREATE OR REPLACE PACKAGE BODY PPRACOWNIK IS
    -- POKAZ WSZYSTKICH PRACOWNIKOW
  FUNCTION SELECTPRACOWNICY
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := 'SELECT K.KONTO_ID ,K.IMIE, K.NAZWISKO, K.UPRAWNIENIA, P.DATA_ZATRUDNIENIA, P.DATA_ZWOLNIENIA, P.PENSJA, P.PREMIA, A.MIEJSCOWOSC, A.WOJEWODZTWO ,A.KOD_POCZTOWY, A.ULICA, A.NR_DOMU, A.NR_LOKALU, KT.EMAIL, KT.NR_TEL FROM KONTO K, PRACOWNIK P, ADRES A, KONTAKT KT WHERE K.KONTO_ID = P.KONTO_ID AND A.ADRES_ID = K.ADRES_ID AND KT.KONTAKT_ID = K.KONTAKT_ID';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTPRACOWNICY;

  -- POKAZ DANE PRACOWNIKA PO ID
  FUNCTION SELECTPRACOWNIKKONTOID(IDKONTO INT)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT DISTINCT K.KONTO_ID ,K.IMIE, K. NAZWISKO, K.UPRAWNIENIA, A.MIEJSCOWOSC, A.WOJEWODZTWO ,A.KOD_POCZTOWY, A.ULICA, A.NR_DOMU, A.NR_LOKALU, KT.EMAIL, KT.NR_TEL FROM KONTO K, PRACOWNIK P, ADRES A, KONTAKT KT WHERE A.ADRES_ID = K.ADRES_ID AND K.UPRAWNIENIA = 'pracownik' AND KT.KONTAKT_ID = K.KONTAKT_ID AND K.KONTO_ID=]' || IDKONTO;

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTPRACOWNIKKONTOID;

  -- DODAJ PRACOWNIKA
  -- Trigger zmienia automatycznie uprawnienia
  PROCEDURE INSERTPRACOWNIK
         (
          KON_ID IN INT,
          PEN IN FLOAT,
          PREM IN FLOAT
         )
  AS
  BEGIN
      INSERT INTO PRACOWNIK(KONTO_ID, PENSJA, PREMIA) VALUES (KON_ID, PEN, PREM);

  END INSERTPRACOWNIK;
END;
/



/* #########################    KURIERZY    ######################### */

CREATE OR REPLACE PACKAGE PKURIER IS
  FUNCTION SELECTKURIERZY RETURN SYS_REFCURSOR;
  FUNCTION SELECTKURIERID (KURIER_ID INT) RETURN SYS_REFCURSOR;
  PROCEDURE INSERTKURIER (NAZ_FIRMY IN VARCHAR2);
  PROCEDURE DELETEKURIER (KON_ID IN INT);
END;
/
CREATE OR REPLACE PACKAGE BODY PKURIER IS
   -- POKAZ WSYZSTKICH KURIEROW
  FUNCTION SELECTKURIERZY
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := 'SELECT * FROM KURIER';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTKURIERZY;

  -- POKAZ KURIER PO ID
  FUNCTION SELECTKURIERID(KURIER_ID INT)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := 'SELECT * FROM KURIER WHERE KURIER_ID = ' || KURIER_ID;

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTKURIERID;

  -- DODAJ KURIERA
  PROCEDURE INSERTKURIER(NAZ_FIRMY IN VARCHAR2)
  AS
  BEGIN

      INSERT INTO KURIER(NAZWA_FIRMY) VALUES (NAZ_FIRMY);

  END INSERTKURIER;

  -- USUN KURIERA O ID
  PROCEDURE DELETEKURIER
  (KON_ID IN INT)
  AS
  BEGIN
    DELETE FROM KURIER
    WHERE KURIER_ID = KON_ID;
  END DELETEKURIER;
END;
/



/* #########################    DOSTAWCY    ######################### */

CREATE OR REPLACE PACKAGE PDOSTAWCA IS
  FUNCTION SELECTDOSTAWCA RETURN SYS_REFCURSOR;
  FUNCTION SELECTDOSTAWCAID (DOSTAW_ID INT) RETURN SYS_REFCURSOR;
  PROCEDURE INSERTDOSTAWCA (NAZWA_FIR IN VARCHAR2, MIEJSC IN VARCHAR2, WOJ IN VARCHAR2, KOD_POCZT IN VARCHAR2, UL IN VARCHAR2, NR_DOM IN INT, NR_LOK IN INT, MAIL IN VARCHAR2, TEL IN VARCHAR2, FAX_1 IN VARCHAR2, WWW_1 IN VARCHAR2);
  PROCEDURE DELETEDOSTAWCA (KON_ID IN INT);
END;
/
CREATE OR REPLACE PACKAGE BODY PDOSTAWCA IS
   -- POKAZ WSZYSTKICH DOSTAWCOW
  FUNCTION SELECTDOSTAWCA
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT DISTINCT D.DOSTAWCA_ID, D.NAZWA_FIRMY, A.MIEJSCOWOSC, A.WOJEWODZTWO ,A.KOD_POCZTOWY, A.ULICA, A.NR_DOMU, A.NR_LOKALU, KT.EMAIL, KT.NR_TEL, KT.FAX, KT.WWW FROM DOSTAWCA D, ADRES A, KONTAKT KT WHERE A.ADRES_ID = D.ADRES_ID AND KT.KONTAKT_ID = D.KONTAKT_ID]';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTDOSTAWCA;

  -- WYBIERZ DOSTAWCE PO ID
  FUNCTION SELECTDOSTAWCAID(DOSTAW_ID INT)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := 'SELECT DISTINCT D.DOSTAWCA_ID, D.NAZWA_FIRMY, A.MIEJSCOWOSC, A.WOJEWODZTWO ,A.KOD_POCZTOWY, A.ULICA, A.NR_DOMU, A.NR_LOKALU, KT.EMAIL, KT.NR_TEL, KT.FAX, KT.WWW FROM DOSTAWCA D, ADRES A, KONTAKT KT WHERE A.ADRES_ID = D.ADRES_ID AND KT.KONTAKT_ID = D.KONTAKT_ID AND D.DOSTAWCA_ID = ' || DOSTAW_ID;

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTDOSTAWCAID;

  -- DODAJ DOSTAWCE
  PROCEDURE INSERTDOSTAWCA
         (
            NAZWA_FIR IN VARCHAR2,
            MIEJSC IN VARCHAR2,
            WOJ IN VARCHAR2,
            KOD_POCZT IN VARCHAR2,
            UL IN VARCHAR2,
            NR_DOM IN INT,
            NR_LOK IN INT,
            MAIL IN VARCHAR2,
            TEL IN VARCHAR2,
            FAX_1 IN VARCHAR2,
            WWW_1 IN VARCHAR2
         )
  AS
            ADR_ID NUMBER;
            KONT_ID NUMBER;
  BEGIN

            INSERT INTO ADRES(MIEJSCOWOSC, WOJEWODZTWO, KOD_POCZTOWY, ULICA, NR_DOMU, NR_LOKALU) VALUES (MIEJSC, WOJ, KOD_POCZT, UL, NR_DOM, NR_LOK)
            returning ADRES_ID INTO ADR_ID;

            INSERT INTO KONTAKT(NR_TEL, FAX, EMAIL, WWW) VALUES (TEL, FAX_1, MAIL, WWW_1)
            returning KONTAKT_ID INTO KONT_ID;


            INSERT INTO DOSTAWCA(KONTAKT_ID, ADRES_ID, NAZWA_FIRMY) VALUES (KONT_ID, ADR_ID, NAZWA_FIR);

  END INSERTDOSTAWCA;

  -- USUN DOSTAWCE PO ID
  PROCEDURE DELETEDOSTAWCA
  (KON_ID IN INT)
  AS
  BEGIN
    DELETE FROM DOSTAWCA
    WHERE DOSTAWCA_ID = KON_ID;
  END DELETEDOSTAWCA;
END;
/




/* #########################    PRODUKTY    ######################### */

CREATE OR REPLACE PACKAGE PPRODUKT IS
  FUNCTION LAST6PRODUCTS RETURN SYS_REFCURSOR;
  FUNCTION SELECTPRODUKTY RETURN SYS_REFCURSOR;
  FUNCTION SELECTPRODUKTYKATEGORIAID (KAT_ID IN VARCHAR2) RETURN SYS_REFCURSOR;
  FUNCTION SELECTPRODUKTID (IDPROD INT) RETURN SYS_REFCURSOR;
  PROCEDURE DELETEPRODUKT (PROD_ID IN INT);
END;
/
CREATE OR REPLACE PACKAGE BODY PPRODUKT IS
    -- POKAZ OSTATNIE 6 DODANYCH PRODUKTOW
  FUNCTION LAST6PRODUCTS
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT * FROM ( SELECT TO_CHAR(DATA_DODANIA, 'DD-MON-YYYY HH24:MI') AS CTIME, PRODUKT.* FROM PRODUKT ORDER BY DATA_DODANIA DESC ) WHERE ROWNUM <= 6]';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END LAST6PRODUCTS;

  -- POKAZ PRODUKTY Z DANEJ KATEGORI
  FUNCTION SELECTPRODUKTYKATEGORIAID(KAT_ID IN VARCHAR2)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT * FROM ( SELECT TO_CHAR(DATA_DODANIA, 'DD-MON-YYYY HH24:MI') AS CTIME, PRODUKT.* FROM PRODUKT ORDER BY DATA_DODANIA DESC ) WHERE KATEGORIA_ID =']' || KAT_ID || q'[']' ;

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTPRODUKTYKATEGORIAID;

  -- POKAZ WSYZSTKIE PRODUKTY
  FUNCTION SELECTPRODUKTY
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN


    MY_QUERY := q'[SELECT DISTINCT P.PRODUKT_ID, D.NAZWA_FIRMY, K.KATEGORIA_NAZWA, P.PRODUCENT, P.NUMER_KATALOGOWY, P.MODEL, P.CENA, P.SZTUK_NA_MAGAZYNIE, TO_CHAR(P.DATA_DODANIA, 'DD-MON-YYYY HH24:MI') AS DATA_DOD, D.NAZWA_FIRMY, K.KATEGORIA_NAZWA FROM PRODUKT P, DOSTAWCA D, KATEGORIA K WHERE P.DOSTAWCA_ID = D.DOSTAWCA_ID AND P.KATEGORIA_ID = K.KATEGORIA_ID]';
    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTPRODUKTY;

  -- POKAZ PRODUKT PO ID
  FUNCTION SELECTPRODUKTID(IDPROD INT)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT DISTINCT P.PRODUKT_ID, D.NAZWA_FIRMY, K.KATEGORIA_NAZWA, P.PRODUCENT, P.NUMER_KATALOGOWY, P.MODEL, P.CENA, P.SZTUK_NA_MAGAZYNIE, P.DATA_DODANIA, D.NAZWA_FIRMY, K.KATEGORIA_NAZWA FROM PRODUKT P, DOSTAWCA D, KATEGORIA K WHERE P.DOSTAWCA_ID = D.DOSTAWCA_ID AND P.KATEGORIA_ID = K.KATEGORIA_ID AND P.PRODUKT_ID = ']' || IDPROD || q'[']';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTPRODUKTID;

  -- USUN PRODUKT O ID
  PROCEDURE DELETEPRODUKT
  (PROD_ID IN INT)
  AS
  BEGIN
    DELETE FROM PRODUKT
    WHERE PRODUKT_ID = PROD_ID;
  END DELETEPRODUKT;
END;
/



/* #########################    KATEGORIA    ######################### */

CREATE OR REPLACE PACKAGE PKATEGORIA IS
  FUNCTION SELECTKATEGORIA RETURN SYS_REFCURSOR;
  FUNCTION SELECTKATEGORIAID (KATEGORIA_ID INT) RETURN SYS_REFCURSOR;
  PROCEDURE DELETEKATEGORIA (KAT_ID IN INT);
  PROCEDURE INSERTKATEGORIA (VKATEGORIA_NAZWA IN VARCHAR2, VOPIS IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY PKATEGORIA IS
  -- POKAZ WSZYSTKIE KATEGORIE
  FUNCTION SELECTKATEGORIA
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT * FROM KATEGORIA]';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTKATEGORIA;

  -- POKAZ KATEGORIE PO ID
  FUNCTION SELECTKATEGORIAID(KATEGORIA_ID INT)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := 'SELECT * FROM KATEGORIA WHERE KATEGORIA_ID = ' || KATEGORIA_ID;

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END SELECTKATEGORIAID;

  -- USUN KATEGORIE PO ID
  PROCEDURE DELETEKATEGORIA
  (KAT_ID IN INT)
  AS
  BEGIN
    DELETE FROM KATEGORIA
    WHERE KATEGORIA_ID = KAT_ID;
  END DELETEKATEGORIA;

  -- DODAJ KATEGORIE
  PROCEDURE INSERTKATEGORIA(VKATEGORIA_NAZWA IN VARCHAR2, VOPIS IN VARCHAR2)
  AS
  BEGIN

      INSERT INTO KATEGORIA(KATEGORIA_NAZWA, OPIS) VALUES (VKATEGORIA_NAZWA, VOPIS);

  END INSERTKATEGORIA;
END;
/



/* #########################    KOSZYK    ######################### */

CREATE OR REPLACE PACKAGE PKOSZYK IS
  FUNCTION POKAZKOSZYK (KONT_ID IN NUMBER) RETURN SYS_REFCURSOR;
  FUNCTION COUNTSZTUKI(TABLENAME VARCHAR2, COLUMNNAME VARCHAR2, CONDITION VARCHAR2) RETURN NUMBER;
  PROCEDURE INSERTKOSZYK (PROD_ID IN VARCHAR2, KON_ID IN VARCHAR2, ILOSC_SZT IN VARCHAR2);
  PROCEDURE UPDATEKOSZYKINC (PROD_ID IN VARCHAR2, KON_ID IN VARCHAR2);
  PROCEDURE UPDATEKOSZYKDEC (PROD_ID IN VARCHAR2, KON_ID IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY PKOSZYK IS
  -- DODAJ PRODUKT DO KOSZYKA
  PROCEDURE INSERTKOSZYK(PROD_ID IN VARCHAR2, KON_ID IN VARCHAR2, ILOSC_SZT IN VARCHAR2)
  AS
  BEGIN

      INSERT INTO KOSZYK(PRODUKT_ID, KONTO_ID, ILOSC_SZTUK) VALUES (PROD_ID, KON_ID, ILOSC_SZT);

  END INSERTKOSZYK;

  -- ZWIEKSZ ILOSC SZTUK DANEGO PRODUKTU O 1
  PROCEDURE UPDATEKOSZYKINC(PROD_ID IN VARCHAR2, KON_ID IN VARCHAR2)
  AS
  BEGIN

      UPDATE KOSZYK
          SET ILOSC_SZTUK = ILOSC_SZTUK + 1
      WHERE PRODUKT_ID = PROD_ID
      AND KONTO_ID = KON_ID;

  END UPDATEKOSZYKINC;

  -- ZMNIEJSZ ILOSC SZTUK DANEGO PRODUKTU O 1
  PROCEDURE UPDATEKOSZYKDEC(PROD_ID IN VARCHAR2, KON_ID IN VARCHAR2)
  AS
      VILOSC_SZTUK NUMBER;
      VPRODUKT_ID NUMBER;
      VKONTO_ID NUMBER;
  BEGIN

      UPDATE KOSZYK
          SET ILOSC_SZTUK = ILOSC_SZTUK - 1
      WHERE PRODUKT_ID = PROD_ID
      AND KONTO_ID = KON_ID
      returning
      ILOSC_SZTUK, PRODUKT_ID, KONTO_ID INTO VILOSC_SZTUK, VPRODUKT_ID, VKONTO_ID;

      IF VILOSC_SZTUK < 1 THEN
          DELETE FROM KOSZYK WHERE PRODUKT_ID = VPRODUKT_ID AND KONTO_ID = VKONTO_ID;
      END IF;

  END UPDATEKOSZYKDEC;

  -- POKAZ KOSZYK
  /* POLACZENIE TABELI KOSZYK I PRODUKT */
  FUNCTION POKAZKOSZYK(KONT_ID IN NUMBER)
    RETURN SYS_REFCURSOR
  AS
    MY_CURSOR SYS_REFCURSOR;
    MY_QUERY VARCHAR2(500);
  BEGIN

    MY_QUERY := q'[SELECT KOSZYK.PRODUKT_ID, PRODUKT.PRODUCENT, PRODUKT.MODEL,  PRODUKT.NUMER_KATALOGOWY, PRODUKT.CENA, KOSZYK.ILOSC_SZTUK, (KOSZYK.ILOSC_SZTUK*PRODUKT.CENA) as ILOCZYN, PRODUKT.SZTUK_NA_MAGAZYNIE FROM KOSZYK INNER JOIN PRODUKT ON KOSZYK.PRODUKT_ID = PRODUKT.PRODUKT_ID WHERE KOSZYK.KONTO_ID =']' || KONT_ID || q'[']';

    OPEN MY_CURSOR FOR MY_QUERY;

    RETURN MY_CURSOR;
  END POKAZKOSZYK;

  -- LICZ SZTUKI W KOSZYKU
  FUNCTION COUNTSZTUKI(TABLENAME VARCHAR2, COLUMNNAME VARCHAR2, CONDITION VARCHAR2)
     RETURN NUMBER
  AS
     row_count NUMBER;
     MY_QUERY VARCHAR2(500);
  BEGIN

      MY_QUERY := 'SELECT ' || COLUMNNAME || ' FROM ' || TABLENAME || ' WHERE ' || CONDITION;
      EXECUTE IMMEDIATE MY_QUERY INTO row_count;

      RETURN row_count;
  END COUNTSZTUKI;
END;
/



/* #########################    ZAMOWIENIE    ######################### */

CREATE OR REPLACE PACKAGE PZAMOWIENIE IS
  PROCEDURE STWORZ_ZAMOWIENIE (KON_ID IN INT, KUR_ID IN INT, KOSZT_ZAM IN FLOAT, METODA_PLAT IN VARCHAR2, DOKUMENT_SPRZ IN VARCHAR2);
  FUNCTION SELECTZAMOWIENIA RETURN SYS_REFCURSOR;
  FUNCTION SELECTSZCZEGOLYZAMOWIENIA (ZAM_ID IN INT) RETURN SYS_REFCURSOR;
  FUNCTION SELECTZAMOWIENIAID (ID_KONT IN INT) RETURN SYS_REFCURSOR;
  PROCEDURE UPDATEZAMOWIENIE (ZAM_ID IN INT, VDATAREALIZACJI IN VARCHAR2, VZAAKCEPTOWANO IN INT, VZAPLACONO IN INT, VZREALIZOWANO IN INT);
END;
/
CREATE OR REPLACE PACKAGE BODY PZAMOWIENIE IS
  --Tworzy nowe zamowienie >> dodaje produkty z KOSZYK do ZAMOWIONE_PRODUKTY >> usuwa KOSZYK
  PROCEDURE STWORZ_ZAMOWIENIE
  (KON_ID IN INT, KUR_ID IN INT, KOSZT_ZAM IN FLOAT, METODA_PLAT IN VARCHAR2, DOKUMENT_SPRZ IN VARCHAR2)
  AS
    ID_ZAM NUMBER;
    liczba NUMBER;
  BEGIN
    liczba := 0;
    SELECT COUNT(*) INTO liczba FROM KOSZYK K, PRODUKT P
    WHERE K.PRODUKT_ID = P.PRODUKT_ID AND K.ILOSC_SZTUK > P.SZTUK_NA_MAGAZYNIE AND K.KONTO_ID = KON_ID;

    IF liczba > 0
    THEN
      raise_application_error(-20008, 'zamowienie - blad');
    ELSE
      INSERT INTO ZAMOWIENIE (KONTO_ID, KURIER_ID, KOSZT_ZAMOWIENIA, METODA_PLATNOSCI, DOKUMENT_SPRZEDAZY)
      VALUES (KON_ID, KUR_ID, KOSZT_ZAM, METODA_PLAT, DOKUMENT_SPRZ)
      returning ZAMOWIENIE_ID INTO ID_ZAM;

      INSERT INTO ZAMOWIONE_PRODUKTY (PRODUKT_ID, ZAMOWIENIE_ID, ILOSC_SZTUK)
      SELECT PRODUKT_ID, ID_ZAM, ILOSC_SZTUK FROM KOSZYK
      WHERE
        KONTO_ID = KON_ID;

      DELETE FROM KOSZYK
      WHERE
        KONTO_ID = KON_ID;
    END IF;

  END;

  -- POKAZ WSZYSTKIE ZAMÓWIENIA
  FUNCTION SELECTZAMOWIENIA
    RETURN SYS_REFCURSOR
  AS
      MY_CURSOR SYS_REFCURSOR;
  BEGIN

  OPEN MY_CURSOR FOR
     SELECT
          ZAMOWIENIE.ZAMOWIENIE_ID,
          ZAMOWIENIE.KONTO_ID,
          KONTO.IMIE,
          KONTO.NAZWISKO,
          KURIER.NAZWA_FIRMY,
          ZAMOWIENIE.DATA_PRZYJECIA_ZAMOWIENIA,
          ZAMOWIENIE.DATA_REALIZACJI_ZAMOWIENIA,
          ZAMOWIENIE.ZAMOWIENIE_ZAAKCEPTOWANE,
          ZAMOWIENIE.ZAPLACONO,
          ZAMOWIENIE.ZREALIZOWANO,
          ZAMOWIENIE.KOSZT_ZAMOWIENIA,
          ZAMOWIENIE.METODA_PLATNOSCI,
          ZAMOWIENIE.DOKUMENT_SPRZEDAZY,
          ADRES.MIEJSCOWOSC,
          ADRES.WOJEWODZTWO,
          ADRES.KOD_POCZTOWY,
          ADRES.ULICA,
          ADRES.NR_DOMU,
          ADRES.NR_LOKALU,
          KONTAKT.NR_TEL,
          KONTAKT.EMAIL
      FROM ZAMOWIENIE
        INNER JOIN KURIER ON ZAMOWIENIE.KURIER_ID = KURIER.KURIER_ID
        INNER JOIN KONTO ON ZAMOWIENIE.KONTO_ID = KONTO.KONTO_ID
        INNER JOIN ADRES ON KONTO.ADRES_ID = ADRES.ADRES_ID
        INNER JOIN KONTAKT ON KONTO.KONTAKT_ID = KONTAKT.KONTAKT_ID;
  RETURN MY_CURSOR;

  END SELECTZAMOWIENIA;

  -- POKAZ SZCZEGOLY ZAMÓWIENIA
  FUNCTION SELECTSZCZEGOLYZAMOWIENIA (ZAM_ID IN INT)
    RETURN SYS_REFCURSOR
  AS
      MY_CURSOR SYS_REFCURSOR;
  BEGIN

  OPEN MY_CURSOR FOR
     SELECT
          ZAMOWIONE_PRODUKTY.PRODUKT_ID,
          PRODUKT.PRODUCENT,
          PRODUKT.MODEL,
          PRODUKT.NUMER_KATALOGOWY,
          ZAMOWIONE_PRODUKTY.ILOSC_SZTUK,
          ZAMOWIONE_PRODUKTY.ILOSC_SZTUK * PRODUKT.CENA AS KOSZT
      FROM ZAMOWIONE_PRODUKTY
        INNER JOIN PRODUKT ON ZAMOWIONE_PRODUKTY.PRODUKT_ID = PRODUKT.PRODUKT_ID
     WHERE ZAMOWIONE_PRODUKTY.ZAMOWIENIE_ID = ZAM_ID;

  RETURN MY_CURSOR;

  END SELECTSZCZEGOLYZAMOWIENIA;

  -- POKAZ ZAMOWIENIA: ID KLIENT
  FUNCTION SELECTZAMOWIENIAID (ID_KONT IN INT)
    RETURN SYS_REFCURSOR
  AS
      MY_CURSOR SYS_REFCURSOR;
  BEGIN

  OPEN MY_CURSOR FOR
     SELECT
          ZAMOWIENIE.ZAMOWIENIE_ID,
          KURIER.NAZWA_FIRMY,
          ZAMOWIENIE.DATA_PRZYJECIA_ZAMOWIENIA,
          ZAMOWIENIE.DATA_REALIZACJI_ZAMOWIENIA,
          ZAMOWIENIE.ZAMOWIENIE_ZAAKCEPTOWANE,
          ZAMOWIENIE.ZAPLACONO,
          ZAMOWIENIE.ZREALIZOWANO,
          ZAMOWIENIE.KOSZT_ZAMOWIENIA,
          ZAMOWIENIE.METODA_PLATNOSCI,
          ZAMOWIENIE.DOKUMENT_SPRZEDAZY,
          ADRES.MIEJSCOWOSC,
          ADRES.WOJEWODZTWO,
          ADRES.KOD_POCZTOWY,
          ADRES.ULICA,
          ADRES.NR_DOMU,
          ADRES.NR_LOKALU,
          KONTAKT.NR_TEL,
          KONTAKT.EMAIL
      FROM ZAMOWIENIE
        INNER JOIN KURIER ON ZAMOWIENIE.KURIER_ID = KURIER.KURIER_ID
        INNER JOIN KONTO ON ZAMOWIENIE.KONTO_ID = KONTO.KONTO_ID
        INNER JOIN ADRES ON KONTO.ADRES_ID = ADRES.ADRES_ID
        INNER JOIN KONTAKT ON KONTO.KONTAKT_ID = KONTAKT.KONTAKT_ID
      WHERE ZAMOWIENIE.KONTO_ID =  ID_KONT;
  RETURN MY_CURSOR;

  END SELECTZAMOWIENIAID;

  -- UPDATE ZAMOWIENIE
  PROCEDURE UPDATEZAMOWIENIE
         (
            ZAM_ID IN INT,
            VDATAREALIZACJI IN VARCHAR2,
            VZAAKCEPTOWANO IN INT,
            VZAPLACONO IN INT,
            VZREALIZOWANO IN INT
         )
  AS

  BEGIN

      IF VDATAREALIZACJI IS NOT NULL THEN
              UPDATE ZAMOWIENIE
                  SET DATA_REALIZACJI_ZAMOWIENIA =  TO_DATE(VDATAREALIZACJI, 'yyyy/mm/dd')
              WHERE ZAMOWIENIE_ID = ZAM_ID;
      END IF;

      IF VZAAKCEPTOWANO IS NOT NULL THEN
              UPDATE ZAMOWIENIE
                  SET ZAMOWIENIE_ZAAKCEPTOWANE = VZAAKCEPTOWANO
              WHERE ZAMOWIENIE_ID = ZAM_ID;
      END IF;

      IF VZAPLACONO IS NOT NULL THEN
              UPDATE ZAMOWIENIE
                  SET ZAPLACONO = VZAPLACONO
              WHERE ZAMOWIENIE_ID = ZAM_ID;
      END IF;

      IF VZREALIZOWANO IS NOT NULL THEN
              UPDATE ZAMOWIENIE
                  SET ZREALIZOWANO = VZREALIZOWANO
              WHERE ZAMOWIENIE_ID = ZAM_ID;
      END IF;

  END UPDATEZAMOWIENIE;
END;
/



/* #########################    ADRES    ######################### */

CREATE OR REPLACE PACKAGE PADRES IS
  PROCEDURE INSERTADRES (KON_ID IN INT, MIEJSC IN VARCHAR2, WOJ IN VARCHAR2, KOD_POCZT IN VARCHAR2, UL IN VARCHAR2, NR_DOM IN INT, NR_LOK IN INT);
END;
/
CREATE OR REPLACE PACKAGE BODY PADRES IS
-- TWORZY NOWY ADRES I DODAJE GO DO KONTA(KON_ID)
  PROCEDURE INSERTADRES
         (
            KON_ID IN INT,
            MIEJSC IN VARCHAR2,
            WOJ IN VARCHAR2,
            KOD_POCZT IN VARCHAR2,
            UL IN VARCHAR2,
            NR_DOM IN INT,
            NR_LOK IN INT
         )
  AS
    ADR_ID NUMBER;
  BEGIN

      INSERT INTO ADRES(ADRES_ID, MIEJSCOWOSC, WOJEWODZTWO, KOD_POCZTOWY, ULICA, NR_DOMU, NR_LOKALU) VALUES (ADR_ID, MIEJSC, WOJ, KOD_POCZT, UL, NR_DOM, NR_LOK);

      ADR_ID:= ADRES_SEQ.CURRVAL;

      UPDATE KONTO
    SET ADRES_ID = ADR_ID
    WHERE KONTO_ID = KON_ID;

  END INSERTADRES;
END;
/




/* #########################    KONTAKT    ######################### */

CREATE OR REPLACE PACKAGE PKONTAKT IS
  PROCEDURE INSERTKONTAKT (KON_ID IN INT, TEL IN VARCHAR2, FAX_1 IN VARCHAR2, MAIL IN VARCHAR2, WWW_1 IN VARCHAR2);
END;
/
  CREATE OR REPLACE PACKAGE BODY PKONTAKT IS
  --TWORZY NOWY KONTAKT I DODAJE GO DO KONTA(KON_ID)
  PROCEDURE INSERTKONTAKT
         (
            KON_ID IN INT,
            TEL IN VARCHAR2,
            FAX_1 IN VARCHAR2,
            MAIL IN VARCHAR2,
            WWW_1 IN VARCHAR2
         )
  AS
    KONT_ID NUMBER;
  BEGIN

          INSERT INTO KONTAKT(KONTAKT_ID, NR_TEL, FAX, EMAIL, WWW) VALUES (KONT_ID, TEL, FAX_1, MAIL, WWW_1);

          KONT_ID:= KONTAKT_SEQ.CURRVAL;

          UPDATE KONTO
          SET KONTAKT_ID = KONT_ID
          WHERE KONTO_ID = KON_ID;

  END INSERTKONTAKT;
END;
/



/* #########################     INNE FUNKCJE    ######################### */

CREATE OR REPLACE PACKAGE PINNE IS
    FUNCTION COUNTRW (TABLENAME VARCHAR2, COLUMNNAME VARCHAR2, CONDITION VARCHAR2) RETURN NUMBER;
    PROCEDURE REJESTRACJA (USERNAME VARCHAR2 DEFAULT NULL, PASSWORD VARCHAR2 DEFAULT NULL, PASSWORD2 VARCHAR2 DEFAULT NULL, VIMIE VARCHAR2 DEFAULT NULL, VNAZWISKO VARCHAR2 DEFAULT NULL, VMAIL VARCHAR2 DEFAULT NULL, VWOJ VARCHAR2 DEFAULT NULL, VMIEJSC VARCHAR2 DEFAULT NULL, VPOCZT VARCHAR2 DEFAULT NULL, VULICA VARCHAR2 DEFAULT NULL, VNR_DOMU INT DEFAULT NULL, VNR_TEL VARCHAR2 DEFAULT NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY PINNE IS
-- POKAZ LICZBE WIERSZY DLA DANEJ TABELI ORAZ KOLUMNY O ZADANYM WARUNKU
FUNCTION COUNTRW(TABLENAME VARCHAR2, COLUMNNAME VARCHAR2, CONDITION VARCHAR2)
   RETURN NUMBER
AS
   row_count NUMBER;
   MY_QUERY VARCHAR2(500);
BEGIN

    MY_QUERY := 'SELECT COUNT(' || COLUMNNAME || ') FROM ' || TABLENAME || ' WHERE ' || CONDITION;
    EXECUTE IMMEDIATE MY_QUERY INTO row_count;

    RETURN row_count;
END COUNTRW;

-- REJESTRACJA
PROCEDURE REJESTRACJA( USERNAME VARCHAR2 DEFAULT NULL,
      PASSWORD VARCHAR2 DEFAULT NULL,
      PASSWORD2 VARCHAR2 DEFAULT NULL,
      VIMIE VARCHAR2 DEFAULT NULL,
      VNAZWISKO VARCHAR2 DEFAULT NULL,
      VMAIL VARCHAR2 DEFAULT NULL,
      VWOJ VARCHAR2 DEFAULT NULL,
      VMIEJSC VARCHAR2 DEFAULT NULL,
      VPOCZT VARCHAR2 DEFAULT NULL,
      VULICA VARCHAR2 DEFAULT NULL,
      VNR_DOMU INT DEFAULT NULL,
      VNR_TEL VARCHAR2 DEFAULT NULL
      )
     IS
       n BOOLEAN;
       m INTEGER;
       DIFFER INTEGER;
       ISDIGIT BOOLEAN;
       ISCHAR  BOOLEAN;
       ISPUNCT BOOLEAN;
       DIGITARRAY VARCHAR2(20);
       PUNCTARRAY VARCHAR2(25);
       CHARARRAY VARCHAR2(52);
       num_rows NUMBER:=0;

    BEGIN
       DIGITARRAY:= '0123456789';
       CHARARRAY:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
       PUNCTARRAY:='!"#$%&()``*+,-/:;<=>?_.';

       -- Sprawdz czy nazwa uzytkownika jest w systemie
       SELECT COUNT(*) INTO num_rows
            FROM KONTO
            WHERE LOGIN = USERNAME;

        IF num_rows > 0 THEN
            raise_application_error(-20006, 'Nazwa uzytkownika jest juz w systemie');
        END IF;

       -- jezeli pole jest puste
       IF USERNAME IS NULL OR PASSWORD IS NULL OR PASSWORD2 IS NULL OR VIMIE IS NULL OR VNAZWISKO IS NULL OR VMAIL IS NULL
       OR VWOJ IS NULL OR VMIEJSC IS NULL OR VPOCZT IS NULL OR VULICA IS NULL OR VNR_DOMU IS NULL OR VNR_TEL IS NULL THEN
         raise_application_error(-20001, 'Wypelnij wszystkie pola');
       END IF;

       -- Hasla sie roznia
       IF PASSWORD != PASSWORD2 THEN
         raise_application_error(-20002, 'hasla sie od siebie roznia');
       END IF;

       -- Sprawdz dlugosc hasla
       IF LENGTH(PASSWORD) < 7 THEN
          raise_application_error(-20003, 'Haslo zbyt krotkie (mniej niz 7 znakow)');
       END IF;

       -- Sprawdz czy hasla nie ma na blackliscie
       IF NLS_LOWER(PASSWORD) IN ('welcome', 'database', 'account', 'user', 'password',
    'oracle', 'gofishingshop', 'admin', 'login') THEN
          raise_application_error(-20004, 'haslo zbyt latwe');
       END IF;

       -- Sprawdz czy haslo ma przynajmniej jedna litere, jedna cyfre i znak przestankowy
       -- 1. Szukaj cyfry
       ISDIGIT:=FALSE;
       m := LENGTH(PASSWORD);
       FOR i IN 1..10 LOOP
          FOR j IN 1..m LOOP
             IF SUBSTR(PASSWORD,j,1) = SUBSTR(DIGITARRAY,i,1) THEN
                ISDIGIT:=TRUE;
                 GOTO findchar;
             END IF;
          END LOOP;
       END LOOP;
     IF ISDIGIT = FALSE THEN
          raise_application_error(-20005, 'haslo powinno miec przynajmniej jedna litere, jedna cyfre i znak przestankowy');
       END IF;
<<findchar>>
       -- 2. Szukaj litery
       ISCHAR:=FALSE;
       FOR i IN 1..LENGTH(CHARARRAY) LOOP
          FOR j IN 1..m LOOP
             IF SUBSTR(PASSWORD,j,1) = SUBSTR(CHARARRAY,i,1) THEN
                ISCHAR:=TRUE;
                 GOTO findpunct;
             END IF;
          END LOOP;
       END LOOP;
       IF ISCHAR = FALSE THEN
          raise_application_error(-20005, 'haslo powinno miec przynajmniej jedna litere, jedna cyfre i znak przestankowy');
       END IF;
<<findpunct>>
       -- 3. Szukaj znaku przestankowego
       ISPUNCT:=FALSE;
       FOR i IN 1..LENGTH(PUNCTARRAY) LOOP
          FOR j IN 1..m LOOP
             IF SUBSTR(PASSWORD,j,1) = SUBSTR(PUNCTARRAY,i,1) THEN
                ISPUNCT:=TRUE;
                 GOTO endsearch;
             END IF;
          END LOOP;
       END LOOP;
       IF ISPUNCT = FALSE THEN
          raise_application_error(-20005, 'haslo powinno miec przynajmniej jedna litere, jedna cyfre i znak przestankowy');
       END IF;
<<endsearch>>

 INSERT INTO ADRES(MIEJSCOWOSC, WOJEWODZTWO, KOD_POCZTOWY, ULICA, NR_DOMU)
    VALUES(VMIEJSC, VWOJ, VPOCZT, VULICA, VNR_DOMU);
 INSERT INTO KONTAKT(NR_TEL, EMAIL)
    VALUES(VNR_TEL, VMAIL);
 INSERT INTO KONTO(ADRES_ID, KONTAKT_ID, LOGIN, HASLO, IMIE, NAZWISKO)
    VALUES(ADRES_SEQ.CURRVAL, KONTAKT_SEQ.CURRVAL, USERNAME, PASSWORD, VIMIE, VNAZWISKO);
 END;

END;