При переходе с типовых механизмов запросов 1С:Предприятия 7.7 на прямые SQL-запросы с использованием внешней компоненты 1С++ разработчики часто сталкиваются с проблемой неверного расчета остатков на момент проведения документа. Стандартный временный расчет регистров возвращает одни цифры, а прямой запрос к виртуальной таблице остатков — другие.
В этой статье мы подробно разберем, почему возникает эта проблема, как устроена ось времени и позиция документа в базе данных SQL, а также как правильно использовать модификаторы параметров 1С++ для получения абсолютно точных итогов.
Рассмотрим классическую задачу. В документе «Инвентаризация» необходимо скорректировать остатки по регистру «Коробки» на момент проведения самого документа. Логика проведения следующая:
Если мы выполняем расчет остатков через стандартный объект Регистр, код выглядит привычно и работает корректно:
Рег = СоздатьОбъект("Регистр.Коробки");
Рег.УстановитьЗначениеФильтра("МестоОбработки", МестоОбработки, 2);
Рег.ВременныйРасчет(1);
Если СравнитьТА() = -1 Тогда
РассчитатьРегистрыНа(ТекущийДокумент());
Иначе
РассчитатьРегистрыНа(ПолучитьПозициюТА());
КонецЕсли;
Рег.ВыбратьИтоги();
// Дальнейший обход и получение верных остатков...
Однако при попытке переписать этот алгоритм на быстрый прямой запрос через класс ODBCRecordset, разработчики часто получают неверные данные. Итоги либо вообще не сходятся, либо «захватывают» движения самого проводимого документа.
Давайте проанализируем нерабочий вариант запроса, который изначально использовал автор темы:
RS = СоздатьОбъект("ODBCRecordset");
ТекстЗапроса = "
|SELECT
| РегК.Продукт as [Продукт $Справочник],
| РегК.Партия as [Партия $Справочник.Партии],
| РегК.НеттоОстаток as НеттоНачОст,
| РегК.БруттоОстаток as БруттоНачОст,
| РегК.КоличествоКоробокОстаток as КоличествоКоробокНачОст
|FROM
| $РегистрОстатки.Коробки(:ДатаКонца~,,
| МестоОбработки = :ВыбЦФУ,
| (Продукт, Партия), (Брутто, Нетто, КоличествоКоробок)) as РегК
|";
RS.УстановитьТекстовыйПараметр("ДатаКонца", СформироватьПозициюДокумента(ТекущийДокумент(), -1));
RS.УстановитьТекстовыйПараметр("ВыбЦФУ", МестоОбработки);
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
В данном случае остатки получаются неверными. Выясним, почему ручное формирование позиции документа через СформироватьПозициюДокумента(..., -1) дает сбой при работе с прямыми SQL-запросами.
Чтобы понять причину ошибки, заглянем «под капот» базы данных. В MS SQL Server хронологическая точка (позиция) каждого документа записывается в виде строки фиксированной длины в 23 символа (поле date_time_iddoc). Она имеет следующую структуру:
YYYYMMDD (например, 20191001).Пример полной строки позиции: 201910016OYRO01HNEDAЯЯЯ.
Когда мы используем функцию СформироватьПозициюДокумента(ТекущийДокумент(), -1), мы получаем строковое представление позиции, смещенное назад. Но при передаче этой строки в качестве параметра напрямую в SQL-запрос парсер 1С++ может неверно сопоставить типы данных, особенно если в тексте запроса используются не те модификаторы параметров (символы тильды ~).
Для правильной трансляции параметров встроенного языка 1С в SQL-запрос разработчики используют специальные символы-модификаторы после имени параметра. Количество этих символов строго определяет поведение парсера.
Рассмотрим, как преобразуются типы данных в зависимости от количества тильд:
1. Если мы передаем строковую позицию документа (полученную через СформироватьПозициюДокумента):
:Параметр (без тильды) — преобразуется в обычный текстовый литерал. При попытке передать его в функцию расчета остатков MS SQL выдаст ошибку: "Conversion failed when converting datetime from character string".:Параметр~ (одна тильда) — парсер понимает, что передается строка позиции, и корректно приводит её к типу char(23). Именно этот вариант должен использоваться, если позиция подготавливается вручную во встроенном языке.2. Если мы передаем сам объект типа «Документ» (рекомендуемый и наиболее надежный способ):
:Параметр (без тильды) — транслируется в 9-символьный идентификатор документа IDDOC.:Параметр~ (одна тильда) — транслируется в 13-символьное значение (вид документа + IDDOC).:Параметр~~~ (три тильды) — транслируется в числовой идентификатор вида документа (IDDocDef).:Параметр~~~~ (четыре тильды) — самый важный модификатор для расчетов! Он автоматически извлекает из базы данных полную 23-символьную позицию переданного документа (date_time_iddoc).Вместо того чтобы вручную формировать строку позиции и вычитать из нее единицу, гораздо проще и надежнее передать в параметры запроса сам объект ТекущийДокумент() и использовать модификатор из четырех тильд. В этом случае 1С++ самостоятельно получит точную позицию проведения документа прямо из базы данных.
Давайте посмотрим на пример правильной реализации запроса:
RS = СоздатьОбъект("ODBCRecordset");
ТекстЗапроса = "
|SELECT
| РегК.Продукт as [Продукт $Справочник],
| РегК.Партия as [Партия $Справочник.Партии],
| РегК.НеттоОстаток as НеттоНачОст,
| РегК.БруттоОстаток as БруттоНачОст,
| РегК.КоличествоКоробокОстаток as КоличествоКоробокНачОст
|FROM
| $РегистрОстатки.Коробки(:ВыбДок~~~~,,
| МестоОбработки = :ВыбЦФУ,
| (Продукт, Партия), (Брутто, Нетто, КоличествоКоробок)) as РегК
|";
// Передаем сам объект документа напрямую!
RS.УстановитьТекстовыйПараметр("ВыбДок", ТекущийДокумент());
RS.УстановитьТекстовыйПараметр("ВыбЦФУ", МестоОбработки);
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
При использовании конструкции :ВыбДок~~~~ виртуальная таблица 1С++ вернет остатки строго на момент до выполнения движений текущего документа. Это именно то, что требуется при проведении для контроля остатков и расчета корректировок.
Проанализируем, в какие SQL-инструкции превращаются оба варианта при генерации запроса парсером 1С++ (это можно увидеть, включив режим отладки через метод RS.Отладка(1)):
Вариант с ручным формированием строки и одной тильдой (:ДатаКонца~):
-- Парсер транслирует условие следующим образом:
WHERE ra45_vt.date_time_iddoc > '20191001'
AND ra45_vt.date_time_iddoc < '201910016OYRO01HNEDAЯЯЯ'
Здесь из-за ручного сдвига позиции назад мы рискуем потерять документы, которые были записаны в ту же секунду, или получить смещение временной границы.
Вариант с передачей ссылки на документ и четырьмя тильдами (:ВыбДок~~~~):
-- Парсер транслирует условие строго по границе документа:
WHERE ra45_vt.date_time_iddoc > '20191001'
AND ra45_vt.date_time_iddoc < '201910016OYRO01HNEDB '
В данном случае граница устанавливается филигранно точно. Запрос вернет все итоги, актуальные на микросекунду начала проведения нашего документа, гарантируя 100% совпадение с типовым временным расчетом регистров 1С.
RS.Отладка(1); перед выполнением инструкции. В открывшемся окне вы сможете детально изучить сгенерированный SQL-текст и подставляемые параметры.~~~~ не подойдет, так как она всегда считает остатки «ДО» документа. В таких редких случаях действительно приходится использовать ручное смещение позиции вперед и передавать ее как строковый параметр с одной тильдой.