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