Имитация таблицы остатков и оборотов при помощи запроса или как нарисовать несуществующее

26.11.18

Разработка - Запросы

Имитация таблицы остатков и оборотов при помощи запроса.

Вашему вниманию представляется маленькое упражнение с языком запросов 1С.

Предыстория.  Потребовался как-то сервисному подразделению отчет следующего вида.

Период

Склад

Номенклатура

Остаток на начало

Поступило

Отгружено

Остаток наконец

01.01.ГГГГ

Основной

Ложка

 

 

 

 

….

…..

…..

 

 

 

 

14.01.ГГГГ

Основной

Ложка

 

 

 

 

 

В общем, типичный регистр накопления с остатками. Но особенность в том, что хотели они знать эти остатки на 1-2 недели вперед. Это значит, что указанная номенклатура не только не поступила на склад, но она даже еще не произведена фабрикой. А что касается отгрузки, то она лишь запланирована заказом клиента и случится только после того как фабрика заказ произведет. Заказчики хотели знать, хватит ли им емкости склада под будущие поступления товара с разбросом до 5%. Естественно в конфигурации не нашлось никакого регистра, учитывавшего поступления в таком не обычном разрезе.  Но зато были 2 документа ЗаказФабрике  где указывалось когда и на какой склад поступит товар и ЗаказКлиента где указывался день и место отгрузки купленного товара. Из всего этого было решено соорудить отчет имитирующий таблицу остатков и оборотов.

 

Для тех, кто знаком с языком запросов сразу приведу все решение.  Ну а для тех, кто решит дочитать до конца после дам пояснения.

// 1.Выберем из БД исходные данные
Выбрать
   &ДатаНачала Как Период,
   ОстаткиНаНачало.Склад,
   ОстаткиНаНачало.Номенклатура,
   ОстаткиНаНачало.КоличествоОстаток как ОстатокНаНачало,
   0 как Поступления,
   0 как Отгрузка
 Поместить ВТ_ИсходныеДанные
ИЗ РегистрНакопления.НоменклатураНаСкладе.Остатки(
   КонецПериода(ДобавитьКДате(&ДатаНачала,День,-1), День ) ) как ОстаткиНаНачало

Объединить все

Выбрать
    ЗаказФабрике.ДатаПоступления Как Период,
    ЗаказФабрике.Склад,
    ЗаказФабрике.Номенклатура,
    0 как ОстатокНаНачало,
    ЗаказФабрике.Количество как  Поступления,
    0 как Отгрузка
ИЗ Документ.ЗаказФабрике как ЗаказФабрике
Где ЗаказФабрике.Проведено 
        И ЗаказФабрике.ДатаПоступления между &ДатаНачала И &ДатаОкончания

Объединить все

Выбрать
   НоменклатураЗаказа.ДатаОтгрузки Как Период,
   НоменклатураЗаказа.Склад,
   НоменклатураЗаказа.Номенклатура,
   0 как ОстатокНаНачало,
   0 как  Поступления,
   НоменклатураЗаказа.Количество как Отгрузка
ИЗ Документ.ЗаказКлиента.Номенклатура как НоменклатураЗаказа
Где НоменклатураЗаказа.Ссылка.Проведено
    И НоменклатураЗаказа.ДатаОтгрузки между &ДатаНачала И &ДатаОкончания;

//2.Выберем из исходных данных все имеющиеся даты
Выбрать Различные Период 
 Поместить ВТ_Периоды
ИЗ ВТ_ИсходныеДанные;

//3.Выберем из исходных данных все имеющиеся варианты измерений.
//В нашем случае это Склад и Номенклатура
// и размножим их на каждый день отчета.
// Получим таблицу недостающих измерений для расчета остатков.
Выбрать 
   Периоды.Период,
   ПереченьИзмерений.Склад, 
   ПереченьИзмерений.Номенклатура
 Поместить ВТ_Дополнения
ИЗ ВТ_Периоды как Периоды
Левое Соединение
  (Выбрать Различные Склад, Номенклатура
      ИЗ ВТ_ИсходныеДанные )  как ПереченьИзмерений
По Истина;


