/* pokracovani - neselektove procedury */

/* vylepsime si proceduru POCITAC_PUJCIT (nelze si pujcit nefunkcni pocitac) */
DROP PROCEDURE pocitac_pujcit;

SET TERM ^;
CREATE PROCEDURE pocitac_pujcit (idpoc INTEGER, idzam INTEGER)
AS
DECLARE VARIABLE pom INTEGER;
BEGIN
  /* neni rozbity? */
  IF (EXISTS(SELECT id_stanice FROM pocitac WHERE funkcni='ne' AND id_stanice=:idpoc)) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac nelze pujcit - je nefunkcni!';
    EXIT;
  END

  /* neni nahodou pujceny? */
  SELECT pocitac FROM pouziti WHERE (pocitac=:idpoc AND den=CAST('today' AS DATE) AND cas_do IS NULL) INTO :pom;
  IF (pom IS NOT NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac je momentalne pujceny!';
    EXIT;
  END

  /* pracuje zamestnanec na pobocce, kde je pocitac? */
  SELECT pobocka FROM zamestnanec WHERE id_zamestnance=:idzam INTO :pom;
  IF (pom IS NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Zamestnanec neexistuje!';
    EXIT;
  END
  IF (NOT EXISTS(SELECT id_stanice FROM pocitac WHERE id_stanice=:idpoc AND pobocka=:pom)) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac neexistuje anebo neni na stejne pobocce jako zamestnanec!';
    EXIT;
  END

  /* pokus ulozit data */
  BEGIN /* BEGIN je nutne kvuli EXCEPTIONu */
    INSERT INTO pouziti VALUES(:idpoc, CAST('today' AS DATE), CAST('now' AS TIME), NULL, :idzam, 'ne');
    WHEN ANY DO EXCEPTION chyba 'Pocitac nelze pujcit!';
  END
END^
SET TERM ;^
EXECUTE PROCEDURE pocitac_pujcit(2,1);
EXECUTE PROCEDURE pocitac_pujcit(1,1);
EXECUTE PROCEDURE pocitac_pujcit(5,4);
EXECUTE PROCEDURE pocitac_pujcit(4,5);

SET TERM ^;
CREATE PROCEDURE pocitac_vratit (idpoc INTEGER, zapl VARCHAR(3))
RETURNS (cena FLOAT) /* cenu vratime jako desetinne cislo */
AS
DECLARE VARIABLE dnes DATE;
DECLARE VARIABLE cas TIME;
DECLARE VARIABLE cas2 TIME;
BEGIN
  dnes = CAST('today' AS DATE);
  /* je opravdu pujceny? */
  SELECT cas_od FROM pouziti WHERE (pocitac=:idpoc AND den=:dnes AND cas_do IS NULL) INTO :cas;
  IF (cas IS NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac nebyl pujceny!';
    EXIT;
  END
  /* udelame nekolik veci najednou => transakce??? */
  UPDATE pocitac SET posledni_pouziti=:dnes WHERE id_stanice=:idpoc; /* byl pouzit dnes */
  cas2 = CAST('now' AS TIME);
  UPDATE pouziti SET zaplaceno=:zapl, cas_do=:cas2 WHERE pocitac=:idpoc AND den=:dnes AND cas_od=:cas;
  /* vypocet ceny za pouziti */
  SELECT cena_za_min FROM pobocka WHERE id_pobocky=(SELECT pobocka FROM pocitac WHERE id_stanice=:idpoc) INTO :cena;
  IF (cena IS NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac nebyl pujceny!';
    EXIT;
  END
  ELSE
    cena = cena*(cas2-cas)/60; /* vypocet ceny za pujceni, zaokrouhleno na INTEGER */
END^
SET TERM ;^
/* pouziti teto procedury je uvedeno az uplne na konci SQL prikazu (potrebujeme ziskat cas na pujceni pocitace) :-) */


/* neselektove procedury pro zmenu dat */

SET TERM ^;
CREATE PROCEDURE pocitac_pouziti_zaplaceno(idpoc INTEGER, den DATE, cas TIME) /* potrebujeme UPLNY primarni klic */
AS
BEGIN
  /* tady by se melo zkontrolovat, ze pocitac byl v ten den a cas pujceny... */

  UPDATE pouziti SET zaplaceno='ano' WHERE pocitac=:idpoc AND den=:den AND cas_od=:cas;
END^

CREATE PROCEDURE pocitac_nefunkcni(idpoc INTEGER)
AS
BEGIN
  /* tady by se melo zkontrolovat, ze pocitac existuje... */

  UPDATE pocitac SET funkcni='ne' WHERE id_stanice=:idpoc;
END^

CREATE PROCEDURE pocitac_opet_funkcni(idpoc INTEGER)
AS
BEGIN
  /* tady by se melo zkontrolovat, ze pocitac existuje... */

  UPDATE pocitac SET funkcni='ano' WHERE id_stanice=:idpoc;
END^

SET TERM ;^


/* neselektove procedury pro mazani dat - ZADNE NEMAME */



/* vytvoreni pohledu - v zadnem nesmi byt ORDER BY!!!!!! */

CREATE VIEW pobocky_pocet_zamestnancu (pob,ulice,cp,mesto,psc,pocet_zam)
AS
SELECT id_pobocky, MIN(ulice), MIN(cp), MIN(mesto), MIN(psc), COUNT(id_zamestnance)
  FROM pobocka LEFT JOIN zamestnanec ON (id_pobocky=pobocka)
  GROUP BY id_pobocky;


CREATE VIEW pobocky_pocet_pocitacu (pob,ulice,cp,mesto,psc,pocet_poc)
AS
SELECT id_pobocky, MIN(ulice), MIN(cp), MIN(mesto), MIN(psc), COUNT(id_stanice)
  FROM pobocka LEFT JOIN pocitac ON (id_pobocky=pobocka)
  GROUP BY id_pobocky;


CREATE VIEW pouziti_s_cenou (poc,den,cas1,cas2,zam,zapl,cena_min,pob) /* zaplacene i nezaplacene, i prave pujcene pocitace, k nim cena za min. na pobocce, do ktere pocitac patri */
AS
SELECT pocitac, den, cas_od, cas_do, zamestnanec, zaplaceno, cena_za_min, id_pobocky
  FROM (pouziti JOIN pocitac ON (pocitac=id_stanice)) JOIN pobocka ON (pobocka=id_pobocky);


CREATE VIEW pocitace_pujcene_ted (pob,poc,zam,den,cas1,cas2,delka_min_zatim,cena_zatim)
AS
SELECT pob, poc, zam, den, cas1, cas2, (CAST('now' AS TIME)-cas1)/60, (CAST('now' AS TIME)-cas1)/60*cena_min
  FROM pouziti_s_cenou
  WHERE cas2 IS NULL;
 

CREATE VIEW zamestnanci_trzba (zam, jmeno, prijmeni, rc, pob, den, trzba) /* trzba zamestnance na pobockach, kde pracoval */
AS
SELECT id_zamestnance, MIN(jmeno), MIN(prijmeni), MIN(rodne_cislo), pob, den, sum(cas2-cas1)/60*MIN(cena_min)
  FROM zamestnanec LEFT JOIN pouziti_s_cenou ON (id_zamestnance=zam AND cas2 IS NOT NULL) /* zajimaji nas jen ty vracene pocitace */
  GROUP BY id_zamestnance, pob, den;


CREATE VIEW pocitace_trzba (poc, cislo, ip_adresa, pob, den, trzba) /* trzba za pocitac v ruzne dny */
AS
SELECT id_stanice, MIN(cislo), MIN(ip_adresa), MIN(pob), den, sum(cas2-cas1)/60*MIN(cena_min)
  FROM pocitac LEFT JOIN pouziti_s_cenou ON (id_stanice=poc AND cas2 IS NOT NULL) /* zajimaji nas jen ty vracene pocitace */
  GROUP BY id_stanice, den;


CREATE VIEW pobocky_trzba (pob, den, trzba)
AS
SELECT id_pobocky, den, SUM(trzba)
  FROM pobocka LEFT JOIN pocitace_trzba ON (id_pobocky=pob)
  GROUP BY id_pobocky, den;


CREATE VIEW hrisnici_a_dluhy (zam, poc, pob, den, cas1, cas2, kolik_sekund, dluh)
AS
SELECT zam, poc, pob, den, cas1, cas2, cas2-cas1, (cas2-cas1)/60*cena_min
  FROM pouziti_s_cenou
  WHERE cas2 IS NOT NULL AND zapl='ne';


COMMIT WORK;


/* vytvoreni selektovych procedur */

SET TERM ^;

CREATE PROCEDURE zamestnanec_trzba_den(idzam INTEGER)
RETURNS (trzba FLOAT, den DATE, pobocka INTEGER)
AS
BEGIN
  FOR SELECT SUM(trzba), den, pob FROM zamestnanci_trzba WHERE zam=:idzam GROUP BY den, pob ORDER BY den DESC INTO :trzba, :den, :pobocka
    DO SUSPEND;
END^


CREATE PROCEDURE zamestnanec_dluhy(idzam INTEGER)
RETURNS (dluh FLOAT, pocitac INTEGER, den DATE, cas_od TIME, cas_do TIME, pobocka INTEGER)
AS
BEGIN
  FOR SELECT dluh,poc,den,cas1,cas2,pob FROM hrisnici_a_dluhy WHERE zam=:idzam ORDER BY den DESC, cas1 
        INTO :dluh, :pocitac, :den, :cas_od, :cas_do, pobocka
    DO SUSPEND;
END^

SET TERM ;^


COMMIT WORK;

/* vraceni pujcenych pocitacu */

EXECUTE PROCEDURE pocitac_vratit(4,'ne'); /* nezaplaceno */
EXECUTE PROCEDURE pocitac_vratit(2,'ano');
EXECUTE PROCEDURE pocitac_vratit(1,'ano');
EXECUTE PROCEDURE pocitac_vratit(5,'ano');

/* pouziti pohledu pro vypis dluhu */


/* zaplaceni 

EXECUTE PROCEDURE pobocka_pridej('Strmá', '11', 'Jílové', 40500, 1);
SELECT * FROM pobocky_pocet_zamestnancu;
SELECT pob,pocet_zam FROM pobocky_pocet_zamestnancu;
SELECT pob,pocet_zam FROM pobocky_pocet_zamestnancu ORDER BY mesto, ulice, cp;
SELECT pob,mesto,ulice,pocet_zam FROM pobocky_pocet_zamestnancu ORDER BY mesto, ulice, cp;

SELECT * FROM pobocky_pocet_pocitacu;
SELECT pob,pocet_poc FROM pobocky_pocet_pocitacu;

SELECT * FROM pocitace_pujcene_ted; /* ktere pocitace jsou ted pujcene na vsech pobockach */
SELECT * FROM pocitace_pujcene_ted WHERE pob=1; /* ktere pocitace jsou momentalne pujcene na pobocce s id=1 */

SELECT * FROM pouziti_s_cenou ORDER BY pob, den;

SELECT * FROM zamestnanci_trzba ORDER BY trzba DESC; /* kdo nejvic vydelal? */
SELECT * FROM pocitace_trzba ORDER BY trzba DESC; /* ktery pocitac nejvic vydelal? */
SELECT * FROM pobocky_trzba ORDER BY trzba DESC; /* kde nejvic vydelali? */


/* umele pridame nekolik vypujceni pro zamestnance s id=4 */
INSERT INTO pouziti VALUES (3,'2009-05-04','9:15:24','10:11:00',4,'ano');
INSERT INTO pouziti VALUES (4,'2009-05-12','11:35:00','11:50:30',4,'ne');
INSERT INTO pouziti VALUES (5,'2009-05-12','11:02:40','11:48:30',4,'ne');
INSERT INTO pouziti VALUES (4,'2009-05-24','17:45:20','17:51:00',4,'ano');

SELECT * FROM zamestnanec_trzba_den(4); /* trzby jednoho zamestnance */
SELECT * FROM zamestnanec_dluhy(4); /* za ktera vypujceni pocitacu danym zamestnancem neni zaplaceno? */
