Регистры накопления. Виртуальные таблицы. Часть №1: Обороты

О регистрах накопления

В нескольких статьях представлены основные сведения о внутреннем устройстве регистров накопления, о SQL-запросах платформы при работе с ними и их изменение в зависимости от настроек регистра. Подробно описана работа платформы с разными типами регистров (остатков и накопления), а также принцип действия агрегатов.

Материалы созданы во времена платформы 8.2, поэтому некоторые моменты могут быть уже не актуальными, но основные принципы работы остались неизменными.

Больше года назад сайт был закрыт. Некоторые из его материалов будут реанимированы на Инфостарт.

Конкретно в этой статье речь идет о виртуальной таблице «Обороты» регистров накопления в базе данных. Все примеры из публикации Вы можете найти на GitHub.

Виртуальные таблицы

В статье «Регистры накопления. Структура хранения в базе данных» мы рассматривали таблицы, которые использует платформа для хранения движений в регистрах накопления, а также его итоговых оборотов или остатков в зависимости от вида регистра («Остатки» или «Обороты»). Также были подробно рассмотрены действия платформы с таблицами остатков и оборотов при записи движений в регистр.

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

Как мы видим, кроме физической таблицы движений, для которой в базе данных создается отдельная таблица, также имеются виртуальные таблицы. Всего их три:

Последние две становятся доступными только если вид регистра установлен как «Остатки».

Главное отличие виртуальных таблиц от физических: виртуальные таблицы не хранятся непосредственно в базе данных. При выборке данных из виртуальной таблицы платформа формирует некоторый запрос в зависимости от переданных параметров, который может получать записи из двух и более таблиц для формирования конечного результата.

Далее в статье проанализируем SQL-запросы платформы 1С:Предприятие 8.2 при обращении к виртуальной таблицам. При этом будем выполнять запросы при различных комбинациях параметров.

Сторона СУБД

Начнем анализ с виртуальной таблицы «Обороты», так как она присутствует вне зависимости от вида регистра.

Виртуальная таблица «Обороты»

Виртуальная таблица «Обороты» есть, как у регистра накопления с видом «Обороты», так и с видом «Остатки». Рассмотрим оба варианта. Начнем с последнего.

Вид регистра «Остатки»

Посмотрим состав полей таблицы оборотов на примере регистра «ОстаткиНоменклатуры».

В нем содержатся поля каждого из измерений, а также поля «Приход», «Расход» и «Оборот» для каждого из ресурсов в регистре. В нашем случае у нас два измерения («Номенклатура» и «Склад»), а также три поля «КоличествоПриход», «КоличествоРасход» и «КоличестоОборот».

В зависимости от установленного параметра «Периодичность» в состав доступных полей вирт. таблицы будут добавляться соответствующие периоды («ПериодДень», «ПериодМесяц» и т.д.).

Теперь напишем простой запрос для получения оборотов по номенклатуре за период. В параметрах виртуальной таблицы установим поля «НачалоПериода» и «КонецПериода», а в условия добавим отбор по складу «Склад №1». При выполнении запроса платформа сформирует два SQL-запроса к базе данных. Первый запрос получает настройки регистра накопления:

Используя эти настройки, платформа формирует SQL-запрос непосредственно на получение оборотов. Вот так выглядит SQL-запрос платформы для получения оборотов:

Старался подробно разобрать весь запрос. Если будут непонятные моменты, то прошу в комментарии. Исходный текст запроса на языке 1С:Предприятия выглядит следующим образом:

В случае, если для виртуальной таблицы также устанавливается параметр «Периодичность», например, в значение «Месяц», то SQL-запрос немного видоизменится:

Если в виртуальной таблице периодичность установлена «Авто», то в SQL-запросе будут содержаться поля периода для каждой из получаемой в запросе периодичности («День», «Месяц», «Год» и т.д.). Причина, по которой платформа хранит значения периода с увеличением части даты «Год» на 2000 лет мне не известна. Если кто из читателей подскажет, буду благодарен.

