Plpgsql

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



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

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