CREATE GENERATOR GEN_CASERECORD;
CREATE GENERATOR GEN_NOM_CASERECORD;

CREATE TABLE CASERECORD (
    XCASERECORD     LONGINT NOT NULL /* LONGINT = INTEGER */,
    XBRANCH         DX /* DX = INTEGER NOT NULL */,
    XNURSE          DINTLONG /* DINTLONG = INTEGER */,
    XPATIENT        DX /* DX = INTEGER NOT NULL */,
    XCITY           DX /* DX = INTEGER NOT NULL */,
    XSOCIUM         DX /* DX = INTEGER NOT NULL */,
    XLPU            DINTLONG /* DINTLONG = INTEGER */,
    XASSUREROMS     DINTLONG /* DINTLONG = INTEGER */,
    XASSURERDMS     DINTLONG /* DINTLONG = INTEGER */,
    XCURATOR        DINTLONG /* DINTLONG = INTEGER */,
    XSURGEON        DINTLONG /* DINTLONG = INTEGER */,
    XTRANSPORT      DINTSHORT DEFAULT 1 /* DINTSHORT = SMALLINT */,
    XDIET           DINTLONG /* DINTLONG = INTEGER */,
    XCAUSE          SMALLINT DEFAULT 1,
    XTIMEIN         DINTSHORT /* DINTSHORT = SMALLINT */,
    XMANNAME        DINTLONG /* DINTLONG = INTEGER */,
    XMANPATRO       DINTLONG /* DINTLONG = INTEGER */,
    XFINANCE        DX /* DX = INTEGER NOT NULL */,
    XOMS_RESULT     DX DEFAULT 1 /* DX = INTEGER NOT NULL */,
    XDISCHARGE      DINTSHORT DEFAULT 1 /* DINTSHORT = SMALLINT */,
    XLPUOMS         DINTLONG /* DINTLONG = INTEGER */,
    FAMILY          STRING20 NOT NULL /* STRING20 = VARCHAR(20) */,
    SEX             BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */,
    POSTINDEX       STRING6 /* STRING6 = VARCHAR(6) */,
    STREET          STRING30 /* STRING30 = VARCHAR(30) */,
    HOUSE           STRING6 /* STRING6 = VARCHAR(6) */,
    FLAT            STRING4 /* STRING4 = VARCHAR(4) */,
    WORKING         STRING150 /* STRING150 = VARCHAR(150) */,
    AGE             STRING6 /* STRING6 = VARCHAR(6) */,
    NOM             DX /* DX = INTEGER NOT NULL */,
    TS              TIMESTAMP,
    STATUS          DSHORTINT /* DSHORTINT = SMALLINT DEFAULT 0 NOT NULL */,
    ANAMNEZCL       STRING4 /* STRING4 = VARCHAR(4) */,
    AD              STRING7 /* STRING7 = VARCHAR(7) */,
    WEIGHT          DMASS /* DMASS = NUMERIC(10,3) DEFAULT 0 */,
    TEMPERATURE     DMASS /* DMASS = NUMERIC(10,3) DEFAULT 0 */,
    CASUAL          BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */,
    DATEOMSBEGIN    DDATE /* DDATE = DATE DEFAULT 'NOW' */,
    DATEOMS         DDATE /* DDATE = DATE DEFAULT 'NOW' */,
    DATEDMS         DDATE /* DDATE = DATE DEFAULT 'NOW' */,
    CONTRACTOMS     STRING25 /* STRING25 = VARCHAR(25) */,
    CONTRACTDMS     STRING25 /* STRING25 = VARCHAR(25) */,
    POLISOMSSER     STRING10 /* STRING10 = VARCHAR(10) */,
    POLISOMS        STRING20 /* STRING20 = VARCHAR(20) */,
    POLISDMS        STRING20 /* STRING20 = VARCHAR(20) */,
    WARRANTOMS      STRING10 /* STRING10 = VARCHAR(10) */,
    WARRANTDMS      STRING25 /* STRING25 = VARCHAR(25) */,
    ARRCAUSE        SMALLINT [1:12],
    ARRINVALID      SMALLINT [1:10],
    ARRPOLL         SMALLINT [1:12],
    DIAGNOSIS0      STRING255 /* STRING255 = VARCHAR(255) */,
    DIAGNOSIS1      STRING255 /* STRING255 = VARCHAR(255) */,
    DATEBORN        DATE,
    KLADR           STRING13 /* STRING13 = VARCHAR(13) */,
    TSOUT           TIMESTAMP,
    HEIGHT          DINTSHORT /* DINTSHORT = SMALLINT */,
    BOX             STRING4 /* STRING4 = VARCHAR(4) */,
    KEYGRACE        STRING4 /* STRING4 = VARCHAR(4) */,
    KEYSOCIUM       STRING4 /* STRING4 = VARCHAR(4) */,
    HEPATITYEAR     DINTSHORT /* DINTSHORT = SMALLINT */,
    PRIM            DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    BAD             BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */,
    MOAN            DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    ANAMNES         DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    ANAMNESLIFE     DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    STINGOD         STRING12 /* STRING12 = VARCHAR(12) */,
    STINGOS         STRING12 /* STRING12 = VARCHAR(12) */,
    VGDOD           STRING12 /* STRING12 = VARCHAR(12) */,
    VGDOS           STRING12 /* STRING12 = VARCHAR(12) */,
    MADE            DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    DIAGNOSISOUT    DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    STINGODOUT      STRING12 /* STRING12 = VARCHAR(12) */,
    STINGOSOUT      STRING12 /* STRING12 = VARCHAR(12) */,
    PLUG            DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    CLS             BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */,
    CODEMKB         STRING9 NOT NULL /* STRING9 = VARCHAR(9) */,
    EYE             STRING3 /* STRING3 = VARCHAR(3) */,
    RESULTOD        DSHORTINT /* DSHORTINT = SMALLINT DEFAULT 0 NOT NULL */,
    RESULTOS        DSHORTINT /* DSHORTINT = SMALLINT DEFAULT 0 NOT NULL */,
    DAYNOW          COMPUTED BY (cast('NOW' as DATE)-cast(TS as DATE)),
    CO1             DINTSHORT /* DINTSHORT = SMALLINT */,
    USR             STRING6 DEFAULT current_user /* STRING6 = VARCHAR(6) */,
    SMT             STRING5 /* STRING5 = VARCHAR(5) */,
    DAYS            DSHORTINT /* DSHORTINT = SMALLINT DEFAULT 0 NOT NULL */,
    OMS_DATE        DATE,
    DATEOUTBRANCH   TIMESTAMP,
    SICKLIST        DINTLONG /* DINTLONG = INTEGER */
);




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

