Перейти на главную страничку сайта (список статей, файлы для скачивания)

ФОРУМ (здесь можно обсудить эту статью, а также любые проблемы программирования на различных макроязыках и в скриптовых средах)

Эффективное использование MSSQL в 1С при помощи ВК 1С++

Автор статьи - Ситников Анатолий.

Оригинал стати расположен по адресу http://acsent.nm.ru/others.htm.


Данная статья предполагает наличие базовых знаний по TSQL у читателя.


Содержание: 

Глава 1: Мой первый запрос
Глава 2: Условия в запросах
    Приведение параметров к типу колонки
    Отбор по пустому значению
    Отбор по списку или группе
Глава 3: Работа с документами
    Общие реквизиты
    Использование граф отбора
    Получение представлений в запросе
Глава 4: Работа с регистрами
    Получение документа из регистра
    Виртуальные таблицы
    Оптимизация регистров
Глава 5: Вывод остатков в форме списка
Глава 6: Некоторые методы ODBCRecordset
Глава 7: Контроль остатков и партионный учет
Глава 8: Периодические реквизиты
Глава 9: Получение итогов в запросе
Глава 10: Операции UPDATE и DELETE
Глава 11: Эмуляция OLAP кубов, как на T-SQL реализовать опции <Все> и <ВошедшиеВЗапрос>
Глава 12: Разные примеры использования прямых запросов

Глава 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.ВыполнитьИнструкцию(ТекстЗапроса);
ТЗ.ВыбратьСтроку();

Вот и выполнился наш первый запрос. Это конечно хорошо, но справочников в конфигурации много, смотреть каждый раз в DDS ой как не хочется, да и читать такие запросы совсем неудобно. Если использовать ADO, а не 1С++, то так и придется делать. Но скажем спасибо разработчикам 1С++, что в ней есть метапарсер имен, который сам переведет все идентификаторы объектов в их реальные имена. А делается это так: 

ТекстЗапроса = "
|SELECT
|    Спр.Code as Код,
|    Спр.Descr as Наименование
|FROM
|    $Справочник.Номенклатура as Спр";

Заменяются следующие имена:

Немножко усложним пример. Выберем дополнительно реквизит “ТипНоменклатуры”: 

ТекстЗапроса = "
|SELECT
|    Спр.Code as Код,
|    Спр.Descr as Наименование,
|    $Спр.ТипНоменклатуры as ТипНоменклатуры
|FROM
|    $Справочник.Номенклатура as Спр";

Замечание: чтобы избежать коллизий, всегда пользуйтесь алиасами.

Как вы уже заметили, мы не стали искать соответствий имен в DDS, а переложили эту задачу на метапарсер. Для того чтобы парсер понял, что это реквизит, который нужно перевести на язык SQL, ставим знак $ перед именем таблицы: $Спр.ТипНоменклатуры.

Правда, не все имена метапарсер преобразовывает в поля таблиц. Есть исключения – это предопределенные имена.

Для справочников:

Для документов:

Пример: выберем непомеченные элементы справочника “Номенклатура”, которые не являются группами. 

ТекстЗапроса = "
|SELECT
|    Спр.Code as Код,
|    Спр.Descr as Наименование,
|FROM
|    $Справочник.Номенклатура as Спр
|WHERE
|    Спр.IsFolder = 2 AND
|    Спр.IsMark = 0";

Для поля 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 '

Такие запросы удобно отлаживать в 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
|    %Склад ";
ТЗ = Запрос.Выполнить();
ТЗ.ВыбратьСтроку();

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

Глава 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

Пример: получить документы “Реализация” за период с НачДата по КонДата. 

ТекстЗапроса = "
|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 :КонДата~";

Получение представлений в запросе

Это первый принцип оптимизации: получать в запросе всю необходимую (или как можно больше) информацию.

Если мы попросим 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
|    $Рег.Количество 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С так делает всегда, поэтому при долгом проведении оно продвигается все медленнее и медленнее). К методу УложитьСписокОбъектов() это не относится. 

ОбратныйРасчетОтТА(Вкл)

При расчете регистра на дату, близкую к ТА, удобнее делать Остаток = НачОст + Оборот, а Остаток = ОстатокНаТА – Оборот, за что собственно и отвечает эта процедура.

Глава 7: Контроль остатков и партионный учет

Вот мы уже и добрались до оптимизации проведения документа. Обычно самыми тормозными местами при проведении являются контроль остатков и партионный учет. Да еще сюда приплетается ошибка 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(Рег.КоличествоОстаток) ";

Глава 8: Периодические реквизиты

