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

FORWARDER_GROUPSELECT

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_GROUPSELECT(
    BEGINDATE DATE,
    ENDDATE DATE,
    GROUPACT INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    AOWNERID INTEGER,
    APROVIDERID INTEGER,
    ASTORAGEID INTEGER,
    ASTATIONID INTEGER,
    TRSUBTYPEID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    REGDATE DATE,
    ACTDATE DATE,
    EXPORTACTID INTEGER,
    EXPORTACTPARENTID INTEGER,
    ACTNUMBER VARCHAR(10),
    OWNERID INTEGER,
    QUALITYID INTEGER,
    PROVIDERID INTEGER,
    STORAGEID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    OWNERFULLNAME VARCHAR(100),
    PROVIDERSFULLNAME VARCHAR(100),
    STATIONFULLNAME VARCHAR(128),
    CROPFULLNAME VARCHAR(100),
    TRANSPORTSUBTYPE INTEGER,
    STATIONID INTEGER,
    OPERATIONID INTEGER,
    RESTWEIGHTCOUNT INTEGER,
    NETSUM INTEGER,
    ORDERITEMID INTEGER)
AS
declare variable actweightsum integer;
begin
  ---//---
  for
    select
      ExportActs.RegDate,
      ExportActs.ActDate,
      ExportActs.ExportActID,
      ExportActs.ExportActParentID,
      ExportActs.ActNumber,
      ExportActs.OwnerID,
      ExportActs.QualityID,
      ExportActs.OrderItemID,
      cast(null as integer),
      ExportActs.StorageID,
      Quality.CropID,
      Quality.ClassID,
      Owners.ClientFullName as OwnerFullName,
      Providers.ClientFullName as ProvidersFullName,
      cast('' as varchar(128)),
      cast('' as varchar(100)),
      ExportActs.TransportSubTypeID,
      ExportActs.StationID, -1,
      cast(0 as bigint),
      cast(0 as bigint) NetSum,
      (select sum (ExportActItems_View.Weight) from ExportActItems_View
           where ExportActItems_View.ExportActID = ExportActs.ExportActID
           and ExportActItems_View.State <> 10) ActWeightSum
    from ExportActs
      left join Clients Owners on (ExportActs.OwnerID = Owners.ClientID)
      left join Clients Providers on (ExportActs.ProviderID = Providers.ClientID)
      left join Quality on (ExportActs.QualityID = Quality.QualityID)
      left join ExportActItems_View EAIMain on (ExportActs.ExportActID = EAIMain.ExportActID)
      left join IT on (EAIMain.InventoryID = IT.InventoryID)
      join GetWorkParties(:IsCurrent,:APartyDate,
        case when coalesce(:ACropID,-1) = -1 then null else coalesce(:ACropID,-1) end,
        case when coalesce(:AClassID,-1) = -1 then null else coalesce(:AClassID,-1) end,
        case when coalesce(:Astorageid,-1) = -1 then null else coalesce(:Astorageid,-1) end )
        on (IT.PartyID = GetWorkParties.PartyID)
    where ExportActs.OperationID in (1,17)
      and ((ExportActs.ActDate between :BeginDate and :EndDate and :GroupAct = 1)
           or ((ExportActs.RegDate between :BeginDate and :EndDate and coalesce(:GroupAct,0) = 0)))
      and (ExportActs.OwnerID = :AOwnerID or (coalesce(:AOwnerID,-1) = -1))
      and (ExportActs.ProviderID = :AProviderID or (coalesce(:AProviderID,-1) = -1))
      and (ExportActs.StorageID = :AStorageID or (coalesce(:AStorageID,-1) = -1))
      and (ExportActs.transportsubtypeid = :trsubtypeid or (coalesce(:trsubtypeid,-1) = -1))
      and (ExportActs.StationID = :AStationID or (coalesce(:AStationID,-1) = -1))

      and (Quality.CropID = :ACropID or (coalesce(:ACropID,-1) = -1))
      and (Quality.ClassID = :AClassID or (coalesce(:AClassID,-1) = -1))
      and  exists (select first 1 ExportActID from ExportActItems
        left join IT on (ExportActItems.InventoryID = IT.inventoryID)
        where IT.State >= 0 and ExportActItems.ExportActID = ExportActs.ExportActID)
  union
      select
      IT.RegDate,
      cast (null as date),
      cast (null as integer), --
      cast (null as integer),
      cast (null as varchar(10)) ,
      IT.OwnerID,
      cast (null as integer),
      ExportActs.OrderItemID,
      IT.ProviderID,
      IT.StorageID,
      Quality.CropID,
      Quality.ClassID,
      Owners.ClientFullName,
      Providers.ClientFullName,
      Stations.StationFullName,
      Quality_CropFullName.CropFullName,
      TransportType.TransportSubType,
      Invoices.StationID,
      IT.OperationID,
      sum(case
        when ((select sum(weight) from ExportActItems_View
               where ExportActItems_View.InventoryID = IT.InventoryID
               and ExportActItems_View.State <> 10) <> IT.Net)

        then 1
        else 0
      end) RestWeightCount,
      sum (IT.Net) NetSum,
      sum (0) ActWeightSum
    from IT
      join GetWorkParties(:IsCurrent,:APartyDate,
        case when coalesce(:ACropID,-1) = -1 then null else coalesce(:ACropID,-1) end,
        case when coalesce(:AClassID,-1) = -1 then null else coalesce(:AClassID,-1) end,
        case when coalesce(:Astorageid,-1) = -1 then null else coalesce(:Astorageid,-1) end )
        on (IT.PartyID = GetWorkParties.PartyID)
      left join Clients Owners on (IT.OwnerID = Owners.ClientID)
      left join Clients Providers on (IT.ProviderID = Providers.ClientID)
      left join Quality on (IT.SamplerQualityID = Quality.QualityID)
      left join Quality_CropFullName (Quality.CropID, null, null, 1) on (0=0)
      left join Invoices  on (IT.InvoiceID = Invoices.InvoiceID)
      left join Stations on (Invoices.StationID = Stations.StationID)
      left join ITTransport on (IT.InventoryID = ITTransport.InventoryID)
      left join TransportType on (ITTransport.TransportTypeID = TransportType.TransportTypeID) --
      left join ExportActItems on (IT.InventoryID = ExportActItems.InventoryID)
      left join ExportActs on (ExportActItems.ExportactID = ExportActs.ExportActID)
    where  IT.RegDate between :BeginDate and :EndDate
      and IT.OperationID = 1
      and IT.State >= 0
      and (IT.OwnerID = :AOwnerID or (coalesce(:AOwnerID,-1) = -1))
      and (IT.ProviderID = :AProviderID or (coalesce(:AProviderID,-1) = -1))
      and (IT.StorageID = :AStorageID or (coalesce(:AStorageID,-1) = -1))
      and (TransportType.transportsubtype = :trsubtypeid or (coalesce(:trsubtypeid,-1) = -1))
      and (Invoices.StationID = :AStationID or (coalesce(:AStationID,-1) = -1))
      and (Quality.CropID = :ACropID or (coalesce(:ACropID,-1) = -1))
      and (Quality.ClassID = :AClassID or (coalesce(:AClassID,-1) = -1))
      and ExportActs.ExportActID is null
      and coalesce(:GroupAct,0) = 0
    group by
      IT.RegDate,
      ExportActs.ActDate,
      ExportActItems.ExportactID, --???????
      ExportActs.ExportActParentID,
      ExportActs.ActNumber,
      IT.OwnerID,
      ExportActs.QualityID,
      ExportActs.OrderItemID,
      IT.ProviderID,
      IT.StorageID,
      Quality.CropID,
      Quality.ClassID,
      Owners.ClientFullName,
      Providers.ClientFullName,
      Stations.StationFullName,
      Quality_CropFullName.CropFullName,
      TransportType.TransportSubType,
      Invoices.StationID,
      IT.OperationID
    into
         :REGDATE,
         :ActDate,
         :EXPORTACTID,
         :ExportActParentID,
         :ACTNUMBER,
         :OWNERID,
         :QUALITYID,
         :OrderItemID,
         :PROVIDERID,
         :STORAGEID,
         :CROPID,
         :CLASSID,
         :OWNERFULLNAME,
         :PROVIDERSFULLNAME,
         :STATIONFULLNAME,
         :CROPFULLNAME,
         :TRANSPORTSUBTYPE,
         :STATIONID,
         :OPERATIONID,
         :RESTWEIGHTCOUNT,
         :NETSUM,
         :ACTWEIGHTSUM
  do
  begin
    suspend;
    if (RestWeightCount = 1) then
    begin
      EXPORTACTID = null;
      ACTNUMBER = null;
      suspend;
    end
  end
end