IDAS2_SEM_TOFRONTEND

CREATE TABLE SEM_Kategorie
  ( Typ NUMBER NOT NULL , Nazev VARCHAR2 (100)
  ) ;
ALTER TABLE SEM_Kategorie ADD CONSTRAINT SEM_Kategorie_PK PRIMARY KEY ( Typ ) ;

CREATE TABLE SEM_LogTable
  ( ID NUMBER NOT NULL , User_ID NUMBER , Datum DATE
  ) ;
ALTER TABLE SEM_LogTable ADD CONSTRAINT SEM_LogTable_PK PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_Meny
  ( Kod VARCHAR2 (3) NOT NULL , Nazev VARCHAR2 (50)
  ) ;
ALTER TABLE SEM_Meny ADD CONSTRAINT SEM_Meny_PK PRIMARY KEY ( Kod ) ;

CREATE TABLE SEM_Objednavky
  (
    ID          NUMBER NOT NULL ,
    Datum       DATE NOT NULL ,
    Stav        VARCHAR2 (100) NOT NULL ,
    Obsah       VARCHAR2 (500) NOT NULL ,
    Produkty_ID NUMBER NOT NULL ,
    Users_ID    NUMBER NOT NULL
  ) ;
ALTER TABLE SEM_Objednavky ADD CONSTRAINT SEM_Objednavky_PK PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_Produkty
  (
    ID            NUMBER NOT NULL ,
    Nazev         VARCHAR2 (100) ,
    Cena          NUMBER ,
    Kategorie_Typ NUMBER NOT NULL
  ) ;
ALTER TABLE SEM_Produkty ADD CONSTRAINT SEM_Produkty_PK PRIMARY KEY ( ID ) ;

CREATE TABLE "SEM_Produkty-foto"
  (
    ID NUMBER NOT NULL ,
    Data BLOB ,
    Pripona_souboru VARCHAR2 (4) ,
    Datum_nahrani   DATE ,
    Produkty_ID     NUMBER NOT NULL
  ) ;
ALTER TABLE "SEM_Produkty-foto" ADD CONSTRAINT "SEM_Produkty-foto_PK" PRIMARY KEY ( ID ) ;

CREATE TABLE "SEM_Produkty-parametry"
  (
    Popis       VARCHAR2 (300) ,
    Upozorneni  VARCHAR2 (200) ,
    Schnuti     VARCHAR2 (50) ,
    Obsah       VARCHAR2 (50) ,
    ID          NUMBER NOT NULL ,
    Produkty_ID NUMBER NOT NULL
  ) ;
ALTER TABLE "SEM_Produkty-parametry" ADD CONSTRAINT "SEM_Produkty-parametry_PK" PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_Role
  ( ID NUMBER NOT NULL , Nazev VARCHAR2 (100)
  ) ;
ALTER TABLE SEM_Role ADD CONSTRAINT SEM_Role_PK PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_Staty
  (
    ID       NUMBER NOT NULL ,
    Nazev    VARCHAR2 (100) ,
    Meny_Kod VARCHAR2 (3) NOT NULL
  ) ;
ALTER TABLE SEM_Staty ADD CONSTRAINT SEM_Staty_PK PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_TEMPORARY
  (
    ID    NUMBER NOT NULL ,
    Datum DATE ,
    LOG   VARCHAR2 (500)
  ) ;
ALTER TABLE SEM_TEMPORARY ADD CONSTRAINT SEM_Temporary_PK PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_Users
  (
    ID       NUMBER NOT NULL ,
    Jmeno    VARCHAR2 (100) ,
    Prijmeni VARCHAR2 (100) ,
    Email    VARCHAR2 (100) ,
    Heslo    VARCHAR2 (200) ,
    Role_ID  NUMBER NOT NULL ,
    Staty_ID NUMBER NOT NULL
  ) ;
ALTER TABLE SEM_Users ADD CONSTRAINT SEM_Users_PK PRIMARY KEY ( ID ) ;

CREATE TABLE SEM_UsersToLogTable
  (
    Users_ID    NUMBER NOT NULL ,
    LogTable_ID NUMBER NOT NULL
  ) ;
ALTER TABLE SEM_UsersToLogTable ADD CONSTRAINT Relation_12__IDX PRIMARY KEY ( Users_ID, LogTable_ID ) ;

ALTER TABLE SEM_UsersToLogTable ADD CONSTRAINT FK_ASS_7 FOREIGN KEY ( Users_ID ) REFERENCES SEM_Users ( ID ) ;

ALTER TABLE SEM_UsersToLogTable ADD CONSTRAINT FK_ASS_8 FOREIGN KEY ( LogTable_ID ) REFERENCES SEM_LogTable ( ID ) ;

ALTER TABLE SEM_Objednavky ADD CONSTRAINT SEM_Objednavky_Produkty_FK FOREIGN KEY ( Produkty_ID ) REFERENCES SEM_Produkty ( ID ) ;

