Выполнение прямых запросов к SQL в 1С с накоплением итогов по группировкам   

Как известно, при работе в c прямыми запросами к SQL-серверу результат запроса возвращается в виде простого набора записей (результирующей таблицы), для которой отсутствует возможность удобного пользования механизмом группировок, таким, каким является встроенный механизм группировок в запросе . В связи с этим отсутствуют и итоги и подытоги по группировкам запроса. Таким образом, при осуществлении сравнения «запроса 1С» и «прямого запроса» при явно выраженной более высокой производительности второго имеет место значительно более высокое удобство использования первого.

Напрашивается вопрос, есть ли возможность упростить и сделать более наглядным процесс создания текста «прямого запроса» и последующей работы с результирующими данными с тем, чтобы было удобно использовать прямые запросы для построения отчетов? Ответ на этот вопрос – Да. Такая возможность есть. В части удобства создания текста запросов могут пригодиться шаблоны, универсальные для каждой конфигурации, в независимости от ее структуры. Эту часть нам может обеспечить любое средство работы с метаданными, будь то Rainbow, 1С++, либо любая другая компонента. А возможность подсчета итогов по группировкам имеется и во встроенных средствах SQL-сервера.

Речь в данном случае идет о группировке с накоплением: GROUP BY …, …, WITH ROLLUP. Это позволяет нам получить результаты, максимально приближенные к запросу 1С со всеми итогами и подытогами.

Я приведу пример построения отчета по регистру партий. В качестве примера запроса будет использован типовой запрос к регистру (прочитать можно здесь). Но вместо прямых указаний на используемые измерения и ресурсы, текст запроса будет построен с помощью переменных, содержащих обозначения этих измерений и ресурсов. Переменные эти будут заполняться с помощью средств работы с метаданными конфигурации. Это обеспечит легкую переносимость и настраиваемость данного текста запроса. При желании его можно легко модифицировать и вмонтировать в любую конфигурацию.

В качестве примера механизма выполнения прямого запроса я выбрал 1С++ в частности за то, что результаты можно получить в таблицу значений без особого труда и с меньшим количеством кода. Это обеспечит наилучшую наглядность кода и легкость модификации. Также, 1С++ позволяет таким образом установить параметры, чтобы получить в результирующей таблице значений объекты .

В данном рассматриваемом примере есть и таблица, в которую выводятся результаты. Таким образом – на выходе мы получаем полноценный отчет по регистру партий товаров. Отчет строится за выбранный период без условий, но с группировкой по измерениям «Магазин, Товар». Используется один единственный ресурс – «Количество». После ознакомления с текстом запроса и, собственно, с самим внешним отчетом, вы можете легко его модифицировать под свои нужды.

Внимание! В новой версии отчета текст запроса оптимизирован по скорости до 40% в зависимости от периода выполнения запроса. Это достигнуто путем совмещение двух запросов по приходу и расходу в один общий.

