public List<HorarioCancha> listar(Integer idCancha, Integer distrito, String dia, String hora) throws BaseExcepcion {
System.out.println("CanchaDAO: obtener(String nombre)");
List<HorarioCancha> lista = new ArrayList<HorarioCancha>();
HorarioCancha vo = null;
Cancha cancha = null;
Local local = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// String query = "select b.idHorario, b.dia,b.hora,a.idCancha,a.nombre,a.tarifadiurna,a.tarifanocturna, exists ( select 1 from tb_alquilercancha ac where ac.idhorario = b.idhorario ) as alquilado "
// + " from tb_cancha a, tb_horariocancha b, tb_local c where a.idCancha = b.idCancha and a.idLocal = c.idLocal ";
String query = "select b.idHorario, b.dia,b.hora,a.idCancha,a.nombre,a.tarifadiurna,a.tarifanocturna, exists ( select 1 from tb_alquilercancha ac where ac.idhorario = b.idhorario AND ac.fecha = '" + dia + " ') as alquilado, c.nombre as localNombre "
+ " from tb_cancha a, tb_horariocancha b, tb_local c where a.idCancha = b.idCancha and a.idLocal = c.idLocal ";
if (idCancha != null) {
query = query + " and a.idCancha = " + idCancha;
}
if (distrito != null) {
query = query + " and c.distrito = " + distrito;
}
//if (dia != null) {
query = query + " and b.dia = DATE_FORMAT('" + dia + "','%w')";
//}
if (hora != null) {
query = query + " and b.hora = '" + hora + "'";
}
System.out.println("CanchaDAO: query=" + query);
con = ConexionBD.obtenerConexion();
stmt = con.prepareStatement(query);
rs = stmt.executeQuery();
while (rs.next()) {
vo = new HorarioCancha();
vo.setId(rs.getInt(1));
vo.setDia(rs.getString(2));
vo.setHora(rs.getString(3));
vo.setAlquilado(rs.getInt("alquilado") == 1 ? true : false);
cancha = new Cancha();
cancha.setId(rs.getInt(4));
cancha.setNombre(rs.getString(5));
cancha.setTarifaDiurna(rs.getDouble(6));
cancha.setTarifaNocturna(rs.getDouble(7));
local = new Local();
local.setNombre(rs.getString("localNombre"));
cancha.setLocal(local);
vo.setCancha(cancha);
lista.add(vo);
}