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

COSTSCALCULATION

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

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

Описание

Расчет стоимости хранения зерна

Определение

CREATE PROCEDURE COSTSCALCULATION(
    OWNERID INTEGER,
    ACLASSID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    CONTRACTID INTEGER,
    PARTYID INTEGER,
    DOCROP INTEGER,
    ACROPID INTEGER,
    PARTYDATE DATE,
    ISWEIGHT INTEGER,
    PRICEPERMONTH INTEGER)
RETURNS (
    REINCOME INTEGER,
    INCOME INTEGER,
    INITIAL_REMINDER INTEGER,
    REOUTCOME INTEGER,
    OUTCOME INTEGER,
    TEMPDATE DATE,
    RESTS INTEGER,
    STORAGECOST DECIMAL(15,2),
    CONTRACTCOSTDATE DATE,
    RESTCALCULATION INTEGER,
    STORINGWEIGHT DECIMAL(15,9),
    STORING_WEIGHT DECIMAL(15,3),
    TEMPMONTH INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER)
AS
declare variable REGDATE date;
declare variable OPERATIONID integer;
declare variable OPERATIONSUBTYPE smallint;
declare variable NET integer;
declare variable WEIGHT integer;
declare variable INVOICEID integer;
declare variable ASTORINGWEIGHT decimal(18,9) = 0;
declare variable DAYCOUNT integer;
declare variable TEMP_MONTH integer;
begin

  if (DATE2 > current_date) then
    PARTYDATE = current_date;

  STORINGWEIGHT = 0;
  TEMPDATE = :DATE1;
  TEMPMONTH = extract(month from :TEMPDATE);
  STORING_WEIGHT = 0;

  while (TEMPDATE <= DATE2) do begin

    INITIAL_REMINDER = 0;
