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

COMMONSTORINGREMINDER

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

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

Описание

(Нет описания для процедуры COMMONSTORINGREMINDER)

Определение

CREATE PROCEDURE COMMONSTORINGREMINDER(
    OWNERID INTEGER,
    STORINGID INTEGER,
    CONTRACTID INTEGER,
    STORAGEID INTEGER,
    CLASSID INTEGER,
    DATE1 DATE,
    PARTY_ID INTEGER)
RETURNS (
    RESULT NUMERIC(18,0))
AS
DECLARE VARIABLE WEIGHT NUMERIC(18,0);
DECLARE VARIABLE STARTDATE DATE;
DECLARE VARIABLE INCOME NUMERIC(18,0);
DECLARE VARIABLE OUTCOME NUMERIC(18,0);
DECLARE VARIABLE INCOMEBEFORE NUMERIC(18,0);
DECLARE VARIABLE OUTCOMEBEFORE NUMERIC(18,0);
BEGIN
  income = 0 ;
  outcome = 0;
  select parties.begindate from parties where partyid = :party_id
    into :startdate;
  SELECT SUM( IT.NET )
    FROM IT
      LEFT OUTER JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE >= :startdate
      and IT.LabQualityid in (select qualityid from quality where classid = :classid)
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Partyid = :party_id
      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
    INTO :result;
  if (:result is null) then result = 0;

   SELECT SUM( IT.NET )
    FROM IT
      LEFT OUTER JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where classid = :classid)
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Partyid = :party_id
      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 = 0
    INTO :incomebefore;
    if (:incomebefore is null) then
    incomebefore = 0;
      SELECT SUM( IT.NET )
    FROM IT
      LEFT OUTER JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where classid = :classid)
      and (IT.Storingid = :storingid or :storingid=0)
      and it.state >= 0
      and it.parentid >= 0
      and IT.Partyid = :party_id
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 1
    INTO :outcomebefore;
    if (:outcomebefore is null) then
    outcomebefore = 0;


  while (:startdate <= :date1) do begin
    SELECT SUM( IT.NET )
    FROM IT
      LEFT OUTER JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where classid = :classid)
      and (IT.Storingid = :storingid or :storingid=0)
      and IT.Partyid = :party_id
      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 = 0
    INTO :weight;
    if (:weight is not null) then
    income = :income + :weight;
      SELECT SUM( IT.NET )
    FROM IT
      LEFT OUTER JOIN OPERATION ON (IT.OPERATIONID = OPERATION.OPERATIONID)
    WHERE  IT.RegDATE = :startdate
      and IT.LabQualityid in (select qualityid from quality where classid = :classid)
      and (IT.Storingid = :storingid or :storingid=0)
      and it.state >= 0
      and it.parentid >= 0
      and IT.Partyid = :party_id
      and IT.Ownerid = :ownerid
      and (IT.storageid = :storageid or :storageid = 0)
      and (IT.contractid = :contractid or :contractid=0)
      and operation.operationsubtype = 1
    INTO :weight;
    if (:weight is not null) then
    outcome = :outcome + :weight;
    startdate = :startdate + 1;
    result = :result + (:income - :outcome);
  end
  result = :result - (:incomebefore - :outcomebefore);
  SUSPEND;
END