
Created 19.4.2014
Modified 22.4.2014
Model Oracle 11g Release 2
Database Oracle 11g Release 2


-- Create tables section -------------------------------------------------

-- Table Osoby

CREATE TABLE Osoby(
  id_osoby Number NOT NULL,
  jmeno Varchar2(20 ) NOT NULL,
  prijmeni Varchar2(20 ) NOT NULL,
  cislo_dokladu Number NOT NULL,
  datum_narozeni Date NOT NULL,
  poznamka Varchar2(100 ),
  id_adresy Number NOT NULL,
  id_kontaktu Number NOT NULL,
  id_login Number NOT NULL
)


-- Add keys for table Osoby

ALTER TABLE Osoby ADD CONSTRAINT Key1 PRIMARY KEY (id_osoby)


-- Table Adresy

CREATE TABLE Adresy(
  id_adresy Number NOT NULL,
  ulice Varchar2(30 ) NOT NULL,
  cislo_popisne Number NOT NULL,
  mesto Varchar2(30 ) NOT NULL,
  psc Varchar2(6 ) NOT NULL,
  stat Varchar2(30 ) NOT NULL
)


-- Add keys for table Adresy

ALTER TABLE Adresy ADD CONSTRAINT Key2 PRIMARY KEY (id_adresy)


-- Table Kontakty

CREATE TABLE Kontakty(
  id_kontaktu Number NOT NULL,
  telefon Number NOT NULL,
  email Varchar2(50 ) NOT NULL
)


-- Add keys for table Kontakty

ALTER TABLE Kontakty ADD CONSTRAINT Key3 PRIMARY KEY (id_kontaktu)


ALTER TABLE Kontakty ADD CONSTRAINT email UNIQUE (email)


-- Table Login

CREATE TABLE Login(
  id_login Number NOT NULL,
  login_name Varchar2(50 ) NOT NULL,
  password Varchar2(50 ) NOT NULL,
  id_typ_login Number NOT NULL
)


-- Add keys for table Login

ALTER TABLE Login ADD CONSTRAINT Key4 PRIMARY KEY (id_login)


ALTER TABLE Login ADD CONSTRAINT login_name UNIQUE (login_name)


-- Table Typ_loginu

CREATE TABLE Typ_loginu(
  id_typ_login Number NOT NULL,
  typ_login Varchar2(30 ) NOT NULL
)


-- Add keys for table Typ_loginu

ALTER TABLE Typ_loginu ADD CONSTRAINT Key5 PRIMARY KEY (id_typ_login)


-- Table Objednavky

CREATE TABLE Objednavky(
  id_objednavky Number NOT NULL,
  datum_objednavky Date NOT NULL,
  datum_zacatku Date NOT NULL,
  datum_konce Date NOT NULL,
  poznamka Varchar2(300 ),
  pocet_osob Number NOT NULL,
  pocet_deti Number,
  faktura Blob,
  id_osoby Number NOT NULL,
  id_stavu Number NOT NULL
)


-- Add keys for table Objednavky

ALTER TABLE Objednavky ADD CONSTRAINT Key6 PRIMARY KEY (id_objednavky)


-- Table Stav_objednavky

CREATE TABLE Stav_objednavky(
  id_stavu Number NOT NULL,
  stav Varchar2(15 ) NOT NULL
)


-- Add keys for table Stav_objednavky

ALTER TABLE Stav_objednavky ADD CONSTRAINT Key7 PRIMARY KEY (id_stavu)


-- Table Pokoje

CREATE TABLE Pokoje(
  id_pokoje Number NOT NULL,
  nazev_pokoje Varchar2(30 ) NOT NULL,
  cena_dosp Number NOT NULL,
  cena_dite Number NOT NULL,
  id_typu_pokoje Number NOT NULL
)


-- Add keys for table Pokoje

ALTER TABLE Pokoje ADD CONSTRAINT Key8 PRIMARY KEY (id_pokoje)


-- Table Typ_pokoje

CREATE TABLE Typ_pokoje(
  id_typu_pokoje Number NOT NULL,
  typ_pokoje Varchar2(40 ) NOT NULL,
  max_osob Number NOT NULL
)


-- Add keys for table Typ_pokoje

ALTER TABLE Typ_pokoje ADD CONSTRAINT Key9 PRIMARY KEY (id_typu_pokoje)


-- Table Sluzby

CREATE TABLE Sluzby(
  id_sluzby Number NOT NULL,
  nazev_sluzby Varchar2(40 ) NOT NULL,
  cena_sluzby Number NOT NULL
)


-- Add keys for table Sluzby

ALTER TABLE Sluzby ADD CONSTRAINT Key10 PRIMARY KEY (id_sluzby)


-- Table Platba