ALTER TABLE CASERECORD ADD CONSTRAINT PK_CASERECORD PRIMARY KEY (XCASERECORD);


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

ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_BRANCH FOREIGN KEY (XBRANCH) REFERENCES SHOP (XSHOP);
ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_CITY FOREIGN KEY (XCITY) REFERENCES CITY (XCITY);
ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_CURATOR FOREIGN KEY (XCURATOR) REFERENCES MAN (XMAN);
ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_FINANCE FOREIGN KEY (XFINANCE) REFERENCES FINANCE (XFINANCE);
ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_OMSRESULT FOREIGN KEY (XOMS_RESULT) REFERENCES OMS_RESULT (XOMS_RESULT);
ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_PATIENT FOREIGN KEY (XPATIENT) REFERENCES PATIENT (XPATIENT);
ALTER TABLE CASERECORD ADD CONSTRAINT FK_CASERECORD_SURGEON FOREIGN KEY (XSURGEON) REFERENCES MAN (XMAN);


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


SET TERM ^ ;



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



/* Trigger: CASERECORD_BIUD */
CREATE OR ALTER TRIGGER CASERECORD_BIUD FOR CASERECORD
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
as
declare variable i integer;
declare variable supervisor integer;
begin
 if (deleting) then
  begin
    delete from oms_err e where e.xcaserecord=new.xcaserecord;
    delete from oms_summ o where o.xcaserecord=new.xcaserecord;
    delete from intensive i where i.xcaserecord=new.xcaserecord;
    delete from diagnosis d where d.xcaserecord=new.xcaserecord;
    delete from revisionoft d where d.xcaserecord=new.xcaserecord;
    delete from revisioninf d where d.xcaserecord=new.xcaserecord;
  end
  else begin
         if (trim(new.family)='' or (new.family is null)) then exception e_exception_1('Не указана фамилия пациента');
       end

 if (inserting) then
  begin
     new.ts = 'NOW';
     if ((new.dateborn is null) or (new.dateborn>extractdate(new.ts)) or ((extractyear(new.dateborn)<(extractyear(new.ts))-120))) then
       exception e_exception_1('Дата рождения пациента введена с ошибкой!');

     if (new.XCaseRecord is null) then  new.XCaseRecord = gen_id(gen_caserecord,1);
     if (new.nom is null) then new.nom = gen_id(gen_nom_caserecord,1);
                          else if (new.nom<>GEN_ID(gen_nom_caserecord,0)) then
                                             i=GEN_ID(gen_nom_caserecord,new.nom-GEN_ID(gen_nom_caserecord,0));
     if (new.XDiet is null) then  new.XDiet = 8;  -- 15 диета
     if (new.XFinance is null) then  new.XFinance = 2;  -- OMS
      /*new.city = 'Кемерово';  */
     if (new.cls is null) then new.cls = 0;
     if (new.codemkb is null) then new.codemkb='';
  end

 if (updating) then
  begin
    select m.supervisor from man m where m.nameib=current_user into :supervisor;
    if (:supervisor=0) then
      begin
        if (old.cls=1) then exception e_exception_1('Изменение архивных данных запрещено!');
        if (new.cls=1) then exception e_exception_1('Вы не можете отправить карту в архив!');
        --  extractdate(null) даёт '17.11.1858'
        if (extractdate(new.tsout)<>extractdate(old.tsout))
          then begin
                if ((new.tsout is not null) and (extractdate(new.tsout)<>extractdate('NOW'))) then exception e_exception_1('Пациента можно выписать только текущей датой!');
               end
      end
     else begin
            if (new.cls=1 and new.DateOutBranch is null) then new.DateOutBranch='NOW';
          end

     if (new.dateborn<>old.dateborn) then begin
                                             update patient p set p.dateborn=new.dateborn where p.xpatient=new.xpatient;
                                          end
     if (new.tsout is null)
      then new.days=0;   -- Сколько пролежал, когда лечился
      else begin
            new.days=extractdate(new.tsout)-extractdate(new.ts);
             if (new.days<0) then exception e_exception_1('Пациента нельзя выписать раньше поступления!');
           end
      if (new.codemkb<>old.codemkb) then
       begin
         if (new.codemkb='') then delete from diagnosis d where d.xcaserecord=old.xcaserecord and d.codemkb=old.codemkb;
                             else begin
                                    delete from diagnosis d where d.xcaserecord=old.xcaserecord and d.codemkb=old.codemkb;
                                    insert into diagnosis (xcaserecord, codemkb, xtip, text) values(new.xcaserecord, new.codemkb, 1, '');
                                  end
       end
  end
