При разработке управленческих отчетов программисты часто сталкиваются с задачей сопоставления данных из разных источников: например, фактических оплат из регистра накопления ВзаиморасчетыСКонтрагентами и плановых начислений (ставок) из документов, которые не делают движений по регистрам (например, ЗаказыНаТранспорт). Типичная ошибка в таких случаях — использование соединений по периоду и контрагенту, что приводит к "расползанию" сумм или их дублированию. Рассмотрим подробнее, как решить эту проблему с помощью объединения таблиц и корректного получения курсов валют.
Проанализируем ситуацию: если мы используем ЛЕВОЕ СОЕДИНЕНИЕ таблицы оплат с таблицей заказов по полям Контрагент и ПериодМесяц, система пытается сопоставить каждую запись об оплате с каждой записью о заказе в рамках одного месяца. Если в одном месяце было три оплаты и два заказа, в результате соединения мы получим шесть строк (декартово произведение), и итоговые суммы будут неверными.
Более того, если в первом месяце была только оплата, а заказ появился во втором, при левом соединении по оплате данные второго месяца могут вообще не попасть в выборку или привязаться к первому месяцу, если условие соединения составлено неверно. Выясним причину: для корректного сопоставления разнородных данных в 1С общепринятым стандартом является использование конструкции ОБЪЕДИНИТЬ ВСЕ.
Рассмотрим правильную структуру запроса. Мы должны собрать данные из всех источников в одну временную таблицу, создав в каждой части объединения "поля-заглушки" (пустые поля) для показателей из другой таблицы. Посмотрим на пример реализации:
// Часть 1: Получаем оплаты
ВЫБРАТЬ
Взаиморасчеты.ПериодМесяц КАК Период,
Взаиморасчеты.Контрагент КАК Контрагент,
Взаиморасчеты.СуммаВзаиморасчетовПриход КАК СуммаОплаты,
0 КАК СуммаФрахта
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, , ) КАК Взаиморасчеты
ОБЪЕДИНИТЬ ВСЕ
// Часть 2: Получаем заказы (начисления)
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(Заказы.Дата, МЕСЯЦ),
Заказы.ПрямойВладелецТранспорта,
0,
Заказы.ФрахтСтавка
ИЗ
Документ.ЗаказыНаТранспорт КАК Заказы
ГДЕ
Заказы.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
// Часть 3: Финальная группировка
ВЫБРАТЬ
ВТ_Данные.Период,
ВТ_Данные.Контрагент,
СУММА(ВТ_Данные.СуммаОплаты) КАК СуммаОплаты,
СУММА(ВТ_Данные.СуммаФрахта) КАК СуммаФрахта
ИЗ
ВТ_Данные КАК ВТ_Данные
СГРУППИРОВАТЬ ПО
ВТ_Данные.Период,
ВТ_Данные.Контрагент
Такой подход гарантирует, что каждая сумма попадет ровно в свой период (месяц), а финальная группировка "схлопнет" записи по контрагенту, показав и оплату, и фрахт в одной строке, если они относятся к одному месяцу.
Если ставка фрахта в документе указана в валюте (например, EUR), а отчет должен быть в рублях, необходимо пересчитать сумму по курсу на дату документа. Использовать виртуальную таблицу СрезПоследних в соединении с физической таблицей документов внутри запроса нельзя, так как СрезПоследних без параметров возвращает курс на текущий момент, а с параметром-датой — на одну конкретную дату для всей выборки.
Разберем по шагам алгоритм получения курса для каждой строки документов:
КурсыВалют, где период курса меньше или равен дате документа.МАКСИМУМ(Период) регистра. Это и будет дата последнего установленного курса перед созданием документа.КурсыВалют еще раз для получения числовых значений.Проанализируем пример кода для получения итоговой суммы с учетом курса и кратности:
// 1. Получаем данные документов
ВЫБРАТЬ
Заказы.Дата КАК ДатаДок,
Заказы.ВалютаРасчета КАК Валюта,
Заказы.ФрахтСтавка КАК СуммаВалюта,
Заказы.ПрямойВладелецТранспорта КАК Контрагент
ПОМЕСТИТЬ ВТ_Логиста
ИЗ
Документ.ЗаказыНаТранспорт КАК Заказы;
////////////////////////////////////////////////////////////////////////////////
// 2. Находим ближайшую дату курса для каждого документа
ВЫБРАТЬ
ВТ_Логиста.ДатаДок,
ВТ_Логиста.Валюта,
МАКСИМУМ(КурсыВалют.Период) КАК ПериодКурса
ПОМЕСТИТЬ ВТ_ТочкиКурса
ИЗ
ВТ_Логиста КАК ВТ_Логиста
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют
ПО ВТ_Логиста.Валюта = КурсыВалют.Валюта
И ВТ_Логиста.ДатаДок >= КурсыВалют.Период
СГРУППИРОВАТЬ ПО
ВТ_Логиста.ДатаДок,
ВТ_Логиста.Валюта;
////////////////////////////////////////////////////////////////////////////////
// 3. Получаем значения курса и пересчитываем сумму
ВЫБРАТЬ
ВТ_Логиста.ДатаДок,
ВТ_Логиста.Контрагент,
ВТ_Логиста.СуммаВалюта * ЕСТЬNULL(Курсы.Курс, 1) / ЕСТЬNULL(Курсы.Кратность, 1) КАК СуммаРуб
ИЗ
ВТ_Логиста КАК ВТ_Логиста
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТочкиКурса КАК ТК
ПО ВТ_Логиста.ДатаДок = ТК.ДатаДок И ВТ_Логиста.Валюта = ТК.Валюта
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК Курсы
ПО ТК.Валюта = Курсы.Валюта И ТК.ПериодКурса = Курсы.Период
При реализации подобных запросов следует обратить внимание на несколько критических моментов:
Кратность. Для некоторых валют (например, иена или тенге) курс может указываться за 100 или 1000 единиц. Игнорирование этого поля приведет к ошибке в расчетах в десятки и сотни раз.ОБЪЕДИНИТЬ ВСЕ старайтесь типизировать пустые поля. Вместо простого 0 КАК Валюта лучше использовать ЗНАЧЕНИЕ(Справочник.Валюты.ПустаяСсылка). Это поможет избежать ошибок сопоставления типов в сложных запросах.КурсыВалют может быть затратным. Если база ведется много лет, добавьте ограничение по периоду и в ту часть запроса, которая выбирает курсы, чтобы система не просматривала записи десятилетней давности.ЕСТЬNULL. Если курс на какую-то дату не заведен, деление на 0 (пустую кратность) вызовет фатальную ошибку выполнения запроса.Таким образом, комбинируя методику объединения таблиц для сопоставления оплат и заказов с алгоритмом поиска среза курса на дату, мы получаем гибкий и точный механизм для управленческого анализа взаиморасчетов в любой валюте.