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

XPPCLIENTOBOROT

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

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

Описание

Бухгалтерия - Оборотная ведомость движения зерна по клиенту

Определение

CREATE PROCEDURE XPPCLIENTOBOROT(
    CLIENTID INTEGER,
    CONTRACTID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    REMAIN_WEIGHT INTEGER,
    REMAIN_NET INTEGER,
    NETINCOME INTEGER,
    WEIGHTINCOME INTEGER,
    REINCOME INTEGER,
    OUTCOME INTEGER,
    REOUTCOME INTEGER,
    CLASSNAME VARCHAR(100),
    RESTWEIGHT INTEGER,
    RESTNET INTEGER)
AS
declare variable operationid integer;
declare variable weight integer;
declare variable regdate date;
declare variable operationsubtype smallint;
declare variable net integer;
declare variable classid integer;
declare variable cropid integer;
declare variable incomebefore integer;
declare variable outcomebefore integer;
begin
  for
    select
      qn.cropfullname,
      quality.classid,
      quality.cropid
    from IT
      join GetWorkParties(:PARTYID,:PARTYDATE, null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
      left JOIN quality on (IT.samplerqualityid  = quality.qualityid)
      left join quality_cropfullname(quality.cropid, quality.classid, null, 1) qn on (0=0)
      left join operation on (it.operationid = operation.operationid)
      left join crop on (quality.cropid = crop.cropid)
    where IT.Regdate <= :date2
      and  operationsubtype IN (0,1)
      and IT.Net is not null and it.net <> 0
      and it.ownerid = :clientid
      and (it.contractid = :contractid or (:contractid = 0) )
      and it.parentid >= 0
      and cropTypeid <> 3
      and it.state >= 0
    group by
      qn.cropfullname,
      quality.classid,
      quality.cropid
    into :classname,
      :CLASSID,
      :CROPID
    do
    begin
      incomebefore = 0;
      outcomebefore = 0;
      RestWeight = 0;
      RestNet = 0;
      netincome = 0;
      outcome = 0;
      reincome = 0;
      reoutcome = 0;
      WEIGHTINCOME = 0;
      remain_weight = 0;
      remain_net = 0;
      for
        select
          it.net,
          operation.operationsubtype,
          it.regdate,
          It.weight,
          IT.operationid
        from IT
          join GetWorkParties(:PARTYID,:PARTYDATE, :CropID,:ClassID,null)
            on (IT.PartyID = GetWorkParties.PartyID)
          LEFT JOIN quality on (IT.samplerqualityid = quality.qualityid)
          left join operation on (it.operationid = operation.operationid)
        where IT.Regdate <= :date2
          and it.ownerid = :clientid
          and (it.contractid = :contractid or (:contractid = 0) )
          and it.net <> 0
          and operationsubtype IN (0,1)
          and it.parentid >= 0
          and it.state >= 0
          and IT.Net is not null
          and (((quality.classid = :classid) and (:CLASSID is not null)) or ((:CLASSID is null) and (quality.cropid = :CROPID and quality.classid is null)))
       into :net,
         :operationsubtype,
         :regdate,
         :weight,
         :operationid
       do
       begin
         if ((:operationsubtype = 0) and (:regdate between :date1 and :date2) and (:operationid <> 17)) then
         begin
           netincome = :Netincome + :net;
           WEIGHTINCOME = :WEIGHTINCOME + :WEIGHT;
         end
         if ((:operationid = 17) and (:regdate between :date1 and :date2) and (:net > 0)) then reincome = :reincome + :net;
         else  if ((:operationid = 17) and (:regdate between :date1 and :date2) and (:net < 0)) then reoutcome = :reoutcome + :net*-1;

         if (:operationsubtype = 0 and :regdate < :date1) then
         begin
           remain_weight = :remain_weight + :WEIGHT;
           remain_net = :remain_net + :NET;
         end

        if (:OperationSubType = 0) then begin
         RestWeight = :RestWeight + :Weight;
         RestNet = :RestNet + :Net;
       end
       else begin
         RestWeight = :RestWeight - :Weight;
         RestNet = :RestNet - :Net;
       end

         if (:operationsubtype = 1 and :regdate < :date1) then
         begin
           remain_net = :remain_net - :NET;
           remain_weight = :remain_net;
         end

         if (:operationsubtype = 1 and :regdate between :date1 and :date2) then outcome = :outcome + :net;
--   if (:operationid in (9,13,24,25) and :regdate between :date1 and :date2 and :net < 0) then outcome = :outcome + :weight*-1;
     end

     if ((:INCOMEBEFORE - :outcomebefore <> 0)
      or (:netincome <> 0)
      or (:weightincome <> 0)
      or (:reincome <> 0)
      or (:outcome <> 0)
      or (:reoutcome <> 0) )then
        suspend;
   end
--  EXIT;
END