Подсистема справочников и констант.

Предисловие

    Данная статья предназначена для тех, кто пытается разобраться в структуре хранения данных в системе 1С версии 7.7. А также покажет как можно получать данные напрямую из таблиц 1С, минуя программу 1С. Для понимания о чем идет речь в статье необходимо понимать принципы работы 1С версии 7.7 и иметь начальные навыки работы с SQL Server Enterprise Manager-ом и SQL Server Query Analyzer-ом.

Введение

Что являют собой справочники? В понимании 1С это объекты для хранения условной постоянной информации (константы - для хранения постоянной или очень редко изменяющейся информации).
С токи зрения же теории баз данных, справочники являются типичными таблицами (по одной на каждый справочник). А вот с константами - гораздо сложнее. Точнее с первого взгляда проще, но это только с первого взгляда. Так, все константы хранятся в одной таблице _1SCONST, но, также в этой таблице хранятся и значения всех периодических реквизитов справочников.
Все таблицы справочников имеют почти одинаковые имена, эти таблицы именуются первыми 2 символами SC, далее следует десятичное представление справочника. Это десятичное представление - сквозная нумерация всех объектов внутри конфигурации (включая реквизиты и т.д.). В наших примерах таблица справочника будет иметь имя таблицы SC19, т.е. этот справочник был создан 19 по номеру в конфигурации среди всех объектов.

Описание полей таблиц справочников


Табл.1 Описание полей таблиц справочников SCxxx
Название поля  Описание  
ROW_ID Порядковый номер записи в таблице. Тип - Число(int)
ID ID элемента, тип «строка», по этому полю осуществляется связь с таблицами, где в качестве реквизита выбирается справочник, а также с таблицей констант (для периодических реквизитов). Нумерация сквозная, именно этот код должен быть уникальный в пределах таблицы. Тип - Char(9)
CODE Номер элемента (Код) справочника. Тип - Char(n), где n - длина номера справочника. Если длина кода = 0, это поле отсутствует.
DESCR Наименование элемента. Тип - Char(n), где n - длина номера справочника. Если длина кода = 0, это поле отсутствует.
ISMARK Флаг пометки на удаление элемента. Тип - bit. 0 - не помечен, 1 - помечен.
VERSTAMP Количество изменений записи таблицы. Изменением считается любое действие "Изменить (открыть)" + действия при изменении структуры. Тип - Integer.
SPххx Реквизит справочника(типы: Numeric, DateTime, Char(n), n=1:999)
TSPххx Дополнение к реквизиту, заполняется только для неопределенных типов значений (используется совместно с полем «SPххx»). Тип - Char(3)
PARENTID ID элемента являющимся родителем (группой) для текущей записи (элемента). Поле связано с полем ID или же если родителя нет - заполнено пустым ID '     0   '. Тип - Сhar(9). Это поле появляется в таблице справочника, лишь тогда, когда справочник имеет больше 1 уровня.
ISFOLDER Флаг того, запись является элементом или группой. Для групп это поле равно 1, для элементов - 2. Тип - tinyint (0-255).
PARENTEXT  ID элемента являющимся владельцем (этот элемент подчинен владельцу с этим ID).Тип - Сhar(9). Это поле появляется в таблице справочника, лишь тогда, когда справочнику установлено значение "Подчинен" одному из справочников системы.
 
    Особое внимание надо уделить полям «TSP». Это поле создается лишь тогда, когда реквизит (измерение, реквизит) имеет неопределенный тип (длина ID кода 23 символа). Опытным путем было установлено:
