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

OBOROTSTORAGE1

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

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

Описание

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

Определение

CREATE PROCEDURE OBOROTSTORAGE1(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    PARTYID INTEGER)
RETURNS (
    OUTCOMEWOWASTE INTEGER,
    OUTCOME INTEGER,
    INCOME INTEGER,
    CLASSNAME VARCHAR(20),
    INCOMEBEFORE INTEGER,
    CROPNAME VARCHAR(20),
    INCOMEAFTER INTEGER,
    OUTCOMEAFTER INTEGER,
    OUTCOMEBEFORE INTEGER)
AS
DECLARE VARIABLE OPERATIONID INTEGER;
DECLARE VARIABLE OPERATIONSUBTYPE INTEGER;
DECLARE VARIABLE REGDATE DATE;
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE CLASSID INTEGER;
begin
/*for
SELECT  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)
     where it.storageid = :storageid 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  CROP.CROPNAME, CLASS.CLASSNAME, Quality.classid, it.storageid
into
  :cropname,
  :classname,
  :classid,
  :storageid
  do
 begin
  income = 0;
  outcome =0;
  incomeafter = 0;
  outcomeafter =0;
  outcomeWOWaste = 0;
  incomebefore = 0;
  outcomebefore = 0;
 for
  SELECT it.net, iT.regdate, operationsubtype, it.operationid
   FROM IT
   Left outer JOIN QUALITY ON (IT.LABQUALITYID = Quality.QUALITYID)
   LEFT OUTER JOIN Operation ON (IT.operationid = operation.operationid)
   where it.storageid = :storageid 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 not in (13,22,23,24,25)) then outcomeWOWaste = :outcomeWOWaste + :net;
  if (:regdate < :date1 and :operationsubtype = 0) then incomebefore = :incomebefore + :net;
  if (:regdate < :date1 and :operationsubtype = 1) then outcomebefore = :outcomebefore + :net;
 end
  suspend;
 end*/
END