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

Автор: Ситников Анатолий aka acsent
P.S. Большое спасибо разработчикам 1С++,
особенно Дмитрию Ощепкову aka DmitrO
Адрес проекта: http://www.1cpp.ru


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

Содержание

Введение
Глава 1: Мой первый запрос 1

Глава 2: Условия в запросах. 4

Приведение параметров к типу колонки. 4

Отбор по пустому значению. 5

Отбор по списку или группе. 5

Глава 3: Работа с документами. 6

Общие реквизиты. 9

Использование граф отбора. 10

Глава 3: Получение представлений в запросе. 11

Глава 4: Работа с регистрами. 12

Получение документа из регистра. 12

Виртуальные таблицы. 13

Оптимизация регистров. 16

Глава 5: Вывод остатков в форме списка. 16

Глава 6: Некоторые методы ODBCRecordset 17

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

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

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

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

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

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

Введение

        Эта копмонента, разработанная группой разработчиков и любезно предосталвенная всем для использования на свой страх и риск (т.е. бесплатно). Компонента позволяет делать то, что не может делать стандартная 1С, в компоненту включены несколько классов, каждый для решения той или иной фичы, которой в 1С V7.7 или вобще нет или же работет медленно и требует для реализации очень много ресурсов (временых и процессорных). Но основное достоинтсво этой компоненты - это реализация объектно-ориентированного программирования в 1С, а также классы определяемые пользователем - КОП, т.е. можно использовать свои классы с их методами а атибутами.
        Пока рассмотрим только классы, позволяющие выполнять прямой доступ к базе данных 1С.

1. Где скачать и как подключить 1С++

        Скачать можно здесь:
        http://www.1cpp.ru/files
Рекомендуется пользоватся или последним стабильным релизом или же последним релизом. Также есть возможность воспользоваться обновленным релизом (ночной сборкой).

2. Как подключить 1С++

    1С++ поддерживает работу в режиме внешней компоненты для 1С, поэтому для загрузки достаточно в модуле написать:  ЗагрузитьВнешнююКомпоненту("1CPP.dll")
    Рекомендуется в глобальном модуле определить переменные для доступа к классам 1С++ (с помощью которых реализуется прямой доступ к базе данных). Вот пример глобального модуля:

Перем глMDW Экспорт, глMetaInfo Экспорт,    глБазаSQL Экспорт;
Перем глDataBase Экспорт, глODBCRecordset Экспорт;
...

Процедура ПриНачалеРаботыСистемы()
    Если ЗагрузитьВнешнююКомпоненту("1CPP.dll") = 0 Тогда
        Сообщить("Не найдена внешняя компонента 1С++","!!!");
    Иначе
        глMDW = СоздатьОбъект("MetaDataWork");
        глMetaInfo = СоздатьОбъект("MetaInfoClasses");
        глБазаSQL = глMetaInfo.ЭтоSQL_Версия();
        глDataBase = СоздатьОбъект("ODBCDatabase");
        глODBCRecordset = СоздатьОбъект("ODBCRecordSet");
    КонецЕсли; 
КонецПроцедуры


 3. Что такое прямой доступ и как он работает

        Прямой доступ подразумевает доступ к базе не через механизмы 1С, а напрямую. Например просмотр данных напрямую можно сравнить с просмотром какой то таблицы 1С (например 1SJOURN) средствами любого просмотрщика dbf файлов формата dbase3 или више.
        Прямой доступ в компоненте 1С++ организован посредством использования технологий ODBC и OLE DB. Сама 1С V 7.7 использует ODBC для работы с базой в формате SQL, длябазы в формате dbf (файловая версия) 1С V 7.7 использует внутренний механизм. Но поскольку 1С работает с файлами dbf в формет dbase3 - то для доступа к таким файлам подойдет любой провайдер ODBC или OLE DB, поддерживающий работу с файлами в форате dbase3.
Рекомендуется использовать провайдеры MS Foxpro, так как синтаксис этих провайдеров очень близко похож на Transact SQL.
        В режиме ODBC рекомендуется использовать  "Visual FoxPro ODBC driver 6.0", а в режиме OLEDB - "Visual FoxPro OLEDB Provider 9.0". Найти их можно или вот здесь: http://msdn.microsoft.com/vfoxpro  или, если вы не нашли ссылик здесь: ODBC драфвер, OLE DB драйвер.
        

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

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

 

Содержание

 

Глава 1: Мой первый запрос

Для начала нужно научиться выполнять элементарные запросы к базе 1С непосредственно из самой программы.

Рассмотрим такой пример: выбрать Код, Наименование из справочника “Номенклатура”

Как известно имена таблиц и полей не совпадают с теми идентификаторами, которые мы задаем в конфигураторе (соответствия можно посмотреть в файле 1Cv7.DDS):

Справочник.Номенклатура – таблица SC433 (в разных базах это может быть разным)

Код – Code, Наименование – Descr

Запрос на TSQL будет выглядеть следующим образом

 

SELECT

     Спр.Code as Код,

     Спр.Descr as Наименование

