gifts2017

1C8.3 SQL view (удобный просмотр содержания базы на SQL-сервере)

Опубликовал Женька Ture (ture) в раздел Программирование - Инструментарий

Обработка создания представлений (view) для SQL базы 1С8.3 и кратенький экскурс по работе сними.
Представления показывают тип составных реквизитов, также есть представление для критериев отбора (по которым можно строить структуру подчиненности на основе "соседней базы", рабочий вариант у меня есть, и это совсем не трудно)

Как известно, 1С обладает великолепной системой извлечения информации - консолью запросов.

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

Ну как тут не помочь человеку, ведь именно в эту секунду мы для него - последняя надежда, спасательный круг, добрый волшебник из сказки... а если еще это девушка прибежала... о-о-о! А ведь самых красивых и подсылают в таких случаях!

 

К делу!

Рядом сидит и молится наш добрый друг, а мы неспешно и поглядывая на него с интересов приступаем:

- Система 1С хороша, но язык запросов слабоват и остановить выполнение формируемого запроса нельзя. А мы еще и не уяснили, что там требуется собрать из базы.

- Нам нужен мощный современный механизм – Management Studio. Я не знаю, что может быть лучше. Все меркнет рядом с этим средством, если оно в опытных руках.

- Нам нужно представлять себе наполнение нашей базы 1С на сервере SQL детально. Чтобы решить эту проблему нужно создать представления для всех хранимых таблиц. (см. обработку).

 

Простой запрос и выглядеть должен просто:

select *

from DПриходнаяНакладнаяID

(запрос выбирает все из таблички)

Упростим себе жизнь и заработаем очко в глазах окружающих – введем короткое имя таблички:

select top 10

       d.*

from DПриходнаяНакладнаяID as d

(запрос выбирает первые 10 строк из таблички целиком)

 

Здесь мы озадаченно смотри нашего друга и спрашиваем – Что он хочет увидеть?

(в этом месте выплескивается потоком сбивчивая речь, из которой выясняются некоторые поля таблички)

select top 10

       d.Номер       as [НомерПНК],

       d.Дата        as [ДатаПНК],

       d.Контрагент  as [Контрагент],

       d.*

from DПриходнаяНакладнаяID as d

(количество строк ограничено 10, но мы ведь только строим отчет; а вот поля таблички уже начали формироваться)

Заработаем еще одно очко (так 1С уже не умеет в своих запросах):

select top 10

       d.Номер                                as [НомерПНК],

       convert(varchar(10),d.Дата,104)        as [ДатаПНК],

       (select top 1 k.Наименование from RКонтрагентыID as k where k.Ссылка=d.Контрагент)  as [Контрагент],

       d.*

from DПриходнаяНакладнаяID as d

(конструкция convert(varchar(10),t.Дата,104) превратит дату в удобную для восприятия, т.к. время в отчетах фигурирует редко; вложенный запрос заменит нечитаемую ссылку на контрагента в его наименование, впрочем, поле представления может быть любым, главное не забывайте добавлять top 1)

Сейчас, внимание на нашего друга, его взгляд в этом месте проясняется – мы добавляем итоговые суммы:

select top 10

       d.Номер                                as [НомерПНК],

       convert(varchar(10),d.Дата,104)        as [ДатаПНК],

       (select top 1 k.Наименование from RКонтрагентыID as k where k.Ссылка=d.Контрагент)  as [Контрагент],

 

       i.Сумма                                as [Сумма],

       i.СуммаНДС                             as [СуммаНДС],

       d.*

from

       DПриходнаяНакладнаяID as d

       outer apply(select sum(t.Сумма) as [Сумма],

                          sum(t.НДС)   as [СуммаНДС] 

                   from TПриходнаяНакладная_НоменклатураID as t

                           where t.Ссылка=d.Ссылка) as i

 

(запрос выбирает все те же 10, но теперь по каждой выбранной строке формируется дополнительный запрос и его содержание цепляется левым соединение – это особая фишка! можно с её помощью расширять состав полезных полей и не морочиться группировками, а вот если заменить outer на cross то соединение будет уже внутренним!)

 

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

ГТД? Что ж сразу трудно было сказать?!!!

select top 10

       d.Номер                                as [НомерПНК],

       convert(varchar(10),d.Дата,104)        as [ДатаПНК],

       (select top 1 k.Наименование from RКонтрагентыID as k where k.Ссылка=d.Контрагент)  as [Контрагент],

 

       i.Сумма                                as [Сумма],

       i.СуммаНДС                             as [СуммаНДС],

      /*все по движениям ГТД */

       r.*