это поле по умолчанию заполняется пустой строкой (3 пробела).
  • Для невыбранного типа (заполняется по умолчанию):
    • TSP235 = '  ' (3 пробела), при этом связанное неопределенное поле SP235 заполняется значением 'U                      ' (символ U и 22 пробела), что скорее всего означает «Undefined», т.е. неопределенный.
  •  Для типов «Число»:
    • связанное поле SP235 заполняется строковым значением числа, но с символом «N» вначале строки (например 'N                     2' для целого числа 2). Возможные значения TSP235:    
    'F30' - число с плавающей запятой (float);
    '320' - число, состоящее только из десятичных (0.99, т.е. Число(3,2)).
    'A00' - целое число (int);
  •  Для типов «Строка»:
    • связанное поле SP235 заполняется строкой, но с символом 'S' слева (например 'SАбвгдежзклимно        '). Возможные значения TSP235 в этом случае: количество знаков, определенных для строкового значения, например, '14'  - количество знаков в строке, в 36-ричном формате (_IDToStr). Следует отметить, хотя 1С и назначает длину строки больше 22 символов, но фактически в этом поле можно хранить только 22 символа (1 разряд приходиться на обозначение типа поля 'S').
  • Для типов «Дата»:
    • связанное поле SP235 заполняется строкой, но с символом 'D' слева (например 'D20050303              ').  TSP235 - '   ' (3 пробела), т.е. поле пустое.
  • Для типов 1С (Справочник, Документ, Перечисление, Счет, и т.д.):
    • связанное поле SP235 заполняется строкой включающий полный идентификатор объекта (его тип, вид, внутренний ИД код). Следует отметить, что для типов «Перечисление» нельзя не назначать вид, так как не назначение вида приведет к несовпадению типов и не заполнению  неопределенного поля. Возможные значения TSP235:
'0  ' - был назначен только тип объекта, вид не назначен (например «НазначитьТип(«Справочник»)»)
'1  ' - был назначен как тип объекта, так и его вид (например «НазначитьТип(«Справочник.Клиенты»)»)

Описание полей таблицы констант и периодических реквизитов справочников


Описание таблицы _1SCONST находиться здесь.
Необходимо только учесть, что значения периодических реквизитов элементов справочников хранятся в таблице _1SCONST с заполненным полем OBJID, которое равно полю ID таблицы справочника. Для констант же, значение поля OBJID всегда равно '     0   '. Также необходимо учесть, что выбрать все периодические значения всех реквизитов одного элемента (одной записи) невозможно. Дело в том, что в поле OBJID хранится краткий (строка 9 символов) ID, а такое значение ID не подразумевает определения вида справочника. Соответственно чтобы получить значение конкретного периодического реквизита надо знать десятичное значение реквизита (_StrToID). Т.е., выбрав только с условием по OBJID получим периодические реквизиты всех справочников, с таким ID, а не только одного. Но, поскольку нумерация всех ID объектов конфигурации (включая и все реквизиты) сквозная, то не может быть в двух разных справочников реквизитов с одинаковым ID реквизита. Соответственно, необходимым условием для получения значений периодических реквизитов является как условие по полю OBJID (ID элемента справочника), так и по полю ID (десятичное значение ID реквизита справочника).

Примеры запросов

Пример 1. 

    Получение всех записей справочника (кроме периодических реквизитов). SC19 - имя таблицы справочника
Код:
SELECT
               *
FROM
                SC19
  

Пример 2. 

    Получение записей только групп справочника (кроме периодических реквизитов). ISFOLDER - флаг того это элемент или группа элементов.
Код:
SELECT
               *
FROM
                SC19
WHERE
                ISFOLDER = 1
  

Пример 3. 

    Получение записей только элементов (не групп) справочника (кроме периодических реквизитов)
Код:
SELECT
               *
FROM
                SC19
WHERE
                ISFOLDER = 0

Пример 4. 

    Получение записей только непомеченных элементов (не групп) справочника (кроме периодических реквизитов). ISMARK - флаг пометки элемента (или группы) на удаление.
Код:
SELECT
               *
FROM
                SC19
WHERE
                ISFOLDER = 0  AND ISMARK = 0
  