FROM

     sc433 as Спр

 

Его можно запустить в QA и увидеть полученный результат.

 

Теперь попробуем получить результат из 1С:

Для этого в 1С++ есть встроенный тип “ODBCRecordset”. Полный перечень методов и свойств можно увидеть в документации и в синтаксис-помощнике. Пока остановимся на методе ВыполнитьИнструкцию(Текст, ТЗ = "", ОчищатьТЗ = ""), которая возвращает результат работы запроса, переданного в параметре Текст в таблицу значений

 

Для DBF версии рекомендуется использовать объект “ OLEDBData”(для версии 1С++ 2.0.0.0 и выше) следующим образом:

         База = СоздатьОбъект("OLEDBData");

         Соединение = "Provider=VFPOLEDB.1;Deleted=Yes;Data Source=" + КаталогИБ()+ ";Mode=ReadWrite;Extended Properties="";User ID="";Password="";Mask Password=False;Collating Sequence=RUSSIAN;DSN=""";

         Рез = База.Соединение(Соединение);

         Запрос = База.СоздатьКоманду();

 

RS = СоздатьОбъект("ODBCRecordset");

SQL версия не требует дополнительной инструкции подключения к базе

//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 ставим знак $ перед именем таблицы: $Спр.ТипНоменклатуры

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

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

ИД (Ссылка)           ID     

Код                       Code

Наименование         Descr

Родитель                ParentID

Владелец               ParentExt

ЭтоГруппа              IsFolder

ПометкаУдаления    IsMark

 

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

ИД (Ссылка)           IDDoc

ДатаДок                 Date_Time_IDDoc (для DBF версии Date)

НомерДок               DocNo

ВидДок                  IDDocDef

 

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

 

ТекстЗапроса = "

|SELECT

|  Спр.Code as Код,

|  Спр.Descr as Наименование

|FROM

|  $Справочник.Номенклатура as Спр

|WHERE

|  Спр.IsFolder = 2 AND

|  Спр.IsMark = 0";

 

В DBF версии запрос будет немного отличатся

ТекстЗапроса = "

|SELECT

|  Спр.Code as Код,

|  Спр.Descr as Наименование

|FROM

|  $Справочник.Номенклатура as Спр

|WHERE

|  Спр.IsFolder = 2 AND

|  Спр.IsMark = ‘’";

Дело в том, что в DBF и SQL немного отличаются поля таблиц, а также некоторые системные таблицы имеют другое имя.

В этом примере поле IsMark текстовое, а в SQL – числовое.

 

Для поля IsMark: 1 – Помечен на удаление, 0 – Нет. В DBF версии: ‘*’ – помечен на удаление, ‘’- нет.

Для IsFolder: 2 – Элемент, 1 – Группа. Это сделано для того чтобы упорядочивание по этому полю сначала выдавало группы, а затем элементы.

    

Все конечно работает, но вместо типа номенклатуры получаются какие-то буковки вида ‘   C3A ‘. Это внутренние идентификаторы объектов 1С, как они хранятся в базе. Существует несколько способов получения объектов по их внутренним идам[R1] , например с помощью функции ЗначениеВСтрокуВнутр(). Но у нас в руках такая мощная вещь – она практически все умеет делать сама:

 

ТекстЗапроса = "

|SELECT

|  Спр.Code as Код,

|  Спр.Descr as Наименование,

|  $Спр.ТипНоменклатуры as [ТипНоменклатуры $Перечисление.ТипНоменклатуры]

|FROM

|  $Справочник.Номенклатура as Спр";

 

В результате мы получим ТЗ, в которой будет 3 колонки: Код, Наименование и ТипНоменклатуры. В последней колонке уже будут знакомые нам названия: Товар, Услуга и др.

 

Общий принцип таков:

Имя колонки пишется в квадратных скобочках [] и состоит из 2х частей: собственно наименования и типа значения, разделенных пробелом

Типы бывают следующие:

$Справочник – справочник неопределенного вида

$Справочник.ХХХ – конкретный справочник

$Документ – документ неопределенного вида

$Документ.ХХХ – конкретный документ

$Перечисление.ХХХ

$Счет.ХХХ – счет, где ХХХ – имя плана счетов

$Субконто – специальный тип для бухгалтерской подсистемы

$Неопределенный,

Также есть типы $Число, $Строка, $Дата – но их можно не указывать

 

Замечание: Приводить нужно не к тому типу, который мы хотим получить, а к тому, который задан в конфигураторе

 

Глава 2: Условия в запросах

Основным преимуществом прямых запросов является то, что мы не будем выбирать не нужные нам данные и их отбор предоставим MSSQL, который делает это очень хорошо и главное на сервере, в отличие от 1С.

 

Как всегда рассмотрим примерчик:

Выбрать все элементы спр. Номенклатура, у которых реквизит ТипНоменклатуры = Перечисление.ТипыНоменклатуры.Товар

 

RS = СоздатьОбъект("ODBCRecordset");

ТекстЗапроса = "

|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 вида модификаторов:

Вообщето их гораздо больше.

Полный перечень есть в документации к 1С++

