  
-- Storeprocedure {getSumClasseComp(character varying, date, date)} Versao {003} Esquema {padrao} Descricao {Retornar total da classe}
-- ------------------------------------------------------------
 
CREATE OR REPLACE FUNCTION padrao.getsumclasseComp(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);
 v_total_rp_cl NUMERIC(16,4);
 v_total_cc_movcl NUMERIC(16,4);
 r_parameters RECORD; -- Parametros
 BEGIN
    v_retorna = 0;
    SELECT SUM(CASE WHEN movcl.natureza = 'Credito' THEN valor ELSE  valor * -1 END) AS total
      FROM financ_rp_cl AS movcl
     INNER JOIN financ_classificacao_g ON movcl.financ_classificacao_g_id = financ_classificacao_g.id 
      LEFT JOIN financ_rp             ON movcl.financ_rp_id              = financ_rp.id
      INTO v_total_rp_cl
     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;
       
 -- Posiciona parameters
    SELECT *
    INTO r_parameters 
    FROM fat_parameters 
    LIMIT 1 ; 

    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_total_cc_movcl
     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 (   coalesce(financ_cc_movto.contas_receber_pagar, false) = false
            OR movcl.financ_classificacao_g_id = r_parameters.financjurosrecebidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdescconcedidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdevrecebidas 
            OR movcl.financ_classificacao_g_id = r_parameters.financdespacessoriasrec 
            OR movcl.financ_classificacao_g_id = r_parameters.financjurospagos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdescobtidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdevenviadas 
            OR movcl.financ_classificacao_g_id = r_parameters.financdespacessoriaspag  
            )
       AND financ_cc.ccapuracao;          
       
   RETURN coalesce(v_total_rp_cl,0) + coalesce(v_total_cc_movcl,0);
 END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
 
-- ------------------------------------------------------------  
-- Storeprocedure {getSumClasseComp(character varying, date, date, INT4, INT4)} Versao {007} Esquema {padrao} Descricao {Retornar total da classe}
-- ------------------------------------------------------------
 
CREATE OR REPLACE FUNCTION padrao.getsumclasseComp(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);
 v_total_rp_cl NUMERIC(16,4);
 v_total_cc_movcl NUMERIC(16,4);
 r_parameters RECORD; -- Parametros
 BEGIN
    v_retorna = 0;
    v_total_rp_cl = 0;
    v_total_cc_movcl = 0; 
    SELECT SUM(CASE WHEN movcl.natureza = 'Credito' THEN valor ELSE  valor * -1 END) AS total
      FROM financ_rp_cl AS movcl
     INNER JOIN financ_classificacao_g ON movcl.financ_classificacao_g_id = financ_classificacao_g.id 
      LEFT JOIN financ_rp              ON movcl.financ_rp_id              = financ_rp.id
      INTO v_total_rp_cl
     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_rp.cad_filial_id  >= p_filial_i
       AND financ_rp.cad_filial_id  <= p_filial_f;
 
 -- Posiciona parameters
    SELECT *
    INTO r_parameters 
    FROM fat_parameters 
    LIMIT 1 ; 

    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_total_cc_movcl
     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 (   coalesce(financ_cc_movto.contas_receber_pagar, false) = false
            OR movcl.financ_classificacao_g_id = r_parameters.financjurosrecebidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdescconcedidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdevrecebidas 
            OR movcl.financ_classificacao_g_id = r_parameters.financdespacessoriasrec 
            OR movcl.financ_classificacao_g_id = r_parameters.financjurospagos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdescobtidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdevenviadas 
            OR movcl.financ_classificacao_g_id = r_parameters.financdespacessoriaspag  
            )
       AND financ_cc.ccapuracao;          
       
   RETURN coalesce(v_total_rp_cl,0) + coalesce(v_total_cc_movcl,0);
 END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
  
-- ------------------------------------------------------------  
-- Storeprocedure {getSumClasseComp(INT4, date, date, INT4, INT4)} Versao {020} Esquema {padrao} Descricao {Retornar total da classe}
-- ------------------------------------------------------------
 
CREATE OR REPLACE FUNCTION padrao.getsumclasseComp(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);
 v_total_rp_cl NUMERIC(16,4);
 v_total_cc_movcl NUMERIC(16,4);
 r_parameters RECORD; -- Parametros

 BEGIN
    v_retorna = 0;
    v_total_rp_cl = 0;
    v_total_cc_movcl = 0; 
    SELECT SUM(CASE WHEN movcl.natureza = 'Credito' THEN valor ELSE  valor * -1 END) AS total
      FROM financ_rp_cl AS movcl
      LEFT JOIN financ_rp             ON movcl.financ_rp_id              = financ_rp.id
      INTO v_total_rp_cl
     WHERE financ_classificacao_g_id = p_classe 
       AND movcl.datalcto  >= p_data_i
       AND movcl.datalcto  <= p_data_f
       AND financ_rp.cad_filial_id  >= p_filial_i
       AND financ_rp.cad_filial_id  <= p_filial_f;
    
 -- Posiciona parameters
    SELECT *
    INTO r_parameters 
    FROM fat_parameters 
    LIMIT 1 ; 

    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_total_cc_movcl
      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 (   coalesce(financ_cc_movto.contas_receber_pagar, false) = false
            OR movcl.financ_classificacao_g_id = r_parameters.financjurosrecebidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdescconcedidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdevrecebidas 
            OR movcl.financ_classificacao_g_id = r_parameters.financdespacessoriasrec 
            OR movcl.financ_classificacao_g_id = r_parameters.financjurospagos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdescobtidos 
            OR movcl.financ_classificacao_g_id = r_parameters.financdevenviadas 
            OR movcl.financ_classificacao_g_id = r_parameters.financdespacessoriaspag  
            )
       AND financ_cc.ccapuracao;   
       
       
    RETURN coalesce(v_total_rp_cl,0) + coalesce(v_total_cc_movcl,0);
 END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;