Расчет с помощью СКД остатков регистра по реквизиту регистратора

Программирование - Практика программирования

При построении различных ОСВ-подобных отчетов из регистра бухгалтерии посредством СКД, мы можем строить отчеты, группируя данные по измерениям регистра, субконто и периодам. При этом СКД практически все делает сама, главное, чтобы были правильно расставлены роли. Но что делать, если группировкой выступает, например, реквизит документа-регистратора, который не является измерением? Столкнувшись с такой задачей, мне не удалось найти готового решения (или плохо искал), и я хочу поделиться своим решением в этой статье.

Несмотря на то, что платформа 1С способна многое делать сама, иногда возникают ситуации, которые сложно предусмотреть - например, расчет ОСВ по несуществующему измерению, или субконто. В случае, когда еще одним субконто выступает, например, реквизит документа-регистратора. Остатки посчитаны и хранятся в регистрах в соответствии с тем, как это предусмотрено разработчиками конфигурации. В случае, если мы попытаемся получить что-то вроде

ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Регистратор КАК Регистратор,
    ХозрасчетныйОстаткиИОбороты.Регистратор.Договор КАК Договор,
    ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Субконто1,
    ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Субконто2,
    ХозрасчетныйОстаткиИОбороты.Субконто3 КАК Субконто3,
    ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстаток КАК СуммаНачальныйОстаток,
    ХозрасчетныйОстаткиИОбороты.СуммаОборот КАК СуммаОборот,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(, , Авто, , , , ) КАК ХозрасчетныйОстаткиИОбороты

нас ждет неудача - остатков для этого псевдо-субконто (Договор) нет и отчет покажет совершенно невообразимые остатки на начало и конец.

Поэтому начнем с расчета остатков по этому новому измерению

ВЫБРАТЬ
    ВЫБОР
        КОГДА ХозрасчетныйОбороты.Период < &ДатаНачала
            ТОГДА &ДатаНачала
        ИНАЧЕ ХозрасчетныйОбороты.Период
    КОНЕЦ КАК Период,
    ВЫБОР
        КОГДА ХозрасчетныйОбороты.Период < &ДатаНачала
            ТОГДА NULL
        ИНАЧЕ ХозрасчетныйОбороты.Регистратор
    КОНЕЦ КАК Регистратор,
    ЕСТЬNULL(ХозрасчетныйОбороты.Регистратор.Договор, "Без договора") КАК Договор,
    ХозрасчетныйОбороты.Субконто1 КАК Субконто1,
    ХозрасчетныйОбороты.Субконто2 КАК Субконто2,
    ХозрасчетныйОбороты.Организация КАК Организация,
    ХозрасчетныйОбороты.СуммаОборотДт КАК СуммаОборотДт,
    ХозрасчетныйОбороты.СуммаОборотКт КАК СуммаОборотКт,
    ХозрасчетныйОбороты.КоличествоОборотДт КАК КоличествоОборотДт,
    ХозрасчетныйОбороты.КоличествоОборотКт КАК КоличествоОборотКт
ПОМЕСТИТЬ ВТ_Обороты
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Обороты(, &ДатаОкончания, Регистратор, Счет В ИЕРАРХИИ (&Счет), , {(Организация = &Организация), (Подразделение = &Подразделение), (Субконто1 = &Субконто1), (Субконто2 = &Субконто2)}, , ) КАК ХозрасчетныйОбороты

ИНДЕКСИРОВАТЬ ПО
    Период,
    Субконто1,
    Субконто2,
    Организация

Как видите, формирование Остатков идет через Обороты - поскольку наше псевдо-субконто не определено типовым способом, то и остатки по нему нам придется считать с начала времен - да, вариант не самый лучший, но по-другому, увы, никак. Сами регистраторы вне рамок периода отчета нас не интересуют, поэтому сразу группируем до интересующего нас реквизита

