В процессе разработки и сопровождения конфигураций на платформе 1С:Предприятие 8 (особенно таких классических решений, как «Управление торговлей 10.3») программисты часто сталкиваются с унаследованным кодом. Одной из наиболее критичных проблем производительности является прямое обращение в запросах к таблицам документов, таких как Документ.ЗаказПокупателя или Документ.РеализацияТоваровУслуг. Подобный подход приводит к серьезным блокировкам базы данных, медленной работе системы при проведении документов и невозможности корректно учесть движения вспомогательных документов.
В этой статье мы подробно разберем, как уйти от запросов к физическим таблицам документов и перевести всю логику на работу с регистрами накопления Продажи и ЗаказыПокупателей. Также мы решим специфическую задачу: как корректно вычесть суммы закрытия заказов и избавиться от коварного значения NULL, которое возвращается СУБД при пустой выборке за период.
Прежде чем мы перейдем к написанию кода, выясним причину, почему прямой запрос к документам считается грубым нарушением стандартов разработки 1С. Рассмотрим основные факторы:
Проанализируем ситуацию: нам необходимо получить общую согласованную сумму по контрагенту за период, исходя из следующих четырех условий:
Дополнительно необходимо учесть, что часть заказа могла быть аннулирована или закрыта документом Закрытие заказов покупателей. Эту сумму закрытия нужно обязательно вычитать из суммы заказа перед сравнением с реализацией.
Для получения сумм фактических отгрузок логично использовать регистр накопления Продажи. Вместо обращения к реальной таблице регистра мы задействуем виртуальную таблицу оборотов Продажи.Обороты. Это обеспечит максимальную скорость работы СУБД.
Рассмотрим пример построения первого пакета запроса:
ВЫБРАТЬ
ПродажиОбороты.ДокументПродажи КАК Реализация,
ПродажиОбороты.ДокументПродажи.Сделка КАК ЗаказСсылка,
СУММА(ПродажиОбороты.СтоимостьОборот) КАК СуммаРТУ
ПОМЕСТИТЬ ВТ_РТУ
ИЗ
РегистрНакопления.Продажи.Обороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
Контрагент = &Контрагент
И ДокументПродажи ССЫЛКА Документ.РеализацияТоваровУслуг) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.ДокументПродажи,
ПродажиОбороты.ДокументПродажи.Sделка
;
Обратите внимание на важный нюанс: в параметрах виртуальной таблицы мы сразу накладываем жесткий отбор по контрагенту и типу документа. Это позволяет СУБД не считывать лишние данные из базы.
Для получения сумм по заказам обратимся к регистру остатков ЗаказыПокупателей. Поскольку это регистр остатков, нам нужно получить именно приходные движения, которые сформировал сам документ Заказ покупателя при проведении. Для этого мы обратимся к виртуальной таблице оборотов этого регистра, выбрав только приходные значения ресурса СуммаВзаиморасчетов:
ВЫБРАТЬ
ЗаказыОбороты.ЗаказПокупателя КАК ЗаказПокупателя,
СУММА(ЗаказыОбороты.СуммаВзаиморасчетовПриход) КАК СуммаЗаказов
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
РегистрНакопления.ЗаказыПокупателей.Обороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
ЗаказПокупателя.Контрагент = &Контрагент) КАК ЗаказыОбороты
СГРУППИРОВАТЬ ПО
ЗаказыОбороты.ЗаказПокупателя
;
В этой таблице мы сагрегировали первоначальные суммы всех заказов контрагента за выбранный период времени.
Документ Закрытие заказов покупателей корректирует регистр ЗаказыПокупателей, делая движение расхода по неотгруженным позициям. Нам необходимо вычленить эти суммы, чтобы скорректировать конечную сумму заказа. Напишем подзапрос к оборотам того же регистра, но отфильтруем движения по регистратору:
ВЫБРАТЬ
ЗаказыОбороты.ЗаказПокупателя КАК ЗаказПокупателя,
СУММА(ЗаказыОбороты.СуммаВзаиморасчетовРасход) КАК СуммаЗакрытия
ПОМЕСТИТЬ ВТ_Закрытие
ИЗ
РегистрНакопления.ЗаказыПокупателей.Обороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
ЗаказПокупателя.Контрагент = &Контрагент
И Регистратор ССЫЛКА Документ.ЗакрытиеЗаказовПокупателей) КАК ЗаказыОбороты
СГРУППИРОВАТЬ ПО
ЗаказыОбороты.ЗаказПокупателя
;
Теперь у нас есть три подготовленные временные таблицы: продажи, заказы и суммы их закрытия. Перейдем к их соединению и реализации условий.
Разберем по шагам, как правильно связать таблицы. Нам потребуется использовать левое соединение от таблицы заказов к таблице закрытия (так как закрыты могут быть далеко не все заказы). После этого мы сопоставим данные с таблицей реализации. При этом важно корректно использовать функцию ЕСТЬNULL, чтобы избежать ошибок расчетов при отсутствии записей в одной из таблиц.
Посмотрим на пример логики сравнения сумм в результирующей временной таблице:
ВЫБРАТЬ
ВЫБОР
КОГДА (ЕСТЬNULL(Заказы.СуммаЗаказов, 0) - ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0)) <= ЕСТЬNULL(Реализации.СуммаРТУ, 0)
ТОГДА ЕСТЬNULL(Реализации.СуммаРТУ, 0)
ИНАЧЕ ЕСТЬNULL(Заказы.СуммаЗаказов, 0) - ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0)
КОНЕЦ КАК СуммаЗаПериод
ПОМЕСТИТЬ ВТ_ИТОГ
ИЗ
ВТ_Заказы КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Закрытие КАК Закрытие
ПО Заказы.ЗаказПокупателя = Закрытие.ЗаказПокупателя
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_РТУ КАК Реализации
ПО Реализации.ЗаказСсылка = Заказы.ЗаказПокупателя
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Реализации.СуммаРТУ
ИЗ
ВТ_РТУ КАК Реализации
ГДЕ
Реализации.ЗаказСсылка = ЗНАЧЕНИЕ(Документ.ЗаказПокупателя.ПустаяСсылка)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Заказы.СуммаЗаказов - ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0)
ИЗ
ВТ_Заказы КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Закрытие КАК Закрытие
ПО Заказы.ЗаказПокупателя = Закрытие.ЗаказПокупателя
ГДЕ
НЕ Заказы.ЗаказПокупателя В (ВЫБРАТЬ РТУ.ЗаказСсылка ИЗ ВТ_РТУ КАК РТУ)
;
В этом пакете мы полностью воссоздали все четыре условия автора. За счет вычитания ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0) из суммы заказа мы всегда оперируем актуальной суммой обязательств по заказу, исключая аннулированные позиции.
При получении итогового значения за период часто возникает ситуация, когда у контрагента вообще не было движений. В этом случае итоговая выборка пуста. При вызове агрегатной функции СУММА без группировки по конкретным полям СУБД возвращает одну строку со значением NULL.
Проанализируем типичную ошибку начинающих разработчиков. Они пытаются обработать этот результат на уровне встроенного языка 1С, проверяя значение на пустую строку или равенство Неопределено. Однако правильный и наиболее изящный способ — решить проблему непосредственно внутри SQL-запроса.
Чтобы гарантировать получение числового нуля вместо пустого значения, функцию СУММА необходимо обернуть в конструкцию ЕСТЬNULL на самом верхнем уровне запроса:
ВЫБРАТЬ
ЕСТЬNULL(СУММА(ВТ_ИТОГ.СуммаЗаПериод), 0) КАК СуммаЗаПериод
ИЗ
ВТ_ИТОГ КАК ВТ_ИТОГ
Данный подход гарантирует, что даже если в таблице ВТ_ИТОГ не будет ни одной строки, запрос вернет числовое значение 0, которое можно сразу использовать в математических вычислениях или алгоритмах контроля лимитов без дополнительных проверок в коде.
Сведем все наши шаги в один готовый шаблон запроса, который можно вставить в рабочую базу или консоль запросов:
ВЫБРАТЬ
ПродажиОбороты.ДокументПродажи КАК Реализация,
ПродажиОбороты.ДокументПродажи.Сделка КАК ЗаказСсылка,
СУММА(ПродажиОбороты.СтоимостьОборот) КАК СуммаРТУ
ПОМЕСТИТЬ ВТ_РТУ
ИЗ
РегистрНакопления.Продажи.Обороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
Контрагент = &Контрагент
И ДокументПродажи ССЫЛКА Документ.РеализацияТоваровУслуг) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.ДокументПродажи,
ПродажиОбороты.ДокументПродажи.Сделка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЗаказыОбороты.ЗаказПокупателя КАК ЗаказПокупателя,
СУММА(ЗаказыОбороты.СуммаВзаиморасчетовПриход) КАК СуммаЗаказов
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
РегистрНакопления.ЗаказыПокупателей.Обороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
ЗаказПокупателя.Contragent = &Контрагент) КАК ЗаказыОбороты
СГРУППИРОВАТЬ ПО
ЗаказыОбороты.ЗаказПокупателя
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЗаказыОбороты.ЗаказПокупателя КАК ЗаказПокупателя,
СУММА(ЗаказыОбороты.СуммаВзаиморасчетовРасход) КАК СуммаЗакрытия
ПОМЕСТИТЬ ВТ_Закрытие
ИЗ
РегистрНакопления.ЗаказыПокупателей.Обороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
ЗаказПокупателя.Контрагент = &Контрагент
И Регистратор ССЫЛКА Документ.ЗакрытиеЗаказовПокупателей) КАК ЗаказыОбороты
СГРУППИРОВАТЬ ПО
ЗаказыОбороты.ЗаказПокупателя
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВЫБОР
КОГДА (ЕСТЬNULL(Заказы.СуммаЗаказов, 0) - ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0)) <= ЕСТЬNULL(Реализации.СуммаРТУ, 0)
ТОГДА ЕСТЬNULL(Реализации.СуммаРТУ, 0)
ИНАЧЕ ЕСТЬNULL(Заказы.СуммаЗаказов, 0) - ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0)
КОНЕЦ КАК СуммаЗаПериод
ПОМЕСТИТЬ ВТ_ИТОГ
ИЗ
ВТ_Заказы КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Закрытие КАК Закрытие
ПО Заказы.ЗаказПокупателя = Закрытие.ЗаказПокупателя
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_РТУ КАК Реализации
ПО Реализации.ЗаказСсылка = Заказы.ЗаказПокупателя
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Реализации.СуммаРТУ
ИЗ
ВТ_РТУ КАК Реализации
ГДЕ
Реализации.ЗаказСсылка = ЗНАЧЕНИЕ(Документ.ЗаказПокупателя.ПустаяСсылка)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Заказы.СуммаЗаказов - ЕСТЬNULL(Закрытие.СуммаЗакрытия, 0)
ИЗ
ВТ_Заказы КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Закрытие КАК Закрытие
ПО Заказы.ЗаказПокупателя = Закрытие.ЗаказПокупателя
ГДЕ
НЕ Заказы.ЗаказПокупателя В (ВЫБРАТЬ РТУ.ЗаказСсылка ИЗ ВТ_РТУ КАК РТУ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЕСТЬNULL(СУММА(ВТ_ИТОГ.СуммаЗаПериод), 0) КАК СуммаЗаПериод
ИЗ
ВТ_ИТОГ КАК ВТ_ИТОГ
;
Мы выполнили важную задачу по рефакторингу legacy-кода в 1С. Переведя алгоритм с физических таблиц документов на виртуальные таблицы оборотов регистров, мы получили кратный прирост производительности. База данных больше не будет подвержена избыточным транзакционным блокировкам при расчете согласованной суммы контрагента.
Дополнительно мы внедрили логику вычитания закрытых сумм заказов, что сделало расчеты абсолютно точными и устойчивыми к корректировкам менеджеров. Обертывание результирующей суммы в функцию ЕСТЬNULL на уровне СУБД избавило нас от написания громоздких условий во встроенном языке 1С.