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

MOVEMENTSTORAGE

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

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

Описание

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

Определение

CREATE PROCEDURE MOVEMENTSTORAGE(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    WASTES INTEGER,
    INCOME INTEGER,
    OUTCOME INTEGER,
    INITIALREMINDER INTEGER,
    SUPLIERNAME VARCHAR(100),
    CROPCLASS VARCHAR(40),
    ENDREMINDER INTEGER)
AS
declare variable WEIGHT integer;
declare variable REGDATE date;
declare variable OPERATIONID integer;
declare variable OPERATIONSUBTYPE smallint;
declare variable NET integer;
declare variable CLASSID integer;
declare variable OWNERID integer;
declare variable OPERATIONTYPE integer;
declare variable OUTCOMEBEFORE integer;
declare variable INCOMEBEFORE integer;
begin
 for
 select clients.clientname, it.ownerid, qn.cropfullname,
  q.classid
 FROM it
   left join quality q on (it.samplerqualityid = q.qualityid)
   left join quality_cropfullname(q.cropid, q.classid, null, 1) qn on (0=0)
   left join class on (Q.classid = class.classid)
   left join crop on (Q.cropid = crop.cropid)
   left join operation on (it.operationid = operation.operationid)
   left join clients on (It.ownerid = clients.clientid)
 where
      it.partyid =  (select partyid from getpartyid(:partyid,:partydate, q.cropid, q.classid, :storageid))
  and it.state >= 0
  and operationsubtype in (0,1)
  and it.net is not null and (it.net <> 0 or it.weight <> 0 )
  and it.storageid = :storageid
  and it.regdate <= :date2
  and it.parentid >= 0
  and it.operationid not in (4)
 group by  clients.clientname,  it.ownerid, qn.cropfullname, q.classid
 into
   :supliername,
   :ownerid,
   :cropclass,
   :classid
 do
 begin
  incomebefore = 0;
  outcomebefore = 0;
  income = 0;
  outcome = 0;
  wastes = 0;
 for
 SELECT it.net, operation.operationsubtype, it.regdate, It.operationid, it.weight, operation.operationtype
  FROM it
   left join quality q on (it.samplerqualityid = q.qualityid)
   left join crop on (Q.cropid = crop.cropid)
   left join operation on (it.operationid = operation.operationid)
 where
      it.partyid =  (select partyid from getpartyid(:partyid,:partydate, q.cropid, q.classid, :storageid))
  and it.state >= 0 and operationsubtype in (0,1)
 -- and it.operationid not in (22,23)
  and it.parentid >= 0
  and q.classid = :classid
  and (it.ownerid = :ownerid or (:ownerid is null))
  and it.storageid = :storageid
  and it.net is not null and (it.net <> 0 or it.weight<> 0 )
  and it.regdate <= :date2
 into
  :net,
  :operationsubtype,
  :regdate,
  :operationid,
  :weight,
  :OPERATIONTYPE
 do
 begin
   if (((:operationsubtype = 0) and (:net >= 0) ) and :regdate between :date1 and :date2) then income = :income + :net;
   if (:operationsubtype = 0 and :regdate < :date1) then incomebefore = :incomebefore + :net;
   if (:operationsubtype = 1 and :regdate < :date1) then outcomebefore = :outcomebefore + :net;
   if ((:operationsubtype = 1)  and :regdate between :date1 and :date2) then outcome = :outcome + :net;
   if ((:operationid = 17 and :net < 0)  and :regdate between :date1 and :date2) then outcome = :outcome + -1*:net;
   if ((:OPERATIONTYPE = 2) and :regdate between :date1 and :date2) then wastes = :wastes + :weight*-1;
 end

 initialreminder = :INCOMEBEFORE - :OUTCOMEBEFORE;

 endreminder = :initialreminder + :income - :outcome - :wastes;

 if ((:initialreminder <> 0) or
     (:income <> 0) or
     (:outcome <> 0) or
     (:wastes <> 0))
 then
   suspend;
 end
END