Мы с Вами рассмотрели SQL-запросы платформы при работе с виртуальной таблицей «Обороты» регистра накопления с видом «Остатки». Как мы видим, виртуальная таблица «Обороты» в этом случае берет данные из таблицы движений регистра. Даже, если обороты в запросе получаются за несколько месяцев, необходимые данные будут также формироваться по таблице движений без использования каких-либо сохраненных ранее итогов. И это понятно, регистр накопления с видом «Остатки» предназначен для учета остатков, а не оборотов. Далее мы увидим, почему для решения задач учета оборотов лучше использовать соответствующий вид регистра накопления.

Вид регистра «Обороты»

Виртуальная таблица «Обороты» включает в себя поля для каждого из измерений регистра, а также по одному полю на каждый ресурс. Для оборотного регистра «ДвиженияНоменклатуры» из тестовой конфигурации состав полей таблицы следующий:

Как и в предыдущем случае, в зависимости от параметра «Периодичность» в составе доступных полей появятся соответствующие периоды.

И так, выполним несколько запросов к таблице «Обороты» и проанализируем SQL-запросы платформы. Первый запрос на языке запросов платформы:

Первым делом платформа 1С:Предприятие получит настройки регистра накопления, к которому выполняется запрос. Запрос будет идентичный рассматриваемому ранее примеру, пойдем дальше. Сформированный платформой SQL-запрос тогда будет такой:

К комментариям в приведенном тексте добавлю, что вне зависимости от значений параметров «НачалоПериода» и «КонецПериода» запрос пытается получить данные и из итоговых таблиц, и из таблицы движений регистра.

Если в запросе на языке платформы мы добавим использование параметра «Периодичность» (например, поставим значение «Месяц»), то SQL-запрос платформы изменится аналогично рассмотренному примеру для регистра накопления с видом остатки. Будут добавлены поля выбранных периодов («ПериодДень», «ПериодМесяц» и т.д.) в секции запроса «SELECT» и «GROUP BY». Для нашего примера это месяц. Поля и группировки будут добавлены для всех вложенных запросов и, конечно, содержаться в результатирующей выборке. В нашем примере, выражения в поле запроса для получения периода будет таким:

Принцип получения значений периода был описан выше для регистра с видом «Обороты».

Заключение

Если представить действия SQL-запросов схематично, то выглядеть это будет примерно так:

По схеме видно, что наиболее оптимальным образом платформа работает с виртуальной таблицей «Обороты» для регистра накопления с видом «Обороты», поскольку использует рассчитанных итоги по оборотам в разрезе месяцев. И лишь в тех случаях, когда рассчитанных итогов для периода нет, тогда использует таблицу движений. Для регистра вида «Остатки» всегда используется таблица движений вне зависимости от настроек хранения итоговых записей. Именно поэтому следует внимательно отнестись к настройке вида регистра накопления при проектировании структуры метаданных конфигурации.

Интересен тот факт, что если для регистра накопления отключить использование итогов, то тогда запрос к виртуальной таблице «Остатки» станет невозможным. Будет появляться такая ошибка:

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

Что дальше

В следующих статьях будет рассмотрена работа платформы с виртуальными таблицами «Остатки» и «Остатки и обороты». Также коснемся работы агрегатов регистра накопления.

Источник

Виртуальные таблицы регистра накопления в 1С 8.3

Выборка из регистра накопления

Перебрать все записи регистра накопления можно с помощью метода Выбрать. Данный метод вернет выборку, которую можно перебрать в цикле. Через выборку будут доступны все поля регистра накопления (измерения, ресурсы, реквизиты, служебные поля):

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

Если измерение Товар не проиндексировано, то будет выброшено исключение «Недопустимое значение параметра (параметр номер ‘3’)».

Если нужно выбрать записи только одного регистратора, то можно воспользоваться методом ВыбратьПоРегистратору, передав параметром ссылку на нужный документ:

Чаще всего для выборки используется запрос к таблице движений регистра:

Виртуальные таблицы регистра накопления

