protected DataTable geraRelatorioIntervalo(DateTime dtInicio, DateTime dtFim, string forma)
{
DataTable dt = new DataTable();
try
{
string inicio = Convert.ToDateTime(dtInicio).ToShortDateString();
string fim = Convert.ToDateTime(dtFim).ToShortDateString();
if (forma == "todos")
{
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter("SELECT COD_EMPRS,NUM_RGTRO_EMPRG,NOM_EMPRG,COD_EMAIL_EMPRG,DATA_CADASTRO,VALOR_INGRESSO,QTDE_INGRESSO,QTDE_PARCELAS,FORM_PAGAMENTO,DATA_PAGAMENTO FROM OWN_FUNCESP.CRM_TBL_FESTAAPOSENTADOS WHERE DATA_CANCELAMENTO IS NULL AND to_date(DATA_CADASTRO,'dd/MM/yyyy') BETWEEN to_date('" + inicio + "','dd/MM/yyyy') AND to_date('" + fim + "','dd/MM/yyyy')");
adpt.Fill(dt);
adpt.Dispose();
}
else
{
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter("SELECT COD_EMPRS,NUM_RGTRO_EMPRG,NOM_EMPRG,COD_EMAIL_EMPRG,DATA_CADASTRO,VALOR_INGRESSO,QTDE_INGRESSO,QTDE_PARCELAS,FORM_PAGAMENTO,DATA_PAGAMENTO FROM OWN_FUNCESP.CRM_TBL_FESTAAPOSENTADOS WHERE DATA_CANCELAMENTO IS NULL AND FORM_PAGAMENTO = '" + forma + "' AND to_date(DATA_CADASTRO,'dd/MM/yyyy') BETWEEN to_date('" + inicio + "','dd/MM/yyyy') AND to_date('" + fim + "','dd/MM/yyyy')");
adpt.Fill(dt);
adpt.Dispose();
}
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
return(dt);
}
public List <String> Obter_lista_conversao_nomes(Intranet.Aplicacao.BLL.Filtro_Pesquisa filtro)
{
// Variavel com o array DE/PARA
List <String> Lista_conversao_nomes = new List <String>();
//Consultar tabela de Representante Oracle para fazer o DE/PARA de nome
String strString = "";
strString = strString + " select distinct A.COD_EMPRS, A.NUM_RGTRO_EMPRG, A.NUM_IDNTF_RPTANT, A.NOME_ATU, A.NOME_ANT, A.DATA_ATU ";
strString = strString + " from opportunity.TB_NOME_ALTERADO A ";
strString = strString + " where A.COD_EMPRS = '" + filtro.CodigoEmpresa + "' ";
strString = strString + " and A.NUM_RGTRO_EMPRG = '" + filtro.Registro + "' ";
strString = strString + " and A.NUM_IDNTF_RPTANT in ";
strString = strString + " (select A.NUM_IDNTF_RPTANT ";
strString = strString + " from opportunity.TB_NOME_ALTERADO A ";
strString = strString + " where A.COD_EMPRS = '" + filtro.CodigoEmpresa + "' ";
strString = strString + " and A.NUM_RGTRO_EMPRG = '" + filtro.Registro + "' ";
//strString = strString + " and A.NOME_ATU = '" + filtro.ParticipanteNome + "' ) ";
strString = strString + " and A.NOME_ATU = '" + filtro.ParticipanteNome.Replace("'", "") + "' ) ";
//SqlDataSource sdsDinamico = new SqlDataSource("System.Data.OracleClient", Consulta_GED_CRM.Properties.Settings.Default.Conexao_Oracle, strString);
//System.Data.DataView dv = (System.Data.DataView)sdsDinamico.Select(DataSourceSelectArguments.Empty);
//Conexao objConexao = new Conexao();
ConexaoOracle objConexao = new ConexaoOracle();
DataTable dt = new DataTable();
try
{
objConexao.ExecutarQueryAdapter(strString);
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter(strString);
adpt.Fill(dt);
adpt.Dispose();
foreach (DataRow datarow in dt.Rows)
{
Lista_conversao_nomes.Add(datarow[0].ToString());
}
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(Lista_conversao_nomes);
}
public DataTable selectRetornaProtocolo(string numChamado)
{
DataTable dt = new DataTable();
ConexaoOracle objConexao = new ConexaoOracle();
try
{
StringBuilder querysql = new StringBuilder();
querysql.Append(" Select D.id_cham_cd_chamado, D.cham_ds_protocolo FROM OWN_PLUSOFTCRM.CS_NGTB_CHAMADO_CHAM D ");
querysql.Append(" WHERE ");
querysql.Append(" D.id_cham_cd_chamado =" + numChamado.ToString());
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter(querysql.ToString());
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(dt);
}
public DataTable selectCancelarPlanoRepress(string emp, string num_Matricula, string repres)
{
DataTable dt = new DataTable();
ConexaoOracle objConexao = new ConexaoOracle();
try
{
StringBuilder querysql = new StringBuilder();
querysql.Append(" Select NOM_PARTICIP, DES_PLANO, NUM_SUB_MATRIC, CASE WHEN DES_PLANO LIKE '%DIGNA%' THEN 1 ELSE 0 END PLANO_DIGNA ");
querysql.Append(" FROM OWN_FUNCESP.ATE_VW_CANCELPLANOREPRES ");
querysql.Append(" WHERE ");
querysql.Append(" cod_emprs = " + emp.ToString());
querysql.Append(" AND num_matricula = " + num_Matricula.ToString());
querysql.Append(" AND NUM_IDNTF_RPTANT = " + repres.ToString());
querysql.Append(" ORDER BY PLANO_DIGNA, DES_PLANO, NOM_PARTICIP ");
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter(querysql.ToString());
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(dt);
}
public Int32 GetNumRepr(short emp, int matricula, Int32 numRep)
{
ConexaoOracle obj = new ConexaoOracle();
DataTable dt = new DataTable();
Int32 numRepr = 0;
try
{
OracleDataAdapter adpt = obj.ExecutarQueryAdapter("select distinct r.num_idntf_rptant from representante_fss r, repres_depend_fss d where r.num_idntf_rptant = d.num_idntf_rptant and d.cod_emprs = " + emp + " and d.num_rgtro_emprg = " + matricula + " and d.num_idntf_rptant = " + numRep);
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas com a conexão com o banco de dados, favor entrar em contato com Administrador do sistema " + ex.Message);
}
if (dt.Rows.Count > 0)
{
numRepr = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return(numRepr);
}
public Int32 GetNumDepend(short emp, int matricula, Int64 cpf)
{
ConexaoOracle obj = new ConexaoOracle();
DataTable dt = new DataTable();
Int32 numDepend = 0;
try
{
OracleDataAdapter adpt = obj.ExecutarQueryAdapter("select DEP.NUM_IDNTF_DPDTE from emprg_dpdte dep, dependente dd where dep.num_idntf_dpdte = dd.num_idntf_dpdte and dep.cod_emprs = " + emp + " and dep.num_rgtro_emprg = " + matricula + " and dd.num_cpf_dpdte = " + cpf);
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas com a conexão com o banco de dados, favor entrar em contato com Administrador do sistema " + ex.Message);
}
if (dt.Rows.Count > 0)
{
numDepend = Convert.ToInt32(dt.Rows[0][0].ToString());
}
return(numDepend);
}
public DataTable selectUnimedPlanoSaudeRepres(string emp, string num_Matricula, string nrepr)
{
DataTable dt = new DataTable();
ConexaoOracle objConexao = new ConexaoOracle();
try
{
StringBuilder querysql = new StringBuilder();
querysql.Append(" Select * FROM OWN_FUNCESP.ATE_VW_UnimedPlanoSaudeRepres ");
querysql.Append(" WHERE ");
querysql.Append(" cod_emprs = " + emp.ToString());
querysql.Append(" AND num_matricula = " + num_Matricula.ToString());
querysql.Append(" AND NUM_IDNTF_RPTANT = " + nrepr.ToString());
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter(querysql.ToString());
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(dt);
}
protected DataTable selecionaUsuarioMatricula(string matricula)
{
DataTable dt = new DataTable();
try
{
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter("SELECT * FROM OWN_FUNCESP.FUN_TBL_ACESSO_PAGFOLHA WHERE NUM_RGTRO_EMPRG = '" + matricula + "'");
adpt.Fill(dt);
adpt.Dispose();
}catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
return(dt);
}
public DataTable RetornarPgtos(string nempr, string nreg, string repres, string mesIni, string mesFim, string anoIni, string anoFim)
{
DataTable dt = new DataTable();
var periodoIni = string.Concat("'", mesIni, "/", anoIni, "'");
var periodoFim = string.Concat("'", mesFim, "/", anoFim, "'");
if (periodoIni.Length != 9)
{
return(dt);
}
if (periodoFim.Length != 9)
{
return(dt);
}
//Consultar tabela de Aviso de Pagamento do Oracle para pegar a ultima data gerada
String queryString = "";
queryString = queryString + "select * from ( ";
queryString = queryString + "select distinct avp.ano_referencia, Upper(avp.mes_referencia) as mes_referencia, nvl(to_char(avp.dat_pagto_pcpgbf, 'rrrrmm'), 0)";
queryString = queryString + " || case when to_char(avp.dat_pagto_pcpgbf, 'dd') < 25 then 'S' ELSE 'N' END as referencia, ";
//queryString = queryString + " Upper(avp.mes_referencia) || case when to_char(avp.dat_pagto_pcpgbf, 'dd') < 25 then ";
queryString = queryString + " Upper(avp.mes_referencia) || case when to_char(avp.dat_pagto_pcpgbf, 'dd') < 23 then ";
queryString = queryString + " case when to_char(avp.dat_pagto_pcpgbf, 'mm') = 11 then ' - ADIANTAMENTO ABONO' else ' - ABONO' end ";
queryString = queryString + " end as mesAnoref ";
queryString = queryString + " from opportunity.tb_avisopagto avp ";
queryString = queryString + " where avp.cod_emprs = " + nempr + " "; // --Parâmetro de Empresa
queryString = queryString + " and avp.num_rgtro_emprg = " + nreg + " "; //--Parâmetro de Registro
queryString = queryString + " and avp.num_idntf_rptant = " + repres + " "; //--Parâmetro de Representante (Pensão Previdenciária)
queryString = queryString + " and avp.num_idntf_dpdte = 0"; //--Parâmetro de Representante (Pensão Alimentícia)"
queryString = queryString + " AND to_date(to_char(avp.dat_pagto_pcpgbf, 'MM/rrrr'),'MM/rrrr') BETWEEN to_date(" + periodoIni + ", 'MM/rrrr') ";
queryString = queryString + "AND to_date(" + periodoFim + ", 'MM/rrrr' )";
queryString = queryString + " and to_char(avp.dat_pagto_pcpgbf,'rrrrmm') > 201501";
//queryString = queryString + " order by referencia";
queryString = queryString + " ) order by ano_referencia desc, referencia asc";
ConexaoOracle objConexao = new ConexaoOracle();
try
{
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter(queryString);
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(dt);
}
public DataTable consultaGridMatr(int codMatr)
{
ConexaoOracle obj = new ConexaoOracle();
DataTable dt = new DataTable();
try
{
OracleDataAdapter adpt = obj.ExecutarQueryAdapter("select s.cod_emprs, s.num_rgtro_emprg, s.num_idntf_rptant, s.nom_emprg_repres, s.dt_inclusao,s.dt_exclusao, s.nom_solic_inclusao,s.flag_judicial,s.flag_insucesso from opportunity.tb_nao_gerar_cobranca_saude s where s.num_rgtro_emprg= " + codMatr + "");
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas com a conexão com o banco de dados, favor entrar em contato com Administrador do sistema " + ex.Message);
}
return(dt);
}
protected DataTable GeraListBoletoEmail()
{
DataTable dt = new DataTable();
ConexaoOracle objCon = new ConexaoOracle();
try
{
OracleDataAdapter adpt = objCon.ExecutarQueryAdapter("select * from own_portal.vw_boleto_saude_email");
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas com a consulta referente aos boletos da saúde, favor entrar em contato com o administrador do sistema //n" + ex.Message);
}
return(dt);
}
public DataTable geraGridGeral()
{
ConexaoOracle obj = new ConexaoOracle();
DataTable dt = new DataTable();
try
{
OracleDataAdapter adpt = obj.ExecutarQueryAdapter("SELECT n.cod_emprs, n.num_rgtro_emprg, n.num_idntf_rptant, n.nom_emprg_repres, n.dt_inclusao,n.dt_exclusao ,n.nom_solic_inclusao,n.flag_judicial,n.flag_insucesso from opportunity.tb_nao_gerar_cobranca_saude n order by n.dt_inclusao");
adpt.Fill(dt);
adpt.Dispose();
} //where n.dt_exclusao is null
catch (Exception ex)
{
throw new Exception("Problemas com a conexão com o banco de dados, favor entrar em contato com Administrador do sistema " + ex.Message);
}
return(dt);
}
public DataTable GetListaDataDT_ATU_CONTAS()
{
ConexaoOracle obj = new ConexaoOracle();
DataTable dt = new DataTable();
try
{
OracleDataAdapter adpt = obj.ExecutarQueryAdapter("select distinct h.nome_arq from own_funcesp.fun_tbl_atu_cc_hist h order by h.nome_arq");
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas com a conexão com o banco de dados, favor entrar em contato com Administrador do sistema " + ex.Message);
}
return(dt);
}
public List <FIN_TBL_RES_ABERT_FINANCEIRA_view3> GetDataConsolidado(int?pMes, int?pAno)
{
ConexaoOracle objConexao = new ConexaoOracle();
FIN_TBL_RES_ABERT_FINANCEIRA_view3 reg = new FIN_TBL_RES_ABERT_FINANCEIRA_view3();
List <FIN_TBL_RES_ABERT_FINANCEIRA_view3> result = new List <FIN_TBL_RES_ABERT_FINANCEIRA_view3>();
DataTable dt = new DataTable();
try
{
StringBuilder querysql = new StringBuilder();
querysql.Append(" SELECT emp.COD_EMPRS,emp.NOM_ABRVO_EMPRS,abert.NATUREZA,abert.VALOR_PART,abert.VALOR_CRED_BRUTO,abert.IMPOSTO,abert.VALOR_CRED_LIQ ");
querysql.Append(" FROM FIN_TBL_RES_ABERT_FINANCEIRA abert inner join EMPRESA emp on abert.COD_EMPRS = emp.COD_EMPRS ");
querysql.Append(" WHERE ");
querysql.Append(" MES_REF = " + pMes + " and ANO_REF = " + pAno);
OracleDataAdapter adpt = objConexao.ExecutarQueryAdapter(querysql.ToString());
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
for (int i = 0; i < dt.Rows.Count; i++)
{
reg = new FIN_TBL_RES_ABERT_FINANCEIRA_view3();
reg.CODIGO = dt.Rows[i]["COD_EMPRS"].ToString();
reg.EMPRESA = dt.Rows[i]["NOM_ABRVO_EMPRS"].ToString();
reg.NATUREZA = dt.Rows[i]["NATUREZA"].ToString();
reg.VALOR = Convert.ToDouble(dt.Rows[i]["VALOR_PART"].ToString());
reg.BRUTO = Convert.ToDouble(dt.Rows[i]["VALOR_CRED_BRUTO"].ToString());
reg.IMPOSTO = Convert.ToDouble(dt.Rows[i]["IMPOSTO"].ToString());
reg.LIQUIDO = Convert.ToDouble(dt.Rows[i]["VALOR_CRED_LIQ"].ToString());
result.Add(reg);
}
}
return(result);
}
public string GetNomeRepresentanteExt(int nRep, Int64 cpf)
{
ConexaoOracle obj = new ConexaoOracle();
DataTable dt = new DataTable();
string numRep = "";
try
{
OracleDataAdapter adpt = obj.ExecutarQueryAdapter("select r.num_idntf_rptant from representante_fss r, repres_depend_fss d where r.num_idntf_rptant = d.num_idntf_rptant and d.num_idntf_rptant = " + nRep + " and r.num_cpf_rptant = " + cpf);
adpt.Fill(dt);
adpt.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas com a conexão com o banco de dados, favor entrar em contato com Administrador do sistema " + ex.Message);
}
if (dt.Rows.Count > 0)
{
numRep = dt.Rows[0][0].ToString();
}
return(numRep);
}