CREATE OR ALTER PROCEDURE SPC_FORMULARIO_SATISF_GERAL (
    FORMULARIO_ID INTEGER)
RETURNS (
    PERC_SATISF NUMERIC(12,1),
    TOTAL_SATISF INTEGER,
    TOTAL_INSATISF INTEGER)
AS
DECLARE VARIABLE QTD_SATISF INTEGER;
DECLARE VARIABLE QTD_INSATISF INTEGER;
DECLARE VARIABLE PESO SMALLINT;
DECLARE VARIABLE META_SATISF INTEGER;
DECLARE VARIABLE NIVEL_SATISF SMALLINT;
BEGIN
    QTD_SATISF = 0;
    QTD_INSATISF = 0;
    PESO = 0;
    META_SATISF = 0;
    NIVEL_SATISF = 0;
    PERC_SATISF = 0;
    
    FOR
        SELECT
            IIF(X.RESPONDEU_PESO_COLAB IS NULL, 
                X.RESPONDEU_PESO_USUARIO, 
                X.RESPONDEU_PESO_COLAB
            ) RESPONDEU_PESO,
            
            X.NIVEL_SATISFACAO
            
        FROM (
            SELECT
                (SELECT FIRST 1 FD.PESO_RESPOSTA 
                    FROM TBFORMULARIO_DESTINATARIO FD 
                    WHERE 
                        FD.STATUSEXCLUSAO = '0'
                    AND FD.FORMULARIO_ID = FR.FORMULARIO_ID 
                    AND FD.USUARIO_ID = FR.USUARIO_ID   
                ) RESPONDEU_PESO_USUARIO,
                
                (SELECT FIRST 1 FD.PESO_RESPOSTA 
                    FROM TBFORMULARIO_DESTINATARIO FD 
                    WHERE
                        FD.STATUSEXCLUSAO = '0'
                    AND FD.FORMULARIO_ID = FR.FORMULARIO_ID 
                    AND FD.CCUSTO = FR.CCUSTO
                ) RESPONDEU_PESO_COLAB,

                (SELECT FIRST 1 FA.FORMULARIO_ALTERN_NIVEL_ID
                    FROM TBFORMULARIO_ALTERNATIVA FA
                    WHERE
                        FA.STATUSEXCLUSAO = '0'
                    AND FA.FORMULARIO_ID = FR.FORMULARIO_ID
                    AND FA.ID = FR.ALTERNATIVA_ESCOLHIDA_ID
                ) NIVEL_SATISFACAO

            FROM
                TBFORMULARIO_RESPOSTA FR

            WHERE
                FR.STATUSEXCLUSAO = '0'
            AND FR.USUARIO_ID IS NOT NULL
            AND FR.FORMULARIO_ID = :FORMULARIO_ID
        ) X    
            
    INTO
        :PESO,
        :NIVEL_SATISF
    
    DO BEGIN
     
        -- SATISFACAO
        IF (NIVEL_SATISF = 1) THEN
            QTD_SATISF = QTD_SATISF + COALESCE(PESO, 1);
            
        -- INSATISFACAO
        ELSE IF (NIVEL_SATISF = 2) THEN
            QTD_INSATISF = QTD_INSATISF + COALESCE(PESO, 1);
    END

    META_SATISF = QTD_SATISF + QTD_INSATISF;
    if(META_SATISF = 0)then
    begin
        META_SATISF = 1;
    end

    TOTAL_SATISF    = QTD_SATISF;
    TOTAL_INSATISF  = QTD_INSATISF;
    PERC_SATISF     = ((QTD_SATISF * 100.0) /  META_SATISF);
                    
    SUSPEND;
END;

COMMENT ON PROCEDURE SPC_FORMULARIO_SATISF_GERAL IS 'Calcular o percentual de satisfacao geral das pesquisas.';