Итак, текст отчета:
//Отчет по регистру партий с группировками и накоплением итогов.
// (с) www.perlscript.ru
//________________________________________________________
Процедура Сформировать()
    //получим значения всех таблиц и полей
    Мета=СоздатьОбъект("MetaDataWork");
    СтрокаЗаголовка="Магазин / Товар";
    РегИтоги=Мета.ИмяТаблицыИтогов("Партии");
    РегДвижения=Мета.ИмяТаблицыДвижений("Партии");
    ФлагДвижения="RF"+Мета.ИДРегистра("Партии");
    Измерение1="SP"+Мета.ИДИзмеренияРегистра("Партии","Магазин");
    Измерение2="SP"+Мета.ИДИзмеренияРегистра("Партии","Товар");
    РесурсКол="SP"+Мета.ИДРесурсаРегистра("Партии","Количество");
    //Назначим даты для итогов и движений регистра.
    ВремДата=(НачМесяца(НачДата)-1);
    Запрос=СоздатьОбъект("ODBCRecordSet");
    ДатаОстатков=""+ДатаГод(ВремДата)+"-"+Формат(ДатаМесяц(ВремДата),"Ч(0)2")+"-01";
    ДатаНачала=""+ДатаГод(НачДата)+Формат(ДатаМесяц(НачДата),"Ч(0)2")+Формат(ДатаЧисло(НачДата),"Ч(0)2");
    НачалоМесяца=НачМесяца(НачДата);
    ДатаНачалаОст=""+ДатаГод(НачалоМесяца)+Формат(ДатаМесяц(НачалоМесяца),"Ч(0)2")+Формат(ДатаЧисло(НачалоМесяца),"Ч(0)2");
    ВремДата=КонДата+1;
    ДатаКонца=""+ДатаГод(ВремДата)+Формат(ДатаМесяц(ВремДата),"Ч(0)2")+Формат(ДатаЧисло(ВремДата),"Ч(0)2");
    //формируем текст запроса
    ТекстЗапроса="SELECT Измерение1, Измерение2, SUM(Нач) AS Нач, SUM(Прих) AS Прих, SUM(Расх) AS Расх, SUM(Нач) + SUM(Прих) - SUM(Расх) AS Кон
    | FROM (SELECT "+Измерение1+" AS Измерение1, "+Измерение2+" AS Измерение2, "+РесурсКол+" AS Нач, 0 AS Прих, 0 AS Расх
    | FROM "+РегИтоги+" WHERE PERIOD = CONVERT(DATETIME, '"+ДатаОстатков+" 00:00:00', 102)
    |
    | UNION ALL
    |
    | SELECT "+РегДвижения+"."+Измерение1+" AS Измерение1, "+РегДвижения+"."+Измерение2+" AS Измерение2, "+РегДвижения+"."+РесурсКол+" * (1 - "+РегДвижения+".DEBKRED * 2) AS Нач, 0 AS Прих, 0 AS Расх
    | FROM "+РегДвижения+" INNER JOIN _1SJOURN ON "+РегДвижения+".IDDOC = _1SJOURN.IDDOC
    | WHERE (_1SJOURN.DATE_TIME_IDDOC >= '"+ДатаНачалаОст+"') AND (_1SJOURN.DATE_TIME_IDDOC < '"+ДатаНачала+"') AND (_1SJOURN."+ФлагДвижения+" = 1) AND
    | (_1SJOURN.CLOSED & 1 = 1)
    |
    | UNION ALL
    |
    | SELECT "+РегДвижения+"."+Измерение1+" AS Измерение1, "+РегДвижения+"."+Измерение2+" AS Измерение2, 0 AS Нач,
    | CASE WHEN "+РегДвижения+".DEBKRED = 0 THEN "+РегДвижения+"."+РесурсКол+" ELSE 0 END AS Прих,
    | CASE WHEN "+РегДвижения+".DEBKRED = 1 THEN "+РегДвижения+"."+РесурсКол+" ELSE 0 END AS Расх
    | FROM "+РегДвижения+" INNER JOIN _1SJOURN ON "+РегДвижения+".IDDOC = _1SJOURN.IDDOC
    | WHERE (_1SJOURN.DATE_TIME_IDDOC >= '"+ДатаНачала+"') AND (_1SJOURN.DATE_TIME_IDDOC < '"+ДатаКонца+"') AND (_1SJOURN."+ФлагДвижения+" = 1) AND
    | (_1SJOURN.CLOSED & 1 = 1)) TMP
    |GROUP BY TMP.Измерение1, TMP.Измерение2 WITH ROLLUP
    |ORDER BY TMP.Измерение1, TMP.Измерение2";
    //при желании можем выдать в строку сообщений текст запроса
    //Сообщить(ТекстЗапроса);
    Запрос.Открыть(ТекстЗапроса);
    //устанавливаем параметры для результирующей таблицы
    Запрос.УстТипыКолонок1С("Справочник.Магазины,Справочник.Товары,Число,Число,Число,Число");
    //получаем результаты
    ТЗ=СоздатьОбъект("ТаблицаЗначений");
    Запрос.ПолучитьРезультатыВ_ТЗ(ТЗ,1);
    Запрос.Закрыть();
    //теперь работа с таблицей значений
    ТЗ.Сортировать("Измерение1,Измерение2");
    ИтогоНач=0;
    ИтогоПрих=0;
    ИтогоРасх=0;
    ИтогоКон=0;
    //Если хотите посмотреть как выглядит таблица значений
    //раскомментируйте следующую строку!
    //ТЗ.ВыбратьСтроку();
    ТЗ.ВыбратьСтроки();
    Прошлый1="";
    //Подготавливаем и выводим таблицу
    Таб=СоздатьОбъект("Таблица");
    Таб.ИсходнаяТаблица("Таблица");
    Таб.ВывестиСекцию("Шапка");
    Пока ТЗ.ПолучитьСтроку()=1 Цикл
        Если ТЗ.НомерСтроки=1 Тогда
            //Это будет для секции "Итого"
            ИтогоНач=ТЗ.Нач;
            ИтогоПрих=ТЗ.Прих;
            ИтогоРасх=ТЗ.Расх;
            ИтогоКон=ТЗ.Кон;
        Иначе
            Если Прошлый1<>ТЗ.Измерение1 Тогда
                ТекАтрибут=ТЗ.Измерение1;
                Таб.ВывестиСекцию("1");
                Прошлый1=ТЗ.Измерение1;
            Иначе
                ТекАтрибут=ТЗ.Измерение2;
                Таб.ВывестиСекцию("2");
            КонецЕсли;
        КонецЕсли;
    КонецЦикла;
    Таб.ВывестиСекцию("Итого");
    Таб.Опции(0,0,0,0);
    Таб.ТолькоПросмотр(1);
    Таб.Показать();
КонецПроцедуры
//________________________________________________________
Процедура ПриОткрытии()
    //Загружаем 1С++
    Если ЗагрузитьВнешнююКомпоненту("1cpp.dll")=0 тогда
        Предупреждение ("Компонента 1с++ не найдена");
        СтатусВозврата(0);
    КонецЕсли;
КонецПроцедуры

Если сравнить время построения стандартного отчета средствами и прямого запроса, то прямой запрос быстрее в 3-5 раз. Речь идет о полном времени от начала нажатия на кнопку до отображения печатной формы.

Этот внешний отчет можно загрузить в разделе "Скачать".

 


 

Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").