ALTER TABLE TBWORKFLOW_TAREFA
    ADD PONTO_APROVACAO INTEGER;

COMMENT ON COLUMN TBWORKFLOW_TAREFA.PONTO_APROVACAO IS
'ID da tarefa que assumira a proxima (tarefas anteriores a escolhida serao encerradas))';

/**************************************************************************************/
/**************************************************************************************/
/**************************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_tarefa_b1u for tbworkflow_tarefa
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

  IF (OLD.TITULO <> NEW.TITULO) THEN
  BEGIN
     HISTORICO = HISTORICO||' TIT.:'||OLD.TITULO||'->'||NEW.TITULO;
  END

  IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
  BEGIN
     HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
  END
  
  IF (OLD.STATUS <> NEW.STATUS) THEN
  BEGIN
     HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS||'->'||NEW.STATUS;
  END

  IF (OLD.SEQUENCIA <> NEW.SEQUENCIA) THEN
  BEGIN
     HISTORICO = HISTORICO||' SEQ:'||OLD.SEQUENCIA||'->'||NEW.SEQUENCIA;
  END

  IF (OLD.TEMPO_PREVISTO <> NEW.TEMPO_PREVISTO) THEN
  BEGIN
     HISTORICO = HISTORICO||' TEMPO:'||OLD.TEMPO_PREVISTO||'->'||NEW.TEMPO_PREVISTO;
  END

  IF (OLD.PONTO_REPROVACAO <> NEW.PONTO_REPROVACAO) THEN
  BEGIN
     HISTORICO = HISTORICO||' PONTO REPROV:'||OLD.PONTO_REPROVACAO||'->'||NEW.PONTO_REPROVACAO;
  END

  IF (OLD.PONTO_APROVACAO <> NEW.PONTO_APROVACAO) THEN
  BEGIN
     HISTORICO = HISTORICO||' PONTO APROV:'||OLD.PONTO_APROVACAO||'->'||NEW.PONTO_APROVACAO;
  END

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_TAREFA', OLD.ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     :HISTORICO);
  END
END
^

SET TERM ; ^

/********************************************************************************************************/
/********************************************************************************************************/
/********************************************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPLIT_BLOB2 (
    ITEXTO blob sub_type 1 segment size 80,
    ISEPARADOR varchar(10))
returns (
    OSPLIT blob sub_type 1 segment size 80)
as
declare variable VTEXTO blob sub_type 1 segment size 80;
declare variable VPOSICAO integer;
declare variable VPOSICAO_INICIAL integer;
declare variable VSPLIT blob sub_type 1 segment size 80;
BEGIN
    vtexto = itexto;
    vposicao_inicial = 1;
    vposicao = 1;
    
    IF (iseparador IS NULL OR itexto IS NULL) THEN
    BEGIN
        -- Sai da função sem executar
    END
    ELSE
    BEGIN
        WHILE (CHAR_LENGTH(vtexto) > 0) DO
        BEGIN
            vposicao = POSITION (:iseparador, vtexto, vposicao_inicial);
            IF (vposicao <= 0) THEN
            BEGIN
                vsplit = SUBSTRING (:vtexto FROM :vposicao_inicial FOR
                        CHAR_LENGTH (:vtexto) - vposicao_inicial + 1);
                IF (NOT :vsplit IS NULL AND NOT :vsplit = '') THEN
                BEGIN
                    osplit = :vsplit;   
                    SUSPEND;
                END
                BREAK;
            END
            vsplit = SUBSTRING (:vtexto FROM :vposicao_inicial
                    FOR :vposicao - vposicao_inicial);
            IF (NOT :vsplit IS NULL AND NOT :vsplit = '') THEN
            BEGIN
                osplit = :vsplit;   
            END
            vsplit = NULL;
            vposicao_inicial = :vposicao + 1;
            SUSPEND;
        END
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER function FN_JSON_V4 (
    JSON blob sub_type 1 segment size 80,
    CAMPO varchar(50))
returns blob sub_type 1 segment size 80
AS
declare variable retorno blob sub_type 1 segment size 80;
declare variable pos integer;
begin
    RETORNO = '';

    POS = POSITION ('"'||:CAMPO||'":', :JSON, 1);
    if(POS > 0)then
    begin
        RETORNO = SUBSTRING (:JSON FROM (:POS + CHAR_LENGTH ('"'||:CAMPO||'":')) FOR CHAR_LENGTH (:JSON) - 0 + 1);

        POS = POSITION (',', :RETORNO, 2);

        IF (:POS > 1) THEN
        BEGIN
            RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
        END ELSE
        BEGIN
            POS = POSITION ('"', :RETORNO, 2);
    
            IF (:POS > 1) THEN
            BEGIN
                RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
            END ELSE
            BEGIN
                POS = POSITION ('}', :RETORNO, 2);
    
                IF (:POS > 1) THEN
                BEGIN
                    RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
                END
            END
        END

    end

    RETORNO = replace(RETORNO,'"','');
    RETORNO = replace(RETORNO,'"','');

    return replace(RETORNO,'"','');
end
^

SET TERM ; ^

/********************************************************************************************************/
/********************************************************************************************************/