from

       DПриходнаяНакладнаяID as d

      

       cross apply(select sum(t.Сумма) as [Сумма],

                          sum(t.НДС)   as [СуммаНДС] 

                   from TПриходнаяНакладная_НоменклатураID as t

                           where t.Ссылка=d.Ссылка) as i

      

       left join AОстаткиГТДID as r on(r.Регистратор=d.Ссылка)

(прицепили движения по ГТД, да так что напугать посильнее, что всё опять плохо, т.к. ничего знакомого)

Сжалимся и покажем номера ГТД:

select top 10

       d.Номер                                as [НомерПНК],

       convert(varchar(10),d.Дата,104)        as [ДатаПНК],

       (select top 1 k.Наименование from RКонтрагентыID as k where k.Ссылка=d.Контрагент)  as [Контрагент],

 

       i.Сумма                                as [Сумма],

       i.СуммаНДС                             as [СуммаНДС],

       (select top 1 g.Наименование from RГТДID as g where g.Ссылка=r.ГТД)  as [ГТД],

       /*все по движениям ГТД */

       r.*

from

       DПриходнаяНакладнаяID as d

      

       cross apply(select sum(t.Сумма) as [Сумма],

                          sum(t.НДС)   as [СуммаНДС] 

                   from TПриходнаяНакладная_НоменклатураID as t

                           where t.Ссылка=d.Ссылка) as i

      

       left join AОстаткиГТДID as r on(r.Регистратор=d.Ссылка)

К этому моменту Вы должны уже ощутить мощь Management Studio, т.к. возможность остановить выполнение запроса и сам язык запросов крайне мощные инструменты.

 

Наложим ограничения, уберем лишние поля и top 10:

select

       d.Номер                                as [НомерПНК],

       convert(varchar(10),d.Дата,104)        as [ДатаПНК],

       (select top 1 k.Наименование from RКонтрагентыID as k where k.Ссылка=d.Контрагент)  as [Контрагент],

      

       i.Сумма                                as [Сумма],

       i.СуммаНДС                             as [СуммаНДС],

       (select top 1 g.Наименование from RГТДID as g where g.Ссылка=r.ГТД)  as [ГТД]

      

from

       DПриходнаяНакладнаяID as d

      

       cross apply(select sum(t.Сумма) as [Сумма],

                          sum(t.НДС)   as [СуммаНДС] 

                   from TПриходнаяНакладная_НоменклатураID as t

                           where t.Ссылка=d.Ссылка) as i

      

       left join AОстаткиГТДID as r on(r.Регистратор=d.Ссылка)

where

       d.Дата>='20150101' and d.Дата<'20150201'

       and d.Валюта=0x8FC00014C2585F8311DBB72497BCFFFF

 

(фильтры накладывать очень просто, а выполняется только выделенный фрагмент текста)

Сформированный результа легко скопировать в Excel или сохранить в csv (а потом открыть в Excel) и добавить автофильтры.

Не забывайте, что такая вольность становится возможной только с корректными  view, которые я рекомендую создавать с умом.

 

 

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
View1C83.epf
.epf 10,12Kb
31.05.15
12
.epf 10,12Kb 12 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Игорь Пашутин (Alien_job) 05.05.15 07:25
И счастливый пользователь встает и говорит "отлично, куда мне нажать чтобы сформировать такой отчет?" и работа начинается по новой, только уже штатными средствами 1С.
" and d.Валюта=0x8FC00014C2585F8311DBB72497BCFFFF " - не очень удобно.
2. Женька Ture (ture) 05.05.15 09:05
(1) справку по студии я не решился бы писать. Формирование выделенного фрагмента запроса или всего запроса не должно вызвать мучительных проблем, как я малодушно понадеялся. Очень жаль.
3. Женька Ture (ture) 05.05.15 18:54
Для тех, кто в танке:
КОГДА ИСПОЛЬЗУЕТСЯ МЕХАНИЗМ ПРЕДСТАВЛЕНИЙ?
- когда нужно формирования отчетов непосредственно на SQL сервере (об этом и речь шла! А кто это будет делать? - программист, т.е. Вы)
- когда нужно ходить за информацией из одной базы в другую (А как это делается? - прямыми запросами! а Вы подумали о веб-сервисах в одной локальной сети? сказал бы, как есть, но в бане окажусь)
- когда нужно делать правки в базе не выходя из студии (А как же бизнес логика 1С? - Отвечу вопросом на это: а как её починить без регламентных процедур с вышвыриванием всех юзаков? мы здесь вообще серьёзные вещи обсуждаем!)

