Как рассчитать себестоимость товара прямым SQL-запросом в 1С 7.7

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

Основы использования виртуальных таблиц остатков

Для начала разберем самый простой и эффективный способ получения остатков — использование макроса $РегистрОстатки. Этот макрос автоматически разворачивается компонентой 1С++ в сложный SQL-запрос, который учитывает актуальные итоги в таблицах RG* и движения в таблицах RA*.

Для получения количественного и суммового остатка в разрезе номенклатуры по конкретной фирме, проанализируем следующий пример кода:

SELECT
    Рег.Номенклатура AS [Номенклатура $Справочник.Номенклатура],  
    Рег.КоличествоОстаток AS Количество,
    Рег.СуммаРубОстаток AS СуммаРуб
FROM
    $РегистрОстатки.ПартииНаличие(:ВыбДата~,,
        Фирма = :ВбрФирма AND Номенклатура = :ВыбТовар,
        (Номенклатура),
        (Количество, СуммаРуб)) AS Рег

В этом запросе мы используем несколько важных параметров:

1. :ВыбДата~ — передает дату, на которую нужно получить остатки. Знак тильды указывает на необходимость включения движений на саму дату (границу периода).
2. Фильтр (третий параметр) — позволяет ограничить выборку на уровне SQL-сервера. Здесь мы фильтруем данные по конкретной фирме и товару.
3. Измерения (четвертый параметр) — указываем, в разрезе каких полей нам нужны остатки (в данном случае — Номенклатура).
4. Ресурсы (пятый параметр) — перечисляем числовые поля, остатки по которым нас интересуют (Количество и СуммаРуб).

Расчет средней себестоимости

Чтобы получить среднюю себестоимость, нам необходимо разделить общую сумму остатков на общее количество. Однако в SQL-запросах нужно быть крайне осторожными с операцией деления, чтобы избежать ошибки Division by zero (деление на ноль), если товара на остатке нет.

Рассмотрим доработанный вариант запроса, который вычисляет себестоимость "на лету":

SELECT
    Рег.Номенклатура AS [Номенклатура $Справочник.Номенклатура],    
    Рег.КоличествоОстаток AS [Количество $Число],
    Рег.СуммаРубОстаток AS [СуммаРуб $Число],
    CASE 
        WHEN Рег.КоличествоОстаток = 0 THEN 0 
        ELSE Рег.СуммаРубОстаток / Рег.КоличествоОстаток 
    END AS [Себестоимость $Число]
FROM
    $РегистрОстатки.ПартииНаличие(:ВыбДата~,,
        Фирма = :ВбрФирма,
        (Номенклатура),
        (Количество, СуммаРуб)) AS Рег

Использование конструкции CASE WHEN гарантирует стабильность работы отчета даже при наличии некорректных данных в базе.

Реализация сложной логики: проверка последней закупки

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

Для решения такой задачи нам потребуется:

1. Найти последний документ прихода по каждому товару.
2. Определить его сумму.
3. Связать эти данные с общими остатками по регистру.

Разберем пример реализации через временную таблицу товаров:

SELECT 
    subsel.Номенклатура AS [Номенклатура $Справочник.Номенклатура], 
    CASE 
        WHEN (ISNULL(РегПосл.СуммаРуб, 0)) = 0 THEN 0 
        ELSE (sebestoimost.СуммаРуб / NULLIF(sebestoimost.Количество, 0)) 
    END AS [Себестоимость $Число]
FROM
    #TempTable temp 
LEFT JOIN (
    -- Поиск даты и времени последнего прихода по каждому товару
    SELECT
        $Рег.Номенклатура AS [Номенклатура],
        MAX(Рег.Date_Time_IDDOC) AS ДатаПослПрихода
    FROM
        $Регистр.ПартииНаличие as Рег
    INNER JOIN
        _1Sjourn as ЖурналДок ON ЖурналДок.IDDoc = Рег.IDDoc 
    WHERE
        ЖурналДок.iddocdef = $ВидДокумента.ПоступлениеТМЦ_Склад
        AND $Рег.Фирма = :ВбрФирма
    GROUP BY 
        $Рег.Номенклатура
) subsel ON temp.val = subsel.Номенклатура
LEFT JOIN (
    -- Получение общих остатков через виртуальную таблицу
    SELECT
        РегОст.Номенклатура AS [Номенклатура],  
        РегОст.КоличествоОстаток AS Количество,
        РегОст.СуммаРубОстаток AS СуммаРуб
    FROM
        $РегистрОстатки.ПартииНаличие(:ВыбДата~,,
            Фирма = :ВбрФирма, (Номенклатура), (Количество, СуммаРуб)) AS РегОст
) sebestoimost ON temp.val = sebestoimost.Номенклатура
LEFT JOIN 
    $Регистр.ПартииНаличие AS РегПосл ON $РегПосл.Номенклатура = subsel.Номенклатура 
    AND РегПосл.Date_time_iddoc = subsel.ДатаПослПрихода

В данном запросе мы используем MAX(Date_Time_IDDOC). Это стандартное поле в 1С 7.7, которое объединяет дату, время и уникальный идентификатор документа, что позволяет точно определить "самый последний" документ в хронологии системы.

Важные технические нюансы и оптимизация

При написании прямых запросов важно учитывать внутреннее устройство хранения данных 1С:

1. Учет доп. расходов. В типовых конфигурациях (например, ТиС 9.2) ресурс СуммаРуб в регистре ПартииНаличие включает в себя не только стоимость по накладной, но и распределенные дополнительные расходы. Если ваши цифры в запросе не сходятся с "простой" ценой закупки, проверьте наличие записей по услугам, увеличивающим стоимость партий.

2. Блокировки (NOLOCK). SQL-сервер при выполнении тяжелых отчетов может блокировать таблицы итогов, мешая операторам проводить документы. Чтобы избежать этого, рекомендуется добавлять хинт WITH (NOLOCK) к таблицам, если вы используете "чистый" SQL без макросов 1С++.

3. Точка актуальности (ТА). Прямой запрос к таблице итогов RG* вернет данные на момент ТА. Если вы строите отчет на "вчера", макрос $РегистрОстатки автоматически вычтет обороты за текущий месяц из таблицы RA*. Это ресурсоемкая операция, поэтому для оперативных отчетов лучше всегда ориентироваться на текущее состояние итогов.

4. Обработка пустых значений. В SQL NULL не равен нулю. Всегда используйте функцию ISNULL(ВашеПоле, 0) при выполнении арифметических операций с результатами LEFT JOIN, иначе один отсутствующий приход превратит всю итоговую сумму в NULL.

Подводя итог, использование прямых запросов для расчета себестоимости — это мощный инструмент, позволяющий сократить время формирования отчетов в десятки раз. Однако он требует от программиста четкого понимания структуры метаданных и внимательности при обработке исключительных ситуаций, таких как отсутствие движений или нулевые остатки.