Сложный запрос к 1С 7.7 через OLE

[vdeltsov]

Есть такая конфигурация, как в приложенном файле.

Вкратце конфигурация такая:
- Есть Справочник.Контрагенты
- К нему подчиненный Справочник.Договоры
- Есть Акты с заказчиками (Документ.ОказаниеУслуг)
- К этим актам есть табличная часть, в которой указана номенклатура и сумма, например: "Обучение 200 руб" и "Субподрядные работы 600 руб"
- Есть Акты, с субподрядчиками (Документ.ПоступлениеПрочее)
- К этим актам есть табличная часть, в которой указана номенклатура и сумма, например: "Обучение 100 руб" и "Субподрядные работы 500 руб",
  а так же в подчиненной таблицы есть ссылка на наш договор с заказчиком.
  Типа этот подрядчик в по этой строке этого акта выполнял работы для нашего договора с нашим конечным заказчиком.

----------------------------------------------------------------------------------------------
Теперь надо через OLE сделать два запроса.
Первый запрос. Вытащить Акты с заказчиком и номенклатуру за период.
Этот запрос сделал, он работает за 10 секунд:
----------------------------------------------------------------------------------------------

Public Sub Customer()
    Dim sSql As String
    Dim q1 As Object
    Dim mSheet As Object
    Dim iRow As Long
   
    Dim v7 As Object
    Dim result As Boolean
   
    Set v7 = CreateObject("v77.Application")
   
    result = v7.Initialize(v7.RMTrade, "C:\Program Files\1Cv77\BIN enterprises /D\\Server\1C\", "")
   
    If Not result Then Exit Sub

    Set mSheet = ThisWorkbook.Sheets("ОказаниеУслугЗапрос")
    mSheet.Cells.Clear

    mSheet.Cells(1, 1) = "НомерДок"
    mSheet.Cells(1, 2) = "ДатаДок"
   
    mSheet.Cells(1, 3) = "Номенклатура"
    mSheet.Cells(1, 4) = "СуммаРуб"
   
    mSheet.Cells(1, 5) = "Договор"
    mSheet.Cells(1, 6) = "ДоговорКод"
    mSheet.Cells(1, 7) = "ДоговорВладелец"
    mSheet.Cells(1, 8) = "ДоговорВладелецКод"
   
   
    Set q1 = v7.CreateObject("Запрос")

    sSql = ""
    sSql = sSql & " ТекущийДокумент = Документ.ОказаниеУслуг.ТекущийДокумент;"
   
    sSql = sSql & " НомерДок = Документ.ОказаниеУслуг.НомерДок;"
    sSql = sSql & " ДатаДок = Документ.ОказаниеУслуг.ДатаДок;"
   
    sSql = sSql & " Номенклатура = Документ.ОказаниеУслуг.Номенклатура.ВидыНоменклатуры.Наименование;"
    sSql = sSql & " СуммаРуб = Документ.ОказаниеУслуг.СуммаРуб;"
   
    sSql = sSql & " Договор = Документ.ОказаниеУслуг.Договор.Наименование;"
    sSql = sSql & " ДоговорКод = Документ.ОказаниеУслуг.Договор.Код;"
    sSql = sSql & " ДоговорВладелец = Документ.ОказаниеУслуг.Договор.Владелец.Наименование;"
    sSql = sSql & " ДоговорВладелецКод = Документ.ОказаниеУслуг.Договор.Владелец.Код;"

    sSql = sSql & " Группировка ТекущийДокумент;"
    sSql = sSql & " Группировка СтрокаДокумента;"

    sSql = sSql & " Условие(ДатаДок >= '01.11.2010');"
    sSql = sSql & " Условие(ДатаДок < '01.12.2010');"

    result = q1.Выполнить(sSql)
    iRow = 1
    While q1.Группировка(1) = 1
        While q1.Группировка(2) = 1
            iRow = iRow + 1
            mSheet.Cells(iRow, 1) = q1.НомерДок
            mSheet.Cells(iRow, 2) = q1.ДатаДок
           
            mSheet.Cells(iRow, 3) = q1.Номенклатура
            mSheet.Cells(iRow, 4) = q1.СуммаРуб
           
            mSheet.Cells(iRow, 5) = q1.Договор
            mSheet.Cells(iRow, 6) = q1.ДоговорКод
            mSheet.Cells(iRow, 7) = q1.ДоговорВладелец
            mSheet.Cells(iRow, 8) = q1.ДоговорВладелецКод
        Wend
    Wend
End Sub

----------------------------------------------------------------------------------------------

Второй запрос. Вытащить Акты с номенклатурой и договоры с подрядчиком, по которым работы делались для договоров с заказчиком, по которым есть акты за тот же период, что и в первом запросе. Но этот запрос не работает
----------------------------------------------------------------------------------------------
ТекущийДокумент = Документ.ПоступлениеПрочее.ТекущийДокумент;

НомерДок = Документ.ПоступлениеПрочее.НомерДок;
ДатаДок = Документ.ПоступлениеПрочее.ДатаДок;

Номенклатура = Документ.ПоступлениеПрочее.Номенклатура.ВидыНоменклатуры.Наименование;
Сумма = Документ.ПоступлениеПрочее.Сумма;

ДоговорНаименование = Документ.ПоступлениеПрочее.Договор.Наименование;
ДоговорКод = Документ.ПоступлениеПрочее.Договор.Код;
ДоговорВладелец = Документ.ПоступлениеПрочее.Договор.Владелец.Наименование;
ДоговорВладелецКод = Документ.ПоступлениеПрочее.Договор.Владелец.Код;

НашДоговорНаименование = Документ.ПоступлениеПрочее.НашДоговор.Наименование, Документ.ОказаниеУслуг.Договор.Наименование;
НашДоговорКод = Документ.ПоступлениеПрочее.НашДоговор.Код, Документ.ОказаниеУслуг.Договор.Код;
НашДоговорВладелец = Документ.ПоступлениеПрочее.НашДоговор.Владелец.Наименование, Документ.ОказаниеУслуг.Договор.Владелец.Наименование;
НашДоговорВладелецКод = Документ.ПоступлениеПрочее.НашДоговор.Владелец.Код, Документ.ОказаниеУслуг.Договор.Владелец.Код;

ДатаДокЗаказчик = Документ.ОказаниеУслуг.ДатаДок;

Группировка ТекущийДокумент;
Группировка СтрокаДокумента;
Условие(ДатаДокЗаказчик >= '01.11.2010');
Условие(ДатаДокЗаказчик < '01.12.2010');

----------------------------------------------------------------------------------------------

Пробовал добавить вот такую строку. Но не помогло.

НашДоговор = Документ.ПоступлениеПрочее.НашДоговор, Документ.ОказаниеУслуг.Договор;


В итоге вытаскиваются вытаскивают абсолютно все акты из таблицы по ПоступлениеПрочее,
но из ОказаниеУслуг ничего не выводится и по полю ДатаДокЗаказчик не фильтруется.

И еще одна проблема такая. Даже если убрать в запросе все ссылки на ОказаниеУслуг,
то в результате Договор и НашДоговор будут ссылаться на один и тот же договор (с подрядчиком), а не на два разных.
И только если из запроса убрать вывод полей договора с подрядчиком, то НашДоговор будет правильно ссылать на НашДоговор.


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

PS: Я - не программист 1С. Скорее VB+SQL
Поэтому могу пояснить, чтобы написал, если бы нужен был SQL-запрос.

select НомерДок = Документ.ПоступлениеПрочее.НомерДок,
ДатаДок = Документ.ПоступлениеПрочее.ДатаДок,

Номенклатура = Документ.ПоступлениеПрочее.Номенклатура.ВидыНоменклатуры.Наименование,
Сумма = Документ.ПоступлениеПрочее.Сумма,

ДоговорНаименование = Документ.ПоступлениеПрочее.Договор.Наименование,
ДоговорКод = Документ.ПоступлениеПрочее.Договор.Код,
ДоговорВладелец = Документ.ПоступлениеПрочее.Договор.Владелец.Наименование,
ДоговорВладелецКод = Документ.ПоступлениеПрочее.Договор.Владелец.Код,

НашДоговорНаименование = Документ.ПоступлениеПрочее.НашДоговор.Наименование, Документ.ОказаниеУслуг.Договор.Наименование,
НашДоговорКод = Документ.ПоступлениеПрочее.НашДоговор.Код, Документ.ОказаниеУслуг.Договор.Код,
НашДоговорВладелец = Документ.ПоступлениеПрочее.НашДоговор.Владелец.Наименование, Документ.ОказаниеУслуг.Договор.Владелец.Наименование,
НашДоговорВладелецКод = Документ.ПоступлениеПрочее.НашДоговор.Владелец.Код, Документ.ОказаниеУслуг.Договор.Владелец.Код,

ДатаДокЗаказчик = Документ.ОказаниеУслуг.ДатаДок

from Документ.ПоступлениеПрочее, Документ.ОказаниеУслуг
where Документ.ПоступлениеПрочее.НашДоговор = Документ.ОказаниеУслуг.Договор

and Документ.ОказаниеУслуг.ДатаДок >= '01.11.2010'
     Документ.ОказаниеУслуг.ДатаДок < '01.12.2010'

group by Документ.ПоступлениеПрочее, Документ.ПоступлениеПрочее.СтрокаДокумента

[prog1c7.7]

без способов решения  без ole и проч.
просто по пунктам от начала до конца задачу пожалуйста,
что как надо, и куда результаты получить хотите

[vdeltsov]

без способов решения  без ole и проч.
просто по пунктам от начала до конца задачу пожалуйста,
что как надо, и куда результаты получить хотите

>>>куда результаты получить хотите
Из базы 1С через OLE надо в итоге закачать в базу SyBase SQL. Но это не принципиально.
Для начала вывожу это просто в Excel файл. Кодвыполняется сейчас на VBA в самом Excel.


>>>просто по пунктам от начала до конца задачу пожалуйста,

Вся задача по пунктам описана.
Первый запрос. Это запрос тут уже написан и работает.
"Вытащить Акты с заказчиком за период и номенклатуру по этим актам. А также ссылки на договоры с заказчиком, к которым относится этот акт."

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


Пример за Март 2011 г. по указанной картинке.

В первом запросе выдается.
Из таблицы "Документ.ОказаниеУслуг"
Акт №2 от 01.03.2011, Договор с заказчиком №2

И номенклатура по этому акту из таблицы "Документ.ОказаниеУслуг - Табличная часть":
Акт №2 Субподрядные работы   600 руб
Акт №2 Обучение             200 руб

Во втором запросе надо найти все акты с подрядчиками, которые относятся к только что найденному Договор с заказчиком №2, то есть вот нужен вот такой результат:
Из таблицы "Документ.ПоступлениеПрочее":
Акт №4   01.02.2011   Договор с подрядчиком №4
Акт №5   01.03.2011   Договор с подрядчиком №5

Из таблицы: "Документ.ПоступлениеПрочее - Табличная часть"
Акт №4   Субподрядные работы   500 руб   Договор с заказчиком №2
Акт №5   Обучение   100 руб   Договор с заказчиком №2

[prog1c7.7]

While q1.Группировка(2) = 1
   ДлясравнДог = Запрос1.ТекущийДокумент;

Далее выполняем ваш второй запрос

Во второй запрос добавляем переменную
  НашДог1 = Документ.ПоступлениеПрочее.НашДоговор;

добавляем условие в него:
  Условие (ДлясравнДог = НашДог1 );

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

[vdeltsov]

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

Но ведь тогда получится, что если в первом запросе будет около 200 наших договоров (за месяц).
То потом придется к базе делать 200 запросов (по одному на каждый наш договор с заказчиком).
А это уже наверное сравнимо с вытаскивание вообще всех договоров с подрядчиком и полным перебором профильтровать их уже на клиентском комьютере. Да и займет это не 10 секунд, а все 10 минут.

[prog1c7.7]

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

[vdeltsov]

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

Имелось в виду, что к базе желательно выполнить всего 2 запроса по 10 секунд:
за 1-ый запрос вытащить сразу все документы с заказчиками,
а за 2-ой запрос вытащить сразу все документы с подрядчиками, которые относятся к тех же договорам с заказчиками.


вот что написал один товарищ:
>>>что бы не делать кучу запросов во втором запросе, написать:
>>>Условие (ДлясравнДог в НашиДоговора)
>>>
>>>НашиДоговора - это список значений, заполненный договорами из первого запроса

Можете пояснить, как этот список значений заполнить договорами из первого запроса?
При условии, что все это делается через OLE (а не в 1С) и передать список объкетов невозможно.

[prog1c7.7]

правильно написал, но опять 2 запроса получается.
Вообще сомнююсь я чота на 10 скекундах,
если честно то оба запроса (или вообще один получится - эт еще покумекать нужно)
надо в 1с делать, а выкинуть что нужно в эксель не трудно

[vdeltsov]

правильно написал, но опять 2 запроса получается.
Вообще сомнююсь я чота на 10 скекундах,
если честно то оба запроса (или вообще один получится - эт еще покумекать нужно)
надо в 1с делать, а выкинуть что нужно в эксель не трудно

2 запроса - это правильно, это - не 201 запрос.

На счет 10-ти секунд (кроме времени на запуск самого объекта 1С) быть может будет откровением, но это реально.

Причем отработка первого запроса и вытаскивание всех значений работают за 3 секунды.
Но если делать точно такой же запрос, но обращаться не к переменной запроса, типа
q1.ДатаДок
q1.НомерДок
q1.КонтрагентКод

а к свойствам текущего документа (как зачем-то советовал 1С-ник):
q1.ТекущийДокумент.ДатаДок
q1.ТекущийДокумент.НомерДок
q1.ТекущийДокумент.Контрагент.Код

то вытаскивание значений работает в 10 раз медленнее (30 секунд).
Очевидно, что при таком способе в запрос идет только ссылка на ТекущийДокумент,
а сам контрагент и затем его код вытаскиваются из базы новым фоновым запросом.
Боюсь, что многие 1С-ники именно такими способами и пользуются.

[vdeltsov]

вот что написал один товарищ:
>>>что бы не делать кучу запросов во втором запросе, написать:
>>>Условие (ДлясравнДог в НашиДоговора)
>>>
>>>НашиДоговора - это список значений, заполненный договорами из первого запроса

Можете пояснить, как этот список значений заполнить договорами из первого запроса?
При условии, что все это делается через OLE (а не в 1С) и передать список объкетов невозможно.

Вот другой знакомый расшифровал, что значит Список значений.

------------------------------------------------------------------------------------------
Перем Запрос, ТекстЗапроса, Таб;
                //Создание объекта типа Запрос

                //Это первый запрос в котором собираются акты.
Запрос = СоздатьОбъект("Запрос");
                ТекстЗапроса =
                "//{{ЗАПРОС(ва)
                |с ВыбНачПериода по ВыбКонПериода;
                |ОбрабатыватьДокументы все;
                |ТекущийДокумент = Документ.ОказаниеУслуг.ТекущийДокумент;
                |НомерДок = Документ.ОказаниеУслуг.НомерДок;
                |ДатаДок = Документ.ОказаниеУслуг.ДатаДок;
                |Номенклатура = Документ.ОказаниеУслуг.Номенклатура.Наименование;
                |СуммаРуб = Документ.ОказаниеУслуг.Сумма;
                |Договор = Документ.ОказаниеУслуг.НашДоговор.Наименование;
                |ДоговорКод = Документ.ОказаниеУслуг.НашДоговор.Код;
                |ДоговорВладелец = Документ.ОказаниеУслуг.НашДоговор.Владелец.Наименование;
                |ДоговорВладелецКод = Документ.ОказаниеУслуг.НашДоговор.Владелец.Код;
                |Группировка ТекущийДокумент все;
                |Группировка СтрокаДокумента;
                |"//}}ЗАПРОС
                ;
                // Если ошибка в запросе, то выход из процедуры
                Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
                               Возврат;
                КонецЕсли;
   
                //Создаем список значений.
СЗ = СоздатьОбъект("СписокЗначений");
               
                //Здесь пока идет перебор документов записываем удовлетворяющие документы в список значений
Пока Запрос.Группировка(1) = 1 Цикл
                               Если Запрос.ТекущийДокумент.НомерДок <> "" Тогда
                                   СЗ.ДобавитьЗначение(Запрос.ТекущийДокумент.НашДоговор);
                               КонецЕсли;
                КонецЦикла;
               
                Запрос = СоздатьОбъект("Запрос");
                ТекстЗапроса =
                "//{{ЗАПРОС(Сформировать)
                |Период с ВыбНачПериода по ВыбКонПериода;
                |ОбрабатыватьДокументы все;
                |Обрабатывать НеПомеченныеНаУдаление;
                |Без итогов;
                |ТекущийДокумент = Документ.ПоступлениеПрочее.ТекущийДокумент;
                |ДатаДок = Документ.ПоступлениеПрочее.ДатаДок;
                |НомерДок = Документ.ПоступлениеПрочее.НомерДок;
                |ДоговорПодряд = Документ.ПоступлениеПрочее.ДоговорПодряд;
                |Номенклатура = Документ.ПоступлениеПрочее.Номенклатура;
                |Сумма = Документ.ПоступлениеПрочее.Сумма;
                |НашДоговор = Документ.ПоступлениеПрочее.НашДоговор,Документ.ОказаниеУслуг.НашДоговор;
                |ДатаДок1 = Документ.ОказаниеУслуг.ДатаДок;
                |Группировка ТекущийДокумент;
                |Группировка СтрокаДокумента;
                |Условие(НашДоговор в СЗ);    // -  добавляем условие отбора
                |"//}}ЗАПРОС
                ;
                // Если ошибка в запросе, то выход из процедуры
                Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
                               Возврат;
                КонецЕсли;

                // Подготовка к заполнению выходных форм данными запроса
                Таб = СоздатьОбъект("Таблица");
                Таб.ИсходнаяТаблица("ва");
                // Заполнение полей "Заголовок"
                Таб.ВывестиСекцию("Заголовок");
                Состояние("Заполнение выходной таблицы...");
                Таб.Опции(0, 0, Таб.ВысотаТаблицы(), 0);
                Пока Запрос.Группировка() = 1 Цикл
                               // Заполнение полей Сформировать
                               Таб.ВывестиСекцию("ТекущийДокумент");
                КонецЦикла;
                // Вывод заполненной формы
                Таб.ТолькоПросмотр(1);
                Таб.Показать("ва", "");
------------------------------------------------------------------------------------------


Но через OLE эти строки работают лекго:
СЗ = v7.CreateObject("СписокЗначений")
СЗ.ДобавитьЗначение q1.ТекущийДокумент.Договор

А вот эта строка не работает, так как 1С не знает, что такое СЗ. О нем знает только Visual Basic.
|Условие(НашДоговор в СЗ);    // -  добавляем условие отбора

После чего этот знакомый выразил уверенность, что написать второй запрос (с связкой документов по подрядчику и заказчику) в 1С 7.7 невозможно.
Такие запросы стало возможным писать только в 1С 8 версии.
Прошу знающих людей подтвердить или опровергнуть данное утверждение.

PS: в конфигурацию 1С ничего добавлять нельзя (замучаешся согласовывать).
Работать можно только через OLE.
Пока иду в сторону полного перебора документов уже в Visual Basic
(но тогда перекачка может занять несколько минут).