Помимо таблицы движений в запросах доступны виртуальные таблицы регистра накопления:

Все их можно увидеть через конструктор запроса:

При этом у регистра накопления с видом Остатки есть все три виртуальные таблицы, а у регистра накопления с видом Обороты только одна, так как у оборотного регистра нет остатков.

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

Виртуальная таблица остатков

Алгоритм построения таблицы остатков

Виртуальная таблица остатков содержит в себе все измерения и остатки по всем ресурсам:

Также для таблицы остатков можно указать параметры:

В запросе параметры указываются в скобках после имени таблицы:

Алгоритм построения таблицы остатков:

Например, таблица движений имеет следующий вид:

Итоги рассчитаны за все периоды, соответственно таблица итогов выглядит так:

Получим остатки на 1 февраля (параметр Период = 01.02.2021):

Теперь выполним тот же самый запрос, но параметр период установим равным 12.02.2021.

Теперь вообще не будем указывать параметр Период в запросе:

В этом случае будет использоваться только таблица итогов, из нее будут сразу получены текущие итоги:

Так как в запросе нет поля склад, то оно будет отброшено. Поле Количество будет просуммировано и для товара Шкаф станет равным нулю. Такие записи будут отброшены. В итоге в таблице остатков будет только одна строка:

Полученная таблица автоматически не сортируется. Если в параметрах виртуальной таблицы был наложен отбор, то условие отбора будет подставлено во все шаги получения остатков. Если отбор использовался в предложении ГДЕ в запросе, то сначала будет получена таблица остатков без отбора, а потом отброшены все записи не подходящие под условие. Поэтому для виртуальных таблиц отборы всегда нужно накладывать в параметрах виртуальной таблицы, если это возможно.

Если бы в нашем примере не было рассчитанных остатков за февраль 2021, то при получении остатков на втором шаге на 12.02.2021 были бы получены текущие итоги на 01.11.3999. Если нет текущих итогов (отключены для регистра), то был бы взят остаток на 01.02.2021, а на третьем шаге при получении записей из таблицы движений для прихода бы использовался знак плюс, для расхода — минус.

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

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

Граница

В качестве параметра Период можно передать:

Если передать пустую дату, Неопределено или не заполнять параметр, то будут получены текущие итоги.

Если передать дату, то будут получены остатки с учетом времени, но без учета движений за эту дату. То есть если параметром указать дату 31.01.2021 23:59:59, то будут получены следующие остатки:

Если параметром передать момент времени, то будут получены остатки на указанный момент времени, но исключая движения ссылки из момента времени. Например, если передавать параметром момент времени 31.01.2021 23:59:59 + Приход №4, то остатки будут выглядеть так:

Если нужно получить остатки с учетом движений ссылки, то нужно передать параметром границу. Граница — это программный объект, который включает в себя дату и вид границы: включать граничное значение в интервал или нет:

Источник

Устройство регистра накопления

Структура регистра накопления

Все поля регистра накопления можно разделить на три категории: измерения, ресурсы, реквизиты. К этим категориям относятся и все системные поля регистра. Период является измерением. Регистратор и НомерСтроки, с одной стороны, являются измерениями, так как вместе периодом определяют момент времени в которое произошло движение; с другой стороны, они характеризуют конкретную запись и могут быть отнесены к категории реквизитов. Вид движения является реквизитом так как является только характеристикой конкретной записи.

Таблицы регистра накопления остатков

Регистр накопления остатков состоит из двух таблиц: таблицы движения и таблицы итогов. В таблице движений хранятся записи, которые либо вводятся пользователем вручную, либо генерируются в процессе проведения документа или исполнения обработки. Таблица движений имеет следующую структуру:
1. Период
2. Регистратор
3. Номер строки
4. Вид движения
5.
6.
7.
В таблице итогов хранятся остатки в разрезе всех измерений с периодичностью месяц, на начало месяца. Временной интервал, за который хранятся остатки, ограничивается установкой периода рассчитанных итогов. Период рассчитанных итогов указывается как последний день месяца, по который рассчитаны итоги. То есть если период рассчитанных итогов равен 31.07.2004, то итоги будут рассчитаны по 01.08.2004 включительно. Кроме того, в таблице итогов отдельно хранятся актуальные итоги. Таблица итогов имеет следующую структуру:
1. Период
2.
3.
Если период рассчитанных итогов равен 31.07.2004, а самое раннее движение было сделано 02.05.2004, то итоги будут хранится за следующие периоды: 01.06.2004, 01.07.2004, 01.08.2004 и актуальные итоги.

