public IList <Pago> ObtenerTodos()
{
IList <Pago> res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT IdPago, Importe, p.FechaDePago, p.IdContrato," +
" c.IdInquilino, i.Nombre, i.Apellido, n.IdInmueble, n.Direccion" +
" FROM Pagos p INNER JOIN Contratos c ON p.IdContrato = c.IdContrato INNER JOIN Inquilinos i ON i.IdInquilino = c.IdInquilino " +
"INNER JOIN Inmuebles n ON n.IdInmueble = c.IdInmueble";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago entidad = new Pago
{
IdPago = reader.GetInt32(0),
Importe = reader.GetInt32(1),
FechaDePago = reader.GetDateTime(2),
IdContrato = reader.GetInt32(3),
Contrato = new Contrato
{
IdContrato = reader.GetInt32(3),
IdInquilino = reader.GetInt32(4),
Inquilino = new Inquilino
{
IdInquilino = reader.GetInt32(4),
Nombre = reader.GetString(5),
Apellido = reader.GetString(6),
},
Inmueble = new Inmueble
{
IdInmueble = reader.GetInt32(7),
Direccion = reader.GetString(8),
}
},
};
res.Add(entidad);
}
connection.Close();
}
}
return(res);
}
public Pago ObtenerPorId(int id)
{
Pago entidad = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdPago, Importe, FechaDePago, p.IdContrato, c.IdInquilino, i.Nombre, i.Apellido, n.IdInmueble, n.Direccion" +
$" FROM Pagos p INNER JOIN Contratos c ON p.IdContrato = c.IdContrato INNER JOIN Inquilinos i ON i.IdInquilino = c.IdInquilino " +
"INNER JOIN Inmuebles n ON n.IdInmueble = c.IdInmueble" +
$" WHERE IdPago=@IdPago";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@IdPago", SqlDbType.Int).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
entidad = new Pago
{
IdPago = reader.GetInt32(0),
Importe = reader.GetInt32(1),
FechaDePago = reader.GetDateTime(2),
IdContrato = reader.GetInt32(3),
Contrato = new Contrato
{
IdContrato = reader.GetInt32(3),
IdInquilino = reader.GetInt32(4),
Inquilino = new Inquilino
{
IdInquilino = reader.GetInt32(4),
Nombre = reader.GetString(5),
Apellido = reader.GetString(6),
},
Inmueble = new Inmueble
{
IdInmueble = reader.GetInt32(7),
Direccion = reader.GetString(8),
}
}
};
}
connection.Close();
}
}
return(entidad);
}
public List <Pago> ObtenerTodos()
{
var res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT p.id,nroPago, fecha,p.importe,idContrato, c.fechaInicio, " +
"c.fechaFin,i.id,i.apellido,i.nombre FROM Pagos p ,Contratos c, Inquilinos i " +
"Where (c.idInquilino= i.id) and (c.id= p.idContrato) ";
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
var e = new Pago
{
Id = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
FechaP = reader.GetDateTime(2),
Importe = reader.GetDecimal(3),
IdContrato = reader.GetInt32(4),
ContratoPago = new Contrato
{
Id = reader.GetInt32(4),
FechaInicio = reader.GetDateTime(5),
FechaFin = reader.GetDateTime(6),
InquilinoContrato = new Inquilino
{
Id = reader.GetInt32(7),
Apellido = reader.GetString(8),
Nombre = reader.GetString(9),
},
}
};
res.Add(e);
}
connection.Close();
}
return(res);
}
}
public int Modificacion(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Pagos SET IdAlquiler='{p.IdAlquiler}', NumPago='{p.NumPago}', Importe='{p.Importe}', FechaPago='{p.FechaPago}'" +
$"WHERE IdPago = {p.IdPago}";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Modificacion(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Pagos SET NumeroPago={p.NumeroPago}, Fecha='{p.Fecha}', Importe={p.Importe}, Contrato='{p.Contrato.Id}'" +
$"WHERE Id = {p.Id}";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public IList <Pago> ObtenerPagosPorContrato(int id)
{
IList <Pago> res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT IdPago, FechaPago, NumeroPago, Importe, p.IdContrato, c.IdInquilino, i.Nombre , i.Apellido" +
" FROM Pagos p INNER JOIN Contratos c ON p.IdContrato = c.IdContrato" +
" INNER JOIN Inquilinos i ON c.IdInquilino = i.IdInquilino" +
" WHERE c.IdContrato = @id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago p = new Pago
{
IdPago = reader.GetInt32(0),
FechaPago = reader.GetDateTime(1),
NumeroPago = reader.GetInt32(2),
Importe = reader.GetDecimal(3),
IdContrato = reader.GetInt32(4),
contrato = new Contrato
{
IdInquilino = reader.GetInt32(5),
inquilino = new Inquilino
{
Nombre = reader.GetString(6),
Apellido = reader.GetString(7),
},
}
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public int Alta(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Pagos (IdAlquiler, NumPago, Importe, FechaPago) " +
$"VALUES ('{p.IdAlquiler}', '{p.NumPago}','{p.Importe}','{p.FechaPago}')";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
command.CommandText = "SELECT SCOPE_IDENTITY()";
var id = command.ExecuteScalar();
p.IdPago = Convert.ToInt32(id);
connection.Close();
}
}
return(res);
}
public int Alta(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Pagos (NumeroPago, Fecha, Contrato) " +
$"VALUES ('{p.NumeroPago}', SYSDATETIME(), {p.Contrato.Id})";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
command.CommandText = "SELECT SCOPE_IDENTITY()";
var id = command.ExecuteScalar();
p.Id = Convert.ToInt32(id);
connection.Close();
}
}
return(res);
}
public int Modificacion(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Pagos SET FechaPago=@fechaPago, NumeroPago=@numeroPago, Importe=@importe " +
$"WHERE IdPago = @id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@fechaPago", p.FechaPago);
command.Parameters.AddWithValue("@numeroPago", p.NumeroPago);
command.Parameters.AddWithValue("@importe", p.Importe);
command.Parameters.AddWithValue("@id", p.IdPago);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public IList <Pago> BuscarPorContrato(int id)
{
List <Pago> res = new List <Pago>();
Pago entidad = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT p.Id,p.NroPago, p.FechaPago,p.Importe,p.ContratoId,c.InquilinoId,InmuebleId" +
$" FROM Pago p INNER JOIN Contrato c ON p.ContratoId = c.Id" +
$" WHERE ContratoId=@id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
entidad = new Pago
{
Id = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
FechaPago = reader.GetDateTime(2),
Importe = reader.GetDecimal(3),
ContratoId = reader.GetInt32(4),
Contrato = new Contrato
{
Id = reader.GetInt32(4),
InquilinoId = reader.GetInt32(5),
InmuebleId = reader.GetInt32(6),
}
};
res.Add(entidad);
}
connection.Close();
}
}
return(res);
}
public int Modificacion(Pago entidad)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "UPDATE Pagos SET " +
"FechaDePago=@FechaDePago, Importe=@Importe, IdContrato=@IdContrato " +
"WHERE IdPago = @IdPago";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@FechaDePago", entidad.FechaDePago);
command.Parameters.AddWithValue("@Importe", entidad.Importe);
command.Parameters.AddWithValue("@IdContrato", entidad.IdContrato);
command.Parameters.AddWithValue("@IdPago", entidad.IdPago);
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Alta(Pago entidad)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Pagos (FechaDePago, Importe, IdContrato) " +
$"VALUES (@FechaDePago, @Importe, @IdContrato);" +
"SELECT SCOPE_IDENTITY();"; //devuelve el id insertado (LAST_INSERT_ID para mysql)
using (var command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@FechaDePago", entidad.FechaDePago);
command.Parameters.AddWithValue("@Importe", entidad.Importe);
command.Parameters.AddWithValue("@IdContrato", entidad.IdContrato);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
entidad.IdPago = res;
connection.Close();
}
}
return(res);
}
public int Modificacion(Pago entidad)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "UPDATE Pago SET " +
"NroPago=nroPago,FechaPago=@fechaPago, Importe=@importe " +
"WHERE Id = @id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@nroPago", entidad.NroPago);
command.Parameters.AddWithValue("@fechaPago", entidad.FechaPago);
command.Parameters.AddWithValue("@importe", entidad.Importe);
command.Parameters.AddWithValue("@id", entidad.Id);
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Alta(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Pagos (FechaPago, NumeroPago, Importe ,IdContrato) " +
$"VALUES (@fechaPago, @numeroPago, @importe ,@idContrato);" +
$"SELECT SCOPE_IDENTITY();"; //devuelve el id insertado
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@fechaPago", p.FechaPago);
command.Parameters.AddWithValue("@numeroPago", p.NumeroPago);
command.Parameters.AddWithValue("@importe", p.Importe);
command.Parameters.AddWithValue("@idContrato", p.IdContrato);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
p.IdPago = res;
connection.Close();
}
}
return(res);
}
public IList <Pago> ObtenerTodos()
{
IList <Pago> res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT p.Id,p.NroPago p.FechaPago,p.Importe,p.ContratoId,c.InquilinoId,c.InmuebleId" +
" FROM Pago p INNER JOIN Contrato c ON p.ContratoId = c.Id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago entidad = new Pago
{
Id = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
FechaPago = reader.GetDateTime(2),
Importe = reader.GetDecimal(3),
ContratoId = reader.GetInt32(4),
Contrato = new Contrato
{
Id = reader.GetInt32(5),
InquilinoId = reader.GetInt32(6),
InmuebleId = reader.GetInt32(7),
}
};
res.Add(entidad);
}
connection.Close();
}
}
return(res);
}
public int Alta(Pago p)
{
throw new NotImplementedException();
}