1С параметры виртуальной таблицы остатки

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

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

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

Давным давно мной был создан блог DevelPlatform, в котором были статьи по разработке на платформе 1С, администрированию, онлайн-инструменты, а также немного о платформе .NET.

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

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

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

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

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

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

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

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

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

Сторона СУБД

Отмечу, что для экспериментов использовал простую тестовую конфигурацию, ссылка на которую приведена в конце статьи. В конфигурации созданы два регистра накопления различного вида, а также два документа для выполнения + и – движений по регистрам. Узнать подробнее о составе объектов тестовой конфигурации Вы можете либо скачав файл конфигурации, либо прочитав статью "Регистры накопления. Структура хранения в базе данных". Используемая версия платформы 8.2.17.153.

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

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

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

Вид регистра "Остатки"

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Установка в данную область жесткого диска невозможна

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

Запрос принимает также три параметра: "НачалоПериода", "КонецПериода" и "Склад". Как начало периода возьмем начало 2012 года, конец периода – 13 апреля 2013 00:00:00. Склад пусть будет "Склад №1".

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

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

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

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

Заключение

Эксперименты с регистром показали, что для виртуальной таблицы "Обороты" платформа 1С:Предприятие 8 имеет разный принцип построения SQL-запросов к базе данных в зависимости от вида регистра накопления ("Остатки" или "Обороты"). Если вид регистра – "Остатки", то SQL-запрос получает данные по оборотам непосредственно из таблицы движений. В случае, если вид регистра "Обороты", то тогда уже используется таблица итогов оборотов и запрос работает более оптимально, нежели чем для регистра остатков.

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

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

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

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

Что дальше

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

О чем эта статья

Статья описывает физическую реализацию виртуальной таблицы остатков конфигурации, работающей в клиент-серверном режиме работы на примере использования СУБД MS SQL Server.

Применимость

В статье рассматривается платформа «1С:Предприятие» редакции 8.3.5.1383. В актуальной версии платформы возможны некоторые изменения в тексте, описанного в материале, запроса T-SQL, выполняемого на стороне сервера СУБД.

Устройство виртуальной таблицы остатков

Рассмотрим, в какой запрос к СУБД трансформируется запрос с использованием виртуальной таблицы остатков регистра накопления. Для примера будет рассматриваться следующий текст запроса:

Сначала при помощи метода глобального контекста ПолучитьСтруктуруХраненияБазыДанных() получим список таблиц базы данных, в которых хранятся данные регистра накопления «ТоварныеЗапасы»:

Состав полей основной таблицы регистра накопления и таблицы итогов приведен ниже:

Хранение итогов для данного регистра настроено в режиме «1С:Предприятие 8» следующим образом:

Параметры в рассматриваемом запросе заполним следующим образом:


Платформа преобразует текст запроса в следующий запрос, который и будет выполнен на сервере СУБД:

Разберем подробнее полученный запрос.

Сначала при помощи первого запроса, входящего в объединение, выбираются данные из итоговой таблицы AccumRgT85. Итоги получаются на дату хранения текущих итогов (01.11.3999), дополнительно накладывается условие на поле Склад (поскольку такое условие использовалось в параметрах виртуальной таблицы). Дополнительно выполняется проверка на отсутствие в результате строк с нулевыми остатками.

Обратите внимание, что производится группировка по выбранным в тексте запроса измерениям. Именно поэтому не требуется в тексте на языке запросов «1С:Предприятие» дополнительно выполнять группировку по измерениям.

Во втором запросе объединения используется таблица движений регистра AccumRg81. В зависимости от вида движения (если RecordKind равно 0, то это Приход, в противном случае – Расход) проставляется знак в выражении. Платформа выбирает данные за период с даты, указанной в качестве параметра виртуальной таблицы, по дату хранения текущих итогов (01.11.3999).

Кроме этого отбираются только активные записи, поле Склад должно быть равно заданному значению. Как и в первом запросе объединения, здесь также производится группировка по выбранным измерениям и отбрасываются записи с нулевыми значениями ресурсов.

Если используется СУБД MS SQL Server и для базы данных установлено смещение дат 2000, то все даты будут храниться с указанным смещением, т.е. вместо 01.11.3999 Вы увидите 01.11.5999.

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

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

Обратите внимание на следующее условие в тексте запроса:

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

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

PDF-версия статьи для участников группы ВКонтакте

Если Вы еще не вступили в группу – сделайте это сейчас и в блоке ниже (на этой странице) появятся ссылка на скачивание материалов.

Читайте также:  Съемка с зонтом на просвет

Статья в PDF-формате

Комментарии / обсуждение (4):

А как получить это запрос, который передаётся в SQL?

Получить такой запрос можно из технологического журнала, который формирует платформа. Также существуют консоли запросов, которые умеют показывать запрос, передаваемый на СУБД. Например, консоль запросов для управляемого приложения с сайта ИТС.

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

