"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