Эффективное использование MSSQL с помощью ВК 1C++
Ключевые слова: 1C++, прямые запросы
Глава 1: Мой первый запрос
Для начала нужно научиться выполнять элементарные запросы к базе 1С непосредственно из самой программы.
Рассмотрим такой запрос:
Код = Справочник.Номенклатура.Наименование;
Наименование = Справочник.Номенклатура.Наименование;
ТекущийЭлемент = Справочник.Номенклатура.ТекущийЭлемент;
Группировка ТекущийЭлемент;
Как известно имена таблиц и полей не совпадают с теми идентификаторами, которые мы задаем в конфигураторе (соответствия можно посмотреть в файле 1Cv7.DDS):
Справочник.Номенклатура – таблица SC433 (в разных базах это может быть разным)
Код – Code, Наименование – Descr
Запрос на TSQL будет выглядеть следующим образом:
SELECT
СпрНом.Code as Код,
СпрНом.Descr as Наименование
FROM
sc433 as СпрНом
Его можно запустить в QA и увидеть полученный результат.
Теперь попробуем получить результат из 1С:
Для этого в 1С++ есть встроенный тип “ODBCRecordset”. Полный перечень методов и свойств можно увидеть в документации и в синтаксис-помощнике. Пока остановимся на методе ВыполнитьИнструкцию(Текст, ТЗ = "", ОчищатьТЗ = ""), которая возвращает результат работы запроса, переданного в параметре Текст в таблицу значений
RS = СоздатьОбъект("ODBCRecordset");
RS.УстБД1С();
ТекстЗапроса = "
|SELECT
| СпрНом.Code as Код,
| СпрНом.Descr as Наименование
|FROM
| sc433 as СпрНом";
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
ТЗ.ВыбратьСтроку();
|
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса =
"//{{ЗАПРОС(НаЯзыке1С)
|Код = Справочник.Номенклатура.Код;
|Наименование = Справочник.Номенклатура.Наименование;
|ТекущийЭлемент = Справочник.Номенклатура.ТекущийЭлемент;
|Группировка ТекущийЭлемент;
|"//}}ЗАПРОС
;
Запрос.Выполнить(ТекстЗапроса);
|
Вот и выполнился наш первый запрос. Это конечно хорошо, но справочников в конфигурации много, смотреть каждый раз в DDS ой как не хочется, да и читать такие запросы совсем не удобно. Если использовать ADO, а не 1С++, то так и придется делать. Но скажем спасибо разработчикам 1С++, что в ней есть метапарсер имен, который сам переведет все идентификаторы объектов в их реальные имена. А делается это так:
ТекстЗапроса = "
|SELECT
| СпрНом.Code as Код,
| СпрНом.Descr as Наименование
|FROM
| $Справочник.Номенклатура as СпрНом";
Заменяются следующие имена:
$Справочник.ХХХ – справочник
$Документ.ХХХ – документ
$ДокументСтроки.ХХХ – табличная часть документа
А также регистры, журналы расчетов (об этом поговорим позднее)
Немножко усложним пример. Выберем дополнительно реквизит “ТипНоменклатуры”
ТекстЗапроса = "
|SELECT
| СпрНом.Code as Код,
| СпрНом.Descr as Наименование,
| $СпрНом.ТипНоменклатуры as ТипНоменклатуры
|FROM
| $Справочник.Номенклатура as СпрНом";
|
ТекстЗапроса =
"//{{ЗАПРОС(НаЯзыке1С)
|Код = Справочник.Номенклатура.Код;
|Наименование = Справочник.Номенклатура.Наименование;
|ТипНоменклатуры = Справочник.Номенклатура.ТипНоменклатуры;
|ТекущийЭлемент = Справочник.Номенклатура.ТекущийЭлемент;
|Группировка ТекущийЭлемент;";
|
Замечание: Чтобы избежать коллизий, всегда пользуйтесь алиасами.
Как вы уже заметили, мы не стали искать соответствий имен в DDS, а переложили эту задачу на метапарсер. Для того чтобы парсер понял, что это реквизит нужно перевести на язык SQL ставим знак $ перед именем таблицы: $Спр.ТипНоменклатуры.
Правда, не все имена метапарсер преобразовывает в поля таблиц. Есть исключения – это предопределенные имена:
Для справочников
ИД (Ссылка) ID
Код Code
Наименование Descr
Родитель ParentID
Владелец ParentExt
ЭтоГруппа IsFolder
ПометкаУдаления IsMark
Для документов
ИД (Ссылка) IDDoc
ДатаДок Date_Time_IDDoc
НомерДок DocNo
ВидДок IDDocDef
Пример: Выберем непомеченные элементы справочника “Номенклатура”, которые не являются группами
ТекстЗапроса = "
|SELECT
| Спр.Code as Код,
| Спр.Descr as Наименование
|FROM
| $Справочник.Номенклатура as Спр
|WHERE
| Спр.IsFolder = 2 AND
| Спр.IsMark = 0";
|
ТекстЗапроса =
"//{{ЗАПРОС(НаЯзыке1С)
|Обрабатывать НеПомеченныеНаУдаление;
|Код = Справочник.Номенклатура.Код;
|ТекущийЭлемент = Справочник.Номенклатура.ТекущийЭлемент;
|Наименование = Справочник.Номенклатура.Наименование;
|ВидТовара = Справочник.Номенклатура.ВидТовара;
|Группировка ТекущийЭлемент без групп;";
|
Для поля IsMark: 1 – Помечен на удаление, 0 – Нет.
Для IsFolder: 2 – Элемент, 1 – Группа. Это сделано для того чтобы упорядочивание по этому полю сначала выдавало группы, а затем элементы.
Все конечно работает, но вместо типа номенклатуры получаются какие-то буковки вида ‘ C3A ‘. Это внутренние идентификаторы объектов 1С, как они хранятся в базе. Существует несколько способов получения объектов по их внутренним идам, например с помощью функции ЗначениеВСтрокуВнутр().
Но у нас в руках такая мощная вещь – она практически все умеет делать сама:
ТекстЗапроса = "
|SELECT
| Спр.Code as Код,
| Спр.Descr as Наименование,
| $Спр.ТипНоменклатуры as [ТипНоменклатуры $Перечисление.ТипНоменклатуры]
|FROM
| $Справочник.Номенклатура as Спр";
В результате мы получим Таблицу значений, в которой будет 3 колонки: Код, Наименование и ТипНоменклатуры. В последней колонке уже будут знакомые нам названия: Товар, Услуга и др.
Общий принцип таков:
- Имя колонки пишется в квадратных скобочках [] и состоит из 2х частей: собственно наименования и типа значения, разделенных пробелом
- Типы бывают следующие:
- $Справочник – справочник неопределенного вида
- $Справочник.ХХХ – конкретный справочник
- $Документ – документ неопределенного вида
- $Документ.ХХХ – конкретный документ
- $Перечисление.ХХХ
- $Счет.ХХХ – счет, где ХХХ – имя плана счетов
- $Субконто – специальный тип для бухгалтерской подсистемы
- $Неопределенный,
- Также есть типы $Число, $Строка, $Дата – но их можно не указывать
Замечание: Приводить нужно не к тому типу, который мы хотим получить, а к тому, который задан в конфигураторе
Глава 2: Условия в запросах
Основным преимуществом прямых запросов является то, что мы не будем выбирать не нужные нам данные и их отбор предоставим MSSQL, который делает это очень хорошо и главное на сервере, в отличие от 1С.
Как всегда рассмотрим примерчик:
Выбрать все элементы спр. Номенклатура, у которых реквизит ТипНоменклатуры = Перечисление.ТипыНоменклатуры.Товар
RS = СоздатьОбъект("ODBCRecordset");
RS.УстБД1С();
ТекстЗапроса = "
|SELECT
| Спр.ID as [Элемент $Справочник.Номенклатура]
|FROM
| $Справочник.Номенклатура as Спр
|WHERE
| $Спр.ТипНоменклатуры = :Товар";
RS.УстановитьТекстовыйПараметр("Товар", Перечисление.ТипыНоменклатуры.Товар);
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
ТЗ.ВыбратьСтроку();
Что бы посмотреть, какой же запрос в итоге уйдет на сервер у объекта ODBCRecordset существует метод Отладка(Вкл).
RS.Отладка(1);
ТЗ = RS.ВыполнитьИнструкцию(Текст);
В окно сообщений выведется текст
SELECT
Спр.ID as [Элемент $Справочник.Номенклатура]
FROM
sc433 as Спр
WHERE
Спр.SP3456 = ‘ C3A ‘
Такие запросы удобно отлаживать в Query Analyzer (стандартное средство Microsoft SQL, далее QA). Выделяем, копируем (не забывая перед этим включить русскую раскладку) и выполняем. В отличие от 1С++, QA показывает номер строки с ошибкой, к которой можно перейти двойным кликом.
Приведение параметров к типу колонки
Пример: Документ реализация, в котором Реквизит контрагент задан как тип справочник: Контрагенты или Сотрудники. Тогда значения этого реквизита в базе будут выглядеть примерно так: ‘ 1В C3A ‘. Естественно, если мы установим параметр ‘ C3A ‘, то выборка получится пустой. Для этого существует понятие модификаторов
Существует 2 вида модификаторов:
- Справочник.ХХХ > Справочник, аналогично для документов
- Какой-то тип > Неопределенный
Первый записывается так :ИмяПараметра~, второй :ИмяПараметра* или :ИмяПараметра~~
Добьем наш пример:
ТекстЗапроса = "
|SELECT
| Док.IDDoc as [Док $Документ.Реализация],
| $Док.Контрагент as [Контрагент $Справочник]
|FROM
| $Документ.Реализация as Док
|WHERE
| $Док.Контрагент = :ВыбКонтрагент~";
Отбор по пустому значению
Для этой цели существуют специальные переменные $ПустойИД = ‘ 0 ’ и $ПустойИД13 = ‘ 0 0 ’. В запросе это выглядит так
Пример: Пусть в документе реализация есть реквизит “Склад” типа “Справочник.Склады” . Выберем все документы, у которых пустой склад или контрагент
ТекстЗапроса = "
|SELECT
| Док.IDDoc as [Док $Документ.Реализация]
|FROM
| $Документ.Реализация as Док
|WHERE
| $Док.Контрагент = $ПустойИД13 OR
| $Док.Склад = $ПустойИД";
Отбор по списку или группе
Для таких случаев у объекта ODBCRecordset есть метод
УложитьСписокОбъектов(Список, Таблица, ВидСправочника) и
УложитьСписокОбъектов13(Список, Таблица)
Список – это список или группа. После выполнения этого метода появляется таблица с именем Таблица и колонками Val, IsFolder
Пример: Выберем все документы реализации, у которых склад входит в выбранную группу
ТекстЗапроса = "
|SELECT
| Док.IDDoc as [Док $Документ.Реализация]
|FROM
| $Документ.Реализация as Док
|WHERE
| $Док.Склад IN (SELECT Val FROM #Группа)";
RS.УложитьСписокОбъектов(ВыбГруппа, "#Группа", "Склады");
Замечание: Рекомендуется использовать локальные временные таблицы – префикс #. В этом случае не нужно заботиться об уникальности имени таблицы для разных пользователей, а также об их последующем удалении.
Тот же пример, но по списку контрагентов:
ТекстЗапроса = "
|SELECT
| Док.IDDoc as [Док $Документ.Реализация]
|FROM
| $Документ.Реализация as Док
|WHERE
| $Док.Контрагент IN (SELECT Val FROM #Группа)";
RS.УложитьСписокОбъектов13(Список, "#Группа");
Обычно при написании отчетов нужно делать переменные условия: по всем, по элементы, по группе или по списку. Отрабатывать все варианты в каждом отчете – проще застрелиться. И для того, чтобы так не делать мною был разработан класс обертка ЗапросSQL (можно найти на acsent.nm.ru). Суть это класса состоит в макроподстановках
Пример:
Запрос = СоздатьОбъект("ЗапросSQL");
Запрос.ДобавитьУсловие("$Док", "Склад", "", ВыбСклад, "");
Запрос.Текст = "
|SELECT
| Док.IDDoc as [Док $Документ.Реализация]
|FROM
| $Документ.Реализация as Док
|WHERE
| %Склад ";
ТЗ = Запрос.Выполнить();
ТЗ.ВыбратьСтроку();
В зависимости от значения ВыбСклад в запрос встанет условие:
0 = 0 (Пустое значение, по всем)
$Док.Склад = :Склад (Выбран элемент)
$Док.Склад IN (SELECT Val FROM #__Склад) (Выбрана группа или список)
Глава 3: Работа с документами
Все документы 1С хранятся в таблице _1SJourn. Конечно, это не очень хорошо, особенно когда блокируется вся таблица при проведении 1 документа, но мы не можем изменять структуры БД, поэтому будем довольствоваться тем, что есть
Структура таблицы
F=ROW_ID |Row ID |I |0 |0
F=IDJOURNAL |ID of Journal |I |0 |0
F=IDDOC |ID Document |C |9 |0
F=IDDOCDEF |ID Def Document |I |0 |0
F=APPCODE |App code |S |0 |0
F=DATE_TIME_IDDOC |Date+Time+IDDoc |C |23 |0
F=DNPREFIX |Prefix Document No |C |18 |0
F=DOCNO |Document No |C |10 |0
F=CLOSED |Flag document is clo |Y |0 |0
F=ISMARK |Doc is Marked for De |L |0 |0
F=ACTCNT |Action counter |I |0 |0
F=VERSTAMP |Version stamp |I |0 |0
F=RF32735 |Reg Action Flag |L |0 |0
F=SP12955 |(P)Автор |C |9 |0
F=SP31982 |(P)Фирма |C |9 |0
F=DS13520 |Flag document in seq |Y |0 |0
IDDoc – уникальный идентификатор документа
IDDocDef – вид
Date_Time_IDDoc – позиция документа, она же дата
Пример: Получить документы “Реализация” за период с НачДата по КонДата
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид
|FROM
| _1SJourn as Жур
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.IDDocDef = $ВидДокумента.Реализация";
RS.УстановитьТекстовыйПараметр("НачДата", НачДата);
RS.УстановитьТекстовыйПараметр("КонДата", КонДата);
Здесь стоит обратить внимание на 2 вещи:
1) Для типизации документа по полю IDDoc необходимо, чтобы в выборке присутствовало поле содержащее IDDocDef с именем <ИмяПоляIDDoc>_вид, в нашем случае это будет Док_вид. Для полей, которые содержат реквизит типа “Документ” вспомогательного поля не требуется.
2) Модификатор параметра КонДата. Он необходим для того, чтобы в выборку попали документы за последний день.
Поле Date_Time_IDDoc, отвечающее за дату содержит значения вида ‘20030731767WS0 1O6P ‘. Соответственно параметр НачДата будет выглядеть как ‘20030701’, а КонДата с модификатором - ‘20030731Z’
Посмотрим, как в запросе преобразовать поле Date_Time_IDDoc в дату документа
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид,
| CAST(LEFT(Жур.Date_Time_IDDoc, 8) as DateTime) as ДатаДок
|FROM
| _1SJourn Жур
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.IDDocDef = $ВидДокумента.Реализация”;
В реальной жизни обычно приходится выбирать только проведенные документы. Для этой цели в таблице _1SJourn есть поле Closed первый бит которого отвечает за проведенность документа.
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид,
| CAST(LEFT(Жур.Date_Time_IDDoc, 8) as DateTime) as ДатаДок
|FROM
| _1SJourn Жур
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.IDDocDef = $ВидДокумента.Реализация AND
| Жур.Closed & 1 = 1";
Дополним пример выбором контрагента
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид,
| CAST(LEFT(Жур.Date_Time_IDDoc, 8) as DateTime) as ДатаДок,
| $Док.Контрагент as [Контрагент $Справочник]
|FROM
| _1SJourn Жур
|INNER JOIN
| $Документ.Реализация as Док ON Док.IDDoc = Жур.IDDoc
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.IDDocDef = $ВидДокумента.Реализация AND
| Жур.Closed & 1 = 1";
В этом примере можно было и не делать отбор по виду документа, т.к. соединение с таблицей документов “Реализация” автоматически выполняет эту задачу, но для попадания в индекс условие все-таки осталось.
Более сложный пример:
Путь наш документ “Реализация” имеет табличную часть с колонками Товар – “Справочник.Номенклатура” и Количество.
Выберем все товары с количеством из всех проведенных документов за период по выбранному складу
ТекстЗапроса = "
|SELECT
| $ДокС.Товар as [Товар $Справочник.Товары],
| SUM($ДокС.Количество) as Количество
|FROM
| $ДокументСтроки.Реализация as ДокС
|INNER JOIN
| $Документ.Реализация as Док ON Док.IDDoc = ДокС.IDDoc AND
| $Док.Склад = :ВыбСклад
|INNER JOIN
| _1SJourn as Жур ON Жур.IDDoc = ДокС.IDDoc
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.Closed & 1 = 1
|GROUP BY
| $ДокС.Товар";
Совет: накладывайте условия на соединяемые таблицы в месте их присоединения. Это повышает читабельность кода, если это конечно не меняет суть запроса (проходит только для INNER JOIN)
Общие реквизиты
Если для общего реквизита стоит отбор, то этот реквизит будет находиться в таблице _1SJourn, иначе в таблице документа. Доступ к этому реквизиту осуществляется через метаимя $ОбщийРеквизит.ХХХ
Пример: с отбором
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид,
| Жур.$ОбщийРеквизит.Фирма as [Фирма $Справочник.Фирмы]
|FROM
| _1SJourn Жур
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.IDDocDef = $ВидДокумента.Реализация AND
| Жур.Closed & 1 = 1";
Без отбора, по 2м видам документов
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид,
| COALESCE(ДокР.$ОбщийРеквизит.Фирма, ДокП.$ОбщийРеквизит.Фирма) as
| [Фирма $Справочник.Фирмы]
|FROM
| _1SJourn Жур
|LEFT JOIN
| $Документ.Реализация as ДокР ON ДокР.IDDoc = Жур.IDDoc
|LEFT JOIN
| $Документ.Поступление as ДокП ON ДокП.IDDoc = Жур.IDDoc
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.Closed & 1 = 1";
Использование граф отбора
Графы отбора и подчиненные документы лежат в таблице _1SCRDOC. Вид графы в поле MDID, значение отбора или документа владельца в поле ParentVal
Пример: Выберем документы по графе отбора Контрагент
ТекстЗапроса = "
|SELECT
| Жур.IDDoc [Док $Документ],
| Жур.IDDocDef Док_вид
|FROM
| _1SJourn
|INNER JOIN
| _1SCRDOC Отбор ON Отбор.ChildID = Жур.IDDoc AND
| Отбор. MDID = $ГрафаОтбора.Контрагент AND
| Отбор.ParentVal = :ВыбКонтрагент* AND
| Отбор.Child_Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~";
Пример: Для данного документа выберем подчиненные за период
ТекстЗапроса = "
|SELECT
| Жур.IDDoc [Док $Документ],
| Жур.IDDocDef Док_вид
|FROM
| _1SJourn
|INNER JOIN
| _1SCRDOC Отбор ON Отбор.ChildID = Жур.IDDoc AND
| Отбор. MDID = 0 AND
| Отбор.ParentVal = :ВыбДок* AND
| Отбор.Child_Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~";
Глава 3: Получение представлений в запросе
Это первый принцип оптимизации: Получать в запросе всю необходимую (или как можно больше) информацию.
Если мы попросим 1С вывести на экран, например контрагента из типизированного поля, то мы конечно увидим его наименование, но при этом будет выполнен еще один запрос к серверу, который его получит. Стоит придерживаться следующего принципа: типизацию использовать только там, где без нее не обойтись: Проведение документа, расшифровка в отчете.
Пример: Получим Номер, дату и наименование склада в запросе
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ.Реализация],
| Жур.DocNo as НомерДок,
| CAST(LEFT(Жур.Date_Time_IDDoc, 8) as DateTime) as ДатаДок,
| $Док.Склад as [Склад $Справочник.Склады],
| СпрС.Descr as Склад_Наименование
|FROM
| _1SJourn Жур
|INNER JOIN
| $Документ.Реализация as Док ON Док.IDDoc = Жур.IDDoc
|INNER JOIN
| $Справочник.Склады as СпрС ON СпрС.ID = $Док.Склад
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.Closed & 1 = 1";
Поля Док, Склад пойдут в расшифровку, а остальные поля выведутся на экран
А что делать, если заранее не известно кокой справочник хранится в поле?
В общем случае ничего нельзя сделать. Но если мы заранее можем ограничить тип этого поля тогда решение можно найти. Например, в нашем случае поле “Контрагент” может быть только Контрагентом или Сотрудником.
Вот как будет выглядеть запрос
ТекстЗапроса = "
|SELECT
| Жур.IDDoc as [Док $Документ.Реализация],
| COALESCE(СпрК.Descr, СпрС.Descr) as Контрагент_Наименование
|FROM
| _1SJourn Жур
|INNER JOIN
| $Документ.Реализация as Док ON Док.IDDoc = Жур.IDDoc
|LEFT JOIN
| $Справочник.Контрагенты as СпрК ON
| $ВидСправочника36.Контрагенты + СпрК.ID = $Док.Контрагент
|LEFT JOIN
| $Справочник. Сотрудники as СпрС ON
| $ВидСправочника36.Сотрудники + СпрС.ID = $Док.Контрагент
|WHERE
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| Жур.Closed & 1 = 1";
Глава 4: Работа с регистрами
Как известно регистр остатков состоит из 2х таблиц: Итоги и Движения. В таблице итогов хранятся остатки на ТА и конец каждого месяца (или другой период, как установлено в Операции > Управление оперативными итогами > Периодичность сохранения остатков. Для больших регистров не рекомендуется уменьшать это значение). В таблице движений хранятся соответственно движения за весь период.
Для работы с этими таблицами в 1С++ для них есть свои имена
$Регистр.ХХХ – таблица движений регистра ХХХ
$РегистрИтоги.ХХХ – таблица итогов регистра ХХХ
Пример: Получим движения по регистру ОстаткиТоваров у документа Реализация
ТекстЗапроса = "
|SELECT
| $Рег.Склад as [Склад $Справочник.Склады],
| $Рег.Товар as [Товар $Справочник.Номенклатура],
| $Рег.Количество as Количество
|FROM
| $Регистр.ОстаткиТоваров as Рег
|WHERE
| Рег.IDDoc = :ВыбДок";
Получение документа из регистра
В зависимости от наличия флага БыстаяОбработкаДвижений (значение флага смотрите в разделе Оптимизация регистров) получается 2 способа
Способ 1: При наличии флага
ТекстЗапроса = "
|SELECT
| Рег.IDDoc as [Док $Документ],
| Рег.IDDocDef as Док_вид,
| $Рег.Склад as [Склад $Справочник.Склады],
| $Рег.Товар as [Товар $Справочник.Номенклатура],
| $Рег.Количество as Количество
|FROM
| $Регистр.ОстаткиТоваров as Рег
|WHERE
| Рег.IDDoc = :ВыбДок";
Способ 2: Если флаг не стоит
ТекстЗапроса = "
|SELECT
| Рег.IDDoc as [Док $Документ],
| Жур.IDDocDef as Док_вид,
| $Рег.Склад as [Склад $Справочник.Склады],
| $Рег.Товар as [Товар $Справочник.Номенклатура],
| $Рег.Количество as Количество
|FROM
| $Регистр.ОстаткиТоваров as Рег
|INNER JOIN
| _1Sjourn as Жур ON Жур.IDDoc = Рег.IDDoc
|WHERE
| Рег.IDDoc = :ВыбДок";
Как всегда, при типизации документа по полю IDDoc не забываем включать в выборку поле IDDocDef.
Виртуальные таблицы
Чтобы получить остаток на некоторую дату, допустим на середину месяца, нужно объединить два запроса: Итоги на конец предыдущего месяца и Обороты с начала месяца по выбранную дату. Чтобы облегчить нам работу были придуманы, так называемые, виртуальные таблицы (не путать с представлениями VIEW), которые являются простыми макроподстановками (хотя на самом деле не такими уж и простыми. С большой вероятностью, если вы сами будете их разворачивать, то у вас получится хуже, т.к. лучше уже просто уже некуда).
Существует несколько видов виртуальных таблиц
Остатки, ОстаткиОбороты, Обороты.
Первые 2 только для регистров остатков, 2 – для оборотного регистра.
Пример: Получим остатки по складу в разрезе товаров на дату
ТекстЗапроса = "
|SELECT
| Рег.Товар as [Товар $Справочник.Номенклатура],
| Рег.КоличествоОстаток as Количество
|FROM
| $РегистрОстатки.ОстаткиТоваров(:ВыбДата,,
| Склад = :ВыбСклад,
| (Товар), (Количество)) as Рег";
В этом примере мы получим остатки на начало ВыбДата. Если мы хотим на конец, то нужно указывать модификатор :ВыбДата~. Если вообще опустить параметр ВыбДата, то получатся остатки на ТА.
В модуле документа обычно необходимо получить остатки на документ. Делается это так:
ТекстЗапроса = “
|SELECT
| Рег.Товар as [Товар $Справочник.Номенклатура],
| Рег.КоличествоОстаток as Количество,
| Рег.СуммаОстаток as Сумма
|FROM
| $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,,
| Склад = :ВыбСклад,
| (Товар), (Сумма, Количество)) as Рег";
RS.УстановитьТекстовыйПараметр("ВыбДата",
СформироватьПозициюДокумента(ТекущийДокумент(), -1));
Замечание: Все фильтры нужно накладывать внутри ВТ. Нельзя накладывать фильтр по реквизитам регистра. Это также касается таблицы ОстаткиИОбороты, а для таблицы Обороты можно.
А что если нужно получить остатки отфильтрованные по типу номенклатуры. А для этого нужно использовать 2 параметр ВТ, который называется Соединение
ТекстЗапроса = "
|SELECT
| Рег.Товар as [Товар $Справочник.Номенклатура],
| Рег.КоличествоОстаток as Количество
|FROM
| $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,
| INNER JOIN $Справочник.Номенклатура СпрН ON
| СпрН.ID = Товар AND
| $СпрН.ТипНоменклатуры = :ВыбТип,
| Склад = :ВыбСклад,
| (Товар), (Количество)) as Рег”;
Таблица ОстаткиИОбороты похоже на таблицу Остатки, только выбирается начальная и конечная даты и периодичность
Без периодичности (за период)
ТекстЗапроса = "
|SELECT
| Рег.Товар as [Товар $Справочник.Номенклатура],
| Рег.КоличествоНачальныйОстаток as КоличествоНачОст,
| Рег.КоличествоПриход as КоличествоПриход,
| Рег.КоличествоРасход as КоличествоРасход,
| Рег.КоличествоКонечныйОстаток as КоличествоКонОст,
| Рег.СуммаНачальныйОстаток as СуммаНачОст,
| Рег.СуммаПриход as СуммаПриход,
| Рег.СуммаРасход as СуммаРасход,
| Рег.СуммаКонечныйОстаток as СуммаКонОст
|FROM
| $РегистрОстаткиОбороты.ОстаткиТоваров(:НачДата, :КонДата~,,,
| Склад = :ВыбСклад,
| (Товар), (Сумма, Количество)) as Рег";
С периодичностью
Период может быть: День, Неделя, Месяц, Квартал, Год
ТекстЗапроса = "
|SELECT
| Рег.Товар as [Товар $Справочник.Номенклатура],
| Рег.КоличествоНачальныйОстаток as КоличествоНачОст,
| Рег.КоличествоПриход as КоличествоПриход,
| Рег.КоличествоРасход as КоличествоРасход,
| Рег.КоличествоКонечныйОстаток as КоличествоКонОст,
| Рег.Период Период
|FROM
| $РегистрОстаткиОбороты.ОстаткиТоваров(:НачДата, :КонДата~, Месяц,,
| Склад = :ВыбСклад,
| (Товар), (Количество)) as Рег";
В данном случае в поле период будет начало каждого месяца (периода). Как всегда при указании периода первую дату ставим без модификатора, вторую с модификатором.
Аналогично работает ВТ Обороты, только поля называются <ИмяИзмерения>Оборот
Оптимизация регистров
Существует всего 3 способа.
1) Установка флага БыстраяОбработкаДвижений. Очень полезен при частых расчетах регистра задним числом, а также при снятии отчета за не полный период.
При установке этого флага в таблицу движений регистра добавляется поле Date_Time_IDDoc и IDDocDef, что убирает необходимость присоединения таблицы _1SJourn для определения даты.
2) Правильная расстановка измерений ресурса: Рассматриваем только те, по которым идет отбор. Сначала идет измерение с самым большим количеством значений, потом поменьше и в конце измерения по которым менее всего нужен отбор. Это связано с наличием одного индекса по всем измерениям.
Пример: Регистр.Партии: Склад, Товар, Партия, Фирма
Отбор по партии практически не нужен, поэтому правильно расположить измерения так: Товар, Склад, Фирма, Партия
3) Установка флага отбор движений у измерения
1 и 3 способы приводят к заметному увеличению индекса, поэтому нужно помнить о балансе записи и чтения.
Глава 5: Вывод остатков в форме списка
Для этой цели больше всего подходят параметризированные запросы.
Выглядит такой запрос так:
ТекстЗапроса = "
|SELECT
| SUM($Рег.Количество) as Количество
|FROM
| $РегистрИтоги.ОстаткиТоваров as Рег
|WHERE
| Рег.Period = {d’2005-01-01’} AND
| $Рег.Склад = ? AND
| $Рег.Товар = ?";
Суть состоит в том, что запрос компилируется только 1 раз, а в остальные разы меняются только параметры, помеченные знаком “?”. За счет этого достигается некоторый выигрыш в скорости.
Для того, чтобы воспользоваться этой хорошей штукой поступим следующим образом:
В модуле формы заведем переменную RS. В процедуре ПриОткрытии() проинициализируем ее:
Процедура ПриОткрытии()
RS = СоздатьОбъект("ODBCRecordset");
ТекстЗапроса = // … смотри выше
RS.Подготовить(ТекстЗапроса);
RS.ПостроитьПараметры();
RS.УстПараметр(1, ВыбСклад);
КонецПроцедуры
В качестве периода в запросе будем использовать начало текущего месяца.
Добавим текстовую колонку, в которой пропишем формулу
Функция ПолучитьОстаток()
RS.УстПараметр(2, ТекущийЭлемент());
Возврат RS.ВыполнитьСкалярный();
КонецФункции
Здесь нужно помнить, что запрос нужно писать такой, который вернет только одну позицию. Это можно всегда сделать с помощью функции SUM(). Опять же для того чтобы это быстро бегало необходимо наиболее точно попасть в индекс, т.е указать все поля участвующие в индексе. В данном случае это все измерения.
В этом примере мы использовали метод ВыполнитьСкалярный() который возвращает не таблицу, а единственное значение или структуры, если выбирается несколько колонок.
Вы конечно спросите: А почему не использовали ВТ Остатки? Да, это единственное место где ее применять не очень хорошо, т.к. там присутствует метод GROUP BY, что несколько снижает скорость работы.
Замечание: при использовании ВТ в параметризированных запросах нужно поступать немного не так. Ведь не известно во что он развернется. Поэтому делаем следующим образом:
ТекстЗапроса = "
|@Товар = ?
|SELECT
| Рег.КоличествоОстаток as Количество
|FROM
| $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,,
| Склад = :ВыбСклад AND Товар = @Товар,
| (Товар), (Количество)) as Рег";
Глава 6: Некоторые методы ODBCRecordset
РежимRPC(Вкл)
С этим методом запросы выполняются с помощью вызова удаленных процедур (RPC).
Например запрос
“SELECT Спр.Descr FROM sc433 WHERE SP345 = ‘ BE4 ’ ” превратиться в запрос
“sp_executesql N’SELECT Спр.Descr FROM sc433 WHERE SP345 =@ТипНом’,‘ BE4 ’ ”
Что практически равносильно использованию параметризированных запросов. Вывод: нужно всегда использовать РежимRPC(1), кроме тех случаев, когда вы вручную создаете и заполняете временные таблицы (баг MSSQL, приводящий к замедлению таких операций. Кстати сама 1С так делает всегда, поэтому при долгом проведении оно продвигается все медленнее и медленнее). К методу УложитьСписокОбъектов() это не относится.
ОбратныйРасчетОтТА(Вкл)
При расчете регистра на дату близкую к ТА, удобнее делать Остаток = НачОст + Оборот, а Остаток = ОстатокНаТА – Оборот, за что собственно и отвечает эта процедура.
Глава 6: Контроль остатков и партионный учет
Вот мы уже и добрались до оптимизации проведения документа. Обычно самыми тормозными местами при проведении являются контроль остатков и партионный учет. Да еще сюда приплетается ошибка MSSQL, поэтому наш вердикт – однозначно переделывать.
Пример: Выберем те позиции из документа, которых нет на остатке. Склад табличной части. Причем выберем только товары и остаток будем рассчитывать на документ. В этом примере учтем, что может быть несколько одинаковых товаров в одном документе
ТекстЗапроса = "
|SELECT
| $Док.Товар as [Товар $Справочник.Номенклатура],
| SUM($Док.Количество) as Количество,
| MIN(Рег.КоличествоОстаток) as КолОст
|FROM
| $ДокументСтроки." + Вид() + " as Док
|
|INNER JOIN
| $Справочник.Номенклатура as СпрН ON СпрН.ID = $ДокС.Номенклатура AND
| $СпрН.ТипНоменклатуры <> :Услуга
|
|LEFT JOIN
| $РегистрОстатки.Остатки("+?(ИтогиАктуальны()=1,",",":ДатаРасчета~,")+"
| INNER JOIN
| (SELECT DISTINCT
| $Д1.Товар as Товар,
| FROM
| $ДокументСтроки." + Вид() + " as Д1
| WHERE Д1.IDDOC = :ВыбДок) as Д ON
| Товар = Д.Товар
| (Товар), (Количество)) as Рег
| ON
| ($Док.Товар = Рег.Товар)
|WHERE
| Док.IDDOC = :ВыбДок AND
|
|GROUP BY
| $Док.Товар
|HAVING
| SUM($Док.Количество) > MIN(Рег.КоличествоОстаток)";
Пример: тот же пример, но склад в табличной части. Значение склада в ТЧ может быть пустым, тогда берем его из шапки, те. Просто передаем параметром.
ТекстЗапроса = "
|SELECT
| $Док.Товар as [Товар $Справочник.Номенклатура]
| CASE
| WHEN $Док.СкладВТЧ = $ПустойИД THEN :ВыбСклад
| ELSE $Док.СкладВТЧ END as [Склад $Справочник.Склады],
|
| SUM($Док.Количество) as Количество,
| MIN(Рег.КоличествоОстаток) as КолОст,
|
|FROM
| $ДокументСтроки." + Вид() + " as Док
|
|INNER JOIN
| $Справочник.Номенклатура as СпрН ON СпрН.ID = $ДокС.Номенклатура AND
| $СпрН.ТипНоменклатуры <> :Услуга
|
|LEFT JOIN
| $РегистрОстатки.Остатки("+?(ИтогиАктуальны()=1,",",":ДатаРасчета~,")+"
| INNER JOIN
| (SELECT DISTINCT
| $Д1.Товар as Товар,
| CASE
| WHEN $Док.СкладВТЧ = $ПустойИД THEN :ВыбСклад
| ELSE $Док.СкладВТЧ END as Склад
| FROM
| $ДокументСтроки." + Вид() + " as Д1
| WHERE Д1.IDDOC = :ВыбДок) as Д ON
| Товар = Д.Товар AND Склад = Д.Склад
| (Склад, Товар), (Количество)) as Рег
| ON
| ($Док.Товар = Рег.Товар) AND
| ((Рег.Склад = $Док.СкладВТЧ) OR
| (Рег.Склад = :ВыбСклад AND $Док.СкладВТЧ = $ПустойИД))
|WHERE
| Док.IDDOC = :ВыбДок AND
|
|GROUP BY
| CASE
| WHEN $Док.СкладВТЧ = $ПустойИД THEN :ВыбСклад
| ELSE $Док.СкладВТЧ END,
| $Док.Товар
|HAVING
| SUM($Док.Количество) > MIN(Рег.КоличествоОстаток) ";
Глава 7: Периодические реквизиты
Периодические реквизиты хранятся в файле _1SConst
Для получения их значений служит виртуальное значение
$ПоследнееЗначение.<ИмяСправочника >| Константа.<ИмяРеквизита |
ИмяКонстанты>(<ИдОбъекта>, <Дата>[, <Время>[, <ИДДокумента>]]), которое является коррелированным подзапросом (вложенный запрос, в котором используется значения основного).
Пример: Справочник.Номенклатура, подчиненный справочник Цены с периодической ценой.
ТекстЗапроса = "
|SELECT
| СпрН.Descr Наименование,
| $ПоследнееЗначение.Цены.Цена(СпрЦ.ID, :ВыбДата) Цена
|FROM
| $Справочник.Номенклатура СпрН
|LEFT JOIN
| $Справочник.Цены СпрЦ ON СпрЦ.ParentExt = СпрН.ID AND
| $СпрЦ.ТипЦен = :ТипЦен";
Глава 8: Разные примеры использования прямых запросов
Удаление дублирующихся значений в истории для справочника Номенклатура
Автор Quan.
Готовая обработка лежит здесь: http://itland.ru/forum/index.php?showtopic=13810
мСпр = Метаданные.Справочники("Номенклатура");
лМета = СоздатьОбъект("MetaDataWork");
СписокМета = СоздатьОбъект("ТаблицаЗначений");
СписокМета.НоваяКолонка("ID","Число");
Для ъ = 1 По мСпр.Реквизит() Цикл
Если мСпр.Реквизит(ъ).Периодический = 1 Тогда
СписокМета.НоваяСтрока();
СписокМета.ID = Число(лМета.ИДОбъекта(мСпр.Реквизит(ъ)));
КонецЕсли;
КонецЦикла;
лЗапрос = СоздатьОбъект("ODBCRecordSet");
лЗапрос.Выполнить("
|IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
|ID=OBJECT_ID('tempdb..#TempTab') AND sysstat & 0xf = 3 )
|DROP TABLE #TempTab);
лЗапрос.Выполнить("
|CREATE TABLE #TempTab (ID INT, PRIMARY KEY CLUSTERED (ID) )");
лЗапрос.Подготовить("Insert into #TempTab Values (?)");
лЗапрос.ВыполнитьSQL_ИзТЗ(СписокМета);
лЗапрос.Выполнить("delete from
|_1sconst
|where
|id in (select id from #TempTab)
|and docid = ' 0 '
|and value = (select top 1 value from _1sconst as ref
|where
|ref.id = _1sconst.id
|AND
|ref.date < _1sconst.date
|AND
|ref.objid = _1sconst.objid
|Order by ref.date desc, ref.time desc, ref.docid desc, ref.row_id desc)
|");
Сообщить("Удалено " + лЗапрос.СтрокОбработанно() + " записей");
Уменьшение размера журнала транзакций *.ldf
Для начала нужно перевести Recovery model в режим Simple.
ЕМ (Enterprise Manager) > Свойства базы > Options > Recovery model
Потом запусть скрипт в QA
BACKUP LOG <DBName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE (<DBName>_Log)
Восстановление БД из дампа в ручном режиме
Естественно, имена файлов и БД нужно заменить на свои
RESTORE FILELISTONLY
FROM
DISK = 'D:\Temp\prommebel_db_20050518.bak'
RESTORE DATABASE [PromMebel_b]
FROM
DISK = 'D:\Temp\prommebel_db_20050518.bak'
WITH
MOVE 'PromMebel_Data' TO 'D:\MSSQL\prommebel_b.mdf',
MOVE 'PromMebel_Log' TO 'D:\MSSQL\TranLog\prommebel_b.ldf',
REPLACE
P.S. Word'овая версия этой статьи
На форуме компоненты 1C++ есть Word'овая версия данной статьи,
дополненная различиями в запросах к DBF и SQL базам
Забирать тут: http://www.1cpp.ru/forumfiles/Attachments/mod_002.zip |