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

FORWARDER_OUTCOMEACT

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_OUTCOMEACT(
    BEGINDATE DATE,
    ENDDATE DATE,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    PLUGINUNDERWORK INTEGER,
    APROVIDERID INTEGER,
    ASTATIONID INTEGER,
    ASTORAGEID INTEGER,
    TRSUBTYPE INTEGER)
RETURNS (
    ACTDATE DATE,
    ACTNUMBER VARCHAR(10),
    PROVIDERFULLNAME VARCHAR(100),
    REGDATE DATE,
    CROPFULLNAME VARCHAR(100),
    OWNERFULLNAME VARCHAR(100),
    SUMWEIGHT BIGINT,
    EXPORTACTID INTEGER,
    OWNERID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    PROVIDERID INTEGER,
    STORAGEID INTEGER,
    STATE INTEGER,
    ORDERITEMID INTEGER,
    ORDERNUMBER VARCHAR(10),
    ORDERLOADID INTEGER,
    STATIONID INTEGER,
    WASTENET INTEGER,
    ORDERNET INTEGER,
    SAMPLENET INTEGER,
    WASTE02NET INTEGER,
    ALLNET INTEGER,
    TRANSPORTSUBTYPEID INTEGER,
    SHIPNAME VARCHAR(50),
    STATIONNAME VARCHAR(30),
    TYPESTATION VARCHAR(10),
    ISSHIP VARCHAR(10),
    EXPORTACTIDCALC INTEGER,
    PREWASTENET INTEGER,
    STORAGENAME VARCHAR(100),
    PARTYID INTEGER,
    OPERATIONID INTEGER,
    CONTRACTID INTEGER)
