gifts2017

Нарастающие итоги. Объединение двух таблиц с нарастающими итогами.

Опубликовал Петр Базелюк (pbazeliuk) в раздел Программирование - Практика программирования

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

Что такое нарастающие итоги можно посмотреть здесь: http://infostart.ru/public/61295/

Постановка задачи.

Менеджерам компании понадобилось знать, что нужно заказать у поставщиков для клиентов и видеть что уже едет под заказы клиентов. Они захотели использовать метод ФИФО, то есть кто раньше заказал тому и раньше приедет товар, дата отгрузки товаров и дата поставки товаров из заказов не учитывается. Так же считается что все заказы имеют уникальную дату. Операции учета ведутся в Управлении торговлей версии 11.0.9.8.

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

 

1. Для начало нужно сформировать список номенклатуры, которую необходимо заказать и которая ожидается к поступлению. Для чего это нужно? - Справочник номенклатуры содержит более 100 тыс. наименований и нужно как-то ограничить выборку из виртуальных таблиц регистров.


ВЫБРАТЬ
         Номенклатура
ПОМЕСТИТЬ СпрНоменклатура
ИЗ
         РегистрНакопления.СвободныеОстатки.Остатки
ГДЕ
         ВНаличииОстаток - ВРезервеОстаток < 0
ОБЪЕДИНИТЬ
ВЫБРАТЬ
         Номенклатура
ИЗ
         РегистрНакопления.ЗаказыПоставщикам.Остатки
ИНДЕКСИРОВАТЬ ПО
         Номенклатура;

Конечно можно было бы использовать РегистрНакопления.ЗаказыКлиентов.Остатки вместо РегистрНакопления.СвободныеОстатки.Остатки, но ведь нужно же выбрать только номенклатуру, которую нужно заказать.

 

2. Далее выбираем остатки заказов поставщиков и клиентов.


ВЫБРАТЬ
         ОстаткиЗаказов.ЗаказПоставщику.Дата КАК Период,
         ОстаткиЗаказов.Номенклатура,
         ОстаткиЗаказов.ЗаказПоставщику,
         ОстаткиЗаказов.КОформлениюОстаток
ПОМЕСТИТЬ ОстаткиЗаказовПоставщиков
ИЗ
         РегистрНакопления.ЗаказыПоставщикам.Остатки(, Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиЗаказов
;
ВЫБРАТЬ
         ОстаткиЗаказов.ЗаказКлиента.Дата КАК Период,
         ОстаткиЗаказов.Номенклатура,
         ОстаткиЗаказов.ЗаказКлиента,
         ОстаткиЗаказов.ЗаказаноОстаток
ПОМЕСТИТЬ ОстаткиЗаказовКлиентов
Из
         РегистрНакопления.ЗаказыКлиентов.Остатки(, ЗаказКлиента.Статус НЕ В (ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.НеСогласован), ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.Согласован))
                                                                          И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиЗаказов
;

Используем статусы заказов клиентов для отсечения заказов которые не делают движений в РегистрНакопления.СвободныеОстатки.Остатки.


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


ВЫБРАТЬ
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказПоставщику,
         ОстаткиПоЗаказам.КОформлениюОстаток,
         СУММА(ОстаткиПоЗаказамКопия.КОформлениюОстаток) КАК СуммаПосле,
         СУММА(ОстаткиПоЗаказамКопия.КОформлениюОстаток) - ОстаткиПоЗаказам.КОформлениюОстаток КАК СуммаДо
ПОМЕСТИТЬ КОформлениюНарастающие
ИЗ
         ОстаткиЗаказовПоставщиков КАК ОстаткиПоЗаказам
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиЗаказовПоставщиков КАК ОстаткиПоЗаказамКопия
ПО  ОстаткиПоЗаказам.Номенклатура = ОстаткиПоЗаказамКопия.Номенклатура
И    ОстаткиПоЗаказам.Период >= ОстаткиПоЗаказамКопия.Период
СГРУППИРОВАТЬ ПО
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказПоставщику,
         ОстаткиПоЗаказам.КОформлениюОстаток
;
ВЫБРАТЬ
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказКлиента,
         ОстаткиПоЗаказам.ЗаказаноОстаток,
         СУММА(ОстаткиПоЗаказамКопия.ЗаказаноОстаток) КАК СуммаПосле,
         СУММА(ОстаткиПоЗаказамКопия.ЗаказаноОстаток) - ОстаткиПоЗаказам.ЗаказаноОстаток КАК СуммаДо
ПОМЕСТИТЬ ОстаткиНарастающие
ИЗ
         ОстаткиЗаказовКлиентов КАК ОстаткиПоЗаказам

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиЗаказовКлиентов КАК ОстаткиПоЗаказамКопия
ПО  ОстаткиПоЗаказам.Номенклатура = ОстаткиПоЗаказамКопия.Номенклатура
И    ОстаткиПоЗаказам.Период >= ОстаткиПоЗаказамКопия.Период
СГРУППИРОВАТЬ ПО
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказКлиента,
         ОстаткиПоЗаказам.ЗаказаноОстаток
;


4. Теперь можно было бы попытаться объединить эти таблицы с помощью Полного, Левого и прочего соединения, но когда таблиц нарастающих итогов больше 2 это уже очень проблематично. Но есть довольно простое решение - достаточно выбрать номенклатуру с уникальными значениями "СуммаПосле" из нарастающих итогов обеих таблиц и потом к этой таблице присоединить все что нужно. 


ВЫБРАТЬ
         ОстаткиНарастающие.Номенклатура,
         ОстаткиНарастающие.СуммаПосле КАК Точка
