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

CROPSTORINGREMINDER

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

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

Описание

пока не используется

Определение

CREATE PROCEDURE CROPSTORINGREMINDER(
    DATE1 DATE,
    STORAGEID INTEGER,
    CROPID INTEGER,
    STORINGID INTEGER,
    SILAGEID INTEGER,
    PARTY_ID 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 INCOME NUMERIC(18,0);
DECLARE VARIABLE STARTDATE DATE;
DECLARE VARIABLE OUTCOME NUMERIC(18,0);
DECLARE VARIABLE INCOMEBEFORE NUMERIC(18,0);
DECLARE VARIABLE OUTCOMEBEFORE NUMERIC(18,0);
BEGIN
 if (:PARTY_ID = 0) then
 begin
   select parties.partyid from parties where parties.state = 1 and parties.storageid = :STORAGEID
   into
     :PARTY_ID;
  end
  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)
    WHERE operation.operationsubtype = 3
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and IT.storageid = :storageid
      and ((it.storingid = :storingid) or (:storingid = 0))
      and IT.State >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :Result;
    if (:Result is null) then Result = 0;

   SELECT SUM( IT.NET ) FROM IT
    Left join operation on (it.operationid = operation.operationid)
    WHERE operation.operationsubtype = 0
      and IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and ((it.storingid = :storingid) or (:storingid = 0))
      and IT.storageid = :storageid
      and IT.State >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :incomebefore;
    if (:incomebefore is null) then
     incomebefore = 0;

     SELECT SUM( IT.NET ) FROM IT
    Left join operation on (it.operationid = operation.operationid)
    WHERE operation.operationsubtype = 0
      and IT.RegDATE = :date1
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and IT.storageid = :storageid
      and ((it.storingid = :storingid) or (:storingid = 0))
      and IT.State >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :incomeday;

    SELECT SUM( IT.NET ) FROM IT
    Left join operation on (it.operationid = operation.operationid)
    WHERE operation.operationsubtype = 1
      and IT.RegDATE = :date1
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and IT.storageid = :storageid
      and ((it.storingid = :storingid) or (:storingid = 0))
      and IT.State >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :outcomeday;

    SELECT SUM( IT.NET ) FROM IT
    Left join operation on (it.operationid = operation.operationid)
    WHERE operation.operationsubtype = 1
      and IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and it.regdate = :startdate
      and IT.storageid = :storageid
      and ((it.storingid = :storingid) or (:storingid = 0))
      and it.state >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :outcomebefore;
    if (:outcomebefore is null) then
     outcomebefore = 0;

  while ((:startdate <= :date1)) do begin
    SELECT SUM( IT.NET ) FROM IT
    Left join operation on (it.operationid = operation.operationid)
    WHERE operation.operationsubtype = 0
      and IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and IT.storageid = :storageid
      and ((it.storingid = :storingid) or (:storingid = 0))
      and IT.State >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :weight;
    if (not :weight is null) then
     income = :income + :weight;

    SELECT SUM( IT.NET ) FROM IT
    Left join operation on (it.operationid = operation.operationid)
    WHERE operation.operationsubtype = 1
      and IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where cropid = :cropid)
      and IT.Partyid = :party_id
      and IT.storageid = :storageid
      and ((it.storingid = :storingid) or (:storingid = 0))
      and it.state >= 0
      and it.parentid >= 0
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    INTO :weight;
    if (not :weight is null) then
     outcome = :outcome + :weight;
     result = :result + (:income - :outcome);
   startdate = :startdate + 1;
     end
     result = :result - (:incomebefore - :outcomebefore);
     reminder = :income - :outcome;
   SUSPEND;
END