AS
begin
  for
    select EXPORTACTS.ACTDATE, EXPORTACTS.EXPORTACTID, EXPORTACTS.ACTNUMBER,
        EXPORTACTS.REGDATE, EXPORTACTS.OWNERID, EXPORTACTS.PROVIDERID, EXPORTACTS.STORAGEID,
        EXPORTACTS.ORDERITEMID, EXPORTACTS.STATE, QUALITY.CROPID, QUALITY.CLASSID,
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
        PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
        EXPORTACTS.STATIONID, EXPORTACTS.TRANSPORTSUBTYPEID, ORDERLOADITEMS.NET ORDERNET,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            (select first 1 SHIPS.SHIPNAME
               from IT ITSH
                 left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
                 left join SHIPS on (SHIPS.SHIPID = ITS.RELATIONID)
               where (ITSH.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
                 and (ITSH.OPERATIONID in (5,1005)) and (not (ITSH.STATE in (-1,-100))))
        end,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
            ST.STATIONNAME
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            (select first 1 ST2.STATIONNAME
               from IT ITSH
                 left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
                 left join INVOICES on (INVOICES.INVOICEID = ITS.INVOICEID)
                 left join STATIONS ST2 on (ST2.STATIONID = INVOICES.STATIONID)
               where (ITSH.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
                 and (ITSH.OPERATIONID in (5,1005)) and (not (ITSH.STATE in (-1,-100))))
        end STATIONNAME,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
            'Станция:'
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            'Порт:'
        end TYPESTATION,
        case
          when (EXPORTACTS.TRANSPORTSUBTYPEID = 2) then
            'Судно:'
        end ISSHIP,
        EXPORTACTQUALITY.EXPORTACTID EXPORTACTIDCALC, STORAGE.STORAGESHORTNAME,
        sum(case
              when (EAI.WEIGHT > 0) then
                EAI.WEIGHT
            end) SUMWEIGHT,
        sum(case
              when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 22) and (ITM.STATE = 0)) then
                -EAI.WEIGHT
            end) WASTENET,
        sum(case
              when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 22) and (ITM.STATE = -10)) then
                -EAI.WEIGHT
            end) PREWASTENET,
        sum(case
              when ((EAI.WEIGHT < 0) and (ITM.OPERATIONID = 70))
                then -EAI.WEIGHT
            end) SAMPLENET,
        sum(case
              when ((EAI.WEIGHT < 0) and (EAI.OPERATIONID = 64))
                then -EAI.WEIGHT
            end) WASTE02NET,
        sum(case
              when (EAI.WEIGHT < 0) then
                -EAI.WEIGHT
              else EAI.WEIGHT
            end) ALLNET,
        min(ORDERLOADQUALITY.PARTYID), min(ORDERLOAD.OPERATIONID),
        min(ORDERLOAD.CONTRACTID)
      from EXPORTACTS
        left join CLIENTS OWNER on ((case
                                       when ((EXPORTACTS.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                         EXPORTACTS.OWNERID
                                       else EXPORTACTS.PROVIDERID
                                     end) = OWNER.CLIENTID)
        left join CLIENTS PROVIDER on ((case
                                          when ((EXPORTACTS.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                            EXPORTACTS.PROVIDERID
                                          else EXPORTACTS.OWNERID
                                        end) = PROVIDER.CLIENTID)
        left join STORAGE on (STORAGE.STORAGEID = EXPORTACTS.STORAGEID)
        left join STATIONS ST on ((case
                                     when (EXPORTACTS.TRANSPORTSUBTYPEID = 1) then
                                       EXPORTACTS.STATIONID
                                   end) = ST.STATIONID)
        left join EXPORTACTITEMS EAI on (EAI.EXPORTACTID = EXPORTACTS.EXPORTACTID)
        left join IT ITM on (ITM.INVENTORYID = EAI.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(:STORAGEID,-1) = -1) then
                                null
                              else coalesce(:STORAGEID,-1)
                            end) on (GETWORKPARTIES.PARTYID = ITM.PARTYID)
        left join QUALITY on (QUALITY.QUALITYID = EXPORTACTS.QUALITYID)
        left join QUALITY_CROPFULLNAME_BYID (EXPORTACTS.QUALITYID, 1) on (0=0)
        left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = EXPORTACTS.ORDERITEMID)
        left join ORDERLOAD on (ORDERLOAD.ORDERLOADID = ORDERLOADITEMS.ORDERLOADID)
        left join EXPORTACTQUALITY on (EXPORTACTQUALITY.EXPORTACTID = EXPORTACTS.EXPORTACTID)
        left join QUALITY ORDERLOADQUALITY on (ORDERLOADQUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
      where (EXPORTACTS.ACTDATE between :BEGINDATE and :ENDDATE)
        and (EXPORTACTS.OPERATIONID in (5,1005)) and (EXPORTACTS.STATE <> -1) and (EAI.OPERATIONID <> -64)
        and ((EXPORTACTS.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
        and ((EXPORTACTS.PROVIDERID = :APROVIDERID) or (coalesce(:APROVIDERID,-1) = -1))
        and ((EXPORTACTS.STATIONID = :ASTATIONID) or (coalesce(:ASTATIONID,-1) = -1))
        and ((EXPORTACTS.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
        and (EXPORTACTS.EXPORTACTPARENTID is null)
        and ((EXPORTACTS.TRANSPORTSUBTYPEID = :TRSUBTYPE) or (coalesce(:TRSUBTYPE,-1) = -1))
        and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
        and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
    group by EXPORTACTS.ACTDATE, EXPORTACTS.REGDATE, EXPORTACTS.EXPORTACTID,
        EXPORTACTS.ACTNUMBER, EXPORTACTS.OWNERID, EXPORTACTS.PROVIDERID, EXPORTACTS.STORAGEID,
        EXPORTACTS.TRANSPORTSUBTYPEID, EXPORTACTS.ORDERITEMID, EXPORTACTS.STATE, QUALITY.CROPID,
        QUALITY.CLASSID, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, OWNER.CLIENTFULLNAME,
        PROVIDER.CLIENTFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
        EXPORTACTS.STATIONID, ORDERLOADITEMS.NET, EXPORTACTQUALITY.EXPORTACTID,
        ST.STATIONNAME, STORAGE.STORAGESHORTNAME
    union
      select ORDERLOAD.BEGINDATE, cast (null as integer), cast (null as varchar(10)),
          cast (null as date), ORDERLOAD.OWNERID, ORDERLOAD.PROVIDERID, ORDERLOAD.STORAGEID,
          ORDERLOADITEMS.ORDERLOADITEMID, cast(0 as integer) STATE, QUALITY.CROPID,
          QUALITY.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME, OWNER.CLIENTFULLNAME OWNERFULLNAME,
          PROVIDER.CLIENTFULLNAME PROVIDERFULLNAME, ORDERLOAD.ORDERNUMBER, ORDERLOAD.ORDERLOADID,
          ORDERLOAD.DESTINATIONPOINTID, ORDERLOAD.TRANSPORTSUBTYPEID, ORDERLOADITEMS.NET ORDERNET,
          SHIPS.SHIPNAME, STATIONS.STATIONNAME,
          case
            when (ORDERLOAD.TRANSPORTSUBTYPEID = 2) then
              'Порт:'
            when (ORDERLOAD.TRANSPORTSUBTYPEID = 1) then
              'Станция:'
          end as TYPESTATION,
          case
            when (ORDERLOAD.TRANSPORTSUBTYPEID = 2) then
              'Судно:'
          end ISSHIP,
          cast(null as integer) EXPORTACTIDCALC, STORAGE.STORAGESHORTNAME,
          sum(cast(null as integer)) SUMWEIGHT, sum(cast(null as integer)) WASTENET,
          sum(cast(null as integer)) PREWASTENET, sum(cast(null as integer)) SAMPLENET,
          sum(cast(null as integer)) WASTE02NET, sum(cast(null as integer)) ALLNET,
          min(QUALITY.PARTYID), min(ORDERLOAD.OPERATIONID), min(ORDERLOAD.CONTRACTID)
        from ORDERLOAD
          left join CLIENTS OWNER on ((case
                                         when ((ORDERLOAD.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                           ORDERLOAD.OWNERID
                                         else ORDERLOAD.PROVIDERID
                                       end) = OWNER.CLIENTID)
          left join CLIENTS PROVIDER on ((case
                                            when ((ORDERLOAD.TRANSPORTSUBTYPEID = 2) and (:PLUGINUNDERWORK = 5)) then
                                              ORDERLOAD.PROVIDERID
                                            else ORDERLOAD.OWNERID
                                          end) = PROVIDER.CLIENTID)
          left join STORAGE on (STORAGE.STORAGEID = ORDERLOAD.STORAGEID)
          left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADID = ORDERLOAD.ORDERLOADID)
          left join QUALITY on (QUALITY.QUALITYID = ORDERLOADITEMS.QUALITYID)
          left join QUALITY_CROPFULLNAME (QUALITY.CROPID, QUALITY.CLASSID, null, 1) on (0=0)
          left join IT ITSH on ((ITSH.ORDERLOADITEMID = ORDERLOADITEMS.ORDERLOADITEMID) and (ITSH.OPERATIONID in (5,1005)) and (ITSH.STATE not in (0,-1,-5,-100)))
          left 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(:STORAGEID,-1) = -1) then
                                       null
                                     else coalesce(:STORAGEID,-1)
                                   end) on (GETWORKPARTIES.PARTYID = ITSH.PARTYID)
          left join IT ITS on ((ITS.INVENTORYID = ITSH.RELATIONID) and (ITS.PARENTID = -1))
          left join SHIPS on (SHIPS.SHIPID = ITS.RELATIONID)
          left join INVOICES on (INVOICES.INVOICEID = ITS.INVOICEID)
          left join STATIONS on ((case
                                    when ORDERLOAD.TRANSPORTSUBTYPEID = 2
                                      then INVOICES.STATIONID
                                    when ORDERLOAD.TRANSPORTSUBTYPEID = 1
                                      then ORDERLOAD.DESTINATIONPOINTID
                                  end) = STATIONS.STATIONID)
          left join CROP on (CROP.CROPID = QUALITY.CROPID)
        where (ORDERLOAD.BEGINDATE between :BEGINDATE and :ENDDATE)
          and (not (exists (select EXPORTACTID from EXPORTACTS
                              where (EXPORTACTS.ORDERITEMID = ORDERLOADITEMS.ORDERLOADITEMID)
                                and (EXPORTACTS.STATE > 0))))
          and ((ORDERLOAD.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
          and ((ORDERLOAD.PROVIDERID = :APROVIDERID) or (coalesce(:APROVIDERID,-1) = -1))
          and ((ORDERLOAD.TRANSPORTSUBTYPEID = :TRSUBTYPE) or (coalesce(:TRSUBTYPE,-1) = -1))
          and ((STATIONS.STATIONID = :ASTATIONID) or (coalesce(:ASTATIONID,-1) = -1))
          and ((ORDERLOAD.STORAGEID = :ASTORAGEID) or (coalesce(:ASTORAGEID,-1) = -1))
          and (ORDERLOAD.STATE > 0) and (ORDERLOAD.OPERATIONID in (5,1005)) and (CROP.CROPTYPEID <> 3)
          and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
          and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))
      group by ORDERLOAD.BEGINDATE, ORDERLOAD.OWNERID, ORDERLOAD.PROVIDERID,
          ORDERLOAD.STORAGEID, ORDERLOADITEMS.ORDERLOADITEMID, ORDERLOAD.STATE,
          QUALITY.CROPID, QUALITY.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME,
          OWNER.CLIENTFULLNAME, PROVIDER.CLIENTFULLNAME, ORDERLOAD.ORDERNUMBER,
          ORDERLOAD.ORDERLOADID, ORDERLOAD.DESTINATIONPOINTID, ORDERLOAD.TRANSPORTSUBTYPEID,
          ORDERLOADITEMS.NET, SHIPS.SHIPNAME, STATIONS.STATIONNAME, STORAGE.STORAGESHORTNAME,
          STATIONS.REGIONID
    order by 1, 15, 3, 12, 13
    into :ACTDATE, :EXPORTACTID, :ACTNUMBER, :REGDATE, :OWNERID, :PROVIDERID,
         :STORAGEID, :ORDERITEMID, :STATE, :CROPID, :CLASSID, :CROPFULLNAME,
         :OWNERFULLNAME, :PROVIDERFULLNAME, :ORDERNUMBER, :ORDERLOADID,
         :STATIONID, :TRANSPORTSUBTYPEID, :ORDERNET, :SHIPNAME, :STATIONNAME,
         :TYPESTATION, :ISSHIP, :EXPORTACTIDCALC, :STORAGENAME, :SUMWEIGHT,
         :WASTENET, :PREWASTENET, :SAMPLENET, :WASTE02NET, :ALLNET, :PARTYID,
         :OPERATIONID, :CONTRACTID
  do begin
    suspend;
  end
end