Идеальным, с точки зрения 1С: Предприятие 7.7, является такой запрос — который построен на одной [виртуальной] таблице, без соединений, с отбором по индексированному полю и минимальным набором реквизитов в результирующей таблице. Такой запрос будет достаточно быстро выполнен платформой и поэтому, в качестве методических рекомендаций, фирма 1С предлагает, в частности, под каждый аналитический отчет, создавать отдельный регистр оперативного учета. Среди прочих рекомендаций, предлагается не делать вызов запроса в цикле и не выгружать результаты запроса в таблицу значений, для дальнейшей работы с данными.
В этой статье, я приведу пример расчетной задачи, для решения которой, придется нарушить, как минимум, две из вышеуказанных рекомендаций. По-другому, эту задачу не решить, просто. Потом приведу пример решения этой задачи через прямые запросы, с вызовом vfpoledb-драйвера dbf-баз данных от Microsoft, используя навигационный подход работы с таблицами базы данных.
Постановка задачи:
- Фирма реализует товар оптовым покупателям;
- По реализации, клиентам предоставляется отсрочка платежа, некоторое количество дней, указано в карточке клиента;
- Для отдельных клиентов, отсрочка платежа рассчитывается по календарю задолженности, также указанному в карточке клиента;
- Требуется найти всех клиентов, имеющих просроченную задолженность.
Перед тем, как решать эту задачу, хотел бы отметить некоторые ее особенности. Во-первых, период отсрочки платежа, на всей совокупности клиентов — разный, а, следовательно, коль скоро период отбора движений является параметром виртуальной таблицы итогов регистра, нам потребуется столько раз выполнить запрос, сколько у нас будет разных периодов отсрочки платежа, с учетом корректировки сроков по календарям. Во-вторых, этих календарей может быть несколько, соответственно, множество клиентов по периодам «дробится» еще больше. И, наконец, следует заметить, что расчитанный ранее результат устаревает не только с вводом новых документов движения взаиморасчетов, а просто, с течением времени, что не позволяет использовать таблицы остатков, для извлечения этих данных.
//*******************************************
Функция ОтборКлиентовСДолгамиНаТекущуюДату(тзКлиенты)
// стандартный объект 'запрос'
Запрос = СоздатьОбъект("Запрос");
// текст запроса на выборку клиентов, имеющих актуальный долг
ТекстЗапроса = "//{{ЗАПРОС(Сформировать)
|Без итогов;
|Клиент = Регистр.ВзаиморасчетыПокупателей.Клиент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг;
|Функция ДолгКонОст = КонОст(Долг);
|Группировка Клиент без упорядочивания без групп;
|"//}}ЗАПРОС
;
// замер времени выполнения запроса
время = _getPerformanceCounter();
// процедура выполнения запроса
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
Возврат 0;
КонецЕсли;
// вывод времени, потраченного системой на исполнения запроса
Сообщить("Время запроса: "
+ (_getPerformanceCounter() - время));
// замер времени на выгрузку результатов
время = _getPerformanceCounter();
Запрос.Выгрузить(тзКлиенты);
// вывод времени, потраченного на выгрузку результатов
Сообщить("Время выгрузки результатов:"
+ (_getPerformanceCounter() - время));
// вывод количества результирующих строк запроса
Сообщить("Строк запроса: " + тзКлиенты.КоличествоСтрок());
Возврат 1;
КонецФункции
//*******************************************
Процедура ВычислитьДатуНачалаОтсрочкиДолга(тзКлиенты)
// новый реквизит таблицы для хранения даты начала периода отсрочки
тзКлиенты.НоваяКолонка("ДатаНачала");
тзКлиенты.ВыбратьСтроки();
Пока тзКлиенты.ПолучитьСтроку() = 1 Цикл
Календарь = тзКлиенты.Клиент.КалендарьЗадолженности;
Если Календарь.Выбран() = 1 Тогда
// расчет даты начала периода отсрочки по календарю задолженности
тзКлиенты.ДатаНачала = Календарь.ПолучитьДату(ТекущаяДата(),
- тзКлиенты.Клиент.Глубина.Получить(ТекущаяДата()) - 1);
Иначе
// расчет даты начала периода отсрочки от текущей даты
тзКлиенты.ДатаНачала = ТекущаяДата()
- тзКлиенты.Клиент.Глубина.Получить(ТекущаяДата()) - 1;
КонецЕсли;
КонецЦикла;
КонецПроцедуры
//*******************************************
Функция ВыполнитьЗапросПоПросрДолгу(тзКлиенты, ДатаНачала, сзКлиенты)
Если сзКлиенты.РазмерСписка() = 0 Тогда
// передано пустое подмножество таблицы клиентов
Возврат 1;
КонецЕсли;
// стандартный объект 'запрос'
Запрос = СоздатьОбъект("Запрос");
// текст запроса на вычисление накопленного за период отсрочки долга
ТекстЗапроса = "//{{ЗАПРОС(Сформировать2)
|Период с ДатаНачала;
|Без итогов;
|Клиент = Регистр.ВзаиморасчетыПокупателей.Клиент;
|Долг = Регистр.ВзаиморасчетыПокупателей.Долг;
|Функция ДолгПриход = Приход(Долг);
|Группировка Клиент без упорядочивания без групп;// все;
|Условие(Клиент в сзКлиенты);
|"//}}ЗАПРОС
;
// замер времени выполнения запроса
время = _getPerformanceCounter();
// процедура выполнения запроса
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
Возврат 0;
КонецЕсли;
// вывод времени, потраченного системой на исполнения запроса
Сообщить("Время запроса: "
+ (_getPerformanceCounter() - время));
Пока Запрос.Группировка() = 1 Цикл
НС = 0;
Если тзКлиенты.НайтиЗначение(Запрос.Клиент, НС, "Клиент") = 1 Тогда
// для каждого из результатов запроса корректируем строку в таблице клиентов
тзКлиенты.УстановитьЗначение(НС, "ПросрДолг",
Макс(0, тзКлиенты.ПолучитьЗначение(НС, "ДолгКонОст")
- Запрос.ДолгПриход));
КонецЕсли;
КонецЦикла;
Возврат 1;
КонецФункции
//*******************************************
Функция ВычислитьПросроченныйДолг(тзКлиенты)
Перем ДатаНачала;
// текущая "порция" клиентов для запроса по просроченному долгу
сзКлиенты = СоздатьОбъект("СписокЗначений");
тзКлиенты.НоваяКолонка("ПросрДолг");
//
тзКлиенты.ВыбратьСтроки();
Пока тзКлиенты.ПолучитьСтроку() = 1 Цикл
тзКлиенты.ПросрДолг = Макс(0, тзКлиенты.ДолгКонОст);
КонецЦикла;
//
тзКлиенты.Сортировать("ДатаНачала");
НС = 1;
Пока 1=1 Цикл
Если НС > тзКлиенты.КоличествоСтрок() Тогда
// запуск процедуры расчета просроченного долга для последней порции клиентов
Если ВыполнитьЗапросПоПросрДолгу(тзКлиенты,
ДатаНачала, сзКлиенты) = 0 Тогда
Возврат 0;
КонецЕсли;
// выход из цикла и процедуры
Возврат 1;
Иначе
тзКлиенты.ПолучитьСтрокуПоНомеру(НС);
Если ДатаНачала <> тзКлиенты.ДатаНачала Тогда
// нашли начало новой порции данных
Если ПустоеЗначение(ДатаНачала) = 1 Тогда
// это - первая порция данных
Иначе
// данные для расчетов по предыдущей порции - собраны
Если ВыполнитьЗапросПоПросрДолгу(тзКлиенты,
ДатаНачала, сзКлиенты) = 0 Тогда
Возврат 0;
КонецЕсли;
сзКлиенты.УдалитьВсе();
КонецЕсли;
ДатаНачала = тзКлиенты.ДатаНачала;
Иначе
// продолжаем собирать данные...
КонецЕсли;
сзКлиенты.ДобавитьЗначение(тзКлиенты.Клиент);
КонецЕсли;
// итератор по строкам таблицы
НС = НС + 1;
КонецЦикла;
КонецФункции
//*******************************************
Процедура Сформировать()
Перем тзКлиенты;
// замер времени, необходимого для вычислений
время = _getPerformanceCounter();
// выборка клиентов, имеющих долг на текущую дату
Если ОтборКлиентовСДолгамиНаТекущуюДату(тзКлиенты) = 1 Тогда
ВычислитьДатуНачалаОтсрочкиДолга(тзКлиенты);
Если ВычислитьПросроченныйДолг(тзКлиенты) = 0 Тогда
Сообщить("Не удалось сформировать отчет", "!");
КонецЕсли;
КонецЕсли;
// оценка временных затрат
Сообщить("Всего затрачено времени: "
+ (_getPerformanceCounter() - время));
тзКлиенты.Сортировать("Клиент");
тзКлиенты.ВыбратьСтроку();
КонецПроцедурыСогласно статистике выполнения данного запроса на рабочей базе, при порядке 2-3 тыс. активных клиентов, данный расчет выполняется в течении 9-10 секунд. При этом, максимальная продолжительность выполнения единичного запроса расчета просроченной задолженности по группе клиентов с одинаковым кредитным периодом, примерно равна 4-5 секунд.
глмOLEDBQuery.ВыполнитьИнструкцию("_test(CTOD('11/14/2019'),CTOD('12/26/2019'))", "", 1, 0, 0, 0);
тзРезультат = глмOLEDBQuery.ВыполнитьИнструкцию("SELECT * FROM credits", "", 1, 0, 0, 0);&& параметры процедуры:
&& - текущая (системная) дата
&& - дата среза таблицы актуальных итогов
PARAMETER dCurrentDate, dSliceDate
&& результирующая таблица сведений по долгам и просроченным долгам покупателей
CREATE TABLE credits(customer CHAR(9), debt NUMERIC(15,2), overdue_d NUMERIC(15,2))
&& временная таблица с расчетами параметров кредитного периода
CREATE TABLE calendar_c(ID CHAR(4), grace_p NUMERIC(3,0), start_d DATETIME)
INDEX ON ID TAG ID OF calendar_c
&& таблица итогов остатков взаиморасчетов
USE rg169 AGAIN ALIAS totals IN 0 ORDER TAG prop DESC SHARED
&& таблица констант для вычисления текущего значения периода отсрочки
USE 1sconst AGAIN ALIAS constants IN 0 ORDER TAG idd DESC SHARED
&& таблица клиентов для поиска сведений о календаре исчисления кредитного периода
USE sc46 AGAIN ALIAS customers IN 0 ORDER TAG idd SHARED
&& открытие таблицы календарей для вычисления граниз периода кредита
USE cl AGAIN ALIAS cl IN 0 ORDER TAG 'id+date' DESC SHARED
&& открытие таблицы движений регистра взаиморасчетов
USE ra169 AGAIN ALIAS entries IN 0 ORDER TAG via170 DESC SHARED
&& анализ среза таблицы итогов
SELECT totals
IF SEEK(DTOS(dSliceDate), 'totals', 'prop')
SCAN WHILE totals.period = dSliceDate
IF totals.sp171 > 0
&& рассматриваем только дебиторскую задолженность
cCurrentCustomer = totals.sp170
nCurrentDebt = totals.sp171
&& отсрочку платежа еще требуется вычислить
nCurrentGracePeriod = 0
SELECT constants
IF SEEK(' P3' + cCurrentCustomer, 'constants', 'idd')
SCAN WHILE constants.ID + constants.Objid = '' + cCurrentCustomer
&& перебор периодических значений реквизита в обратном хронологическом порядке
nCurrentGracePeriod = VAL(constants.Value)
EXIT
ENDSCAN
ENDIF
&& расчет параметров кредитного периода
dGracePeriodStartDate = dCurrentDate - nCurrentGracePeriod
SELECT customers
IF SEEK(cCurrentCustomer, 'customers', 'idd')
SCAN WHILE customers.ID = cCurrentCustomer
&& определение заданного в карточке клиента календаря
cCurrentCalendar = SUBSTR(customers.sp6588, 7, 4)
EXIT
ENDSCAN
ENDIF
IF cCurrentCalendar <> ' 0'
&& задан непустой календарь
SELECT calendar_c
IF SEEK(cCurrentCalendar, 'calendar_c', 'id')
SCAN WHILE calendar_c.ID = cCurrentCalendar
IF calendar_c.grace_p = nCurrentGracePeriod
&& нашли ранее рассчитанный результат по выбранному календарю
dGracePeriodStartDate = calendar_c.start_d
EXIT
ENDIF
ENDSCAN
ELSE
SELECT cl
&& общее количество рабочих дней
nWorkingDays = 0
IF SEEK(cCurrentCalendar, 'cl', 'id+date')
SCAN WHILE cl.ID = cCurrentCalendar
IF cl.daylen > 0
nWorkingDays = nWorkingDays + 1
ENDIF
IF nCurrentGracePeriod = nWorkingDays
dGracePeriodStartDate = cl.date
EXIT
ENDIF
&& если расчет дат по календарю по разным причинам не будет закончен - используется разность дат
ENDSCAN
&& сохранить результат расчетов в таблицу
INSERT INTO calendar_c(ID, grace_p, start_d) _
VALUES(cCurrentCalendar, nCurrentGracePeriod, dGracePeriodStartDate)
ENDIF
ENDIF
ENDIF
&& расчет прихода долга за кредитный период
nReceiptDebt = 0
SELECT entries
IF SEEK(cCurrentCustomer, 'entries', 'via170')
SCAN WHILE entries.sp170 = cCurrentCustomer _
AND entries.date >= dGracePeriodStartDate
IF entries.sp171 > 0
nReceiptDebt = nReceiptDebt + entries.sp171
ENDIF
&& дальнейший расчет можно не продолжать, просроченного долга нет
IF nReceiptDebt >= nCurrentDebt
EXIT
ENDIF
ENDSCAN
ENDIF
&& расчет просроченной задолженности
IF nCurrentDebt > nReceiptDebt
nOverdueDebt = nCurrentDebt - nReceiptDebt
ELSE
nOverdueDebt = 0
ENDIF
&& запись результатов расчета в таблицу
INSERT INTO credits(customer, debt, overdue_d) _
VALUES(cCurrentCustomer, nCurrentDebt, nOverdueDebt)
ENDIF
ENDSCAN
ELSE
&& не нашли таблицу итогов регистра, соответствующую выбранной дате
RETURN 0
ENDIF
&& процедура расчета завершена успешно
RETURN 1Скорость выполнения этого запроса на том же массиве данных уже существенно выше. Он выполняется, примерно, за 950 миллисекунд, то есть, мы имеем десятикратное увеличение производительности запроса. Оно происходит, во-первых, от того, что навигационный метод с dbf-таблицами, сам по себе, работает существенно быстрее. Во-вторых, все вычисления у нас укладываются в один запрос. В-третьих, используя навигационный метод, мы можем заранее пресечь дальнейшее выполнение расчетов по определенной аналитике, когда «видим» явные признаки того, что в них нет необходимости. Как это было, когда мы прерывали цикл расчета накопления задолженности, по достижению суммы текущего долга.
В заключение, хочется отметить, что выполнение прямых запросов посредством vfpoledb-драйвера от Microsoft — не единственное решение. Также можно применять vfpodbc-драйвер, от той-же Microsoft или 1sqlite-драйвер от разработчиков внешних компонент для 1С: Предприятие 7.7. В своем решении, следует выбирать более подходящий вариант, в зависимости от текущей задачи, формата базы данных, текущего системного окружения и других, существенных по мнению разработчика, моментов.