//4. Свернем исходные данные с  таблицей недостающих измерений
Выбрать
   ДополненыеИсходныеДанные.Период,
   ДополненыеИсходныеДанные.Склад,
   ДополненыеИсходныеДанные.Номенклатура,
   Сумма(ДополненыеИсходныеДанные.ОстатокНаНачало) как ОстатокНаНачало,
   Сумма(ДополненыеИсходныеДанные.Поступления) как Поступления,
   Сумма(ДополненыеИсходныеДанные.Отгрузка) как Отгрузка
 Поместить ВТ_ПодготовленаяТаблица
ИЗ  (
     Выбрать
        ИсходныеДанные.Период,
        ИсходныеДанные.Склад,
        ИсходныеДанные.Номенклатура,
        ИсходныеДанные.ОстатокНаНачало,
        ИсходныеДанные.Поступления,
        ИсходныеДанные.Отгрузка
      ИЗ  ВТ_ИсходныеДанные как ИсходныеДанные
      Объединить все
      Выбрать 
         Дополнения.Период ,
         Дополнения .Склад, 
         Дополнения.Номенклатура,
         0 как ОстатокНаНачало,
         0 как Поступления,
         0 как Отгрузка
       ИЗ  ВТ_Дополнения как Дополнения
       ) КАК ДополненыеИсходныеДанные
Сгруппировать по
    ДополненыеИсходныеДанные.Период,
    ДополненыеИсходныеДанные.Склад,
    ДополненыеИсходныеДанные.Номенклатура
ИНДЕКСИРОВАТЬ ПО 
    ДополненыеИсходныеДанные.Период,
    ДополненыеИсходныеДанные.Склад,
    ДополненыеИсходныеДанные.Номенклатура;


// 5. Рассчитаем сначала остатки на конец, а затем обратным, счетом остатки на
 // начало, заодно, убрав нулевые строки.
Выбрать
   ИтоговаяТаблица.Период,
   ИтоговаяТаблица.Склад,
   ИтоговаяТаблица.Номенклатура,
   ИтоговаяТаблица.ОстатокНаКонец+ ИтоговаяТаблица.Отгрузка- 
                   ИтоговаяТаблица.Поступления как ОстатокНаНачало,
   ИтоговаяТаблица.Поступления,
   ИтоговаяТаблица.Отгрузка,
   ИтоговаяТаблица.ОстатокНаКонец 
 ИЗ  (
      Выбрать
         ПодготовленаяТаблица.Период,
         ПодготовленаяТаблица.Склад,
         ПодготовленаяТаблица.Номенклатура,
         ПодготовленаяТаблица.ОстатокНаНачало,
         ПодготовленаяТаблица.Поступления,
         ПодготовленаяТаблица.Отгрузка,
         Сумма(КонОстаток.ОстатокНаНачало+КонОстаток.Поступления-
               КонОстаток.Отгрузка) как ОстатокНаКонец 
      ИЗ ВТ_ПодготовленаяТаблица как ПодготовленаяТаблица
      Левое Соединение ВТ_ПодготовленаяТаблица как КонОстаток
      ПО  (ПодготовленаяТаблица.Склад = КонОстаток.Склад
           И  ПодготовленаяТаблица.Номенклатура= КонОстаток.Номенклатура
           И  КонОстаток.Период<=ПодготовленаяТаблица.Период ) 
      Сгруппировать по 
         ПодготовленаяТаблица.Период,
         ПодготовленаяТаблица.Склад,
         ПодготовленаяТаблица.Номенклатура,
         ПодготовленаяТаблица.ОстатокНаНачало,
         ПодготовленаяТаблица.Поступления,
         ПодготовленаяТаблица.Отгрузка
     )  КАК ИтоговаяТаблица
Где НЕ (
        ИтоговаяТаблица.Поступления=0
        И ИтоговаяТаблица.Отгрузка=0
        И ИтоговаяТаблица.ОстатокНаКонец=0 )
Упорядочить по 
        ИтоговаяТаблица.Период,
        ИтоговаяТаблица.Склад,
        ИтоговаяТаблица.Номенклатура

Вот и вся премудрость.

Ну а теперь обещанные пояснения.

