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

GRAINMOVEMENT2

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

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

Описание

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

Определение

CREATE PROCEDURE GRAINMOVEMENT2(
    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))
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;
begin
/*  if (:isstoragenull = 0) then storageid = null;
-- select partyid from parties where state = 1 into :partyid;
  for
    select  Crop.cropname|| ' ' || Class.Classname, class.classid
    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 storage on (it.storageid = storage.storageid)
    where IT.regdate <= :date2 and It.partyid = :partyid
      and (it.storageid = :storageid or (:storageid is null and storage.storagetypeid = 1))
      and IT.State >= 0
      and it.parentid >= 0
      and IT.Net is not null and crop.croptypeid = 3
      and operation.operationsubtype IN (1,0)
    group by CROP.CROPNAME, CLASS.CLASSNAME, class.classid
    into
      :CROPCLASS,
      :CLASSID
  do begin
    OUTCOME = 0;
    OTHEROUTCOME = 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 outer join QUALITY on (IT.SAMPLERQUALITYID = Quality.QUALITYID)
        left outer 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 = 3 and It.partyid = :partyid and IT.regdate <= :date2
        and (it.storageid = :storageid or (:storageid is null and storage.storagetypeid = 1))
        and quality.classid = :classid and IT.State >= 0
        and IT.Net is not null
        and it.parentid >= 0
        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;
      else
        if (:regdate between :date1 and :date2 and :transporttypeid = 2 and :operationsubtype = 0) then
          zdincome = :zdincome + :net;
        else if (:regdate between :date1 and :date2  and :operationsubtype = 0) 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;
      else
        if (:providerid = 4 and :operationsubtype = 1 and :regdate between :date1 and :date2) then
          outcomeM2 = :outcomem2 + :net;
        else
          if (:providerid = 5 and :operationsubtype = 1 and :regdate between :date1 and :date2) then
            outcomeM3 = :outcomem3 + :net;
          else
            if (:operationid = 30 and :regdate between :date1 and :date2) then
              outcomekz = :outcomekz + :net;
            else 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 not in (24,25,9,13)) 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