public void RegistraEntrada(RegistroEntrada registroEntrada)
{
char?sentido = null;
switch (registroEntrada.Sentido)
{
case 1:
sentido = 'E';
break;
case 2:
sentido = 'S';
break;
}
string comando = $"UPDATE TBREGISTROACESSO SET REGACGIRO = '{sentido}'" +
$"WHERE REGACMATRICULA = {registroEntrada.Matricula} AND REGACTIPOPESSOA = 1 AND " +
$"REGACDIA = {registroEntrada.Horario.ToString("yyyyMMdd")} AND REGACHORA = '{registroEntrada.Horario.ToString("HH:mm:ss")}'";
using (var conn = new FbConnection(_stringDeConexao))
{
conn.Open();
var cmd = new FbCommand(comando, conn);
if (cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = "INSERT INTO TBREGISTROACESSO (REGACMATRICULA, REGACTIPOPESSOA, REGACDIA, REGACHORA, REGACGIRO, REGACAUTOMATICO)" +
$"VALUES({registroEntrada.Matricula}, 1, {registroEntrada.Horario.ToString("yyyyMMdd")}, " +
$"'{registroEntrada.Horario.ToString("HH:mm:ss")}','{sentido}','S'); ";
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public int BuscaUltimoRegistro()
{
var registroEntradas = new List <RegistroEntrada>();
using (SqlConnection sqlConn = SqlConecao())
{
var cmd = new SqlCommand("SELECT [CD_LOG_ACESSO] ,[NU_MATRICULA] ,[NU_DATA_REQUISICAO] ,[TP_SENTIDO_CONSULTA] FROM [dbo].[LOG_ACESSO] ORDER by [CD_LOG_ACESSO]", sqlConn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
registroEntradas.Add(new RegistroEntrada
{
Id = dr.GetInt32(0),
Matricula = dr.GetInt32(1),
Horario = dr.GetDateTime(2),
Sentido = dr.GetInt32(3)
});
}
}
}
var registro = new RegistroEntrada();
if (registroEntradas.Count > 1)
{
registro = registroEntradas[registroEntradas.Count - 1];
return(registro.Id + 1);
}
return(registro.Id);
}
public void UltimoRegistro(RegistroEntrada registroEntrada)
{
File.Delete("UltimoRegistro.txt");
StreamWriter writer = new StreamWriter("UltimoRegistro.txt", true);
using (writer)
{
writer.WriteLine(registroEntrada.Id.ToString());
}
}
public void InserirRegistros(RegistroEntrada registroEntrada)
{
try
{
var sqlConn = SqlConecao();
string sql = $"INSERT INTO [dbo].[LOG_ACESSO]([CD_LOG_ACESSO], [NU_MATRICULA], [NU_DATA_REQUISICAO], [TP_SENTIDO_CONSULTA]) VALUES({registroEntrada.Id}, {registroEntrada.Matricula}, '{registroEntrada.Horario}', {registroEntrada.Sentido})";
var cmd = new SqlCommand(sql, sqlConn);
cmd.ExecuteNonQuery();
sqlConn.Close();
}
catch (Exception)
{
throw;
}
}
public int BuscaUltimoRegistro()
{
var registroEntradas = new List <RegistroEntrada>();
using (var sqlConn = new SqlConnection(_stringConexao))
{
sqlConn.Open();
var cmd = new SqlCommand("SELECT CD_LOG_ACESSO , NU_CREDENCIAL ,DT_REQUISICAO ,TP_SENTIDO_CONSULTA FROM [dbo].[LOG_ACESSO] ORDER by [CD_LOG_ACESSO]", sqlConn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
try
{
var id = dr.GetDecimal(0);
decimal?matricula = dr.GetDecimal(1);
var Horario = dr.GetDateTime(2);
decimal?sentido = dr.GetDecimal(3);
registroEntradas.Add(new RegistroEntrada
{
Id = Convert.ToInt32(id),
Matricula = Convert.ToInt32(matricula),
Horario = Horario,
Sentido = Convert.ToInt32(sentido)
});
}
catch (Exception ex)
{
}
}
}
sqlConn.Close();
}
var registro = new RegistroEntrada();
if (registroEntradas.Count > 1)
{
registro = registroEntradas[registroEntradas.Count - 1];
return(registro.Id + 1);
}
return(registro.Id);
}
public void InserirRegistros(RegistroEntrada registroEntrada)
{
try
{
var sqlConn = new SqlConnection(_stringConexao);
sqlConn.Open();
string sql = $"INSERT INTO [dbo].[LOG_ACESSO](NU_CREDENCIAL ,DT_REQUISICAO ,TP_SENTIDO_CONSULTA,NU_DATA_REQUISICAO,NU_HORA_REQUISICAO,DT_PERSISTENCIA,NU_FUNCAO) " +
$"VALUES({registroEntrada.Matricula}, '{registroEntrada.Horario.ToString("yyyy/MM/dd hh:mm:ss")}', {registroEntrada.Sentido}," +
$"{registroEntrada.Horario.Year}{registroEntrada.Horario.Month}{registroEntrada.Horario.Day},{registroEntrada.Horario.Hour}{registroEntrada.Horario.Minute}," +
$"'{registroEntrada.Horario.ToString("yyyy/MM/dd hh:mm:ss")}',0)";
var cmd = new SqlCommand(sql, sqlConn);
cmd.ExecuteNonQuery();
sqlConn.Close();
}
catch (Exception ex)
{
throw;
}
}
public void RegistraEntrada(RegistroEntrada registroEntrada)
{
var sentido = registroEntrada.Sentido == 1 ? 'E' : 'S';
string comando = $"UPDATE TBREGISTROACESSO SET REGACGIRO = '{sentido}'" +
$"WHERE REGACMATRICULA = {registroEntrada.Matricula} AND REGACTIPOPESSOA = 1 AND " +
$"REGACDIA = {tranformaData(registroEntrada.Horario)} AND REGACHORA = '{tranformahora(registroEntrada.Horario)}'";
using (var conn = SqlConecao())
{
conn.Open();
var cmd = new FbCommand(comando, conn);
if (cmd.ExecuteNonQuery() == 0)
{
cmd.CommandText = "INSERT INTO TBREGISTROACESSO (REGACMATRICULA, REGACTIPOPESSOA, REGACDIA, REGACHORA, REGACGIRO, REGACAUTOMATICO)" +
$"VALUES({registroEntrada.Matricula}, 1, {tranformaData(registroEntrada.Horario)}, " +
$"'{tranformahora(registroEntrada.Horario)}','{sentido}','S'); ";
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public void RegistraUltimoRegistro(RegistroEntrada registroEntrada)
{
_ultimoRegistro.UltimoRegistroAcesso(registroEntrada.Id);
}