При построении ежедневных отчетов по движениям денежных средств или товаров разработчики часто сталкиваются с классической проблемой. Если за какой-то день выбранного периода в базе данных нет проведенных документов (например, в выходные или праздничные дни), то встроенный запрос 1С:Предприятия 7.7 просто исключает эти даты из результирующей выборки. Нам же, как правило, требуется видеть красивую непрерывную хронологическую последовательность, где "пустые" дни выводятся с нулевыми суммами оборотов.
Первая мысль, которая приходит в голову начинающим разработчикам, — совершить "технический грех": организовать цикл по всем дням периода и внутри этого цикла выполнять запрос на каждую дату отдельно. Делать так категорически нельзя, поскольку это создаст огромную паразитную нагрузку на базу данных и приведет к критическому падению производительности отчета. В этой статье мы подробно разберем три профессиональных способа решения этой задачи без ущерба для скорости работы системы.
В платформе 1С 7.7 существует элегантное штатное решение, заложенное создателями языка запросов. Для временных группировок (таких как День, Неделя, Декада, Месяц, Квартал, Год) предусмотрен специальный модификатор Все. Он заставляет систему принудительно генерировать непрерывную временную шкалу в пределах указанного периода.
Давайте посмотрим, как модифицировать исходный запрос автора темы, чтобы решить проблему в один шаг. Для этого достаточно добавить ключевое слово Все сразу после указания временной группировки:
// Создаем объект Запрос и задаем его текст
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса =
"//{{ЗАПРОС(ПолучитьОборотыКассы)
|Период с НачДата по КонДата;
|Обрабатывать НеПомеченныеНаУдаление;
|ОбрабатыватьДокументы Проведенные;
|ДатаДокумента = Документ.ПКО.ДатаДок,
| Документ.РКО.ДатаДок;
|Касса = Документ.ПКО.Касса,
| Документ.РКО.Касса;
|СуммаДокументаПКО= Документ.ПКО.СуммаВзаиморасчетов;
|СуммаДокументаРКО= Документ.РКО.СуммаВзаиморасчетов;
|ФирмаДокумента = Документ.ПКО.Фирма,
| Документ.РКО.Фирма;
|Группировка День Все; // Вот ключевое изменение!
|Функция СуммаДокументаПК=Сумма(СуммаДокументаПКО);
|Функция СуммаДокументаРК=Сумма(СуммаДокументаРКО);
|Условие(ФирмаДокумента = ВбрФирма);
|Условие(Касса = ВбрКасса);
|Без Итогов;
|";
Если Запрос.Выполнить(ТекстЗапроса) = 1 Тогда
// Обработка результатов запроса
Пока Запрос.Группировка(1) = 1 Цикл
// Здесь будут обходиться абсолютно все дни периода,
// даже те, в которых не было ни ПКО, ни РКО.
// Значения функций СуммаДокументаПК и СуммаДокументаРК для них будут равны 0.
Сообщить("Дата: " + Запрос.День + " | ПКО: " + Запрос.СуммаДокументаПК + " | РКО: " + Запрос.СуммаДокументаРК);
КонецЦикла;
КонецЕсли;
Разберем подробнее, как работает этот механизм. Платформа анализирует границы периода, указанного в директиве Период с ... по ..., и при обходе группировки по дням самостоятельно генерирует недостающие даты "на лету". Если на определенную дату документов в базе нет, то переменные функций (в нашем примере это СуммаДокументаПК и СуммаДокументаРК) автоматически инициализируются нулевыми значениями.
Важное предупреждение: Модификатор Все может конфликтовать при использовании вложенных временных группировок (например, если вы решите сделать одновременно Группировка Месяц Все; и подчиненную ей Группировка День Все;). Платформа 1С 7.7 в некоторых случаях некорректно сопоставляет дни внутри месяцев, из-за чего в выборку конкретного месяца могут ошибочно подмешиваться дни из другого. Если вам нужна иерархия периодов, лучше использовать только группировку День Все, а группировку по месяцам выстраивать программно на этапе вывода отчета.
Хотя первый способ выглядит максимально простым, у него есть скрытый недостаток. При работе на крупных базах данных SQL-версия встроенного запроса 1С 7.7 с модификатором Все может генерировать тяжелые и неоптимальные SQL-операции (сложные соединения таблиц и виртуальные курсоры), что приводит к существенному замедлению формирования отчета.
Для оптимизации скорости работы мы можем переложить задачу заполнения пустых дат на плечи встроенного языка 1С. Схема действий выглядит следующим образом:
Все (запрос отработает моментально, вернув только "живые" даты, в которых были движения).ТаблицаЗначений.Рассмотрим детальный пример реализации этого алгоритма:
// Шаг 1. Выполняем быстрый запрос без модификатора "Все"
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса =
"//{{ЗАПРОС(ПолучитьОборотыКассыБыстрый)
|Период с НачДата по КонДата;
|Обрабатывать НеПомеченныеНаУдаление;
|ОбрабатыватьДокументы Проведенные;
|ДатаДок = Документ.ПКО.ДатаДок, Документ.РКО.ДатаДок;
|Касса = Документ.ПКО.Касса, Документ.РКО.Касса;
|СуммаПКО= Документ.ПКО.СуммаВзаиморасчетов;
|СуммаРКО= Документ.РКО.СуммаВзаиморасчетов;
|Фирма = Документ.ПКО.Фирма, Документ.РКО.Фирма;
|Группировка ДатаДок;
|Функция ОборотПКО=Сумма(СуммаПКО);
|Функция ОборотРКО=Сумма(СуммаРКО);
|Условие(Фирма = ВбрФирма);
|Условие(Касса = ВбрКасса);
|Без Итогов;
|";
Если Запрос.Выполнить(ТекстЗапроса) = 1 Тогда
// Шаг 2. Выгружаем результаты запроса в ТЗ
ТЗ = СоздатьОбъект("ТаблицаЗначений");
Запрос.Выгрузить(ТЗ, 0, 0);
// Переименуем колонки ТЗ для удобства работы
ТЗ.УстановитьПараметрыКолонки("ДатаДок", , , , "Дата");
ТЗ.УстановитьПараметрыКолонки("ОборотПКО", , , , "СуммаПК");
ТЗ.УстановитьПараметрыКолонки("ОборотРКО", , , , "СуммаРК");
// Шаг 3. Заполняем пропущенные даты
ТекущаяДатаПериода = НачДата;
Пока ТекущаяДатаПериода <= КонДата Цикл
СтрокаНомер = 0;
// Ищем текущую дату в колонке "Дата" нашей таблицы значений
Если ТЗ.НайтиЗначение(ТекущаяДатаПериода, СтрокаНомер, "Дата") = 0 Тогда
// Если дата отсутствует, добавляем новую строку
ТЗ.НоваяСтрока();
ТЗ.Дата = ТекущаяДатаПериода;
ТЗ.СуммаПК = 0;
ТЗ.СуммаРК = 0;
КонецЕсли;
ТекущаяДатаПериода = ТекущаяДатаПериода + 1; // Переходим к следующему дню
КонецЦикла;
// Шаг 4. Сортируем таблицу по возрастанию даты
ТЗ.Сортировать("Дата+");
// Шаг 5. Выводим результат в отчет из подготовленной ТЗ
ТЗ.ВыбратьСтроки();
Пока ТЗ.ПолучитьСтроку() = 1 Цикл
// Выводим строки в печатную форму или на экран
Сообщить("Дата: " + ТЗ.Дата + " | ПКО: " + ТЗ.СуммаПК + " | РКО: " + ТЗ.СуммаРК);
КонецЦикла;
КонецЕсли;
Этот способ работает стабильно, предсказуемо и одинаково быстро как на файловых базах данных (DBF), так и на серверных (MS SQL Server), поскольку ресурсоемкие операции поиска и вставки производятся в оперативной памяти клиентского компьютера средствами движка 1С 7.7.
Если вы работаете в SQL-версии 1С 7.7 с использованием внешних компонент (таких как 1С++ или 1sqlite), то оптимальным архитектурным решением будет написание прямого SQL-запроса к таблицам базы данных. В реляционных базах данных для получения непрерывного календаря используется генерация виртуальной таблицы дат с последующим левым соединением (LEFT OUTER JOIN) с таблицами документов.
Проанализируем концептуальный T-SQL шаблон для MS SQL Server, который решает эту задачу на стороне СУБД за миллисекунды:
-- Пример концептуального построения прямого запроса
-- Шаг 1: Генерируем временную таблицу дат (календарь) за период
DECLARE @StartDate DateTime = :НачДата;
DECLARE @EndDate DateTime = :КонДата;
;WITH Calendar AS (
SELECT @StartDate AS [DateValue]
UNION ALL
SELECT DATEADD(day, 1, [DateValue])
FROM Calendar
WHERE [DateValue] < @EndDate
)
-- Шаг 2: Объединяем календарь с реальными данными документов ПКО и РКО
SELECT
c.DateValue AS [ДатаДок],
ISNULL(SUM(pko_docs.Summa), 0) AS [СуммаПКО],
ISNULL(SUM(rko_docs.Summa), 0) AS [СуммаРКО]
FROM Calendar c
LEFT JOIN (
-- Подзапрос по приходным кассовым ордерам
SELECT
$DocPKO.ДатаДок AS [DateDoc],
$DocPKO.СуммаВзаиморасчетов AS [Summa]
FROM $Документ.ПКО AS DocPKO
WHERE DocPKO.Closed & 1 = 1 -- учитываем только проведенные документы
AND $DocPKO.Фирма = :ВбрФирма
) pko_docs ON pko_docs.DateDoc = c.DateValue
LEFT JOIN (
-- Подзапрос по расходным кассовым ордерам
SELECT
$DocRKO.ДатаДок AS [DateDoc],
$DocRKO.СуммаВзаиморасчетов AS [Summa]
FROM $Документ.РКО AS DocRKO
WHERE DocRKO.Closed & 1 = 1
AND $DocRKO.Фирма = :ВбрФирма
) rko_docs ON rko_docs.DateDoc = c.DateValue
GROUP BY c.DateValue
ORDER BY c.DateValue
OPTION (MAXRECURSION 0);
Прямой запрос полностью переносит расчеты на сервер СУБД. Использование рекурсивного обобщенного табличного выражения (CTE) создает непрерывную последовательность дат "на лету". Функция ISNULL() гарантирует, что если по конкретной дате соединение вернет пустое значение (NULL), в результат запишется честный математический ноль. Этот метод незаменим, когда в периоде содержатся тысячи дат, а сам отчет строится в разрезе множества дополнительных аналитик.
Выбор конкретного метода зависит от масштаба вашей информационной базы и требований к производительности:
1С++ наиболее эффективным выбором станет Способ 3 (прямой SQL-запрос), который обеспечит мгновенный результат при любых объемах данных.