ВЫБОР
	КОГДА ХозрасчетныйОбороты.Период < &ДатаНачала
		ТОГДА &ДатаНачала
	ИНАЧЕ ХозрасчетныйОбороты.Период
КОНЕЦ КАК Период,
ВЫБОР
	КОГДА ХозрасчетныйОбороты.Период < &ДатаНачала
		ТОГДА NULL
	ИНАЧЕ ХозрасчетныйОбороты.Регистратор
КОНЕЦ КАК Регистратор,

Теперь, имея таблицу оборотов, рассчитаем Остатки на начало для каждой записи таблицы оборотов. В первой части запроса получаем остатки для расчета остатков на начало периода отчета, во второй для расчета остатков для каждого регистратора.

ВЫБРАТЬ
	ВТ_Обороты.Период КАК Период,
	ВТ_Обороты.Регистратор КАК Регистратор,
	ВТ_Обороты.Договор КАК Договор,
	ВТ_Обороты.Субконто1 КАК Субконто1,
	ВТ_Обороты.Субконто2 КАК Субконто2,
	ВТ_Обороты.Организация КАК Организация,
	ЕСТЬNULL(ВТ_Обороты.КоличествоОборотДт, 0) - ЕСТЬNULL(ВТ_Обороты.КоличествоОборотКт, 0) КАК КоличествоОстатокНачало,
	ЕСТЬNULL(ВТ_Обороты.СуммаОборотДт, 0) - ЕСТЬNULL(ВТ_Обороты.СуммаОборотКт, 0) КАК СуммаОстатокНачало,
	ВТ_Обороты.КоличествоОборотДт КАК КоличествоОборотДт,
	ВТ_Обороты.СуммаОборотДт КАК СуммаОборотДт,
	ВТ_Обороты.КоличествоОборотКт КАК КоличествоОборотКт,
	ВТ_Обороты.СуммаОборотКт КАК СуммаОборотКт
ПОМЕСТИТЬ ВТ_НачальныеОстаткиИОбороты
ИЗ
	ВТ_Обороты КАК ВТ_Обороты
ГДЕ
	ВТ_Обороты.Регистратор ЕСТЬ NULL

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ВТ_Обороты.Период,
	ВТ_Обороты.Регистратор,
	ВТ_Обороты.Договор,
	ВТ_Обороты.Субконто1,
	ВТ_Обороты.Субконто2,
	ВТ_Обороты.Организация,
	СУММА(ЕСТЬNULL(ВТ_ОборотыДляРасчетаОстатков.КоличествоОборотДт, 0) - ЕСТЬNULL(ВТ_ОборотыДляРасчетаОстатков.КоличествоОборотКт, 0)),
	СУММА(ЕСТЬNULL(ВТ_ОборотыДляРасчетаОстатков.СуммаОборотДт, 0) - ЕСТЬNULL(ВТ_ОборотыДляРасчетаОстатков.СуммаОборотКт, 0)),
	ВТ_Обороты.КоличествоОборотДт,
	ВТ_Обороты.СуммаОборотДт,
	ВТ_Обороты.КоличествоОборотКт,
	ВТ_Обороты.СуммаОборотКт
ИЗ
	ВТ_Обороты КАК ВТ_Обороты
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Обороты КАК ВТ_ОборотыДляРасчетаОстатков
		ПО (ВТ_ОборотыДляРасчетаОстатков.Период <= ВТ_Обороты.Период)
			И (ВТ_ОборотыДляРасчетаОстатков.Договор = ВТ_Обороты.Договор)
			И (ВТ_ОборотыДляРасчетаОстатков.Субконто1 = ВТ_Обороты.Субконто1)
			И (ВТ_ОборотыДляРасчетаОстатков.Субконто2 = ВТ_Обороты.Субконто2)
			И (ВТ_ОборотыДляРасчетаОстатков.Организация = ВТ_Обороты.Организация)
			И (ВТ_ОборотыДляРасчетаОстатков.Регистратор <> ВТ_Обороты.Регистратор
				ИЛИ ВТ_ОборотыДляРасчетаОстатков.Регистратор ЕСТЬ NULL)
