Использование инструментария запросов

Как и любая СУБД, 1С:Предприятие (которая несомненно является таковой) поддерживает работу с хранимыми данными с использованием языка запросов (SQL). Запрос можно сформировать в любой процедуре (в любом модуле) как непосредственно на языке SQL, так и используя конструктор запросов[3]. Использование запросов позволяет осуществлять любые выборки, агрегирование данных, фильтры и т.д.

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

Прикладная задача: заполнение актов об оказании услуг на основании договоров с учетом выполненных работ

Возвращаемся к документу Акт об оказании услуг. Необходимо реализовать процедуру заполнения акта на основании договора (данная задача оговаривалась на стр. 7).

Постановка задачи:

· при заполнении акта, после выбора договора в шапке документа автоматически заполняются поля Контрагент, а также поля табличной части Спецификация Акта по услугам;

· при заполнении спецификации проверяется «остаток» по договору и в поле количество вносится не договорное, а «не выполненное» количество услуг;

· пользователь самостоятельно корректирует значение поля Количество. Если измененное пользователем значение превышает долг по договору, то выводится информационное сообщение.

 

Технология реализации заполнения спецификации акта по услугам:

Открыть форму документа Акт об оказании услуг. Вызвать свойства поля ввода Договор. Для события При изменении запустить создание процедуры.

Сформировать процедуру, включающую запрос. Текст процедуры с комментариями представлен ниже:

 

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

Комментарии:

1. Формирование запроса в режиме конструктора

Вид конструктора запрос представлен на Рис. 2.

1. Из окошка База данных в качестве источника выбираем регистр накопления ВыполнениеДоговоровОстаткиИОбороты. Это одно из представлений регистра накоплений ВыполнениеДоговоров, где автоматически рассчитываются показатели движения ресурсов (КоличествоПриход, КоличествоРасход, СуммаПриход, СуммаРасход) и их остатки ресурсов (КоличествоКонечныйОстаток, СуммаКонечныйОстаток).

2. Из регистра выбираем поля: Услуга, КоличествоКонечныйОстаток, СуммаКонечныйОстаток.

3. Для расчета цены по договору немного схитрим и рассчитаем ее как частное от деления суммы и количества (1).

4. На вкладке Условия добавить ограничения по договору (выборка идет по конкретному договору). См. Рис. 3.

5. На вкладке Объединения/Псевдонимы задать имена результирующих полей (см. текст запроса выше).

6. Нажать ОК, просмотреть текст запроса в модуле формы. Продолжить разработку процедуры.

 

Рис. 2 Конструктор запроса

 

 

Рис. 3 Добавление условий запроса

 

 

2. Таблица значений – виртуальная таблица, создаваемая на время выполнения процедуры и служащая для временного хранения данных. В рассматриваемом случае таблица значений включает 4 столбца, определенных в запросе. Записи таблицы соответствуют результату выборки (запросу). Имя таблицы задается в процедуре (ТаблЗнач – в рассматриваемом примере). Во встроенном языке существует набор методов для работы с таблицей значений. После выполнения процедуры таблица значений удаляется.

 

Технология реализации задачи контроля при корректировке значения поля

В форме документа Акт об оказании услу г вызвать свойства поля ввода Количество. Для события При изменении запустить создание процедуры.