ИТ-шники “от рождения” конечно поймут запрос “с листа”

Урок 6. Виртуальные таблицы и их использование в конструкторе запросов

Задача 1: Получить остатки номенклатуры на указанном складе на конец месяца.

Задача 2: Получить актуальную цену на конец месяца по указанной номенклатуре и типу цен.

Новые механизмы: заполнение параметров виртуальных таблиц.

Теоретическая часть урока №6

У некоторых объектов метаданных помимо основной таблицы в базе данных присутствуют виртуальные таблицы. Они облегчают доступ к некоторой информации содержащейся в основной таблице. Использовать данные виртуальных таблиц можно при помощи запросов, либо при помощи специальных методов встроенного языка 1с 8.

Рассмотрим основные виды виртуальных таблиц 1с для различных объектов метаданных:

  • СрезПоследних. Метаданные: периодические регистры сведений. Данная таблица позволяет получить последние актуальные данные на указанную дату, такие данные называются срезом последних. Возможно получить срез как в целом по регистру, так и по одному, либо нескольким измерениям;
  • СрезПервых. Метаданные: периодические регистры сведений. Данная таблицы позволяет получить первые актуальные данные появившееся в регистре в указанную дату, либо после нее, такие данные называются срезом первых. Возможно получить срез как в целом по регистру, так и по одному, либо нескольким измерениям;
  • Остатки. Метаданные: регистры бухгалтерии, регистры накопления с видом регистра остатки. Данная таблица позволяет получить остатки по ресурсам регистра на указанную дату. Возможно получать остатки как в целом по регистру, так и в разрезе определенных измерений, для регистров бухгалтерии в измерения также включаются счет и субконто относящиеся к данному счету;
  • Обороты. Метаданные: регистры бухгалтерии, регистры накопления. Данная таблица позволяет получить обороты по ресурсам регистра за указанный период. Возможно получать обороты как в целом по регистру, так и в разрезе определенных измерений, для регистров бухгалтерии в измерения также включаются счет, субконто, кор. счет, кор. субконто;
  • ОстаткиИОбороты. Метаданные: регистры бухгалтерии, регистры накопления с видом регистра остатки. Данная таблица позволяет получать остатки по ресурсам на начало и конец указанного периода и обороты по ресурсам за указанный период. Возможно получать остатки и обороты как в целом по регистру, так и в разрезе определенных измерений, для регистров бухгалтерии в измерения также включаются счет и субконто относящиеся к данному счету;
  • ОборотыДтКт. Метаданные: регистры бухгалтерии. Данная таблица позволяет получить обороты по ресурсам регистра за указанный период. Возможно получать обороты как в целом по регистру, так и в разрезе определенных измерений, в измерения также включаются дебетовая и кредитовая части проводки: счет дебета, счет кредита, субконто дебета, субконто кредита и т.д.;
  • ДвиженияССубконто. Метаданные: регистры бухгалтерии. Данная таблица позволяет получить записи регистра бухгалтерии вместе со значениями субконто.

Конструктор запросов позволяет работать с виртуальными таблицами регистров. Если у регистра есть виртуальные таблицы, то они будут находится в разделе База данных на вкладке Таблицы и поля после основной таблицы регистра.

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

СрезПоследних и СрезПервых

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

    ПРИМЕЧЕНИЕ! Если вам необходимо задать условия на поля виртуальной таблицы, делать это необходимо именно в параметрах таблицы, а не на вкладке Условия. Все дело в том, что запрос сначала выбирает данные из виртуальной таблицы, учитывая указанные параметры, а уже потом накладывает на него отбор из вкладки Условия. Поэтому условия наложенные в параметрах таблицы отработают в разы быстрее, чем те которые указаны на вкладке Условия.
Остатки в регистре накопления

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

Остатки в регистре бухгалтерии

  • УсловиеСчета. В данном поле задается условие на поле Счет виртуальной таблицы. В отличии поля Условия, для создания условий доступно только измерение Счет;
  • Субконто. В данном поле задается параметр, содержащий массив видов субконто, которые должны анализироваться в данной виртуальной таблице. Например, если вы хотите получить остатки по 41 счету бухгалтерского учета, но вам не нужен разрез по складам, то в данный параметр можно передать массив состоящий из одного элемента: Номенклатура из плана видов характеристик ВидыСубконтоХозрасчетные.

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

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

  • НачалоПериода. Параметр в котором хранится дата начала периода, за который будут браться обороты;
  • КонецПериода. Параметр в котором хранится дата окончания периода, за который будут браться обороты;
  • Периодичность. Определяет группировку по периоду в виртуальной таблице. Если, например, указана периодичность Месяц, то данные в таблице будут сгруппированы по всем измерениям и месяцу, в полях таблицы появится поле Период, в котором будет хранится первый день месяца, в котором было сделано движение регистра. Если оставить поле Периодичность пустым, то периода в полях виртуальной таблицы не будет. Помимо периодичности связанной с временными промежутками есть еще несколько ее типов:
  • Запись. Данные будут выбираться по отдельным записям регистра, точно также как в полной таблице. В полях появляются Период и Регистратор (документ который сделал данное движение);
  • Регистратор. Данные будут сгруппированы по документу сделавшему движения в регистре. Данная группировка удобна как раз тогда, когда вам необходимо иметь разрез по документам. В полях появляются Период и Регистратор;
  • Период. Данные группируются по измерениям регистра за весь период оборота;
  • Авто. Данные группируются до секунды, в полях появляются Регистратор, ПериодСекунда, …. ПериодГод.