ГДЕ
	ВТ_Обороты.Регистратор ЕСТЬ НЕ NULL 

СГРУППИРОВАТЬ ПО
	ВТ_Обороты.Период,
	ВТ_Обороты.Регистратор,
	ВТ_Обороты.Договор,
	ВТ_Обороты.Субконто1,
	ВТ_Обороты.Субконто2,
	ВТ_Обороты.Организация,
	ВТ_Обороты.СуммаОборотДт,
	ВТ_Обороты.СуммаОборотКт,
	ВТ_Обороты.КоличествоОборотДт,
	ВТ_Обороты.КоличествоОборотКт

Теперь получим конечную таблицу, содержащую остатки и обороты. Запрос разбит на 2 части - в первой у нас остатки на начало периода для тех наборов группируемых полей(Организация, Субконто1, Субконто2, Договор), у кого не было оборотов в периоде отчета, во второй для тех наборов, у кого движения есть.

ВЫБРАТЬ
	ВТ_НачальныеОстаткиИОбороты.Период КАК Период,
	ВТ_НачальныеОстаткиИОбороты.Регистратор КАК Регистратор,
	ВТ_НачальныеОстаткиИОбороты.Договор КАК Договор,
	ВТ_НачальныеОстаткиИОбороты.Субконто1 КАК Субконто1,
	ВТ_НачальныеОстаткиИОбороты.Субконто2 КАК Субконто2,
	ВТ_НачальныеОстаткиИОбороты.Организация КАК Организация,
	ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОборотДт, 0) - ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОборотКт, 0) КАК КоличествоОстатокНачало,
	ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОборотДт, 0) - ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОборотКт, 0) КАК СуммаОстатокНачало,
	0 КАК КоличествоОборотДт,
	0 КАК СуммаОборотДт,
	0 КАК КоличествоОборотКт,
	0 КАК СуммаОборотКт,
	ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОборотДт, 0) - ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОборотКт, 0) КАК КоличествоОстатокКонец,
	ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОборотДт, 0) - ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОборотКт, 0) КАК СуммаОстатокКонец
{ВЫБРАТЬ
	Период,
	Регистратор.*,
	Договор.*,
	Субконто1.*,
	Субконто2.*,
	Организация.*,
	КоличествоОстатокНачало,
	СуммаОстатокНачало,
	КоличествоОборотДт,
	СуммаОборотДт,
	КоличествоОборотКт,
	СуммаОборотКт,
	КоличествоОстатокКонец,
	СуммаОстатокКонец}
ИЗ
	ВТ_НачальныеОстаткиИОбороты КАК ВТ_НачальныеОстаткиИОбороты
ГДЕ
	ВТ_НачальныеОстаткиИОбороты.Регистратор ЕСТЬ NULL
	И НЕ (ВТ_НачальныеОстаткиИОбороты.Договор, ВТ_НачальныеОстаткиИОбороты.Субконто1, ВТ_НачальныеОстаткиИОбороты.Субконто2, ВТ_НачальныеОстаткиИОбороты.Организация) В
				(ВЫБРАТЬ
					ВТ_Обороты.Договор,
					ВТ_Обороты.Субконто1,
					ВТ_Обороты.Субконто2,
					ВТ_Обороты.Организация
				ИЗ
					ВТ_Обороты
				ГДЕ
					ВТ_Обороты.Регистратор ЕСТЬ НЕ NULL )

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ВТ_НачальныеОстаткиИОбороты.Период,
	ВТ_НачальныеОстаткиИОбороты.Регистратор,
	ВТ_НачальныеОстаткиИОбороты.Договор,
	ВТ_НачальныеОстаткиИОбороты.Субконто1,
	ВТ_НачальныеОстаткиИОбороты.Субконто2,
	ВТ_НачальныеОстаткиИОбороты.Организация,
	ВТ_НачальныеОстаткиИОбороты.КоличествоОстатокНачало,
	ВТ_НачальныеОстаткиИОбороты.СуммаОстатокНачало,
	ВТ_НачальныеОстаткиИОбороты.КоличествоОборотДт,
	ВТ_НачальныеОстаткиИОбороты.СуммаОборотДт,
	ВТ_НачальныеОстаткиИОбороты.КоличествоОборотКт,
	ВТ_НачальныеОстаткиИОбороты.СуммаОборотКт,
	ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОстатокНачало, 0) + ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОборотДт, 0) - ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.КоличествоОборотКт, 0),
	ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОстатокНачало, 0) + ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОборотДт, 0) - ЕСТЬNULL(ВТ_НачальныеОстаткиИОбороты.СуммаОборотКт, 0)
