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

FORWARDER_FIRMGROUP

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_FIRMGROUP(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ASTORAGEID INTEGER,
    APROVIDERID INTEGER,
    ATRANSPORTSUBTYPE INTEGER,
    ASTATIONID INTEGER)
RETURNS (
    EXPORTACTID INTEGER,
    ACTNUMBER VARCHAR(31),
    REGDATE DATE,
    ACTDATE DATE,
    PROVIDERFULLNAME VARCHAR(100),
    SUMINVOICENET BIGINT,
    SUMWEIGHT BIGINT,
    SUMDEFICIT BIGINT,
    OPERATIONID INTEGER,
    CROPFULLNAME VARCHAR(100),
    OWNERFULLNAME VARCHAR(100),
    OWNERID INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    STATIONFULLNAME VARCHAR(100),
    INVENTORYID INTEGER,
    OUTCOME INTEGER,
    INCOME INTEGER,
    ORDERITEMID INTEGER,
    PARTYDATE DATE,
    STORAGENAME VARCHAR(100))
AS
begin
--/*
 partydate = :apartydate;
 for -- 1
    select
      exportacts.exportactid,
      exportacts.actnumber,
      exportacts.regdate,
      case
        when exportacts.operationid = 17
        then exportacts.regdate
        else exportacts.actdate
      end as actdate,
      exportacts.ownerid,
      exportacts.transportsubtypeid,
      exportacts.operationid,
      provider.clientfullname as providerfullname,
      owner.clientfullname as ownerfullname,
      quality_cropfullname.cropfullname,
      case exportacts.transportsubtypeid
         when 0 then
          case
            when exportacts.operationid = 17 then 'приказ №'||orderload.ordernumber
          end
         else
           case when exportacts.operationid = 17
             then 'приказ №'||orderload.ordernumber||' '||stations.stationfullname
             else stations.stationfullname
           end
       end stationfullname,
      cast(null as integer) orderitemid,
      sum (case
             when exportacts.operationid = 1
             then ittransport.invoicenet
           end) as suminvoicenet,
      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,
     sum (case
             when exportacts.operationid = 1
             then it1.net - ittransport.invoicenet
          end) as sumdeficit,
     sum (case
             when eaiv.operationid = 5 and eaiv.weight > 0 then eaiv.weight
          end) as outcome,
     sum (case
             when eaiv.operationid in (1,17) then eaiv.weight
          end) as income,
      cast(null as integer) as inventoryid,
      Storage.storageshortname
    from exportacts
      left join exportactitems eaiv on (exportacts.exportactid = eaiv.exportactid)
      left join it it1 on (eaiv.inventoryid = it1.inventoryid)
      join getworkparties(:iscurrent,:apartydate,
            case when coalesce(:cropid,-1) = -1 then null else coalesce(:cropid,-1) end,
            case when coalesce(:classid,-1) = -1 then null else coalesce(:classid,-1) end,
            case when coalesce(:astorageid,-1) = -1 then null else coalesce(:astorageid,-1) end )
        on (IT1.partyid = getworkparties.partyid)
      left join Storage on (IT1.storageid = Storage.StorageID)
      left join clients provider on (exportacts.providerid = provider.clientid)
      left join clients owner on (exportacts.ownerid = owner.clientid)
      left join quality on (exportacts.qualityid = quality.qualityid)
      left join ittransport on (eaiv.inventoryid = ittransport.inventoryid)
      left join quality_cropfullname(quality.cropid,quality.classid,null,1)  on (0 = 0)
      left join stations on (exportacts.stationid = stations.stationid)
      left join orderloaditems on (exportacts.orderitemid = orderloaditems.orderloaditemid)
      left join orderload on (orderloaditems.orderloadid = orderload.orderloadid)
    where exportacts.actdate between :begindate and :enddate
      and exportacts.operationid = 1
    --  and Parties.State = 1
      and eaiv.operationid = 1
      and (exportacts.ownerid = coalesce(:aownerid,-1) or (coalesce(:aownerid,-1) = -1))
      and (exportacts.providerid = coalesce(:aproviderid,-1) or (coalesce(:aproviderid,-1) = -1))
      and (stations.stationid = coalesce(:astationid,-1) or (coalesce(:astationid,-1) = -1))
      and (exportacts.transportsubtypeid = coalesce(:atransportsubtype,-1) or (coalesce(:atransportsubtype,-1) = -1))
      and (quality.cropid = coalesce(:cropid,-1) or (coalesce(:cropid,-1) = -1))
      and (quality.classid = coalesce(:classid,-1) or (coalesce(:classid,-1) = -1))
      and it1.state >=0
      and (it1.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )
    group by
      exportacts.exportactid,
      exportacts.actnumber,
      exportacts.regdate,
      exportacts.actdate,
      exportacts.ownerid,
      exportacts.transportsubtypeid,
      exportacts.operationid,
      provider.clientfullname,
      owner.clientfullname,
      quality_cropfullname.cropfullname,
      stationfullname,
      Storage.storageshortname
    into :exportactid,
         :actnumber,
         :regdate,
         :actdate,
         :ownerid,
         :transportsubtypeid,
         :operationid,
         :providerfullname,
         :ownerfullname,
         :cropfullname,
         :stationfullname,
         :orderitemid,
         :suminvoicenet,
         :sumweight,
         :sumdeficit,
         :outcome, 
         :income,
         :inventoryid,
         :storagename
  do
  begin
    suspend;
  end --//*/
  for --5--
    select
      ea.exportactid,
      case  orderload.transportsubtypeid
        when 0 then 'приказ №'||orderload.ordernumber
        when 1 then 'приказ №'||orderload.ordernumber||' '||coalesce(stations.stationname, '')
        when 2 then 'приказ №'||orderload.ordernumber||' '||coalesce(ships.shipname, '')
      end stationfullname,
      ea.actnumber,
      itm.regdate,
      /*case when EA.ExportActID is null then ITm.RegDate else EA.ActDate end as ActDate,*/
      ea.actdate,
      itm.ownerid,
      orderload.transportsubtypeid,
      itm.operationid,
      provider.clientfullname as providerfullname,
      owner.clientfullname as ownerfullname,
      quality_cropfullname.cropfullname,
      ea.orderitemid,
      sum (itm.net) as sumweight,
      sum (itm.net) as outcome,
      cast(null as integer) as income,
      cast(null as integer) as inventoryid,
      cast(null as integer) as sumdeficit,
      cast(null as integer) as suminvoicenet,
      Storage.storageshortname
    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 coalesce(:cropid,-1) end,
            case when coalesce(:classid,-1) = -1 then null else coalesce(:classid,-1) end,
            case when coalesce(:astorageid,-1) = -1 then null else coalesce(:astorageid,-1) end )
          on (itm.partyid = getworkparties.partyid)
     left join orderloaditems on (itm.orderloaditemid = orderloaditems.orderloaditemid)
     left join Storage on (ITm.storageid = Storage.StorageID)
      left join orderload on (orderloaditems.orderloadid = orderload.orderloadid)
      left join clients provider on (itm.providerid = provider.clientid)
      left join clients owner on (itm.ownerid = owner.clientid)
      left join quality on (itm.samplerqualityid = quality.qualityid)
      left join crop on (quality.cropid = crop.cropid)
      left join it its on (orderload.transportsubtypeid = 2
        and itm.relationid  = its.inventoryid and its.parentid=-1)

      left join stations on (orderload.destinationpointid = stations.stationid)
      left join ships on (its.relationid = ships.shipid)
      left join quality_cropfullname(quality.cropid,quality.classid,null,1)  on (0 = 0)
    where ea.actdate between :begindate and :enddate
      and ea.operationid = 5
      and (ea.state = 1 or (ea.exportactid is null))
      and itm.operationid = 5
      and (ea.ownerid = coalesce(:aownerid,-1) or (coalesce(:aownerid,-1) = -1))
      and (ea.providerid = coalesce(:aproviderid,-1) or (coalesce(:aproviderid,-1) = -1))
      and (stations.stationid = coalesce(:astationid,-1) or (coalesce(:astationid,-1) = -1))
      and (ea.transportsubtypeid = coalesce(:atransportsubtype,-1) or (coalesce(:atransportsubtype,-1) = -1))
      and (quality.cropid = coalesce(:cropid,-1) or (coalesce(:cropid,-1) = -1))
      and (quality.classid = coalesce(:classid,-1) or (coalesce(:classid,-1) = -1))
      and itm.state >= 0
      and (itm.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )
      and crop.croptypeid <> 3
     -- and Parties.State =1
    group by
      ea.exportactid,
      stationfullname,
      ea.actnumber,
      itm.regdate,
      ea.actdate,
     -- case         when EA.ExportActID is null then ITm.RegDate         else EA.ActDate       end,
      itm.ownerid,
      orderload.transportsubtypeid,
      itm.operationid,
      provider.clientfullname,
      owner.clientfullname,
      quality_cropfullname.cropfullname,
      ea.orderitemid,
      Storage.storageshortname
    into :exportactid,
         :stationfullname,
         :actnumber,
         :regdate,
         :actdate,
         :ownerid,
         :transportsubtypeid,
         :operationid,
         :providerfullname,
         :ownerfullname,
         :cropfullname,
         :orderitemid,
         :sumweight,
         :outcome, 
         :income,
         :inventoryid,
         :sumdeficit,
         :suminvoicenet,
         :storagename
  do
      begin
        suspend;
      end
 --/*-
  for    -- 17
    select
      cast(null as integer) exportactid,
      orderload.ordernumber,
      it.regdate,
      it.regdate,
      case
        when orderload.ownerid = :aownerid then orderload.ownerid
        when orderload.providerid = :aownerid then orderload.providerid
        else -1
      end ownerid, --!
      17 as transportsubtypeid,
      orderload.operationid,
      case
        when orderload.ownerid = :aownerid then provider.clientfullname
        else owner.clientfullname
      end providerfullname,
      case
        when orderload.ownerid = :aownerid then owner.clientfullname
        else provider.clientfullname
      end ownerfullname,
      quality_cropfullname.cropfullname,
      'приказ о переоф.',
      orderloaditems.orderloaditemid,
      cast(null as integer) as suminvoicenet,
      case
        when it.net< 0 then -it.net
        else it.net
      end as sumweight,
      cast(null as integer) as sumdeficit,
      case
        when orderload.ownerid = :aownerid
        then case
              when it.net< 0 then -it.net
             else it.net
         end
      end  as outcome,
      case
        when orderload.providerid = :aownerid
        then case
              when it.net< 0 then -it.net
             else it.net
             end
      end as income,
      cast(null as integer) as inventoryid,
      Storage.storageshortname
    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 orderloaditems on (it.orderloaditemid = orderloaditems.orderloaditemid)
      left join Storage on (IT.storageid = Storage.StorageID)
      left join orderload  on (orderloaditems.orderloadid = orderload.orderloadid)
      left join clients provider on (orderload.providerid = provider.clientid)
      left join clients owner on (orderload.ownerid = owner.clientid)
      left join quality on (orderloaditems.qualityid = quality.qualityid)
      left join ittransport on (it.inventoryid = ittransport.inventoryid)
      left join quality_cropfullname(quality.cropid,quality.classid,null,1)  on (0 = 0)
      left join stations on (orderload.destinationpointid = stations.stationid)
    where it.regdate between :begindate and :enddate
      and it.operationid = 17
--      and Parties.State = 1
      and it.state >=0
      and (it.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )
      and ((orderload.ownerid = :aownerid)
            or (coalesce(:aownerid,-1) = -1)
            or (orderload.providerid = :aownerid ))
      and (it.providerid = coalesce(:aproviderid,-1) or (coalesce(:aproviderid,-1) = -1))
      and coalesce(:astationid,-1) = -1
      and coalesce(:atransportsubtype,-1) = -1

      and (quality.cropid = coalesce(:cropid,-1) or (coalesce(:cropid,-1) = -1))
      and (quality.classid = coalesce(:classid,-1) or (coalesce(:classid,-1) = -1))
      and orderload.state in (0,1)
      and orderload.operationid = 17
    --//--
    group by
      orderload.ordernumber,
      it.regdate,
      it.regdate,
      ownerid, --!
      orderload.transportsubtypeid,
      orderload.operationid,
      providerfullname,
      ownerfullname,
      quality_cropfullname.cropfullname,
      orderloaditems.orderloaditemid,
      Storage.storageshortname,
      outcome,
      income,
      sumweight

    into :exportactid,
         :actnumber,
         :regdate,
         :actdate,
         :ownerid,
         :transportsubtypeid,
         :operationid,
         :providerfullname,
         :ownerfullname,
         :cropfullname,
         :stationfullname,
         :orderitemid,
         :suminvoicenet,
         :sumweight,
         :sumdeficit,
         :outcome, 
         :income,
         :inventoryid,
         :storagename
  do
  begin
    suspend;
  end --*/
