Эффективное использование MSSQL при помощи ВК 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=""";

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

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

А что делать, если на компьютере не установлен драйвер фокса? Дык, качать!

Запрос = СоздатьОбъект("ODBCRecordset");

Внимание! SQL версия не требует дополнительной инструкции подключения к базе, метод "УстБД1С()" нужен для подключения к другой(не текущей) базе.

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

|SELECT

| Спр.Code as Код,

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

|FROM

| sc433 as Спр";

ТЗ = Запрос.ВыполнитьИнструкцию(ТекстЗапроса);

ТЗ.ВыбратьСтроку();

Вот и выполнился наш первый запрос. Это конечно хорошо, но справочников в конфигурации много, смотреть каждый раз в 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

Метапарсер так же не обрабатывает имена служебных таблиц, таких как (_)1sjourn, (_)1sconst и т.п.

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

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

|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С, как они хранятся в базе. Существует несколько способов получения объектов по их внутренним идентификаторам, например с помощью функции ЗначениеВСтрокуВнутр(). Но у нас в руках такая мощная вещь – она практически все умеет делать сама:

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

|SELECT

| Спр.Code as Код,

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

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

|FROM

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Внимание! Для документа неопределенного вида обязательно нужно указывать поле <имя>_вид, например для поля Док допполе будет Док_вид (чувствительно к регистру!)

SELECT

jr.iddocdef as Док_вид,

jr.iddoc as [Док $Документ]

FROM _1sjourn as jr

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

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

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

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

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

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

//здесь и далее подключение происходит через 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 показывает номер строки с ошибкой, к которой можно перейти двойным кликом.

А еще 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

Однако не стоит злоупотреблять этим методом. Анализируя производительность можно увидеть, что этот метод на группах работает медленно и это совершенно очевидно, т.к. группа может содержать огромное число элементов, которые надо переписать во временную таблицу. ГОРАЗДО эффективней получить список подгрупп входящих в выбранную группу и делать условие по принадлежности к родителю, а не по ID

Пример: Выберем все документы реализации, у которых склад входит в выбранную группу

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

|SELECT

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

|FROM

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

|WHERE

| $Док.Склад IN (SELECT Val FROM #Группа)";

RS.УложитьСписокОбъектов(ВыбГруппа, "#Группа", "Склады");

Замечание: Рекомендуется использовать локальные временные таблицы – префикс #. В этом случае не нужно заботиться об уникальности имени таблицы для разных пользователей, а также об их последующем удалении.

В ДБФ версии драйвер сам создает такие таблицы и сам же генерирует им имя.

Можно сделать универсальный способ получения имени таблицы, работающий как в ДБФ, так и в СКЛ:

ИмяТаблицы="#Группа ";

Запрос.УложитьСписокОбъектов(СписокРодителей,ИмяТаблицы);

ТекстЗапроса=" (SELECT Val FROM "+ИмяТаблицы+")";

В случае СКЛ запрос выполнится из таблицы "#Группа", а в случае ДБФ система сгенерит имя и вернет ее в переменную ИмяТаблицы. В случае ДБФ имя временной таблицы выглядит так ‘f6d3b234a56c765f52da’ что, конечно не очень красиво для текста запроса(особенно если его еще и разбирать на ошибки нужно или на дополнения). Можно сделать запрос более красивым и универсальным: для этого воспользуемся методом УстановитьТекстовыйПараметр()

ИмяТаблицы="#Группа";

Запрос.УложитьСписокОбъектов(СписокРодителей,ИмяТаблицы);

Запрос. УстановитьТекстовыйПараметр ("Группа", ИмяТаблицы);

ТекстЗапроса=" (SELECT Val FROM "+?(ЭтоСКЛ=0,":","#")+"Группа)";

В этом случае если работа происходит в СКЛе то запрос проигнорирует текстовый параметр, а если в ДБФ, то вместо :Группа подставит имя временной таблицы.

Тот же пример, но по списку контрагентов:

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

|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 – позиция документа, она же дата

Важно! Это поле в СКЛ версии – строка, тогда как в ДБФ – это дата. Это приводит к тому, что в СКЛ версии чтобы получить дату документа нужно выполнить преобразование CAST(LEFT(date_time_iddoc,8) as datetime) а в дбф этого делать не нужно. Однако на условия по дете это никак не сказывается.

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

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

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

как видно оба запроса идентичны, но отличаются именами полей, таблиц и модификаторами. Я стараюсь всегда делать универсальный запрос, который будет работать как на ДБФ так и на СКЛ, поэтому я добавляю проверку на версию 1С(СКЛ или ДЮФ) и дописываю недостающие переменные или поля в зависимости от этой версии. Т.е. предыдущий запрос выглядел бы так:

|SELECT

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

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

|FROM

| "+?(ЭтоСКЛ=1,"_","")+"1SJourn as Жур

|WHERE

| Жур.Date"+?(ЭтоСКЛ=1,"_Time_IdDoc","")+" BETWEEN :НачДата"+?(ЭтоСКЛ=1,"","~~")+" AND :КонДата~"+?(ЭтоСКЛ=1,"","~")+" 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 не может ничего получить из базы.

Замечание от Uzhast: На самом деле в модуле проведения запросы работают. Но есть тонкости: выбирать данные из регистра нужно ДО того, как в модуле проведения будет как-либо произведена запись в регистр. После записи в регистр 1С запрещает блокировать таблицу регистра и Fox не может из нее вытащить данные. Запись в регистр происходит, например, при выполнении ДвижениеПриходВыполнить и ПРИ УДАЛЕНИИ ДВИЖЕНИЙ перед перепроведением документа. Поэтому, чтобы можно было использовать запросы в модуле документа, нужно отключить автоматическое удаление движений в "Конфигураторе". Соответственно, при выборке остатков нужно из них, в определенных случаях, вычитать собственные неудаленные движения документа. После выборки данных из регистра удаляем движения документа самостоятельно и делаем проведение с использованием собранных прямым запросом данных.

Вообще, Fox не может вытащить данные потому, что пытается заблокировать таблицу перед чтением. Если 1С ее занимает, то блокировка не удается. Из-за этого, в частности, прямые запросы не работают, если пытаются вытащить данные из регистра, по которому в текущий момент проводится документ. Меня это сильно разозлило - ведь 1С спокойно формирует отчеты по регистрам в момент проведения документов. Немного поковырял драйвер OLE DB (vfpoledb.dll) и повырезал там вызовы функции LockFile. В результате, теперь прямые запросы работают, даже если в этот момент проводится документ. Вероятно, и запросы к регистру в модуле проведения будут работать после модификации самого регистра. Естественно, такой драйвер нельзя использовать для записи данных в таблицу ДБФ - можно обгадить базу.

Также этот патч решает потенциальную проблему: ведь Fox при выполнении запроса блокирует таблицу. Соответственно, если вдруг кто-то захочет провести документ по регистру, по которому формируется запрос, то его ждет облом. Что при этом будет с 1С - не знаю, но для 1С это явно будет нештатная ситуация. Возможно, такой патч - это аналог скульного NOLOCK'а для ДБФ.

А вот собственно файл http://uzhast.fatal.ru/vfpoledb/

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

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

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

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

| WHERE

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

| UNION ALL

| SELECT

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

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

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

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

| FROM

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

| INNER JOIN

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

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

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

| WHERE

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

| ) Рег

|GROUP BY

| Товар

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

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

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

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

|SELECT

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

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

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

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

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

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

|FROM

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

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

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

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

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

Для ДБФ версии придется немного потрудится.

Сначала сделаем простой запрос с группировкой день

|SELECT

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

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

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

|FROM

| (SELECT

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

| $Р.Количество as Количество,

| jr.date as Период

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

| INNER JOIN

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

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

| WHERE

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

| ) as Рег

|GROUP BY

| Рег.Товар,

| Рег.Период

Как видно запрос получился очень простой, но абсолютно бестолковый J . Теперь немного усложним его – сделаем запрос с группировкой неделя. Для этого воспользуемся функцией dow(Дата) которая возвращает номер недели в году, но для операций с датой этого не достаточно, нужно еще использовать преобразование 60*60*24=86400 – число секунд в одном дне. Дело в том, что Фокс работает с датой как и со временем, т.е. Дата-1 отнимет одну секунду от даты и получится что то вроде
’01.01.2000: 23:59:59’ вместо просто ’01.01.2000’

|SELECT

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

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

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

|FROM

| (SELECT

| Рег.Товар,

| IIF(Рег.Период<:НачДата~~,:НачДата~~,Рег.Период) as Период

| FROM

| (SELECT

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

| $Р.Количество as Количество,

| jr.date-dow(jr.date)*86400 as Период

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

| INNER JOIN

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

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

| WHERE

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

| ) as Рег

|GROUP BY

| Рег.Товар,

| Рег.Период

В этом примере условие IIF(Условие,Правда,Ложь) выполняет роль фильтра, т.к. запрос 1С с группировкой Неделя отрезает все даты меньшие чем дата начала. Сам запрос практически не изменился, разве что для удобства группировок мы создали еше один вложенный SELECT, без которого, впрочем, можно было бы обойтись.

Теперь посмотрим на запрос с группировкой Месяц

|SELECT

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

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

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

|FROM

| (SELECT

| Рег.Товар,

| IIF(Рег.Период<:НачДата~~,:НачДата~~,Рег.Период) as Период

| FROM

| (SELECT

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

| $Р.Количество as Количество,

| CAST(

| STR(MONTH(jr.date))+’.01.’+STR(YEAR(jr.date))

| as datetime) as Период

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

| INNER JOIN

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

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

| WHERE

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

| ) as Рег

|GROUP BY

| Рег.Товар,

| Рег.Период

Как и в предыдущем примере условие IIF(Условие,Правда,Ложь) выполняет роль фильтра, а вот подзапрос немного изменился. Функция MONTH(дата) возвращает месяц даты, а YEAR(дата) – год.

Обратите внимание на формат даты: «ММ.ДД.ГГГГ». Функция STR(Ч) преобразовывает результат в строку, а CAST(Строка as datetime) – обратно в дату. Таким образом из даты ‘23.04.2006’ мы получили дату ’01.04.2006’

Самой сложной операцией будет группировка Квартал.

|SELECT

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

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

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

|FROM

| (SELECT

| Рег.Товар,

| IIF(Рег.Период<:НачДата~~,:НачДата~~,Рег.Период) as Период

| FROM

| (SELECT

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

| $Р.Количество as Количество,

| CAST(

| STR(

| IIF(MONTH(jr.date)<4, '01',

| IIF(MONTH(jr.date)<7, '04',

| IIF(MONTH(jr.date)<10, '07',10

| )

| )+’.01.’+STR(YEAR(jr.date))

| as datetime) as Период

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

| INNER JOIN

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

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

| WHERE

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

| ) as Рег

|GROUP BY

| Рег.Товар,

| Рег.Период

Здесь делается проверка на принадлежноть месяца к определенному кварталу и производится подменена номера месяца на начало квартала. Так, дата ’12.08.2006’ преобразуется в дату ’01.07.2006’

Последняя группировка - Год

|SELECT

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

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

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

|FROM

| (SELECT

| Рег.Товар,

| IIF(Рег.Период<:НачДата~~,:НачДата~~,Рег.Период) as Период

| FROM

| (SELECT

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

| $Р.Количество as Количество,

| CAST(

| STR(’01.01.’+STR(YEAR(jr.date))

| as datetime) as Период

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

| INNER JOIN

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

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

| WHERE

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

| ) as Рег

|GROUP BY

| Рег.Товар,

| Рег.Период

Запрос тоже очень простой - от даты остается только номер года, а месяц и день заменяются на 01.01

Теперь вернемся к примеру СКЛ с периодичностью и попробуем переложить его на ДБФ запрос.

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

естьДень=1; естьМесяц=1; естьКвартал=0;

Если Группировка = "день" Тогда

ПеременнаяПериода = "Рег.Период";

ИначеЕсли Группировка = "неделя" Тогда

ПеременнаяПериода = "Рег.Период-dow(Рег.Период)*86400";

ИначеЕсли Группировка = "месяц" Тогда

ПеременнаяПериода = "CAST(STR(MONTH(Рег.Период))+'.01.'+STR(YEAR(Рег.Период)) as datetime)";

естьДень=0;

ИначеЕсли Группировка = "квартал" Тогда

ПеременнаяПериода = "CAST(

| STR(

| IIF(MONTH(jr.date)<4, '01',

| IIF(MONTH(jr.date)<7, '04',

| IIF(MONTH(jr.date)<10, '07',10

| )

| )+’.01.’+STR(YEAR(jr.date))

| as datetime)";

естьДень=0; естьКвартал=1;

ИначеЕсли Группировка = "год" Тогда

ПеременнаяПериода = "CAST('01.01.'+STR(YEAR(Рег.Период) as datetime)";

естьДень=0; естьМесяц=0;

КонецЕсли;

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

|SELECT

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

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

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

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

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

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

|FROM

| (SELECT

| Рег.Товар,

| IIF("+ПеременнаяПериода +"<:НачДата~~,:НачДата~~,

| "+ПеременнаяПериода +"

| ) as Период,

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

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

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

| FROM

| (SELECT

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

| Р.period as Период,

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

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

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

| FROM

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

| WHERE

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

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

| UNION ALL

| SELECT

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

| jr.date as Период,

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

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

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

| FROM

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

| INNER JOIN

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

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

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

| WHERE

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

| UNION ALL

| SELECT

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

| jr.date as Период,

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

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

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

| FROM

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

| INNER JOIN

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

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

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

| WHERE

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

| ) Рег

|GROUP BY

| Товар,

| Период

|";

Как видно, общий смысл группировки по периоду – задать нужное представление даты. Таким образом можно получить группировку по любому произвольному периоду, отличному от стандартных День/Неделя/Месяц/Год

Во всех предыдущих примерах на выходе получалась таблица значений с одним реквизитом «Товар». Но что делать, в случае, когда реквизитов несколько, например Товар и Период? Работать с такой таблицей не удобно, т.к. ТЗ, в отличие от стандартного запроса, не умеет получать промежуточные итоги по реквизиту, поэтому приходится сортировать запрос, сравнивать предыдущий товар с текущим и выводить результат. Все это усложняет отображение результата и программный код. Для упрощения кода может помочь ИндексированнаяТаблица. Вот пример показывающий как она работает:

ТЗ=Запрос.ВыполнитьИнструкцию(ТекстЗапроса);

ИТЗТовары=СоздатьОбъект("ИндексированнаяТаблица");

ИТЗТовары.Загрузить(ТЗ);

ИТЗТовары.Группировать("Товар:Товар; Период: Период"," КоличествоНачОст, КоличествоПриход, КоличествоРасход,КоличествоКонОст");

ИТЗТовары.ВыбратьСтроки();

Пока ИТЗТовары.ПолучитьСтроку()=1 Цикл

итзПериод=ИТЗТовары.тзПотомки;

итзПериод.ВыбратьСтроки();

ИтогКолвоНач = ИТЗТовары.КоличествоНачОст;

ИтогКолвоПриход = ИТЗТовары.КоличествоПриход;

ИтогКолвоРасход = ИТЗТовары.КоличествоРасход;

ИтогКолвоКон = ИТЗТовары.КоличествоКонОст;

Пока итзПериод.ПолучитьСтроку()=1 Цикл

КолвоНач = ИТЗТовары.КоличествоНачОст;

КолвоПриход = ИТЗТовары.КоличествоПриход;

КолвоРасход = ИТЗТовары.КоличествоРасход;

КолвоКон = ИТЗТовары.КоличествоКонОст;

КонецЦикла;

В этом примере метод Группировать сам сгруппирует линейную таблицу по реквизитам Товар,Период и подсчитает итоги по колонкам КоличествоНачОст, КоличествоПриход, КоличествоРасход,КоличествоКонОст. Фактически это аналог работы стандартного Запроса с группировками Товар и Период. Однако, не стоит делать группировки по приведенным полям (в нашем случае это поле Товар), т.к. для сравнения строк 1С делает подзапрос к базе и все ускорение сводится на «нет». Лучше завести дополнительное поле Товар_ИД, которое будет содержать текстовый идентификатор товара и группировать по нему. Получить представление товара можно будет всегда, т.к. метод Группировать() не удаляет «лишние» поля,как это делает метод Свернуть(), а поля Товар и Товар_ИД по сути идентичны и уникальны для всей таблицы.

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

Существует всего 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, поэтому наш вердикт – однозначно переделывать.

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

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

|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

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

RECALC – битовое поле содержащее статус записи:

&1 - расчитанная;
&2 - сторнированная;
&4 - фиксированная;
&8 - отредактированная в ручную;
&16 - ?;
&32 - перерасчет

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

|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 на DBF – char(4). Для работы с условием по этому полю сществует модификатор ВР~~, который необходимо описать в тексте запроса. Например так:

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

|SELECT

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

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

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

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

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

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

|FROM

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

|WHERE

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

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

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

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

Автор: Ситников Анатолий aka acsent

Дополнения по работе с DBF, индексированной таблицей и журналом расчета: Vaicartana