/******************************************************************************/
/****         Generated by IBExpert 2005.02.14 13.4.2006 15:54:04          ****/
/******************************************************************************/

SET SQL DIALECT 3;


CREATE DATABASE 'localhost:/Databases/laborant.gdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET WIN1250;

/******************************************************************************/
/****                        User Defined Functions                        ****/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION RJUST
    CSTRING(256),
    INTEGER
    RETURNS CSTRING(256) FREE_IT
    ENTRY_POINT 'RJust' MODULE_NAME 'CSC_UDF';


DECLARE EXTERNAL FUNCTION STRBLOB
    CSTRING(32765),
    BLOB
    RETURNS PARAMETER 2
    ENTRY_POINT 'StrBlob' MODULE_NAME 'BlobUDFLib.dll';


DECLARE EXTERNAL FUNCTION STRIM
    CSTRING(256)
    RETURNS CSTRING(256) FREE_IT
    ENTRY_POINT 'STrim' MODULE_NAME 'CSC_UDF';


DECLARE EXTERNAL FUNCTION SUBSTR
    CSTRING(256),
    INTEGER,
    INTEGER
    RETURNS CSTRING(256) FREE_IT
    ENTRY_POINT 'Substr' MODULE_NAME 'CSC_UDF';


CREATE DOMAIN T_AKTDAT AS DATE default 'today' NOT NULL;
CREATE DOMAIN T_AKTIV  AS INTEGER default 1;
CREATE DOMAIN T_BNAZEV AS VARCHAR(80) COLLATE PXW_CSY;
CREATE DOMAIN T_DATUM AS TIMESTAMP;
CREATE DOMAIN T_DIC AS CHAR(12);
CREATE DOMAIN T_ICO AS CHAR(10);
CREATE DOMAIN T_ICZ AS CHAR(8);
CREATE DOMAIN T_IDENT AS INTEGER NOT NULL;
CREATE DOMAIN T_IDENTZ AS INTEGER default 0;
CREATE DOMAIN T_IDNAZ AS CHAR(12) NOT NULL COLLATE PXW_CSY;
CREATE DOMAIN T_INT AS INTEGER default 0;
CREATE DOMAIN T_JMENO AS VARCHAR(30) COLLATE PXW_CSY;
CREATE DOMAIN T_KOD1 AS CHAR(1) default ' ' NOT NULL;
CREATE DOMAIN T_KOD2 AS CHAR(2) DEFAULT '@';
CREATE DOMAIN T_KOD3 AS CHAR(3);
CREATE DOMAIN T_KOD5 AS CHAR(5);
CREATE DOMAIN T_KOEF AS DOUBLE PRECISION default 0;
CREATE DOMAIN T_LINE AS VARCHAR(1024) COLLATE PXW_CSY;
CREATE DOMAIN T_LNAZEV AS VARCHAR(60) COLLATE PXW_CSY;
CREATE DOMAIN T_LOGICAL AS SMALLINT default 0;
CREATE DOMAIN T_NAJEM AS DOUBLE PRECISION default 0;
CREATE DOMAIN T_ODB AS SMALLINT default 0;
CREATE DOMAIN T_PENIZE AS NUMERIC(15,2) default 0;
CREATE DOMAIN T_PLOCHA AS NUMERIC(15,2) default 0;
CREATE DOMAIN T_POCET AS INTEGER default 0 check (value >= 0);
CREATE DOMAIN T_POZN AS BLOB SUB_TYPE 1 SEGMENT SIZE 80;
CREATE DOMAIN T_PROCENTA AS NUMERIC(15,2) default 0;
CREATE DOMAIN T_PSC AS CHAR(6);
CREATE DOMAIN T_RC AS CHAR(11);
CREATE DOMAIN T_SLINE AS VARCHAR(240) COLLATE PXW_CSY;
CREATE DOMAIN T_SNAZEV AS CHAR(12) COLLATE PXW_CSY;
CREATE DOMAIN T_ZKRATKA AS CHAR(4) COLLATE PXW_CSY;

/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR GEN_CISVY;
CREATE GENERATOR GEN_IDLEK;
CREATE GENERATOR GEN_IDOBJ;
CREATE GENERATOR GEN_IDOBJCISLO;
CREATE GENERATOR GEN_IDPAC;
CREATE GENERATOR GEN_IDPF;
CREATE GENERATOR GEN_IDVYS;
SET TERM ^ ;

/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/