1)Справочник.ХХХ > Справочник, аналогично для документов

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 #__Склад)  (Выбрана группа или список)

 

На небольших списках лучше обойтись без метода «уложения».

Я использую следующую конструкцию

|SELECT

|  Док.IDDoc as Док

|FROM

|  $Документ.Реализация as Док

|WHERE

|  $Док.Склад IN ('     6', '     3', '     2')

|  ";

Где '     6' – внутренний идентификатор элемента справочника «склады».

Получить его можно с помощью метода ЗначениеВСтрокуБД() объекта «MetaDataWork»

     МД=СоздатьОбъект("MetaDataWork");

     Условия ="(";

     Для Н=1 По СписокТипов.РазмерСписка() Цикл

          Условия = Условия + "'" + МД.ЗначениеВСтрокуБД(СписокТипов.ПолучитьЗначение(Н)) + ?( Н=СписокТипов.РазмерСписка(),")","',");

     КонецЦикла;

 

Этот способ удобен тем, что нет лишней траты времени на выгрузку списка значений во временную таблицу.

 

Глава 3: Работа с документами

Все документы 1С хранятся в таблице _1Sjourn(или 1Sjourn для DBF). Конечно, это не очень хорошо, особенно когда блокируется вся таблица при проведении 1 документа, но мы не можем изменять структуры БД, поэтому будем довольствоваться тем, что есть

Структура таблицы

F=ROW_ID                |Row ID                 |I   |0     |0 ß Это поле есть только в SQL версии       

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  ß Это поле есть только в SQL версии        

В DBF его аналог выглядит так:

F=DATE      |date                |D   |8     |0

F=TIME      |Time                |C   |6     |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 = $ВидДокумента.Реализация";

 

Для DBF запрос будет таким:

|SELECT

|  Жур.IDDoc as [Док $Документ],

|  Жур.IDDocDef as Док_вид

|FROM

|  1SJourn as Жур

|WHERE

|  Жур.Date BETWEEN :НачДата~~ AND :КонДата~~ AND

|  Жур.IDDocDef = $ВидДокумента.Реализация";

 

RS.УстановитьТекстовыйПараметр("НачДата", НачДата);

RS.УстановитьТекстовыйПараметр("КонДата", КонДата);

 

 

Здесь стоит обратить внимание на 2 вещи:

1)         Для типизации документа по полю IDDoc необходимо, чтобы в выборке присутствовало поле содержащее IDDocDef с именем <ИмяПоляIDDoc>_вид, в нашем случае это будет Док_вид. Для полей, которые содержат реквизит типа “Документ” вспомогательного поля не требуется.

2)         Модификатор параметра КонДата. Он необходим для того, чтобы в выборку попали документы за последний день.

Поле Date_Time_IDDoc, отвечающее за дату содержит значения вида ‘20030731767WS0  1O6P   ‘. Соответственно параметр НачДата будет выглядеть как ‘20030701’, а КонДата с модификатором - ‘20030731Z, кроме того, в DBF версии модификатор должен быть ~~.

Он переведет дату из 01.01.2006 в {d '2006-01-01'}.

 

Посмотрим, как в запросе преобразовать поле Date_Time_IDDoc в дату документа

Для DBF такого перевода не требуется, т.е. можно написать просто Жур.Date

 

ТекстЗапроса = "

|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";

 

Для DBF битовая маска “&” работать не будет, поэтому можно использовать условие

|  Жур.Closed = 1

Но в реальной базе это значение у проведенного документа может быть отличным от 1(например в бухучете оно равно 5=00000101b)

 

Дополним пример выбором контрагента

 

ТекстЗапроса = "

|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";

 

DBF вариант:

ТекстЗапроса = "

|SELECT

|  Жур.IDDoc as [Док $Документ],

|  Жур.IDDocDef as Док_вид,

|  Жур.Date as ДатаДок,

|  $Док.Контрагент as [Контрагент $Справочник]

|FROM

|  1SJourn Жур

|INNER JOIN

|  $Документ.Реализация as Док ON Док.IDDoc = Жур.IDDoc

|WHERE

|  Жур.Date BETWEEN :НачДата~~ AND :КонДата~~ AND

|  Жур.IDDocDef = $ВидДокумента.Реализация AND

|  Жур.Closed = 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

|  $ДокС.Товар";

 

DBF вариант:

ТекстЗапроса = "

|SELECT

|  $ДокС.Товар as [Товар $Справочник.Товары],

|  SUM($ДокС.Количество) as Количество

|FROM

|  $ДокументСтроки.Реализация as ДокС

|INNER JOIN

|  $Документ.Реализация as Док ON Док.IDDoc = ДокС.IDDoc AND

|                                 $Док.Склад = :ВыбСклад

|INNER JOIN

|  1SJourn as Жур ON Жур.IDDoc = ДокС.IDDoc

|                     Жур.Date BETWEEN :НачДата~~ AND :КонДата~~ AND

|                     Жур.Closed = 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";

 

Не уверен, есть ли в foxpro драйвере аналог функции COALESCE, но в общем случае можно сделать так

