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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        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);
        }
Esempio n. 8
0
        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);
        }
Esempio n. 9
0
        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);
        }