Виртуальная таблица остатков

Виртуальная таблица остатков для расчета данных всегда использует таблицу итогов и иногда таблицу движений. Использование таблицы движений зависит от момента времени, на который считаются остатки, и периода рассчитанных итогов. При расчете остатков используются довольно простая стратегия.
1. Подбирается ближайший больший или равный момент времени, на который рассчитаны остатки.
2. На этот момент получаются остатки из таблицы итогов.
3. Если момент времени, на который считаются остатки, не совпадает с моментом времени итогов, то остатки досчитываются по движениям за период с момента запроса остатков по момент итогов.

Рассмотрим несколько примеров. Пусть период рассчитанных итогов равен 31.07.2004. Мы хотим получить остатки на 01.07.2004, 15.07.2004, 01.08.2004, 15.08.2004 и актуальные остатки.
Для случаев получения остатков на 01.07.2004, 01.08.2004 и актуальных остатков данные будут получены непосредственно из таблицы итогов. В случае получения остатков на 15.07.2004 сначала будут получены данные из таблицы итогов на момент времени 01.08.2004, так как это ближайший больший момент времени, на который посчитаны остатки, а затем будут обработаны данные из таблицы движений за период с 15.07.2004 по 31.07.2004 включительно. В случае получения остатков на 15.08.2004, ближайшим большим моментом времени, на который посчитаны остатки является момент актуальных остатков. Таким образом, для расчета остатков на 15.08.2004, будут получены актуальные итоги и обработаны данные таблицы движений начиная с 15.08.2004.

Виртуальная таблица оборотов

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

Виртуальная таблица остатков и оборотов

В случае если периодичность задана, расчет данных разбивается на следующие шаги:
1. Получение остатков на начало заданного периода.
2. Получение оборотов с заданной периодичностью за заданный период.
3. Объединение данных двух запросов.

Отличие оборотного регистра от регистра остатков

В отличие от регистра остатков, оборотный регистр накапливает обороты. По данному регистру нельзя посчитать остатки, и поэтому для него существуют только одна виртуальная таблица оборотов. Структура таблицы движений оборотного регистра не сильно отличается от таблицы движений регистра остатков. Она имеет следующую структуру:
1. Период
2. Регистратор
3. Номер строки
4.
5.
6.

Очевидно, что в таблице движений оборотного регистра отсутствует только поле ВидДвижения. Таблица же итогов оборотного регистра по своей структуре идентична структуре таблицы регистра остатков:
1. Период
2.
3.

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

Виртуальная таблица оборотов

Виртуальная таблица оборотов в своей работе может использовать как таблицу итогов, так и таблицу движений. Зависит это от заданного периода и периодичности. Если периодичность задана, и она меньше месяца, то используется только таблица движений. Если периодичность не задана или задана большей или равной месяцу, то использование таблицы итогов или движений зависит от заданного периода. Если в заданный период попадают целые месяцы, то данные за них считаются по таблице итогов, остальное считается по таблице движений. Например считаются данные с периодичностью месяц за периоды:
1. с 01.03.2004 по 31.03.2004
2. с 02.03.2004 по 03.05.2004
3. с 02.03.2004 по 03.04.2004

В первом случае все данные будут посчитаны по таблице итогов. Во втором случае данные за период с 01.04.2004 по 30.04.2004 включительно будут посчитаны по таблице итогов, а за периоды с 02.03.2004 по 31.03.2004 включительно и с 01.05.2004 по 03.05.2004 включительно будут посчитаны по таблице движений. В третьем случае данные за весь указанный период будут посчитаны по таблице движений.

