"localhost:/firebird/data/ILICHEVSK2.FDB". ���������

ACC_FORM36_HEAD_1

������� ���������

�������� ���������

��������

(��� �������� ��� ��������� ACC_FORM36_HEAD_1)

�����������

CREATE PROCEDURE ACC_FORM36_HEAD_1(
    DATEBEG DATE,
    PARTYID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    STORINGID INTEGER,
    STORAGEID INTEGER,
    SILAGEID INTEGER,
    CLIENTID INTEGER,
    CONTRACTID INTEGER,
    YEARCROP INTEGER)
RETURNS (
    REGDATE DATE,
    ANALIS_NUM NUMERIC(9,2),
    NUMDOC VARCHAR(31),
    OWNERPROVIDERNAME VARCHAR(100),
    COMNET BIGINT,
    HUMIDITY_C INTEGER,
    HUMIDITY NUMERIC(4,1),
    DIRT_C INTEGER,
    DIRT NUMERIC(4,1),
    EXPENSENET BIGINT,
    HUMIDITY_C_EXP INTEGER,
    EXPENSEHUMIDITY NUMERIC(9,2),
    DIRT_C_EXP INTEGER,
    EXPENSEDIRT NUMERIC(4,1),
    REBCOMNET BIGINT,
    REBHUM_C NUMERIC(18,2),
    REBHUM NUMERIC(18,2),
    REBDIRT_C NUMERIC(18,2),
    REBDIRT NUMERIC(18,2),
    REBEXPNET BIGINT,
    REBEXPHUM_C NUMERIC(18,2),
    REBEXPHUM NUMERIC(18,2),
    REBEXPDIRT_C NUMERIC(18,2),
    REBEXPDIRT NUMERIC(18,2),
    HUMWEIGHT INTEGER,
    OPERATIONID INTEGER)