|SELECT

|  Док as Док,

|  Док_вид as Док_вид,

|  Реквизит

|FROM

|  (

|  SELECT

|     Жур.iddoc as Док,

|     Жур.iddocdef as Док_вид,

|     Док.$ОбщийРеквизит.Реквизит as Реквизит

|  FROM

|     1SJourn Жур

|  LEFT JOIN

|     $Документ.Реализация as Док ON Док.IDDoc = Жур.IDDoc

|  WHERE

|     Жур.Date BETWEEN :НачДата~~ AND :КонДата~~ AND

|     Жур.Closed = 1

|

|  UNION ALL

|

|  SELECT

|     Жур.iddoc as Док,

|     Жур.iddocdef as Док_вид,

|     Док.$ОбщийРеквизит.Реквизит as Реквизит

|  FROM

|     1SJourn Жур

|  LEFT JOIN

|     $Документ.ПоступлениеТМЦ as Док ON Док.IDDoc = Жур.IDDoc

|  WHERE

|     Жур.Date BETWEEN :НачДата~~ AND :КонДата~~ AND

|     Жур.Closed = 1

|  ) as Журнал

 

 

 

Использование граф отбора

Графы отбора и подчиненные документы лежат в таблице _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 :КонДата~";  

 

DBF вариант выглядит так:

| ТекстЗапроса = "

|SELECT

|  Жур.IDDoc [Док $Документ],

|  Жур.IDDocDef Док_вид

|FROM

|  1Sjourn Жур

|INNER JOIN

|  1SCRDOC Отбор ON Отбор.ChildID = Жур.IDDoc AND

|                    Отбор. MDID = $ГрафаОтбора.Контрагент AND

|                    Отбор.ParentVal = :ВыбКонтрагент* AND

|                    Отбор.ChildDate 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 :КонДата~";  

 

DBF вариант:

| ТекстЗапроса = "

|SELECT

|  Жур.IDDoc [Док $Документ],

|  Жур.IDDocDef Док_вид

|FROM

|  1Sjourn Жур

|INNER JOIN

|  1SCRDOC Отбор ON Отбор.ChildID = Жур.IDDoc AND

|                    Отбор. MDID = 0 AND

|                    Отбор.ParentVal = :ВыбДок* AND

|                    Отбор.ChildDate 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 Рег";

 

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

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  SUM(Рег.КоличествоОстаток) as Количество

|FROM

|  (SELECT

|     $Р.Номенклатура AS Товар,

|     $Р.Количество AS КоличествоОстаток

|  FROM

|     $РегистрИтоги.ОстаткиТоваров as Р

|  WHERE

|     (period = :ПредМесяц~~)

|     AND ($Р.Склад=:Склад)

|

|  UNION ALL

|

|  SELECT

|     $Р.Номенклатура AS Товар,

|     $Р.Количество * (1 - Р.debkred * 2) AS КоличествоОстаток

|  FROM

|     $Регистр.ОстаткиТоваров AS Р

|  INNER JOIN

|     1sjourn jr ON Р.iddoc = jr.iddoc

|           AND (jr.date BETWEEN :НачалоМесяца~~ AND :ПредДата~~)

|           AND ($ФлагРегистра. ОстаткиТоваров = 1)

|  WHERE

|     ($Р.Склад=:Склад)

|  ) Рег

|GROUP BY

|  Рег.Товар

 

Здесь параметр ПредМесяц – это дата начала предыдущего месяца от ВыбДата, а ПредДата=ВыбДата-1;

 

В этом примере мы получим остатки на начало ВыбДата. Если мы хотим на конец, то нужно указывать модификатор :ВыбДата~. Если вообще опустить параметр ВыбДата, то получатся остатки на ТА. Для DBF остатком на конец будет замена ПредДата на ВыбДата, ну а если нужны остатки на ТА, надо условие по датам заменить на

|           AND (jr.date>=НачалоМесяца~~)

 

В модуле документа обычно необходимо получить остатки на документ. Делается это так:

 

ТекстЗапроса =

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  Рег.КоличествоОстаток as Количество,

|  Рег.СуммаОстаток as Сумма

|FROM

|  $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,,

|                               Склад = :ВыбСклад,

|                               (Товар), (Сумма, Количество)) as Рег";

RS.УстановитьТекстовыйПараметр("ВыбДата",

СформироватьПозициюДокумента(ТекущийДокумент(), -1));

 

В DBF версии нужно сделать условие на время. В системе время хранится не в минутах и секундах, а в миллисекундах, прошедших с начала дня. Поэтому время – уникально для каждого документа.

И еще: в DBF версии в модуле проведения ЗАПРОСЫ НЕ РАБОТАЮТ! Т.к. при этом происходит начало транзакции и драйвер FoxPro не может ничего получить из базы.

 

Замечание: Все фильтры нужно накладывать внутри ВТ. Нельзя накладывать фильтр по реквизитам регистра. Это также касается таблицы ОстаткиИОбороты, а для таблицы Обороты можно.

 