Пример 5.

    Получение записей элементов с периодическим реквизитом, значение которого получается на максимальную дату (возможно и будущую). В данном примере условие TabConst.ID = 101 необходимо для отбора периодических значений только по реквизиту с десятичным ID кодом равным 101.
Код1:
SELECT
               TabSpr.*, ISNULL(TabConst.VALUE,'')
FROM
               SC19 As TabSpr
LEFT OUTER JOIN _1SCONST As TabConst
               ON ((TabSpr.ID = TabConst.OBJID)
                       AND (TabConst.ID = 101)
                       AND (TabConst.DATE =
                                                 (SELECT MAX(TabConstl.DATE)
                                                  FROM _1SCONST AS TabConstl
                                                  WHERE TabConstl.OBJID = TabConst.OBJID
                                                                 AND TabConstl.ID = TabConst.ID))
               )
  
Код2, в этом примере могут возвращаться и значения NULL для тех реквизитов, для которых не были установлены периодические реквизиты:
SELECT
               TabSpr.*,
               (SELECT
                             (TabConst.VALUE)
                FROM
                              _1SCONST As TabConst
                WHERE
                              (TabSpr.ID = TabConst.OBJID)
                              AND (TabConst.ID = 101)
                              AND (TabConst.DATE =
                              (SELECT MAX(TabConstl.DATE)
                              FROM _1SCONST AS TabConstl
                              WHERE TabConstl.OBJID = TabConst.OBJID
                                            AND TabConstl.ID = TabConst.ID)
                              )
               )
FROM SC19 As TabSpr
Код3, применение конструкции UNION, в первом запросе получем список только тех записей, для которых есть установленные периодические реквизиты, а во втором - всех остальных:
SELECT
               TabSpr.*, ISNULL(TabConst.VALUE,'')
FROM
               SC19 As TabSpr
LEFT OUTER JOIN _1SCONST As TabConst
               ON ((TabSpr.ID = TabConst.OBJID) AND (TabConst.ID = 101))
WHERE
               (TabConst.DATE =
                                 (SELECT MAX(TabConstl.DATE)
                                  FROM _1SCONST AS TabConstl
                                  WHERE (TabConstl.OBJID = TabConst.OBJID)
                                                 AND (TabConstl.ID = TabConst.ID)
                                  )
                )

UNION ALL
SELECT
                TabSpr.*, ''
FROM
                SC19 As TabSpr
WHERE
                NOT EXISTS(
                            SELECT *
                            FROM _1SCONST As TabConst
                            WHERE (TabSpr.ID = TabConst.OBJID)
                                           AND (TabConst.ID = 101)
                            )
 

Пример 6.

    Получение записей элементов с периодическим реквизитом, на конкретную дату.
    Периодический реквизит получается на 11.03.2006. В данном примере условие TabConst.ID = 101 необходимо для отбора периодических значений только по реквизиту с десятичным ID кодом равным 101.
Код1:
SELECT
               TabSpr.*, ISNULL(TabConst.VALUE,'')
FROM
               SC19 As TabSpr
LEFT OUTER JOIN _1SCONST As TabConst
               ON ((TabSpr.ID = TabConst.OBJID)
                       AND (TabConst.ID = 101)
                       AND (TabConst.DATE =
                                                 (SELECT MAX(TabConstl.DATE)
                                                  FROM _1SCONST AS TabConstl
                                                  WHERE TabConstl.OBJID = TabConst.OBJID
                                                                 AND TabConstl.ID = TabConst.ID
                                                                 AND TabConstl.DATE <=
                                                                           Convert(DateTime,'20060311',112)
                                                  )
                                    )
                        )
  