ПОЧЕМУ ПРЕДСТАВЛЕНИЯ ДОЛЖНЫ БЫТЬ СОЗДАНЫ ПРАВИЛЬНО?
- потому что неопытный программист, получив с ними власть Бога, может остановить работу предприятия на время формирования своего отчета (отчеты строятся в 100% случаях с прицелом на данные, а не индексы и ресурсы сервера)
- потому что представления могут быть бесполезны из-за неполноты

ПОЧЕМУ t-sql, а не запросы 1С?
- потому что сразу виден план запроса, который можно перевести на язык запросов 1С
- потому что язык запросов 1С строился на основе sql и многое осталось в t-sql без соответствующего образа в 1С

ЧЕМ-ТАКИМ студия с t-sql лучше того, чтобы всё делать сразу в 1С?
- тем что новые навыки и знания повысят Вашу стоимость
- это разные вещи как отдых заграницей и работа в крупной и престижной фирме (1С и t-sql - это Ваши инструменты, а как ими пользоваться зависит от Вас)

От чего может быть неудобно?
- от собственного невежества
- от привычки решать все проблемы одним способом
- от лени писать больше, когда можно писать короче
- от того, что забыл вложить в письмо с результатом отчета сам запрос, а отчет попросили повторно или немного изменили требования (отправляйте Excel с отчетом и копируйте туда запрос - пусть проникнутся глубоким непониманием и уважением)
4. Игорь Пашутин (Alien_job) 06.05.15 07:25
(3) ture, зря вы так про невежество
Я только за новые знания и умения, но в данном примере потеряно больше чем получено:
- использование пользователем ( я не очень понял как текст запроса в эксель файле вложенном в электронное письмо поможет пользователю построить такой отчет завтра )
- стоимость поддержки - когда что-то сломается обычный программист не разберется как это работает, ему придется переписать это штатными средствами. Чтобы это поддерживать нужны именно вы с вашей высокой стоимостью
- настраиваемые отборы, группировки
- расшифровка - банально открыть контрагента сразу из отчета
- не увидел в примере использования виртуальных таблиц, полагаю эмулировать их придется самостоятельно

Плюсы
+ Вы смогли использовать методы t-sql которых нет в 1С (хотя в примере таких нет)
+ Пользователи считают вас очень умным

Зачем вам придумывать план запроса, который _не всегда_ можно перевести в 1С, если можно сразу писать в 1С (и корректировать запрос 1с оглядываясь на созданный системой план запроса)?

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

Получать данные из базы 1с с помощью инструментов запросов 1с полезная привычка, ведь этот инструмент специально для этого предназначен и работает хорошо.

Описанный в статье подход отличный способ быть незаменимым программистом (будет работать пока не найдется нормальный программист который не будет взрывать пользователю мозг)
5. Женька Ture (ture) 06.05.15 11:54
(4) У нас с Вами разные позиции по отношению к одному делу. Мы смотрим не просто с разных высот на одно и то же, что лежит под ногами в дали, мы смотрим через разные очки. Это две крайности: Ваша - замкнуться на 1С и жить только тем, что ободрило для использования 1С, а моя - смотреть на продукт 1С со стороны продуктов m$ (производитель ведра, гигант в софт-индустрии, всех давно выбесил тормозами).

Такие разные точки зрения могут быть обусловлены только разной сферой деятельности. Мне ближе это, а Вам другое; у меня один процесс работы, а у Вас другой.
Поэтому расскажу немного о своих условиях:
- я не могу давать пользователям запускать внешние отчеты и обработки (даже сохраненные в базе)
- не могу остановить работу базы без заранее спланированных дней ради обновлений и исправлений
- не могу скопировать базу ночью в тестовый контур ради его обновления или получения актуальных данных для отчетов
- не могу оставлять в кодовой базе лазейки, чтоб обходить проверки записи и проведения
- не могу писать отчеты с нуля на рабочей базе, т.к. не смогу остановить плохой запрос без потери написанного фрагмента запроса, но смогу подвесить формирование запроса на долго (хорошо если не базу до кучи)
- не могу выполнять обмен между базами штатными средствами (нужна мне и скорость и гибкость, а объемы большие на фоне требования к максимальным задержкам синхронизации)
- пользователи для меня - это сплошные нештатные ситуации, срочные отчеты, срочные исправления, "ой, мы забыли, как с этим работать" и, конечно, гора плохо сформулированных доработок, которые надо успеть до релиза формализовать в кодовой базе

