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

ACC_FORM36C

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_FORM36C(
    DATEBEG DATE,
    PARTYID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    STORINGID INTEGER,
    STORAGEID INTEGER,
    SILAGEID INTEGER,
    PLUGINWORK INTEGER,
    YEARCROP INTEGER)
RETURNS (
    REGDATE DATE,
    ANALIS_ID NUMERIC(9,2),
    OWNER DECIMAL(15,2),
    COMNET BIGINT,
    HUMIDITY_C INTEGER,
    HUMIDITY NUMERIC(4,1),
    DIRT_C INTEGER,
    DIRT NUMERIC(4,1),
    EXPENSENET BIGINT,
    HUMIDITY_C_EXP INTEGER,
    EXPENSEHUMIDITY NUMERIC(4,1),
    DIRT_C_EXP INTEGER,
    EXPENSEDIRT NUMERIC(4,1),
    OPERATIONID INTEGER,
    REBCOMNET INTEGER,
    REBHUM_C INTEGER,
    REBDIRT_C INTEGER,
    REBEXPNET INTEGER,
    REBEXPHUM INTEGER,
    REBEXPDIRT INTEGER,
    NUMDOC VARCHAR(31),
    HUMWEIGHT BIGINT)
AS
declare variable PARTYBEGFDATE date;
BEGIN
 select min(parties.begindate)
 from parties
 where (parties.partyid = :partyid
         or (coalesce(:partyID,-100) = -100 and parties.state = 1))
     and parties.cropid = :cropid
 into: partybegfdate;

  FOR
    select
      it.regdate,
      case when numanal.qualityvalue is null
        then it.inventoryid
        else numanal.qualityvalue
      end as analis_id,
--IG
     case when it.operationid = 22 then -1
       else
       it.ownerid
     end as ownerid,
--IG
      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)
          then it.orderloaditemid
          when ((operation.operationtype in (1,3,4,5) or (operation.operationsubtype = 1)))
          then invoices.invoicenumber
         else it.registerid
        end
      end as numdoc,
      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 (4,2)))/100 as integer)
       else
        cast(sum(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 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*
        cast(form36acoming.rebillingdirt as  numeric (3,1))) /100 as integer) as rebdirt_c,

      sum(form36aexpense.rebillingnet) as rebexpnet,
      cast(sum( form36aexpense.rebillingnet*
        cast(form36aexpense.rebillinghumidity as numeric (3,1))) / 100 as integer) as rebexphum,
      cast(sum( 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 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 ittransport on it.inventoryid = ittransport.inventoryid
     left join operation on it.operationid = operation.operationid
     left join invoices on it.invoiceid = invoices.invoiceid
     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 cropsort on quality.sortid = cropsort.sortid
    where (it.regdate >= :partybegfdate and it.regdate < :datebeg)
      and ((it.operationid in (1,4,5,7,13,18,19,22,23,45,46,70) )
           or (it.operationid = 17 and :pluginwork <> 4 ))
      and ((parties.state = 1 and coalesce(:partyid, -100) = -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 (cropsort.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.net is not null
      and it.net <> 0
      and (YearCrop.qualityvalue = coalesce(:YearCrop, -1) or (coalesce(:YearCrop, -1) = -1))
    group by it.regdate, it.providerid, numdoc, ownerid,
      it.operationid, quality.classid,
      analis_id
    order by it.regdate
    INTO :regdate,
         :analis_id,
         :owner,
         :numdoc,
         :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