private void HotelMayorCantidadDiasFueraServicio()
{
List <HotelesReservasCanceladas> hoteles = new List <HotelesReservasCanceladas>();
var query = " SELECT TOP 5 hotel_id, SUM(DATEDIFF(d, hoteles_bajas.fecha_desde, hoteles_bajas.fecha_hasta)+1) AS cantidad_dias ";
query += " FROM [LOS_NORMALIZADORES].[hoteles] ";
query += " RIGHT JOIN [LOS_NORMALIZADORES].[hoteles_bajas] ON hoteles.id = hoteles_bajas.hotel_id ";
query += " WHERE fecha_desde BETWEEN '" + fecha1 + "' AND '" + fecha2 + "' ";
query += " GROUP BY hotel_id";
query += " ORDER BY cantidad_dias DESC ";
SqlCommand command = new SqlCommand(query, ConnectionManager.getInstance().getConnection());
using (SqlDataReader result = command.ExecuteReader())
{
while (result.Read())
{
HotelesReservasCanceladas reserva_cancelada = new HotelesReservasCanceladas();
Hotel hotel_temporal = new Hotel();
hotel_temporal.id = Convert.ToInt32(result["hotel_id"].ToString());
reserva_cancelada.hotel = hotel_temporal;
reserva_cancelada.cantidad = Convert.ToInt32(result["cantidad_dias"]);
hoteles.Add(reserva_cancelada);
}
}
hoteles.ForEach(h => h.hotel = EntityManager.getEntityManager().findById <Hotel>(h.hotel.id));
//List<HotelesReservasCanceladas> hoteles = EntityManager.getEntityManager().findAll<HotelesReservasCanceladas>();
dataGridView1.DataSource = hoteles;
}
private void HotelMayorCantidadReservasCanceladas()
{
List <HotelesReservasCanceladas> hoteles = new List <HotelesReservasCanceladas>();
var query = "SELECT TOP 5 hotel_id, COUNT(*) AS cantidad ";
query += "FROM [LOS_NORMALIZADORES].[reservas] ";
query += "INNER JOIN [LOS_NORMALIZADORES].[reservas_habitaciones] ON [reservas].id = reservas_habitaciones.reserva_id ";
query += "INNER JOIN [LOS_NORMALIZADORES].[habitaciones] ON [reservas_habitaciones].habitacion_id = habitaciones.id ";
query += "INNER JOIN [LOS_NORMALIZADORES].[hoteles] ON [hoteles].id = [habitaciones].hotel_id ";
query += "INNER JOIN [LOS_NORMALIZADORES].[reservas_canceladas] ON [reservas].id = reservas_canceladas.reserva_id ";
query += "WHERE (reserva_estado = 3 OR reserva_estado = 4 OR reserva_estado = 5) ";
query += "AND [reservas_canceladas].[fecha] BETWEEN '" + fecha1.ToShortDateString() + "' AND '" + fecha2.ToShortDateString() + "' ";
query += "GROUP BY hotel_id ORDER BY COUNT(*) DESC";
SqlCommand command = new SqlCommand(query, ConnectionManager.getInstance().getConnection());
using (SqlDataReader result = command.ExecuteReader())
{
while (result.Read())
{
HotelesReservasCanceladas reserva_cancelada = new HotelesReservasCanceladas();
Hotel hotel_temporal = new Hotel();
hotel_temporal.id = Convert.ToInt32(result["hotel_id"].ToString());
reserva_cancelada.hotel = hotel_temporal;
reserva_cancelada.cantidad = Convert.ToInt32(result["cantidad"]);
hoteles.Add(reserva_cancelada);
}
}
hoteles.ForEach(h => h.hotel = EntityManager.getEntityManager().findById <Hotel>(h.hotel.id));
//List<HotelesReservasCanceladas> hoteles = EntityManager.getEntityManager().findAll<HotelesReservasCanceladas>();
dataGridView1.DataSource = hoteles;
}
