SET TERM ^ ;

CREATE OR ALTER function FN_JSON_ARRAY3 (
    JSON varchar(7000),
    CAMPO varchar(500))
returns varchar(7000)
AS
DECLARE VARIABLE RETORNO VARCHAR(5000);
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));
        POS = POSITION ('"', :RETORNO);

        if(POS is distinct from 1)then
        begin
            POS = POSITION (',"', :RETORNO, 2);

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

    END

    POS = POSITION('"', :RETORNO, 1);

    IF (POS = 1) THEN
    BEGIN
        RETORNO = SUBSTRING (:RETORNO FROM 2 FOR CHAR_LENGTH(:RETORNO));
    END

    RETURN RETORNO;
END
^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure CASO_DETALHE_V2 (
    PAINEL_ID integer,
    CASO_ID integer)
returns (
    TIPO integer,
    DESCRICAO varchar(100),
    VALOR varchar(7000),
    CAMPO_ID integer)
as
declare variable EMAIL varchar(5000);
declare variable MONITOR varchar(5000);
declare variable MOTIVO varchar(5000);
declare variable TIPOV varchar(5000);
declare variable ORIGEM varchar(5000);
declare variable RESPONSAVEL varchar(5000);
declare variable COR varchar(5000);
declare variable STATUS varchar(5000);
begin

  for
       SELECT first 1
            p.email,
            p.email_monitor,
            m.descricao   as MOTIVO,
            T.descricao   as TIPO,
            o.descricao   as ORIGEM,
            u.usuario     as RESPONSAVEL,
            i.descricao   as STATUS,
            i.cor         as COR
        
        FROM tbcaso_registro r, tbcaso_painel p, tbcaso_tipo t,tbcaso_motivo m,
            tbcaso_origem o, tbusuario u, tbcaso_status i
        where p.id   = :painel_id
        and r.id     = :caso_id
        and t.id     = r.tipo_id
        and m.id     = r.motivo_id
        and o.id     = r.origem_id
        and i.id     = r.status_id
        and u.codigo = r.responsavel_id
    into
        :email,
        :monitor,
        :MOTIVO,
        :TIPOV,
        :ORIGEM,
        :RESPONSAVEL,
        :STATUS,
        :COR
    do
    begin
        :campo_id = 0;
        tipo = 7;
        descricao = 'Motivo';         valor = MOTIVO;      suspend;
        descricao = 'Tipo';           valor = TIPOV;       suspend;
        descricao = 'Tipo de Origem'; valor = ORIGEM;      suspend;
        descricao = 'Responsavel';    valor = RESPONSAVEL; suspend;
        descricao = 'Status';         valor = STATUS;      suspend;
    end

    for
       select
        
            a.tipo,
            a.campo_id,
            a.descricao,
            iif(a.tipo = 9 or a.tipo = 9 , i.descricao,
            iif(a.tipo = 4,
            (
                select
                    list(s.descricao||':'||r.selecionado,', ')
                from tbcaso_registro_itens r, tbcaso_painel_conf_itens s
                where r.campo_id = a.id
                and CAST(r.valor AS VARCHAR(100)) = CAST(s.valor AS VARCHAR(100))
                and s.campo_id = r.campo_id
                and r.registro_id = a.registro_id
            ),
            iif(a.tipo = 7,
        
                FN_JSON_ARRAY3(a.json, a.campo_visualizar)
        
            ,a.valor))) as valor
        
        from
            (
                select
                
                    f.id,
                    c.campo_id,
                    c.registro_id,
                    f.tipo,
                    f.descricao,
                    c.json,
                    f.campo_visualizar,
                    fn_is_empty2(cast(c.valor as varchar(7000)), '0') as valor,
                    iif(f.tipo = 9 or f.tipo = 5, fn_is_empty2(c.valor, '0'), 0) as chave
                
                from tbcaso_registro_detalhe c, tbcaso_painel_conf f
                where c.registro_id = :caso_id
                and   c.painel_id   = :painel_id
                and   c.campo_id    = f.id
                order by c.campo_id
            )a
            left join tbcaso_painel_conf_itens i on (i.campo_id = a.ID and i.valor = a.chave)
    into
        :tipo,
        :campo_id,
        :descricao,
        :valor
    do
    begin
        if(:valor = '')then
        begin
            :valor = null;
        end
        suspend;
    end


end
^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_registro_itens_b01u for tbcaso_registro_itens
active before update position 0
AS
    declare variable descricao varchar(100);
    declare variable campo varchar(100);
    declare variable tipo integer;
    declare variable painel integer;
begin

    if(old.selecionado <> new.selecionado)then
    begin
        painel = (select first 1 r.painel_id from tbcaso_registro r where r.id = new.registro_id);

        for
            select
                first 1
                c.descricao,
                j.descricao as campo
            from
            tbcaso_painel_conf_itens c, tbcaso_painel_conf j
                where c.painel_id = :painel
                  and c.campo_id = new.campo_id
                  and c.valor = cast(new.valor as integer)
                  and j.id = c.campo_id
        into
            :descricao,
            :campo
        do begin

            if(new.selecionado = 1)then
            begin
                execute procedure SPI_CASO_HISTORICO(new.registro_id,painel,campo||': '||descricao||' Marcado');
            end
            else
            begin
                execute procedure SPI_CASO_HISTORICO(new.registro_id,painel,campo||': '||descricao||' Desmarcado');
            end

        end


    end
end
^

SET TERM ; ^