Процедура СпецификацияАктаПоУслугамКоличествоПриИзменении(Элемент)
СТЧ=ЭлементыФормы.СпецификацияАктаПоУслугам.ТекущиеДанные;	Ссылку на текущую строку присваиваем переменной СТЧ
Запрос=Новый Запрос; Запрос.Текст= "ВЫБРАТЬ |ВыполнениеДоговораОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоОстаток |ИЗ |РегистрНакопления.ВыполнениеДоговора.ОстаткиИОбороты КАК ВыполнениеДоговораОстаткиИОбороты |ГДЕ |ВыполнениеДоговораОстаткиИОбороты.Договор.Ссылка = &Договор |И ВыполнениеДоговораОстаткиИОбороты.Услуга.Ссылка = &Услуга";	Формирование запроса (использовать конструктор) для выборки «остатка» конкретной услуги по договору, на основании которого сформирован акт.
Запрос.УстановитьПараметр("Договор",Договор); Запрос.УстановитьПараметр("Услуга",СТЧ.Услуга);	Установка условий на поля Договор и Услуга
ТаблЗнач=Запрос.Выполнить().Выгрузить();	Выгрузить результат в таблицу значений
Для каждого СтрокаТаблицыЗначений из ТаблЗнач цикл Если СТЧ.Количество>СтрокаТаблицыЗначений.КоличествоОстаток тогда Предупреждение("Вводимое значение превышает остаток по договору"); Иначе СТЧ.Сумма=СТЧ.Количество*СТЧ.Цена; СТЧ.НДС=СТЧ.Сумма*0.18; СТЧ.СуммаСНдс=СТЧ.Сумма+СТЧ.НДС; ИтогоПоУслугам=СпецификацияАктаПоУслугам.Итог("СуммаСНдс"); Итого=ИтогоПоМатериалам+ИтогоПоУслугам; КонецЕсли КонецЦикла	Организация цикла по строкам таблицы значений (обратить внимание – строка в этой таблице одна). Проверка изменяемого значения количества. При превышении значения над договорным – вывод предупреждения. Пересчет значений связанных с количеством полей.
КонецПроцедуры

 

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

 

Прикладная задача: расчет учетных цен (формирование записей регистра сведений УчетныеЦены)

Выше упоминалось о методах формирования учетных цен на материалы для нашей организации (см. стр. 8). Теперь пришло время рассмотреть сам механизм автоматизации расчета учетных цен (формирование записей в регистре сведений УчетныеЦены).

Еще раз обозначим, что в организации для определения фактической себестоимости материальных ресурсов и учетных цен применяется метод ЛИФО, то есть в качестве учетной цены (в несколько упрощенном варианте) используется цена материала по последней закупке.

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

Алгоритм решения задачи

 

 

Рис. 4 Алгоритм формирования записей регистра Учетные цены

 

Технология реализации

Создать форму списка регистра сведений УчетныеЦены. В форме разместить кнопку, вызвать окно свойств кнопки, задать имя – FIFO. Для события Нажатие запустить создание процедуры.

Сформировать процедуру, включающую запрос. Текст процедуры с комментариями представлен ниже:

Процедура FIFOНажатие(Элемент)
Запрос=Новый Запрос; Запрос.Текст=	Создание объекта запрос и вызов метода Текст для формирования текста запроса
Формирование запроса в режиме конструктора описано в комментариях после текста процедуры.
"ВЫБРАТЬ |МатериалДата.Материа.Ссылка КАК Материал, |МатериалДата.Цен КАК Цена |ИЗ |(ВЫБРАТЬ | МАКСИМУМ(ПоставкаМатериалов.Дата) КАК Дата, | ПоставкаМатериаловСпецификацияПоставки.Материал.Ссылка КАК Материал |ИЗ | Документ.ПоставкаМатериалов.СпецификацияПоставки КАК ПоставкаМатериаловСпецификацияПоставки |         ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоставкаМатериалов КАК ПоставкаМатериалов |         ПО ПоставкаМатериаловСпецификацияПоставки.Ссылка = ПоставкаМатериалов.Ссылка | |СГРУППИРОВАТЬ ПО | ПоставкаМатериаловСпецификацияПоставки.Материал.Ссылка) КАК ДатаМакс | ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ |         ПоставкаМатериалов.Дата КАК Дата, |         ПоставкаМатериаловСпецификацияПоставки.Материал.Ссылка КАК Материа, |         ПоставкаМатериаловСпецификацияПоставки.ЦенаПоставки КАК Цен | ИЗ |         Документ.ПоставкаМатериалов.СпецификацияПоставки КАК ПоставкаМатериаловСпецификацияПоставки |                    ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоставкаМатериалов КАК ПоставкаМатериалов |                    ПО ПоставкаМатериаловСпецификацияПоставки.Ссылка = ПоставкаМатериалов.Ссылка) КАК МатериалДата | ПО ДатаМакс.Дата = МатериалДата.Дата |         И ДатаМакс.Материал.Ссылка = МатериалДата.Материа.Ссылка";
Табл=Запрос.Выполнить().Выгрузить();	Выполнить запрос, выгрузить результат в таблицу значений Табл
Табл.Колонки.Вставить(1,"Период");	Добавить колонку в таблицу значений на первую позицию
Табл.ЗаполнитьЗначения(ТекущаяДата(), "Период");	Заполнить новую колонку значением текущей даты (даты формирования учетных цен)
Для каждого СтрокаТаблицыЗначений из Табл цикл	Организовать цикл по перебору строк таблицы значений
НоваяСтрока= РегистрыСведений.УчетныеЦены. СоздатьМенеджерЗаписи();	Создает объект «НоваяСтрока» для управления записью регистра сведений. По-другому говоря, создает новую запись в регистре сведений.
НоваяСтрока.Материал= СтрокаТаблицыЗначений. Материал; НоваяСтрока.Период= СтрокаТаблицыЗначений. Период; НоваяСтрока.ЦенаУчетная= СтрокаТаблицыЗначений. Цена;	Присваивает значения измерениям и ресурсам добавленной строки регистра сведений из обрабатываемой строки таблицы значений.
НоваяСтрока.Записать(); КонецЦикла	Записываем новую строку регистра сведений
КонецПроцедуры