Для расчета потребуется во первых 2 параметра, дата  начала и дата окончания, это наш отчетный период.

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

   Далее в запросе 2 и 3 создается таблица дополнений. В ней хранятся все варианты склада и номенклатуры на каждый день отчетного периода. Зачем это нужно? А затем, что каждый день на склад приходуется и расходуется далеко не вся номенклатура, что есть на складе. Ну а при подсчете перетекающих  итогов мы должны гарантированно найти эту номенклатуру на нашем складе, даже если ее поступлений или отгрузки в этот день небыло.

   В 4 запросе происходит сворачивание таблицы исходных данных и дополнений, так что поля период, склад и номенклатура становятся уникальными.

   Наконец в 5 запросе происходит собственно расчет перетекающих остатков на начало и на конец дня.  Язык запросов 1С не имеет операторов для того чтобы взять сумму из предыдущий строки и использовать ее в следующей. Но можно рассчитать накопительные итоги.  Для этого мы склеиваем полученную после 4 запроса таблицу  саму с собой

ВТ_ПодготовленаяТаблица как ПодготовленаяТаблица
Левое Соединение ВТ_ПодготовленаяТаблица как КонОстаток
ПО  (ПодготовленаяТаблица.Склад = КонОстаток.Склад
     И  ПодготовленаяТаблица.Номенклатура= КонОстаток.Номенклатура
     И  КонОстаток.Период<=ПодготовленаяТаблица.Период )

Таким образом, что к каждому варианта склада и номенклатуры подсоединяются обороты  за все дни от текущего до начала отчетного периода. Просуммировав остаток на начало отчетного периода  и обороты за все дни получаем остаток на конец. Ну и остаток на начало получается обратным счетом.  

Условие в самом конце запроса убирает строки с нулевыми остатками и оборотами.

Надо заметить, что в нашем отчете использовались 2 измерения склад и номенклатура. Однако количество измерений может быть любым.  В этом случае изменятся условия группировки и соединений в запросах с 3 по 5.

P.S.  В заключение хочу сказать, что приведенная статья не претендует на оригинальность, и является одним из вариантов многочисленных статей, поясняющих, расчет накопительных итогов в запросе.

Язык запросов

См. также

SALE! %

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

12000 10000 руб.

02.09.2020    93711    477    380    

532

Нахождение уникальных наборов строк таблицы запросом

Запросы Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Решение задачи нахождения уникальных наборов строк таблицы запросом

23.07.2023    4418    tormozit    78    

38

Структура запроса

Инструментарий разработчика Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Абонемент ($m)

Обработка использует схему запроса и строит визуальную структуру источников данных в виде дерева. Пригодится при разборе прежде всего чужих, громоздких запросов и позволит быстро получить список источников данных. Единственное ограничение - текст запроса должен соответствовать метаданным текущей конфигурации.

1 стартмани

21.06.2023    4299    50    obmailok    35    

53

MS SQL Server: изучаем планы запросов

Запросы HighLoad оптимизация Запросы Бесплатно (free)

Многие знают, что для ускорения работы запроса нужно «изучить план». При этом сам план обычно обескураживает: куча разноцветных иконок и стрелочек; ничего не понятно, но очень интересно! Аналитик производительности Александр Денисов на конференции Infostart Event 2021 Moscow Premiere рассказал, как выполняется план запроса и что нужно сделать, чтобы с его помощью находить проблемы производительности.

20.06.2023    7846    Филин    37    

92

Как собрать отладчиком отдельные части запроса в один

Запросы Платформа 1С v8.3 Запросы Россия Бесплатно (free)

Подробное описание функционала загрузки данных запроса из отладчика в консоли "Анализатор сложных запросов".

21.03.2023    3475    manuel    2    

19

Все консоли запросов для 1С

Запросы Бесплатно (free)

Список всех популярных обработок.

17.03.2023    19617    kuzyara    70    

148

Обработка результатов запроса произвольными вычисляемыми полями. Обзор некоторых новых функций СКД

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных Конфигурации 1cv8 Бесплатно (free)

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

07.02.2023    4792    quazare    7    

38

Идентификатор объекта в запросе. Вы этого хотели?

Запросы Механизмы платформы 1С Платформа 1С v8.3 Запросы Бесплатно (free)

В платформе 8.3.22 появилась возможность получать идентификатор в запросе. Лично я ждал этого давно, но по итогу ждал большего. Что не так?

12.01.2023    22766    dsdred    24    

83
Оставьте свое сообщение