SET TERM ^ ;

CREATE OR ALTER procedure SPU_REGUA_COBRANCA_CASO (
    IPAINEL_ID integer not null,
    IDIAS integer = 0)
as
declare variable VEMPRESA_ID integer;
declare variable VVINCULO_CASO_ID integer;
declare variable VPAGAMENTO_FORMA_ID integer;
declare variable VQTD_DOCUMENTOS integer;
declare variable VTOTAL_VALOR numeric(15,4);
declare variable VNFS_PARC_ID varchar(6000);
declare variable VDOCUMENTOS varchar(6000);
declare variable VVINCULO_CASO_SITUACAO integer;
declare variable VFEED_ID integer;
declare variable VCASO_ID integer;
declare variable VSPLIT varchar(6000);
declare variable VMENSAGEM varchar(6000);
declare variable VASSUNTO varchar(6000);
declare variable PAINEL_ID integer;
declare variable MOTIVO_ID integer;
declare variable TIPO_ID integer;
declare variable ORIGEM_ID integer;
declare variable CASO_ID integer;
declare variable CASO_ID2 integer;
BEGIN

    FOR
        SELECT /*FIRST 5*/ DISTINCT
            X.EMPRESA_CODIGO AS EMPRESA_ID,
            X.CASO_ID,
            COALESCE((SELECT FIRST 1 CAST(COALESCE(S.OSPLIT, '0') AS INTEGER) FROM SPLIT2(LIST(DISTINCT X.PAGAMENTO_FORMA), ',') S), 0) AS PAGAMENTO_FORMA_ID,
        
            COUNT(X.NUMERO_NOTAFISCAL) AS QTD_DOCUMENTOS,
            CAST(SUM(X.VALOR_SALDO) AS NUMERIC(15,4)) AS TOTAL_VALOR,
        
            LIST(X.CONTROLE, ',') AS NFS_PARC_ID,
            LIST('ESTAB.: '||FN_LPAD(X.ESTABELECIMENTO_ID, 4, '0')||' | DOC.: '||FN_LPAD(X.NUMERO_NOTAFISCAL, 8, '0')||'/'||FN_LPAD(X.SERIE, 3, '0')||'-'||FN_LPAD(X.PARCELA, 3, '0')||' | DT.VENC.: '||FORMATDATE(X.DATA_VENCIMENTO)||' | VALOR: R$ '||FN_LPAD(FN_FORMAT_NUMBER2(X.VALOR_SALDO, 2), 10, ''), ';') AS DOCUMENTOS
        
        FROM (
            SELECT
                A.EMPRESA_CODIGO,
            
                A.ESTABELECIMENTO_CODIGO AS ESTABELECIMENTO_ID,
                A.NUMERO_NOTAFISCAL,
                A.SERIE,
                A.PARCELA,
                A.DATA_EMISSAO,
                A.DATA_VENCIMENTO,
                A.VALOR_SALDO,
                A.PAGAMENTO_FORMA,

                A.CONTROLE,

                COALESCE(A.CASO_ID, 0) AS CASO_ID
            
            FROM (
                SELECT
                    CAST(DATEADD(DAY, -CAST(:IDIAS AS INTEGER), CURRENT_TIMESTAMP) AS DATE) AS DATA_TOLERANCIA
        
                FROM RDB$DATABASE
            ) B, TBNFS_PARCELAMENTO A
            WHERE A.SITUACAO = 1
            AND A.DATA_VENCIMENTO < B.DATA_TOLERANCIA
            AND A.VALOR_SALDO > 0
            
            ORDER BY
                A.EMPRESA_CODIGO,
                A.DATA_VENCIMENTO
        ) X
        
        GROUP BY
            X.EMPRESA_CODIGO,
            X.CASO_ID

    INTO
        :VEMPRESA_ID,
        :VVINCULO_CASO_ID,
        :VPAGAMENTO_FORMA_ID,
        :VQTD_DOCUMENTOS,
        :VTOTAL_VALOR,
        :VNFS_PARC_ID,
        :VDOCUMENTOS

    DO
    BEGIN
        
        FOR
            SELECT
                D.PAINEL_ID,
                D.MOTIVO_ID,
                D.TIPO_ID,
                D.ORIGEM_ID,
    
                COALESCE((SELECT FIRST 1
                            E.REGISTRO_ID
                        FROM TBCASO_REGISTRO_DETALHE E, TBCASO_PAINEL_CONF E2
                        WHERE E.CAMPO_ID = E2.ID
                        AND E.PAINEL_ID = E2.PAINEL_ID
                        AND E.PAINEL_ID = D.PAINEL_ID
                        AND E2.PARAM_EXEC = 1
                        AND TRIM(E.VALOR) = :VEMPRESA_ID), 0) AS CASO_ID,
    
                COALESCE((SELECT FIRST 1
                            C.ID
                        FROM TBCASO_REGISTRO C
                        WHERE C.PAINEL_ID = D.PAINEL_ID
                        AND C.CASO_ORIGEM_ID = :VEMPRESA_ID
                        AND C.CASO_ORIGEM_TABELA = 'TBCLIENTE'), 0) AS CASO_ID2
    
            FROM TBCASO_PAINEL_PROCEDIMENTO D
            WHERE D.PAINEL_ID = :IPAINEL_ID
            AND D.OPERACAO = 0

        INTO
           :PAINEL_ID,
           :MOTIVO_ID,
           :TIPO_ID,
           :ORIGEM_ID,
           :CASO_ID,
           :CASO_ID2

        DO
        BEGIN

            IF ((COALESCE(:CASO_ID, 0) > 0) OR (COALESCE(:CASO_ID2, 0) > 0)) THEN
            BEGIN

                :VVINCULO_CASO_SITUACAO = COALESCE((SELECT FIRST 1 S.TIPO FROM TBCASO_REGISTRO C, TBCASO_STATUS S WHERE C.STATUS_ID = S.ID AND C.PAINEL_ID = S.PAINEL_ID AND C.ID = :VVINCULO_CASO_ID), 0);

                IF (
                    (COALESCE(:VVINCULO_CASO_ID, 0) = 0) OR
                    (COALESCE(:VVINCULO_CASO_ID, 0) > 0 AND COALESCE(:VVINCULO_CASO_ID, 0) <> COALESCE(:CASO_ID2, 0) AND COALESCE(:VVINCULO_CASO_ID, 0) <> COALESCE(:CASO_ID, 0)) OR
                    (COALESCE(:VVINCULO_CASO_ID, 0) > 0 AND COALESCE(:VVINCULO_CASO_SITUACAO, 0) > 2)
                ) THEN
                BEGIN

                    /* verificando o ID do caso */
                    :VCASO_ID = 0;
                    IF (COALESCE(:CASO_ID, 0) > 0) THEN
                    BEGIN
                        :VCASO_ID = :CASO_ID;
                    END ELSE
                    BEGIN
                        :VCASO_ID = :CASO_ID2;
                    END
    
    
                    :VMENSAGEM = '<p><strong>Parcelas do Cliente fora do Periodo de Tolerancia ('||:IDIAS||' dias):</strong> '||:VQTD_DOCUMENTOS||' Parcelas Vinculadas </p>'|| ASCII_CHAR(13) || ASCII_CHAR(10) ||'';
                    :VASSUNTO  = 'AGENDAMENTO: [CASO: '||FN_LPAD(:VCASO_ID, 8, '0')||'] - INFORMACOES DE COBRANCA';
    
                    FOR
                        SELECT
                            SP.OSPLIT
                        FROM SPLIT_V2(:VDOCUMENTOS, ';') SP
    
                    INTO
                        :VSPLIT
    
                    DO
                    BEGIN
    
                        :VMENSAGEM = :VMENSAGEM ||'<p> '|| :VSPLIT ||' </p>'|| ASCII_CHAR(13) || ASCII_CHAR(10) ||'';
    
                    END
    
    
                    /* Inclusão do FEED no caso */
                    :VFEED_ID = GEN_ID(GTBCASO_FEED, 1);
    
                    INSERT INTO TBCASO_FEED (ID, DE, PARA, EM_COPIA, EM_COPIA_OCULTA, MENSAGEM, ASSUNTO, PAINEL_ID, CASO_ID, FILES, SUBFEED, TIPO, USUARIO_ID, COMENTARIO, LISTA_FILE, WORKFLOW_ITEM_ID)
                        VALUES (:VFEED_ID, '', '', '', '', :VMENSAGEM, FN_ELIPSE2(:VASSUNTO, 300), :PAINEL_ID, :VCASO_ID, 1, 0, 2, 99999, 0, '', 0);


                    /* Atualizando vinculo das parcelas */
                    FOR
                        SELECT
                            SP.OSPLIT
                        FROM SPLIT_V2(:VNFS_PARC_ID, ',') SP

                    INTO
                        :VSPLIT

                    DO
                    BEGIN

                        UPDATE TBNFS_PARCELAMENTO
                            SET CASO_ID = :VCASO_ID
                            WHERE CONTROLE = :VSPLIT;

                    END
                END

            END ELSE
            BEGIN

                :VCASO_ID = 0;

                :VMENSAGEM = '';

                FOR
                    SELECT
                        SP.OSPLIT
                    FROM SPLIT_V2(:VDOCUMENTOS, ';') SP

                INTO
                    :VSPLIT

                DO
                BEGIN

                    :VMENSAGEM = :VMENSAGEM ||''|| :VSPLIT ||' '|| ASCII_CHAR(13) || ASCII_CHAR(10) ||'';

                END


                FOR
                    SELECT
                        SPC.OCASO_ID
                    FROM SPI_CASO(:PAINEL_ID, :MOTIVO_ID, :TIPO_ID, :ORIGEM_ID, 'AGENDA_AUT', :VEMPRESA_ID, CAST(:VTOTAL_VALOR AS NUMERIC(15,2)), FN_ELIPSE2(:VMENSAGEM, 6000), :VPAGAMENTO_FORMA_ID, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 'TBAGENDAMENTO') SPC
    
                INTO
                    :VCASO_ID
    
                DO
                BEGIN

                    /* Atualizando vinculo das parcelas */
                    FOR
                        SELECT
                            SP.OSPLIT
                        FROM SPLIT_V2(:VNFS_PARC_ID, ',') SP

                    INTO
                        :VSPLIT

                    DO
                    BEGIN

                        UPDATE TBNFS_PARCELAMENTO
                            SET CASO_ID = :VCASO_ID
                            WHERE CONTROLE = :VSPLIT;

                    END
    
                    /* Historico para geracao de novo caso */
                    EXECUTE PROCEDURE SPI_CASO_HISTORICO(:VCASO_ID, :PAINEL_ID, 'CASO REGISTRADO VIA AGENDAMENTO DE SISTEMA - '||:VQTD_DOCUMENTOS||' Parcelas Vinculadas');

                END
            END

        END
    END