CREATE TABLE Platba(
  id_platby Number NOT NULL,
  castka Number NOT NULL,
  datum_platby Date,
  id_objednavky Number NOT NULL,
  id_zpusobu_platby Number NOT NULL
)


-- Add keys for table Platba

ALTER TABLE Platba ADD CONSTRAINT Key11 PRIMARY KEY (id_platby)


-- Table Zpusob_platby

CREATE TABLE Zpusob_platby(
  id_zpusobu_platby Number NOT NULL,
  zpusob_platby Varchar2(20 ) NOT NULL
)


-- Add keys for table Zpusob_platby

ALTER TABLE Zpusob_platby ADD CONSTRAINT Key12 PRIMARY KEY (id_zpusobu_platby)


-- Table Pokoje_objednavky

CREATE TABLE Pokoje_objednavky(
  id_pokoje Number NOT NULL,
  id_objednavky Number NOT NULL
)


-- Add keys for table Pokoje_objednavky

ALTER TABLE Pokoje_objednavky ADD CONSTRAINT Key13 PRIMARY KEY (id_pokoje,id_objednavky)


-- Table Objednavky_sluzby

CREATE TABLE Objednavky_sluzby(
  id_objednavky Number NOT NULL,
  id_sluzby Number NOT NULL,
  pocet Number NOT NULL
)


-- Add keys for table Objednavky_sluzby

ALTER TABLE Objednavky_sluzby ADD CONSTRAINT Key14 PRIMARY KEY (id_objednavky,id_sluzby)


-- Create relationships section ------------------------------------------------- 

ALTER TABLE Osoby ADD CONSTRAINT AdresyOsoby FOREIGN KEY (id_adresy) REFERENCES Adresy (id_adresy)


ALTER TABLE Osoby ADD CONSTRAINT KontaktyOsoby FOREIGN KEY (id_kontaktu) REFERENCES Kontakty (id_kontaktu)


ALTER TABLE Login ADD CONSTRAINT TypLoginuLogin FOREIGN KEY (id_typ_login) REFERENCES Typ_loginu (id_typ_login)


ALTER TABLE Osoby ADD CONSTRAINT LoginOsoby FOREIGN KEY (id_login) REFERENCES Login (id_login)


ALTER TABLE Objednavky ADD CONSTRAINT OsobyObjednavky FOREIGN KEY (id_osoby) REFERENCES Osoby (id_osoby)


ALTER TABLE Objednavky ADD CONSTRAINT StavObjednavkyObjednavky FOREIGN KEY (id_stavu) REFERENCES Stav_objednavky (id_stavu)


ALTER TABLE Pokoje ADD CONSTRAINT TypPokojePokoje FOREIGN KEY (id_typu_pokoje) REFERENCES Typ_pokoje (id_typu_pokoje)


ALTER TABLE Pokoje_objednavky ADD CONSTRAINT PokojePokojeObjednavky FOREIGN KEY (id_pokoje) REFERENCES Pokoje (id_pokoje)


ALTER TABLE Pokoje_objednavky ADD CONSTRAINT ObjednavkyPokojeObjednavky FOREIGN KEY (id_objednavky) REFERENCES Objednavky (id_objednavky)


ALTER TABLE Objednavky_sluzby ADD CONSTRAINT ObjednavkyObjednavkySluzby FOREIGN KEY (id_objednavky) REFERENCES Objednavky (id_objednavky)


ALTER TABLE Objednavky_sluzby ADD CONSTRAINT SluzbyObjednavkySluzby FOREIGN KEY (id_sluzby) REFERENCES Sluzby (id_sluzby)


ALTER TABLE Platba ADD CONSTRAINT ObjednavkyPlatba FOREIGN KEY (id_objednavky) REFERENCES Objednavky (id_objednavky)


ALTER TABLE Platba ADD CONSTRAINT ZpusobPlatbyPlatby FOREIGN KEY (id_zpusobu_platby) REFERENCES Zpusob_platby (id_zpusobu_platby)


-- SEKVENCE

CREATE SEQUENCE  ID_ADRESY_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  ID_KONTAKTU_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  ID_LOGIN_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  ID_OBJEDNAVKY_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  ID_OSOBY_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  ID_PLATBA_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;
CREATE SEQUENCE  ID_POKOJ_SEQ  MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;

-- TRIGERY
create or replace 
TRIGGER ADRESA_ID 
BEFORE INSERT ON ADRESY 
FOR EACH ROW 
BEGIN
 SELECT ID_ADRESY_SEQ.nextval INTO :new.id_adresy FROM DUAL; 
END;

create or replace 
TRIGGER DEFAULT_TYPE 
BEFORE INSERT ON LOGIN 
FOR EACH ROW 
BEGIN
  SELECT 2 INTO :new.id_typ_login FROM DUAL; 
END;

