Автоматические устанавливаемые резервы

CREATE OR ALTER TRIGGER USER$DOCTSHEDULE_RESERVE_SET FOR DOCTSHEDULE
active after INSERT POSITION 5
AS
BEGIN
  IF (NEW.daytype=1 AND NEW.schedtype=1 AND
      EXISTS(SELECT * FROM repl$getaccess WHERE repl$access = 'USER')) THEN
  BEGIN
    EXECUTE PROCEDURE USER$schedule_reserve_set(NEW.schedident);
  END
END

CREATE OR ALTER PROCEDURE USER$SCHEDULE_RESERVE_SET
(
  SCHEDIDENT TYPE OF COLUMN DOCTSHEDULE.SCHEDIDENT
)
AS
DECLARE a_btime TICODE;
DECLARE a_ftime TICODE;
-- данные по графику
DECLARE filial TYPE OF COLUMN doctshedule.filial;
DECLARE rzd TYPE OF COLUMN razdel.razdid;
DECLARE depnum TYPE OF COLUMN doctshedule.depnum;
DECLARE dcode TYPE OF COLUMN doctshedule.dcode;
DECLARE chair TYPE OF COLUMN doctshedule.chair;
DECLARE weeknum INT;
DECLARE wdate TYPE OF COLUMN doctshedule.wdate;
DECLARE ds_btime TICODE;
DECLARE ds_ftime TICODE;
DECLARE shinterv TYPE OF COLUMN doctshedule.shinterv;
--
DECLARE restricted INT;
-- данные по резерву
DECLARE rsid TYPE OF COLUMN dicinfo.dicid;
DECLARE tmstatus TYPE OF COLUMN dicinfo.rekvint1;
DECLARE shinterv_krat TYPE OF COLUMN dicinfo.rekvint4;
DECLARE rs_btime TICODE;
DECLARE rs_ftime TICODE;
DECLARE interv TYPE OF COLUMN dicinfo.rekvint5;
DECLARE all_free_time TYPE OF COLUMN dicinfo.rekvint3;
DECLARE enable_by_hour_more TYPE OF COLUMN dicinfo.rekvint10;
-- данные по расписанию
DECLARE sc_btime TICODE;
DECLARE sc_ftime TICODE;
-- процедура поиска свободного времени
DECLARE PROCEDURE prc_freetime (
  schedident TYPE OF COLUMN DOCTSHEDULE.SCHEDIDENT
)
RETURNS (
  btime TICODE,
  ftime TICODE
)
AS
DECLARE obtime TICODE;
DECLARE ftimen TICODE;
BEGIN
  obtime = 0;
  FOR SELECT bhour*60+bmin, fhour*60+fmin,
             lead(bhour*60+bmin) OVER (ORDER BY bhour*60+bmin)
        FROM SCHED_INTERVALS(:schedident, 0)
       WHERE atype IN (4,5)
  INTO btime,ftime,ftimen do
  BEGIN
    IF (ftime = ftimen) THEN
    BEGIN
      IF (obtime = 0) THEN
        obtime = btime;
    END
    ELSE
    BEGIN
      IF (obtime != 0) THEN
        btime = obtime;
      obtime = 0;
      suspend;
    END
  END
END
--  end procedure               prc_freetime
-- функция проверки общих ограничений
DECLARE FUNCTION prc_restrict_all (
  -- время предполагаемого резерва
  btime INT,
  ftime INT,
  -- время по графику работы
  ds_btime INT,
  ds_ftime INT,
  -- время по авто-резерву
  rs_btime INT,
  rs_ftime INT,
  enable_by_hour_more INT
)
RETURNS INTEGER
AS
DECLARE restricted INT;
BEGIN
  restricted = 0;
  IF (btime = ftime) THEN
    restricted = 1;
  IF (btime IS NULL OR ftime IS NULL) THEN
    restricted = 1;
  IF (COALESCE(enable_by_hour_more,0)>0 AND ds_ftime-ds_btime<enable_by_hour_more*60) THEN
    restricted = 1;
  --
  RETURN restricted;
END
--  end function                prc_restrict_all
-- функция проверки заданных ограничений
DECLARE FUNCTION prc_restrict (
  rsid   TYPE OF COLUMN dicinfo.dicid,
  filial TYPE OF COLUMN filials.filid,
  rzdid  TYPE OF COLUMN razdel.razdid,
  depnum TYPE OF COLUMN departments.depnum,
  dcode  TYPE OF COLUMN doctor.dcode,
  chid   TYPE OF COLUMN chairs.chid,
  weeknum INT,
  btime INT,
  ftime INT
)
RETURNS INTEGER
AS
DECLARE restricted INT;
DECLARE cur_type INT;
DECLARE cur_val INT;
--
DECLARE cur cursor FOR (SELECT
         sign(SUM(CASE WHEN rekvint3>0 THEN 1 ELSE 0 END)) exists_allow,
         sign(SUM(CASE WHEN rekvint3<0 THEN 1 ELSE 0 END)) exists_deny,
         sign(SUM(CASE WHEN rekvint3>0 AND rekvint1= :cur_val THEN 1 ELSE 0 END)) is_allow,
         sign(SUM(CASE WHEN rekvint3<0 AND rekvint1= :cur_val THEN 1 ELSE 0 END)) is_deny
    FROM dicinfo rd
   WHERE rd.refid=-10010087 AND rd.parentdicid = :rsid AND abs(rd.rekvint3) = :cur_type );