Я смотрю на 1С как на проблему, которая исторически сложилась, и от которой уходить никто не собирается, потому что привыкли к невысокой стоимости доработок.
У 1С есть слабое место - замкнутость на себе (это характерная черта всех ERP-систем) и медленный отклик на инновации (если вообще он имеет место). Это заставляет разработчиков в 1С плодить велосипеды, но главное это обязательно подразумевает наличие более эффективных подходов. Мир вокруг 1С развивается быстро, в нем появляются и умирают разные технологии, а потом они появляются снова с некоторыми новшествами. В это время 1С занимается сортировкой и выбором, чего включать, а что оставить в стороне.

Еще со времен "динозавров" люди обнаружили, что задачи имеют много решений. 1С естественно не упоминает все те варианты, что стоят в стороне и не могут быть интегрированы в платформу. Показанный мной вариант работы с базой не интегрируется мной в платформу. Я именно здесь не предлагаю интерфейса конечному пользователю (особенно если учесть мои теплые чувства к нему), я показываю вариант извлечения информации из базы 1С (в некоторых случаях, её можно и изменять, но для этого лучше использовать более чистые инструменты - прямая правка строк в таблице SQL).

t-sql процедурный язык со всеми характерными конструкциями. Там есть циклы, условные операторы, обработка запросов и перебор результатов. Всего охватить в двух словах непросто, но я попробую, это мощный язык. Навязывать его конечно нужды нет, ведь каждый сам решает, на чем остановить свое изучение. Где-то достаточно просто прогать на шарпе в консоли, где-то достаточно просто устанавливать обновления 1С. А где-то надо использовать подручные инструменты по возможности максимально, и тогда мы уже вплотную изучаем и используем все возможности. Где-то не поэкспериментировать и надо все заранее выяснить, а где-то есть поле для экспериментов. Если душа лежит к программированию, то лучше не отворачиваться от естественных инструментов. А если важен карьерный рост в бухгалтерии, то лучше сосредоточить внимание на ПБУ. Можно мыслить понятиями конкретного специфичного учета, как часто принято у 1С-ников, но финал такой деятельности, увы, далек от программирования (хорошо, если должность деградирует до "аналитик учета", но ведь можно скатиться до "тестор", "постановщик задачи программистам" часто посовместительству и "сторож")
6. Max Avramenko Avramenko (A_Max) 06.05.15 16:44
Хотел поставить за демонстрацию работы +
Объяснение "для чего это нужно" -

Итого к сожалению ноль.

Складывается впечатление, что вы хорошо разбираетесь в SQL и при этом абсолютно не хотите пользоваться возможностями самой платформы. Все ваши приведённые "не могу" не реализуемы с использованием самой платформы. Т.е. тут больше подходит поговорка "короче дорога которую знаешь" и вы делаете всё на t-sql становясь единственным и незаменимым. И после вашего ухода, как бы не печально это было, всё будет выкинуто/похоронено и сделано заново.
Mantis; xsazar; vasyak319; +3 Ответить 1
7. Женька Ture (ture) 06.05.15 17:39
(6) Я не собирался уходить. Я могу сменить проект и окажусь как в другой фирме.
Я показал как работать с данными в консоли СУБД.
Там в консоли нет языка 1С и на шарпе там не попрогать. Но это не значит, что там совсем нечего делать.
Программист разве всегда настраивает интерфейс пользователя?
Разве работа должна служить всегда будущему, а не целям в настоящем?
Почему возникает желание замкнуться на 1С?
Почему за списком "не могу" Вы видите только слабость, там ведь явно перечислены требования бизнеса к разработки и сопровождению? Надо вообще радоваться, что Вы сможете что-то делать и успешно, когда вокруг ограничения. Эти "не могу" должны означать для Вас размах и серьезность задач. Для того я их и привел. Когда этих "не могу" нет, то нет и соответствующего уровня профессиональности.

