Вариант расчета остатков на указанную дату

© 2003 Евгений Варламов

...всем "складописателям" посвящается...

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

Подход 1: Периодическое сохранение остатков в отдельных таблицах(регистрах). Представим себе некую табличку с полями Дата/КодТовара/Остаток. Конечно, схема эта упрощена, ведь таким образом мы можем получить данные лишь на начало/конец некоторой даты, но смысл метода понятен. Конкретные значения заносятся в табличку с определенной периодичностью и для дальнейших расчетов и используются. Т.е. если на конец дня 1 мая товар с кодом 1 присутствовал в количестве N шт., то для расчета остатков на 10 мая необходимо расчитать его приходы и расходы лишь за этот период.

Видимые трудности возникают при вводе документов "задним числом". То есть когда фактуру недельной давности нашли при генуборке или поставщик наконец привез давно обещанные документы... Да и просто бывает, когда изменить чего-нибудь старое НАДО...

Так вот, что произойдет с заботливо сохраненными вами остатками 1 мая, если "нашелся" документ за 25 января? Ну, понятно, остатки надо пересчитывать. А если после этого промежуточные данные в табличку заносились уже не раз? Судя по всему пересчитывать надо и их. Проведение такого документа может занять...достаточное время. А если произойдет сбой при записи, то понять на какой момент данные корректны, а на какой нет будет довольно сложно(невозможно?). Ну и грозит это полным пересчетом сохраненных в этой самой табличке данных.

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

Подход 2: Промежуточные остатки вообще не сохраняются. Все что имеется, это таблица приходов-расходов(или проводок) и остатки на текущий или начальный момент. Остальное можно рассчитать.

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

А) Tovar - Таблица товаров с текущими остатками:

CREATE TABLE TOVAR (
  IDTOVAR INTEGER NOT NULL, //primary key
  TOVARNAME VARCHAR(80) NOT NULL,
  TECOST FLOAT ); //текущий остаток

Б) Doc -Таблица документов (заголовков документов с датами):

CREATE TABLE DOC (
  ID INTEGER NOT NULL, //primary key номер документа
  DATEDOC DATE, //дата документа
  IDCLIENT INTEGER NOT NULL,//код клиента
  SUMDOC FLOAT NOT NULL);//сумма документа

В) TovDvij - таблица товарных строк: содержимое конкретного документа.

CREATE TABLE TOVDVIJ (
  IDDV INTEGER NOT NULL, //primary key
  IDDOC INTEGER NOT NULL,//код документа
  IDTOVAR INTEGER NOT NULL,//код товара
  KOLVO FLOAT NOT NULL); //количество товара в документе(+ приход, - расход)

Первый алгоритм, пришедший в голову, выглядит так: пробегая по табличке товаров, для каждого товара высчитываем сумму приходов и расходов за период между текущей датой и датой D1+1. Разница между текущим остатком и вычисленным количеством есть искомая величина для конкретного товара. т.е.вырисовывается процедура такого вида:

for select T.idtovar,T.tecost from TOVAR T into :IDTOVAR,:tecost
do begin
  KOL=0;
  select sum(TD.kolvo) from tovdvij TD
  join doc d on TD.iddoc=D.id and D.datedoc>:D1
  where TD.idtovar=:idtovar
  into :KOL;
  if (:TECOST is NULL) then TECOST=0;
  OSTATOK=:TECOST-:KOL;
  suspend;
end

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

Execute time = 48s 634ms
чтений TOVDVIJ - 66 138
чтений DOC - 19 628 302

...хм-хм, не радует. Отказываемся от этого порочного метода.

Попробуем несколько модифицировать процедуру. Объединим цикл FOR SELECT и запрос по движению и получим процедуру следующего вида:

for select T.IDTOVAR,T.tecost,sum(TD.kolvo) from TOVAR T
left join tovdvij TD on TD.idtovar=T.idtovar
join doc d on TD.iddoc=D.id and D.datedoc>:D1
group by T.IDTOVAR,T.tecost
  into :idtovar,:tecost,:KOL
  do begin
    if (:kol is NULL) then KOL=0;
    if (:TECOST is NULL) then TECOST=0;
    OSTATOK=:TECOST-:KOL;
    suspend;
  end

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

Execute time = 12 m 30s 926ms

Ужастно... Думаем дальше.

Без особых затрат можно вывести изменение количества того товара, по которому это движение было. Без особых опять же затрат можно получить и текущие остатки ВСЕХ товаров (уж коли они хранятся прямо в таблице TOVAR). И еще: если движения по товару не было, то и остаток его не изменился. Вооружившись этими соображениями, объединим результаты двух запросов(по остаткам и по движению) в один:

select T.idtovar as IDTOVAR, cast(T.tecost as Float)as KOL, 1 as IND
from TOVAR T
  union
select TD.idtovar as IDTOVAR,cast(sum(TD.kolvo) as Float)as KOL, 0 as IND
from doc d
join tovdvij TD on TD.iddoc=D.id
where D.datedoc>:D1
group by TD.idtovar
order by 1,3

поле IND добавлено, чтобы понять из какого запроса получена результатирующая строка. Оно нам потом понадобится. Итак, время исполнения данного запроса уже приемлимо (Execute time = 3s 495ms), а результатирующий набор выглядит примерно так

IdTovar	KOL	IND
1	0	1
2	10	0
2	5	1
3	1	1

Видим, что если в наборе появляется строка с IND=0, то движение по этому товару было и количество, на которое изменился остаток прописано в поле KOL. Таким образом, нам необходимо пробежать по результату такого запроса и выполнить следующие действия:

• если это строка с IND=0 то сохраним поле KOL во временной переменной TEMPKOL и перейдем к следующей строке.
• если это строка с IND=1 то остаток на запрашиваемую дату равен величине, содержащейся в поле KOL за минусом величины TEMPKOL. Выведем получившееся значение в результатирующий набор, обнулим TEMPKOL и перейдем к следующей строке.

Получившаяся в результате процедура примет следующий вид:

TEMPKOL=0;
for select T.idtovar as IDTOVAR, cast(T.tecost as Float)as KOL,1 as IND
from TOVAR T
union
select TD.idtovar as IDTOVAR,cast(sum(TD.kolvo) as Float)as KOL,0 as IND
from doc d
join tovdvij TD on TD.iddoc=D.id
where D.datedoc>:D1
group by TD.idtovar
order by 1,3
into :idtovar,:kol,:ind
  do begin
    if (:KOL is NULL) then KOL=0;
    if (:ind=0) then TEMPKOL=:kol;
  else
  begin
    OSTATOK=:kol-:tempkol;
    TEMPKOL=0;
    suspend;
  end
end

Тестируем процедуру, получаем вполне приемлимое время выполнения:

Execute time = 3s 634ms
чтений TOVDVIJ - 199 222
чтений DOC - 4792

Коротко о тестовой базе:
  Таблица TOVAR -около 30 000 записей
  Таблица TOVDVIJ -около 4 000 000 записей
  Таблица DOC -около 150 000 записей

Используемый сервер : Firebird 1.5 RC6

Copyright© 2003 Варламов Евгений Владиленович  Специально для Delphi Plus