public Pago ObtenerPorId(int id)
{
Pago p = null;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT Pago.Id, NroPago, Pago.ContratoId, Contrato.InmuebleId, " +
$"Inmuebles.Direccion, Contrato.InquilinoId, Inquilinos.Nombre, Inquilinos.Apellido, " +
$"Fecha, Pago.Importe " +
$"FROM Pago INNER JOIN Contrato ON (Contrato.Id=Pago.ContratoId) " +
$"INNER JOIN Inmuebles ON (Inmuebles.Id = Contrato.InmuebleId) " +
$"INNER JOIN Inquilinos ON (Inquilinos.Id = Contrato.InquilinoId) " +
$"WHERE Pago.Id=@id";
using (var 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 Pago
{
Id = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
ContratoId = reader.GetInt32(2),
Contrato = new Contrato
{
Id = reader.GetInt32(2),
Inmueble = new Inmueble
{
Id = reader.GetInt32(3),
Direccion = reader.GetString(4),
},
Inquilino = new Inquilino
{
Id = reader.GetInt32(5),
Nombre = reader.GetString(6),
Apellido = reader.GetString(7)
}
},
Fecha = reader.GetDateTime(8),
Importe = reader.GetDecimal(9)
};
}
connection.Close();
}
}
return(p);
}
public IList <Pago> ObtenerPorIdInquilino(int id)
{
IList <Pago> res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT id_Pago,p.id_Contrato,importe,fechaPago, " +
$" inqui.nombreI, inqui.apellidoI, i.direccionInm " +
$" FROM Pago p INNER JOIN Contrato c ON p.id_Contrato=c.id_Contrato " +
$" INNER JOIN Inmueble i ON c.id_Inmueble= i.id_Inmueble " +
$" INNER JOIN Inquilino inqui ON c.id_Inquilino= inqui.id_Inquilino " +
$" WHERE c.id_Inquilino=@idI ";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@idI", id);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago p = new Pago
{
Id_Pago = reader.GetInt32(0),
Id_Contrato = reader.GetInt32(1),
Importe = reader.GetDecimal(2),
FechaPago = reader.GetDateTime(3),
Contrato = new Contrato
{
Id_Contrato = reader.GetInt32(1),
Inquilino = new Inquilino
{
NombreI = reader.GetString(4),
ApellidoI = reader.GetString(5),
},
Inmueble = new Inmueble
{
DireccionInm = reader.GetString(6),
}
}
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public IList <Pago> ObtenerTodos()
{
IList <Pago> res = new List <Pago>();
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT Pago.Id, NroPago, Pago.ContratoId, Contrato.InmuebleId, Inmueble.Direccion, Contrato.InquilinoId, Inquilino.Nombre, Inquilino.Apellido, Fecha, Pago.Importe FROM Pago INNER JOIN Contrato ON (Contrato.Id=Pago.Id) INNER JOIN Inmueble ON (Inmueble.Id = Contrato.Id) INNER JOIN Inquilino ON (Inquilino.Id = Contrato.Id)";
using (var command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago i = new Pago
{
Id = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
Contrato = new Contrato
{
Id = reader.GetInt32(2),
Inmueble = new Inmueble
{
Id = reader.GetInt32(3),
Direccion = reader.GetString(4),
},
Inquilino = new Inquilino
{
Id = reader.GetInt32(5),
Nombre = reader.GetString(6),
Apellido = reader.GetString(7)
}
},
Fecha = reader.GetDateTime(8),
Importe = reader.GetDecimal(9)
};
res.Add(i);
}
connection.Close();
}
}
return(res);
}
public int Modificacion(Pago pago)
{
int res = -1;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"UPDATE Pago SET Importe=@importe " +
$"WHERE Id = {pago.Id}";
using (var command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@importe", MySqlDbType.Decimal).Value = pago.Importe;
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 Pago(id_Contrato,importe,fechaPago) " +
$" VALUES (@id_C,@importe,@fec); " +
$" SELECT SCOPE_IDENTITY();";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@importe", p.Importe);
command.Parameters.AddWithValue("@fec", p.FechaPago);
command.Parameters.AddWithValue("@id_C", p.Id_Contrato);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
p.Id_Pago = res;
connection.Close();
}
}
return(res);
}
public int Modificacion(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Pago SET id_Contrato=@id_C, importe=@importe,fechaPago=@fec " +
$" WHERE id_Pago=@id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@importe", p.Importe);
command.Parameters.AddWithValue("@fec", p.FechaPago);
command.Parameters.AddWithValue("@id_C", p.Id_Contrato);
command.Parameters.AddWithValue("@id", p.Id_Pago);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Alta(Pago p)
{
int res = -1;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"INSERT INTO Pago (NroPago, Fecha, Importe, ContratoId) " +
$"VALUES (@nroPago, @fecha, @importe, @contratoId);" +
$"SELECT LAST_INSERT_ID();";;
using (var command = new MySqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@nroPago", p.NroPago);
command.Parameters.AddWithValue("@fecha", p.Fecha);
command.Parameters.AddWithValue("@importe", p.Importe);
command.Parameters.AddWithValue("@contratoId", p.ContratoId);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
p.Id = res;
connection.Close();
}
}
return(res);
}
public IList <Pago> ObtenerTodos()
{
IList <Pago> res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdPago, NroPago, FechaPago, Importe, c.IdContrato, c.IdInmueble, c.IdInquilino " +
$"FROM Pagos p " +
$"INNER JOIN Contratos c ON p.IdContrato = c.IdContrato " +
$"INNER JOIN Inmuebles i ON c.IdInmueble = i.IdInmueble " +
$"INNER JOIN inquilinos l ON c.IdInquilino = l.IdInquilino;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago p = new Pago
{
IdPago = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
FechaPago = reader.GetDateTime(2),
Importe = reader.GetInt32(3),
IdContrato = reader.GetInt32(4),
};
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 (NroPago, FechaPago, Importe, IdContrato) " +
$"VALUES (@nroPago, @fechaPago, @importe, @idContrato);" +
"SELECT SCOPE_IDENTITY();"; //devuelve el id insertado (LAST_INSERT_ID para mysql)
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nroPago", p.NroPago);
command.Parameters.AddWithValue("@fechaPago", p.FechaPago);
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 Pago ObtenerPagoPorNroYContratoId(int contratoId, int nroPago)
{
Pago p = null;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT pago.Id, NroPago, Pago.ContratoId, contrato.InmuebleId, Contrato.InquilinoId, " +
$"Contrato.FechaInicio, Contrato.FechaFin, Contrato.Importe, Contrato.NombreCompletoGarante, " +
$"Contrato.TelefonoGarante, Contrato.EmailGarante, Contrato.DniGarante, Fecha, Pago.Importe, " +
$"propietarios.Id, propietarios.Apellido, inquilinos.Id, inquilinos.Apellido, Inmuebles.Direccion " +
$"FROM Pago INNER JOIN Contrato ON (Contrato.Id=Pago.ContratoId) " +
$"INNER JOIN Inmuebles ON (Inmuebles.Id = Contrato.InmuebleId) " +
$"INNER JOIN Inquilinos ON (Inquilinos.Id = Contrato.InquilinoId) " +
$"INNER JOIN propietarios ON (propietarios.Id = inmuebles.PropietarioId) " +
$"WHERE Pago.ContratoId=@contratoId AND Pago.NroPago=@nroPago";
using (var command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@contratoId", MySqlDbType.Int32).Value = contratoId;
command.Parameters.Add("@nroPago", MySqlDbType.Int32).Value = nroPago;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
p = new Pago
{
Id = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
ContratoId = reader.GetInt32(2),
Contrato = new Contrato
{
Id = reader.GetInt32(2),
InmuebleId = reader.GetInt32(3),
InquilinoId = reader.GetInt32(4),
Inmueble = new Inmueble
{
Id = reader.GetInt32(3),
Direccion = reader.GetString(18),
},
Propietario = new Propietario
{
Id = reader.GetInt32(14),
Apellido = reader.GetString(15),
},
Inquilino = new Inquilino
{
Id = reader.GetInt32(16),
Apellido = reader.GetString(17)
},
FechaInicio = reader.GetDateTime(5),
FechaFin = reader.GetDateTime(6),
Importe = reader.GetDecimal(7),
NombreCompletoGarante = reader.GetString(8),
TelefonoGarante = reader.GetString(9),
EmailGarante = reader.GetString(10),
DniGarante = reader.GetString(11),
},
Fecha = reader.GetDateTime(12),
Importe = reader.GetDecimal(13)
};
}
connection.Close();
}
}
return(p);
}