gifts2017

[ПОЛЕЗНЯШКА, 7.7] Как посчитать итоги по документам черным запросом с изподвывертом?

Опубликовал Сергей (Che) Коцюра (CheBurator) в раздел Программирование - Практика программирования

Мелкая полезняшка, считает запросом итоговую сумму документов и количество документов.

Дано: Документ, в котором в строках ТЧ есть реквизит "Сумма" (то есть суммы введены только в строках ТЧ).

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

Исходные даные:

  • период: ДатаН, ДатаК
  • ВыбКонтрагент: заданный клиент

Решение:

Исполнять будем на типовой ТиС 9.2 на примере документа "Реализация". ПРИМЕР УПРОЩЕННЫЙ (это не значит, что мы должны учесть все тонкости, типа, что разные документы одного вида могут быть оформлены в разных валютах или что надо обрабатывать только проведенные)

 //*******************************************************************

//
Процедура Сформировать()

Запрос=СоздатьОбъект("Запрос");
ТекстЗапроса="
|Период С ДатаН По ДатаК;
|СуммаПоДокументу = Документ.Реализация.Сумма;
|Контрагент = Документ.Реализация.Контрагент;
|Рейтинг = Документ.Реализация.НомерСтроки;
|Функция СуммаСумм = Сумма(СуммаПоДокументу);
|Функция КоличествоДокументов = Счётчик() Когда(Рейтинг<2);
|Группировка Документ;
|Условие (Контрагент = ВыбКонтрагент);
|";

Запрос.Выполнить(ТекстЗапроса);
//отладка
//ТЗ = ""; Запрос.Выгрузить(ТЗ,1,1);
//ПечатьТЗ(ТЗ);

Сообщить("Итого сумма сумм за период: "+Запрос.СуммаСумм);
Сообщить("Итого документов за период: "+Запрос.КоличествоДокументов);

КонецПроцедуры // Сформировать

Может, кому-то и пригодится.
Возможно, я туплю  и есть более простой и красивый вариант черным запросом? - тогда поделитесь...

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Антон Азанов (Djelf) 12.03.16 21:53
Для 1sqlite как то так это выглядит

