Консоль запросов EX (внешние данные)

15.09.21

Разработка - Инструментарий разработчика

Работа с внешними данными в консоли запросов (1С 7.7, 8.x, XBase, Excel, ADO).

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование Бесплатно
Консоль запросов EX.epf
.epf 109,96Kb ver:v1.03b
126
126 Скачать бесплатно
Сравнение данных.sel
.sel 11,16Kb
43
43 Скачать бесплатно

Тексты всех модулей открыты, автором не предполагается в дальнейших версиях изменять это обстоятельство. Тем не менее, рекомендуется проверить отсутствие ВРЕДОНОСНОГО и ШПИОНСКОГО кода техническими специалистами, штатными или с высоким уровнем доверия, в связи с работой с учетными данными (паролями).

Выполнение запроса будет сопровождено предварительным выполнением запросов, описанных как внешние и являющиеся подчиненными этого запроса в дереве запросов консоли.
Результаты выполнения внешних запросов будут помещены в менеджер временных таблиц запроса с именами, соответствующими именам внешних запросов в дереве консоли, становясь, таким образом, доступными для запроса.

Внешний запрос может быть выполнен отдельно, либо в составе запроса; в любом случае подключение будет внесено в пул подключений консоли, доступный всем запросам консоли.

В случае, если запросы обращаются к копиям баз или совпадающим по GUID данным баз 1С, возможна работа с типами данных, отличных от примитивных; остальные запросы будут работать только с примитивными типами, и извлекать данные следует в строковых представлениях.
Отличные от примитивных данные передаются по GUID, поэтому вместо данных источника будут отображены совпадающие по GUID. Поэтому, например, документ копии с одинаковым GUID с измененной датой, будет отображен с датой документа текущей базы.

Параметры запроса 7.7 следует вносить вручную, перед выполнением запроса 7.7 в тексте запроса имена параметров заменяются их значениями, поэтому имена параметров не должны совпадать с лексемами текста запроса.
Значения параметров следует указывать по шаблону: например, стандартный шаблон кода счета ##:#:#:#, поэтому счет 205 должен выглядеть: "20.5    ".

v1.01b
Добавлена функция GetEX, позволяющая обращаться к отдельным запросам файла запроса из приложений 1С:

Функция GetEX(ФайлSel = Неопределено, СтрокаSel = Неопределено, Параметры = Неопределено) Экспорт

Функция возвращает выборку из результата запроса, в случае ошибки – строку с описанием ошибки.
Если не указано имя файла в первом параметре, будет открыто окно основной формы в режиме диалога выбора, остальные параметры будут игнорированы.
Приложения 1С могут получать данные запросов, в т.ч. "внешних", компонентным использованием консоли:

Перем ОбработкаEX Экспорт;
...

ИмяФайла = d:\Консоль запросов EX.epf";
Параметры = Новый Структура; // часть параметров запроса может быть переопределена без повторных сохранений файла запроса
Параметры.Вставить("ДатаС", '20160101');
Параметры.Вставить("ДатаПо", '20161231');

Если ОбработкаEX = Неопределенно Тогда
	ОбработкаEX = ВнешниеОбработки.Создать(ИмяФайла);