Пример 7.

    Рассмотрим теперь пример получения значений непериодических реквизитов справочников, которые являются документами или элементами справочника. В системе 1С получение значений таких реквизитов осещуствляется обращением к реквизитам и их атрибутам. Например, получение значение реквизита "Менеджер" элемента справочника "Контрагенты" (наименование менеджера). В 1С это легко реализуется, например, если СпрКонтрагенты является объектом "Справочник.Контрагенты" и спозиционирован на конкретном элементе (например "НайтиПоНаименованию("Иванов А. А.")") - то получение менеджера этого элемента осуществляется так:  "СпрКонтрагенты.Менеджер.Наименование". Но в самой таблице справочника "Контрагенты" (допустим это таблица "SC191") в поле, отвечающем за реквизит "Менеджер" (например "SP10494") будут значение ID элемента справочника "Менеджеры", а не наименование менеджера (и это вполне нормально). Само же наименование менеджера хранится в другой таблице, это таблица "Сотрудники" (SC258).
   Итак, для этого примера надо выполнить запрос, выполняющий 2 действия: 1 - позиционирование на элемент с наименованием "Иванов А. А.", 2 - получение наименования менеджера, для спозиционированного элемента справончика Контрагенты.
Первая часть запроса будет выглядеть так
Код1:
SELECT
               TabSpr.DESCR As Наименование
FROM
               SC191 As  TabSpr
WHERE
               TabSpr.DESCR = 'Иванов А. А.'
  Но приведенный више код содержит ошибку. Дело в том, что поле DESCR определено как Char, и его длина строго задана и равна длине наименования для справочника "Контрагенты". Напримр если длина наименования 50 символов, то предыдущий запрос надо было написать так:
Код2:
SELECT
               TabSpr.DESCR As Наименование
FROM
               SC191 As  TabSpr
WHERE
               TabSpr.DESCR = 'Иванов А. А.                                      '
   Как видно, в условии происходит сравнение на полную строку, включая недостающие пробелы. Для того, чтобы не заполнять строку поиска недостающими пробелами можно воспользоваться строкой усечения пробелов справа, или же определить переменную как Char(50) и поиск вести по значению этой переменной.
Для первого случая пример запроса будет такой:
Код3:
SELECT
               TabSpr.DESCR As Наименование
FROM
               SC191 As  TabSpr
WHERE
               RTRIM(TabSpr.DESCR) = 'Иванов А. А.'

Для второго случая пример запроса будет такой:
Код4:
DECLARE @NAIM CHAR(50)
SET @NAIM = 'Иванов А. А.'
SELECT
               TabSpr.DESCR As Наименование
FROM
               SC191 As  TabSpr
WHERE
               TabSpr.DESCR = @NAIM

Вторая часть запроса, получение имени менеджера. Необходимо сделать выборку с 2 таблиц. Это можно сделать как минимум двумя способами. В первом примере выборка с 2 таблиц с услоивем:
Код5:
DECLARE @NAIM CHAR(50)
SET @NAIM = 'Иванов А. А.'
SELECT
               TabSpr.DESCR As Наименование, TabManag.DESCR As Менеджер
FROM
               SC191 As  TabSpr,
               SC258 As TabManag
WHERE
               TabSpr.DESCR = @NAIM
                AND TabSpr.SP10494 = TabManag.ID

Во втором примере выборка с 2 таблиц с объединением. Этот код аналогичный предыдущему, за исключением того, что происходит объединение с условием а не выборка из таблиц с условием:
Код6:
DECLARE @NAIM CHAR(50)
SET @NAIM = 'Иванов А. А.'
SELECT
               TabSpr.DESCR As Наименование, TabManag.DESCR As Менеджер
FROM
               SC191 As  TabSpr
INNER JOIN SC258 As TabManag ON TabSpr.SP10494 = TabManag.ID
WHERE
               TabSpr.DESCR = @NAIM
    Но эти оба примеры не совсем корректные. Дело в том, что запросы работают по полном обьединении (или полном условии), т.е. в выборку попадут лишь те записи из таблицы Контрагентов, для которых есть записи в таблице Сотрудников (т.е. поле "Менеджер" справочника Контрагенты заполнено). Если же для элемента с наименованием 'Иванов А. А.' поле Менеджер пустое (в таблице находиться или NULL или значение '     0   ', именно так 1С хранит значения невыбранных реквизитов), то в выборку не попадет строка таблицы, так как не выполняется условие на вхождение в таблицу Сотрудников (в таблице SC258 в столбце ID нет ни одной записи для которой есть значение '     0   ').
     Первый пример будет выглядеть так:
