private void destinosPasajesButton_Click(object sender, EventArgs e) { String title = "Destinos más comprados"; String rango = this.getRango(); String query = ""; query += "select top 5 d.descripcion as 'Destino', count(1) as 'Ventas'"; query += " from BIEN_MIGRADO_RAFA.Compra_Pasaje"; query += " inner join BIEN_MIGRADO_RAFA.Pasaje p on pasaje_id = p.id"; query += " inner join BIEN_MIGRADO_RAFA.Viaje v on p.viaje_id = v.id"; query += " inner join BIEN_MIGRADO_RAFA.Ruta r on v.ruta_id = r.id"; query += " inner join BIEN_MIGRADO_RAFA.Ciudad d on r.ciudad_destino_id = d.id"; query += " inner join BIEN_MIGRADO_RAFA.Compra c on compra_id = c.id"; query += " WHERE (c.fecha_compra " + rango + ")"; query += " group by d.descripcion"; query += " order by count(1) DESC"; DataTable table = this.buildDatatable(query); TableDialog dialog = new TableDialog(table, title); dialog.ShowDialog(); }
private void clientesPuntosButton_Click(object sender, EventArgs e) { String title = "Clientes con más puntos acumulados"; String rango = this.getRango(); String queryPasajes = ""; queryPasajes += "(select sum(precio) from BIEN_MIGRADO_RAFA.Pasaje p "; queryPasajes += "inner join BIEN_MIGRADO_RAFA.Compra c on c.cliente_id = cl.id "; queryPasajes += "inner join BIEN_MIGRADO_RAFA.Compra_Pasaje cp on c.id = cp.compra_id and cp.pasaje_id = p.id "; queryPasajes += " WHERE (c.fecha_compra " + rango + ")"; queryPasajes += ") "; String queryPaquetes = ""; queryPaquetes += "(select sum(precio) from BIEN_MIGRADO_RAFA.Paquete p "; queryPaquetes += "inner join BIEN_MIGRADO_RAFA.Compra c on c.cliente_id = cl.id "; queryPaquetes += "inner join BIEN_MIGRADO_RAFA.Compra_Paquete cp on c.id = cp.compra_id and cp.paquete_id = p.id "; queryPaquetes += " WHERE (c.fecha_compra " + rango + ")"; queryPaquetes += ") "; String queryCanjes = ""; queryCanjes += "(select sum(c.cantidad * cat.costo) from BIEN_MIGRADO_RAFA.Canje c "; queryCanjes += "inner join BIEN_MIGRADO_RAFA.Catalogo cat on c.catalogo_id = cat.id "; queryCanjes += "where c.cliente_id = cl.id "; queryCanjes += "and (c.fecha " + rango + ") "; queryCanjes += ") "; String queryPuntos = queryPasajes + " + " + queryPaquetes + " - " + queryCanjes; String query = ""; query += "select top 5 cl.nombre + ' ' + cl.apellido as 'Cliente', " + queryPuntos + " as 'Puntos Acumulados' "; query += " from BIEN_MIGRADO_RAFA.Cliente cl"; query += " order by [Puntos Acumulados] DESC"; DataTable table = this.buildDatatable(query); TableDialog dialog = new TableDialog(table, title); dialog.ShowDialog(); }
private void destinosVaciosButton_Click(object sender, EventArgs e) { String title = "Destinos más comprados"; String rango = this.getRango(); String query = "SELECT top 5 c.descripcion 'Ciudad destino', " + "(SELECT count(*) FROM BIEN_MIGRADO_RAFA.Ciudad d " + "JOIN BIEN_MIGRADO_RAFA.Ruta r ON r.ciudad_destino_id = pv.destinoId " + "JOIN BIEN_MIGRADO_RAFA.Viaje v ON v.ruta_id = r.id " + "JOIN BIEN_MIGRADO_RAFA.Aeronave a ON v.aeronave_id = a.id " + "JOIN BIEN_MIGRADO_RAFA.Butaca b ON b.aeronave_id = a.id " + "where d.id = pv.destinoId AND v.fecha_salida " + rango + ") 'Butacas totales', " + "pv.viajesTotales 'Butacas vendidas', " + "((SELECT count(*) FROM BIEN_MIGRADO_RAFA.Ciudad d " + "JOIN BIEN_MIGRADO_RAFA.Ruta r ON r.ciudad_destino_id = pv.destinoId " + "JOIN BIEN_MIGRADO_RAFA.Viaje v ON v.ruta_id = r.id " + "JOIN BIEN_MIGRADO_RAFA.Aeronave a ON v.aeronave_id = a.id " + "JOIN BIEN_MIGRADO_RAFA.Butaca b ON b.aeronave_id = a.id " + "where d.id = pv.destinoId AND v.fecha_salida " + rango + ") - pv.viajesTotales) 'Butacas libres', " + "1 - CAST(pv.viajesTotales AS DECIMAL) / (SELECT count(*) FROM BIEN_MIGRADO_RAFA.Ciudad d " + "JOIN BIEN_MIGRADO_RAFA.Ruta r ON r.ciudad_destino_id = pv.destinoId " + "JOIN BIEN_MIGRADO_RAFA.Viaje v ON v.ruta_id = r.id " + "JOIN BIEN_MIGRADO_RAFA.Aeronave a ON v.aeronave_id = a.id " + "JOIN BIEN_MIGRADO_RAFA.Butaca b ON b.aeronave_id = a.id " + "where d.id = pv.destinoId AND v.fecha_salida " + rango + ") 'Porcentaje butacas libres' " + "FROM (SELECT COUNT(*) viajesTotales, d.id destinoId FROM BIEN_MIGRADO_RAFA.Ciudad d " + "JOIN BIEN_MIGRADO_RAFA.Ruta r ON r.ciudad_destino_id = d.id " + "JOIN BIEN_MIGRADO_RAFA.Viaje v ON v.ruta_id = r.id " + "JOIN BIEN_MIGRADO_RAFA.Aeronave a ON v.aeronave_id = a.id " + "JOIN BIEN_MIGRADO_RAFA.Pasaje p ON p.viaje_id = v.id " + "WHERE v.fecha_salida " + rango + " group by d.id) pv " + "JOIN BIEN_MIGRADO_RAFA.Ciudad c ON c.id = pv.destinoId " + "order by 'Porcentaje butacas libres' DESC"; DataTable table = this.buildDatatable(query); TableDialog dialog = new TableDialog(table, title); dialog.ShowDialog(); }
private void aeronaveBajaButton_Click(object sender, EventArgs e) { String title = "Aeronaves con más días fuera de servicio"; String rango = this.getRango(); String query = ""; query += "select top 5 a.matricula as 'Matrícula', sum(DATEDIFF(DAY, b.fecha_baja, b.fecha_reinicio)) as 'Días fuera de servicio'"; query += " from BIEN_MIGRADO_RAFA.Aeronave a"; query += " inner join BIEN_MIGRADO_RAFA.Baja_Aeronave b on a.id = b.aeronave_id"; query += " WHERE (b.fecha_baja " + rango + ") AND b.tipo_baja_id != 2"; query += " group by a.matricula"; query += " order by count(1) DESC"; DataTable table = this.buildDatatable(query); TableDialog dialog = new TableDialog(table, title); dialog.ShowDialog(); }