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

ACN_PRILACT

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

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

Описание

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

Определение

CREATE PROCEDURE ACN_PRILACT(
    BEGINDATE DATE,
    ENDDATE DATE,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ASORTID INTEGER,
    ASTORAGEID INTEGER,
    ACURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    TPARTYID INTEGER,
    ANALIS_ID INTEGER,
    NUMDOC VARCHAR(31),
    PARTYBDATE DATE,
    PARTYEDATE DATE,
    OWNERID INTEGER,
    OWNERFULLNAME VARCHAR(100),
    NET45 INTEGER,
    NETI17 INTEGER,
    NETI INTEGER,
    NET46 INTEGER,
    NETO17 INTEGER,
    NETO INTEGER,
    NETW INTEGER,
    NETW3K INTEGER,
    NETDELTA INTEGER,
    NETALLIN INTEGER,
    NETALLOUT INTEGER,
    CDIRT45 BIGINT,
    CDIRTI17 BIGINT,
    CDIRTI BIGINT,
    CDIRT46 BIGINT,
    CDIRTO17 BIGINT,
    CDIRTO BIGINT,
    CDIRTW BIGINT,
    CDIRTW3K BIGINT,
    CDIRTALLIN BIGINT,
    CDIRTALLOUT BIGINT,
    CHUM45 BIGINT,
    CHUMI17 BIGINT,
    CHUMI BIGINT,
    CHUM46 BIGINT,
    CHUMO17 BIGINT,
    CHUMO BIGINT,
    CHUMW BIGINT,
    CHUMW3K BIGINT,
    CHUMALLIN BIGINT,
    CHUMALLOUT BIGINT,
    CLASSID INTEGER,
    REGDATE DATE)
