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

GRAINMOVEMENT

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

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

Описание

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

Определение

CREATE PROCEDURE GRAINMOVEMENT(
    DATE1 DATE,
    DATE2 DATE,
    STORING SMALLINT,
    STORAGEID INTEGER,
    ISSTORAGENULL INTEGER,
    PARTYID INTEGER)
RETURNS (
    OTHEROUTCOME NUMERIC(15,3),
    CROPNAME VARCHAR(10),
    OUTCOME NUMERIC(15,3),
    OUTCOME_WEIGHT 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),
    OUTCOMEBEFORE_WEIGHT NUMERIC(15,3),
    INCOMEBEFORE NUMERIC(15,3),
    INCOMEBEFORE_WEIGHT NUMERIC(15,3),
    OTHERINCOME NUMERIC(15,3),
    OTHERINCOME_WEIGHT NUMERIC(15,3),
    ZDINCOME NUMERIC(15,3),
    ZDINCOME_WEIGHT NUMERIC(15,3),
    AVTOINCOME NUMERIC(15,3),
    AVTOINCOME_WEIGHT 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;
 for
   select  crop.cropname|| ' ' || class.classname, class.classid, crop.cropname, quality.sortid, cropsort.sortname
   from it
     left join quality on (it.samplerqualityid = quality.qualityid)
     left join class on (quality.classid = class.classid)
     left join operation on (it.operationid = operation.operationid)
     left join cropsort on (quality.sortid = cropsort.sortid)
     left join crop on (quality.cropid = crop.cropid)
     left join storage on (it.storageid = storage.storageid)
   where crop.croptypeid = 1
     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.storingid <> 1 or it.storingid is null) or (:storing = 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,
    :cropname,
    :sortid,
    :sortname

  do begin
    OUTCOME = 0;
    OUTCOME_WEIGHT = 0;
    OUTCOMEOUT = 0;
    OUTCOMEKZ = 0;
    OUTCOMEM3 = 0;
    OUTCOMEM2 = 0;
    OUTCOMEM1 = 0;
    OUTCOMEBEFORE = 0;
    Outcomebefore_Weight = 0;
    INCOMEBEFORE = 0;
    INCOMEBEFORE_Weight = 0;
    OTHERINCOME = 0;
    OTHERINCOME_Weight = 0;
    ZDINCOME = 0;
    ZDINCOME_Weight = 0;
    AVTOINCOME = 0;
    AVTOINCOME_Weight = 0;
    otheroutcome = 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 clients on (it.providerid = clients.clientid)
        left join storage on (it.storageid = storage.storageid)
        left join ittransport on (it.inventoryid = ittransport.inventoryid)
      where crop.croptypeid = 1
        and it.partyid = :partyid
        and (it.storageid = :storageid or (:storageid is null and storage.storagetypeid = 1))
        and quality.classid = :classid
        and IT.State >= 0
        and ((quality.sortid = :sortid) or ((:sortid is null) and (quality.sortid is null)))
        and ((it.storingid <> 1 or it.storingid is null) or (:storing = 1))
        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 begin
        avtoincome = :avtoincome + :net;
        AVTOINCOME_Weight = :AVTOINCOME_Weight + :weight;
      end
      if (:regdate between :date1 and :date2 and :transporttypeid = 2 and :operationsubtype = 0) then begin
        zdincome = :zdincome + :net;
        ZDINCOME_Weight = :ZDINCOME_Weight + :weight;
      end
      if (:regdate between :date1 and :date2  and :operationid in (4,17,32)) then begin
        otherincome = :otherincome + :net;
        otherincome_weight = :otherincome_weight + :weight;
      end
      if (:regdate < :date1 and :operationsubtype = 0) then begin
        incomebefore = :incomebefore + :net;
        incomebefore_Weight = :incomebefore_Weight + :weight;
      end

      if (:regdate < :date1 and :operationsubtype = 1) then begin
        outcomebefore = :outcomebefore + :net;
       if (:operationid in (9, 13)) then begin
         if (:weight > 0) then Outcomebefore_Weight = :Outcomebefore_Weight + :weight;
         else Outcomebefore_Weight = :Outcomebefore_Weight - :weight;
       end
       else begin
         Outcomebefore_Weight = :Outcomebefore_Weight + :weight;
       end
      end

      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 begin
       Outcome = :Outcome + :net;
       if (:operationid in (9,13)) then begin
         if (:weight > 0) then Outcome_Weight = :Outcome_Weight + :weight;
         else Outcome_Weight = :Outcome_Weight - :weight;
       end
       else begin
         Outcome_Weight = :Outcome_Weight + :weight;
       end
      end
      if (:operationid in (22,23,24,25,9,13,46)  and :regdate between :date1 and :date2) then
      begin
        if (:operationid in (13,22,23,24,25,46)) then
          otherOutcome = :otherOutcome + :net;
        else
          otherOutcome = :otherOutcome + :weight * -1;
      end
    end
    OUTCOME = :OUTCOME/1000;
    outcome_weight = :outcome_weight/1000;
    OUTCOMEOUT = :OUTCOMEOUT/1000;
    OUTCOMEKZ = :OUTCOMEKZ/1000;
    OUTCOMEM3 = :OUTCOMEM3/1000;
    OUTCOMEM2 = :OUTCOMEM2/1000;
    OUTCOMEM1 = :OUTCOMEM1/1000;
    OUTCOMEBEFORE = :OUTCOMEBEFORE/1000;
    Outcomebefore_Weight = :Outcomebefore_Weight / 1000;
    INCOMEBEFORE = :INCOMEBEFORE/1000;
    INCOMEBEFORE_Weight = :INCOMEBEFORE_Weight/1000;
    OTHERINCOME = :OTHERINCOME/1000;
    otherincome_weight = :otherincome_weight/1000;
    ZDINCOME = :ZDINCOME/1000;
    ZDINCOME_Weight = :ZDINCOME_Weight/1000;
    AVTOINCOME = :AVTOINCOME/1000;
    AVTOINCOME_Weight = :AVTOINCOME_Weight / 1000;
    otheroutcome = :otheroutcome /1000;
    suspend;
  end*/
END