Не нужно строить баррикады и делить людей на своих и чужих. Если Вы владеете 1С, шарпом или каким-то процедурным SQL, то не нужно закрываться и доказывать всему миру, что этого достаточно и все можно и так сделать в рамках уже имеющегося багажа знаний и инструментов. IT развивается быстрыми темпами и требует от нас быть на плаву и в курсе нового. А то я постоянно сталкиваюсь с мнением, что если программист 1С, то это и не программист совсем. А говорят это часто такие же ребятишки, которые замкнуты на то единственное, что знают! Ух как они запросы пишут... мама дорогая, сразу видно становится, что под специализацией своей они понимают какую-то клетку, из которой не могут или боятся выбраться.
Знаете что говорят программисты с++ о прогерах на шарпе? Удивитесь, наверно, они их не воспринимают в качестве серьезных программистов.
Но вот дефакто доминирующую СУБД "тайком друг от друга" изучают все.

Какое положить начало для изучения t-sql программисту 1С?
- Я считаю, что нужно начать с отчетов, которые требуются от случая к случаю. Т.е. отчет можно написать один раз и дать его пользователю, но можно поступить иначе - выдать ему сразу выборку данных. он в любом случае запихнет их в Excel, чтоб анализировать не дергая базу. Так и начнется знакомство с СУБД, если не знакомы еще. Отправляйте отчет свою выборку по почте и крепите туда запрос, тогда его можно будет найти в отправленных, если понадобится снова его формировать.

Главное понять, что чем крупнее предприятие, тем вероятнее деление отдела разработки на роли. Т.е. если программист, то он уже не решает каким будет бизнес-процесс, а если менеджер проекта, то он уже не пишет код. Частое заблуждение, что можно стартовать на одном 1С большую карьеру, разбивается болезненно, когда "вдруг" выясняется, что начальник оказывается занят людьми и их поведением, а не технической стороной производственного процесса.

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

Ну я вроде уже много всего написал. Пора и остановиться.
8. Андрей Акулов (DrAku1a) 07.05.15 04:16
Необходимость работы с SQL напрямую всегда может понадобиться. Так что будет подспорьем! Плюс!
9. Женька Ture (ture) 11.05.15 23:31
Я не стал упоминать почему плохо непосредственно работать с таблицами?
- потому что не прозрачные имена полей
- потому что ресурсами сервера придется управлять хинтами, о чем обычно не помнят или не знают
- потому что можно искать имена полей непосредственно на сервере
10. Сергей Ка (graZy) 20.05.15 15:01
мне вот в 1с не хватает update и delete (из ANSI SQL - как часность t-sql) ... с остальным всем пока удавалось выкрутиться через "ЗАПРОС" ((( ...
11. Женька Ture (ture) 20.05.15 15:20
(10) graZy, Вам разве это требуется?
Пусть так. DML на таблицах прекрасно работает. Я даже нечто подобное писал раньше http://infostart.ru/public/118593/

Однако DML в консоле SQL совсем другое.
Можно через view, но не те, которые перерабатывают отдельные поля (у меня как раз такие, а вам надо чтоб один к одному все поля были). Конечно можно и эти брать (delete, truncate отработают, update пройдут всюду, кроме преобразованных полей).
Сложности скорее связаны не с конкретными view, а с прямотой ручек. Я вот не решился такое предлагать здесь, потому что правка через DML и без четкого понимания самого действа скорее сослужит отрицательную службу популярности публикации.

view, которые я предложил, направлены в первую очередь на отчеты и обмен с другими базами. Знаете ли подключился и вытянул все, что требовалось, из одной базы в другую. Я, к примеру, строю структуру подчиненности на основе критериев отбора или проверяю даты запрета редактирования.
12. Сергей Ка (graZy) 21.05.15 13:23
(11) ture, я часто не до конца развиваю мысль (сорри).

1. я согласен с (пост 8) DrAku1a
2. "Запрос" 1C позволяет нагородить многое

инструмент подобный вашему нужен, но очень специфичен.
думаю может быть полезный тем кто не хочет лезть в детали 1с и/или тому кто захочет/потребуется полезть напрямую в базу.
* вот точно понравилась ваша возможность перехлеста разных баз (не проверю, нет задачи, но с меня плюсик).

примечание: я вырос на SQL c 90 года так или иначе ворочал разные СУБД, с 1с столкнулся последние пару лет. Это к тому что мне "душевно" ближе "чистый" SQL, чем 1С, но работаем на том что дает проще результат.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа