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

REGISTER_TO_ACT

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

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

(Нет выходных параметров)

Описание

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

Определение

CREATE PROCEDURE REGISTER_TO_ACT(
    REGISTERID INTEGER)
AS
declare variable TREGDATE date;
declare variable TSTORAGEID integer;
declare variable TQUALITYID integer;
declare variable TTRANSPORTSUBTYPE integer;
declare variable TREGISTERNUMBER integer;
declare variable TOWNERID integer;
declare variable TPROVIDERID integer;
declare variable TSTATIONID integer;
declare variable TEXPORTACTID integer;
declare variable TINVENTORYID integer;
declare variable TNET integer;
declare variable TWEIGHT integer;
declare variable TEXPORTACTITEMID integer;
declare variable COUNTREG integer = 0;
declare variable ACTID integer = 0;
declare variable ITEMID integer;
declare variable ITITEMID integer;
declare variable COUNTREGACT integer;
declare variable TEMPID integer;
declare variable TEMPACTID integer;
declare variable TEMPCOUNT integer;
begin
/*
  COUNTREG = 0;
  ACTID = 0;
*/
  select count(IT.INVENTORYID)
    from IT
    where ((IT.REGISTERID = :REGISTERID) and (IT.STATE <> -1))
  into :COUNTREG;

  select count(EXPORTACTS.EXPORTACTID)
    from EXPORTACTS
    where ((EXPORTACTS.REGISTERID = :REGISTERID) and (EXPORTACTS.STATE <> -1))
  into :COUNTREGACT;

  if ((COUNTREG = 0) and (COUNTREGAct > 0)) then begin
    delete from EXPORTACTS
      where EXPORTACTS.REGISTERID = :REGISTERID;
  end
  else
    if (COUNTREG > 0) then begin
  --// не вкл.
      for
        select EXPORTACTITEMS.EXPORTACTID, EXPORTACTITEMS.EXPORTACTITEMID
          from EXPORTACTS
            left join EXPORTACTITEMS on (EXPORTACTS.EXPORTACTID = EXPORTACTITEMS.EXPORTACTID)
          where (EXPORTACTS.REGISTERID = :REGISTERID)
            and (not exists(select IT.INVENTORYID
                              from IT
                              where ((IT.INVENTORYID = EXPORTACTITEMS.INVENTORYID)
                                and (IT.REGISTERID = :REGISTERID))))
        into :TEMPACTID, :TEMPID
      do begin
        delete from EXPORTACTITEMS
          where EXPORTACTITEMID = :TEMPID;

        select count (EXPORTACTITEMID)
          from EXPORTACTITEMS
          where (EXPORTACTID = :TEMPACTID)
        into :TEMPCOUNT;
        if (TEMPCOUNT = 0) then
          delete from EXPORTACTS
            where EXPORTACTID = :TEMPACTID;
      end

  --//
      for
        select IT.REGDATE, IT.STORAGEID, IT.LABQUALITYID, TRANSPORTTYPE.TRANSPORTSUBTYPE,
            INVOICEREGISTERS.REGISTERNUMBER, IT.OWNERID, IT.PROVIDERID, INVOICES.STATIONID
          from IT
            left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
            left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
            left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
            left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
          where ((IT.REGISTERID = :REGISTERID) and (IT.STATE <> -1))
        group by  IT.REGDATE, IT.STORAGEID, IT.LABQUALITYID, TRANSPORTTYPE.TRANSPORTSUBTYPE,
            INVOICEREGISTERS.REGISTERNUMBER, IT.OWNERID, IT.PROVIDERID, INVOICES.STATIONID
        into :TREGDATE, :TSTORAGEID, :TQUALITYID, :TTRANSPORTSUBTYPE,
            :TREGISTERNUMBER, :TOWNERID, :TPROVIDERID, :TSTATIONID
      do begin
        select EXPORTACTS.EXPORTACTID
          from EXPORTACTS
          where (REGISTERID = :REGISTERID)
            and (STORAGEID = :TSTORAGEID)
            and (QUALITYID =:TQUALITYID)
            and (TRANSPORTSUBTYPEID = :TTRANSPORTSUBTYPE)
            and (OWNERID = :TOWNERID)
            and ((PROVIDERID = :TPROVIDERID) or (:TPROVIDERID is null))
            and ((STATIONID = :TSTATIONID) or (:TSTATIONID is null))
        into :ACTID;

        if (ACTID = 0) then begin
          TEXPORTACTID = gen_id(EXPORTACTIDGEN,1);
          insert into EXPORTACTS (EXPORTACTID, REGDATE, ACTDATE, OPERATIONID, QUALITYID,
              TRANSPORTSUBTYPEID, ACTNUMBER, STORAGEID, OWNERID, PROVIDERID, STATIONID,
              STATE, REGISTERID)
            values (:TEXPORTACTID, :TREGDATE, :TREGDATE, 1, :TQUALITYID,
                :TTRANSPORTSUBTYPE, :TREGISTERNUMBER, :TSTORAGEID, :TOWNERID, :TPROVIDERID, :TSTATIONID,
                0, :REGISTERID);
          ACTID = TEXPORTACTID;
        end
        else begin
          update EXPORTACTS
          set REGDATE = :TREGDATE,
              ACTDATE = :TREGDATE,
              QUALITYID = :TQUALITYID,
              TRANSPORTSUBTYPEID = :TTRANSPORTSUBTYPE,
              ACTNUMBER = :TREGISTERNUMBER,
              STORAGEID = :TSTORAGEID,
              OWNERID = :TOWNERID,
              PROVIDERID = :TPROVIDERID,
              STATIONID = :TSTATIONID,
              STATE = 0
          where ((EXPORTACTID = :ACTID)
            and (REGISTERID = :REGISTERID));

        /*for
          select EXPORTACTITEMS.EXPORTACTITEMID
          from EXPORTACTITEMS
            left join IT on (EXPORTACTITEMS.INVENTORYID = IT.INVENTORYID
              and IT.REGISTERID = :REGISTERID)
          where EXPORTACTITEMS.EXPORTACTID = :ACTID
           and IT.INVENTORYID is null
          into :ITITEMID
        do
          delete from EXPORTACTITEMS where EXPORTACTITEMS.EXPORTACTITEMID = :ITITEMID;
        */
        end

        for
          select IT.INVENTORYID, IT.NET, IT.WEIGHT
            from IT
              left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
              left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
              left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
              left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
            where (IT.REGISTERID = :REGISTERID)
              and (IT.STATE <> -1)
              and (IT.REGDATE = :TREGDATE)
              and (IT.STORAGEID = :TSTORAGEID)
              and (IT.LABQUALITYID =:TQUALITYID)
              and (TRANSPORTTYPE.TRANSPORTSUBTYPE = :TTRANSPORTSUBTYPE)
              and (INVOICEREGISTERS.REGISTERNUMBER = :TREGISTERNUMBER)
              and (IT.OWNERID = :TOWNERID)
              and ((IT.PROVIDERID = :TPROVIDERID) or (:TPROVIDERID is null))
              and ((INVOICES.STATIONID = :TSTATIONID) or (:TSTATIONID is null))
          into :TINVENTORYID, :TNET, :TWEIGHT
        do begin
          ITEMID = 0;

          if (ACTID > 0) then
            select EXPORTACTITEMS.EXPORTACTITEMID
              from EXPORTACTITEMS
                left join EXPORTACTS on (EXPORTACTS.EXPORTACTID = EXPORTACTITEMS.EXPORTACTID)
              where (EXPORTACTITEMS.EXPORTACTID = :ACTID)
                and (EXPORTACTITEMS.INVENTORYID = :TINVENTORYID)
                and (EXPORTACTS.STATE = 0)
            into :ITEMID;

          if (:ITEMID = 0) then begin
            TEXPORTACTITEMID = gen_id(EXPORTACTITEMIDGEN,1);
            insert into EXPORTACTITEMS (EXPORTACTITEMID, EXPORTACTID, OPERATIONID, INVENTORYID, WEIGHT, TESTWEIGHT)
              values (:TEXPORTACTITEMID, :ACTID, 1, :TINVENTORYID, :TNET, :TWEIGHT);
          end
          else
            update EXPORTACTITEMS
            set WEIGHT = :TNET,
                TESTWEIGHT = :TWEIGHT
            where (EXPORTACTITEMID = :ITEMID);
        end
        ACTID = 0;
      end
    end
end