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