ALTER TABLE SEM_Objednavky ADD CONSTRAINT Objednavky_Users_FK FOREIGN KEY ( Users_ID ) REFERENCES SEM_Users ( ID ) ;

ALTER TABLE "SEM_Produkty-foto" ADD CONSTRAINT "SEM_Produkty-foto_Produkty_FK" FOREIGN KEY ( Produkty_ID ) REFERENCES SEM_Produkty ( ID ) ;

ALTER TABLE "SEM_Produkty-parametry" ADD CONSTRAINT "SEM_parametry_Produkty_FK" FOREIGN KEY ( Produkty_ID ) REFERENCES SEM_Produkty ( ID ) ;

ALTER TABLE SEM_Produkty ADD CONSTRAINT SEM_Produkty_Kategorie_FK FOREIGN KEY ( Kategorie_Typ ) REFERENCES SEM_Kategorie ( Typ ) ;

ALTER TABLE SEM_Staty ADD CONSTRAINT SEM_Staty_Meny_FK FOREIGN KEY ( Meny_Kod ) REFERENCES SEM_Meny ( Kod ) ;

ALTER TABLE SEM_Users ADD CONSTRAINT SEM_Users_Role_FK FOREIGN KEY ( Role_ID ) REFERENCES SEM_Role ( ID ) ;

ALTER TABLE SEM_Users ADD CONSTRAINT Users_Staty_FK FOREIGN KEY ( Staty_ID ) REFERENCES SEM_Staty ( ID ) ;

ALTER TABLE SEM_MENY add  KURZ NUMBER(7,2);
/*TABULKY DONE*/



INSERT INTO SEM_ROLE (id,nazev) VALUES (1,'Testovaci Faggot');
INSERT INTO SEM_MENY (kod,nazev) VALUES ('MRK','Říšská marka');
INSERT INTO sem_staty (id,nazev,meny_kod) VALUES (1,'treti rise','MRK');
INSERT INTO SEM_USERS (id,jmeno,prijmeni,email,heslo,role_id,staty_id) VALUES
(1,'Hitler','TESTOVAC','natzi1@gestapo.de','SwastikaForWin',1,1);
INSERT INTO SEM_KATEGORIE(typ,nazev) VALUES (1,'Hrebiky');
INSERT INTO sem_produkty(id,nazev,cena,kategorie_typ) VALUES (1,'Hrebiky ve spreji',666,1);
INSERT INTO SEM_OBJEDNAVKY(id,datum,stav,obsah,produkty_id,users_id) VALUES
(sem_seq_obj.NEXTVAL, SYSDATE,'Nemame, 3x jsme se divaly','hrebiky ve spreji',1,1);

/*TESTOVACI DATA CO SEM POUZIL*/

CREATE OR REPLACE
TRIGGER "SEM_USERS_VALIDACE"
BEFORE INSERT OR UPDATE ON SEM_USERS
FOR EACH ROW
BEGIN
:NEW.jmeno := INITCAP(:NEW.JMENO);
:NEW.prijmeni := INITCAP(:NEW.PRIJMENI);
IF
IS_VALID_EMAIL(:NEW.email)= 0
THEN
raise_application_error(-20001,'Email je uveden ve špatném tvaru which is forbidden by the law ');
END IF;
IF
:NEW.heslo IS NULL
THEN
raise_application_error(-20001,'cyka blyat nemas tam heslo');
END IF;
END;

CREATE OR REPLACE TRIGGER "SEM_LOGIN_TRIGGER"
BEFORE INSERT ON SEM_LOGTABLE
FOR EACH ROW
BEGIN
INSERT INTO SEM_temporary
(id,datum,LOG)
VALUES
  (SEM_TEMP.NEXTVAL,SYSDATE,:NEW.user_id || ' provedl pokus o prihlaseni');
END;

CREATE OR REPLACE
TRIGGER "SEM_LOGIN_TRIGGER_suc"
AFTER INSERT ON SEM_LOGTABLE
FOR EACH ROW
BEGIN
INSERT INTO SEM_temporary
(id,datum,LOG)
VALUES
  (SEM_TEMP.NEXTVAL,SYSDATE,:NEW.user_id || ' se uspesne prihlasil');
END;

/*TRIGGERY DONE*/

CREATE OR REPLACE VIEW SEM_PREHLED_STAV_ZADANA
AS
SELECT * FROM SEM_OBJEDNAVKY WHERE stav LIKE 'Zadana'
ORDER BY id ASC;

CREATE OR REPLACE VIEW SEM_PREHLED_STAV_VYRIZENA
AS
SELECT * FROM SEM_OBJEDNAVKY WHERE stav LIKE 'vyrizena'
ORDER BY id ASC;

CREATE OR REPLACE VIEW SEM_PRODUKTY_PREHLED
AS
SELECT * FROM "SEM_Produkty-foto" join sem_produkty using (ID)
ORDER BY id ASC;