AS
BEGIN
  FOR
    select IT.RegDate,
      NumAnal.QualityValue as Analis_Num,
      case when not(IT.OperationID in (45,46)) then
        case when (ittransport.transporttypeid = 2) then
          ittransport.transportnumber
        when (ittransport.transporttypeid in (0,1) and IT.OperationID = 5 and IT.OrderLoadItemID is not null
            and IT.OrderLoadItemID > 0) then
          IT.OrderLoadItemID
        when    ((operation.operationtype in (1,3,4,5) or (operation.operationsubtype = 1))
            or (it.operationId = 17)) then
          invoices.invoicenumber
        else    invoiceregisters.registernumber
      end
      end as NumDoc,
      form36adatatext.ownerprovidername,
      sum( form36acoming.outnet ) as ComNet,
    
      cast(sum( 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,
    
      cast(sum( 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 when it.operationid = 22 then
        cast(sum(form36aexpense.outnet *
            cast(form36aexpense.outhumidity as numeric (9,2)))/100 as integer)
      else
      cast(sum(form36aexpense.outnet *
          cast(form36aexpense.outhumidity as numeric (9,1)))/100 as integer)
       end as Humidity_c_Exp,
    
      case when it.operationid = 22 then
      cast(sum( form36aexpense.outnet * form36aexpense.outhumidity)/ sum(form36aexpense.outnet)
           as numeric(9,2))
      else
        cast(sum( form36aexpense.outnet * form36aexpense.outhumidity)/ sum(form36aexpense.outnet)
           as numeric(3,1))
       end  as ExpenseHumidity,
    
      case when it.operationid = 22 then
       cast(sum(form36aexpense.outnet *  cast(form36aexpense.outdirt as numeric (4,2)))/100  as integer)
       else
       cast(sum(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,
      cast(sum( form36acoming.rebillingnet *
        cast(form36acoming.rebillinghumidity as numeric (3,1)))/100 as integer) as RebHum_c,
      cast(sum( form36acoming.rebillingnet *
        form36acoming.rebillinghumidity)/sum(form36acoming.rebillingnet) as numeric(3,1)) as RebHum,
      cast(sum( form36acoming.rebillingnet *
        cast(form36acoming.rebillingdirt as numeric (3,1)))/100 as integer) as RebDirt_c,
      cast(sum( form36acoming.rebillingnet * form36acoming.rebillingdirt) /
        sum(form36acoming.rebillingnet)  as numeric(3,1)) as RebDirt,

      sum(form36aexpense.rebillingnet) as RebExpNet,
      cast(sum( form36aexpense.rebillingnet *
        cast(form36aexpense.rebillinghumidity as numeric (3,1)))/100 as integer) as RebExpHum_c,
      cast(sum( form36aexpense.rebillingnet *
        form36aexpense.rebillinghumidity)/sum(form36aexpense.rebillingnet) as numeric(3,1)) as RebExpHum,
      cast(sum( form36aexpense.rebillingnet *
        cast(form36aexpense.rebillingdirt as numeric (3,1)))/100 as integer) as RebExpDirt_c,
      cast(sum( form36aexpense.rebillingnet * form36aexpense.rebillingdirt) /
        sum(form36aexpense.rebillingnet)  as numeric(3,1)) as RebExpDirt,

      cast(sum (ittransport.humweight) as integer) as humweight,
      IT.OperationID
    from IT
      left join QualityData NumAnal on (IT.BuhQualityID = NumAnal.QualityID and NumAnal.qualitytypeid = 1)
      left join QualityData Vlagn on (IT.BuhQualityID = Vlagn.QualityID and Vlagn.qualitytypeid = 2)
      left join QualityData Sorn on (IT.BuhQualityID = Sorn.QualityID and Sorn.qualitytypeid = 13)
      left join QualityData YearCrop on (IT.BuhQualityid = YearCrop.qualityid and YearCrop.qualitytypeid = 6)
      left join Parties on  IT.partyid = Parties.partyid
      left join Quality on IT.SamplerQualityID = Quality.QualityID
      left join Clients Owner on IT.OwnerID = Owner.ClientID
      left join Clients Provider on IT.ProviderID = Provider.ClientID
      left join ITTransport on IT.InventoryID = ITTRansport.InventoryID
      left join Operation on IT.OperationID = Operation.OperationID
      left join operations_operationname(IT.OperationID,  IT.OperationParam) on (0=0)
      left join Invoices on IT.InvoiceID = Invoices.InvoiceID
      left join InvoiceRegisters on (It.RegisterID = InvoiceRegisters.InvoiceRegisterID)
      left join Stations on Invoices.StationID = Stations.StationID
      left join form36adatatext(ITTransport.TransportNumber,Stations.StationName,operations_operationname.operationname,
        IT.OperationID, Provider.ClientName, Owner.ClientName, ITTransport.TransporttypeID) on (0=0)
      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 < :DateBeg)
      and (IT.OperationID in (1,4,5,7,13,17,18,19,22,23,45,46,70) )
      and ((Parties.State = 1 and :PartyID = -100) or (Parties.PartyID = :PartyID))
      and Quality.CropID = :CropID
      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 (Owner.ClientID = :ClientID or coalesce(:ClientID,-1) = -1 )
      and (IT.ContractID = :ContractID or coalesce(:ContractID,-1) = -1 )
      and IT.Net is not null
      and IT.Net <> 0
      and (YearCrop.qualityvalue = coalesce(:YearCrop, -1) or (coalesce(:YearCrop, -1) = -1))
    group by IT.RegDate, NumAnal.QualityValue, 3,
    form36adatatext.ownerprovidername, IT.OperationID,  Quality.ClassID
    order  by IT.RegDate
    INTO :REGDATE,
         :ANALIS_NUM,
         :NUMDOC,
         :OWNERPROVIDERNAME,
         :COMNET,
         :HUMIDITY_C,
         :HUMIDITY,
         :DIRT_C,
         :DIRT,
         :EXPENSENET,
         :HUMIDITY_C_EXP,
         :EXPENSEHUMIDITY,
         :DIRT_C_EXP,
         :EXPENSEDIRT,
         :REBCOMNET,
         :REBHUM_C, :RebHum,
         :REBDIRT_C, :REBDIRT,
         :REBEXPNET,
         :REBEXPHUM_C,:REBEXPHUM,
         :REBEXPDIRT_C,:REBEXPDIRT,
         :HUMWEIGHT,
         :OPERATIONID
  DO
  BEGIN
    SUSPEND;
  END
END