/*
Neuen Kunden einfügen / Kundennummer wird automatisch generiert
Quelle: https://stackoverflow.com/a/11296469
*/
DECLARE kunde_anrede    VARCHAR(4)   := 'Herr';
        kunde_name      VARCHAR(255) := 'Anton Müller';
        kunde_anschrift VARCHAR(255) := 'Johannes Strasse 85';
        kunde_plz       INTEGER      := '22097';
        kunde_stadt     VARCHAR(255) := 'Berlin';

BEGIN

    INSERT INTO KUNDEN(KUNDENANREDE, KUNDENNAME, KUNDENANSCHRIFT, KUNDENPLZ, KUNDENSTADT) VALUES (kunde_anrede,kunde_name,kunde_anschrift,kunde_plz,kunde_stadt);

END;
/




/*
 Pflege Kundendaten Update
*/
DECLARE kundenr           INTEGER      := '90050';
        kunde_anschrift_n VARCHAR(255) := 'Johannes Strasse 85';
        kunde_plz_n       INTEGER      := '22097';
        kunde_stadt_n     VARCHAR(255) := 'Berlin';

BEGIN

    UPDATE KUNDEN SET KUNDENANSCHRIFT=kunde_anschrift_n,KUNDENPLZ=kunde_plz_n,KUNDENSTADT=kunde_stadt_n WHERE KUNDENNR = kundenr;

END;
/




/*
 Pflege Kundendaten Löschen
*/
DECLARE del_kundennummer NUMBER := '90052' ;

BEGIN
    DELETE FROM KUNDEN WHERE KUNDENNR = del_kundennummer;

END;
/




/*
 Produkt anlegen
*/
DECLARE produkt_name            VARCHAR(255)    := 'Semesterbeitrag';
        produkt_beschreibung    VARCHAR(512)    := 'Semesterbeitrag für Studierende der Hochschule für angewandte Wissenschaften';
        produkt_typ             VARCHAR(255)    := 'Gebühren';
        produkt_preis           DECIMAL         :=  355.50;

BEGIN

    INSERT INTO PRODUKTE(PRODUKTNAME,PRODUKTBESCHREIBUNG,PRODUKTTYP,PRODUKTPREIS) VALUES (produkt_name,produkt_beschreibung,produkt_typ,produkt_preis);

END;
/



/*
 Pflege Produkt Löschen
*/
DECLARE del_produktnummer NUMBER := '5033' ;

BEGIN
    DELETE FROM PRODUKTE WHERE PRODUKTNR = del_produktnummer;

END;
/



/*
 Pflege Produkt Update
*/
DECLARE produkt_nr                INTEGER      := '5033';
        produkt_name             VARCHAR(255) := 'Ein tooles Produkt';
        produkt_beschreibung     VARCHAR(512) := 'Das ist eine BEschreibung';
        produkt_typ              VARCHAR(255) := 'Test';
        produkt_preis            DECIMAL      :=  2500.99;

BEGIN

    UPDATE PRODUKTE SET PRODUKTNAME=produkt_name,PRODUKTBESCHREIBUNG=produkt_beschreibung,PRODUKTTYP=produkt_typ,PRODUKTPREIS=produkt_preis WHERE PRODUKTNR = produkt_nr;

END;
/




/*
 Neue Bestellung
*/
DECLARE
    Bestellnummer   VARCHAR(100);
    Kundennummer    VARCHAR(100) := '00002';
    artikel_count   VARCHAR(20);
    lager_counter   VARCHAR(20);
    lager_rest      VARCHAR(20);
    check_zubestell VARCHAR(2);
    zubestell_value VARCHAR(20);

    TYPE artikel IS RECORD (
        produkt_nr      VARCHAR2(5),
        produkt_anzahl  VARCHAR2(5)
        );
    TYPE artikel_p IS TABLE OF artikel
        INDEX BY BINARY_INTEGER ;

    artikelliste artikel_p;
