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

FORWARDER_FIRMGROUPSUM

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_FIRMGROUPSUM(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ASTORAGEID INTEGER,
    APROVIDERID INTEGER,
    ATRSUBTYPE INTEGER,
    ASTATIONID INTEGER)
RETURNS (
    SUMWEIGHT BIGINT,
    OUTCOME INTEGER,
    INCOME INTEGER,
    T_REGDATE DATE)
AS
begin

   if (:CROPID = -1) then CROPID = null;
   if (:CLASSID = -1) then CLASSID = null;
--*5--
 for    -- 17
    select
      cast(0 as integer) as income,
      exportacts.regdate,
      sum(case
        when (exportacts.regdate >= :begindate and (:aownerid <> -1) and itm.operationid = 17)
       then itm.net
      end)  as outcome,
      sum(case
        when (:aownerid <> -1 and itm.operationid = 17) then   -itm.net
       -- else  -itm.net
       end)  as sumweight
    from it itm
      join getworkparties(:iscurrent,:apartydate,
        case when coalesce(:cropid,-1) = -1 then null else :cropid end,
        case when coalesce(:classid,-1) = -1 then null else :classid end,
        case when coalesce(:astorageid,-1) = -1 then null else coalesce(:astorageid,-1) end)
          on (itm.partyid = getworkparties.partyid)
      join quality on (itm.samplerqualityid = quality.qualityid)
      join crop on (quality.cropid = crop.cropid and itm.operationid = 5)
      left join exportacts on (itm.orderloaditemid = exportacts.orderitemid and itm.operationid in (17))
    where (itm.regdate <=:enddate)
      and itm.operationid = 17
      and itm.providerid  = :aownerid
      and (:aownerid <> -1)
      and exportacts.operationid = 17
      and itm.net > 0
      and (exportacts.providerid  = :aownerid and (coalesce(:aownerid,-1) <> -1))
      and (exportacts.ownerid  = coalesce(:aownerid,-1) and (coalesce(:aownerid,-1) <> -1))
      and (quality.cropid = :cropid or ( coalesce(:cropid,-1) = -1))
      and (quality.classid = :classid or (coalesce(:classid,-1) = -1))
      and (itm.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )
      and (itm.ownerid = coalesce(:aownerid,-1) or (coalesce(:aownerid,-1) = -1))
      and coalesce(:astationid,-1) = -1
      and coalesce(:atrsubtype,-1) = -1
      and itm.state >=0
      group by
        exportacts.regdate
    --//--
    into :income,
         :t_regdate,
         :outcome,
         :sumweight
  do
  begin
    suspend;
  end