ПОМЕСТИТЬ ТочкиПересечений
ИЗ
         ОстаткиНарастающие
ОБЪЕДИНИТЬ
ВЫБРАТЬ
         Номенклатура,
         СуммаПосле
ИЗ
         КОформлениюНарастающие
;


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


ВЫБРАТЬ
         ТочкиПересечений.Номенклатура,
         ТочкиПересечений.Точка,

         КОформлениюНарастающие.ЗаказПоставщику,

         ОстаткиНарастающие.ЗаказКлиента,
         ОстаткиНарастающие.ЗаказаноОстаток,
         ОстаткиНарастающие.СуммаПосле,
         ОстаткиНарастающие.СуммаДо

ПОМЕСТИТЬ ОстаткиПоЗаказамНаростающие 
ИЗ
         ТочкиПересечений КАК ТочкиПересечений

ЛЕВОЕ СОЕДИНЕНИЕ КОформлениюНарастающие КАК КОформлениюНарастающие
ПО КОформлениюНарастающие.Номенклатура = ТочкиПересечений.Номенклатура
И    КОформлениюНарастающие.СуммаПосле >= ТочкиПересечений.Точка
И    КОформлениюНарастающие.СуммаДо < ТочкиПересечений.Точка
 
 
ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиНарастающие КАК ОстаткиНарастающие
ПО ОстаткиНарастающие.Номенклатура = ТочкиПересечений.Номенклатура
И    ОстаткиНарастающие.СуммаПосле >= ТочкиПересечений.Точка
И    ОстаткиНарастающие.СуммаДо < ТочкиПересечений.Точка
;


6. Следующие 2 запроса формирует решение поставленной задачи с дополнительными полями "ВНаличии", "ВПеремещениях", "ВЗаказахПоставщику" и "НужноЗаказать".


ВЫБРАТЬ
         СпрНоменклатура.Номенклатура,
         IsNull(ОстаткиТоваров.ВНаличииОстаток, 0) КАК ВНаличии,
         IsNull(ОстаткиТоваров.ВРезервеОстаток, 0) КАК ВРезерве,
         IsNull(ТоварыВДвиженииПеремещения.КПоступлениюОстаток, 0) КАК ВПеремещениях,
         IsNull(ТоварыВДвиженииЗаказы.КПоступлениюОстаток, 0) КАК ВЗаказахПоставщику,
         IsNull(ОстаткиТоваров.ВНаличииОстаток, 0)
      + IsNull(ТоварыВДвиженииПеремещения.КПоступлениюОстаток, 0)
      + IsNull(ТоварыВДвиженииЗаказы.КПоступлениюОстаток, 0) КАК Доступно
 
ПОМЕСТИТЬ ТаблицаНаличия
ИЗ
         СпрНоменклатура КАК СпрНоменклатура

ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки(,Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиТоваров
ПО ОстаткиТоваров.Номенклатура = СпрНоменклатура.Номенклатура
 
 
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКПоступлению.Остатки(, ТипЗначения(ДокументПоступления) <> Тип(Документ.ЗаказПоставщику)
                                                                                                     И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ТоварыВДвиженииПеремещения
ПО ТоварыВДвиженииПеремещения.Номенклатура = СпрНоменклатура.Номенклатура

ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКПоступлению.Остатки(, ТипЗначения(ДокументПоступления) = Тип(Документ.ЗаказПоставщику)
                                                                                                     И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ТоварыВДвиженииЗаказы
ПО ТоварыВДвиженииЗаказы.Номенклатура = СпрНоменклатура.Номенклатура
;
ВЫБРАТЬ
        ОстаткиПоЗаказам.ЗаказПоставщику,
        ОстаткиПоЗаказам.ЗаказКлиента,
        ОстаткиПоЗаказам.Номенклатура,
        ОстаткиПоЗаказам.ЗаказаноОстаток КАК Количество,
        ТаблицаНаличия.ВНаличии,
        ТаблицаНаличия.ВПеремещениях,
        ТаблицаНаличия.ВЗаказахПоставщику,
        ВЫБОР
                КОГДА ТаблицаНаличия.Доступно > ОстаткиПоЗаказам.СуммаПосле
                ТОГДА 0
                КОГДА ТаблицаНаличия.Доступно > ОстаткиПоЗаказам.СуммаДо
                ТОГДА ОстаткиПоЗаказам.СуммаПосле - ТаблицаНаличия.Доступно
                ИНАЧЕ ОстаткиПоЗаказам.ЗаказаноОстаток
        КОНЕЦ КАК НужноЗаказать
ИЗ
        ОстаткиПоЗаказамНаростающие КАК ОстаткиПоЗаказам

ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаНаличия КАК ТаблицаНаличия
ПО  ТаблицаНаличия.Номенклатура = ОстаткиПоЗаказам.Номенклатура
И    ТаблицаНаличия.Доступно < ОстаткиПоЗаказам.СуммаПосле
 
 
ГДЕ
        ТаблицаНаличия.Доступно - ТаблицаНаличия.ВРезерве < 0
 
УПОРЯДОЧИТЬ ПО ОстаткиПоЗаказам.Номенклатура, ОстаткиПоЗаказам.Точка
;

В результате получится что-то типа этого очета (с двумя вариантами внутри): 



Послесловие.

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

P.S. На правах рекламы. Мы так же разрабатываем отличные сайты. - www.ktc-ua.com.





Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
Отчет на СКД с двумя вариантами
.erf 7,48Kb
20.10.12
23
.erf 7,48Kb 23 Скачать

См. также

Подписаться Добавить вознаграждение
В этой теме еще нет сообщений.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа