Производственный календарь своими руками в Firebird

Здравствуйте, меня зовут Денис, я явлюсь разработчиком информационных систем, пишу статьи и документацию по СУБД Firebird. В этой статье я хочу рассказать о реализации производственного календаря с использованием СУБД Firebird.

На написание этой статьи меня натолкнули похожие статьи на хабре: производственный календарь с использование PostgreSQL и MS SQL. Я решил использовать смешанный подход. С одной стороны, хранить только исключения для дат, и генерировать календарь «на лету», с другой такой календарь можно сохранять в постоянную таблицу и осуществлять быстрый поиск по дате или другим атрибутам.

Для разработки будем использовать Firebird 3.0, в нём был значительно расширены возможности PSQL по сравнению с предыдущими версиями. Все процедуры и функции для работы с календарём будут инкапсулированы в пакете DATE_UTILS.

Первым делом создадим таблицу для хранения стандартных праздничных дат.

CREATE TABLE HOLIDAYS (
    ID      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    AMONTH  SMALLINT NOT NULL,
    ADAY    SMALLINT NOT NULL,
    REMARK  VARCHAR(255) NOT NULL,
    CONSTRAINT PK_HOLIDAYS PRIMARY KEY (ID),
    CONSTRAINT UNQ_HOLIDAYS UNIQUE (AMONTH, ADAY
);

INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (1, 1, 1, 'Новый год');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (2, 1, 7, 'Рождество');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (3, 2, 23, 'День защитника отечества');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (4, 3, 8, 'Международный женский день');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (5, 5, 1, 'Праздник весны и труда');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (6, 5, 9, 'День победы');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (7, 6, 12, 'День России');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (8, 11, 4, 'День народного единства');

COMMIT;

Такая таблица поможет нам автоматизировать процесс заполнения календаря, чтобы не добавлять праздники каждый раз в качестве выходных.

Теперь создадим таблицу для хранения исключений. В ней будут храниться как будние дни, которые стали выходными, так и наоборот выходные, которые назначены рабочими днями.

Кроме того, для любой даты можно оставить произвольную заметку.

CREATE TABLE CALENDAR_NOTES (
    BYDATE    DATE NOT NULL,
    DAY_TYPE  SMALLINT NOT NULL,
    REMARK    VARCHAR(255),
    CONSTRAINT PK_CALENDAR_NOTES PRIMARY KEY (BYDATE)
);

Поле DAY_TYPE указывает тип даты: 0 – рабочий день. 1 – выходной, 2 – праздник.

Для работы с таблицей исключений создадим 2 хранимые процедуры и разместим их внутри пакета DATE_UTILS.

  
  -- Устанавливает пометку для даты и её тип
  PROCEDURE SET_DATE_NOTE (
      ADATE     DATE,
      ADAY_TYPE SMALLINT,
      AREMARK   VARCHAR(255))
  AS
  BEGIN
    UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
    VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
  END

  -- снимает пометку у даты
  PROCEDURE UNSET_DATE_NOTE (
      ADATE DATE)
  AS
  BEGIN
    DELETE FROM CALENDAR_NOTES
    WHERE BYDATE = :ADATE;
  END

В Firebird в отличие от PostgreSQL отсутствует специальная функция для генерации серий значений. Такую генерацию можно сделать с помощью рекурсивного CTE, но в этом случае мы будем ограничены глубиной рекурсии. Мы поступим несколько проще, напишем специальную селективную хранимую процедуру для генерации последовательности дат и разместим её внутри пакета DATE_UTILS.

  
  -- генерация последовательности дат
  -- с интервалом 1 день
  PROCEDURE GENERATE_SERIES (
      MIN_DATE DATE,
      MAX_DATE DATE)
  RETURNS (
      BYDATE DATE)
  AS
  BEGIN
    IF (MIN_DATE > MAX_DATE) THEN
      EXCEPTION E_MIN_DATE_EXCEEDS;
    BYDATE = MIN_DATE;
    WHILE (BYDATE <= MAX_DATE) DO
    BEGIN
      SUSPEND;
      BYDATE = BYDATE + 1;
    END
  END

В процедуре предусмотрена защита от зацикливания, если минимальная дата будет больше максимальной, то будет брошено исключение E_MIN_DATE_EXCEEDS, которое определено следующим образом:

CREATE EXCEPTION E_MIN_DATE_EXCEEDS 'Минимальная дата превышает максимальную';

Теперь собственно перейдём к генерации календаря «на лету». Если дата содержится в таблице исключений, то будет выведен тип даты и примечание из таблицы исключений. Если даты нет в таблице исключений, но она присутствует в таблице с праздничными датами, то выводим примечание из таблицы праздников. Выходные определяем по номеру дня недели, остальные даты являются рабочими. Описанный алгоритм реализовывается следующим запросом

SELECT
    D.BYDATE,
    CASE
        WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
        WHEN HOLIDAYS.ID IS NOT NULL THEN 2
        WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
        ELSE 0
    END AS DATE_TYPE,
    COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
    LEFT JOIN HOLIDAYS 
      ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
         HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
    LEFT JOIN CALENDAR_NOTES NOTES 
      ON NOTES.BYDATE = D.BYDATE

Сохраним данный запрос в селективную хранимую процедуру и добавим вывод некоторых дополнительных столбцов

-- возвращает календарь
PROCEDURE GET_CALENDAR (
    MIN_DATE DATE,
    MAX_DATE DATE)
RETURNS (
    BYDATE     DATE,
    YEAR_OF    SMALLINT,
    MONTH_OF   SMALLINT,
    DAY_OF     SMALLINT,
    WEEKDAY_OF SMALLINT,
    DATE_TYPE  SMALLINT,
    REMARK     VARCHAR(255))
AS
BEGIN
    FOR
      SELECT
          D.BYDATE,
          EXTRACT(YEAR FROM d.BYDATE) AS YEAR_OF,
          EXTRACT(MONTH FROM d.BYDATE) AS MONTH_OF,
          EXTRACT(DAY FROM d.BYDATE) AS DAY_OF,
          EXTRACT(WEEKDAY FROM d.BYDATE) AS WEEKDAY_OF,
          CASE
            WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
            WHEN HOLIDAYS.ID IS NOT NULL THEN 2
            WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
            ELSE 0
          END AS DATE_TYPE,
          COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
      FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
          LEFT JOIN HOLIDAYS
            ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
               HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
          LEFT JOIN CALENDAR_NOTES NOTES
            ON NOTES.BYDATE = D.BYDATE
      INTO BYDATE,
           YEAR_OF,
           MONTH_OF,
           DAY_OF,
           WEEKDAY_OF,
           DATE_TYPE,
           REMARK
    DO
      SUSPEND;
END

Добавим несколько функций для вывода дней недели, названий месяцев и типа даты на русском языке.

-- возвращает короткое имя дня недели
  FUNCTION GET_WEEKDAY_NAME(AWEEKDAY SMALLINT) RETURNS CHAR(2)
  AS
  BEGIN
    RETURN CASE AWEEKDAY
      WHEN 1 THEN 'пн'
      WHEN 2 THEN 'вт'
      WHEN 3 THEN 'ср'
      WHEN 4 THEN 'чт'
      WHEN 5 THEN 'пт'
      WHEN 6 THEN 'сб'
      WHEN 0 THEN 'вс'
    END;
  END

  -- возвращает имя месяца
  FUNCTION GET_MONTH_NAME(AMONTH SMALLINT) RETURNS VARCHAR(10)
  AS
  BEGIN
    RETURN CASE AMONTH
      WHEN 1 THEN 'январь'
      WHEN 2 THEN 'февраль'
      WHEN 3 THEN 'март'
      WHEN 4 THEN 'апрель'
      WHEN 5 THEN 'май'
      WHEN 6 THEN 'июнь'
      WHEN 7 THEN 'июль'
      WHEN 8 THEN 'август'
      WHEN 9 THEN 'сентябрь'
      WHEN 10 THEN 'октябрь'
      WHEN 11 THEN 'ноябрь'
      WHEN 12 THEN 'декабрь'
    END;
  END

  -- возвращает наименование типа даты
  FUNCTION GET_DAY_TYPE_NAME(ADAY_TYPE SMALLINT) RETURNS VARCHAR(11)
  AS
  BEGIN
    RETURN CASE ADAY_TYPE
      WHEN 0 THEN 'Рабочий'
      WHEN 1 THEN 'Выходной'
      WHEN 2 THEN 'Праздничный'
    END;
  END

Теперь мы можем вывести календарь используя следующий запрос:

SELECT
    D.BYDATE AS BYDATE,
    D.YEAR_OF,
    DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
    D.DAY_OF,
    DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
    DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
    D.REMARK AS REMARK
FROM DATE_UTILS.GET_CALENDAR(DATE '01.05.2019', DATE '31.05.2019') D

BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01     2019 май              1 ср           Праздничный Праздник весны и труда
2019-05-02     2019 май              2 чт           Выходной    Майские праздники
2019-05-03     2019 май              3 пт           Выходной    Майские праздники
2019-05-04     2019 май              4 сб           Выходной    Майские праздники
2019-05-05     2019 май              5 вс           Выходной    Майские праздники
2019-05-06     2019 май              6 пн           Рабочий     <null>
2019-05-07     2019 май              7 вт           Рабочий     <null>
2019-05-08     2019 май              8 ср           Рабочий     <null>
2019-05-09     2019 май              9 чт           Праздничный День победы
2019-05-10     2019 май             10 пт           Выходной    Майские праздники
2019-05-11     2019 май             11 сб           Выходной    <null>
2019-05-12     2019 май             12 вс           Выходной    <null>
2019-05-13     2019 май             13 пн           Рабочий     <null>
2019-05-14     2019 май             14 вт           Рабочий     <null>
2019-05-15     2019 май             15 ср           Рабочий     <null>
2019-05-16     2019 май             16 чт           Рабочий     <null>
2019-05-17     2019 май             17 пт           Рабочий     <null>
2019-05-18     2019 май             18 сб           Выходной    <null>
2019-05-19     2019 май             19 вс           Выходной    <null>
2019-05-20     2019 май             20 пн           Рабочий     <null>


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21     2019 май             21 вт           Рабочий     <null>
2019-05-22     2019 май             22 ср           Рабочий     <null>
2019-05-23     2019 май             23 чт           Рабочий     <null>
2019-05-24     2019 май             24 пт           Рабочий     <null>
2019-05-25     2019 май             25 сб           Выходной    <null>
2019-05-26     2019 май             26 вс           Выходной    <null>
2019-05-27     2019 май             27 пн           Рабочий     <null>
2019-05-28     2019 май             28 вт           Рабочий     <null>
2019-05-29     2019 май             29 ср           Рабочий     <null>
2019-05-30     2019 май             30 чт           Рабочий     <null>
2019-05-31     2019 май             31 пт           Рабочий     <null>

Если необходимо пометить какую-то дату, как выходной или будний день используем следующий запрос:

EXECUTE PROCEDURE DATE_UTILS.SET_DATE_NOTE(date '05.05.2019', 1, 'Майские праздники');

Чтобы убрать дату из списка исключений необходимо выполнить запрос

EXECUTE PROCEDURE DATE_UTILS.UNSET_DATE_NOTE(date '05.05.2019');

Теперь создадим таблицу для хранения производственного календаря, и напишем процедуру для её заполнения.

CREATE TABLE CALENDAR (
    BYDATE      DATE NOT NULL,
    YEAR_OF     SMALLINT NOT NULL,
    MONTH_OF    SMALLINT NOT NULL,
    DAY_OF      SMALLINT NOT NULL,
    WEEKDAY_OF  SMALLINT NOT NULL,
    DATE_TYPE   SMALLINT NOT NULL,
    REMARK      VARCHAR(255),
    CONSTRAINT PK_CALENDAR PRIMARY KEY (BYDATE)
);

  -- заполнение/обновление таблицы календаря
  PROCEDURE FILL_CALENDAR (
      MIN_DATE DATE,
      MAX_DATE DATE)
  AS
  BEGIN
    MERGE INTO CALENDAR
    USING (
      SELECT
        BYDATE,
        YEAR_OF,
        MONTH_OF,
        DAY_OF,
        WEEKDAY_OF,
        DATE_TYPE,
        REMARK
      FROM DATE_UTILS.GET_CALENDAR(:MIN_DATE, :MAX_DATE)
    ) S
    ON CALENDAR.BYDATE = S.BYDATE
    WHEN NOT MATCHED THEN
    INSERT (
      BYDATE,
      YEAR_OF,
      MONTH_OF,
      DAY_OF,
      WEEKDAY_OF,
      DATE_TYPE,
      REMARK
    )
    VALUES (
      S.BYDATE,
      S.YEAR_OF,
      S.MONTH_OF,
      S.DAY_OF,
      S.WEEKDAY_OF,
      S.DATE_TYPE,
      S.REMARK
    )
    WHEN MATCHED AND
      (CALENDAR.DATE_TYPE <> S.DATE_TYPE OR 
       CALENDAR.REMARK <> S.REMARK) THEN
    UPDATE SET
      DATE_TYPE = S.DATE_TYPE,
      REMARK = S.REMARK;
  END

Процедура заполнения таблицы для хранения календаря спроектирована таким образом, что если в нём уже существует дата, то произойдёт обновление типа даты и заметки, только если изменения произошли в таблице исключений, или дата удалена из списка исключений.

Для того, чтобы изменения в таблице исключений сразу отражались в таблице календаря немного изменим процедуры SET_DATE_NOTE и UNSET_DATE_NOTE. Первое изменение довольно тривиально, мы просто добавляем в процедуру ещё один запрос на обновление примечания и типа даты в таблице CALENDAR.

  -- Устанавливает пометку для даты и её тип
  PROCEDURE SET_DATE_NOTE (
      ADATE     DATE,
      ADAY_TYPE SMALLINT,
      AREMARK   VARCHAR(255))
  AS
  BEGIN
    UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
    VALUES (:ADATE, :ADAY_TYPE, :AREMARK);

    -- если дата уже есть в календаре обновляем её
    UPDATE CALENDAR
    SET DATE_TYPE = :ADAY_TYPE,
        REMARK = :AREMARK
    WHERE BYDATE = :ADATE
      AND (DATE_TYPE <> :ADAY_TYPE OR REMARK <> :AREMARK);
  END

Удаление пометки для даты несколько сложнее, поскольку мы должны вернуть замечание, которое было у даты до того, как она была внесена в список исключений. Для этого, мы используем туже логику определения типа даты и замечания, что уже использовалась в процедуре GET_CALENDAR.

  -- снимает пометку у даты
  PROCEDURE UNSET_DATE_NOTE (
      ADATE DATE)
  AS
  BEGIN
    DELETE FROM CALENDAR_NOTES
    WHERE BYDATE = :ADATE;

    -- надо вернуть тип даты и примечание по умолчанию
    MERGE INTO CALENDAR
    USING (
      SELECT
          :ADATE AS BYDATE,
          CASE
            WHEN HOLIDAYS.ID IS NOT NULL THEN 2
            WHEN EXTRACT(WEEKDAY FROM :ADATE) IN (0, 6) THEN 1
            ELSE 0
          END AS DATE_TYPE,
          HOLIDAYS.REMARK AS REMARK
      FROM RDB$DATABASE
      LEFT JOIN HOLIDAYS ON
        HOLIDAYS.AMONTH = EXTRACT(MONTH FROM :ADATE) AND
        HOLIDAYS.ADAY = EXTRACT(DAY FROM :ADATE)
    ) S
    ON CALENDAR.BYDATE = S.BYDATE
    WHEN MATCHED THEN
    UPDATE SET
      DATE_TYPE = S.DATE_TYPE,
      REMARK = S.REMARK;
  END

Вывести календарь из таблицы можно с использованием следующего запрос:

SELECT
    D.BYDATE AS BYDATE,
    D.YEAR_OF,
    DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
    D.DAY_OF,
    DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
    DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
    D.REMARK AS REMARK
FROM CALENDAR D
WHERE D.BYDATE BETWEEN DATE '01.05.2019' AND DATE '31.05.2019'

BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01     2019 май              1 ср           Праздничный Праздник весны и труда
2019-05-02     2019 май              2 чт           Выходной    Майские праздники
2019-05-03     2019 май              3 пт           Выходной    Майские праздники
2019-05-04     2019 май              4 сб           Выходной    Майские праздники
2019-05-05     2019 май              5 вс           Выходной    Майские праздники
2019-05-06     2019 май              6 пн           Рабочий     <null>
2019-05-07     2019 май              7 вт           Рабочий     <null>
2019-05-08     2019 май              8 ср           Рабочий     <null>
2019-05-09     2019 май              9 чт           Праздничный День победы
2019-05-10     2019 май             10 пт           Выходной    Майские праздники
2019-05-11     2019 май             11 сб           Выходной    <null>
2019-05-12     2019 май             12 вс           Выходной    <null>
2019-05-13     2019 май             13 пн           Рабочий     <null>
2019-05-14     2019 май             14 вт           Рабочий     <null>
2019-05-15     2019 май             15 ср           Рабочий     <null>
2019-05-16     2019 май             16 чт           Рабочий     <null>
2019-05-17     2019 май             17 пт           Рабочий     <null>
2019-05-18     2019 май             18 сб           Выходной    <null>
2019-05-19     2019 май             19 вс           Выходной    <null>
2019-05-20     2019 май             20 пн           Рабочий     <null>


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21     2019 май             21 вт           Рабочий     <null>
2019-05-22     2019 май             22 ср           Рабочий     <null>
2019-05-23     2019 май             23 чт           Рабочий     <null>
2019-05-24     2019 май             24 пт           Рабочий     <null>
2019-05-25     2019 май             25 сб           Выходной    <null>
2019-05-26     2019 май             26 вс           Выходной    <null>
2019-05-27     2019 май             27 пн           Рабочий     <null>
2019-05-28     2019 май             28 вт           Рабочий     <null>
2019-05-29     2019 май             29 ср           Рабочий     <null>
2019-05-30     2019 май             30 чт           Рабочий     <null>
2019-05-31     2019 май             31 пт           Рабочий     <null>

На этом, всё. Мы получили возможность генерировать производственный календарь «на лету», управлять исключениями для дат, а также сохранять календарь в таблице для быстрого поиска по дате. Скрипт для создания таблиц и пакетов календаря вы можете найти здесь.