А что если нужно получить остатки отфильтрованные по типу номенклатуры. А для этого нужно использовать 2 параметр ВТ, который называется Соединение

 

ТекстЗапроса = "

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  Рег.КоличествоОстаток as Количество

|FROM

|  $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,

|                               INNER JOIN $Справочник.Номенклатура СпрН ON

|                                          СпрН.ID =  Товар AND

|                                          $СпрН.ТипНоменклатуры = :ВыбТип,

|                               Склад = :ВыбСклад,

|                               (Товар), (Количество)) as Рег”;

 

Т.к. в DBF мы формировали запрос без ВТ, то добавить туда еще одно условие не составит труда

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  SUM(Рег.КоличествоОстаток) as Количество

|FROM

|  (SELECT

|     $Р.Номенклатура AS Товар,

|     $Р.Количество AS КоличествоОстаток

|  FROM

|     $РегистрИтоги.ОстаткиТоваров as Р

|  INNER JOIN $Справочник.Номенклатура СпрН ON

|                 СпрН.id=Товар

|                 AND $СпрН.ТипНоменклатуры = :ВыбТип,

|  WHERE

|     (period = :ПредМесяц~~)

|     AND ($Р.Склад=:Склад)

|

|  UNION ALL

|

|  SELECT

|     $Р.Номенклатура AS Товар,

|     $Р.Количество * (1 - Р.debkred * 2) AS КоличествоОстаток

|  FROM

|     $Регистр.ОстаткиТоваров AS Р

|  INNER JOIN

|     1sjourn jr ON Р.iddoc = jr.iddoc

|           AND (jr.date BETWEEN :НачалоМесяца~~ AND :ПредДата~~)

|           AND ($ФлагРегистра. ОстаткиТоваров = 1)

|  INNER JOIN $Справочник.Номенклатура СпрН ON

|                 СпрН.id=Товар

|                 AND $СпрН.ТипНоменклатуры = :ВыбТип,

|  WHERE

|     ($Р.Склад=:Склад)

|  ) Рег

|GROUP BY

|  Рег.Товар

 

Таблица ОстаткиИОбороты похоже на таблицу Остатки, только выбирается начальная и конечная даты и периодичность

 

Без периодичности (за период)

ТекстЗапроса = "

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  Рег.КоличествоНачальныйОстаток as КоличествоНачОст,

|  Рег.КоличествоПриход as КоличествоПриход,

|  Рег.КоличествоРасход as КоличествоРасход,

|  Рег.КоличествоКонечныйОстаток as КоличествоКонОст,

|  Рег.СуммаНачальныйОстаток as СуммаНачОст,

|  Рег.СуммаПриход as СуммаПриход,

|  Рег.СуммаРасход as СуммаРасход,

|  Рег.СуммаКонечныйОстаток as СуммаКонОст,

|FROM

|  $РегистрОстаткиОбороты.ОстаткиТоваров(:НачДата, :КонДата~,,,

|                               Склад = :ВыбСклад,

|                               (Товар), (Сумма, Количество)) as Рег";

 

Для DBF усложняем вариант с остатками:

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  SUM(Рег.КоличествоНачОст) AS КоличествоНачОст,

|  SUM(Рег.КоличествоПриход) AS КоличествоПриход,

|  SUM(Рег.КоличествоРасход) AS КоличествоРасход,

|  SUM(Рег.КоличествоНачОст) + SUM(Рег.КоличествоПриход) - SUM(Рег.КоличествоРасход) AS КоличествоКонОст

|FROM

|  (SELECT

|     $Р.Номенклатура AS Товар,

|     $Р.Количество AS КоличествоНачОст,

|     $0 AS КоличествоПриход,

|     $0 AS КоличествоРасход

|  FROM

|     $РегистрИтоги.ОстаткиТоваров as Р

|  WHERE

|     (period = :ПредМесяц~~)

|     AND($Р.Склад=:Склад)

|

|  UNION ALL

|

|  SELECT

|     $Р.Номенклатура AS Товар,

|     $Р.Количество * (1 - Р.debkred * 2) AS КоличествоНачОст,

|     $0 AS КоличествоПриход,

|     $0 AS КоличествоРасход

|  FROM

|     $Регистр.ОстаткиТоваров AS Р

|  INNER JOIN

|     1sjourn jr ON Р.iddoc = jr.iddoc

|  WHERE

|     (jr.date BETWEEN :НачалоМесяца~~ AND :ПредДата~~)

|     AND ($ФлагРегистра.ОстаткиТМЦ = 1)

|     AND ($Р.Склад=:Склад)

|

|  UNION ALL

|

|  SELECT

|     $Р.Номенклатура AS Товар,

|     $0 AS КоличествоНачОст,

|     (1-Р.debkred)* $Р.Количество AS КоличествоПриход,

|     (Р.debkred) * $Р.Количество AS КоличествоРасход

|  FROM

|     $Регистр.ОстаткиТоваров AS Р

|  INNER JOIN

|     1sjourn jr ON Р.iddoc = jr.iddoc

|  WHERE

|     (jr.date BETWEEN : НачДата~~ AND : КонДата~~)

|     AND ($ФлагРегистра.ОстаткиТМЦ = 1)

|     AND ($.Р.Склад=:Склад)

|  ) Рег

