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

CLIENTOBOROTCOMMON

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

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

Описание

не используется

Определение

CREATE PROCEDURE CLIENTOBOROTCOMMON(
    DATE1 DATE,
    DATE2 DATE,
    SUPLIERID INTEGER,
    CONTRACTID INTEGER,
    PARTYID INTEGER)
RETURNS (
    REGISTRYINCOME INTEGER,
    TTNOUTCOME INTEGER,
    STORINGOUTCOME INTEGER,
    OUTCOMEBEFORE INTEGER,
    INCOMEBEFORE INTEGER,
    CROPCLASS VARCHAR(35))
AS
declare variable operationid integer;
declare variable operationsubtype smallint;
declare variable net integer;
declare variable regdate date;
declare variable classid integer;
declare variable weight integer;
begin
  for
    SELECT crop.cropname|| ' ' || class.classname cropclass, q1.classid
    FROM it
   LEFT JOIN quality q1 ON (it.BuhQualityid = q1.qualityid)
   left JOIN class ON (q1.classid = class.classid)
   left JOIN crop ON (q1.cropid = crop.cropid)
   Left join contracts on (it.contractid = contracts.contractid)
   where it.partyid = :partyid
   and q1.classid is not null
   and it.net is not null
   and it.parentid <> -1
   and it.operationid not in(22,4)
   and it.regdate <= :date2
   and it.state >= 0
   and it.BuhQualityid is not null
   and it.ownerid = :suplierid and ((it.contractid = :contractid) or (:contractid = 0))
   group by    crop.cropname, class.classname, q1.classid
   order by crop.cropname, class.classname
   into :cropclass,
        :classid
  do
  begin
   registryincome = 0;
   ttnoutcome = 0;
   storingoutcome = 0;
   outcomebefore = 0;
   incomebefore = 0;
   for
    SELECT it.net, operation.operationsubtype, it.regdate,  it.operationid, it.weight
    FROM it
    LEFT JOIN quality q1 ON (it.BuhQualityid = q1.qualityid)
    LEFT JOIN operation ON (it.operationid = operation.operationid)
    left JOIN class ON (q1.classid = class.classid)
    left JOIN crop ON (q1.cropid = crop.cropid)
    Left join contracts on (it.contractid = contracts.contractid)
    where it.partyid = :partyid
    and it.ownerid = :suplierid and ((it.contractid = :contractid) or (:contractid = 0))
    and it.BuhQualityid is not null
    and it.net is not null
    and q1.classid = :classid
    and it.parentid <> -1
   and it.operationid not in(22,4)
    and q1.classid is not null
    and it.state >= 0
    and it.regdate <= :date2
    order by crop.cropname, class.classname
    into :net,
         :operationsubtype,
         :regdate,
         :operationid,
         :weight
   do
   begin
    if (:operationsubtype = 0 and :operationid <> 17 and :regdate between :date1 and :date2) then
     registryincome =:registryincome + :net;
    if (:operationsubtype = 1 and :regdate between :date1 and :date2 ) then
     ttnoutcome =:ttnoutcome + :net;
    if (:operationid = 17 and :regdate between :date1 and :date2 and :net < 0) then
     ttnoutcome =:ttnoutcome + :net *-1;
    if (:operationid = 17 and :regdate between :date1 and :date2 and :net > 0) then
     registryincome =:registryincome + :net;
    if (:operationsubtype = 0 and :regdate < :date1) then
     incomebefore =:incomebefore + :net;
    if (:operationsubtype = 1 and :regdate < :date1) then
     outcomebefore =:outcomebefore + :net;
    if (:operationid in (9,13) and :regdate between :date1 and :date2) then
     ttnoutcome =:ttnoutcome + (:weight*(-1));
    end
   suspend;
 end
END