Производственный календарь своими руками в 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>

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

    Комментарии 1

      0
      Чуток не доделано, как по мне. Тут для реального применения явно не хватает поддержки нескольких календарей. Ну потому что на многих предприятиях производственный календарь не один. Бухгалтерия работает по общегосударственному, коллцентр работает 24х7, розничные торговые отделы работают 6 дней в неделю, какое-либо производство 7 дней в неделю по 8 часов, кроме праздников.

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

      Самое читаемое