Загрузка данных из DocsVision

 &НаСервере
Функция БазаДВ_ПодключитьсяКСерверу(ДанныеПодключения)
    СтрокаПодключения = "DRIVER={SQL Server Native Client 11.0};SERVER="+ДанныеПодключения.Сервер+";UID="+ДанныеПодключения.Пользователь+";PWD="+ДанныеПодключения.Пароль+";DATABASE="+ДанныеПодключения.База;
    Соединение  = Новый COMОбъект("ADODB.Connection");
    Соединение.ConnectionString = СтрокаПодключения;
    Соединение.ConnectionTimeout = 30;
    Соединение.CommandTimeout = 600;
    Соединение.Open();        
    Возврат Соединение;
КонецФункции

&НаСервере
Функция БазаДВ_ВыполнитьЗапрос(Соединение, ТекстЗапроса)
    Результ = Неопределено;
    Команда = Новый COMОбъект("ADODB.Command");
    Попытка
        Команда.CommandText = ТекстЗапроса;
        Команда.ActiveConnection = Соединение;
        Результ = Команда.Execute()
    Исключение
        ЛогДобавить(ОписаниеОшибки());
    КонецПопытки;
    Возврат Результ;
КонецФункции
 

select
Employ.FirstName
,Employ.MiddleName
,Employ.LastName
,Employ.AccountName
,Employ.Email
,Employ.DisplayString
,Employ.SysAccountName
,Position.Name as PositionName
,Employ.Status
from
[dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}] as Employ
left join
[dvtable_{CFDFE60A-21A8-4010-84E9-9D2DF348508C}] as Position
on Employ.Position = Position.RowID

select
Osnova.InstanceID as id,
NameProject.DisplayValue as NameProject,
Sostoyanie.DisplayValue as Sostoyanie,
DataDogovora.DisplayValue as DataDogovora,
Nomer.DisplayValue as Nomer,
TypeDogovor.DisplayValue as TypeDogovor,
VidDoc.DisplayValue as VidDoc,
Comment.DisplayValue as Comment,
Summa.DisplayValue as Summa,
KodProekta.DisplayValue  as KodProekta,
DateOkonchania.DisplayValue as DateOkonchania,
KontragentSpr.FullName as KontragentFullName,
KontragentSpr.INN as KontragentINN,
KontragentSpr.KPP as KontragentKPP,
RegistratorSpr.FirstName as RegistratorFirstName,
RegistratorSpr.LastName as RegistratorLastName,
RegistratorSpr.MiddleName as RegistratorMiddleName,
OtvetstvenniySpr.FirstName as OtvetstvenniyFirstName,
OtvetstvenniySpr.LastName as OtvetstvenniyLastName,
OtvetstvenniySpr.MiddleName as OtvetstvenniyMiddleName,
IspolnitelSDOSpr.FirstName as IspolnitelSDOFirstName,
IspolnitelSDOSpr.LastName as IspolnitelSDOLastName,
IspolnitelSDOSpr.MiddleName as IspolnitelSDOMiddleName

from [dvtable_{4BF5A4C4-CA53-4294-9D6B-00FAA48AB320}] as Osnova        // dvtable_{4BF5A4C4-CA53-4294-9D6B-00FAA48AB320} Универсальный документ
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Sostoyanie ON    // dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4} вложенная таблица свойств документа
Sostoyanie.InstanceID = Osnova.InstanceID
and Sostoyanie.Name = 'Состояние'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS DataDogovora ON
DataDogovora.InstanceID = Osnova.InstanceID
and DataDogovora.Name = 'Дата договора'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Nomer ON
Nomer.InstanceID = Osnova.InstanceID
and Nomer.Name = 'Юр. номер'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS TypeDogovor ON
TypeDogovor.InstanceID = Osnova.InstanceID
and TypeDogovor.Name = 'Тип договора'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS VidDoc ON
VidDoc.InstanceID = Osnova.InstanceID
and VidDoc.Name = 'Вид документа'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Comment ON
Comment.InstanceID = Osnova.InstanceID
and Comment.Name = 'Комментарии'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Summa ON
Summa.InstanceID = Osnova.InstanceID
and Summa.Name = 'Сумма'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS KodProekta ON
KodProekta.InstanceID = Osnova.InstanceID
and KodProekta.Name = 'Код проекта'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS NameProject ON
NameProject.InstanceID = Osnova.InstanceID
and NameProject.Name = 'Наименование проекта'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS DateOkonchania ON
DateOkonchania.InstanceID = Osnova.InstanceID
and DateOkonchania.Name = 'Дата окончания договора'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Registrator ON
Registrator.InstanceID = Osnova.InstanceID
and Registrator.Name = 'Регистратор'
left outer join
[dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}] AS RegistratorSpr ON         // dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}  Сотрудники
RegistratorSpr.RowID = Registrator.Value
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Otvetstvenniy ON
Otvetstvenniy.InstanceID = Osnova.InstanceID
and Otvetstvenniy.Name = 'Ответственный исполнитель'
left outer join
[dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}] AS OtvetstvenniySpr ON
OtvetstvenniySpr.RowID = Otvetstvenniy.Value
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS IspolnitelSDO ON
IspolnitelSDO.InstanceID = Osnova.InstanceID
and IspolnitelSDO.Name = 'Исполнитель СДО'
left outer join
[dvtable_{DBC8AE9D-C1D2-4D5E-978B-339D22B32482}] AS IspolnitelSDOSpr ON
IspolnitelSDOSpr.RowID = IspolnitelSDO.Value
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Kontragent ON
Kontragent.InstanceID = Osnova.InstanceID
and Kontragent.Name = 'Контрагент'
left outer join
[dvtable_{C78ABDED-DB1C-4217-AE0D-51A400546923}] AS KontragentSpr ON             // dvtable_{C78ABDED-DB1C-4217-AE0D-51A400546923} Контрагенты
KontragentSpr.RowID = Kontragent.Value
where
Sostoyanie.DisplayValue <> 'Аннулирован'
order by DataDogovora.Value

