Разработчики 1С часто сталкиваются со специфической задачей: необходимо построить отчет по остаткам и оборотам номенклатуры (или других разрезов учета), полностью исключив при этом влияние документов определенного типа или конкретного регистратора. Например, когда внутренние перемещения между виртуальными складами не должны искажать общую картину оборотов компании, но при этом конечный и начальный остатки должны оставаться математически верными.
В этой статье мы подробно разберем, почему стандартные и интуитивно понятные подходы в данном случае приводят к неверным результатам, и рассмотрим профессиональные способы решения этой задачи с помощью языка запросов 1С и механизмов компоновки данных.
Рассмотрим классическую ошибку. Начинающий разработчик пытается решить задачу «в лоб», накладывая условие исключения регистратора непосредственно в секции ГДЕ виртуальной таблицы остатков и оборотов:
ВЫБРАТЬ
Товары.Склад,
Товары.Номенклатура,
Товары.КоличествоНачальныйОстаток,
Товары.КоличествоПриход,
Товары.КоличествоРасход,
Товары.КоличествоКонечныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НП, &КП, Регистратор, , ) КАК Товары
ГДЕ
НЕ (Товары.Регистратор ССЫЛКА Документ.ПеремещениеТоваров
И Товары.Регистратор.СкладОтправитель = Товары.Регистратор.СкладПолучатель)
Проанализируем ситуацию: почему этот запрос вернет некорректные остатки? Дело в том, как платформа 1С хранит и рассчитывает данные в регистрах накопления. Остатки физически хранятся в СУБД только в разрезе измерений регистра (например, Склад, Номенклатура). Поле Регистратор не является измерением — это системный реквизит физической таблицы движений.
Когда мы обращаемся к виртуальной таблице ОстаткиИОбороты с периодичностью Регистратор, платформа сначала рассчитывает остатки на контрольные точки времени (границы документов), а уже затем накладывает фильтр из секции ГДЕ. В результате:
Таким образом, просто отсечь «ненужный» регистратор фильтром в виртуальной таблице невозможно. Требуется применить один из описанных ниже методов обхода этого ограничения.
Это наиболее быстрый и производительный метод для СУБД. Вместо того чтобы пересчитывать всю историю движений с «начала времен», мы берем уже готовые (верные) остатки из регистра и математически вычитаем из них обороты, которые сделал исключаемый документ.
Разберем алгоритм по шагам:
ОБЪЕДИНИТЬ ВСЕ и производим расчет итоговых полей.Посмотрим на пример практической реализации этого запроса:
ВЫБРАТЬ
ИсходныеДанные.Склад КАК Склад,
ИсходныеДанные.Номенклатура КАК Номенклатура,
ИсходныеДанные.НачальныйОстаток КАК НачальныйОстаток,
ИсходныеДанные.КонечныйОстаток КАК КонечныйОстаток,
ИсходныеДанные.Приход КАК Приход,
ИсходныеДанные.Расход КАК Расход
ПОМЕСТИТЬ ВТ_ВсеДанные
ИЗ
(ИЗБЫТОЧНЫЙ_ЗАПРОС_ПО_РЕГИСТРУ) // Шаг 1: Получаем стандартные остатки и обороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ИсключаемыеДвижения.Склад КАК Склад,
ИсключаемыеДвижения.Номенклатура КАК Номенклатура,
// Если движение было ДО начала периода, оно уменьшает/увеличивает начальный остаток
ВЫБОР
КОГДА ИсключаемыеДвижения.Период < &НП
ТОГДА ВЫБОР
КОГДА ИсключаемыеДвижения.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
ТОГДА -ИсключаемыеДвижения.Количество
ИНАЧЕ ИсключаемыеДвижения.Количество
КОНЕЦ
ИНАЧЕ 0
КОНЕЦ КАК КорректировкаНачала,
// Если движение было ВНУТРИ периода, оно корректирует обороты
ВЫБОР
КОГДА ИсключаемыеДвижения.Период МЕЖДУ &НП И &КП И ИсключаемыеДвижения.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
ТОГДА ИсключаемыеДвижения.Количество
ИНАЧЕ 0
КОНЕЦ КАК ИсключаемыйПриход,
ВЫБОР
КОГДА ИсключаемыеДвижения.Период МЕЖДУ &НП И &КП И ИсключаемыеДвижения.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Расход)
ТОГДА ИсключаемыеДвижения.Количество
ИНАЧЕ 0
КОНЕЦ КАК ИсключаемыйРасход
ПОМЕСТИТЬ ВТ_Исключения
ИЗ
РегистрНакопления.ТоварыНаСкладах КАК ИсключаемыеДвижения
ГДЕ
ИсключаемыеДвижения.Регистратор ССЫЛКА Документ.ПеремещениеТоваров
И ИсключаемыеДвижения.Регистратор.СкладОтправитель = ИсключаемыеДвижения.Регистратор.СкладПолучатель
И ИсключаемыеДвижения.Период < &КП
;
////////////////////////////////////////////////////////////////////////////////
// Финальное объединение и расчет скорректированных показателей
ВЫБРАТЬ
ОбъединенныеДанные.Склад КАК Склад,
ОбъединенныеДанные.Номенклатура КАК Номенклатура,
СУММА(ОбъединенныеДанные.НачальныйОстаток) КАК НачальныйОстаток,
СУММА(ОбъединенныеДанные.КонечныйОстаток) КАК КонечныйОстаток,
СУММА(ОбъединенныеДанные.Приход) КАК Приход,
СУММА(ОбъединенныеДанные.Расход) КАК Расход
ИЗ
(ВЫБРАТЬ
ВТ_ВсеДанные.Склад КАК Склад,
ВТ_ВсеДанные.Номенклатура КАК Номенклатура,
ВТ_ВсеДанные.НачальныйОстаток КАК НачальныйОстаток,
ВТ_ВсеДанные.КонечныйОстаток КАК КонечныйОстаток,
ВТ_ВсеДанные.Приход КАК Приход,
ВТ_ВсеДанные.Расход КАК Расход
ИЗ
ВТ_ВсеДанные КАК ВТ_ВсеДанные
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ_Исключения.Склад,
ВТ_Исключения.Номенклатура,
ВТ_Исключения.КорректировкаНачала, // Корректируем начальный остаток
ВТ_Исключения.КорректировкаНачала - ВТ_Исключения.ИсключаемыйПриход + ВТ_Исключения.ИсключаемыйРасход, // Корректируем конечный остаток
-ВТ_Исключения.ИсключаемыйПриход,
-ВТ_Исключения.ИсключаемыйРасход
ИЗ
ВТ_Исключения КАК ВТ_Исключения) КАК ОбъединенныеДанные
СГРУППИРОВАТЬ ПО
ОбъединенныеДанные.Склад,
ОбъединенныеДанные.Номенклатура
Преимущества метода: СУБД использует индексы по полям регистра и периоду, что обеспечивает высокое быстродействие даже на больших объемах данных.
Если вам требуется детальный отчет (например, ведомость движений с остатками после каждого документа), предыдущий способ не подойдет. Здесь придется рассчитывать остатки нарастающим итогом «вручную».
Для этого мы полностью отказываемся от виртуальных таблиц остатков и обращаемся напрямую к физической таблице движений регистра РегистрНакопления.ТоварыНаСкладах.
Рассмотрим схему реализации в запросе:
Остатки.ГДЕ НЕ Регистратор ССЫЛКА Документ.ПеремещениеТоваров...Период текущей записи >= Период предыдущих записей, чтобы рассчитать сумму накопленным итогом для каждой строки.Внимание! Соединение таблицы саму с собой для расчета нарастающего итога — крайне тяжелая операция для СУБД. На базах данных с миллионами записей такой запрос вызовет критическое падение производительности или переполнение базы tempdb.
Если получить данные исключительно запросом не удается из-за падения производительности (как в Способе №2), оптимальным решением будет перенос расчета нарастающего итога на сторону сервера приложений 1С с использованием встроенного языка.
Разберем, как сделать это эффективно:
ГДЕ НЕ....Таблицу Значений и в цикле рассчитываем остаток для каждой строки.Посмотрим на пример программного кода:
// 1. Получаем начальный остаток и движения одним пакетом запросов
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТоварыОстатки.Склад КАК Склад,
| ТоварыОстатки.Номенклатура КАК Номенклатура,
| ТоварыОстатки.КоличествоОстаток КАК КоличествоОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(&НП, ) КАК ТоварыОстатки
|;
|
|ВЫБРАТЬ
| ТоварыДвижения.Период КАК Период,
| ТоварыДвижения.Регистратор КАК Регистратор,
| ТоварыДвижения.Склад КАК Склад,
| ТоварыДвижения.Номенклатура КАК Номенклатура,
| ТоварыДвижения.ВидДвижения КАК ВидДвижения,
| ТоварыДвижения.Количество КАК Количество
|ИЗ
| РегистрНакопления.ТоварыНаСкладах КАК ТоварыДвижения
|ГДЕ
| ТоварыДвижения.Период МЕЖДУ &НП И &КП
| И НЕ (ТоварыДвижения.Регистратор ССЫЛКА Документ.ПеремещениеТоваров
| И ТоварыДвижения.Регистратор.СкладОтправитель = ТоварыДвижения.Регистратор.СкладПолучатель)
|УПОРЯДОЧИТЬ ПО
| Период";
Запрос.УстановитьПараметр("НП", НачалоПериода);
Запрос.УстановитьПараметр("КП", КонецПериода);
ПакетРезультатов = Запрос.ВыполнитьПакет();
ТаблицаОстатков = ПакетРезультатов[0].Выгрузить();
ТаблицаДвижений = ПакетРезультатов[1].Выгрузить();
// Добавим колонку под расчетный остаток
ТаблицаДвижений.Колонки.Добавить("ТекущийОстаток", Новый ОписаниеТипов("Число"));
// 2. Рассчитываем остатки в цикле
Для Каждого СтрокаДвижения Из ТаблицаДвижений Цикл
// Ищем начальный остаток для текущего склада и номенклатуры
ПараметрыОтбора = Новый Структура("Склад, Номенклатура", СтрокаДвижения.Склад, СтрокаДвижения.Номенклатура);
НайденныеСтроки = ТаблицаОстатков.НайтиСтроки(ПараметрыОтбора);
НачОстаток = 0;
Если НайденныеСтроки.Количество() > 0 Тогда
НачОстаток = НайденныеСтроки[0].КоличествоОстаток;
КонецЕсли;
// Вычисляем новое значение остатка
Если СтрокаДвижения.ВидДвижения = ВидДвиженияНакопления.Приход Тогда
НовыйОстаток = НачОстаток + СтрокаДвижения.Количество;
Иначе
НовыйОстаток = НачОстаток - СтрокаДвижения.Количество;
КонецЕсли;
СтрокаДвижения.ТекущийОстаток = НовыйОстаток;
// Обновляем таблицу остатков для последующих строк
Если НайденныеСтроки.Количество() > 0 Тогда
НайденныеСтроки[0].КоличествоОстаток = НовыйОстаток;
Иначе
НоваяСтрокаОстатка = ТаблицаОстатков.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрокаОстатка, СтрокаДвижения);
НоваяСтрокаОстатка.КоличествоОстаток = НовыйОстаток;
КонецЕсли;
КонецЦикла;
Этот метод работает на порядок быстрее, чем сложные левые соединения в SQL-запросах, и гарантирует линейную масштабируемость системы при росте базы данных.
Если вы разрабатываете отчет на базе СКД, важно учитывать особенности расчета остатков самой системой компоновки:
ПериодСекунда (или сам Регистратор), и у него должна быть корректно настроена роль "Период" в схеме компоновки.Сложные математические изыскания по вычитанию документов из остатков на лету часто являются следствием ошибочного проектирования архитектуры метаданных. Если определенные документы (например, перемещения на тот же самый склад) не должны влиять на остатки и обороты, то наиболее правильным решением будет:
ТоварыНаСкладах просто не записывались. Это полностью исключит проблему "холостых" оборотов.ЗонаСклада или ВидЗапасов). В таком случае вы сможете использовать стандартные быстрые отборы по измерениям виртуальных таблиц без написания сложных и неоптимальных запросов.