public void CrudCliente()
{
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
StringBuilder name = new StringBuilder();
name.Append(this.Nombre);
name.Append(" ");
name.Append(this.Apellido);
if (this.ClienteId == 0)
{
context.Entry(this).Entity.Estado = 1;//Activo = 1, De Baja = 0
context.Entry(this).Entity.NombreCompleto = name.ToString();
context.Entry(this).State = EntityState.Added;
}
else
{
context.Entry(this).Entity.NombreCompleto = name.ToString();
context.Entry(this).State = EntityState.Modified;
}
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
new Exception(e.Message);
}
}
}
}
public void CrudEspecie()
{
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
if (this.EspecieId == 0)
{
context.Entry(this).State = EntityState.Added;
}
else
{
context.Entry(this).State = EntityState.Modified;
}
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
new Exception(e.Message);
}
}
}
}
public List <Consulta> GetAllConsultas(int estado)
{
List <Consulta> consultas = new List <Consulta>();
using (var context = new VeterinariaBDContext())
{
try
{
if (estado == 1)//Por Atender
{
consultas = context.Consulta.Include("Cliente").Include("Mascota")
.OrderBy(x => x.FechaAtencion).Where(x => x.Estado == estado && DateTime.Today <= x.FechaAtencion)
.ToList();
}
else if (estado == 0)//Anuladas, Expiradas
{
consultas = context.Consulta.Include("Cliente").Include("Mascota")
.OrderBy(x => x.FechaAtencion).Where(x => x.Estado == estado || (DateTime.Today > x.FechaAtencion && x.Estado == 1))
.ToList();
}
else if (estado == 2)//Atendidas
{
consultas = context.Consulta.Include("Cliente").Include("Mascota")
.OrderBy(x => x.FechaAtencion).Where(x => x.Estado == estado)
.ToList();
}
}
catch (Exception e)
{
Message = e.Message;
}
}
return(consultas);
}
public int CambiarPassword(string Password, string NewPassword, int UsuarioId)
{
int usu = 0;
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
usu = context.Database.ExecuteSqlCommand(
"UPDATE Usuario set Password = '******' WHERE Password = @Password AND UsuarioId = @UsuarioId",
new SqlParameter("Password", Password),
new SqlParameter("UsuarioId", UsuarioId)
);
context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw new Exception(e.Message);
}
}
}
return(usu);
}
/*
* public List<Diagnostico> GetAllDiagnosticos()
* {
* using (var context = new VeterinariaBDContext())
* {
* try
* {
* var data = (from d in context.Diagnostico
* join c in context.Consulta on d.ConsultaId equals c.ConsultaId
* join u in context.Usuario on d.VeterinarioId equals u.UsuarioId
* join cli in context.Cliente on c.ClienteId equals cli.ClienteId
* join m in context.Mascota on c.MascotaId equals m.MascotaId
* select new {
* Cliente = cli.Nombre+' '+cli.Apellido,
* Veterinario = u.Nombre+' '+u.Apellido,
* FechaDiagnostico = d.FechaDiagnostico,
* DiagnosticoId = d.DiagnosticoId
* });
*
* }
* catch (Exception)
* {
* throw;
* }
* }
*
* return data;
* }
*/
public void registrarDiagnostico(int VeterinarioId, int ConsultaId)
{
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
context.Entry(this).Entity.FechaDiagnostico = DateTime.Now;
context.Entry(this).Entity.VeterinarioId = VeterinarioId;
context.Entry(this).Entity.ConsultaId = ConsultaId;
context.Entry(this).State = EntityState.Added;
/*
* foreach (var r in this.Receta)
* context.Entry(r).State = EntityState.Unchanged;
*/
context.Database.ExecuteSqlCommand(
"UPDATE Consulta set Estado = 2 WHERE ConsultaId = @ConsultaId",
new SqlParameter("ConsultaId", ConsultaId)
);
transaction.Commit();
context.SaveChanges();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
/*
* Eliminar 0 -- Eliminado
* Restaurar 1 -- Activo
*/
public int CambiarEstado(int estado, int UsuarioId)
{
int usu = 0;
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
usu = context.Database.ExecuteSqlCommand(
"UPDATE Usuario set Estado = " + estado + " WHERE UsuarioId = @UsuarioId",
new SqlParameter("UsuarioId", UsuarioId)
);
context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw new Exception(e.Message);
}
}
}
return(usu);
}
public int CambiarEstado(int ConsultaId, int estado)
{
int c = 0;
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
c = context.Database.ExecuteSqlCommand(
"UPDATE Consulta set Estado = " + estado + " WHERE ConsultaId = @ConsultaId",
new SqlParameter("ConsultaId", ConsultaId)
);
context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Message = e.Message;
}
}
}
return(c);
}
public void CrudMascota()
{
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
if (this.MascotaId == 0)
{
context.Entry(this).Entity.Estado = 1;//Vivo = 1, Fallecido = 0, Enfermo = 2
context.Entry(this).State = EntityState.Added;
}
else
{
context.Entry(this).State = EntityState.Modified;
}
context.SaveChanges();
transaction.Commit();
Message = "Registro guardado";
}
catch (Exception e)
{
transaction.Rollback();
Message = e.Message;
}
}
}
}
public int GetConsultaId(int id)
{
using (var context = new VeterinariaBDContext())
{
try
{
id = context.Consulta
.Where(x => x.ConsultaId == id)
.Single().ConsultaId;
}
catch (Exception e)
{
Message = e.Message;
}
}
return(id);
}
public List <Raza> GetAllRazas()
{
List <Raza> razas = new List <Raza>();
using (var context = new VeterinariaBDContext())
{
try
{
razas = context.Raza.Include("Especie")
.ToList();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(razas);
}
public List <Laboratorio> GetAllLaboratorios()
{
List <Laboratorio> laboratorios = new List <Laboratorio>();
using (var context = new VeterinariaBDContext())
{
try
{
laboratorios = context.Laboratorio
.ToList();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(laboratorios);
}
public List <TipoMedicamento> GetAllTipoMedicamento()
{
List <TipoMedicamento> tipoMedicamento = new List <TipoMedicamento>();
using (var context = new VeterinariaBDContext())
{
try
{
tipoMedicamento = context.TipoMedicamento
.ToList();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(tipoMedicamento);
}
public Especie GetEspecie(int id)
{
Especie especie = new Especie();
using (var context = new VeterinariaBDContext())
{
try
{
especie = context.Especie
.Where(x => x.EspecieId == id)
.Single();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(especie);
}
public List <Especie> GetAllEspecies()
{
List <Especie> especies = new List <Especie>();
using (var context = new VeterinariaBDContext())
{
try
{
especies = context.Especie
.ToList();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(especies);
}
public int CrudConsulta(int id)
{
int crud = 0;
using (var context = new VeterinariaBDContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
if (this.ConsultaId == 0)
{
context.Entry(this).Entity.CajeroId = id;
context.Entry(this).Entity.Estado = 1;//Por atender = 1, Anulada = 0, Atendida = 2
context.Entry(this).Entity.FechaRegistro = DateTime.Now;
context.Entry(this).State = EntityState.Added;
crud = 1;
}
else
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
context.Entry(this).State = EntityState.Modified;
context.Entry(this).Property(x => x.CajeroId).IsModified = false;
context.Entry(this).Property(x => x.Estado).IsModified = false;
context.Entry(this).Property(x => x.FechaRegistro).IsModified = false;
crud = 2;
}
context.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Message = e.Message;
}
}
}
return(crud);
}
public Raza GetRaza(int id)
{
Raza raza = new Raza();
using (var context = new VeterinariaBDContext())
{
try
{
raza = context.Raza
.Include("Especie")
.Where(x => x.EspecieId == id)
.Single();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(raza);
}
public Usuario Login(string user, string password)
{
Usuario usuario = new Usuario();
using (var context = new VeterinariaBDContext())
{
try
{
usuario = context.Usuario
.Include("Rol")
.Where(x => x.NombreUsuario == user && x.Password == password)
.FirstOrDefault();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(usuario);
}
public Usuario GetUsuario(int id)
{
Usuario usuario = new Usuario();
using (var context = new VeterinariaBDContext())
{
try
{
usuario = context.Usuario
.Include("Rol")
.Where(x => x.UsuarioId == id)
.Single();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(usuario);
}
public List <Rol> GetAllRoles()
{
List <Rol> roles = new List <Rol>();
using (var context = new VeterinariaBDContext())
{
try
{
roles = context.Rol
.OrderBy(x => x.RolId)
.ToList();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(roles);
}
public Cliente GetCliente(int id)
{
Cliente cliente = new Cliente();
using (var context = new VeterinariaBDContext())
{
try
{
cliente = context.Cliente
.Include("Mascota")
.Where(x => x.ClienteId == id)
.Single();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(cliente);
}
public List <Cliente> GetAllClientes()
{
List <Cliente> clientes = new List <Cliente>();
using (var context = new VeterinariaBDContext())
{
try
{
clientes = context.Cliente
.OrderBy(x => x.Apellido)
.ToList();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(clientes);
}
public Cliente GetClienteByQuery(string query)
{
Cliente cliente = new Cliente();
using (var context = new VeterinariaBDContext())
{
try
{
cliente = context.Cliente
.Include(m => m.Mascota)
.Where(x => x.Nombre == query)
.Single();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(cliente);
}
public List <Consulta> GetAllConsultasDia()
{
List <Consulta> consultas = new List <Consulta>();
using (var context = new VeterinariaBDContext())
{
try
{
consultas = context.Consulta.Include("Cliente").Include("Mascota")
.OrderBy(x => x.FechaAtencion).Where(x => x.Estado == 1 && x.FechaAtencion == DateTime.Today)
.ToList();
}
catch (Exception e)
{
Message = e.Message;
}
}
return(consultas);
}
public List <Cliente> GetAllClientesByLast()
{
List <Cliente> clientes = new List <Cliente>();
using (var context = new VeterinariaBDContext())
{
try
{
clientes = context.Cliente
.Include("Mascota")
.OrderByDescending(x => x.ClienteId)
.ToList();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(clientes);
}
public Consulta GetConsulta(int id)
{
Consulta consulta = new Consulta();
using (var context = new VeterinariaBDContext())
{
try
{
consulta = context.Consulta
.Include("Cliente")
.Include("Mascota")
.Include("Usuario")
.Where(x => x.ConsultaId == id)
.Single();
}
catch (Exception e)
{
Message = e.Message;
}
}
return(consulta);
}
public List <Mascota> GetAllMascotas()
{
List <Mascota> mascotas = new List <Mascota>();
using (var context = new VeterinariaBDContext())
{
try
{
mascotas = context.Mascota
.Include("Cliente")
.Include("Especie")
.Include("Raza")
.ToList();
}
catch (Exception e)
{
Message = e.Message;
}
}
return(mascotas);
}
public Medicamento GetMedicamento(int id)
{
Medicamento medicamento = new Medicamento();
using (var context = new VeterinariaBDContext())
{
try
{
medicamento = context.Medicamento
.Include("TipoMedicamento")
.Include("Especie")
.Include("Laboratorio")
.Where(x => x.MedicamentoId == id)
.Single();
}
catch (Exception e)
{
new Exception(e.Message);
}
}
return(medicamento);
}
public Mascota GetMascota(int id)
{
Mascota mascota = new Mascota();
using (var context = new VeterinariaBDContext())
{
try
{
mascota = context.Mascota
.Include("Cliente")
.Include("Especie")
.Include("Raza")
.Where(x => x.MascotaId == id)
.Single();
}
catch (Exception e)
{
Message = e.Message;
}
}
return(mascota);
}
public List <Usuario> GetAllUsuarios(int estado)
{
List <Usuario> usuarios = new List <Usuario>();
using (var context = new VeterinariaBDContext())
{
try
{
usuarios = context.Usuario
.Include("Rol")
.OrderByDescending(x => x.UsuarioId)
.Where(x => x.Estado == estado)
.ToList();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(usuarios);
}
public Consulta VerificarConsulta(int ClienteId, int MascotaId, DateTime FechaAtencion)
{
Consulta consulta = new Consulta();
using (var context = new VeterinariaBDContext())
{
try
{
consulta = context.Consulta
.Include("Cliente")
.Include("Mascota")
.Include("Usuario")
.Where(x => x.ClienteId == ClienteId && x.MascotaId == MascotaId && x.FechaAtencion.CompareTo(FechaAtencion.Date) == 0)
.FirstOrDefault();
}
catch (Exception e)
{
Message = e.Message;
}
}
return(consulta);
}