"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