Краткие теоретические сведения. 2.1 Источники данных запросов
2.1 Источники данных запросов
Исходную информацию запрос получает из набора таблиц. Эти таблицы представляют данные реальных таблиц базы данных в удобном для анализа виде. Их можно разделить на две большие группы: реальные и виртуальные.
Реальные таблицы, в свою очередь, могут быть объектными (ссылочными) или не объектными (не ссылочными).
Рисунок 1 – Таблицы запроса
Отличительной особенностью реальных таблиц является то, что они содержат данные какой-либо одной реальной таблицы, хранящейся в базе данных. Например, реальной является таблица «Справочник.Клиенты», соответствующая справочнику «Клиенты», или таблица «РегистрНакопления.ОстаткиМатериалов», соответствующая регистру накопления «ОстаткиМатериалов».
Виртуальные таблицы формируются в основном из данных нескольких таблиц базы данных. «Например», виртуальной является таблица «РегистрНакопления.ОстаткиМатериалов.ОстаткиИОбороты», формируемая из нескольких таблиц регистра накопления «ОстаткиМатериалов».
Иногда виртуальные таблицы могут формироваться и из одной реальной таблицы (например, виртуальная таблица «Цены.СрезПоследних» формируется на основе таблицы регистра сведений «Цены»). Однако общим для всех виртуальных таблиц является то, что им можно задать ряд параметров, определяющих какие данные будут включены в эти виртуальные таблицы. Набор таких параметров может быть различным для разных виртуальных таблиц, и определяется данными, хранящимися в исходных таблицах базы данных.
Реальные таблицы подразделяются на объектные (ссылочные) и не объектные (не ссылочные).
В объектных (ссылочных) таблицах представлена информация ссылочных типов данных (справочники, документы, планы видов характеристик и т.д.). А в не объектных (не ссылочных) – всех остальных типов данных (константы, регистры и т.д.).
Отличительной особенностью объектных (ссылочных) таблиц является то, что они включают в себя поле «Ссылка», содержащее ссылку на текущую запись. Кроме этого для таких таблиц возможно получение пользовательского представления объекта. Эти таблицы могут быть иерархическими, и поля таких таблиц могут содержать вложенные таблицы (табличные части).
2.2 Язык запросов
Алгоритм, по которому данные будут выбраны из исходных таблиц запроса, описывается в тексте запроса на специальном языке – языке запросов. Текст запроса состоит из нескольких частей:
- описание запроса;
- объединение запросов;
- упорядочивание результатов;
- автоупорядочивание;
- описание итогов.
Обязательной частью запроса является только первая – описание запроса. Все остальные присутствуют по необходимости.
Описание запроса определяет источники данных, поля выборки, группировки и т.д.
Объединение запросов определяет, как будут объединены результаты выполнения нескольких запросов.
Упорядочивание результатов определяет условия упорядочивания строк результата запроса.
Автоупорядочивание позволяет включить режим автоматического упорядочивания строк результата запроса.
Описание итогов определяет, какие итоги необходимо рассчитывать в запросе и каким образом группировать результат.
Следует заметить, что в случае, когда язык запросов используется для описания источников данных в системе компоновки данных, секция описания итогов языка запросов не используется. Это связано с тем, что система компоновки данных самостоятельно рассчитывает итоги на основании тех настроек, которые сделаны разработчиком или пользователем.
2.3 Система компоновки данных
Система компоновки данных предназначена для создания произвольных отчетов в системе 1С:Предприятие 8.1 и состоит из нескольких основных частей.
Исходные данные для компоновки отчета содержит в себе схема компоновки данных – это наборы данных и методы работы с ними.
Рисунок 2 – Общая схема работы с системой компоновки данных
Разработчик создает схему компоновки данных, в которой описывает текст запроса, наборы данных, связи между ними, доступные поля, параметры получения данных и задает первоначальные настройки компоновки – структуру отчета, макет оформления данных и др.
Отчет системы компоновки данных, который получит пользователь, представляет собой не просто таблицу записей, удовлетворяющих запросу. Он имеет сложную иерархическую структуру и может состоять из различных элементов, таких как: группировки, таблицы и диаграммы. При этом пользователь может изменить существующую или вообще создать совершенно новую структуру отчета, настроить необходимый ему отбор, оформление элементов структуры отчета, получить расшифровку по каждому элементу и т.д.
В общем виде система компоновки данных представляет собой совокупность нескольких объектов. При формировании и исполнении отчета происходит последовательная передача данных от одного объекта системы компоновки данных к другому, до получения конечного результата – документа, показанного пользователю.
Алгоритм взаимодействия этих объектов выглядит следующим образом: разработчик создает схему компоновки данных и настройки по умолчанию. В общем случае на основе одной схемы компоновки данных может быть создано большое количество различных отчетов. Настройки компоновки данных – создаваемые разработчиком или изменяемые пользователем – определяют, какой именно отчет будет получен в конкретном случае.
На основе схемы компоновки и имеющихся настроек компоновщик макета создает макет компоновки данных. Это этап подготовки к исполнению отчета. Макет компоновки данных является уже готовым заданием для выполнения процессором компоновки. Макет компоновки содержит необходимые запросы, макеты областей отчета и др.
Процессор компоновки данных выбирает данные из информационной базы согласно макету компоновки, агрегирует и оформляет эти данные.
Результат компоновки обрабатывается процессором вывода, и в итоге пользователь получает результирующий табличный документ.
Рисунок 3 – Схема работы системы компоновки
Задание
3.1 Создать отчет «Реестр документов Оказание услуги», используя систему компоновки данных. Отчет должен выводить список существующих в базе данных документов ОказаниеУслуги в порядке их дат и номеров.
3.2 Создать отчет «Рейтинг услуг», который должен содержать информацию о том, выполнение каких услуг принесло наибольшую прибыль в указанном периоде.
Ход работы
4.1 Отчет «Реестр документов Оказание услуги»
Создать отчет «Реестр документов Оказание услуги», используя систему компоновки данных. Этот отчет будет выводить список существующих в базе данных документов «ОказаниеУслуги» в порядке их дат и номеров.
Создать в конфигураторе новый объект конфигурации «Отчет». Установить курсор на «Отчеты» в древе конфигурации, вызвать контекстное меню, в котором выбрать «Добавить». Присвоить ему имя «РеестрДокументовОказаниеУслуги». На закладке «Основные» нажать кнопку «Открыть схему компоновки данных». В открывшемся диалоговом окне конструктора макета нажать «Готово». В конструкторе схемы компоновки данных создать «Набор данных – Запрос», установив курсор на «Наборы данных» и в контекстном меню выбрать «Добавить набор данных - запрос» и запустить конструктор запроса с помощью кнопки «Конструктор запроса.
В качестве источника данных для запроса выбрать объектную (ссылочную) таблицу документов «ОказаниеУслуги». Из этой таблицы выбрать следующие поля :
- дата;
- номер;
- склад;
- мастер;
- клиент.
Рисунок 4 – Конструктор запроса для объектной таблицы «ОказаниеУслуги»
После этого перейти на вкладку «Порядок» и указать, что результат запроса должен быть сначала упорядочен по значению поля «Дата», а затем – по значению поля «ОказаниеУслуги.Ссылка».
Рисунок 5 – Упорядочивание результата запроса
Нажать «ОK» и рассмотреть, какой запрос сформировал конструктор запроса:
ВЫБРАТЬ
//поля выборки
ОказаниеУслуги.Дата КАК Дата,
ОказаниеУслуги.Номер,
ОказаниеУслуги.Склад,
ОказаниеУслуги.Мастер,
ОказаниеУслуги.Клиент
ИЗ
Документ.ОказаниеУслуги КАК ОказаниеУслуги
УПОРЯДОЧИТЬ ПО
//упорядочивание результатов
Дата,
ОказаниеУслуги.Ссылка
Описание запроса начинается с обязательного ключевого слова «ВЫБРАТЬ». Затем следует список полей выборки. В нем описываются поля, которые должны содержаться в результате запроса. Этот список может содержать как собственно поля, так и некоторые выражения, вычисляемые на основе значений полей.
После ключевого слова «ИЗ» указываются источники данных – исходные таблицы запроса, содержимое которых обрабатывается в запросе. В данном случае это объектная (ссылочная) таблица «Документ.ОказаниеУслуги». После ключевого слова «КАК» указывается псевдоним источника данных. В нашем случае это «ОказаниеУслуги». В дальнейшем к этому источнику данных можно будет обращаться в тексте запроса, используя псевдоним.
Поля выборки также могут иметь псевдонимы, по которым в дальнейшем в тексте запроса можно обращаться к этому полю. В нашем случае это псевдоним «Дата».
После части описания запроса в данном примере следует часть упорядочивания результатов.
Предложение «УПОРЯДОЧИТЬ ПО» позволяет сортировать строки в результате запроса. После этого ключевого предложения располагается выражение упорядочивания, которое в общем случае представляет собой перечисление полей (выражений) и порядка вывода. В нашем случае упорядочивание будет выполняться сначала по полю «Дата», а потом по полю «ОказаниеУслуги.Ссылка». В обоих случаях порядок сортировки будет по возрастанию (настроен по умолчанию).
Перейти на вкладку «Настройки» и добавить новую группировку в структуру отчета. Установить курсор на «Отчет» и с помощью контекстного меню вызвать «Новая группировка».
В окне выбора поля группировки просто нажать «ОK» (тем самым мы указывая, что в группировке будут выводиться детальные записи из информационной базы) и на вкладке «Выбранные поля» задать поля, которые будут выводиться в отчет:
- дата;
- номер;
- склад;
- мастер;
- клиент.
Рисунок 6 – Поля, выводимые в отчет
Теперь запустить 1С:Предприятие в режиме отладки и открыть отчет «РеестрДокументовОказаниеУслуги».
Рисунок 7 – Отчет «РеестрДокументовОказаниеУслуги»
4.2 Отчет «Рейтинг услуг»
Отчет «Рейтинг услуг» будет содержать информацию о том, выполнение каких услуг принесло наибольшую прибыль в указанном периоде. На примере отчета «Рейтинг услуг» проиллюстрируем, как отбирать данные в некотором периоде, как задавать параметры запроса и как использовать в запросе данные из нескольких таблиц и включать в результат запроса все данные одного из источников.
Создать новый объект конфигурации «Отчет». Назвать его «РейтингУслуг». На закладке «Основные» нажать кнопку «Открыть схему компоновки данных». В открывшемся диалоговом окне конструктора макета нажать «Готово».
В конструкторе схемы компоновки данных создать «Набор данных – запрос» и запустить конструктор запроса.
Выбрать объектную (ссылочную) таблицу справочника «Номенклатура» и виртуальную таблицу регистра накопления «Продажи.Обороты». Для того чтобы исключить неоднозначность имен в запросе, переименовать таблицу «Номенклатура» в «СпрНоменклатура» (контекстное меню правой кнопки мыши – «Переименовать таблицу»).
Затем выберать из таблиц поля «СпрНоменклатура.Ссылка» и «ПродажиОбороты.ВыручкаОборот.»
Рисунок 8 – Вкладка «Таблицы и поля»
Перейти на закладку «Связи». Конструктор уже создал связь между двумя выбранными таблицами – значение изменения регистра «Номенклатура» должно быть равно ссылке на элемент справочника «Номенклатура».
Единственное, что останется сделать, это сбросить флаг «Все» у таблицы регистра и установить его у таблицы справочника.
Рисунок 9 – Вкладка «Связи»
Установка флага «Все» у таблицы справочника будет означать, что из справочника будут выбраны все элементы и этим элементам будет поставлено в соответствие значение оборота выручки из регистра. Таким образом, в результате запроса будут присутствовать все услуги, и для некоторых из них будут указаны обороты выручки. Для тех услуг, которые не оказывались в выбранном периоде, не будет указано ничего.
Перейти на закладку «Условия» и задать условия выбора элементов из справочника «Номенклатура». При задании условий выбора будут использоваться параметры запроса. Первым условием должно быть то, что выбранный элемент не является группой (для этого следует переключиться в режим «Произвольное условие» – установить флаг «Произвольное»). Затем ввести в поле условие следующий текст
СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
Вторым условием должно быть то, что выбранный элемент является услугой (это – Простое условие).
Рисунок 10 – Условия запроса
В дальнейшем, перед выполнением запроса, соответствующее значение перечисления передастся в параметр «ВидНоменклатуры».
Перейдем на закладку «Объединения/Псевдонимы» и указать, что представление элемента справочника будет иметь псевдоним «Услуга», а поле регистра будет иметь псевдоним «Выручка».
Рисунок 11 – Объединения/Псевдонимы
Перейти на закладку «Порядок» и указать, что результат запроса должен быть отсортирован по убыванию значения поля «Выручка». Создание запроса закончено, нажать кнопку «ОK».
Рисунок 12 – Порядок сортировки результата запроса
Запрос, сформированный конструктором:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Услуга,
ПродажиОбороты.ВыручкаОборот КАК Выручка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты КАК ПродажиОбороты
ПО ПродажиОбороты.Номенклатура = СпрНоменклатура.Ссылка
ГДЕ
СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
И СпрНоменклатура.ВидНоменклатуры = &ВидНоменклатуры
УПОРЯДОЧИТЬ ПО
Выручка УБЫВ
Сначала, как обычно, идет часть описания запроса, и в ней есть новые конструкции.
При описании источников запроса (после ключевого слова ИЗ) использована возможность определения нескольких источников запроса.
В данном случае выбираются записи из двух источников: «СпрНоменклатура» и «ПродажиОбороты», причем ключевым предложением «ЛЕВОЕ СОЕДИНЕНИЕ … ПО» описан способ, которым будут скомбинированы между собой записи этих двух источников.
«ЛЕВОЕ СОЕДИНЕНИЕ» означает, что в результат запроса надо включить комбинации записей из обоих источников, которые соответствуют указанному после ключевого слова «ПО» условию. Кроме этого в результат запроса надо включить еще и записи из первого (указанного слева от слова «СОЕДИНЕНИЕ») источника, для которых не найдено соответствующих условию записей из второго источника.
В части описания запроса есть еще одна новая конструкция – задание условий отбора данных из исходных таблиц.
Условию отбора всегда предшествует ключевое слово «ГДЕ». После него описывается само условие. Следует обратить внимание, что поля исходных таблиц, на которые накладывается условие, могут и не входить в список выборки (как в данном случае). Кроме того, в условии использован параметр запроса «ВидНоменклатуры».
Перейти на вкладку «Ресурсы» и нажмем кнопку >>, чтобы конструктор выбрал все доступные ресурсы, по которым можно вычислять итоги. В нашем случае – это единственный ресурс «Выручка».
Рисунок 13 – Доступный ресурс для вычисления итогов
Перейти на закладку «Параметры».
На этой закладке имеются три параметра: «НачалоПериода», «КонецПериода» и «ВидНоменклатуры». Дело в том, что система компоновки данных самостоятельно анализирует текст запроса и помимо тех параметров, которые указаны в нем в явном виде («ВидНоменклатуры»), предоставляет возможность настроить и те параметры, которые в явном виде в запросе не указаны, но с большой долей вероятности могут быть использованы в отчете. Такими параметрами являются «НачалоПериода» и «КонецПериода». Это первые два параметра виртуальной таблицы «РегистрНакопления.Продажи.Обороты», которая использовалась в запросе, в левом соединении.
Первым параметром передается начало периода расчета итогов, вторым – конец периода. В результате исходная таблица будет содержать только обороты, рассчитанные в переданном периоде.
Для параметра «НачалоПериода» задать заголовок, который будет отображаться пользователю – «Дата начала».
Здесь всегда следует помнить, что если передать в качестве этих параметров дату, то дата содержит и время с точностью до секунды.
Заранее известно, что пользователя не будут интересовать результаты работы отчета в периодах, указанных с точностью до секунды. В этом случае следует учесть две особенности.
Во-первых, пользователя надо избавить от необходимости указывать время при вводе даты. Для этого мы изменим существующее описание типа для параметра «НачалоПериода». Дважды щелкнуть в ячейке «Доступные типы», соответствующей параметру «НачалоПериода», нажмем кнопку выбора (…) и в нижней части окна редактирования типа данных установить «Состав даты» в значение «Дата».
Рисунок 14 – Состав даты
Нажать «ОK».
Вторая особенность заключается в том, что по умолчанию время в дате установлено 00:00:00. Поэтому, если пользователь задаст период отчета с 01.03.2004 по 31.03.2004, итоги регистра будут рассчитаны с начала дня 01.03.2004 00:00:00 по начало дня 31.03.2004 00:00:00. Таким образом, данные за 31 число, отличные от начала дня, в расчет не войдут, что может показаться неправильным пользователю.
Для того чтобы исключить эту ситуацию, следует добавить еще один параметр, в который пользователь будет вводить дату окончания. Значение параметра «КонецПериода» будет рассчитываться автоматически таким образом, чтобы оно указывало на конец дня даты, введенной пользователем. Поэтому для параметра «КонецПериода» установить флаг «Ограничение доступности».
С помощью кнопки командной панели добавить новый параметр с именем «ДатаОкончания».
Для этого параметра платформа автоматически сформирует заголовок – «Дата окончания». Оставить его без изменений. Задать тип значения параметра – «Дата». При этом, как и для параметра «НачалоПериода», указать состав даты – «Дата».
Следует обратить внимание, что по умолчанию добавленный параметр доступен для пользователя (ограничение доступности в последней колонке снято).
Перейти к параметру «КонецПериода». Для него система установила флаг ограничения доступности.
Рисунок 15 – Вкладка «Параметры»
Чтобы задать формулу, по которой будет вычисляться значение параметра «КонецПериода», можно воспользоваться языком выражений системы компоновки данных. Он содержит функцию «КонецПериода()», которая позволяет получить дату, соответствующую концу какого-либо периода, например, указанного дня.
В ячейке Выражение задать для параметра «КонецПериода» следующее выражение
КонецПериода(&ДатаОкончания, "День")
И в заключение настроить параметр «ВидНоменклатуры». Поскольку отчет должен отображать выручку, полученную только от реализации услуг, значение параметра «ВидНоменклатуры» пользователь изменять не должен. Оно должно быть задано непосредственно в схеме компоновки как «Перечисление.ВидыНоменклатуры.Услуга». Флаг ограничения использования у параметра «ВидНоменклатуры» платформа установила по умолчанию, поэтому остается только указать нужное значение перечисления «ВидыНоменклатуры» в ячейке «Значение», соответствующей параметру «ВидНоменклатуры».
Рисунок 16 - Основная схема компоновки данных
На вкладке «Настройки» добавить группировку и снова не указывая поле группировки. На вкладке «Выбранные поля» указать поля «Услуга» и «Выручка».
Рисунок 17 – Структура отчета
В заключение перейти на вкладку «Другие настройки» и задать заголовок отчета – «РейтингУслуг».
Рисунок 18 – Установка заголовка
Запустить 1С:Предприятие в режиме отладки.
Запустить отчет «Рейтинг услуг», но перед тем, как нажать кнопку «Сформировать», открыть «Настройки» и на закладке «Параметры» данных задать период отчета с любой даты по текущую.
Рисунок 19 – Сформированный отчет «Рейтинг услуг»
Контрольные вопросы
5.1 Для чего предназначен объект встроенного языка Запрос?
5.2 В чем отличие между реальными, виртуальными и ссылочными таблицами?
5.3 Каковы основные синтаксические конструкции языка запросов?
5.4 Что такое левое соединение?
Содержание отчета
6.1 Название работы
6.2 Цель работы
6.3 Описание хода работы со снимками экрана
6.4 Ответы на контрольные вопросы
6.5 Выводы к работе
Список литературы
7.1 Радченко М. Г. 1С:Предприятие 8.1. Практическое пособие разработчика. Примеры и типовые приемы. – СПБ.: 1С-Паблишинг, 2008. – 512 с.
Практическая работа №11
Подсистема
Цель работы
1.1 Изучить принципы работы объекта конфигурации «Подсистемы»