Источник

Виртуальные таблицы регистра бухгалтерии

Выборка из регистра бухгалтерии и работа с виртуальными таблицами мало чем отличается от выборки из регистра накопления. Поэтому перед чтением данной статьи рекомендую ознакомиться со статьей про работу с виртуальными таблица регистра накопления. В данной статье подробно будет рассмотрена только та функциональность, которая есть только у регистра бухгалтерии.

Выборка из регистра бухгалтерии

Помимо выборки из основной таблицы регистра бухгалтерии в запросе можно выбрать данные из таблицы субконто.

Выборка значений субконто:

Виртуальные таблицы регистра бухгалтерии

У регистра бухгалтерии немного больше виртуальных таблиц, чем у регистра накопления:

При этом у регистра бухгалтерии без поддержки корреспонденции не будет виртуальной таблицы ОборотыДтКт.

ДвиженияССубконто

Виртуальная таблица ДвиженияССубконто содержит в себе поля как основной таблицы регистра бухгалтерии, так и таблицы значений субконто:

Данная таблица получается соединением основной таблицы и таблицы значений субконто. Из всех виртуальных таблиц только данная таблица может содержать неактивные записи (поле Активность = Ложь), но при этом в самой таблице есть поле Активность, поэтому неактивные записи можно будет исключить в запросе.

Таблица ДвиженияССубконто имеет следующие параметры:

Данный запрос выберет первые 10 записей за период между &НачДата и &КонДата, с отбором по счету дебета = &СчетДт, выборка будет отсортирована по полю Организация.

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

Как правило данную таблицу используют для получения проводок вместе с субконто.

Виртуальная таблица остатки

Отличия от виртуальной таблицы регистра накопления

Виртуальная таблица остатков регистра бухгалтерии очень сильно похожа на виртуальную таблицу остатков регистра накопления, поэтому рассмотрим только основные отличия.

Во-первых помимо измерений регистра бухгалтерии в таблице остатков присутствуют поля Счет и субконто. Количество субконто равно максимальному количеству субконто в плане счетов.

В нашем примере в настройках плана счетов было указано максимум два субконто, поэтому в таблице есть два поля для субконто: Субконто1 и Субконто2. Если на каком-то счете присутствует только одно субконто, то в поле Субконто2 будет NULL.

Во-вторых, для каждого ресурса создается по пять полей:

В поле Остаток будет остаток, как он хранится в таблице итогов. Дебетовый остаток будет больше нуля, кредитовый меньше.

Поля ОстатокДт и ОстатокКт рассчитываются по разному, в зависимости от вида счета:

В данном примере счете 41.01 активный, счет 60 — пассивный, а счета 76.01 и 76.02 — активно-пассивные.

Даже если у активного счета остаток будет меньше нуля, то он будет отображаться как дебетовый остаток, но с минусом. Аналогично с пассивным счетом: если остаток больше нуля, то он будет по кредиту, но с минусом:

Признак Балансовый у измерений и ресурсов никак не влияет на количество полей.

В-третьих, есть отличие при формировании виртуальной таблицы. Если в таблице итогов последние рассчитанные итоги были за июль 2021 года (то есть в таблице итогов есть запись с датой 01.07.2021 с остатком на конец июня и оборотом за июль), то при получении остатка на 20 июля для регистра накопления будет получен остаток из текущих итогов и по записям таблицы движений за период 20.07.2021 — 01.11.3999 будет досчитан остаток на 20 июля.

У регистра бухгалтерии в одной строке с остатком хранится оборот за текущий месяц, поэтому к остатку на конец июня будет прибавлен дебетовый оборот за июль, вычтен кредитовый оборот за июль, а потом от этого остатка по движениям за 20.07.2021 — 31.07.2021 будет досчитан остаток на 20 июля 2021.

В-четвертых, есть два новых параметра: УсловиеСчета и Субконто:

УсловиеСчета позволяет задать отбор по счетам учета, можно указать несколько счетов.

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

Источник