/*******************************************************************************
* Copyright (c) 2011 Michel DAVID mimah35-at-gmail.com
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
******************************************************************************/
package fr.gotorennes.persistence;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import android.app.ProgressDialog;
import android.content.Context;
import android.database.Cursor;
import fr.gotorennes.domain.Arret;
import fr.gotorennes.domain.Circuit;
import fr.gotorennes.domain.Ligne;
import fr.gotorennes.domain.SensCirculation;
import fr.gotorennes.domain.Station;
import fr.gotorennes.domain.StationGroup;
import fr.gotorennes.util.JoursUtils;
public class BusDao {
private static BusDao instance;
private BusDatabase database;
private BusDao(Context context, ProgressDialog progress) {
database = new BusDatabase(context);
database.initOrUpdate(progress, false);
}
public void update(ProgressDialog progress) {
database.initOrUpdate(progress, true);
}
public static synchronized BusDao getInstance(Context context, ProgressDialog progress) {
if (instance == null) {
instance = new BusDao(context, progress);
}
return instance;
}
public List<String> getTypesLigne() {
Cursor cursor = database.query("SELECT distinct type FROM LIGNE", new String[] {});
List<String> types = new ArrayList<String>();
if (cursor != null) {
do {
types.add(cursor.getString(0));
} while (cursor.moveToNext());
cursor.close();
}
return types;
}
public List<Ligne> getLignes(String type) {
Cursor cursor = database.query("SELECT * FROM LIGNE WHERE type = ?", new String[] { type });
List<Ligne> lines = new ArrayList<Ligne>();
if (cursor != null) {
do {
lines.add(new Ligne(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return lines;
}
public List<Ligne> getLignes() {
Cursor cursor = database.query("SELECT * FROM LIGNE", new String[] {});
List<Ligne> lines = new ArrayList<Ligne>();
if (cursor != null) {
do {
lines.add(new Ligne(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return lines;
}
public List<SensCirculation> getCircuits(Ligne ligne) {
Cursor cursor = database.query("select group_concat(nom, ', ') nom, idLigne, sens from CIRCUIT where idLigne = ? group by idLigne, sens order by sens", new String[] { String.valueOf(ligne.id) });
List<SensCirculation> sens = new ArrayList<SensCirculation>();
if (cursor != null) {
do {
sens.add(new SensCirculation(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return sens;
}
public List<StationGroup> getStations() {
Cursor cursor = database.query(
"SELECT nom, commune, avg(latitude) latitude, avg(longitude) longitude, min(accessible) accessible, min(banc) banc, min(eclairage) eclairage, min(couvert) couvert FROM STATION GROUP BY nom, commune ORDER BY nom",
new String[] { });
List<StationGroup> stations = new ArrayList<StationGroup>();
if (cursor != null) {
do {
stations.add(new StationGroup(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public List<Station> getStations(Ligne ligne, SensCirculation sens) {
Cursor cursor = database.query(
"SELECT distinct STATION.* FROM STATION_CIRCUIT, STATION, CIRCUIT WHERE STATION_CIRCUIT.idStation = STATION._id AND STATION_CIRCUIT.idCircuit = CIRCUIT._id AND CIRCUIT.idLigne = ? AND CIRCUIT.sens = ? ORDER BY sequence",
new String[] { String.valueOf(ligne.id), String.valueOf(sens.sens) });
List<Station> stations = new ArrayList<Station>();
if (cursor != null) {
do {
stations.add(new Station(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public List<Station> getStations(Ligne ligne) {
Cursor cursor = database.query(
"SELECT STATION.* FROM CIRCUIT, STATION_CIRCUIT, STATION WHERE STATION_CIRCUIT.idStation = STATION._id AND STATION_CIRCUIT.idCircuit = CIRCUIT._id AND CIRCUIT.idLigne = ?",
new String[] { String.valueOf(ligne.id) });
List<Station> stations = new ArrayList<Station>();
if (cursor != null) {
do {
stations.add(new Station(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public Station getStation(long idStation) {
Cursor cursor = database.query("select * from STATION where _id = ? LIMIT 1", new String[] { String.valueOf(idStation) });
Station station = null;
if (cursor != null) {
station = new Station(cursor);
cursor.close();
}
return station;
}
public Station getStation(String codeStation) {
Cursor cursor = database.query("select * from STATION where code = ? LIMIT 1", new String[] { codeStation });
Station station = null;
if (cursor != null) {
station = new Station(cursor);
cursor.close();
}
return station;
}
public Station getStation(long idCircuit, String nom) {
String request = "select STATION.* from STATION_CIRCUIT, STATION where STATION_CIRCUIT.idCircuit = ? AND STATION_CIRCUIT.idStation = station._id AND station.nom = ? limit 1";
Cursor cursor = database.query(request, new String[] { String.valueOf(idCircuit), nom });
Station station = null;
if (cursor != null) {
station = new Station(cursor);
cursor.close();
}
return station;
}
public StationGroup getStationGroup(Station station) {
String request = "select nom, commune, avg(latitude) latitude, avg(longitude) longitude, min(accessible) accessible, min(banc) banc, min(eclairage) eclairage, min(couvert) couvert from STATION where station.nom = ? and station.commune = ? group by nom, commune";
Cursor cursor = database.query(request, new String[] { station.nom, station.commune });
StationGroup stationGroup = null;
if (cursor != null) {
stationGroup = new StationGroup(cursor);
cursor.close();
}
return stationGroup;
}
public List<StationGroup> getStationsProches(double latitude, double longitude) {
String request = "select nom, commune, avg(latitude) latitude, avg(longitude) longitude, min(accessible) accessible, min(banc) banc, min(eclairage) eclairage, min(couvert) couvert from STATION group by nom, commune order by (latitude - ?) * (latitude - ?)+ (longitude - ?) * (longitude - ?) limit 4";
Cursor cursor = database.query(request, new String[] { String.valueOf(latitude), String.valueOf(latitude), String.valueOf(longitude), String.valueOf(longitude) });
List<StationGroup> stations = new ArrayList<StationGroup>();
if (cursor != null) {
do {
stations.add(new StationGroup(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public List<Station> getStations(double latitudeMin, double latitudeMax, double longitudeMax, double longitudeMin) {
String request = "select * from STATION where latitude >= ? and latitude <= ? and longitude >= ? and longitude <= ? LIMIT 50";
Cursor cursor = database.query(request,
new String[] { String.valueOf(latitudeMin), String.valueOf(latitudeMax), String.valueOf(longitudeMin), String.valueOf(longitudeMax) });
List<Station> stations = new ArrayList<Station>();
if (cursor != null) {
do {
stations.add(new Station(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public List<Ligne> getLignes(Station station) {
String request = "select distinct LIGNE.* from STATION_CIRCUIT, CIRCUIT, LIGNE where STATION_CIRCUIT.idCircuit = CIRCUIT._id AND CIRCUIT.idLigne = LIGNE._id AND STATION_CIRCUIT.idStation = ? order by LIGNE.code";
Cursor cursor = database.query(request, new String[] { String.valueOf(station.id) });
List<Ligne> lignes = new ArrayList<Ligne>();
if (cursor != null) {
do {
lignes.add(new Ligne(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return lignes;
}
public List<Station> getStations(StationGroup station) {
String request = "select * from STATION where nom = ? AND commune = ?";
Cursor cursor = database.query(request, new String[] { station.nom, station.commune });
List<Station> stations = new ArrayList<Station>();
if (cursor != null) {
do {
stations.add(new Station(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public List<Circuit> getCircuits(Station station) {
String request = "select CIRCUIT.* from STATION_CIRCUIT, CIRCUIT where STATION_CIRCUIT.idCircuit = CIRCUIT._id AND STATION_CIRCUIT.idStation = ? order by idLigne, nom";
Cursor cursor = database.query(request, new String[] { String.valueOf(station.id) });
List<Circuit> circuits = new ArrayList<Circuit>();
if (cursor != null) {
do {
circuits.add(new Circuit(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return circuits;
}
public Circuit getCircuit(long id) {
String request = "select * from CIRCUIT where _id = ? LIMIT 1";
Cursor cursor = database.query(request, new String[] { String.valueOf(id) });
Circuit circuit = null;
if (cursor != null) {
circuit = new Circuit(cursor);
cursor.close();
}
return circuit;
}
public Arret getProchainPassage(long idStation, long idLigne, boolean nuit) {
return getPassage(idStation, idLigne, Calendar.getInstance(), nuit);
}
public Arret getPassage(long idStation, long idLigne, Calendar calendrier, boolean nuit) {
String requete = "SELECT ARRET.* FROM ARRET, TRAJET, CIRCUIT WHERE " +
"ARRET.idStation = ? " +
"AND ARRET.idTrajet = TRAJET._id " +
"AND TRAJET.idCircuit = CIRCUIT._id " +
"AND CIRCUIT.idLigne = ? " +
"AND TRAJET.calendrier & ? > 0 " +
"AND ARRET.depart > ? " +
"AND ARRET.sequence < TRAJET.nbArrets " +
"ORDER BY depart LIMIT 1";
Cursor cursor = database.query(requete, new String[] { String.valueOf(idStation), String.valueOf(idLigne), JoursUtils.getCalendrier(calendrier, nuit), Arret.getTimeInMinutes(calendrier, nuit) });
Arret arret = null;
if (cursor != null) {
arret = new Arret(cursor);
cursor.close();
}
return arret;
}
public Arret getPassagePrecedent(long idStation, long idLigne, boolean nuit, int sens) {
String requete = "SELECT ARRET.* FROM ARRET, TRAJET, CIRCUIT WHERE " +
"ARRET.idStation = ? " +
"AND ARRET.idTrajet = TRAJET._id " +
"AND TRAJET.idCircuit = CIRCUIT._id " +
"AND CIRCUIT.idLigne = ? " +
"AND TRAJET.calendrier & ? > 0 " +
"AND ARRET.depart <= ? " +
"AND CIRCUIT.sens = ? " +
"AND ARRET.sequence < TRAJET.nbArrets " +
"ORDER BY depart desc LIMIT 1";
Cursor cursor = database.query(requete, new String[] { String.valueOf(idStation), String.valueOf(idLigne), JoursUtils.getCalendrier(nuit), Arret.getTimeInMinutes(nuit), String.valueOf(sens) });
Arret arret = null;
if (cursor != null) {
arret = new Arret(cursor);
cursor.close();
}
return arret;
}
public List<Arret> getProchainsPassages(long idStation, long idLigne, boolean nuit, int sens) {
String requete = "SELECT ARRET.* FROM ARRET, TRAJET, CIRCUIT WHERE " +
"ARRET.idStation = ? " +
"AND ARRET.idTrajet = TRAJET._id " +
"AND TRAJET.idCircuit = CIRCUIT._id " +
"AND CIRCUIT.idLigne = ? " +
"AND TRAJET.calendrier & ? > 0 " +
"AND ARRET.depart > ? " +
"AND CIRCUIT.sens = ? " +
"AND ARRET.sequence < TRAJET.nbArrets " +
"ORDER BY depart LIMIT 2";
Cursor cursor = database.query(requete, new String[] { String.valueOf(idStation), String.valueOf(idLigne), JoursUtils.getCalendrier(nuit), Arret.getTimeInMinutes(nuit), String.valueOf(sens) });
List<Arret> stopTimes = new ArrayList<Arret>();
if (cursor != null) {
do {
stopTimes.add(new Arret(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stopTimes;
}
public List<Arret> getPassagesDuJour(long idStation, long idLigne, Calendar calendrier, boolean nuit, boolean filtrePasse) {
String time = filtrePasse ? Arret.getTimeInMinutes(calendrier, nuit) : "0";
String requete = "SELECT ARRET.* FROM ARRET, TRAJET, CIRCUIT WHERE ARRET.idStation = ? AND ARRET.idTrajet = TRAJET._id AND TRAJET.idCircuit = CIRCUIT._id AND CIRCUIT.idLigne = ? AND "
+ "TRAJET.calendrier & ? > 0 and depart >= ? ORDER BY depart";
Cursor cursor = database.query(requete, new String[] { String.valueOf(idStation), String.valueOf(idLigne), JoursUtils.getCalendrier(calendrier, nuit), time });
List<Arret> stopTimes = new ArrayList<Arret>();
if (cursor != null) {
do {
stopTimes.add(new Arret(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stopTimes;
}
public List<Arret> getArrets(long idTrajet, long idStation) {
// AND sequence >= (select sequence from arret where idTrajet = ? and idStation = ?)
// String.valueOf(idTrajet), String.valueOf(idStation)
Cursor cursor = database.query("SELECT * FROM ARRET WHERE idTrajet = ? ORDER BY sequence",
new String[] { String.valueOf(idTrajet) });
List<Arret> stopTimes = new ArrayList<Arret>();
if (cursor != null) {
do {
stopTimes.add(new Arret(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stopTimes;
}
public Arret getArret(long idStation, long idCircuit, Calendar calendrier, boolean nuit) {
String requete = "SELECT ARRET.* FROM ARRET, TRAJET WHERE ARRET.idStation = ? AND ARRET.idTrajet = TRAJET._id AND TRAJET.idCircuit = ? AND "
+ "TRAJET.calendrier & ? > 0 AND ARRET.depart > ? ORDER BY depart LIMIT 1";
Cursor cursor = database.query(requete, new String[] { String.valueOf(idStation), String.valueOf(idCircuit), JoursUtils.getCalendrier(calendrier, nuit), Arret.getTimeInMinutes(calendrier, nuit) });
Arret arret = null;
if (cursor != null) {
arret = new Arret(cursor);
cursor.close();
}
return arret;
}
public Arret getArret(long idTrajet, long idStation) {
Cursor cursor = database.query("SELECT ARRET.* FROM ARRET, TRAJET WHERE ARRET.idStation = ? AND ARRET.idTrajet = ? LIMIT 1",
new String[] { String.valueOf(idStation), String.valueOf(idTrajet) });
Arret arret = null;
if (cursor != null) {
arret = new Arret(cursor);
cursor.close();
}
return arret;
}
public Ligne getLigne(long idLigne) {
Cursor cursor = database.query("select * from LIGNE where _id = ? LIMIT 1", new String[] { String.valueOf(idLigne) });
Ligne line = null;
if (cursor != null) {
line = new Ligne(cursor);
cursor.close();
}
return line;
}
public Ligne getLigne(String codeLigne) {
Cursor cursor = database.query("select * from LIGNE where code = ? LIMIT 1", new String[] { codeLigne });
Ligne line = null;
if (cursor != null) {
line = new Ligne(cursor);
cursor.close();
}
return line;
}
public long getTrajetSuivant(long idLigne, long idStation, Calendar calendrier, boolean nuit) {
String requete = "SELECT ARRET.idTrajet FROM ARRET, TRAJET, CIRCUIT WHERE ARRET.idStation = ? AND ARRET.idTrajet = TRAJET._id AND TRAJET.idCircuit = CIRCUIT._id AND CIRCUIT.idLigne = ? AND TRAJET.calendrier & ? > 0 AND depart > ? order by depart LIMIT 1";
Cursor cursor = database.query(requete,
new String[] { String.valueOf(idStation), String.valueOf(idLigne), JoursUtils.getCalendrier(calendrier, nuit), Arret.getTimeInMinutes(calendrier, nuit) });
long idTrajet = 0;
if (cursor != null) {
idTrajet = cursor.getLong(0);
cursor.close();
}
return idTrajet;
}
public long getTrajetPrecedent(long idLigne, long idStation, Calendar calendrier, boolean nuit) {
String requete = "SELECT ARRET.idTrajet FROM ARRET, TRAJET, CIRCUIT WHERE ARRET.idStation = ? AND ARRET.idTrajet = TRAJET._id AND TRAJET.idCircuit = CIRCUIT._id AND CIRCUIT.idLigne = ? AND TRAJET.calendrier & ? > 0 AND depart < ? order by depart DESC LIMIT 1";
Cursor cursor = database.query(requete,
new String[] { String.valueOf(idStation), String.valueOf(idLigne), JoursUtils.getCalendrier(calendrier, nuit), Arret.getTimeInMinutes(calendrier, nuit) });
long idTrajet = 0;
if (cursor != null) {
idTrajet = cursor.getLong(0);
cursor.close();
}
return idTrajet;
}
public String getIdStationLaPlusProche(double latitude, double longitude) {
String request = "select _id from STATION order by (latitude - ?) * (latitude - ?)+ (longitude - ?)* (longitude - ?) limit 1";
Cursor cursor = database.query(request, new String[] { String.valueOf(latitude), String.valueOf(latitude), String.valueOf(longitude), String.valueOf(longitude) });
String idStation = null;
if(cursor != null) {
idStation = cursor.getString(0);
cursor.close();
}
return idStation;
}
public List<Station> getStationsLesPlusProches(double latitude, double longitude) {
String request = "select s.* from station s where " +
" abs(s.latitude - ?) < 0.005 and " +
" abs(s.longitude - ?) < 0.005";
Cursor cursor = database.query(request, new String[] {String.valueOf(latitude), String.valueOf(longitude) });
List<Station> stations = new ArrayList<Station>();
if (cursor != null) {
do {
stations.add(new Station(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public List<Station> getStationsProches(StationGroup stationGroup) {
String request = "select s2.* from station s1, station s2 where " +
" s1.nom = ? and " +
" s1.commune = ? and " +
" abs(s1.latitude - s2.latitude) < 0.003 and " +
" abs(s1.longitude - s2.longitude) < 0.003";
Cursor cursor = database.query(request, new String[] {stationGroup.nom, stationGroup.commune });
List<Station> stations = new ArrayList<Station>();
if (cursor != null) {
do {
stations.add(new Station(cursor));
} while (cursor.moveToNext());
cursor.close();
}
return stations;
}
public boolean isCircuitValid(Circuit circuit, Station stationDepart, Station stationArrivee) {
String requete = "select * from station s1, station s2, station_circuit sc1, station_circuit sc2 where sc1.idCircuit = ? and sc2.idCircuit = sc1.idCircuit and sc1.idStation = s1._id and sc2.idStation = s2._id and s1._id = ? and s2._id = ? and sc1.sequence < sc2.sequence limit 1";
Cursor cursor = database.query(requete, new String[] { String.valueOf(circuit.id), String.valueOf(stationDepart.id), String.valueOf(stationArrivee.id) });
if(cursor != null) {
cursor.close();
return true;
}
return false;
}
public long[] getCorrespondance(Circuit circuit1, Station station1, Circuit circuit2, Station station2) {
String requeteCircuits = "select " +
"station1._id station1, " +
"station2._id station2 " +
"from " +
"(select s.*, (sc2.sequence - sc1.sequence) nbArrets from station s, station_circuit sc1, station_circuit sc2 where " +
" sc1.idCircuit = ? and sc1.idCircuit = sc2.idCircuit and " +
" sc1.idStation = ? and " +
" sc1.sequence < sc2.sequence and " +
" s._id = sc2.idStation " +
") station1, " +
"(select s.*, (sc1.sequence - sc2.sequence) nbArrets from station s, station_circuit sc1, station_circuit sc2 where " +
" sc1.idCircuit = ? and sc1.idCircuit = sc2.idCircuit and " +
" sc1.idStation = ? and " +
" sc1.sequence > sc2.sequence and " +
" s._id = sc2.idStation " +
") station2 " +
"where " +
"abs(station1.latitude - station2.latitude) < 0.0015 and " +
"abs(station1.longitude - station2.longitude) < 0.0015 " +
"order by station1.nbArrets + station2.nbArrets " +
"limit 1";
Cursor cursor = database.query(requeteCircuits, new String[] { String.valueOf(circuit1.id), String.valueOf(station1.id), String.valueOf(circuit2.id), String.valueOf(station2.id) });
if(cursor != null) {
long[] correspondance = new long[2];
correspondance[0] = cursor.getLong(0);
correspondance[1] = cursor.getLong(1);
cursor.close();
return correspondance;
}
return null;
}
public Long getCorrespondance(Circuit circuit, Station station, double latitude, double longitude, boolean retour) {
String requeteCircuits = "select " +
"station1._id " +
"from " +
"(select s.*, (sc2.sequence - sc1.sequence) nbArrets from station s, station_circuit sc1, station_circuit sc2 where " +
" sc1.idCircuit = ? and sc1.idCircuit = sc2.idCircuit and " +
" sc1.idStation = ? and " +
(retour ? " sc1.sequence > sc2.sequence and " : " sc1.sequence < sc2.sequence and ") +
" s._id = sc2.idStation " +
") station1 " +
"where " +
"abs(station1.latitude - ?) < 0.0015 and " +
"abs(station1.longitude - ?) < 0.0015 " +
"order by station1.nbArrets " +
"limit 1";
Cursor cursor = database.query(requeteCircuits, new String[] { String.valueOf(circuit.id), String.valueOf(station.id), String.valueOf(latitude), String.valueOf(longitude) });
if(cursor != null) {
long idStation = cursor.getLong(0);
cursor.close();
return idStation;
}
return null;
}
public Circuit getCircuit(Ligne ligne, Station station) {
String request = "select CIRCUIT.* from STATION_CIRCUIT, CIRCUIT where STATION_CIRCUIT.idCircuit = CIRCUIT._id AND STATION_CIRCUIT.idStation = ? AND CIRCUIT.idLigne = ?";
Cursor cursor = database.query(request, new String[] { String.valueOf(station.id), String.valueOf(ligne.id) });
Circuit circuit = null;
if (cursor != null) {
circuit = new Circuit(cursor);
cursor.close();
}
return circuit;
}
public Circuit getCircuitByIdTrajet(long idTrajet) {
String requete = "SELECT CIRCUIT.* FROM TRAJET, CIRCUIT WHERE TRAJET._id = ? AND TRAJET.idCircuit = CIRCUIT._id LIMIT 1";
Cursor cursor = database.query(requete, new String[] { String.valueOf(idTrajet) });
Circuit circuit = null;
if (cursor != null) {
circuit = new Circuit(cursor);
cursor.close();
}
return circuit;
}
}