При разработке отчетов на платформе 1С:Предприятие 7.7 с использованием внешней компоненты 1С++ (Прямые запросы) программисты часто сталкиваются с классической задачей: получить остатки и обороты по списку товаров за каждый день выбранного периода. При этом крайне важно, чтобы в отчете выводились даже те дни, когда по товару не было никаких движений (с нулевыми показателями).
Если ваша база данных работает под управлением современной СУБД, задача решается относительно просто с помощью обобщенных табличных выражений (CTE) и оконных функций. Однако, если рабочая база до сих пор развернута на устаревшей СУБД MS SQL Server 2000, разработчик сталкивается с жесткими ограничениями: отсутствие поддержки оператора WITH, невозможность использования оконных функций типа ROW_NUMBER() или SUM() OVER(), а также особенности синтаксиса объявления локальных переменных.
В этой статье мы подробно разберем, как обойти ограничения MS SQL 2000, построить непрерывный календарь дат, связать его со справочником номенклатуры и рассчитать ежедневный остаток нарастающим итогом прямо на сервере базы данных.
Предположим, у нас есть переданная из 1С временная таблица товаров #TempTable (содержит идентификаторы товаров во временном поле val). Нам требуется получить таблицу следующей структуры:
Для решения этой задачи нам понадобится пройти несколько шагов. Рассмотрим их подробно.
Чтобы гарантировать вывод строк с нулевым движением, нам необходимо создать так называемую «сетку» из всех возможных комбинаций дат периода и выбранных товаров. Для этого сначала нужно сгенерировать список дат.
На СУБД MS SQL Server 2005 и выше для этого используется рекурсивный запрос CTE. Посмотрим на этот пример кода, чтобы понять логику:
|WITH ДатаПериода AS
|(
|SELECT cast(:НачДата as date) AS [Дата]
|UNION ALL
|SELECT dateadd(dd,1,ДатаПериода.ДАТА) FROM ДатаПериода WHERE ДатаПериода.Дата < :КонДата
|)
|SELECT * FROM ДатаПериода
|OPTION (MAXRECURSION 0)
Поскольку MS SQL Server 2000 не поддерживает CTE, этот элегантный способ вызовет ошибку синтаксиса. Нам придется генерировать список дат классическим циклом WHILE во временную таблицу.
Рассмотрим процедурный код на T-SQL для создания таблицы дат:
-- Создаем временную таблицу для дат периода
CREATE TABLE #Период (val char(8) PRIMARY KEY)
;
-- Объявляем переменные.
-- В MS SQL 2000 нельзя совмещать объявление переменной и присвоение значения,
-- поэтому делаем это отдельными инструкциями SET.
DECLARE @начДата as datetime
SET @начДата = :НачДата
DECLARE @конДата as datetime
SET @конДата = :КонДата~
-- Заполняем таблицу дат в цикле шаг в 1 день
WHILE @начДата <= @конДата
BEGIN
INSERT INTO #Период VALUES (CONVERT(char(8), @начДата, 112))
SET @начДата = DATEADD(day, 1, @начДата)
END
Обратите внимание на использование преобразования CONVERT(char(8), @начДата, 112). Код формата 112 преобразует дату в стандартный ISO-вид ГГГГММДД. Это идеальный строковый формат для последующего сопоставления с датами из журнала документов 1С 7.7.
Получив список дат в таблице #Период, мы выполняем операцию декартова произведения (CROSS JOIN) с нашей таблицей фильтра номенклатуры #TempTable. Это дает нам базовую матрицу, к которой мы будем прикручивать начальные остатки и обороты.
Для сбора данных мы используем объединение нескольких запросов через UNION ALL:
$РегистрОстатки.ОстаткиТМЦ на момент :НачДата.$Регистр.ОстаткиТМЦ за весь период.Проанализируем структуру этого объединения. При расчете оборотов важно правильно обрабатывать флаг дебета/кредита (приход/расход). В регистрах остатков 1С 7.7 поле DEBKRED принимает значение 0 для прихода и 1 для расхода. Таким образом, оборот за день рассчитывается как разница между приходом и расходом.
Также важно наложить фильтр по конкретной фирме ($Обороты.Фирма = :ВыбФирма), чтобы не смешивать остатки разных юридических лиц, если учет ведется в разрезе нескольких организаций.
Главная сложность без оконных функций — рассчитать остаток на каждый последующий день. Мы знаем остаток на самую первую дату. Остаток на второй день — это остаток первого дня плюс оборот первого дня. Остаток на третий день — это остаток второго дня плюс оборот второго дня и так далее.
Для реализации этого алгоритма на MS SQL Server 2000 мы воспользуемся механизмом T-SQL курсоров (CURSOR). Мы будем перебирать строки результирующей таблицы #Итоги, отсортированные строго в разрезе каждого товара по возрастанию даты. В процессе обхода мы будем накапливать остаток в переменной и обновлять текущую строку таблицы.
Рассмотрим логику работы курсора:
DECLARE @CurrentMove int -- Переменная для хранения оборота за день
DECLARE @CurrentQty int -- Текущий начальный остаток из таблицы
DECLARE @NextQty int -- Расчетный остаток на следующий день
DECLARE @CurrentItem char(9) -- Идентификатор текущего товара
DECLARE @LastItem char(9) -- Идентификатор предыдущего обработанного товара
SET @LastItem = ''
-- Объявляем курсор для обновления данных
DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR
SELECT Оборот, Товар, НачОст
FROM #Итоги
ORDER BY Товар, Дата
FOR UPDATE
OPEN cur
FETCH NEXT FROM cur INTO @CurrentMove, @CurrentItem, @CurrentQty
WHILE @@FETCH_STATUS = 0
BEGIN
-- Если мы перешли к новому товару в выборке
IF NOT @LastItem = @CurrentItem
BEGIN
-- Запоминаем остаток на следующий день как СтартовыйОстаток + Оборот
SET @NextQty = @CurrentQty + @CurrentMove
SET @LastItem = @CurrentItem
END
ELSE
-- Если мы продолжаем обрабатывать тот же самый товар
BEGIN
-- Обновляем начальный остаток текущего дня расчетным остатком предыдущего дня
UPDATE #Итоги
SET НачОст = @NextQty
WHERE CURRENT OF cur
-- Рассчитываем накопление на следующий день
SET @NextQty = @NextQty + @CurrentMove
END
FETCH NEXT FROM cur INTO @CurrentMove, @CurrentItem, @CurrentQty
END
CLOSE cur
DEALLOCATE cur
Этот процедурный блок гарантирует, что для каждого товара цепочка остатков по дням рассчитается абсолютно корректно.
Теперь объединим все шаги в один итоговый запрос, готовый к выполнению через объект класса ODBCRecordset внешней компоненты 1С++.
ТекстЗапроса = "
|SET NOCOUNT ON
|;
|-- Шаг 1. Создание и заполнение таблицы дат периода
|IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Период'))
| DROP TABLE #Период
|;
|CREATE TABLE #Период (val char(8) PRIMARY KEY)
|;
|DECLARE @начДата as datetime
|SET @начДата = :НачДата
|DECLARE @конДата as datetime
|SET @конДата = :КонДата~
|
|WHILE @начДата <= @конДата
|BEGIN
| INSERT INTO #Период VALUES (CONVERT(char(8), @начДата, 112))
| SET @начДата = DATEADD(day, 1, @начДата)
|END
|;
|
|-- Шаг 2. Создание таблицы итогов
|IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Итоги'))
| DROP TABLE #Итоги
|;
|CREATE TABLE #Итоги (Дата char(8), Товар char(9), НачОст int, Оборот int, Поступления int)
|;
|-- Создаем индекс для ускорения работы курсора
|CREATE INDEX DateItem ON #Итоги (Товар, Дата)
|;
|
|-- Шаг 3. Первичный сбор данных (Сетка + Остатки + Движения)
|INSERT INTO #Итоги (Дата, Товар, НачОст, Оборот, Поступления)
|SELECT
| ДатыТовары.Дата as [Дата],
| ДатыТовары.Товар as [Товар],
| SUM(ДатыТовары.НачОст) as [НачОстаток],
| SUM(ДатыТовары.Оборот) as [Оборот],
| SUM(ДатыТовары.Поступления) as [Поступления]
|FROM
| (
| -- Строим полную сетку дат и товаров (все комбинации с нулевыми итогами)
| SELECT
| #Период.val as [Дата],
| #TempTable.val as [Товар],
| 0 as [НачОст],
| 0 as [Оборот],
| 0 as [Поступления]
| FROM
| #Период CROSS JOIN #TempTable
|
| UNION ALL
|
| -- Получаем начальный остаток на самую первую дату отчета
| SELECT
| CONVERT(char(8), :НачДата, 112),
| Остатки.Номенклатура,
| Остатки.КоличествоОстаток,
| 0,
| 0
| FROM
| $РегистрОстатки.ОстаткиТМЦ(:НачДата,, Номенклатура IN (SELECT val FROM #TempTable) AND Фирма = :ВыбФирма, (Номенклатура), (Количество)) as Остатки
|
| UNION ALL
|
| -- Получаем обороты и поступления за весь период отчета
| SELECT
| SUBSTRING(Обороты.Date_Time_Iddoc, 1, 8) as [Дата],
| $Обороты.Номенклатура as [Товар],
| 0,
| SUM(CASE WHEN Обороты.DEBKRED = 0 THEN $Обороты.Количество ELSE $Обороты.Количество * (-1) END) as [Оборот],
| SUM(CASE WHEN Обороты.IDDOCDEF = $ВидДокумента.ПоступлениеТМЦ_Склад THEN $Обороты.Количество ELSE 0 END) as [Поступления]
| FROM
| $Регистр.ОстаткиТМЦ as Обороты
| WHERE
| Обороты.Date_Time_Iddoc BETWEEN :НачДата AND :КонДата~
| AND $Обороты.Номенклатура IN (SELECT val FROM #TempTable)
| AND $Обороты.Фирма = :ВыбФирма
| GROUP BY
| SUBSTRING(Обороты.Date_Time_Iddoc, 1, 8), $Обороты.Номенклатура
| ) as ДатыТовары
|GROUP BY
| ДатыТовары.Дата, ДатыТовары.Товар
|;
|
|-- Удаляем временную таблицу дат, она больше не нужна
|DROP TABLE #Период
|;
|
|-- Шаг 4. Расчет нарастающего итога с помощью курсора
|DECLARE @CurrentMove as int
|DECLARE @CurrentQty as int
|DECLARE @NextQty as int
|DECLARE @CurrentItem as char(9)
|DECLARE @LastItem as char(9)
|SET @LastItem = ''
|
|DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR
|SELECT Оборот, Товар, НачОст
|FROM #Итоги
|ORDER BY Товар, Дата
|FOR UPDATE
|
|OPEN cur
|FETCH NEXT FROM cur INTO @CurrentMove, @CurrentItem, @CurrentQty
|WHILE @@FETCH_STATUS = 0
|BEGIN
| IF NOT @LastItem = @CurrentItem
| BEGIN
| SET @NextQty = @CurrentQty + @CurrentMove
| SET @LastItem = @CurrentItem
| END
| ELSE
| BEGIN
| UPDATE #Итоги
| SET НачОст = @NextQty
| WHERE CURRENT OF cur
|
| SET @NextQty = @NextQty + @CurrentMove
| END
| FETCH NEXT FROM cur INTO @CurrentMove, @CurrentItem, @CurrentQty
|END
|;
|CLOSE cur
|;
|DEALLOCATE cur
|;
|
|-- Шаг 5. Финальная выборка данных с соединением со справочником Номенклатуры
|SELECT
| Итоги.Дата as [Дата $Дата],
| Итоги.Товар as [Товар $Справочник.Номенклатура],
| Итоги.НачОст as [НачОстаток $Число],
| Итоги.Поступления as [Поступление $Число],
| Товары.Code as [Код],
| Товары.DESCR as [Наименование]
|FROM
| #Итоги as Итоги
|JOIN
| $Справочник.Номенклатура as Товары ON Итоги.Товар = Товары.ID
|ORDER BY
| Итоги.Дата, Товары.DESCR, Товары.ID
|;
|
|-- Очистка временных таблиц
|DROP TABLE #Итоги
|;
|SET NOCOUNT OFF
|";
Для того чтобы данный запрос работал максимально быстро, обратите внимание на следующие технические настройки вашей базы данных 1С 7.7:
_1SREGxxxx) не хранит в себе поля даты и времени напрямую. В этом случае обращение к полю Date_Time_Iddoc вызовет ошибку СУБД Invalid column name 'Date_Time_Iddoc'. Разработчику придется связывать таблицу регистра с журналом документов _1SJourn по полю IDDoc, что существенно снизит скорость выполнения запроса.COALESCE является стандартом ANSI SQL, на старых версиях MS SQL Server 2000 она транслируется оптимизатором как конструкция CASE WHEN, что в некоторых сложных вычислениях может приводить к двойному расчету аргументов. В простых соединениях рекомендуется использовать более производительную встроенную функцию ISNULL().
CREATE INDEX DateItem ON #Итоги (Товар, Дата). Создание индекса перед запуском курсора критически важно. Без этого индекса операция позиционирования курсора WHERE CURRENT OF cur на каждой строке будет приводить к полному сканированию временной таблицы (Table Scan), что на больших периодах и длинных списках товаров полностью заблокирует работу сервера.
Применяя предложенный подход, мы получаем стабильный и производительный отчет, который корректно рассчитывает остатки на каждый день периода даже на таких раритетных конфигурациях, как связка 1С 7.7 и MS SQL Server 2000.