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

FORWARDER_WASTESELECT

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_WASTESELECT(
    AINVENTORYID INTEGER,
    AORDERITEMID INTEGER,
    AOWNERID INTEGER,
    ACROPID INTEGER,
    ACLASSID INTEGER,
    AOPERATIONID INTEGER,
    AEXPORTACTID INTEGER,
    ISWEIGHT SMALLINT,
    EA_OPERATIONID SMALLINT,
    APARTYID INTEGER)
RETURNS (
    EXPORTACTID INTEGER,
    OPERATIONID INTEGER,
    EAOUT INTEGER,
    ACTDATE DATE,
    ACTNUMBER VARCHAR(10),
    PROVIDERFULLNAME VARCHAR(100),
    SUMACTNET BIGINT,
    CUSTOMWASTE BIGINT,
    READ_REC BIGINT,
    OUT_EXPORTACTID INTEGER,
    ALL_EXPORTACTID INTEGER)
AS
begin
  if (not :AOPERATIONID in (64,65)) then begin
    for
      select
          EA.EXPORTACTID, EA.OPERATIONID, 0 EAOUT,
          case
            when (EA.OPERATIONID = -17) then
              EA.REGDATE
            else EA.ACTDATE
          end ACTDATE,
          EA.ACTNUMBER, PROVIDERS.CLIENTFULLNAME PROVIDERFULLNAME,
          sum(FWW.TRANSPORTWEIGHT + coalesce(FWW.CUSTOMWASTE,0)) SUMACTNET,
          sum(FWW.CUSTOMWASTE) CUSTOMWASTE, sum(FWW.READ_REC) READ_REC,
          max((select EXPORTACTITEMS.EXPORTACTID
                 from EXPORTACTITEMS
                   join EXPORTACTITEMS EITEMS on ((EITEMS.PARENTITEMID = EXPORTACTITEMS.EXPORTACTITEMID)
                                                  and (EITEMS.INVENTORYID = :AINVENTORYID)
                                                  and (EITEMS.OPERATIONID = 5))
                 where (EXPORTACTITEMS.PARENTITEMID = EAIMAIN.EXPORTACTITEMID)
                 and (EXPORTACTITEMS.OPERATIONID = 5))
              ) EAIAD_EXPORTACTID
        from EXPORTACTS EA
          left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = EA.PROVIDERID)
          left join EXPORTACTITEMS EAIMAIN on (EAIMAIN.EXPORTACTID = EA.EXPORTACTID)
          left join EXPORTACTITEMS EAIAD on ((EAIAD.PARENTITEMID = EAIMAIN.EXPORTACTITEMID) and (EAIAD.INVENTORYID = :AINVENTORYID))
