--Vytvoreni Package obsahujici funkce, ktere vyuzivaji triggery pro kontrolu vkladanych dat.
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

--Vytvoreni Package obchod_pkg
------------------------------
CREATE OR REPLACE PACKAGE obchod_pkg IS
  FUNCTION POCET_CD_S_ALBEM_NA_FAKTURE (Cislo_CD in CD.ID_CD%type,Cislo_Faktury IN Faktura.CIS_F%type) RETURN NUMBER;
  FUNCTION POCET_ALB_NA_FAKTURE (Jmeno_Alba IN Album.Nazev_A%type,Cislo_Faktury IN Faktura.Cis_F%type) RETURN NUMBER;
  FUNCTION LZE_OBJEDNAT (IdCislo IN zakaznik.RC_Zak%type) RETURN BOOLEAN;
  FUNCTION JE_NA_SKLADU (JmenoAlba IN Album.Nazev_A%type) RETURN BOOLEAN;
  PROCEDURE KONTROLA_UPOMINEK;
END obchod_pkg;
/

--Vytvoreni tela Package obchod_pkg
-----------------------------------
CREATE OR REPLACE PACKAGE BODY obchod_pkg IS

  --Funkce vracejici pocet CD na fakture, ktere obsahuji stejne album, jako CD zadane jako vstupni parametr.
  --------------------------------------------------------------------------------------------------------------------
  FUNCTION POCET_CD_S_ALBEM_NA_FAKTURE (Cislo_CD in CD.ID_CD%type,Cislo_Faktury IN Faktura.CIS_F%type) RETURN NUMBER
  IS
  Jmeno CD.NAZEV_A%type;
  Pocet NUMBER;
  cursor SeznamCD is select * from na natural join cd where Cis_F=Cislo_faktury;
  BEGIN
    select Nazev_A into Jmeno from cd where ID_CD=Cislo_CD;
    Pocet:=0;
    for i in SeznamCd LOOP
      if i.Nazev_A=Jmeno then 
        Pocet:=Pocet+1;
      end if;
    END LOOP;
    RETURN(Pocet);
  END;

  --Funkce vracejici pocet objednavek daneho Alba na fakture.
  -------------------------------------------------------------------------------------------------------------------
  FUNCTION POCET_ALB_NA_FAKTURE (Jmeno_Alba IN Album.Nazev_A%type,Cislo_Faktury IN Faktura.Cis_F%type) RETURN NUMBER
  IS
  CURSOR ObjednanaAlba is select * from za natural join objednavka where (Nazev_A=Jmeno_Alba)and(Cislo_FAktury=Cis_F);
  Pocet NUMBER;
  BEGIN
    Pocet:=0;
    for i IN ObjednanaAlba LOOP
      Pocet:=Pocet+i.Pocet;
    END LOOP;
    RETURN(Pocet);
  END;

  --Funkce vracejici true v pripade, ze zadany zakaznik si muze vytvorit novou objednavku.
  ----------------------------------------------------------------------------------------
  FUNCTION LZE_OBJEDNAT (IdCislo IN zakaznik.RC_Zak%type) RETURN BOOLEAN IS
  Cislo NUMBER;
  BEGIN
    select CIS_F into Cislo from faktura natural join za natural join objednavka 
      where (RC_Zak=IdCislo)and(zaplaceno='N')and(Dat_splatnosti < Sysdate );
    RETURN(false);
  EXCEPTION
    WHEN NO_DATA_FOUND then RETURN(true);
    WHEN TOO_MANY_ROWS then RETURN(false);
  END;

  --Funkce vracejici true, jestlize zadane Album se nachazi na nekterem CD na sklade.
  -----------------------------------------------------------------------------------
  FUNCTION JE_NA_SKLADU (JmenoAlba IN Album.Nazev_A%type) RETURN BOOLEAN IS
  POmCislo NUMBER;
  BEGIN
    select ID_CD INTO PomCislo from CD 
      where Nazev_A=JmenoAlba and not ID_CD in (select ID_CD from CD natural join na where Nazev_A=JmenoAlba);
    RETURN(true);
  EXCEPTION
    when NO_DATA_FOUND then RETURN(false);
    when TOO_MANY_ROWS then RETURN(true);
  END;
 
  --Procedura pro kontrolu zasilanych upominek.
  --Jestlize zakaznik neni v tabulce upominek a ma nezaplacenou fakturu tak ho tam vlozi.
  --Jestlize zakaznik v tabulce je a datum zaslani upominky je starsi nez 14 dni aktualizuje udaje v tabulce.
  ----------------------------------------------------------------------------------------------------------- 
  PROCEDURE Kontrola_Upominek IS
  CURSOR Neplatici is select * from Zakaznik;
  rc Zakaznik.RC_ZAK%type;
  DatumPom Upominka.DAtum%type;
  BEGIN
    for I in Neplatici LOOP
      BEGIN
        if (not obchod_pkg.lze_objednat(i.RC_ZAK)) then
          select RC_ZAK,Datum into rc, DatumPom from Upominka where RC_ZAK=I.RC_ZAK;
          if (DatumPom<(sysdate-14)) then
            UPDATE Upominka SET Pocet=Pocet+1,
                                Datum=sysdate
            where RC_ZAK=I.RC_ZAK;
          end if;
        end if;
      EXCEPTION
        when NO_DATA_FOUND then INSERT INTO Upominka(RC_ZAK,Pocet,Datum)
                                        Values(I.RC_ZAK,1,sysdate);
      END;  
    end LOOP;
  END;

END obchod_pkg;
/
show errors;