public Usuario Create(Usuario entity, long codigoCampo)
{
connection = new SqlServerConnection();
DbTransaction transaction = connection.BeginTransaction();
try
{
var clave = Encrypt.GetMD5(entity.pass);
var parametros = new Dictionary <string, object>
{
{ "@usuario", entity.usuario },
{ "@nombre", entity.nombre },
{ "@apellido", entity.apellido },
{ "@pass", clave },
{ "@idRol", entity.idRol },
{ "@codigoCampo", codigoCampo }
};
entity.idUsuario = connection.Execute("spRegistrarUsuario", parametros, System.Data.CommandType.StoredProcedure, transaction);
if (entity.idUsuario == 0)
{
throw new ArgumentException("Create Usuario Error");
}
else if (entity.idUsuario == -1)
{
throw new ArgumentException("El usuario ya existe para este campo");
}
var param = new Dictionary <string, object>
{
{ "@idUsuario", entity.idUsuario },
{ "@codigoCampo", codigoCampo }
};
var insert = connection.Execute("spRegistrarUsuarioEnCampo", param, System.Data.CommandType.StoredProcedure, transaction);
if (insert == 0)
{
throw new ArgumentException("Create Usuario por Campo Error");
}
connection.Commit(transaction);
return(entity);
}
catch (Exception ex)
{
connection.Rollback(transaction);
throw;
}
finally
{
connection.Close();
connection = null;
transaction = null;
}
}
public InconsistenciaResolver Create(InconsistenciaResolver entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>();
if (entity.inseminacionAnterior != null && entity.inseminacionNueva != null && entity.inseminacionResultante != null)
{
parametros.Add("@idInseminacionAnterior", entity.inseminacionAnterior.idInseminacion);
parametros.Add("@idInseminacionConflictiva", entity.inseminacionNueva.idInseminacion);
parametros.Add("@fechaInseminacion", entity.inseminacionResultante.fechaInseminacion);
parametros.Add("@tipoInseminacion", entity.inseminacionNueva.idTipoInseminacion);
var update = connection.Execute("spResolverInseminacionConflictiva", parametros, System.Data.CommandType.StoredProcedure);
if (entity.inseminacionNueva.idTipoInseminacion == 2)
{
var parametrosToro = new Dictionary <string, object>()
{
{ "@idToro", 0 },
{ "@idInseminacion", entity.inseminacionAnterior.idInseminacion }
};
for (int i = 0; i < entity.inseminacionNueva.listaBovinos.Count(); i++)
{
parametrosToro["@idToro"] = entity.inseminacionNueva.listaBovinos.ElementAt(i).idBovino;
update = connection.Execute("spActualizarTorosXInseminacionXConflicto", parametrosToro, System.Data.CommandType.StoredProcedure);
}
}
}
else if (entity.tactoAnterior != null && entity.tactoNuevo != null && entity.tactoResultante != null)
{
parametros.Add("@idInseminacionAnterior", entity.tactoAnterior.idInseminacion);
parametros.Add("@idInseminacionConflictiva", entity.tactoNuevo.idInseminacion);
parametros.Add("@fechaTactoAnterior", entity.tactoAnterior.fechaTacto);
parametros.Add("@fechaTacto", entity.tactoResultante.fechaTacto);
parametros.Add("@exitoso", entity.tactoResultante.exitoso);
parametros.Add("@idTipoTacto", entity.tactoResultante.idTipoTacto);
var update = connection.Execute("spResolverTactoConflictivo", parametros, System.Data.CommandType.StoredProcedure);
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Tacto Insert(Tacto tacto)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>
{
{ "@idTipoTacto", tacto.idTipoTacto },
{ "@exitoso", tacto.exitoso },
{ "@idInseminacion", tacto.idInseminacion },
{ "@fechaTacto", tacto.fechaTacto },
{ "@codigoCampo", tacto.codigoCampo }
};
var insert = connection.Execute("spRegistrarTactoXInseminacion", parametros, System.Data.CommandType.StoredProcedure);
return(tacto);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Tacto Update(long id, Tacto entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>
{
{ "@idTipoTacto", entity.idTipoTacto },
{ "@exitoso", entity.exitoso },
{ "@idInseminacion", entity.idInseminacion },
{ "@fechaTacto", entity.fechaTacto }
};
var insert = connection.Execute("spUpdateTacto", parametros, System.Data.CommandType.StoredProcedure);
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public UsuarioLogueado UpdatePerfil(UsuarioLogueado entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>()
{
{ "@nombre", entity.nombre },
{ "@apellido", entity.apellido },
{ "@usuario", entity.usuario }
};
entity.idUsuario = connection.Execute("spActualizarDatosPerfil", parametros, System.Data.CommandType.StoredProcedure);
if (entity.idUsuario == 0)
{
throw new ArgumentException("Update Perfil Error");
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public EstablecimientoOrigen Create(EstablecimientoOrigen entity)
{
connection = new SqlServerConnection();
try
{
var parametros = new Dictionary <string, object>
{
{ "@nombre", entity.nombre },
{ "@idLocalidad", entity.idLocalidad },
{ "@codigoCampo", entity.codigoCampo }
};
entity.idEstablecimiento = connection.Execute("spRegistrarEstablecimientoOrigen", parametros, System.Data.CommandType.StoredProcedure);
if (entity.idEstablecimiento == 0)
{
throw new ArgumentException("Create Establecimiento Origen Error");
}
else if (entity.idEstablecimiento == -1)
{
throw new ArgumentException("Establecimiento Origen ya existe");
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Multimedia Update(long id, Multimedia entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>()
{
{ "@idUsuario", entity.idUsuario },
{ "@mulPath", entity.mulPath }
};
entity.mulId = connection.Execute("spActualizarMultimedia", parametros, System.Data.CommandType.StoredProcedure);
if (entity.mulId == 0)
{
Create(entity);
}
//throw new ArgumentException("Update Perfil Error");
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public void Delete(long id)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>()
{
{ "@codigoCampo", id }
};
var delete = connection.Execute("spDeleteCampo", parametros, System.Data.CommandType.StoredProcedure);
if (delete == 0)
{
throw new ArgumentException("DeleteCampoError");
}
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Multimedia Create(Multimedia entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>
{
{ "@mulTipo", entity.mulTipo },
{ "@mulPath", entity.mulPath }
};
if (entity.idCampo != 0)
{
parametros.Add("@idCampo", entity.idCampo);
}
if (entity.idUsuario != 0)
{
parametros.Add("@idUsuario", entity.idUsuario);
}
entity.mulId = connection.Execute("spRegistrarMultimedia", parametros, System.Data.CommandType.StoredProcedure);
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Alimento Create(Alimento entity)
{
connection = new SqlServerConnection();
try
{
var parametros = new Dictionary <string, object>
{
{ "@nombre", entity.nombre },
{ "@codigoCampo", entity.codigoCampo }
};
entity.idAlimento = connection.Execute("spRegistrarAlimento", parametros, System.Data.CommandType.StoredProcedure);
if (entity.idAlimento == 0)
{
throw new ArgumentException("Create Alimento Error");
}
else if (entity.idAlimento == -1)
{
throw new ArgumentException("Alimento ya existe");
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Usuario Update(long id, Usuario entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>
{
{ "@idUsuario", id },
{ "@nombre", entity.nombre },
{ "@apellido", entity.apellido },
{ "@usuario", entity.usuario },
{ "@idRol", entity.idRol }
};
if (entity.contrasenia != null)
{
var clave = Encrypt.GetMD5(entity.contrasenia);
parametros.Add("@contrasenia", clave);
}
var update = connection.Execute("spModificarUsuario", parametros, System.Data.CommandType.StoredProcedure);
if (update == 0)
{
throw new ArgumentException("Update Usuario Error");
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public void Activar(long id, long codigoCampo)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>
{
{ "@idUsuario", id },
{ "@codigoCampo", codigoCampo }
};
var activar = connection.Execute("spActivarUsuario", parametros, System.Data.CommandType.StoredProcedure);
if (activar == 0)
{
throw new ArgumentException("Activar Usuario Error");
}
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public void ShouldEmitErrorMessage()
{
var messages = new List <string>();
var sut = new SqlServerConnection("Data Source=.;Initial Catalog=TestDatabase;Integrated Security=True;");
sut.MessageReceived += (x, y) => messages.Add(y.Message);
try
{
sut.Execute("PRINT 'Hi there'");
}
catch { }
Assert.That(messages.SingleOrDefault() == "Hi there");
}
public void ShouldExecute()
{
var sut = new SqlServerConnection("Data Source=.;Initial Catalog=DbSessionTests;Integrated Security=True;");
sut.Execute(
"INSERT INTO TestTable VALUES(5555, @Value)",
new ParameterSet {
new Parameter <int>("Value", 7)
});
using (var connection = new SqlConnection("Data Source=.;Initial Catalog=DbSessionTests;Integrated Security=True;"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT TestValue FROM TestTable WHERE Id = 5555";
connection.Open();
var result = int.Parse(command.ExecuteScalar().ToString());
Assert.That(result, Is.EqualTo(7));
}
}
public Campo Create(Campo entity)
{
try
{
connection = new SqlServerConnection();
var parametros = new Dictionary <string, object>()
{
{ "@nombre", entity.nombre },
{ "@superficie", entity.superficie },
{ "@idLocalidad", entity.idLocalidad },
{ "@usuario", entity.usuario }
};
if (entity.latitud != 0)
{
parametros.Add("@latitud", entity.latitud);
}
if (entity.longitud != 0)
{
parametros.Add("@longitud", entity.longitud);
}
entity.idCampo = connection.Execute("spRegistrarCampo", parametros, System.Data.CommandType.StoredProcedure);
if (entity.idCampo == 0)
{
throw new ArgumentException("CreateCampoError");
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
connection = null;
}
}
public Estado Create(Estado entity)
{
connection = new SqlServerConnection();
try
{
var parametros = new Dictionary <string, object>
{
{ "@nombre", entity.nombre },
{ "@descripcion", entity.descripcion },
{ "@codigoCampo", entity.codigoCampo }
};
if (entity.genero < 2)
{
parametros.Add("@genero", entity.genero);
}
entity.idEstado = connection.Execute("spRegistrarEstado", parametros, System.Data.CommandType.StoredProcedure);
if (entity.idEstado == 0)
{
throw new ArgumentException("Create Estado Error");
}
else if (entity.idEstado == -1)
{
throw new ArgumentException("Estado ya existe");
}
return(entity);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
}
}
public override void OnActionExecuting(HttpActionContext actionContext)
{
SqlServerConnection connection = new SqlServerConnection();
Dictionary <string, object> parametros;
try
{
var param = actionContext.Request.Headers.Authorization == null ? "" : actionContext.Request.Headers.Authorization.ToString();
if (param.Length == 0)
{
actionContext.Response = new HttpResponseMessage(HttpStatusCode.NotAcceptable)
{
Content = new StringContent(string.Format("Token_Invalido")),
ReasonPhrase = "Token_Invalido"
};
}
else
{
long validar = 0;
if (!param.Contains("-"))
{
parametros = new Dictionary <string, object>
{
{ "@NombreApp", null },
{ "@Usu_Token", param },
{ "@TokenVigencia", int.Parse(ConfigurationManager.AppSettings["TokenVigencia"]) }
};
validar = connection.Execute("Token_Validar", parametros, System.Data.CommandType.StoredProcedure);
}
else
{
var nombreApp = param.Split('-')[1];
if (nombreApp.Equals("5") || nombreApp.Equals("10067"))
{
validar = 1;
}
else
{
parametros = new Dictionary <string, object>
{
{ "@NombreApp", nombreApp },
{ "@Usu_Token", param.Split('-')[0] },
{ "@TokenVigencia", 0 }
};
validar = connection.Execute("Token_Validar", parametros, System.Data.CommandType.StoredProcedure);
}
}
if (validar == 0)
{
actionContext.Response = new HttpResponseMessage(HttpStatusCode.NotAcceptable)
{
Content = new StringContent(string.Format("Token_Invalido")),
ReasonPhrase = "Token_Invalido"
};
}
}
}
catch (Exception)
{
actionContext.Response = new HttpResponseMessage(HttpStatusCode.NotAcceptable)
{
Content = new StringContent(string.Format("Token_Invalido")),
ReasonPhrase = "Token_Invalido"
};
}
finally
{
parametros = null;
connection.Close();
}
}
public Evento Insert(Evento evento, List <long> lista)
{
connection = new SqlServerConnection();
DbTransaction transaction = connection.BeginTransaction();
try
{
var parametrosEvento = new Dictionary <string, object>
{
{ "@cant", evento.cantidad },
{ "@idTipoEvento", evento.idTipoEvento },
{ "@fechaHora", evento.fechaHora }
};
switch (evento.idTipoEvento)
{
case 1: //vacunacion
parametrosEvento.Add("@idVacuna", evento.idVacuna);
break;
case 2: //antibiotico
parametrosEvento.Add("@idAntibiotico", evento.idAntibiotico);
break;
case 3: //manejo
parametrosEvento.Add("@codigoCampo", evento.idCampoDestino);
parametrosEvento.Add("@idRodeoDestino", evento.idRodeoDestino);
break;
case 4: //alimenticio
parametrosEvento.Add("@idAlimento", evento.idAlimento);
break;
}
evento.idEvento = connection.Execute("spRegistrarEvento", parametrosEvento, System.Data.CommandType.StoredProcedure, transaction);
if (evento.idEvento == 0)
{
throw new ArgumentException("Create Evento Error");
}
var parametrosDetalle = new Dictionary <string, object>()
{
{ "@idEvento", evento.idEvento },
{ "@idTipoEvento", evento.idTipoEvento },
{ "@fechaHora", evento.fechaHora }
};
switch (evento.idTipoEvento)
{
case 1: //vacunacion
parametrosDetalle.Add("@idVacuna", evento.idVacuna);
break;
case 3: //manejo
parametrosDetalle.Add("@idRodeoDestino", evento.idRodeoDestino);
break;
case 4: //alimenticio
parametrosDetalle.Add("@idAlimento", evento.idAlimento);
break;
}
var insert = 0;
parametrosDetalle.Add("@idBovino", 0);
for (int i = 0; i < lista.Count; i++)
{
parametrosDetalle["@idBovino"] = lista.ElementAt(i);
insert = connection.Execute("spRegistrarEventosXBovino", parametrosDetalle, System.Data.CommandType.StoredProcedure, transaction);
if (insert == 0)
{
throw new ArgumentException("Create EventosXBovino Error");
}
}
//if (evento.idTipoEvento == 3)
//{
// insert = 0;
// var parametros = new Dictionary<string, object>
// {
// {"@idRodeo", evento.idRodeoDestino },
// {"@idBovino", 0 }
// };
// for (int i = 0; i < lista.Count; i++)
// {
// parametros["@idBovino"] = lista.ElementAt(i);
// insert = connection.Execute("spActualizarRodeoBovino", parametros, System.Data.CommandType.StoredProcedure, transaction);
// if (insert == 0)
// throw new ArgumentException("Update rodeo bovino Error");
// }
//}
connection.Commit(transaction);
return(evento);
}
catch (Exception ex)
{
connection.Rollback(transaction);
throw;
}
finally
{
connection.Close();
connection = null;
transaction = null;
}
}
public Evento Update(long id, Evento entity, List <long> lista)
{
connection = new SqlServerConnection();
DbTransaction transaction = connection.BeginTransaction();
try
{
if (lista == null)
{
var param = new Dictionary <string, object>
{
{ "@idEvento", id }
};
connection.Execute("spDeleteDetalleXBovino", param, System.Data.CommandType.StoredProcedure, transaction);
var delete = connection.Execute("spEliminarEvento", param, System.Data.CommandType.StoredProcedure, transaction);
if (delete == 0)
{
throw new ArgumentException("Delete Evento Error");
}
}
else
{
var parametrosEvento = new Dictionary <string, object>
{
{ "@idEvento", id },
{ "@idTipoEvento", entity.idTipoEvento },
{ "@fechaHora", entity.fechaHora },
{ "@cantidad", entity.cantidad }
};
switch (entity.idTipoEvento)
{
case 1:
parametrosEvento.Add("@idVacuna", entity.idVacuna);
break;
case 2:
parametrosEvento.Add("@idAntibiotico", entity.idAntibiotico);
break;
case 3:
parametrosEvento.Add("@idCampoDestino", entity.idCampoDestino);
parametrosEvento.Add("@idRodeoDestino", entity.idRodeoDestino);
break;
case 4:
parametrosEvento.Add("@idAlimento", entity.idAlimento);
break;
}
var update = connection.Execute("spModificarEvento", parametrosEvento, System.Data.CommandType.StoredProcedure, transaction);
if (update == 0)
{
throw new ArgumentException("Update evento error");
}
var parametrosDetalle = new Dictionary <string, object>
{
{ "@idEvento", id }
};
connection.Execute("spDeleteDetalleXBovino", parametrosDetalle, System.Data.CommandType.StoredProcedure, transaction);
var insertDetalle = 0;
var fechaHora = DateTime.Now.ToString("yyyyMMddHHmmss");
parametrosDetalle.Add("@idBovino", 0);
parametrosDetalle.Add("@fechaHora", fechaHora);
parametrosDetalle.Add("@idTipoEvento", entity.idTipoEvento);
switch (entity.idTipoEvento)
{
case 1:
parametrosDetalle.Add("@idVacuna", entity.idVacuna);
break;
case 3:
parametrosDetalle.Add("@idRodeoDestino", entity.idRodeoDestino);
break;
case 4:
parametrosDetalle.Add("@idAlimento", entity.idAlimento);
parametrosDetalle.Add("@cantAlimento", entity.cantidad);
break;
}
for (int i = 0; i < lista.Count; i++)
{
parametrosDetalle["@idBovino"] = lista.ElementAt(i);
insertDetalle = connection.Execute("spRegistrarEventosXBovino", parametrosDetalle, System.Data.CommandType.StoredProcedure, transaction);
if (insertDetalle == 0)
{
throw new ArgumentException("Update EventosXBovino Error");
}
}
//if (entity.idTipoEvento == 3)
//{
// var insert = 0;
// var parametros = new Dictionary<string, object>
// {
// {"@idRodeo", entity.idRodeoDestino },
// {"@idBovino", 0 }
// };
// for (int i = 0; i < lista.Count; i++)
// {
// parametros["@idBovino"] = lista.ElementAt(i);
// insert = connection.Execute("spActualizarRodeoBovino", parametros, System.Data.CommandType.StoredProcedure, transaction);
// if (insert == 0)
// throw new ArgumentException("Update rodeo bovino Error");
// }
//}
}
connection.Commit(transaction);
return(entity);
}
catch (Exception ex)
{
connection.Rollback(transaction);
throw;
}
finally
{
connection.Close();
connection = null;
transaction = null;
}
}