SET TERM ^ ;

CREATE OR ALTER procedure SPLIT5 (
    ITEXTO blob sub_type 0 segment size 160,
    ISEPARADOR varchar(10))
returns (
    OSPLIT varchar(8000))
as
declare variable VTEXTO blob sub_type 0 segment size 160;
declare variable VPOSICAO integer;
declare variable VPOSICAO_INICIAL integer;
declare variable VSPLIT varchar(8000);
declare variable TAM_SEP integer;
BEGIN
    vtexto = itexto;
    vposicao_inicial = 1;
    vposicao = 1;
    
    TAM_SEP = char_length(ISEPARADOR);

    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 + TAM_SEP);

                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 + TAM_SEP;
            SUSPEND;
        END
    END
END
^

SET TERM ; ^


SET TERM ^ ;

CREATE OR ALTER procedure ARRAY_JSON_BLOB (
    VALOR blob sub_type 1 segment size 80)
returns (
    JSON blob sub_type 1 segment size 80)
as
begin

    for
        select * from split_blob2((replace(replace( substring(:VALOR from 2 for char_length(:VALOR) -1),'},{','}},{{'),'},{','|')),'|')
    into
      JSON
    do begin
       suspend;
    end


end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER function FN_ARRAY2 (
    VALOR blob sub_type 1 segment size 80,
    POSICAO integer,
    SEPARADOR varchar(20))
returns blob sub_type 1 segment size 80
AS
declare variable linha numeric(15,5);
declare variable ret BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;
begin

    FOR
        select row_number() over(),a.OSPLIT from split5(:valor,:separador) a
    INTO
        :linha,
        :ret
    DO
    BEGIN
        if(linha = posicao)then
        begin
          return ret;
        end
    END
end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPC_COMPARATIVO_ITENS (
    COMPARATIVO_ID integer)
returns (
    QUANTIDADE numeric(15,4),
    QUANTIDADE_ALOCADA numeric(15,4),
    SALDO numeric(15,4))
as
declare variable ITENS blob sub_type 0 segment size 80;
declare variable JSON varchar(1000);
declare variable RET_ITENS varchar(1000);
declare variable QTD_CHAR varchar(1000);
declare variable QTD_CHAR2 varchar(1000);
declare variable QTD_AUX numeric(15,4);
declare variable QTD_ALOCADA_AUX numeric(15,4);
declare variable SALDO_AUX numeric(15,4);
declare variable INDEX_ARRAY_QTD integer;
declare variable INDEX_ARRAY_QTD_ALOC integer;
declare variable INDEX_ARRAY_SALDO integer;
declare variable FOUND_QTD integer;
BEGIN

    FOR SELECT FIRST 1
        COALESCE(T.COMPARATIVO,'[]') AS COMPARATIVO
    FROM TBCOLETOR_COMPARATIVO T
    WHERE T.ID = :COMPARATIVO_ID
    INTO :ITENS
    DO BEGIN
            
        :QTD_AUX            = 0;
        :QTD_ALOCADA_AUX    = 0;
        :SALDO_AUX          = 0;

        FOR SELECT JSON
        FROM ARRAY_JSON_BLOB(:ITENS)
        INTO :JSON
        DO BEGIN
            :RET_ITENS          = :JSON;
            :QTD_AUX            = :QTD_AUX + CAST(FN_JSON_V4(JSON, 'QTD') AS NUMERIC(15,4));
            :QTD_ALOCADA_AUX    = :QTD_ALOCADA_AUX + CAST(FN_JSON_V4(JSON, 'ALOCADO') AS NUMERIC(15,4));
            :SALDO_AUX          = :SALDO_AUX + CAST(FN_JSON_V4(JSON, 'SALDO') AS NUMERIC(15,4));

        END

        :QUANTIDADE             = :QTD_AUX;
        :QUANTIDADE_ALOCADA     = :QTD_ALOCADA_AUX;
        :SALDO                  = :SALDO_AUX;


        SUSPEND;
    END

END
^

SET TERM ; ^

