Получение остатков и оборотов по каждому товару на каждый день запросом

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

Вся соль задачи состоит в том, что обращения к виртуальной таблице остатков и оборотов в запросе не достаточно, так как эта виртуальная таблица содержит периоды, по которым были движения, если так можно выразиться (см. рисунок 1). Иными словами, если Товар №1 поступил в магазин в начале недели (в понедельник) в количестве 5ти штук и за неделю не было ни одной продажи, то виртуальная таблица по этому товару за эту неделю выдаст лишь одну запись за понедельник, которая будет отражать остатки на начало и конец дня, приход (+5), расход (0) и оборот (0).

Данную задачу я решил разделить на несколько подзадач, ведь слона легче есть кусками, чем сразу целиком. Получились такие подзадачи:

Получить таблицу с датами на каждый день в пределах задаваемого периода
Получить таблицу остатков и оборотов по каждому товару за период с периодичностью ДЕНЬ
Как-то соединить полученные таблицы в первых двух подзадачах для получения желаемого результата

Первая подзадача решается довольно просто. Создаем таблицу значений с колонкой Дата. Зная границы периода, в цикле заполняем таблицу датами на каждый день.

  
    ДатыНаКаждыйДень = Новый ТаблицаЗначений;
    ДатыНаКаждыйДень.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата", , , Новый КвалификаторыДаты(ЧастиДаты.Дата)));
  
    НачальнаяДата = Объект.НачалоПериода;
    Пока НачальнаяДата <= Объект.КонецПериода Цикл 
	
      НоваяСтрока = ДатыНаКаждыйДень.Добавить();
      НоваяСтрока.Дата = НачальнаяДата;
    
      НачальнаяДата = НачальнаяДата + 86400;
    
    КонецЦикла;

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

  
    Запрос = Новый Запрос;
    Запрос.Текст = 
      "ВЫБРАТЬ
      |  ДатыНаКаждыйДень.Дата КАК Дата
      |ПОМЕСТИТЬ ВТ_ДатыНаКаждыйДень
      |ИЗ
      |  &ДатыНаКаждыйДень КАК ДатыНаКаждыйДень
      |;
      |
      |////////////////////////////////////////////////////////////////////////////////
      |ВЫБРАТЬ
      |  ВТ_ДатыНаКаждыйДень.Дата
      |ИЗ
      |  ВТ_ДатыНаКаждыйДень КАК ВТ_ДатыНаКаждыйДень";
  
    Запрос.УстановитьПараметр("ДатыНаКаждыйДень", ДатыНаКаждыйДень);

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

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

  
    Запрос = Новый Запрос;
    Запрос.Текст = 
      "ВЫБРАТЬ
      |  Товары.Ссылка КАК Товар,
      |  ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.Период, &НачалоПериода) КАК Период,
      |  ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоНачальныйОстаток, 0) КАК КоличествоНачальныйОстаток,
      |  ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоКонечныйОстаток, 0) КАК КоличествоКонечныйОстаток,
      |  ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоОборот, 0) КАК КоличествоОборот,
      |  ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоПриход, 0) КАК КоличествоПриход,
      |  ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоРасход, 0) КАК КоличествоРасход
      |ИЗ
      |  Справочник.Номенклатура КАК Товары
      |    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, ДвиженияИГраницыПериода, ) КАК ОстаткиТоваровОстаткиИОбороты
      |      ПО Товары.Ссылка = ОстаткиТоваровОстаткиИОбороты.Номенклатура
      |ГДЕ
      |  НЕ Товары.ЭтоГруппа
      |
      |УПОРЯДОЧИТЬ ПО
      |  Товары.Наименование,
      |  Период";
  
    Запрос.УстановитьПараметр("НачалоПериода", Объект.НачалоПериода);
    Запрос.УстановитьПараметр("КонецПериода", КонецДня(Объект.КонецПериода));