Периодические реквизиты хранятся в файле _1SConst. Для получения их значений служит виртуальное значение $ПоследнееЗначение.<ИмяСправочника> | Константа.<ИмяРеквизита | ИмяКонстанты>(<ИдОбъекта>, <Дата>[, <Время>[, <ИДДокумента>]]), которое является коррелированным подзапросом (вложенный запрос, в котором используется значения основного).

Пример: Справочник.Номенклатура, подчиненный справочник Цены с периодической ценой. 

ТекстЗапроса = "
|SELECT  
|    СпрН.Descr Наименование,
|    $ПоследнееЗначение.Цены.Цена(СпрЦ.ID, :ВыбДата) Цена    
|FROM
|    $Справочник.Номенклатура СпрН
|LEFT JOIN
|    $Справочник.Цены СпрЦ ON СпрЦ.ParentExt = СпрН.ID AND
|                             $СпрЦ.ТипЦен = :ТипЦен";

Глава 9: Получение итогов в запросе

Итоги по группировкам можно получать прямо в запросе, используя конструкцию: 

GROUP BY ... WITH ROLLUP

Пример: запрос по остаткам, с итогами по складам, которые будут выводиться перед списком товаров (как в обычных запросах 1С). 

ТекстЗапроса = "
|SELECT
|    Выборка.Склад as [Склад $Справочник.Склады]
|    СпрС.Descr as Склад_Наименование,
|    Выборка.Товар as [Товар $Справочник.Номенклатура],
|    СпрН.Descr as Товар_Наименование
|    Выборка.ИтогПоСкладам as ИтогПоСкладам,
|    Выборка.ИтогПоТоварам as ИтогПоТоварам,
|    Выборка.Количество as Количество
|FROM
|(
|SELECT
|    Рег.Склад as Склад,
|    Рег.Товар as Товар,
|    GROUPING(Рег.Склад) as ИтогПоСкладам,
|    GROUPING(Рег.Товар) as ИтогПоТоварам,
|    SUM(Рег.КоличествоОстаток) as Количество
|FROM
|    $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,, 
|                               Склад = :ВыбСклад AND Товар = @Товар, 
|                               (Склад, Товар), (Количество)) as Рег
|GROUP BY
|    Рег.Склад, Рег.Товар WITH ROLLUP
|) as Выборка
|LEFT JOIN
|    $Справочник.Склады as СпрС ON СпрС.ID = Выборка.Склад 
|LEFT JOIN
|    $Справочник.Номенклатура as СпрН ON СпрН.ID = Выборка.Товар 
|ORDER BY
|    СпрС.Descr, СпрН.Descr, Выборка.ИтогПоСкладам DESC, Выборка.ИтогПоТоварам DESC    
|";

В результате получаем таблицу:


СкладТоварИтогПоСкладамИтогПоТоварамКоличество
  11220
Основной  1100
ОсновнойТовар1  60
ОсновнойТовар2  40
Вспомогательный  1120
ВспомогательныйТовар1  90
ВспомогательныйТовар2  30

Выведем эту таблицу на печать: 

ТЗ.ВыбратьСтроки();
Пока ТЗ.ПолучитьСтроку() = 1 Цикл
    Если (ТЗ.ИтогПоСкладам = 1) И (ТЗ.ИтогПоТоварам = 1) Тогда
        Таб.ВывестиСекцию("Итого");
    ИначеЕсли ТЗ.ИтогПоТовару = 1 Тогда
        Таб.ВывестиСекцию("Склад");
    Иначе
        Таб.ВывестиСекцию("Товар");
    КонецЕсли;
КонецЦикла;

Как всегда, поля Склад, Товар - в Расшифровку, Наименования на печать.

Пример: Получить итоги с разворотом по документам. Воспользуемся ВТ ОстаткиОбороты с периодичностью Документ. Для упрощения примера представления получать не будем. 

ТекстЗапроса = "
|SELECT
|    Рег.Склад as Склад,
|    Рег.Товар as Товар, 
|    RIGHT(Рег.ПозицияДокумента,9) [Док $Документ],
|    MAX(Рег.ВидДокумента) Док_вид,
|    SUM(Рег.КоличествоОстаток) as Количество
|FROM
|    $РегистрОстаткиОбороты.ОстаткиТоваров(:НачДата, КонДата, Документ,, 
|                               Склад = :ВыбСклад AND Товар = @Товар, 
|                               (Склад, Товар), (Количество)) as Рег
|GROUP BY
|    Рег.Склад, Рег.Товар Рег.ПозицияДокумента WITH ROLLUP

