При разработке отчетов и механизмов расчета в конфигурациях на платформе 1С (особенно в исторических решениях вроде "Управление производственным предприятием" или "Зарплата и Управление Персоналом" редакции 2.5) разработчики часто сталкиваются с задачей: определить реальное количество дней отпуска сотрудника за вычетом государственных праздников. Для этого традиционно сопоставляют данные регистра расчета с производственным календарем.
Однако в процессе реализации нетиповых запросов нередко возникает неприятная ошибка: если в период отпуска сотрудника попадает хотя бы один праздничный день, запрос отрабатывает корректно, но если праздников внутри периода нет — весь результат запроса становится абсолютно пустым. В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем ошибки в соединениях таблиц и рассмотрим профессиональное, оптимизированное решение этой задачи.
Для начала проанализируем архитектуру проблемного запроса. Разработчик использует пакетный запрос, состоящий из нескольких временных таблиц:
РегистрРасчета.ОсновныеНачисленияРаботниковОрганизаций.РегистрСведений.РегламентированныйПроизводственныйКалендарь, которые попадают в период проведения отпуска.Основная критическая ошибка кроется во второй временной таблице и в финальном соединении. Посмотрим на этот фрагмент кода:
ИЗ РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь,
ЗапросОтпуска КАК ЗапросОтпуска
ГДЕ РегламентированныйПроизводственныйКалендарь.ВидДня = &ВидДня
И РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ ЗапросОтпуска.ПериодДействияНачало И ЗапросОтпуска.ПериодДействияКонец
Использование нескольких таблиц в секции ИЗ через запятую без явного указания типа соединения порождает на уровне СУБД так называемое декартово произведение (или CROSS JOIN). СУБД сначала перемножает каждую строчку первой таблицы на каждую строчку второй таблицы, а уже затем накладывает фильтр из секции ГДЕ.
Если в периоде отпуска сотрудника нет ни одного праздничного дня, то условие в секции ГДЕ не находит ни одной подходящей записи. В результате временная таблица КоличестоПраздников оказывается **абсолютно пустой** (в ней содержится 0 строк).
Далее в финальном запросе автор снова соединяет таблицы неявно:
ИЗ ЗапросОтпуска КАК ЗапросОтпуска, КоличестоПраздников КАК КоличестоПраздников
Здесь происходит ключевой сбой логики СУБД. При перемножении любой таблицы на пустую таблицу (0 строк) в результате декартова произведения всегда получается **пустой результат**. Именно поэтому по сотрудникам, у которых в периоде отпуска не было праздников, запрос не возвращал даже исходные дни отпуска.
В процессе обсуждения проблемы на форуме были предложены различные варианты решения. Рассмотрим их подробнее, чтобы понять, какие из них являются "костылями", а какие соответствуют стандартам разработки 1С.
Вариант с соединением ПО ИСТИНА
Один из участников предложил использовать явное ЛЕВОЕ СОЕДИНЕНИЕ с условием связи ПО ИСТИНА:
ИЗ ЗапросОтпуска КАК ЗапросОтпуска
ЛЕВОЕ СОЕДИНЕНИЕ КоличестоПраздников КАК КоличестоПраздников ПО ИСТИНА
Этот способ действительно заставляет запрос выводить данные даже при пустой таблице праздников. За счет левого соединения записи из левой таблицы (отпуска) сохраняются, а поля правой таблицы заполняются значениями NULL. Но у этого подхода есть огромный минус: условие ПО ИСТИНА по-прежнему генерирует декартово произведение. Если мы уберем фильтр по конкретному сотруднику &Сотрудник и решим сформировать отчет по всей организации, СУБД перемножит все отпуска на все праздники. Это приведет к лавинообразному росту потребления памяти на сервере и неверным математическим расчетам (дни праздников будут хаотично вычитаться из чужих отпусков).
Арифметика с NULL
Еще одна важная деталь — правильное вычитание. Напрямую вычесть значение из пустой ячейки (NULL) нельзя. Любая математическая операция вида КалендарныеДни - NULL в языке SQL возвращает значение NULL. Чтобы этого избежать, обязательно нужно использовать функцию ЕСТЬNULL(), которая заменяет пустоту на ноль:
ЗапросОтпуска.КалендарныеДни - ЕСТЬNULL(КоличестоПраздников.ДатаКалендаря, 0)
Давайте откажемся от избыточных временных таблиц, декартовых произведений и неоптимальных связей. Спроектируем запрос, который будет работать мгновенно, не "повесит" базу данных при формировании отчета по всем сотрудникам сразу и корректно обработает ситуации с отсутствием праздников.
Рассмотрим по шагам логику правильного запроса:
ЛЕВОГО СОЕДИНЕНИЯ.Календарь.ДатаКалендаря МЕЖДУ ВТ_ЗапросОтпуска.ПериодДействияНачало И ВТ_ЗапросОтпуска.ПериодДействияКонец.КОЛИЧЕСТВО(Календарь.ДатаКалендаря). Ее уникальное свойство заключается в том, что она автоматически игнорирует значения NULL при группировке. Если праздников нет, функция вернет 0, и нам даже не придется писать громоздкие конструкции ЕСТЬNULL или ВЫБОР.Ниже представлен готовый оптимизированный текст запроса, готовый к использованию в вашей конфигурации:
ВЫБРАТЬ
ОсновныеНачисления.Сотрудник КАК Сотрудник,
ОсновныеНачисления.ПериодДействияНачало КАК ПериодДействияНачало,
ОсновныеНачисления.ПериодДействияКонец КАК ПериодДействияКонец,
СУММА(ОсновныеНачисления.КалендарныеДни) КАК КалендарныеДни
ПОМЕСТИТЬ ВТ_ЗапросОтпуска
ИЗ
РегистрРасчета.ОсновныеНачисленияРаботниковОрганизаций КАК ОсновныеНачисления
ГДЕ
ОсновныеНачисления.ВидРасчета В(&ВидРасчета)
И ОсновныеНачисления.ПериодРегистрации МЕЖДУ &Дата1 И &Дата2
// Для отбора по конкретному сотруднику раскомментируйте строку ниже:
// И ОсновныеНачисления.Сотрудник = &Сотрудник
СГРУППИРОВАТЬ ПО
ОсновныеНачисления.Сотрудник,
ОсновныеНачисления.ПериодДействияНачало,
ОсновныеНачисления.ПериодДействияКонец
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ЗапросОтпуска.Сотрудник КАК Сотрудник,
ВТ_ЗапросОтпуска.ПериодДействияНачало КАК Начало,
ВТ_ЗапросОтпуска.ПериодДействияКонец КАК Конец,
ВТ_ЗапросОтпуска.КалендарныеДни - КОЛИЧЕСТВО(Календарь.ДатаКалендаря) КАК КалендарныеДниБезПраздников
ИЗ
ВТ_ЗапросОтпуска КАК ВТ_ЗапросОтпуска
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ПО Календарь.ДатаКалендаря МЕЖДУ ВТ_ЗапросОтпуска.ПериодДействияНачало И ВТ_ЗапросОтпуска.ПериодДействияКонец
И Календарь.ВидДня = ЗНАЧЕНИЕ(Перечисление.ВидыДнейПроизводственногоКалендаря.Праздник)
СГРУППИРОВАТЬ ПО
ВТ_ЗапросОтпуска.Сотрудник,
ВТ_ЗапросОтпуска.ПериодДействияНачало,
ВТ_ЗапросОтпуска.ПериодДействияКонец,
ВТ_ЗапросОтпуска.КалендарныеДни
&ВидДня мы использовали конструкцию ЗНАЧЕНИЕ(Перечисление.ВидыДнейПроизводственногоКалендаря.Праздник). Это делает код на встроенном языке более лаконичным и снижает риск передачи неверного типа данных извне.ВТ_ЗапросОтпуска, что снижает нагрузку на менеджер временных таблиц СУБД.При использовании регистра сведений РегламентированныйПроизводственныйКалендарь в реальных проектах необходимо учитывать человеческий фактор. Очень часто в начале нового календарного года пользователи забывают заполнить производственный календарь в пользовательском интерфейсе программы. В таком случае в регистре не будет записей о праздничных днях на новый год, и ваш отчет начнет выдавать неверные данные (праздники перестанут вычитаться).
Чтобы избежать подобных инцидентов, настоятельно рекомендуется перед выполнением основного запроса делать экспресс-проверку заполненности календаря на встроенном языке. Например, проверять наличие записей в регистре за указанный период &Дата1 и &Дата2. Если записей нет, следует вывести пользователю предупреждение: "Внимание! Не заполнен регламентированный производственный календарь на текущий период!".