Некоторые изыскания на тему разработки складских программ

Оговорюсь сразу, речь пойдет о формировании динамического склада. Формирование статического склада с его отрезкой по периодам чаще всего вещь неприменимая а иногда и даже вредная поскольку невозможно в сию секунду узнать точное кол-во товара в остатках, процент "затарки" склада и как следствие кол во денег лежащих так называемым "мертвым грузом" (да простят меня экономисты ).

Все скрипты использованные в статье проверены на работоспособность с использованием Yaffil SQL Server 872b.

Допустим, имеем в наличии типичную структуру складского хозяйства:

Таблица товара.

CREATE TABLE TOVAR (
    ID INTEGER NOT NULL,
    NAIM VARCHAR(40),
    PRICE_Z NUMERIC(15,2),
    PRICE_R NUMERIC(15,2))

где PRICE_Z– стоимость закупки товара, PRICE_R – стоимость продажи товара и NAIM соответственно наименование.

Таблица накладных.

CREATE TABLE NAKLAD (
    ID INTEGER NOT NULL,
    TIP SMALLINT NOT NULL,
    SENDER INTEGER NOT NULL,
    TOV INTEGER NOT NULL,
    PRICE_R NUMERIC(15,2),
    KOL NUMERIC(15,4))

где KOL – кол-во отпущенного/полученного товара, SENDER – отправитель/получатель товара, TOV – ссылка на ID из таблицы TOVAR и TIP – признак типа накладной.

На поле TIP, позволю себе, остановится чуть подробнее. В большинстве случаев TIP представляет собой всего 2 варианта, например
0 – приходные накладные
1 – расходные накладные

Замечу, что на практике чаще всего к этим накладным приходится добавлять так называемый форс-мажор – возвратные накладные (просроченный срок реализации, выход из моды и т.д.) и накладные на продажу, то есть проданный товар. Исходя из этого, получаем:
    0 – приходные накладные
    1 – расходные накладные
    2 – возвратные накладные
    3 – накладные на продажу

Хранение накладных разных типов в разных таблицах сразу же приходит на ум, но если для создания статического склада это большого значения не имеет, то для динамики это накладывает большие ограничения по быстродействию. Правда, возможен вариант использования VIEW, но зачем загружать сервер лишней работой.

Итак, после некоторых размышлений приходим к следующей хранимой процедуре для создания склада:

CREATE PROCEDURE GET_SKLAD 
RETURNS (
    ID INTEGER,
    KOL NUMERIC(15,4))
AS
DECLARE VARIABLE TMP_KOL NUMERIC(15,4);
DECLARE VARIABLE TMP_KOL_P NUMERIC(15,2);
DECLARE VARIABLE TMP_KOL_R NUMERIC(15,2);
DECLARE VARIABLE TMP_KOL_V NUMERIC(15,2);
DECLARE VARIABLE TMP_KOL_I NUMERIC(15,2);
DECLARE VARIABLE TMP_TIP INTEGER;
begin
For
  Select T.ID from TOVAR T
  Order By T.NAIM
  INTO :ID
 Do
  Begin
  TMP_KOL_P = 0;
  TMP_KOL_R = 0;
  TMP_KOL_V = 0;
  TMP_KOL_I = 0;
  For
   Select TIP, SUM(KOL) From NAKLAD
   where tov = :id
   Group by TIP
   INTO :TMP_TIP, :tmp_kol
  do
  begin
   if (:TMP_TIP = 0) then TMP_KOL_P = :TMP_KOL;
   if (:TMP_TIP = 1) then TMP_KOL_R = :TMP_KOL;
   if (:TMP_TIP = 2) then TMP_KOL_V = :TMP_KOL;
   if (:TMP_TIP = 3) then TMP_KOL_I = :TMP_KOL;
  END
   KOL = :TMP_KOL_P - :tmp_kol_r + :tmp_kol_v;
  SUSPEND;
  END
End

При добавлении соответствующих индексов получаем, на 1000000 записей время отработки процедуры около 5 сек.

Уже хорошо, но как говорится, нет предела совершенству. Мыслим дальше. На каждую запись в таблице TOVAR у нас возвращается 4 записи из таблицы NAKLAD (по всем 4м тапам определенных накладных) вне зависимости от того есть ли накладные с этими типами по данному товару или нет. Попробуем хранить кол-во товара в накладной по каждому типу в отдельности.

ALTER TABLE NAKLAD ADD KOL_P NUMERIC(15,4)
ALTER TABLE NAKLAD ADD KOL_R NUMERIC(15,4)
ALTER TABLE NAKLAD ADD KOL_V NUMERIC(15,4)
ALTER TABLE NAKLAD ADD KOL_I NUMERIC(15,4)

