Когда мы работаем с данными взаиморасчетов в 1С 7.7, одной из распространенных задач является получение общей суммы долга по каждому контрагенту и последующий вывод на печать только тех из них, у кого имеется ненулевая задолженность. Мы рассмотрим два основных подхода к решению этой задачи: непосредственно в запросе к базе данных и с использованием объекта ТаблицаЗначений в коде 1С.
Наш клиент сталкивается с этой задачей, получая данные из регистра "ВзаиморасчетыПокупателей" и затем формируя из них табличную часть для печати. Важно также учитывать особенности платформы 1С 7.7, в частности, конфигурации ПУБ (Производство-Услуги-Бухгалтерия), что накладывает свои нюансы на написание запросов и обработку данных.
Прежде чем приступить к решению, давайте проанализируем исходный запрос, который предоставляет наш клиент. Он уже получает данные о долге и группирует их, но для агрегации общей суммы по контрагенту и фильтрации нулевых значений потребуется доработка:
ТекстЗапроса = "//{{ЗАПРОС(Сформировать)
|Период с (НачалоПериодаБИ) по(ТекущаяДата);
|Обрабатывать НеПомеченныеНаУдаление;
|Контрагент = Регистр.ВзаиморасчетыПокупателей.Контрагент;
|КредДокумент = Регистр.ВзаиморасчетыПокупателей.КредДокумент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг;
|Функция ДолгКонОст = КонОст(Долг);
|Группировка Контрагент упорядочить по Контрагент.Наименование без групп;
|Группировка КредДокумент;
|Условие(Контрагент в ВыбКонтрагент);
|Условие(Долг<>0);
|"//}}ЗАПРОС
В данном запросе мы видим несколько ключевых моментов:
Использование Период с (НачалоПериодаБИ) по(ТекущаяДата): это указывает на то, что данные выбираются за достаточно широкий период, определенный в настройках системы.
Поля Контрагент, КредДокумент, Долг: извлекаются соответствующие измерения и ресурс регистра взаиморасчетов.
Функция ДолгКонОст = КонОст(Долг): эта функция предназначена для получения конечного остатка по ресурсу Долг. В контексте регистра взаиморасчетов она обычно возвращает остаток по каждому измерению, то есть в данном случае — по каждому кредитному документу для каждого контрагента.
Группировка по Контрагент и КредДокумент: поскольку присутствует группировка по КредДокумент, результат запроса будет содержать отдельные строки для каждого кредитного документа, а не общую сумму по контрагенту.
Условие Условие(Долг<>0): это условие фильтрует отдельные записи регистра, где значение поля Долг равно нулю. Однако, если наша задача — отфильтровать контрагентов, у которых итоговая суммарная задолженность равна нулю, это условие может быть недостаточным или даже некорректным, если нам нужно сначала суммировать, а потом фильтровать агрегированное значение.
Условие Условие(Контрагент в ВыбКонтрагент): это условие позволяет ограничить выборку конкретными контрагентами. Мы обсудим его влияние на производительность позднее.
Цель состоит в том, чтобы получить одну строку на каждого контрагента с его общей суммой долга, при этом исключив тех, у кого итоговый долг равен нулю. Рассмотрим, как этого достичь.
Этот метод является наиболее эффективным, поскольку вся работа по агрегации и фильтрации выполняется на стороне базы данных. Это позволяет минимизировать объем передаваемых данных и сократить нагрузку на приложение 1С. Мы модифицируем запрос таким образом, чтобы он сразу возвращал итоговую сумму долга по каждому контрагенту, исключая тех, у кого итоговый долг равен нулю.
Изменяем функцию агрегации для получения общей суммы:
Вместо Функция ДолгКонОст = КонОст(Долг);, которая вычисляет остаток по каждому документу или отдельной записи, нам необходима общая сумма всех долговых обязательств по каждому контрагенту. Для этого мы будем использовать агрегатную функцию Сумма(Долг). Эта функция просуммирует значения поля Долг для всех записей, попадающих в каждую группу.
Таким образом, строка изменится на: Функция СуммаДолга = Сумма(Долг);.
Убираем ненужную группировку по кредитному документу:
Если наша цель — получить общий долг по контрагенту, нам не нужна детализация по каждому КредДокумент. Наличие Группировка КредДокумент; привело бы к тому, что для одного контрагента было бы несколько строк, каждая из которых соответствовала бы отдельному документу. Удаляя эту группировку, мы гарантируем, что агрегация (Сумма(Долг)) будет производиться для всех записей, относящихся к одному контрагенту.
Мы удаляем строку: Группировка КредДокумент;.
Добавляем условие фильтрации по агрегированной сумме:
После того как запрос вычислит сумму долга для каждого контрагента, мы можем отфильтровать тех контрагентов, у которых эта итоговая суммарная задолженность равна нулю. Исходное условие Условие(Долг<>0); фильтровало отдельные записи регистра. Для фильтрации итоговой суммы нам нужно использовать условие на агрегированное поле.
Мы добавляем условие: Условие(СуммаДолга <> 0);. Это условие будет применено к результату агрегации, отбирая только тех контрагентов, чья общая задолженность не равна нулю.
Обратите внимание, что условие Условие(Долг<>0); из оригинального запроса в этом случае можно удалить, так как оно фильтрует отдельные записи до суммирования, что может привести к неточным результатам, если, например, по контрагенту есть как положительные, так и отрицательные записи с ненулевым долгом, которые в сумме дают ноль.
Давайте посмотрим на модифицированный текст запроса:
ТекстЗапроса = "//{{ЗАПРОС(Сформировать)
|Период с (НачалоПериодаБИ) по(ТекущаяДата);
|Обрабатывать НеПомеченныеНаУдаление;
|Контрагент = Регистр.ВзаиморасчетыПокупателей.Контрагент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг;
|Функция СуммаДолга = Сумма(Долг); // Суммируем долг по каждому контрагенту
|Группировка Контрагент упорядочить по Контрагент.Наименование без групп;
|Условие(СуммаДолга <> 0); // Фильтруем контрагентов с нулевым итоговым долгом
|Условие(Контрагент в ВыбКонтрагент); // Условие по выбранным контрагентам
|"//}}ЗАПРОС
После выполнения такого запроса мы получим выборку, где каждая строка будет соответствовать одному контрагенту, и в этой строке будет уже просуммированный и ненулевой остаток долга. Такой подход обеспечивает максимальную производительность, так как все вычисления выполняются на уровне СУБД.
Свернуть() для ТаблицыЗначенийЭтот подход полезен, если по каким-либо причинам нам необходимо сначала получить более детализированные данные, а затем уже агрегировать их и фильтровать на уровне ТаблицыЗначений (ТЗ) в коде 1С. Например, если нам нужны дополнительные поля из исходной выборки, которые не участвуют в группировке, или если мы предпочитаем выполнять часть логики обработки данных в памяти приложения.
Выгружаем данные запроса в ТЗ без предварительного суммирования:
Сначала мы выполняем запрос, который получает данные о долге по каждой записи регистра, включающей поля Контрагент и Долг. На этом этапе мы не будем выполнять суммирование в запросе, чтобы иметь возможность манипулировать данными в ТЗ.
Важно: на этом этапе мы убираем условие Условие(Долг<>0);, если оно изначально присутствовало и фильтровало записи до суммирования. Это делается для того, чтобы никакие записи, которые могут повлиять на общую сумму (например, если есть оплата и начисление, которые в сумме дают ноль), не были отфильтрованы преждевременно.
ТекстЗапроса = "//{{ЗАПРОС(Сформировать)
|Период с (НачалоПериодаБИ) по(ТекущаяДата);
|Обрабатывать НеПомеченныеНаУдаление;
|Контрагент = Регистр.ВзаиморасчетыПокупателей.Контрагент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг; // Получаем долг по каждой записи
|Группировка Контрагент; // Группировка может быть и без упорядочивания, если важен только контрагент
|КредДокумент = Регистр.ВзаиморасчетыПокупателей.КредДокумент; // Можно оставить для детализации, если нужна, но Свернуть() ее удалит если не указать
|Условие(Контрагент в ВыбКонтрагент); // Это условие может быть убрано для оптимизации, как описано ниже
|"//}}ЗАПРОС
Запрос = СоздатьОбъект("Запрос");
Запрос.УстановитьПараметр("НачалоПериодаБИ", НачалоПериодаБИ);
Запрос.УстановитьПараметр("ТекущаяДата", ТекущаяДата());
Запрос.УстановитьПараметр("ВыбКонтрагент", ВыбКонтрагент);
Если Запрос.Выполнить() = 0 Тогда // Проверяем успешность выполнения запроса
Возврат; // Или выводим сообщение об ошибке
КонецЕсли;
ТЗРезультат = СоздатьОбъект("ТаблицаЗначений");
Запрос.Выполнить().Выгрузить(ТЗРезультат);
После выполнения этого кода в переменной ТЗРезультат будет находиться ТаблицаЗначений, содержащая все записи, соответствующие условиям запроса, с полями Контрагент и Долг (и, возможно, другими, если они были запрошены).
Сворачиваем ТаблицуЗначений:
Теперь, когда данные находятся в ТЗ, мы используем метод Свернуть(). Этот метод позволяет сгруппировать строки по указанным колонкам и просуммировать значения в других колонках. Это аналог агрегации GROUP BY и SUM() в запросе, но выполняемый в памяти 1С.
// Предположим, что в ТЗРезультат есть колонки "Контрагент" и "Долг"
// Сворачиваем по контрагенту, суммируя значения в колонке "Долг"
ТЗРезультат.Свернуть("Контрагент", "Долг");
В этом примере мы указываем "Контрагент" как группировочную колонку, а "Долг" как суммируемую. Метод Свернуть() изменит текущую ТЗ: будет создана одна строка для каждого уникального контрагента, и в колонке Долг будет находиться их суммарная задолженность. Колонки, не указанные ни как группировочные, ни как суммируемые (например, КредДокумент), будут автоматически удалены из таблицы значений.
Фильтруем контрагентов с нулевым суммарным долгом:
После свертки в ТЗРезультат теперь находятся итоговые суммы долга по каждому контрагенту. Нам осталось пройтись по этой таблице и удалить или пропустить строки, где суммарный долг равен нулю.
// Обходим ТаблицуЗначений с конца, чтобы избежать проблем со смещением индексов
// при удалении строк
Для i = ТЗРезультат.КоличествоСтрок() по 1 Цикл
ТЗРезультат.ПолучитьСтрокуПоНомеру(i);
Если ТЗРезультат.Долг = 0 Тогда
ТЗРезультат.УдалитьСтроку(i);
КонецЕсли;
КонецЦикла;
Важно: При удалении строк из ТаблицыЗначений в цикле всегда рекомендуется обходить ТЗ с конца к началу (от ТЗ.КоличествоСтрок() до 1). Это гарантирует, что при удалении строки индексы оставшихся строк не "сдвинутся" таким образом, чтобы мы пропустили следующую строку или попытались обратиться к несуществующему индексу.
После выполнения всех этих шагов ТЗРезультат будет содержать только те контрагенты, у которых есть ненулевой суммарный долг, и будет готова для дальнейшей обработки или вывода на печать.
В процессе обсуждения был затронут крайне важный аспект — производительность запросов, особенно при использовании условия Условие(Контрагент в ВыбКонтрагент);. В 1С 7.7 оптимизация запросов работает не так эффективно, как в более новых версиях платформы, и это может сильно влиять на скорость работы системы при больших объемах данных.
Проблема с условием "В" (IN):
Когда список ВыбКонтрагент содержит большое количество элементов (например, несколько сотен или тысяч), или когда поле "Контрагент" в базе данных (часто это поле "CODE" или "ID" справочника в SQL-таблице) неоптимально проиндексировано, условие Условие(Контрагент в ВыбКонтрагент); может значительно замедлять выполнение запроса. Это связано с тем, что СУБД (например, MS SQL Server), лежащая под 1С 7.7, может неэффективно использовать индексы при обработке большого списка значений в операторе IN. В некоторых случаях СУБД может переходить к полному сканированию таблицы, что катастрофически сказывается на времени выполнения.
Альтернативный подход: фильтрация в коде 1С вместо запроса:
В некоторых ситуациях, для повышения производительности, более быстрым решением может оказаться отказ от условия Условие(Контрагент в ВыбКонтрагент); непосредственно в тексте запроса. Вместо этого мы можем:
Выполнить запрос, который выбирает данные для всех контрагентов или для более широкого круга, чем список ВыбКонтрагент. Это означает, что запрос будет выполняться без сложного условия IN, потенциально используя более простые и эффективные индексы.
Выгрузить результат запроса в ТаблицуЗначений. На этом этапе в памяти приложения 1С будет находиться большой объем данных.
Уже на уровне кода 1С, после получения всех данных, отфильтровать ненужных контрагентов. Для этого мы можем использовать методы СпискаЗначений или ТаблицыЗначений, в которой хранится список ВыбКонтрагент. Например, метод НайтиЗначение() для объекта СписокЗначений является довольно быстрым для поиска наличия элемента.
// Пример фильтрации после получения данных в ТЗ, если ВыбКонтрагент - СписокЗначений
// Предполагается, что ТЗРезультат содержит колонку "Контрагент"
// Если ВыбКонтрагент является СпискомЗначений
Если ВыбКонтрагент.Вид() = "СписокЗначений" Тогда
Для i = ТЗРезультат.КоличествоСтрок() по 1 Цикл
ТЗРезультат.ПолучитьСтрокуПоНомеру(i);
// Если контрагент из ТЗРезультат не найден в списке выбранных
Если ВыбКонтрагент.НайтиЗначение(ТЗРезультат.Контрагент) = 0 Тогда
ТЗРезультат.УдалитьСтроку(i);
КонецЕсли;
КонецЦикла;
ИначеЕсли ВыбКонтрагент.Вид() = "ТаблицаЗначений" Тогда
// Если ВыбКонтрагент - ТаблицаЗначений с колонкой "Контрагент"
// Можно создать временный СписокЗначений из ВыбКонтрагент для быстрого поиска
СписокВыбранныхКонтрагентов = СоздатьОбъект("СписокЗначений");
ВыбКонтрагент.ВыбратьСтроки();
Пока ВыбКонтрагент.ПолучитьСтроку() = 1 Цикл
СписокВыбранныхКонтрагентов.ДобавитьЗначение(ВыбКонтрагент.Контрагент);
КонецЦикла;
Для i = ТЗРезультат.КоличествоСтрок() по 1 Цикл
ТЗРезультат.ПолучитьСтрокуПоНомеру(i);
Если СписокВыбранныхКонтрагентов.НайтиЗначение(ТЗРезультат.Контрагент) = 0 Тогда
ТЗРезультат.УдалитьСтроку(i);
КонецЕсли;
КонецЦикла;
КонецЕсли;
Этот подход переносит часть фильтрации с сервера базы данных на сторону приложения 1С. Он требует тестирования на вашей конкретной базе данных с реальными объемами данных, чтобы определить, какой метод (фильтрация в запросе или в коде) оказывается быстрее в вашей конкретной ситуации. Иногда загрузка большого объема данных по сети и их последующая фильтрация в приложении может быть быстрее, чем заставить СУБД медленно обрабатывать сложный IN-запрос.
Влияние периода обработки:
Мы также должны внимательно относиться к периоду, за который формируется отчет. Использование Период с (НачалоПериодаБИ) по(ТекущаяДата) может охватывать очень большой промежуток времени, иногда за несколько лет, что само по себе может значительно замедлять выполнение запроса. Мы всегда должны стремиться максимально сужать период выборки, если это возможно по логике отчета. Например, если отчет нужен только за текущий год или месяц, мы должны четко это указать в параметрах запроса.
Подведем итог и рассмотрим общий алгоритм формирования отчета с суммарной задолженностью по контрагентам в 1С 7.7, учитывая наши решения и рекомендации по производительности:
Определение и получение параметров отчета:
Мы собираем от пользователя необходимые параметры, такие как период отчета (например, дата начала и дата окончания) и список выбранных контрагентов (объект ВыбКонтрагент, который может быть СпискомЗначений или ТаблицейЗначений). Предположим, что НачалоПериодаБИ и ТекущаяДата уже определены или получены.
Формирование текста запроса:
Конструируем текст запроса, используя один из описанных выше методов:
Если выбран метод "Суммирование в запросе": Включаем Функция СуммаДолга = Сумма(Долг);, оставляем Группировка Контрагент;, убираем Группировка КредДокумент; и добавляем Условие(СуммаДолга <> 0);. Условие Условие(Контрагент в ВыбКонтрагент); также добавляем сюда, если решили его использовать в запросе.
Если выбран метод "Свертка ТЗ": Запрашиваем Контрагент и Долг (и другие нужные поля) без агрегации Сумма() и без фильтрации Условие(Долг<>0);. Условие Условие(Контрагент в ВыбКонтрагент); можно как оставить, так и убрать, если планируется фильтрация в коде для оптимизации.
Создание и выполнение объекта "Запрос":
Запрос = СоздатьОбъект("Запрос");
Запрос.УстановитьПараметр("НачалоПериодаБИ", НачалоПериодаБИ); // Используем переменные, определенные ранее
Запрос.УстановитьПараметр("ТекущаяДата", ТекущаяДата); // Если нужна текущая дата
Если ТипЗначенияСтр(ВыбКонтрагент) = "СписокЗначений" Тогда
Запрос.УстановитьПараметр("ВыбКонтрагент", ВыбКонтрагент); // Только если фильтруем в запросе
КонецЕсли;
Запрос.Текст(ТекстЗапроса);
ТЗДляОтчета = СоздатьОбъект("ТаблицаЗначений");
Если Запрос.Выполнить() = 0 Тогда
Сообщить("Ошибка выполнения запроса!");
Возврат;
КонецЕсли;
Запрос.Выгрузить(ТЗДляОтчета);
Дополнительная обработка ТаблицыЗначений (если суммирование и/или фильтрация не были выполнены полностью в запросе):
Если выбран метод "Свертка ТЗ": Применяем метод Свернуть():
ТЗДляОтчета.Свернуть("Контрагент", "Долг");
Фильтрация по нулевому долгу после свертки: Если суммирование было выполнено в ТЗ (или даже в запросе, но мы хотим перепроверить), проходим по ТЗ и удаляем строки с нулевым долгом, обходя ТЗ с конца:
Для i = ТЗДляОтчета.КоличествоСтрок() по 1 Цикл
ТЗДляОтчета.ПолучитьСтрокуПоНомеру(i);
Если ТЗДляОтчета.Долг = 0 Тогда
ТЗДляОтчета.УдалитьСтроку(i);
КонецЕсли;
КонецЦикла;
Фильтрация по ВыбКонтрагент в коде: Если условие Условие(Контрагент в ВыбКонтрагент); было убрано из запроса для оптимизации, выполняем фильтрацию программно, как было описано в разделе о производительности. Этот шаг нужно выполнять перед сверткой, если ВыбКонтрагент не участвует в свертке, или после, если это не принципиально.
Вывод данных на печать:
Полученная итоговая ТаблицаЗначений (ТЗДляОтчета) теперь содержит только контрагентов с ненулевым суммарным долгом и готова для вывода в табличную часть печатной формы или отчета. Наш клиент упомянул, что использует некий "отчет по ТЗ", который является удобным инструментом для форматированного вывода подготовленной таблицы значений.
Используя эти подходы и учитывая рекомендации по производительности, мы сможем эффективно решить задачу по формированию отчета с суммарной задолженностью по контрагентам в 1С 7.7, обеспечивая при этом хорошую скорость работы и точность данных. Мы всегда должны стремиться выбирать наиболее подходящий метод исходя из объема данных, сложности отчета и требований к производительности системы.