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

ACCOUNTINGSTORINGREMINDER

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

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

Описание

Расчет хранящего остатка.
Используется в
форма 36 общая
форма 36 по культуре(проблема)
Акт-расчет

Определение

CREATE PROCEDURE ACCOUNTINGSTORINGREMINDER(
    DATE1 DATE,
    STORAGEID INTEGER,
    CLASSID INTEGER,
    STORINGID INTEGER,
    SILAGEID INTEGER,
    PARTY_ID INTEGER,
    DOCROP INTEGER,
    PARTYDATE DATE)
RETURNS (
    RESULT NUMERIC(18,0),
    INCOMEDAY NUMERIC(18,0),
    OUTCOMEDAY NUMERIC(18,0),
    REMINDER NUMERIC(18,0))
AS
declare variable startdate date;
declare variable incomebefore numeric(18,0);
declare variable outcomebefore numeric(18,0);
declare variable income numeric(18,0);
declare variable outcome numeric(18,0);
declare variable incomefromstart numeric(18,0);
declare variable outcomefromstart numeric(18,0);
declare variable rdate date;
declare variable rdate_old date;
declare variable difference numeric(18,0);
declare variable cropid integer;
BEGIN
  income = 0 ;
  outcome = 0;
  outcomefromstart = 0;
  incomefromstart = 0;

  if (:docrop = 1) then
    cropid = :CLASSID;
  else
  begin
   select cropid from class where class.classid = :classid
   into
   :cropid;
  end

  select parties.begindate from parties
  where
    parties.partyid =
      (select partyid from GetWorkParties(:PARTY_ID,:PARTYDATE, :cropid, :classid, :storageid))

  into :startdate;

  SELECT
    coalesce(SUM(IT.NET), 0)
  FROM IT
    join GetWorkParties(:PARTY_ID,:PARTYDATE,null,null,:storageID)
       on (IT.PartyID = GetWorkParties.PartyID)
    Left join operation on (it.operationid = operation.operationid)
    left join quality on (it.BuhQualityid = quality.qualityid)
  WHERE operation.operationsubtype = 3
    and ((it.storingid = :storingid) or (:storingid = 0))
    and IT.storageid = :storageid
    and IT.State >= 0
    and it.parentid >= 0
    and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
    and ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
  INTO :Result;

  DIFFERENCE = 0;
  for
    SELECT SUM(coalesce(storingreminder_weight.weight0,0)), SUM(coalesce(storingreminder_weight.weight1, 0)), IT.RegDATE
    FROM IT
      join GetWorkParties(:PARTY_ID,:PARTYDATE,null,null,:storageid)
         on (IT.PartyID = GetWorkParties.PartyID)
      Left join operation on (it.operationid = operation.operationid)
      left join quality on (it.BuhQualityid = quality.qualityid)
      left join storingreminder_weight(operation.operationsubtype,it.net) on (0=0)
    WHERE operation.operationsubtype in (0,1)
      and IT.RegDATE between :startdate and :date1
      and ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and ((it.storingid = :storingid) or (:storingid = 0))
      and ((it.silageid = :SILAGEID) or (:SILAGEID = 0))
      and IT.storageid = :storageid
      and it.parentid >= 0
      and IT.State >= 0
      and it.net is not null
    group by IT.RegDATE
    INTO :INCOME, :outcome, :RDATE
  do begin
    while (:RDATE_OLD + 1 < :RDATE) do begin
      if (:result is null) then result = 0;
      if (:DIFFERENCE is null) then DIFFERENCE = 0;
      result = :result + :DIFFERENCE;
      RDATE_OLD = :RDATE_OLD + 1;
    end

 /*  if (:OUTCOME is null) then OUTCOME = 0;
    if (:inCOME is null) then inCOME = 0;*/

    outcomefromstart = :OUTCOMEFROMSTART + :OUTCOME;
    incomefromstart = :INCOMEFROMSTART + :INCOME;

    DIFFERENCE = :INCOMEFROMSTART - :OUTCOMEFROMSTART;
    result = :result + :DIFFERENCE;

    RDATE_OLD = :RDATE;

    if (:RDATE = :startdate) then begin
      incomebefore = :Income;
      outcomebefore = :OUTCOME;
    end
    if (:RDATE = :date1) then begin
      incomeday = :Income;
      outcomeday = :OUTCOME;
    end
  end
  while (:RDATE_OLD < :date1) do begin
    result = :result + :DIFFERENCE;
    RDATE_OLD = :RDATE_OLD + 1;
  end

  if (:incomebefore is null) then incomebefore = 0;
  if (:outcomebefore is null) then outcomebefore = 0;
  if (:incomeday is null) then incomeday = 0;
  if (:outcomeday is null) then outcomeday = 0;

    result = :result - (:incomebefore - :outcomebefore);
    reminder = :INCOMEFROMSTART - :OUTCOMEFROMSTART;
  SUSPEND;
END