/*POHLEDY DONE*/

/*TUDLE FCI UŽ bys měl mít v DB TAK BACHA Ať ji neděláš znova, ale klidně ji přepiš*/
CREATE OR REPLACE FUNCTION IS_VALID_EMAIL
(
  EMAIL IN VARCHAR2
) RETURN NUMBER AS

  i_result NUMBER;

BEGIN

  SELECT
    COUNT(*) INTO i_result
  FROM
    dual
  WHERE
    REGEXP_LIKE(EMAIL,'^[a-zA-Z0-9._-]+@+[a-zA-Z0-9.-]+\.+[a-zA-Z]{2,4}$');

  RETURN i_result;

END IS_VALID_EMAIL;

CREATE OR REPLACE FUNCTION sem_CENA_MENA
(P_PROD_ID IN NUMBER,
P_MENA_ID IN VARCHAR2)
RETURN VARCHAR2 AS
cena_v_kc NUMBER;
v_kurz NUMBER;
v_cena NUMBER;
BEGIN
SELECT cena INTO cena_v_kc FROM SEM_PRODUKTY WHERE id=p_prod_id;
IF p_mena_id LIKE 'CZK'
THEN
RETURN cena_v_kc || ' CZK';
END IF;
IF p_mena_id LIKE 'MRK'
THEN
RETURN cena_v_kc || ' MRK';
END IF;
IF p_mena_id LIKE 'USD'
THEN
SELECT kurz INTO v_kurz FROM sem_meny WHERE kod LIKE 'USD';
v_cena := cena_v_kc*v_kurz;
RETURN v_cena || 'USD';
END IF;
END sem_cena_mena;

/* FCE DONE */

CREATE OR REPLACE PROCEDURE SEM_ZMENA_STAVU_OBEDNAVKY
(
P_OBJ_ID IN NUMBER,
P_OBJ_STAV IN VARCHAR2
) AS
any_rows_found NUMBER;
BEGIN
any_rows_found := 0;
SELECT COUNT(*)
  INTO   any_rows_found
  FROM   sem_objednavky
  WHERE  id = P_OBJ_ID;
IF any_rows_found = 0
THEN
raise_application_error(-20001,'Critical Error or no data found. A team of highly trained monkeys has been dispatched to deal with this situation.');
END IF;
UPDATE SEM_OBJEDNAVKY SET stav= P_OBJ_STAV WHERE ID = P_OBJ_ID;
DBMS_OUTPUT.PUT_LINE('Stav obednavky zmenen na: ' || p_obj_stav);
END SEM_ZMENA_STAVU_OBEDNAVKY;

CREATE OR REPLACE PROCEDURE SEM_ZMENA_ROLE_USERA
(
P_USER_ID IN NUMBER,
P_ROLE_ID IN NUMBER
) AS
any_rows_found5 NUMBER;
BEGIN
any_rows_found5 := 0;
SELECT COUNT(*)
  INTO   any_rows_found5
  FROM   SEM_ROLE
  WHERE  id = P_ROLE_ID;
IF any_rows_found5 = 0
THEN
raise_application_error(-20016,'Critical Error or no data found. A team of highly trained monkeys has been dispatched to deal with this situation.');
END IF;
UPDATE SEM_USERS SET ROLE_ID= P_ROLE_ID WHERE ID = P_USER_ID;
DBMS_OUTPUT.PUT_LINE('Role zmenena na: ' || P_ROLE_ID);
END SEM_ZMENA_ROLE_USERA;

CREATE OR REPLACE PROCEDURE SEM_ZMENA_UPOZORNENI_PRODUKT
(
P_ID_PRODUKTY IN NUMBER,
P_UPOZORNENI IN VARCHAR2
) AS
any_rows_found NUMBER;
BEGIN
any_rows_found := 0;
SELECT COUNT(*)
  INTO   any_rows_found
  FROM   "SEM_PRODUKTY-foto"
  WHERE  produkty_id = P_ID_PRODUKTY;
IF any_rows_found5 = 0
THEN
raise_application_error(-20016,'Critical Error or no data found. A team of highly trained monkeys has been dispatched to deal with this situation.');
END IF;
UPDATE "SEM_PRODUKTY-parametry" SET UPOZORNENI= P_UPOZORNENI WHERE produkty_id = p_id_produkty;
DBMS_OUTPUT.PUT_LINE('Upozorneni zmeneno na: ' || p_upozorneni);
END SEM_ZMENA_UPOZORNENI_PRODUKT;

/*PROCEDURY DONE*/

CREATE SEQUENCE SEM_SEQ_OBJ
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_USERS
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_TEMP
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_LOGIN
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_PRODKUTY
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_KATEGORIE
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_PRODUKTY_PARAMETRY
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_PRODUKTY_FOTO
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_ROLE
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

CREATE SEQUENCE SEM_STATY
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1;

/*SEKVENCE DONE*/