end
^


SET TERM ; ^



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

COMMENT ON TABLE CASERECORD IS 
'История болезни';



/******************************************************************************/
/***                          Fields descriptions                           ***/
/******************************************************************************/

COMMENT ON COLUMN CASERECORD.XCASERECORD IS 
'ключ таблицы';

COMMENT ON COLUMN CASERECORD.XBRANCH IS 
'Первичное отделение';

COMMENT ON COLUMN CASERECORD.XNURSE IS 
'Сиделка, мамочка ребёнка';

COMMENT ON COLUMN CASERECORD.XCITY IS 
'код города';

COMMENT ON COLUMN CASERECORD.XSOCIUM IS 
'код соцального статуса';

COMMENT ON COLUMN CASERECORD.XLPU IS 
'код ЛПУ, прикреплённого к пациенту';

COMMENT ON COLUMN CASERECORD.XASSUREROMS IS 
'Код страховой компании ОМС';

COMMENT ON COLUMN CASERECORD.XASSURERDMS IS 
'Код страховой компании ДМС';

COMMENT ON COLUMN CASERECORD.XCURATOR IS 
'код куратора';

COMMENT ON COLUMN CASERECORD.XSURGEON IS 
'хирург';

COMMENT ON COLUMN CASERECORD.XTRANSPORT IS 
'Вид транспортировки
1 - может идти
2 - в кресле
3 - на каталке';

