public IList <Inmueble> obtenerPorPropietario(int id)
{
List <Inmueble> res = new List <Inmueble>();
Inmueble p = null;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT * FROM Inmuebles" +
$" WHERE propietarioId=@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())
{
p = new Inmueble
{
Id = reader.GetInt32(0),
PropietarioId = reader.GetInt32(1),
Direccion = reader.GetString(2),
Ambientes = reader.GetInt32(3),
Uso = reader.GetString(4),
Tipo = reader.GetString(5),
Precio = reader.GetFloat(6),
Disponible = reader.GetBoolean(7),
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public int Alta(Inmueble p)
{
int res = -1;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"INSERT INTO inmuebles (propietarioId, direccion, ambientes, uso, tipo, precio, disponible) " +
$"VALUES (@propietarioId, @direccion, @ambientes, @uso, @tipo, @precio, @disponible);" +
$"SELECT LAST_INSERT_ID();";//devuelve el id insertado
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@propietarioId", p.PropietarioId);
command.Parameters.AddWithValue("@direccion", p.Direccion);
command.Parameters.AddWithValue("@ambientes", p.Ambientes);
command.Parameters.AddWithValue("@uso", p.Uso);
command.Parameters.AddWithValue("@tipo", p.Tipo);
command.Parameters.AddWithValue("@precio", p.Precio);
command.Parameters.AddWithValue("@disponible", p.Disponible);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
p.Id = res;
connection.Close();
}
}
}
catch (Exception e)
{
throw;
Console.WriteLine(e.Message);
}
return(res);
}
public IList <Inmueble> ObtenerTodos()
{
IList <Inmueble> res = new List <Inmueble>();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT id, propietarioId, direccion, ambientes, uso, tipo, precio, disponible" +
$" FROM Inmuebles";
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Inmueble i = new Inmueble
{
Id = reader.GetInt32(0),
PropietarioId = reader.GetInt32(1),
Direccion = reader.GetString(2),
Ambientes = reader.GetInt32(3),
Uso = reader.GetString(4),
Tipo = reader.GetString(5),
Precio = reader.GetFloat(6),
Disponible = reader.GetBoolean(7),
};
i.Propietario = propietarioData.ObtenerPorId(i.PropietarioId);
res.Add(i);
}
connection.Close();
}
}
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);
}