"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

STORINGREMINDER

Входные параметры

Выходные параметры

Описание

хранящийся остаток для формы 36 общей

Определение

CREATE PROCEDURE STORINGREMINDER(
    DATE1 DATE,
    STORAGEID INTEGER,
    CLASSID INTEGER,
    PARTY_ID INTEGER,
    DOCROP INTEGER)
RETURNS (
    RESULT NUMERIC(18,0),
    INCOMEDAY NUMERIC(18,0),
    OUTCOMEDAY NUMERIC(18,0),
    REMINDER NUMERIC(18,0))
AS
DECLARE VARIABLE WEIGHT NUMERIC(18,0);
DECLARE VARIABLE WEIGHT2 INTEGER;
DECLARE VARIABLE STARTDATE DATE;
DECLARE VARIABLE INCOMEBEFORE NUMERIC(18,0);
DECLARE VARIABLE OUTCOMEBEFORE NUMERIC(18,0);
DECLARE VARIABLE INCOME NUMERIC(18,0);
DECLARE VARIABLE OUTCOME NUMERIC(18,0);
begin
  income = 0 ;
  outcome = 0;
  select parties.begindate from parties where parties.partyid = :party_id
  into :startdate;

  select sum(it.net)
  from it
    left join operation on (it.operationid = operation.operationid)
    left join quality on (it.labqualityid = quality.qualityid)
  where operation.operationsubtype = 3
    and it.partyid = :party_id
    and it.storageid = :storageid
    and it.state >= 0
    and it.parentid >= 0
    and (quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1)
  into :result;

  if (:result is null) then result = 0;

  select sum(storingreminder_weight.weight0), sum(storingreminder_weight.weight1)
  from it
    left join operation on (it.operationid = operation.operationid)
    left join quality on (it.labqualityid = quality.qualityid)
    left join storingreminder_weight(operation.operationsubtype, it.net) on (0 = 0)
  where it.regdate = :startdate
    and it.partyid = :party_id
    and it.storageid = :storageid
    and it.state >= 0
    and it.parentid >= 0
    and operation.operationsubtype in (0, 1)
    and (quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1)
  into :incomebefore, :outcomebefore;

  if (:incomebefore is null) then incomebefore = 0;
  if (:outcomebefore is null) then outcomebefore = 0;

  select sum(storingreminder_weight.weight0), sum(storingreminder_weight.weight1)
  from it
    left join operation on (it.operationid = operation.operationid)
    left join quality on (it.labqualityid = quality.qualityid)
    left join storingreminder_weight(operation.operationsubtype, it.net) on (0 = 0)
  where it.regdate = :date1
    and it.partyid = :party_id
    and it.storageid = :storageid
    and it.state >= 0
    and it.parentid >= 0
    and (quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1)
    and operation.operationsubtype in (0, 1)
  into :incomeday, :outcomeday;

  if (:incomeday is null) then incomeday = 0;
  if (:outcomeday is null) then outcomeday = 0;
    select sum(storingreminder_weight.weight0), sum(storingreminder_weight.weight1)
    from it
      left join operation on (it.operationid = operation.operationid)
      left join quality on (it.labqualityid = quality.qualityid)
      left join storingreminder_weight(operation.operationsubtype, it.net) on (0 = 0)
    where it.regdate between :startdate and :DATE1
      and it.storageid = :storageid
      and it.partyid = :party_id
      and it.parentid >= 0
      and it.state >= 0
      and (quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1)
      and operation.operationsubtype in (0, 1)
    into :weight, :WEIGHT2;

    if (not :weight is null) then income = :income + :weight;
    if (not :WEIGHT2 is null) then outcome = :outcome + :WEIGHT2;
    result = :result + (:income - :outcome);


  result = :result - (:incomebefore - :outcomebefore);
  reminder = :income - :outcome;
  suspend;
end