
-- Storeprocedure {getSumClasse(character varying, date, date)} Versao {005} Esquema {padrao} Descricao {Retornar total da classe}
-- ------------------------------------------------------------

CREATE OR REPLACE FUNCTION padrao.getsumclasse(character varying, date, date)
  RETURNS numeric AS
$BODY$
 DECLARE
 p_classe    alias for $1;
 p_data_i    alias for $2;
 p_data_f    alias for $3;
 v_retorna   NUMERIC(16,4);
 BEGIN
    v_retorna = 0;
    SELECT SUM(CASE WHEN movcl.natureza = 'Credito' THEN valor ELSE  valor * -1 END) AS total
      FROM financ_cc_movcl AS movcl
     INNER JOIN financ_classificacao_g ON movcl.financ_classificacao_g_id = financ_classificacao_g.id 
      LEFT JOIN financ_cc              ON movcl.financ_cc_id              = financ_cc.id
      INTO v_retorna
     WHERE SUBSTR(financ_classificacao_g.classificacao_g, 1 ,LENGTH(p_classe)) = p_classe 
       AND movcl.datalcto  >= p_data_i
       AND movcl.datalcto  <= p_data_f
       AND financ_cc.ccapuracao;
    RETURN  coalesce(v_retorna,0);
 END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
 
-- ------------------------------------------------------------  
-- Storeprocedure {getSumClasse(character varying, date, date, INT4, INT4)} Versao {009} Esquema {padrao} Descricao {Retornar total da classe}
-- ------------------------------------------------------------

CREATE OR REPLACE FUNCTION padrao.getsumclasse(character varying, date, date, INT4, INT4)
  RETURNS numeric AS
$BODY$
 DECLARE
 p_classe    alias for $1;
 p_data_i    alias for $2;
 p_data_f    alias for $3;
 p_filial_i  alias for $4;
 p_filial_f  alias for $5;
 v_retorna   NUMERIC(16,4);
 BEGIN
    v_retorna = 0;
    SELECT SUM(CASE WHEN movcl.natureza = 'Credito' THEN movcl.valor ELSE  movcl.valor * -1 END) AS total
      FROM financ_cc_movcl AS movcl
     INNER JOIN financ_classificacao_g ON movcl.financ_classificacao_g_id = financ_classificacao_g.id 
      LEFT JOIN financ_cc_movto        ON movcl.financ_cc_movto_controle  = financ_cc_movto.controle
      LEFT JOIN financ_cc              ON movcl.financ_cc_id              = financ_cc.id
      INTO v_retorna
     WHERE SUBSTR(financ_classificacao_g.classificacao_g, 1 ,LENGTH(p_classe)) = p_classe 
       AND movcl.datalcto  >= p_data_i
       AND movcl.datalcto  <= p_data_f
       AND financ_cc_movto.cad_filial_id  >= p_filial_i
       AND financ_cc_movto.cad_filial_id  <= p_filial_f
       AND financ_cc.ccapuracao;
    RETURN  coalesce(v_retorna,0);
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 -- ------------------------------------------------------------  
-- Storeprocedure {getSumClasse(INT4, date, date, INT4, INT4)} Versao {013} Esquema {padrao} Descricao {Retornar total da classe}
-- ------------------------------------------------------------

CREATE OR REPLACE FUNCTION padrao.getsumclasse(INT4, date, date, INT4, INT4)
  RETURNS numeric AS
$BODY$
 DECLARE
 p_classe    alias for $1;
 p_data_i    alias for $2;
 p_data_f    alias for $3;
 p_filial_i  alias for $4;
 p_filial_f  alias for $5;
 v_retorna   NUMERIC(16,4);
 BEGIN
    v_retorna = 0;
    SELECT SUM(CASE WHEN movcl.natureza = 'Credito' THEN movcl.valor ELSE  movcl.valor * -1 END) AS total
      FROM financ_cc_movcl AS movcl
      LEFT JOIN financ_cc              ON movcl.financ_cc_id              = financ_cc.id
      LEFT JOIN financ_cc_movto        ON movcl.financ_cc_movto_controle  = financ_cc_movto.controle
      INTO v_retorna
     WHERE financ_classificacao_g_id = p_classe 
       AND movcl.datalcto  >= p_data_i
       AND movcl.datalcto  <= p_data_f
       AND financ_cc_movto.cad_filial_id  >= p_filial_i
       AND financ_cc_movto.cad_filial_id  <= p_filial_f
       AND financ_cc.ccapuracao;
    RETURN  coalesce(v_retorna,0);
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;
 
 