"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры Previous topic Chapter index Next topic

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

 

 

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

Параметр

Тип

Описание

REGDATE

DATE

 

RECEIPT

INTEGER

 

EXPENSE

INTEGER

 

REST

INTEGER

 

QUANTITYDAYS

INTEGER

 

SUMDAILYRESTS

BIGINT

 

SUMREST

BIGINT

 

SUMRECEIPT

INTEGER

 

PERIODSTORAGE

INTEGER

 

STRDAILY

VARCHAR(10)

 

 

Описание

(Нет описания для процедуры 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

 

     Previous topic Chapter index Next topic