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

AVERAGE_PERIOD_STORAGE_GRAIN

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

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

Описание

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

Определение

CREATE PROCEDURE AVERAGE_PERIOD_STORAGE_GRAIN(
    PARTYID INTEGER,
    BEGINDATE DATE,
    ENDDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    STORAGEID INTEGER,
    STORINGID INTEGER,
    OWNERID INTEGER,
    CONTRACTID INTEGER,
    TOCONSIDERINCENTNERS SMALLINT,
    SHOWTRANSFERINEXPENSE INTEGER)
RETURNS (
    REGDATE DATE,
    RECEIPT INTEGER,
    EXPENSE INTEGER,
    REST INTEGER,
    QUANTITYDAYS INTEGER,
    SUMDAILYRESTS BIGINT,
    SUMREST BIGINT,
    SUMRECEIPT INTEGER,
    PERIODSTORAGE INTEGER,
    STRDAILY VARCHAR(10))
AS
declare variable AREGDATE date;
declare variable ARECEIPT integer;
declare variable AEXPENSE integer;
declare variable ASUMRECEIPT bigint;
begin

  REST = 0;
  SUMREST = 0;
  SUMRECEIPT = 0;
  ASUMRECEIPT = 0;

  for
    select
        IT.REGDATE,
        sum(case
          when ((OPERATION.OPERATIONSUBTYPE = 0)
                and (((coalesce(:OWNERID,-1) = -1) and ((IT.NET > 0) or (:SHOWTRANSFERINEXPENSE = 0)))
                     or ((coalesce(:OWNERID,-1) <> -1) and (IT.NET > 0)))) then
            IT.NET
        end) INCOME,
        sum(case
          when (OPERATION.OPERATIONSUBTYPE = 1) then
            IT.NET
          when (((coalesce(:OWNERID,-1) <> -1) or (:SHOWTRANSFERINEXPENSE = 1))
                and (IT.OPERATIONID = 17) and (IT.NET < 0)) then
            -IT.NET
        end) OUTCOME
      from IT
--        join GETWORKPARTIES(null, :ENDDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join QUALITY on (QUALITY.QUALITYID = IT.BUHQUALITYID)
--      left join storingreminder_weight(operation.operationsubtype,it.net) on (0=0)
      where (IT.REGDATE between :BEGINDATE and :ENDDATE)
        and (QUALITY.CROPID = :CROPID)
        and ((QUALITY.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1))
        and ((QUALITY.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
        and (IT.STORAGEID = :STORAGEID)
        and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
        and (IT.PARTYID = :PARTYID)
        and ((IT.OWNERID = :OWNERID) or (coalesce(:OWNERID,-1) = -1))
        and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
        and ((IT.PARENTID >= 0) and (IT.STATE >= 0) and (IT.NET is not null))
        and ((((OPERATION.OPERATIONSUBTYPE in (0,1)) and (not (IT.OPERATIONID in (9,29))))
              and ((IT.OPERATIONID <> 17) or (coalesce(:OWNERID,-1) <> -1)
                or ((IT.OPERATIONID = 17) and (IT.OPERATIONPARAM = 1700))))
             and ((not (IT.OPERATIONID in (65)))
                  or ((IT.OPERATIONID = 17) and (not IT.OPERATIONPARAM is null))))
      group by 1
    union
      select (:ENDDATE + 1), 0, 0
        from rdb$database
    into :AREGDATE, :ARECEIPT, :AEXPENSE
  do begin

    if ((RECEIPT <> 0) or (EXPENSE <> 0)) then begin

      if (REST > 0) then begin
        QUANTITYDAYS = :AREGDATE - :REGDATE;
        SUMDAILYRESTS = :REST * :QUANTITYDAYS;
        ASUMRECEIPT = :ASUMRECEIPT + :RECEIPT;
        SUMRECEIPT = :ASUMRECEIPT;

        if (TOCONSIDERINCENTNERS = 1) then begin
          SUMDAILYRESTS = cast((cast(:SUMDAILYRESTS as numeric(12,2)) / 100) as integer);
          SUMRECEIPT = cast((cast(:SUMRECEIPT as numeric(12,2)) / 100) as integer);
        end

        SUMREST = :SUMREST + :SUMDAILYRESTS;
        PERIODSTORAGE = cast((cast(:SUMREST as numeric(12,2)) / :SUMRECEIPT) as integer);

        if ((mod(:PERIODSTORAGE, 10) = 1) and (:PERIODSTORAGE <> 11)) then
          STRDAILY = 'день';
        else
          if ((mod(:PERIODSTORAGE, 10) in (2,3,4)) and (not(:PERIODSTORAGE in (12,13,14)))) then
            STRDAILY = 'дні';
          else STRDAILY = 'днів';
      end
      else QUANTITYDAYS = 0;
      suspend;
    end

    REGDATE = :AREGDATE;
    RECEIPT = coalesce(:ARECEIPT, 0);
    EXPENSE = coalesce(:AEXPENSE, 0);
    REST = :REST + :RECEIPT - :EXPENSE;
  end
end