КонецЕсли;
Результат = ОбработкаEX.GetEX(d:\Мои запросы.sel", "Анализ_счета_7_7", Параметры);
Если ТипЗнч(Результат) = Тип("Строка") Тогда
	Сообщить(Результат); // сообщение об ошибке извлечения данных
	Возврат;
КонецЕсли;
...

ОбработкаEX.Закрыть(); // Очистка пула подключений

Разработчик может хранить в двоичных данных как саму консоль, так и файл запросов.

Для пользователя модифицирована универсальная обработка ВыгрузкаЗагрузкаДанныхXML.epf.

При повторных обращениях к данным пул подключений может быть сохранен, с данными предыдущих авторизаций, это может существенно сократить длительность выполнения, для этого нужно сохранять созданный объект внешней обработки в глобальной переменной.

Обращаю Ваше внимание: вопрос обеспечения безопасности данных аутентификации открыт.
Если при определении типа запроса не указывать данные аутентификации, пароли в файлах *.sel не будут сохранены, предусматривая необходимость первичной аутентификации.
Предлагаю защищать паролями тексты модулей как внешней обработки, так и приведенных примеров, ограничивать права записи двоичных данных, хранящих файл консоли, для предотвращения подмены файла консоли.

v1.02b
Исправления в интерфейсе.

v1.03b
Добавлены новые типы внешних данных: Excel и ADO.
Excel
Решена задача об извлечении данных, не содержащих структуры, промежуточных итогов по группировкам.
Определена методика автоопределения многострочного заголовка таблицы, результаты автоопределения могут быть пересмотрены и сохранены.
ADO
При трудностях определения типа значения параметра DataTypeEnum предлагаю использовать следующее: измените текст запроса, исключив из инструкции SQL параметры (возможно, добавив фразу TOP 1) и выполните запрос. Как правило, параметры представляют собой ограничения на извлечение полей отдельных таблиц.
Выполнив запрос, провайдер предоставит коллекцию Fields, содержащую тип DataTypeEnum каждого поля.
Эту коллекцию можно отобразить: Тип запроса –> Структура. Используя эти данные, присвойте параметру соответствующий тип.
К области применения
Крылья для 1С 7.7

Представленный инструмент позволяет работать с данными 1С 7.7 средствами запросов SQL.
Обращаясь из 7.7 к 8.x, можно выполнять полноценные запросы SQL, и наоборот, из 8.x можно работать с данными 7.7, используя интерфейсные преимущества.
Сравнение таблиц.
При использовании инструкции ПОЛНОЕ СОЕДИНЕНИЕ (OUTER JOIN) можно сравнить остатки/обороты/результаты выполнения запроса к рабочей БД и копии БД, для РИБ сравнивать можно результат, сохраненный в Excel.
Средствами Excel можно сравнить две таблицы несколькими способами.
Сравнить таблицы Excel представленным инструментом имеет, в сущности, технологическое преимущество: 1С поддерживает OUTER JOIN, и пользовательское преимущество: чтобы сравнить что-нибудь, пользователю достаточно поменять в параметрах даты и, может быть, обновить файл Excel.
В прилагаемом файле запросов приводятся примеры запросов сравнения остатков и оборотов, в описаниях внешних типов источников установите Ваши настройки. Требуется следить за тем, чтобы параметры запросов совпадали, иначе можно столкнуться с ситуацией, когда сравниваются остатки различных баз на различные периоды.
Установив период, в котором остатки совпадают, можно приступать к сравнению оборотов с точностью до регистратора. Путем эксперимента обнаружено, что сравнение представлений в инструкции ПО (ON) не вызывает ошибки сравнения полей неограниченных типов, иначе сравнивать следует результаты запросов, выгруженных в Excel. Здесь также есть подводные камни: это ограничение количества строк. Столкнувшись с этим ограничением, выбирайте данные с меньшим диапазоном в случае оборотов по регистраторам, или с отборами по счетам/субконто/измерениям.

Привожу схематично решение автоматизации задачи периодического сравнения и дальнейшей синхронизации данных в базах с использованием Конвертации данных как пример использования GetEX:

Процедура Выгрузка() Экспорт
	//...
	ВыгрузитьМакет("Перенос_данных",				  Каталог + "Перенос данных.sel");
	ВыгрузитьМакет("V8Exchan82",					  Каталог + "V8Exchan82.epf");
	ВыгрузитьМакет("ПравилаОбмена_УТП",				  Каталог + "ПравилаОбмена.xml");
	ВыгрузитьМакет("КонсольЗапросовEX",				  Каталог + "Консоль запросов EX.epf");

	Обработка = Каталог + "V8Exchan82.epf";
	Обработка = ВнешниеОбработки.Создать(Обработка);
	Обработка.ИмяФайлаПравилОбмена					= Каталог + "ПравилаОбмена.xml";
	Обработка.ИмяФайлаОбмена						= Каталог + "ФайлДанных.xml";
	Обработка.ЗагрузитьПравилаОбмена();
	Обработка.ИмяФайлаПротоколаОбмена				= Каталог + "Протокол выгрузки.txt";
	Обработка.ДописыватьДанныеВПротоколОбмена		= Истина;
	Обработка.ДатаНачала							= ДатаНач;
	Обработка.ДатаОкончания							= ДатаКон;
	Обработка.ИспользоватьОтборПоДатеДляВсехОбъектов= Истина;
	УстановитьТаблицыПравилВыгрузки(Обработка.ТаблицаПравилВыгрузки, Обработка.ДатаНачала, Обработка.ДатаОкончания);
	Обработка.ВыполнитьВыгрузку();
	//...
КонецПроцедуры

Процедура Загрузка() Экспорт
	//...
	ВыгрузитьМакет("V8Exchan82",						  Каталог + "V8Exchan82.epf");
	Обработка = Каталог + "V8Exchan82.epf";
	Обработка = ВнешниеОбработки.Создать(Обработка);
	Обработка.ИмяФайлаОбмена							= Каталог + "ФайлДанных.xml";
	Обработка.ИмяФайлаПротоколаОбмена					= Каталог + "Протокол загрузки.txt";
	Обработка.ДописыватьДанныеВПротоколОбмена			= Истина;
	Обработка.РежимОбмена								= "Загрузка";
	Обработка.ЗагружатьДанныеВРежимеОбмена				= Истина;
	Обработка.ВыполнитьЗагрузку();
	//...	
КонецПроцедуры

Процедура ВыгрузитьМакет(ИмяМакета, ИмяФайла)
	ПолучитьМакет(ИмяМакета).Записать(ИмяФайла); // см. https://forum.infostart.ru/forum15/topic228623/
КонецПроцедуры

Процедура УстановитьТаблицыПравилВыгрузки(ТаблицаПравилВыгрузки, ДатаНачала, ДатаОкончания)
	Для Каждого Строка Из ТаблицаПравилВыгрузки.Строки Цикл
		Если Не Строка.Имя = "Документы" Тогда
			Строка.Включить = 0;
		КонецЕсли;
		Для Каждого Подстрока Из Строка.Строки Цикл
			Построитель = Новый ПостроительОтчета;
			Если Подстрока.Имя = "ОтчетОРозничныхПродажах" Тогда
				Построитель.Текст = "ВЫБРАТЬ Разрешенные _.* ИЗ Документ.ОтчетОРозничныхПродажах КАК _ {ГДЕ _.Ссылка.* КАК Документ_ОтчетОРозничныхПродажах}";
				
				ЭлементОтбора = Построитель.Отбор.Добавить("Документ_ОтчетОРозничныхПродажах.Проведен");
				ЭлементОтбора.Использование = Истина;
				ЭлементОтбора.ВидСравнения = ВидСравнения.Равно;
				ЭлементОтбора.Значение = Истина;
				
				ЭлементОтбора = Построитель.Отбор.Добавить("Документ_ОтчетОРозничныхПродажах.Номер");
				ЭлементОтбора.Использование = Истина;
				ЭлементОтбора.ВидСравнения = ВидСравнения.ВСписке;
				Список = Новый СписокЗначений;
				Список.ЗагрузитьЗначения(ПолучитьМассивДокументов("Документы_ОРП", ДатаНачала, ДатаОкончания));
				ЭлементОтбора.Значение = Список;
			Иначе
				Подстрока.Включить = 0;
				Продолжить;
			КонецЕсли;
			Подстрока.НастройкиПостроителя = Построитель.ПолучитьНастройки();
			Подстрока.ИспользоватьОтбор = Истина;
		КонецЦикла;
	КонецЦикла;
КонецПроцедуры

Функция ПолучитьМассивДокументов(ТипДокументов, ДатаНач, ДатаКон)
	
	ИмяФайлаEX = Каталог + "Консоль запросов EX.epf";
	Параметры = Новый Структура; // часть параметров запроса может быть переопределена без повторных сохранений файла запроса
	Параметры.Вставить("ДатаНач", ДатаНач);
	Параметры.Вставить("ДатаКон", ДатаКон);

	Если ОбработкаEX = Неопределено Тогда
		ОбработкаEX = ВнешниеОбработки.Создать(ИмяФайлаEX);
	КонецЕсли;
	Результат = ОбработкаEX.GetEX(Каталог + "Перенос данных.sel", ТипДокументов, Параметры);
	Если ТипЗнч(Результат) = Тип("Строка") Тогда
		Возврат Новый Массив; // ошибка извлечения данных
	Иначе
		Возврат Результат.ВыгрузитьКолонку("Р_Номер")
	КонецЕсли;
	ОбработкаEX.Закрыть(); // Очистка пула подключений
КонецФункции

v1.04
Для внешних источников обработка формировала таблицу значений и помещала ее во временную таблицу (ВТ) запроса, при выводе запрос извлекал ВТ; при этом для запросов, содержащих в качестве подчиненных строки дерева запросов, становились доступными ВТ, сформированные на уровне подчиненных строк.
Данные различных источников, в т.ч. различных БД/серверов, оказывались доступными для работы на уровне одного запроса (иначе говоря, в менеджере ВТ оказывались все ВТ запросов подчиненных строк TreeTempTablesManager). Для запроса, результат которого выводился, данные извлекались из ВТ: происходила промежуточная выгрузка/загрузка ВТ. Для больших объемов были отмечены нарушения сортировки: источник возвращал результат с правильной сортировкой, он помещался в ВТ и далее на стороне обработки извлекался без инструкций ORDER BY, с возможными нарушениями сортировки (TempDB, как другие, отрабатывает по https://habr.com/ru/company/otus/blog/504144/ (Почему SQL Server не гарантирует сортировку результатов без ORDER BY)).
Для полноты описания в обработке 1С с базой на SQL создадим заведомо упорядоченную таблицу значений (ТЗ), поместим ее в ВТ и извлечем - у меня проблемы сортировки начались на первом десятке тысяч строк:

	ВТ = Новый ТаблицаЗначений();	
	КвалификаторТипа = Новый КвалификаторыЧисла(10000, 0, ДопустимыйЗнак.Любой);
	ОписаниеТипов = Новый ОписаниеТипов("Число", КвалификаторТипа);
	ВТ.Колонки.Добавить("Номер", ОписаниеТипов);

	КвалификаторТипа = Новый КвалификаторыСтроки(10);
	ОписаниеТипов = Новый ОписаниеТипов("Строка", , КвалификаторТипа);
	ВТ.Колонки.Добавить("Имя", ОписаниеТипов);
	
	Для Счетчик = 1 По 10000 Цикл
		СтрокаВТ = ВТ.Добавить();
		СтрокаВТ.Номер = Счетчик;
		СтрокаВТ.Имя = " " + Формат(Счетчик, "ЧГ=") + "+";
	КонецЦикла;
	ОбъектЗапрос = Новый Запрос;
	Менеджер = Новый МенеджерВременныхТаблиц;
	ОбъектЗапрос.МенеджерВременныхТаблиц = Менеджер;
	ОбъектЗапрос.Текст = "ВЫБРАТЬ * ПОМЕСТИТЬ ТаблицаВТ ИЗ &ВТ КАК ТаблицаВТ";
	ОбъектЗапрос.УстановитьПараметр("ВТ", ВТ);
	ОбъектЗапрос.Выполнить();
	ОбъектЗапрос.Текст = "ВЫБРАТЬ * ИЗ ТаблицаВТ";
	//ОбъектЗапрос.Текст = "ВЫБРАТЬ * ИЗ ТаблицаВТ УПОРЯДОЧИТЬ ПО Номер";
	Результат = ОбъектЗапрос.Выполнить().Выгрузить();
	ЭлементыФормы.Результат.СоздатьКолонки();

Эта проблема решена заменой загрузки из ВТ на прямую загрузку результатом - ТЗ, полученной из внешнего источника, выполнение TreeTempTablesManager не изменилось.
Для ADODB (в частности, для прямых запросов 1С++) техника TreeTempTablesManager может быть излишней и заменена использованием ВТ SQL:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmpAccs') IS NOT NULL BEGIN DROP TABLE #tmpAccs; END

SELECT *
INTO #tmpAccs
FROM _1SACCS AS БухСчета;

SELECT *
FROM #tmpAccs

Основным преимуществом этой консоли запросов вижу в технике TreeTempTablesManager - это возможность сопоставлять данные различных БД на этапе разработки запроса.

См. также

SALE! %

Инструментарий разработчика Роли и права Запросы СКД Программист Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

12000 10000 руб.

02.09.2020    161272    893    399    

872

Инструментарий разработчика Чистка данных Свертка базы Инструменты администратора БД Системный администратор Программист Руководитель проекта Платформа 1С v8.3 Конфигурации 1cv8 1С:Управление торговлей 10 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 Россия Платные (руб)

Инструмент представляет собой обработку для проведения свёртки или обрезки баз данных. Работает на ЛЮБЫХ конфигурациях (УТ, БП, ERP и т.д.). Поддерживаются управляемые и обычные формы. Может выполнять свертку сразу нескольких баз данных и выполнять их автоматически без непосредственного участия пользователя.

8400 руб.

20.08.2024    8566    66    29    

76

Пакетная печать Печатные формы Инструментарий разработчика Программист Платформа 1С v8.3 Запросы 1С:Зарплата и кадры бюджетного учреждения 1С:Конвертация данных 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 Платные (руб)

Инструмент, позволяющий абсолютно по-новому взглянуть на процесс разработки печатных форм. Благодаря конструктору можно значительно снизить затраты времени на разработку печатных форм, повысить качество и "прозрачность" разработки, а также навести порядок в многообразии корпоративных печатных форм.

22200 руб.

06.10.2023    15682    39    8    

73

Инструментарий разработчика Программист Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

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

9360 руб.

17.05.2024    23840    70    45    

119

SALE! %

Инструменты администратора БД Инструментарий разработчика Роли и права Программист Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

Расширение позволяет без изменения кода конфигурации выполнять проверки при вводе данных, скрывать от пользователя недоступные ему данные, выполнять код в обработчиках. Не изменяет данные конфигурации, легко устанавливается практически на любую конфигурацию на управляемых формах.

15000 10000 руб.

10.11.2023    10664    38    27    

63

SALE! %

Инструментарий разработчика Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

Универсальный инструмент программиста для администрирования конфигураций. Сборник наиболее часто используемых обработок под единым интерфейсом.

4800 3840 руб.

14.01.2013    188493    1142    0    

913

SALE! %

Инструментарий разработчика Программист 8.3.14 1С:Конвертация данных Россия Платные (руб)

Расширение для конфигурации “Конвертация данных 3”. Добавляет подсветку синтаксиса, детальную контекстную подсказку, глобальный поиск по коду.

20000 15000 руб.

07.10.2021    17556    6    32    

42

Инструментарий разработчика Программист Платные (руб)

Менеджер конфигураций 1С — альтернативный стартер информационных баз 1С:Предприятие.

1800 руб.

21.02.2023    7829    8    35    

23
Оставьте свое сообщение