--    OUTCOMEbefore = 0;
--    INCOMEbefore = 0;
    INCOME = 0;
    OUTCOME = 0;
    REINCOME = 0;
    REOUTCOME = 0;

    for

      select
          coalesce(sum(IT.NET), 0), coalesce(sum(IT.WEIGHT), 0), OPERATION.OPERATIONSUBTYPE,
          IT.OPERATIONID, IT.REGDATE, IT.INVOICEID, QUALITY.CROPID, QUALITY.CLASSID
        from IT
          join GETWORKPARTIES(:PARTYID, :PARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          inner join QUALITY  on (QUALITY.QUALITYID = IT.BUHQUALITYID)
          inner join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        where (IT.STATE >= 0)
          and (IT.PARENTID >= 0)
          and (OPERATIONSUBTYPE in (0,1,3,4))
          and ((((QUALITY.CLASSID = :ACLASSID) and (:DOCROP = 0))
               or ((QUALITY.CROPID = :ACROPID) and (:DOCROP = 1))
               or ((coalesce(:ACROPID,0) = 0) and (coalesce(:ACLASSID,0) = 0))))
          and (IT.OWNERID = :OWNERID)
          and ((IT.CONTRACTID = :CONTRACTID) or (:CONTRACTID = 0))
      group by IT.REGDATE, OPERATION.OPERATIONSUBTYPE, IT.OPERATIONID, IT.INVOICEID, QUALITY.CROPID, QUALITY.CLASSID
      into :NET, :WEIGHT, :OPERATIONSUBTYPE, :OPERATIONID, :REGDATE, :INVOICEID, :CROPID, :CLASSID

    do begin

      if (:OPERATIONSUBTYPE = 0) then begin
        if (:REGDATE = :TEMPDATE) then begin
          if (:OPERATIONID = 17) then begin
            if (:NET > 0) then
              if (:ISWEIGHT = 0) then
                REINCOME = :REINCOME + :NET;
              else REINCOME = :REINCOME + :WEIGHT;
            else
              if (:ISWEIGHT = 0) then
                REOUTCOME = :REOUTCOME - :NET;
              else REOUTCOME = :REOUTCOME - :WEIGHT;
          end
          else begin
            if (:ISWEIGHT = 0) then
              INCOME = :INCOME + :NET;
            else INCOME = :INCOME + :WEIGHT;
          end
        end
        if (:REGDATE < :TEMPDATE) then begin
          if (:ISWEIGHT = 0) then
            INITIAL_REMINDER = :INITIAL_REMINDER + :NET;
          else INITIAL_REMINDER = :INITIAL_REMINDER + :WEIGHT;
        end
      end

      if (:OPERATIONSUBTYPE in (1,4)) then begin
        if (:ISWEIGHT = 0) then begin
          if (:REGDATE < :TEMPDATE) then
            INITIAL_REMINDER = :INITIAL_REMINDER - :NET;

          if (:REGDATE = :TEMPDATE) then
            OUTCOME = :OUTCOME + :NET;
        end
        else begin
          if (not (:OPERATIONID in (9,13,22,24,25))) then begin
            if (:REGDATE < :TEMPDATE) then
              INITIAL_REMINDER = :INITIAL_REMINDER - :WEIGHT;

            if (:REGDATE = :TEMPDATE) then
              OUTCOME = :OUTCOME + :WEIGHT;
          end
        end
      end
/*
      if (:OPERATIONSUBTYPE = 1) then begin
        if (:REGDATE < :TEMPDATE) then begin
          if ((not OPERATIONID in (9, 24, 25, 64, 65)) and (:ISWEIGHT = 0)) then
            INITIAL_REMINDER = :INITIAL_REMINDER - :NET;
          else INITIAL_REMINDER = :INITIAL_REMINDER - WEIGHT;
--          OUTCOMEbefore = :OUTCOMEbefore + :NET;
        end
        if (:REGDATE = :TEMPDATE) then begin
          if (:ISWEIGHT = 0) then
            OUTCOME = :OUTCOME + :NET;
          else OUTCOME = :OUTCOME + :WEIGHT;
        end
      end

      if (:OPERATIONID in (9, 13, 24, 25)) then begin
        if (:REGDATE = :TEMPDATE) then begin
          if (:ISWEIGHT = 0) then
            OUTCOME = :OUTCOME + (:WEIGHT * -1);
          else OUTCOME = :OUTCOME + (:WEIGHT * -1);
        end
        if (:REGDATE < :TEMPDATE) then begin
          INITIAL_REMINDER = :INITIAL_REMINDER + (:WEIGHT * -1);
--          OUTCOMEbefore = :OUTCOMEbefore + :WEIGHT*-1;
        end
      end
*/
    end

    RESTS = INITIAL_REMINDER + INCOME + REINCOME - OUTCOME - REOUTCOME;

    select distinct GET_CONTRACT_COSTS.COST, GET_CONTRACT_COSTS.CONTRACTCOSTDATE
      from GET_CONTRACT_COSTS(:TEMPDATE, :CONTRACTID, 1, :CROPID, :CLASSID)
    into :STORAGECOST, :CONTRACTCOSTDATE;

    if (STORAGECOST > 0) then
      RESTCALCULATION = RESTS;
    else RESTCALCULATION = 0;

/*
    if ((TEMPDATE = DATE2) or (TEMPMONTH <> extract(month from :TEMPDATE))) then begin
      if (TEMPDATE = DATE2) then begin
        select DAYS_IN_MONTH.DAYCOUNT
          from DAYS_IN_MONTH(:TEMPDATE)
        into DAYCOUNT;
        ASTORINGWEIGHT = ASTORINGWEIGHT + RESTS;
      end
      else
        select DAYS_IN_MONTH.DAYCOUNT
          from DAYS_IN_MONTH(:TEMPDATE - 1)
        into DAYCOUNT;

      if (PRICEPERMONTH = 0) then
        STORING_WEIGHT = STORING_WEIGHT + (:ASTORINGWEIGHT / 1000);
      else
        if (PRICEPERMONTH = 1) then
          ASTORINGWEIGHT = ASTORINGWEIGHT + cast(:RESTS as numeric(18,9)) / 1000 / DAYCOUNT;
        else STORING_WEIGHT = STORING_WEIGHT + (:ASTORINGWEIGHT / 1000 / 30);
      ASTORINGWEIGHT = 0;
    end
*/

    if (PRICEPERMONTH = 0) then
      ASTORINGWEIGHT = ASTORINGWEIGHT + (cast(:RESTS as numeric(18,9)) / 1000);
    else
      if (PRICEPERMONTH = 1) then begin
        select DAYS_IN_MONTH.DAYCOUNT
          from DAYS_IN_MONTH(:TEMPDATE)
        into DAYCOUNT;

        ASTORINGWEIGHT = ASTORINGWEIGHT + (cast(:RESTS as numeric(18,9)) / 1000 / DAYCOUNT);
      end
      else ASTORINGWEIGHT = ASTORINGWEIGHT + (cast(:RESTS as numeric(18,9)) / 1000 / 30);

    STORING_WEIGHT = cast(cast(ASTORINGWEIGHT as numeric(15,4)) as numeric(15,3));
    TEMPMONTH = extract(month from :TEMPDATE);

    if (PRICEPERMONTH = 0) then
      STORINGWEIGHT = RESTS / 1000;
    else
      if (PRICEPERMONTH = 1) then begin
        select DAYS_IN_MONTH.DAYCOUNT
          from DAYS_IN_MONTH(:TEMPDATE)
        into DAYCOUNT;

        STORINGWEIGHT = cast(RESTS as decimal(15,9)) / 1000 / DAYCOUNT;
      end
      else STORINGWEIGHT = RESTS / 1000 / 30;

/*
      if (abs(STORINGWEIGHT) < 0.1) then
        STORINGWEIGHT = STORINGWEIGHT * 6;
*/
    if ((STORINGWEIGHT <= -0.019) and (STORINGWEIGHT <= -0.019)) then
      STORINGWEIGHT = -0.1;

    suspend;

    TEMPDATE = :TEMPDATE + 1;
  end
end