"localhost:/firebird/data/ILICHEVSK2.FDB". ��������� Previous topic Chapter index Next topic

PRECALCULATIONREM

 

������� ���������

��������

���

��������

DATE1

DATE

 

CLASSID

INTEGER

 

STORAGEID

INTEGER

 

OWNERID

INTEGER

 

CONTRACTID

INTEGER

 

WITHWASTES

INTEGER

 

PARTYID

INTEGER

 

 

�������� ���������

��������

���

��������

RESULT

INTEGER

 

HUM

NUMERIC(15,2)

 

DIRT

NUMERIC(15,2)

 

 

��������

(��� �������� ��� ��������� 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

 

     Previous topic Chapter index Next topic