Код7:
DECLARE @NAIM CHAR(50)
SET @NAIM = 'Иванов А. А.'
SELECT
               TabSpr.DESCR As Наименование, TabManag.DESCR As Менеджер
FROM
               SC191 As  TabSpr,
               SC258 As TabManag
WHERE
               TabSpr.DESCR = @NAIM
                AND TabSpr.SP10494 *= TabManag.ID
   Второй пример выборки с связыванием 2 таблиц:
Код8:
DECLARE @NAIM CHAR(50)
SET @NAIM = 'Иванов А. А.'
SELECT
               TabSpr.DESCR As Наименование, TabManag.DESCR As Менеджер
FROM
               SC191 As  TabSpr
LEFT OUTER JOIN SC258 As TabManag ON TabSpr.SP10494 = TabManag.ID
WHERE
               TabSpr.DESCR = @NAIM


Пример 8.

    Рассмотрим пример получения значений записей элементов справочников с их родителями (группами)
Для примера возьмем тот же справочник "Контрагенты" (допустим это таблица "SC191"). Самый простой пример - получение родителя для каждого элемента (не для родителей).
Код1:
SELECT
               TabSpr.DESCR As Наименование, TabSprGr1.DESCR As Родитель
FROM
               SC191 As  TabSpr
LEFT OUTER JOIN SC191 As TabSprGr1 ON TabSpr.PARENTID = TabSprGr1.ID
WHERE
               TabSpr.ISFOLDER = 2
   В этом коде условие TabSpr.ISFOLDER = 2 необходимо для отбора только элементов (не групп). Левое внешнее связывание применяется для того чтобы вывести все элементы, а не только те, у которых есть выбранные родители (в случае с INNER JOIN).
   Рассмотрим теперь более сложный пример. Получим всех родителей для элементов (т.е. включая и родителей родителей). Для получения родителей надо знать максимальное количество родителей. Это число легко узнать с помощью команды "Метаданные.Справочник(х).КоличествоУровней". Приведем пример для значения количества уровней равным 4.
Код2:
SELECT
              TabSprGr3.DESCR As Родитель3,
              TabSprGr2.DESCR As Родитель2,
              TabSprGr1.DESCR As Родитель,
              TabSpr.DESCR As Наименование
FROM
              SC191 As  TabSpr
LEFT OUTER JOIN SC191 As TabSprGr1 ON TabSpr.PARENTID = TabSprGr1.ID
LEFT OUTER JOIN SC191 As TabSprGr2 ON TabSprGr1.PARENTID = TabSprGr2.ID
LEFT OUTER JOIN SC191 As TabSprGr3 ON TabSprGr2.PARENTID = TabSprGr3.ID
WHERE
               TabSpr.ISFOLDER = 2
   В этом коде условие TabSpr.ISFOLDER = 2 необходимо для отбора только элементов (не групп). Количество внешних связываний равно количеству уровней справочника - 1 (в приведенном примере 3).
   В результате выполнения данного запроса получаются данные по всем возможным родителям, даже елси родителей у элемента 1 или 2, то все равно будет 3 колонки с родителями, но в таком случае в качестве родителя будет значение NULL. Для того чтобы в первой колонке всегда был родитель (если есть у элемента родитель), то необходимо выполнить вот такой запрос:
