/// <summary>
/// Mapeio os dados do detalhe do arquivo cri de identificação
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private static Identificacao.ACSOIDTSC_R.IdentificacaoSimplifRetornoEN MapeiaDetalheIdentificacao(SqlDataReader dr)
{
var idfSimpRetEN = new Identificacao.ACSOIDTSC_R.IdentificacaoSimplifRetornoEN()
{
IdArquivo = (int)dr["IdArquivo"],
TpIdentificacao = (EnumRetornoBase.TipoIdentificacao)((byte)(dr["TpIdentificacao"])),
Identificacao = dr["Identificacao"].ToString(),
Cpf = dr["Cpf"].ToString(),
StatusCart = (upSight.CartaoCorp.EnumRetornoBase.StatusCartao)((byte)(dr["StatusCart"])),
Chave = dr["Chave"].ToString(),
Retorno = BDGeral.ObtemValorBD(dr["Retorno"]),
DtRetorno = Convert.ToDateTime(dr["DtRetorno"])
};
return(idfSimpRetEN);
}
/// <summary>
/// Mapeio os dados do detalhe do arquivo cri de carga
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private static Carga.ACSOPRGCR_R.CargaRetornoDetalheEN MapeiaDetalheCarga(SqlDataReader dr)
{
var crgRetDetEN = new Carga.ACSOPRGCR_R.CargaRetornoDetalheEN()
{
IdArquivo = (int)dr["IdArquivo"],
TpIdentificacao = (EnumRetornoBase.TipoIdentificacao)((byte)(dr["TpIdentificacao"])),
Identificacao = dr["Identificacao"].ToString(),
StatusCart = (EnumRetornoBase.StatusCartao)dr["StatusCart"],
Valor = (decimal)dr["Valor"],
Chave = dr["Chave"].ToString(),
Retorno = BDGeral.ObtemValorBD(dr["Retorno"]),
DtRetorno = Convert.ToDateTime(dr["DtRetorno"])
};
return(crgRetDetEN);
}
/// <summary>
/// Insere os detalhes da identificação
/// </summary>
/// <param name="acsIdtretDet"></param>
public static void Insere(this ACSOIDTSC_RDetalheEN acsIdtretDet)
{
using (SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["Global"].ConnectionString))
{
try
{
string query = " INSERT crpACSOIDTSC_RDetalhe " +
" (idArquivo, TpRegistro, TpPanProxy, PanProxy, Cpf, DataProc, StatusProc, StatusCart, Descricao, IdRegistro, NumLinha) " +
" SELECT @idArquivo, @TpRegistro, @TpPanProxy, @PanProxy, @Cpf, @DataProc, @StatusProc, @StatusCart, @Descricao, @IdRegistro, @NumLinha ";
using (SqlCommand cmd = new SqlCommand(query, cnx))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("IdArquivo", SqlDbType.Int).Value = acsIdtretDet.IdArquivo;
cmd.Parameters.Add("TpRegistro", SqlDbType.Char, 1).Value = ACSOIDTSC_RDetalheEN.TpRegistro;
cmd.Parameters.Add("TpPanProxy", SqlDbType.TinyInt).Value = Convert.ToByte(acsIdtretDet.TpIdentificacao);
cmd.Parameters.Add("PanProxy", SqlDbType.VarChar, 32).Value = acsIdtretDet.Identificacao;
cmd.Parameters.Add("Cpf", SqlDbType.VarChar, 11).Value = acsIdtretDet.Cpf;
cmd.Parameters.Add("DataProc", SqlDbType.DateTime2).Value = acsIdtretDet.DataProc;
cmd.Parameters.Add("StatusProc", SqlDbType.Int).Value = Convert.ToInt32(acsIdtretDet.StatusProc);
cmd.Parameters.Add("StatusCart", SqlDbType.TinyInt).Value = BDGeral.BDObtemValor <short>(Convert.ToInt16(acsIdtretDet.StatusCart));
cmd.Parameters.Add("Descricao", SqlDbType.VarChar, 50).Value = BDGeral.BDObtemValor(acsIdtretDet.Retorno);
cmd.Parameters.Add("IdRegistro", SqlDbType.VarChar, 10).Value = BDGeral.BDObtemValor(acsIdtretDet.IdRegistro);
cmd.Parameters.Add("NumLinha", SqlDbType.Int).Value = acsIdtretDet.NumLinha;
cnx.Open();
cmd.ExecuteNonQuery();
}
}
catch (SqlException sqlExc)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.CrtCorp.ACSOIDTSC_R.DetBD", sqlExc });
}
throw;
}
catch (Exception ex)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.CrtCorp.ACSOIDTSC_R.Det", ex });
}
throw;
}
}
}
public static void Insere(this ACSOPRGCRDetalheEN acsCrgDet)
{
using (SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["Global"].ConnectionString))
{
try
{
string query = "[crpInsereCargaDetalhe]";
using (SqlCommand cmd = new SqlCommand(query, cnx))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("IdArquivo", SqlDbType.Int).Value = acsCrgDet.IdArquivo;
cmd.Parameters.Add("TpRegistro", SqlDbType.Char, 1).Value = ACSOPRGCRDetalheEN.TpRegistro;
cmd.Parameters.Add("CodPrgCrg", SqlDbType.VarChar, 10).Value = acsCrgDet.CodPrgCrg;
cmd.Parameters.Add("TpPanProxy", SqlDbType.TinyInt).Value = (byte)acsCrgDet.TpPanProxy;
cmd.Parameters.Add("PanProxy", SqlDbType.VarChar, 32).Value = acsCrgDet.PanProxy;
cmd.Parameters.Add("Valor", SqlDbType.Money).Value = acsCrgDet.Valor;
cmd.Parameters.Add("IdRegistro", SqlDbType.VarChar, 10).Value = BDGeral.BDObtemValor(acsCrgDet.IdRegistro);
cmd.Parameters.Add("NumLinha", SqlDbType.Int).Value = acsCrgDet.NumLinha;
cnx.Open();
cmd.ExecuteNonQuery();
}
}
catch (SqlException sqlExc)
{
if (BDGeral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.CrtCorp.ACSOPRGCR.DetBD", sqlExc });
}
throw;
}
catch (Exception exp)
{
if (BDGeral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.CrtCorp.ACSOPRGCR.Det", exp });
}
throw;
}
}
}
/// <summary>
/// Insere os dados em crtACSOIDTSCDetalhe
/// </summary>
/// <param name="acsIdstDet"></param>
public static void Insere(this crtACSOIDTSCDetalheEN acsIdstDet)
{
using (SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["Global"].ConnectionString))
{
try
{
string query = " INSERT [crtACSOIDTSCDetalhe] " +
" (IdArquivo, TpRegistro, TpPanProxy, PanProxy, CPF, Nome, NomeFacial, DtNascimento, Sexo, CnpjFilial, " +
" Grupo, Email, DDDCel, Celular, NomeMae, IdRegistro, NumLinha) " +
" SELECT @IdArquivo, @TpRegistro, @TpPanProxy, @PanProxy, @CPF, @Nome, @NomeFacial, @DtNascimento, @Sexo, @CnpjFilial, " +
" @Grupo, @Email, @DDDCel, @Celular, @NomeMae, @IdRegistro, @NumLinha ";
using (SqlCommand cmd = new SqlCommand(query, cnx))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("IdArquivo", SqlDbType.Int).Value = acsIdstDet.IdArquivo;
cmd.Parameters.Add("TpRegistro", SqlDbType.Char, 1).Value = acsIdstDet.TpRegistro;
cmd.Parameters.Add("TpPanProxy", SqlDbType.Char, 1).Value = acsIdstDet.TpPanProxy;
cmd.Parameters.Add("PanProxy", SqlDbType.VarChar, 32).Value = acsIdstDet.PanProxy;
cmd.Parameters.Add("CPF", SqlDbType.VarChar, 11).Value = acsIdstDet.Cpf;
cmd.Parameters.Add("Nome", SqlDbType.VarChar, 50).Value = acsIdstDet.Nome;
cmd.Parameters.Add("NomeFacial", SqlDbType.VarChar, 25).Value = BDGeral.BDObtemValor(acsIdstDet.NomeFacial);
cmd.Parameters.Add("DtNascimento", SqlDbType.Date).Value = BDGeral.BDObtemValor <DateTime>(acsIdstDet.DtNascimento);
cmd.Parameters.Add("Sexo", SqlDbType.Char, 1).Value = BDGeral.BDObtemValor(acsIdstDet.Sexo);
cmd.Parameters.Add("CnpjFilial", SqlDbType.VarChar, 14).Value = BDGeral.BDObtemValor(acsIdstDet.CnpjFilial);
cmd.Parameters.Add("Grupo", SqlDbType.VarChar, 20).Value = BDGeral.BDObtemValor(acsIdstDet.Grupo);
cmd.Parameters.Add("Email", SqlDbType.VarChar, 30).Value = BDGeral.BDObtemValor(acsIdstDet.Email);
cmd.Parameters.Add("DDDCel", SqlDbType.VarChar, 2).Value = BDGeral.BDObtemValor(acsIdstDet.DddCel);
cmd.Parameters.Add("Celular", SqlDbType.VarChar, 9).Value = BDGeral.BDObtemValor(acsIdstDet.Celular);
cmd.Parameters.Add("NomeMae", SqlDbType.VarChar, 50).Value = BDGeral.BDObtemValor(acsIdstDet.NomeMae);
cmd.Parameters.Add("IdRegistro", SqlDbType.VarChar, 10).Value = BDGeral.BDObtemValor(acsIdstDet.IdRegistro);
cmd.Parameters.Add("NumLinha", SqlDbType.Int).Value = acsIdstDet.NumLinha;
cnx.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception sqlExc)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.TISC.TISC.Det", sqlExc });
}
throw;
}
}
}
public static void Insere(this Portador ptr, int idArquivo, int numLinha)
{
using (SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["Global"].ConnectionString))
{
try
{
string query = "[crtCargaDetalheIdentificacao]";
using (SqlCommand cmd = new SqlCommand(query, cnx))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("IdArquivo", SqlDbType.Int).Value = idArquivo;
cmd.Parameters.Add("TpRegistro", SqlDbType.Char, 1).Value = ptr.TpRegistro;
cmd.Parameters.Add("TpPanProxy", SqlDbType.Char, 1).Value = ptr.TpIdentif;
cmd.Parameters.Add("PanProxy", SqlDbType.VarChar, 32).Value = ptr.Identificacao.TrimEnd(null);
cmd.Parameters.Add("CPF", SqlDbType.VarChar, 11).Value = ptr.CPF.TrimEnd(null);
cmd.Parameters.Add("Nome", SqlDbType.VarChar, 50).Value = ptr.Nome.TrimEnd(null);
cmd.Parameters.Add("NomeFacial", SqlDbType.VarChar, 25).Value = BDGeral.BDObtemValor(ptr.NomeFacial.TrimEnd(null));
cmd.Parameters.Add("DtNascimento", SqlDbType.Date).Value = BDGeral.BDObtemValor <DateTime>(ptr.DtNascimento);
cmd.Parameters.Add("Sexo", SqlDbType.Char, 1).Value = BDGeral.BDObtemValor(ptr.Sexo);
cmd.Parameters.Add("CnpjFilial", SqlDbType.VarChar, 14).Value = BDGeral.BDObtemValor(ptr.CnpjFilial.TrimEnd(null));
cmd.Parameters.Add("Grupo", SqlDbType.VarChar, 20).Value = BDGeral.BDObtemValor(ptr.Grupo.TrimEnd(null));
cmd.Parameters.Add("Email", SqlDbType.VarChar, 30).Value = BDGeral.BDObtemValor(ptr.Email.TrimEnd(null));
cmd.Parameters.Add("DDDCel", SqlDbType.VarChar, 2).Value = BDGeral.BDObtemValor(ptr.DDDCel.TrimEnd(null));
cmd.Parameters.Add("Celular", SqlDbType.VarChar, 9).Value = BDGeral.BDObtemValor(ptr.Celular.TrimEnd(null));
cmd.Parameters.Add("NomeMae", SqlDbType.VarChar, 50).Value = BDGeral.BDObtemValor(ptr.NomeMae.TrimEnd(null));
cmd.Parameters.Add("IdRegistro", SqlDbType.VarChar, 10).Value = BDGeral.BDObtemValor(ptr.IdRegistro);
cmd.Parameters.Add("NumLinha", SqlDbType.Int).Value = numLinha;
cnx.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception sqlExc)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.CrtCorp.ACSOIDTS.PtrBD", sqlExc });
}
throw;
}
}
}
/// <summary>
/// Insere os dados em crtACSOIDTSCDetalhe
/// </summary>
/// <param name="idArquivo"></param>
/// <param name="tpRegistro"></param>
/// <param name="tpPanProxy"></param>
/// <param name="panProxy"></param>
/// <param name="cpf"></param>
/// <param name="nome"></param>
/// <param name="nomeFacial"></param>
/// <param name="dtNascimento"></param>
/// <param name="sexo"></param>
/// <param name="cnpjFilial"></param>
/// <param name="grupo"></param>
/// <param name="email"></param>
/// <param name="dddCel"></param>
/// <param name="celular"></param>
/// <param name="nomeMae"></param>
/// <param name="idRegistro"></param>
/// <param name="numLinha"></param>
private void InsereDetalhe(int idArquivo, string tpRegistro, string tpPanProxy, string panProxy, string cpf, string nome, string nomeFacial, DateTime?dtNascimento,
string sexo, string cnpjFilial, string grupo, string email, string dddCel, string celular, string nomeMae, string idRegistro,
int numLinha)
{
using (SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["Global"].ConnectionString))
{
try
{
string query = " INSERT [crtACSOIDTSCDetalhe] " +
" (IdArquivo, TpRegistro, TpPanProxy, PanProxy, CPF, Nome, NomeFacial, DtNascimento, Sexo, CnpjFilial, " +
" Grupo, Email, DDDCel, Celular, NomeMae, IdRegistro, NumLinha) " +
" SELECT @IdArquivo, @TpRegistro, @TpPanProxy, @PanProxy, @CPF, @Nome, @NomeFacial, @DtNascimento, @Sexo, @CnpjFilial, " +
" @Grupo, @Email, @DDDCel, @Celular, @NomeMae, @IdRegistro, @NumLinha ";
using (SqlCommand cmd = new SqlCommand(query, cnx))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("IdArquivo", SqlDbType.Int).Value = idArquivo;
cmd.Parameters.Add("TpRegistro", SqlDbType.Char, 1).Value = tpRegistro;
cmd.Parameters.Add("TpPanProxy", SqlDbType.Char, 1).Value = tpPanProxy;
cmd.Parameters.Add("PanProxy", SqlDbType.VarChar, 32).Value = panProxy;
cmd.Parameters.Add("CPF", SqlDbType.VarChar, 11).Value = cpf.TrimEnd(null);
cmd.Parameters.Add("Nome", SqlDbType.VarChar, 50).Value = nome.TrimEnd(null);
cmd.Parameters.Add("NomeFacial", SqlDbType.VarChar, 25).Value = BDGeral.BDObtemValor(nomeFacial.TrimEnd(null));
cmd.Parameters.Add("DtNascimento", SqlDbType.Date).Value = BDGeral.BDObtemValor <DateTime>(dtNascimento);
cmd.Parameters.Add("Sexo", SqlDbType.Char, 1).Value = BDGeral.BDObtemValor(sexo);
cmd.Parameters.Add("CnpjFilial", SqlDbType.VarChar, 14).Value = BDGeral.BDObtemValor(cnpjFilial.TrimEnd(null));
cmd.Parameters.Add("Grupo", SqlDbType.VarChar, 20).Value = BDGeral.BDObtemValor(grupo.TrimEnd(null));
cmd.Parameters.Add("Email", SqlDbType.VarChar, 30).Value = BDGeral.BDObtemValor(email.TrimEnd(null));
cmd.Parameters.Add("DDDCel", SqlDbType.VarChar, 2).Value = BDGeral.BDObtemValor(dddCel.TrimEnd(null));
cmd.Parameters.Add("Celular", SqlDbType.VarChar, 9).Value = BDGeral.BDObtemValor(celular.TrimEnd(null));
cmd.Parameters.Add("NomeMae", SqlDbType.VarChar, 50).Value = BDGeral.BDObtemValor(nomeMae.TrimEnd(null));
cmd.Parameters.Add("IdRegistro", SqlDbType.VarChar, 10).Value = BDGeral.BDObtemValor(idRegistro);
cmd.Parameters.Add("NumLinha", SqlDbType.Int).Value = numLinha;
cnx.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception sqlExc)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.TISC.TISC.Det", sqlExc });
}
throw;
}
}
}
/// <summary>
/// Atualiza o registro através do arquivo de retorno
/// </summary>
/// <param name="idfSimpRet"></param>
public static void AtualizaDetalheIdentificacao(this Identificacao.ACSOIDTSC_R.IdentificacaoSimplifRetornoEN idftDetRet)
{
using (SqlConnection cnx = new SqlConnection(upSight.Consulta.Base.BD.Conexao.StringConexaoBDGlobal))
{
try
{
string query = " UPDATE A " +
" SET " +
" A.Retorno = @retorno, " +
" A.DtRetorno = SYSDATETIME(), " +
" A.DtAlteracao = SYSDATETIME() " +
" FROM crpCRIIdentificacaoDetalhe AS A ";
string condicao = String.Empty;
if (!String.IsNullOrEmpty(idftDetRet.Chave))
{
condicao = " WHERE A.Chave = @chave AND A.IdArquivo = @idArquivo ";
}
else
{
condicao = " WHERE A.IdArquivo = @idArquivo AND A.Retorno IS NULL ";
}
using (SqlCommand cmd = new SqlCommand(String.Concat(query, condicao), cnx))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("idArquivo", SqlDbType.Int).Value = idftDetRet.IdArquivo;
cmd.Parameters.Add("retorno", SqlDbType.VarChar, 50).Value = BDGeral.BDObtemValor(idftDetRet.Retorno);
cmd.Parameters.Add("chave", SqlDbType.VarChar, 15).Value = BDGeral.BDObtemValor(idftDetRet.Chave);
cnx.Open();
cmd.ExecuteNonQuery();
}
}
catch (SqlException sqlExc)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.Integ.Servicos.CrtCorp, IdtnfRBD", sqlExc });
}
throw;
}
catch (Exception ex)
{
if (upSight.Consulta.Base.BD.Geral.TS.TraceError)
{
Trace.TraceError("{0}: {1}", new object[] { "u.Integ.Servicos.CrtCorp, IdtnfR", ex });
}
throw;
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
}
return;
}
}
