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

ACC_AVALANDINCOMESTAT_CALC

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

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

Описание

Бухгалтерия - Формы 1-зерно (полугодовая и месячная)

Определение

CREATE PROCEDURE ACC_AVALANDINCOMESTAT_CALC(
    PARTYID INTEGER,
    PARTYDATE DATE,
    CROPGHI INTEGER,
    CLASSGHI INTEGER,
    SORTID INTEGER,
    DATE1 DATE,
    DATE2 DATE)
RETURNS (
    AVALIABLE NUMERIC(15,3),
    INCOME NUMERIC(15,3),
    INCOME4 NUMERIC(15,3),
    INCOME5 NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    INCOME_WITH_PARTY NUMERIC(15,3))
AS
declare variable TEMP numeric(15,3);
declare variable TEMP4 numeric(15,3);
declare variable TEMP5 numeric(15,3);
declare variable OPERATIONSUBTYPE integer;
declare variable TEMPPARTY integer;
declare variable TEMPSTORAGE integer;
declare variable INPARTY integer;
declare variable TEMPCLASS integer;
declare variable TEMPCROP integer;
declare variable ISINPERIOD integer;
begin
  INCOME = 0;
  INCOME4 = 0;
  INCOME5 = 0;
  OUTCOME = 0;
  AVALIABLE = 0;
  INCOME_WITH_PARTY = 0;

  if ((CROPGHI = -1) and (CLASSGHI = -1) and (SORTID = -1)) then begin
    suspend;
    exit;
  end

  for
    select coalesce(sum(abs(IT.WEIGHT)),0),
        sum(case
              when (IT.STORINGID = 1) then
                IT.WEIGHT
            end),
        sum(case
              when (IT.STORINGID = 7) then
                IT.WEIGHT
            end),
        OPERATION.OPERATIONSUBTYPE, IT.PARTYID,
        IT.STORAGEID, QUALITY.CROPID, QUALITY.CLASSID,
        case
          when ((IT.REGDATE >= :DATE1) and ((OPERATION.OPERATIONTYPE <> 4) or (IT.PARTYID = 122)) and (IT.PARTYID <> 121)) then
            1
          else 0
        end ISINPERIOD
      from IT
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join CROP on (CROP.CROPID = QUALITY.CROPID)
        left join CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join CLIENTS on (CLIENTS.CLIENTID = INVOICES.SENDERID)
      where (IT.REGDATE <= :DATE2) and (IT.PARENTID >= 0) and (IT.STATE >= 0) and (not (IT.OPERATIONID in (9,31)))
        and ((CROP.CROPGHI = :CROPGHI) or ((:CROPGHI < 0) and (CROP.CROPTYPEID = (:CROPGHI * -1))))
        and ((CLASS.CLASSGHI = :CLASSGHI) or (:CLASSGHI = -1))
        and ((((QUALITY.SORTID = :SORTID) or (:SORTID = -1)) and (:SORTID >= -1))
             or (((QUALITY.SORTID <> (:SORTID * -1)) or (QUALITY.SORTID is null))
                  and (:SORTID < -1)))
        and (IT.WEIGHT is not null) and (IT.WEIGHT <> 0) and (CLIENTS.STORAGEID is null)
        and (STORAGE.STORAGETYPEID = 1) and (OPERATION.OPERATIONTYPE <> 6)
    group by OPERATION.OPERATIONSUBTYPE, IT.PARTYID, IT.STORAGEID,
        QUALITY.CROPID, QUALITY.CLASSID, ISINPERIOD
    into :TEMP, :TEMP4, :TEMP5, :OPERATIONSUBTYPE, :TEMPPARTY, :TEMPSTORAGE,
        :TEMPCROP, :TEMPCLASS, :ISINPERIOD
  do begin
    select GETWORKPARTIES.PARTYID
      from GETWORKPARTIES(:PARTYID, :PARTYDATE, :TEMPCROP, :TEMPCLASS, :TEMPSTORAGE)
    into :INPARTY;

    if (:OPERATIONSUBTYPE = 0) then begin
      if (:TEMPPARTY = :INPARTY) then
       INCOME_WITH_PARTY = :INCOME_WITH_PARTY + :TEMP;

      if (:ISINPERIOD = 1) then begin
        INCOME = :INCOME + :TEMP;
        INCOME4 = :INCOME4 + :TEMP4;
        INCOME5 = :INCOME5 + :TEMP5;
      end
    end
    else
      if ((:OPERATIONSUBTYPE = 1) and (:TEMPPARTY = :INPARTY)) then
        OUTCOME = :OUTCOME + :TEMP;
  end

  INCOME_WITH_PARTY = cast(:INCOME_WITH_PARTY as numeric(12,2)) / 1000;
  OUTCOME = cast(:OUTCOME as numeric(12,3)) / 1000;
  AVALIABLE = INCOME_WITH_PARTY - OUTCOME;
  INCOME = cast(:INCOME as numeric(12,3)) / 1000;
  INCOME4 = cast(:INCOME4 as numeric(12,3)) / 1000;
  INCOME5 = cast(:INCOME5 as numeric(12,3)) / 1000;

  suspend;
end