Код3:
SELECT
               CASE WHEN TabSprGr3.DESCR IS NOT NULL THEN TabSprGr3.DESCR
               ELSE CASE WHEN TabSprGr2.DESCR IS NOT NULL THEN TabSprGr2.DESCR
               ELSE TabSprGr1.DESCR END END As Родитель3,
               CASE WHEN (TabSprGr2.DESCR IS NOT NULL)
               AND (TabSprGr3.DESCR IS NOT NULL) THEN TabSprGr2.DESCR
               ELSE TabSprGr1.DESCR END As Родитель2,
               CASE WHEN (TabSprGr2.DESCR IS NOT NULL)
               AND (TabSprGr3.DESCR IS NOT NULL) THEN TabSprGr1.DESCR
               ELSE NULL END As Родитель,
               TabSpr.DESCR As Наименование
FROM
               SC191 As  TabSpr
LEFT OUTER JOIN SC191 As TabSprGr1 ON TabSpr.PARENTID = TabSprGr1.ID
LEFT OUTER JOIN SC191 As TabSprGr2 ON TabSprGr1.PARENTID = TabSprGr2.ID
LEFT OUTER JOIN SC191 As TabSprGr3 ON TabSprGr2.PARENTID = TabSprGr3.ID
WHERE
               TabSpr.ISFOLDER = 2
   В этом коде условие TabSpr.ISFOLDER = 2 необходимо для отбора только элементов (не групп).
   Вместо проверки на NULL можно вопспользоатеся функцией COALESCE, которая ищет слева направо в переданных параметрах значения не равные NULL, но тогда усложняется процесс получения различных значений групп на каждом уровне, поэтому рекомендуется это делать уже на клиентском приложении (после выполнения запроса).

Пример 9.

    Следующий этап - выборка элементов с подчиненного справочника. Для примера возмьем справочник "Контрагенты" (SC191) и справочник "ДенежныеСчета" (SC146) подчиненного справочник Контрагенты. 
Код1:
SELECT
               TabSpr.DESCR As Наименование,
               TabRS.SP143 As НомерСчета
FROM
               SC191 As  TabSpr
LEFT OUTER JOIN SC146 As TabRS ON TabSpr.ID = TabRS.PARENTEXT
WHERE
               TabSpr.ISFOLDER = 2
   В этом коде условие TabSpr.ISFOLDER = 2 необходимо для отбора только элементов (не групп). Ведь в 1С подчиненные элементы справочника существуют только в элементов. В результат выборки попадут и помеченные на удаление подчиненные элементы. Если надо получить только непомеченные подчиненные элементы (расчетные счета), тогда текст запроса должен быть таким:
Код2:
SELECT
               TabSpr.DESCR As Наименование,
               TabRS.SP143 As НомерСчета
FROM
               SC191 As  TabSpr
LEFT OUTER JOIN SC146 As TabRS ON TabSpr.ID = TabRS.PARENTEXT
               AND TabRS.ISMARK = 0
WHERE
               TabSpr.ISFOLDER = 2
   В этом коде условие TabRS.ISMARK = 0 необходимо включать как раз в условие связывание, так как если включить условие в раздел WHERE - то те, записи справочника Контрагенты, у которых есть запись в подчиненном справочнике и этот элемент помечен на удаление - то эти элементы вообще не войдут в результат запроса (справочника Контрагенты).

Пример 10.

    Получение списка єлементов справочника со списком значений периодического реквизита за период. Другими словами получается, к примеру, список валют и все значения курсов по всем валютам. Для примера возмьем справочник "Валюты" (SC15) и периодический реквизит "Курс" (ID = 17). Пример приведен только по одному элементу справочника Валюты с ID = 2.
SELECT
	TabConst.DATE AS ДатаПер,
	TabConst.VALUE AS ЗначПер,
	TabSpr.*
FROM
	_1SCONST As TabConst
INNER JOIN SC15 As TabSpr
	ON (TabSpr.ID = TabConst.OBJID)
WHERE 
	(TabConst.ID = 17)
	AND (TabConst.DATE BETWEEN '19800101' AND '20071231') -- ограничение по периоду периодических значений
	AND TabSpr.ID = '     2   ' -- условие по элементу справочника

© pvase 2007