COMMENT ON COLUMN CASERECORD.XDIET IS 
'код диеты';

COMMENT ON COLUMN CASERECORD.XCAUSE IS 
'Повод госпитализации:
1-Заболевание первично
2-Заболевание повторно
3-Травма производств.
4-Травма непроизводств.';

COMMENT ON COLUMN CASERECORD.XTIMEIN IS 
'1-доставлен в первые 6 час
2 - от 7-24часов
3 - свыше 24 часов';

COMMENT ON COLUMN CASERECORD.XMANNAME IS 
'код имени';

COMMENT ON COLUMN CASERECORD.XMANPATRO IS 
'код отчества';

COMMENT ON COLUMN CASERECORD.XDISCHARGE IS
'как выписан больной';

COMMENT ON COLUMN CASERECORD.FAMILY IS 
'Фамилия';

COMMENT ON COLUMN CASERECORD.SEX IS 
'0 - женщина 1 - мужчина';

COMMENT ON COLUMN CASERECORD.POSTINDEX IS 
'почтовый индекс';

COMMENT ON COLUMN CASERECORD.STREET IS 
'улица';

COMMENT ON COLUMN CASERECORD.HOUSE IS 
'дом';

COMMENT ON COLUMN CASERECORD.FLAT IS 
'квартира';

COMMENT ON COLUMN CASERECORD.WORKING IS 
'место работы';

COMMENT ON COLUMN CASERECORD.AGE IS 
'возраст';

COMMENT ON COLUMN CASERECORD.NOM IS 
'Номер истории болезни';

COMMENT ON COLUMN CASERECORD.TS IS 
'Время поступления';

COMMENT ON COLUMN CASERECORD.STATUS IS 
'статус';

COMMENT ON COLUMN CASERECORD.AD IS 
'Артериальное давление';

COMMENT ON COLUMN CASERECORD.WEIGHT IS 
'вес при поступлении';

COMMENT ON COLUMN CASERECORD.TEMPERATURE IS 
'температура';

COMMENT ON COLUMN CASERECORD.CASUAL IS 
'Внеплановая, экстренная госпитализация';

COMMENT ON COLUMN CASERECORD.DATEOMSBEGIN IS 
'Дата начала полиса ОМС';

COMMENT ON COLUMN CASERECORD.DATEOMS IS 
'Дата завершения ОМС';

COMMENT ON COLUMN CASERECORD.DATEDMS IS 
'Дата завершения ДМС';

COMMENT ON COLUMN CASERECORD.CONTRACTOMS IS 
'Номер договора ОМС';

COMMENT ON COLUMN CASERECORD.CONTRACTDMS IS 
'Номер договора ДМС';

COMMENT ON COLUMN CASERECORD.POLISOMSSER IS 
'Серия полиса ОМС';

COMMENT ON COLUMN CASERECORD.POLISOMS IS 
'Номер полиса ОМС';

COMMENT ON COLUMN CASERECORD.POLISDMS IS 
'Номер полиса ДМС';