AS
begin
  if (:acurrent = 1) then
    select
      parties.partyid,
      parties.begindate,
      coalesce(parties.enddate, current_date)
    from parties
    where parties.cropid = :acropid
      and (parties.classid = :aclassid
        or (:aclassid = -1 and parties.classid is null)
        or (:aclassid <> -1 and parties.classid is null))
      and parties.storageid = :astorageid
      and parties.state = 1
    into :tpartyid,
         :partybdate,
         :partyedate;
  else
    select
      parties.partyid,
      parties.begindate,
      coalesce(parties.enddate, current_date)
    from parties
    where
      :apartydate between parties.begindate
        and coalesce(parties.enddate, current_date)
      and parties.cropid = :acropid
      and (parties.classid = :aclassid
        or (:aclassid = -1 and parties.classid is null)
        or (:aclassid <> -1 and parties.classid is null))
      and parties.storageid = :astorageid
    INTO :tpartyid,
         :partybdate,
         :partyedate;
     --//
   For
    select
      it.regdate,
      owner.clientfullname ownerfullname,
      it.ownerid,
      case when qa.qualityvalue is null
        then it.inventoryid
        else qa.qualityvalue
      end as analis_id,
      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(acn_30oper.net45) net45,
      sum(acn_30oper.net17i) neti17,
      sum(acn_30oper.neti)  neti,

      sum(acn_30oper.net46) net46,
      sum(acn_30oper.net17o) neto17,
      sum(acn_30oper.net5)  neto,
      sum(acn_30oper.netw) netw,
      sum(coalesce(acn_30oper.net3k,0)) netw3k,
     --Dirt--
      cast(sum(acn_30oper.net45 *
         cast(acn_30oper.dirt45 as numeric (3,1)))/100 as integer) cdirt45,
      cast(sum(acn_30oper.net17i *
          cast(acn_30oper.dirt17i as numeric (3,1)))/100 as integer)  cdirti17,
      cast(sum(acn_30oper.neti *
          cast(acn_30oper.dirti as numeric (3,1)))/100 as integer)  cdirti,


      cast(sum(acn_30oper.net46 *
          cast(acn_30oper.dirt46 as numeric (3,1)))/100 as integer)  cdirt46,
      cast(sum(acn_30oper.net17o *
          cast(acn_30oper.dirt17o as numeric (3,1)))/100 as integer)  cdirto17,
      cast(sum(acn_30oper.net5 *
          cast(acn_30oper.dirt5 as numeric (3,1)))/100 as integer)  cdirto,
      cast(sum(acn_30oper.netw *
          cast(acn_30oper.dirtw as numeric (3,1)))/100 as integer)  cdirtw,
      cast(sum(acn_30oper.net3k *
          cast(acn_30oper.dirt3k as numeric (4,2)))/100 as integer)  cdirtw3k,
      --Hum--
      cast(sum(acn_30oper.net45 *
         cast(acn_30oper.hum45 as numeric (3,1)))/100 as integer) chum45,
      cast(sum(acn_30oper.net17i *
          cast(acn_30oper.hum17i as numeric (3,1)))/100 as integer)  chum7,
      cast(sum(acn_30oper.neti *
          cast(acn_30oper.humi as numeric (3,1)))/100 as integer)  chumi,

      cast(sum(acn_30oper.net46 *
          cast(acn_30oper.hum46 as numeric (3,1)))/100 as integer)  chum46,
      cast(sum(acn_30oper.net17o *
          cast(acn_30oper.hum17o as numeric (3,1)))/100 as integer)  chumo17,
      cast(sum(acn_30oper.net5 *
          cast(acn_30oper.hum5 as numeric (3,1)))/100 as integer)  chumo,
      cast(sum(acn_30oper.netw *
          cast(acn_30oper.humw as numeric (3,1)))/100 as integer)  chumw,
      cast(sum(acn_30oper.net3k *
          cast(acn_30oper.hum3k as numeric (4,2)))/100 as integer)  chumw3k,
      q.classid
    from it
      left join operation op on (it.operationid = op.operationid)
      left join clients owner on (it.ownerid = owner.clientid)
      left join quality q on (it.BuhQualityid = q.qualityid)
      left join qualitydata humq on (it.BuhQualityid = humq.qualityid
        and humq.qualitytypeid = 2)
      left join qualitydata dirtq on (it.BuhQualityid = dirtq.qualityid
        and dirtq.qualitytypeid = 13)
      left join qualitydata qa on (it.BuhQualityid = qa.qualityid
        and qa.qualitytypeid = 1)
      left join quality wq on (it.operationparam = wq.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 acn_30oper(it.operationid,dirtq.qualityvalue,
        humq.qualityvalue,coalesce(wq.classid,0),it.net) on (0 = 0)
    where (   it.operationid = 45
          or (it.operationid = 46)
          or (it.regdate BETWEEN :begindate and :enddate) )
      and it.storageid = :astorageid
      and it.state >= 0
      and it.parentid >= 0
      and it.net <> 0
      and it.partyid = :tpartyid
      and q.cropid = :acropid
      and (q.classid = :aclassid or (coalesce(:aclassid,-1) = -1))
      and ((q.sortid = :asortid) or (coalesce(:asortid,-1) = -1))
    group by
      it.regdate,
      owner.clientfullname,
      it.ownerid,analis_id,numdoc, q.classid
   into :regdate,
      :ownerfullname,
      :ownerid,
      :analis_id,
      :numdoc,
      :net45,
      :neti17,
      :neti,
      :net46,
      :neto17,
      :neto,
      :netw, 
      :netw3k,
      :cdirt45,
      :cdirti17,
      :cdirti,
      :cdirt46,
      :cdirto17,
      :cdirto,
      :cdirtw,
      :cdirtw3k,
      :chum45,
      :chumi17,
      :chumi,
      :chum46,
      :chumo17,
      :chumo,
      :chumw,
      :chumw3k,
      :classid
   do
   begin

     netallin = coalesce(:net45,0) + coalesce(:neti17,0) + coalesce(:neti,0);
     netallout = coalesce(:net46,0) + coalesce(:neto17,0) + coalesce(:neto,0) +
       coalesce(:netw,0) + coalesce(:netw3k,0);
     cdirtallin = coalesce(:cdirt45,0) + coalesce(:cdirti17,0) + coalesce(:cdirti,0);
     cdirtallout = coalesce(:cdirt46,0) + coalesce(:cdirto17,0) + coalesce(:cdirto,0) +
       coalesce(:cdirtw,0) + coalesce(:cdirtw3k,0);
     chumallin = coalesce(:chum45,0) + coalesce(:chumi17,0) + coalesce(:chumi,0);
     chumallout = coalesce(:chum46,0) + coalesce(:chumo17,0) + coalesce(:chumo,0) +
       coalesce(:chumw,0) + coalesce(:chumw3k,0);

     netdelta = coalesce(:netallin,0) - coalesce(:netallout,0);
    suspend;
     netallin = 0;
     netallout = 0;
     cdirtallin = 0;
     cdirtallout = 0;
     chumallin = 0;
     chumallout = 0;

   end
end