После окончания запустить приложение, открыть регистр накопления УчетныеЦены, проверить формирование записей регистра.

 

Комментарии:

Для выборки требуемых сведений (см. Рис. 4) в качестве источников результирующего запроса необходимо сформировать два вложенных. Первый вложенный запрос (имя запроса ДатаМакс) сформирует список последних дат поставки по каждому материалу. Второй вложенный запрос (имя – МатериалДата) создаст список всех цен поставки по всем материалам с привязкой к дате поставки.

Разработка запроса ДатаМакс:

1. Для создания вложенного запроса в режиме конструктора запроса в окне Таблицы нажать кнопку Добавить. Откроется окошко вложенного запроса (см. Рис. 5).

2. Для вложенного запроса источником являются таблицы: ПоставкаМатериалов, СпецификацияПоставки. Поля выборки: МатериалСсылка (из спецификации поставки), дата поставки (из таблицы ПоставкаМатериалов).

3. Связь между таблицами устанавливается на вкладке Связи конструктора запроса по полю ПоставкаМатериалов.Ссылка (номер документа поставки).

4. На вкладке Группировка определить поле группировки - МатериалСсылка, суммируемое (точнее агрегируемое) поле – Дата поставки, функция агрегирования – максимум.

На вкладке Объединения/Псевдонимы задать имена полей выборки: Дата, Материал.

Рис. 5 Создание вложенного запроса

 

5. Просмотреть запрос (кнопка Запрос в левом нижнем углу окна конструктора).

6. Завершить создание вложенного запроса (кнопка ОК). В окне конструктора главного запроса, в разделе Таблицы появился новый объект – Вложенный запрос. Используя контекстное меню переименовать его в ДатаМакс.

 

Разработка вложенного запроса МатериалДата:

1. Создать второй вложенный запрос

2. Источники: ПоставкаМатериалов, СпецификацияПоставки. Поля выборки: МатериалСсылка (из спецификации поставки), дата поставки (из таблицы ПоставкаМатериалов), ЦенаПоставки.

3. Проверить связи между таблицами. Задать имена полей выборки.

4. Завершить создание вложенного запроса, присвоить новому запросу имя МатериалДата.

 

После завершения создания вложенных запросов окно основного запроса имеет вид:

Рис. 6 Вид основного запроса

Поля выборки основного запроса: МатериалДата.Материал.Ссылка, МатериалДата.Цена.

На вкладке Связи основного запроса установить связи между вложенными запросами по двум полям: Дата, Материал.

Переименовать поля выборки на вкладке Объединения/Псевдонимы.

Просмотреть итоговый текст запроса, завершить создание запроса, просмотреть текст запроса в процедуре.

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