Здравствуйте, меня зовут Виктор и я разработчик в компании 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:



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

А как вы ведете производственный календарь?