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

XPPOBOROTCLIENT

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

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

Описание

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

Определение

CREATE PROCEDURE XPPOBOROTCLIENT(
    CROPID INTEGER,
    CLASSID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE,
    DOCROP INTEGER)
RETURNS (
    INCOMEBEFORE INTEGER,
    OUTCOMEBEFORE INTEGER,
    NETINCOME INTEGER,
    WEIGHTINCOME INTEGER,
    REINCOME INTEGER,
    OUTCOME INTEGER,
    REOUTCOME INTEGER,
    SUPLIERNAME VARCHAR(100),
    REGIONNAME VARCHAR(100),
    RESTNET INTEGER,
    RESTWEIGHT INTEGER,
    OUTCOMEWEIGHT INTEGER,
    OUTCOMEBEFOREW INTEGER,
    INCOMEBEFOREW INTEGER,
    RESTBEFOREW INTEGER,
    RESTBEFOREN INTEGER)
AS
declare variable operationid integer;
declare variable weight integer;
declare variable regdate date;
declare variable operationsubtype smallint;
declare variable net integer;
declare variable ownerid integer;
begin
 for
   select
     Clients.ClientName,
     Regions.RegionName,
     IT.OwnerID
   from IT
      join GetWorkParties(:PartyID,:PartyDate,:CropID, :ClassID,null)
        on (IT.PartyID = GetWorkParties.PartyID)
     left join Quality on (IT.SamplerQualityID = Quality.QualityID)
     left join Class on (Quality.Classid = Class.ClassID)
     left join Operation on (IT.OperationID = Operation.OperationID)
     left join Storage on (IT.StorageID = Storage.StorageID)
     left join Clients on (IT.OwnerID = Clients.ClientID)
     left join Regions on (Clients.Regionid = Regions.RegionID)
   where IT.RegDate <= :Date2
     and IT.Net is not null
     and IT.Net <> 0
     and IT.State >= 0
     and IT.ParentID >= 0
     and ((Quality.ClassID = :ClassID and :DoCrop = 0)
       or (Quality.CropID = :CropID and :DoCrop = 1))
     and OperationSubType in (0,1)
     and Storage.StorageTypeID = 1
   group by
     Clients.ClientName,
     IT.OwnerID,
     Regions.RegionName
   into :SuplierName,
        :RegionName,
        :OwnerID
   do
   begin
     IncomeBefore = 0;
     OutcomeBefore = 0;
     IncomeBeforeW = 0;
     OutcomeBeforeW = 0;
     restbeforen = 0;
     restbeforeW = 0;
     RestWeight = 0;
     RestNet = 0;
     NetIncome = 0;
     Outcome = 0;
     OutcomeWeight = 0;
     ReIncome = 0;
     ReOutcome = 0;
     WeightIncome = 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 Crop on (Quality.CropID = Crop.CropID)
        left join Storage on (IT.StorageID = Storage.StorageID)
        left join Operation on (IT.OperationID = Operation.OperationID)
      where IT.RegDate <= :Date2
        and OperationSubType in (0,1)
        and Storage.Storagetypeid = 1
        and ((Quality.ClassID = :ClassID and :DoCrop = 0)
          or (Quality.CropID = :CropID and :DoCrop = 1))
        and IT.OwnerID = :OwnerID
        and IT.Net <> 0
        and IT.Net is not null
        and IT.ParentID >= 0
        and IT.State >= 0
     /* group by
        Operation.OperationSubType,
        IT.RegDate,
        IT.OperationID
     */ 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
         RestbeforeN = :RestbeforeN + :Net;
         RestbeforeW = :RestbeforeW + :Weight;
       end

       if (:OperationSubType = 1 and :Regdate < :Date1) then begin
         RestbeforeN = :RestbeforeN - :Net;
         RestbeforeW = :RestbeforeW - :Weight;
       end

       if (:OperationSubType = 1 and :RegDate between :Date1 and :Date2) then begin
         Outcome = :Outcome + :Net;
         outcomeweight = :outcomeweight + :weight;
        end

     -- if (:OperationID in (9,13,24,25) and :regdate between :date1 and :date2 ) then
      --if (:net < 0) then      then OutCome = :OutCome + :Weight*-1;
      --  OutCome = :OutCome + :Net*-1;

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

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