create or replace 
TRIGGER KONTATKY_ID 
BEFORE INSERT ON KONTAKTY 
FOR EACH ROW 
BEGIN
  SELECT ID_KONTAKTU_SEQ.nextval INTO :new.id_kontaktu FROM DUAL; 
END;

create or replace 
TRIGGER LOGIN_ID 
BEFORE INSERT ON LOGIN 
FOR EACH ROW 
BEGIN
  SELECT ID_LOGIN_SEQ.nextval INTO :new.id_login FROM DUAL; 
END;

create or replace 
TRIGGER OBJEDNAVKY_ID 
BEFORE INSERT ON OBJEDNAVKY 
FOR EACH ROW 
BEGIN
  SELECT ID_OBJEDNAVKY_SEQ.nextval INTO :new.id_objednavky FROM DUAL; 
END;

create or replace 
TRIGGER OSOBA_ID 
BEFORE INSERT ON OSOBY 
FOR EACH ROW 
BEGIN
  SELECT ID_OSOBY_SEQ.nextval INTO :new.id_osoby FROM DUAL; 
END;

create or replace 
TRIGGER PLATBA_ID 
BEFORE INSERT ON PLATBA 
FOR EACH ROW 
BEGIN
  SELECT ID_PLATBA_SEQ.nextval INTO :new.id_platby FROM DUAL; 
END;

create or replace 
TRIGGER POKOJ_ID 
BEFORE INSERT ON POKOJE 
FOR EACH ROW 
BEGIN
  SELECT ID_POKOJ_SEQ.nextval INTO :new.id_pokoje FROM DUAL; 
END;

-- PROCEDURY

create or replace 
PROCEDURE NOVA_REZERVACE 
(
  DATUM_OD IN VARCHAR2  
, DATUM_DO IN VARCHAR2  
, ID IN NUMBER  
, CENA IN NUMBER  
, POCET_POLO IN NUMBER  
, POCET_PLNA IN NUMBER  
, POCET_OSOB IN NUMBER  
, POCET_DETI IN NUMBER  
, POKOJ IN VARCHAR2  
) AS 
id_obj number;
id_pokoj number;
BEGIN
  INSERT INTO OBJEDNAVKY(id_stavu, id_osoby, datum_objednavky,datum_zacatku, datum_konce, poznamka, pocet_osob, pocet_deti)
  VALUES(4,ID,SYSDATE, to_date(datum_od,'dd.MM.yyyy'), to_date(datum_do,'dd.MM.yyyy'), 'Rezervace pes webov portl', pocet_osob, pocet_deti);
  
  SELECT MAX(id_objednavky) INTO id_obj FROM OBJEDNAVKY;
  
  INSERT INTO PLATBA(castka, id_zpusobu_platby, id_objednavky) VALUES (cena, 1, id_obj);
  
  SELECT id_pokoje INTO id_pokoj FROM POKOJE WHERE nazev_pokoje = pokoj;
  
  INSERT INTO POKOJ_OBJEDNAVKA(id_pokoje, id_objednavky) VALUES (id_pokoj, id_obj);
  
  IF pocet_polo != 0 THEN
    INSERT INTO SLUZBY_OBJEDNAVKA(id_sluzby, id_objednavky, pocet) VALUES(1, id_obj, pocet_polo);
  END IF;
  
  IF pocet_plna != 0 THEN
    INSERT INTO SLUZBY_OBJEDNAVKA(id_sluzby, id_objednavky, pocet) VALUES(2, id_obj, pocet_plna);
  END IF;
END NOVA_REZERVACE;

create or replace 
PROCEDURE UPDATEUDAJE 
(
  ID IN NUMBER  
, ULICE IN VARCHAR2  
, CPOPISNE IN NUMBER  
, MESTO IN VARCHAR2  
, PSC IN NUMBER  
, CDOKLADU IN NUMBER  
, TELEFON IN NUMBER  
) AS 
BEGIN

  UPDATE (SELECT ADRESY.ulice as ul, ADRESY.cislo_popisne as cp,
  ADRESY.mesto as me, ADRESY.psc as ps
  FROM OSOBY
  JOIN ADRESY using(id_adresy)
  JOIN KONTAKTY using(id_kontaktu)
  WHERE id_osoby = ID) t
  SET t.ul = ulice,
  t.cp = cpopisne,
  t.me = mesto,
  t.ps = psc;
  
  UPDATE OSOBY SET cislo_dokladu = cdokladu WHERE id_osoby = ID;
  
  UPDATE (SELECT KONTAKTY.telefon as te
  FROM OSOBY
  JOIN KONTAKTY using(id_kontaktu)
  WHERE id_osoby = ID) t
  SET t.te = telefon;
  
  
END UPDATEUDAJE;

-- TYPY POKOJU

INSERT INTO TYP_POKOJE(ID_TYPU_POKOJE, TYP_POKOJE, MAX_OSOB)
VALUES(1,'1+1',1);