END
^

SET TERM ; ^

COMMENT ON PARAMETER SPU_REGUA_COBRANCA_CASO.IDIAS IS
'Dias de tolerância';

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

SET TERM ^ ;

CREATE OR ALTER procedure SPC_CASO_DETALHE (
    IPAINEL_ID integer not null,
    ICASO_ID integer not null,
    ICAMPO_VISUALIZACAO varchar(100) not null)
returns (
    OCAMPO_ID integer,
    OTIPO integer,
    ODESCRICAO varchar(100),
    ODECIMAIS integer,
    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,
            A.DECIMAIS,
        
            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,
                F.STEP,
                IIF(F.STEP >= 1, 0, IIF(F.STEP >= 0.1, 1, IIF(F.STEP >= 0.01, 2, IIF(F.STEP >= 0.001, 3, IIF(F.STEP >= 0.0001, 4, 0))))) AS DECIMAIS,
        
                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,
        :ODECIMAIS,
        :OVALOR

    DO
    BEGIN

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

        SUSPEND;

    END
END
^

SET TERM ; ^

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

SET TERM ^ ;

CREATE OR ALTER function FN_JSON_ARRAY2 (
    JSON varchar(5000),
    CAMPO varchar(100))
returns varchar(500)
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);

            RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
        END ELSE
        BEGIN
            RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 2));
        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 ; ^

