Здравствуйте, меня зовут Виктор и я разработчик в компании Gems Development. Я хочу рассказать, как мы реализовывали создание и заполнение производственного календаря в Postgresql.
Рабочий процесс зависит от официальных государственных и международных праздников. В корпоративных приложениях часто возникает задача расчета даты с учетом рабочих дней и выходных. Например, такие услуги, как «Выдача разрешения на строительство», «Выдача разрешения на ввод в эксплуатацию» должны быть оказаны в определенные сроки.
После проведения анализа задачи мы пришли к выводу, что в календаре достаточно хранить выходные и праздничные дни, т.к именно они представляют сложность для расчетов и могут меняться в соответствии с производственным календарем в каждом году.
Правила работы с календарем:
- Определение рабочего дня зависит от того, находится ли дата в списке выходных и праздников или нет.
- Если рабочий день в связи с переносом становится нерабочим, то он тоже записывается в таблицу.
- Если выходной день является рабочим, то, соответственно, запись из этой таблицы удаляется.
Для демонстрации создадим отдельную схему и установим переменную в сессии, чтобы каждый раз не указывать схему в запросах:
create schema calendar;
set search_path=calendar;
Для поддержки подобных вычислений в базе данных была создана таблица:
CREATE TABLE d_holidays
(
holiday_date date,
comment character varying(1000),
CONSTRAINT d_holidays_pk PRIMARY KEY (holiday_date)
);
А также индекс, позволяющий ускорить поиск нужной даты в таблице:
CREATE UNIQUE INDEX d_holidays_pk_idx
ON d_holidays
USING btree
(holiday_date);
Для создания данных используем стандартную функцию из Postgresql, возвращающую множество. Проверим работу индекса, предварительно заполнив таблицу достаточно большим набором сгенерированных данных: все дни с 01.01.2017 по 31.12.2025.
insert into d_holidays(holiday_date)
select generate_series(to_date('01.01.2017','dd.mm.yyyy'),
to_date('31.12.2025','dd.mm.yyyy'),
'1 day');
У нас получилось 3287 записей, что соответствует количеству дней с указанных дат. Важно обратить внимание на то, чтобы в конфигурации Postgresql был включен параметр “autovacuum = on”.
Посмотрим план запроса:
explain(analyze)
select comment from d_holidays where holiday_date='09.09.2020';
Мы убедились, что индекс используется:
Index Scan using d_holidays_pk on d_holidays (cost=0.28..8.30 rows=1 width=516) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (holiday_date = '2020-09-09'::date)
Planning time: 0.083 ms
Execution time: 0.044 ms
Расчет выходных дней будем проводить в рамках года. Проверив все даты с 1 января указанного года по 31 декабря выберем выходные дни и запишем их в таблицу. Для реализации алгоритма используем процедурный язык plpgsql и конструкцию анонимного блока.
Приготовим конструкцию, которая формирует дату на 1 января и на 31 декабря.
do
$$
declare
calc_year integer:=2020; --Указанный год
begin_date date; --переменная для начальной даты года
end_date date; --Переменная для конечной даты годы
dow integer; --номер дня недели. Пн-1,...,Сб-6,Вс-0
begin
--Проверка года на корректность
if calc_year between 1988 and 2099 then
begin_date :=to_date('01.01.'||calc_year, 'dd.mm.yyyy');
end_date :=to_date('31.12.'||calc_year, 'dd.mm.yyyy');
end if;
end$$;
Для вычисления дня недели воспользуемся функцией extract, которая определит номер дня недели (с учетом того, что воскресенье — 0). Для определения наименования дня недели используем простую конструкцию case.
Получаем следующий скрипт:
do
$$
declare
calc_year integer:=2020; --Указанный год
begin_date date; --переменная для начальной даты года
end_date date; --Переменная для конечной даты годы
dow_value integer; --номер дня недели. Пн-1,...,Сб-6,Вс-0
begin
--Проверка года на коорректность
if calc_year between 1988 and 2099 then
begin_date :=to_date('01.01.'||calc_year, 'dd.mm.yyyy');
end_date :=to_date('31.12.'||calc_year, 'dd.mm.yyyy');
while begin_date<=end_date
loop
raise info '%',to_char(begin_date,'dd.mm.yyyy');
begin_date:=begin_date+interval '1 day';
dow_value:=extract(dow from begin_date);
if dow_value in(0,6) then
insert into d_holidays(holiday_date,comment)
values(begin_date,
case when dow_value=6 then 'Суббота' else 'Воскресенье' end);
end if;
end loop;
end if;
end$$;
Разберем алгоритм добавления праздничных дней:
insert into d_holidays(holiday_date,comment)
values(to_date('01.01.2020','dd.mm.yyyy'),'Новогодние каникулы')
on conflict(holiday_date) do update set comment=EXCLUDED.comment;
…
insert into d_holidays(holiday_date,comment)
values(to_date('04.05.2020','dd.mm.yyyy'),'Перенос с 04.01.2020')
on conflict(holiday_date) do update set comment=EXCLUDED.comment;
Возможна ситуация, когда выходной становится рабочим днём. В таком случае алгоритм следующий (для примера выбран 2018 год, т.к. в 2020 отсутствуют рабочие выходные):
delete from d_holidays where holiday_date=to_date('29.12.2018','dd.mm.yyyy');
delete from d_holidays where holiday_date=to_date('09.06.2018','dd.mm.yyyy');
Обратим внимание на конструкцию:
on conflict(holiday_date) do update set comment=EXCLUDED.comment;
Данное нововведение появилось в версии Postgresql 9.5 и позволяет эффективно обрабатывать конфликты при вставке записей. В нашем примере, если праздник уже является выходным, то чтобы избежать конфликта по первичному ключу при добавлении записи с уже существующей датой мы обновляем описание даты, не создавая дубликат. Подробнее об этом синтаксисе можно прочитать в документации:
Собрав полный скрипт, мы получаем такой результат:
do
$$
declare
calc_year integer:=2020; --Указанный год
begin_date date; --переменная для начальной даты года
end_date date; --Переменная для конечной даты годы
dow_value integer; --номер дня недели. Пн-1,...,Сб-6,Вс-0
begin
--Проверка года на коорректность
if calc_year between 1988 and 2099 then
begin_date :=to_date('01.01.'||calc_year, 'dd.mm.yyyy');
end_date :=to_date('31.12.'||calc_year, 'dd.mm.yyyy');
while begin_date<=end_date
loop
raise info '%',to_char(begin_date,'dd.mm.yyyy');
begin_date:=begin_date+interval '1 day';
dow_value:=extract(dow from begin_date);
if dow_value in(0,6) then
insert into d_holidays(holiday_date,comment)
values(begin_date,
case when dow_value=6 then 'Суббота' else 'Воскресенье' end);
end if;
end loop;
/*Блок праздников и переносов*/
insert into d_holidays(holiday_date,comment)
values(to_date('01.01.2020','dd.mm.yyyy'),'Новогодние каникулы')
on conflict(holiday_date) do update set comment=EXCLUDED.comment;
/*Опустим часть Insert для сокращения объема*/
insert into d_holidays(holiday_date,comment)
values(to_date('05.11.2020','dd.mm.yyyy'),'День народного единства')
on conflict(holiday_date) do update set comment=EXCLUDED.comment;
insert into d_holidays(holiday_date,comment)
values(to_date('04.05.2020','dd.mm.yyyy'),'Перенос с 04.01.2020')
on conflict(holiday_date) do update set comment=EXCLUDED.comment;
/*Блок праздников и переносов*/
end if;
end$$;
В настоящее время мы также реализовали логику заполнения и ведения производственного календаря в приложении через интерфейс и перенесли реализацию на C#. Также у нас есть возможность формировать отчеты календаря в Excel и LibreOffice.
Графический интерфейс календаря и сформированный отчет в Excel:
В статье я показал, с чего мы начинали при добавлении производственного календаря в наше приложение и какие алгоритмы использовали. Основная трудность заключается в ежегодном обновлении данных о праздничных днях. Тема эффективного ведения производственного календаря в системе всегда актуальна. Надеюсь, мой опыт реализации был полезен.
А как вы ведете производственный календарь?