select
Osnova.InstanceID as id,
Links.LinkDesc as LinkDesc,
Links.Link as Link

from [dvtable_{4BF5A4C4-CA53-4294-9D6B-00FAA48AB320}] as Osnova
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Sostoyanie ON
Sostoyanie.InstanceID = Osnova.InstanceID
and Sostoyanie.Name = 'Состояние'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS DataDogovora ON
DataDogovora.InstanceID = Osnova.InstanceID
and DataDogovora.Name = 'Дата договора'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS TypeDogovor ON
TypeDogovor.InstanceID = Osnova.InstanceID
and TypeDogovor.Name = 'Тип договора'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS KodProekta ON
KodProekta.InstanceID = Osnova.InstanceID
and KodProekta.Name = 'Код проекта'
left outer join
[dvtable_{EF30744A-7A2C-4ADC-B275-28FF3C57E754}] AS Links ON
Links.InstanceID = Osnova.InstanceID
where
Links.Link is not null
order by id

select
Osnova.InstanceID as InstanceID,
Nomer.DisplayValue as Nomer,
CardFile.FileName,
CardFile.FileID,
max(VersionedFileCard.Version) as MaxVesrionNumber

into #tmpVersionFiles  // Помещаем во временную таблицу

from [dvtable_{4BF5A4C4-CA53-4294-9D6B-00FAA48AB320}] as Osnova
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS Nomer ON
Nomer.InstanceID = Osnova.InstanceID
and Nomer.Name = 'Юр. номер'
left outer join
[dvtable_{d25f1089-c63d-43e1-9fa4-864c48eeccb4}] AS NameProject ON
NameProject.InstanceID = Osnova.InstanceID
and NameProject.Name = 'Наименование проекта'
left outer join
[dvtable_{3F8270DB-3603-463C-BA59-26B89EBB6CB5}] AS FileReferences ON /* Список Файлов основная таблица*/
FileReferences.InstanceID = Osnova.FilesID
left outer join
[dvtable_{E962AC85-0F53-4439-A1CD-171E46C3EF91}] AS FileReferencesRow ON /* Список Файлов строки */
FileReferences.InstanceID = FileReferencesRow.InstanceID
left outer join
[dvtable_{B4562DF8-AF19-4D0F-85CA-53A311354D39}] AS CardFile ON /* Карточка файла */
FileReferencesRow.CardFileID = CardFile.InstanceID
left outer join
[dvtable_{F831372E-8A76-4ABC-AF15-D86DC5FFBE12}] AS VersionedFileCard ON /* Карточка файла с версиями */
CardFile.FileID = VersionedFileCard.InstanceID

group by
Osnova.InstanceID, Nomer.DisplayValue, CardFile.FileName, CardFile.FileID

// ======= Получаем данные из временной таблицы и связываем с таблицей файлов.

select
#tmpVersionFiles.InstanceID as id,
#tmpVersionFiles.FileName as FileName,
#tmpVersionFiles.MaxVesrionNumber as MaxVesrionNumber,
VersionedFileCard.FileID as FileID,   /* ид версии */
Files.BinaryID as BinaryID

from #tmpVersionFiles
left outer join
[dvtable_{F831372E-8A76-4ABC-AF15-D86DC5FFBE12}] AS VersionedFileCard ON /* Карточка файла с версиями */
#tmpVersionFiles.FileID = VersionedFileCard.InstanceID
and #tmpVersionFiles.MaxVesrionNumber = VersionedFileCard.Version
left outer join
[dvsys_files] AS Files ON                                            /* Файлы */
VersionedFileCard.FileID = Files.FileID
left outer join
dvsys_binaries AS binaries ON                                        /* Бинарные данные */
binaries.id = Files.BinaryID

where
#tmpVersionFiles.FileID is not null
order by #tmpVersionFiles.InstanceID

Запрос получения файла по ID

select data from [dvsys_binaries] where ID = ‘"+ИдБинарныхДанных+"’

Где ИдБинарныхДанных = это поле из вышележашего запроса.

Получение и запись бинарных данных в файл лучше делать через Stream.

&НаСервереБезКонтекста
Функция ПолучитьФайлАДО_Stream(Value, ИмяФайла)
    Файл = Неопределено;
    Stream = Новый COMОбъект("ADODB.Stream");
    Stream.Type = 1;
    Stream.Open();
    Stream.Write(Value);
    Stream.SaveToFile(ИмяФайла);
    Stream.Close();
КонецФункции