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

← На главную

В платформе 1С 7.7 периодические реквизиты (такие как цены номенклатуры) хранятся в базе данных не на каждый день, а только на момент их изменения. Это создает определенные трудности, когда нам необходимо построить график или отчет, где цена должна быть отображена для каждой даты периода, даже если она не менялась. В этой статье мы разберем, как эффективно решить эту задачу с помощью прямых SQL-запросов и внешней компоненты 1С++.

Постановка задачи и создание временной таблицы дат

Для начала проанализируем ситуацию: у нас есть период (начальная и конечная даты) и список товаров. Нам нужно получить таблицу, в которой каждой дате соответствует актуальная цена товара. Первым шагом мы должны сформировать «календарь» — временную таблицу со всеми датами выбранного периода. Рассмотрим пример создания такой таблицы на языке T-SQL:


|SET NOCOUNT ON;  
|IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Период'))
|DROP TABLE #Период;
|CREATE TABLE #Период (val datetime PRIMARY KEY); 
|DECLARE @НачДата as DATETIME 
|SET @НачДата = '"+метаДата.ПолучитьСтрИзДаты(НачДатаПрод)+"'
|DECLARE @КонДата as DATETIME 
|SET @КонДата = '"+метаДата.ПолучитьСтрИзДаты(КонДатаПрод)+"'    
|WHILE @НачДата <= @КонДата
|BEGIN
|INSERT INTO #Период VALUES (@НачДата)
|SET @НачДата = DATEADD(DAY, 1, @НачДата)
|END;

Использование типа datetime для колонки val в таблице #Период критически важно. Это позволяет избежать ошибок при сравнении дат с внутренними полями таблиц 1С и корректно использовать виртуальные методы 1С++.

Способ 1: Использование виртуального поля $ПоследнееЗначение

Самый простой путь — использование встроенного в 1С++ макроса $ПоследнееЗначение. Этот макрос автоматически генерирует подзапрос для поиска актуального значения периодического реквизита на указанную дату. Рассмотрим, как соединить таблицу дат, справочник номенклатуры и получить цену:


|SELECT
|   Товары.CODE as Код,
|   ДатыТовары.Товар as [Номенклатура $Справочник.Номенклатура],
|   ДатыТовары.Дата as Дата,
|   $ПоследнееЗначение.Цены.Цена(СпрЦены.ID, ДатыТовары.Дата) as Цена
|FROM
|(
|   SELECT 
|       #Период.val as [Дата],
|       #TempTable.val as [Товар]
|   FROM #Период CROSS JOIN #TempTable
|) as ДатыТовары
|LEFT JOIN $Справочник.Номенклатура as Товары ON ДатыТовары.Товар = Товары.ID
|LEFT JOIN $Справочник.Цены as СпрЦены ON (СпрЦены.ParentExt = ДатыТовары.Товар) AND ($СпрЦены.ТипЦен = :ТипЦены)
|ORDER BY ДатыТовары.Дата

В данном примере мы используем CROSS JOIN, чтобы создать сетку «Товар — Дата». Важный момент: так как цена в типовых конфигурациях 7.7 (например, в Торговле и Складе) является подчиненным справочником, нам сначала нужно найти ID элемента справочника Цены для конкретного товара и типа цены, а затем передать этот ID в функцию $ПоследнееЗначение.

Способ 2: Оптимизированный запрос через таблицу _1SCONST

Если товаров и дат много, использование $ПоследнееЗначение может существенно замедлить выполнение запроса, так как для каждой строки будет выполняться коррелированный подзапрос. Более производительный метод — прямое обращение к системной таблице _1SCONST, где хранятся все изменения периодических реквизитов.

Разберем логику получения данных напрямую. Нам нужно найти запись в _1SCONST, у которой дата меньше или равна искомой, а идентификатор записи соответствует нужному реквизиту. Пример запроса (адаптированный под MSSQL):


SELECT
    Цены.PARENTEXT [Номенклатура $Справочник.Номенклатура],
    Цены.ТипЦен [ТипЦен :Справочник.ТипыЦен],
    CAST(Константы.VALUE AS NUMERIC(15,2)) [Цена],
    Константы.DATE [ДатаЦен]
FROM _1SCONST AS Константы
INNER JOIN $Справочник.Цены AS Цены ON Цены.ID = Константы.OBJID
WHERE Константы.ID = :ИсторияРеквизита.Цены.Цена
  AND Константы.DATE <= :НужнаяДата
ORDER BY Константы.DATE DESC

Этот подход позволяет извлечь всю историю изменений одним пакетом. Однако для заполнения «пустот» (дней, когда цена не менялась) на стороне SQL придется использовать сложные конструкции с OUTER APPLY или дополнительную постобработку в 1С (заполнение значений из предыдущей строки).

Развертка цен по типам (Pivot) в одну строку

Часто возникает задача вывести несколько типов цен (например, «Закупочная» и «Розничная») в одну строку по каждому товару. Поскольку количество типов цен может быть произвольным, текст запроса лучше формировать динамически в цикле 1С. Выясним, как это реализовать:


|SELECT
|   СпрНоменклатура.ID [Номенклатура $Справочник.Номенклатура]
|   /*ЦенаПоТипу*/
|FROM
|   $Справочник.Номенклатура AS СпрНоменклатура With (NOLOCK)
|WHERE СпрНоменклатура.ID in (SELECT val FROM #TempTable)

В цикле по списку значений типов цен мы будем заменять комментарий /*ЦенаПоТипу*/ на конструкцию получения цены:


Для Тек = 1 По сзТипыЦен.РазмерСписка() Цикл
    ТипЦены = сзТипыЦен.ПолучитьЗначение(Тек);
    ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "/*ЦенаПоТипу*/",
    ", ISNULL($ПоследнееЗначение.Цены.Цена(
    |    (SELECT TOP 1 ID FROM $Справочник.Цены WHERE ParentExt = СпрНоменклатура.ID AND $СпрЦены.ТипЦен = :Тип" + Тек + "),
    |    :ДатаЦен), 0) AS Цена" + Тек + " /*ЦенаПоТипу*/");
    Запрос.УстановитьТекстовыйПараметр("Тип" + Тек, ТипЦены);
КонецЦикла;

Обратите внимание: Мы используем вложенный SELECT TOP 1 для поиска ID элемента справочника цен прямо внутри параметров виртуального поля. Это компактный, хоть и требовательный к ресурсам способ получения «шахматки» цен.

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

Проанализировав работу прямых запросов в 1С 7.7, выделим несколько ключевых правил:

Индексация: Всегда проверяйте, чтобы поля, участвующие в JOIN (например, ParentExt в справочнике Цены), были проиндексированы.
Обработка NULL: SQL возвращает NULL, если цена не найдена. Обязательно используйте функцию ISNULL(..., 0), чтобы в 1С возвращалось числовое значение, а не пустое значение базы данных.
Типизация: В таблице _1SCONST поле VALUE имеет строковый тип. Для математических операций или корректного отображения в 1С используйте CAST или CONVERT к числовому типу (например, NUMERIC(15,2)).
Номенклатура: При работе с большими справочниками всегда ограничивайте выборку через временную таблицу #TempTable с нужными ID товаров, чтобы не сканировать весь справочник номенклатуры.

Используя приведенные методы, мы можем строить быстрые и эффективные отчеты по динамике цен, которые будут работать значительно быстрее стандартных средств встроенного языка 1С 7.7.