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

AC_STORE_ILI_NEW

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

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

Описание

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

Определение

CREATE PROCEDURE AC_STORE_ILI_NEW(
    PARTYID INTEGER,
    PARTYDATE DATE,
    BEGINDATE DATE,
    ENDDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    OWNERID INTEGER,
    COST DECIMAL(15,4))
RETURNS (
    INVENTORYID INTEGER,
    STARTDATE DATE,
    ALLWEIGHT INTEGER,
    DAYSTORE INTEGER,
    CURRENTDATE DATE,
    CURRENTENDDATE DATE,
    CURCOST DECIMAL(15,4),
    CURPRIVILEGEDAYS INTEGER,
    DESCRIPTION VARCHAR(405),
    OUTCOME INTEGER,
    CROPFULLNAME VARCHAR(100),
    I INTEGER)
AS
declare variable BEGPARTYDATE date;
declare variable STOREDAY integer;
declare variable MINEXP integer;
declare variable R1 date;
declare variable CURINCOME integer;
declare variable N integer = 0;
declare variable COST1 decimal(15,4);
declare variable PRIVILEGEDAYS1 integer;
declare variable COST2 decimal(15,4);
declare variable PRIVILEGEDAYS2 integer;
declare variable COST3 decimal(15,4);
declare variable PRIVILEGEDAYS3 integer;
declare variable COST4 decimal(15,4);
declare variable PRIVILEGEDAYS4 integer;
declare variable COST5 decimal(15,4);
declare variable PRIVILEGEDAYS5 integer;
declare variable COST6 decimal(15,4);
declare variable PRIVILEGEDAYS6 integer;
declare variable COST7 decimal(15,4);
declare variable PRIVILEGEDAYS7 integer;
declare variable COST8 decimal(15,4);
declare variable PRIVILEGEDAYS8 integer;
declare variable COST9 decimal(15,4);
declare variable PRIVILEGEDAYS9 integer;
declare variable COST10 decimal(15,4);
declare variable PRIVILEGEDAYS10 integer;
declare variable COST11 decimal(15,4);
declare variable PRIVILEGEDAYS11 integer;
declare variable COST12 decimal(15,4);
declare variable PRIVILEGEDAYS12 integer;
declare variable DEND date;
begin

  select
      first 1 EXPORTACTS.REGDATE
    from EXPORTACTS
    where (EXPORTACTS.REGDATE >= (select min(GETWORKPARTIES.BEGINDATE)
                                    from GETWORKPARTIES(:PARTYID, :PARTYDATE, null, null, null)))
  order by REGDATE
  into :BEGPARTYDATE;

  BEGINDATE = BEGINDATE - 1;

  for
    select GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.PRIVILEGEDAYS
      from GET_CONTRACT_COSTS(:ENDDATE, (select distinct CONTRACTS.CONTRACTID
                                           from CONTRACTS
                                           where (CONTRACTS.OWNERID = :OWNERID)
                                             and (:ENDDATE between CONTRACTS.CONTRACTDATE
                                                             and coalesce(CONTRACTS.CONTRACTENDDATE,current_date))),
                                        1, :CROPID, :CLASSID)
    into :CURCOST, :CURPRIVILEGEDAYS

  do begin
    N = N + 1;

    if (N = 1) then begin
      COST1 = CURCOST;
      PRIVILEGEDAYS1 = CURPRIVILEGEDAYS;
    end

    if (N = 2) then begin
      COST2 = CURCOST;
      PRIVILEGEDAYS2 = CURPRIVILEGEDAYS;
    end

    if (N = 3) then begin
      COST3 = CURCOST;
      PRIVILEGEDAYS3 = CURPRIVILEGEDAYS;
    end

    if (N = 4) then begin
      COST4 = CURCOST;
      PRIVILEGEDAYS4 = CURPRIVILEGEDAYS;
    end

    if (N = 5) then begin
      COST5 = CURCOST;
      PRIVILEGEDAYS5 = CURPRIVILEGEDAYS;
    end

    if (N = 6) then begin
      COST6 = CURCOST;
      PRIVILEGEDAYS6 = CURPRIVILEGEDAYS;
    end

    if (N = 7) then begin
      COST7 = CURCOST;
      PRIVILEGEDAYS7 = CURPRIVILEGEDAYS;
    end

    if (N = 8) then begin
      COST8 = CURCOST;
      PRIVILEGEDAYS8 = CURPRIVILEGEDAYS;
    end

    if (N = 9) then begin
      COST9 = CURCOST;
      PRIVILEGEDAYS9 = CURPRIVILEGEDAYS;
    end

    if (N = 10) then begin
      COST10 = CURCOST;
      PRIVILEGEDAYS10 = CURPRIVILEGEDAYS;
    end

    if (N = 11) then begin
      COST11 = CURCOST;
      PRIVILEGEDAYS11 = CURPRIVILEGEDAYS;
    end

    if (N = 12) then begin
      COST12 = CURCOST;
      PRIVILEGEDAYS12 = CURPRIVILEGEDAYS;
    end
  end

  if (N = 0) then begin
    select coalesce(CLIENTS.STOREDAY,0)
      from CLIENTS
      where (CLIENTS.CLIENTID = :OWNERID)
    into :PRIVILEGEDAYS1;

    N = 1;
    COST1 = COST;
  end

  for
    select
        EAI.INVENTORYID, min(EA.REGDATE), min(EAI.EXPORTACTITEMID),
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME
      from EXPORTACTS EA
        join EXPORTACTITEMS EAI on ((EAI.EXPORTACTID = EA.EXPORTACTID) and (EAI.WEIGHT is not null))
        join QUALITY on (QUALITY.QUALITYID = EA.QUALITYID)
        left join QUALITY_CROPFULLNAME_BYID(EA.QUALITYID, 1) on (0=0)
      where (EA.REGDATE between :BEGPARTYDATE and :ENDDATE)
