При переходе на использование прямых запросов в среде 1С:Предприятие 7.7 разработчики часто сталкиваются со специфической структурой хранения данных. Особые трудности вызывает получение истории периодических реквизитов (например, цен номенклатуры, курсов валют или ставок налогов). В файловом варианте базы данных (DBF) для выполнения высокоскоростных запросов традиционно применяется внешняя компонента 1sqlite.dll.
В этой статье мы подробно разберем, как правильно составить запрос к периодическим реквизитам подчиненного справочника на примере цен номенклатуры в типовой конфигурации «Торговля и Склад 7.7» (ТиС), проанализируем типичные ошибки разработчиков и рассмотрим наиболее эффективные механизмы решения этой задачи.
Рассмотрим ситуацию, когда программисту требуется получить цену определенного типа на выбранную дату для конкретной номенклатурной позиции. Типичный ошибочный запрос к базе данных выглядит следующим образом:
ТекстЗапросаЦены = "
|SELECT спр.id as [Ссылка :Справочник.Цены],
|(
| SELECT value FROM __1S_Const
| WHERE id=:ТипЦены AND (date < :ВыбДата or date = :ВыбДата) and objid = :Владелец
| ORDER BY date desc LIMIT 1
|) as Цена
|FROM Справочник_Цены as спр
|";
При выполнении этого кода система стабильно возвращает пустое значение. Давайте разберем по шагам, почему этот запрос не работает и какие логические нестыковки в нем присутствуют.
Во-первых, проанализируем структуру хранения периодических реквизитов. В платформе 1С 7.7 все изменения периодических реквизитов записываются в единую системную таблицу истории констант — _1SCONST (в терминологии SQLite она может быть представлена как __1S_Const или _1S.Const).
Поле objid в этой таблице хранит идентификатор объекта, к которому привязан периодический реквизит. Автор запроса совершает критическую ошибку, передавая в параметр :Владелец ссылку на элемент справочника Номенклатура. Однако в конфигурации «Торговля и Склад» периодическим является реквизит Цена, который принадлежит подчиненному справочнику Цены, а не самой Номенклатуре! Соответственно, в поле objid таблицы истории констант хранится уникальный девятисимвольный идентификатор (ID) конкретной записи из справочника «Цены», а вовсе не ID номенклатуры.
Во-вторых, обратите внимание на фильтрацию по полю id в таблице констант: WHERE id = :ТипЦены. В таблице _1SCONST колонка id хранит внутренний идентификатор метаданных самого периодического реквизита (в данном случае — реквизита «Цена»). Передавать туда конкретный тип цен (например, «Оптовая», «Розничная» или «Закупочная»), являющийся элементом справочника «ТипыЦен», грубейшая ошибка. Выборка по типу цен должна производиться в таблице самого справочника цен, а не в истории констант.
Прежде чем писать исправленный SQL-запрос, нам необходимо точно понимать структуру таблиц в базе данных. Для этого заглянем в файл описания схемы данных 1Cv7.DD. Справочник «Цены» обычно описывается следующим образом (на примере таблицы SC319):
ID — уникальный идентификатор элемента справочника «Цены» (тип Char(9)).PARENTEXT — ссылка на владельца элемента. Поскольку справочник «Цены» подчинен справочнику «Номенклатура», в этом поле хранится ID элемента справочника «Номенклатура».ISMARK — пометка удаления ("*", если помечен на удаление, иначе пробел).SP327 (или аналогичное наименование поля с префиксом SP) — реквизит «ТипЦен». Хранит ссылку на справочник «Типы Цен».Периодический реквизит «Цена» не создается в виде отдельной колонки в таблице SC319. Вместо этого его история записывается в глобальную таблицу _1SCONST со следующей структурой:
ID — внутренний числовой или строковый идентификатор периодического реквизита «Цена» (определяется конфигуратором).OBJID — идентификатор конкретного элемента справочника «Цены» (соответствует полю ID из таблицы SC319).DATE — дата изменения значения реквизита (в формате YYYYMMDD).VALUE — строковое представление значения периодического реквизита (для чисел, строк и ссылок).Теперь соберем воедино все правила связывания таблиц и напишем корректный, быстродействующий запрос для SQLite, который вернет актуальную цену на выбранную дату.
Для корректного извлечения данных мы должны связать таблицу справочника цен Справочник_Цены (далее Цены) с таблицей констант _1SCONST (далее Константы) по условию совпадения Цены.ID = Константы.OBJID. При этом поиск конкретного товара нужно вести по полю PARENTEXT.
Ниже приведен работающий пример программного кода на языке 1С 7.7 с использованием библиотеки 1sqlite:
Попытка
База = СоздатьОбъект("SQLiteBase");
Исключение
ЗагрузитьВнешнююКомпоненту("1sqlite.dll");
База = СоздатьОбъект("SQLiteBase");
КонецПопытки;
База.Открыть(":memory:"); // Открываем временную базу в памяти
// Формируем текст корректного запроса
ТекстЗапроса = "
|SELECT
| Цены.ID [ЦенаСсылка :Справочник.Цены],
| (
| SELECT K.VALUE
| FROM [_1S.Const] AS K
| WHERE K.ID = :ИдРеквизитаЦена
| AND K.OBJID = Цены.ID
| AND K.DATE <= :ВыбДата
| ORDER BY K.DATE DESC, K.TIME DESC, K.DOCID DESC, K.ROW_ID DESC
| LIMIT 1
| ) AS Цена
|FROM [Справочник.Цены] AS Цены
|WHERE Цены.PARENTEXT = :ВыбНом
| AND Цены.ТипЦен = :ВыбТипЦен
| AND Цены.ISMARK = ' '
|";
Запрос = База.НовыйЗапрос;
// Получаем внутренний идентификатор периодического реквизита "Цена" из метаданных.
// В реальном коде его можно получить через метаданные 1С++ или прочитать из 1Cv7.DD.
// Для примера передадим его напрямую.
ИдРеквизитаЦена = " 328 "; // Внутренний ID реквизита Цена в базе данных
Запрос.Подставлять("ВыбНом", ВыбНом); // Передаем ссылку на Номенклатуру
Запрос.Подставлять("ВыбТипЦен", ВыбТипЦен); // Передаем ссылку на Тип Цен
Запрос.Подставлять("ВыбДата", ВыбДата); // Передаем дату среза
Запрос.Подставлять("ИдРеквизитаЦена", ИдРеквизитаЦена);
ТабРед = Запрос.ВыполнитьЗапрос(ТекстЗапроса);
Если ТабРед.КоличествоСтрок() > 0 Тогда
АктуальнаяЦена = Число(ТабРед.ПолучитьЗначение(1, "Цена"));
Сообщить("Актуальная цена: " + АктуальнаяЦена);
Иначе
Сообщить("Цена на выбранную дату не установлена!");
КонецЕсли;
Обратим особое внимание на конструкцию сортировки внутри подзапроса к таблице констант:
ORDER BY K.DATE DESC, K.TIME DESC, K.DOCID DESC, K.ROW_ID DESC LIMIT 1
Многие начинающие программисты ограничиваются сортировкой только по дате (ORDER BY K.DATE DESC). Это серьезная логическая ошибка, из-за которой периодически будут возвращаться неверные цены. Разберем, почему это происходит.
В системе 1С 7.7 периодические реквизиты могут изменяться не один раз в день, а несколько раз. Значение может быть установлено как вручную на конкретную дату, так и документом в течение дня. Чтобы гарантированно получить самое последнее актуальное значение на момент времени, мы должны выстроить строгую иерархию сортировки:
Использование связки такой сортировки с оператором LIMIT 1 гарантирует получение ровно одной — самой свежей и актуальной — записи на указанную дату среза.
Если в вашем проекте наряду с 1sqlite подключена внешняя компонента 1С++, мы настоятельно рекомендуем отказаться от ручного написания сложных подзапросов к таблице констант _1SCONST. Класс ПрямойЗапрос предоставляет мощные виртуальные таблицы метаданных, аналогичные тем, что используются в платформе 1С 8.х.
Давайте посмотрим, как лаконично и красиво решается наша задача с помощью виртуальной таблицы $СрезПоследних:
Запрос = СоздатьОбъект("ПрямойЗапрос");
ТекстЗапроса = "
|SELECT
| Цены.ТекущийЭлемент [ЦенаСсылка $Справочник.Цены],
| Цены.Цена [Цена $Число]
|FROM
| $СрезПоследних.Цены(:ВыбДата, , , (Владелец = :ВыбНом И ТипЦен = :ВыбТипЦен), (Цена)) AS Цены
|";
Запрос.УстановитьПараметр("ВыбДата", ВыбДата);
Запрос.УстановитьПараметр("ВыбНом", ВыбНом);
Запрос.УстановитьПараметр("ВыбТипЦен", ВыбТипЦен);
ДТ = Запрос.Выполнить(ТекстЗапроса);
Если ДТ.КоличествоСтрок() > 0 Тогда
АктуальнаяЦена = ДТ.ПолучитьЗначение(1, "Цена");
Предупреждение("Найдена цена: " + АктуальнаяЦена);
КонецЕсли;
Преимущества использования виртуальных таблиц класса ПрямойЗапрос очевидны:
1Cv7.DD, чтобы узнать физические имена полей вроде SP327 или внутренние идентификаторы реквизитов из базы данных. Компонента самостоятельно транслирует русскоязычные наименования объектов конфигурации в физические таблицы СУБД._1SCONST разбивается на части (поле PARTNO). Ручная склейка таких строк в чистом SQL крайне сложна, в то время как виртуальная таблица делает это автоматически в фоновом режиме.Использование рассмотренных методик позволит вам значительно ускорить работу с базой данных 1С 7.7, минимизировать количество блокировок файлов и получать точные данные периодических реквизитов в минимальные сроки.