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

ACC_FORM36CLIENT

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_FORM36CLIENT(
    DATEBEG DATE,
    PARTYID INTEGER,
    ACROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    STORINGID INTEGER,
    STORAGEID INTEGER,
    SILAGEID INTEGER,
    CLIENTID INTEGER,
    CONTRACTID INTEGER)
RETURNS (
    CROPID INTEGER,
    COMNET BIGINT,
    HUMIDITY_C BIGINT,
    HUMIDITY NUMERIC(18,2),
    DIRT_C BIGINT,
    DIRT NUMERIC(18,2),
    EXPENSENET BIGINT,
    HUMIDITY_C_EXP BIGINT,
    EXPENSEHUMIDITY NUMERIC(18,2),
    DIRT_C_EXP BIGINT,
    EXPENSEDIRT NUMERIC(18,2),
    REBCOMNET BIGINT,
    REBHUM_C NUMERIC(18,2),
    REBDIRT_C NUMERIC(18,2),
    REBEXPNET BIGINT,
    REBEXPHUM NUMERIC(18,2),
    REBEXPDIRT NUMERIC(18,2),
    OPERATIONID INTEGER,
    HUMWEIGHT BIGINT)
AS
BEGIN
  FOR
    select Quality.cropid,
      sum( form36acoming.outnet ) as ComNet,
      sum(cast( form36acoming.outnet *
        cast(form36acoming.outhumidity as numeric (3,1))/100 as integer)) as Humidity_c,
      cast(sum( form36acoming.outnet *
        form36acoming.outhumidity)/sum(form36acoming.outnet) as numeric(3,1)) as Humidity,
      sum( cast(form36acoming.outnet *
        cast(form36acoming.outdirt as numeric (3,1))/100 as integer)) as Dirt_c,
      cast(sum( form36acoming.outnet * form36acoming.outdirt) /
        sum(form36acoming.outnet)  as numeric(3,1)) as Dirt,
    
      sum(form36aexpense.outnet) as ExpenseNet,
      case IT.OperationID when 22 then
        sum(cast(form36aexpense.outnet *  cast(form36aexpense.outhumidity as numeric (4,2))/100  as integer))
         else
           sum(cast(form36aexpense.outnet *  cast(form36aexpense.outhumidity as numeric (4,1))/100  as integer))
      end as Humidity_c_Exp,

      cast(sum( form36aexpense.outnet * form36aexpense.outhumidity)/ sum(form36aexpense.outnet)
           as numeric(3,1))  as ExpenseHumidity,

      case IT.OperationID when 22 then
        sum(cast(form36aexpense.outnet *  cast(form36aexpense.outdirt as numeric (4,2))/100  as integer))
         else
           sum(cast(form36aexpense.outnet *  cast(form36aexpense.outdirt as numeric (4,1))/100  as integer))
      end    as Dirt_c_Exp,
      cast(sum( form36aexpense.outnet * form36aexpense.outdirt)/
        sum(form36aexpense.outnet) as numeric(3,1)) as ExpenseDirt,

      sum(form36acoming.rebillingnet) as RebComNet,
      sum(cast(form36acoming.rebillingnet*
        cast(form36acoming.rebillinghumidity as numeric(3,1)) / 100 as integer))  as RebHum_c,
      sum( cast(form36acoming.rebillingnet*
        cast(form36acoming.rebillingdirt as  numeric (3,1)) /100 as integer)) as RebDirt_c,

      sum(form36aexpense.rebillingnet) as RebExpNet,
      sum(cast( form36aexpense.rebillingnet*
        cast(form36aexpense.rebillinghumidity as numeric (3,1)) / 100 as integer)) as RebExpHum,
      sum(cast( form36aexpense.rebillingnet*
       cast(form36aexpense.rebillingdirt as numeric (3,1)) /100 as integer)) as RebExpDirt,
      sum(ittransport.humweight)as humweight,
    IT.OperationID
    from IT
     left join QualityData Vlagn on (IT.BuhQualityID = Vlagn.QualityID and Vlagn.qualitytypeid = 2)
     left join ITTransport on (IT.inventoryid = ittransport.inventoryid)
     left join QualityData Sorn on (IT.BuhQualityID = Sorn.QualityID and Sorn.qualitytypeid = 13)
     left join Parties on  IT.partyid = Parties.partyid
     left join Quality on IT.SamplerQualityID = Quality.QualityID
     left join form36aexpense(it.operationid, Sorn.QualityValue, Vlagn.QualityValue,
         it.net) on (0=0)
     left join form36acoming(Sorn.QualityValue, Vlagn.QualityValue,IT.OperationID,
         IT.Net, Quality.CropID) on (0=0)
     left join Contracts on IT.contractid = Contracts.contractid
    where (IT.RegDate >= Parties.BeginDate and IT.RegDate < :DateBeg)
      and IT.OperationID in (1,4,5,13,17,18,19,22,23,45,46,70)
      and ((Parties.State = 1 and :PartyID = -100) or (Parties.PartyID = :PartyID))
      and Quality.CropID = :ACropID
      and it.state >= 0
      and it.parentid <> -1
      and (Quality.ClassID = :ClassID or coalesce(:ClassID, -1) = -1 )
      and (Quality.SortID = :SortID or coalesce(:SortID, -1) = -1 )
      and (IT.StorageID = :StorageID or coalesce(:StorageID, -1) = -1 )
      and (IT.StoringID = :StoringID or coalesce(:StoringID, -1) = -1 )
      and (IT.SilageID = :SilageID or coalesce(:SilageID,-1) = -1 )
      and (IT.OwnerID = :ClientID or coalesce(:ClientID,-1) = -1 )
      and (Contracts.ContractID = :ContractID or coalesce(:ContractID,-1) = -1 )
    group by Quality.cropid, IT.OperationID
    INTO :CROPID,
         :COMNET,
         :HUMIDITY_C,
         :HUMIDITY,
         :DIRT_C,
         :DIRT,
         :EXPENSENET,
         :HUMIDITY_C_EXP,
         :EXPENSEHUMIDITY,
         :DIRT_C_EXP,
         :EXPENSEDIRT,
         :REBCOMNET,
         :REBHUM_C,
         :REBDIRT_C,
         :REBEXPNET,
         :REBEXPHUM,
         :REBEXPDIRT,
         :humweight,
         :OPERATIONID
  DO
  BEGIN
    SUSPEND;
  END
END