Бухгалтерам, банкам кредиты - Одержання даних із зовнішньої бази 1С

Одержання даних із зовнішньої бази 1С

Описаний спосіб не є єдино можливим для одержання даних з бази 1С, але в нього є ряд переваг.: Втім, як і недоліків.

Загалом, видаю на-гора, і судите самі.

Автор: Андрій Вахрин

Джерело : 1CSQL

Отже, дія номер один. Насамперед, створюємо у вилученій базі удобочитаемие в'юшки й деякі таблиці функції, які нам знадобляться для одержання даних.

В'юшки створюються для таблиць довідників, журналу, шапок і табличних частин документів. Я використав для цього, та й для створення таблиць і функцій інструмент 1С++, тому як він поширюється безкоштовно. А ставити його доводилося в фірмах, у яких не було настільки мною улюбленого ToySQL. Хоча, використовуючи саме бібліотеку ToySQL, деякі моменти можна значно спростити.

Обробку запускаємо на вилученій базі. Створимо для початку необхідні для роботи об'єкти:

загрузитьвнешнююкомпоненту("1CPP.dll"); мд = Создатьобъект("MetaDataWork"); скл = Создатьобъект("ODBCRecordSet"); скл.Устбд1С(); теперь безпосередньо самі в'юшки: Почнемо з журналу документів. попитка СКЛ.Виконати("DROP VIEW dbo.Журнал "); исключение конецпопитки; стр = "CREATE VIEW dbo.Журнал |AS |SELECT DATE_TIME_IDDOC Датадок, IDDOC Посилання, IDDOCDEF Вид, DOCNO Номер, Closed Проведений, ISMARK Пометкаудаления | "; для інд = 1 По Метаданние.Общийреквизитдокумента() Цикл Якщо Метаданние.Общийреквизитдокумента(інд).Сортування = 1 Тоді стр = стр + ", sp"+ МД.Идобъекта(Метаданние.Общийреквизитдокумента(інд)) + " "+ Метаданние.Общийреквизитдокумента(інд).Ідентифікатор; Конецесли; конеццикла; стр = стр + " |FROM dbo._1SJOURN"; попитка СКЛ.Виконати(стр); исключение Повідомити("Не створений журнал документів..."); конецпопитки; т.е. як ми бачимо, всі реквізити мають тепер звичні для нас найменування й ми можемо тепер звернутися до журналу документів наступним запитом: SELECT Датадок, номер, Фірма, Контрагент FROM Журнал WHERE Проведений 0 Тепер перейдемо к шапкам і табличним частинам документів. для інд = 1 По Метаданние.Документ() Цикл Спроба СКЛ.Виконати("DROP VIEW dbo.Дока"+ Метаданние.Документ(інд).Ідентифікатор); Виключення Конецпопитки; стр = "CREATE VIEW dbo.Дока"+ Метаданние.Документ(інд).Ідентифікатор + " |AS |SELECT IDDOC Посилання, "+ МД.Идобъекта(Метаданние.Документ(інд)) + " Вид"; Для рік = 1 По Метаданние.Общийреквизитдокумента() Цикл Якщо Метаданние.Общийреквизитдокумента(рік).Сортування = 0 Тоді стр = стр + ", sp"+ МД.Идобъекта(Метаданние.Общийреквизитдокумента(рік)) + " "+ Метаданние.Общийреквизитдокумента(рік).Ідентифікатор; Конецесли; Конеццикла; Для рік = 1 По Метаданние.Документ(інд).Реквизитшапки() Цикл стр = стр + " | , sp"+ МД.Идобъекта(Метаданние.Документ(інд).Реквизитшапки(рік)) + " "+ Метаданние.Документ(інд).Реквизитшапки(рік).Ідентифікатор; Конеццикла; стр = стр + " |FROM dbo.DH"+ МД.Идобъекта(Метаданние.Документ(інд)); Спроба СКЛ.Виконати(стр); Виключення Повідомити("Не створені шапки документів... "+ Метаданние.Документ(інд).Ідентифікатор); Конецпопитки; Спроба СКЛ.Виконати("DROP VIEW dbo.ТЧ"+ Метаданние.Документ(інд).Ідентифікатор); Виключення Конецпопитки; стр = "CREATE VIEW dbo.ТЧ"+ Метаданние.Документ(інд).Ідентифікатор + " |AS |SELECT IDDOC Посилання, "+ МД.Идобъекта(Метаданние.Документ(інд)) + " Вид |, LINENO_ Номерстроки"; Для рік = 1 По Метаданние.Документ(інд).Реквизиттабличнойчасти() Цикл стр = стр + " | , sp"+ МД.Идобъекта(Метаданние.Документ(інд).Реквизиттабличнойчасти(рік)) + " "+ Метаданние.Документ(інд).Реквизиттабличнойчасти(рік).Ідентифікатор; Конеццикла; стр = стр + " |FROM dbo.DT"+ МД.Идобъекта(Метаданние.Документ(інд)); Спроба СКЛ.Виконати(стр); Виключення Повідомити("Не створені табличні частини документів... "+ Метаданние.Документ(інд).Ідентифікатор); Конецпопитки; конеццикла; Тепер ми можемо сміло розширити запит по документах, вставивши туди реквізити шапок і табличних частин. SELECT Ж.Датадок , Ж.Номер , Ж.Фірма , Ж.Контрагент , Д.Валюта , Д.Курс , ТЧ.Номенклатура , ТЧ.Кількість , ТЧ.Ціна FROM Журнал Ж , Докпоступлениетоваров Д , Тчпоступлениетоваров ТЧ WHERE Проведений 0 AND Ж.Посилання = Д.Посилання AND Ж.Посилання = ТЧ.Посилання Обмаль даних, не чи правда? Але що ми одержимо в результаті виконання запиту в реквізитах, що є посиланнями на елементи довідників? Правильно: ми одержимо всего лише 36-тиричний внутрішній ИД об'єкта. І ИД це буде належати зовсім не нашій базі й нічого гарного ми з нього не витягнемо. Тому створюємо в'юшки на довідники й дивимося як обходити дану проблему. Для інд = 1 По Метаданние.Довідник() Цикл Спроба СКЛ.Виконати("DROP VIEW dbo.Спр"+ Метаданние.Довідник(інд).Ідентифікатор); Виключення Конецпопитки; стр = "CREATE VIEW dbo.Спр"+ Метаданние.Довідник(інд).Ідентифікатор + " |AS |SELECT ID Посилання |, ISMARK Пометкаудаления" + ?(Метаданние.Довідник(інд).Длинакода 0, ", CODE Код", "") + ?(Метаданние.Довідник(інд).Длинанаименования 0, ", DESCR Найменування", "") + ", " + МД.Идобъекта(Метаданние.Довідник(інд)) + " Вид"; Якщо Сокрлп(Метаданние.Довідник(інд).Власник) "Метаданние" Тоді стр = стр + ", PARENTEXT Власник"; Конецесли; Якщо Метаданние.Довідник(інд).Количествоуровней 1 Тоді стр = стр + ", PARENTID Батько |, ISFOLDER Етогруппа"; Конецесли; Для рік = 1 По Метаданние.Довідник(інд).Реквізит() Цикл Якщо Метаданние.Довідник(інд).Реквізит(рік).Періодичний = 1 Тоді Продовжити; Конецесли; стр = стр + " | , sp"+ МД.Идобъекта(Метаданние.Довідник(інд).Реквізит(рік)) + " "+ Метаданние.Довідник(інд).Реквізит(рік).Ідентифікатор; Конеццикла; стр = стр + " |FROM dbo.SC" + МД.Идобъекта(Метаданние.Довідник(інд)); Спроба СКЛ.Виконати(стр); Виключення Повідомити("Не створені довідники... " + Метаданние.Довідник(інд).Ідентифікатор); Конецпопитки; конеццикла; И тепер сміло міняємо запит: SELECT Ж.Датадок , Ж.Номер , СФ.Код Фірма , СК.Код Контрагент , СВ.Код Валюта , Д.Курс , СН.Код Номенклатура , ТЧ.Кількість , ТЧ.Ціна FROM Журнал Ж , Докпоступлениетоваров Д , Тчпоступлениетоваров ТЧ , Спрфирми СФ , Спрконтрагенти СК , Спрвалюти СВ , Спрноменклатура СН WHERE Проведений 0 AND Ж.Посилання = Д.Посилання AND Ж.Посилання = ТЧ.Посилання AND СВ.Посилання = Д.Валюта AND СН.Посилання = ТЧ.Номенклатура

