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

GRAINMOVEMENT1

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

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

Описание

Движение зерна на элеваторе

Определение

CREATE PROCEDURE GRAINMOVEMENT1(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    ISSTORAGENULL INTEGER,
    PARTYID INTEGER)
RETURNS (
    OTHEROUTCOME NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    OUTCOMEOUT NUMERIC(15,3),
    OUTCOMEKZ NUMERIC(15,3),
    OUTCOMEM3 NUMERIC(15,3),
    OUTCOMEM2 NUMERIC(15,3),
    OUTCOMEM1 NUMERIC(15,3),
    OUTCOMEBEFORE NUMERIC(15,3),
    INCOMEBEFORE NUMERIC(15,3),
    OTHERINCOME NUMERIC(15,3),
    ZDINCOME NUMERIC(15,3),
    AVTOINCOME NUMERIC(15,3),
    CROPCLASS VARCHAR(41),
    SORTNAME VARCHAR(30))
AS
DECLARE VARIABLE WEIGHT INTEGER;
DECLARE VARIABLE OPERATIONID SMALLINT;
DECLARE VARIABLE PROVIDERID INTEGER;
DECLARE VARIABLE OPERATIONSUBTYPE SMALLINT;
DECLARE VARIABLE NET INTEGER;
DECLARE VARIABLE REGDATE DATE;
DECLARE VARIABLE TRANSPORTTYPEID SMALLINT;
DECLARE VARIABLE CLASSID SMALLINT;
DECLARE VARIABLE SORTID INTEGER;
begin
/* if (:isstoragenull = 0) then storageid = null;
-- select partyid from parties where state = 1 into :partyid;
 for
   select  Crop.cropname|| ' ' || Class.Classname, class.classid, quality.sortid, cropsort.sortname
   from IT
     left outer join QUALITY on (IT.samplerqualityid = Quality.QUALITYID)
     inner join CLASS on (QUALITY.CLASSID = CLASS.CLASSID)
     left outer join operation on (IT.operationid = operation.operationid)
     inner join CROP on (QUALITY.CROPID = CROP.CROPID)
     left join cropsort on (QUALITY.sortid = cropsort.sortid)
     left join Storage on (it.storageid = storage.storageid)
   where crop.croptypeid = 1 and It.partyid = :partyid and IT.regdate between :date1 and :date2
     and (it.storageid = :storageid or (:storageid is null and storage.storagetypeid = 1))
     and IT.State >= 0
     and it.parentid >= 0
     and IT.Storingid = 1  and IT.Net is not null
     and operation.operationsubtype in(0,1)
     group by    CROP.CROPNAME, CLASS.CLASSNAME, class.classid, quality.sortid, cropsort.sortname
   into
     :CROPCLASS,
     :CLASSID,
     :sortid,
     :sortname
 do begin
   OTHEROUTCOME = 0;
   OUTCOME = 0;
   OUTCOMEOUT = 0;
   OUTCOMEKZ = 0;
   OUTCOMEM3 = 0;
   OUTCOMEM2 = 0;
   OUTCOMEM1 = 0;
   OUTCOMEBEFORE = 0;
   INCOMEBEFORE = 0;
   OTHERINCOME = 0;
   ZDINCOME = 0;
   AVTOINCOME = 0;
   for
     select  it.net, ittransport.transporttypeid, it.operationid, it.regdate, operation.operationsubtype,
       clients.Storageid as providerid, it.weight
     from IT
       left join QUALITY on (IT.samplerqualityid = Quality.QUALITYID)
       left join operation on (IT.operationid = operation.operationid)
       inner join CROP on (QUALITY.CROPID = CROP.CROPID)
       left join STORAGE on (IT.STORAGEID = STORAGE.STORAGEID)
       left join Clients on (it.providerid = Clients.ClientID)
       left join ittransport on (it.inventoryid = ittransport.inventoryid)
     where crop.croptypeid = 1 and It.partyid = :partyid and IT.regdate between :date1 and :date2
       and (it.storageid = :storageid or (:storageid is null and storage.storagetypeid = 1))
       and quality.classid = :classid
       and IT.State >= 0
       and it.parentid >= 0
       and ( quality.sortid = :sortid or (:sortid is null and quality.sortid is null))
       and IT.Storingid = 1 and IT.Net is not null
       and Operation.operationsubtype in (0,1)
     into
       :net,
       :transporttypeid,
       :operationid,
       :regdate,
       :operationsubtype,
       :providerid,
       :weight
   do begin
     if (:regdate between :date1 and :date2 and :transporttypeid in (0,1) and :operationsubtype = 0) then
       avtoincome = :avtoincome + :net;
     if (:regdate between :date1 and :date2 and :transporttypeid = 2 and :operationsubtype = 0) then
       zdincome = :zdincome + :net;
     if (:regdate between :date1 and :date2  and :operationid in (4,17,32)) then
       otherincome = :otherincome + :net;
     if (:regdate < :date1 and :operationsubtype = 0) then
       incomebefore = :incomebefore + :net;
     if (:regdate < :date1 and :operationsubtype = 1) then
       outcomebefore = :outcomebefore + :net;
     if (:providerid = 3 and :operationsubtype = 1 and :regdate between :date1 and :date2) then
       outcomeM1 = :outcomem1 + :net;
     if (:providerid = 4 and :operationsubtype = 1 and :regdate between :date1 and :date2) then
       outcomeM2 = :outcomem2 + :net;
     if (:providerid = 5 and :operationsubtype = 1 and :regdate between :date1 and :date2) then
       outcomeM3 = :outcomem3 + :net;
     if (:operationid = 30 and :regdate between :date1 and :date2) then
       outcomekz = :outcomekz + :net;
     if (:operationid = 5  and :regdate between :date1 and :date2) then
       Outcomeout = :Outcomeout + :net;
     if (:operationsubtype = 1  and :regdate between :date1 and :date2) then
       Outcome = :Outcome + :net;
     if (:operationid in (22,23,24,25,9,13)  and :regdate between :date1 and :date2) then
     begin
       if (:operationid in (22,23,24,25)) then
         otherOutcome = :otherOutcome + :net;
       else
         otherOutcome = :otherOutcome + :weight * -1;
     end
   end
   OUTCOME = :OUTCOME/1000;
   OUTCOMEOUT = :OUTCOMEOUT/1000;
   OUTCOMEKZ = OUTCOMEKZ/1000;
   OUTCOMEM3 = OUTCOMEM3/1000;
   OUTCOMEM2 = :OUTCOMEM2/1000;
   OUTCOMEM1 = :OUTCOMEM1/1000;
   OUTCOMEBEFORE = :OUTCOMEBEFORE/1000;
   INCOMEBEFORE = :INCOMEBEFORE/1000;
   OTHERINCOME = :OTHERINCOME/1000;
   ZDINCOME = :ZDINCOME/1000;
   AVTOINCOME = :AVTOINCOME/1000;
   OTHEROUTCOME = :OTHEROUTCOME/1000;
   suspend;
 end*/
END