SET TERM ^ ;

CREATE OR ALTER function FN_JSON_ARRAY2 (
    JSON varchar(500),
    CAMPO varchar(50))
returns varchar(100)
AS
declare variable retorno varchar(500);
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, 2);

        if(POS = 0)then
        begin
          POS = POSITION('"}', :RETORNO, 2);
        end

        RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS -2 ));
    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 SPC_CASO_DETALHE (
    IPAINEL_ID integer not null,
    ICASO_ID integer not null,
    ICAMPO_VISUALIZACAO varchar(50) not null)
returns (
    OCAMPO_ID integer,
    OTIPO integer,
    ODESCRICAO varchar(100),
    OVALOR varchar(7000))
as
declare variable EMAIL varchar(500);
declare variable MONITOR varchar(500);
declare variable MOTIVO varchar(500);
declare variable TIPO varchar(500);
declare variable ORIGEM varchar(500);
declare variable RESPONSAVEL varchar(500);
declare variable COR varchar(500);
declare variable STATUS varchar(500);
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
        INNER JOIN TBCASO_PAINEL P ON P.ID = R.PAINEL_ID
        INNER JOIN TBCASO_TIPO T ON T.ID = R.TIPO_ID
        INNER JOIN TBCASO_MOTIVO M ON M.ID = R.MOTIVO_ID
        INNER JOIN TBCASO_ORIGEM O ON O.ID = R.ORIGEM_ID
        INNER JOIN TBUSUARIO U ON U.CODIGO = R.RESPONSAVEL_ID
        INNER JOIN TBCASO_STATUS I ON I.ID = R.STATUS_ID
        WHERE P.ID = :IPAINEL_ID
        AND R.ID = :ICASO_ID
    
    INTO
        :EMAIL,
        :MONITOR,
        :MOTIVO,
        :TIPO,
        :ORIGEM,
        :RESPONSAVEL,
        :STATUS,
        :COR

    DO
    BEGIN
        
        :OCAMPO_ID = 0;
        :OTIPO     = 7;

        :ODESCRICAO = 'Motivo';
        :OVALOR = :MOTIVO;
        SUSPEND;

        :ODESCRICAO = 'Tipo';
        :OVALOR = :TIPO;
        SUSPEND;

        :ODESCRICAO = 'Tipo de Origem';
        :OVALOR = :ORIGEM;
        SUSPEND;

        :ODESCRICAO = 'Responsável';
        :OVALOR = :RESPONSAVEL;
        SUSPEND;

        :ODESCRICAO = 'Status';
        :OVALOR = :STATUS;
        SUSPEND;

    END

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

    FOR
        SELECT
            A.TIPO,
            A.CAMPO_ID,
            A.DESCRICAO,
        
            IIF(A.TIPO = 9, FN_IS_EMPTY2(I.DESCRICAO, ''),
            IIF(((A.TIPO = 4) OR (A.TIPO = 5)), COALESCE((SELECT LIST(S.DESCRICAO||':'||R.SELECIONADO, ', ') FROM TBCASO_REGISTRO_ITENS R, TBCASO_PAINEL_CONF_ITENS S WHERE R.CAMPO_ID = A.ID AND R.VALOR = S.VALOR AND S.CAMPO_ID = R.CAMPO_ID AND R.REGISTRO_ID = A.REGISTRO_ID), ''),
            IIF(A.TIPO = 7, FN_JSON_ARRAY2(A.JSON, COALESCE((SELECT FIRST 1 V.CAMPO FROM TBCASO_VISUALIZACAO_CAMPO V WHERE V.CAMPO_ID = A.ID AND V.PAINEL_ID = A.PAINEL_ID AND V.CAMPO LIKE COALESCE(:ICAMPO_VISUALIZACAO, '')), COALESCE(:ICAMPO_VISUALIZACAO, ''))), A.VALOR))) AS VALOR
        
        FROM (
            SELECT
                F.ID,
                F.TIPO,
                F.DESCRICAO,
                F.CAMPO_VISUALIZAR,
        
                C.CAMPO_ID,
                C.PAINEL_ID,
                C.REGISTRO_ID,
                C.JSON,
        
                FN_IS_EMPTY2(CAST(C.VALOR AS VARCHAR(7000)), '') AS VALOR,
        
                IIF(((F.TIPO = 4) OR (F.TIPO = 5) OR (F.TIPO = 9)), FN_IS_EMPTY2(C.VALOR, '0'), 0) AS CHAVE
            
            FROM TBCASO_REGISTRO_DETALHE C
            INNER JOIN TBCASO_PAINEL_CONF F ON F.ID = C.CAMPO_ID
            WHERE C.REGISTRO_ID = :ICASO_ID
            AND C.PAINEL_ID = :IPAINEL_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
        :OTIPO,
        :OCAMPO_ID,
        :ODESCRICAO,
        :OVALOR

    DO
    BEGIN

        IF (COALESCE(:OVALOR, '') = '') THEN
        BEGIN
            :OVALOR = NULL;
        END

        SUSPEND;

    END
END
^

SET TERM ; ^

