public IList <Inquilino> BuscarPor(string columna, string valor)
{
List <Inquilino> res = new List <Inquilino>();
Inquilino p = null;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT * FROM inquilinos WHERE @columna=@valor";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@columna", MySqlDbType.VarChar).Value = columna;
command.Parameters.Add("@valor", MySqlDbType.VarChar).Value = valor;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
p = new Inquilino
{
Id = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
LugarTrabajo = reader.GetString(6),
Estado = reader.GetString(7),
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public int Modificacion(Inquilino p)
{
int res = -1;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"UPDATE inquilinos SET nombre=@nombre, apellido=@apellido, dni=@dni, telefono=@telefono, email=@email, lugar_trabajo=@lugar_trabajo WHERE Id = @id";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@id", p.Id);
command.Parameters.AddWithValue("@nombre", p.Nombre);
command.Parameters.AddWithValue("@apellido", p.Apellido);
command.Parameters.AddWithValue("@dni", p.Dni);
command.Parameters.AddWithValue("@telefono", p.Telefono);
command.Parameters.AddWithValue("@email", p.Email);
command.Parameters.AddWithValue("@lugar_trabajo", p.LugarTrabajo);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public Inquilino ObtenerPorId(int id)
{
Inquilino p = null;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT * FROM inquilinos" +
$" WHERE Id=@id";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
p = new Inquilino
{
Id = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
LugarTrabajo = reader.GetString(6),
Estado = reader.GetString(7),
};
}
connection.Close();
}
}
return(p);
}
public IList <Inquilino> ObtenerTodos()
{
try
{
IList <Inquilino> res = new List <Inquilino>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT * FROM Inquilinos";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
List <Contrato> contratos = null;
while (reader.Read())
{
Inquilino p = new Inquilino
{
Id = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
LugarTrabajo = reader.GetString(6),
Estado = reader.GetString(7),
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
catch (MySqlException ex)
{
throw;
}
}
public int Alta(Inquilino p)
{
int res = -1;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"INSERT INTO inquilinos (nombre, apellido, dni, telefono, email, lugar_trabajo, estado) " +
$"VALUES (@nombre, @apellido, @dni, @telefono, @email, @lugar_trabajo, 'activo');" +
$"SELECT LAST_INSERT_ID();";//devuelve el id insertado
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nombre", p.Nombre);
command.Parameters.AddWithValue("@apellido", p.Apellido);
command.Parameters.AddWithValue("@dni", p.Dni);
command.Parameters.AddWithValue("@telefono", p.Telefono);
command.Parameters.AddWithValue("@email", p.Email);
command.Parameters.AddWithValue("@lugar_trabajo", p.LugarTrabajo);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
p.Id = res;
connection.Close();
}
}
}
catch (Exception e)
{
throw;
Console.WriteLine(e.Message);
}
return(res);
}
public IList <Contrato> ObtenerPorFechas(DateTime inicio, DateTime fin)
{
String start = inicio.ToString("yyyy-MM-dd"); // yyyy-MM-dd
String end = fin.ToString("yyyy-MM-dd"); //dd-MM-yyyy
Contrato c = null;
IList <Contrato> res = new List <Contrato>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = "select * from contratos c INNER JOIN inquilinos i ON c.inquilinoId = i.id INNER JOIN inmuebles d ON c.inmuebleId = d.id WHERE c.fecha_inicio >= @inicio AND c.fecha_fin <= @fin AND c.estado != 'rescindido'";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@inicio", MySqlDbType.String).Value = start;
command.Parameters.Add("@fin", MySqlDbType.String).Value = end;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
c = new Contrato
{
Id = reader.GetInt32(0),
InmuebleId = reader.GetInt32(1),
InquilinoId = reader.GetInt32(2),
FechaInicio = reader.GetDateTime(3),
FechaFin = reader.GetDateTime(4),
Monto = reader.GetDecimal(5),
NombreGarante = reader.GetString(6),
DniGarante = reader.GetString(7),
TelefonoGarante = reader.GetString(8),
MailGarante = reader.GetString(9),
Estado = reader.GetString(10),
};
Inquilino inq = new Inquilino
{
Id = reader.GetInt32(11),
Nombre = reader.GetString(12),
Apellido = reader.GetString(13),
Dni = reader.GetString(14),
Telefono = reader.GetString(15),
Email = reader.GetString(16),
LugarTrabajo = reader.GetString(17),
Estado = reader.GetString(18),
};
Inmueble i = new Inmueble
{
Id = reader.GetInt32(19),
PropietarioId = reader.GetInt32(20),
Direccion = reader.GetString(21),
Ambientes = reader.GetInt32(22),
Uso = reader.GetString(23),
Tipo = reader.GetString(24),
Precio = reader.GetFloat(25),
Disponible = reader.GetBoolean(26),
};
c.Inquilino = inq;
c.Inmueble = i;
res.Add(c);
}
connection.Close();
}
}
return(res);
}
public IList <Contrato> ObtenerPorInmuebleId(int id)
{
Contrato c = null;
IList <Contrato> res = new List <Contrato>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"select * from contratos c join inquilinos i join inmuebles m WHERE c.inquilinoId = i.id AND c.inmuebleId = m.id and c.inmuebleId =@Id";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
c = new Contrato
{
Id = reader.GetInt32(0),
InmuebleId = reader.GetInt32(1),
InquilinoId = reader.GetInt32(2),
FechaInicio = reader.GetDateTime(3),
FechaFin = reader.GetDateTime(4),
Monto = reader.GetDecimal(5),
NombreGarante = reader.GetString(6),
DniGarante = reader.GetString(7),
TelefonoGarante = reader.GetString(8),
MailGarante = reader.GetString(9),
Estado = reader.GetString(10),
};
Inquilino inq = new Inquilino
{
Id = reader.GetInt32(11),
Nombre = reader.GetString(12),
Apellido = reader.GetString(13),
Dni = reader.GetString(14),
Telefono = reader.GetString(15),
Email = reader.GetString(16),
LugarTrabajo = reader.GetString(17),
Estado = reader.GetString(18),
};
Inmueble i = new Inmueble
{
Id = reader.GetInt32(19),
PropietarioId = reader.GetInt32(20),
Direccion = reader.GetString(21),
Ambientes = reader.GetInt32(22),
Uso = reader.GetString(23),
Tipo = reader.GetString(24),
Precio = reader.GetFloat(25),
Disponible = reader.GetBoolean(26),
};
c.Inquilino = inq;
c.Inmueble = i;
res.Add(c);
}
connection.Close();
}
}
return(res);
}
