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

OBOROTSTORAGE

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

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

Описание

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

Определение

CREATE PROCEDURE OBOROTSTORAGE(
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER)
RETURNS (
    OUTCOMEWOWASTE INTEGER,
    OUTCOME INTEGER,
    INCOME INTEGER,
    CLASSNAME VARCHAR(20),
    CROPNAME VARCHAR(20),
    INCOMEAFTER INTEGER,
    OUTCOMEAFTER INTEGER,
    STORAGENAME VARCHAR(100))
AS
DECLARE VARIABLE OPERATIONID INTEGER;
DECLARE VARIABLE OPERATIONSUBTYPE INTEGER;
DECLARE VARIABLE REGDATE DATE;
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE CLASSID INTEGER;
DECLARE VARIABLE STORAGEID INTEGER;
begin
for
SELECT Storage.Storagename, Crop.cropname, Class.Classname, Quality.classid, it.storageid
FROM IT
   LEFT OUTER JOIN QUALITY ON (IT.LABQUALITYID = Quality.QUALITYID)
   LEFT OUTER JOIN Operation ON (IT.operationid = operation.operationid)
   INNER JOIN CLASS ON (QUALITY.CLASSID = CLASS.CLASSID)
   INNER JOIN CROP ON (QUALITY.CROPID = CROP.CROPID)
   LEFT OUTER JOIN STORAGE ON (IT.STORAGEID = storage.STORAGEID)
   where Storage.storagetypeid = 1
   and IT.partyid = :partyid
   and it.state >= 0
   and it.net is not null and operation.operationsubtype IN (0,1)
   AND IT.REGDATE <= :DATE2 AND IT.NET <> 0
   and it.parentid >= 0
group by   storage.storagename, CROP.CROPNAME, CLASS.CLASSNAME, Quality.classid, it.storageid
into
  :storagename,
  :cropname,
  :classname,
  :classid,
  :storageid
  do
 begin
  income = 0;
  outcome =0;
  incomeafter = 0;
  outcomeafter =0;
  outcomeWOWaste = 0;
 for
  SELECT it.net, iT.regdate, operationsubtype, it.operationid
   FROM IT
   LEFT OUTER JOIN QUALITY ON (IT.LABQUALITYID = Quality.QUALITYID)
   LEFT OUTER JOIN STORAGE ON (IT.STORAGEID = storage.STORAGEID)
   LEFT OUTER JOIN Operation ON (IT.operationid = operation.operationid)
   where Storage.storagetypeid = 1 and IT.partyid = :partyid and it.state >= 0
   and  quality.classid = :classid and it.storageid = :storageid
   and it.net is not null and operation.operationsubtype IN (0,1)
   and it.parentid >= 0
   into
   :net,
   :regdate,
   :operationsubtype,
   :operationid
 do
 begin
  if (:regdate between :date1 and :date2 and :operationsubtype = 0) then income = :income + :net;
  if (:regdate between :date1 and :date2 and :operationsubtype = 1) then outcome = :outcome + :net;
  if (:REGDATE <= :date2 and :operationsubtype = 1) then outcomeafter = :outcomeafter + :net;
  if (:REGDATE <= :date2 and :operationsubtype = 0) then incomeafter = :incomeafter + :net;
  if (:REGDATE <= :date2 and :operationsubtype = 1 and :operationid <> 13) then outcomeWOWaste = :outcomeWOWaste + :net;
 end
  suspend;
 end
end