|GROUP BY

|  Товар

Здесь стоит остановиться на параметре $0. Это замена числа ноль на 00000000000.0000

 

 

С периодичностью

Период может быть: День, Неделя, Месяц, Квартал, Год

 

ТекстЗапроса = "

|SELECT

|  Рег.Товар as [Товар $Справочник.Номенклатура],

|  Рег.КоличествоНачальныйОстаток as КоличествоНачОст,

|  Рег.КоличествоПриход as КоличествоПриход,

|  Рег.КоличествоРасход as КоличествоРасход,

|  Рег.КоличествоКонечныйОстаток as КоличествоКонОст,

|  Рег.Период Период

|FROM

|  $РегистрОстаткиОбороты.ОстаткиТоваров(:НачДата, :КонДата~, Месяц,,

|                               Склад = :ВыбСклад,

|                               (Товар), (Количество)) as Рег";

 

Вот тут даже не знаю, как в DBF это на запрос перевести…

 

В данном случае в поле период будет начало каждого месяца (периода). Как всегда при указании периода первую дату ставим без модификатора, вторую с модификатором.

 

Аналогично работает ВТ Обороты, только поля называются <ИмяИзмерения>Оборот

 

Оптимизация регистров

Существует всего 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).

Работает только в SQL!!!

Например запрос

SELECT Спр.Descr FROM sc433  WHERE SP345 = ‘  BE4 ’ ” превратиться в запрос

sp_executesql N’SELECT Спр.Descr FROM sc433  WHERE SP345 =@ТипНом’,‘  BE4 ’

Что практически равносильно использованию параметризированных запросов. Вывод: нужно всегда использовать РежимRPC(1), кроме тех случаев, когда вы вручную создаете и заполняете временные таблицы (баг MSSQL, приводящий к замедлению таких операций. Кстати сама 1С так делает всегда, поэтому при долгом проведении оно продвигается все медленнее и медленнее). К методу УложитьСписокОбъектов() это не относится.

 

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

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

 

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

Вот мы уже и добрались до оптимизации проведения документа. Обычно самыми тормозными местами при проведении являются контроль остатков и партионный учет. Да еще сюда приплетается ошибка MSSQL, поэтому наш вердикт – однозначно переделывать.

 

Пример: Выберем те позиции из документа, которых нет на остатке. Склад табличной части[R2] . Причем выберем только товары и остаток будем рассчитывать на документ. В этом примере учтем, что может быть несколько одинаковых товаров в одном документе

 

ТекстЗапроса = "

|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 = :ВыбДок

|

|GROUP BY 

|  $Док.Товар

|HAVING

|  SUM($Док.Количество) > MIN(Рег.КоличествоОстаток)";    

 

Попробуем то же описать для ДБФ

|SELECT 

|  $Док.Товар as [Товар $Справочник.Номенклатура],

|  SUM($Док.Количество) as Количество,

|  MIN(Рег.КоличествоОстаток) as КолОст,

|

|FROM

|  $ДокументСтроки." + Вид() + " as Док

|

|INNER JOIN

|  $Справочник.Номенклатура as СпрН ON СпрН.ID = $ДокС.Номенклатура AND

|                                     $СпрН.ТипНоменклатуры <> :Услуга

|

|LEFT JOIN

|     (SELECT

|           $Р.Номенклатура AS Товар,

|           $Р.Количество AS КоличествоОстаток

|     FROM

|           $РегистрИтоги.ОстаткиТоваров as Р

|     INNER JOIN

|           (SELECT DISTINCT

|                 $Д1.Товар as Товар

|           FROM

|                 $ДокументСтроки." + Вид() + " as Д1

|           WHERE Д1.IDDOC = :ВыбДок

|           ) as Д

|           ON Товар = Д.Товар

|     WHERE

|           (period = :ПредМесяц~~)

|           AND ($Р.Склад=:Склад)

|

|     UNION ALL

|

|     SELECT

|           $Р.Номенклатура AS Товар,

|           $Р.Количество * (1 - Р.debkred * 2) AS КоличествоОстаток

|     FROM

|           $Регистр.ОстаткиТоваров AS Р

|     INNER JOIN

|           1sjourn jr ON Р.iddoc = jr.iddoc

|                 AND (jr.date BETWEEN :НачалоМесяца~~ AND :ПредДата~~)

|                 AND ($ФлагРегистра. ОстаткиТоваров = 1)

|     INNER JOIN

|           (SELECT DISTINCT

|                 $Д1.Товар as Товар

|           FROM

|                 $ДокументСтроки." + Вид() + " as Д1

|           WHERE Д1.IDDOC = :ВыбДок

|           ) as Д

|           ON Товар = Д.Товар

|     WHERE

|           ($Р.Склад=:Склад)

|     ) Рег ON ($Док.Товар = Рег.Товар)

|WHERE   

|  Док.IDDOC = :ВыбДок