ИЗ
	ВТ_НачальныеОстаткиИОбороты КАК ВТ_НачальныеОстаткиИОбороты
ГДЕ
	ВТ_НачальныеОстаткиИОбороты.Регистратор ЕСТЬ НЕ NULL 

УПОРЯДОЧИТЬ ПО
	Период

Определим роли полей

Сформируем отчет

См. также

Комментарии
1. Петр Малыгин (pm74) 61 18.05.18 15:01 Сейчас в теме
(0) если в измерении указать родителя разве неправильно считает ?
2. Александр Мясников (bomber99544) 7 18.05.18 15:18 Сейчас в теме
(1)
да этот вариант я тоже пробовал - но ситуацию он не спас. Либо я не умею его готовить.
Этот способ работает с реквизитом измерения.
3. Дмитрий Касминюк (Vortigaunt) 16 21.05.18 22:42 Сейчас в теме
А не правильнее ли для такой задачи добавить регистр остатков с нужными измерениями: Организация, Контрагент, Договор, Номенклатура? Подписки на все влияющие документы. И обработкой заполнить движения по существующим документам.
Описанное решение прикольное, но боюсь будет сильно тупить на больших объемах данных. Ведь автор указал что
Как видите, формирование Остатков идет через Обороты - поскольку наше псевдо-субконто не определено типовым способом, то и остатки по нему нам придется считать с начала времен - да, вариант не самый лучший, но по-другому, увы, никак.
4. Александр Мясников (bomber99544) 7 22.05.18 09:02 Сейчас в теме
(3)
условия задачи были - не менять конфигурацию
5. Николай Беляев (freez1301) 216 24.05.18 08:39 Сейчас в теме
При все уважении, но Вы понимаете что будет на уровне СУБД, когда Вы пишите следующее?
ХозрасчетныйОбороты.Регистратор.Договор
6. Александр Мясников (bomber99544) 7 24.05.18 09:26 Сейчас в теме
(5)
что то типа
sel ect
registr.registrator,
documents.dogovor
from registr
left join (sel ect registrator, dogovor fr om document1
unuion
select registrator, dogovor fr om document2
union
......) as documents
on registr.registrator = documents.registrator

я заказчика предупредил что метод крайне нагружающий СУБД, но ему надо только так.
Заказчик кстати - франч.
7. Николай Беляев (freez1301) 216 24.05.18 15:12 Сейчас в теме
(6) да, но есть же выразить...
8. Александр Мясников (bomber99544) 7 24.05.18 18:44 Сейчас в теме
(7)
реквизит вроде как не составного типа, мне кажется тут ВЫРАЗИТЬ будет как мертвому припарки
9. Николай Беляев (freez1301) 216 25.05.18 09:15 Сейчас в теме
(8) Регистратор не составного?
10. Александр Мясников (bomber99544) 7 25.05.18 10:22 Сейчас в теме
(9)
регистратор понятно . Вы же предлагаете реквизит регистратора через ВЫРАЗИТЬ.
Оставьте свое сообщение