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

ACC_STORINGREMINDER

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

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

Описание

Хранящийся остаток для Акта расчета

Определение

CREATE PROCEDURE ACC_STORINGREMINDER(
    DATE1 DATE,
    DATE2 DATE,
    STORAGEID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    DOCROP INTEGER,
    OWNERID INTEGER,
    CONTRACTID INTEGER,
    PARTY_ID INTEGER,
    PARTYDATE DATE,
    CALCFROMSTART INTEGER)
RETURNS (
    RESULT NUMERIC(18,0))
AS
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;
declare variable reminder integer;
BEGIN
  income = 0 ;
  outcome = 0;
  outcomefromstart = 0;
  incomefromstart = 0;
  result = 0;

  if (:calcfromstart = 2) then
  begin
    select parties.begindate from parties where partyid = :party_id
     into :date1;
  end

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

  DIFFERENCE = 0;

    SELECT coalesce(SUM(coalesce(storingreminder_weight.weight0,0)),0), coalesce(SUM(coalesce(storingreminder_weight.weight1, 0)), 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)
      left join storingreminder_weight(operation.operationsubtype,it.net) on (0=0)
    WHERE IT.RegDATE < :date1
      and operation.operationsubtype in (0,1)
      and ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and IT.storageid = :storageid
      and it.parentid >= 0
      and IT.state >= 0
      and it.net is not null
      and ((It.ownerid = :ownerid) or (:ownerid = 0))
      and ((it.contractid = :contractid) or (:contractid = 0))
      and ((quality.sortid = :sortid) or (:sortid = 0))
    INTO
     :incomefromstart,
     :outcomefromstart;

  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 IT.RegDATE between :date1 and :date2
      and IT.storageid = :storageid
      and it.parentid >= 0
      and IT.state >= 0
      and operation.operationsubtype in (0,1)
      and ((quality.classid = :classid and :docrop = 0) or (quality.cropid = :classid and :docrop = 1))
      and it.net is not null
      and ((It.ownerid = :ownerid) or (:ownerid = 0))
      and ((it.contractid = :contractid) or (:contractid = 0))
      and ((quality.sortid = :sortid) or (:sortid = 0))
    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

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

    DIFFERENCE = :INCOMEFROMSTART - :OUTCOMEFROMSTART;
    if (:DIFFERENCE < 0) then
        difference = 0;
    result = :result + :DIFFERENCE;

    RDATE_OLD = :RDATE;

    if (:RDATE = :DATE1) then begin
      incomebefore = :Income;
      outcomebefore = :OUTCOME;
    end

/*    if (:RDATE = :date2) then begin
      incomeday = :Income;
      outcomeday = :OUTCOME;
    end*/

  end
  while (:RDATE_OLD < :date2) 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 (:RDATE_OLD <> :date2) then
    begin
      result = :result - (:incomebefore - :outcomebefore);
      reminder = :INCOMEFROMSTART - :OUTCOMEFROMSTART;
    end
  SUSPEND;
END