|

|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

|                           $СпрЦ.ТипЦен = :ТипЦен";

 

NOTE: В DBF метапарсер выдает не работающий запрос.

Попытаемся переложить это в DBF

 

|SELECT 

|  СпрН.Descr AS Наименование,

|  Цены.Цена as [Цена $Число]

|FROM

|  $Справочник.Номенклатура СпрН

|LEFT JOIN

|  $Справочник.Цены СпрЦ ON СпрЦ.ParentExt = СпрН.ID AND

|                           $СпрЦ.ТипЦен = :ТипЦен";

|LEFT JOIN (

|  SELECT

|     Период.objid as objid,

|     Период.value as Цена

|  FROM 1sconst as Период

|  WHERE

|     Период.date IN

|     (SELECT

|           MAX(Константа.date)

|     FROM 1sconst as Константа

|     WHERE

|           (Константа.date <= :ВыбДата ~~)

|           AND (Константа.id = $ИсторияРеквизита.Цены.Цена)

|           AND (Константа.objid = Период.objid)

|     )

|     AND Период.id = $ИсторияРеквизита.Цены.Цена

|  ) as Цены ON Цены.objid = СпрЦ.id

 

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

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

GROUP BY … WITH ROLLUP

 

Пример: Запрос по остаткам, с итогами по складам, которые будут выводиться в[R3]  перед списком товаров (как в обычных запросах 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

|";

 

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

Склад

Товар

ИтогПоСкладам

ИтогПоТоварам

Количество

 

 

1

1

220

Основной

 

 

1

100

Основной

Товар1

 

 

60

Основной

Товар2

 

 

40

Вспомогательный

 

 

1

120

Вспомогательный

Товар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 (иначе мы получили бы дополнительные итоги по виду документа, которые нам совсем не нужны). Поэтому мы и делаем таким образом.

 

Глава A:  Работа с журналом расчета

Журнал расчета – простая таблица. В ней нет ни остатков, ни оборотов.

 

F=IDDOC     |                    |C   |9     |0       

F=IDS       |                    |C   |9     |0       

F=IDALG     |                    |C   |4     |0       

F=ORDER     |                    |N   |3     |0       

F=RESULT    |Result              |N   |15    |2       

F=DATEB     |date1               |D   |8     |0       

F=DATEE     |date2               |D   |8     |0       

F=PERIOD    |                    |C   |9     |0       

F=RECALC    |                    |N   |3     |0       

F=ID        |                    |C   |9     |0       

F=DP        |                    |N   |1     |0       

F=IDPARDOC  |                    |C   |9     |0       

F=IDRECALC  |                    |C   |9     |0       

F=FF202     |(P)ОсновнойЭлемент  |C   |9     |0       

F=SP448     |(P)Дни              |N   |7     |2       

F=SP449     |(P)Часы             |N   |8     |2       

F=SP1089    |(P)НомерСтрокиДокуме|N   |5     |0       

F=SP456     |(P)СтрокаИсправления|N   |4     |0       

 

Основные поля:

IDDOC – Документ, который ввел запись

IDPARDOC – Родительский документ

IDS – объект

IDALG – вид расчета

RESULT – результат

ID – Запись журнала расчетов

IDRECALC – Ссылка на запись журнала расчетов. Имеет смысл только для записи-перерасчета

DATEB – дата начала периода

DATEE – дата окончания периода

PERIOD – дата периода

 

Получим простой запрос, в котором будет весь журнал за все время

 

|SELECT

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

Переменные:

ИмяЖурнала – Название журнала расчетов, как оно задано в конфигураторе(например «Зарплата»),

ВидОбъекта – вид объекта, которому подчинен журнал расчетов. В 1С: Зарплата и Кадры это «Сотрудники»

 

Глава A.1:  Отбор «за» и «в»  месяце (по периоду действия)

Как известно журнал расчетов можно выбрать «ЗА» месяц, а можно «В» месяце.

Для первого случая воспользуемся следующим запросом

 

ТекстЗапроса="

|SELECT

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

|WHERE

|  (ЖР.dateb>=:ДатаНачала~~) AND (ЖР.datee<=:ДатаОкончания~~)";

Запрос.УстановитьТекстовыйПараметр("ДатаНачала",ДатаНачала);

Запрос.УстановитьТекстовыйПараметр("ДатаОкончания",ДатаОкончания);

 

Для второго случая запрос будет таким

 

ТекстЗапроса="

|SELECT

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

|WHERE

|  (ЖР.period=’"+Формат(НачМесяца(ДатаПериода),"Д ГГГГММДД")+"M’)";

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

 

Если мы объединим оба запроса в один, то получится выборка, записи которой введены в текущем месяце и только ЗА текущий месяц.

|SELECT

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

|WHERE

|  (ЖР.dateb>=:ДатаНачала~~) AND (ЖР.datee<=:ДатаОкончания~~)

|  AND (ЖР.period=’"+Формат(НачМесяца(ДатаПериода),"Д ГГГГММДД")+"M’)

 

Глава A.2:  Отбор по сотруднику: «ВыбратьЗаписиПоОбъекту» и «ВыбратьПериодПоОбъекту»

 

Воспользуемся предыдущим запросом «за» месяц, но при этом добавим условие по сотруднику

ТекстЗапроса="

|SELECT

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

|WHERE

|  (ЖР.ids=:Сотрудник)

|  AND (ЖР.dateb>=:ДатаНачала~~) AND (ЖР.datee<=:ДатаОкончания~~)";

Запрос.УстановитьТекстовыйПараметр("ДатаНачала",ДатаНачала);

Запрос.УстановитьТекстовыйПараметр("ДатаОкончания",ДатаОкончания);

Запрос.УстановитьТекстовыйПараметр("Сотрудник ", ВыбСотрудник);

 

Усложним пример «в», добавив условие по сотруднику

ТекстЗапроса="

|SELECT

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

|WHERE

|  (ЖР.ids=:Сотрудник)

|  AND (ЖР.period=’"+Формат(НачМесяца(ДатаПериода),"Д ГГГГММДД")+"M’)";

Запрос.УстановитьТекстовыйПараметр("Сотрудник ", ВыбСотрудник);

 

Глава A.3:  Отбор по реквизитам: «ВыбратьПоЗначению»

 

Как и в случае с регистрами все реквизиты описываются в метапарсере.

Например в 1С:Зарлата и Кадры есть реквизит ОсновнойЭлемент, который может объединять фактически разные Объекты журнала расчетов. Вот запрос, который выберет такие записи «в» выбранном периоде

 

|SELECT

|  ЖР.ids AS [Объект $Справочник.Сотрудники],

|  $ЖР.ОсновнойЭлемент AS [ОсновнойЭлемент $Справочник.Сотрудники],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов.Зарплата AS ЖР

|WHERE

|  ($ЖР.ОсновнойЭлемент=:Сотрудник)

|  AND (ЖР.period=’"+Формат(НачМесяца(ДатаПериода),"Д ГГГГММДД")+"M’)

 

Глава A.4:  Получение документов из журнала расчетов

 

Как известно, каждая запись в журнале расчетов вводится каким либо документом. Попробуем получить этот документ

 

|SELECT

|  Журнал.iddocdef AS Документ_вид,

|  ЖР.iddoc AS [Документ $Документ],

|  ЖР.ids AS [Объект $Справочник."+ВидОбъекта+"],

|  ЖР.idalg AS [ВР $ВидРасчета],

|  ЖР.order"+СКЛрежим+" AS [Приоритет $Число],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период,

|  ЖР.recalc AS ЕстьПерерасчет,

|  ЖурналРодитель.iddocdef AS РодительскийДокумент_вид,

|  ЖР.idpardoc AS [РодительскийДокумент $Документ],

|  ЖР.idrecalc AS ПервичнаяЗапись

|FROM

|  $ЖурналРасчетов."+ИмяЖурнала+" AS ЖР

|INNER JOIN "+ СКЛрежим +"1sjourn j_doc on j_doc.iddoc = JR.iddoc

|INNER JOIN "+ СКЛрежим +"1sjourn j_pardoc on j_pardoc.iddoc = JR.idpardoc";

Переменная СклРежим принимает значение либо "" либо "_" в зависимости от того, где происходит работа, в DBF или SQL базах.

 

Здесь стоит обратить внимание на две связи INNER JOIN с журналом документов. Дело в том, что в журнале расчетов нет ссылки на вид документа, только на сам документ.

 

Глава A.5:  Фильтр по виду расчета

Особо стоит отметить работу с видом расчета. Поле idalg таблицы $ЖурналРасчетов для разных форматов баз отличается. На SQL это int, a на DBFchar(4). Для работы с условием по этому полю сществует модификатор ВР~~, который необходимо описать в тексте запроса. Например так:

 

ТекстЗапроса="

|SELECT

|  ЖР.ids AS [Объект $Справочник.Сотрудники],

|  ЖР.idalg AS [ВидРасч $ВидРасчета],

|  ЖР.result AS Результат,

|  ЖР.dateb AS ДатаНачала,

|  ЖР.datee AS ДатаОкончания,

|  ЖР.period AS Период

|FROM

|  $ЖурналРасчетов.Зарплата AS ЖР

|WHERE

|  (ЖР.idalg=:ВидРасчета~~)

|  AND (ЖР.period=’"+Формат(НачМесяца(ДатаПериода),"Д ГГГГММДД")+"M’)";

Запрос.УстановитьТекстовыйПараметр("ВидРасчета", ВидРасчета.Оклад);

 

 

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

Пример: В регистр остатки добавили реквизит[R4]  фирма. Необходимо заполнить это поле по общему реквизиту документа (возможно, только если по реквизиту установлен отбор. Хотя конечно воможно и без установки отбора, но запрос получается гораздо больше и сложнее)

ТекстЗапроса = "

|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

В краце: суть метода состоит в умножении таблиц со значениями группировок и присоединении таблицы со занчениями [R5] функций

 

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

ТекстЗапроса = "

|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