--          left join EXPORTACTITEMS EAIAD on (EAIAD.PARENTITEMID = EAIMAIN.EXPORTACTITEMID)
--          left join EXPORTACTS EAAD on (EAAD.EXPORTACTID = EAIAD.EXPORTACTID)
          left join IT on (IT.INVENTORYID = EAIMAIN.INVENTORYID)
          left join GETWORKPARTIES(1, current_date, :ACROPID, :ACLASSID, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          left join QUALITY on (QUALITY.QUALITYID = EA.QUALITYID)
          left join FORWARDER_WEIGH64WASTE(:AORDERITEMID, :AINVENTORYID,
              :AOPERATIONID, EAIMAIN.EXPORTACTITEMID, :ISWEIGHT, :EA_OPERATIONID) FWW on (0=0)
        where (EA.OWNERID = :AOWNERID) and (QUALITY.CROPID = :ACROPID)
          and (QUALITY.CLASSID = :ACLASSID) and (EA.OPERATIONID <> 5)
          and (EAIMAIN.OPERATIONID in (1,17)) and (EAIMAIN.WEIGHT > 0)
          and (IT.PARTYID = GETWORKPARTIES.PARTYID)
          and (IT.PARTYID = :APARTYID)
          and ((EA.STATE in (0,1))
               or ((EA.STATE = 10)
                   and ((EA.ORDERITEMID = :AORDERITEMID)
                        or ((EA.ORDERITEMID is null)
                            and (EA.EXPORTACTID = (select EXPORTACTID
                                                     from EXPORTACTITEMS
                                                     where (INVENTORYID = :AINVENTORYID)
                                                       and (EXPORTACTITEMS.OPERATIONID = -64)))
                            )
                       )
                  )
              )
--          and ((coalesce(EAIAD.EXPORTACTID,0) <> EA.EXPORTACTID) or (FWW.READ_REC = 0))
--          and (((FWW.TRANSPORTWEIGHT + coalesce(FWW.CUSTOMWASTE,0)) > 0) or (FWW.CUSTOMWASTE > 0))

      group by ACTDATE, EA.ACTNUMBER, PROVIDERS.CLIENTFULLNAME, EA.OPERATIONID, EA.EXPORTACTID
      having ((sum(FWW.TRANSPORTWEIGHT + coalesce(FWW.CUSTOMWASTE,0)) > 0) or (sum(FWW.CUSTOMWASTE) > 0))
      into :EXPORTACTID, :OPERATIONID, :EAOUT, :ACTDATE, :ACTNUMBER,
           :PROVIDERFULLNAME, :SUMACTNET, :CUSTOMWASTE, :READ_REC, :OUT_EXPORTACTID
    do begin
      ALL_EXPORTACTID = :EXPORTACTID;

      suspend;
    end
  end

  if (:AOPERATIONID = 64) then begin
    for
/*      select
          EA.EXPORTACTID, 64 OPERATIONID, coalesce(:AEXPORTACTID,0) EAOUT,
          case
            when (EA.EXPORTACTID = 17) then
              EA.REGDATE
            else EA.ACTDATE
          end ACTDATE,
          EA.ACTNUMBER, PROVIDERS.CLIENTFULLNAME PROVIDERFULLNAME,
          sum(EAIO.WEIGHT) + sum(coalesce(-EAI64.WEIGHT,0)) SUMACTNET,
          -sum(EAI64.WEIGHT) CUSTOMWASTE, sum(0) READ_REC
        from EXPORTACTS EAO
          left join EXPORTACTITEMS EAIO on (EAIO.EXPORTACTID = EAO.EXPORTACTID)
          left join EXPORTACTITEMS EAIMAIN on (EAIMAIN.EXPORTACTITEMID = EAIO.PARENTITEMID)
          left join EXPORTACTITEMS EAI64 on ((EAI64.PARENTITEMID = EAIMAIN.EXPORTACTITEMID) and (EAI64.EXPORTACTID = :EXPORTACTID) and (EAI64.OPERATIONID = 64))
          left join EXPORTACTS EA on (EA.EXPORTACTID = EAIMAIN.EXPORTACTID)
          left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = EA.PROVIDERID)
        where (EAO.EXPORTACTID = :AEXPORTACTID) and (:AOPERATIONID = 64)
          and (EAIO.OPERATIONID in (5,17)) and (EAIMAIN.OPERATIONID in (1,17))
          and (EAIO.WEIGHT > 0)
      group by ACTDATE, EA.ACTNUMBER, PROVIDERS.CLIENTFULLNAME, EA.EXPORTACTID*/
      select
          case
            when (EAO.OPERATIONID = -17) then
              EA.EXPORTACTID
            else EAP.EXPORTACTID
          end EXPORTACTID,
          64 OPERATIONID, coalesce(:AEXPORTACTID,0) EAOUT,
          case
            when (EAP.EXPORTACTID = 17) then
              EAP.REGDATE
            else EAP.ACTDATE
          end ACTDATE,
          EAP.ACTNUMBER, PROVIDERS.CLIENTFULLNAME PROVIDERFULLNAME,
          sum(EAIO.WEIGHT) + sum(coalesce(-EAI64.WEIGHT,0)) SUMACTNET,
          -sum(EAI64.WEIGHT) CUSTOMWASTE, sum(0) READ_REC,
          case
            when (EAO.OPERATIONID = -17) then
              EA.EXPORTACTPARENTID
            else EAP.EXPORTACTID
          end ALL_EXPORTACTID
        from EXPORTACTS EAO
          left join EXPORTACTS EA on (EA.ORDERITEMID = EAO.ORDERITEMID)
          left join EXPORTACTITEMS EAIO on (EAIO.EXPORTACTID = EA.EXPORTACTID)
          left join EXPORTACTITEMS EAIMAIN on (EAIMAIN.EXPORTACTITEMID = EAIO.PARENTITEMID)
          left join EXPORTACTITEMS EAI64 on ((EAI64.PARENTITEMID = EAIMAIN.EXPORTACTITEMID) and (EAI64.EXPORTACTID = EAO.EXPORTACTID) and (EAI64.OPERATIONID = 64))
          left join EXPORTACTS EAP on (EAP.EXPORTACTID = EAIMAIN.EXPORTACTID)
          left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = EAP.PROVIDERID)
        where (EAO.EXPORTACTID = :AEXPORTACTID) and (:AOPERATIONID = 64)
          and (EAIO.OPERATIONID in (5,17,1005)) and (EAIMAIN.OPERATIONID in (1,17))
          and (EAIO.WEIGHT > 0)
      group by ACTDATE, EAP.ACTNUMBER, PROVIDERS.CLIENTFULLNAME, EXPORTACTID, ALL_EXPORTACTID
      into :EXPORTACTID, :OPERATIONID, :EAOUT, :ACTDATE, :ACTNUMBER,
         :PROVIDERFULLNAME, :SUMACTNET, :CUSTOMWASTE, :READ_REC, :ALL_EXPORTACTID
    do suspend;
  end

  if (:AOPERATIONID = 65) then begin
    for
      select
          EA.EXPORTACTID, EA.OPERATIONID, 0 EAOUT,
          case
            when (EA.EXPORTACTID = 17) then
              EA.REGDATE
            else EA.ACTDATE
          end ACTDATE,
          EA.ACTNUMBER, PROVIDERS.CLIENTFULLNAME PROVIDERFULLNAME,
          sum(FWW.TRANSPORTWEIGHT + coalesce(FWW.CUSTOMWASTE,0)) SUMACTNET,
          sum(FWW.CUSTOMWASTE) CUSTOMWASTE, sum(FWW.READ_REC) READ_REC
        from EXPORTACTS EA
          left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = EA.PROVIDERID)
          left join EXPORTACTITEMS EAIMAIN on (EAIMAIN.EXPORTACTID = EA.EXPORTACTID)
          left join IT on (IT.INVENTORYID = EAIMAIN.INVENTORYID)
          left join GETWORKPARTIES(1, current_date, :ACROPID, :ACLASSID, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
          left join QUALITY on (QUALITY.QUALITYID = EA.QUALITYID)
          left join FORWARDER_WEIGH64WASTE(:AORDERITEMID, :AINVENTORYID,
              :AOPERATIONID, EAIMAIN.EXPORTACTITEMID, :ISWEIGHT, :EA_OPERATIONID) FWW on (0=0)
        where (EA.OWNERID = :AOWNERID) and (:AOPERATIONID = 65)
          and (EA.OPERATIONID <> 5) and (EAIMAIN.OPERATIONID in (1,17))
          and (QUALITY.CROPID = :ACROPID) and (QUALITY.CLASSID = :ACLASSID)
          and (IT.PARTYID = GETWORKPARTIES.PARTYID) and (EAIMAIN.WEIGHT > 0)
          and ((EA.STATE in (0,1))
               or ((EA.STATE = 10)
                   and ((EA.ORDERITEMID = :AORDERITEMID)
                        or ((EA.ORDERITEMID is null)
                            and (EA.EXPORTACTID = (select EXPORTACTID
                                                     from EXPORTACTITEMS
                                                     where (EXPORTACTITEMS.INVENTORYID = :AINVENTORYID)
                                                       and (EXPORTACTITEMS.OPERATIONID = -64))))))
              )
      group by ACTDATE, EA.ACTNUMBER, PROVIDERS.CLIENTFULLNAME, EA.OPERATIONID, EA.EXPORTACTID
      having ((sum(FWW.TRANSPORTWEIGHT + coalesce(FWW.CUSTOMWASTE,0)) > 0) or (sum(FWW.CUSTOMWASTE) > 0))
      into :EXPORTACTID, :OPERATIONID, :EAOUT, :ACTDATE, :ACTNUMBER,
           :PROVIDERFULLNAME, :SUMACTNET, :CUSTOMWASTE, :READ_REC
    do begin
      ALL_EXPORTACTID = :EXPORTACTID;

      suspend;
    end
  end
end