Принаймні, ми тепер бачимо коди елементів довідників, що беруть участь в запиті й запросто можемо знайти відповідності у власній базі (а далі ще і буде інформація, як одержати відразу посилання на елемент!).

Ну й заодно спорудимо в'юшки для одержання констант і періодичних реквізитів довідників. Думаю, вони нам не перешкодять, хоча їхнє одержання в запитах іноді досить стомлююче заняття.

// періодичні значення реквізитів довідників попитка СКЛ.Виконати("DROP VIEW dbo.Періодичні"); исключение конецпопитки; стр = ""; для інд = 1 По Метаданние.Довідник() Цикл тспр = Метаданние.Довідник(інд); Для чрек = 1 По тспр.Реквізит() Цикл трек = тспр.Реквізит(чрек); Якщо трек.Періодичний = 1 Тоді стр = стр + ?(Стрдлина(стр) = 0, "", " |UNION ALL |"); стр = стр + "select 'Спр"+ тспр.Ідентифікатор + "' Довідник"+ ", '"+ трек.Ідентифікатор + " |' Реквізит, S.ID Посилання, C.DATE Дата, C.VALUE Значення |FROM SC"+ МД.Идобъекта(тспр) + " S, _1SCONST C WHERE S.ID = C.OBJID"; Конецесли; Конеццикла; конеццикла; стр = "CREATE VIEW dbo.Періодичні |AS |" + стр; попитка СКЛ.Виконати(стр); исключение Повідомити("Не створені періодичні... "); конецпопитки; // константи попитка СКЛ.Виконати("DROP VIEW dbo.Константи"); исключение конецпопитки; стр = "CREATE VIEW dbo.Константи |AS |SELECT C1.ID ID | , C2.Ідентифікатор Ідентифікатор | , C2.Періодична Періодична | , C1.DATE Дата | , C1.VALUE Значення |FROM _1SCONST C1, Конст C2 |WHERE C1.OBJID = ' 0 ' | AND C1.ID = C2.ID"; попитка СКЛ.Виконати(стр); исключение Повідомити("Не створені константи... "); конецпопитки;