Немножко пояснений. Допустим, пользователь указал период 01.01.2020 - 31.12.2020 в форме обработки, то есть осуществляется ввод дат без времени. В системе 1С такие даты будут храниться как 01.01.2020 00:00 и 31.12.2020 00:00, время присутствует и оно нулевое, соответственно остатки и обороты мы получим за период 01.01.2020 00:00 - 31.12.2020 00:00, что неправильно, т.к. данные за 31 декабря не попадут в результат запроса. Для этого к концу периода применяется функция КонецДня(), что бы получить остатки и обороты за период 01.01.2020 00:00 - 31.12.2020 23:59, или можно воспользоваться встроенной функцией КОНЕЦПЕРИОДА() языка запросов. Для параметра Периодичность виртуальной таблицы указана периодичность День, что и требуется в задаче, для параметра Метод дополнения периодов указано значение ДвиженияИГраницыПериода, что бы в результате запроса всегда присутствовали периоды на начало и конец заданного периода. Результат запроса представлен на рисунке 1.

Рисунок 1. Остатки и обороты по товару "Стол дуб." за 2015 год

А вот и самое интересное, решение третьей подзадачи. Соединяем таблицы первой и второй подзадачи таким образом, что бы в результате каждому дню соответствовал необходимый период остатков и оборотов, результат помещаем во временную таблицу (см. рисунок 2).

  
    |ВЫБРАТЬ
    |   ВТ_ДатыНаКаждыйДень.Дата КАК День,
    |   ВТ_ОстаткиИОбороты.Товар КАК Товар,
    |   МАКСИМУМ(ВТ_ОстаткиИОбороты.Период) КАК ПериодОстатковИОборотов
    |ПОМЕСТИТЬ ВТ_СоответствиеДатыПериоду
    |ИЗ
    |   ВТ_ДатыНаКаждыйДень КАК ВТ_ДатыНаКаждыйДень
    |     ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК ВТ_ОстаткиИОбороты
    |     ПО (ВТ_ОстаткиИОбороты.Период <= ВТ_ДатыНаКаждыйДень.Дата)
    |
    |СГРУППИРОВАТЬ ПО
    |   ВТ_ДатыНаКаждыйДень.Дата,
    |   ВТ_ОстаткиИОбороты.Товар

Рисунок 2. Промежуточная таблица с ключом соединения по товару и периоду

Полученную временную таблицу соединяем с таблицей второй подзадачи по составному ключу {Товар : ПериодОстатковИОборотов}, на поля выборки НачальныйОстаток, КонечныйОстаток, Приход, Расход и Оборот накладываем условие по периоду остатков и оборотов для правильного расчета значений (см. последний запрос в пакете запросов), в итоге получаем остатки и обороты на каждый день.