--
BEGIN
  restricted = 0;
  /* правила в ограничениях:
        явное разрешение                            allow
        явное запрещение                            deny
        есть разрешения, но данное не разрешено     deny
        есть зпрещения, но данное не запрещено      allow
  */
  -- филиал           [1]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 1;
    cur_val = filial;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- раздел медицины  [2]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 2;
    cur_val = rzdid;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- отделение        [3]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 3;
    cur_val = depnum;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- доктор           [4]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 4;
    cur_val = dcode;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- кресло           [5]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 5;
    cur_val = chid;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- день недели      [6]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 6;
    cur_val = weeknum;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- час начала       [8]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 8;
    cur_val = btime/60;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  -- час окончания    [9]
  IF (restricted = 0) THEN
  BEGIN
    cur_type = 9;
    cur_val = ftime/60;
    OPEN cur;
    fetch NEXT FROM cur;
    IF ((cur.is_deny = 1) OR (cur.exists_allow = 1 AND cur.is_allow != 1)) THEN
      restricted = 1;
    close cur;
  END
  --
  RETURN restricted;
END
--  end function                prc_restrinct
-- функция задания резерва
DECLARE FUNCTION prc_newrec (
  schedident   TYPE OF COLUMN schedule.schedident,
  filial TYPE OF COLUMN filials.filid,
  dcode  TYPE OF COLUMN doctor.dcode,
  chid   TYPE OF COLUMN chairs.chid,
  wdate  TYPE OF COLUMN schedule.workdate,
  btime INT,
  ftime INT,
  tmstatus  TYPE OF COLUMN schedule.tmstatus
)
RETURNS INTEGER
AS
DECLARE restricted INT;
DECLARE spresult INT;
DECLARE bhour INT;
DECLARE bmin INT;
DECLARE fhour INT;
DECLARE fmin INT;
DECLARE uid TYPE OF COLUMN doctor.dcode;
BEGIN
  bhour = btime/60;
  bmin  = btime-(btime/60)*60;
  fhour = ftime/60;
  fmin  = ftime-(ftime/60)*60;
  uid = rdb$get_context('USER_SESSION', 'USER_ID');
  --
  SELECT schedid FROM schednewrec (:dcode, :wdate, :chid, :filial,
         :bhour, :bmin, :fhour, :fmin, NULL, :uid, NULL, :tmstatus,
         NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
         EXTRACT(HOUR FROM CURRENT_TIMESTAMP), 'now', NULL, NULL, NULL, 1,
         :filial, NULL, NULL, NULL, :schedident, 0, NULL, NULL, NULL, NULL)
    INTO :spresult;
  --
  RETURN 0;