Обратите внимание: MAX(Рег.ВидДокумента). Для типизации $Документ, в выборке должно присутствовать поле Док_вид. Группировать по нему нельзя, т.к. используется конструкция ROLLUP (иначе мы получили бы дополнительные итоги по виду документа, которые нам совсем не нужны). Поэтому мы и делаем таким образом.

Глава 10: Операции UPDATE и DELETE

Пример: в регистр Остатки добавили реквизит Фирма. Необходимо заполнить это поле по общему реквизиту документа (возможно, только если по реквизиту установлен отбор; хотя конечно возможно и без установки отбора, но запрос получается гораздо больше и сложнее). 

ТекстЗапроса = "
|UPDATE
|    $Регистр.Остатки 
|SET
|    $Регистр.Остатки.Фирма = Жур.$ОбщийРеквизит.Фирма
|FROM
|    $Регистр.Остатки Рег
|INNER JOIN
|    _1SJourn Жур ON Жур.IDDoc = Рег.IDDoc";

Обратите внимание на предложение FROM. Только так можно указывать алиасы в предложении UPDATE.

После выполнения запроса необходимо пересчитать регистр. Это можно сделать с помощью ТиИ, но лучше воспользоваться обработкой: УстановкаТА, автор DmitrO. Брать здесь: http://metaprog.km.ru/secrprog1c/sql/apsetup_2_2.zip.

А теперь удалим что-нибудь: 

ТекстЗапроса = "
|DELETE
|    $Регистр.Остатки 
|FROM
|    $Регистр.Остатки Рег
|INNER JOIN
|    _1SJourn Жур ON Жур.IDDoc = Рег.IDDoc
|WHERE
|    Жур.$OбщийРеквизит.Фирма = :ВыбФирма";

Глава 11: Эмуляция OLAP кубов. Или о том, как на T-SQL реализовать опции <Все> и <ВошедшиеВЗапрос>

На основе статьи с сайта 1csql.ru: http://1csql.ru/materials/articles/sql/001.html. Вкратце: суть метода состоит в умножении таблиц со значениями группировок и присоединении таблицы со значениями функций.

Пример: Регистр ОстаткиТоваров, Измерения: Фирма, Склад, Товар. Необходимо получить остатки по товарам в разрезе складов по выбранной Фирме. 

ТекстЗапроса = "
|SELECT
|    РегТовар.Товар Товар,    
|    РегСклад.Склад Склад,    
|    РегКолво.Количество Количество
|FROM
|(
|SELECT DISTINCT
|    РегТовар.Товар Товар    
|FROM
|    $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,, 
|                                 Фирма = :ВыбФирма, 
|                                 (Товар), (Количество)) as РегТовар
|) as РегТовар,
|(
|SELECT DISTINCT
|    РегСклад.Склад Склад    
|FROM
|    $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,, 
|                                 Фирма = :ВыбФирма, 
|                                 (Склад), (Количество)) as РегСклад
|) as РегСклад
|LEFT JOIN
|(
|SELECT
|    РегКолво.КоличествоОстаток Количество,    
|    РегКолво.Товар Товар,    
|    РегКолво.Склад Склад    
|FROM
|    $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,, 
|                                 Фирма = :ВыбФирма, 
|                                 (Склад, Товар), (Количество)) as РегКолво
|) as РегКолво ON РегКолво.Склад = РегСклад.Склад AND
|                 РегКолво.Товар = РегТовар.Товар    
|";

Глава 12: Разные примеры использования прямых запросов

Удаление дублирующихся значений в истории для справочника Номенклатура.

Автор 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)
|");
Сообщить("Удалено " + лЗапрос.СтрокОбработанно() + " записей");

Поиск дублирующихся элементов. Выберем все элементы справочника Контрагенты, у которых совпадают ИНН. 

ТекстЗапроса = "
|SELECT
|    Спр.ID [Элемент $Справочник.Контрагенты],
|    $Спр.ИНН ИНН
|FROM
|    $Справочник.Контрагенты Спр
|WHERE
|    $Спр.ИНН IN
|    (SELECT
|         $Спр1.ИНН
|     FROM
|         $Справочник.Контрагенты Спр1
|     WHERE
|         $Спр1.ИНН <> ''
|     GROUP BY
|         $Спр1.ИНН
|     HAVING
|         COUNT(*) > 1)
|ORDER BY
|    $Спр.ИНН";

Уменьшение размера журнала транзакций *.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. Большое спасибо разработчикам 1С++, особенно Дмитрию Ощепкову aka DmitrO.

Перейти на главную страничку сайта (список статей, файлы для скачивания)

© 2007 http://www.script-coding.com При любом использовании материалов сайта обязательна ссылка на него как на источник информации, а также сохранение целостности и авторства материалов.