Еще одна таблица
Регистры бухгалтерии - один из самых сложных объектов платформы 1С, как уже говорилось ранее. В прошлой статье мы говорили о таблице оборотов ДТ / КТ и продолжили серию публикаций об этом виде регистров.
Сегодня мы рассмотрим одну из двух оставшихся виртуальных таблиц - таблицу "Остатки" и некоторые нюансы формируемых запросов к базе данных.
Также мы коснемся некоторых вопросов производительности таких запросов и сформируем общие рекомендации по их использованию.
Все данные для публикации получены с помощью инструментов:
На практике виртуальная таблица остатков используются очень часто. В той же типовой конфигурации "Бухгалтерия предприятия 3.0" ее использование можно встретить в таких отчетах как:
- Карточка счета
- Карточка субконто
- Остатки товаров
- Остатки денежных средств
- Задолженность поставщикам
- Задолженность покупателей
- Движение товаров
- И многие другие.
В общем, эта виртуальная таблица используется очень часто как в отчетах бухгалтерского учета, так и в алгоритмах. Итак, поехали дальше!
Просто получаем остатки
Начнем с простого запроса - получим остаток по конкретному счету с отбором по организации. Вот так этот запрос выглядит на языке запросов 1С.
ВЫБРАТЬ
ХозрасчетныйОстатки.Организация КАК Организация,
ХозрасчетныйОстатки.Счет КАК Счет,
ХозрасчетныйОстатки.СуммаОстаток КАК СуммаОстаток,
ХозрасчетныйОстатки.СуммаОстатокДт КАК СуммаОстатокДт,
ХозрасчетныйОстатки.СуммаОстатокКт КАК СуммаОстатокКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(, Счет = &Счет, , Организация = &Организация) КАК ХозрасчетныйОстатки
Как и в предыдущих публикациях, здесь мы видим один маленький запрос 1С, но очень большой SQL-запрос к базе данных.
Получение остатков по счету
SELECT
T1.Fld787RRef, -- Организация
T1.AccountRRef, -- Счет
T1.Fld790Balance_, -- СуммаОстаток
T1.Fld790BalanceDt_, -- СуммаОстатокДт
T1.Fld790BalanceCt_ -- СуммаОстатокКт
FROM (
SELECT
T2._Fld787RRef AS Fld787RRef, -- Организация
T2._AccountRRef AS AccountRRef, -- Счет
CASE
WHEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) IS NULL
THEN 0.0 ELSE CAST(SUM(T2._Fld790) AS NUMERIC(27, 2))
END AS Fld790Balance_, -- СуммаОстаток
CASE
WHEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
-- Если вид счета активный или активно-пассивный и сумма больше 0, то значение относится к сумме остатка ДТ
WHEN MAX(T3._Kind) = 0.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2))
ELSE 0.0
END AS Fld790BalanceDt_, -- СуммаОстатокДт
CASE
WHEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
-- Если вид счета пассивный или активно-пассивный и сумма меньше 0, то значение относится к сумме остатка КТ
WHEN MAX(T3._Kind) = 1.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) < 0.0
THEN -(CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)))
ELSE 0.0
END AS Fld790BalanceCt_, -- СуммаОстатокКт
MAX(T3._Kind) AS AccKind_ -- ВидСчета
FROM dbo._AccRgAT0800 T2 -- РегистрБухгалтерии.Хозрасчетный.ИтогиПоСчетам
INNER JOIN dbo._Acc22 T3 -- ПланСчетов.Хозрасчетный
ON T3._IDRRef = T2._AccountRRef
WHERE -- Разделитель данных
(((T2._Fld774 = @P1)) AND (T3._Fld774 = @P2))
-- Отбор по периоду записи '5999-11-01 00:00:00'
AND (T2._Period = @P3
-- Отбор по счету
AND ((T2._AccountRRef = @P4))
-- Отбор по организации
AND ((T2._Fld787RRef = @P5))
-- Сумма
AND (T2._Fld790 <> @P6))
GROUP BY
T2._Fld787RRef, -- Организация
T2._AccountRRef -- Счет
HAVING
-- Сумма не должна быть равна 0
(CASE
WHEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2._Fld790) AS NUMERIC(27, 2))
END) <> 0.0
-- ИЛИ Если счет активный или активно-пассивный и сумма больше 0
OR (CASE
WHEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
WHEN MAX(T3._Kind) = 0.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2))
ELSE 0.0
END) <> 0.0
-- ИЛИ Если счет пассивный или активно-пассивный и сумма меньше 0
OR (CASE
WHEN CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
WHEN MAX(T3._Kind) = 1.0 OR MAX(T3._Kind) = 2.0 AND CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)) < 0.0
THEN -(CAST(SUM(T2._Fld790) AS NUMERIC(27, 2)))
ELSE 0.0
END) <> 0.0
) T1
Текст сформированного SQL-запроса очень большой, но принцип работы его очень простой:
- Мы не указали параметр "Период" виртуальной таблицы, поэтому платформа 1С попытается получить данные на максимальную дату - 3999 год, 1 ноября. (Дата конца света по календарю 1С). Т.к. в базе SQL Server, на которой мы рассматриваем все примеры, даты хранятся со смещением в 2000 лет, то на стороне базы данных это дата '5999-11-01 00:00:00'.
- Для регистра бухгалтерии имеются рассчитанные итоги - как по месяцам, так и текущие итоги. Т.к. текущие итоги рассчитаны и включены, то платформа получает остаток из них.
- В примере мы не выбирали аналитику субконто, поэтому остатки можно получить из итогов по счетам - таблицы "ИтогиПоСчетам" на дату '5999-11-01 00:00:00'.
- Получение данных из таблицы итогов "ИтогиПоСчетам" отбираются с учетом фильтра по дате '5999-11-01 00:00:00', организации и счету.
- Для исключения излишних данных из результата исключаются записи с пустой суммой.
В тексте самого запроса оставил комментарии, чтобы проще было разобраться. Подобные запросы работают максимально эффективно как из-за рассчитанных итогов, так и из-за относительно небольшого размета таблицы "ИтогиПоСчетам". Даже если информационная система функционирует несколько лет, в этой таблице редко бывает больше миллиона записей, но не всегда конечно. Ранее мы уже рассматривали вопрос с итогами и пример проблем обслуживания таблиц итогов:
Но давайте усложним текст запроса и рассмотрим что сделает платформа.
Остатки на период
Сделаем простое изменение - просто добавим отбор по периоду. Например, поставим период как 10.05.2019. При этом запомним, что итоги по регистру бухгалтерии рассчитаны с 01.01.2019 по 31.08.2020.
ВЫБРАТЬ
ХозрасчетныйОстатки.Организация КАК Организация,
ХозрасчетныйОстатки.Счет КАК Счет,
ХозрасчетныйОстатки.СуммаОстаток КАК СуммаОстаток,
ХозрасчетныйОстатки.СуммаОстатокДт КАК СуммаОстатокДт,
ХозрасчетныйОстатки.СуммаОстатокКт КАК СуммаОстатокКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(&Период, Счет = &Счет, , Организация = &Организация) КАК ХозрасчетныйОстатки
SQL-запрос в результате стал еще больше, т.к. платформа 1С получает данные не только из таблиц итогов "ИтогиПоСчетам", но и из основных таблиц регистра.
SELECT
T1.Fld787RRef, -- Организация
T1.AccountRRef, -- Счет
T1.Fld790Balance_, -- СуммаОстаток
T1.Fld790BalanceDt_, -- СуммаОстатокДт
T1.Fld790BalanceCt_ -- СуммаОстатокКт
FROM (
SELECT
T2.Fld787RRef AS Fld787RRef, -- Организация
T2.AccountRRef AS AccountRRef, -- Счет
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
END AS Fld790Balance_, -- СуммаОстаток
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
-- Если вид счета активный или активно-пассивный и сумма больше 0, то значение относится к сумме остатка ДТ
THEN 0.0 WHEN MAX(T6._Kind) = 0.0 OR MAX(T6._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END AS Fld790BalanceDt_, -- СуммаОстатокДт
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
-- Если вид счета пассивный или активно-пассивный и сумма меньше 0, то значение относится к сумме остатка КТ
WHEN MAX(T6._Kind) = 1.0 OR MAX(T6._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) < 0.0
THEN -CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END AS Fld790BalanceCt_, -- СуммаОстатокКт
MAX(T6._Kind) AS AccKind_ -- ВидСчета
FROM (
SELECT
T3._Fld787RRef AS Fld787RRef, -- Организация
T3._AccountRRef AS AccountRRef, -- Счет
T3._Fld790 AS Fld790Balance_ -- Сумма
FROM dbo._AccRgAT0800 T3 -- РегистрБухгалтерии.Хозрасчетный.ИтогиПоСчетам
WHERE ((T3._Fld774 = @P1)) -- Разделитель данных
AND (T3._Period = @P2 -- Период '4019-06-01 00:00:00'
AND ((T3._AccountRRef = @P3)) -- Счет
AND ((T3._Fld787RRef = @P4)) -- Организация
AND (T3._Fld790 <> @P5)) -- Сумма
UNION ALL
SELECT
T4._Fld787RRef AS Fld787RRef, -- Организация
T4._AccountDtRRef AS AccountRRef, -- Счет
CAST(-T4._Fld790 AS NUMERIC(21, 2)) AS Fld790Balance_ -- Сумма
FROM dbo._AccRg786 T4 -- РегистрБухгалтерии.Хозрасчетный.Основная
WHERE ((T4._Fld774 = @P6)) -- Разделитель данных
AND (T4._Active = 0x01 -- Активность
AND T4._AccountDtRRef <> @P7 -- Не пустой счет
AND ((T4._AccountDtRRef = @P8)) -- Счет
AND ((T4._Fld787RRef = @P9)) -- Организация
AND T4._Period >= @P10 -- Период больше или равен '4019-05-10 00:00:00'
AND T4._Period < @P11) -- Период меньше '4019-06-01 00:00:00'
UNION ALL
SELECT
T5._Fld787RRef AS Fld787RRef, -- Организация
T5._AccountCtRRef AS AccountRRef, -- Счет
CAST(T5._Fld790 AS NUMERIC(21, 2)) AS Fld790Balance_ -- Сумма
FROM dbo._AccRg786 T5 -- РегистрБухгалтерии.Хозрасчетный.Основная
WHERE ((T5._Fld774 = @P12)) -- Разделитель данных
AND (T5._Active = 0x01 -- Активность
AND T5._AccountCtRRef <> @P13 -- Не пустой счет
AND ((T5._AccountCtRRef = @P14)) -- Счет
AND ((T5._Fld787RRef = @P15)) -- Организация
AND T5._Period >= @P16 -- Период больше или равен '4019-05-10 00:00:00'
AND T5._Period < @P17) -- Период меньше '4019-06-01 00:00:00'
) T2
INNER JOIN dbo._Acc22 T6 -- ПланСчетов.Хозрасчетный.Основная
ON T6._IDRRef = T2.AccountRRef
WHERE (T6._Fld774 = @P18)
GROUP BY
T2.Fld787RRef, -- Организация
T2.AccountRRef -- Счет
HAVING
-- СуммаОстаток не равен 0
(CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
END) <> 0.0
-- СуммаОстатокДт не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
WHEN MAX(T6._Kind) = 0.0 OR MAX(T6._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END) <> 0.0
-- СуммаОстатокКт не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
WHEN MAX(T6._Kind) = 1.0 OR MAX(T6._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) < 0.0
THEN -CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END) <> 0.0
) T1
Причина, почему в запросе теперь есть получение данных очень простая: итоги рассчитываются по месяцам. Если мы получаем остаток на дату, отличную от начала месяца, то для периода, который не учитывается в рассчитанных итогах, нужно рассчитать данные по основным таблицам регистра.
Тот же принцип работает и для регистра накопления. Подробнее эту тему мы рассматривали в публикации Регистры накопления. Виртуальные таблицы. Часть №2: "Остатки" и "Остатки и обороты".
Вот какие шаги выполняются при получении данных в этом случае:
- Сначала платформа 1С получает остаток из таблицы итогов "ИтогиПоСчетам" на 01.06.2019, т.к. это ближайшие рассчитанные итоги на указанное значение периода в виртуальной таблице - 10.05.2019. Этот же шаг выполнялся в первом примере.
- Затем платформа выполняет два запроса к основной таблице регистра с учетом периода с 10.05.2019 до 01.06.2019: первый запрос по счету ДТ, второй по счету КТ. Посмотрите в тексте запроса, что часть с ДТ делает сумму отрицательной.
- В заключении данные итогов из шага 1 корректируются данными из основных таблиц регистра (из шага 2) и мы получаем рассчитанное значение остатка.
- Профит!
Этот запрос уже значительно "тяжелее" для выполнения, т.к. платформе 1С приходится "заглянуть" в основные таблицы регистра. Даже с учетом отборов по счету и организации данных для обработки может быть очень много. Отбор может быть просто неселективным и на уровне СУБД будет выполнено сканирование таблицы.
Таким образом, получение остатков может быть очень тяжелой операцией. Если это приходится делать часто и нужна высокая производительность при выполнении запросов, то настало время задуматься о регистрах накопления.
Добавим немного субконто
Пойдем дальше и добавим в наш запрос аналитику субконто и ресурс "Количество". В примере делал отбор по счету с 2 субконто, поэтому и в запросе выведено только два поля.
ВЫБРАТЬ
ХозрасчетныйОстатки.Организация КАК Организация,
ХозрасчетныйОстатки.Счет КАК Счет,
ХозрасчетныйОстатки.Субконто1 КАК Субконто1,
ХозрасчетныйОстатки.Субконто2 КАК Субконто2,
ХозрасчетныйОстатки.СуммаОстаток КАК СуммаОстаток,
ХозрасчетныйОстатки.СуммаОстатокДт КАК СуммаОстатокДт,
ХозрасчетныйОстатки.СуммаОстатокКт КАК СуммаОстатокКт,
ХозрасчетныйОстатки.КоличествоОстаток КАК КоличествоОстаток,
ХозрасчетныйОстатки.КоличествоОстатокДт КАК КоличествоОстатокДт,
ХозрасчетныйОстатки.КоличествоОстатокКт КАК КоличествоОстатокКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(&Период, Счет = &Счет, , Организация = &Организация) КАК ХозрасчетныйОстатки
В этот раз пример усложнился еще сильнее, ведь для хранения аналитики субконто платформа 1С использует отдельную таблицу значений субконто и отдельные таблицы итогов (их мы рассматривали в прошлых публикациях). В этот раз генерируется не один, а серия запросов. Сначала подготавливаем служебные данные.
Служебные данные по счетам и их настройки
Сначала выполняется получение служебных данных:
- Создаем таблицу с номерами строк субконто (фактически количество субконто)
- После получаем информацию по счетам из параметров виртуальных таблиц (признаки учета субконто и настройку хранения итогов)
- Подготавливаем служебную таблицу с настройками счетов и их субконто для основного запроса виртуальной таблицы.
- Получаем количество субконто для каждого счета с учетом фильтров.
- И еще пару небольших служебных запросов.
SQL-запрос получения служебных данных
-- Подготавливаем таблицу номеров строк в таблице видов субконто
-- В дальнейшем используется для отборов с учетом количества субконто в счете
INSERT INTO #tt6 (_LineNo) VALUES(@P1) -- @P1 numeric(2)',1
INSERT INTO #tt6 (_LineNo) VALUES(@P1) -- @P1 numeric(2)',2
-- Информация о счете в отборе
SELECT
T1._IDRRef, -- Ссылка
T2._Fld782, -- Количественный
T2._Fld780, -- Суммовой
T2._TurnoverOnly -- ТолькоОбороты
FROM dbo._Acc22 T1 -- ПланСчетов.Хозрасчетный.Основная
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T2 -- ПланСчетов.Хозрасчетный.ВидыСубконто
INNER JOIN #tt6 T3 WITH(NOLOCK)
ON T3._LineNo = T2._LineNo -- Номер строки
ON T2._Acc22_IDRRef = T1._IDRRef -- Ссылка по счету
AND T2._Fld774 = T1._Fld774 -- ОбластьДанныхОсновныеДанные
WHERE ((T1._Fld774 = @P1)) -- Разделитель данных
AND (((T1._IDRRef = @P2))) -- Отбор по счету
ORDER BY
T1._IDRRef,
T2._LineNo
-- Подготовка служебной таблицы с информацией по счетам
INSERT INTO #tt7 (_IDRRef,_ED1,_ED2,_RF1,_RF2,_BRF1,_BRF2) VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7)
-- ',N'@P1 varbinary(16),@P2 varbinary(1),@P3 varbinary(1),@P4 varbinary(1),@P5 varbinary(1),@P6 varbinary(1),@P7 varbinary(1)',
-- <Идентификатор счета>,0x01,0x01,0x01,0x00,0x00,0x00
INSERT INTO #tt7 (_IDRRef,_ED1,_ED2,_RF1,_RF2,_BRF1,_BRF2) VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7)
--',N'@P1 varbinary(16),@P2 varbinary(1),@P3 varbinary(1),@P4 varbinary(1),@P5 varbinary(1),@P6 varbinary(1),@P7 varbinary(1)',
-- <Идентификатор счета>,0x01,0x00,0x00,0x01,0x01,0x01
-- Информация о количестве субконто по счетам
INSERT INTO #tt8 WITH(TABLOCK) (_IDRRef, _Cnt)
SELECT
T1._IDRRef,
CAST(COUNT_BIG(T2._Acc22_IDRRef) AS NUMERIC(12))
FROM dbo._Acc22 T1 -- ПланСчетов.Хозрасчетный.Основная
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T2 -- ПланСчетов.Хозрасчетный.ВидыСубконто
ON T2._Acc22_IDRRef = T1._IDRRef -- По счету
AND T2._Fld774 = T1._Fld774 -- По разделителю
WHERE ((T1._Fld774 = @P1)) -- Разделитель данных
AND ((T1._IDRRef = @P2)) -- По ссылке счета
GROUP BY T1._IDRRef
-- Служебный запрос для получения списка счетов
SELECT DISTINCT
T1._Cnt
FROM #tt8 T1 WITH(NOLOCK)
Когда все данные подготовлены просыпается мафия формируем основной запрос.
Основной запрос в этот раз также значительно вырос в объеме.
SELECT
T1.Fld787RRef, -- Организация
T1.AccountRRef, -- Счет
T1.Value1_TYPE, -- Вид субконто 1
T1.Value1_RTRef, -- Тип ссылки субконто 1
T1.Value1_RRRef, -- Значение субконто 1
T1.Value2_TYPE, -- Вид субконто 2
T1.Value2_RTRef, -- Тип ссылки субконто 2
T1.Value2_RRRef, -- Значение субконто 2
T1.Fld790Balance_, -- СуммаОстаток
T1.Fld790BalanceDt_, -- СуммаОстатокДт
T1.Fld790BalanceCt_, -- СуммаОстатокКт
T1.Fld792Balance_, -- КоличествоОстаток
T1.Fld792BalanceDt_, -- КоличествоОстатокДт
T1.Fld792BalanceCt_ -- КоличествоОстатокКт
FROM (
SELECT
T2.Fld787RRef AS Fld787RRef, -- Организация
T2.AccountRRef AS AccountRRef, -- Счет
T2.Value1_TYPE AS Value1_TYPE, -- Вид субконто 1
T2.Value1_RTRef AS Value1_RTRef, -- Тип ссылки субконто 1
T2.Value1_RRRef AS Value1_RRRef, -- Значение субконто 1
T2.Value2_TYPE AS Value2_TYPE, -- Вид субконто 2
T2.Value2_RTRef AS Value2_RTRef, -- Тип ссылки субконто 2
T2.Value2_RRRef AS Value2_RRRef, -- Значение субконто 2
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
END AS Fld790Balance_, -- СуммаОстаток
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0 WHEN MAX(T17._Kind) = 0.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0 END
AS Fld790BalanceDt_, -- СуммаОстатокДт
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0 WHEN MAX(T17._Kind) = 1.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) < 0.0
THEN -CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END AS Fld790BalanceCt_, -- СуммаОстатокКт
CASE
WHEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3))
END AS Fld792Balance_, -- КоличествоОстаток
CASE
WHEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) IS NULL
THEN 0.0
WHEN MAX(T17._Kind) = 0.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) > 0.0
THEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3))
ELSE 0.0
END AS Fld792BalanceDt_, -- КоличествоОстатокДт
CASE
WHEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) IS NULL
THEN 0.0
WHEN MAX(T17._Kind) = 1.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) < 0.0
THEN -CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3))
ELSE 0.0
END AS Fld792BalanceCt_, -- КоличествоОстатокКт
MAX(T17._Kind) AS AccKind_ -- ВидСчета
FROM (
-- Получаем данные из таблицы итогов
SELECT
T3._Fld787RRef AS Fld787RRef, -- Организация
T3._AccountRRef AS AccountRRef, -- Счет
T3._Value1_TYPE AS Value1_TYPE, -- Вид субконто 1
T3._Value1_RTRef AS Value1_RTRef, -- Тип ссылки субконто 1
T3._Value1_RRRef AS Value1_RRRef, -- Значение субконто 1
T3._Value2_TYPE AS Value2_TYPE, -- Вид субконто 2
T3._Value2_RTRef AS Value2_RTRef, -- Тип ссылки субконто 2
T3._Value2_RRRef AS Value2_RRRef, -- Значение субконто 2
T3._Fld790 AS Fld790Balance_, -- Сумма
T3._Fld792 AS Fld792Balance_ -- Количество
FROM dbo._AccRgAT2820 T3 -- РегистрБухгалтерии.Хозрасчетный.ИтогиПоСчетамССубконто2
INNER JOIN #tt8 T4 WITH(NOLOCK) -- Таблица с информацией о количестве субконто для счетов
ON T4._IDRRef = T3._AccountRRef -- По счету
AND T4._Cnt = @P1 -- По количеству субконто
WHERE ((T3._Fld774 = @P2)) -- Разделитель данных
AND (((T3._Fld787RRef = @P3)) -- Организация
AND T3._Period = @P4 -- Период текущих итогов
AND (T3._Fld790 <> @P5 OR T3._Fld792 <> @P6)) -- Сумма и Количество заполнены
UNION ALL
-- Получаем данные из основных таблиц движений для ДТ
SELECT
T5._Fld787RRef AS Fld787RRef, -- Организация
T5._AccountDtRRef AS AccountRRef, -- Счет
CASE WHEN T6._ED1 = 0x01 THEN T8._Value_TYPE ELSE CAST(NULL AS BINARY(1)) END AS Value1_TYPE, -- Вид субконто 1
CASE
WHEN T6._ED1 = 0x01
THEN T8._Value_RTRef
ELSE CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN 0x00000000
END
END AS Value1_RTRef, -- Тип ссылки субконто 1
CASE
WHEN T6._ED1 = 0x01
THEN T8._Value_RRRef
ELSE CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN @P7
END
END AS Value1_RRRef, -- Значение субконто 1
CASE
WHEN T6._ED2 = 0x01
THEN T10._Value_TYPE
ELSE CAST(NULL AS BINARY(1))
END AS Value2_TYPE, -- Вид субконто 2
CASE
WHEN T6._ED2 = 0x01
THEN T10._Value_RTRef
ELSE CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN 0x00000000
END
END AS Value2_RTRef, -- Тип ссылки субконто 2
CASE
WHEN T6._ED2 = 0x01
THEN T10._Value_RRRef
ELSE CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN @P8
END
END AS Value2_RRRef, -- Значение субконто 2
CAST(CASE
WHEN T6._BRF1 = 0x01
THEN -T5._Fld790
ELSE @P9
END AS NUMERIC(21, 2))
AS Fld790Balance_, -- Сумма
CAST(
CASE
WHEN T6._BRF2 = 0x01
THEN -T5._Fld792Dt
WHEN T5._Fld792Dt IS NULL
THEN CAST(NULL AS NUMERIC(38,8))
ELSE @P10
END AS NUMERIC(21, 3))
AS Fld792Balance_ -- Количество
FROM dbo._AccRg786 T5 -- РегистрБухгалтерии.Хозрасчетный.Основная
INNER JOIN #tt7 T6 WITH(NOLOCK) -- Служебная таблица с информацией по счетам
ON T6._IDRRef = T5._AccountDtRRef
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T7 -- ПланСчетов.Хозрасчетный.ТабличнаяЧасть.ВидыСубконто
ON (T7._Acc22_IDRRef = T5._AccountDtRRef AND T7._LineNo = @P11) AND (T7._Fld774 = @P12)
LEFT OUTER JOIN dbo._AccRgED823 T8 -- РегистрБухгалтерии.Хозрасчетный.Субконто
ON (T8._RecorderTRef = T5._RecorderTRef AND T8._RecorderRRef = T5._RecorderRRef AND T8._LineNo = T5._LineNo AND T8._Period = T5._Period AND T8._Correspond = @P13 AND T8._KindRRef = T7._DimKindRRef AND T8._Period >= @P14 AND T8._Period < @P15) AND (T8._Fld774 = @P16)
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T9 -- ПланСчетов.Хозрасчетный.ТабличнаяЧасть.ВидыСубконто
ON (T9._Acc22_IDRRef = T5._AccountDtRRef AND T9._LineNo = @P17) AND (T9._Fld774 = @P18)
LEFT OUTER JOIN dbo._AccRgED823 T10 -- РегистрБухгалтерии.Хозрасчетный.Субконто
ON (T10._RecorderTRef = T5._RecorderTRef -- Тип регистратора
AND T10._RecorderRRef = T5._RecorderRRef -- Регистратор
AND T10._LineNo = T5._LineNo -- Номер строки
AND T10._Period = T5._Period -- Период
AND T10._Correspond = @P19 -- Корреспонденция
AND T10._KindRRef = T9._DimKindRRef -- Вид
AND T10._Period >= @P20 -- Отбор по началу периода
AND T10._Period < @P21) -- Отбор по окончанию периода
AND (T10._Fld774 = @P22) -- Разделитель данных
WHERE ((T5._Fld774 = @P23)) -- Разделитель данных
AND (T5._Active = 0x01 -- Активность
AND T5._AccountDtRRef <> @P24 -- Счет не пустой
AND ((T5._AccountDtRRef = @P25)) -- Отбор по счету
AND ((T5._Fld787RRef = @P26)) -- Отбор по организации
AND T5._Period >= @P27 -- Отбор по началу периода
AND T5._Period < @P28) -- Отбор по окончанию периода
UNION ALL
-- Получаем данные из основных таблиц движений для КТ
SELECT
T11._Fld787RRef AS Fld787RRef, -- Организация
T11._AccountCtRRef AS AccountRRef, -- Счет
CASE WHEN T12._ED1 = 0x01 THEN T14._Value_TYPE ELSE CAST(NULL AS BINARY(1)) END AS Value1_TYPE, -- Вид субконто 1
CASE
WHEN T12._ED1 = 0x01
THEN T14._Value_RTRef
ELSE
CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN 0x00000000
END
END AS Value1_RTRef, -- Тип ссылки субконто 1
CASE
WHEN T12._ED1 = 0x01
THEN T14._Value_RRRef
ELSE CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN @P29
END
END AS Value1_RRRef, -- Значение субконто 1
CASE
WHEN T12._ED2 = 0x01
THEN T16._Value_TYPE
ELSE CAST(NULL AS BINARY(1)) END AS Value2_TYPE, -- Вид субконто 2
CASE
WHEN T12._ED2 = 0x01
THEN T16._Value_RTRef
ELSE CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN 0x00000000
END
END AS Value2_RTRef, -- Тип ссылки субконто 2
CASE
WHEN T12._ED2 = 0x01
THEN T16._Value_RRRef
ELSE CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN @P30 END END AS Value2_RRRef, -- Значение субконто 2
CAST(CASE
WHEN T12._BRF1 = 0x01
THEN T11._Fld790
ELSE @P31
END AS NUMERIC(21, 2)) AS Fld790Balance_, -- Сумма
CAST(CASE
WHEN T12._BRF2 = 0x01
THEN T11._Fld792Ct
WHEN T11._Fld792Ct IS NULL
THEN CAST(NULL AS NUMERIC(38,8))
ELSE @P32
END AS NUMERIC(21, 3)) AS Fld792Balance_ -- Количество
FROM dbo._AccRg786 T11 -- РегистрБухгалтерии.Хозрасчетный.Основная
INNER JOIN #tt7 T12 WITH(NOLOCK) -- Служебная таблица с информацией о счетах
ON T12._IDRRef = T11._AccountCtRRef
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T13 -- ПланСчетов.Хозрасчетный.ТабличнаяЧасть.ВидыСубконто
ON (T13._Acc22_IDRRef = T11._AccountCtRRef
AND T13._LineNo = @P33)
AND (T13._Fld774 = @P34) -- Разделитель данных
LEFT OUTER JOIN dbo._AccRgED823 T14 -- РегистрБухгалтерии.Хозрасчетный.Субконто
ON (T14._RecorderTRef = T11._RecorderTRef -- Тип регистратора
AND T14._RecorderRRef = T11._RecorderRRef -- Регистратор
AND T14._LineNo = T11._LineNo -- Номер строки
AND T14._Period = T11._Period -- Период
AND T14._Correspond = @P35 -- Корреспондернция
AND T14._KindRRef = T13._DimKindRRef -- Вид
AND T14._Period >= @P36 -- Отбор по началу периода
AND T14._Period < @P37) -- Отбор по окончанию периода
AND (T14._Fld774 = @P38) -- Разделитель данных
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T15 -- ПланСчетов.Хозрасчетный.ТабличнаяЧасть.ВидыСубконто
ON (T15._Acc22_IDRRef = T11._AccountCtRRef
AND T15._LineNo = @P39)
AND (T15._Fld774 = @P40) -- Разделитель данных
LEFT OUTER JOIN dbo._AccRgED823 T16 -- РегистрБухгалтерии.Хозрасчетный.Субконто
ON (T16._RecorderTRef = T11._RecorderTRef
AND T16._RecorderRRef = T11._RecorderRRef
AND T16._LineNo = T11._LineNo -- Номер строки
AND T16._Period = T11._Period -- Период
AND T16._Correspond = @P41 -- Корреспондерция
AND T16._KindRRef = T15._DimKindRRef -- Вид
AND T16._Period >= @P42 -- Отбор по началу периода
AND T16._Period < @P43) -- Отбор по окончанию периода
AND (T16._Fld774 = @P44) -- Разделитель данных
WHERE ((T11._Fld774 = @P45)) -- Разделитель данных
AND (T11._Active = 0x01 -- Активность
AND T11._AccountCtRRef <> @P46 -- Счет не пустой
AND ((T11._AccountCtRRef = @P47)) -- Отбор по счету
AND ((T11._Fld787RRef = @P48)) -- Организация
AND T11._Period >= @P49 -- Отбор по началу периода
AND T11._Period < @P50) -- Отбор по окончанию периода
) T2
INNER JOIN dbo._Acc22 T17 -- ПланСчетов.Хозрасчетный.Основной
ON T17._IDRRef = T2.AccountRRef -- По ссылкам
WHERE (T17._Fld774 = @P51) -- Разделитель данных
GROUP BY
T2.Fld787RRef, -- Организация
T2.AccountRRef,
T2.Value1_TYPE,
T2.Value1_RTRef,
T2.Value1_RRRef,
T2.Value2_TYPE,
T2.Value2_RTRef,
T2.Value2_RRRef
HAVING
-- СуммаОстаток не равен 0
(CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
END) <> 0.0
-- СуммаОстатокДт не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
WHEN MAX(T17._Kind) = 0.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) ELSE 0.0 END) <> 0.0
-- СуммаОстатокКт не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0 WHEN MAX(T17._Kind) = 1.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) < 0.0
THEN -CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END) <> 0.0
-- КоличествоОстаток не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3))
END) <> 0.0
-- КоличествоОстатокДт не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) IS NULL
THEN 0.0
WHEN MAX(T17._Kind) = 0.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) > 0.0
THEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3))
ELSE 0.0
END) <> 0.0
-- КоличествоОстатокКт не равен 0
OR (CASE
WHEN CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) IS NULL
THEN 0.0
WHEN MAX(T17._Kind) = 1.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3)) < 0.0
THEN -CAST(SUM(T2.Fld792Balance_) AS NUMERIC(27, 3))
ELSE 0.0
END) <> 0.0
) T1
Итоги по счетам теперь уже не используются, т.к. в них нет аналитики субконто. Вместо этого мы используем таблицу итогов "ИтогиПоСчетамССубконто2", т.к. в параметрах виртуальной таблицы используется счет с 2 субконто. Принцип работы запроса теперь такой:
- Получаем данные рассчитанных итогов на 01.06.2019 из таблицы "ИтогиПоСчетамССубконто2" с учетом аналитики по субконто и установленным отборам по организации, периоду и счету. Отбор по счету устанавливается с помощью временной таблицы с параметрами счетов, которую мы сформировали выше. В тексте запроса Вы можете посмотреть комментарии в части отборов.
- Далее, как и в предыдущем примере, выполняется получение данных из основных таблиц регистра (основная таблица и таблица значений субконто в этом случае) с учетом всех установленных ранее отборов. Запроса выполняется два: один для данных по дебету, другой для данных по кредиту.
- Данные из основных таблиц регистра корректируют данные рассчитанных итогов.
- Из результата исключаются записи с пустыми значениями ресурсов.
На последнем этапе платформа 1С "подчищает" за собой служебные временные таблицы.
-- Очищаем временные таблицы
TRUNCATE TABLE #tt8
TRUNCATE TABLE #tt6
TRUNCATE TABLE #tt7
Интересными моментами в примере могут быть следующие:
* Если служебные данные, полученные первыми запросами, не подтверждают не подтверждают наличие субконто с определенным номером у счета, то платформа явно подставляет значения NULL или пустые идентификаторы. Именно поэтому служебная таблица и формируется перед основным запросом. Делается это с помощью подобных инструкций:
ELSE
CASE
WHEN CAST(NULL AS BINARY(1)) IS NOT NULL
THEN 0x00000000
END
END AS Value1_RTRef, -- Тип ссылки субконто 1
* Для расчета показателей по ДТ или КТ в запросе явно присутствует условие по виду счета (активный, пассивный или активно-пассивный), которое комбинируется с проверкой значения ресурса (положительное или отрицательное). Вот так, например, выглядит расчет суммы остатка по ДТ и КТ:
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0
ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
END AS Fld790Balance_, -- СуммаОстаток
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0 WHEN MAX(T17._Kind) = 0.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) > 0.0
THEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0 END
AS Fld790BalanceDt_, -- СуммаОстатокДт
CASE
WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL
THEN 0.0 WHEN MAX(T17._Kind) = 1.0 OR MAX(T17._Kind) = 2.0 AND CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) < 0.0
THEN -CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2))
ELSE 0.0
END AS Fld790BalanceCt_, -- СуммаОстатокКт
Поле "_Kind" может принимать следующие значения:
- 0 - активный
- 1 - пассивный
- 2 - активно-пассивный
Для остатка по дебету берутся только положительные значения активных или активно-пассивных счетов. Для остатка по кредиту учитываются только отрицательные значения по пассивно или активно-пассивных счетам, при этом итоговое значение инвертируется по знаку. Подробнее смотрите в основном тексте запроса с комментариями выше.
Отбор по виду субконто
Рассмотрим еще один пример. Добавим в наш запрос указание вида субконто в параметр виртуальной таблицы.
ВЫБРАТЬ
ХозрасчетныйОстатки.Организация КАК Организация,
ХозрасчетныйОстатки.Валюта КАК Валюта,
ХозрасчетныйОстатки.Подразделение КАК Подразделение,
ХозрасчетныйОстатки.Счет КАК Счет,
ХозрасчетныйОстатки.Субконто1 КАК Субконто1,
ХозрасчетныйОстатки.СуммаОстаток КАК СуммаОстаток,
ХозрасчетныйОстатки.СуммаОстатокДт КАК СуммаОстатокДт,
ХозрасчетныйОстатки.СуммаОстатокКт КАК СуммаОстатокКт,
ХозрасчетныйОстатки.КоличествоОстаток КАК КоличествоОстаток,
ХозрасчетныйОстатки.КоличествоОстатокДт КАК КоличествоОстатокДт,
ХозрасчетныйОстатки.КоличествоОстатокКт КАК КоличествоОстатокКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(&Период, Счет = &Счет, &СубконтоНоменклатура, Организация = &Организация) КАК ХозрасчетныйОстатки
На самом деле SQL-запросы платформы будут не сильно отличаться от предыдущего примера. Единственное отличие - это появление дополнительных отборов по виду субконто в служебных запросах.
SELECT
T1._Acc22_IDRRef,
T1._Fld782,
T1._Fld780,
T1._TurnoverOnly
FROM dbo._Acc22_ExtDim779 T1
WHERE ((T1._Fld774 = @P1))
-- !!! Новый отбор по виду субконто
AND ((T1._DimKindRRef = @P2)
AND ((T1._Acc22_IDRRef = @P3)))
INSERT INTO #tt18 WITH(TABLOCK)
(_IDRRef, _LineNo1, _Cnt)
SELECT
T1._IDRRef,
T2._LineNo,
CAST(COUNT_BIG(T3._Acc22_IDRRef) AS NUMERIC(12))
FROM dbo._Acc22 T1
INNER JOIN dbo._Acc22_ExtDim779 T2
ON T2._Acc22_IDRRef = T1._IDRRef
-- !!! Новый отбор по виду субконто
AND T2._DimKindRRef = @P1
AND T2._Fld774 = T1._Fld774
LEFT OUTER JOIN dbo._Acc22_ExtDim779 T3
ON T3._Acc22_IDRRef = T1._IDRRef AND T3._Fld774 = T1._Fld774
WHERE ((T1._Fld774 = @P2)) AND ((T1._IDRRef = @P3))
GROUP BY
T1._IDRRef,
T2._LineNo
При этом основной запрос получения данных практически не изменится. Поэтому приводить его здесь еще раз смысла нет.
Таким образом, указание явных видов субконто в запросе меняет лишь предварительное получение служебных данных по счетам, которые фильтруют данные в основном запросе.
Что нужно учитывать
Мы рассмотрели небольшую серию примеров работы виртуальной таблиц "Остатки" регистра бухгалтерии, но в некоторых случаях поведение платформы 1С может отличаться от того, что Вы здесь увидели. На это влияют:
- Наличие рассчитанных итогов и их период. А также использование текущих итогов. Не забывайте их обслуживать :)
- Количество субконто у счетов, а также максимально допустимое количество субконто для основного плана счетов регистра.
- Настройка хранения итогов "Только обороты", которая влияет на хранение значений итогов в таблицах. Подробнее об этом рассказывалось здесь.
Но общий принцип работы виртуальной таблицы будет тот же, что и в примерах выше.
Вместо заключения
Пока что все в части работы виртуальной таблицы "Остатки" регистра бухгалтерии. Остается еще пару тем, которые будут затронуты в будущих публикациях:
- Виртуальную таблицу "Остатки и обороты", которая сложнее и интереснее таблицы "Остатки".
- Неплатформенные индексы для регистра бухгалтерии.
- Влияние количества субконто на производительность.
Ссылки на предыдущие статьи по регистру бухгалтерии даны в конце.
Все данные для публикации получены с помощью инструментов:
На этом все. Удачи и до связи!
Другие ссылки
Авторские разработки
Другие разработки (бесплатные и за $m)