END
--  end function                prc_newrec
BEGIN
  /* описание структуры:
            резервы
        dicid       идентификатор записи
        refid       идентификатор справочника в системе
        dicorder    порядок применения
        disdate     дата отмены действия
        rekvint1    резерв (schedmarks)
        rekvint2    филиал
        rekvint3    признак: на все свободное время
        rekvint4    признак: кратно интервалам
        rekvint5    признак интервал постановки (перв, посл)
        rekvint6    время постановки резерва: час начала
        rekvint7                              мин начала
        rekvint8                              час окончания
        rekvint9                              мин окончания
        rekvint10   применять, если график более чем ... часов
            ограничения
        dicid       идентификатор записи
        refid       идентификатор справочника в системе
        parentdicid идентификатор записи резерва
        rekvint3    тип ограничения ("+" разр / "-" запр)
        rekvint1    значение ограничения
            справочник типов ограничений
        dicid       идентификатор записи
        refid       идентификатор справочника в системе
        rekvint3    тип ограничения
        simplename  назначение типа ограничения
 
  */
  -- получаем данные
  SELECT ds.filial, dep.razdel, ds.depnum, ds.dcode, ds.chair, EXTRACT(weekday FROM ds.wdate) week,
         ds.wdate,ds.beghour*60+ds.begmin,ds.endhour*60+ds.endmin,ds.shinterv
    FROM doctshedule ds
    LEFT JOIN departments dep ON dep.depnum= ds.depnum
   WHERE ds.schedident = :schedident
    INTO filial,rzd,depnum,dcode,chair,weeknum,wdate,ds_btime,ds_ftime,shinterv;
 
  IF (rzd IS NOT NULL) THEN
  BEGIN
    -- смотрим возможные резервы
    FOR SELECT dicid,rekvint1,rekvint4,rekvint5,rekvint3,rekvint10,rekvint6*60+rekvint7,rekvint8*60+rekvint9
          FROM dicinfo r
         WHERE r.refid=-10010088 AND :filial = COALESCE(r.rekvint2, :filial) AND COALESCE(disdate,:wdate)>= :wdate
         ORDER BY dicorder
    INTO rsid,tmstatus,shinterv_krat,interv,all_free_time,enable_by_hour_more,rs_btime,rs_ftime do
    BEGIN
      a_btime = NULL;
      a_ftime = NULL;
 
      -- проверяем время + корректируем время
      -- на первый интервал
      IF (interv = 1) THEN
      BEGIN
        SELECT FIRST 1 btime, ftime FROM prc_freetime(:schedident) ORDER BY btime ASC
          INTO a_btime, a_ftime;
        IF (shinterv_krat=1) THEN
        BEGIN
          a_ftime = minvalue(a_ftime, a_btime+shinterv);
        END
        ELSE
        BEGIN
          a_ftime = minvalue(a_ftime, COALESCE(rs_ftime,0));
        END
        -- проверяем общие ограничения
        restricted = prc_restrict_all(:a_btime,:a_ftime,:ds_btime,:ds_ftime,:rs_btime,:rs_ftime,:enable_by_hour_more);
        -- проверяем ограничения на резерв
        IF (restricted = 0) THEN
          restricted = prc_restrict(:rsid,:filial,:rzd,:depnum,:dcode,:chair,:weeknum,:a_btime,:a_ftime);
        -- ставим резерв
        IF (restricted = 0) THEN
          restricted = prc_newrec(:schedident,:filial,:dcode,:chair,:wdate,:a_btime,:a_ftime,:tmstatus);
      END
 
      -- на последний интервал
      ELSE IF (interv = -1) THEN
      BEGIN
        SELECT FIRST 1 btime, ftime FROM prc_freetime(:schedident) ORDER BY btime DESC
          INTO a_btime, a_ftime;
        IF (shinterv_krat=1) THEN
        BEGIN
          a_btime = maxvalue(a_btime, a_ftime-shinterv);
        END
        ELSE
        BEGIN
          a_btime = maxvalue(a_btime, COALESCE(rs_btime,0));
        END
        -- проверяем общие ограничения
        restricted = prc_restrict_all(:a_btime,:a_ftime,:ds_btime,:ds_ftime,:rs_btime,:rs_ftime,:enable_by_hour_more);
        -- проверяем ограничения на резерв
        IF (restricted = 0) THEN
          restricted = prc_restrict(:rsid,:filial,:rzd,:depnum,:dcode,:chair,:weeknum,:a_btime,:a_ftime);
        -- ставим резерв
        IF (restricted = 0) THEN
          restricted = prc_newrec(:schedident,:filial,:dcode,:chair,:wdate,:a_btime,:a_ftime,:tmstatus);
      END
 
      -- указано время постановки
      ELSE IF (COALESCE(rs_btime,0)>0 AND COALESCE(rs_ftime,0)>0) THEN
      BEGIN
        -- время резерва за пределами графика работы
        IF (rs_ftime <= ds_btime OR rs_btime>=ds_ftime) THEN
        BEGIN
          continue;
        END
 
        -- время резерва попадает в график (?частично)
        ELSE
        BEGIN
          FOR SELECT btime, ftime FROM prc_freetime(:schedident)
               WHERE NOT (btime > :rs_ftime OR ftime < :rs_btime)
          INTO a_btime, a_ftime do
          BEGIN
            restricted = 0;
            a_btime = maxvalue(a_btime, rs_btime);
            a_ftime = minvalue(a_ftime, rs_ftime);
 
            -- проверяем общие ограничения
            restricted = prc_restrict_all(:a_btime,:a_ftime,:ds_btime,:ds_ftime,:rs_btime,:rs_ftime,:enable_by_hour_more);
            -- проверяем ограничения на резерв
            IF (restricted = 0) THEN
              restricted = prc_restrict(:rsid,:filial,:rzd,:depnum,:dcode,:chair,:weeknum,:a_btime,:a_ftime);
            -- ставим резерв
            IF (restricted = 0) THEN
              restricted = prc_newrec(:schedident,:filial,:dcode,:chair,:wdate,:a_btime,:a_ftime,:tmstatus);
            --
          END
        END
      END
 
      -- на все свободное
      ELSE IF (all_free_time=1) THEN
      BEGIN
        -- при этом пункте игнорируем все остальные настройки, кроме ограничений
        FOR SELECT btime, ftime FROM prc_freetime(:schedident)
        INTO a_btime, a_ftime do
        BEGIN
          -- проверяем общие ограничения
          restricted = prc_restrict_all(:a_btime,:a_ftime,:ds_btime,:ds_ftime,:rs_btime,:rs_ftime,:enable_by_hour_more);
          -- проверяем ограничения на резерв
          IF (restricted = 0) THEN
            restricted = prc_restrict(:rsid,:filial,:rzd,:depnum,:dcode,:chair,:weeknum,:a_btime,:a_ftime);
          -- ставим резерв
          IF (restricted = 0) THEN
            restricted = prc_newrec(:schedident,:filial,:dcode,:chair,:wdate,:a_btime,:a_ftime,:tmstatus);
        END
      END
      ELSE
      BEGIN
        -- не указано нормально варианта постановки
        continue;
      END
    END
  END
END