Разработка отчетов в среде 1С:Предприятие 7.7 часто преподносит сюрпризы, связанные с особенностями работы встроенного языка запросов. Одной из наиболее неочевидных проблем является использование отрицания в условиях отбора, например, конструкции Условие (НЕ (Товар в СписокЗначений)). На первый взгляд, логика абсолютно прозрачна: мы выбираем товары из регистра, исключая те, которые содержатся в заранее подготовленном списке. Однако на практике этот фильтр может работать непредсказуемо — либо полностью очищать результирующую выборку, либо пропускать элементы, которые гарантированно должны быть в отчете.
В рамках этой статьи мы детально разберем причины такого поведения, изучим "иерархическую ловушку", рассмотрим проблему пустого списка и предложим эффективные пути решения данной задачи как для файловых (DBF), так и для SQL-версий баз данных.
Представим стандартную бизнес-задачу: нам необходимо сформировать отчет по товарам, по которым на складах есть остатки или движения за период, но при этом полностью исключить из отчета те товары, которые в данном периоде продавались (то есть фигурировали в проведенных документах РасходнаяНакладная). Нам важно увидеть именно "залежавшийся" товар, по которому могли быть внутренние перемещения или поступления, но не было реальных розничных или оптовых продаж.
Рассмотрим классический двухшаговый алгоритм, который часто пытаются реализовать разработчики:
СписокЗначений.Условие (НЕ (Товар в СписокПроданныхТоваров)).Проанализируем пример исходного кода вспомогательного и основного запросов, который изначально написал автор темы:
// Шаг 1. Получение списка проданных товаров за период
Если ТолькоНеПроданныеТовары = 1 Тогда
ТекстЗапросаПроданныеТовары = "
|Период с НП по КП;
|Товар = Документ.РасходнаяНакладная.Товар;
|ТекДокумент = Документ.РасходнаяНакладная.ТекущийДокумент;
|МестоХранения = Документ.РасходнаяНакладная.МестоХранения;
|Кво = Документ.РасходнаяНакладная.Кво;
|Функция КвоСумма = Сумма(Кво);
|Группировка Товар;
|Условие (ТекДокумент.Проведен()=1);";
// Добавление дополнительных фильтров по складам и товарам
Если ВыбСклад.РазмерСписка() <> 0 Тогда
ТекстЗапросаПроданныеТовары = ТекстЗапросаПроданныеТовары + "
|Условие (" + ?(НеСклад=1, "НЕ(МестоХранения в ВыбСклад)", "МестоХранения в ВыбСклад") + ");";
КонецЕсли;
Если ВыбТовар.РазмерСписка() <> 0 Тогда
ТекстЗапросаПроданныеТовары = ТекстЗапросаПроданныеТовары + "
|Условие (" + ?(НеТовар=1, "НЕ(Товар в ВыбТовар)", "Товар в ВыбТовар") + ");";
КонецЕсли;
ЗапросПродажи = СоздатьОбъект("Запрос");
Если ЗапросПродажи.Выполнить(ТекстЗапросаПроданныеТовары) = 0 Тогда
Возврат;
КонецЕсли;
// Заполнение списка значений проданными товарами
ПроданныеТовары = СоздатьОбъект("СписокЗначений");
Пока ЗапросПродажи.Группировка(1) = 1 Цикл
ПроданныеТовары.ДобавитьЗначение(ЗапросПродажи.Товар);
КонецЦикла;
// Добавление условия исключения в основной запрос
ТекстОсновногоЗапроса = ТекстОсновногоЗапроса + "
|Условие (НЕ (Товар в ПроданныеТовары));";
КонецЕсли;
Казалось бы, код написан абсолютно логично. Но при формировании отчета выясняется, что из него пропадают целые группы товаров, которые заведомо не продавались в указанном периоде. Выясним, почему так происходит.
Основная причина скрывается в поведении оператора принадлежности в платформы 1С:Предприятие 7.7. В отличие от современных версий платформы (например, 1С 8), где разработчик должен явно указывать использование иерархии (через конструкции типа В ИЕРАРХИИ), в "семерке" оператор в по умолчанию работает с учетом иерархии справочников.
Разберем этот механизм по шагам:
Группировка Товар;, платформа по умолчанию обходит справочник иерархически. Если во вспомогательную выборку попадает хотя бы одна папка (группа) номенклатуры, она добавляется в наш СписокЗначений под именем ПроданныеТовары.Товар в ПроданныеТовары. Из-за встроенной логики иерархического сравнения, если в списке ПроданныеТовары присутствует папка "Строительные материалы", то абсолютно любой товар, лежащий внутри этой папки (или её подпапок), будет признан системой как входящий в этот список.НЕ, истинное выражение превращается в ложное. В итоге, если за весь период была продана хотя бы одна позиция из папки, в список исключений попадает вся папка целиком, и система полностью исключает все товары данной категории из финального отчета!Именно поэтому у автора пропали товары вроде "брикета торфяного" — они просто находились в одной группе справочника с позициями, по которым были продажи.
Для исправления этой логической ошибки нам необходимо гарантировать, что во вспомогательный список будут попадать только конкретные элементы (элементы-детали), но не группы справочника. В языке запросов 1С 7.7 для этого предусмотрено специальное ключевое слово без групп.
Достаточно изменить строку группировки во вспомогательном запросе продаж следующим образом:
|Группировка Товар без групп;
Благодаря этому изменению вспомогательный запрос вернет исключительно конечные элементы номенклатуры. В список ПроданныеТовары больше не попадут папки, и условие отрицания в основном запросе отработает корректно, отфильтровав только реально проданные единицы товара.
Исправив работу с иерархией, разработчики часто сталкиваются со следующей проблемой: если в выбранном периоде вообще не было продаж (например, база новая или выбран очень узкий фильтр), то вспомогательный запрос вернет пустой результат. Наш объект СписокЗначений останется абсолютно пустым.
Казалось бы, если список исключений пуст, то условие НЕ (Товар в ПроданныеТовары) не должно ничего исключать, и отчет должен вывести все остатки. Но в 1С 7.7 пустой список в условиях запроса ведет себя аномально:
Товар в СписокЗначений при пустом списке часто трактуется платформой как Истина (или фильтр просто игнорируется, пропуская все элементы).НЕ (Товар в СписокЗначений) превращается в НЕ (Истина), то есть в Ложь для всех элементов.Для предотвращения этой критической ошибки всегда проверяйте размер списка перед модификацией текста основного запроса. Накладывать условие исключения нужно только тогда, когда в списке есть хотя бы один элемент:
// Безопасное добавление условия отрицания в 1С 7.7
Если ПроданныеТовары.РазмерСписка() > 0 Тогда
ТекстОсновногоЗапроса = ТекстОсновногоЗапроса + "
|Условие (НЕ (Товар в ПроданныеТовары));";
КонецЕсли;
Если список пуст, мы просто не добавляем строчку условия в запрос, и он отрабатывает в штатном режиме, возвращая все складские остатки.
Если ваша информационная база работает на СУБД MS SQL Server, использование отрицания в запросах встроенного языка 1С 7.7 может вызвать дополнительные трудности:
Условие (НЕ (Товар в Список)) в тяжелые SQL-запросы с использованием операторов NOT IN или сложных вложенных условий. На больших базах данных это приводит к катастрофическому падению скорости выполнения отчета и сильной нагрузке на сервер.Условие (ПроданныеТовары.Принадлежит(Товар) = 0). Однако помните, что вызов методов объектов (таких как СписокЗначений) внутри условий запроса заставляет платформу выполнять фильтрацию на стороне клиента. SQL-сервер выгрузит миллионы записей на рабочую станцию пользователя, и лишь затем локальная машина будет фильтровать их. Это гарантированно приведет к зависанию системы.1С++. Прямой запрос с использованием оператора LEFT JOIN к таблице продаж и последующей проверкой на NULL отработает в десятки и сотни раз быстрее встроенного механизма запросов.Если вы хотите написать максимально надежный код, который будет одинаково быстро и предсказуемо работать как на файловой версии (DBF), так и на SQL, без использования внешних компонент, воспользуйтесь процедурным методом обработки данных с помощью ТаблицыЗначений.
Разберем этот алгоритм по шагам:
ТаблицуЗначений.СписокЗначений (обязательно с использованием без групп).Посмотрим на пример практической реализации данного метода:
// Шаг 1. Получаем остатки товаров в Таблицу Значений
ЗапросОстатки = СоздатьОбъект("Запрос");
ТекстОстатки = "
|Период с НП по КП;
|Товар = Регистр.Остатки.Товар;
|Кво = Регистр.Остатки.Кво;
|Функция КвоНачОст = НачОст(Кво);
|Группировка Товар без групп;";
Если ЗапросОстатки.Выполнить(ТекстОстатки) = 0 Тогда
Возврат;
КонецЕсли;
ТЗ_Остатки = СоздатьОбъект("ТаблицаЗначений");
ЗапросОстатки.Выгрузить(ТЗ_Остатки, 1, 0); // Выгружаем с группировками
// Шаг 2. Получаем список проданных товаров за этот же период
ЗапросПродажи = СоздатьОбъект("Запрос");
ТекстПродажи = "
|Период с НП по КП;
|Товар = Документ.РасходнаяНакладная.Товар;
|Группировка Товар без групп;";
СписокПроданных = СоздатьОбъект("СписокЗначений");
Если ЗапросПродажи.Выполнить(ТекстПродажи) = 1 Тогда
Пока ЗапросПродажи.Группировка(1) = 1 Цикл
СписокПроданных.ДобавитьЗначение(ЗапросПродажи.Товар);
КонецЦикла;
КонецЕсли;
// Шаг 3. Фильтруем таблицу на клиенте (быстро и надежно)
Если СписокПроданных.РазмерСписка() > 0 Тогда
КолСтрок = ТЗ_Остатки.КоличествоСтрок();
Для НомерСтроки = -КолСтрок По -1 Цикл // Обход с конца таблицы для безопасного удаления
ТекСтрока = -НомерСтроки;
ТекущийТовар = ТЗ_Остатки.ПолучитьЗначение(ТекСтрока, "Товар");
Если СписокПроданных.Принадлежит(ТекущийТовар) = 1 Тогда
ТЗ_Остатки.УдалитьСтроку(ТекСтрока);
КонецЕсли;
КонецЦикла;
КонецЕсли;
// Теперь в ТЗ_Остатки остались только те товары, по которым не было продаж!
Использование обхода таблицы значений с конца (с отрицательным шагом) гарантирует, что индексы строк не "съедут" при удалении элементов. Этот метод полностью избавляет нас от багов встроенного парсера запросов 1С 7.7, связанных с отрицанием условий, и работает стабильно на любой конфигурации.
Для крупных баз данных на платформе SQL стандартные запросы 1С 7.7 часто становятся узким местом. Если вы используете внешнюю компоненту 1С++, то данную задачу можно решить с помощью прямого SQL-запроса. Это позволит выполнить фильтрацию непосредственно на стороне СУБД с максимальной скоростью.
Смысл метода заключается в связывании виртуальной таблицы остатков с таблицей продаж по левому соединению (LEFT JOIN) и отсечении записей, где в правой части получился NULL (то есть продаж не было). Проанализируем пример такого запроса:
// Пример прямого SQL-запроса через класс ODBCRecordset компоненты 1С++
БазаДанных = СоздатьОбъект("ODBCRecordset");
ТекстЗапросаSQL = "
|SELECT
| Рег.SP1234 AS [Товар $Справочник.Товар],
| SUM(Рег.SP5678) AS КоличествоОстаток
|FROM
| $РегистрИтоги.Остатки AS Рег
|LEFT JOIN
| (SELECT DISTINCT
| DocTab.SP9876 AS ТоварID
| FROM
| $ДокументСтроки.РасходнаяНакладная AS DocTab
| INNER JOIN
| _1SJOURN AS Journ ON DocTab.IDDOC = Journ.IDDOC
| WHERE
| Journ.DATE_TIME_IDDOC BETWEEN :НачДата AND :КонДата
| AND Journ.CLOSED & 1 = 1
| ) AS Sales ON Рег.SP1234 = Sales.ТоварID
|WHERE
| Sales.ТоварID IS NULL
|GROUP BY
| Рег.SP1234
|";
БазаДанных.УстановитьТекстовыйПараметр("НачДата", НачПериода);
БазаДанных.УстановитьТекстовыйПараметр("КонДата", КонПериода);
ТЗ_Результат = БазаДанных.ВыполнитьИнструкцию(ТекстЗапросаSQL);
Этот подход отрабатывает за доли секунды даже на миллионных справочниках номенклатуры, так как SQL-сервер строит оптимальный план выполнения запроса, используя индексы таблиц документов и регистров.