На написание этой статьи меня натолкнули похожие статьи на хабре: производственный календарь с использование 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>На этом, всё. Мы получили возможность генерировать производственный календарь «на лету», управлять исключениями для дат, а также сохранять календарь в таблице для быстрого поиска по дате. Скрипт для создания таблиц и пакетов календаря вы можете найти здесь.
ссылка на оригинал статьи https://habr.com/ru/post/509050/