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

DATABASE_CLEARING

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

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

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

Описание

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

Определение

CREATE PROCEDURE DATABASE_CLEARING
RETURNS (
    PARTYID INTEGER,
    PARTYNAME VARCHAR(100))
AS
declare variable PERIODID integer;
declare variable INVENTORYID integer;
declare variable INVOICEID integer;
declare variable INVOICEREGISTERID integer;
declare variable INVOICEREGISTERSQUALITYID integer;
declare variable SAMPLERQUALITYID integer;
declare variable LABQUALITYID integer;
declare variable BUHQUALITYID integer;
declare variable QUALITYHUMIDITYID integer;
declare variable ORDERLOADITEMID integer;
declare variable ORDERLOADID integer;
declare variable TABLEOPERATIONID integer;
declare variable RECORD_COUNT integer;
declare variable EXPORTACTITEMID integer;
declare variable EXPORTACTID integer;
declare variable EXPORTACTSQUALITYID integer;
begin
  select PERIODS.PERIODID
    from PERIODS
    where (PERIODS.STATE = 1)
  into :PERIODID;

  if (PERIODID is not null) then
    update PERIODS
    set STATE = 0
    where (PERIODS.PERIODID = :PERIODID);

  for
    select PARTIES.PARTYID, PARTIES.PARTYNAME
      from PARTIES
      where (PARTIES.STATE = 0)
/*
    union
      select -999, 'пусто'
        from rdb$database
*/
    into :PARTYID, :PARTYNAME

  do begin
    for
      select IT.INVENTORYID, IT.INVOICEID, IT.REGISTERID,
          IT.SAMPLERQUALITYID, IT.LABQUALITYID, IT.BUHQUALITYID,
          IT.ORDERLOADITEMID, ORDERLOAD.ORDERLOADID
        from IT
          left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = IT.ORDERLOADITEMID)
          left join ORDERLOAD on (ORDERLOAD.ORDERLOADID = ORDERLOADITEMS.ORDERLOADID)
        where (coalesce(IT.PARTYID,0) = :PARTYID)
      into :INVENTORYID, :INVOICEID, :INVOICEREGISTERID,
           :SAMPLERQUALITYID, :LABQUALITYID, :BUHQUALITYID,
           :ORDERLOADITEMID, :ORDERLOADID

    do begin
      INVOICEREGISTERSQUALITYID = null;

      select INVOICEREGISTERS.QUALITYID
        from INVOICEREGISTERS
        where (INVOICEREGISTERS.INVOICEREGISTERID = :INVOICEREGISTERID)
      into INVOICEREGISTERSQUALITYID;

      if (INVOICEREGISTERSQUALITYID is not null) then begin
        delete from QUALITY
        where (QUALITY.QUALITYID = :INVOICEREGISTERSQUALITYID);

        delete from QUALITYDATA
        where (QUALITYDATA.QUALITYID = :INVOICEREGISTERSQUALITYID);

        delete from INVOICEREGISTERS
        where (INVOICEREGISTERS.INVOICEREGISTERID = :INVOICEREGISTERID);
      end

      if (SAMPLERQUALITYID <> 638640) then begin
        for
          select QUALITY.QUALITYHUMIDITYID
            from QUALITY
            where (QUALITYID in (:LABQUALITYID, :BUHQUALITYID))
          into :QUALITYHUMIDITYID
        do begin
          delete from QUALITYHUMIDITY
          where (QUALITYHUMIDITY.QUALITYHUMIDITYID = :QUALITYHUMIDITYID);
        end

        delete from QUALITY
        where (QUALITY.QUALITYID in (:SAMPLERQUALITYID, :LABQUALITYID, :BUHQUALITYID));

        delete from QUALITYDATA
        where (QUALITYDATA.QUALITYID in (:SAMPLERQUALITYID, :LABQUALITYID, :BUHQUALITYID));
      end

      if (cast(:ORDERLOADITEMID as integer) > 0) then begin
        delete from ORDERLOADITEMS
        where (ORDERLOADITEMS.ORDERLOADID = :ORDERLOADID);

        delete from ORDERLOAD
        where (ORDERLOAD.ORDERLOADID = :ORDERLOADID);
      end

      TABLEOPERATIONID = null;

      for
        select ILT$TABLEOPERATION.TABLEOPERATIONID
          from ILT$TABLEOPERATION
          where (ILT$TABLEOPERATION.RECORDID in (:SAMPLERQUALITYID, :LABQUALITYID, :BUHQUALITYID, :INVENTORYID))
        into :TABLEOPERATIONID
      do begin
        if (TABLEOPERATIONID is not null) then begin
          select count(ILT$FIELDINTEGER.FIELDID)
            from ILT$FIELDINTEGER
            where (ILT$FIELDINTEGER.TABLEOPERATIONID = :TABLEOPERATIONID)
          into :RECORD_COUNT;
          if (RECORD_COUNT > 0) then
            delete from ILT$FIELDINTEGER
            where (ILT$FIELDINTEGER.TABLEOPERATIONID = :TABLEOPERATIONID);

          delete from ILT$FIELDDATE
          where (ILT$FIELDDATE.TABLEOPERATIONID = :TABLEOPERATIONID);
/*
          select count(ILT$TABLEOPERATION.TABLEOPERATIONID)
            from ILT$TABLEOPERATION
            where (ILT$TABLEOPERATION.TABLEOPERATIONID = :TABLEOPERATIONID)
          into :RECORD_COUNT;
          if (RECORD_COUNT > 0) then
            delete from ILT$TABLEOPERATION
            where (ILT$TABLEOPERATION.TABLEOPERATIONID = :TABLEOPERATIONID);
*/
        end
      end

      delete from ILT$TABLEOPERATION
      where (ILT$TABLEOPERATION.RECORDID in (:SAMPLERQUALITYID, :LABQUALITYID, :BUHQUALITYID, :INVENTORYID));

      EXPORTACTITEMID = null;
      EXPORTACTID = null;

      for
        select
            EXPORTACTITEMS.EXPORTACTITEMID, EXPORTACTITEMS.EXPORTACTID
          from EXPORTACTITEMS
          where (EXPORTACTITEMS.INVENTORYID = :INVENTORYID)
        into :EXPORTACTITEMID, :EXPORTACTID
      do begin
        if (EXPORTACTITEMID is not null) then begin
          delete from EXPORTACTITEMS
          where (EXPORTACTITEMS.EXPORTACTITEMID = :EXPORTACTITEMID);

          select EXPORTACTS.QUALITYID
            from EXPORTACTS
            where (EXPORTACTS.EXPORTACTID = :EXPORTACTID)
          into :EXPORTACTSQUALITYID;

          delete from QUALITY
          where (QUALITY.QUALITYID = :EXPORTACTSQUALITYID);

          delete from QUALITYDATA
          where (QUALITYDATA.QUALITYID = :EXPORTACTSQUALITYID);

          delete from EXPORTACTS
          where (EXPORTACTS.EXPORTACTID = :EXPORTACTID);
        end
      end

      delete from INVOICES
      where (INVOICES.INVOICEID = :INVOICEID);

      delete from IT
      where (IT.INVENTORYID = :INVENTORYID);
    end

    delete from PARTIES
    where (PARTIES.PARTYID = :PARTYID);

    PARTYNAME = PARTYNAME || ' - удалено';

    suspend;
  end

  PARTYID = null;
  PARTYNAME = 'Удаление завершено';

  suspend;

  if (PERIODID is not null) then
    update PERIODS
    set STATE = 1
    where (PERIODS.PERIODID = :PERIODID);
end