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

PRECALCULATIONREM

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

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

Описание

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

Определение

CREATE PROCEDURE PRECALCULATIONREM(
    DATE1 DATE,
    CLASSID INTEGER,
    STORAGEID INTEGER,
    OWNERID INTEGER,
    CONTRACTID INTEGER,
    WITHWASTES INTEGER,
    PARTYID INTEGER)
RETURNS (
    RESULT INTEGER,
    HUM NUMERIC(15,2),
    DIRT NUMERIC(15,2))
AS
DECLARE VARIABLE OUTCOME INTEGER;
DECLARE VARIABLE INCOME INTEGER;
DECLARE VARIABLE WASTES INTEGER;
begin /*$$$$
 result = 0;
 income = 0;
 outcome = 0;
 select sum(Net), sum(quality.humidity*net)/sum(net) , sum(quality.dirt*net)/sum(net) from it
left outer join Quality on (IT.LabQualityid  = Quality.qualityid)
left outer join Operation on (IT.Operationid = Operation.operationid)
where
 Quality.classid = :classid
 and It.Storageid = :storageid
 and It.ownerid = :ownerid
 and (it.contractid = :contractid or :contractid = 0)
 and Operation.operationsubtype = 0
 and  IT.regdate <= :date1
 and IT.State >= 0
 and it.parentid <> -1
 and IT.Partyid = :partyid
 into
 :income,
 :hum,
 :dirt;
 if (income is null) then income = 0;

  select cast(sum(Net) as integer) from it
left outer join Quality on (IT.LabQualityid  = Quality.qualityid)
left outer join Operation on (IT.Operationid = Operation.operationid)
where
 Quality.classid = :classid
 and It.Storageid = :storageid
 and It.ownerid = :ownerid
 and (it.contractid = :contractid or :contractid = 0)
 and Operation.operationsubtype = 1
 and it.operationid not in (22,23)
 and  IT.regdate <= :date1
 and IT.State >= 0
 and it.parentid <> -1
 and IT.Partyid = :partyid
 into
 :outcome;
 if (outcome is null) then outcome = 0;

  select cast(sum(Net) as integer) from it
left outer join Quality on (IT.LabQualityid  = Quality.qualityid)
where
 Quality.classid = :classid
 and It.Storageid = :storageid
 and It.ownerid = :ownerid
 and (it.contractid = :contractid or :contractid = 0)
 and it.operationid in (22,23)
 and IT.regdate <= :date1
 and IT.State >= 0
 and it.parentid <> -1
 and IT.Partyid = :partyid
 into
 :wastes;
 result = :income - :outcome;
 if (:withwastes = 1 and :wastes is not null) then
 result = :result - :wastes;
  suspend;
 $$$$*/ EXIT;
end