Обороты в регистре бухгалтерии

  • УсловиеКорСчета. В данном поле задается условие на поле корреспондирующий счет проводки. В отличии поля Условия, для создания условий доступно только измерение КорСчет;
  • КорСубконто. В данном поле задается параметр, содержащий массив видов субконто корреспондирующего счета, работает аналогично параметру Субконто, см. раздел «Остатки в регистре бухгалтерии».
Читайте также:  Широкие вертикальные полосы на экране телевизора
Остатки и обороты в регистре накопления

  • МетодДополнения. Метод дополнения периодов, данный параметр определяет за какие периоды будут получены движения регистра. Если указано значение Движения, то будут получены периоды, в которых были движения (обороты не равны нулю), если ДвиженияИГраницыПериода, то за периоды, по которым были движения, плюс начальный и конечный периоды, если на их начало был остаток. Данный параметр имеет смысл только, если Периодичность не равна Период. ДвиженияИГраницыПериода является значением по умолчанию, поэтому если нужен данный метод дополнения, поле можно оставить пустым.
Остатки и обороты в регистре бухгалтерии

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

Обороты Дебет Кретит

  • УсловиеСчетаДт. В данном поле задается условие на поле СчетДт виртуальной таблицы, это счет левой части бухгалтерской проводки;
  • УсловиеСчетаКт. В данном поле задается условие на поле СчетКт виртуальной таблицы, это счет правой части бухгалтерской проводки;
  • СубконтоДт. В данном поле задается параметр, содержащий массив видов субконто, которые должны анализироваться для левой (дебетовой) части проводки;
  • СубконтоКт. В данном поле задается параметр, содержащий массив видов субконто, которые должны анализироваться для правой (кредитовой) части проводки;
Движения с субконто

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

Практическая часть урока №6

В данном разделе нам предстоит решить две задачи по пройденной теме.

Задача 1

Получить остатки номенклатуры на указанном складе на конец месяца.

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

  • Создадим новый запрос;
  • Запустим конструктор запросов;
  • В раздел Таблицы перетащим таблицу Хозрасчетный.Остатки из ветки РегистрыБухгалтерии;
  • Выделим данную таблицу и нажмем кнопку Параметры виртуальной таблицы;
  • В поле Период впишем параметр, в который будет передаваться дата на которую будут браться остатки. Так как мы используем таблицу Остатки, в параметр передадим не дату а границу, с видом Включая;
  • В поле УсловиеСчета вручную или используя редактор произвольных выражений вписываем условие на счет, используя оператор В ИЕРАРХИИ, для того чтобы в запрос попали остатки по всем субсчетам счета 41;
  • В поле Субконто зададим параметр, в который передадим массив субконто. В нашей задаче нам нужны только склады и номенклатура, первым элементом массива зададим вид субконто Склады, а вторым Номенклатура;
  • В поле Условие вручную или используя редактор произвольных выражений наложим отбор на склад, который в нашем случае хранится в поле Субконто1.
  • На этом заполнение параметров виртуальной таблицы завершено, нажимаем кнопку ОК;
  • В раздел Поля перетащим Субконто1, Субконто2 и КоличествоОстаток;
  • Перейдем на вкладку Объединения / Псевдонимы;
  • Зададим псевдонимы для полей Субконто1 и Субконто2, назначим для них Склад и Номенклатура соответственно;
  • Запрос готов, нажимаем кнопку «ОК» в нижней части окна конструктора.

В итоге у нас получится запрос со следующим текстом:

Задача 2

Получить актуальную цену на конец месяца по указанной номенклатуре и типу цен.

  • Создадим новый запрос;
  • Запустим конструктор запросов;
  • В раздел Таблицы перетащим таблицу ЦеныНоменклатуры.СрезПоследних;
  • Выделим данную таблицу и нажмем кнопку Параметры виртуальной таблицы;
  • В поле Период впишем параметр, в который будет передаваться дата на которую будет браться срез последних;
  • В поле Условие вручную или используя редактор произвольных выражений наложим отбор на поля ТипЦен и Номенклатура;
  • В раздел Поля перетащим ресурс Цена;
  • Запрос готов, нажимаем кнопку «ОК» в нижней части окна конструктора.

В итоге у нас получится запрос со следующим текстом:

Оцените статью
Добавить комментарий

Adblock
detector