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

COMMONSTORINGREMINDERWEIGHT

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

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

Описание

Хранящийся остаток для формы 36 по клинету

Определение

CREATE PROCEDURE COMMONSTORINGREMINDERWEIGHT(
    OWNERID INTEGER,
    STORINGID INTEGER,
    CONTRACTID INTEGER,
    STORAGEID INTEGER,
    CLASSID INTEGER,
    DATE1 DATE,
    SILAGEID INTEGER,
    PARTY_ID INTEGER,
    PARTYDATE DATE,
    DOCROP SMALLINT)
RETURNS (
    RESULT NUMERIC(18,0),
    INCOMEDAY NUMERIC(18,0),
    OUTCOMEDAY NUMERIC(18,0),
    REMINDER NUMERIC(18,0))
AS
DECLARE VARIABLE STARTDATE DATE;
DECLARE VARIABLE INCOME NUMERIC(18,0);
DECLARE VARIABLE OUTCOME NUMERIC(18,0);
DECLARE VARIABLE WEIGHT NUMERIC(18,0);
DECLARE VARIABLE INCOMEBEFORE NUMERIC(18,0);
DECLARE VARIABLE OUTCOMEBEFORE NUMERIC(18,0);
DECLARE VARIABLE CROPID INTEGER;
BEGIN
  income = 0 ;
  outcome = 0;
  result = 0;

  if (:docrop = 0) then
  begin
    select cropid from class where class.classid = :CLASSID into
    :CROPID;
  end
  else
    cropid = :classid;

  select parties.begindate
  from parties
  where parties.partyid =  (select partyid from getworkparties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid))
  into :startdate;

    SELECT coalesce(SUM(IT.weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
    WHERE
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and it.state >= 0
      and it.parentid >= 0
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 3
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :result;
    if (:result is null) then result = 0;

    SELECT coalesce(SUM(IT.weight),0)
    FROM IT
      join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
        on (IT.PartyID = GetWorkParties.PartyID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
    WHERE  IT.RegDATE = :date1 and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and it.parentid >= 0
      and it.state >= 0
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :incomeday;

    SELECT coalesce(SUM(IT.weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
    left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
    LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :date1 and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and it.parentid >= 0
      and it.state >= 0
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 1
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :outcomeday;

    SELECT coalesce(SUM(IT.weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and it.parentid >= 0
      and it.state >= 0
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :incomebefore;

    SELECT coalesce(SUM(IT.weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and it.parentid >= 0
      and it.state >= 0
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 1
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :outcomebefore;

   while (:startdate <= :date1) do begin
    SELECT coalesce(SUM(IT.weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Ownerid = :ownerid
      and it.parentid >= 0
      and (IT.storageid = :storageid or :storageid = 0)
      and it.state >= 0
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :weight;
    income = :income + :weight;


    SELECT coalesce(SUM(weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
    WHERE  IT.RegDATE = :startdate and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and it.parentid >= 0
      and it.state >= 0
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 1 and operation.operationtype <> 2
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :weight;
    outcome = :outcome + :weight;

    SELECT coalesce(SUM(-weight),0)
    FROM IT
     join GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid)
       on (IT.PartyID = GetWorkParties.PartyID)
      LEFT JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
      left join quality on (IT.LABQUALITYID = QUALITY.QUALITYID)
    WHERE  iT.RegDATE = :startdate and
      ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and (IT.Storingid = :storingid or :storingid=0)
      and it.parentid >= 0
      and it.state >= 0
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationtype = 2
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :weight;
    outcome = :outcome + :weight;

    startdate = :startdate + 1;
    result = result + (:income - :outcome);
  end
   result = :result - (:incomebefore - :outcomebefore);
   reminder = :income - :outcome;
  SUSPEND;
END