В останньому прикладі (створення в'юшок на константи), є згадування таблиці Конст - це службова, допоміжна таблиця й формується вона в такий спосіб:

попитка СКЛ.Виконати("CREATE Table dbo.Конст |(ID Char(9), Ідентифікатор Char(60), Періодична Int)"); исключение Повідомити("Не створена таблиця Конст"); конецпопитки; стр = "DELETE FROM Конст"; попитка СКЛ.Виконати(стр); исключение конецпопитки; для інд = 1 По Метаданние.Константа() Цикл тконст = Метаданние.Константа(інд); Спроба СКЛ.Виконати("INSERT INTO Конст (ID,Ідентифікатор,Періодична) | VALUES ('"+ МД.Идобъекта(тконст) + "','"+ тконст.Ідентифікатор + "',"+ тконст.Періодичний + ")"); Виключення Конецпопитки; конеццикла; Тепер дозволите привести приклад одержання періодичного реквізиту довідника на прикладі вибору курсів валют на зазначену дату: SELECT Спр.Код , Пер.Значення Курс FROM Спрвалюти Спр LEFT JOIN (SELECT Пер.Посилання, Пер.Значення FROM Періодичні Пер WHERE Пер.Реквізит = 'Курс' AND Пер.Дата = (SELECT Max(Дата) FROM Періодичні WHERE Пер.Посилання = Посилання AND Дата 0) | begin | set @tval = SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',1+@v%36,1)+@tval; | set @v = @v/36; | end; | | if @tval='' set @tval='0'; | | while (len(@tval) < @len1) | begin | set @tval = ' '+@tval ; | end; | | RETURN(@tval); |end"; попитка СКЛ.Виконати(стр); исключение конецпопитки; стр = "CREATE FUNCTION sp_toint(@val VarChar(9)) RETURNS int |AS |begin | declare @i int | declare @tval int | declare @v varchar(9) | | set @v = ltrim(rtrim(@val)) | set @tval = 0 | set @i = 0 | | while @i < len(@v) + 1 | begin | set @i = @i + 1 | set @tval = @tval + (CharIndex(SubString(@v, @i, 1), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)*Power(36,(len(@v) - @i)) | end | | RETURN(@tval) |end"; попитка СКЛ.Виконати(стр); исключение конецпопитки; 2. Іноді треба одержати час документа в легкотравному виді (ЧЧ:ММ:СС): стр = "CREATE FUNCTION sp_gettime(@val VarChar(9)) RETURNS VarChar(8) |AS |begin | declare @i int | declare @h int | declare @m int | declare @s int | | set @i = dbo.sp_toint(@val) | | set @h = ((@i/10000) / 60) / 60 | set @m = ((@i/10000) / 60) - @h * 60 | set @s = (@i/10000) - (@h * 60 * 60 + @m * 60) | | RETURN( cast(@h as char(2)) + ':' + cast(@m as char(2)) + ':' + cast(@s as char(2)) ) |end"; попитка СКЛ.Виконати(стр); исключение конецпопитки;

3. Функції одержання рівня для елемента довідника і його повного коду:

Спроба стр = "CREATE Table dbo.Перерахування |(Вид char(30),Значення char(30),ID char(9))"; СКЛ.Виконати(стр); исключение конецпопитки; стр = "DELETE FROM Перерахування"; попитка СКЛ.Виконати(стр); исключение конецпопитки; для інд = 1 По Метаданние.Перерахування() Цикл пер = Перерахування.Получитьатрибут(Метаданние.Перерахування(інд).Ідентифікатор); Для інд1 = 1 По пров.Количествозначений() Цикл стр = "INSERT INTO Перерахування (Вид, Значення, ID) VALUES | ('"+ Метаданние.Перерахування(інд).Ідентифікатор + "', | '"+ пров.Значениепономеру(інд1).Ідентифікатор() + "', | '"+ МД.Значениевстрокубд(пров.Значениепономеру(інд1)) + "')"; Спроба СКЛ.Виконати(стр); Виключення Повідомити("Не створені перерахування..."); Конецпопитки; Конеццикла; конеццикла;

Так, ще можна створити таблицю видів для документів і довідників, видів субконто и т.д и т.п. Але це ви вуж самостійно. Єдине, що я вам дам, так це пример запиту з розробки. Цей запит одержує таблицю для переносу документов з Комплексної конфігурації в Тис:

Select 'Заявкапокупателя' твід, Left(Ж.Датадок, 8) Датадок, dbo.sp_gettime(substring(Ж.Датадок,9,6)) Час, ж.Номер, ф.Код Фірма, ю.Код Юрлицо, спрсчета.Код Банковскийсчет, к.Код Контрагент, спрдог.Код Договір, спрвал.Код Валюта, д.Курс, Д.Учитиватьндс, д.Суммавклндс, д.Учитиватьнп, д.Суммавклнп, д.Суммавзаиморасчетов, спртц.Код Типцен, спрск.Код скидка, Д.Датаоплати, д.Датаотгрузки, спрскл.Код Склад, перво.Значення Видоперации, перср.Значення Способрезервирования, спрном.Код Номенклатура, тч.Кількість, тч.Коефіцієнт, тч.Ціна, тч.Сума, перндс.Значення Ставкандс, тч.Суммандс From журнал Ж, докзаявкапокупателя Д, тчзаявкапокупателя ТЧ, спрфирми Ф, спрсвоиюрлица Ю, спрбанковскиесчета Спрсчета, спрконтрагенти ДО, спрдоговори Спрдог, спрвалюти Спрвал, спртипицен Спртц, спрскидки Спрск, спрсклади Спрскл, спрноменклатура Спрном, перечисления Перво, перечисления Перср, перечисления Перндс where ж.Посилання = Д.Посилання and Ж.Посилання = ТЧ.Посилання and Ф.Посилання = Ж.Фірма and Ю.Посилання = Ж.Юрлицо and К.Посилання = Д.Контрагент and Д.Договір *= Спрдог.Посилання and Д.Валюта *= Спрвал.Посилання and Д.Типцен *= Спртц.Посилання and Д.Знижка *= Спрск.Посилання and Д.Склад *= Спрскл.Посилання and ТЧ.Номенклатура *= Спрном.Посилання and Д.Банковскийсчет *= Спрсчета.Посилання and ТЧ.Ставкандс *= Перндс.ID and Д.Видоперации *= Перво.ID and Д.Способрезервирования *= Перср.ID

Ну а на закуску - саме смачне. Виконавши запит, що розташований ледве вище по тексту, ми одержимо таблицю, по якій зможемо знайти всі необхідні посилання и створити документи у власній базі. Але хіба це було б швидше, ніж більшість других способів переносу, де є така необхідність пошуку посилань? Ні, звичайно же! Нам було б цікаво відразу одержати ці самі посилання на об'єкти власної бази, потім тільки підставляти їх і записувати знову створені або що редагують документи. І такий спосіб є! Рішення, що я приведу нижче, використає компонент ToySQL.

SELECT ExtQuery.Датадок , ExtQuery.Час , ExtQuery.Номер , [Спр1.Посилання] Фірма , [Спр2.Посилання] Юрлицо , [Спр3.Посилання] Банковскийсчет , [Спр4.Посилання] Контрагент , ExtQuery.Договір , [Спр5.Посилання] Валюта , ExtQuery.Курс , ExtQuery.Учитиватьндс , ExtQuery.Суммавклндс , ExtQuery.Суммавзаиморасчетов , ExtQuery.Типцен , ExtQuery.Знижка , ExtQuery.Датаоплати , ExtQuery.Датаотгрузки , ExtQuery.Склад , ExtQuery.Видоперации , ExtQuery.Способрезервирования , [Спр6.Посилання] Номенклатура , ExtQuery.Кількість , ExtQuery.Коефіцієнт , ExtQuery.Ціна , ExtQuery.Сума , ExtQuery.Ставкандс , ExtQuery.Суммандс FROM (SELECT * FROM OPENQUERY(K44, 'Select Left(Ж.Датадок, 8) Датадок, dbo.sp_gettime(substring(Ж.Датадок,9,6)) Час, Ж.Номер, Ф.Код Фірма, Ю.Код Юрлицо, Спрсчета.Код Банковскийсчет, К.Код Контрагент, Спрдог.Код Договір, Спрвал.Код Валюта, Д.Курс, Д.Учитиватьндс, Д.Суммавклндс, Д.Учитиватьнп, Д.Суммавклнп, Д.Суммавзаиморасчетов, Спртц.Код Типцен, Спрск.Код Знижка, Д.Датаоплати, Д.Датаотгрузки, Спрскл.Код Склад, Перво.Значення Видоперации, Перср.Значення Способрезервирования, Спрном.Код Номенклатура, ТЧ.Кількість, ТЧ.Коефіцієнт, ТЧ.Ціна, ТЧ.Сума, Перндс.Значення Ставкандс, ТЧ.Суммандс From Журнал Ж, Докзаявкапокупателя Д, Тчзаявкапокупателя ТЧ, Спрфирми Ф, Спрсвоиюрлица Ю, Спрбанковскиесчета Спрсчета, Спрконтрагенти ДО, Спрдоговори Спрдог, Спрвалюти Спрвал, Спртипицен Спртц, Спрскидки Спрск, Спрсклади Спрскл, Спрноменклатура Спрном, Перерахування Перво, Перерахування Перср, Перерахування Перндс where Ж.Посилання = Д.Посилання and Ж.Посилання = ТЧ.Посилання and Ф.Посилання = Ж.Фірма and Ю.Посилання = Ж.Юрлицо and К.Посилання = Д.Контрагент and Д.Договір *= Спрдог.Посилання and Д.Валюта *= Спрвал.Посилання and Д.Типцен *= Спртц.Посилання and Д.Знижка *= Спрск.Посилання and Д.Склад *= Спрскл.Посилання and ТЧ.Номенклатура *= Спрном.Посилання and Д.Банковскийсчет *= Спрсчета.Посилання and ТЧ.Ставкандс *= Перндс.ID and Д.Видоперации *= Перво.ID and Д.Способрезервирования *= Перср.ID')) ExtQuery , [Довідник.Фірми] Спр1 , [Довідник.Своиюрлица] Спр2 , [Довідник.Банковскиесчета] Спр3 , [Довідник.Контрагенти] Спр4 , [Довідник.Валюти] Спр5 , [Довідник.Номенклатура] Спр6 WHERE ExtQuery.Фірма*=[Спр1.Код] AND ExtQuery.Юрлицо*=[Спр2.Код] AND ExtQuery.Банковскийсчет*=[Спр3.Код] AND ExtQuery.Контрагент*=[Спр4.Код] AND ExtQuery.Валюта*=[Спр5.Код] AND ExtQuery.Номенклатура*=[Спр6.Код]

Як бачимо, більша частина запиту нам уже відома. Що ж є його ? Це рішення побудоване на можливості звернутися до зв'язаного сервера (див. sp_addlinkedserver). Хоча це може бути абсолютно будь-яким образом отриманий подзапрос. А от уже зв'язування із власними об'єктами по яких-небудь унікальних ключах (наприклад, код для довідника) і одержання готових посилань на об'єкти - це вже саме те, що ми й хотіли. Залишилося тільки виконати наведений Метазапрос і Вивантажити результати його виконання в таблицю значень.

От властиво й всі

Оригінал статті: http://1csql.ru/materials/articles/develop/017.htm
опубликована: 8 листопада 2004

« Пред.		След. »

Вернуться

Главное меню