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);
        }
Esempio n. 3
0
        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);
            }
        }
Esempio n. 4
0
        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);
        }
Esempio n. 7
0
        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);
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        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);
        }
Esempio n. 13
0
        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);
        }
Esempio n. 14
0
        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);
        }
Esempio n. 15
0
        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);
        }
Esempio n. 16
0
 public int Alta(Pago p)
 {
     throw new NotImplementedException();
 }