CREATE PROCEDURE DELETE_DOWN (PROFIL INTEGER, NODE INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE DELETE_NODE (
    PROFIL INTEGER,
    NODE INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE DELETE_UP (
    PROFIL INTEGER,
    NODE INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GEN_CVY
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GEN_LEK
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GEN_OBJ
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GEN_OBJCISLO
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GEN_PAC
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GEN_VYS
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GRANT_ALL (
    NAZEV VARCHAR(50))
RETURNS (
    N INTEGER,
    TEXT VARCHAR(1024))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE CHECKDG (
    DG CHAR(4))
RETURNS (
    OK INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE CHECKLEKID (
    ICZ CHAR(11))
RETURNS (
    OK INTEGER,
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE CHECKPACID (
    RC CHAR(11))
RETURNS (
    OK INTEGER,
    ID INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE INSERT_PRIJEM (
    NODE INTEGER,
    OBJ INTEGER,
    SPEC VARCHAR(30))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE NIC
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE ROZTEST
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SAVE_DOWN (
    PROFIL INTEGER,
    NODE INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SAVE_NODE (
    PROFIL INTEGER,
    NODE INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SAVE_UP (
    PROFIL INTEGER,
    NODE INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE SHOWGEN_OBJ
RETURNS (
    CISLO INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE VYSLEDEK (
    OBJ INTEGER)
RETURNS (
    IDVYS INTEGER,
    COBJ INTEGER,
    KODVYS CHAR(5),
    NAZEV VARCHAR(80),
    VYSLTEXT VARCHAR(240),
    NORMA VARCHAR(50),
    ODESLANO DATE,
    DATVYS DATE,
    TEXT1 VARCHAR(240),
    TEXT2 VARCHAR(240),
    TEXT3 VARCHAR(240),
    POZNAMKA VARCHAR(1024),
    LEKAR CHAR(10))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE VYSLEDEKTEXT (
    IDVYS INTEGER)
RETURNS (
    NAZEV VARCHAR(80),
    VYSLTEXT VARCHAR(240))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE VYSLEDKY (
    IDPAC INTEGER)
RETURNS (
    IDVYS INTEGER,
    COBJ INTEGER,
    KODVYS CHAR(5),
    NAZEV VARCHAR(80),
    VYSLTEXT VARCHAR(240),
    NORMA VARCHAR(50),
    ODESLANO DATE,
    DATVYS DATE,
    TEXT1 VARCHAR(240),
    TEXT2 VARCHAR(240),
    TEXT3 VARCHAR(240),
    POZNAMKA VARCHAR(1024),
    LEKAR CHAR(10))
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE CISBOD (
    KOD   T_KOD5 NOT NULL,
    BODY  T_IDENTZ,
    TAG   T_IDENTZ
);


CREATE TABLE CISDG (
    CISDG  T_ZKRATKA NOT NULL,
    TEXT   T_LNAZEV,
    TAG    T_IDENTZ
);


CREATE TABLE CISDMR (
    CISDMR  T_IDENT,
    DAT1    T_DATUM,
    DAT2    T_DATUM,
    MES     T_INT,
    ROK     T_INT,
    OBD     T_SNAZEV,
    MMYYYY  CHAR(6)
);


CREATE TABLE CISKAT (
    CISKAT   T_IDENT NOT NULL,
    ZKRATKA  T_ZKRATKA,
    TEXT     T_LNAZEV
);


CREATE TABLE CISMAT (
    CISMAT   T_IDENT,
    NAZEV    T_JMENO,
    ZKRATKA  T_ZKRATKA,
    TAG      T_IDENTZ
);


CREATE TABLE CISODB (
    CISODB  T_KOD3 NOT NULL,
    NAZEV   T_BNAZEV,
    TAG     T_IDENTZ
);


CREATE TABLE CISPOJ (
    CISPOJ   T_IDENT,
    NAZEV    T_LNAZEV,
    ZKRATKA  T_JMENO,
    TAG      T_IDENTZ,
    ADRESA1  T_LNAZEV COLLATE PXW_CSY,
    ADRESA2  T_LNAZEV COLLATE PXW_CSY,
    PSC      T_PSC,
    MISTO    T_LNAZEV COLLATE PXW_CSY,
    TELEFON  T_LNAZEV COLLATE PXW_CSY,
    MAIL     T_LNAZEV COLLATE PXW_CSY,
    URL      T_LNAZEV COLLATE PXW_CSY
);


CREATE TABLE CISVYS (
    CISVYS    T_IDENT,
    CHILD     T_LOGICAL,
    PARENT    T_IDENTZ,
    USED      T_IDENTZ,
    KEYCODE   T_KOD1,
    KOD       T_KOD5,
    NAZEV     T_BNAZEV,
    ZKRATKA   T_JMENO,
    SKM       T_KOD2,
    NORMA     T_LNAZEV,
    TVIN      T_IDENTZ,
    TVOUT     T_IDENTZ,
    TAG       T_IDENTZ,
    NAZLN1    T_JMENO,
    NAZLZ1    T_JMENO,
    NAZLL1    T_JMENO,
    NAZLN2    T_JMENO,
    NAZLZ2    T_JMENO,
    NAZLL2    T_JMENO,
    JEDN1     T_JMENO,
    JEDN2     T_JMENO,
    KODZ1     T_JMENO,
    SKUPZ1    T_INT,
    KODZ2     T_JMENO,
    SKUPZ2    T_INT,
    DOLMEZ1   T_KOEF,
    PODMEZI1  T_LNAZEV,
    VMEZICH1  T_LNAZEV,
    NADMEZI1  T_LNAZEV,
    HORMEZ1   T_KOEF,
    DOLMEZ2   T_KOEF,
    PODMEZI2  T_LNAZEV,
    VMEZICH2  T_LNAZEV,
    NADMEZI2  T_LNAZEV,
    HORMEZ2   T_KOEF,
    TEXT1     T_SLINE,
    TEXT2     T_SLINE COLLATE PXW_CSY,
    TEXT3     T_SLINE COLLATE PXW_CSY,
    TYPVYSL   T_IDENTZ,
    SPEC      T_IDENTZ,
    ROZTEST   T_IDENTZ
);


CREATE TABLE KODPOVED (
    KOD      T_KOD1,
    SKUPINA  T_IDENT,
    TEXT     T_JMENO,
    HODNOTA  T_KOEF,
    POZN     T_LINE
);


CREATE TABLE LABORATORE (
    IDLAB         T_IDENT NOT NULL,
    LABICO        T_DIC,
    LABICZ        T_ICO NOT NULL,
    LABNAZEV      T_LNAZEV,
    LABVEDOUCI    T_LNAZEV,
    LABLEKAR      T_LNAZEV,
    LABADRESA1    T_LNAZEV,
    LABADRESA2    T_LNAZEV,
    LABADRESA3    T_LNAZEV,
    LABTELEFON    T_LNAZEV,
    LABEMAIL      T_LNAZEV,
    LABODBORNOST  T_KOD3,
    LABOBCISLO    T_IDENTZ
);


CREATE TABLE LEKARI (
    IDLEK      T_IDENT,
    LETITPR    T_JMENO,
    LEJMENO    T_LNAZEV,
    LEPRIJM    T_LNAZEV,
    LETITZA    T_JMENO,
    LEODB      T_KOD3 NOT NULL,
    LEIDZA     T_KOD5,
    LEOSCISLO  T_SNAZEV,
    LEADR1     T_LNAZEV,
    LEADR2     T_LNAZEV,
    LEADR3     T_LNAZEV,
    LEPSC      T_PSC,
    LEMISTO    T_LNAZEV,
    LETEL      T_LNAZEV,
    LEMAIL     T_LNAZEV COLLATE PXW_CSY,
    TAG        T_IDENTZ,
    LECISLO    COMPUTED BY (LEIDZA||LEOSCISLO),
    DMR        T_AKTDAT NOT NULL,
    LECAPTION  COMPUTED BY (LETITPR||' '||LEJMENO||' '||LEPRIJM||LETITZA||', '||LEADR1||', '||LEADR2)
);


CREATE TABLE OBJEDNAVKY (
    IDOBJ       T_IDENT,
    IDL         T_IDENTZ,
    OBCISLO     T_IDENT,
    OBIDPAC     T_IDENT,
    OBIDLEK     T_IDENT,
    OBODESLANO  T_DATUM,
    OBPRIJEM    T_DATUM,
    OBDG        T_ZKRATKA NOT NULL,
    OBIDMAT     T_IDENTZ,
    OBODBER     T_LOGICAL,
    OBPV        T_POCET,
    OBPHV       T_POCET,
    OBHOTOVO    T_LOGICAL,
    OBPRUVODKA  T_DATUM,
    OBOBDOBI    T_PSC,
    OBPOZN      T_SLINE,
    OBTEMP      T_JMENO COLLATE PXW_CSY,
    TAG         T_IDENTZ,
    USR         T_IDENTZ,
    DMR         T_AKTDAT NOT NULL,
    OBZP        T_IDENT,
    OBDATODB    T_DATUM
);


CREATE TABLE PACIENTI (
    IDPAC      T_IDENT,
    PATITPR    T_JMENO,
    PAJMENO    T_JMENO,
    PAPRIJM    T_JMENO,
    PAPRIJMS   T_JMENO,
    PARC       T_RC NOT NULL,
    PATITZA    T_JMENO,
    PAULICE1   T_JMENO,
    PAULICE2   T_JMENO,
    PAPSC      T_PSC,
    PAMISTO    T_JMENO,
    PATEL      T_LNAZEV,
    PAZP       T_IDENT NOT NULL,
    PAIDLEK    T_IDENTZ,
    TAG        T_IDENTZ,
    PACAPTION  COMPUTED BY (PATITPR||' '||PAJMENO||' '||PAPRIJM||PATITZA),
    PADRESA    COMPUTED BY (PAULICE1||' '||PAULICE2||', '||PAPSC||PAMISTO),
    DMR        T_AKTDAT NOT NULL,
    PAZEME     T_IDENTZ,
    PAKAT      T_IDENTZ
);


CREATE TABLE PARLAB (
    COBDOBI    T_DATUM,
    AOBDOBI    T_DATUM,
    DATARCH    T_DATUM,
    AKTDAT     T_AKTDAT,
    SUPERUSER  T_LNAZEV,
    DATUMVYTV  T_AKTDAT,
    CORG       T_ICO,
    ORGICO     T_ICO,
    ORGDIC     T_DIC,
    CLAB       T_KOD3,
    AKTDATOBJ  T_AKTDAT
);


CREATE TABLE PROFILY (
    IDPF      T_IDENT,
    PFUSER    T_IDENT,
    PFCISVYS  T_IDENTZ,
    PFSHOW    T_LOGICAL
);


CREATE TABLE UZIVATEL (
    IDUZIVATEL  T_IDENTZ NOT NULL,
    USERNAME    T_JMENO,
    HESLO       T_LNAZEV,
    IDL         T_IDENT,
    PRAVA       T_IDENTZ,
    DMR         T_AKTDAT,
    LASTLOGIN   TIMESTAMP
);


CREATE TABLE VYSETRENI (
    IDVYS     T_IDENT,
    VYIDOBJ   T_IDENT,
    VYIDVYS   T_IDENT,
    VYSPEC    T_JMENO,
    VYSETREN  T_DATUM,
    VYHOTOVO  T_LOGICAL,
    VYSLN1    T_KOEF,
    VYSLZ1    T_LNAZEV,
    VYSLK1    T_KOD2,
    VYSLL1    T_LOGICAL,
    VYSLN2    T_KOEF,
    VYSLZ2    T_LNAZEV COLLATE PXW_CSY,
    VYSLK2    T_KOD2,
    VYSLL2    T_LOGICAL,
    VYPOZN    T_LINE,
    TAG       T_IDENTZ,
    VYTEMP    T_KOD5,
    USR       T_IDENTZ,
    DMR       T_AKTDAT NOT NULL,
    VYBODY    T_IDENTZ
);


CREATE TABLE ZEME (
    IDZEME   T_IDENT NOT NULL,
    NAZEV    T_LNAZEV,
    ZKRATKA  T_ZKRATKA
);




/******************************************************************************/
/****                                Views                                 ****/
/******************************************************************************/


/* View: PRIJEM_VYSETRENI */
CREATE VIEW PRIJEM_VYSETRENI(
    IDVYS,
    VYSPEC,
    VYHOTOVO,
    VYPOZN,
    BODY,
    USR,
    IDOBJ,
    OBCISLO,
    IDL,
    NAZEV,
    VYIDVYS,
    VYSETREN,
    OBIDPAC,
    OBPRIJEM,
    OBODESLANO,
    ROK)
AS
select v.IDVYS, v.VYSPEC, v.VYHOTOVO, v.VYPOZN, b.BODY, v.USR, v.VYIDOBJ,
       o.OBCISLO, o.IDL, c.NAZEV, v.VYIDVYS, v.VYSETREN, o.OBIDPAC,
       o.OBPRIJEM, o.OBODESLANO, dmr.ROK
from CISVYS c, CISBOD b, VYSETRENI v
left outer join OBJEDNAVKY o on v.VYIDOBJ=o.IDOBJ
left outer join CISDMR dmr on (dmr.DAT1<=o.OBPRIJEM and dmr.DAT2>=o.OBPRIJEM)
where b.KOD=c.KOD and v.IDVYS>0 and v.VYIDVYS=c.CISVYS
;




/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE CISBOD ADD CONSTRAINT PK_CISBOD PRIMARY KEY (KOD);
ALTER TABLE CISDG ADD CONSTRAINT PK_CISDG PRIMARY KEY (CISDG);
ALTER TABLE CISDMR ADD CONSTRAINT PK_CISDMR PRIMARY KEY (CISDMR);
ALTER TABLE CISKAT ADD CONSTRAINT PK_CISKAT PRIMARY KEY (CISKAT);
ALTER TABLE CISMAT ADD CONSTRAINT PK_CISMAT PRIMARY KEY (CISMAT);
ALTER TABLE CISODB ADD CONSTRAINT PK_CISODB PRIMARY KEY (CISODB);
ALTER TABLE CISPOJ ADD CONSTRAINT PK_CISPOJ PRIMARY KEY (CISPOJ);
ALTER TABLE CISVYS ADD CONSTRAINT PK_CISVYS PRIMARY KEY (CISVYS);
ALTER TABLE KODPOVED ADD CONSTRAINT FK_KODPOVED PRIMARY KEY (SKUPINA, KOD);
ALTER TABLE LABORATORE ADD CONSTRAINT PK_LABORATORE PRIMARY KEY (IDLAB);
ALTER TABLE LEKARI ADD CONSTRAINT PK_LEKARI PRIMARY KEY (IDLEK);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT PK_OBJEDNAVKY PRIMARY KEY (IDOBJ);
ALTER TABLE PACIENTI ADD CONSTRAINT PK_PACIENTI PRIMARY KEY (IDPAC);
ALTER TABLE PARLAB ADD CONSTRAINT PK_PARLAB PRIMARY KEY (DATUMVYTV);
ALTER TABLE PROFILY ADD CONSTRAINT PK_PROFILE PRIMARY KEY (IDPF);
ALTER TABLE UZIVATEL ADD CONSTRAINT PK_UZIVATEL PRIMARY KEY (IDUZIVATEL);
ALTER TABLE VYSETRENI ADD CONSTRAINT PK_VYSETRENI PRIMARY KEY (IDVYS);
ALTER TABLE ZEME ADD CONSTRAINT PK_ZEME PRIMARY KEY (IDZEME);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE CISVYS ADD CONSTRAINT FK_CISVYS2BOD FOREIGN KEY (KOD) REFERENCES CISBOD (KOD);
ALTER TABLE LEKARI ADD CONSTRAINT FK_LEK2ODB FOREIGN KEY (LEODB) REFERENCES CISODB (CISODB);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2DG FOREIGN KEY (OBDG) REFERENCES CISDG (CISDG);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2LAB FOREIGN KEY (IDL) REFERENCES LABORATORE (IDLAB);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2LEK FOREIGN KEY (OBIDLEK) REFERENCES LEKARI (IDLEK);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2MAT FOREIGN KEY (OBIDMAT) REFERENCES CISMAT (CISMAT);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2PAC FOREIGN KEY (OBIDPAC) REFERENCES PACIENTI (IDPAC);
ALTER TABLE OBJEDNAVKY ADD CONSTRAINT FK_OB2USR FOREIGN KEY (USR) REFERENCES UZIVATEL (IDUZIVATEL);
ALTER TABLE PACIENTI ADD CONSTRAINT FK_PAC2LEK FOREIGN KEY (PAIDLEK) REFERENCES LEKARI (IDLEK);
ALTER TABLE PACIENTI ADD CONSTRAINT FK_PAC2POJ FOREIGN KEY (PAZP) REFERENCES CISPOJ (CISPOJ);
ALTER TABLE PROFILY ADD CONSTRAINT FK_PF2USR FOREIGN KEY (PFUSER) REFERENCES UZIVATEL (IDUZIVATEL)
  USING INDEX FK_PROFILY_1;
ALTER TABLE PROFILY ADD CONSTRAINT FK_PF2VYS FOREIGN KEY (PFCISVYS) REFERENCES CISVYS (CISVYS);
ALTER TABLE VYSETRENI ADD CONSTRAINT FK_VYS2OBJ FOREIGN KEY (VYIDOBJ) REFERENCES OBJEDNAVKY (IDOBJ);
ALTER TABLE VYSETRENI ADD CONSTRAINT FK_VYS2USR FOREIGN KEY (USR) REFERENCES UZIVATEL (IDUZIVATEL);
ALTER TABLE VYSETRENI ADD CONSTRAINT FK_VYS2VYS FOREIGN KEY (VYIDVYS) REFERENCES CISVYS (CISVYS);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: LEKARI_AD */
CREATE TRIGGER LEKARI_AD FOR LEKARI
ACTIVE AFTER DELETE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: LEKARI_AI */
CREATE TRIGGER LEKARI_AI FOR LEKARI
ACTIVE AFTER INSERT POSITION 0
as begin execute procedure Nic; end^


/* Trigger: LEKARI_AU */
CREATE TRIGGER LEKARI_AU FOR LEKARI
ACTIVE AFTER UPDATE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: LEKARI_BD */
CREATE TRIGGER LEKARI_BD FOR LEKARI
ACTIVE BEFORE DELETE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: LEKARI_BI */
CREATE TRIGGER LEKARI_BI FOR LEKARI
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.IDLEK is null) then new.IDLEK = gen_id(gen_IDLEK,1);
  if (new.LETITPR is null) then new.LETITPR=' ';
  if (new.LEJMENO is null) then new.LEJMENO=' ';
  if (new.LETITZA is null) then new.LETITZA=' ';
  if (new.LEADR1 is null) then new.LEADR1=' ';
  if (new.LEADR2 is null) then new.LEADR2=' ';
  if (new.lejmeno is null) then new.LEJMENO=' ';
  if (new.DMR is null) then new.DMR='today';
end^


/* Trigger: LEKARI_BU */
CREATE TRIGGER LEKARI_BU FOR LEKARI
ACTIVE BEFORE UPDATE POSITION 0
as begin new.DMR='today'; end^


/* Trigger: OBJEDNAVKY_AD */
CREATE TRIGGER OBJEDNAVKY_AD FOR OBJEDNAVKY
ACTIVE AFTER DELETE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: OBJEDNAVKY_AI */
CREATE TRIGGER OBJEDNAVKY_AI FOR OBJEDNAVKY
ACTIVE AFTER INSERT POSITION 0
as
  declare variable datumpr timestamp;
  declare variable ID integer;
  begin
    select AKTDATOBJ from PARLAB into datumpr;
    if (new.OBPRIJEM<>datumpr) then update PARLAB set AKTDATOBJ=:datumpr;
    ID=gen_id(gen_idobjcislo,(new.OBCISLO-gen_id(gen_idobjcislo,1)));
    if (new.OBODBER>0) then
     begin
     if (new.OBIDMAT=1)
        then insert into VYSETRENI (IDVYS,VYIDOBJ,VYIDVYS,VYSPEC,VYHOTOVO,TAG)
             values (gen_id(gen_IDVYS,1),new.IDOBJ,1,'',1,0);
     if (new.OBIDMAT=2)
        then insert into VYSETRENI (IDVYS,VYIDOBJ,VYIDVYS,VYSPEC,VYHOTOVO,TAG)
             values (gen_id(gen_IDVYS,1),new.IDOBJ,2,'',1,0);
     end
  end^


/* Trigger: OBJEDNAVKY_AU */
CREATE TRIGGER OBJEDNAVKY_AU FOR OBJEDNAVKY
ACTIVE AFTER UPDATE POSITION 0
as
begin
  if (not(new.OBODBER=old.OBODBER and new.OBIDMAT=old.OBIDMAT)) then
    begin
    if (new.OBODBER=1) then
      begin
      if (new.OBIDMAT=1) 
         then insert into VYSETRENI (IDVYS,VYIDOBJ,VYIDVYS,VYSPEC,VYHOTOVO,TAG) 
              values (gen_id(gen_IDVYS,1),new.IDOBJ,1,'',1,0);
      if (new.OBIDMAT=2) 
         then insert into VYSETRENI (IDVYS,VYIDOBJ,VYIDVYS,VYSPEC,VYHOTOVO,TAG) 
              values (gen_id(gen_IDVYS,1),new.IDOBJ,2,'',1,0);
      end
    if (old.OBODBER=1) then
      begin
      if (old.OBIDMAT=1) then delete from VYSETRENI where VYIDOBJ=old.IDOBJ and VYIDVYS=1;
      if (old.OBIDMAT=2) then delete from VYSETRENI where VYIDOBJ=old.IDOBJ and VYIDVYS=2;
      end
    end
end^


/* Trigger: OBJEDNAVKY_BD */
CREATE TRIGGER OBJEDNAVKY_BD FOR OBJEDNAVKY
ACTIVE BEFORE DELETE POSITION 0
as
  begin 
  if (exists(select * from VYSETRENI where VYIDOBJ=old.IDOBJ)) then
    exception E_NODEL_OBJ;
  end^


/* Trigger: OBJEDNAVKY_BI */
CREATE TRIGGER OBJEDNAVKY_BI FOR OBJEDNAVKY
ACTIVE BEFORE INSERT POSITION 0
as 
begin if (new.IDOBJ is null) then new.IDOBJ = gen_id(gen_IDOBJ,1);
      if (new.OBCISLO is null) then new.OBCISLO = gen_id(gen_IDOBJCISLO,1);
      if (new.OBPV is null) then new.OBPV=0;
      if (new.OBPHV is null) then new.OBPHV=0;
      if (new.TAG is null) then new.TAG=0;
      if (new.DMR is null) then new.DMR='today';
end^


/* Trigger: OBJEDNAVKY_BU */
CREATE TRIGGER OBJEDNAVKY_BU FOR OBJEDNAVKY
ACTIVE BEFORE UPDATE POSITION 0
as
begin
  if (new.OBPV<0) then new.OBPV=0;
  if (new.OBPV=new.OBPHV) then new.OBHOTOVO=1; else new.OBHOTOVO=0;     
  new.DMR='today';
end^


/* Trigger: PACIENTI_AD */
CREATE TRIGGER PACIENTI_AD FOR PACIENTI
ACTIVE AFTER DELETE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: PACIENTI_AI */
CREATE TRIGGER PACIENTI_AI FOR PACIENTI
ACTIVE AFTER INSERT POSITION 0
as begin execute procedure Nic; end^


/* Trigger: PACIENTI_AU */
CREATE TRIGGER PACIENTI_AU FOR PACIENTI
ACTIVE AFTER UPDATE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: PACIENTI_BD */
CREATE TRIGGER PACIENTI_BD FOR PACIENTI
ACTIVE BEFORE DELETE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: PACIENTI_BI */
CREATE TRIGGER PACIENTI_BI FOR PACIENTI
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.IDPAC is null) then new.IDPAC = gen_id(gen_IDPAC,1);
  if (new.DMR is null) then new.DMR='today';
end^


/* Trigger: PACIENTI_BU */
CREATE TRIGGER PACIENTI_BU FOR PACIENTI
ACTIVE BEFORE UPDATE POSITION 0
as begin new.DMR='today'; end^


/* Trigger: PARLAB_BD */
CREATE TRIGGER PARLAB_BD FOR PARLAB
ACTIVE BEFORE DELETE POSITION 0
as begin exception E_NODEL; end^


/* Trigger: PARLAB_BI */
CREATE TRIGGER PARLAB_BI FOR PARLAB
ACTIVE BEFORE INSERT POSITION 0
as begin exception E_NOINS; end^


/* Trigger: VYSETRENI_AD */
CREATE TRIGGER VYSETRENI_AD FOR VYSETRENI
ACTIVE AFTER DELETE POSITION 0
as
  begin 
  update OBJEDNAVKY set OBPV=OBPV-1, OBPHV=OBPHV-old.VYHOTOVO  where IDOBJ=old.VYIDOBJ; 
  end^


/* Trigger: VYSETRENI_AI */
CREATE TRIGGER VYSETRENI_AI FOR VYSETRENI
ACTIVE AFTER INSERT POSITION 0
as
  begin 
  update OBJEDNAVKY set OBPV=OBPV+1, OBPHV=OBPHV+new.VYHOTOVO where IDOBJ=new.VYIDOBJ; 
  end^


/* Trigger: VYSETRENI_AU */
CREATE TRIGGER VYSETRENI_AU FOR VYSETRENI
ACTIVE AFTER UPDATE POSITION 0
as
  begin 
  if (new.VYHOTOVO<>old.VYHOTOVO) 
    then update OBJEDNAVKY set OBPHV=OBPHV+2*new.VYHOTOVO-1 where IDOBJ=new.VYIDOBJ; 
  end^


/* Trigger: VYSETRENI_BD */
CREATE TRIGGER VYSETRENI_BD FOR VYSETRENI
ACTIVE BEFORE DELETE POSITION 0
as begin execute procedure Nic; end^


/* Trigger: VYSETRENI_BI */
CREATE TRIGGER VYSETRENI_BI FOR VYSETRENI
ACTIVE BEFORE INSERT POSITION 1
as
  begin 
  select OBODESLANO from OBJEDNAVKY where IDOBJ=new.VYIDOBJ into new.VYSETREN;
  if (new.IDVYS is null) then new.IDVYS = gen_id(gen_IDVYS,1);
  if (new.VYHOTOVO is null) then new.VYHOTOVO=0;     
  if (new.TAG is null) then new.TAG=0;     
  if (new.DMR is null) then new.DMR='today';
  end^


/* Trigger: VYSETRENI_BU */
CREATE TRIGGER VYSETRENI_BU FOR VYSETRENI
ACTIVE BEFORE UPDATE POSITION 0
as
  begin 
/*  if (not(new.VYSLN1=old.VYSLN1 and new.VYSLZ1=old.VYSLZ1 and new.VYSLK1=old.VYSLK1
      and new.VYSLL1=old.VYSLL1 and new.VYSLN2=old.VYSLN2 and new.VYSLK2=old.VYSLK2
      and new.VYSLL2=old.VYSLL2 and new.VYSLZ2=old.VYSLZ2)) then begin
    if (new.VYSLN1=0 and new.VYSLZ1 is null and new.VYSLK1='@' and new.VYSLL1=0
        and new.VYSLN2=0 and new.VYSLK2='@' and new.VYSLL2=0 and new.VYSLZ2 is null)
      then new.VYHOTOVO=0; else new.VYHOTOVO=1; end
  if (new.VYHOTOVO>1) then new.VYHOTOVO=1;
  if (new.VYHOTOVO<>old.VYHOTOVO) then begin
     if (new.VYHOTOVO=0 and new.VYSETREN is not null) then new.VYSETREN=null;     
     if (new.VYHOTOVO=1 and new.VYSETREN is null) then new.VYSETREN='today'; end    
*/
  new.DMR='today';
  end^


SET TERM ; ^



/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE DELETE_DOWN (
    PROFIL INTEGER,
    NODE INTEGER)
AS
  declare variable node2 integer;    declare variable haschild integer;
  begin 
    delete from PROFILY where PFUSER=:profil and PFCISVYS=:node;
    select CHILD from CISVYS where CISVYS=:node into :haschild;
    if (haschild>0) then
       for select CISVYS from CISVYS where PARENT=:node into :node2 do   
           execute procedure DELETE_DOWN(profil,node2);    
end
^

ALTER PROCEDURE DELETE_NODE (
    PROFIL INTEGER,
    NODE INTEGER)
AS
  begin 
    execute procedure DELETE_DOWN(profil,node);
    execute procedure DELETE_UP(profil,node);
end
^

ALTER PROCEDURE DELETE_UP (
    PROFIL INTEGER,
    NODE INTEGER)
AS
  declare variable node2 integer;
  begin 
    delete from PROFILY where PFUSER=:profil and PFCISVYS=:node;
    select PARENT from CISVYS where CISVYS=:node into :node2;
    if ((node2>0) and not(exists (select * from PROFILY where PFUSER=:profil and 
        PFCISVYS in (select CISVYS from CISVYS where PARENT=:node2)))) 
          then execute procedure DELETE_UP(profil,node2);    
end
^

ALTER PROCEDURE GEN_CVY
RETURNS (
    ID INTEGER)
AS
 begin ID=gen_id(gen_CISVY,1);end
^

ALTER PROCEDURE GEN_LEK
RETURNS (
    ID INTEGER)
AS
 begin ID=gen_id(gen_IDLEK,1);end
^

ALTER PROCEDURE GEN_OBJ
RETURNS (
    ID INTEGER)
AS
 begin ID=gen_id(gen_IDOBJ,1);end
^

ALTER PROCEDURE GEN_OBJCISLO
RETURNS (
    ID INTEGER)
AS
begin ID=gen_id(gen_IDOBJCISLO,1);end
^

ALTER PROCEDURE GEN_PAC
RETURNS (
    ID INTEGER)
AS
 begin ID=gen_id(gen_IDPAC,1);end
^

ALTER PROCEDURE GEN_VYS
RETURNS (
    ID INTEGER)
AS
 begin ID=gen_id(gen_IDVYS,1);end
^

ALTER PROCEDURE GRANT_ALL (
    NAZEV VARCHAR(50))
RETURNS (
    N INTEGER,
    TEXT VARCHAR(1024))
AS
   declare variable jmeno varchar(50);     
   begin
     n=0; text=Strim(nazev)||'.sql'; suspend; 
     for select RDB$RELATION_NAME from RDB$USER_PRIVILEGES 
          where RDB$OBJECT_TYPE=0 and RDB$USER='SYSDBA' 
          group by RDB$RELATION_NAME into :jmeno 
       do begin
          n=n+1;  
          text='grant ALL on '||Strim(jmeno)||' to PUBLIC;';
     suspend;
     end
     for select RDB$RELATION_NAME from RDB$USER_PRIVILEGES 
          where RDB$OBJECT_TYPE=5 and RDB$USER='SYSDBA' 
          group by RDB$RELATION_NAME into :jmeno 
       do begin
          n=n+1;  
          text='grant EXECUTE on procedure '||Strim(jmeno)||' to PUBLIC;';
     suspend;
     end
  end
^

ALTER PROCEDURE CHECKDG (
    DG CHAR(4))
RETURNS (
    OK INTEGER)
AS
begin 
    select COUNT(CISDG) from CISDG where CISDG=:Dg into :OK;    
end
^

ALTER PROCEDURE CHECKLEKID (
    ICZ CHAR(11))
RETURNS (
    OK INTEGER,
    ID INTEGER)
AS
begin 
    ID=0;
    select COUNT(IDLEK), MIN(IDLEK) from LEKARI where IDLEK=:ICZ into :OK,:ID;    
end
^

ALTER PROCEDURE CHECKPACID (
    RC CHAR(11))
RETURNS (
    OK INTEGER,
    ID INTEGER)
AS
begin 
    ID=0;
    select COUNT(IDPAC), MIN(IDPAC) from PACIENTI where PARC=:RC into :OK,:ID;    
end
^

ALTER PROCEDURE INSERT_PRIJEM (
    NODE INTEGER,
    OBJ INTEGER,
    SPEC VARCHAR(30))
AS
  declare variable children integer;   declare variable specvys varchar(30);
  declare variable used integer;       declare variable node2 integer; 
  declare variable kod char(5); 
begin 
    select CHILD,NAZEV,USED from CISVYS where CISVYS=:node into :children,:specvys,:used;
    specvys=specvys||spec;
    if (children=0 and used>0) then 
       insert into VYSETRENI (IDVYS,VYIDOBJ,VYIDVYS,VYSPEC,VYHOTOVO,TAG) 
           values (gen_id(gen_IDVYS,1),:obj,:node,:specvys,0,0);
    if (children=1 and used>0) then 
       for select CISVYS from CISVYS where PARENT=:node into :node2 do   
          execute procedure INSERT_PRIJEM(node2,obj,specvys);  
    if (used>0) then begin
       select KOD from CISVYS where CISVYS=:used into :kod;
       if (kod like '@%' and not exists (select IDVYS from VYSETRENI where VYIDOBJ=:obj and VYSPEC=:kod)) 
         then insert into VYSETRENI (IDVYS,VYIDOBJ,VYIDVYS,VYSPEC,VYHOTOVO,TAG) 
                             values (gen_id(gen_IDVYS,1),:obj,:used,:kod,1,0);
       end
end
^

ALTER PROCEDURE NIC
AS
 begin exit; end
^

ALTER PROCEDURE ROZTEST
AS
 begin exit; end
^

ALTER PROCEDURE SAVE_DOWN (
    PROFIL INTEGER,
    NODE INTEGER)
AS
  declare variable node2 integer;
  begin 
    if (not(exists (select * from PROFILY where PFUSER=:profil and PFCISVYS=:node))) 
     then insert into PROFILY values (gen_id(gen_IDPF,1),:profil,:node,1);
    for select CISVYS from CISVYS where PARENT=:node into :node2 do   
       execute procedure SAVE_DOWN(profil,node2);    
end
^

ALTER PROCEDURE SAVE_NODE (
    PROFIL INTEGER,
    NODE INTEGER)
AS
  begin 
    execute procedure SAVE_DOWN(profil,node);
    execute procedure SAVE_UP(profil,node);
end
^

ALTER PROCEDURE SAVE_UP (
    PROFIL INTEGER,
    NODE INTEGER)
AS
  declare variable node2 integer;
  begin 
    if (not(exists (select * from PROFILY where PFUSER=:profil and PFCISVYS=:node))) 
     then insert into PROFILY values (gen_id(gen_IDPF,1),:profil,:node,1);
    select PARENT from CISVYS where CISVYS=:node into :node2;
    if (node2>0) then execute procedure SAVE_UP(profil,node2);    
end
^

ALTER PROCEDURE SHOWGEN_OBJ
RETURNS (
    CISLO INTEGER)
AS
begin 
   cislo=gen_id(gen_IDOBJCISLO,0)+1;
end
^

ALTER PROCEDURE VYSLEDEK (
    OBJ INTEGER)
RETURNS (
    IDVYS INTEGER,
    COBJ INTEGER,
    KODVYS CHAR(5),
    NAZEV VARCHAR(80),
    VYSLTEXT VARCHAR(240),
    NORMA VARCHAR(50),
    ODESLANO DATE,
    DATVYS DATE,
    TEXT1 VARCHAR(240),
    TEXT2 VARCHAR(240),
    TEXT3 VARCHAR(240),
    POZNAMKA VARCHAR(1024),
    LEKAR CHAR(10))
AS
DECLARE VARIABLE TVOUT INTEGER;
DECLARE VARIABLE IDOBJ INTEGER;
DECLARE VARIABLE HOTOVO INTEGER;
DECLARE VARIABLE SPECIFIKACE VARCHAR(30);
DECLARE VARIABLE JEDN1 VARCHAR(30);
DECLARE VARIABLE JEDN2 VARCHAR(30);
DECLARE VARIABLE VYSLN1 NUMERIC(15,4);
DECLARE VARIABLE VYSLN2 NUMERIC(15,4);
DECLARE VARIABLE VYSLK1 CHAR(2);
DECLARE VARIABLE VYSLK2 CHAR(2);
DECLARE VARIABLE VYSLZ1 VARCHAR(50);
DECLARE VARIABLE VYSLL1 INTEGER;
DECLARE VARIABLE VYSLL2 INTEGER;
DECLARE VARIABLE KODZ1 VARCHAR(30);
DECLARE VARIABLE KODZ2 VARCHAR(30);
DECLARE VARIABLE SKUPZ1 INTEGER;
DECLARE VARIABLE SKUPZ2 INTEGER;
DECLARE VARIABLE PODMEZ1 VARCHAR(50);
DECLARE VARIABLE PODMEZ2 VARCHAR(50);
DECLARE VARIABLE DMEZ1 NUMERIC(15,2);
DECLARE VARIABLE DMEZ2 NUMERIC(15,2);
DECLARE VARIABLE VMEZI1 VARCHAR(50);
DECLARE VARIABLE VMEZI2 VARCHAR(50);
DECLARE VARIABLE HORMEZ1 NUMERIC(15,2);
DECLARE VARIABLE HORMEZ2 NUMERIC(15,2);
DECLARE VARIABLE NADMEZ1 VARCHAR(50);
DECLARE VARIABLE NADMEZ2 VARCHAR(50);
begin 
  select IDOBJ,OBPRUVODKA,LECISLO from OBJEDNAVKY,LEKARI where OBCISLO=:obj 
         and IDLEK=OBIDLEK into :idobj,:odeslano,:lekar; 
  cobj=obj;
  for select v.IDVYS,v.VYSPEC,v.VYSETREN,v.VYHOTOVO,v.VYSLN1,v.VYSLZ1,v.VYSLK1,v.VYSLL1,
      v.VYSLN2,v.VYSLK2,v.VYSLL2,v.VYPOZN,c.KOD,c.NAZEV,c.JEDN1,c.JEDN2, 
      c.NORMA, c.TVOUT, c.KODZ1, c.SKUPZ1, c.KODZ2, c.SKUPZ2, c.DOLMEZ1, 
      c.PODMEZI1, c.VMEZICH1, c.NADMEZI1, c.HORMEZ1, c.DOLMEZ2, c.PODMEZI2, 
      c.VMEZICH2, c.NADMEZI2, c.HORMEZ2, c.TEXT1, c.TEXT2, c.TEXT3
      from VYSETRENI v, CISVYS c where VYIDOBJ=:idobj and v.VYIDVYS=c.CISVYS
      into :idvys,:specifikace,:datvys,:hotovo,:vysln1,:vyslz1,:vyslk1,:vysll1,:vysln2,
      :vyslk2,:vysll2,:poznamka,:kodvys,:nazev,:jedn1,:jedn2,:norma,:tvout,:kodz1,
      :skupz1,:kodz2,:skupz2,:dmez1,:podmez1,:vmezi1,:nadmez1,:hormez1,:dmez2,
      :podmez2,:vmezi2,:nadmez2,:hormez2,:text1,:text2,:text3 do 
   begin /* nastavení položky text */
   if (specifikace>' ') then nazev=nazev||' ('||strim(specifikace)||')';
   if (hotovo=0) then vysltext='dosud nehotovo';
   if (tvout=1) then
    begin
    vysltext=RJust(vysln1,18)||' '||jedn1;
    end
   if (tvout=2) then      
    begin
    vysltext=RJust(vysln1,18)||' '||jedn1;
    end 
   if (tvout=3) then 
    begin
    vysltext=RJust(cast(vysln1 as char(8)),8)||' '||jedn1;
    end 
   if (tvout=4) then 
    begin vysltext='nelze rozhodnout';
    if (vysll1<dmez1) then vysltext=podmez1;
    if (vysll1>=dmez1 and vysll1<=hormez1) then vysltext=vmezi1;
    if (vysll1>hormez1) then vysltext=nadmez1;
    end 
   if (tvout=5) then 
    begin vysltext='nelze rozhodnout';
    if (vysln1<dmez1) then vysltext=podmez1;
    if (vysln1>=dmez1 and vysln1<=hormez1) then vysltext=vmezi1;
    if (vysln1>hormez1) then vysltext=nadmez1;
    end 
  suspend;
  end
end
^

ALTER PROCEDURE VYSLEDEKTEXT (
    IDVYS INTEGER)
RETURNS (
    NAZEV VARCHAR(80),
    VYSLTEXT VARCHAR(240))
AS
DECLARE VARIABLE TVOUT INTEGER;
DECLARE VARIABLE HOTOVO INTEGER;
DECLARE VARIABLE SPECIFIKACE VARCHAR(30);
DECLARE VARIABLE JEDN1 VARCHAR(30);
DECLARE VARIABLE JEDN2 VARCHAR(30);
DECLARE VARIABLE VYSLN1 FLOAT;
DECLARE VARIABLE VYSLN2 FLOAT;
DECLARE VARIABLE VYSLK1 CHAR(2);
DECLARE VARIABLE VYSLK2 CHAR(2);
DECLARE VARIABLE VYSLZ1 VARCHAR(50);
DECLARE VARIABLE VYSLL1 INTEGER;
DECLARE VARIABLE VYSLL2 INTEGER;
DECLARE VARIABLE KODZ1 VARCHAR(30);
DECLARE VARIABLE KODZ2 VARCHAR(30);
DECLARE VARIABLE SKUPZ1 INTEGER;
DECLARE VARIABLE SKUPZ2 INTEGER;
DECLARE VARIABLE PODMEZ1 VARCHAR(50);
DECLARE VARIABLE PODMEZ2 VARCHAR(50);
DECLARE VARIABLE DMEZ1 FLOAT;
DECLARE VARIABLE DMEZ2 FLOAT;
DECLARE VARIABLE VMEZI1 VARCHAR(50);
DECLARE VARIABLE VMEZI2 VARCHAR(50);
DECLARE VARIABLE HORMEZ1 FLOAT;
DECLARE VARIABLE HORMEZ2 FLOAT;
DECLARE VARIABLE NADMEZ1 VARCHAR(50);
DECLARE VARIABLE NADMEZ2 VARCHAR(50);
begin
  select v.VYSPEC,v.VYHOTOVO,v.VYSLN1,v.VYSLZ1,v.VYSLK1,v.VYSLL1, 
      v.VYSLN2,v.VYSLK2,v.VYSLL2,c.ZKRATKA,c.JEDN1,c.JEDN2,
      c.TVOUT, c.KODZ1, c.SKUPZ1, c.KODZ2, c.SKUPZ2, c.DOLMEZ1, 
      c.PODMEZI1, c.VMEZICH1, c.NADMEZI1, c.HORMEZ1, c.DOLMEZ2, c.PODMEZI2, 
      c.VMEZICH2, c.NADMEZI2, c.HORMEZ2
      from VYSETRENI v, CISVYS c where IDVYS=:idvys and v.VYIDVYS=c.CISVYS
      into :specifikace,:hotovo,:vysln1,:vyslz1,:vyslk1,:vysll1,:vysln2, 
      :vyslk2,:vysll2,:nazev,:jedn1,:jedn2,:tvout,:kodz1,:skupz1,:kodz2,
      :skupz2,:dmez1,:podmez1,:vmezi1,:nadmez1,:hormez1,:dmez2,
      :podmez2,:vmezi2,:nadmez2,:hormez2;
   if (specifikace<>'') then nazev=nazev||' ('||strim(specifikace)||')';
   if (hotovo=0) then vysltext='dosud nehotovo';
   if (tvout=1) then
    begin
    vysltext=RJust(vysln1,18)||' '||jedn1;
    end
   if (tvout=2) then      
    begin
    vysltext=RJust(vysln1,18)||' '||jedn1;
    end 
   if (tvout=3) then 
    begin
    vysltext=RJust(cast(vysln1 as char(8)),8)||' '||jedn1;
    end 
   if (tvout=4) then 
    begin vysltext='nelze rozhodnout';
    if (vysll1<dmez1) then vysltext=podmez1;
    if (vysll1>=dmez1 and vysll1<=hormez1) then vysltext=vmezi1;
    if (vysll1>hormez1) then vysltext=nadmez1;
    end 
   if (tvout=5) then 
    begin vysltext='nelze rozhodnout';
    if (vysln1<dmez1) then vysltext=podmez1;
    if (vysln1>=dmez1 and vysln1<=hormez1) then vysltext=vmezi1;
    if (vysln1>hormez1) then vysltext=nadmez1;
    end 
  suspend;
end
^

ALTER PROCEDURE VYSLEDKY (
    IDPAC INTEGER)
RETURNS (
    IDVYS INTEGER,
    COBJ INTEGER,
    KODVYS CHAR(5),
    NAZEV VARCHAR(80),
    VYSLTEXT VARCHAR(240),
    NORMA VARCHAR(50),
    ODESLANO DATE,
    DATVYS DATE,
    TEXT1 VARCHAR(240),
    TEXT2 VARCHAR(240),
    TEXT3 VARCHAR(240),
    POZNAMKA VARCHAR(1024),
    LEKAR CHAR(10))
AS
DECLARE VARIABLE OBJ INTEGER;
begin
  for select OBCISLO from OBJEDNAVKY where OBIDPAC=:IDpac into :obj do
  begin
    for select * from VYSLEDEK(:obj) into :idvys,:cobj,:kodvys,:nazev,:vysltext,:norma,
      :odeslano,:datvys,:text1,:text2,:text3,:poznamka,:lekar do
  suspend;
  end
end
^


SET TERM ; ^


/******************************************************************************/
/****                             Descriptions                             ****/
/******************************************************************************/