BEGIN

    artikelliste(1).produkt_nr      := '1112';
    artikelliste(1).produkt_anzahl  := 1;
    artikelliste(2).produkt_nr      := '2223';
    artikelliste(2).produkt_anzahl  := 2;
    artikelliste(3).produkt_nr      := '3334';
    artikelliste(3).produkt_anzahl  := 3;
    artikelliste(4).produkt_nr      := '4445';
    artikelliste(4).produkt_anzahl  := 4;
    artikelliste(5).produkt_nr      := '5556';
    artikelliste(5).produkt_anzahl  := 5;

    artikel_count := artikelliste.COUNT;


    INSERT INTO BESTELLUNG(KUNDENNR,BEZAHLT,VERSANDBEREIT,VERSENDET) VALUES (Kundennummer,0,0,0);
    SELECT BESTELLNR INTO Bestellnummer FROM BESTELLUNG WHERE BESTELLNR = (SELECT BESTELLNR FROM BESTELLUNG ORDER BY BESTELLNR DESC FETCH FIRST 1 ROWS only);


    FOR i IN 1..artikel_count LOOP

        INSERT INTO BESTELLLISTE(PRODUKTNR, BESTELLNR,STUECKZAHL) VALUES (artikelliste(i).produkt_nr,Bestellnummer,artikelliste(i).produkt_anzahl);

        SELECT COALESCE(SUM(STUECKZAHL),0) INTO lager_counter FROM LAGER WHERE PRODUKTNR = artikelliste(i).produkt_nr;

        IF lager_counter >= artikelliste(i).produkt_anzahl THEN

            UPDATE BESTELLLISTE SET GELIEFERT=artikelliste(i).produkt_anzahl WHERE PRODUKTNR=artikelliste(i).produkt_nr AND BESTELLNR=Bestellnummer;

        ELSE
            /* Falls auf Lager ein Teil vorhanden ist dann nur die benötigten Teile bestellen */
            lager_rest := (artikelliste(i).produkt_anzahl - lager_counter);

            /* Überprüfen ob eine Zuliefererbestellung bereits erfolgt ist */
            SELECT COUNT(*) INTO check_zubestell FROM ZULIEFERERBESTELLUNG WHERE PRODUKTNR=artikelliste(i).produkt_nr;

            /* Alten Wert der Zuliefererbestellung */
            SELECT COALESCE(SUM(STUECKZAHL),0) INTO zubestell_value FROM ZULIEFERERBESTELLUNG WHERE PRODUKTNR=artikelliste(i).produkt_nr;

            IF check_zubestell = 1 THEN
                UPDATE ZULIEFERERBESTELLUNG SET STUECKZAHL = zubestell_value + artikelliste(i).produkt_anzahl WHERE PRODUKTNR=artikelliste(i).produkt_nr;
                ELSE
                INSERT INTO ZULIEFERERBESTELLUNG(PRODUKTNR, STUECKZAHL) VALUES (artikelliste(i).produkt_nr,lager_rest);

            END IF;

        END IF;

    END LOOP;

 END;
/




/*
Bestellung ändern
*/
DECLARE
    Bestellnummer   VARCHAR(100) := '2025';
    bestell_count   VARCHAR(20);

    TYPE bestell_aendern IS RECORD (
        produkt_nr      VARCHAR2(5),
        produkt_anzahl  VARCHAR2(5)
        );
    TYPE bestell_p IS TABLE OF bestell_aendern
        INDEX BY BINARY_INTEGER ;

    teillieferung bestell_p;

BEGIN

    teillieferung(1).produkt_nr      := '1112';
    teillieferung(1).produkt_anzahl  := 1;
    teillieferung(2).produkt_nr      := '2223';
    teillieferung(2).produkt_anzahl  := 2;
    teillieferung(3).produkt_nr      := '3334';
    teillieferung(3).produkt_anzahl  := 3;
    teillieferung(4).produkt_nr      := '4445';
    teillieferung(4).produkt_anzahl  := 4;
    teillieferung(5).produkt_nr      := '5556';
    teillieferung(5).produkt_anzahl  := 5;

    bestell_count := teillieferung.COUNT;

    /* Zur einfachere Bearbeitung kompletten Bestellliste der Bestellung droppen*/
    DELETE FROM BESTELLLISTE WHERE BESTELLNR= Bestellnummer;

    FOR i IN 1..bestell_count LOOP

    INSERT INTO BESTELLLISTE(PRODUKTNR, BESTELLNR, STUECKZAHL) VALUES (teillieferung(i).produkt_nr,Bestellnummer,teillieferung(i).produkt_anzahl);

    END LOOP;

END;
/









/*
 Teillieferung
*/
DECLARE
    Bestellnummer   VARCHAR(100) := '2025';
    lieferung_count   VARCHAR(20);

    TYPE lieferung IS RECORD (
        produkt_nr      VARCHAR2(5),
        produkt_anzahl  VARCHAR2(5)
        );
    TYPE lieferung_p IS TABLE OF lieferung
        INDEX BY BINARY_INTEGER ;

    teillieferung lieferung_p;

BEGIN

    teillieferung(1).produkt_nr      := '1112';
    teillieferung(1).produkt_anzahl  := 1;
    teillieferung(2).produkt_nr      := '2223';
    teillieferung(2).produkt_anzahl  := 2;
    teillieferung(3).produkt_nr      := '3334';
    teillieferung(3).produkt_anzahl  := 3;
    teillieferung(4).produkt_nr      := '4445';
    teillieferung(4).produkt_anzahl  := 4;
    teillieferung(5).produkt_nr      := '5556';
    teillieferung(5).produkt_anzahl  := 5;

    lieferung_count := teillieferung.COUNT;


    FOR i IN 1..lieferung_count LOOP
    UPDATE BESTELLLISTE SET GELIEFERT=teillieferung(i).produkt_anzahl WHERE PRODUKTNR=teillieferung(i).produkt_nr AND BESTELLNR=Bestellnummer;
    END LOOP;

END;
/


/*
Zuli
 */