SELECT sum(S.Сумма) AS Сумма, count(*) AS Счетчик
FROM
(SELECT sum(ДР.Сумма) AS Сумма
FROM Журнал AS Ж
JOIN [ДокументСтроки.Реализация] AS ДР ON ДР.IDDOC=Ж.IDDOC
WHERE Ж.IDDOCDEF=:ВидДокумента.Реализация AND Ж.DATE BETWEEN :НачДата AND :КонДата
GROUP BY Ж.IDDOC) AS S
2. Антон Азанов (Djelf) 12.03.16 22:13
А 1с`запросом красиво получилось. Не видал пока такого! Спасибо.
3. Сергей (Che) Коцюра (CheBurator) 12.03.16 22:19
Спасибо
Потестю на досуге если соображу как этот текст всунуть в 1sqlite
4. Сергей (Che) Коцюра (CheBurator) 12.03.16 22:27
(2) спасибо на добром слове
Тут фишка над которой я малость поломал голову в условии когда - в результате получилось красиво потому что получается типа

Документ1 номерстроки0
Или
Документ2 номерстроки1
Документ2 номерстроки2
5. Сергей (Che) Коцюра (CheBurator) 12.03.16 22:29
(2) у меня вот просьба если можно
Можешь ли ты посмотреть в какой скульный запрос транслируется этот черный запрос - интересно мне. А у меня в округе нет скульных баз семерочных да и со скулем чтобы посмотреть во что это превратилось - я не дружу
??
6. Антон Азанов (Djelf) 12.03.16 23:08
(5) MS SQL не использую ;(
И я не верю что превратится в то же самое...
На дбф 1с переваривает такой запрос перебором, т.е. сначала дергает документы, а потом по каждому дергает табличную часть.
Это видно в строке состояния. Красиво, но по скорости - гадость!

А тут четкое попадание в яблочко!
первый этап WHERE Ж.IDDOCDEF=:ВидДокумента.Реализация AND Ж.DATE BETWEEN :НачДата AND :КонДата
попадает в индекс DOCTYPE - IDDOCDEF,DATE,TIME,IDDOC, по трем первым позициям
второй этап JOIN [ДокументСтроки.Реализация] AS ДР ON ДР.IDDOC=Ж.IDDOC
попадает в индекс IDLINE - IDDOC,LINENO, по 1й позиции
дальше 2 свертки, а 1sqlite делает это безумно быстро

Ха... 1С делает то же самое, судя по виду выполнения, но почему то значительно медленнее ;)

имхо, быстрее выдумать уже некуда!

З.Ы. Этот запрос и для MS SQL должен сработать с минимальными изменениями.
7. Сергей (Che) Коцюра (CheBurator) 12.03.16 23:28
(6) а на чем у тебя клюшки крутятся псли не на скуле?
8. Сергей (Che) Коцюра (CheBurator) 12.03.16 23:29
9. Антон Азанов (Djelf) 12.03.16 23:51
(7) А у меня 1C на Ubuntu в основной конторе ;) И терминальный доступ.
Дисковые операции отдыхают по нагрузке, правда formex грузит не слабо, но ресурс проца еще есть т.е. не критично.
MSSQL на отдельном сервере был, Selta@etersoft на PostgreSQL пробовался, но видимо dbf мое все!

И 1sqlite не работает с таблицами mssql, починить то можно, но нет стимула ;)
10. Il Il (Il) 13.03.16 06:24
(6) Djelf, можно использовать класс http://infostart.ru/public/20823/, так вообще будет универсально под скуль и 1sqlite, вот только под связку постгри и этерсофт не пробовал.
11. Сергей Иванов (chel-new) 13.03.16 10:02
(5) CheBurator, если еще интересно:
declare @p1 int
set @p1=180150017
declare @p3 int
set @p3=2
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'sel ect convert(datetime, substring(_1SJOURN.DATE_TIME_IDDOC, 1, 8)), substring(_1SJOURN.DATE_TIME_IDDOC, 9, 6),
 _1SJOURN.IDDOCDEF, _1SJOURN.IDDOC,DT1611.SP1604 ,0,DT1611.SP1604,DH1611.SP1583,DT1611.LINENO_ 
fr om  _1SJOURN(NOLOCK) left outer join DT1611(NOLOCK) on _1SJOURN.IDDOC=DT1611.IDDOC join DH1611(NOLOCK) on _1SJOURN.IDDOC=DH1611.IDDOC 
where _1SJOURN.IDDOCDEF = 1611 and _1SJOURN.DATE_TIME_IDDOC >= ''20160201     0     0   '' and _1SJOURN.DATE_TIME_IDDOC <= ''20160229FHML6O     0   ''
 and _1SJOURN.CLOSED&1 = 1 and (((DH1611.SP1583 =''   4NG   '')))',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
...Показать Скрыть


если честно нифига не нашел где Счётчик и где проверка на номер строки, похоже это уже 1Ска сама делает...
12. Максим *** (premier) 13.03.16 14:50
(0) CheBurator, а во такое условие Функция КоличествоДокументов = Счётчик() Когда(Рейтинг<2); зачем? Если в документе нет строк, то он тоже скорее всего, попадёт в результат и увеличит количество документов, хотя по сумме ни в какой результат не попадет.
P.S. Так, мелкая придирка, давно на 7.7 не писал, но код, как ни странно, ошеломления не вызвал )))
13. Сергей (Che) Коцюра (CheBurator) 13.03.16 14:54
(12) Да, все верно. Запрос считает максимально "широко" - без ограничений на проведенность/непроведенность/удаление документа. В т.ч. и без ограничений по товароному составу (количеству строк документа). Если надо считать ТОЛЬКО ДОК С ЗАПОЛНЕННОЙ ТЧ (подробно обсасывали на форуме, по следам которого и сделал эту минипубликашку), то Функция КоличествоДокументов = Счётчик() Когда(Рейтинг=1);

14. Сергей (Che) Коцюра (CheBurator) 13.03.16 14:57
(12) Опять же - это как пример. Кто-то захочет посчитать количество документов, где нет ТЧ (например, ПКО) - поправит реквизиты выборки и все. А если НомерСтроки=1 - то ничего и не посчитает. А с исходным условием - посчитается норм.
15. leov-001 (leov-001) 16.03.16 14:36
SET NOCOUNT ON
SELECT
  SUM($docsReal.Сумма) as Сумма 
, COUNT(j.IDDOC) КоличествоДокументов
FROM
  _1SJOURN as j WITH (NOLOCK)
  LEFT JOIN $Документ.Реализация as docReal WITH (NOLOCK) on docReal.IDDOC = j.IDDOC AND $docReal.Контрагент = :ВыбКонтрагент
  INNER JOIN $ДокументСтроки.Реализация as docsReal WITH (NOLOCK) on docsReal.IDDOC = docReal.IDDOC
WHERE
  j.IDDOCDEF=$ВидДокумента.Реализация 
  AND j.DATE BETWEEN :ДатаН AND :ДатаК 
  AND j.CLOSED&1 = 1 
GROUP BY j.IDDOC
...Показать Скрыть