--        and (EAI.INVENTORYID = 258671)
        and ((EA.OPERATIONID in (1,17)) and (EA.STATE = 0))
        and ((QUALITY.CROPID = :CROPID) or (coalesce(:CROPID,-1) = -1))
        and ((QUALITY.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1))
        and (EA.OWNERID = :OWNERID)
    group by 1, 4
    into :INVENTORYID, :R1, :MINEXP, :CROPFULLNAME

  do begin
    ALLWEIGHT = 0;
    OUTCOME = 0;

    for
      select
          AC_STORE_ILI_DATEWEIGHT.CURBEGINDATE, AC_STORE_ILI_DATEWEIGHT.CURENDDATE,
          AC_STORE_ILI_DATEWEIGHT.CURINCOME, AC_STORE_ILI_DATEWEIGHT.CUROUTCOME,
          AC_STORE_ILI_DATEWEIGHT.CURWEIGHT, AC_STORE_ILI_DATEWEIGHT.DESCRIPTION
        from AC_STORE_ILI_DATEWEIGHT(:OWNERID, :INVENTORYID, :R1, :MINEXP, :BEGPARTYDATE, :BEGINDATE, :ENDDATE)
      into :STARTDATE, :DEND, :CURINCOME, :OUTCOME, :ALLWEIGHT, :DESCRIPTION

      do begin
        I = 0;
        STOREDAY = 0;
        DAYSTORE = 0;

        while ((I < N) and (DAYSTORE = 0)) do begin
          I = I + 1;

          if (I = 1) then begin
            CURCOST = COST1;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS1;
          end

          if (I = 2) then begin
            CURCOST = COST2;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS2;
          end

          if (I = 3) then begin
            CURCOST = COST3;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS3;
          end

          if (I = 4) then begin
            CURCOST = COST4;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS4;
          end

          if (I = 5) then begin
            CURCOST = COST5;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS5;
          end

          if (I = 6) then begin
            CURCOST = COST6;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS6;
          end

          if (I = 7) then begin
            CURCOST = COST7;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS7;
          end

          if (I = 8) then begin
            CURCOST = COST8;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS8;
          end

          if (I = 9) then begin
            CURCOST = COST9;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS9;
          end

          if (I = 10) then begin
            CURCOST = COST10;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS10;
          end

          if (I = 11) then begin
            CURCOST = COST11;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS11;
          end

          if (I = 12) then begin
            CURCOST = COST12;
            CURPRIVILEGEDAYS = PRIVILEGEDAYS12;
          end

          if ((DEND - R1) > CURPRIVILEGEDAYS) then begin
            CURRENTDATE = STARTDATE;
            CURRENTENDDATE = DEND;
            DAYSTORE = (DEND - R1) - CURPRIVILEGEDAYS;

            if (DAYSTORE > (DEND - STARTDATE)) then
              DAYSTORE = DEND - STARTDATE;

            if ((DEND - DAYSTORE) < BEGINDATE) then
              DAYSTORE = DEND - BEGINDATE;

            STOREDAY = STOREDAY + DAYSTORE;

            if (STOREDAY < (ENDDATE - BEGINDATE)) then begin
              if ((DAYSTORE > 0) and (ALLWEIGHT > 0)) then
                suspend;

              DEND = DEND - DAYSTORE;
              DAYSTORE = 0;
            end
            else begin
              DAYSTORE = DAYSTORE - (STOREDAY - (ENDDATE - BEGINDATE));
            end

          end
        end
      end

      if ((DAYSTORE > 0) and (ALLWEIGHT > 0)) then
        suspend;
    end

end