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