INSERT INTO TYP_POKOJE(ID_TYPU_POKOJE, TYP_POKOJE, MAX_OSOB)
VALUES(2,'2+1',2);

INSERT INTO TYP_POKOJE(ID_TYPU_POKOJE, TYP_POKOJE, MAX_OSOB)
VALUES(3,'3+1',3);

-- POKOJE

INSERT INTO POKOJE(ID_POKOJE, NAZEV_POKOJE, ID_TYPU_POKOJE, CENA_DOSP, CENA_DITE)
VALUES(1, 'AP01', 1, 300, 150);

INSERT INTO POKOJE(ID_POKOJE, NAZEV_POKOJE, ID_TYPU_POKOJE, CENA_DOSP, CENA_DITE)
VALUES(2, 'AP02', 2, 400, 200);

INSERT INTO POKOJE(ID_POKOJE, NAZEV_POKOJE, ID_TYPU_POKOJE, CENA_DOSP, CENA_DITE)
VALUES(3, 'AP03', 3, 500, 250);

-- STAV OBJEDNAVKY

INSERT INTO STAV_OBJEDNAVKY(ID_STAVU, STAV)
VALUES(1, 'Rezervovano');

INSERT INTO STAV_OBJEDNAVKY(ID_STAVU, STAV)
VALUES(2, 'Check IN');

INSERT INTO STAV_OBJEDNAVKY(ID_STAVU, STAV)
VALUES(3, 'Zaplaceno');

INSERT INTO STAV_OBJEDNAVKY(ID_STAVU, STAV)
VALUES(4, 'Nepotvrzeno');

-- ZPUSOB PLATBY

INSERT INTO ZPUSOB_PLATBY(ID_ZPUSOBU_PLATBY, ZPUSOB_PLATBY)
VALUES(1, 'Hotov');

INSERT INTO ZPUSOB_PLATBY(ID_ZPUSOBU_PLATBY, ZPUSOB_PLATBY)
VALUES(2, 'Kartou');

-- SLUZBY

INSERT INTO SLUZBY(ID_SLUZBY, NAZEV_SLUZBY, CENA_SLUZBY)
VALUES(1, 'Polopenze', 150);

INSERT INTO SLUZBY(ID_SLUZBY, NAZEV_SLUZBY, CENA_SLUZBY)
VALUES(2, 'Plnapenze', 300);

-- TYP LOGINU

INSERT INTO TYP_LOGINU(ID_TYP_LOGIN, TYP_LOGIN)
VALUES(1, 'zamestnanec');

INSERT INTO TYP_LOGINU(ID_TYP_LOGIN, TYP_LOGIN)
VALUES(2, 'host');

-- VYTVORENI TESTOVACIHO HOSTA

INSERT INTO ADRESY(ID_ADSESY, ULICE, CISLO_POPISNE, MESTO, PSC, STAT)
VALUES(1, 'Prask', 112, 'Brno', '542 03', 'Czech Republic);

INSERT INTO KONTAKTY(ID_KONTAKTU, TELEFON, EMAIL)
VALUES(1, 777555333, 'test@test.cz');

INSERT INTO LOGIN(ID_LOGIN, LOGIN_NAME, PASSWORD, ID_TYP_LOGIN)
VALUES(1, 'test@test.cz', 'a94a8fe5ccb19ba61c4c0873d391e987982fbbd3', 2);

INSERT INTO OSOBY(ID_OSOBY, ID_LOGIN, ID_KONTAKTU, ID_ADRESY, JMENO, PRIJMENI, CISLO_DOKLADU, DATUM_NAROZENI)
VALUES(1, 1, 1, 1, 'Tomas', 'Tester', 1125334, to_date('15.1.1992', dd.MM.yyyy));

-- VYTVORENI TESTOVACIHO ZAMESTNANCE

INSERT INTO ADRESY(ID_ADSESY, ULICE, CISLO_POPISNE, MESTO, PSC, STAT)
VALUES(2, 'Adminsk', 105, 'Praha', '541 23', 'Czech Republic);

INSERT INTO KONTAKTY(ID_KONTAKTU, TELEFON, EMAIL)
VALUES(2, 356989654, 'admin@admin.cz');

INSERT INTO LOGIN(ID_LOGIN, LOGIN_NAME, PASSWORD, ID_TYP_LOGIN)
VALUES(2, 'admin', 'bakalarka', 1);

INSERT INTO OSOBY(ID_OSOBY, ID_LOGIN, ID_KONTAKTU, ID_ADRESY, JMENO, PRIJMENI, CISLO_DOKLADU, DATUM_NAROZENI)
VALUES(2, 2, 2, 2, 'Tomas', 'Admin', 4589785, to_date('15.5.1988', dd.MM.yyyy));