-- /*-- 22, 64, 70 , 65
  for
    select
      invoices.invoiceid,
      case
        when it.operationid = 64 then exportacts.actnumber
        else  invoices.invoicenumber
      end actnumber,
      it.regdate,
      it.regdate as actdate,
      it.ownerid,
      it.operationid as transportsubtypeid,
      it.operationid,
      provider.clientfullname as providerfullname,
      owner.clientfullname as ownerfullname,
      quality_cropfullname.cropfullname,
      case
        when it.operationid = 64 then operation.operationname||' (№'||exportacts.actnumber||')'
        when it.operationid = 22 and it.state = 0 then 'Отходы 3 кат.'
        when it.operationid = 22 and it.state = -10 then 'Отходы 3 кат. ПР'
        else case
           when it.state = -10 then operation.operationname||' ПР'
           else operation.operationname
        end
      end operationname,
      cast(null as integer),
      cast(null as integer),-- -sum (EAIV.Weight) as SumInvoiceNet,
      case
        when it.operationid in (64,65) then it.weight
        else -it.net
      end as sumweight,
      cast(null as integer) as sumdeficit,
      sum (case when it.operationid in (64,65) then -it.weight
             else  it.net
           end  ) as outcome,
      cast(null as integer) as income,
      it.inventoryid,
      Storage.storageshortname
    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 Storage on (IT.storageid = Storage.StorageID)
      left join invoices on (not(it.operationid=22 and it.net<0) and it.invoiceid = invoices.invoiceid)
      left join clients provider on (it.providerid = provider.clientid)
      left join ittransport on (it.inventoryid = ittransport.inventoryid)
      left join transporttype on (ittransport.transporttypeid = transporttype.transporttypeid)
      left join clients owner on (it.ownerid = owner.clientid)
      left join quality q1 on (it.samplerqualityid = q1.qualityid)
      left join quality_cropfullname(q1.cropid,q1.classid,null,1)  on (0 = 0)
      left join operation on (it.operationid = operation.operationid)
      left join exportactitems eai on (it.operationid = 64 and it.inventoryid = eai.inventoryid)
      left join exportacts on (eai.exportactid = exportacts.exportactid)
      left join quality wastename on (it.operationparam = wastename.qualityid)
    where (it.regdate between :begindate and :enddate)
      and (it.ownerid = coalesce(:aownerid,-1) or (coalesce(:aownerid,-1) = -1))
      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(:atransportsubtype,-1) or (coalesce(:atransportsubtype,-1) = -1))
     and (it.net > 0 or (it.operationid in (64,65)))
      and (it.storageid = coalesce(:astorageid,-1) or (coalesce(:astorageid,-1) = -1) )
      and (q1.cropid = coalesce(:cropid,-1) or (coalesce(:cropid,-1) = -1))
      and (q1.classid = coalesce(:classid,-1) or (coalesce(:classid,-1) = -1))
      and ( (it.operationid in (70,64,65))
          or (it.operationid = 22 and wastename.cropid = 9 and wastename.classid in (41,141)))
      and (it.state >= 0 or (it.state = -10))
    --//--
    group by
      invoices.invoiceid,
      actnumber,
      it.regdate,
      actdate,
      it.operationid,
      it.ownerid,
      provider.clientfullname,
      owner.clientfullname,
      quality_cropfullname.cropfullname,
      operationname,
      sumweight,
      it.inventoryid,
      Storage.storageshortname
    into :exportactid,
         :actnumber,
         :regdate,
         :actdate,
         :ownerid,
         :transportsubtypeid,
         :operationid,
         :providerfullname,
         :ownerfullname,
         :cropfullname,
         :stationfullname,
         :orderitemid,
         :suminvoicenet,
         :sumweight,
         :sumdeficit,
         :outcome, 
         :income,
         :inventoryid,
         :storagename
  do
  begin
    suspend;
  end --*/
end