Как известно, 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, которые я рекомендую создавать с умом.
