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

Программирование - Практика программирования

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

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

Вашему вниманию представляется маленькое упражнение с языком запросов 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.  В заключение хочу сказать, что приведенная статья не претендует на оригинальность, и является одним из вариантов многочисленных статей, поясняющих, расчет накопительных итогов в запросе.

6

См. также

Специальные предложения

Избранное Подписка Сортировка: Древо
В этой теме еще нет сообщений.
Оставьте свое сообщение