Как получить данные по субконто из базы 1С с помощью прямого SQL-запроса?

При работе с внешними системами аналитики, такими как Power Query в Excel или Power BI, часто возникает необходимость получать данные напрямую из базы данных SQL, минуя интерфейс 1С. Одна из самых сложных задач в этом процессе — извлечение данных из регистра бухгалтерии, особенно когда речь идет об аналитике (субконто). Давайте подробно разберем, как устроена структура хранения этих данных и как правильно составить запрос, чтобы получить информацию о контрагентах, договорах или номенклатуре.

Понимаем структуру хранения регистра бухгалтерии в SQL

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

1. Основная таблица движений (например, _AccRg2428): здесь хранятся основные реквизиты проводки — период, регистратор, счет дебета и кредита, сумма и количество. Однако полей субконто здесь часто либо нет, либо они содержат только идентификаторы, если субконто являются балансовыми.
2. Таблица значений субконто (например, _AccRgED2430): именно здесь хранятся значения аналитики. Буква ED в названии таблицы означает "Extra Data" (дополнительные данные). Каждая запись в этой таблице связана с записью в основной таблице по ключевым полям: периоду, номеру строки и регистратору.

Для того чтобы выяснить точные имена таблиц в вашей базе, мы рекомендуем использовать встроенную функцию 1С ПолучитьСтруктуруХраненияБазыДанных(). Это самый надежный способ сопоставить объекты метаданных 1С с физическими таблицами SQL.

Шаг 1: Поиск имен таблиц и полей

Проанализируем ситуацию, когда программисты не могут предоставить готовую структуру. Мы можем запустить в консоли запросов 1С простой скрипт, который выведет нам карту соответствия имен. Если же доступа к конфигуратору нет, воспользуемся логикой именования. Поля субконто в таблице _AccRgED обычно именуются как _ValueNNN_TYPE и _ValueNNN_RRef, где NNN — это внутренний номер реквизита.

Важно помнить, что субконто в 1С — это составной тип данных. В SQL это означает, что для одного субконто выделяется несколько колонок:

• _TYPE — двоичный код типа данных (например, справочник "Контрагенты" или "Договоры").
• _RRef — уникальный идентификатор (GUID) конкретной записи в таблице справочника.

Шаг 2: Объединение основной таблицы и таблицы субконто

Чтобы получить сумму вместе с именем контрагента, нам нужно выполнить операцию LEFT JOIN между основной таблицей регистра и таблицей субконто. Рассмотрим пример SQL-запроса, который собирает эти данные воедино:

SELECT 
    CAST(Main._Period AS DATETIME) AS Дата,
    CAST(Main._RecorderRRef AS UNIQUEIDENTIFIER) AS ID_Регистратора,
    Main._Amount AS Сумма,
    CAST(Sub._Value1_RRef AS UNIQUEIDENTIFIER) AS ID_Контрагента
FROM 
    _AccRg2428 AS Main
LEFT JOIN 
    _AccRgED2430 AS Sub ON Main._Period = Sub._Period 
    AND Main._RecorderTRef = Sub._RecorderTRef 
    AND Main._RecorderRRef = Sub._RecorderRRef 
    AND Main._LineNo = Sub._LineNo
WHERE 
    Main._Period >= '2023-01-01' AND Main._Period <= '2023-12-31'

В данном примере мы используем _LineNo (номер строки), чтобы гарантировать точность соединения. Без этого поля при наличии нескольких проводок в одном документе данные могут "размножиться" или перемешаться.

Шаг 3: Получение понятных имен (Join со справочниками)

Полученный ID_Контрагента в формате GUID мало полезен для отчета в Excel. Нам нужно "подтянуть" наименование из таблицы справочника контрагентов (например, _Reference150). Разберем, как это сделать:

SELECT 
    CAST(Main._Period AS DATETIME) AS Дата,
    Main._Amount AS Сумма,
    Contr._Description AS НаименованиеКонтрагента
FROM 
    _AccRg2428 AS Main
LEFT JOIN 
    _AccRgED2430 AS Sub ON Main._Period = Sub._Period 
    AND Main._RecorderRRef = Sub._RecorderRRef 
    AND Main._LineNo = Sub._LineNo
LEFT JOIN 
    _Reference150 AS Contr ON Sub._Value1_RRef = Contr._IDRRef
WHERE 
    Main._AccountDtRRef = 0x80DA00505691456A011E63C2A5D5D9A5 -- Пример фильтра по счету

Обратите внимание: 1С хранит GUID в инвертированном виде по сравнению со стандартом SQL. При использовании CAST(... AS UNIQUEIDENTIFIER) порядок байтов может измениться. Если вы планируете сравнивать GUID в Power Query, лучше оставлять их в исходном бинарном виде binary(16) или использовать специальные функции конвертации.

Альтернативные способы решения проблемы

Если прямой SQL-запрос кажется слишком сложным из-за постоянно меняющихся имен таблиц (после реструктуризации базы имена _Fld могут измениться), проанализируем другие варианты:

1. Использование OData (REST API): Это наиболее современный и безопасный способ для Power Query. Вам достаточно включить стандартный интерфейс OData в 1С, и вы сможете обращаться к данным по понятным именам: AccountingRegister_Хозрасчетный. Система сама выполнит все JOIN на стороне сервера 1С и отдаст вам готовый результат.
2. Создание SQL Views (Представлений): Попросите администратора базы данных или программиста 1С создать в SQL Server "Представление". Внутри него будет написан сложный запрос со всеми соединениями, а для вас это будет выглядеть как обычная плоская таблица с колонками "Дата", "Сумма", "Контрагент".
3. Консоль запросов с генерацией SQL: В профессиональных инструментах для разработчиков 1С есть возможность написать запрос на человеческом языке 1С (через конструктор), а затем нажать кнопку "Показать SQL". Полученный код можно скопировать и вставить в Power Query с минимальными правками.

Важные рекомендации по безопасности

Прямое чтение из базы SQL — это мощный инструмент, но он требует осторожности. Всегда используйте инструкцию WITH (NOLOCK) при обращении к таблицам 1С, чтобы ваши отчеты не блокировали работу пользователей в программе. 1С очень чувствительна к блокировкам таблиц итогов и движений.

Выясним причину, по которой генератор запросов может не видеть субконто: часто такие инструменты анализируют только основную таблицу регистра. Если вы понимаете, что данных нет в _AccRg<номер>, сразу ищите соответствующую таблицу _AccRgED<номер>. Сопоставив их, вы получите полную картину выручки с нужной вам детализацией по контрагентам.