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