SET TERM ^ ;

create or alter procedure SPADM_CLEARUP_TABLEPREPARE (
    TABLENAME char(31),
    PARAMSTR varchar(1024))
returns (
    TEXT varchar(8000),
    WARNING_COUNT integer,
    ERROR_COUNT integer)
as
declare variable CANCLEARUP smallint;
declare variable FIELDS_IN_WORKTABLES varchar(8000);
declare variable WORKTABLES varchar(8000);
declare variable ALWAYSEXCLUDEDTABLES varchar(8000);
declare variable CHECK_FIELDS_IN_WORKTABLES smallint;
declare variable CHECK_WORKTABLES smallint;
declare variable CRLF char(2);
declare variable GENERATOR_NAME char(31);
declare variable GENERATOR_COUNT integer;
declare variable DELETE_STAFF smallint;
declare variable DELETE_USER smallint;
begin
-- Author mvs 2015.09.17
-- mvs 2015.10.02
-- mvs 2017.10.05
-- mvs 2021.08.10
--  CRLF = ascii_char(13)|| ascii_char(10);
  CRLF = '';
  TABLENAME = trim(XUpper(sn(TABLENAME)));
  TEXT = '-- BEGIN CLEARUP ' || TABLENAME || CRLF;
  WARNING_COUNT = 0;
  ERROR_COUNT = 0;
  suspend;
  canclearup = 0;
  ALWAYSEXCLUDEDTABLES = XUPPER('DEFAULTS,DEFAULT_VALUES,ENTERPRISEDEBTOR');
  FIELDS_IN_WORKTABLES = XUPPER('GUEST,FOLIO,DIARY,ABONEMENT,ABONEMENT_PACKAGE,POS,INVOICE,FCID');
  WORKTABLES = XUPPER('APP_USERS_PROFILE,GUESTS,FOLIO,WORKSHIFTS,POS,ADVANCE_PURPOSE,DATA_BLOBS,FOLIO_CONTENTS_EX,FOLIO_JOINTRANS,IMAGES,REASONS,STAFF_WORK,SYS$LOG,TRANSACTION_JOURNAL,CHECK_CONTENT_DETAILS,HEAP');
  CHECK_FIELDS_IN_WORKTABLES = getnamedparamdefnum(PARAMSTR, 'CHECK_FIELDS_IN_WORKTABLES', 1);
  CHECK_WORKTABLES = getnamedparamdefnum(PARAMSTR, 'CHECK_WORKTABLES', 1);
  delete_staff = getnamedparamdefnum(PARAMSTR, 'delete_staff', 1);
  delete_user = getnamedparamdefnum(PARAMSTR, 'delete_user', 1);
  if (exists(
               select f.rdb$field_name
                 from rdb$relation_fields f
               where XUpper(f.rdb$relation_name)=:TABLENAME
                 and snn(f.rdb$system_flag)=0
--                 and XUpper(f.rdb$field_name)='ID'
                 and
                 (
                   (
                     :CHECK_FIELDS_IN_WORKTABLES=0
                     or
                     ','||:FIELDS_IN_WORKTABLES||',' containing ','||trim(XUPPER(f.rdb$field_name))||','
                   )
                   or
                   (
                     :CHECK_WORKTABLES=0
                     or
                     ','||:WORKTABLES||',' containing ','||trim(XUPPER(f.rdb$relation_name))||','
                   )
                 )
                 and not (','||:ALWAYSEXCLUDEDTABLES||',' containing ','||trim(XUPPER(f.rdb$relation_name))||',')

   )) then
    canclearup = 1;
  if (canclearup=0) then
  begin
    TEXT = '-- CAN''T CLEARUP ' || TABLENAME || CRLF;
    suspend;
  end
  else
  begin
    if ((TABLENAME='GUESTS') or (TABLENAME='FOLIO') or (TABLENAME='STAFF') or (TABLENAME='USERS') or (TABLENAME='POS'))  then
    begin
      TEXT = '-- SPECIAL PROCESSING FOR ' || TABLENAME || CRLF;
      suspend;
      if (TABLENAME='GUESTS') then
      begin
        TEXT = 'DELETE FROM ' || TABLENAME || ' WHERE ID >=1000 and not(GuestType=2) and personkind=0 ;' || CRLF;
        suspend;
      end
      if (TABLENAME='FOLIO') then
      begin
        TEXT = 'DELETE FROM ' || TABLENAME || ' f WHERE f.ID >=1000 '
          || CRLF
          || ' AND NOT EXISTS(select g.id from guests g where g.folio=f.id ) '
          || CRLF
          || ' AND NOT EXISTS(select e.id from ENTERPRISEDEBTOR e where e.folio=f.id ) '
          || ' ;' || CRLF;
        suspend;
        TEXT = 'update FOLIO set SERVICEBALANCE=0,PAYMENTBALANCE=0,PERMISSIBLEBALANCE=0,FISCALBALANCE=0,CONTENTCOUNT=0,PAYLASTSHIFT=0, EXTRABALANCE=0;' || CRLF;
        suspend;
      end
      if (TABLENAME='STAFF') then
      begin
        if (delete_staff=1) then
        begin
          TEXT = 'DELETE FROM ' || TABLENAME || CRLF;
          suspend;
          TEXT = 'DELETE FROM GUESTS WHERE ID >=1000 and not(GuestType=2) and bin_and(personkind,1)=1 ;' || CRLF;
          suspend;
        end
      end
      if (TABLENAME='USERS') then
      begin
        if (delete_user=1) then
        begin
          TEXT = 'DELETE FROM ' || TABLENAME || CRLF;
          suspend;
          TEXT = 'DELETE FROM GUESTS WHERE ID >=1000 and not(GuestType=2) and bin_and(personkind,2)=2 ;' || CRLF;
          suspend;
        end
      end
      if (TABLENAME='POS') then
      begin
        TEXT = 'UPDATE POS P SET P.SHIFT=0 , P.SHIFTSUM=0, P.SHIFTBILLS=0;' || CRLF;
        suspend;
      end

    end
    else
    begin
      TEXT = '-- PROCESSING FOR ' || TABLENAME || CRLF;
      suspend;
      TEXT = 'DELETE FROM ' || TABLENAME || ';' || CRLF;
      suspend;
    end
    GENERATOR_COUNT = 0;
    for select rg.rdb$generator_name from
      (
        with
          t as
            (
              select s.line, s.item, tr.rdb$trigger_name from rdb$triggers tr
                left join sys$string_split(tr.rdb$trigger_source,'') s on 1=1
              where snn(tr.rdb$system_flag)=0
                and tr.rdb$trigger_inactive=0
                and tr.rdb$trigger_type in (1,17)
                and tr.rdb$relation_name=:TABLENAME
            ),
          gL as
            (
              select t.line, t.item, t.rdb$trigger_name from t
                where upper(t.item)='GEN_ID'
               order by t.line
            )
        select t.item GENERATOR_NAME from gl
          join t on t.line=gl.line+1 and t.rdb$trigger_name=gl.rdb$trigger_name
          join t t0 on t0.line=gl.line-1 and t0.rdb$trigger_name=gl.rdb$trigger_name and UPPER(t0.item)='ID'
          union select 'G_'|| :TABLENAME from dummy
      )
      mgl
        join rdb$generators rg on XUPPER(rg.rdb$generator_name)=XUPPER(mgl.GENERATOR_NAME) and snn(rg.rdb$system_flag)=0 into GENERATOR_NAME
      do
      begin
        GENERATOR_COUNT = GENERATOR_COUNT + 1;
        TEXT = 'SELECT gen_id('|| GENERATOR_NAME ||', -gen_id('|| GENERATOR_NAME || ',0) + coalesce((select max(id) from '|| TABLENAME ||'),0)) from rdb$database ;'  || CRLF;
        suspend;
      end
    if (GENERATOR_COUNT=0) then
    begin
      TEXT = '-- GENERATOR NOT FOUND FOR ' || TABLENAME || CRLF;
      suspend;
    end
    else
    begin
      if (GENERATOR_COUNT>1) then
      begin
        TEXT = '-- WARNING!!! GENERATOR_COUNT FOR ' || TABLENAME || ' = ' || GENERATOR_COUNT || CRLF;
        WARNING_COUNT = WARNING_COUNT + 1;
        suspend;
      end
    end
  end
  WARNING_COUNT = 0;
  TEXT = '-- END CLEARUP ' || TABLENAME || CRLF;
  suspend;
end
^
commit ^

SET TERM ; ^