--//----------
 for --5--
    select
      cast(0 as integer) income, 
      ea.actdate  as t_regdate,
      sum (case
        when (ea.regdate >= :begindate)  then itm.net end) as outcome,
      sum (-itm.net) as sumweight
    from exportacts ea
      left join it itm on (ea.orderitemid = itm.orderloaditemid)
      join getworkparties(:iscurrent,:apartydate,
        case when coalesce(:cropid,-1) = -1 then null else :cropid end,
        case when coalesce(:classid,-1) = -1 then null else :classid end,
        case when coalesce(:astorageid,-1) = -1 then null else coalesce(:astorageid,-1) end)
          on (itm.partyid = getworkparties.partyid)
      left join quality on (EA.QualityID = quality.qualityid)
      join crop on (quality.cropid = crop.cropid)
    where ea.actdate <=:enddate
      and ea.operationid = 5
      and (ea.state = 1 or (ea.exportactid is null))
      and itm.operationid = 5
      and (itm.ownerid = :aownerid or (coalesce(:aownerid,-1) = -1))
      and (ea.stationid = coalesce(:astationid,-1) or (coalesce(:astationid,-1) = -1))
      and (ea.transportsubtypeid = coalesce(:atrsubtype,-1) or (coalesce(:atrsubtype,-1) = -1))
      and (quality.cropid = :cropid or (coalesce(:cropid,-1) = -1))
      and (quality.classid = :classid or (coalesce(:classid,-1) = -1))
      and itm.state >= 0
      and itm.storageid = 1
      and crop.croptypeid <> 3
     -- and Parties.State =1
    group by
      ea.actdate
    into :income,
         :t_regdate,
         :outcome,
         :sumweight
  do
  begin
    suspend;
  end --*/--/*----
  for -- 1,17
    select
     sum (case
            when eaiv.operationid in (1,17)
              and (exportacts.regdate >= :begindate and exportacts.operationid = 17
                or (exportacts.actdate >= :begindate and exportacts.operationid = 1))
            then eaiv.weight
          end) as income,
     sum (case
             when eaiv.operationid = 5 and eaiv.weight > 0
              and (exportacts.regdate >= :begindate )
             then eaiv.weight
          end) as outcome,
      sum (case
             when exportacts.operationid in (1,17) and eaiv.weight > 0 then eaiv.weight
             when exportacts.operationid = 5 and eaiv.weight > 0 then -eaiv.weight
           end) as sumweight
    from exportacts
      join quality on (exportacts.qualityid = quality.qualityid)
      join exportactitems eaiv on (exportacts.exportactid = eaiv.exportactid)
      join it it1 on (eaiv.inventoryid = it1.inventoryid)
      join getworkparties(:iscurrent,:apartydate,
        case when coalesce(:cropid,-1) = -1 then null else :cropid end,
        case when coalesce(:classid,-1) = -1 then null else :classid end,
        case when coalesce(:astorageid,-1) = -1 then null else coalesce(:astorageid,-1) end)
          on (it1.partyid = getworkparties.partyid)
    where((exportacts.actdate <=:enddate and  exportacts.operationid in (1)) or
           (exportacts.regdate <=:enddate and
           (exportacts.operationid = 17 and (coalesce(:aownerid,-1) <> -1))))
      and (exportacts.ownerid = :aownerid or (coalesce(:aownerid,-1) = -1))
      and eaiv.operationid in (1,17)
      and (quality.cropid = :cropid or (coalesce(:cropid,-1) = -1))
      and (quality.classid = :classid or (coalesce(:classid,-1) = -1))
      and exportacts.state in (0,1)
      and it1.state >=0
      and (it1.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )
      and (exportacts.providerid = coalesce(:aproviderid,-1) or (coalesce(:aproviderid,-1) = -1))
      and (exportacts.stationid = coalesce(:astationid,-1) or (coalesce(:astationid,-1) = -1))
      and (exportacts.transportsubtypeid = coalesce(:atrsubtype,-1) or (coalesce(:atrsubtype,-1) = -1))

    into :income,
         :outcome,
         :sumweight
  do
  begin
    suspend;
  end --*/

-- /*--// 22, 64, 70
  for
    select
      cast(0 as integer),
      (case
         when it.regdate >=:begindate
         then case
                when it.operationid in (64,65) then -it.weight
                else  it.net
              end
           end  ) as outcome,
      case
        when it.operationid = 64 then it.weight
        else -it.net
      end as sumweight
    from it
      join getworkparties(:iscurrent,:apartydate,
        case when coalesce(:cropid,-1) = -1 then null else :cropid end,
        case when coalesce(:classid,-1) = -1 then null else :classid end,
        case when coalesce(:astorageid,-1) = -1 then null else coalesce(:astorageid,-1) end)
          on (it.partyid = getworkparties.partyid)
      left join quality q1 on (it.samplerqualityid = q1.qualityid)
      left join quality wastename on (it.operationparam = wastename.qualityid)
      left join ittransport on (it.inventoryid = ittransport.inventoryid)
      left join transporttype on (ittransport.transporttypeid = transporttype.transporttypeid)
      left join invoices on (not(it.operationid=22 and it.net<0) and it.invoiceid = invoices.invoiceid)
    where (it.regdate <=:enddate)
      and (it.ownerid = :aownerid or (coalesce(:aownerid,-1) = -1))
      and (q1.cropid = :cropid or (coalesce(:cropid,-1) = -1))
      and (q1.classid = :classid or (coalesce(:classid,-1) = -1))
      and ((it.operationid in (64,70,65))
           or (it.operationid = 22 and wastename.cropid = 9 and wastename.classid in (41,141) ))
      and it.state >= 0
      and (it.providerid = coalesce(:aproviderid,-1) or (coalesce(:aproviderid,-1) = -1))
      and (invoices.stationid = coalesce(:astationid,-1) or (coalesce(:astationid,-1) = -1))
      and (transporttype.transportsubtype = coalesce(:atrsubtype,-1) or (coalesce(:atrsubtype,-1) = -1))
      and (it.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )

    into :income,
         :outcome,
         :sumweight
  do
  begin
    suspend;
  end --*/
end