COMMENT ON COLUMN CASERECORD.WARRANTOMS IS 
'Гарантийный талон ОМС';

COMMENT ON COLUMN CASERECORD.WARRANTDMS IS 
'Номер гарантийного талона';

COMMENT ON COLUMN CASERECORD.ARRCAUSE IS 
'Массив повод для госпитализации';

COMMENT ON COLUMN CASERECORD.ARRINVALID IS 
'Массив инвалидности';

COMMENT ON COLUMN CASERECORD.ARRPOLL IS 
'Массив опроса, осмотра';

COMMENT ON COLUMN CASERECORD.DIAGNOSIS0 IS 
'Диагноз направившего учреждения';

COMMENT ON COLUMN CASERECORD.DIAGNOSIS1 IS 
'Диагноз при поступлении (приёмного отделения)';

COMMENT ON COLUMN CASERECORD.DATEBORN IS 
'дата рождения';

COMMENT ON COLUMN CASERECORD.KLADR IS 
'код населённого пункта по КЛАДР';

COMMENT ON COLUMN CASERECORD.TSOUT IS 
'Время выписки';

COMMENT ON COLUMN CASERECORD.HEIGHT IS 
'Рост';

COMMENT ON COLUMN CASERECORD.BOX IS 
'Палата';

COMMENT ON COLUMN CASERECORD.KEYGRACE IS 
'Код льготы';

COMMENT ON COLUMN CASERECORD.KEYSOCIUM IS 
'Код социума';

COMMENT ON COLUMN CASERECORD.HEPATITYEAR IS 
'Год установления гепатита';

COMMENT ON COLUMN CASERECORD.PRIM IS 
'Примечание, свободная информация';

COMMENT ON COLUMN CASERECORD.BAD IS 
'Признак испорченной истории';

COMMENT ON COLUMN CASERECORD.MOAN IS 
'Жалобы';

COMMENT ON COLUMN CASERECORD.ANAMNES IS 
'Анамнез';

COMMENT ON COLUMN CASERECORD.ANAMNESLIFE IS 
'Анамнез жизни';

COMMENT ON COLUMN CASERECORD.STINGOD IS 
'Острота зрения OD';

COMMENT ON COLUMN CASERECORD.STINGOS IS 
'Острота зрения OS';

COMMENT ON COLUMN CASERECORD.VGDOD IS 
'Глазное давление OD';

COMMENT ON COLUMN CASERECORD.VGDOS IS 
'Глазное давление OS';

COMMENT ON COLUMN CASERECORD.MADE IS 
'Проведено';

COMMENT ON COLUMN CASERECORD.DIAGNOSISOUT IS 
'Диагноз при выписке';

COMMENT ON COLUMN CASERECORD.STINGODOUT IS 
'Острота зрения OD при выписке';

COMMENT ON COLUMN CASERECORD.STINGOSOUT IS 
'Острота зрения OS при выписке';

COMMENT ON COLUMN CASERECORD.PLUG IS 
'Рекомендации';

COMMENT ON COLUMN CASERECORD.CLS IS 
'Признак отправки истории в архив';

COMMENT ON COLUMN CASERECORD.CODEMKB IS 
'Код МКБ';

COMMENT ON COLUMN CASERECORD.EYE IS 
'больной глаз (левый, правый, оба)';

COMMENT ON COLUMN CASERECORD.RESULTOD IS 
'Результат лечения OD';

COMMENT ON COLUMN CASERECORD.RESULTOS IS 
'Результат лечения OS';

COMMENT ON COLUMN CASERECORD.SMT IS 
'Стандарт мед. технологий';

COMMENT ON COLUMN CASERECORD.DAYS IS 
'Сколько пролежал на лечении';

COMMENT ON COLUMN CASERECORD.DATEOUTBRANCH IS 
'Дата передачи истории болезни из отделения на обработку статистикам';

COMMENT ON COLUMN CASERECORD.SICKLIST IS 
'0 - не нужен, 1 - нужен, 2 - имеет больничный';