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

ACN_DOBASVODKA

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

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

Описание

(Нет описания для процедуры ACN_DOBASVODKA)

Определение

CREATE PROCEDURE ACN_DOBASVODKA(
    ADATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    CROPFULLNAME VARCHAR(100),
    INCOMEDAY BIGINT,
    INCOMESUM BIGINT,
    OUTCOMEDAY BIGINT,
    OUTCOMESUM BIGINT,
    MELCOMEDAY BIGINT,
    MELCOMESUM BIGINT,
    RESTNET BIGINT)
AS
DECLARE VARIABLE FIRSTDAY DATE;
DECLARE VARIABLE TPARTYID INTEGER;
DECLARE VARIABLE MINDATE DATE;
BEGIN
  select getfirstdayofmonth.FirstDate
  from getfirstdayofmonth(:ADate)
  into :FirstDay;
  FOR
    select Parties.PartyID,
      min(IT.RegDate) MinDate
    from Parties
      join IT on (Parties.PartyID = IT.PartyID)
    where
      ((:APartyDate between BeginDate and coalesce(EndDate, Current_Date) and :IsCurrent <> 1)
      or (:IsCurrent = 1 and Parties.State = 1))
      and IT.RegDate is not null
      and IT.Weight is not null
      and IT.State <> -1
      and Parties.StorageID = 1
    group by Parties.PartyID
    INTO :TPARTYID,
      :MinDate
    do
    begin
      for
      select
        Quality_CropFullName.CropFullName,
        sum(case
             when IT.RegDate = :ADate and IT.OperationID = 1
             then IT.Net end) InComeDay,
        sum(case
             when IT.OperationID = 1
              and IT.RegDate between :FirstDay and :ADate
             then IT.Net end) InComeSum,
        sum(case
             when IT.RegDate = :ADate
             and Operation.OperationSubType = 1
             and Operation.Operationtype in (0,1,2,5,4)
             and IT.OperationID <> 7
             then IT.Net end) OutComeDay,
        sum(case
             when Operation.OperationSubType = 1
             and Operation.Operationtype in (0,1,2,5,4)
             and IT.OperationID <> 7
             and IT.RegDate between :FirstDay and :ADate
             then IT.Net end) OutComeSum,
        sum(case
             when IT.RegDate = :ADate and IT.OperationID = 7
             then IT.Net end) MelComeDay,
        sum(case
             when IT.OperationID = 7
              and IT.RegDate between :FirstDay and :ADate
             then IT.Net end) MelComeSum,
        sum(case
             when Operation.OperationSubType = 0 and Operation.Operationtype in (0, 4)
             then IT.Net
             else -1*IT.Net end) RestNet
      from IT
        join Quality on (IT.SamplerQualityID = Quality.QualityID)
        left join Operation on (IT.OperationID = Operation.OperationID)
        left join Quality_CropFullName (Quality.CropID, ClassID, null, 1) on (0=0)
      where IT.RegDate between :MinDate and :ADate
        and ((Operation.OperationSubType = 0 and Operation.Operationtype in (0, 4))
              or (Operation.OperationSubType = 1 and Operation.Operationtype in (0,1,2,5,4)
              ))
        and IT.State >= 0
        and IT.StorageID = 1
        and (IT.OwnerID = :AOWNERID or (:AOWNERID = -1))
        and IT.PartyID = :TPartyID
        and (Quality.CropID = :ACropID or (:ACropID = -1))
        and (Quality.ClassID = :AClassID or (:AClassID = -1))
        and ( (Operation.OperationSubType = 0 and Operation.Operationtype in (0, 4))
             or (Operation.OperationSubType = 1 and Operation.Operationtype in (0,1,2,5,4)))
      group by
        Quality_CropFullName.CropFullName
      INTO :CROPFULLNAME,
           :INCOMEDAY, 
           :INCOMESUM,
           :OUTCOMEDAY,
           :OUTCOMESUM,
           :MELCOMEDAY,
           :MELCOMESUM,
           :RESTNET
     DO
     BEGIN
      suspend;
    End
  END
END