Здравствуйте, меня зовут Виктор и я разработчик в компании 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:
В статье я показал, с чего мы начинали при добавлении производственного календаря в наше приложение и какие алгоритмы использовали. Основная трудность заключается в ежегодном обновлении данных о праздничных днях. Тема эффективного ведения производственного календаря в системе всегда актуальна. Надеюсь, мой опыт реализации был полезен.
А как вы ведете производственный календарь?
