Одной из классических задач автоматизации торгового и складского учета является выявление залежавшихся товаров (неликвидов). Бизнесу крайне важно своевременно определять позиции, которые мертвым грузом лежат на складе и «замораживают» оборотные средства. Для решения этой задачи разработчику необходимо построить отчет, который выведет товары, не имевшие движений в течение последнего года.
На первый взгляд задача кажется простой, однако при ее решении начинающие разработчики 1С часто совершают грубые логические и технические ошибки. В этой статье мы подробно разберем, почему стандартные подходы «в лоб» не работают, проанализируем физический смысл виртуальных таблиц и построим правильный, оптимизированный запрос для решения этой задачи.
Рассмотрим ситуацию, с которой часто сталкиваются новички. Пытаясь найти товары без движений, разработчики обращаются к виртуальной таблице оборотов регистра накопления (например, ТоварыНаСкладах.Обороты) и накладывают условие равенства нулю прихода и расхода. Выглядит это примерно так:
ВЫБРАТЬ
ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК ТоварыНаСкладахОбороты
ГДЕ
ТоварыНаСкладахОбороты.КоличествоПриход = 0
И ТоварыНаСкладахОбороты.КоличествоРасход = 0
Выясним причину, почему данный запрос всегда будет возвращать пустой результат. Дело в особенностях работы СУБД и механизма виртуальных таблиц 1С. Виртуальная таблица оборотов формируется динамически на основе физических таблиц движений регистров. Если по товару за указанный период не было ни одной записи (ни прихода, ни расхода), то СУБД просто не создаст строк для этой номенклатуры в результирующей выборке. В этой таблице физически отсутствуют строки с нулевыми оборотами. Там содержатся только те позиции, по которым были реальные движения.
Вторая распространенная ошибка — попытка использовать виртуальную таблицу остатков и оборотов (ТоварыНаСкладах.ОстаткиИОбороты). Разработчики полагают, что раз в таблице есть остатки, то мы сможем увидеть товары, у которых начальный и конечный остаток равны, а обороты нулевые.
Проанализируем эту ситуацию подробнее. С помощью таблицы остатков и оборотов мы действительно сможем получить товары без движений, но только те, по которым на складе в данный момент есть остаток. Если мы отгрузили последнюю единицу какого-то товара полтора года назад (остаток стал нулевым) и с тех пор движений не было, этот товар в выборку не попадет. С точки зрения бизнеса это тоже залежалый товар, который, возможно, больше не стоит закупать, но в отчете мы его не увидим.
Чтобы гарантированно получить все товары, по которым не было движений, мы должны отталкиваться не от регистра, а от первоисточника — справочника Номенклатура. Мы берем весь список товаров и левым соединением связываем его с таблицей оборотов за интересующий период (в нашем случае — за последний год).
Разберем этот алгоритм по шагам:
Номенклатура.Ссылка = Номенклатура.ГДЕ накладываем условие, что поле номенклатуры из таблицы оборотов равно NULL. Это означает, что для данного товара в регистре за год не нашлось ни одной записи.Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Товар
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК ТоварыОбороты
ПО Номенклатура.Ссылка = ТоварыОбороты.Номенклатура
ГДЕ
ТоварыОбороты.Номенклатура ЕСТЬ NULL
И НЕ Номенклатура.ЭтоГруппа
Этот шаблон является базовым и рабочим. Если в указанный период по товару не было ни приходов, ни расходов, левое соединение вернет значение NULL в колонках таблицы оборотов, и фильтр ЕСТЬ NULL выведет эту позицию в отчет.
Существует альтернативный вариант решения, который отлично подходит для построения гибких отчетов, где пользователю нужно показать не просто факт отсутствия движения, но и конкретную дату, когда товар двигался в последний раз.
Идея заключается в том, чтобы обратиться к таблице оборотов без ограничения по периоду «сверху» (или за все время существования базы), сгруппировать данные по номенклатуре и найти максимальную дату движения. Затем мы фильтруем полученную таблицу, оставляя только те записи, у которых максимальная дата движения меньше, чем год назад от даты отчета.
Рассмотрим пример запроса:
ВЫБРАТЬ
ТоварыОбороты.Номенклатура КАК Товар,
МАКСИМУМ(ТоварыОбороты.Период) КАК ДатаПоследнегоДвижения
ПОМЕСТИТЬ ВТ_ПоследниеДвижения
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(, , День, ) КАК ТоварыОбороты
СГРУППИРОВАТЬ ПО
ТоварыОбороты.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ПоследниеДвижения.Товар КАК Товар,
ВТ_ПоследниеДвижения.ДатаПоследнегоДвижения КАК ДатаПоследнегоДвижения
ИЗ
ВТ_ПоследниеДвижения КАК ВТ_ПоследниеДвижения
ГДЕ
ВТ_ПоследниеДвижения.ДатаПоследнегоДвижения < ДОБАВИТЬКДАТЕ(&ДатаОтчета, ГОД, -1)
Этот метод дает больше информативности, однако он может работать медленнее на огромных базах данных, так как СУБД приходится анализировать обороты за весь период существования предприятия для поиска максимальной даты.
В реальной практике enterprise-разработки на платформе 1С написания простого левого соединения недостаточно. Чтобы отчет работал быстро, не вешал базу данных и возвращал корректные с точки зрения бизнеса результаты, нам необходимо учесть несколько критически важных факторов.
1. Проблема услуг, тары и помеченных на удаление элементов.
В справочнике номенклатуры любой компании хранятся услуги (например, «Доставка», «Установка»), работы, возвратная тара и огромное количество элементов, помеченных на удаление или являющихся группами (папками). Услуги физически не могут иметь остатков или движений по складу. Если мы выполним простое соединение всего справочника, отчет заполнится тысячами «залежавшихся» услуг и мусорных позиций. Нам необходимо жестко фильтровать справочник.
2. Бизнес-смысл: «Складские движения» против «Продаж».
Что бизнес понимает под «товаром без движения»? Обычно это неликвид, который не продается. Если товар в течение года перемещался с одного склада на другой (документ ПеремещениеТоваров) или списывался/оприходовался по результатам инвентаризации, регистр ТоварыНаСкладах зафиксирует движения. В результате товар пропадет из отчета, хотя реальных продаж покупателям по нему не было. Для поиска настоящих неликвидов правильнее анализировать регистры продаж (например, ВыручкаИСебестоимостьПродаж в ERP/КА/УТ11 или Продажи в УТ10).
3. Связка с остатками (Замороженные деньги).
Для руководства компании не представляют интереса товары, по которым не было движений, если их текущий остаток на складе равен нулю. Они не занимают места на складе и в них не вложены деньги. Нам нужно анализировать только те позиции, которые имеют положительный остаток на текущий момент, но при этом по ним не было продаж за последний год.
4. Оптимизация производительности (Временные таблицы).
Прямое левое соединение многотысячного справочника номенклатуры с виртуальными таблицами СУБД в одном запросе может вызвать колоссальную нагрузку на сервер. Мы должны использовать временные таблицы с обязательным индексированием связующих полей.
Проанализируем профессиональный шаблон запроса, который учитывает все вышеперечисленные требования бизнеса и правила оптимизации производительности СУБД:
// Шаг 1: Выбираем только интересующие нас товары, которые сейчас реально лежат на остатках
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.Характеристика КАК Характеристика,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК Остаток
ПОМЕСТИТЬ ВТ_ТоварыНаОстатках
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаОтчета, ) КАК ТоварыНаСкладахОстатки
ГДЕ
ТоварыНаСкладахОстатки.КоличествоОстаток > 0
И НЕ ТоварыНаСкладахОстатки.Номенклатура.ПометкаУдаления
И НЕ ТоварыНаСкладахОстатки.Номенклатура.ЭтоГруппа
// Отсекаем услуги и работы, оставляем только физические товары
И ТоварыНаСкладахОстатки.Номенклатура.ТипНоменклатуры = ЗНАЧЕНИЕ(Перечисление.ТипыНоменклатуры.Товар)
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Характеристика
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выбираем товары, по которым были РЕАЛЬНЫЕ ПРОДАЖИ за последний год
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВыручкаОбороты.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
ВыручкаОбороты.АналитикаУчетаНоменклатуры.Характеристика КАК Характеристика
ПОМЕСТИТЬ ВТ_ТоварыСПродажами
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериодаГОД, &ДатаОтчета, , ) КАК ВыручкаОбороты
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Характеристика
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем остатки с продажами и выявляем неликвид (где продажи ЕСТЬ NULL)
ВЫБРАТЬ
ВТ_ТоварыНаОстатках.Номенклатура КАК Номенклатура,
ВТ_ТоварыНаОстатках.Характеристика КАК Характеристика,
ВТ_ТоварыНаОстатках.Остаток КАК ОстатокНаСкладе
ИЗ
ВТ_ТоварыНаОстатках КАК ВТ_ТоварыНаОстатках
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТоварыСПродажами КАК ВТ_ТоварыСПродажами
ПО ВТ_ТоварыНаОстатках.Номенклатура = ВТ_ТоварыСПродажами.Номенклатура
И ВТ_ТоварыНаОстатках.Характеристика = ВТ_ТоварыСПродажами.Характеристика
ГДЕ
ВТ_ТоварыСПродажами.Номенклатура ЕСТЬ NULL
АВТОУПОРЯДОЧИВАНИЕ
Давайте разберем преимущества данного решения:
Номенклатура и Характеристика, что обеспечивает моментальное выполнение левого соединения в СУБД.Используя предложенные методики, мы можем гибко настраивать отчеты по неликвидам, адаптируя их под индивидуальные требования заказчика и обеспечивая высокую скорость работы системы даже на базах данных объемом в терабайты.