При разработке отчетов, планировщиков или систем бронирования в 1С перед программистом регулярно встает задача: выбрать документы или записи регистров, период действия которых пересекается с периодом, выбранным пользователем на форме. На первый взгляд задача кажется простой, но попытки решить её «в лоб» часто приводят к написанию громоздких, медленных и ошибочных условий.
В этой статье мы подробно разберем, как реализовать разветвленное условие по периодам, проанализируем типичные ошибки разработчиков и найдем наиболее изящное, быстрое и математически верное решение.
Рассмотрим практическую ситуацию, описанную в Сообщении 7. На форме отчета пользователь выбирает период анализа, например, с 14.06.2019 по 17.06.2019. В базе данных есть документы со своими интервалами планирования (поля ПланДатаНачала и ПланДатаОкончания):
Нам необходимо написать такое условие в секции ГДЕ нашего запроса, которое гарантированно выберет все четыре документа, так как каждый из них был активен в запрашиваемый отрезок времени.
Давайте проанализируем подходы, предложенные в обсуждении на форуме, чтобы понять, почему они не подходят для реальных высоконагруженных систем.
Ошибка №1: Использование конструкции ВЫБОР в секции ГДЕ (Сообщение 2)
Предложение использовать условный оператор ВЫБОР КОГДА ... ТОГДА Истина ИНАЧЕ Ложь в условиях отбора — это серьезный антипаттерн разработки в 1С. Использование логических разветвлений внутри ГДЕ лишает СУБД возможности использовать индексы таблиц (запрос теряет свойство SARGability). СУБД будет вынуждена выполнить полное сканирование таблицы (Full Table Scan), что приведет к критическому падению производительности на реальных объемах данных.
Ошибка №2: Избыточные логические цепочки с ИЛИ (Сообщение 5)
Часто разработчики пытаются перебрать все возможные варианты пересечения через оператор ИЛИ:
ГДЕ
(СЦентр_ЗагрузкаМастеров.Документ.ПланДатаНачала МЕЖДУ &НачДата И &КонДата
ИЛИ СЦентр_ЗагрузкаМастеров.Документ.ПланДатаОкончания МЕЖДУ &НачДата И &КонДата)
Такое условие выглядит логичным, но оно содержит логическую брешь. Оно полностью упускает Документ №4, у которого начало периода находится ДО &НачДата, а окончание — ПОСЛЕ &КонДата. Ни одна из дат этого документа не попадет в операторы МЕЖДУ, и документ будет ошибочно исключен из выборки. Кроме того, обилие операторов ИЛИ сильно усложняет построение оптимального плана запроса для оптимизатора СУБД.
В действительности для определения пересечения двух любых временных отрезков существует простая и универсальная математическая формула. Два интервала пересекаются тогда и только тогда, когда начало каждого из них меньше или равно окончанию другого.
Перенесем это правило на язык запросов 1С. Нам нужно сравнить интервал документа [ПланДатаНачала, ПланДатаОкончания] и интервал параметров [&НачДата, &КонДата]:
ГДЕ
СЦентр_ЗагрузкаМастеров.Документ.ПланДатаНачала <= &КонДата
И СЦентр_ЗагрузкаМастеров.Документ.ПланДатаОкончания >= &НачДата
Давайте проанализируем, как это условие сработает на наших примерах:
Это условие является максимально лаконичным, легко читается, не содержит избыточных ИЛИ и позволяет СУБД эффективно использовать индексы по полям дат начала и окончания.
В реальных бизнес-процессах дата окончания задачи или действия документа часто остается незаполненной (например, работа запланирована, но точный срок её завершения еще не определен). В системе 1С пустая дата хранится как значение 01.01.0001 00:00:00 (в запросе представляется как ДАТАВРЕМЯ(1, 1, 1)).
Если мы применим базовую формулу, документы с пустой датой окончания будут проигнорированы, так как пустая дата заведомо меньше, чем параметр &НачДата. Чтобы решить эту проблему, нам необходимо доработать условие, добавив проверку на пустую дату окончания:
ГДЕ
СЦентр_ЗагрузкаМастеров.Документ.ПланДатаНачала <= &КонДата
И (СЦентр_ЗагрузкаМастеров.Документ.ПланДатаОкончания >= &НачДата
ИЛИ СЦентр_ЗагрузкаМастеров.Документ.ПланДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))
В данном случае, если у документа нет даты окончания, система считает его период «бесконечным в будущее», и он корректно попадет в выборку, если дата его начала не позже, чем &КонДата.
При работе с датами в 1С критически важно помнить про время суток. Если пользователь выбирает на форме период по «17.06.2019», то без явной обработки в параметр &КонДата передается значение начала дня: 17.06.2019 00:00:00.
Если документ запланирован на выполнение, например, на 17.06.2019 в 10:00:00, то простое сравнение ПланДатаНачала <= &КонДата отсечет этот документ, так как 10:00:00 больше, чем 00:00:00 начала дня. Чтобы этого избежать, параметр окончания периода необходимо приводить к концу дня.
Сделать это можно двумя способами:
Способ 1. На уровне встроенного языка при установке параметров запроса:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| СЦентр_ЗагрузкаМастеров.Ссылка КАК Документ
|ИЗ
| Документ.СЦентр_ЗагрузкаМастеров КАК СЦентр_ЗагрузкаМастеров
|ГДЕ
| СЦентр_ЗагрузкаМастеров.ПланДатаНачала <= &КонДата
| И (СЦентр_ЗагрузкаМастеров.ПланДатаОкончания >= &НачДата
| ИЛИ СЦентр_ЗагрузкаМастеров.ПланДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))";
Запрос.УстановитьПараметр("НачДата", НачалоДня(ИнтервалНачало));
// Принудительно устанавливаем конец дня для даты окончания:
Запрос.УстановитьПараметр("КонДата", КонецДня(ИнтервалОкончание));
Способ 2. Непосредственно в самом тексте запроса (особенно полезно при работе с СКД):
ГДЕ
СЦентр_ЗагрузкаМастеров.Документ.ПланДатаНачала <= КОНЕЦПЕРИОДА(&КонДата, ДЕНЬ)
И (СЦентр_ЗагрузкаМастеров.Документ.ПланДатаОкончания >= &НачДата
ИЛИ СЦентр_ЗагрузкаМастеров.Документ.ПланДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))
Для создания быстрого, надежного и безошибочного отбора по пересекающимся периодам придерживайтесь следующих правил:
НачалоДокумента <= КонецПериода И ОкончаниеДокумента >= НачалоПериода.ВЫБОР в секциях отбора ГДЕ.ДАТАВРЕМЯ(1, 1, 1).КонецДня() во встроенном языке или КОНЕЦПЕРИОДА() в тексте запроса.