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

AC_STORE_ILI_DATEWEIGHT

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

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

Описание

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

Определение

CREATE PROCEDURE AC_STORE_ILI_DATEWEIGHT(
    OWNERID INTEGER,
    INVENTORYID INTEGER,
    STARTDATE DATE,
    MINEXP INTEGER,
    BEGPARTYDATE DATE,
    BEGINDATE DATE,
    ENDDATE DATE)
RETURNS (
    CURBEGINDATE DATE,
    CURENDDATE DATE,
    CURINCOME INTEGER,
    CUROUTCOME INTEGER,
    CURWEIGHT INTEGER,
    DESCRIPTION VARCHAR(405))
AS
declare variable OPERATIONNAME varchar(150);
declare variable ISFIRST integer = 1;
declare variable FLAGSAME integer;
declare variable RECORDCOUNT integer = 0;
declare variable OUTCOME integer;
declare variable INCOME integer;
declare variable WEIGHT integer;
declare variable CURDATE date;
declare variable D1 date;
begin
  select count(DESCRIPT)
    from (
      select
          case
            when (EAI.OPERATIONID in (22,70)) then
              IT.REGDATE
            else EA.REGDATE
          end REGDATE,
          AC_STORE_ILI_WEIGHT.ALLINCOME ALLINCOME, AC_STORE_ILI_WEIGHT.ALLOUTCOME ALLOUTCOME,
          AC_STORE_ILI_WEIGHT.SUMWEIGHT SUMWEIGHT, AC_STORE_ILI_WEIGHT.DESCRIPT DESCRIPT
        from EXPORTACTITEMS EAI
          left join EXPORTACTS EA on ((EA.EXPORTACTID = EAI.EXPORTACTID) and (EA.STATE in (0,1)))
          left join IT on ((IT.INVENTORYID = EAI.INVENTORYID) and (IT.STATE in (0,1,-20)))
          join AC_STORE_ILI_WEIGHT(:OWNERID, :MINEXP,
                                   case
                                     when (EAI.OPERATIONID in (22,70)) then
                                       IT.REGDATE
                                     else EA.REGDATE
                                   end) on (0=0)
        where ((EAI.INVENTORYID = :INVENTORYID)
               or ((EAI.PARENTITEMID = :MINEXP) and (EAI.OPERATIONID in (5,22,64,70))))
          and (((EAI.OPERATIONID in (22,70)) and (IT.REGDATE between :BEGPARTYDATE and :ENDDATE))
               or ((EAI.OPERATIONID not in (22,70)) and (EA.REGDATE between :BEGPARTYDATE and :ENDDATE)))
          and (EAI.OPERATIONID > 0)
      group by 1, 2, 3, 4, 5
    )
  into :RECORDCOUNT;

  for
    select
        case
          when (EAI.OPERATIONID in (22,70)) then
            IT.REGDATE
          else EA.REGDATE
        end,
        AC_STORE_ILI_WEIGHT.ALLINCOME, AC_STORE_ILI_WEIGHT.ALLOUTCOME,
        AC_STORE_ILI_WEIGHT.SUMWEIGHT, AC_STORE_ILI_WEIGHT.DESCRIPT
      from EXPORTACTITEMS EAI
        left join EXPORTACTS EA on ((EA.EXPORTACTID = EAI.EXPORTACTID) and (EA.STATE in (0,1)))
        left join IT on ((IT.INVENTORYID = EAI.INVENTORYID) and (IT.STATE in (0,1,-20)))
        join AC_STORE_ILI_WEIGHT(:OWNERID, :MINEXP,
                                 case
                                   when (EAI.OPERATIONID in (22,70)) then
                                     IT.REGDATE
                                   else EA.REGDATE
                                 end) on (0=0)
      where ((EAI.INVENTORYID = :INVENTORYID)
             or ((EAI.PARENTITEMID = :MINEXP) and (EAI.OPERATIONID in (5,22,64,70))))
        and (((EAI.OPERATIONID in (22,70)) and (IT.REGDATE between :BEGPARTYDATE and :ENDDATE))
             or ((EAI.OPERATIONID not in (22,70)) and (EA.REGDATE between :BEGPARTYDATE and :ENDDATE)))
        and (EAI.OPERATIONID > 0)
    group by 1, 2, 3, 4, 5
    order by 2 desc, 1
    into :CURDATE, :INCOME, :OUTCOME, :WEIGHT, :OPERATIONNAME

  do begin
    FLAGSAME = 0;
    CURENDDATE = null;
    CUROUTCOME = 0;

    if (:ISFIRST = 1) then begin
      CURINCOME = :INCOME;
      CURWEIGHT = :WEIGHT;
      CURBEGINDATE = :CURDATE;

      if (:RECORDCOUNT = 1) then
        CURENDDATE = :ENDDATE;

      D1 = :CURDATE;
      DESCRIPTION = OPERATIONNAME;
    end
    else begin
      if (CURDATE <> D1) then begin
        FLAGSAME = 0;
        D1 = :CURDATE;
      end
      else FLAGSAME = 1;

      if (:WEIGHT < 0) then begin
        if ((CURWEIGHT + WEIGHT) >= 0) then begin
          if (FLAGSAME = 0) then begin
            CUROUTCOME = :OUTCOME;

            if (DESCRIPTION <> '') then
              DESCRIPTION = DESCRIPTION || '; ';
            DESCRIPTION = DESCRIPTION || OPERATIONNAME;

            if ((CURDATE >= :BEGINDATE) and (CURDATE <= :ENDDATE)) then begin
              CURENDDATE = CURDATE;
              suspend;
            end

            CURBEGINDATE = :CURDATE;
          end
        end

        if (FLAGSAME = 1) then begin
          if ((CURDATE >= :BEGINDATE) and (CURDATE <= :ENDDATE)) then begin
            CURENDDATE = CURDATE;
            CUROUTCOME = :OUTCOME;
            DESCRIPTION = DESCRIPTION || '; "' || OPERATIONNAME || '"';
          end
          else begin
            CURINCOME = CURWEIGHT + WEIGHT;
            DESCRIPTION = 'Остаток от операции "' || OPERATIONNAME || '"';
          end

          CURWEIGHT = CURWEIGHT + :WEIGHT;
        end
        else
          if (FLAGSAME = 0) then begin
            if (((CURDATE >= :BEGINDATE) and (CURDATE <= :ENDDATE)) or (ISFIRST = RECORDCOUNT)) then
              if (((CURENDDATE is not null) and (CURBEGINDATE <> CURENDDATE)) or (ISFIRST = RECORDCOUNT)) then
                CURENDDATE = :ENDDATE;
              else CURENDDATE = null;

            CURINCOME = CURWEIGHT + WEIGHT;
            CUROUTCOME = 0;
            CURWEIGHT = CURWEIGHT + :WEIGHT;
            DESCRIPTION = 'Остаток от операции "' || OPERATIONNAME || '"';
        end
      end
    end

    ISFIRST = ISFIRST + 1;

    if ((CURENDDATE is not null) and (CURWEIGHT <> 0)) then
      suspend;
  end
end