Конечный текст пакета запросов:

  
      Запрос = Новый Запрос;
      Запрос.Текст = 
        "ВЫБРАТЬ
        |   ДатыНаКаждыйДень.Дата КАК Дата
        |ПОМЕСТИТЬ ВТ_ДатыНаКаждыйДень
        |ИЗ
        |   &ДатыНаКаждыйДень КАК ДатыНаКаждыйДень
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |   Товары.Ссылка КАК Товар,
        |   ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.Период, &НачалоПериода) КАК Период,
        |   ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоНачальныйОстаток, 0) КАК КоличествоНачальныйОстаток,
        |   ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоКонечныйОстаток, 0) КАК КоличествоКонечныйОстаток,
        |   ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоОборот, 0) КАК КоличествоОборот,
        |   ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоПриход, 0) КАК КоличествоПриход,
        |   ЕСТЬNULL(ОстаткиТоваровОстаткиИОбороты.КоличествоРасход, 0) КАК КоличествоРасход
        |ПОМЕСТИТЬ ВТ_ОстаткиИОбороты
        |ИЗ
        |   Справочник.Номенклатура КАК Товары
        |     ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, ДвиженияИГраницыПериода, ) КАК ОстаткиТоваровОстаткиИОбороты
        |     ПО Товары.Ссылка = ОстаткиТоваровОстаткиИОбороты.Номенклатура
        |ГДЕ
        |   НЕ Товары.ЭтоГруппа
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |   ВТ_ДатыНаКаждыйДень.Дата КАК День,
        |   ВТ_ОстаткиИОбороты.Товар КАК Товар,
        |   МАКСИМУМ(ВТ_ОстаткиИОбороты.Период) КАК ПериодОстатковИОборотов
        |ПОМЕСТИТЬ ВТ_СоответствиеДатыПериоду
        |ИЗ
        |   ВТ_ДатыНаКаждыйДень КАК ВТ_ДатыНаКаждыйДень
        |     ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК ВТ_ОстаткиИОбороты
        |     ПО (ВТ_ОстаткиИОбороты.Период <= ВТ_ДатыНаКаждыйДень.Дата)
        |
        |СГРУППИРОВАТЬ ПО
        |   ВТ_ДатыНаКаждыйДень.Дата,
        |   ВТ_ОстаткиИОбороты.Товар
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |   ВТ_СоответствиеДатыПериоду.День КАК День,
        |   ВТ_ОстаткиИОбороты.Период КАК Период,
        |   ВТ_ОстаткиИОбороты.Товар КАК Товар,
        |   ВЫБОР
        |     КОГДА ВТ_СоответствиеДатыПериоду.День = ВТ_ОстаткиИОбороты.Период
        |       ТОГДА ВТ_ОстаткиИОбороты.КоличествоНачальныйОстаток
        |     ИНАЧЕ ВТ_ОстаткиИОбороты.КоличествоКонечныйОстаток
        |   КОНЕЦ КАК НачальныйОстаток,
        |   ВТ_ОстаткиИОбороты.КоличествоКонечныйОстаток КАК КонечныйОстаток,
        |   ВЫБОР
        |     КОГДА ВТ_СоответствиеДатыПериоду.День = ВТ_ОстаткиИОбороты.Период
        |       ТОГДА ВТ_ОстаткиИОбороты.КоличествоОборот
        |     ИНАЧЕ 0
        |   КОНЕЦ КАК Оборот,
        |   ВЫБОР
        |     КОГДА ВТ_СоответствиеДатыПериоду.День = ВТ_ОстаткиИОбороты.Период
        |       ТОГДА ВТ_ОстаткиИОбороты.КоличествоПриход
        |     ИНАЧЕ 0
        |   КОНЕЦ КАК Приход,
        |   ВЫБОР
        |     КОГДА ВТ_СоответствиеДатыПериоду.День = ВТ_ОстаткиИОбороты.Период
        |       ТОГДА ВТ_ОстаткиИОбороты.КоличествоРасход
        |     ИНАЧЕ 0
        |   КОНЕЦ КАК Расход
        |ИЗ
        |   ВТ_СоответствиеДатыПериоду КАК ВТ_СоответствиеДатыПериоду
        |     ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК ВТ_ОстаткиИОбороты
        |     ПО ВТ_СоответствиеДатыПериоду.ПериодОстатковИОборотов = ВТ_ОстаткиИОбороты.Период
        |       И ВТ_СоответствиеДатыПериоду.Товар = ВТ_ОстаткиИОбороты.Товар
        |
        |УПОРЯДОЧИТЬ ПО
        |   ВТ_СоответствиеДатыПериоду.Товар.Наименование,
        |   День";

      Запрос.УстановитьПараметр("ДатыНаКаждыйДень",   ДатыНаКаждыйДень);
      Запрос.УстановитьПараметр("НачалоПериода",    Объект.НачалоПериода);
      Запрос.УстановитьПараметр("КонецПериода",     КонецДня(Объект.КонецПериода));

      РезультатЗапроса = Запрос.Выполнить();

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

Поиск по этому блогу

АйТиБложик

Получение остатков и оборотов по каждому товару на каждый день запросом

Комментарии

Отправить комментарий