где KOL_P – кол-во товара по приходу, KOL_R – кол-во товара по расходу, KOL_V – кол-во товара по возврату и KOL_I кол-во товара по продаже (инкассации).

Тогда вышеизложенная процедура примет вид:

CREATE PROCEDURE GET_SKLAD_2 
RETURNS (
    ID INTEGER,
    KOL NUMERIC(15,4))
AS
DECLARE VARIABLE TMP_KOL NUMERIC(15,4);
DECLARE VARIABLE TMP_KOL_P NUMERIC(15,2);
DECLARE VARIABLE TMP_KOL_R NUMERIC(15,2);
DECLARE VARIABLE TMP_KOL_V NUMERIC(15,2);
DECLARE VARIABLE TMP_KOL_I NUMERIC(15,2);
DECLARE VARIABLE TMP_TIP INTEGER;
begin
For
  Select T.ID from TOVAR T
  Order By T.NAIM
  INTO :ID
 Do
  Begin
  TMP_KOL_P = 0;
  TMP_KOL_R = 0;
  TMP_KOL_V = 0;
  TMP_KOL_I = 0;
  For
   Select SUM(KOL_P), SUM(KOL_R), SUM(KOL_V) From NAKLAD
   where tov = :id
   INTO :TMP_KOL_P, :TMP_KOL_R, :TMP_KOL_V
  do
   KOL = :TMP_KOL_P - :tmp_kol_r + :tmp_kol_v;
  SUSPEND;
  END
End

При проверке по тому же 1000000 записей получаем примерное время выполнения 4,2 – 4,4 сек. Время уменьшилось, но не на много. Мыслим дальше. Попробуем убрать циклический алгоритм в конструкции Select SUM(KOL_P), SUM(KOL_R), SUM(KOL_V) From NAKLAD. Для этого попробуем хранить наше кол-во товара в удобном для нас виде, т.е. если это приход или возврат то это (+) если расход или продажа то это (-).

Возвращаем таблицу NAKLAD в исходное состояние.

ALTER TABLE NAKLAD DROP KOL_P
ALTER TABLE NAKLAD DROP KOL_R
ALTER TABLE NAKLAD DROP KOL_V
ALTER TABLE NAKLAD DROP KOL_I

Добавляем вспомогательное поле

ALTER TABLE NAKLAD ADD KOL_REZ NUMERIC(15,4)

И добавляем таблице NAKLAD триггер

CREATE TRIGGER NAKLAD_KOL_REZ FOR NAKLAD
ACTIVE AFTER INSERT OR UPDATE POSITION 0
AS
begin
  if (new.TIP = 0) then new.KOL_REZ = new.KOL;
  if (new.TIP = 1) then new.KOL_REZ = new.KOL * -1;
  if (new.TIP = 2) then new.KOL_REZ = new.KOL;
  if (new.TIP = 3) then new.KOL_REZ = new.KOL * -1;
end

Теперь поле KOL_REZ хранит для нас данные в так сказать логическом виде, и мы можем переписать нашу процедуру следующим образом:

CREATE PROCEDURE GET_SKLAD_3 
RETURNS (
    ID INTEGER,
    KOL NUMERIC(15,4))
AS
begin
For
  Select T.ID from TOVAR T
  Order By T.NAIM
  INTO :ID
 Do
  Begin
  For
   Select SUM(KOL_REZ) From NAKLAD
   where tov = :id and tip < 3
   INTO  :KOL
  do
  begin
  END
  SUSPEND;
  END
End

При проверке с тем же 1000000 записей наша процедура отрабатывает 2,4 – 2,6 сек. Скорость вполне приемлема. Дальнейшее увеличение скорости лично я считаю (на данный момент) нецелесообразным. Дело в том, что если в справочнике товаров будет порядка 8000 -10000 записей то пересылка такого объема информации по сети, даже при 100 Mbit в клиентский компьютер занимает, порядка 2-3 сек и любое повышение производительности процедуры на сервере будет съедено пересылкой данных через сеть.

Внимательным читателям объясню назначение типа накладной (продажа). Дело в том, что если вести и эту накладную (в идеале она должна создаваться сама по средством периодической или постоянной связи с кассовым аппаратом) путем не сложных манипуляций с SQL мы можем получить так называемый склад по филиалу.

CREATE PROCEDURE GET_SKLAD_FIL (
    FIL INTEGER)
RETURNS (
    ID INTEGER,
    KOL NUMERIC(15,4))
AS
begin
For
 Select N.TOV from NAKLAD N
  Where SENDER = :FIL
   Group By N.TOV
 INTO :ID
 Do
  Begin
   Select SUM(KOL_REZ * -1) From NAKLAD
   where tov = :id and SENDER = :fil and tip > 0
   INTO :kol;
     SUSPEND;
  END
End

За сим позвольте откланяться...